statistics.cs

动易SiteFactory&#8482 网上商店系统1.0源代码

namespace PowerEasy.SqlServerDal.Shop
{
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using PowerEasy.Common;
    using PowerEasy.IDal.Shop;
    using PowerEasy.Model.Shop;
    using PowerEasy.SqlServerDal;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Text;

    public class Statistics : IStatistics
    {
        private int m_TotalOfCategorySaleroom;
        private int m_TotalOfCompareHitsOrderNumber;
        private int m_TotalOfMemberExpenditure;
        private int m_TotalOfMemberOrderliness;
        private int m_TotalOfMemberOrders;
        private int m_TotalOfProductHits;
        private int m_TotalOfProductOrderNumber;

        public IList<CategorySaleroomInfo> GetCategorySaleroomList(int startRowIndexId, int maxNumberRows, int orderType, DateTime time, bool isAll)
        {
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Shop_Statistics_GetList");
            if (orderType == 1)
            {
                database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, "SUM(I.SubTotal)");
            }
            else
            {
                database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, "SUM(I.Amount)");
            }
            if (!isAll)
            {
                database.AddInParameter(storedProcCommand, "@Filter", DbType.String, "datediff(m,O.InputTime,'" + time + "')=0 and O.MoneyReceipt >= O.MoneyTotal");
            }
            else
            {
                database.AddInParameter(storedProcCommand, "@Filter", DbType.String, "O.MoneyReceipt >= O.MoneyTotal");
            }
            database.AddInParameter(storedProcCommand, "@StartRows", DbType.Int32, startRowIndexId);
            database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, maxNumberRows);
            database.AddInParameter(storedProcCommand, "@StrColumn", DbType.String, "SUM(I.Amount) AS SalesVolumn, MAX(N.NodeName) AS NodeName, SUM(I.SubTotal) AS Saleroom, N.NodeID");
            database.AddInParameter(storedProcCommand, "@Sorts", DbType.String, "DESC");
            database.AddInParameter(storedProcCommand, "@TableName", DbType.String, " PE_Orders AS O INNER JOIN PE_OrderItem AS I INNER JOIN PE_CommonModel AS C INNER JOIN PE_Nodes AS N ON C.NodeID = N.NodeID ON C.ItemID = I.ProductID AND C.TableName = I.TableName ON O.OrderID = I.OrderID");
            database.AddInParameter(storedProcCommand, "@ID", DbType.String, "N.NodeID");
            database.AddInParameter(storedProcCommand, "@Group", DbType.String, "GROUP BY N.NodeID");
            database.AddOutParameter(storedProcCommand, "@Total", DbType.Int32, maxNumberRows);
            IList<CategorySaleroomInfo> list = new List<CategorySaleroomInfo>();
            using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand)))
            {
                while (reader.Read())
                {
                    CategorySaleroomInfo item = new CategorySaleroomInfo();
                    item.NodeName = reader.GetString("NodeName");
                    item.SalesVolumn = reader.GetInt32("SalesVolumn");
                    item.Saleroom = reader.GetDecimal("Saleroom");
                    list.Add(item);
                }
            }
            this.m_TotalOfCategorySaleroom = (int) database.GetParameterValue(storedProcCommand, "@Total");
            return list;
        }

        public IList<CompareHitsOrderNumberInfo> GetCompareHitsOrderNumberList(int startRowIndexId, int maxNumberRows, int orderType)
        {
            string str;
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Shop_Statistics_GetList");
            switch (orderType)
            {
                case 1:
                    str = "Sum(M.Hits)";
                    break;

                case 2:
                    str = "Sum(P.OrderNum)";
                    break;

                default:
                    str = "(Sum(P.OrderNum)*1.0)/(Sum(M.Hits))";
                    break;
            }
            database.AddInParameter(storedProcCommand, "@StartRows", DbType.Int32, startRowIndexId);
            database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, maxNumberRows);
            database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, str);
            database.AddInParameter(storedProcCommand, "@StrColumn", DbType.String, "Max(P.ProductName) as ProductName,Sum(M.Hits) as Hits,Sum(P.OrderNum) as OrderNum,CONVERT(float,(Sum(P.OrderNum)*1.0)/(Sum(M.Hits))) as CompareRate");
            database.AddInParameter(storedProcCommand, "@Sorts", DbType.String, "DESC");
            database.AddInParameter(storedProcCommand, "@TableName", DbType.String, "PE_CommonProduct P inner join PE_CommonModel M on P.TableName = M.TableName and P.ProductID = M.ItemID");
            database.AddInParameter(storedProcCommand, "@Filter", DbType.String, "M.Hits>0");
            database.AddInParameter(storedProcCommand, "@ID", DbType.String, "P.ProductID");
            database.AddInParameter(storedProcCommand, "@Group", DbType.String, "Group by P.ProductID");
            database.AddOutParameter(storedProcCommand, "@Total", DbType.Int32, maxNumberRows);
            IList<CompareHitsOrderNumberInfo> list = new List<CompareHitsOrderNumberInfo>();
            using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand)))
            {
                while (reader.Read())
                {
                    CompareHitsOrderNumberInfo item = new CompareHitsOrderNumberInfo();
                    item.ProductName = reader.GetString("ProductName");
                    item.Hits = reader.GetInt32("Hits");
                    item.OrderNumber = reader.GetInt32("OrderNum");
                    item.CompareRate = reader.GetDouble("CompareRate");
                    list.Add(item);
                }
            }
            this.m_TotalOfCompareHitsOrderNumber = (int) database.GetParameterValue(storedProcCommand, "@Total");
            return list;
        }

        public int GetHaveOrderOfMember()
        {
            return DBHelper.ObjectToInt32(DBHelper.ExecuteScalarSql("SELECT COUNT(DISTINCT U.UserID)  FROM   PE_Users AS U INNER JOIN  PE_Orders AS O ON U.UserName = O.UserName and O.MoneyReceipt >= O.MoneyTotal"));
        }

        public IList<MemberExpenditureInfo> GetMemberExpenditureList(int startRowIndexId, int maxNumberRows)
        {
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Shop_Statistics_GetList");
            database.AddInParameter(storedProcCommand, "@StartRows", DbType.Int32, startRowIndexId);
            database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, maxNumberRows);
            database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, "SUM(O.MoneyReceipt)");
            database.AddInParameter(storedProcCommand, "@StrColumn", DbType.String, " Max(U.UserName) as UserName, SUM(O.MoneyReceipt) AS totalMoneyReceip");
            database.AddInParameter(storedProcCommand, "@Sorts", DbType.String, "DESC");
            database.AddInParameter(storedProcCommand, "@TableName", DbType.String, "PE_Users AS U INNER JOIN  PE_Orders AS O ON O.UserName = U.UserName");
            database.AddInParameter(storedProcCommand, "@Filter", DbType.String, "O.MoneyReceipt>0");
            database.AddInParameter(storedProcCommand, "@ID", DbType.String, "U.UserID");
            database.AddInParameter(storedProcCommand, "@Group", DbType.String, "GROUP BY U.UserID");
            database.AddOutParameter(storedProcCommand, "@Total", DbType.Int32, maxNumberRows);
            IList<MemberExpenditureInfo> list = new List<MemberExpenditureInfo>();
            using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand)))
            {
                while (reader.Read())
                {
                    MemberExpenditureInfo item = new MemberExpenditureInfo();
                    item.UserName = reader.GetString("UserName");
                    item.MoneyReceipt = reader.GetDecimal("totalMoneyReceip");
                    list.Add(item);
                }
            }
            this.m_TotalOfMemberExpenditure = (int) database.GetParameterValue(storedProcCommand, "@Total");
            return list;
        }

        public IList<MemberOrderlinessInfo> GetMemberOrderlinessList(int startRowIndexId, int maxNumberRows, string userName, DateTime time, bool isAll)
        {
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Shop_Statistics_GetList");
            StringBuilder builder = new StringBuilder();
            builder.Append("O.UserName ='" + userName + "' and O.MoneyReceipt >= O.MoneyTotal");
            if (!isAll)
            {
                builder.Append(" and datediff(m,O.InputTime,'" + time + "')=0");
            }
            database.AddInParameter(storedProcCommand, "@StartRows", DbType.Int32, startRowIndexId);
            database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, maxNumberRows);
            database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, "SUM(I.SubTotal)");
            database.AddInParameter(storedProcCommand, "@StrColumn", DbType.String, "MAX(P.ProductName) as ProductName,SUM(I.Amount) AS Amount, SUM(I.SubTotal) AS SubTotal");
            database.AddInParameter(storedProcCommand, "@Sorts", DbType.String, "DESC");
            database.AddInParameter(storedProcCommand, "@TableName", DbType.String, "PE_Orders AS O INNER JOIN  PE_OrderItem AS I INNER JOIN PE_CommonProduct AS P ON I.ProductID = P.ProductID AND I.TableName = P.TableName ON O.OrderID = I.OrderID");
            database.AddInParameter(storedProcCommand, "@Filter", DbType.String, builder.ToString());
            database.AddInParameter(storedProcCommand, "@ID", DbType.String, " P.ProductID");
            database.AddInParameter(storedProcCommand, "@Group", DbType.String, "GROUP BY  P.ProductID");
            database.AddOutParameter(storedProcCommand, "@Total", DbType.Int32, maxNumberRows);
            IList<MemberOrderlinessInfo> list = new List<MemberOrderlinessInfo>();
            using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand)))
            {
                while (reader.Read())
                {
                    MemberOrderlinessInfo item = new MemberOrderlinessInfo();
                    item.ProductName = reader.GetString("ProductName");
                    item.Amount = reader.GetInt32("Amount");