<%@ LANGUAGE="VBSCRIPT" %> <% PageStrings = "333, 334, 366, 368, 369, 372, 373" strThisBackPage = "orders" %> <% '=============================================== ' CactuShop ASP Shopping Cart ' ©1999-2004 Cactusoft Ltd. www.cactusoft.com '=============================================== ' All rights reserved. ' Use of this code is covered by the terms and ' conditions in the license agreement. No ' unauthorized duplication or distribution is ' permitted. Cactusoft's copyright notices must ' remain in the ASP sections of the code. '=============================================== '----------------------------------------------- 'PAGE HIT STATS '----------------------------------------------- 'CHECK FOR VALID MONTH/YEAR 'Just check that the given month and year is 'valid. If not, set to current month/year. '----------------------------------------------- numGivenMonth = Request.QueryString("numGivenMonth") numGivenYear = Request.QueryString("numGivenYear") If Not IsNumeric(numGivenMonth) Or numGivenMonth = "" Or Not IsNumeric(numGivenYear) Or numGivenYear = "" Then numGivenMonth = Month(Date()) numGivenYear = Year(Date()) ElseIf DateDiff("m", numGivenYear & "/" & numGivenMonth & "/1", Year(Date) & "/" & Month(Date) & "/1") < 0 Or DateDiff("m", numGivenYear & "/" & numGivenMonth & "/1", DateAdd("m", -11, Year(Date) & "/" & Month(Date) & "/1")) > 0 Then numGivenMonth = Month(Date()) numGivenYear = Year(Date()) End If %>

<% WriteString("PageTitle_AffiliateHitsReport") %> <% Response.Write(MonthName(numGivenMonth) & " " & numGivenYear) %> <% '----------------------------------------------- 'QUERY 'Simple Join, don't need to code specifically 'for MySQL/Access '----------------------------------------------- strQuery = "SELECT COUNT(tblCactuShopAffiliateLog.AFLG_ID) AS Hits, tblCactuShopAffiliates.AF_ID, tblCactuShopAffiliates.AF_Name FROM tblCactuShopAffiliateLog INNER JOIN tblCactuShopAffiliates ON tblCactuShopAffiliateLog.AFLG_AffiliateID = tblCactuShopAffiliates.AF_ID WHERE MONTH(tblCactuShopAffiliateLog.AFLG_DateTime) = " & numGivenMonth & " AND YEAR(tblCactuShopAffiliateLog.AFLG_DateTime) = " & numGivenYear & " GROUP BY tblCactuShopAffiliates.AF_Name, tblCactuShopAffiliates.AF_ID ORDER BY tblCactuShopAffiliates.AF_Name" Call ExecuteSQL(strQuery, numCursorType, objRecordSet) numRecoundCount = objRecordSet.RecordCount '----------------------------------------------- 'STORE VALUES IN ARRAY '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. '----------------------------------------------- ReDim aryData(numRecoundCount, 2) numCounter = 1 numMaxValue = 0 numTotalHits = 0 Do Until objRecordSet.EOF aryData(numCounter, 0) = CInt(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 > numRecoundCount '----------------------------------------------- '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 strBGColour = "#EEEEEE" Else strBGColour = "#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 %> <% numCounter = numCounter + 1 Loop %> <% WriteString("ContentText_ID") %> <% WriteString("ContentText_ShareOfHits") %>   <% WriteString("ContentText_AffiliateName") %> <% =aryData(numCounter, 1) %> <% =aryData(numCounter, 0) %> <% =aryData(numCounter, 2) %> <% WriteString("ContentText_TotalHitsForAboveMonth") %> <% =numTotalHits %> <% WriteString("ContentText_LastTwelveMonths") %> <% '----------------------------------------------- 'USE ARRAY AGAIN FOR EFFICIENCY 'Needs resizing to ensure there is space for 12 'months. 1st of Year ago today. '----------------------------------------------- datYearAgo = DateAdd("m", -11, Year(Date) & "/" & Month(Date) & "/1") '----------------------------------------------- 'QUERY 'Simple Join, don't need to code specifically 'for MySQL/Access '----------------------------------------------- strQuery = "SELECT COUNT(tblCactuShopAffiliateLog.AFLG_ID) AS Hits, YEAR(tblCactuShopAffiliateLog.AFLG_DateTime) AS TheYear, MONTH(tblCactuShopAffiliateLog.AFLG_DateTime) AS TheMonth FROM tblCactuShopAffiliateLog WHERE tblCactuShopAffiliateLog.AFLG_DateTime >= " & strDateDelimiter & ReverseFormatYear(datYearAgo) & strDateDelimiter & " GROUP BY YEAR(tblCactuShopAffiliateLog.AFLG_DateTime), MONTH(tblCactuShopAffiliateLog.AFLG_DateTime) ORDER BY YEAR(tblCactuShopAffiliateLog.AFLG_DateTime) DESC, MONTH(tblCactuShopAffiliateLog.AFLG_DateTime) DESC" Call ExecuteSQL(strQuery, numCursorType, objRecordSet) numRecoundCount = objRecordSet.RecordCount ReDim aryData(12, 2) datDecDate = Year(Date) & "/" & Month(Date) & "/1" numCounter = 1 numMaxValue = 0 numTotalHits = 0 Do Until datDecDate < datYearAgo If objRecordSet.EOF Then 'Got to end of stats - give blank aryData(numCounter, 0) = 0 aryData(numCounter, 1) = Year(datDecDate) aryData(numCounter, 2) = Month(datDecDate) ElseIf CInt(objRecordSet(1)) = Year(datDecDate) And CInt(objRecordSet(2)) = Month(datDecDate) Then 'Stats not finished, and is current month aryData(numCounter, 0) = CInt(objRecordSet(0)) 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(datDecDate) aryData(numCounter, 2) = Month(datDecDate) End If If aryData(numCounter, 0) > numMaxValue Then numMaxValue = aryData(numCounter, 0) numTotalHits = numTotalHits + aryData(numCounter, 0) datDecDate = DateAdd("m", -1, datDecDate) numCounter = numCounter + 1 Loop objRecordSet.Close For numCounter = 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 numCounter / 2 = Int(numCounter / 2) Then strBGColour = "#EEEEEE" Else strBGColour = "#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 %> <% Next %>   <% WriteString("ContentText_ShareOfHits") %>   <% WriteString("ContentText_Month") %>   <% =aryData(numCounter, 0) %> <% If aryData(numCounter, 2) = Int(numGivenMonth) And aryData(numCounter, 1) = Int(numGivenYear) Then Response.Write("" & MonthName(aryData(numCounter, 2)) & " " & aryData(numCounter, 1) & "") Else %><% =MonthName(aryData(numCounter, 2)) & " " & aryData(numCounter, 1) %><% End If %>