Attribute VB_Name = "ReportFunctions"
Option Explicit

'''routines to create sales reports
Public Sub GenerateVatReport()
Dim rstIn As ADODB.Recordset
Dim rstOut As ADODB.Recordset
Dim strSQL As String
Dim dblTotal As Double
Dim dblGrandTotal As Double

   Main.cn.Execute "DELETE * FROM tmpVatReport"
   Set rstOut = New ADODB.Recordset
   Set rstIn = New ADODB.Recordset
   rstOut.Open "tmpVatReport", Main.cn, adOpenDynamic, adLockOptimistic
'vatable-----
   dblTotal = 0
   dblGrandTotal = 0
   'owner charges
   rstOut.AddNew
   rstOut.Fields(0) = "Vatable Owner Charges"
   rstOut.Fields(2) = 1
   rstOut.Fields(3) = 1
   strSQL = "SELECT Sum(tGLOwnerCharge.dblAmount) AS SumOfdblAmount " & _
            "From tGLOwnerCharge " & _
            "WHERE (((tGLOwnerCharge.bVATCharge)=True));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0))
      dblTotal = ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal + ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'vat
   rstOut.AddNew
   rstOut.Fields(0) = "VAT on Owner Charges"
   rstOut.Fields(1) = dblTotal * SF_VATRate
   rstOut.Fields(2) = 1
   rstOut.Fields(3) = 2
   rstOut.Update
   'total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Owner Charges"
   rstOut.Fields(1) = dblTotal + (dblTotal * SF_VATRate)
   rstOut.Fields(2) = 1
   rstOut.Fields(3) = 9
   rstOut.Update

   'Horse Charges
   dblTotal = 0
   rstOut.AddNew
   rstOut.Fields(0) = "Vatable Horse Charges"
   rstOut.Fields(2) = 2
   rstOut.Fields(3) = 1
   strSQL = "SELECT Sum(([dblAmount]*[dblPercentage]/100)) AS SumOfdblAmount " & _
            "FROM tGLHorseCharge INNER JOIN tHorseOwner ON tGLHorseCharge.refHorseID = tHorseOwner.refHorseID " & _
            "GROUP BY tGLHorseCharge.bVATCharge, tHorseOwner.bCharge " & _
            "HAVING (((tGLHorseCharge.bVATCharge)=True) AND ((tHorseOwner.bCharge)=True));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0))
      dblTotal = ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal + ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'vat
   rstOut.AddNew
   rstOut.Fields(0) = "VAT on Horse Charges"
   rstOut.Fields(1) = dblTotal * SF_VATRate
   rstOut.Fields(2) = 2
   rstOut.Fields(3) = 2
   rstOut.Update
   'total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Horse Charges"
   rstOut.Fields(1) = dblTotal + (dblTotal * SF_VATRate)
   rstOut.Fields(2) = 2
   rstOut.Fields(3) = 9
   rstOut.Update
   
   'owner credits
   dblTotal = 0
   rstOut.AddNew
   rstOut.Fields(0) = "Vatable Owner Credits"
   rstOut.Fields(2) = 3
   rstOut.Fields(3) = 1
   strSQL = "SELECT Sum(tGLOwnerCredit.dblAmount) AS SumOfdblAmount " & _
            "From tGLOwnerCredit " & _
            "WHERE (((tGLOwnerCredit.bVATCharge)=True));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0)) * -1
      dblTotal = dblTotal - ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal - ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'vat
   rstOut.AddNew
   rstOut.Fields(0) = "VAT on Owner Credits"
   rstOut.Fields(1) = dblTotal * SF_VATRate
   rstOut.Fields(2) = 3
   rstOut.Fields(3) = 2
   rstOut.Update
   'total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Owner Credits"
   rstOut.Fields(1) = dblTotal + (dblTotal * SF_VATRate)
   rstOut.Fields(2) = 3
   rstOut.Fields(3) = 9
   rstOut.Update
   
   
   'horse credits
   dblTotal = 0
   rstOut.AddNew
   rstOut.Fields(0) = "Vatable Horse Credits"
   rstOut.Fields(2) = 4
   rstOut.Fields(3) = 1
'   strSQL = "SELECT Sum(tGLHorseCredit.dblAmount) AS SumOfdblAmount " & _
            "From tGLHorseCredit " & _
            "WHERE (((tGLHorseCredit.bVATCharge)=True));"
   strSQL = "SELECT Sum(([dblAmount]*[dblPercentage]/100)) AS SumOfdblAmount " & _
            "FROM tGLHorseCredit INNER JOIN tHorseOwner ON tGLHorseCredit.refHorseID = tHorseOwner.refHorseID " & _
            "GROUP BY tGLHorseCredit.bVATCharge, tHorseOwner.bCharge " & _
            "HAVING (((tGLHorseCredit.bVATCharge)=True) AND ((tHorseOwner.bCharge)=True));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0)) * -1
      dblTotal = dblTotal - ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal - ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'vat
   rstOut.AddNew
   rstOut.Fields(0) = "VAT on Horse Credits"
   rstOut.Fields(1) = dblTotal * SF_VATRate
   rstOut.Fields(2) = 4
   rstOut.Fields(3) = 2
   rstOut.Update
   'total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Horse Credits"
   rstOut.Fields(1) = dblTotal + (dblTotal * SF_VATRate)
   rstOut.Fields(2) = 4
   rstOut.Fields(3) = 9
   rstOut.Update
   
   'Overall Total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Charges and Credits"
   rstOut.Fields(1) = dblGrandTotal
   rstOut.Fields(2) = 5
   rstOut.Fields(3) = 1
   rstOut.Update
   'Overall Vat Total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Output VAT"
   rstOut.Fields(1) = dblGrandTotal * SF_VATRate
   rstOut.Fields(2) = 5
   rstOut.Fields(3) = 2
   dblTotal = 0
   rstOut.Update
   'total including vat
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Sales, Incl VAT"
   rstOut.Fields(1) = dblGrandTotal + (dblGrandTotal * SF_VATRate)
   rstOut.Fields(2) = 5
   rstOut.Fields(3) = 9
   dblTotal = 0
   rstOut.Update
   
'nonvatable---------
   dblGrandTotal = 0
   'owner charges
   dblTotal = 0
   rstOut.AddNew
   rstOut.Fields(0) = "Owner Charges - Non Vatable (Excl Interest)"
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 1
   strSQL = "SELECT Sum(tGLOwnerCharge.dblAmount) AS SumOfdblAmount " & _
            "From tGLOwnerCharge " & _
            "WHERE (((tGLOwnerCharge.bVATCharge)=false) AND ((tGLOwnerCharge.refGLAccountNameID)<>1));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0))
      dblTotal = dblTotal + ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal + ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'horse charges
   rstOut.AddNew
   rstOut.Fields(0) = "Horse Charges - Non Vatable"
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 2
'   strSQL = "SELECT Sum(tGLHorseCharge.dblAmount) AS SumOfdblAmount " & _
            "From tGLHorseCharge " & _
            "WHERE (((tGLHorseCharge.bVATCharge)=false));"
   strSQL = "SELECT Sum(([dblAmount]*[dblPercentage]/100)) AS SumOfdblAmount " & _
            "FROM tGLHorseCharge INNER JOIN tHorseOwner ON tGLHorseCharge.refHorseID = tHorseOwner.refHorseID " & _
            "GROUP BY tGLHorseCharge.bVATCharge, tHorseOwner.bCharge " & _
            "HAVING (((tGLHorseCharge.bVATCharge)=False) AND ((tHorseOwner.bCharge)=True));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0))
      dblTotal = dblTotal + ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal + ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'owner credits
   rstOut.AddNew
   rstOut.Fields(0) = "Owner Credits - Non Vatable (Excl Receipts)"
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 3
   strSQL = "SELECT Sum(tGLOwnerCredit.dblAmount) AS SumOfdblAmount " & _
            "From tGLOwnerCredit " & _
            "WHERE (((tGLOwnerCredit.bVATCharge)=false) AND ((tGLOwnerCredit.refGLAccountNameID)<>3));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0)) * -1
      dblTotal = dblTotal - ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal - ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'horse credits
   rstOut.AddNew
   rstOut.Fields(0) = "Horse Credits - Non Vatable"
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 4
'   strSQL = "SELECT Sum(tGLHorseCredit.dblAmount) AS SumOfdblAmount " & _
            "From tGLHorseCredit " & _
            "WHERE (((tGLHorseCredit.bVATCharge)=false));"
   strSQL = "SELECT Sum(([dblAmount]*[dblPercentage]/100)) AS SumOfdblAmount " & _
            "FROM tGLHorseCredit INNER JOIN tHorseOwner ON tGLHorseCredit.refHorseID = tHorseOwner.refHorseID " & _
            "GROUP BY tGLHorseCredit.bVATCharge, tHorseOwner.bCharge " & _
            "HAVING (((tGLHorseCredit.bVATCharge)=False) AND ((tHorseOwner.bCharge)=True));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0)) * -1
      dblTotal = dblTotal - ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal - ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
      
   'Overall Total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Non Vatable Sales"
   rstOut.Fields(1) = dblGrandTotal
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 9
   rstOut.Update
   rstOut.Close
End Sub

Public Sub GenerateVatHistoryReport(fromDate As String, toDate As String)
Dim rstIn As ADODB.Recordset
Dim rstOut As ADODB.Recordset
Dim strSQL As String
Dim dblTotal As Double
Dim dblGrandTotal As Double
   
   Main.cn.Execute "DELETE * FROM tmpVatReport"
   Set rstOut = New ADODB.Recordset
   Set rstIn = New ADODB.Recordset
   rstOut.Open "tmpVatReport", Main.cn, adOpenDynamic, adLockOptimistic
'vatable-----
   dblTotal = 0
   dblGrandTotal = 0
   'owner charges
   rstOut.AddNew
   rstOut.Fields(0) = "Vatable Owner Charges"
   rstOut.Fields(2) = 1
   rstOut.Fields(3) = 1
   strSQL = "SELECT Sum(tMonthOwnerCharge.dblAmount) AS SumOfdblAmount " & _
            "From tMonthOwnerCharge " & _
            "WHERE ((tMonthOwnerCharge.bVATCharge)=True) " & _
            "AND ((tMonthOwnerCharge.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "');"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0))
      dblTotal = ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal + ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'vat
   rstOut.AddNew
   rstOut.Fields(0) = "VAT on Owner Charges"
   rstOut.Fields(1) = dblTotal * SF_VATRate
   rstOut.Fields(2) = 1
   rstOut.Fields(3) = 2
   rstOut.Update
   'total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Owner Charges"
   rstOut.Fields(1) = dblTotal + (dblTotal * SF_VATRate)
   rstOut.Fields(2) = 1
   rstOut.Fields(3) = 9
   rstOut.Update

   'Horse Charges
   dblTotal = 0
   rstOut.AddNew
   rstOut.Fields(0) = "Vatable Horse Charges"
   rstOut.Fields(2) = 2
   rstOut.Fields(3) = 1
'   strSQL = "SELECT Sum(tMonthHorseCharge.dblAmount) AS SumOfdblAmount " & _
            "From tMonthHorseCharge " & _
            "WHERE (((tMonthHorseCharge.bVATCharge)=True));"
   strSQL = "SELECT Sum(([dblAmount]*[dblPercentage]/100)) AS SumOfdblAmount " & _
            "FROM tMonthHorseCharge INNER JOIN tHorseOwner ON tMonthHorseCharge.refHorseID = tHorseOwner.refHorseID " & _
            "WHERE ((tMonthHorseCharge.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "') " & _
            "GROUP BY tMonthHorseCharge.bVATCharge, tHorseOwner.bCharge " & _
            "HAVING ((tMonthHorseCharge.bVATCharge)=True) AND ((tHorseOwner.bCharge)=True)"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0))
      dblTotal = ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal + ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'vat
   rstOut.AddNew
   rstOut.Fields(0) = "VAT on Horse Charges"
   rstOut.Fields(1) = dblTotal * SF_VATRate
   rstOut.Fields(2) = 2
   rstOut.Fields(3) = 2
   rstOut.Update
   'total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Horse Charges"
   rstOut.Fields(1) = dblTotal + (dblTotal * SF_VATRate)
   rstOut.Fields(2) = 2
   rstOut.Fields(3) = 9
   rstOut.Update
   
   'owner credits
   dblTotal = 0
   rstOut.AddNew
   rstOut.Fields(0) = "Vatable Owner Credits"
   rstOut.Fields(2) = 3
   rstOut.Fields(3) = 1
   strSQL = "SELECT Sum(tMonthOwnerCredit.dblAmount) AS SumOfdblAmount " & _
            "From tMonthOwnerCredit " & _
            "WHERE ((tMonthOwnerCredit.bVATCharge)=True) " & _
            "AND ((tMonthOwnerCredit.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "');"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0)) * -1
      dblTotal = dblTotal - ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal - ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'vat
   rstOut.AddNew
   rstOut.Fields(0) = "VAT on Owner Credits"
   rstOut.Fields(1) = dblTotal * SF_VATRate
   rstOut.Fields(2) = 3
   rstOut.Fields(3) = 2
   rstOut.Update
   'total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Owner Credits"
   rstOut.Fields(1) = dblTotal + (dblTotal * SF_VATRate)
   rstOut.Fields(2) = 3
   rstOut.Fields(3) = 9
   rstOut.Update
   
   
   'horse credits
   dblTotal = 0
   rstOut.AddNew
   rstOut.Fields(0) = "Vatable Horse Credits"
   rstOut.Fields(2) = 4
   rstOut.Fields(3) = 1
'   strSQL = "SELECT Sum(tMonthHorseCredit.dblAmount) AS SumOfdblAmount " & _
            "From tMonthHorseCredit " & _
            "WHERE (((tMonthHorseCredit.bVATCharge)=True));"
   strSQL = "SELECT Sum(([dblAmount]*[dblPercentage]/100)) AS SumOfdblAmount " & _
            "FROM tMonthHorseCredit INNER JOIN tHorseOwner ON tMonthHorseCredit.refHorseID = tHorseOwner.refHorseID " & _
            "WHERE ((tMonthHorseCredit.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "') " & _
            "GROUP BY tMonthHorseCredit.bVATCharge, tHorseOwner.bCharge " & _
            "HAVING (((tMonthHorseCredit.bVATCharge)=True) AND ((tHorseOwner.bCharge)=True));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0)) * -1
      dblTotal = dblTotal - ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal - ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'vat
   rstOut.AddNew
   rstOut.Fields(0) = "VAT on Horse Credits"
   rstOut.Fields(1) = dblTotal * SF_VATRate
   rstOut.Fields(2) = 4
   rstOut.Fields(3) = 2
   rstOut.Update
   'total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Horse Credits"
   rstOut.Fields(1) = dblTotal + (dblTotal * SF_VATRate)
   rstOut.Fields(2) = 4
   rstOut.Fields(3) = 9
   rstOut.Update
   
   'Overall Total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Charges and Credits"
   rstOut.Fields(1) = dblGrandTotal
   rstOut.Fields(2) = 5
   rstOut.Fields(3) = 1
   rstOut.Update
   'Overall Vat Total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Output VAT"
   rstOut.Fields(1) = dblGrandTotal * SF_VATRate
   rstOut.Fields(2) = 5
   rstOut.Fields(3) = 2
   dblTotal = 0
   rstOut.Update
   'total including vat
   rstOut.AddNew
   rstOut.Fields(0) = "Total Vatable Sales, Incl VAT"
   rstOut.Fields(1) = dblGrandTotal + (dblGrandTotal * SF_VATRate)
   rstOut.Fields(2) = 5
   rstOut.Fields(3) = 9
   dblTotal = 0
   rstOut.Update
   
'nonvatable---------
   dblGrandTotal = 0
   'owner charges
   dblTotal = 0
   rstOut.AddNew
   rstOut.Fields(0) = "Owner Charges - Non Vatable (Excl Interest)"
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 1
   strSQL = "SELECT Sum(tMonthOwnerCharge.dblAmount) AS SumOfdblAmount " & _
            "From tMonthOwnerCharge " & _
            "WHERE (((tMonthOwnerCharge.bVATCharge)=false) AND ((tMonthOwnerCharge.refGLAccountNameID)<>1)) " & _
            "AND ((tMonthOwnerCharge.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "');"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0))
      dblTotal = dblTotal + ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal + ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'horse charges
   rstOut.AddNew
   rstOut.Fields(0) = "Horse Charges - Non Vatable"
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 2
'   strSQL = "SELECT Sum(tMonthHorseCharge.dblAmount) AS SumOfdblAmount " & _
            "From tMonthHorseCharge " & _
            "WHERE (((tMonthHorseCharge.bVATCharge)=false)) " & _
            "AND ((tMonthHorseCharge.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "');"
   strSQL = "SELECT Sum(([dblAmount]*[dblPercentage]/100)) AS SumOfdblAmount " & _
            "FROM tMonthHorseCharge INNER JOIN tHorseOwner ON tMonthHorseCharge.refHorseID = tHorseOwner.refHorseID " & _
            "WHERE ((tMonthHorseCharge.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "') " & _
            "GROUP BY tMonthHorseCharge.bVATCharge, tHorseOwner.bCharge " & _
            "HAVING ((tMonthHorseCharge.bVATCharge)=False) AND ((tHorseOwner.bCharge)=True)"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0))
      dblTotal = dblTotal + ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal + ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'owner credits
   rstOut.AddNew
   rstOut.Fields(0) = "Owner Credits - Non Vatable (Excl Receipts)"
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 3
   strSQL = "SELECT Sum(tMonthOwnerCredit.dblAmount) AS SumOfdblAmount " & _
            "From tMonthOwnerCredit " & _
            "WHERE (((tMonthOwnerCredit.bVATCharge)=false) AND ((tMonthOwnerCredit.refGLAccountNameID)<>3)) " & _
            "AND ((tMonthOwnerCredit.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "');"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0)) * -1
      dblTotal = dblTotal - ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal - ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
   'horse credits
   rstOut.AddNew
   rstOut.Fields(0) = "Horse Credits - Non Vatable"
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 4
'   strSQL = "SELECT Sum(tMonthHorseCredit.dblAmount) AS SumOfdblAmount " & _
            "From tMonthHorseCredit " & _
            "WHERE (((tMonthHorseCredit.bVATCharge)=false)) " & _
            "AND ((tMonthHorseCredit.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "');"
   strSQL = "SELECT Sum(([dblAmount]*[dblPercentage]/100)) AS SumOfdblAmount " & _
            "FROM tMonthHorseCredit INNER JOIN tHorseOwner ON tMonthHorseCredit.refHorseID = tHorseOwner.refHorseID " & _
            "WHERE ((tMonthHorseCredit.dteDate) BETWEEN '" & fromDate & "' AND '" & toDate & "') " & _
            "GROUP BY tMonthHorseCredit.bVATCharge, tHorseOwner.bCharge " & _
            "HAVING (((tMonthHorseCredit.bVATCharge)=False) AND ((tHorseOwner.bCharge)=True));"
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   If Not rstIn.EOF Then
      rstOut.Fields(1) = ConvertNull(rstIn.Fields(0)) * -1
      dblTotal = dblTotal - ConvertNull(rstIn.Fields(0))
      dblGrandTotal = dblGrandTotal - ConvertNull(rstIn.Fields(0))
   Else
      rstOut.Fields(1) = 0
   End If
   rstIn.Close
   rstOut.Update
      
   'Overall Total
   rstOut.AddNew
   rstOut.Fields(0) = "Total Non Vatable Sales"
   rstOut.Fields(1) = dblGrandTotal
   rstOut.Fields(2) = 6
   rstOut.Fields(3) = 9
   rstOut.Update
   rstOut.Close
End Sub

Public Sub GenerateOestrusReport(BreedingID As Long, Season As String)
   Dim rstIn As ADODB.Recordset
   Dim rstOut As ADODB.Recordset
   Dim strSQL As String
   Dim intDay As Integer
   Dim strHorseName As String

   Main.cn.Execute "DELETE * FROM tmpOestrusReport"
   
   strSQL = "SELECT tHorse.strName, tBreedingActivityType.strAbbreviation, tBreedingActivity.dteDate FROM ((tBreedingActivity INNER JOIN tBreeding ON tBreedingActivity.refBreedingID = tBreeding.BreedingIR) INNER JOIN tBreedingActivityType ON tBreedingActivity.refBreedingActivityTypeID = tBreedingActivityType.BreedingActivityTypeIR) INNER JOIN tHorse ON tBreeding.refMareID = tHorse.HorseIR Where (((tBreeding.BreedingIR) = " & BreedingID & ")) " _
         & "GROUP BY tHorse.strName, tBreedingActivityType.strAbbreviation, tBreedingActivity.dteDate " _
         & "HAVING ((tBreedingActivity.dteDate) Between '" & Season & "/08/00' And '" & Format(Val(Season) + 1, "0000") & "/01/32') " _
         & "ORDER BY tBreedingActivity.dteDate; "

   Set rstIn = New ADODB.Recordset
   rstIn.Open strSQL, Main.cn, adOpenForwardOnly
   Set rstOut = New ADODB.Recordset
   rstOut.Open "tmpOestrusReport", Main.cn, adOpenDynamic, adLockOptimistic
   
   While Not rstIn.EOF
      strHorseName = rstIn.Fields(0)
   '*** August
      rstOut.AddNew
      rstOut.Fields(0) = strHorseName
      rstOut.Fields(1) = "August"
      rstOut.Fields(33) = 1
      rstOut.Fields(34) = Season
      intDay = 1
      Do While (Mid(rstIn.Fields(2), 6, 2) = "08")
         If Mid(rstIn.Fields(2), 9, 2) = (Format(intDay, "00")) Then
            rstOut.Fields(intDay + 1) = rstIn.Fields(1)
            rstIn.MoveNext
            If rstIn.EOF = True Then
               Exit Do
            End If
         End If
         intDay = intDay + 1
         If intDay > 31 Then
            Exit Do
         End If
      Loop
      rstOut.Update
   
   '*** September
      rstOut.AddNew
      rstOut.Fields(0) = strHorseName
      rstOut.Fields(1) = "September"
      rstOut.Fields(33) = 2
      rstOut.Fields(34) = Season
      intDay = 1
      If Not rstIn.EOF Then
         Do While (Mid(rstIn.Fields(2), 6, 2) = "09")
            If Mid(rstIn.Fields(2), 9, 2) = (Format(intDay, "00")) Then
               rstOut.Fields(intDay + 1) = rstIn.Fields(1)
               rstIn.MoveNext
               If rstIn.EOF = True Then
                  Exit Do
               End If
            End If
            intDay = intDay + 1
            If intDay > 31 Then
               Exit Do
            End If
         Loop
      End If
      rstOut.Update
      
   '*** October
      rstOut.AddNew
      rstOut.Fields(0) = strHorseName
      rstOut.Fields(1) = "October"
      rstOut.Fields(33) = 3
      rstOut.Fields(34) = Season
      intDay = 1
      If Not rstIn.EOF Then
         Do While (Mid(rstIn.Fields(2), 6, 2) = "10")
            If Mid(rstIn.Fields(2), 9, 2) = (Format(intDay, "00")) Then
               rstOut.Fields(intDay + 1) = rstIn.Fields(1)
               rstIn.MoveNext
               If rstIn.EOF = True Then
                  Exit Do
               End If
            End If
            intDay = intDay + 1
            If intDay > 31 Then
               Exit Do
            End If
         Loop
      End If
      rstOut.Update
   
   '*** November
      rstOut.AddNew
      rstOut.Fields(0) = strHorseName
      rstOut.Fields(1) = "November"
      rstOut.Fields(33) = 4
      rstOut.Fields(34) = Season
      intDay = 1
      If Not rstIn.EOF Then
         Do While (Mid(rstIn.Fields(2), 6, 2) = "11")
            If Mid(rstIn.Fields(2), 9, 2) = (Format(intDay, "00")) Then
               rstOut.Fields(intDay + 1) = rstIn.Fields(1)
               rstIn.MoveNext
               If rstIn.EOF = True Then
                  Exit Do
               End If
            End If
            intDay = intDay + 1
            If intDay > 31 Then
               Exit Do
            End If
         Loop
      End If
      rstOut.Update
      
      '*** December
      rstOut.AddNew
      rstOut.Fields(0) = strHorseName
      rstOut.Fields(1) = "December"
      rstOut.Fields(33) = 5
      rstOut.Fields(34) = Season
      intDay = 1
      If Not rstIn.EOF Then
         Do While (Mid(rstIn.Fields(2), 6, 2) = "12")
            If Mid(rstIn.Fields(2), 9, 2) = (Format(intDay, "00")) Then
               rstOut.Fields(intDay + 1) = rstIn.Fields(1)
               rstIn.MoveNext
               If rstIn.EOF = True Then
                  Exit Do
               End If
            End If
            intDay = intDay + 1
            If intDay > 31 Then
               Exit Do
            End If
         Loop
      End If
      rstOut.Update
      
      '*** January
      rstOut.AddNew
      rstOut.Fields(0) = strHorseName
      rstOut.Fields(1) = "January"
      rstOut.Fields(33) = 6
      rstOut.Fields(34) = Season
      intDay = 1
      If Not rstIn.EOF Then
         Do While (Mid(rstIn.Fields(2), 6, 2) = "01")
            If Mid(rstIn.Fields(2), 9, 2) = (Format(intDay, "00")) Then
               rstOut.Fields(intDay + 1) = rstIn.Fields(1)
               rstIn.MoveNext
               If rstIn.EOF = True Then
                  Exit Do
               End If
            End If
            intDay = intDay + 1
            If intDay > 31 Then
               Exit Do
            End If
         Loop
      End If
      rstOut.Update
   Wend
End Sub