using System;
using System.Data;
using System.Text;
using System.Data.Common;

using BrnShop.Core;

namespace BrnShop.RDBSStrategy.SqlServer
{
    /// <summary>
    /// SqlServer策略之订单分部类
    /// </summary>
    public partial class RDBSStrategy : IRDBSStrategy
    {
        #region 用户配送地址

        /// <summary>
        /// 创建用户配送地址
        /// </summary>
        public void CreateShipAddress(ShipAddressInfo shipAddressInfo)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@uid", SqlDbType.Int, 4, shipAddressInfo.Uid),
                                        GenerateInParam("@regionid", SqlDbType.SmallInt, 2, shipAddressInfo.RegionId),
                                        GenerateInParam("@isdefault", SqlDbType.TinyInt, 1, shipAddressInfo.IsDefault),
                                        GenerateInParam("@alias", SqlDbType.NVarChar, 50, shipAddressInfo.Alias),
                                        GenerateInParam("@consignee", SqlDbType.NVarChar, 20, shipAddressInfo.Consignee),
                                        GenerateInParam("@mobile", SqlDbType.VarChar, 15, shipAddressInfo.Mobile),
                                        GenerateInParam("@phone", SqlDbType.VarChar, 12, shipAddressInfo.Phone),
                                        GenerateInParam("@email", SqlDbType.VarChar, 50, shipAddressInfo.Email),
                                        GenerateInParam("@zipcode", SqlDbType.Char, 6, shipAddressInfo.ZipCode),
                                        GenerateInParam("@address", SqlDbType.NVarChar, 150, shipAddressInfo.Address)
                                    };
            string commandText = string.Format("INSERT INTO [{0}shipaddresses]([uid],[regionid],[isdefault],[alias],[consignee],[mobile],[phone],[email],[zipcode],[address]) VALUES(@uid,@regionid,@isdefault,@alias,@consignee,@mobile,@phone,@email,@zipcode,@address)",
                                                RDBSHelper.RDBSTablePre);
            RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 更新用户配送地址
        /// </summary>
        public void UpdateShipAddress(ShipAddressInfo shipAddressInfo)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@uid", SqlDbType.Int, 4, shipAddressInfo.Uid),
                                        GenerateInParam("@regionid", SqlDbType.SmallInt, 2, shipAddressInfo.RegionId),
                                        GenerateInParam("@isdefault", SqlDbType.TinyInt, 1, shipAddressInfo.IsDefault),
                                        GenerateInParam("@alias", SqlDbType.NVarChar, 50, shipAddressInfo.Alias),
                                        GenerateInParam("@consignee", SqlDbType.NVarChar, 20, shipAddressInfo.Consignee),
                                        GenerateInParam("@mobile", SqlDbType.VarChar, 15, shipAddressInfo.Mobile),
                                        GenerateInParam("@phone", SqlDbType.VarChar, 12, shipAddressInfo.Phone),
                                        GenerateInParam("@email", SqlDbType.VarChar, 50, shipAddressInfo.Email),
                                        GenerateInParam("@zipcode", SqlDbType.Char, 6, shipAddressInfo.ZipCode),
                                        GenerateInParam("@address", SqlDbType.NVarChar, 150, shipAddressInfo.Address),
                                        GenerateInParam("@said", SqlDbType.Int, 4, shipAddressInfo.SAId)
                                    };
            string commandText = string.Format("UPDATE [{0}shipaddresses] SET [uid]=@uid,[regionid]=@regionid,[isdefault]=@isdefault,[alias]=@alias,[consignee]=@consignee,[mobile]=@mobile,[phone]=@phone,[email]=@email,[zipcode]=@zipcode,[address]=@address WHERE [said]=@said AND [uid]=@uid",
                                                RDBSHelper.RDBSTablePre);
            RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 获得完整用户配送地址列表
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public IDataReader GetFullShipAddressList(int uid)
        {
            DbParameter[] parms = {
                                     GenerateInParam("@uid", SqlDbType.Int, 4, uid)    
                                   };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getfullshipaddresslist", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得用户配送地址数量
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public int GetShipAddressCount(int uid)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@uid", SqlDbType.Int, 4, uid)    
                                    };
            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.StoredProcedure,
                                                                   string.Format("{0}getshipaddresscount", RDBSHelper.RDBSTablePre),
                                                                   parms), -1);
        }

        /// <summary>
        /// 获得默认完整用户配送地址
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public IDataReader GetDefaultFullShipAddress(int uid)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@uid", SqlDbType.Int, 4, uid)    
                                    };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getdefaultfullshipaddress", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得完整用户配送地址
        /// </summary>
        /// <param name="saId">配送地址id</param>
        /// <returns></returns>
        public IDataReader GetFullShipAddressBySAId(int saId)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@said", SqlDbType.Int, 4, saId)   
                                    };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getfullshipaddressbysaid", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得用户配送地址
        /// </summary>
        /// <param name="saId">配送地址id</param>
        /// <returns></returns>
        public IDataReader GetShipAddressBySAId(int saId)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@said", SqlDbType.Int, 4, saId)
                                    };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getshipaddressbysaid", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 删除用户配送地址
        /// </summary>
        /// <param name="saId">配送地址id</param>
        /// <param name="uid">用户id</param>
        public bool DeleteShipAddress(int saId, int uid)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@said", SqlDbType.Int, 4, saId), 
                                        GenerateInParam("@uid", SqlDbType.Int, 4, uid) 
                                    };
            return RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                              string.Format("{0}deleteshipaddress", RDBSHelper.RDBSTablePre),
                                              parms) > 0;
        }

        /// <summary>
        /// 更新用户配送地址的默认状态
        /// </summary>
        /// <param name="saId">配送地址id</param>
        /// <param name="uid">用户id</param>
        /// <param name="isDefault">状态</param>
        /// <returns></returns>
        public bool UpdateShipAddressIsDefault(int saId, int uid, int isDefault)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@said", SqlDbType.Int, 4, saId), 
                                        GenerateInParam("@uid", SqlDbType.Int, 4, uid),
                                        GenerateInParam("@isdefault", SqlDbType.TinyInt, 1, isDefault) 
                                    };
            return RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                              string.Format("{0}updateshipaddressisdefault", RDBSHelper.RDBSTablePre),
                                              parms) > 0;
        }

        #endregion

        #region 订单商品

        /// <summary>
        /// 获得订单商品
        /// </summary>
        /// <param name="recordId">记录id</param>
        /// <returns></returns>
        public IDataReader GetOrderProductByRecordId(int recordId)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@recordid", SqlDbType.Int, 4, recordId)    
                                   };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getorderproductbyrecordid", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得购物车中商品数量
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public int GetShopCartProductCount(int uid)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@uid", SqlDbType.Int, 4, uid)    
                                   };
            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.StoredProcedure,
                                                                   string.Format("{0}getshopcartproductcountbyuid", RDBSHelper.RDBSTablePre),
                                                                   parms), -2);
        }

        /// <summary>
        /// 获得购物车中商品数量
        /// </summary>
        /// <param name="sid">用户sid</param>
        /// <returns></returns>
        public int GetShopCartProductCount(string sid)
        {
            DbParameter[] parms = {
                                     GenerateInParam("@sid", SqlDbType.Char, 16, sid)    
                                   };
            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.StoredProcedure,
                                                                   string.Format("{0}getshopcartproductcountbysid", RDBSHelper.RDBSTablePre),
                                                                   parms), -2);
        }

        /// <summary>
        /// 添加订单商品
        /// </summary>
        /// <param name="orderProductInfo">订单商品</param>
        public int AddOrderProduct(OrderProductInfo orderProductInfo)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@oid", SqlDbType.Int, 4, orderProductInfo.Oid),
                                        GenerateInParam("@uid", SqlDbType.Int, 4, orderProductInfo.Uid),
                                        GenerateInParam("@sid", SqlDbType.Char, 16, orderProductInfo.Sid),
                                        GenerateInParam("@pid", SqlDbType.Int, 4, orderProductInfo.Pid),
                                        GenerateInParam("@psn", SqlDbType.Char, 30, orderProductInfo.PSN),
                                        GenerateInParam("@cateid", SqlDbType.SmallInt, 2, orderProductInfo.CateId),
                                        GenerateInParam("@brandid", SqlDbType.Int, 4, orderProductInfo.BrandId),
                                        GenerateInParam("@name", SqlDbType.NVarChar, 200, orderProductInfo.Name),
                                        GenerateInParam("@showimg", SqlDbType.NVarChar, 100, orderProductInfo.ShowImg),
                                        GenerateInParam("@discountprice", SqlDbType.Decimal, 4, orderProductInfo.DiscountPrice),
                                        GenerateInParam("@costprice", SqlDbType.Decimal, 4, orderProductInfo.CostPrice),
                                        GenerateInParam("@shopprice", SqlDbType.Decimal, 4, orderProductInfo.ShopPrice),
                                        GenerateInParam("@marketprice", SqlDbType.Decimal, 4, orderProductInfo.MarketPrice),
                                        GenerateInParam("@weight", SqlDbType.Int, 4, orderProductInfo.Weight),
                                        GenerateInParam("@isreview", SqlDbType.TinyInt, 1, orderProductInfo.IsReview),
                                        GenerateInParam("@realcount", SqlDbType.Int, 4, orderProductInfo.RealCount),
                                        GenerateInParam("@buycount", SqlDbType.Int, 4, orderProductInfo.BuyCount),
                                        GenerateInParam("@sendcount", SqlDbType.Int, 4, orderProductInfo.SendCount),
                                        GenerateInParam("@type", SqlDbType.TinyInt, 1, orderProductInfo.Type),
                                        GenerateInParam("@paycredits", SqlDbType.Int, 4, orderProductInfo.PayCredits),
                                        GenerateInParam("@coupontypeid", SqlDbType.Int, 4, orderProductInfo.CouponTypeId),
                                        GenerateInParam("@extcode1", SqlDbType.Int, 4, orderProductInfo.ExtCode1),
                                        GenerateInParam("@extcode2", SqlDbType.Int, 4, orderProductInfo.ExtCode2),
                                        GenerateInParam("@extcode3", SqlDbType.Int, 4, orderProductInfo.ExtCode3),
                                        GenerateInParam("@extcode4", SqlDbType.Int, 4, orderProductInfo.ExtCode4),
                                        GenerateInParam("@extcode5", SqlDbType.Int, 4, orderProductInfo.ExtCode5),
                                        GenerateInParam("@extcode6", SqlDbType.Int, 4, orderProductInfo.ExtCode6),
                                        GenerateInParam("@extcode7", SqlDbType.Int, 4, orderProductInfo.ExtCode7),
                                        GenerateInParam("@extcode8", SqlDbType.Int, 4, orderProductInfo.ExtCode8),
                                        GenerateInParam("@extcode9", SqlDbType.Int, 4, orderProductInfo.ExtCode9),
                                        GenerateInParam("@extcode10", SqlDbType.Int, 4, orderProductInfo.ExtCode10),
                                        GenerateInParam("@extcode11", SqlDbType.Int, 4, orderProductInfo.ExtCode11),
                                        GenerateInParam("@extcode12", SqlDbType.Int, 4, orderProductInfo.ExtCode12),
                                        GenerateInParam("@extstr1", SqlDbType.NVarChar, 50, orderProductInfo.ExtStr1),
                                        GenerateInParam("@extstr2", SqlDbType.NVarChar, 50, orderProductInfo.ExtStr2),
                                        GenerateInParam("@extstr3", SqlDbType.NVarChar, 50, orderProductInfo.ExtStr3),
                                        GenerateInParam("@extstr4", SqlDbType.NVarChar, 50, orderProductInfo.ExtStr4),
                                        GenerateInParam("@ip", SqlDbType.Char, 15, orderProductInfo.IP),
                                        GenerateInParam("@addtime", SqlDbType.DateTime, 8, orderProductInfo.AddTime)
                                    };
            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.StoredProcedure,
                                                                   string.Format("{0}addorderproduct", RDBSHelper.RDBSTablePre),
                                                                   parms));
        }

        /// <summary>
        /// 更新订单商品
        /// </summary>
        /// <param name="orderProductInfo">订单商品</param>
        public void UpdateOrderProduct(OrderProductInfo orderProductInfo)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@oid", SqlDbType.Int, 4, orderProductInfo.Oid),
                                        GenerateInParam("@uid", SqlDbType.Int, 4, orderProductInfo.Uid),
                                        GenerateInParam("@sid", SqlDbType.Char, 16, orderProductInfo.Sid),
                                        GenerateInParam("@pid", SqlDbType.Int, 4, orderProductInfo.Pid),
                                        GenerateInParam("@psn", SqlDbType.Char, 30, orderProductInfo.PSN),
                                        GenerateInParam("@cateid", SqlDbType.SmallInt, 2, orderProductInfo.CateId),
                                        GenerateInParam("@brandid", SqlDbType.Int, 4, orderProductInfo.BrandId),
                                        GenerateInParam("@name", SqlDbType.NVarChar, 200, orderProductInfo.Name),
                                        GenerateInParam("@showimg", SqlDbType.NVarChar, 100, orderProductInfo.ShowImg),
                                        GenerateInParam("@discountprice", SqlDbType.Decimal, 4, orderProductInfo.DiscountPrice),
                                        GenerateInParam("@costprice", SqlDbType.Decimal, 4, orderProductInfo.CostPrice),
                                        GenerateInParam("@shopprice", SqlDbType.Decimal, 4, orderProductInfo.ShopPrice),
                                        GenerateInParam("@marketprice", SqlDbType.Decimal, 4, orderProductInfo.MarketPrice),
                                        GenerateInParam("@weight", SqlDbType.Int, 4, orderProductInfo.Weight),
                                        GenerateInParam("@isreview", SqlDbType.TinyInt, 1, orderProductInfo.IsReview),
                                        GenerateInParam("@realcount", SqlDbType.Int, 4, orderProductInfo.RealCount),
                                        GenerateInParam("@buycount", SqlDbType.Int, 4, orderProductInfo.BuyCount),
                                        GenerateInParam("@sendcount", SqlDbType.Int, 4, orderProductInfo.SendCount),
                                        GenerateInParam("@type", SqlDbType.TinyInt, 1, orderProductInfo.Type),
                                        GenerateInParam("@paycredits", SqlDbType.Int, 4, orderProductInfo.PayCredits),
                                        GenerateInParam("@coupontypeid", SqlDbType.Int, 4, orderProductInfo.CouponTypeId),
                                        GenerateInParam("@extcode1", SqlDbType.Int, 4, orderProductInfo.ExtCode1),
                                        GenerateInParam("@extcode2", SqlDbType.Int, 4, orderProductInfo.ExtCode2),
                                        GenerateInParam("@extcode3", SqlDbType.Int, 4, orderProductInfo.ExtCode3),
                                        GenerateInParam("@extcode4", SqlDbType.Int, 4, orderProductInfo.ExtCode4),
                                        GenerateInParam("@extcode5", SqlDbType.Int, 4, orderProductInfo.ExtCode5),
                                        GenerateInParam("@extcode6", SqlDbType.Int, 4, orderProductInfo.ExtCode6),
                                        GenerateInParam("@extcode7", SqlDbType.Int, 4, orderProductInfo.ExtCode7),
                                        GenerateInParam("@extcode8", SqlDbType.Int, 4, orderProductInfo.ExtCode8),
                                        GenerateInParam("@extcode9", SqlDbType.Int, 4, orderProductInfo.ExtCode9),
                                        GenerateInParam("@extcode10", SqlDbType.Int, 4, orderProductInfo.ExtCode10),
                                        GenerateInParam("@extcode11", SqlDbType.Int, 4, orderProductInfo.ExtCode11),
                                        GenerateInParam("@extcode12", SqlDbType.Int, 4, orderProductInfo.ExtCode12),
                                        GenerateInParam("@extstr1", SqlDbType.NVarChar, 50, orderProductInfo.ExtStr1),
                                        GenerateInParam("@extstr2", SqlDbType.NVarChar, 50, orderProductInfo.ExtStr2),
                                        GenerateInParam("@extstr3", SqlDbType.NVarChar, 50, orderProductInfo.ExtStr3),
                                        GenerateInParam("@extstr4", SqlDbType.NVarChar, 50, orderProductInfo.ExtStr4),
                                        GenerateInParam("@ip", SqlDbType.Char, 15, orderProductInfo.IP),
                                        GenerateInParam("@addtime", SqlDbType.DateTime, 8, orderProductInfo.AddTime),
                                        GenerateInParam("@recordid", SqlDbType.Int, 4, orderProductInfo.RecordId)
                                    };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updateorderproduct", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 获得购物车商品列表
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public IDataReader GetShopCartProductList(int uid)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@uid", SqlDbType.Int, 4, uid)    
                                    };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getshopcartproductlistbyuid", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得购物车商品列表
        /// </summary>
        /// <param name="sid">用户sid</param>
        /// <returns></returns>
        public IDataReader GetShopCartProductList(string sid)
        {
            DbParameter[] parms = {
                                        GenerateInParam("@sid", SqlDbType.Char, 16, sid)    
                                    };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getshopcartproductlistbysid", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 更新购物车的用户id
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <param name="sid">用户sid</param>
        public void UpdateShopCartUidBySid(int uid, string sid)
        {
            DbParameter[] parms = {
                                      GenerateInParam("@uid", SqlDbType.Int, 4, uid),
                                      GenerateInParam("@sid", SqlDbType.Char, 16, sid)    
                                    };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updateshopcartuidbysid", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 删除订单商品
        /// </summary>
        /// <param name="recordIdList">记录id列表</param>
        public void DeleteOrderProductByRecordId(string recordIdList)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@recordidlist", SqlDbType.NVarChar, 1000, recordIdList)
                                   };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}deleteorderproductbyrecordid", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 清空购物车的商品
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <returns></returns>
        public int ClearShopCart(int uid)
        {
            DbParameter[] parms = {
                                     GenerateInParam("@uid", SqlDbType.Int, 4, uid)
                                    };
            return RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                              string.Format("{0}clearshopcartbyuid", RDBSHelper.RDBSTablePre),
                                              parms);
        }

        /// <summary>
        /// 清空购物车的商品
        /// </summary>
        /// <param name="sid">sid</param>
        /// <returns></returns>
        public int ClearShopCart(string sid)
        {
            DbParameter[] parms = {
                                     GenerateInParam("@sid", SqlDbType.Char, 16, sid)
                                    };
            return RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                              string.Format("{0}clearshopcartbysid", RDBSHelper.RDBSTablePre),
                                              parms);
        }

        /// <summary>
        /// 更新订单商品的数量
        /// </summary>
        /// <param name="recordId">记录id</param>
        /// <param name="realCount">真实数量</param>
        /// <param name="buyCount">购买数量</param>
        public void UpdateOrderProductCount(int recordId, int realCount, int buyCount)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@recordid", SqlDbType.Int, 4, recordId),
                                    GenerateInParam("@realcount", SqlDbType.Int, 4, realCount),
                                    GenerateInParam("@buycount", SqlDbType.Int, 4, buyCount)
                                    };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updateorderproductcount", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 更新订单商品的满减促销活动
        /// </summary>
        /// <param name="recordIdList">记录id列表</param>
        /// <param name="limitMoney">限制金额</param>
        /// <param name="cutMoney">优惠金额</param>
        public void UpdateFullCutPromotionOfOrderProduct(string recordIdList, int limitMoney, int cutMoney)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@recordidlist", SqlDbType.NVarChar, 1000, recordIdList),
                                    GenerateInParam("@limitmoney", SqlDbType.Int, 4, limitMoney),
                                    GenerateInParam("@cutmoney", SqlDbType.Int, 4, cutMoney)
                                    };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updatefullcutpromotionoforderproduct", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 清空过期购物车
        /// </summary>
        /// <param name="expireTime">过期时间</param>
        public void ClearExpiredShopCart(DateTime expireTime)
        {
            DbParameter[] parms = {
	                                 GenerateInParam("@expiretime", SqlDbType.DateTime,8,expireTime)
                                   };
            string commandText = string.Format("DELETE FROM [{0}orderproducts] WHERE [oid]=0 AND [addtime]<@expiretime",
                                                RDBSHelper.RDBSTablePre);
            RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }

        #endregion

        #region 订单

        /// <summary>
        /// 创建订单
        /// </summary>
        /// <param name="orderInfo">订单信息</param>
        /// <returns>订单id</returns>
        public int CreateOrder(OrderInfo orderInfo)
        {
            DbParameter[] parms = {
	                                    GenerateInParam("@osn", SqlDbType.Char,30,orderInfo.OSN),
	                                    GenerateInParam("@uid", SqlDbType.Int,4 ,orderInfo.Uid),
	                                    GenerateInParam("@orderstate", SqlDbType.TinyInt,1 ,orderInfo.OrderState),
                                        GenerateInParam("@productamount", SqlDbType.Decimal,8 ,orderInfo.ProductAmount),
                                        GenerateInParam("@fullcut", SqlDbType.Int,4 ,orderInfo.FullCut),
                                        GenerateInParam("@orderamount", SqlDbType.Decimal,8 ,orderInfo.OrderAmount),
                                        GenerateInParam("@surplusmoney", SqlDbType.Decimal,8 ,orderInfo.SurplusMoney),
                                        GenerateInParam("@parentid", SqlDbType.Int,4 ,orderInfo.ParentId),
                                        GenerateInParam("@addtime", SqlDbType.DateTime, 8,orderInfo.AddTime),
                                        GenerateInParam("@shipsn", SqlDbType.Char,30 ,orderInfo.ShipSN),
                                        GenerateInParam("@shipsystemname", SqlDbType.VarChar,20 ,orderInfo.ShipSystemName),
                                        GenerateInParam("@shipfriendname", SqlDbType.NVarChar,30 ,orderInfo.ShipFriendName),
                                        GenerateInParam("@paysn", SqlDbType.Char,30 ,orderInfo.PaySN),
                                        GenerateInParam("@paysystemname", SqlDbType.VarChar,20 ,orderInfo.PaySystemName),
                                        GenerateInParam("@payfriendname", SqlDbType.NVarChar,30 ,orderInfo.PayFriendName),
	                                    GenerateInParam("@paymode", SqlDbType.TinyInt,1 ,orderInfo.PayMode),
                                        GenerateInParam("@regionid", SqlDbType.SmallInt,2 ,orderInfo.RegionId),
                                        GenerateInParam("@consignee", SqlDbType.NVarChar,30 ,orderInfo.Consignee),
                                        GenerateInParam("@mobile", SqlDbType.VarChar,15 ,orderInfo.Mobile),
                                        GenerateInParam("@phone", SqlDbType.VarChar,12 ,orderInfo.Phone),
                                        GenerateInParam("@email", SqlDbType.VarChar,50 ,orderInfo.Email),
                                        GenerateInParam("@zipcode", SqlDbType.Char,6 ,orderInfo.ZipCode),
	                                    GenerateInParam("@address", SqlDbType.NVarChar,150 ,orderInfo.Address),
                                        GenerateInParam("@besttime", SqlDbType.DateTime,8 ,orderInfo.BestTime),
	                                    GenerateInParam("@shipfee", SqlDbType.Decimal,8 ,orderInfo.ShipFee),
                                        GenerateInParam("@payfee", SqlDbType.Decimal,8 ,orderInfo.PayFee),
	                                    GenerateInParam("@discount", SqlDbType.Decimal,8 ,orderInfo.Discount),
                                        GenerateInParam("@paidmoney", SqlDbType.Decimal,8 ,orderInfo.PaidMoney),
                                        GenerateInParam("@walletmoney", SqlDbType.Decimal,8 ,orderInfo.WalletMoney),
	                                    GenerateInParam("@paycreditcount", SqlDbType.Int,4 ,orderInfo.PayCreditCount),
	                                    GenerateInParam("@paycreditmoney", SqlDbType.Decimal,8 ,orderInfo.PayCreditMoney),
                                        GenerateInParam("@couponmoney", SqlDbType.Int,4 ,orderInfo.CouponMoney),
	                                    GenerateInParam("@weight", SqlDbType.Int,4 ,orderInfo.Weight),
                                        GenerateInParam("@buyerremark", SqlDbType.NVarChar,250 ,orderInfo.BuyerRemark),
                                        GenerateInParam("@ip", SqlDbType.VarChar,15 ,orderInfo.IP)
                                    };
            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.StoredProcedure,
                                                                   string.Format("{0}createorder", RDBSHelper.RDBSTablePre),
                                                                   parms), -1);
        }

        /// <summary>
        /// 获得订单信息
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <returns>订单信息</returns>
        public IDataReader GetOrderByOid(int oid)
        {
            DbParameter[] parms = {
	                                 GenerateInParam("@oid", SqlDbType.Int,4,oid)
                                    };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getorderbyoid", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得订单信息
        /// </summary>
        /// <param name="osn">订单编号</param>
        /// <returns>订单信息</returns>
        public IDataReader GetOrderByOSN(string osn)
        {
            DbParameter[] parms = {
	                                 GenerateInParam("@osn", SqlDbType.Char,30,osn)
                                    };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getorderbyosn", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得订单状态
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <returns></returns>
        public int GetOrderStateByOid(int oid)
        {
            DbParameter[] parms = {
	                                 GenerateInParam("@oid", SqlDbType.Int,4,oid)
                                    };
            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.StoredProcedure,
                                                                   string.Format("{0}getorderstatebyoid", RDBSHelper.RDBSTablePre),
                                                                   parms));
        }

        /// <summary>
        /// 根据下单时间获得订单数量
        /// </summary>
        /// <param name="orderState">订单状态</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public int GetOrderCountByOrderStateAndAddTime(int orderState, string startTime, string endTime)
        {
            StringBuilder condition = new StringBuilder();

            if (orderState > 0)
                condition.AppendFormat(" AND [orderstate] = {0}", orderState);
            if (!string.IsNullOrEmpty(startTime))
                condition.AppendFormat(" AND [addtime] >= '{0}'", TypeHelper.StringToDateTime(startTime).ToString("yyyy-MM-dd HH:mm:ss"));
            if (!string.IsNullOrEmpty(endTime))
                condition.AppendFormat(" AND [addtime] <= '{0}'", TypeHelper.StringToDateTime(endTime).ToString("yyyy-MM-dd HH:mm:ss"));

            string commandText;
            if (condition.Length > 0)
            {
                commandText = string.Format("SELECT COUNT([oid]) FROM [{0}orders] WHERE {1}",
                                             RDBSHelper.RDBSTablePre,
                                             condition.Remove(0, 4).ToString());
            }
            else
            {
                commandText = string.Format("SELECT COUNT([oid]) FROM [{0}orders]",
                                            RDBSHelper.RDBSTablePre);
            }

            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText));
        }

        /// <summary>
        /// 获得订单列表
        /// </summary>
        /// <param name="pageSize">每页数</param>
        /// <param name="pageNumber">当前页数</param>
        /// <param name="condition">条件</param>
        /// <param name="sort">排序</param>
        /// <returns></returns>
        public DataTable GetOrderList(int pageSize, int pageNumber, string condition, string sort)
        {
            bool noCondition = string.IsNullOrWhiteSpace(condition);
            string commandText;
            if (pageNumber == 1)
            {
                if (noCondition)
                    commandText = string.Format("SELECT [temp1].[oid],[temp1].[osn],[temp1].[uid],[temp1].[orderstate],[temp1].[orderamount],[temp1].[surplusmoney],[temp1].[parentid],[temp1].[addtime],[temp1].[regionid],[temp1].[consignee],[temp1].[mobile],[temp1].[phone],[temp1].[email],[temp1].[zipcode],[temp1].[address],[temp1].[besttime],[temp2].[username],[temp3].[provincename],[temp3].[cityname],[temp3].[name] AS [countyname] FROM (SELECT TOP {0} [oid],[osn],[uid],[orderstate],[orderamount],[surplusmoney],[parentid],[addtime],[regionid],[consignee],[mobile],[phone],[email],[zipcode],[address],[besttime] FROM [{1}orders] ORDER BY {2}) AS [temp1] LEFT JOIN [{1}users] AS [temp2] ON [temp1].[uid]=[temp2].[uid] LEFT JOIN [{1}regions] AS [temp3] ON [temp1].[regionid]=[temp3].[regionid]",
                                                pageSize,
                                                RDBSHelper.RDBSTablePre,
                                                sort);

                else
                    commandText = string.Format("SELECT [temp1].[oid],[temp1].[osn],[temp1].[uid],[temp1].[orderstate],[temp1].[orderamount],[temp1].[surplusmoney],[temp1].[parentid],[temp1].[addtime],[temp1].[regionid],[temp1].[consignee],[temp1].[mobile],[temp1].[phone],[temp1].[email],[temp1].[zipcode],[temp1].[address],[temp1].[besttime],[temp2].[username],[temp3].[provincename],[temp3].[cityname],[temp3].[name] AS [countyname] FROM (SELECT TOP {0} [oid],[osn],[uid],[orderstate],[orderamount],[surplusmoney],[parentid],[addtime],[regionid],[consignee],[mobile],[phone],[email],[zipcode],[address],[besttime] FROM [{1}orders] WHERE {3} ORDER BY {2}) AS [temp1] LEFT JOIN [{1}users] AS [temp2] ON [temp1].[uid]=[temp2].[uid] LEFT JOIN [{1}regions] AS [temp3] ON [temp1].[regionid]=[temp3].[regionid]",
                                                pageSize,
                                                RDBSHelper.RDBSTablePre,
                                                sort,
                                                condition);
            }
            else
            {
                if (noCondition)
                    commandText = string.Format("SELECT [temp1].[oid],[temp1].[osn],[temp1].[uid],[temp1].[orderstate],[temp1].[orderamount],[temp1].[surplusmoney],[temp1].[parentid],[temp1].[addtime],[temp1].[regionid],[temp1].[consignee],[temp1].[mobile],[temp1].[phone],[temp1].[email],[temp1].[zipcode],[temp1].[address],[temp1].[besttime],[temp2].[username],[temp3].[provincename],[temp3].[cityname],[temp3].[name] AS [countyname] FROM (SELECT [oid],[osn],[uid],[orderstate],[orderamount],[surplusmoney],[parentid],[addtime],[regionid],[consignee],[mobile],[phone],[email],[zipcode],[address],[besttime] FROM (SELECT TOP {0} ROW_NUMBER() OVER (ORDER BY {2}) AS [rowid],[oid],[osn],[uid],[orderstate],[orderamount],[surplusmoney],[parentid],[addtime],[regionid],[consignee],[mobile],[phone],[email],[zipcode],[address],[besttime] FROM [{1}orders]) AS [temp] WHERE [temp].[rowid] BETWEEN {3} AND {4}) AS [temp1] LEFT JOIN [{1}users] AS [temp2] ON [temp1].[uid]=[temp2].[uid] LEFT JOIN [{1}regions] AS [temp3] ON [temp1].[regionid]=[temp3].[regionid]",
                                                pageSize,
                                                RDBSHelper.RDBSTablePre,
                                                sort,
                                                pageSize * (pageNumber - 1) + 1,
                                                pageSize * pageNumber);

                else
                    commandText = string.Format("SELECT [temp1].[oid],[temp1].[osn],[temp1].[uid],[temp1].[orderstate],[temp1].[orderamount],[temp1].[surplusmoney],[temp1].[parentid],[temp1].[addtime],[temp1].[regionid],[temp1].[consignee],[temp1].[mobile],[temp1].[phone],[temp1].[email],[temp1].[zipcode],[temp1].[address],[temp1].[besttime],[temp2].[username],[temp3].[provincename],[temp3].[cityname],[temp3].[name] AS [countyname] FROM (SELECT [oid],[osn],[uid],[orderstate],[orderamount],[surplusmoney],[parentid],[addtime],[regionid],[consignee],[mobile],[phone],[email],[zipcode],[address],[besttime] FROM (SELECT TOP {0} ROW_NUMBER() OVER (ORDER BY {2}) AS [rowid],[oid],[osn],[uid],[orderstate],[orderamount],[surplusmoney],[parentid],[addtime],[regionid],[consignee],[mobile],[phone],[email],[zipcode],[address],[besttime] FROM [{1}orders] WHERE {5}) AS [temp] WHERE [temp].[rowid] BETWEEN {3} AND {4}) AS [temp1] LEFT JOIN [{1}users] AS [temp2] ON [temp1].[uid]=[temp2].[uid] LEFT JOIN [{1}regions] AS [temp3] ON [temp1].[regionid]=[temp3].[regionid]",
                                                pageSize,
                                                RDBSHelper.RDBSTablePre,
                                                sort,
                                                pageSize * (pageNumber - 1) + 1,
                                                pageSize * pageNumber,
                                                condition);
            }

            return RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0];
        }

        /// <summary>
        /// 获得订单列表搜索条件
        /// </summary>
        /// <param name="osn">订单编号</param>
        /// <param name="uid">用户id</param>
        /// <param name="consignee">收货人</param>
        /// <param name="orderState">订单状态</param>
        /// <returns></returns>
        public string GetOrderListCondition(string osn, int uid, string consignee, int orderState)
        {
            StringBuilder condition = new StringBuilder();

            if (!string.IsNullOrWhiteSpace(osn))
                condition.AppendFormat(" AND [osn] like '{0}%' ", osn);
            if (uid > 0)
                condition.AppendFormat(" AND [uid] = {0} ", uid);
            if (!string.IsNullOrWhiteSpace(consignee))
                condition.AppendFormat(" AND [consignee] like '{0}%' ", consignee);
            if (orderState > 0)
                condition.AppendFormat(" AND [orderstate] = {0} ", orderState);

            return condition.Length > 0 ? condition.Remove(0, 4).ToString() : "";
        }

        /// <summary>
        /// 获得订单列表排序
        /// </summary>
        /// <param name="sortColumn">排序列</param>
        /// <param name="sortDirection">排序方向</param>
        /// <returns></returns>
        public string GetOrderListSort(string sortColumn, string sortDirection)
        {
            if (string.IsNullOrWhiteSpace(sortColumn))
                sortColumn = "[oid]";
            if (string.IsNullOrWhiteSpace(sortDirection))
                sortDirection = "DESC";

            return string.Format("{0} {1} ", sortColumn, sortDirection);
        }

        /// <summary>
        /// 获得订单数量
        /// </summary>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public int GetOrderCount(string condition)
        {
            string commandText;
            if (string.IsNullOrWhiteSpace(condition))
                commandText = string.Format("SELECT COUNT(oid) FROM [{0}orders]", RDBSHelper.RDBSTablePre);
            else
                commandText = string.Format("SELECT COUNT(oid) FROM [{0}orders] WHERE {1}", RDBSHelper.RDBSTablePre, condition);

            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText), 0);
        }

        /// <summary>
        /// 获得订单商品列表
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <returns></returns>
        public IDataReader GetOrderProductList(int oid)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oid", SqlDbType.Int, 4, oid)    
                                   };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getorderproductlistbyoid", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得订单商品列表
        /// </summary>
        /// <param name="oidList">订单id列表</param>
        /// <returns></returns>
        public IDataReader GetOrderProductList(string oidList)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oidlist", SqlDbType.NVarChar, 1000, oidList)    
                                   };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getorderproductlistbyoidlist", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 更新订单的配送地址
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <param name="regionId">区域id</param>
        /// <param name="consignee">收货人</param>
        /// <param name="mobile">手机号</param>
        /// <param name="phone">固话号</param>
        /// <param name="email">邮箱</param>
        /// <param name="zipCode">邮政编码</param>
        /// <param name="address">详细地址</param>
        /// <param name="bestTime">最佳送货时间</param>
        /// <param name="shipFee">配送费用</param>
        /// <param name="orderAmount">订单合计</param>
        /// <param name="surplusMoney">剩余金额</param>
        public void UpdateOrderShipAddress(int oid, int regionId, string consignee, string mobile, string phone, string email, string zipCode, string address, DateTime bestTime, decimal shipFee, decimal orderAmount, decimal surplusMoney)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oid", SqlDbType.Int, 4, oid), 
                                    GenerateInParam("@regionid", SqlDbType.SmallInt, 2, regionId), 
                                    GenerateInParam("@consignee", SqlDbType.NVarChar, 20, consignee), 
                                    GenerateInParam("@mobile", SqlDbType.VarChar, 15, mobile), 
                                    GenerateInParam("@phone", SqlDbType.VarChar, 12, phone), 
                                    GenerateInParam("@email", SqlDbType.VarChar, 50, email), 
                                    GenerateInParam("@zipcode", SqlDbType.Char, 6, zipCode), 
                                    GenerateInParam("@address", SqlDbType.NVarChar, 150, address), 
                                    GenerateInParam("@besttime", SqlDbType.DateTime, 8, bestTime),
                                    GenerateInParam("@shipfee", SqlDbType.Decimal, 8, shipFee), 
                                    GenerateInParam("@orderamount", SqlDbType.Decimal, 8, orderAmount),
                                    GenerateInParam("@surplusmoney", SqlDbType.Decimal, 8, surplusMoney)
                                   };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updateordershipaddress", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 更新订单的配送类型
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <param name="shipSystemName">配送系统名</param>
        /// <param name="shipFriendName">配送友好名</param>
        /// <param name="shipFee">配送费用</param>
        /// <param name="orderAmount">订单合计</param>
        /// <param name="surplusMoney">剩余金额</param>
        public void UpdateOrderShipType(int oid, string shipSystemName, string shipFriendName, decimal shipFee, decimal orderAmount, decimal surplusMoney)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oid", SqlDbType.Int, 4, oid), 
                                    GenerateInParam("@shipsystemname", SqlDbType.Char, 20, shipSystemName), 
                                    GenerateInParam("@shipfriendname", SqlDbType.NChar, 30, shipFriendName), 
                                    GenerateInParam("@shipfee", SqlDbType.Decimal, 8, shipFee), 
                                    GenerateInParam("@orderamount", SqlDbType.Decimal, 8, orderAmount),
                                    GenerateInParam("@surplusmoney", SqlDbType.Decimal, 8, surplusMoney)
                                   };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updateordershiptype", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 更新订单的支付类型
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <param name="paySystemName">支付系统名</param>
        /// <param name="payFriendName">支付友好名</param>
        /// <param name="payMode">支付方式</param>
        /// <param name="orderState">订单状态</param>
        /// <param name="payFee">手续费</param>
        /// <param name="orderAmount">订单合计</param>
        /// <param name="surplusMoney">剩余金额</param>
        public void UpdateOrderPayType(int oid, string paySystemName, string payFriendName, int payMode, int orderState, decimal payFee, decimal orderAmount, decimal surplusMoney)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oid", SqlDbType.Int, 4, oid), 
                                    GenerateInParam("@paysystemname", SqlDbType.Char, 20, paySystemName), 
                                    GenerateInParam("@payfriendname", SqlDbType.NChar, 30, payFriendName), 
                                    GenerateInParam("@orderstate", SqlDbType.TinyInt, 1, orderState), 
                                    GenerateInParam("@paymode", SqlDbType.TinyInt, 1, payMode), 
                                    GenerateInParam("@payfee", SqlDbType.Decimal, 8, payFee), 
                                    GenerateInParam("@orderamount", SqlDbType.Decimal, 8, orderAmount),
                                    GenerateInParam("@surplusmoney", SqlDbType.Decimal, 8, surplusMoney)
                                   };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updateorderpaytype", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 更新订单折扣
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <param name="discount">折扣</param>
        /// <param name="orderAmount">订单合计</param>
        /// <param name="surplusMoney">剩余金额</param>
        public void UpdateOrderDiscount(int oid, decimal discount, decimal orderAmount, decimal surplusMoney)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oid", SqlDbType.Int, 4, oid), 
                                    GenerateInParam("@discount", SqlDbType.Decimal, 8, discount), 
                                    GenerateInParam("@orderamount", SqlDbType.Decimal, 8, orderAmount),
                                    GenerateInParam("@surplusmoney", SqlDbType.Decimal, 8, surplusMoney)
                                   };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updateorderdiscount", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 更新订单状态
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <param name="orderState">订单状态</param>
        public void UpdateOrderState(int oid, int orderState)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oid", SqlDbType.Int, 4, oid), 
                                    GenerateInParam("@orderstate", SqlDbType.TinyInt, 1, orderState)
                                   };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}updateorderstate", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 发货
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <param name="orderState">订单状态</param>
        /// <param name="shipSN">配送单号</param>
        public void SendOrderProduct(int oid, int orderState, string shipSN)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oid", SqlDbType.Int, 4, oid), 
                                    GenerateInParam("@orderstate", SqlDbType.TinyInt, 1, orderState),
                                    GenerateInParam("@shipsn", SqlDbType.Char, 30, shipSN)
                                   };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}sendorderproduct", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 付款
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <param name="orderState">订单状态</param>
        /// <param name="paySN">支付单号</param>
        public void PayOrder(int oid, int orderState, string paySN)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@oid", SqlDbType.Int, 4, oid), 
                                    GenerateInParam("@orderstate", SqlDbType.TinyInt, 1, orderState),
                                    GenerateInParam("@paysn", SqlDbType.Char, 30, paySN)
                                   };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}payorder", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 清空过期的在线支付订单
        /// </summary>
        /// <param name="expireTime">过期时间</param>
        public void ClearExpiredOnlinePayOrder(DateTime expireTime)
        {
            DbParameter[] parms = {
	                                 GenerateInParam("@expiretime", SqlDbType.DateTime,8,expireTime)
                                   };
            string commandText = string.Format("DELETE FROM [{0}orderproducts] WHERE [oid] IN (SELECT [oid] FROM [{0}orders] WHERE [paymode]=1 AND [surplusmoney]>0 AND [addtime]<@expiretime);DELETE FROM [{0}orders] WHERE [paymode]=1 AND [surplusmoney]>0 AND [addtime]<@expiretime",
                                                RDBSHelper.RDBSTablePre);
            RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 清空过期的线下支付订单
        /// </summary>
        /// <param name="expireTime">过期时间</param>
        public void ClearExpiredOfflinePayOrder(DateTime expireTime)
        {
            DbParameter[] parms = {
	                                 GenerateInParam("@expiretime", SqlDbType.DateTime,8,expireTime)
                                   };
            string commandText = string.Format("DELETE FROM [{0}orderproducts] WHERE [oid] IN (SELECT [oid] FROM [{0}orders] WHERE [paymode]=2 AND [surplusmoney]>0 AND [addtime]<@expiretime);DELETE FROM [{0}orders] WHERE [paymode]=2 AND [surplusmoney]>0 AND [addtime]<@expiretime",
                                                RDBSHelper.RDBSTablePre);
            RDBSHelper.ExecuteNonQuery(CommandType.Text, commandText, parms);
        }

        /// <summary>
        /// 获得用户订单列表
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <param name="pageSize">每页数</param>
        /// <param name="pageNumber">当前页数</param>
        /// <param name="startAddTime">添加开始时间</param>
        /// <param name="endAddTime">添加结束时间</param>
        /// <param name="orderState">订单状态(0代表全部状态)</param>
        /// <returns></returns>
        public DataTable GetUserOrderList(int uid, int pageSize, int pageNumber, string startAddTime, string endAddTime, int orderState)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@uid", SqlDbType.Int, 4, uid), 
                                    GenerateInParam("@pagesize", SqlDbType.Int, 4, pageSize), 
                                    GenerateInParam("@pagenumber", SqlDbType.Int, 4, pageNumber), 
                                    GenerateInParam("@startaddtime", SqlDbType.NVarChar, 60, TypeHelper.StringToDateTime(startAddTime).ToString("yyyy-MM-dd HH:mm:ss")),
                                    GenerateInParam("@endaddtime", SqlDbType.NVarChar, 60, TypeHelper.StringToDateTime(endAddTime).ToString("yyyy-MM-dd HH:mm:ss")),
                                    GenerateInParam("@orderstate", SqlDbType.TinyInt, 1, orderState)
                                   };
            return RDBSHelper.ExecuteDataset(CommandType.StoredProcedure,
                                             string.Format("{0}getuserorderlist", RDBSHelper.RDBSTablePre),
                                             parms).Tables[0];
        }

        /// <summary>
        /// 获得用户订单列表
        /// </summary>
        /// <param name="uid">用户id</param>
        /// <param name="startAddTime">添加开始时间</param>
        /// <param name="endAddTime">添加结束时间</param>
        /// <param name="orderState">订单状态(0代表全部状态)</param>
        /// <returns></returns>
        public int GetUserOrderCount(int uid, string startAddTime, string endAddTime, int orderState)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@uid", SqlDbType.Int, 4, uid), 
                                    GenerateInParam("@startaddtime", SqlDbType.NVarChar, 60, TypeHelper.StringToDateTime(startAddTime).ToString("yyyy-MM-dd HH:mm:ss")),
                                    GenerateInParam("@endaddtime", SqlDbType.NVarChar, 60, TypeHelper.StringToDateTime(endAddTime).ToString("yyyy-MM-dd HH:mm:ss")),
                                    GenerateInParam("@orderstate", SqlDbType.TinyInt, 1, orderState)
                                   };
            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.StoredProcedure,
                                                                   string.Format("{0}getuserordercount", RDBSHelper.RDBSTablePre),
                                                                   parms));
        }

        /// <summary>
        /// 获得销售商品列表
        /// </summary>
        /// <param name="pageSize">每页数</param>
        /// <param name="pageNumber">当前页数</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public DataTable GetSaleProductList(int pageSize, int pageNumber, string startTime, string endTime)
        {
            string condition = GetSaleProductListCondition(startTime, endTime);
            string commandText;
            if (pageNumber == 1)
            {
                commandText = string.Format("SELECT TOP {2} [temp2].[psn],[temp2].[name],[temp2].[realcount],[temp2].[shopprice],[temp1].[osn],[temp1].[addtime] FROM (SELECT [oid],[osn],[addtime] FROM [{0}orders] WHERE {1}) AS [temp1] LEFT JOIN [{0}orderproducts] AS [temp2] ON [temp1].[oid]=[temp2].[oid] ORDER BY [recordid] DESC",
                                             RDBSHelper.RDBSTablePre,
                                             condition,
                                             pageSize);
            }
            else
            {
                commandText = string.Format("SELECT [psn],[name],[realcount],[shopprice],[osn],[addtime] FROM (SELECT TOP {1} ROW_NUMBER() OVER (ORDER BY [recordid] DESC) AS [rowid],[temp2].[psn],[temp2].[name],[temp2].[realcount],[temp2].[shopprice],[temp1].[osn],[temp1].[addtime] FROM (SELECT [oid],[osn],[addtime] FROM [{0}orders] WHERE {3}) AS [temp1] LEFT JOIN [{0}orderproducts] AS [temp2] ON [temp1].[oid]=[temp2].[oid]) AS [temp] WHERE [temp].[rowid] BETWEEN {2} AND {1}",
                                             RDBSHelper.RDBSTablePre,
                                             pageNumber * pageSize,
                                             (pageNumber - 1) * pageSize + 1,
                                             condition);
            }

            return RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0];
        }

        /// <summary>
        /// 获得销售商品数量
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public int GetSaleProductCount(string startTime, string endTime)
        {
            string condition = GetSaleProductListCondition(startTime, endTime);
            string commandText = string.Format("SELECT COUNT([temp2].[recordid]) FROM (SELECT [oid],[osn],[addtime] FROM [{0}orders] WHERE {1}) AS [temp1] LEFT JOIN [{0}orderproducts] AS [temp2] ON [temp1].[oid]=[temp2].[oid]",
                                                RDBSHelper.RDBSTablePre,
                                                condition);
            return TypeHelper.ObjectToInt(RDBSHelper.ExecuteScalar(CommandType.Text, commandText));
        }

        /// <summary>
        /// 获得销售商品列表条件
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        private string GetSaleProductListCondition(string startTime, string endTime)
        {
            StringBuilder condition = new StringBuilder();

            condition.AppendFormat(" [orderstate]={0} ", (int)OrderState.Completed);
            if (!string.IsNullOrEmpty(startTime))
                condition.AppendFormat(" AND [addtime]>='{0}' ", TypeHelper.StringToDateTime(startTime).ToString("yyyy-MM-dd HH:mm:ss"));
            if (!string.IsNullOrEmpty(endTime))
                condition.AppendFormat(" AND [addtime]<='{0}' ", TypeHelper.StringToDateTime(endTime).ToString("yyyy-MM-dd HH:mm:ss"));

            return condition.ToString();
        }

        /// <summary>
        /// 获得销售趋势
        /// </summary>
        /// <param name="trendType">趋势类型(0代表订单数,1代表订单合计)</param>
        /// <param name="timeType">时间类型(0代表小时,1代表天,2代表月,3代表年)</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <returns></returns>
        public DataTable GetSaleTrend(int trendType, int timeType, string startTime, string endTime)
        {
            string timeFormat = "yyyy";
            switch (timeType)
            {
                case 0:
                    timeFormat = "DATEPART(hh,[addtime])";
                    break;
                case 1:
                    timeFormat = "CONVERT(varchar(100), [addtime], 23)";
                    break;
                case 2:
                    timeFormat = "SUBSTRING(CONVERT(varchar(100), [addtime], 23),1,7)";
                    break;
                case 3:
                    timeFormat = "DATEPART(yyyy,[addtime])";
                    break;
                default:
                    timeFormat = "DATEPART(hh,[addtime])";
                    break;
            }

            StringBuilder timeCondition = new StringBuilder();

            if (!string.IsNullOrEmpty(startTime))
                timeCondition.AppendFormat(" AND [addtime]>='{0}' ", TypeHelper.StringToDateTime(startTime).ToString("yyyy-MM-dd HH:mm:ss"));
            if (!string.IsNullOrEmpty(endTime))
                timeCondition.AppendFormat(" AND [addtime]<'{0}' ", TypeHelper.StringToDateTime(endTime).ToString("yyyy-MM-dd HH:mm:ss"));

            string commandText = "";
            if (trendType == 0)
            {
                commandText = string.Format("SELECT COUNT([oid]) AS [value],[time] FROM (SELECT [oid],{3} AS [time] FROM [{0}orders] WHERE [orderstate]={1} {2}) AS [temp] GROUP BY [temp].[time]",
                                             RDBSHelper.RDBSTablePre,
                                             (int)OrderState.Completed,
                                             timeCondition.ToString(),
                                             timeFormat);
            }
            else
            {
                commandText = string.Format("SELECT SUM([orderamount]) AS [value],[time] FROM (SELECT [oid],[orderamount],{3} AS [time] FROM [{0}orders] WHERE [orderstate]={1} {2}) AS [temp] GROUP BY [temp].[time]",
                                             RDBSHelper.RDBSTablePre,
                                             (int)OrderState.Completed,
                                             timeCondition.ToString(),
                                             timeFormat);
            }
            return RDBSHelper.ExecuteDataset(CommandType.Text, commandText).Tables[0];
        }

        #endregion

        #region 订单处理

        /// <summary>
        /// 创建订单处理
        /// </summary>
        /// <param name="orderActionInfo">订单处理信息</param>
        public void CreateOrderAction(OrderActionInfo orderActionInfo)
        {
            DbParameter[] parms = {
	                                    GenerateInParam("@oid", SqlDbType.Int,4,orderActionInfo.Oid),
	                                    GenerateInParam("@uid", SqlDbType.Int,4 ,orderActionInfo.Uid),
	                                    GenerateInParam("@realname", SqlDbType.NVarChar,10,orderActionInfo.RealName),
	                                    GenerateInParam("@admingid", SqlDbType.SmallInt,2 ,orderActionInfo.AdminGid),
	                                    GenerateInParam("@admingtitle", SqlDbType.NVarChar,50 ,orderActionInfo.AdminGTitle),
	                                    GenerateInParam("@actiontype", SqlDbType.TinyInt,1 ,orderActionInfo.ActionType),
                                        GenerateInParam("@actiontime", SqlDbType.DateTime, 8,orderActionInfo.ActionTime),
                                        GenerateInParam("@actiondes", SqlDbType.NVarChar, 250,orderActionInfo.ActionDes)
                                    };
            RDBSHelper.ExecuteNonQuery(CommandType.StoredProcedure,
                                       string.Format("{0}createorderaction", RDBSHelper.RDBSTablePre),
                                       parms);
        }

        /// <summary>
        /// 获得订单处理列表
        /// </summary>
        /// <param name="oid">订单id</param>
        /// <returns></returns>
        public IDataReader GetOrderActionList(int oid)
        {
            DbParameter[] parms = {
	                                    GenerateInParam("@oid", SqlDbType.Int,4,oid)
                                    };
            return RDBSHelper.ExecuteReader(CommandType.StoredProcedure,
                                            string.Format("{0}getorderactionlist", RDBSHelper.RDBSTablePre),
                                            parms);
        }

        /// <summary>
        /// 获得订单id列表
        /// </summary>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="orderActionType">订单操作类型</param>
        /// <returns></returns>
        public DataTable GetOrderIdList(DateTime startTime, DateTime endTime, int orderActionType)
        {
            DbParameter[] parms = {
                                    GenerateInParam("@starttime", SqlDbType.DateTime, 8, startTime),
                                    GenerateInParam("@endtime", SqlDbType.DateTime, 8, endTime),
                                    GenerateInParam("@orderactiontype", SqlDbType.Int, 4, orderActionType)
                                   };
            string commandText = string.Format("SELECT [oid] FROM [{0}orderactions] WHERE [actiontype]=@orderactiontype AND [actiontime]>=@starttime AND [actiontime]<@endtime",
                                                RDBSHelper.RDBSTablePre);
            return RDBSHelper.ExecuteDataset(CommandType.Text, commandText, parms).Tables[0];
        }

        #endregion
    }
}