#include <ButtonConstants.au3>
#include <EditConstants.au3>
#include <GUIConstantsEx.au3>
#include <WindowsConstants.au3>
#include <GuiRichEdit.au3>
#include <File.au3>
#include <MsgBoxConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <FileConstants.au3>

$Form1 = GUICreate("Test", 800, 600) ;Creates The GUI and makes it centered in the screen
Global $Edit1 = _GUICtrlRichEdit_Create($Form1, "", 8, 8, 393, 541, BitOR($ES_WANTRETURN, $ES_MULTILINE, $WS_VSCROLL, $ES_AUTOVSCROLL, $WS_HSCROLL, $ES_AUTOHSCROLL)) ;Creates The richtext edit and makes it global, so that every function can read it out
Global $Edit2 = _GUICtrlRichEdit_Create($Form1, "", 400, 8, 393, 541, BitOR($ES_WANTRETURN, $ES_MULTILINE, $WS_VSCROLL, $ES_AUTOVSCROLL, $WS_HSCROLL, $ES_AUTOHSCROLL)) ;Creates The richtext edit and makes it global, so that every function can read it out
GUICtrlSetData(-1, "")


$Button3 = GUICtrlCreateButton("Close", 304, 564, 89, 25) ;creates a button

$Label1 = GUICtrlCreateLabel("0",650,564,100,25)


 _GUICtrlRichEdit_StreamFromFile($Edit1, "testing.rtf")
 $s = _GUICtrlRichEdit_StreamToVar($Edit1)
_GUICtrlRichEdit_StreamFromVar($Edit2, $s)
$counter = 1


$oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
$oWorkbook = _Excel_BookOpen($oExcel, "Test_data.xlsx")
;Local $oWorkbook = _Excel_BookOpen($oExcel, "C:\Users\knightstalker\Desktop\PMTA\unc70602-Product_Listings-07252019-124028.xlsx")

If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "_Excel1.xls'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf


$aRange = _Excel_RangeRead($oWorkbook, 2, "A2:T207", 1)
    ;_ArrayDisplay($aRange)
$Varx = ""
GUISetState(@SW_SHOW)
for $x = 1 to 3

	 Local $Var3        =   _Excel_RangeRead($oWorkbook, 1, "A" & $x+1, 1)
	 $Varx = "$Var3"
	 $Vary = $Var3
	runner($s)
	_GUICtrlRichEdit_StreamFromVar($Edit2, $s)

	 Local $Var1		=	_Excel_RangeRead($oWorkbook, 1, "D" & $x+1, 1)
	 $Varx = "$Var1"
	 $Vary = $Var1
	runner($s)
	_GUICtrlRichEdit_StreamFromVar($Edit2, $s)

	 Local $Var2		=	_Excel_RangeRead($oWorkbook, 1, "G" & $x+1, 1)
	 $Varx = "$Var2"
	 $Vary = $Var2
	runner($s)
	_GUICtrlRichEdit_StreamFromVar($Edit2, $s)



	 $splitSize = StringSplit($Var1,",")
	 $Var4				=	$splitSize[3]
	 $Varx = "$Var4"
	 $Vary = $Var4
	runner($s)
	_GUICtrlRichEdit_StreamFromVar($Edit2, $s)

	 $Var6				=	$splitSize[2]
	 $Varx = "$Var6"
	 $Vary = $Var6
	runner($s)
	_GUICtrlRichEdit_StreamFromVar($Edit2, $s)

	 Local $Var9		=	_Excel_RangeRead($oWorkbook, 1, "F" & $x+1, 1)
	 $Varx = "$Var9"
	 $Vary = $Var9
	runner($s)
	_GUICtrlRichEdit_StreamFromVar($Edit2, $s)

	 Local $Var5a		=	_Excel_RangeRead($oWorkbook, 1, "E" & $x+1, 1)



	;MsgBox($MB_SYSTEMMODAL,"Range",$Var1 & @CRLF & $Var2 & @CRLF & $Var3 & @CRLF & $Var4 & @CRLF & $Var5 & @CRLF & $Var6 & @CRLF & $Var7 & @CRLF & $splitSize[1] & $Var2)


	$splitFlavor = StringSplit($Var5a,",")
	 $Var5 = ""
	 $Var7 = ""
	 $Var8 = ""

	 for $y = 1 to Ubound($splitFlavor)-1
		 $iIndex = _ArraySearch($aRange, $splitFlavor[$y], 0, 0, 0, 1, 1, 5)
		 $Var7 =  $aRange[$iIndex][4] & " purchased by " & $aRange[$iIndex][18]
		 $Var8 =  $aRange[$iIndex][19]
		if $y <> UBound($splitFlavor)-1 Then

			;$Var8 =  $aRange[$iIndex][19] & @CRLF & " $Var7" & @CRLF & " $Var8" & @CRLF
			$Var8 =  $aRange[$iIndex][19] & @CRLF & "$Var7" & @CRLF & "$Var8"

		 Else
			 ;$Var8 =  $aRange[$iIndex][19]
		 EndIf




		 	 $Varx = "$Var7"
			 $Vary = $Var7 & @CRLF
			runner($s)
			_GUICtrlRichEdit_StreamFromVar($Edit2, $s)

		 	 $Varx = "$Var8"
			 $Vary = $Var8
			runner($s)
			_GUICtrlRichEdit_StreamFromVar($Edit2, $s)
		MsgBox($MB_SYSTEMMODAL,"Range",$Var7 & @CRLF & $Var8)

	 Next

	_save()
	$s = _GUICtrlRichEdit_StreamToVar($Edit1)
	_GUICtrlRichEdit_StreamFromVar($Edit2, $s)
Next


While 1
    $msg = GUIGetMsg() ;gets the GUI "changes"
    Switch $msg
        Case $GUI_EVENT_CLOSE ;on click on the X
            _GUICtrlRichEdit_Destroy($Edit1) ;VERY IMPORTANT!
            Exit
        Case $Button3
            _GUICtrlRichEdit_Destroy($Edit1) ;IMPORTANT before exit!
            Exit

    EndSwitch ;Ends the switch
WEnd ;ends the while

Func _save()


	$savetofile =  _GUICtrlRichEdit_StreamToFile($Edit2, "output" & $counter & ".rtf")
	$counter += 1
	 GUICtrlSetData($Label1, $counter)

EndFunc
Func runner(ByRef $s1)

	Local $sString = StringReplace($s1, $Varx, $Vary)
	$s1 = $sString

EndFunc
Func runner2(ByRef $s1)

	$Vary = "." & @CRLF & "."
	Local $sString = StringReplace($s1, $Varx, $Vary)
	$s1 = $sString

EndFunc