Custom ‘Multiple Subscription’ Modification

Description:

        The code below allows for the ability to have multiple ‘subscriptions’ using topic/product combinations by modifying driver.aspx.cs.
        
        


Constraints:
- Must create matching topic/product pair to work correctly.
- Product Name/topic name must have ‘eBook’ in the name
- ‘topic title’ and ‘product name’ must exactly match
- Must setup product as ‘not for download’; no shipping required; basic product
- Must embed PDFs into topic pages
- Can have as many combos as you wish

Functionality:
- In driver.aspx.cs I modified the on_load event to do a DB lookup against 4 tables and redirect to default.aspx if order not found:
a. Orders
b. Orders_ShoppingCart
c. Product
d. Customer

- Find a record that matches the following criteria: (returns CustomerID)

a. Using CustomerID (derived from aspdnsf specific var ‘This.Customer’; available when logged in)
b. Product.Name matches the page topic name
c. Most recent purchase made <365 days ago and matches topic name
d. Order was not voided, refunded, or frauded

EXAMPLE SQL GENERATED: 
SELECT TOP 1 Customer.CustomerID, Orders.AuthorizedOn, Orders.RefundedOn, Orders.VoidedOn, Orders.FraudedOn, Orders_ShoppingCart.ProductID, Product.Name 
FROM Customer INNER JOIN Orders ON Customer.CustomerID = Orders.CustomerID INNER JOIN Orders_ShoppingCart ON Customer.CustomerID = Orders_ShoppingCart.CustomerID AND Orders.OrderNumber = Orders_ShoppingCart.OrderNumber INNER JOIN Product ON Orders_ShoppingCart.ProductID = Product.ProductID 
WHERE (DATEDIFF(dd, Orders.AuthorizedOn, GETDATE()) < 365) AND (Orders.CustomerID = '0') AND (Product.Name = 'eBook1')AND (Orders.RefundedOn IS NULL) AND (Orders.VoidedOn IS NULL) AND (Orders.FraudedOn IS NULL) 
ORDER BY Orders.AuthorizedOn DESC

**This has been tested both using a login and not using a login. It seems to work. However, I would like some more testing done to ensure I didn’t miss a trick in the SQL query.

THIS IS ENTERED IN THE ON_LOAD EVENT AND AFTER THE LINE
int topicID = Topic.GetTopicID(PN); in driver.aspx
::::
==========================================

                //=================================
                //Modified by Beau D'Amore 
                //6-7-2011
                //use the above topicID to find out if it's the eBook or not.
                //Use existing PN var to compare against topicname selected for eBook(s)
                //=================================
                
                //if we find 'eBook' in the topic title, look up subscription info in Orders table====
                if (PN.Contains("eBook"))
                {
                    //look up in DB if topic was purchased and then if subscription is valid/expired==
                    int ValidSubscription = 0;
                    string sSQL = "SELECT TOP 1 Customer.CustomerID as [N], Orders.AuthorizedOn, Orders.RefundedOn, Orders.VoidedOn, Orders.FraudedOn, Orders_ShoppingCart.ProductID, Product.Name " +
                        "FROM Customer INNER JOIN " +
                        "Orders ON Customer.CustomerID = Orders.CustomerID " +
                        "INNER JOIN Orders_ShoppingCart " +
                        "ON Customer.CustomerID = Orders_ShoppingCart.CustomerID " +
                        "AND Orders.OrderNumber = Orders_ShoppingCart.OrderNumber " +
                        "INNER JOIN Product " +
                        "ON Orders_ShoppingCart.ProductID = Product.ProductID " +
                        "WHERE " +
                        "(DATEDIFF(dd, Orders.AuthorizedOn, GETDATE()) < 365) AND " +                                       //Must be within 365 days of AuthorizedOn date
                        "(Orders.CustomerID = '" + ThisCustomer.CustomerID + "') AND " +
                        "(Product.Name = '" + PN + "')" +                                                                   //Make sure the product name matches the topic name for the page
                        "AND (Orders.RefundedOn IS NULL) AND (Orders.VoidedOn IS NULL) AND (Orders.FraudedOn IS NULL) " +   //Make sure it wasn't refunded/frauded/voided
                        "ORDER BY Orders.AuthorizedOn DESC";                                                                //Make sure it's ordered desc by AuthorizedOn to make sure we have the latest purchase in case they bought the years before
                
                    //debugging for live server below.
                    //Response.Write(sSQL);
                    //Response.Flush();
                    //Response.Clear();

                    //ValidSubscription will stay 0 if no record found
                    ValidSubscription = DB.GetSqlN(sSQL);

                    if (ValidSubscription == 0)
                    {
                        //Abandon page/redirect to purchase
                        Response.Redirect("default.aspx");
                    }
                 
                }
                
                //END of TOPIC MODIFICATION==========================================================
                //===================================================================================