#12 Daily Credit report

[Kenneth Maples]

Requesting a simple report that can be ran based on date (or range of dates) so that we can balance the incoming funds (credit) with the deposited funds.

Here is my sloppy vbs script that I am using now to give you an idea...;)

Dim oConnection, sConnectString, rs, aSIS(), aCredit(), sStudentID, sStudentFirstName, sStudentLastName, sStudentCreditID, sCreditAmount, sFound

'To open database
Set oConnection = CreateObject("adodb.connection")
sConnectString = "Driver={MySQL ODBC 5.1 driver}; server=xxx; port=xxx; database=pscafepos; uid=xxx; pwd=xxx; Option=131242"
oConnection.Open sConnectString

sStart = "1/4/2009"
sEnd = "1/5/2009"

Set rs = oConnection.Execute("Select * From studentcredit_log")
y = 0
While Not rs.EOF And Not rs.BOF
sStudentCreditID = rs.Fields("scl_studentid")
sCreditAmount = rs.Fields("scl_action")
sCreditDateTime = rs.Fields("scl_datetime")

ReDim Preserve aCredit(2, y)
aCredit(0, y) = sStudentCreditID
aCredit(1, y) = sCreditAmount
aCredit(2, y) = sCreditDateTime
y = y + 1

rs.MoveNext
Wend

Call rs.Close
Set rs = Nothing

WScript.Echo "Student Credit ID" & "," & "Credit Amount" & "," & "Credit Created"

n = 0
Do Until n = (UBound(aCredit, 2) + 1)
sCreditDateTime = aCredit(2, n)
sCreditDateTime = Left(sCreditDateTime, InStr(sCreditDateTime, " ")-1)
'WScript.Echo sCreditDateTime
'WScript.Echo sEnd

If sStart <> "" And sEnd <> "" Then
'WScript.Echo "starting Date"
If sCreditDateTime >= sStart And sCreditDateTime <= sEnd Then
'WScript.Echo " Match Date"
WScript.Echo aCredit(0, n) & "," & aCredit(1, n) & "," & aCredit(2, n)
sCreditTotal = aCredit(1, n)
End If
Else
WScript.Echo aCredit(0, n) & "," & aCredit(1, n) & "," & aCredit(2, n)
sCreditTotal = aCredit(1, n)
End If

sTotal = sTotal + sCreditTotal
sCreditTotal = 0
n = n + 1
Loop

WScript.Echo "Total Credit is: $" & sTotal