|
| <% WriteString("PageTitle_VersionSales") %> |
<% WriteString("ContentText_SwitchToSales") %> | <% WriteString("ContentText_SwitchToHits") %> |
| <% Response.Write(MonthName(numGivenMonth) & " " & numGivenYear) %> |
| <% WriteString("ContentText_ID") %> |
<% WriteString("ContentText_ShareOfHits") %> |
|
<% WriteString("ContentText_ProductCategory") %> |
<%
'-----------------------------------------------
'MySQL can't sort by a COUNT field it seems.
'Because we need to get the max-count value to
'draw the graphs correctly, we have to put all
'the values into an array, and then use that for
'drawing out the bars instead.
'-----------------------------------------------
'Grab comma-list of all PT_Name's, used in SELECT and GROUP BYs of the queries
numCounter = 0
For numCounter = 1 to numTotalLanguages
if aryLangLiveBack(numCounter) = "y" then
strPTNames = strPTNames & "tblCactuShopProdtype.PT_Name" & CStr(numCounter) & ", "
end if
next
strQuery = "SELECT SUM(tblCactuShopVersionSalesStats.VSS_Quantity) AS ProductHits, " & strPTNames & " tblCactuShopProdtype.PT_ID FROM (((tblCactuShopVersionSalesStats INNER JOIN tblCactuShopVersions ON tblCactuShopVersionSalesStats.VSS_VersionNo = tblCactuShopVersions.V_ID) INNER JOIN tblCactuShopProducts ON tblCactuShopVersions.V_Product = tblCactuShopProducts.P_ID) INNER JOIN tblCactuShopProductProdTypeLink ON tblCactuShopProducts.P_ID = tblCactuShopProductProdTypeLink.PPT_ProductID) INNER JOIN tblCactuShopProdtype ON tblCactuShopProductProdTypeLink.PPT_ProdTypeID = tblCactuShopProdtype.PT_ID WHERE MONTH(tblCactuShopVersionSalesStats.VSS_Date) = " & numGivenMonth & " AND YEAR(tblCactuShopVersionSalesStats.VSS_Date) = " & numGivenYear & " GROUP BY " & strPTNames & " PT_ID ORDER BY PT_Name" & CStr(numLanguageID)
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
numRecordCount = objRecordSet.RecordCount
ReDim aryHitCount(numRecordCount, 2)
numHits = 1
numMaxValue = 0
numTotalHits = 0
Do Until objRecordSet.EOF
aryHitCount(numHits, 0) = CInt(objRecordSet("ProductHits"))
aryHitCount(numHits, 1) = CInt(objRecordSet("PT_ID"))
'get a non-blank name of the category - start with default
aryHitCount(numHits, 2) = objRecordSet("PT_Name" & CStr(numLanguageID))
If aryHitCount(numHits, 2) & "" = "" then
'if that's blank, loop around all languages till we find a non-blank
numCounter = 0
For numCounter = 1 to numTotalLanguages
if objRecordSet("PT_Name" & cstr(numCounter)) & "" <> "" then
aryHitCount(numHits, 2) = objRecordSet("PT_Name" & CStr(numCounter))
exit for
end if
Next
end if
If aryHitCount(numHits, 0) > numMaxValue Then numMaxValue = aryHitCount(numHits, 0)
numTotalHits = numTotalHits + aryHitCount(numHits, 0)
numHits = numHits + 1
objRecordSet.MoveNext
Loop
objRecordSet.Close
numHits = 1
Do Until numHits > numRecordCount
'-----------------------------------------------
'TOTAL HITS FOR EACH PRODUCT TYPE
'This cycles through each prodtype, calculates
'the hits for each and formulates the graphic
'display bars as a percentage of the best hit
'product.
'-----------------------------------------------
If numHits / 2 = Int(numHits / 2) Then
strBgColour="#EEEEEE"
Else
strBgColour="#DDDDDD"
End If
If numMaxValue = 0 Then
numDisplayWidth = 0
Else
numDisplayWidth = Int((aryHitCount(numHits, 0) / numMaxValue) * 100)
End If
If numDisplayWidth = 0 Then numDisplayWidth = 1
%>
| <% response.write(aryHitCount(numHits, 1)) %> |
  |
<% =aryHitCount(numHits, 0) %> |
<% =aryHitCount(numHits, 2) %> |
<%
numHits = numHits + 1
Loop
%>
| |
<% WriteString("ContentText_Total") %>: |
<% WriteString("ContentText_numTotalHitsForAboveMonth") %><% =numTotalHits %> |
|
|
| <% WriteString("ContentText_LastTwelveMonths") %> |
| |
<% WriteString("ContentText_ShareOfHits") %> |
|
<% WriteString("ContentText_Month") %> |
<%
'-----------------------------------------------
'Again, because MySQL can't order counts,
'we're using an array again.
'-----------------------------------------------
'1st of Year ago today
YearAgo = DateAdd("m", -11, Year(Date) & "/" & Month(Date) & "/1")
'Don't need specific for Access/SQL-MySQL - simple JOIN
strQuery = "SELECT SUM(tblCactuShopVersionSalesStats.VSS_Quantity) AS ProductHits, YEAR(tblCactuShopVersionSalesStats.VSS_Date) AS TheYear, MONTH(tblCactuShopVersionSalesStats.VSS_Date) AS TheMonth FROM (tblCactuShopVersionSalesStats INNER JOIN tblCactuShopVersions ON tblCactuShopVersionSalesStats.VSS_VersionNo = tblCactuShopVersions.V_ID) INNER JOIN tblCactuShopProductProdTypeLink ON tblCactuShopVersions.V_Product = tblCactuShopProductProdTypeLink.PPT_ProductID WHERE tblCactuShopVersionSalesStats.VSS_Date >= " & strDateDelimiter & ReverseFormatYear(YearAgo) & strDateDelimiter & " GROUP BY YEAR(tblCactuShopVersionSalesStats.VSS_Date), MONTH(tblCactuShopVersionSalesStats.VSS_Date) ORDER BY YEAR(tblCactuShopVersionSalesStats.VSS_Date) DESC, MONTH(tblCactuShopVersionSalesStats.VSS_Date) DESC"
Call ExecuteSQL(strQuery, numCursorType, objRecordSet)
numRecordCount = objRecordSet.RecordCount
ReDim aryHitCount(12, 2)
'Define the date which is decreased with each loop
DecDate = Year(Date) & "/" & Month(Date) & "/1"
numHits = 1
numMaxValue = 0
numTotalHits = 0
Do Until DecDate < YearAgo
If objRecordSet.EOF Then
'Got to end of stats - give blank
aryHitCount(numHits, 0) = 0
aryHitCount(numHits, 1) = Year(DecDate)
aryHitCount(numHits, 2) = Month(DecDate)
ElseIf CInt(objRecordSet(1)) = Year(DecDate) And CInt(objRecordSet(2)) = Month(DecDate) Then
'Stats not finished, and is current month
aryHitCount(numHits, 0) = CInt(objRecordSet(0))
aryHitCount(numHits, 1) = CInt(objRecordSet(1))
aryHitCount(numHits, 2) = CInt(objRecordSet(2))
objRecordSet.MoveNext
Else
'Stats not finished, not reached date given by DB
aryHitCount(numHits, 0) = 0
aryHitCount(numHits, 1) = Year(DecDate)
aryHitCount(numHits, 2) = Month(DecDate)
End If
If aryHitCount(numHits, 0) > numMaxValue Then numMaxValue = aryHitCount(numHits, 0)
numTotalHits = numTotalHits + aryHitCount(numHits, 0)
DecDate = DateAdd("m", -1, DecDate)
numHits = numHits + 1
Loop
objRecordSet.Close
For numHits = 1 To 12
'-----------------------------------------------
'TOTAL HITS 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 numHits / 2 = Int(numHits / 2) Then
strBgColour="#EEEEEE"
Else
strBgColour="#DDDDDD"
End If
If numMaxValue = 0 Then
numDisplayWidth = 0
Else
numDisplayWidth = Int((aryHitCount(numHits, 0) / numMaxValue) * 100)
End If
If numDisplayWidth = 0 Then numDisplayWidth = 1
%>
| |
 |
<% =aryHitCount(numHits, 0) %> |
<%
If aryHitCount(numHits, 2) = Int(numGivenMonth) And aryHitCount(numHits, 1) = Int(numGivenYear) Then
Response.Write("" & MonthName(aryHitCount(numHits, 2)) & " " & aryHitCount(numHits, 1) & "")
Else
%><% =MonthName(aryHitCount(numHits, 2)) & " " & aryHitCount(numHits, 1) %><%
End If
%> |
<%
Next
%>
| |
<% WriteString("ContentText_Total") %>: |
<% WriteString("ContentText_numTotalHitsForAboveMonth") %><% =numTotalHits %> |
|
|
|