cards.cs

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

namespace PowerEasy.SqlServerDal.Accessories
{
    using PowerEasy.IDal.Accessories;
    using PowerEasy.Model.Accessories;
    using PowerEasy.SqlServerDal;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Text;

    public class Cards : ICards
    {
        private static Parameters AddCommonParameters(CardInfo info)
        {
            Parameters parameters = new Parameters();
            parameters.AddInParameter("@CardType", DbType.Int32, info.CardType);
            parameters.AddInParameter("@ProductID", DbType.Int32, info.ProductId);
            parameters.AddInParameter("@TableName", DbType.String, info.TableName);
            parameters.AddInParameter("@CardNum", DbType.String, info.CardNum);
            parameters.AddInParameter("@Password", DbType.String, info.Password);
            parameters.AddInParameter("@AgentName", DbType.String, info.AgentName);
            parameters.AddInParameter("@Money", DbType.Decimal, info.Money);
            parameters.AddInParameter("@ValidNum", DbType.Int32, info.ValidNum);
            parameters.AddInParameter("@ValidUnit", DbType.Int32, info.ValidUnit);
            parameters.AddInParameter("@EndDate", DbType.DateTime, info.EndDate);
            parameters.AddInParameter("@UserName", DbType.String, info.UserName);
            parameters.AddInParameter("@CreateTime", DbType.DateTime, info.CreateTime);
            parameters.AddInParameter("@OrderItemID", DbType.Int32, info.OrderItemId);
            return parameters;
        }

        public bool CardAdd(CardInfo info)
        {
            Parameters cmdParams = AddCommonParameters(info);
            cmdParams.AddInParameter("@ProductName", DbType.String, info.ProductName);
            return DBHelper.ExecuteProc("PR_UserManage_Cards_Add", cmdParams);
        }

        private static CardInfo CardFromDataReader(NullableDataReader rdr)
        {
            CardInfo info = new CardInfo();
            info.AgentName = rdr.GetString("AgentName");
            info.CardId = rdr.GetInt32("CardId");
            info.CardNum = rdr.GetString("CardNum");
            info.CardType = rdr.GetInt32("CardType");
            info.CreateTime = rdr.GetDateTime("CreateTime");
            info.EndDate = rdr.GetDateTime("EndDate");
            info.Money = rdr.GetDecimal("Money");
            info.OrderItemId = rdr.GetInt32("OrderItemId");
            info.Password = rdr.GetString("Password");
            info.ProductId = rdr.GetInt32("ProductId");
            info.TableName = rdr.GetString("TableName");
            info.ProductName = rdr.GetString("ProductName");
            info.UserName = rdr.GetString("UserName");
            info.UseTime = rdr.GetNullableDateTime("UseTime");
            info.ValidNum = rdr.GetInt32("ValidNum");
            info.ValidUnit = rdr.GetInt32("ValidUnit");
            return info;
        }

        public bool DelCard(int cardId)
        {
            string strSql = "Delete From PE_Cards Where CardID = @CardId and UserName='' And OrderItemID = 0";
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CardId", DbType.Int32, cardId);
            return DBHelper.ExecuteSql(strSql, cmdParams);
        }

        public CardInfo GetCardById(int cardId)
        {
            CardInfo info = null;
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CardId", DbType.Int32, cardId);
            string strSql = "select top 1 * from pe_cards where cardid=@CardId";
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(strSql, cmdParams))
            {
                if (reader.Read())
                {
                    info = CardFromDataReader(reader);
                }
            }
            return info;
        }

        public CardInfo GetCardByNumAndPassword(string cardNum, string password)
        {
            string strSql = "Select top 1 * From PE_Cards Where UserName='' and CardNum=@CardNum and Password=@Password";
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@CardNum", DbType.String, cardNum);
            cmdParams.AddInParameter("@Password", DbType.String, password);
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(strSql, cmdParams))
            {
                if (reader.Read())
                {
                    return CardFromDataReader(reader);
                }
                return new CardInfo(true);
            }
        }

        public CardInfo GetCardByOrderItemId(int productId, string tableName, int orderItemId)
        {
            string strSql = "select top 1 * from PE_Cards Where ProductId=@ProductId and TableName=@TableName and OrderItemId=@OrderItemId";
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductId", DbType.Int32, productId);
            cmdParams.AddInParameter("@TableName", DbType.String, tableName);
            cmdParams.AddInParameter("@OrderItemId", DbType.Int32, orderItemId);
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(strSql, cmdParams))
            {
                if (reader.Read())
                {
                    return CardFromDataReader(reader);
                }
                return new CardInfo(true);
            }
        }

        public IList<CardInfo> GetCardList(string tableName, int productId, int orderItemId)
        {
            string strSql = "select * from PE_Cards where OrderItemID=@OrderItemID and productID=@ProductID and tableName=@TableName";
            IList<CardInfo> list = new List<CardInfo>();
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductID", DbType.Int32, productId);
            cmdParams.AddInParameter("@OrderItemID", DbType.Int32, orderItemId);
            cmdParams.AddInParameter("@TableName", DbType.String, tableName);
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(strSql, cmdParams))
            {
                while (reader.Read())
                {
                    list.Add(CardFromDataReader(reader));
                }
            }
            return list;
        }

        public IList<CardInfo> GetCardList(int startRowIndexId, int maxiNumRows, int cardType, int cardStatus, int field, string keyword, string agentName)
        {
            IList<CardInfo> list = new List<CardInfo>();
            CommonListParameters cmdParams = new CommonListParameters(startRowIndexId, maxiNumRows);
            cmdParams.TableName = "PE_Cards";
            cmdParams.StrColumn = "*";
            cmdParams.SortColumn = "CardID";
            cmdParams.Sorts = Sorts.Desc;
            cmdParams.Filter = GetField(cardType, cardStatus, field, keyword, agentName);
            cmdParams.CreateParameter();
            using (NullableDataReader reader = DBHelper.ExecuteReaderProc("PR_Common_GetList", cmdParams))
            {
                while (reader.Read())
                {
                    CardInfo item = CardFromDataReader(reader);
                    list.Add(item);
                }
            }
            return list;
        }

        private static string GetField(int cardType, int cardStatus, int field, string keyword, string agentName)
        {
            StringBuilder builder = new StringBuilder("1=1 and ProductID not in (Select Distinct ItemID from PE_CommonModel where Status=-3) ");
            switch (cardType)
            {
                case 0:
                    builder.Append(" and CardType=0 ");
                    break;

                case 1:
                    builder.Append(" and CardType=1 ");
                    break;
            }
            switch (cardStatus)
            {
                case 1:
                    builder.Append(" and UserName='' and datediff(day,getdate(),Enddate)>=0 ");
                    break;

                case 2:
                    builder.Append(" and UserName<>'' ");
                    break;

                case 3:
                    builder.Append(" and UserName='' and datediff(dd,getdate(),Enddate)<0 ");
                    break;
            }
            switch (field)
            {
                case 1:
                    builder.Append(" and CardNum like '%" + keyword + "%'");
                    break;

                case 2:
                    builder.Append(" and Money=" + keyword + " ");
                    break;

                case 3:
                    builder.Append(" and AgentName='" + keyword + "'");
                    break;

                case 4:
                    builder.Append(" and UserName='" + keyword + "'");
                    break;
            }
            if (!string.IsNullOrEmpty(agentName))
            {
                builder.Append(" and AgentName='" + agentName + "'");
            }
            return builder.ToString();
        }

        public int GetTotalofCards(int cardType, int cardStatus, int field, string keyword, string agentName)
        {
            object obj2 = DBHelper.ExecuteScalarSql("select count(*) from pe_cards  where " + GetField(cardType, cardStatus, field, keyword, agentName));
            if (obj2 != null)
            {
                return Convert.ToInt32(obj2);
            }
            return 0;
        }

        public IList<CardInfo> GetUnsoldCard(string tableName, int productId, int amount)
        {
            string strSql = "select top " + amount.ToString() + " * from PE_Cards where OrderItemID=0 and productID=@ProductID and tableName=@TableName";
            IList<CardInfo> list = new List<CardInfo>();
            Parameters cmdParams = new Parameters();
            cmdParams.AddInParameter("@ProductID", DbType.Int32, productId);
            cmdParams.AddInParameter("@TableName", DbType.String, tableName);
            using (NullableDataReader reader = DBHelper.ExecuteReaderSql(strSql, cmdParams))
            {
                while (reader.Read())
                {
                    list.Add(CardFromDataReader(reader));
                }
            }
            return list;
        }

        public bool Update(CardInfo info)
        {
            Parameters cmdParams = AddCommonParameters(info);
            cmdParams.AddInParameter("@CardID", DbType.Int32, info.CardId);
            cmdParams.AddInParameter("@UseTime", DbType.DateTime, info.UseTime);
            return DBHelper.ExecuteProc("PR_UserManage_Cards_Update", cmdParams);
        }
    }
}