[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
autECLSession.SetConnectionByName(ThisSessionName)
REM Variables
DIM pathfile, database, fso, dbo, OutFile, StoreNumber, i, ReturnValue, LineNumber,Stores
DIM StoreTextNum, StoreName, RYear, RQuarter, RPeriod, RWeek, Negative
DIM Sales883, Margin883, Returns883
DIM Sales887, Margin887, Returns887
DIM Budget
REM ***************************************************************
REM * DO NO EDIT ABOVE THIS LINE UNLESS YOU KNOW WHAT YOUR DOING! *
REM ***************************************************************
REM ***************************************************************
REM ***->This is where you want the file to be dumped
pathfile = "C:\Dist91Sales.csv"
database = "C:\StaplesTechSales.mdb"
REM ***->This is your list of stores
REM *** dim StoreList(x) should be one less then the number of stores Listed
REM *** Stores Should be the number of Stores
Stores = 14
dim StoreList (13)
StoreList (0) = "0286"
StoreList (1) = "0409"
StoreList (2) = "0424"
StoreList (3) = "0674"
StoreList (4) = "0718"
StoreList (5) = "0738"
StoreList (6) = "0800"
StoreList (7) = "0801"
StoreList (8) = "0816"
StoreList (9) = "0949"
StoreList (10) = "1013"
StoreList (11) = "1097"
StoreList (12) = "1343"
StoreList (13) = "1380"
REM ***************************************************************
REM ***************************************************************
REM * DO NO EDIT BELOW THIS LINE UNLESS YOU KNOW WHAT YOUR DOING! *
REM ***************************************************************
Set fso = CreateObject("Scripting.FileSystemObject")
Set dbo = CreateObject("ADODB.Connection")
Set OutFile = fso.CreateTextFile(pathfile, True)
dbo.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&database
LineNumber = 2
REM This line calls the macro subroutine
FetchNumbers
sub FetchNumbers()
REM Fetch Data TimePeriod, and Retreival Time/Date
dim DatYear,DatQuarter,DatPeriod,DatWeek,RetDate,RetTime
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SetCursorPos 16, 32
autECLSession.autECLPS.SendKeys "S"
autECLSession.autECLPS.SetCursorPos 17, 32
autECLSession.autECLPS.SendKeys StoreList(0)
autECLSession.autECLPS.SendKeys "[enter]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
DatYear = autECLSession.autECLPS.GetText( 4, 8, 2)
DatQuarter = autECLSession.autECLPS.GetText( 4, 21, 1)
DatPeriod = autECLSession.autECLPS.GetText( 4, 32, 2)
DatWeek = autECLSession.autECLPS.GetText( 4, 42, 1)
RYear = CInt(DatYear)
RQuarter = CInt(DatQuarter)
RPeriod = CInt(DatPeriod)
RWeek = CInt(DatWeek)
RetDate = autECLSession.autECLPS.GetText( 1, 72, 8)
RetTime = autECLSession.autECLPS.GetText( 2, 72, 8)
OutFile.WriteLine("Year:"&DatYear&",Quarter: "&DatQuarter&",Period: "&DatPeriod&",Week: "&DatWeek&",Pulled-> "&RetDate&" @ "&RetTime)
autECLSession.autECLPS.SendKeys "[pf12]"
REM LOOP THROUGH STORES
OutFile.WriteLine("StoreNum,883_Sales,883_Returns,883_Margin,887_Sales,887_Returns,887_Margin,TotalSales,TotalMargin")
for each StoreNumber in StoreList
LineNumber=LineNumber+1
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SetCursorPos 16, 32
autECLSession.autECLPS.SendKeys "S"
autECLSession.autECLPS.SetCursorPos 17, 32
autECLSession.autECLPS.SendKeys StoreNumber
autECLSession.autECLPS.SendKeys "[enter]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
REM Loop Roll to Tech Center Numbers
for i = 0 to 46
REM autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[roll up]"
next
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
rem Fetch Store Number
ReturnValue = Empty
do while (IsEmpty(ReturnValue) or (strComp(ReturnValue, Space(30)) = 0))
ReturnValue = autECLSession.autECLPS.GetText( 3, 12, 30)
loop
storetextnum = CInt(Left(ReturnValue, 4))
storename = LTrim(rtrim(right(ReturnValue, 26)))
OutFile.Write( """" & storetextnum & " " & storename & """" & "," )
REM Fetch Date
REM TODO
REM Sucks how you have to handle negatives off this AS/400
REM 883 Sales
REM negative
Negative=false
ReturnValue = Empty
do while IsEmpty(ReturnValue)
ReturnValue = autECLSession.autECLPS.GetText( 20, 25, 1)
loop
if not(strComp(ReturnValue, "-")) then Negative=true
REM Of the Value
ReturnValue = Empty
do while (IsEmpty(ReturnValue) or (strComp(ReturnValue, Space(10)) = 0))
ReturnValue = autECLSession.autECLPS.GetText( 20, 15, 10)
loop
Sales883=CInt(ReturnValue)
if Negative then Sales883 = Sales883*-1
OutFile.Write(Sales883 & ",")
REM Page over to Returns
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[pf11]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
REM 883 Returns
REM negative
Negative=false
ReturnValue = Empty
do while IsEmpty(ReturnValue)
ReturnValue = autECLSession.autECLPS.GetText( 20, 37, 1)
loop
if not(strComp(ReturnValue, "-")) then Negative=true
REM Of the Value
ReturnValue = Empty
do while (IsEmpty(ReturnValue) or (strComp(ReturnValue, Space(10)) = 0))
ReturnValue = autECLSession.autECLPS.GetText( 20, 27, 10)
loop
Returns883=CInt(ReturnValue)
if Negative then Returns883 = Returns883*-1
OutFile.Write(Returns883 & ",")
REM Page over to Margin
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[pf11]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
REM 883 Margin
REM negative
Negative=false
ReturnValue = Empty
do while IsEmpty(ReturnValue)
ReturnValue = autECLSession.autECLPS.GetText( 20, 25, 1)
loop
if not(strComp(ReturnValue, "-")) then Negative=true
REM Of the Value
ReturnValue = Empty
do while (IsEmpty(ReturnValue) or (strComp(ReturnValue, Space(10)) = 0))
ReturnValue = autECLSession.autECLPS.GetText( 20, 15, 10)
loop
Margin883=CInt(ReturnValue)
if Negative then Margin883 = Margin883*-1
OutFile.Write(Margin883 & ",")
REM Page back to Sales
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[pf11]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
REM Roll to 887
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
autECLSession.autECLPS.SendKeys "[roll up]"
autECLSession.autECLPS.SendKeys "[pf11]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
REM 887 Sales
REM negative
Negative=false
ReturnValue = Empty
do while IsEmpty(ReturnValue)
ReturnValue = autECLSession.autECLPS.GetText( 8, 25, 1)
loop
if not(strComp(ReturnValue, "-")) then Negative=true
REM Of the Value
ReturnValue = Empty
do while (IsEmpty(ReturnValue) or (strComp(ReturnValue, Space(10)) = 0))
ReturnValue = autECLSession.autECLPS.GetText( 8, 15, 10)
loop
Sales887=CInt(ReturnValue)
if Negative then Sales887 = Sales887*-1
OutFile.Write(Sales887 & ",")
REM Page over to Returns
autECLSession.autECLPS.SendKeys "[pf11]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
REM 887 Returns
REM negative
Negative=false
ReturnValue = Empty
do while IsEmpty(ReturnValue)
ReturnValue = autECLSession.autECLPS.GetText( 8, 37, 1)
loop
if not(strComp(ReturnValue, "-")) then Negative=true
REM Of the Value
ReturnValue = Empty
do while (IsEmpty(ReturnValue) or (strComp(ReturnValue, Space(10)) = 0))
ReturnValue = autECLSession.autECLPS.GetText( 8, 27, 10)
loop
Sales887=CInt(ReturnValue)
if Negative then Returns887 = Returns887*-1
OutFile.Write(Returns887 & ",")
REM Page over to Margin
autECLSession.autECLPS.SendKeys "[pf11]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
REM 887 Margin
REM negative
Negative=false
ReturnValue = Empty
do while IsEmpty(ReturnValue)
ReturnValue = autECLSession.autECLPS.GetText( 8, 25, 1)
loop
if not(strComp(ReturnValue, "-")) then Negative=true
REM Of the Value
ReturnValue = Empty
do while (IsEmpty(ReturnValue) or (strComp(ReturnValue, Space(10)) = 0))
ReturnValue = autECLSession.autECLPS.GetText( 8, 15, 10)
loop
Margin887=CInt(ReturnValue)
if Negative then Margin887 = Margin887*-1
OutFile.Write(Margin887 & ",")
REM Total Budget
REM negative
Negative=false
ReturnValue = Empty
do while IsEmpty(ReturnValue)
ReturnValue = autECLSession.autECLPS.GetText( 9, 37, 1)
loop
if not(strComp(ReturnValue, "-")) then Negative=true
REM Of the Value
ReturnValue = Empty
do while (IsEmpty(ReturnValue) or (strComp(ReturnValue, Space(10)) = 0))
ReturnValue = autECLSession.autECLPS.GetText( 9, 27, 10)
loop
Budget=CInt(ReturnValue)
if Negative then Budget = Budget*-1
REM OutFile.Write(Budget & ",")
ReturnValue = Empty
ReturnValue = dbo.Execute("SELECT COUNT(*) AS RecordExists FROM SalesTable WHERE Year = " & RYear & " AND Period = " & RPeriod & " AND Week = " & RWeek & " AND Store = " & Storetextnum)
MsgBox ReturnValue
if ReturnValue = 0 then
MsgBox "New: """ & storetextnum & " " & storename & """"
dbo.Execute("INSERT INTO SalesTable (Year, Quarter, Period, Week, Store, Sales883, Return883, Margin883, Sales887, Return887, Margin887, Budget ) VALUES ( "& RYear & ", " & RQuarter & ", " & RPeriod & ", " & RWeek & ", " & Storetextnum & ", " & Sales883 & ", " & Returns883 & ", " & Margin883 & ", " & Sales887 & ", " & Returns887 & ", " & Margin887 & ", " & Budget & ");")
else
MsgBox "Exists: """ & storetextnum & " " & storename & """"
dbo.Execute("UPDATE SalesTable SET Sales883 = " & Sales883 & ", Returns883 = " & Returns883 & ", Margin883 = " & Margin883 & ", Sales887 = " & Sales887 & ", Returns887 = " & Returns887 & ", Margin887 = " & Margin887 & ", Budget = " & Budget & " WHERE Year = " & RYear & " AND Period = " & RPeriod & " AND Week = " & RWeek & " AND Store = " & Store & ";")
end If
REM Page back to Sales
autECLSession.autECLPS.SendKeys "[pf11]"
autECLSession.autECLOIA.WaitForAppAvailable
autECLSession.autECLOIA.WaitForInputReady
REM Final Numbers
OutFile.Write("=B"&LineNumber&"+E"&LineNumber&",")
OutFile.WriteLine("=D"&LineNumber&"+G"&LineNumber)
autECLSession.autECLPS.SendKeys "[pf12]"
next
OutFile.WriteLine("Totals ,=SUM(B3:B"&Stores+2&"),=SUM(C3:C"&Stores+2&"),=SUM(D3:D"&Stores+2&"),=SUM(E3:E"&Stores+2&"),=SUM(F3:F"&Stores+2&"),=SUM(G3:G"&Stores+2&"),=SUM(H3:H"&Stores+2&"),=SUM(I3:I"&Stores+2&")")
OutFile.Close
end sub