[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