salelist.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;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;

    public class SaleList : ISaleList
    {
        private const string m_FromSql = " PE_CommonProduct P inner join (PE_OrderItem I inner join (PE_Orders O left join PE_Client C on O.ClientID=C.ClientID) on I.OrderID=O.OrderID) on P.ProductID=I.ProductID and P.TableName=I.TableName ";
        private const string m_Querysql = " (O.MoneyReceipt>=O.MoneyTotal or O.MoneyReceipt>0) ";
        private int totalOfSaleList;

        private static string ComplexSearch(string keyword, string filterString)
        {
            if (!string.IsNullOrEmpty(keyword))
            {
                string[] strArray = keyword.Split(new char[] { '|' });
                string str = strArray[0];
                string str2 = strArray[1];
                string str3 = strArray[2];
                string str4 = strArray[3];
                string str5 = strArray[4];
                string str6 = strArray[5];
                string str7 = strArray[6];
                string str8 = strArray[7];
                if ((!string.IsNullOrEmpty(str) && !string.IsNullOrEmpty(str2)) && ((str != "0") && (str2 != "0")))
                {
                    string str9 = filterString;
                    filterString = str9 + " and ( I.ItemID Between " + str + " and " + str2 + ") ";
                }
                if (!string.IsNullOrEmpty(str3) && !string.IsNullOrEmpty(str4))
                {
                    string str10 = filterString;
                    filterString = str10 + " and ( O.InputTime Between '" + str3 + "' and '" + str4 + "') ";
                }
                if (!string.IsNullOrEmpty(str5))
                {
                    filterString = filterString + " and O.OrderNum = '" + str5 + "' ";
                }
                if (!string.IsNullOrEmpty(str6))
                {
                    filterString = filterString + " and C.ShortedForm like '%" + str6 + "%' ";
                }
                if (!string.IsNullOrEmpty(str7))
                {
                    filterString = filterString + " and O.UserName like '%" + str7 + "%' ";
                }
                if (!string.IsNullOrEmpty(str8))
                {
                    filterString = filterString + " and P.ProductName like '%" + str8 + "%' ";
                }
            }
            return filterString;
        }

        private static SaleListInfo CreateSaleListInfo(NullableDataReader rdr)
        {
            SaleListInfo info = new SaleListInfo();
            info.SaleType = rdr.GetInt32("SaleType");
            info.ProductId = rdr.GetInt32("ProductID");
            info.OrderId = rdr.GetInt32("OrderID");
            info.ClientId = rdr.GetInt32("ClientID");
            info.ItemId = rdr.GetInt32("ItemID");
            info.ProductName = rdr.GetString("ProductName");
            info.UserName = rdr.GetString("UserName");
            info.ClientName = rdr.GetString("ClientName");
            info.Unit = rdr.GetString("Unit");
            info.PresentExp = rdr.GetInt32("PresentExp");
            info.Price = rdr.GetDecimal("Price");
            info.TruePrice = rdr.GetDecimal("TruePrice");
            info.Amount = rdr.GetInt32("Amount");
            info.OrderNum = rdr.GetString("OrderNum");
            info.InputTime = rdr.GetDateTime("InputTime");
            info.TableName = rdr.GetString("TableName");
            info.Property = rdr.GetString("Property");
            info.SubTotal = info.TruePrice * DataConverter.CDecimal(info.Amount);
            return info;
        }

        public IList<SaleListInfo> GetSaleList(int startRowIndexId, int maxNumberRows, int searchType, int field, string keyword)
        {
            IList<SaleListInfo> list = new List<SaleListInfo>();
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand storedProcCommand = database.GetStoredProcCommand("PR_Common_GetList");
            string filterString = " (O.MoneyReceipt>=O.MoneyTotal or O.MoneyReceipt>0) ";
            database.AddInParameter(storedProcCommand, "@StartRows", DbType.Int32, startRowIndexId);
            database.AddInParameter(storedProcCommand, "@PageSize", DbType.Int32, maxNumberRows);
            database.AddInParameter(storedProcCommand, "@SortColumn", DbType.String, " I.ItemID ");
            database.AddInParameter(storedProcCommand, "@StrColumn", DbType.String, " P.ProductID,P.TableName,P.ProductName,P.Unit,isnull(I.[Property],'') as [Property] ,I.ItemID,I.SaleType,I.PresentExp,I.Price,I.TruePrice,I.Amount,O.OrderID,O.OrderNum,O.InputTime,O.UserName,O.ClientID, isnull(C.ShortedForm,'') as ClientName ");
            database.AddInParameter(storedProcCommand, "@Sorts", DbType.String, " DESC ");
            database.AddInParameter(storedProcCommand, "@Filter", DbType.String, "");
            database.AddInParameter(storedProcCommand, "@TableName", DbType.String, " PE_CommonProduct P inner join (PE_OrderItem I inner join (PE_Orders O left join PE_Client C on O.ClientID=C.ClientID) on I.OrderID=O.OrderID) on P.ProductID=I.ProductID and P.TableName=I.TableName ");
            switch (searchType)
            {
                case 1:
                    filterString = filterString + " and datediff( dd , O.InputTime , getdate() ) < 1 ";
                    break;

                case 2:
                    filterString = filterString + " and datediff( ww , O.InputTime , getdate() ) < 1 ";
                    break;

                case 3:
                    filterString = filterString + " and datediff( m , O.InputTime , getdate() ) < 1 ";
                    break;

                case 4:
                    if (!string.IsNullOrEmpty(keyword))
                    {
                        string[] strArray = keyword.Split(new char[] { '|' });
                        string str2 = filterString;
                        filterString = str2 + " and I.ProductID=" + strArray[0] + "and I.TableName='" + strArray[1] + "'";
                    }
                    break;

                case 10:
                    filterString = HighLevelSearch(field, keyword, filterString);
                    break;

                case 11:
                    filterString = ComplexSearch(keyword, filterString);
                    break;
            }
            database.SetParameterValue(storedProcCommand, "@Filter", filterString);
            database.AddOutParameter(storedProcCommand, "@Total", DbType.Int32, maxNumberRows);
            using (NullableDataReader reader = new NullableDataReader(database.ExecuteReader(storedProcCommand)))
            {
                while (reader.Read())
                {
                    list.Add(CreateSaleListInfo(reader));
                }
            }
            this.totalOfSaleList = (int) database.GetParameterValue(storedProcCommand, "@Total");
            return list;
        }

        public ArrayList GetSumSubTotalAndExp()
        {
            string strSql = "select sum(I.SubTotal) as SubTotal,sum(I.PresentExp) as Exp from  PE_CommonProduct P inner join (PE_OrderItem I inner join (PE_Orders O left join PE_Client C on O.ClientID=C.ClientID) on I.OrderID=O.OrderID) on P.ProductID=I.ProductID and P.TableName=I.TableName  where  (O.MoneyReceipt>=O.MoneyTotal or O.MoneyReceipt>0) ";
            ArrayList list = new ArrayList();
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(strSql))
            {
                while (reader.Read())
                {
                    list.Add(reader["SubTotal"]);
                    list.Add(reader["Exp"]);
                }
            }
            return list;
        }

        public int GetTotalOfSaleList()
        {
            return this.totalOfSaleList;
        }

        private static string HighLevelSearch(int field, string keyword, string filterString)
        {
            if (!string.IsNullOrEmpty(keyword))
            {
                switch (field)
                {
                    case 0:
                        filterString = filterString + " and C.ShortedForm like '%" + keyword + "%' ";
                        return filterString;

                    case 1:
                        filterString = filterString + " and O.UserName like '%" + keyword + "%' ";
                        return filterString;

                    case 2:
                        filterString = filterString + " and P.ProductName like '%" + keyword + "%' ";
                        return filterString;

                    case 3:
                        filterString = filterString + " and datediff( dd ,O.InputTime,'" + keyword + "')=0 ";
                        return filterString;
                }
            }
            return filterString;
        }
    }
}