<%@ LANGUAGE="VBSCRIPT" %> <% PageStrings = "311, 330, 331, 332, 333, 334, 335, 336, 337, 338, 339, 913, 1083, 1084, 1085" 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. '=============================================== '----------------------------------------------- 'CUSTOMERS LIST 'Shows customers, allows the store owner to find 'a customer, list all their orders and give them 'a discount which will be applied to future 'purchases from the shop. '----------------------------------------------- strAffiliateEmailAddress = request.querystring("strAffiliateEmailAddress") numResultsLimit = request.querystring("numResultsLimit") * 1 If numResultsLimit = 0 then numResultsLimit = 9999 If datevalue1 = "" then datevalue1 = now() else if NOT IsDate(datevalue1) then numErrorNo = 1 else datevalue1=Cdate(formatdatetime(datevalue1)) end if End if If numErrorNo = 0 then datevalue2 = now() numDueDays = CInt(Application(LICENSENUMBER & "affiliatecommissionduedays")) datOldOrder = DateAdd("d", -numDueDays, now) 'Because MySQL sucks and doesn't support sub-clauses, we have to do a 'simple query for mysql and then run a seperate query for each record. if strDataBaseType = "mysql" then strQuery = "SELECT tblCactuShopAffiliates.AF_ID, tblCactuShopAffiliates.AF_Name, tblCactuShopAffiliates.AF_EmailAddress FROM tblCactuShopAffiliates WHERE AF_EmailAddress LIKE '%" & sqlsafe(strAffiliateEmailAddress) & "%' AND AF_EmailAddress<>'' ORDER BY AF_ID" else 'SQL Server uses CASE and Access uses IIF to make IF statements, 'so the queries need to be different here too if strDataBaseType = "mssql" then strQuery = "SELECT tblCactuShopAffiliates.AF_ID, tblCactuShopAffiliates.AF_Name, tblCactuShopAffiliates.AF_EmailAddress, Sum(UnPaidOrders.Commission) AS TotalCommission, Sum(UnPaidOrders.OldCommission) As TotalOldCommission FROM tblCactuShopAffiliates LEFT JOIN (tblCactuShopCardholderDetails LEFT JOIN (SELECT tblCactuShopOrderNumbers.ON_ID, tblCactuShopOrderNumbers.ON_CardHolderID, ROUND(ON_AffiliateTotalPrice * (ON_AffiliatePercentage/100) + 0.0000001, 2) AS Commission, (CASE WHEN ON_Date <= " & strDateDelimiter & ReverseFormatYear(datOldOrder) & strDateDelimiter & " THEN ROUND(ON_AffiliateTotalPrice * (ON_AffiliatePercentage/100) + 0.0000001, 2) ELSE 0 END) AS OldCommission FROM tblCactuShopOrderNumbers WHERE tblCactuShopOrderNumbers.ON_AffiliatePaymentID = 0 AND tblCactuShopOrderNumbers.ON_Sent = 'y') AS UnPaidOrders ON tblCactuShopCardholderDetails.CD_ID = UnPaidOrders.ON_CardHolderID) ON tblCactuShopAffiliates.AF_ID = tblCactuShopCardholderDetails.CD_AffiliateID WHERE AF_EmailAddress LIKE '%" & sqlsafe(strAffiliateEmailAddress) & "%' AND AF_EmailAddress <> '' GROUP BY tblCactuShopAffiliates.AF_ID, tblCactuShopAffiliates.AF_Name, tblCactuShopAffiliates.AF_EmailAddress ORDER BY tblCactuShopAffiliates.AF_ID;" else strQuery = "SELECT tblCactuShopAffiliates.AF_ID, tblCactuShopAffiliates.AF_Name, tblCactuShopAffiliates.AF_EmailAddress, Sum(UnPaidOrders.Commission) AS TotalCommission, Sum(UnPaidOrders.OldCommission) As TotalOldCommission FROM tblCactuShopAffiliates LEFT JOIN (tblCactuShopCardholderDetails LEFT JOIN (SELECT tblCactuShopOrderNumbers.ON_ID, tblCactuShopOrderNumbers.ON_CardHolderID, ROUND(ON_AffiliateTotalPrice * (ON_AffiliatePercentage/100) + 0.0000001, 2) AS Commission, IIF(ON_Date <= " & strDateDelimiter & ReverseFormatYear(datOldOrder) & strDateDelimiter & ", ROUND(ON_AffiliateTotalPrice * (ON_AffiliatePercentage/100) + 0.0000001, 2), 0) AS OldCommission FROM tblCactuShopOrderNumbers WHERE tblCactuShopOrderNumbers.ON_AffiliatePaymentID = 0 AND tblCactuShopOrderNumbers.ON_Sent = 'y') AS UnPaidOrders ON tblCactuShopCardholderDetails.CD_ID = UnPaidOrders.ON_CardHolderID) ON tblCactuShopAffiliates.AF_ID = tblCactuShopCardholderDetails.CD_AffiliateID WHERE AF_EmailAddress LIKE '%" & sqlsafe(strAffiliateEmailAddress) & "%' AND AF_EmailAddress <> '' GROUP BY tblCactuShopAffiliates.AF_ID, tblCactuShopAffiliates.AF_Name, tblCactuShopAffiliates.AF_EmailAddress ORDER BY tblCactuShopAffiliates.AF_ID;" end if end if Call ExecuteSQL(strQuery, numCursorType, objRecordSet) numRecordCount = objRecordSet.recordcount End if %>

<% WriteString("PageTitle_Affiliates") %> <% WriteString("ContentText_New") %> <% WriteString("ContentText_FindAnAffiliate") %> "> <% WriteString("ContentText_EnterEmailAddress") %> <% If numBackgroundCounter>numResultsLimit-1 then %> <% End if %> <% if numRecordCount<>0 then do while numBackgroundCounter ''" call ExecuteSQL(strQuery,numCursorType,objRecordSet2) numTotalCommission = objRecordSet2("TotalCommission") objRecordSet2.Close strQuery = "SELECT SUM(ROUND(ON_AffiliateTotalPrice * (ON_AffiliatePercentage/100) + 0.0000001, 2)) AS TotalOldCommission FROM tblCactuShopOrderNumbers INNER JOIN tblCactuShopCardholderDetails ON tblCactuShopOrderNumbers.ON_CardHolderID = tblCactuShopCardholderDetails.CD_ID WHERE tblCactuShopCardholderDetails.CD_AffiliateID = " & AF_ID & " AND tblCactuShopOrderNumbers.ON_AffiliatePaymentID = 0 AND tblCactuShopOrderNumbers.ON_Sent = 'y' AND CD_EmailAddress LIKE '%" & sqlsafe(strAffiliateEmailAddress) & "%' AND CD_EmailAddress <> '' AND ON_Date <= " & strDateDelimiter & ReverseFormatYear(datOldOrder) & strDateDelimiter call ExecuteSQL(strQuery,numCursorType,objRecordSet2) numTotalOldCommission = objRecordSet2("TotalOldCommission") objRecordSet2.Close else numTotalCommission = objRecordSet("TotalCommission") numTotalOldCommission = objRecordSet("TotalOldCommission") end if strTotalCommission = strDefaultCurrencySymbol & " " & CurrencyDisplay(numTotalCommission, 1, blnDefaultCurrencyHasDecimals) strTotalOldCommission = strDefaultCurrencySymbol & " " & CurrencyDisplay(numTotalOldCommission, 1, blnDefaultCurrencyHasDecimals) strThisBgColour = strBgColourNorm If numTotalCommission = 0 then strThisBgColour = strBgColourFine If numTotalOldCommission > 0 then strThisBgColour = strBgColourWarn %> <% objRecordSet.moveNext loop %> <% else %> <% end if objRecordSet.Close %> <% WriteString("ContentText_ID") %> <% WriteString("ContentText_AffiliateName") %> <% WriteString("ContentText_PaymentDue") %> <% WriteString("ContentText_OverDue") %>   <% =AF_ID %> <% =objRecordSet("AF_Name") %> <% =strTotalCommission %> <% =strTotalOldCommission %> " title="<% WriteSafeString("ImageLabel_EditThisAffiliate") %>" src="images_webshop/b_payment.gif" border="0"> " title="<% WriteSafeString("ImageLabel_EditThisAffiliate") %>" src="images_webshop/modify.gif" border="0"> <% WriteString("ContentText_AffiliateListIndicates") %> <% WriteString("ContentText_NoAffiliatesWereFound") %> <% WriteString("ContentText_ThereAreMoreAffiliates") %> <% WriteString("ContentText_ClickHere") %>

<% objDataConn.Close set objDataConn = nothing %>