Difference between revisions of "Data Access Layer - AC7"

CREATE TABLE [dbo].[ac_Affiliates](
   [AffiliateId] [int] IDENTITY(1,1) NOT NULL,
   [StoreId] [int] NOT NULL,
   [Name] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
   [PayeeName] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [FirstName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [LastName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Company] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Address1] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Address2] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [City] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Province] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [PostalCode] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [CountryCode] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [PhoneNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [FaxNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [MobileNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [WebsiteUrl] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [Email] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [CommissionRate] [decimal](9, 4) NOT NULL,
   [CommissionIsPercent] [bit] NOT NULL,
   [CommissionOnTotal] [bit] NOT NULL,
   [ReferralDays] [smallint] NOT NULL,
CONSTRAINT [ac_Affiliates_PK] PRIMARY KEY CLUSTERED
(
   [AffiliateId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[ac_Affiliates]  WITH CHECK ADD  CONSTRAINT [ac_Stores_ac_Affiliates_FK1] FOREIGN KEY([StoreId])
REFERENCES [dbo].[ac_Stores] ([StoreId])
GO
ALTER TABLE [dbo].[ac_Affiliates] CHECK CONSTRAINT [ac_Stores_ac_Affiliates_FK1]

Affiliate.cs
AffiliateDataSource.cs
Affiliate.Generated.cs
AffiliateCollection.Generated.cs
AffiliateDataSource.Generated.cs

//Affiliate.cs
using System;
using CommerceBuilder.Common;

namespace CommerceBuilder.Marketing
{
    /// <summary>
    /// This class represents a Affiliate object in the database.
    /// </summary>
    public partial class Affiliate
    {
        /// <summary>
        /// Calculates commission for this affiliate
        /// </summary>
        /// <param name="orderCount">Number of orders</param>
        /// <param name="productSubtotal">Sub total of the products</param>
        /// <param name="orderTotal">Total value of orders</param>
        /// <returns>The calculated commission</returns>
        public LSDecimal CalculateCommission(int orderCount, LSDecimal productSubtotal, LSDecimal orderTotal)
        {
            if (this.CommissionIsPercent)
            {
                if (this.CommissionOnTotal) return Math.Round((((Decimal)orderTotal * (Decimal)this.CommissionRate) / 100), 2);
                return Math.Round((((Decimal)productSubtotal * (Decimal)this.CommissionRate) / 100), 2);
            }
            else
            {
                return (orderCount * this.CommissionRate);
            }
        }
    }
}

//AffiliateDataSource.cs
using System;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.Practices.EnterpriseLibrary.Data;
using CommerceBuilder.Common;
using CommerceBuilder.Users;
using CommerceBuilder.Utility;
using System.ComponentModel;
using System.Web.Security;

namespace CommerceBuilder.Marketing
{
    [DataObject(true)]
    public partial class AffiliateDataSource
    {
        /// <summary>
        /// Gets a count of users who were referred by the affiliate during the given timeframe.
        /// </summary>
        /// <param name="affiliateId">Id of the affiliate to count users for.</param>
        /// <param name="startDate">Inclusive start date of timeframe to count.</param>
        /// <param name="endDate">Inclusive end date of timeframe to count.</param>
        /// <returns>The number of new visitors referred during the given timeframe.</returns>
        public static int GetReferralCount(int affiliateId, DateTime startDate, DateTime endDate)
        {
            Database database = Token.Instance.Database;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(*) AS TotalRecords FROM ac_Users WHERE ReferringAffiliateId = @affiliateId");
            if (startDate > DateTime.MinValue) sql.Append(" AND CreateDate >= @startDate");
            if (endDate > DateTime.MinValue) sql.Append(" AND CreateDate <= @endDate");
            DbCommand selectCommand = database.GetSqlStringCommand(sql.ToString());
            database.AddInParameter(selectCommand, "@affiliateId", System.Data.DbType.Int32, affiliateId);
            if (startDate > DateTime.MinValue) database.AddInParameter(selectCommand, "@startDate", System.Data.DbType.DateTime, startDate);
            if (endDate > DateTime.MinValue) database.AddInParameter(selectCommand, "@endDate", System.Data.DbType.DateTime, endDate);
            return CommerceBuilder.Utility.AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));
        }

        /// <summary>
        /// Calculates the percentage of referred users who made purchases for a given timeframe.
        /// </summary>
        /// <param name="affiliateId">Id of the affiliate to calculate conversion rate for.</param>
        /// <param name="startDate">Inclusive start date of timeframe to calculate.</param>
        /// <param name="endDate">Inclusive end date of timeframe to calculate.</param>
        /// <param name="totalReferrals">The number of referrals for the timeframe.</param>
        /// <returns>The conversion rate for the affiliate for the given timeframe.</returns>
        public static LSDecimal GetConversionRate(int affiliateId, DateTime startDate, DateTime endDate, int totalReferrals)
        {
            if (totalReferrals == 0) return 0;
            Database database = Token.Instance.Database;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT COUNT(DISTINCT ac_Users.UserId) AS TotalRecords");
            sql.Append(" FROM ac_Users INNER JOIN ac_Orders ON ac_Users.UserId = ac_Orders.UserId");
            sql.Append(" WHERE ac_Users.ReferringAffiliateId = @affiliateId");
            if (startDate > DateTime.MinValue) sql.Append(" AND ac_Users.CreateDate >= @startDate");
            if (endDate > DateTime.MinValue) sql.Append(" AND ac_Users.CreateDate <= @endDate");
            DbCommand selectCommand = database.GetSqlStringCommand(sql.ToString());
            database.AddInParameter(selectCommand, "@affiliateId", System.Data.DbType.Int32, affiliateId);
            if (startDate > DateTime.MinValue) database.AddInParameter(selectCommand, "@startDate", System.Data.DbType.DateTime, startDate);
            if (endDate > DateTime.MinValue) database.AddInParameter(selectCommand, "@endDate", System.Data.DbType.DateTime, endDate);
            int convertedCustomers = CommerceBuilder.Utility.AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));
            LSDecimal percentage = (LSDecimal)convertedCustomers / (LSDecimal)totalReferrals;
            percentage = percentage * 100;
            percentage = Math.Round((Decimal)percentage, 2);
            return percentage;
        }

    }
}

namespace CommerceBuilder.Marketing
{
    using System;
    using CommerceBuilder.Common;
    /// <summary>
    /// This class implements a PersistentCollection of Affiliate objects.
    /// </summary>
    public partial class AffiliateCollection : PersistentCollection<Affiliate>
    {
        /// <summary>
        /// Gets the index of the CatalogNode object in this collection whose primary key
        /// matches the given value.
        /// </summary>
        /// <param name="affiliateId">Value of AffiliateId of the required object.</param>
        /// <returns>Index of the required object.</returns>
        public int IndexOf(Int32 affiliateId)
        {
            for (int i = 0; i < this.Count; i++)
            {
                if (affiliateId == this[i].AffiliateId) return i;
            }
            return -1;
        }
    }
}

      /// <summary>
        /// Name
        /// </summary>
        public String Name
        {
            get { return this._Name; }
            set
            {
                if (this._Name != value)
                {
                    this._Name = value;
                    this.IsDirty = true;
                }
            }
        }

        /// <summary>
        /// A collection of Order objects associated with this Affiliate object.
        /// </summary>
        public OrderCollection Orders
        {
            get
            {
                if (!this.OrdersLoaded)
                {
                    this._Orders = OrderDataSource.LoadForAffiliate(this.AffiliateId);
                }
                return this._Orders;
            }
        }

/// <summary>
        /// Counts the number of Affiliate objects for the current store.
        /// </summary>
        /// <returns>The Number of Affiliate objects in the current store.</returns>
        public static int CountForStore()
        {
            int storeId = Token.Instance.StoreId;
            Database database = Token.Instance.Database;
            DbCommand selectCommand = database.GetSqlStringCommand("SELECT COUNT(*) AS TotalRecords FROM ac_Affiliates WHERE StoreId = @storeId");
            database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, storeId);
            return CommerceBuilder.Utility.AlwaysConvert.ToInt(database.ExecuteScalar(selectCommand));
        }

        /// <summary>
        /// Loads a collection of Affiliate objects for the current store from the database
        /// </summary>
        /// <returns>A collection of Affiliate objects</returns>
        [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static AffiliateCollection LoadForStore()
        {
            return LoadForStore(0, 0, string.Empty);
        }

        /// <summary>
        /// Loads a collection of Affiliate objects for the current store from the database. Sorts using the given sort exrpression.
        /// </summary>
        /// <param name="sortExpression">The sort expression to use for sorting the loaded objects.</param>
        /// <returns>A collection of Affiliate objects</returns>
        [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static AffiliateCollection LoadForStore(string sortExpression)
        {
            return LoadForStore(0, 0, sortExpression);
        }

        /// <summary>
        /// Loads a collection of Affiliate objects for the current store from the database.
        /// </summary>
        /// <param name="maximumRows">Maximum number of rows to retrieve.</param>
        /// <param name="startRowIndex">Starting index from where to start retrieving.</param>
        /// <returns>A collection of Affiliate objects</returns>
        [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static AffiliateCollection LoadForStore(int maximumRows, int startRowIndex)
        {
            return LoadForStore(maximumRows, startRowIndex, string.Empty);
        }

        /// <summary>
        /// Loads a collection of Affiliate objects for the current store from the database. Sorts using the given sort exrpression.
        /// </summary>
        /// <param name="maximumRows">Maximum number of rows to retrieve.</param>
        /// <param name="startRowIndex">Starting index from where to start retrieving.</param>
        /// <param name="sortExpression">The sort expression to use for sorting the loaded objects.</param>
        /// <returns>A collection of Affiliate objects</returns>
        [System.ComponentModel.DataObjectMethod(System.ComponentModel.DataObjectMethodType.Select)]
        public static AffiliateCollection LoadForStore(int maximumRows, int startRowIndex, string sortExpression)
        {
            int storeId = Token.Instance.StoreId;
            //CREATE THE DYNAMIC SQL TO LOAD OBJECT
            StringBuilder selectQuery = new StringBuilder();
            selectQuery.Append("SELECT");
            if (maximumRows > 0) selectQuery.Append(" TOP " + (startRowIndex + maximumRows).ToString());
            selectQuery.Append(" " + Affiliate.GetColumnNames(string.Empty));
            selectQuery.Append(" FROM ac_Affiliates");
            selectQuery.Append(" WHERE StoreId = @storeId");
            if (!string.IsNullOrEmpty(sortExpression)) selectQuery.Append(" ORDER BY " + sortExpression);
            Database database = Token.Instance.Database;
            DbCommand selectCommand = database.GetSqlStringCommand(selectQuery.ToString());
            database.AddInParameter(selectCommand, "@storeId", System.Data.DbType.Int32, storeId);
            //EXECUTE THE COMMAND
            AffiliateCollection results = new AffiliateCollection();
            int thisIndex = 0;
            int rowCount = 0;
            using (IDataReader dr = database.ExecuteReader(selectCommand))
            {
                while (dr.Read() && ((maximumRows < 1) || (rowCount < maximumRows)))
                {
                    if (thisIndex >= startRowIndex)
                    {
                        Affiliate affiliate = new Affiliate();
                        Affiliate.LoadDataReader(affiliate, dr);
                        results.Add(affiliate);
                        rowCount++;
                    }
                    thisIndex++;
                }
                dr.Close();
            }
            return results;
        }

<cb:SortedGridView ID="AffiliateGrid" runat="server" AllowPaging="true" AllowSorting="true" PageSize="20"
                            AutoGenerateColumns="False" DataKeyNames="AffiliateId" DataSourceID="AffiliateDs"
                            ShowFooter="False" DefaultSortExpression="Name" SkinID="PagedList" Width="400">

.....
.....
.....
.....
.....

    <asp:ObjectDataSource ID="AffiliateDs" runat="server" OldValuesParameterFormatString="original_{0}"
        SelectMethod="LoadForStore" TypeName="CommerceBuilder.Marketing.AffiliateDataSource"
        SelectCountMethod="CountForStore" SortParameterName="sortExpression" DataObjectTypeName="CommerceBuilder.Marketing.Affiliate"
        DeleteMethod="Delete" UpdateMethod="Update">
    </asp:ObjectDataSource>