|
| <% WriteString("PageTitle_AffiliateSalesReport") %> |
| <% Response.Write(MonthName(numGivenMonth) & " " & numGivenYear) %> |
| <% WriteString("ContentText_ValuesShownExCoupons") %> |
| <% WriteString("ContentText_ID") %> |
<% WriteString("ContentText_ShareOfSales") %> |
|
<% WriteString("ContentText_AffiliateName") %> |
<%
'We're storing the values in an array for efficiency
'It means we only have to open one connection to the database,
'not one for every loop/affiliate.
strQuery = "SELECT SUM(ROUND(ON_AffiliateTotalPrice + 0.00000001,2)) AS OrderAmount, tblCactuShopCardholderDetails.CD_AffiliateID, tblCactuShopAffiliates.AF_Name FROM (tblCactuShopAffiliates INNER JOIN tblCactuShopCardholderDetails ON tblCactuShopAffiliates.AF_ID = tblCactuShopCardholderDetails.CD_AffiliateID) INNER JOIN tblCactuShopOrderNumbers ON tblCactuShopOrderNumbers.ON_CardholderID = tblCactuShopCardholderDetails.CD_ID WHERE tblCactuShopOrderNumbers.ON_Sent='y' AND tblCactuShopOrderNumbers.ON_AffiliatePercentage > 0 AND Month(ON_Date)=" & numGivenMonth & " AND Year(ON_Date)=" & numGivenYear & " GROUP BY tblCactuShopCardholderDetails.CD_AffiliateID, tblCactuShopAffiliates.AF_Name, Month(ON_Date), Year(ON_Date)"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
numRecordCount = objRecordSet.RecordCount
ReDim aryData(numRecordCount, 2)
numCounter = 1
numMaxValue = 0
numTotalHits = 0
Do Until objRecordSet.EOF
aryData(numCounter, 0) = objRecordSet(0)
aryData(numCounter, 1) = CInt(objRecordSet(1))
aryData(numCounter, 2) = objRecordSet(2)
If aryData(numCounter, 0) > numMaxValue Then numMaxValue = aryData(numCounter, 0)
numTotalHits = numTotalHits + aryData(numCounter, 0)
numCounter = numCounter + 1
objRecordSet.MoveNext
Loop
objRecordSet.Close
numCounter = 1
Do Until numCounter > numRecordCount
'-----------------------------------------------
'TOTAL HITS FOR EACH AFFILIATE
'This cycles through each affiliate, calculates
'the hits for each and formulates the graphic
'display bars as a percentage of the best hit.
'-----------------------------------------------
If numCounter / 2 = Int(numCounter / 2) Then
bgcolor = "#EEEEEE"
Else
bgcolor = "#DDDDDD"
End If
If numMaxValue = 0 Then
numDisplayWidth = 0
Else
numDisplayWidth = Int((aryData(numCounter, 0) / numMaxValue) * 100)
End If
If numDisplayWidth = 0 Then numDisplayWidth = 1
%>
| <% =aryData(numCounter, 1) %> |
  |
<% Response.Write(strDefaultCurrencySymbol & CurrencyDisplay(aryData(numCounter, 0), 1, blnDefaultCurrencyHasDecimals)) %> |
<% =aryData(numCounter, 2) %> |
<%
numCounter = numCounter + 1
Loop
%>
|
| <% WriteString("ContentText_TotalSalesForAboveMonth") %> <% Response.Write(strDefaultCurrencySymbol & CurrencyDisplay(numTotalHits, 1, blnDefaultCurrencyHasDecimals)) %> |
| <% WriteString("ContentText_LastTwelveMonths") %> |
| <% WriteString("ContentText_ValuesShownExCoupons") %> |
| |
<% WriteString("ContentText_ShareOfSales") %> |
|
<% WriteString("ContentText_Month") %> |
<%
numCounter = 1
numMaxValue = 0
TotalAmount = 0
'1st of Year ago today - actually only 11 months, as including this month, it makes 12
YearAgo = DateAdd("m", -11, Year(Date) & "/" & Month(Date) & "/1")
'Define the date which is decreased with each loop
' - used in first loop for money earnt, unnecessary
' in the second
DecDate = Year(Date) & "/" & Month(Date) & "/1"
'Resize the array...
ReDim aryData(12, 3)
'Open the recordset connection to get order values.
strQuery = "SELECT SUM(ROUND(ON_AffiliateTotalPrice + 0.00000001,2)) AS OrderAmount, YEAR(ON_Date) AS TheYear, MONTH(ON_Date) AS TheMonth FROM (tblCactuShopAffiliates INNER JOIN tblCactuShopCardholderDetails ON tblCactuShopAffiliates.AF_ID = tblCactuShopCardholderDetails.CD_AffiliateID) INNER JOIN tblCactuShopOrderNumbers ON tblCactuShopOrderNumbers.ON_CardholderID = tblCactuShopCardholderDetails.CD_ID WHERE tblCactuShopOrderNumbers.ON_Sent='y' AND tblCactuShopOrderNumbers.ON_AffiliatePercentage > 0 GROUP BY Month(ON_Date), Year(ON_Date) ORDER BY Year(ON_Date) DESC, Month(ON_Date) DESC"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
'Loop to fill the array
Do Until DecDate < YearAgo
If objRecordSet.EOF Then
'Got to end of stats - give blank
aryData(numCounter, 0) = 0
aryData(numCounter, 1) = Year(DecDate)
aryData(numCounter, 2) = Month(DecDate)
ElseIf CInt(objRecordSet(1)) = Year(DecDate) And CInt(objRecordSet(2)) = Month(DecDate) Then
'Stats not finished, and is current month
aryData(numCounter, 0) = Round(objRecordSet(0), 2)
aryData(numCounter, 1) = CInt(objRecordSet(1))
aryData(numCounter, 2) = CInt(objRecordSet(2))
objRecordSet.MoveNext
Else
'Stats not finished, not reached date given by DB
aryData(numCounter, 0) = 0
aryData(numCounter, 1) = Year(DecDate)
aryData(numCounter, 2) = Month(DecDate)
End If
If aryData(numCounter, 0) > numMaxValue Then numMaxValue = aryData(numCounter, 0)
TotalAmount = TotalAmount + aryData(numCounter, 0)
DecDate = DateAdd("m", -1, DecDate)
'First loop - haven't filled date text column yet
aryData(numCounter, 3) = MonthName(aryData(numCounter, 2)) & " " & aryData(numCounter, 1)
numCounter = numCounter + 1
Loop
objRecordSet.Close
For numCounter = 1 To 12
'-----------------------------------------------
'TOTALS FOR EACH MONTH
'This cycles through each month in the array
'and formulates the graphic display bars as a
'percentage of the best hit month.
'-----------------------------------------------
If numCounter / 2 = Int(numCounter / 2) Then
BGColour = "#EEEEEE"
Else
BGColour = "#DDDDDD"
End If
If numMaxValue = 0 Then
numDisplayWidth = 0
Else
numDisplayWidth = Int((aryData(numCounter, 0) / numMaxValue) * 100)
End If
If numDisplayWidth = 0 Then numDisplayWidth = 1
%>
| |
 |
<% Response.Write(strDefaultCurrencySymbol & CurrencyDisplay(aryData(numCounter, 0), 1, blnDefaultCurrencyHasDecimals)) %> |
<% =aryData(numCounter, 3) %> |
<%
Next
If numCounter / 2 = Int(numCounter / 2) Then
BGColour = "#EEEEEE"
Else
BGColour = "#DDDDDD"
End If
%>
| |
<% Response.Write(strDefaultCurrencySymbol & CurrencyDisplay(TotalAmount, 1, blnDefaultCurrencyHasDecimals)) %> |
|
|
<%
objDataConn.Close
Set objRecordSet = Nothing
Set objDataConn = Nothing
%>
|