#NoTrayIcon
#RequireAdmin
#Region ;**** Directives created by AutoIt3Wrapper_GUI ****
#AutoIt3Wrapper_Run_Tidy=y
#AutoIt3Wrapper_Run_Au3Stripper=y
#Au3Stripper_Parameters=/mo/rsln
#EndRegion ;**** Directives created by AutoIt3Wrapper_GUI ****

#include <Array.au3>
#include <File.au3>
#include <Excel.au3>
#include <Date.au3>

Example()


Func Example()
	;=========>>>>>>>> Exclusion File list
	Local $ID = '/Check/Detail/UpdateData/@ID'
	Local $Inst = '/Check/Detail/UpdateData/@IsInstalled'
	Local $Down = '/Check/Detail/UpdateData/References/DownloadURL'
	Local $Title = '/Check/Detail/UpdateData/Title'
	Local $Restart = '/Check/Detail/UpdateData/@RestartRequired'
	Local $KBID = '/Check/Detail/UpdateData/@KBID'

	Local $GetFilePath = "C:\Temp\Test"

	Local $oExcel = _Excel_Open(False)
	Local $xls_result = $GetFilePath & "\Results.xls"
	Local $oWorkbook = _Excel_BookOpen($oExcel, $xls_result)

	Local $XMLOut = _Excel_RangeRead($oWorkbook, Default, "A1")
	Select
		Case $XMLOut = "/XMLOut"
			_Excel_RangeDelete($oWorkbook.ActiveSheet, '1:1', 1) ; Delete 1st line as /XMLOut
			_Excel_BookSave($oWorkbook)
	EndSelect

	Local $ColNum = $oWorkbook.ActiveSheet.UsedRange.Columns.Count ; Get last column Number
	Local $sTab7
	Local $ColName

	For $j = $ColNum To 1 Step -1
		Local $GetFilePath = "C:\Temp\Test"
		Local $FilePath1 = $GetFilePath & "\Output.txt"
		Local $Output_FileOpen = FileOpen($FilePath1, 2)
		Local $FileWrite = FileWrite($Output_FileOpen, $j)
		FileClose($Output_FileOpen)
		Local $TotalCol = FileRead($FilePath1)
		Local $ColName = _Excel_ColumnToLetter($TotalCol) ; Get last column Header Name
		Local $sTab7 = _Excel_RangeRead($oWorkbook, Default, $ColName & '1')
		If $sTab7 = $ID Or $sTab7 = $Inst Or $sTab7 = $Down Or $sTab7 = $Title Or $sTab7 = $Restart Or $sTab7 = $KBID Then
			; Do nothing...

		Else
			_Excel_RangeDelete($oWorkbook.ActiveSheet, $ColName & ':' & $ColName)
		EndIf

		Local $sTab8 = _Excel_RangeRead($oWorkbook, Default, $ColName & '2')
		Local $sTab9 = _Excel_RangeRead($oWorkbook, Default, $ColName & '3')
		If $sTab8 = '' And $sTab9 <> '' Then

			_Excel_RangeDelete($oWorkbook.ActiveSheet, '2:2', 1) ; Delete 1st line if blank but not
		EndIf

		If $sTab7 = $ID Or $sTab7 = $Inst Then
			Local $iCountLines = $oWorkbook.ActiveSheet.UsedRange.Rows.Count

			For $i = $iCountLines To 1 Step -1
				Local $sTab10 = _Excel_RangeRead($oWorkbook, Default, $ColName & $i)
				If $sTab10 = 'True' Then
					_Excel_RangeDelete($oWorkbook.ActiveSheet, $i & ":" & $i, 1)
				EndIf

				Local $sTab11 = _Excel_RangeRead($oWorkbook, Default, $ColName & $i)
				Local $v_Return = Column_GetValues($GetFilePath & "\GetExclusions.xls", "B", "A")
				Local $ExcludeFilePath = $GetFilePath & "\FinalExclusion.txt"

				Local $FinalExclusion_FileOpen = FileOpen($ExcludeFilePath, 1)
				For $f = 1 To _FileCountLines($ExcludeFilePath)
					$ExcludeFile = FileReadLine($ExcludeFilePath, $f)

					If $sTab11 = $ExcludeFile Then
						_Excel_RangeDelete($oWorkbook.ActiveSheet, $i & ":" & $i, 1)
					EndIf
				Next
				FileClose($FinalExclusion_FileOpen)
			Next
		EndIf
	Next

	_Excel_BookClose($oExcel)
	_Excel_Close($oExcel)


	MsgBox(0, '', 'Completed')


EndFunc   ;==>Example


Func Column_GetValues($s_WorkBook_Exclusion, $s_DateColumn, $s_ReturnColumn, $i_ReturnArray = 0)
	Local $o_Excel = _Excel_Open(False)
	Local $o_WorkBook_Exclusion = _Excel_BookOpen($o_Excel, $s_WorkBook_Exclusion)
	Local $as_RangeRead = _Excel_RangeRead($o_WorkBook_Exclusion)

	;=========>>>>>>>> convert the column with the date to a number and - 1 to give the corresponding array column
	Local $i_DateColumn = _Excel_ColumnToNumber($s_DateColumn) - 1

	;=========>>>>>>>> get a number for the column with the desired return value and -1 to give the array column
	Local $i_ReturnColumn = _Excel_ColumnToNumber($s_ReturnColumn) - 1

	;=========>>>>>>>> check the array max values
	If $i_DateColumn > UBound($as_RangeRead, $UBOUND_COLUMNS - 1) Then Return SetError(1, 0, 'Column (' & $s_DateColumn & ') not found in ' & $s_WorkBook_Exclusion)
	If $i_ReturnColumn > UBound($as_RangeRead, $UBOUND_COLUMNS - 1) Then Return SetError(1, 0, 'Column (' & $s_ReturnColumn & ') not found in ' & $s_WorkBook_Exclusion)

	Local $s_Return = ''

	Local $GetFilePath = "C:\Temp\Test"
	Local $ExcludeFilePath = $GetFilePath & "\FinalExclusion.txt" ; Create FinalExclusion.txt file to input the Exclusion Patch details from PatchExclusions.xls
	Local $FileOpen = FileOpen($ExcludeFilePath, 2)

	For $i = 1 To UBound($as_RangeRead) - 1
		If ReturnDateFormat($as_RangeRead[$i][$i_DateColumn]) >= Number(StringRegExpReplace(_NowCalcDate(), '\D', '') & '000000') Then
			FileWrite($FileOpen, $as_RangeRead[$i][$i_ReturnColumn] & @CRLF)
		EndIf
	Next

	_Excel_BookClose($o_Excel)
	FileClose($FileOpen)

	$s_Return = StringTrimRight($s_Return, 1)

	If $i_ReturnArray Then Return StringSplit($s_Return, '|')
	Return $s_Return
EndFunc   ;==>Column_GetValues



Func ReturnDateFormat($v_Date)
	;=========>>>>>>>> check if the date value has forward slashes i.e 20/5/2107
	;=========>>>>>>>> as excel cells could be in General format
	If StringInStr($v_Date, '/') Then
		Local $as_Date = StringSplit($v_Date, '/')
		;=========>>>>>>>> convert to number and desired format (YYYYMMDD) and pad with zeros to correct length
		$v_Date = StringFormat("%04i%02i%02i", $as_Date[3], $as_Date[1], $as_Date[2]) & '000000'
	EndIf

	;=========>>>>>>>> the excel cells could be in date format and returns something like 20170520000000
	;=========>>>>>>>> so convert date to number for comparison
	Return Number($v_Date)
EndFunc   ;==>ReturnDateFormat