#region --- Au3Recorder generated code Start (v3.3.9.5 KeyboardLayout=00000409)  ---

#region --- Internal functions Au3Recorder Start ---
Func _Au3RecordSetup()
Opt('WinWaitDelay',100)
Opt('WinDetectHiddenText',1)
Opt('MouseCoordMode',0)
Local $aResult = DllCall('User32.dll', 'int', 'GetKeyboardLayoutNameW', 'wstr', '')
If $aResult[1] <> '00000409' Then
  MsgBox(64, 'Warning', 'Recording has been done under a different Keyboard layout' & @CRLF & '(00000409->' & $aResult[1] & ')')
EndIf

EndFunc

Func _WinWaitActivate($title,$text,$timeout=0)
	WinWait($title,$text,$timeout)
	If Not WinActive($title,$text) Then WinActivate($title,$text)
	WinWaitActive($title,$text,$timeout)
EndFunc

_AU3RecordSetup()
#endregion --- Internal functions Au3Recorder End ---
#include <Excel.au3>
#include <MsgBoxConstants.au3>
#include <Date.au3>

Global $g_bPaused = False

HotKeySet("{PAUSE}", "TogglePause")
HotKeySet("{ESC}", "Terminate")


Local Const $sMessage = "Choose list from therapists"
Local $sWorkbook = FileOpenDialog ( $sMessage, @WindowsDir, "Excel (*.xls;*.xlsx;*.csv;*.txt)")
Local $oExcel = _Excel_Open()
   If @error Then Exit MsgBox(16, "Excel UDF: _Excel_BookOpen Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)

global $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook)
global $totalrows = $oExcel.ActiveSheet.UsedRange.Rows.Count
global $sRowStart = InputBox("Enter Row Number", "Enter the row you would like to start at", "")
global $sStatMonth = InputBox("Enter Which Month", "which month is the stats for? e.g. 05", "")
Call("EnterInfo", $oWorkbook, $totalrows, $sRowStart, $sStatMonth)

Func EnterInfo($oWorkbook, $totalrows, $sRowStart, $sStatMonth)

   local $i = $sRowStart
   Do
   ToolTip('Row - ' & $i & ' - PAUSE" to Pause - "ESC" to stop')
	  $sRowStart = $i
	  Local $sStartDate = 0
	  Local $sStartTime = 0
	  Local $sEndTime = 0
	  Local $sActivityType = 0
	  local $sStartDateSkip = ""
	  $sStartDate = _Excel_RangeRead($oWorkbook, Default, "Q" & $i)
	  $sStartDate1 = StringSplit(_Excel_RangeRead($oWorkbook, Default, "Q" & $i), "/")
	  if  StringLen($sStartDate) > 1 Then ; Check if startdate is not empty
		 if StringLen($sStartDate) = 14 Then ; Check if YYYYMMDDhhmmss
			if StringMid($sStartDate, 5, 2) = $sStatMonth then
			   $sStartDate = StringMid($sStartDate, 7, 2) & "-" & StringMid($sStartDate, 5, 2) & "-" & StringLeft($sStartDate,4)
			Else
			   $sStartDate = StringMid($sStartDate, 5, 2) & "-" & StringMid($sStartDate, 7, 2) & "-" & StringLeft($sStartDate,4)
			Endif
		 Elseif  StringLen($sStartDate1[1]) = 4 And StringLen($sStartDate1[2]) = 2 And StringLen($sStartDate1[3]) = 2 Then; check if yyyy/mm/dd
			if $sStartDate1[2] = $sStatMonth then
			$sStartDate = $sStartDate1[3] & "-" & $sStartDate1[2] & "-" & $sStartDate1[1]
		 Else
			$sStartDate = $sStartDate1[2] & "-" & $sStartDate1[3] & "-" & $sStartDate1[1]
		 Endif
		 ElseIf StringLen($sStartDate1[3]) = 4 And StringLen($sStartDate1[2]) = 2 And StringLen($sStartDate1[1]) = 2 Then; check if dd/mm/yyyy
			if $sStartDate1[2] = $sStatMonth then
			   $sStartDate = $sStartDate1[1] & "-" & $sStartDate1[2] & "-" & $sStartDate1[3]
			Else
			   $sStartDate = $sStartDate1[2] & "-" & $sStartDate1[1] & "-" & $sStartDate1[3]
			Endif
		 Else ; return error on date format
			$sStartDateSkip = "YES"
			MsgBox($MB_SYSTEMMODAL, "", "stringlength - " & StringLen($sStartDate) & " - is not formatted correctly. Please fix, manually enter then click ok to continue")
			MsgBox($MB_SYSTEMMODAL, "", " Error in startdate - " & $sStartDate & " - is not formatted correctly. Please fix, manually enter then click ok to continue")
		 EndIf
	  EndIf
	  $sStartTime = _Excel_RangeRead($oWorkbook, Default, "S" & $i)
	  $sStartTime = StringLeft ( $sStartTime , 4)
	  if StringLen ($sStartTime) = 3 then
		 $sStartTime = "0" & $sStartTime
	  Endif
	  $sStartTime = StringMid($sStartTime, 1, 2) & ":" & StringMid($sStartTime, 3, 2)
	  $sEndTime = _Excel_RangeRead($oWorkbook, Default, "T" & $i)
	  $sEndTime = StringLeft ( $sEndTime , 4)
	  if StringLen ($sEndTime) = 3 then
		 $sEndTime = "0" & $sEndTime
	  Endif
	  $sEndTime = StringMid($sEndTime, 1, 2) & ":" & StringMid($sEndTime, 3, 2)
	  $sActivityType = _Excel_RangeRead($oWorkbook, Default, "V" & $i)
	  $sActivitySetting = _Excel_RangeRead($oWorkbook, Default, "AB" & $i)
	  if StringLen($sStartDate) > 6  then
	  _WinWaitActivate("Default remote desktop for Jem - TTOHRDS1.HERETAUNGA.TTOH.IWI.NZ - Remote Desktop Connection","Default remote deskt")
		 sleep(500)
		 if $sStartDateSkip <> "Yes" Then
			Send($sStartDate)
		 Endif
		 send("{TAB}")
		 sleep(500)
		 send("{TAB}")
		 sleep(500)
		 Send($sStartTime)
		 send("{TAB}")
		 sleep(500)
		 send("{TAB}")
		 sleep(500)
		 send("{TAB}")
		 sleep(500)
		 Send($sEndTime)
		 send("{TAB}")
		 sleep(500)
		 send($sActivityType)
		 send("{TAB}")
		 sleep(500)
		 send($sActivitySetting)
		 send("{TAB}")
		 sleep(500)
		 send("{TAB}")
		 sleep(2000)
		 send("{ENTER}")
		 sleep(2500)
		#comments-start
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(700)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(700)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(700)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(700)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(700)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(700)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(700)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(700)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 sleep(1500)
		 send("{SHIFTDOWN}{TAB}{SHIFTUP}")
		 #comments-end
		 MouseClick ("left")
		 sleep(100)
		 $i = $i + 1
	  Else
		 Local $sNHI = 0
		 Local $sGender = 0
		 Local $sDOB = 0
		 Local $sRefer = 0
		 Local $sReferDate = 0
		 Local $sReferStartDate = 0
	  Local $sDischargeDate = 0
	  Local $sNHIdischarge = 0
	  $sNHIdischarge = _Excel_RangeRead($oWorkbook, Default, "A" & ($i - 1))
	  $sDischargeDate = _Excel_RangeRead($oWorkbook, Default, "L" & ($i - 1))
	  $sDischargeDate = StringMid($sDischargeDate, 7, 2) & "-" & StringMid($sDischargeDate, 5, 2) & "-" & StringLeft($sDischargeDate,4)
	  if StringLen ($sDischargeDate) > 6 Then
			 MsgBox($MB_SYSTEMMODAL, "", $sNHIdischarge & " has been discharged on " & $sDischargeDate & " click ok to continue")
	  Endif
		 Do
			$i = $i + 1
			$sNHI = _Excel_RangeRead($oWorkbook, Default, "A" & $i)
			$sGender = _Excel_RangeRead($oWorkbook, Default, "B" & $i)
			$sDOB = _Excel_RangeRead($oWorkbook, Default, "E" & $i)
			$sDOB1 = StringSplit(_Excel_RangeRead($oWorkbook, Default, "E" & $i), "/")

			if  StringLen($sDOB) > 1 Then ; Check if startdate is not empty
			   if StringLen($sDOB) = 14 Then ; Check if YYYYMMDDhhmmss
				  $sDOB = StringMid($sDOB, 7, 2) & "-" & StringMid($sDOB, 5, 2) & "-" & StringLeft($sDOB,4)
			   Elseif  StringLen($sDOB1[1]) = 4 And StringLen($sDOB1[2]) = 2 And StringLen($sDOB1[3]) = 2 Then; check if yyyy/mm/dd
				  $sDOB = $sDOB1[3] & "-" & $sDOB1[2] & "-" & $sDOB1[1]
			   ElseIf StringLen($sDOB1[3]) = 4 And StringLen($sDOB1[2]) = 2 And StringLen($sDOB1[1]) = 2 Then; check if dd/mm/yyyy
					 $sDOB = $sDOB1[1] & "-" & $sDOB1[2] & "-" & $sDOB1[3]
			   Else ; return error on date format
				  $sDOB = $sDOB & 'is not formatted correctly'
			   EndIf
			EndIf
			$sRefer = _Excel_RangeRead($oWorkbook, Default, "H" & $i)
			$sReferDate = _Excel_RangeRead($oWorkbook, Default, "K" & $i)
			$sReferDate1 = StringSplit(_Excel_RangeRead($oWorkbook, Default, "K" & $i), "/")

			if  StringLen($sReferDate) > 1 Then ; Check if startdate is not empty
			   if StringLen($sReferDate) = 14 Then ; Check if YYYYMMDDhhmmss
				  $sReferDate = StringMid($sReferDate, 7, 2) & "-" & StringMid($sReferDate, 5, 2) & "-" & StringLeft($sReferDate,4)
			   Elseif  StringLen($sReferDate1[1]) = 4 And StringLen($sReferDate1[2]) = 2 And StringLen($sReferDate1[3]) = 2 Then; check if yyyy/mm/dd
				  $sReferDate = $sReferDate1[3] & "-" & $sReferDate1[2] & "-" & $sReferDate1[1]
			   ElseIf StringLen($sReferDate1[3]) = 4 And StringLen($sReferDate1[2]) = 2 And StringLen($sReferDate1[1]) = 2 Then; check if dd/mm/yyyy
				  $sReferDate = $sReferDate1[1] & "-" & $sReferDate1[2] & "-" & $sReferDate1[3]
			   Else ; return error on date format
				  $sReferDate = $sReferDate & 'is not formatted correctly'
			   EndIf
			EndIf
			$sReferStartDate = _Excel_RangeRead($oWorkbook, Default, "Q" & $i)
			$sReferStartDate1 = StringSplit(_Excel_RangeRead($oWorkbook, Default, "Q" & $i), "/")

			if  StringLen($sReferStartDate) > 1 Then ; Check if startdate is not empty
			   if StringLen($sReferStartDate) = 14 Then ; Check if YYYYMMDDhhmmss
				  $sReferStartDate = StringMid($sReferStartDate, 7, 2) & "-" & StringMid($sReferStartDate, 5, 2) & "-" & StringLeft($sReferStartDate,4)
			   Elseif  StringLen($sReferStartDate1[1]) = 4 And StringLen($sReferStartDate1[2]) = 2 And StringLen($sReferStartDate1[3]) = 2 Then; check if yyyy/mm/dd
				  $sReferStartDate = $sReferStartDate1[3] & "-" & $sReferStartDate1[2] & "-" & $sReferStartDate1[1]
			   ElseIf StringLen($sReferStartDate1[3]) = 4 And StringLen($sReferStartDate1[2]) = 2 And StringLen($sReferStartDate1[1]) = 2 Then; check if dd/mm/yyyy
				  $sReferStartDate = $sReferStartDate1[1] & "-" & $sReferStartDate1[2] & "-" & $sReferStartDate1[3]
			   Else ; return error on date format
				  $sReferStartDate = $sReferStartDate & 'is not formatted correctly'
			   EndIf
			EndIf
		 Until StringLen($sNHI) > 1

		 MsgBox($MB_OK, "", "NEW ENTRY - " & $sNHI & @LF & "NHI: " & $sNHI & @LF & "Gender: " & $sGender & @LF & "Date of Birth:" & $sDOB & @LF & "Referral From:" & $sRefer & @LF & "Refer Date:" & $sReferDate & @LF & "Start Date:" & $sReferStartDate & @LF & "click ok to continue")

		 MsgBox($MB_SYSTEMMODAL, "", $sNHI & " is a new person and needs to be changed to them. Please change to them and click ok to continue")
	  Endif
   Until $i = $totalrows + 1
   MsgBox($MB_SYSTEMMODAL, "", "Successfully completed adding information")
EndFunc   ;==>Enterinfo

Func TogglePause()
   $g_bPaused = Not $g_bPaused
   ToolTip("")
   While $g_bPaused
	  Sleep(100)
	  ToolTip('Row - ' & $sRowStart & ' - Script is "Paused" - press "Pause" again to restart')
   WEnd
   if $g_bPaused = False then
	  If MsgBox(68, "", "Do you want to start entry again (otherwise it will resume from where it was paused?") = 6 Then ; YES,
		 If MsgBox(68, "", "Do you want to set the start row not (will continue from current row otherwise)?") = 6 Then ; YES,
			$sRowStart = InputBox("Enter Row Number", "Enter the row you would like to start at", "")
			Call("EnterInfo", $oWorkbook, $totalrows, $sRowStart, $sStatMonth)
		 Else
			Call("EnterInfo", $oWorkbook, $totalrows, $sRowStart, $sStatMonth)
		 Endif
	  EndIf
   EndIf
EndFunc   ;==>TogglePause



 Func Terminate()
    Exit
EndFunc   ;==>Terminate