<%'***** here is where the other table started ***********************************************************%> <%'***** sales reporting menu *********************************************************%> <%'***** sales reporting menu *********************************************************%> <%'***** sales reporting menu *********************************************************%> StoreFront Sales Reports This tool will allow you to view detailed reports of all transactions. All Orders by Date Beginning Date:  Ending Date:  Sales Summary by Date Beginning Date:  Ending Date:  Transaction Service Reports Beginning Date:  Ending Date:  Referring Trading Partner Reports Beginning Date:  Ending Date:

<%'***** beginning of the really long coding for sales reports ************************%> <%'***** beginning of the really long coding for sales reports ************************%> <%'***** beginning of the really long coding for sales reports ************************%> <% 'Report 1 is the Order Summary Report for the requested time period. If Request("SalesReport") = "1" Then StartDate = Request("StartDate")&" 12:00:01 AM" EndDate = Request("EndDate")&" 11:59:59 PM" SQLStmt = "SELECT CUSTOMER_ID, NAME, ORDER_DATE, GRAND_TOTAL FROM Customer " SQLStmt = SQLStmt & "WHERE ((ORDER_DATE >= #" & StartDate & "#) " SQLStmt = SQLStmt & "AND (ORDER_DATE <= #" & EndDate & "#)) " SQLStmt = SQLStmt & "AND GRAND_TOTAL <> '$0.00' " Set RSSumDate = Connection.Execute(SQLStmt) %> <% If RSSumDate.EOF Then %> <% Else %> <% Do While NOT RSSumDate.EOF %> <% RSSumDate.MoveNext Loop %> <% End If %> There were no sales reported for the period <%= Request("StartDate") %> to <%= Request("EndDate") %> Sales For the Period <%= Request("StartDate") %> to <%= Request("EndDate") %> Date of Order Order ID Customer Name Order Total <%= RSSumDate("ORDER_DATE") %> "><%= RSSumDate("CUSTOMER_ID") %> <%= RSSumDate("NAME") %> <%= RSSumDate("GRAND_TOTAL") %> <%'***** salesreports report 2 ********************************************************%> <%'***** salesreports report 2 ********************************************************%> <%'***** salesreports report 2 ********************************************************%> <% ElseIf Request("SalesReport") = "2" Then 'Report 2 is the Order Detail for a transaction reported 'in the Order Summary report. SQLStmt = "SELECT * FROM Customer WHERE CUSTOMER_ID = " SQLStmt = SQLStmt & "" & Request("OrderID") & " " 'Response.Write (SQLStmt) Set RSCustDetail = Connection.Execute(SQLStmt) SQLStmt = "SELECT orders.AttributeA, orders.AttributeB, orders.AttributeC, " _ & "orders.Product_ID, orders.Description, orders.Price, orders.Quantity, " _ & "orders.Total FROM orders, product WHERE ORDER_ID = " & Request("OrderID") & " AND " _ & "orders.Product_ID = product.Product_ID " 'Response.Write (SQLStmt) Set RSOrderDetail = Connection.Execute(SQLStmt) GrandTotal = Ccur(RSCustDetail("GRAND_TOTAL")) SubTotal = Ccur(RSCustDetail("SUB_TOTAL")) Tax = ccur(RSCustDetail("TAX")) ShipAmt = ccur(RSCustDetail("SHIPPING_AMT")) HandlingCOD = FormatCurrency(GrandTotal-(SubTotal+Tax+ShipAmt)) %> <% If (RSCustDetail("SHIP_STATE")) <> "" AND (RSCustDetail("SHIP_COUNTRY")) <> "" Then %> <% End If %> <% If Trim(RSCustDetail("PAYMENT_METHOD")) = "Credit Card" Then %> <% ElseIf Trim(RSCustDetail("PAYMENT_METHOD")) = "Electronic Check" Then %> <% End If %> Transaction Detail Report for Order Number: <%= Request("OrderID") %> Sold To Name: <%= RSCustDetail("NAME") %> Order Date: <%= FormatDateTime(RSCustDetail("ORDER_DATE"),vbShortDate) %> Company: <%= RSCustDetail("COMPANY") %> Order Time: <%= FormatDateTime(RSCustDetail("ORDER_DATE"),vbLongTime) %> Address: <%= RSCustDetail("ADDRESS_2") %> Address: <%= RSCustDetail("ADDRESS_1") %> City: <%= RSCustDetail("CITY") %> State: <%= RSCustDetail("STATE") %> Zip: <%= RSCustDetail("ZIP") %> Country: <%= RSCustDetail("COUNTRY") %> Phone: <%= RSCustDetail("PHONE") %> Fax: <%= RSCustDetail("FAX") %> E-Mail: <%= RSCustDetail("E_MAIL") %> Ship To Name: <%= RSCustDetail("SHIP_NAME") %> Company: <%= RSCustDetail("SHIP_COMPANY") %> Address: <%= RSCustDetail("SHIP_ADDRESS_2") %> Address: <%= RSCustDetail("SHIP_ADDRESS_1") %> City: <%= RSCustDetail("SHIP_CITY") %> State: <%= RSCustDetail("SHIP_STATE") %> Zip: <%= RSCustDetail("SHIP_ZIP") %> Country: <%= RSCustDetail("SHIP_COUNTRY") %> Phone: <%= RSCustDetail("SHIP_TELEPHONE") %> Special Instructions <%= RSCustDetail("SHIP_MESSAGE") %> Credit Card Information Card Type: <%= RSCustDetail("CARD_TYPE") %> Card Number: <%= RSCustDetail("CARD_NO") %> Card Name: <%= RSCustDetail("NAME") %> Expiration Date: <%= RSCustDetail("CARD_EXP") %> E-Check Information Customer Name: <%= RSCustDetail("NAME") %> Bank Name: <%= RSCustDetail("BANK_NAME") %> Routing Number: <%= RSCustDetail("ROUTING_NO") %> Account Number: <%= RSCustDetail("CHK_ACCT_NO") %> <% RSOrderDetail.MoveFirst CurrentRecord = 0 Do While NOT RSOrderDetail.EOF AttA = RSOrderDetail("AttributeA") AttB = RSOrderDetail("AttributeB") AttC = RSOrderDetail("AttributeC") If AttA <> "" Then ATTResponse = AttA End If If AttB <> "" Then ATTResponse = AttResponse&", "&AttB End If If AttC <> "" Then ATTResponse = AttResponse&", "&AttC End If ATTResponse = ATTResponse&" " %> <% AttResponse = "" RSOrderDetail.MoveNext CurrentRecord = CurrentRecord = 1 Loop %> <% If HandlingCOD > FormatCurrency(0) Then %> <% End If %> <% If Trim(RSCustDetail("PAYMENT_METHOD")) = "Electronic Check" and Trim(RSCustDetail("ROUTING_NO")) <> "AUTHNET TRANSACTION" Then %> <% End If %> Product Code Description Unit Price Quantity Total <%= RSOrderDetail("PRODUCT_ID") %> <%= ATTResponse&RSOrderDetail("DESCRIPTION") %> <%= FormatCurrency(RSOrderDetail("PRICE")) %>   <%= RSOrderDetail("QUANTITY") %>   <%= FormatCurrency(RSOrderDetail("TOTAL")) %>   Sub Total: <%= RSCustDetail("SUB_TOTAL") %> Tax: <%= RSCustDetail("TAX") %> <%=RSCustDetail("SHIPPING_METHOD") %> <%=RSCustDetail("SHIPPING_AMT") %> Handling/COD Charges: <%= HandlingCOD %> Total <%= RSCustDetail("GRAND_TOTAL") %> " method="post" id=form1 name=form1> " method="Post" id=form2 name=form2> " method="Post" id=form3 name=form3> <%'***** salesreports report 3 ********************************************************%> <%'***** salesreports report 3 ********************************************************%> <%'***** salesreports report 3 ********************************************************%> <% ' Print Invoice Routine : performed in printinvoice.asp%> <%'***** salesreports report 4 ********************************************************%> <%'***** salesreports report 4 ********************************************************%> <%'***** salesreports report 4 ********************************************************%> <% ElseIf Request("SalesReport") = "4" Then 'Report 4 is the Sales Transaction Report for summarizing Tax, 'Shipping, Net and Gross Sales for the selected period. StartDate = Request("StartDate")&" 12:00:01 AM" EndDate = Request("EndDate")&" 11:59:59 PM" SQLStmt = "SELECT Sum(CCur(SUB_TOTAL)) AS SumSubTotal, Sum(CCur(TAX)) " _ & "AS SumTax, Sum(CCur(SHIPPING_AMT)) AS SumShipAMT, " _ & "Sum(CCur(GRAND_TOTAL)) AS SumGrandTotal " _ & "FROM Customer WHERE ((ORDER_DATE >= #" & StartDate & "#) AND " _ & "(ORDER_DATE <= #" & EndDate & "#))" Set RSSummary = Connection.Execute(SQLStmt) If IsNull (RSSummary("SumGrandTotal")) Then SumGrandTotal = 0 SumSubTotal = 0 SumShipAMT = 0 SumTax = 0 Else SumGrandTotal = RSSummary("SumGrandTotal") SumSubTotal = RSSummary("SumSubTotal") SumShipAMT = RSSummary("SumShipAMT") SumTax = RSSummary("SumTax") End If SumHandlingCOD = FormatCurrency(SumGrandTotal-(SumSubTotal+SumTax+SumShipAMT)) If SumGrandTotal = "0" Then %> There were no sales for the period <%= Request("StartDate") %> to <%= Request("EndDate") %> <% Else %> <% CurrentRecord = 0 Do While NOT RSSummary.EOF %> <% RSSummary.MoveNext CurrentRecord = CurrentRecord = 1 Loop %> Summary Sales Reports for the period <%= Request("StartDate") %> to <%= Request("EndDate") %> Net Sales:  <%= FormatCurrency(SumSubTotal) %> Total Tax:  <%= FormatCurrency(SumTax) %> Total Shipping:  <%= FormatCurrency(SumShipAMT) %> Total Handling/COD Charges:  <%= SumHandlingCOD %> Total Gross Sales:  <%= FormatCurrency(SumGrandTotal) %> <% End If %> <%'***** salesreports report 5 ********************************************************%> <%'***** salesreports report 5 ********************************************************%> <%'***** salesreports report 5 ********************************************************%> <% ElseIf Request("SalesReport") = "5" Then SQLStmt = "DELETE * FROM customer WHERE CUSTOMER_ID = " & Request("OrderID") & " " Set RSDelete = Connection.Execute(SQLStmt) %> Transaction Number <%= Request("OrderID") %> has been deleted. <%'***** salesreports report 6 (goes to printcheck.asp) *******************************%> <%'***** salesreports report 6 (goes to printcheck.asp) *******************************%> <%'***** salesreports report 6 (goes to printcheck.asp) *******************************%> <% ' Print Check Routine : performed in printcheck.asp%> <%'***** salesreports report 7 ********************************************************%> <%'***** salesreports report 7 ********************************************************%> <%'***** salesreports report 7 ********************************************************%> <% ElseIf Request("SalesReport") = "7" Then StartDate = Request("StartDate")&" 12:00:01 AM" EndDate = Request("EndDate")&" 11:59:59 PM" SQLStmt = "Select DISTINCT REFERER, HTTP_REFERER FROM customer " SQLStmt = SQLStmt & "WHERE ((ORDER_DATE >= #" & StartDate & "#) AND " SQLStmt = SQLStmt & "(ORDER_DATE <= #" & EndDate & "#)) " Set RSRef = Connection.Execute(SQLStmt) %> <% If RSRef.EOF or RSRef.BOF Then %> <% Else %> <% RSRef.MoveFirst Do While NOT RSRef.EOF %> <% SQLSTmt = "SELECT Sum(CCur(SUB_TOTAL)) as refSubTotal from customer " SQLStmt = SQLStmt & "WHERE (customer.REFERER = '" & RSRef("REFERER") & "') " Set RSRefTotal = Connection.Execute(SQLStmt) %> <% 'If RSRefTotal("RefSubTotal") > 0 Then %> <% If Trim(RSRef("REFERER")) = "REFERER_ID" OR Trim(RSRef("REFERER")) = "" Then %> <% ' Do Nothing %> <% Else %> <% End If %> <% RSRef.MoveNext Loop %> <% End If %> There were no Referer sales reported for the period <%= Request("StartDate") %> to <%= Request("EndDate") %> Referer Sales For the Period <%= Request("StartDate") %> to <%= Request("EndDate") %> Referring Partner Referring Domain Total Sales <%= RSRef("REFERER") %> <%= RSRef("HTTP_REFERER") %> <%= FormatCurrency(RSRefTotal("refSubTotal"),2) %> <%'***** salesreports report 8 ********************************************************%> <%'***** salesreports report 8 ********************************************************%> <%'***** salesreports report 8 ********************************************************%> <% ElseIf Request("SalesReport") = "8" Then SQLStmt = "SELECT * FROM transactions, customer, orders " SQLStmt = SQLStmt & "WHERE (((customer.ORDER_DATE >= #" & Request("StartDate") & "#) " SQLStmt = SQLStmt & "AND (customer.ORDER_DATE <= #" & Request("EndDate") & "#)) AND " SQLStmt = SQLStmt & "(customer.CUSTOMER_ID = transactions.ORDER_ID) AND " SQLStmt = SQLStmt & "(customer.CUSTOMER_ID = orders.ORDER_ID)) " Set RSCCDate = Connection.Execute(SQLStmt) If RSCCDate.EOF Then %> There are no reports for the period <%= Request("StartDate") %> to <%= Request("EndDate") %> <% Else %>Transaction Summary Report for: <%= Request("StartDate") %> to <%= Request("EndDate") %> <% CurrentRecord = 0 Do While NOT RSCCDate.EOF %> Order ID Order Date Cust. Trans. No. Merch. Trans. No.  "><%= RSCCDate("ORDER_ID") %>  <%= RSCCDate("ORDER_DATE") %>  <%= RSCCDate("CUST_TRANS_NO") %>  <%= RSCCDate("MERCH_TRANS_NO") %> AVS Code Aux. Msg. Action Code Retrieval Code  <%= RSCCDate("AVS_CODE") %>  <%= RSCCDate("AUX_MSG") %>  <%= RSCCDate("ACTION_CODE") %>  <%= RSCCDate("RETRIEVAL_CODE") %> Auth No. Error Msg. Error Loc. Status  <%= RSCCDate("AUTH_NO") %>  <%= RSCCDate("ERROR_MSG") %>  <%= RSCCDate("ERROR_LOCATION") %>  <%= RSCCDate("STATUS") %> <% RSCCDate.MoveNext CurrentRecord = CurrentRecord = 1 Loop End If %> <%'***** end of the line pal **********************************************************%> <%'***** end of the line pal **********************************************************%> <%'***** end of the line pal **********************************************************%> <% End If %> <% Connection.Close %>