script that does stuff

user = "xxxxxx"
pass = "xxxxxxxxx"
ip = "xxxxxxxxxxxxxxx"

Set fs = CreateObject("Scripting.FileSystemObject")

fs.MoveFile "sendrpts_importodbs_logfile.txt", "sendrpts_importodbs_logfile.fileinuse"

set logfile = fs.OpenTextFile("sendrpts_importodbs_logfile.fileinuse",8, True)

daysago = 1

testing = 0

yy = mid(year(now-daysago),3,2)
mn = monthname(month(now-daysago),true)

if month(now-daysago) < 10 then mm = "0" & month(now-daysago) else mm = month(now-daysago)

if day(now-daysago) < 10 then dd = "0" & day(now-daysago) else dd = day(now-daysago)
datestring = dd & mn & yy

logfile.writeline("============== BEGINNING SCRIPT " & now & "===================")

if (testing) then
    rptsemail = "xxxxxxxxx"
    dailyrptsemail = rptsemail

    logfile.writeline ("TESTING MODE IS ACTIVE FOR THIS RUN")

else
    rptsemail = "xxxxxxxxxxxxxxxxxx"
    dailyrptsemail = "xxxxxxxxxxxxxxxxxxxx"
end if

odbsfilename = "ews_odbs_reports_" & datestring & ".zip"
rptsfilename = "ews_rpts_reports_" & datestring & ".zip"
dailyrptsfilename = "ews_daily_reports_" & datestring & ".zip"

destinationfolder = "c:\inetpub\wwwroot\ews\scripts\"

'msgbox (odbfilename)







strPath = wscript.ScriptFullName
Set a = fs.CreateTextFile("c:\winscp\ftpcommands.ftp", True)
a.WriteLine ("option batch on")
a.WriteLine ("option confirm off")
a.WriteLine ("open sftp://" & user & ":" & pass & "@" & ip)
a.writeline ("get " & rptsfilename)
a.writeline ("get " & dailyrptsfilename)
a.writeline ("get " & odbsfilename)
a.WriteLine ("close")
a.WriteLine ("exit")
a.Close

Set Shell = WScript.CreateObject("Wscript.Shell")

Shell.Run "c:\winscp\winscp /log=ftplog.txt /script=c:\winscp\ftpcommands.ftp ",0,true


'________________________________________________________________________
'Set up the emailer
'________________________________________________________________________

Const cdoSendUsingPickup = 1
Const cdoSendUsingPort = 2
Const cdoAnonymous = 0
Const cdoBasic = 1
Const cdoNTLM = 2
Set objMessage = CreateObject("CDO.Message")
objMessage.From = """trading"" <xxxxxxxxxxxxxxxxxx>"
'objMessage.To = "xxxxxxxxxxxxxx"
objMessage.TextBody = "This is an automated email.  Reports are attached"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "xxxxxxxxxxxxxxxx"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasic
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "trading"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "4rfvGY&"
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = False
objMessage.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
objMessage.Configuration.Fields.Update

'Email the rpts files

objMessage.Subject = "'RPT' files"
objMessage.To = rptsemail
objMessage.Attachments.DeleteAll
objMessage.AddAttachment destinationfolder & rptsfilename
objMessage.Send

'Email the daily reports files

objMessage.Subject = "Daily Report files"
objMessage.To = dailyrptsemail
objMessage.Attachments.DeleteAll
objMessage.AddAttachment destinationfolder & dailyrptsfilename
objMessage.Send





fs.DeleteFile(rptsfilename)
fs.DeleteFile(dailyrptsfilename)




if not fs.folderexists("\\harrier\odbs\") then

    fs.createfolder("\\harrier\odbs")

end if


if not fs.folderexists("\\harrier\odbs\20" & yy) then

    fs.createfolder("\\harrier\odbs\20" & yy)

end if


if not fs.folderexists("\\harrier\odbs\20" & yy & "\" & mm) then

    fs.createfolder("\\harrier\odbs\20" & yy & "\" & mm)

end if

if not fs.folderexists("\\harrier\odbs\20" & yy & "\" & mm & "\" & dd) then

    fs.createfolder("\\harrier\odbs\20" & yy & "\" & mm & "\" & dd)

end if



Shell.Run "..\pkzip25 -extract -over=all " & odbsfilename & " \\harrier\odbs\20" & yy & "\" & mm & "\" & dd & "\", 0, true

fs.DeleteFile(odbsfilename)





'
'
'
'
' ODB IMPORT SECTION
'
'
'
'
'
'

ConString = "Provider=SQLOLEDB;" & _
        "Data Source=xxxxxxxxxxxx;" & _
        "Initial Catalog=xxxxxxx;" & _
        "Network=DBMSSOCN;" & _
        "User Id=xxxxxxxxxxxxx;" & _
        "Password=xxxxxxxxxxxxxx"
set conn = CreateObject("ADODB.Connection")
Conn.connectiontimeout=500
Conn.commandtimeout=500
conn.open ConString


sFolder = "\\harrier\odbs\20" & yy & "\" & mm & "\" & dd

arfn = sFolder & "\" & "ar" & yy & mm & dd & ".odb"
atfn = sFolder & "\" & "at" & yy & mm & dd & ".odb"
aifn = sFolder & "\" & "ai" & yy & mm & dd & ".odb"
snfn = sFolder & "\" & "sn" & yy & mm & dd & ".odb"
crfn = sFolder & "\" & "cr" & yy & mm & dd & ".odb"



'Set fso = CreateObject("Scripting.FileSystemObject")


'
'
'
' ISA FILES
'
'
'

Set folder = fs.GetFolder(sFolder)
Set files = folder.Files

For each folderIdx In files

    if mid(folderIdx.Name,1,8) = "isa_eua_" and fs.GetExtensionName(folderIdx.Name) = "odb" then

        'this is where the file's contents will be imported.





        if not fs.fileExists(Left(sFolder & "\" & folderIdx.Name, Len(sFolder & "\" & folderIdx.Name) - 1) & "_") then



        ' if the renamed version of the file exists bypass the import (can't use a check of whether the file exists, because
        ' if this script is re-run the file would be replaced by the ftp download anyway, so need to check for presence of renamed file
        ' for indication of an already done import)

            set isafile = fs.OpenTextFile(sFolder & "\" & folderIdx.Name, 1, False)


            Do While Not isafile.AtendOfStream
                Line = isafile.readline
                Line = Split(Line, "|")

                For X = 0 To UBound(Line)
                    If X = 0 Then gensource = Line(X)
                    If X = 1 Then source = Line(X)
                    If X = 2 Then custID = Line(X)
                    If X = 3 Then sourcetype = Line(X)
                    If X = 4 Then eventcode = Line(X)
                    If X = 5 Then eotdate = Line(X)
                    If X = 6 Then Race = Line(X)
                    If X = 7 Then pool = Line(X)
                    If X = 8 Then eotcurrency = Line(X)
                    If X = 9 Then netsales = Line(X)
                    If X = 10 Then commission = Line(X)
                    If X = 11 Then payout = Line(X)
                    If X = 12 Then settlement = Line(X)
                    If X = 13 Then
                        breakage = Line(X)

                        Conn.Execute("INSERT INTO AmtoteISA (gen_source, source, cust_ID, source_type, event_code, date, race, pool, currency, net_sales, Commission, Payout, Settlement, Breakage) values ('" & gensource & "','" & source & "','" & custID & "','" & sourcetype & "','" & eventcode & "','" & eotdate & "','" & Race & "','" & pool & "','" & eotcurrency & "',cast('" & netsales & "' as money),cast('" & commission & "' as money),cast('" & payout & "' as money),cast('" & settlement & "' as money),cast('" & breakage & "' as money))")


                    End If
                Next





            Loop

            'rename the file here!!! (to use the same trick the importer used for not re-importing files)


            isafile.close()

            fs.MoveFile sFolder & "\" & folderIdx.Name, Left(sFolder & "\" & folderIdx.Name, Len(sFolder & "\" & folderIdx.Name) - 1) & "_"

        end if




    end if
Next


'
'
'
' AR FILE
'
'
'

if not fs.fileExists(Left(arfn, Len(arfn) - 1) & "_") then

    set arfile = fs.OpenTextFile(arfn)
    Do While Not arfile.AtendOfStream
        Line = arfile.readline
        Line = Split(Line, "|")
        For X = 0 To UBound(Line)
            If X = 0 Then ameotdate = Line(X)
            If X = 1 Then community = Line(X)
            If X = 2 Then acctnumber = Line(X)
            If X = 3 Then branch = Line(X)
            If X = 4 Then window = Line(X)
            If X = 5 Then eottime = Line(X)
            If X = 6 Then balforward = Line(X)
            If X = 7 Then transtype = Left(Line(X), 10)
            If X = 8 Then progname = Line(X)
            If X = 9 Then amrace = Line(X)
            If X = 10 Then ampool = Line(X)
            If X = 11 Then amount = Line(X)
            If X = 12 Then Runners = rtrim(Left(Line(X), 60))
            If X = 13 Then totalbet = Line(X)
            If X = 14 Then debit = Line(X)
            If X = 15 Then credit = Line(X)
            If X = 16 Then txdate = Line(X)
            If X = 17 Then chkclear = Line(X)
            If X = 18 Then refundamt = Line(X)
            If X = 19 Then audittrail = Line(X)
            If X = 20 Then betmodifier = Line(X)
            If X = 23 Then
                betserial = Line(X)
                If (transtype <> "Callup" And transtype <> "Hangup") Then
                    Conn.Execute("INSERT INTO amtoteaccountactivity (_date, Community, AccountNumber, Branch, Window, Time, Balance_Forward, Transaction_Type, Program_Name, Race, Pool_Type, Amount, Runners, Total_Bet_Amount, Debit_Amount, Credit_Amount, Tx_Date, Check_Clear_Date, Refund_Amt, Tag_ID, Bet_Pool_Modifier,serial_number,Audit_Trail) values ('" & ameotdate & "','" & community & "','" & acctnumber & "','" & branch & "','" & window & "','" & eottime & "','" & balforward & "','" & transtype & "','" & progname & "','" & amrace & "','" & ampool & "',cast('" & amount & "' as money),'" & Runners & "','" & totalbet & "','" & debit & "','" & credit & "','" & txdate & "','" & chkclear & "','" & refundamt & "',NULL,'"& betmodifier &"','"& betserial &"','"& audittrail &"')")
                End If
            End If
        Next
    Loop

    arfile.close()

    fs.MoveFile arfn, Left(arfn, Len(arfn) - 1) & "_"

end if


'
'
'
' SN (winnings) FILE
'
'
'

if not fs.fileExists(Left(snfn, Len(snfn) - 1) & "_") then

    set snfile = fs.OpenTextFile(snfn)
    Do While Not snfile.AtendOfStream
        Line = snfile.readline
        Line = Split(Line, "|")
        AW_Meeting = Line(3)
        AW_DateSold = Line(11)
        AW_DateCashed = Line(12)
        AW_SaleSerialNumber = Line(13)
        AW_GrossPay = Line(14)
        AW_IRSWithheld = Line(15)
        AW_FEDWithheld = Line(16)
        AW_StateWithheld = Line(17)
        AW_Loc1Withheld = Line(18)
        AW_Loc2Withheld = Line(19)
        AW_Loc3Withheld = Line(20)
        AW_TotalWithheld = Line(21)
        AW_TicketValue = Line(22)
        AW_Race = Line(28)
        AW_BetType = Line(29)
        AW_BetAmount = Line(30)
        AW_AccountNumber = Line(31)

        Conn.Execute("INSERT INTO AmtoteWinnings (Meeting, DateSold, DateCashed, SaleSerialNumber, GrossPay, IRSWithheld, FEDWithheld, StateWithheld, Loc1Withheld, Loc2Withheld, Loc3Withheld, TotalWithheld, TicketValue, Race, BetType, BetAmount, AccountNumber) values('" & AW_Meeting & "','" & AW_DateSold & "','" & AW_DateCashed & "','" & AW_SaleSerialNumber & "',cast('" & AW_GrossPay & "' as money),cast('" & AW_IRSWithheld & "' as money),cast('" & AW_FEDWithheld & "' as money),cast('" & AW_StateWithheld & "' as money),cast('" & AW_Loc1Withheld & "' as money),cast('" & AW_Loc2Withheld & "' as money),cast('" & AW_Loc3Withheld & "' as money),cast('" & AW_TotalWithheld & "' as money),cast('" & AW_TicketValue & "' as money),'" & AW_Race & "','" & AW_BetType & "',cast('" & AW_BetAmount & "' as money),'" & AW_AccountNumber & "' )")

    Loop

    snfile.close()

    fs.MoveFile snfn, Left(snfn, Len(snfn) - 1) & "_"

end if

'
'
'
' CR FILE
'
'
'

if not fs.fileExists(Left(crfn, Len(crfn) - 1) & "_") then

    set crfile = fs.OpenTextFile(crfn)
    Do While Not crfile.AtendOfStream
        Line = crfile.readline
        Line = Split(Line, "|")
        For X = 0 To UBound(Line)
            If X = 0 Then ameotdate = Line(X)
            If X = 2 Then progname = Line(X)
            If X = 4 Then pooltype = Line(X)
            If X = 5 Then source = Line(X)
            If X = 11 Then
                commission = Line(X)
                If (source = "BI") Then
                    Conn.Execute("INSERT INTO Amtotecommissions_holding (_date, program_name,pool_type,commission) values ('" & ameotdate & "','" & progname & "','" & pooltype & "','" & commission & "')")
                End If
            End If
        Next
    Loop

    Conn.execute("insert into amtotecommissions (_date, program_name,pool_type,commission) select _date,program_name,pool_type,(select top 1 commission) from amtotecommissions_holding group by _date,program_name,pool_type,commission")
    Conn.execute("delete from amtotecommissions_holding")

    crfile.close()

    fs.MoveFile crfn, Left(crfn, Len(crfn) - 1) & "_"

end if


'
'
'
' AT FILE
'
'
'

if not fs.fileExists(Left(atfn, Len(atfn) - 1) & "_") then

    set atfile = fs.OpenTextFile(atfn)

    dbdate = yy & "/" & mm & "/" & dd

    Do While Not atfile.AtendOfStream
        Line = atfile.readline
        Line = Split(Line, "|")
        AT_agent = Line(0)
        AT_cmty = Line(1)
        AT_branch = Line(2)
        AT_window = Line(3)
        AT_tx_date = Line(4)
        AT_tx_time = Line(5)
        AT_tx_type = Line(6)
        AT_accountnumber = Line(7)
        AT_tx_cmty = Line(8)
        AT_amount = Line(9)
        AT_dep_type = Line(10)

        Conn.Execute("INSERT INTO AmtoteAccountTransactions (agent,cmty,branch,window,tx_date,tx_time,tx_type,accountnumber,tx_cmty,amount,dep_type,db_date) values('" & AT_agent & "','" & AT_cmty & "','" & AT_branch & "','" & AT_window & "','" & AT_tx_date & "','" & AT_tx_time & "','" & AT_tx_type & "','" & AT_accountnumber & "','" & AT_tx_cmty & "','" & AT_amount & "','" & AT_dep_type & "','" & dbdate & "' )")

    Loop

    atfile.close()

    fs.MoveFile atfn, Left(atfn, Len(atfn) - 1) & "_"

end if

'
'
'
' AI FILE
'
'
'

if not fs.fileExists(Left(aifn, Len(aifn) - 1) & "_") then

    Conn.execute("delete from ai")

    set aifile = fs.OpenTextFile(aifn)
    Do While Not aifile.AtendOfStream
        Line = aifile.readline
        Line = Replace(Line, "'", " ")
        Line = Split(Line, "|")
        ai_AccountNumber = Line(0)
        ai_cmty = Line(1)
        ai_Col003 = Line(2)
        ai_Col004 = Line(3)
        ai_FirstName = Line(4)
        ai_LastName = Line(5)
        ai_Address1 = Line(6)
        ai_Col008 = Line(7)
        ai_Col009 = Line(8)
        ai_Col010 = Line(9)
        ai_Col011 = Line(10)
        ai_Col012 = Line(11)
        ai_Col013 = Line(12)
        ai_Col014 = Line(13)
        ai_Col015 = Line(14)
        ai_Col016 = Line(15)
        ai_Col017 = Line(16)
        ai_Col018 = Line(17)
        ai_Col019 = Line(18)
        ai_Col020 = Line(19)
        ai_Col021 = Line(20)
        ai_Col022 = Line(21)
        ai_Col023 = Line(22)
        ai_Col024 = Line(23)
        ai_Col025 = Line(24)
        ai_Col026 = Line(25)
        ai_Col027 = Line(26)
        ai_Col028 = Line(27)
        ai_Col029 = Line(28)
        ai_Col030 = Line(29)
        ai_Col031 = Line(30)
        ai_Col032 = Line(31)
        ai_Col033 = Line(32)
        ai_Col034 = Line(33)
        ai_Col035 = Line(34)
        ai_Col036 = Line(35)
        ai_Col037 = Line(36)
        ai_Col038 = Line(37)
        ai_Col039 = Line(38)
        ai_Col040 = Line(39)
        ai_Col041 = Line(40)

        Conn.Execute("INSERT INTO ai (AccountNumber, cmty, Col003, Col004, FirstName, LastName, Address1, Col008, Col009, Col010, Col011, Col012, Col013, Col014, Col015, Col016, Col017, Col018, Col019, Col020, Col021, Col022, Col023, Col024, Col025, Col026, Col027, Col028, Col029, Col030, Col031, Col032, Col033, Col034, Col035, Col036, Col037, Col038, Col039, Col040, Col041) values('" & ai_AccountNumber & "','" & ai_cmty & "','" & ai_Col003 & "','" & ai_Col004 & "','" & ai_FirstName & "','" & ai_LastName & "','" & ai_Address1 & "','" & ai_Col008 & "','" & ai_Col009 & "','" & ai_Col010 & "','" & ai_Col011 & "','" & ai_Col012 & "','" & ai_Col013 & "','" & ai_Col014 & "','" & ai_Col015 & "','" & ai_Col016 & "','" & ai_Col017 & "','" & ai_Col018 & "','" & ai_Col019 & "','" & ai_Col020 & "','" & ai_Col021 & "','" & ai_Col022 & "','" & ai_Col023 & "','" & ai_Col024 & "','" & ai_Col025 & "','" & ai_Col026 & "','" & ai_Col027 & "','" & ai_Col028 & "','" & ai_Col029 & "','" & ai_Col030 & "','" & ai_Col031 & "','" & ai_Col032 & "','" & ai_Col033 & "','" & ai_Col034 & "','" & ai_Col035 & "','" & ai_Col036 & "','" & ai_Col037 & "','" & ai_Col038 & "','" & ai_Col039 & "','" & ai_Col040 & "','" & ai_Col041 & "' )")

    Loop

    aifile.close()

    fs.MoveFile aifn, Left(aifn, Len(aifn) - 1) & "_"

end if



Conn.close



'
'
'
'
'
'
' END ODB IMPORT SECTION
'
'
'
'




logfile.writeline("============== ENDING SCRIPT " & now & "======================")

logfile.close()

fs.MoveFile "sendrpts_importodbs_logfile.fileinuse", "sendrpts_importodbs_logfile.txt"

set fs = nothing
set shell = nothing