using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CY.IDAL;
using System.Data.SqlClient;
using System.Data;
using CY.Model;
using CY.Infrastructure.DESEncrypt;
using CY.Infrastructure.Query;
using CY.Infrastructure.Common;
using System.Transactions;
using CY.IBaseDAL;
using CY.Config;
namespace CY.SQLDAL
{
    public class EC_MemberBasicDAL : IEC_MemberBasicDAL
    {
        private Database _dataBase = null;

        #region 常量

        /// <summary>
        /// 查询目标 
        /// </summary>
        const string SELECTTARGET = " x.* ";
        /// <summary>
        /// 查询来源
        /// </summary>
        const string FROMSOUCEBEFORE = " (select m.*,n.FirmName as ShortName,b.Name as StateName From EC_MemberBasic m join EC_MemberExtend n on m.MemberId = n.Keyid Left Join Sys_Dictionary as b On (m.UseState=b.MeanValue and b.DicType='会员帐号状态') Where  ";
        const string FROMSOUCEEND = " ) as x ";
        /// <summary>
        /// 分页默认排序字段
        /// </summary>
        const string ORDERBY = " UseState Asc, RegisterDate DESC";

        #endregion

        public EC_MemberBasicDAL()
        {
            _dataBase = new Database();
        }

        public EC_MemberBasicDAL(Database database)
        {
            _dataBase = database;
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.EC_MemberBasic trueModel = model as Model.EC_MemberBasic;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@MemberId",trueModel.MemberId),
					new SqlParameter("@MemberType",trueModel.MemberType),
					new SqlParameter("@Name",trueModel.Name),
					new SqlParameter("@LoginId",trueModel.LoginId),
					new SqlParameter("@Password",trueModel.Password),
					new SqlParameter("@UseState",trueModel.UseState),
					new SqlParameter("@RegisterDate",trueModel.RegisterDate),
					new SqlParameter("@Province",trueModel.Province),
					new SqlParameter("@City",trueModel.City),
					new SqlParameter("@County",trueModel.County),
					new SqlParameter("@RegisteredIp",trueModel.RegisteredIp),
					new SqlParameter("@LastLoginDate",trueModel.LastLoginDate),
					new SqlParameter("@LastIp",trueModel.LastIp),
					new SqlParameter("@PayId",trueModel.PayId),
					new SqlParameter("@ExpirationTime",trueModel.ExpirationTime),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark),
					new SqlParameter("@bid",trueModel.bid),
					new SqlParameter("@bdomainname",trueModel.bdomainname),
					new SqlParameter("@bstenciid",trueModel.bstenciid),
					new SqlParameter("@CustomStenc",trueModel.CustomStenc)
            };
            try
            {
                _dataBase.Query("sp_EC_MemberBasic_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.EC_MemberBasic trueModel = model as Model.EC_MemberBasic;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@MemberId",trueModel.MemberId),
					new SqlParameter("@MemberType",trueModel.MemberType),
					new SqlParameter("@Name",trueModel.Name),
					new SqlParameter("@LoginId",trueModel.LoginId),
					new SqlParameter("@Password",trueModel.Password),
					new SqlParameter("@UseState",trueModel.UseState),
					new SqlParameter("@RegisterDate",trueModel.RegisterDate),
					new SqlParameter("@Province",trueModel.Province),
					new SqlParameter("@City",trueModel.City),
					new SqlParameter("@County",trueModel.County),
					new SqlParameter("@RegisteredIp",trueModel.RegisteredIp),
					new SqlParameter("@LastLoginDate",trueModel.LastLoginDate),
					new SqlParameter("@LastIp",trueModel.LastIp),
					new SqlParameter("@PayId",trueModel.PayId),
					new SqlParameter("@ExpirationTime",trueModel.ExpirationTime),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark),
					new SqlParameter("@bid",trueModel.bid),
					new SqlParameter("@bdomainname",trueModel.bdomainname),
					new SqlParameter("@bstenciid",trueModel.bstenciid),
					new SqlParameter("@CustomStenc",trueModel.CustomStenc==null?string.Empty:trueModel.CustomStenc)
            };
            try
            {
                _dataBase.Query("sp_EC_MemberBasic_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.EC_MemberBasic trueModel = model as Model.EC_MemberBasic;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@MemberId",trueModel.MemberId)
            };
            try
            {
                _dataBase.Query("sp_EC_MemberBasic_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 列表查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.EC_MemberBasic> SelectAllModel(Infrastructure.Query.Query query)
        {
            return _dataBase.SelectModel("*", "EC_MemberBasic") as IList<EC_MemberBasic>;//执行查询
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<EC_MemberBasic> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {

            if (null == pagination || null == query || null == query.Criteria || 1 > query.Criteria.Count)
                return null;
            //query.Criteria 首个元素必须是排序字段，其值为结果排序字段

            int maxParamIndex = query.Criteria.Count - 1;//最大索引

            string[] orderbys = new string[] { ORDERBY };
            string resultOrderBy = "";//结果集排序方式

            if ("@orderBy".Equals(query.Criteria[maxParamIndex].PropertyName))
            {
                orderbys = string.Format("{0}", query.Criteria[maxParamIndex].Value).Split(',');
                resultOrderBy = 1 == orderbys.Length ? resultOrderBy : orderbys[1];
            }

            string fromSouce = string.Format("{0}{1}{2}", FROMSOUCEBEFORE, query.Criteria[0].Value, FROMSOUCEEND);//拼装条件
            return _dataBase.SelectModelPage<Model.EC_MemberBasic>(pagination, SELECTTARGET, fromSouce, orderbys[0], resultOrderBy);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<EC_MemberBasic> SelectModelPageByName(Infrastructure.Query.Pagination pagination, string Name)
        {
            if (Name == null)
                return null;//错误数据返会空 

            Query query = new Query(true);//同时实例化参数集合对象
            query.Criteria.Add(new Criterion("condition", string.Format(" Name like '%{0}%' ", Name)));//条件
            IList<EC_MemberBasic> result = SelectModelPage(query, pagination) as IList<EC_MemberBasic>;//执行查询

            return null == result ? null : result;//返回结果
        }

        /// <summary>
        /// 根据会员编号获取实体信息
        /// </summary>
        /// <param name="id">主键编号</param>
        /// <returns></returns>
        public EC_MemberBasic SelectModleByMemberId(Guid MemberId)
        {
            if (MemberId == null)
                return null;//错误数据返会空 

            IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>("*", "EC_MemberBasic", " MemberId = '" + MemberId + "'") as IList<EC_MemberBasic>;//执行查询
            return (null == result || result.Count == 0) ? null : result[0];//返回结果
        }

        /// <summary>
        /// 判断登录会员
        /// </summary>
        /// <param name="LoginId">登录帐号</param>
        /// <param name="LoginId">登录密码</param>
        /// <returns></returns>
        public EC_MemberBasic SelectModleByLoginIdAndPwd(string LoginId, string Password)
        {
            if (string.IsNullOrEmpty(LoginId) || string.IsNullOrEmpty(Password))
                return null;//错误数据返会空 

            IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>(" m.*,n.FirmName as ShortName,b.Name as StateName  ", " EC_MemberBasic m left join EC_MemberExtend n on m.MemberId = n.Keyid Left Join Sys_Dictionary as b On (m.UseState=b.MeanValue and b.DicType='会员帐号状态') ", string.Format(" LoginId='{0}' and Password='{1}' ", LoginId, Password)) as IList<EC_MemberBasic>;//执行查询

            return (null == result || 0 == result.Count) ? null : result[0];//返回结果
        }

        /// <summary>
        /// 检测帐号是否存在
        /// </summary>
        /// <param name="LoginId">登录帐号</param>
        /// <returns></returns>
        public EC_MemberBasic SelectModleMemberByLoginId(string LoginId)
        {
            if (LoginId == null)
                return null;//错误数据返会空 

            IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>("*", "EC_MemberBasic", string.Format(" LoginId='{0}'", LoginId)) as IList<EC_MemberBasic>;//执行查询

            return (null == result || result.Count == 0) ? null : result[0];//返回结果
        }

        /// <summary>
        /// 根据公司名称获取会员
        /// </summary>
        /// <param name="CompanyName">公司名</param>
        /// <returns></returns>
        public EC_MemberBasic SelectModleMemberByCompanyName(string CompanyName)
        {
            if (CompanyName == null)
                return null;//错误数据返会空 

            IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>("*", "EC_MemberBasic", string.Format(" Name='{0}' and MemberType <> '员工' and MemberType <> '管理员' ", CompanyName)) as IList<EC_MemberBasic>;//执行查询

            return (null == result || result.Count == 0) ? null : result[0];//返回结果
        }

        /// <summary>
        /// 模糊查询公司名称
        /// </summary>
        /// <param name="CompanyName">公司名</param>
        /// <returns></returns>
        public IEnumerable<EC_MemberExtend> SelectExtendListByName(string CompanyName)
        {
            if (CompanyName == null)
                return null;//错误数据返会空 
            try
            {
                IList<EC_MemberExtend> result = _dataBase.SelectModel<EC_MemberExtend>(" top 5 * ", string.Format(" EC_MemberExtend a join  EC_MemberBasic b on (a.Keyid = b.MemberId and b.Name like '%{0}%' and b.MemberType <> '管理员') ", CompanyName), "") as IList<EC_MemberExtend>;//执行查询
                return result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 模糊查询公司名称
        /// </summary>
        /// <param name="CompanyName">公司名</param>
        /// <returns></returns>
        public IEnumerable<EC_MemberBasic> SelectMemberByName(string CompanyName)
        {
            string conditon = "  1=1 and MemberType = '印刷厂商' ";
            if (!string.IsNullOrEmpty(CompanyName))
                conditon += string.Format(" And Name like '%{0}%' ", CompanyName);
            try
            {
                IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>(" top 5 * ", " EC_MemberBasic ", conditon) as IList<EC_MemberBasic>;//执行查询
                return result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 事务提交注册事件
        /// </summary>
        /// <param name="m_EC_MemberBasic"></param>
        /// <param name="m_EC_MemberExtend"></param>
        /// <param name="m_EC_SafeValidation"></param>
        /// <param name="m_Pay_PaymentAccount"></param>
        /// <param name="m_EC_FirmInfo"></param>
        /// <param name="m_EC_ShopInfo"></param>
        /// <param name="m_EC_AcceptWayBySeller"></param>
        /// <param name="m_Integrity_IntegrityCard_Buyer"></param>
        /// <param name="m_Integrity_IntegrityCard_Seller"></param>
        /// <param name="m_Sys_Permissions_UserRoleRelation"></param>
        /// <param name="m_OA_Commodity_Paper"></param>
        /// <param name="m_OA_Commodity_Sheet"></param>
        /// <returns></returns>
        public bool InsertModel(EC_MemberBasic m_EC_MemberBasic, EC_MemberExtend m_EC_MemberExtend, EC_SafeValidation m_EC_SafeValidation, Pay_PaymentAccount m_Pay_PaymentAccount, EC_FirmInfo m_EC_FirmInfo, EC_ShopInfo m_EC_ShopInfo, EC_AcceptWayBySeller m_EC_AcceptWayBySeller, Integrity_IntegrityCard m_Integrity_IntegrityCard_Buyer, Integrity_IntegrityCard m_Integrity_IntegrityCard_Seller, Sys_Permissions_UserRoleRelation m_Sys_Permissions_UserRoleRelation, OA_Commodity m_OA_Commodity_Paper, OA_Commodity m_OA_Commodity_Sheet, IList<Model.EC_SellerBusinessLimits> m_EC_SellerBusinessLimitsList)
        {
            try
            {
                IUnitOfWork unit = new UnitOfWorkForSql();

                Pay_PaymentAccountDAL dal_Pay_PaymentAccountDAL = new Pay_PaymentAccountDAL(_dataBase);
                OA_CommodityDAL dal_OA_CommodityDAL = new OA_CommodityDAL(_dataBase);
                Pay_CreditRecardDAL dal_Pay_CreditRecardDAL = new Pay_CreditRecardDAL(_dataBase);


                switch (m_EC_MemberBasic.MemberType)
                {
                    case "印刷厂商":
                        m_EC_MemberBasic.bid = GetMaxBid();
                        m_EC_MemberBasic.bdomainname = m_EC_MemberBasic.bid + "." + Utils.GetSpellCode(m_EC_MemberBasic.Province.Replace("省", "")).ToLower();
                        m_EC_MemberBasic.bstenciid = -1;// GetDefaultStenciid();
                        m_EC_MemberBasic.CustomStenc = "";
                        break;
                    case "买家会员":
                        m_EC_MemberBasic.bid = -1;
                        m_EC_MemberBasic.bdomainname = "0";
                        m_EC_MemberBasic.bstenciid = 0;
                        m_EC_MemberBasic.CustomStenc = "";
                        break;
                    case "个人网店":
                        m_EC_MemberBasic.bid = GetMaxBid();
                        m_EC_MemberBasic.bdomainname = m_EC_MemberBasic.bid + "." + Utils.GetSpellCode(m_EC_MemberBasic.Province.Replace("省", "")).ToLower();
                        m_EC_MemberBasic.bstenciid = -1;// GetDefaultStenciid();
                        m_EC_MemberBasic.CustomStenc = "";
                        break;
                    default:
                        m_EC_MemberBasic.bid = -1;
                        m_EC_MemberBasic.bdomainname = "0";
                        m_EC_MemberBasic.bstenciid = -1;
                        m_EC_MemberBasic.CustomStenc = "";
                        break;
                }

                if (m_EC_MemberBasic.MemberType == "印刷厂商" || m_EC_MemberBasic.MemberType == "个人网店")
                {
                    decimal allMoney = 0;
                    string payType = string.Empty;
                    if (m_EC_MemberBasic.MemberType == "印刷厂商")
                    {
                        allMoney = WebInfo.Instance.RegisterWebPayByFirm;
                        payType = "印刷厂商注册费";
                    }
                    else
                    {
                        allMoney = WebInfo.Instance.RegisterWebPayByShop;
                        payType = "个人网店押金";
                    }

                    EC_PaymentRecordDAL _EC_PaymentRecordDAL = new EC_PaymentRecordDAL(_dataBase);
                    EC_PaymentRecord m_EC_PaymentRecord = new EC_PaymentRecord();

                    m_EC_PaymentRecord.BusinessManagerId = 0;
                    m_EC_PaymentRecord.AccountManagerId = 0;
                    m_EC_PaymentRecord.PayStartTime = DateTime.Now;
                    m_EC_PaymentRecord.PayEndTime = DateTime.Now.AddYears(1);
                    m_EC_PaymentRecord.LastUpdateTime = DateTime.Now;
                    m_EC_PaymentRecord.AdForum = "";
                    m_EC_PaymentRecord.AdImg = "";
                    m_EC_PaymentRecord.AdInstallments = "";
                    m_EC_PaymentRecord.AdLocation = "";
                    m_EC_PaymentRecord.AdSize = "";
                    m_EC_PaymentRecord.CompanyPhone = m_EC_MemberExtend.PhoneNum;
                    m_EC_PaymentRecord.Contact = string.IsNullOrEmpty(m_EC_MemberExtend.BusinessContacts) ? string.Empty : m_EC_MemberExtend.BusinessContacts;
                    m_EC_PaymentRecord.Email = m_EC_MemberExtend.Email;
                    m_EC_PaymentRecord.MemberId = m_EC_MemberBasic.MemberId;
                    m_EC_PaymentRecord.MemberName = m_EC_MemberBasic.Name;
                    m_EC_PaymentRecord.Operator = m_EC_MemberBasic.Operator;
                    m_EC_PaymentRecord.PayAllMoney = allMoney;
                    m_EC_PaymentRecord.PayMoney = 0;
                    m_EC_PaymentRecord.PayRole = m_EC_MemberBasic.MemberType;
                    m_EC_PaymentRecord.PayType = payType;
                    m_EC_PaymentRecord.QQ = m_EC_MemberExtend.QQ;
                    m_EC_PaymentRecord.Remark = _EC_PaymentRecordDAL.GetNewOrderId();
                    ;
                    m_EC_PaymentRecord.SoftwarePermissions = string.Empty;

                    unit.RegisterNew(m_EC_PaymentRecord, _EC_PaymentRecordDAL);
                }

                unit.RegisterNew(m_EC_MemberBasic, this);
                unit.RegisterNew(m_EC_MemberExtend, new EC_MemberExtendDAL(_dataBase));

                if (m_EC_MemberBasic.MemberType.ToString() == "印刷厂商")
                {
                    m_EC_FirmInfo.FirmStencilId = m_EC_MemberBasic.bstenciid;
                    m_EC_FirmInfo.DomainName = m_EC_MemberBasic.bdomainname;
                    unit.RegisterNew(m_EC_FirmInfo, new EC_FirmInfoDAL(_dataBase));
                    unit.RegisterNew(m_OA_Commodity_Paper, dal_OA_CommodityDAL);
                    unit.RegisterNew(m_OA_Commodity_Sheet, dal_OA_CommodityDAL);
                }
                else if (m_EC_MemberBasic.MemberType.ToString() == "个人网店")
                    unit.RegisterNew(m_EC_ShopInfo, new EC_ShopInfoDAL(_dataBase));

                unit.RegisterNew(m_EC_SafeValidation, new EC_SafeValidationDAL(_dataBase));
                unit.RegisterNew(m_Pay_PaymentAccount, dal_Pay_PaymentAccountDAL);
                unit.RegisterNew(m_EC_AcceptWayBySeller, new EC_AcceptWayBySellerDAL(_dataBase));
                unit.RegisterNew(m_EC_MemberExtend, new EC_MemberExtendDAL(_dataBase));
                unit.RegisterNew(m_Integrity_IntegrityCard_Buyer, new Integrity_IntegrityCardDAL(_dataBase));

                if (m_Sys_Permissions_UserRoleRelation.RoleId > 0)
                    unit.RegisterNew(m_Sys_Permissions_UserRoleRelation, new Sys_Permissions_UserRoleRelationDAL(_dataBase));

                if (m_EC_MemberBasic.MemberType.ToString() != "买家会员")
                    unit.RegisterNew(m_Integrity_IntegrityCard_Seller, new Integrity_IntegrityCardDAL(_dataBase));

                if (unit.Commit())
                {
                    if (m_EC_SellerBusinessLimitsList != null && m_EC_SellerBusinessLimitsList.Count > 0)
                    {
                        EC_SellerBusinessLimitsDAL _EC_SellerBusinessLimitsDAL = new EC_SellerBusinessLimitsDAL();
                        _EC_SellerBusinessLimitsDAL.SaveBusinessRangeByFirmId(m_EC_SellerBusinessLimitsList);
                    }
                    Pay_CreditRecard m_Pay_CreditRecard = null;
                    if (m_Pay_PaymentAccount.SurplusCredit > 0)
                    {
                        m_Pay_CreditRecard = new Pay_CreditRecard();
                        m_Pay_CreditRecard.CreateTime = m_EC_MemberBasic.LastUpdateTime;
                        m_Pay_CreditRecard.PayId = m_Pay_PaymentAccount.Keyid;
                        m_Pay_CreditRecard.Remark = "注册初始授信";
                        m_Pay_CreditRecard.ResidualMoney = m_Pay_PaymentAccount.SurplusCredit;
                        m_Pay_CreditRecard.TradingMoney = m_Pay_PaymentAccount.SurplusCredit;
                        m_Pay_CreditRecard.TradingName = "注册初始授信";
                        m_Pay_CreditRecard.TradingState = 0;
                        m_Pay_CreditRecard.TradingType = 1;
                        dal_Pay_CreditRecardDAL.InserModel(m_Pay_CreditRecard);
                    }
                    //if (!string.IsNullOrEmpty(m_EC_MemberExtend.Inviter))
                    //    dal_Pay_PaymentAccountDAL.SendPoOrMoToMember(m_EC_MemberBasic.MemberId, 36, 0);
                    return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据会员编号获得会员信息
        /// </summary>
        /// <param name="MemberId">会员编号</param>
        /// <returns></returns>
        public EC_MemberBasic GetMemberByMemberId(Guid MemberId)
        {
            EC_MemberBasic model = new EC_MemberBasic();
            if (MemberId == null)
                return null;//错误数据返会空 

            IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>("*", "EC_MemberBasic", string.Format(" MemberId='{0}'", MemberId)) as IList<EC_MemberBasic>;//执行查询

            //return (null == result || result.Count == 0) ? null : result[0];//返回结果

            if (result != null && result.Count > 0)
            {
                model = result[0];
                model.EC_MemberExtendMode = new EC_MemberExtend();
                model.EC_MemberExtendMode = (new EC_MemberExtendDAL()).GetModelByMemberId(MemberId);
            }
            return model;
        }

        /// <summary>
        /// 分页根据印刷类型获取其对于的厂商列表
        /// </summary>
        /// <param name="printTypeId"></param>
        /// <param name="pageIndex"></param>
        /// <param name="recordCount"></param>
        /// <returns></returns>
        public IList<EC_MemberBasic> GetFirmListByPage(int printTypeId, int printMode, Guid memberId, int orderType, string firmName, string selectProvince, string selectCity, string selectCounty, int pageIndex, int pageSize, ref int pageCount)
        {
            Pagination pagination = new Pagination();
            pagination.PageIndex = pageIndex;
            pagination.PageSize = pageSize;

            string rowOrderStr = string.Empty;
            string resultOrderStr = string.Empty;
            string selectTarget = " a.*,c.QQ,c.PhoneNum,c.BusinessContacts ";
            string fromSouce = @"  dbo.EC_MemberBasic a left join EC_MemberExtend c on a.MemberId=c.Keyid INNER JOIN dbo.EC_SellerBusinessLimits b ON a.MemberId=b.SellerId ";

            if (orderType != -1)
            {
                if (orderType == 2)
                {
                    rowOrderStr = "a.BusinessCount";
                    resultOrderStr = " BusinessCount ";
                }
                else if (orderType == 3)
                {
                    rowOrderStr = "a.BusinessCount desc ";
                    resultOrderStr = " BusinessCount desc ";
                }
                else if (orderType == 4)
                {
                    fromSouce += @" LEFT JOIN Integrity_IntegrityCard d ON d.MemberId = a.MemberId
                                    left join Integrity_CreditLevelRule e on d.RuleId = e.RuleId
                                    ";
                    selectTarget += " ,d.Credit ";

                    rowOrderStr = "d.Credit";
                    resultOrderStr = "Credit";
                }
                else if (orderType == 5)
                {
                    fromSouce += @" LEFT JOIN Integrity_IntegrityCard d ON d.MemberId = a.MemberId
                                    left join Integrity_CreditLevelRule e on d.RuleId = e.RuleId
                                    ";
                    selectTarget += " ,d.Credit ";

                    rowOrderStr = "d.Credit desc";
                    resultOrderStr = "Credit desc";
                }
                else
                {
                    rowOrderStr = "a.BusinessCount desc";
                    resultOrderStr = " BusinessCount desc";
                }
            }
            else
            {
                rowOrderStr = "a.BusinessCount desc";
                resultOrderStr = " BusinessCount desc";
            }
            fromSouce += " where b.BusinessId=" + printTypeId + "  AND b.Status=1 AND (a.MemberType='印刷厂商' OR a.MemberType='个人网店') AND a.UseState=3 ";

            if (orderType == 4 || orderType == 5)
            {
                fromSouce += "  AND e.RuleType='true' ";
            }
            if (printMode == 1)
            {
                fromSouce += " AND b.JoinFlag=1 ";
            }
            else
            {
                fromSouce += " AND b.SpecialFlag=1 ";
            }
            if (!string.IsNullOrEmpty(firmName))
            {
                fromSouce += " and a.Name like '%" + firmName + "%' ";
            }

            if (memberId != Guid.Empty)
            {
                fromSouce += " and a.MemberId<>'" + memberId.ToString() + "'";
            }

            if (!string.IsNullOrEmpty(selectProvince))
            {
                fromSouce += " and a.Province='" + selectProvince + "'";
            }

            if (!string.IsNullOrEmpty(selectCity))
            {
                fromSouce += " and a.City='" + selectCity + "'";
            }

            if (!string.IsNullOrEmpty(selectCounty))
            {
                fromSouce += " and a.County='" + selectCounty + "'";
            }

            IList<EC_MemberBasic> list = _dataBase.SelectModelPage<EC_MemberBasic>(pagination, selectTarget, fromSouce, rowOrderStr, resultOrderStr);
            pageCount = pagination.PageCount;
            return list;
        }

        /// <summary>
        /// 获取所有的印刷厂商
        /// </summary>
        /// <returns></returns>
        public IList<EC_MemberBasic> GetAllFirm()
        {
            string selectTarget = " a.MemberId,a.Name,a.InquiryId,b.QQ   ";
            string fromSouce = " EC_MemberBasic a inner join EC_MemberExtend b on a.MemberId=b.Keyid ";
            string condition = " a.MemberType='印刷厂商' ";
            IList<Model.EC_MemberBasic> result = _dataBase.SelectModel<Model.EC_MemberBasic>(selectTarget, fromSouce, condition);
            return result;
        }

        /// <summary>
        /// 获取所有的会员
        /// </summary>
        /// <returns></returns>
        public IList<EC_MemberBasic> GetAllMember()
        {
            string selectTarget = " MemberId, Name  ";
            string fromSouce = " EC_MemberBasic ";
            string condition = " (MemberType='印刷厂商' or MemberType='个人网店' or MemberType='买家会员') and usestate=3 ";
            IList<Model.EC_MemberBasic> result = _dataBase.SelectModel<Model.EC_MemberBasic>(selectTarget, fromSouce, condition);
            return result;
        }

        /// <summary>
        /// 修改会员资料，会员缴费，并保存业务范围
        /// </summary>
        /// <param name="m_EC_MemberBasic"></param>
        /// <param name="m_EC_SellerBusinessLimitsList"></param>
        /// <param name="m_EC_PaymentRecord"></param>
        /// <param name="m_EC_FirmInfo"></param>
        /// <returns></returns>
        public bool UpdateModel(EC_MemberBasic m_EC_MemberBasic, IList<Model.EC_SellerBusinessLimits> m_EC_SellerBusinessLimitsList, EC_PaymentRecord m_EC_PaymentRecord, EC_FirmInfo m_EC_FirmInfo)
        {
            EC_SellerBusinessLimitsDAL _EC_SellerBusinessLimitsDAL = new EC_SellerBusinessLimitsDAL(_dataBase);
            EC_PaymentRecordDAL dal_EC_PaymentRecordDAL = new EC_PaymentRecordDAL(_dataBase);
            EC_FirmInfoDAL dal_EC_FirmInfoDAL = new EC_FirmInfoDAL(_dataBase);

            bool isSuccess = true;
            using (TransactionScope scope = new TransactionScope())
            {
                isSuccess = this.UpdateModel(m_EC_MemberBasic);
                if (isSuccess)
                {
                    if (m_EC_SellerBusinessLimitsList != null)
                    {
                        isSuccess = _EC_SellerBusinessLimitsDAL.SaveBusinessRangeByFirmId(m_EC_SellerBusinessLimitsList);
                    }
                }
                if (isSuccess)
                {
                    if (m_EC_PaymentRecord != null)
                    {
                        isSuccess = dal_EC_PaymentRecordDAL.InserModel(m_EC_PaymentRecord);
                    }
                }
                if (isSuccess)
                {
                    isSuccess = this.UpdateModel(m_EC_MemberBasic);
                }
                if (isSuccess)
                {
                    if (m_EC_FirmInfo != null)
                    {
                        isSuccess = dal_EC_FirmInfoDAL.UpdateModel(m_EC_FirmInfo);
                    }
                }
                if (isSuccess)
                    scope.Complete();
            }
            return isSuccess;
        }

        /// <summary>
        /// 我的推广列表
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="RegStartTime"></param>
        /// <param name="RegEndTime"></param>
        /// <param name="Province"></param>
        /// <param name="City"></param>
        /// <param name="Country"></param>
        /// <param name="MemberType"></param>
        /// <param name="MemberName"></param>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public IEnumerable<EC_MemberBasic> MyPromotionMemberList(Infrastructure.Query.Pagination pagination, DateTime? RegStartTime, DateTime? RegEndTime, string Province, string City, string Country, string MemberType, string MemberName, Guid MemberId)
        {
            try
            {
                string condition = " 1=1 ";


                if (RegStartTime != null)
                    condition += " and RegisterDate >= '" + RegStartTime + "' ";

                if (RegEndTime != null)
                    condition += " and RegisterDate <= '" + RegEndTime.Value.AddDays(1) + "' ";

                if (!string.IsNullOrEmpty(Province))
                    condition += " and Province = '" + Province + "' ";

                if (!string.IsNullOrEmpty(City))
                    condition += " and City = '" + City + "' ";

                if (!string.IsNullOrEmpty(Country))
                    condition += " and County = '" + Country + "' ";

                if (!string.IsNullOrEmpty(MemberType))
                    condition += " and MemberType = '" + MemberType + "' ";

                if (!string.IsNullOrEmpty(MemberName))
                    condition += " and Name like '%" + MemberName + "%' ";

                if (MemberId != Guid.Empty)
                    condition += " and c.Keyid = '" + MemberId + "' ";

                return _dataBase.SelectModelPage<Model.EC_MemberBasic>(pagination, " a.* ", " dbo.EC_MemberBasic AS a LEFT JOIN dbo.EC_MemberExtend AS b ON a.MemberId = b.Keyid LEFT JOIN dbo.EC_MemberExtend c ON b.Inviter =c.PopularizeNum ", " RegisterDate DESC ", " RegisterDate DESC ", condition);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 我的推广提成
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="RegStartTime"></param>
        /// <param name="RegEndTime"></param>
        /// <param name="Province"></param>
        /// <param name="City"></param>
        /// <param name="Country"></param>
        /// <param name="MemberType"></param>
        /// <param name="MemberName"></param>
        /// <param name="MemberId"></param>
        /// <param name="FromMemberId"></param>
        /// <returns></returns>
        public IEnumerable<Promotion_IncomeRule> MyPromotionmoneyList(Infrastructure.Query.Pagination pagination, DateTime? RegStartTime, DateTime? RegEndTime, string Province, string City, string Country, string MemberType, string MemberName, Guid MemberId, Guid FromMemberId)
        {
            try
            {
                string conditon_o = "";

                if (RegStartTime != null)
                    conditon_o += " and c.CreateTime >= '" + RegStartTime + "' ";

                if (RegEndTime != null)
                    conditon_o += " and c.CreateTime <= '" + RegEndTime.Value.AddDays(1) + "' ";

                string condition = " b.DicType='来源类型' ";

                if (!string.IsNullOrEmpty(Province))
                    condition += " and d.Province = '" + Province + "' ";

                if (!string.IsNullOrEmpty(City))
                    condition += " and d.City = '" + City + "' ";

                if (!string.IsNullOrEmpty(Country))
                    condition += " and d.County = '" + Country + "' ";

                if (!string.IsNullOrEmpty(MemberType))
                    condition += " and d.MemberType = '" + MemberType + "' ";

                if (!string.IsNullOrEmpty(MemberName))
                    condition += " and d.Name like '%" + MemberName + "%' ";

                //if (MemberId != Guid.Empty)
                //    condition += " and (k.MemberId = '" + MemberId + "'  OR c.PayId IS null)";

                if (FromMemberId != Guid.Empty)
                    condition += " and d.MemberId = '" + FromMemberId + "' ";

                condition += "AND k.MemberId = '" + MemberId + "' ";

                condition += " GROUP BY a.Keyid,a.Remark,b.Name,a.SuperiorNoteMoney,a.PopularizeNoteMoney";

                return _dataBase.SelectModelPage<Model.Promotion_IncomeRule>(pagination, " a.Keyid,a.Remark as aRemark,b.Name AS Name,a.SuperiorNoteMoney,a.PopularizeNoteMoney,SUM(c.TradingMoney) AS TradingMoney ", " dbo.Promotion_IncomeRule AS a LEFT JOIN dbo.Sys_Dictionary AS b ON a.SourceTypeId = b.Keyid LEFT JOIN dbo.Pay_PromotionRecord AS c ON b.Name = c.TradingName and c.IsProxy='False' and c.DataType ='推广' and c.RewardType ='推广金' " + conditon_o + " left join dbo.EC_MemberBasic AS d ON c.Remark = d.MemberId LEFT JOIN dbo.EC_MemberExtend AS e ON d.MemberId = e.Keyid LEFT JOIN dbo.EC_MemberExtend f ON e.Inviter =f.PopularizeNum LEFT JOIN dbo.Pay_PaymentAccount AS k ON c.PayId = k.Keyid ", " a.Remark ASC ", " aRemark ASC ", condition);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 我的代理提成
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="RegStartTime"></param>
        /// <param name="RegEndTime"></param>
        /// <param name="Province"></param>
        /// <param name="City"></param>
        /// <param name="Country"></param>
        /// <param name="MemberType"></param>
        /// <param name="MemberName"></param>
        /// <param name="MemberId"></param>
        /// <param name="FromMemberId"></param>
        /// <returns></returns>
        public IEnumerable<Promotion_IncomeRule> MyProxymoneyList(Infrastructure.Query.Pagination pagination, DateTime? RegStartTime, DateTime? RegEndTime, string Province, string City, string Country, string MemberType, string MemberName, Guid MemberId, Guid FromMemberId)
        {
            try
            {
                string conditon_o = "";

                if (RegStartTime != null)
                    conditon_o += " and c.CreateTime >= '" + RegStartTime + "' ";

                if (RegEndTime != null)
                    conditon_o += " and c.CreateTime <= '" + RegEndTime.Value.AddDays(1) + "' ";

                string condition = " b.DicType='来源类型' ";

                if (!string.IsNullOrEmpty(Province))
                    condition += " and d.Province = '" + Province + "' ";

                if (!string.IsNullOrEmpty(City))
                    condition += " and d.City = '" + City + "' ";

                if (!string.IsNullOrEmpty(Country))
                    condition += " and d.County = '" + Country + "' ";

                if (!string.IsNullOrEmpty(MemberType))
                    condition += " and d.MemberType = '" + MemberType + "' ";

                if (!string.IsNullOrEmpty(MemberName))
                    condition += " and d.Name like '%" + MemberName + "%' ";

                if (MemberId != Guid.Empty)
                    condition += " and (k.MemberId = '" + MemberId + "'  OR c.PayId IS null)";

                if (FromMemberId != Guid.Empty)
                    condition += " and d.MemberId = '" + FromMemberId + "' ";

                condition += " GROUP BY a.Keyid,a.Remark,b.Name,a.SuperiorNoteMoney,a.PopularizeNoteMoney,a.DeputyCityRevenue,a.DeputyDistrictRevenue";

                return _dataBase.SelectModelPage<Model.Promotion_IncomeRule>(pagination, " a.Keyid,a.Remark as aRemark,b.Name AS Name,a.SuperiorNoteMoney,a.PopularizeNoteMoney,a.DeputyCityRevenue,a.DeputyDistrictRevenue,SUM(c.TradingMoney) AS TradingMoney ", " dbo.Promotion_IncomeRule AS a LEFT JOIN dbo.Sys_Dictionary AS b ON a.SourceTypeId = b.Keyid LEFT JOIN dbo.Pay_PromotionRecord AS c ON b.Name = c.TradingName  and c.IsProxy='True'  and c.DataType ='代理' and c.RewardType ='代理金' " + conditon_o + " left join dbo.EC_MemberBasic AS d ON c.Remark = d.MemberId LEFT JOIN dbo.EC_MemberExtend AS e ON d.MemberId = e.Keyid LEFT JOIN dbo.Pay_PaymentAccount AS k ON c.PayId = k.Keyid and (k.MemberId = '" + MemberId + "'  OR c.PayId IS null) ", " a.Remark ASC ", " aRemark ASC ", condition);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 检测IP是否重复
        /// </summary>
        /// <param name="RegisteredIP"></param>
        /// <returns></returns>
        public bool IsExitsRegisterIP(string RegisteredIP)
        {
            try
            {
                IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>("*", "EC_MemberBasic", " RegisteredIp = '" + RegisteredIP + "'") as IList<EC_MemberBasic>;//执行查询
                if (null == result || result.Count == 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取最大的卖家编号
        /// </summary>
        /// <returns></returns>
        public int GetMaxBid()
        {
            try
            {
                IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>(" TOP (1) * ", " EC_MemberBasic ", string.Format(" MemberType <> '员工' and MemberType <> '管理员' and MemberType <> '买家会员'  ORDER BY bid DESC")) as IList<EC_MemberBasic>;//执行查询

                return (null == result || result.Count == 0) ? 1 : ((result[0].bid ?? 0) + 1);//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据卖家编号获得会员信息
        /// </summary>
        /// <param name="bid">卖家编号</param>
        /// <returns></returns>
        public EC_MemberBasic GetMemberBybid(int? bid)
        {
            try
            {
                EC_MemberBasic model = new EC_MemberBasic();
                if (bid <= 0)
                    return model;//错误数据返会空 

                IList<EC_MemberBasic> result = _dataBase.SelectModel<EC_MemberBasic>("*", "EC_MemberBasic", string.Format(" bid='{0}' and (MemberType ='印刷厂商' or MemberType='个人网店')", bid)) as IList<EC_MemberBasic>;//执行查询

                return (null == result || result.Count == 0) ? model : result[0];//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 更新会员询价编号
        /// </summary>
        /// <param name="memberId"></param>
        /// <param name="isShowInquiry"></param>
        /// <returns></returns>
        public bool UpdateIsShowInquiry(Guid memberId, int isShowInquiry)
        {
            bool isSuccess = false;
            string sqlStr = string.Empty;
            sqlStr = "UPDATE EC_MemberBasic SET IsShowInquiry=@isShowInquiry WHERE MemberId=@memberId ";
            SqlParameter[] pars = new SqlParameter[]{
                new SqlParameter("@isShowInquiry",isShowInquiry),
                new SqlParameter("@memberId",memberId)
            };
            try
            {
                int i = _dataBase.ExecuteSql(sqlStr, pars);
                if (i > 0)
                {
                    isSuccess = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return isSuccess;
        }

        /// <summary>
        /// 获取默认模版
        /// </summary>
        /// <returns></returns>
        public int GetDefaultStenciid()
        {
            try
            {
                string selTarget = " *  ";
                string fromSource = " Sys_FirmStencil  ";
                string condition = " IsDefault ='True' ";
                IList<Sys_FirmStencil> m_Sys_FirmStencilList = _dataBase.SelectModel<Model.Sys_FirmStencil>(selTarget, fromSource, condition);
                if (m_Sys_FirmStencilList != null && m_Sys_FirmStencilList.Count > 0)
                {
                    return m_Sys_FirmStencilList[0].Keyid.Value;
                }
                else
                {
                    return 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}