<%@ LANGUAGE="VBSCRIPT" %> <% PageStrings = "355, 374, 375, 376, 377, 378, 379, 380, 381, 382, 383, 386, 828" 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. '=============================================== '----------------------------------------------- 'AFFILIATE STATS PAGE '----------------------------------------------- 'CHECK FOR VALID AFFILIATE ID/MONTH/YEAR 'Just check that the given details are all valid ' - if not, if the AF_ID is incorrect, direct to ' sales stats page, else set the month/year to ' today's month/year. '----------------------------------------------- AF_ID = request.querystring("AF_ID") If Not IsNumeric(AF_ID) Or AF_ID = "" Then response.redirect("_affiliate_salesreport.asp") 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 '------------------------------------------ 'FIND TOTAL OF ORDERS FROM THIS AFFILIATE 'IN THIS MONTH 'Also get the affiliate name to save on 'extra database queries. '------------------------------------------ strQuery = "SELECT Sum(Round(ON_AffiliateTotalPrice,2)) AS OrderTotal, Sum(Round(ON_AffiliateTotalPrice * (ON_AffiliatePercentage/100) + 0.00000001 ,2)) AS Commission, tblCactuShopAffiliates.AF_Name, 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 tblCactuShopCardholderDetails.CD_AffiliateID = " & AF_ID & " 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) If objRecordSet.RecordCount = 0 Then numTotalPrice = 0 numTotalCommission = 0 strAffiliateName = "" Else numTotalPrice = objRecordSet(0) numTotalCommission = objRecordSet(1) strAffiliateName = objRecordSet(2) If IsNull(numTotalPrice) Then numTotalPrice = 0 If IsNull(numTotalCommission) Then numTotalCommission = 0 End If objRecordSet.Close %>

<% WriteString("PageTitle_AffiliateReportFor") %> <% =strAffiliateName %> <% Response.Write(MonthName(numGivenMonth) & " " & numGivenYear) %> <% WriteString("ContentText_ValuesShownExCoupons") %> <% WriteString("ContentText_TotalOrderValue") %> <% Response.Write(strDefaultCurrencySymbol & " " & CurrencyDisplay(numTotalPrice, 1, blnDefaultCurrencyHasDecimals)) %> <% WriteString("ContentText_CommissionDueThisMonth") %> <% Response.Write(strDefaultCurrencySymbol & " " & CurrencyDisplay(numTotalCommission, 1, blnDefaultCurrencyHasDecimals)) %> <% WriteString("ContentText_TotalNumberOfHits") %> <% '------------------------------------------ 'TOTAL CLICK-THROUGHS '------------------------------------------ strQuery = "SELECT COUNT(AFLG_ID) AS Clicks FROM tblCactuShopAffiliateLog INNER JOIN tblCactuShopAffiliates ON tblCactuShopAffiliateLog.AFLG_AffiliateID = tblCactuShopAffiliates.AF_ID WHERE AF_ID = " & AF_ID & " AND MONTH(AFLG_DateTime) = " & numGivenMonth & " AND YEAR(AFLG_DateTime) = " & numGivenYear Call ExecuteSQL(strQuery, numCursorType, objRecordSet) If objRecordSet.RecordCount = 0 Then numClicks = 0 Else numClicks = objRecordSet(0) objRecordSet.Close Response.Write(numClicks) %> <% WriteString("ContentText_RawDataHits") %> <% '--------------------------------------- 'GET RAW DATA FROM AffiliateLog '--------------------------------------- strQuery = "SELECT AFLG_DateTime, AFLG_IP, AFLG_Referer FROM tblCactuShopAffiliateLog WHERE AFLG_AffiliateID = " & AF_ID & " AND MONTH(AFLG_DateTime) = " & numGivenMonth & " AND YEAR(AFLG_DateTime) = " & numGivenYear & " ORDER BY AFLG_DateTime ASC" Call ExecuteSQL(strQuery, numCursorType, objRecordSet) '----------------------------------------------- 'CYCLE THROUGH DATA '----------------------------------------------- numCounter = 1 Do Until objRecordSet.EOF If numCounter / 2 = Int(numCounter / 2) then BGColour = "#EEEEEE" Else BGColour = "#DDDDDD" End If %> <% objRecordSet.MoveNext numCounter = numCounter + 1 Loop objRecordSet.Close %>   <% WriteString("ContentText_DateTime") %> <% WriteString("ContentText_IP") %> <% WriteString("ContentText_Referrer") %> <% Response.Write(numCounter) %> <% Response.Write(FriendlyDate(objRecordSet(0), "t")) %> <% Response.Write(objRecordSet(1)) %> <% Response.Write(objRecordSet(2)) %> <% WriteString("ContentText_RawDataSales") %> <% WriteString("ContentText_ValuesShownExCoupons") %> <% '--------------------------------------- 'GET RAW DATA FROM AffiliateLog '--------------------------------------- strQuery = "SELECT SUM(ROUND(ON_AffiliateTotalPrice,2)) AS OrderTotal, SUM(ROUND(ON_AffiliateTotalPrice * (ON_AffiliatePercentage/100) + 0.00000001 ,2)) AS Commission, tblCactuShopOrderNumbers.ON_AffiliatePercentage, tblCactuShopOrderNumbers.ON_Date, tblCactuShopCardholderDetails.CD_ID, tblCactuShopCardholderDetails.CD_CardHolderName, tblCactuShopCardholderDetails.CD_EmailAddress, tblCactuShopOrderNumbers.ON_ID FROM (tblCactuShopAffiliates INNER JOIN tblCactuShopCardholderDetails ON tblCactuShopAffiliates.AF_ID = tblCactuShopCardholderDetails.CD_AffiliateID) INNER JOIN tblCactuShopOrderNumbers ON tblCactuShopOrderNumbers.ON_CardholderID = tblCactuShopCardholderDetails.CD_ID WHERE tblCactuShopCardholderDetails.CD_AffiliateID = " & AF_ID & " AND tblCactuShopOrderNumbers.ON_AffiliatePercentage > 0 AND YEAR(tblCactuShopOrderNumbers.ON_Date) = " & numGivenYear & " AND MONTH(tblCactuShopOrderNumbers.ON_Date) = " & numGivenMonth & " AND ON_Sent = 'y' GROUP BY tblCactuShopOrderNumbers.ON_ID, tblCactuShopOrderNumbers.ON_AffiliatePercentage, tblCactuShopOrderNumbers.ON_Date, tblCactuShopCardholderDetails.CD_ID, tblCactuShopCardholderDetails.CD_CardHolderName, tblCactuShopCardholderDetails.CD_EmailAddress, tblCactuShopOrderNumbers.ON_ID ORDER BY tblCactuShopOrderNumbers.ON_ID ASC" Call ExecuteSQL(strQuery, numCursorType, objRecordSet) '----------------------------------------------- 'CYCLE THROUGH DATA '----------------------------------------------- numCounter = 0 Do Until objRecordSet.EOF numCounter = numCounter + 1 If numCounter / 2 = Int(numCounter / 2) Then BGColour = "#EEEEEE" Else BGColour = "#DDDDDD" End If strCustoAltText = objRecordSet("CD_CardHolderName") & " (" & objRecordSet("CD_EmailAddress") & ")" %> <% objRecordSet.movenext Loop objRecordSet.Close objDataConn.Close Set objRecordSet = Nothing Set objDataConn = Nothing %>   <% WriteString("ContentText_DateTime") %> <% Response.Write(GetString("ContentText_Value") & " (" & strDefaultCurrencySymbol & ")") %> <% Response.Write(GetString("ContentText_Commission") & " (" & strDefaultCurrencySymbol & ")") %>    <% Response.Write(numCounter) %> <% Response.Write(FriendlyDate(objRecordSet(3), "t")) %> <% Response.Write(CurrencyDisplay(objRecordSet(0), 1, blnDefaultCurrencyHasDecimals)) %>   <% Response.Write(CurrencyDisplay(objRecordSet(1), 1, blnDefaultCurrencyHasDecimals) & " (" & objRecordSet(2) & "%)") %>   "> ">