/**  
* OA_CorporateClientsDAL.cs
*
* 功 能： 合作客户数据访问接口实现类
* 类 名： OA_CorporateClientsDAL
*
* Ver    变更日期             负责人  变更内容
* ───────────────────────────────────
* V0.01  2013-4-2 15:11       吴辉      初版
* V0.02  2013-5-29 17:40      吴崎均    增加转换在线会员为厂商客户方法 
*
*
*
*
*
*
*/
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.IBaseDAL;
using System.Transactions;
using CY.Infrastructure.Common;
using System.Xml;
using System.Data.SqlTypes;

namespace CY.SQLDAL
{
    /// <summary>
    /// 合作客户数据访问接口实现类
    /// </summary>
    public class OA_CorporateClientsDAL : IOA_CorporateClientsDAL
    {

        private Database _dataBase = null;
        #region 常量
        /// <summary>
        /// 查询目标 
        /// </summary>
        const string SELECTTARGET = " t.* ";
        /// <summary>
        /// 查询来源
        /// </summary>
        const string FROMSOUCEBEFORE = " ( select a.*,f.*,b.Mobile as Mobile,b.Email as Email,b.CompanyPhone as CompanyPhone ,d_Shifouwanjie.Name as ShifouwanjieName,b.Province as Province,b.City as City ,b.County as County,d_DegreeImportanId.Name as DegreeImportanIdName,k.Name as CreditIdName,b.BusinessManagers as BusinessManagers,l.Name as SourcesInfoIdName ,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName from  [OA_CorporateClients] a join [OA_CustomerCommunications] b on a.Keyid=b.Keyid Left Join Sys_Dictionary as d_DegreeImportanId On (a.[DegreeImportanId]=d_DegreeImportanId.Keyid)  Left Join Sys_Dictionary as k On (a.[CreditId]=k.Keyid) Left Join Sys_Dictionary as l On (a.SourcesInfoId=l.Keyid) LEFT JOIN dbo.OA_Staff AS  d_OA_Staff ON a.BusinessManagerId=d_OA_Staff.Keyid LEFT JOIN dbo.OA_Staff AS  d_OA_Staff1  ON a.AccountManagerId=d_OA_Staff1.Keyid " +
         " Left Join Sys_Dictionary as d_Shifouwanjie On(d_Shifouwanjie.DicType= '客户完结状态' and a.[Shifouwanjie]= d_Shifouwanjie.MeanValue)  " +
            " LEFT JOIN( SELECT CustomerId AS Fid,Remark as XRemark, SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
        " SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
        " SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
        " SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
        " SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
        " SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial,  " +
        " COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord  WHERE CutomerType ='True' GROUP BY CustomerId,Remark) AS f ON CONVERT(VARCHAR(200),a.CustomerId)=f.Fid and a.FirmId = f.XRemark   where 0=0  ";
        const string FROMSOUCEEND = ") as t  ";
        /// <summary>
        /// 分页默认排序字段
        /// </summary>
        const string ORDERBY = " Keyid DESC ";
        #endregion
        public OA_CorporateClientsDAL()
        {
            _dataBase = new Database();
        }

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

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_CorporateClients trueModel = model as Model.OA_CorporateClients;
            if (trueModel == null)
            {
                return false;
            }

            SqlParameter par = null;
            if (trueModel.LastOrderTime.HasValue)
            {
                par = new SqlParameter("@LastOrderTime", trueModel.LastOrderTime.Value);
            }
            else
            {
                par = new SqlParameter("@LastOrderTime", DBNull.Value);
            }
            SqlParameter par1 = null;
            if (trueModel.ExpireTime.HasValue)
            {
                par1 = new SqlParameter("@ExpireTime", trueModel.ExpireTime.Value);
            }
            else
            {
                par1 = new SqlParameter("@ExpireTime", DBNull.Value);
            }

            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
               	    new SqlParameter("@Keyid",trueModel.Keyid),
					new SqlParameter("@FirmId",trueModel.FirmId),
					new SqlParameter("@CustomerId",trueModel.CustomerId),
					new SqlParameter("@CompanyName",trueModel.CompanyName),
					new SqlParameter("@CustomerIndustriesId",trueModel.CustomerIndustriesId),
					new SqlParameter("@CustomerTypeId",trueModel.CustomerTypeId),
					new SqlParameter("@SourcesInfoId",trueModel.SourcesInfoId),
					new SqlParameter("@DegreeImportanId",trueModel.DegreeImportanId),
					new SqlParameter("@AccountManagerId",trueModel.AccountManagerId),
					new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId),
					new SqlParameter("@CreateTime",trueModel.CreateTime),
					new SqlParameter("@CreditId",trueModel.CreditId),
					new SqlParameter("@LoginPhone",trueModel.LoginPhone),
					new SqlParameter("@CorporateWebsite",trueModel.CorporateWebsite),
					new SqlParameter("@BusinessAnalysisId",trueModel.BusinessAnalysisId),
					new SqlParameter("@Credit",trueModel.Credit),
					new SqlParameter("@IsLoginCorporateWeb",trueModel.IsLoginCorporateWeb),
					new SqlParameter("@Bank",trueModel.Bank),
					new SqlParameter("@TaxID",trueModel.TaxID),
					new SqlParameter("@AccountID",trueModel.AccountID),
					new SqlParameter("@OrderCount",trueModel.OrderCount),
					new SqlParameter("@OrderMoney",trueModel.OrderMoney),
					par,
					new SqlParameter("@Prepayments",trueModel.Prepayments),
					new SqlParameter("@IsOutsourcing",trueModel.IsOutsourcing),
					new SqlParameter("@OutVendorName",trueModel.OutVendorName),
					new SqlParameter("@IsPriority",trueModel.IsPriority),
					new SqlParameter("@CumulativePrepayments",trueModel.CumulativePrepayments),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator),
					new SqlParameter("@Remark",trueModel.Remark),
					new SqlParameter("@InquiryId",trueModel.InquiryId),
					new SqlParameter("@MemberId",trueModel.MemberId) ,
					new SqlParameter("@LoginPwd",trueModel.LoginPwd) ,
					new SqlParameter("@OutRate",trueModel.OutRate) ,
                    new SqlParameter("@Paytime",trueModel.Paytime) ,
                    new SqlParameter("@Paydays",trueModel.Paydays) ,
                     new SqlParameter("@Biddingcompany",trueModel.Biddingcompany) ,
                      new SqlParameter("@Contracttype",trueModel.Contracttype) ,
                       new SqlParameter("@Contractmoney",trueModel.Contractmoney) ,
                       par1,
                        new SqlParameter("@Jigoudaima",trueModel.Jigoudaima) ,
                         new SqlParameter("@Piaoju",trueModel.Piaoju) ,
                          new SqlParameter("@Kaipiaoziliao",trueModel.Kaipiaoziliao) ,
                           new SqlParameter("@Expiretype",trueModel.Expiretype) ,
                           new SqlParameter("@Shifouwanjie",trueModel.Shifouwanjie) ,
                           new SqlParameter("@Note",trueModel.Note) ,
            };
            try
            {
                _dataBase.Query("sp_OA_CorporateClients_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.OA_CorporateClients trueModel = model as Model.OA_CorporateClients;
            if (trueModel == null)
            {
                return false;
            }

            SqlParameter par = null;
            if (trueModel.LastOrderTime.HasValue)
            {
                par = new SqlParameter("@LastOrderTime", trueModel.LastOrderTime.Value);
            }
            else
            {
                par = new SqlParameter("@LastOrderTime", DBNull.Value);
            }
            SqlParameter par1 = null;
            if (trueModel.ExpireTime.HasValue)
            {
                par1 = new SqlParameter("@ExpireTime", trueModel.ExpireTime.Value);
            }
            else
            {
                par1 = new SqlParameter("@ExpireTime", DBNull.Value);
            }
            SqlParameter Biddingcompany = null;
            if (trueModel.Biddingcompany.HasValue)
            {
                Biddingcompany = new SqlParameter("@Biddingcompany", trueModel.Biddingcompany.Value);
            }
            else
            {
                Biddingcompany = new SqlParameter("@Biddingcompany", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
					new SqlParameter("@FirmId",trueModel.FirmId),
					new SqlParameter("@CustomerId",trueModel.CustomerId ?? 10000),
					new SqlParameter("@CompanyName",trueModel.CompanyName??""),
					new SqlParameter("@CustomerIndustriesId",trueModel.CustomerIndustriesId??0),
					new SqlParameter("@CustomerTypeId",trueModel.CustomerTypeId??0),
					new SqlParameter("@SourcesInfoId",trueModel.SourcesInfoId??0),
					new SqlParameter("@DegreeImportanId",trueModel.DegreeImportanId??0),
					new SqlParameter("@AccountManagerId",trueModel.AccountManagerId??0),
					new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId??0),
					new SqlParameter("@CreateTime",trueModel.CreateTime),
					new SqlParameter("@CreditId",trueModel.CreditId??0),
					new SqlParameter("@LoginPhone",trueModel.LoginPhone??""),
					new SqlParameter("@CorporateWebsite",trueModel.CorporateWebsite??""),
					new SqlParameter("@BusinessAnalysisId",trueModel.BusinessAnalysisId??0),
					new SqlParameter("@Credit",trueModel.Credit??0),
					new SqlParameter("@IsLoginCorporateWeb",trueModel.IsLoginCorporateWeb),
					new SqlParameter("@Bank",trueModel.Bank??""),
					new SqlParameter("@TaxID",trueModel.TaxID??""),
					new SqlParameter("@AccountID",trueModel.AccountID??""),
					new SqlParameter("@OrderCount",trueModel.OrderCount??0),
					new SqlParameter("@OrderMoney",trueModel.OrderMoney??0),
					par,
					new SqlParameter("@Prepayments",trueModel.Prepayments??0),
					new SqlParameter("@IsOutsourcing",trueModel.IsOutsourcing),
					new SqlParameter("@OutVendorName",trueModel.OutVendorName??""),
					new SqlParameter("@IsPriority",trueModel.IsPriority),
					new SqlParameter("@CumulativePrepayments",trueModel.CumulativePrepayments??0),
					new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
					new SqlParameter("@Operator",trueModel.Operator??""),
					new SqlParameter("@Remark",trueModel.Remark??""),
					new SqlParameter("@InquiryId",trueModel.InquiryId),
					new SqlParameter("@MemberId",trueModel.MemberId) ,
					new SqlParameter("@LoginPwd",trueModel.LoginPwd ?? "")  ,
					new SqlParameter("@OutRate",trueModel.OutRate ?? 0),
                    new SqlParameter("@Paytime",trueModel.Paytime) ,
                    new SqlParameter("@Paydays",trueModel.Paydays) ,
                     new SqlParameter("@Biddingcompany",trueModel.Biddingcompany??0) ,
                      new SqlParameter("@Contracttype",trueModel.Contracttype??0) ,
                       new SqlParameter("@Contractmoney",trueModel.Contractmoney??0) ,
                       par1,
                        new SqlParameter("@Jigoudaima",trueModel.Jigoudaima??"") ,
                         new SqlParameter("@Piaoju",trueModel.Piaoju??0) ,
                          new SqlParameter("@Kaipiaoziliao",trueModel.Kaipiaoziliao??"") ,
                           new SqlParameter("@Expiretype",trueModel.Expiretype??0) ,
                            new SqlParameter("@Shifouwanjie",trueModel.Shifouwanjie??0) ,
                              new SqlParameter("@Note",trueModel.Note??"") ,
            };
            try
            {
                _dataBase.Query("sp_OA_CorporateClients_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 转移客户
        /// </summary>
        /// <param name="BussinessManager"></param>
        /// <param name="CostomManager"></param>
        /// <param name="type"></param>
        /// <param name="keyids"></param>
        /// <returns></returns>
        public bool ShiftIntOrCus(int? BussinessManager, int? CostomManager, string type, string keyids)
        {
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
					new SqlParameter("@BussinessManager",BussinessManager),
					new SqlParameter("@CostomManager",CostomManager),
					new SqlParameter("@type",type) ,
					new SqlParameter("@keyids",keyids) 
            };
            try
            {
                _dataBase.Query("sp_ShiftIntOrCos_BussinessAndCustom", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

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

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_CorporateClients> 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.OA_CorporateClients>(pagination, SELECTTARGET, fromSouce, orderbys[0], resultOrderBy);
        }

        /// <summary>
        /// 统计金额
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public Model.OA_CorporateClients SumRecordMoney(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);//拼装条件
            IList<Model.OA_CorporateClients> m_OA_CorporateClientsList = _dataBase.SelectModel<Model.OA_CorporateClients>(" sum(t.Prepayments) as SumAllPrepayments,sum(t.Credit) as SumAllCredit,sum(t.OrderMoney) as SumAllOrderMoney", fromSouce);
            if (m_OA_CorporateClientsList != null && m_OA_CorporateClientsList.Count > 0)
                return m_OA_CorporateClientsList[0];
            else
                return null;
        }

        /// <summary>
        /// 分页查询外协厂商
        /// </summary>
        /// <param name="pagination"></param>
        /// <param name="MemberId"></param>
        /// <param name="OutVendorName"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_CorporateClients> SelectModelPage(Infrastructure.Query.Pagination pagination, Guid MemberId, string OutVendorName)
        {
            if (null == pagination || null == MemberId)
                return null;
            //query.Criteria 首个元素必须是排序字段，其值为结果排序字段
            string condition = " MemberId='" + MemberId + "' and IsOutsourcing ='True'";
            if (!string.IsNullOrEmpty(OutVendorName))
            {
                condition += " and OutVendorName like '%" + OutVendorName + "%'";
            }
            return _dataBase.SelectModelPage<Model.OA_CorporateClients>(pagination, "*", "OA_CorporateClients", " IsPriority DESC ", " IsPriority DESC ", condition);
        }

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

        /// <summary>
        /// 单个查询
        /// </summary>
        /// <param name="Keyid">编号</param>
        /// <returns></returns>
        public OA_CorporateClients SelectModelByKeyid(Guid Keyid)
        {
            if (Keyid == null)
                return null;//错误数据返会空 

            IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" a.*,b.QQ,b.CompanyPhone,b.Mobile,b.BusinessManagers ", " dbo.OA_CorporateClients AS a LEFT JOIN dbo.OA_CustomerCommunications AS b ON a.Keyid = b.Keyid ", string.Format(" a.Keyid='{0}'", Keyid)) as IList<OA_CorporateClients>;//执行查询

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

        /// <summary>
        /// 单个查询详细
        /// </summary>
        /// <param name="Keyid">编号</param>
        /// <returns></returns>
        public OA_CorporateClients SelectModelDetailByKeyid(Guid Keyid)
        {
            if (Keyid == null)
                return null;//错误数据返会空 

            string selectTarget = "t.*,d_SourcesInfoId.Name as SourcesInfoIdName,d_CustomerIndustriesId.Name as CustomerIndustriesIdName,d_CustomerTypeId.Name as CustomerTypeIdName,d_CreditId.Name as CreditIdName,d_Shifouwanjie.Name as ShifouwanjieName ,d_Piaoju.Name as PiaojuName ,d_Expiretype.Name as ExpiretypeName,d_Contracttype.Name as ContracttypeName,d_Biddingcompany.CompanyName as BiddingcompanyName ,d_PaytimeId.Name as Paytimename ,d_DegreeImportanId.Name as DegreeImportanIdName,d_BusinessAnalysisId.Name as BusinessAnalysisIdName ,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName";
            string fromSouce = "(select * From OA_CorporateClients  Where Keyid='" + Keyid + "' ) as t " +
                                        "Left Join Sys_Dictionary as d_SourcesInfoId On(t.[SourcesInfoId]=d_SourcesInfoId.Keyid) " +
                                        "Left Join Sys_Dictionary as d_CustomerIndustriesId On(t.[CustomerIndustriesId]=d_CustomerIndustriesId.Keyid) " +
                                        "Left Join Sys_Dictionary as d_CustomerTypeId On(t.[CustomerTypeId]=d_CustomerTypeId.Keyid) " +
                                        "Left Join Sys_Dictionary as d_CreditId On(t.[CreditId]=d_CreditId.Keyid) " +
                                        "Left Join Sys_Dictionary as d_PaytimeId On( d_PaytimeId.DicType='付款时间' and   t.[Paytime]=d_PaytimeId.MeanValue ) " +
                                        "Left Join Sys_Dictionary as d_DegreeImportanId On(t.[DegreeImportanId]=d_DegreeImportanId.Keyid) " +
                                        "Left Join Sys_Dictionary as d_BusinessAnalysisId On(t.[BusinessAnalysisId]=d_BusinessAnalysisId.Keyid) " +
                                        "LEFT JOIN dbo.OA_Staff AS  d_OA_Staff ON (t.BusinessManagerId=d_OA_Staff.Keyid)  " +
                                         "Left Join OA_CorporateClientsBiddingcompany as d_Biddingcompany On t.[Biddingcompany]=d_Biddingcompany.Keyid1  " +
                                          "Left Join Sys_Dictionary as d_Contracttype On( d_Contracttype.DicType='合同金额' and   t.[Contracttype]=d_Contracttype.MeanValue ) " +
                                           "Left Join Sys_Dictionary as d_Expiretype On( d_Expiretype.DicType='到期时间' and   t.[Expiretype]=d_Expiretype.MeanValue ) " +
                                            "Left Join Sys_Dictionary as d_Piaoju On( d_Piaoju.DicType='票据' and   t.[Piaoju]=d_Piaoju.MeanValue ) " +
                                              "Left Join Sys_Dictionary as d_Shifouwanjie On( d_Shifouwanjie.DicType='客户完结状态' and   t.[Shifouwanjie]=d_Shifouwanjie.MeanValue ) " +
                                        "LEFT JOIN dbo.OA_Staff AS  d_OA_Staff1  ON (t.AccountManagerId=d_OA_Staff1.Keyid)  ";

            IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(selectTarget, fromSouce) as IList<OA_CorporateClients>;//执行查询

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

        /// <summary>
        /// 单个查询
        /// </summary>
        /// <param name="FirmId">编号</param>
        /// <returns></returns>
        public OA_CorporateClients SelectModelByFirmId(Guid FirmId)
        {
            if (FirmId == null)
                return null;//错误数据返会空 

            IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}'  ORDER BY CustomerId DESC", FirmId)) as IList<OA_CorporateClients>;//执行查询

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

        /// <summary>
        /// 根据厂商编号查询全部会员
        /// </summary>
        /// <param name="FirmId">编号</param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClients> SelectListByFirmId(Guid FirmId)
        {
            if (FirmId == null)
                return null;//错误数据返会空 

            IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}'  ORDER BY CreateTime DESC", FirmId)) as IList<OA_CorporateClients>;//执行查询

            return result;//返回结果
        }

        /// <summary>
        /// 根据厂商编号查询全部外协厂商
        /// </summary>
        /// <param name="MemberId">编号</param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClients> SelectListByMemberId(Guid MemberId)
        {
            if (MemberId == null)
                return null;//错误数据返会空 

            IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" MemberId='{0}' and IsOutsourcing='True'  ORDER BY CreateTime DESC", MemberId)) as IList<OA_CorporateClients>;//执行查询

            return result;//返回结果
        }

        /// <summary>
        /// 根据会员编号查询所有已添加我为客户的厂商
        /// </summary>
        /// <param name="MemberId">编号</param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClients> SelectListByMyId(Guid MemberId)
        {
            if (MemberId == null)
                return null;//错误数据返会空 

            IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId IN (SELECT MemberId FROM dbo.EC_MemberBasic) and MemberId='{0}'  ORDER BY CreateTime DESC", MemberId)) as IList<OA_CorporateClients>;//执行查询

            return result;//返回结果
        }

        /// <summary>
        /// 新增合作客户
        /// </summary>
        /// <param name="rType"></param>
        /// <param name="m_OA_CustomerCommunications"></param>
        /// <param name="m_EC_AcceptWayByCustomers"></param>
        /// <returns></returns>
        public bool InsertModel(OA_CorporateClients m_OA_CorporateClients, OA_CustomerCommunications m_OA_CustomerCommunications, EC_AcceptWayByCustomers m_EC_AcceptWayByCustomers)
        {
            IUnitOfWork unit = new UnitOfWorkForSql();
            unit.RegisterNew(m_OA_CorporateClients, this);
            unit.RegisterNew(m_OA_CustomerCommunications, new OA_CustomerCommunicationsDAL(_dataBase));
            unit.RegisterNew(m_EC_AcceptWayByCustomers, new EC_AcceptWayByCustomersDAL(_dataBase));
            return unit.Commit();
        }

        /// <summary>
        /// 意向客户导入合作客户
        /// </summary>
        /// <param name="m_OA_CorporateClients"></param>
        /// <param name="m_OA_CustomerCommunications"></param>
        /// <param name="m_EC_AcceptWayByCustomers"></param>
        /// <param name="m_OA_IntentionCustomer"></param>
        /// <returns></returns>
        public bool IntIntoModel(OA_CorporateClients m_OA_CorporateClients, OA_CustomerCommunications m_OA_CustomerCommunications, EC_AcceptWayByCustomers m_EC_AcceptWayByCustomers, OA_IntentionCustomer m_OA_IntentionCustomer)
        {
            IUnitOfWork unit = new UnitOfWorkForSql();
            unit.RegisterNew(m_OA_CorporateClients, this);
            unit.RegisterNew(m_OA_CustomerCommunications, new OA_CustomerCommunicationsDAL(_dataBase));
            unit.RegisterNew(m_EC_AcceptWayByCustomers, new EC_AcceptWayByCustomersDAL(_dataBase));
            unit.RegisterRemoved(m_OA_IntentionCustomer, new OA_IntentionCustomerDAL(_dataBase));
            return unit.Commit();
        }

        /// <summary>
        /// 修改合作客户
        /// </summary>
        /// <param name="rType"></param>
        /// <returns></returns>
        public bool UpdateModel(OA_CorporateClients m_OA_CorporateClients, OA_CustomerCommunications m_OA_CustomerCommunications, EC_AcceptWayByCustomers m_EC_AcceptWayByCustomers)
        {
            IUnitOfWork unit = new UnitOfWorkForSql();
            unit.RegisterAmended(m_OA_CorporateClients, this);
            unit.RegisterAmended(m_OA_CustomerCommunications, new OA_CustomerCommunicationsDAL(_dataBase));
            unit.RegisterAmended(m_EC_AcceptWayByCustomers, new EC_AcceptWayByCustomersDAL(_dataBase));
            return unit.Commit();
        }

        /// <summary>
        /// 获取询价编号
        /// </summary>
        /// <param name="FirmId"></param>
        /// <param name="CustormId"></param>
        /// <returns></returns>
        public Guid GetInquiryId(Guid FirmId, Guid CustormId)
        {
            Guid InquiryId = new Guid();
            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and Keyid='{1}'", FirmId, CustormId)) as IList<OA_CorporateClients>;//执行查询
                if (null == result || result.Count == 0)
                {
                    EC_MemberBasicDAL dal_EC_MemberBasicDAL = new EC_MemberBasicDAL();
                    EC_MemberBasic m_EC_MemberBasic = dal_EC_MemberBasicDAL.GetMemberByMemberId(FirmId);
                    InquiryId = m_EC_MemberBasic.InquiryId;
                }
                else
                {
                    InquiryId = result[0].InquiryId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return InquiryId;
        }

        /// <summary>
        /// 根据会员id和厂商id获取客户编号
        /// </summary>
        /// <param name="FirmId"></param>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public Guid GetCustormId(Guid FirmId, Guid MemberId)
        {
            Guid CustormId = new Guid();
            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and MemberId='{1}'", FirmId, MemberId)) as IList<OA_CorporateClients>;//执行查询
                if (null == result || result.Count == 0)
                {
                    CustormId = Guid.Empty;
                }
                else
                {
                    CustormId = result[0].Keyid;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return CustormId;
        }

        /// <summary>
        /// 判断企业网站能否登陆成功
        /// </summary>
        /// <param name="LoginPhone"></param>
        /// <param name="PwdNum"></param>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public OA_CorporateClients IfHasMember(string LoginPhone, string PwdNum, Guid MemberId)
        {
            string selTarget = " * ";
            string fromSource = "  OA_CorporateClients  where LoginPhone='" + LoginPhone + "' and LoginPwd='" + PwdNum + "' and FirmId='" + MemberId + "' and IsLoginCorporateWeb='True' ";
            if (_dataBase.SelectModel<OA_CorporateClients>(selTarget, fromSource).Count() > 0)
            {
                return _dataBase.SelectModel<OA_CorporateClients>(selTarget, fromSource)[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获取总的预付款余额
        /// </summary>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public decimal GetAllPrepayments(Guid MemberId)
        {
            try
            {
                decimal allPrepayments = 0;
                string selTarget = " SUM(Prepayments) AS AllPrepayments ";
                string fromSource = "dbo.OA_CorporateClients WHERE FirmId='" + MemberId + "'";
                DataTable ds = _dataBase.SelectModel(selTarget, fromSource);
                if (ds.Rows.Count > 0)
                {
                    return Convert.ToDecimal(ds.Rows[0][0]);
                }
                return allPrepayments;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据厂商以及会员编号查询客户信息
        /// </summary>
        /// <param name="FirmId"></param>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public OA_CorporateClients SelectModelByFirmIdandMemberId(Guid FirmId, Guid MemberId)
        {
            OA_CorporateClients m_OA_CorporateClients = new OA_CorporateClients();
            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and MemberId='{1}'", FirmId, MemberId)) as IList<OA_CorporateClients>;//执行查询
                if (null == result || result.Count == 0)
                {
                    m_OA_CorporateClients = null;
                }
                else
                {
                    m_OA_CorporateClients = result[0];
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return m_OA_CorporateClients;
        }

        /// <summary>
        /// 根据厂商以及会员编号查询外协厂商
        /// </summary>
        /// <param name="FirmId"></param>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public OA_CorporateClients SelectOutsourceByFirmIdandMemberId(Guid FirmId, Guid MemberId)
        {
            OA_CorporateClients m_OA_CorporateClients = new OA_CorporateClients();
            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and MemberId='{1}' and IsOutsourcing ='True'", FirmId, MemberId)) as IList<OA_CorporateClients>;//执行查询
                if (null == result || result.Count == 0)
                {
                    m_OA_CorporateClients = null;
                }
                else
                {
                    m_OA_CorporateClients = result[0];
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return m_OA_CorporateClients;
        }

        /// <summary>
        /// 判断会员是否是厂商的客户
        /// </summary>
        /// <param name="FirmId"></param>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public bool IsCustormByFirmIdandMemberId(Guid FirmId, Guid MemberId)
        {
            bool Iscus = false;
            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>("*", "OA_CorporateClients", string.Format(" FirmId='{0}' and MemberId='{1}'", FirmId, MemberId)) as IList<OA_CorporateClients>;//执行查询
                if (null == result || result.Count == 0)
                {
                    Iscus = false;
                }
                else
                {
                    Iscus = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return Iscus;
        }

        /// <summary>
        /// 模糊查询外协公司名称
        /// </summary>
        /// <param name="CompanyName">公司名</param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClients> SelectListListByName(string CompanyName, Guid MemberId)
        {
            if (string.IsNullOrEmpty(CompanyName))
                return null;//错误数据返会空

            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" top 5 * ", "OA_CorporateClients", string.Format(" OutVendorName like '%{0}%' and MemberId ='{1}' AND Keyid <> FirmId ", CompanyName, MemberId)) as IList<OA_CorporateClients>;//执行查询
                return result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 模糊查询客户公司名称
        /// </summary>
        /// <param name="CompanyName"></param>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClients> SelectCustomListByName(string CompanyName, Guid MemberId)
        {
            if (string.IsNullOrEmpty(CompanyName))
                return null;//错误数据返会空

            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" top 5 * ", "OA_CorporateClients", string.Format(" CompanyName = '{0}' and FirmId ='{1}'", CompanyName, MemberId)) as IList<OA_CorporateClients>;//执行查询
                return result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据客户编号获取客户
        /// </summary>
        /// <param name="CustomId">客户编号</param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClients> SelectListListByCustomId(string CustomId, Guid MemberId)
        {
            if (string.IsNullOrEmpty(CustomId))
                return null;//错误数据返会空

            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" a.*,f.*,b.Province as Province, b.City as City,b.County as County , b.DetailedAddress as DetailedAddress,b.Postcode as Postcode,b.Mobile as Mobile,b.CompanyPhone as CompanyPhone,b.QQ as QQ,b.BusinessManagers as BusinessManagers,b.Email as Email,b.LegalRepresentative as LegalRepresentative,d_DegreeImportanId.Name as DegreeImportanIdName,l.Name as SourcesInfoIdName ,d_OA_Staff.Name AS BusinessmanagerName,d_OA_Staff1.Name AS AccountmanagerName,d_CreditId.Name as CreditIdName", "OA_CorporateClients as a left join OA_CustomerCommunications as b on a.Keyid = b.Keyid Left Join Sys_Dictionary as d_DegreeImportanId On (a.[DegreeImportanId]=d_DegreeImportanId.Keyid) Left Join Sys_Dictionary as k On (a.[SourcesInfoId]=k.Keyid) Left Join Sys_Dictionary as l On (a.SourcesInfoId=l.Keyid) Left Join Sys_Dictionary as d_CreditId On(a.[CreditId]=d_CreditId.Keyid)  LEFT JOIN dbo.OA_Staff AS  d_OA_Staff ON a.BusinessManagerId=d_OA_Staff.Keyid LEFT JOIN dbo.OA_Staff AS  d_OA_Staff1  ON a.AccountManagerId=d_OA_Staff1.Keyid " +
        " LEFT JOIN( SELECT CustomerId AS Fid, SUM(CASE when AccessTypeId='1' THEN 1 ELSE 0 END) AS Nupdoor , " +
        " SUM(CASE when AccessTypeId='2' THEN 1 ELSE 0 END) AS Nphone, " +
        " SUM(CASE when AccessTypeId='3' THEN 1 ELSE 0 END) AS Nqq, " +
        " SUM(CASE when AccessTypeId='4' THEN 1 ELSE 0 END) AS Nmessage, " +
        " SUM(CASE when AccessTypeId='5' THEN 1 ELSE 0 END) AS NDirectMail, " +
        " SUM(CASE when AccessTypeId='6' THEN 1 ELSE 0 END) AS Nemial,  " +
        " COUNT(*) AS Nall FROM dbo.OA_CustomerAccessRecord  WHERE CutomerType ='True' GROUP BY CustomerId) AS f ON CONVERT(VARCHAR(200),a.CustomerId)=f.Fid  ", string.Format(" CustomerId in ({0}) and a.FirmId ='{1}' order by CustomerId ASC", CustomId, MemberId)) as IList<OA_CorporateClients>;//执行查询
                return result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 设置自动外协
        /// </summary>
        /// <param name="Keyid"></param>
        /// <param name="i"></param>
        /// <returns></returns>
        public bool EditModel(Guid Keyid, string i)
        {
            try
            {
                bool IsSuccess = true;
                OA_CorporateClients m_OA_CorporateClients = SelectModelByKeyid(Keyid);
                m_OA_CorporateClients.IsPriority = (i == "1");

                List<OA_CorporateClients> m_OA_CorporateClientsList = SelectListByMemberId(m_OA_CorporateClients.MemberId) as List<OA_CorporateClients>;
                using (TransactionScope t_TransactionScope = new TransactionScope())
                {
                    foreach (var item in m_OA_CorporateClientsList)
                    {
                        if (IsSuccess)
                        {
                            item.IsPriority = false;
                            IsSuccess = UpdateModel(item);
                        }
                        else
                            return false;
                    }
                    if (IsSuccess)
                        IsSuccess = UpdateModel(m_OA_CorporateClients);
                    if (IsSuccess)
                        t_TransactionScope.Complete();
                }
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 转化在线用户为某厂商客户方法
        /// </summary>
        /// <param name="firmId">厂商编号</param>
        /// <param name="memberId">会员编号</param>
        /// <param name="theOperator">操作人</param>
        /// <returns></returns>
        public int ConvertToCorporateClient(Guid firmId, Guid memberId, string theOperator)
        {
            List<SqlParameter> paramList = new List<SqlParameter>() { 
                new SqlParameter(){ParameterName="@returnValue",SqlDbType=SqlDbType.Int,Direction=ParameterDirection.ReturnValue},
                new SqlParameter(){ParameterName="@FirmId",Value=firmId,SqlDbType=SqlDbType.UniqueIdentifier,DbType=DbType.Guid},
                new SqlParameter(){ParameterName="@MemberId",Value=memberId,SqlDbType=SqlDbType.UniqueIdentifier,DbType=DbType.Guid},
                new SqlParameter(){ParameterName="@Operator",Value=theOperator,SqlDbType=SqlDbType.VarChar,DbType=DbType.String,Size=20}
            };

            try
            {

                _dataBase.Query("sp_ConvertMemberToCorporateClient", CommandType.StoredProcedure, paramList.ToArray());
                return (int)paramList[0].Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 订单收款，外协付款使用预付款
        /// </summary>
        /// <param name="MemberId">订单收款传客户编号，外协付款传自己的会员编号</param>
        /// <param name="FirmId">订单收款传自己的会员编号，外协付款传外协厂商的编号</param>
        /// <param name="Money">操作金额</param>
        /// <param name="SubjectName">科目名称</param>
        /// <param name="Operator">操作人</param>
        /// <param name="OperatTypeId">操作类型</param>
        /// <returns></returns>
        public bool OrderReciveMoney(Guid MemberId, Guid FirmId, decimal? Money, string SubjectName, string Operator, int? OperatTypeId)
        {
            try
            {

                OA_CorporateClients m_OA_CorporateClients = SelectModelByFirmIdandMemberId(FirmId, MemberId);

                if (m_OA_CorporateClients.Prepayments >= Money)
                {
                    m_OA_CorporateClients.Prepayments -= Money;
                }
                else
                {
                    m_OA_CorporateClients.Credit -= (Money - m_OA_CorporateClients.Prepayments);
                    m_OA_CorporateClients.Prepayments = 0;
                }
                m_OA_CorporateClients.Operator = Operator;
                m_OA_CorporateClients.LastUpdateTime = DateTime.Now;
                bool isWin = UpdateModel(m_OA_CorporateClients);
                if (!isWin)
                    return isWin;
                else
                    ;
                Sys_DictionaryDAL dal_Sys_DictionaryDAL = new Sys_DictionaryDAL(_dataBase);
                OA_AdvanceMoneyRecord m_OA_AdvanceMoneyRecord = new OA_AdvanceMoneyRecord();
                m_OA_AdvanceMoneyRecord.CustomerId = m_OA_CorporateClients.Keyid;
                m_OA_AdvanceMoneyRecord.OperatTypeId = dal_Sys_DictionaryDAL.GetNameByMeanValue(OperatTypeId, "客户预付款明细类型");
                m_OA_AdvanceMoneyRecord.OperatMoney = Money;
                m_OA_AdvanceMoneyRecord.SubjectName = SubjectName;
                m_OA_AdvanceMoneyRecord.AccountType = "";
                m_OA_AdvanceMoneyRecord.AccountName = "";
                m_OA_AdvanceMoneyRecord.LastUpdateTime = DateTime.Now;
                m_OA_AdvanceMoneyRecord.Operator = Operator;
                m_OA_AdvanceMoneyRecord.Remark = "";
                OA_AdvanceMoneyRecordDAL dal_OA_AdvanceMoneyRecordDAL = new OA_AdvanceMoneyRecordDAL(_dataBase);
                isWin = dal_OA_AdvanceMoneyRecordDAL.InserModel(m_OA_AdvanceMoneyRecord);
                if (!isWin)
                    return isWin;
                else
                {
                };
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 检测厂商是否存在
        /// </summary>
        /// <param name="CompanyName"></param>
        /// <param name="FirmId"></param>
        /// <param name="Keyid"></param>
        /// <returns></returns>
        public bool isExistCompanyName(String CompanyName, Guid FirmId, Guid Keyid)
        {
            try
            {
                String selectTarget = " * ";
                string fromSouce = "OA_CorporateClients where CompanyName='" + CompanyName + "' and FirmId='" + FirmId + "'";
                List<OA_CorporateClients> m_OA_CorporateClientsrList = _dataBase.SelectModel<OA_CorporateClients>(selectTarget, fromSouce) as List<OA_CorporateClients>;
                if (m_OA_CorporateClientsrList != null && m_OA_CorporateClientsrList.Count() > 0)
                {
                    if (m_OA_CorporateClientsrList[0].Keyid == Keyid)
                        return false;
                    else
                        return true;
                }
                else
                    return false;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 合并客户
        /// </summary>
        /// <param name="DefaultId"></param>
        /// <param name="DelId"></param>
        /// <param name="InquiryId"></param>
        /// <returns></returns>
        public bool MergerCustom(Guid DefaultId, Guid DelId, Guid InquiryId)
        {
            int returnState = 0;
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
               	    new SqlParameter("@DefaultId",DefaultId),
					new SqlParameter("@DelId",DelId),
					new SqlParameter("@InquiryId",InquiryId),
                    new SqlParameter("@returnState", returnState){SqlDbType=System.Data.SqlDbType.Int, Direction=ParameterDirection.Output}
            };
            try
            {
                _dataBase.Query("sp_CorporateClientsMerger", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
                if (MyConvert.ConvertToInt32(sqlParms[3].Value) <= 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据个人网店编号查询自动外协厂商
        /// </summary>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public OA_CorporateClients GetDefaultOutFirm(Guid MemberId)
        {
            OA_CorporateClients m_OA_CorporateClients = new OA_CorporateClients();
            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" * ", " dbo.OA_CorporateClients AS a LEFT JOIN dbo.EC_MemberBasic AS b ON a.MemberId = b.MemberId ", string.Format(" a.MemberId='{0}' and IsOutsourcing ='True' and IsPriority ='True' AND b.MemberType='个人网店' ", MemberId)) as IList<OA_CorporateClients>;//执行查询
                if (null == result || result.Count == 0)
                {
                    m_OA_CorporateClients = null;
                }
                else
                {
                    m_OA_CorporateClients = result[0];
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return m_OA_CorporateClients;
        }

        /// <summary>
        /// 根据会员ID和外协厂商名称查询外协厂商
        /// </summary>
        /// <param name="MemberId"></param>
        /// <returns></returns>
        public OA_CorporateClients GetModelByMemberIdAndName(Guid MemberId,string OutVendorName)
        {
            OA_CorporateClients m_OA_CorporateClients = new OA_CorporateClients();
            try
            {
                IList<OA_CorporateClients> result = _dataBase.SelectModel<OA_CorporateClients>(" * ", " dbo.OA_CorporateClients", string.Format(" MemberId='{0}' and IsOutsourcing ='True' and OutVendorName='{1}' ", MemberId, OutVendorName)) as IList<OA_CorporateClients>;//执行查询
                if (null == result || result.Count == 0)
                {
                    m_OA_CorporateClients = null;
                }
                else
                {
                    m_OA_CorporateClients = result[0];
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return m_OA_CorporateClients;
        }


        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertModelVisit(CY.Model.OA_CorporateClientsVisit trueModel)
        {
            //Model.OA_CorporateClients trueModel = model as Model.OA_CorporateClientsVist;
            if (trueModel == null)
            {
                return false;
            }

            SqlParameter par = null;
            if (trueModel.VisitTime.HasValue)
            {
                par = new SqlParameter("@VisitTime", trueModel.VisitTime.Value);
            }
            else
            {
                par = new SqlParameter("@VisitTime", DBNull.Value);
            }
            SqlParameter par1 = null;
            if (trueModel.CreateTime.HasValue)
            {
                par1 = new SqlParameter("@CreateTime", trueModel.CreateTime.Value);
            }
            else
            {
                par1 = new SqlParameter("@CreateTime", DBNull.Value);
            }
            SqlParameter par2 = null;
            if (trueModel.LastUpdateTime.HasValue)
            {
                par2 = new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime.Value);
            }
            else
            {
                par2 = new SqlParameter("@LastUpdateTime", DBNull.Value);
            }

            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                       new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@CorporateClientsid",trueModel.CorporateClientsid),
                    new SqlParameter("@VisitTime",trueModel.VisitTime),
                    new SqlParameter("@Remark",trueModel.Remark),
                    new SqlParameter("@Creater",trueModel.Creater),
                    new SqlParameter("@CreateTime",trueModel.CreateTime),
                    new SqlParameter("@Updater",trueModel.Updater),
                    new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
                  
            };
            try
            {
                _dataBase.Query("sp_OA_CorporateClientsVisit_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModelVisit(CY.Model.OA_CorporateClientsVisit trueModel)
        {
            if (trueModel == null)
            {
                return false;
            }

            SqlParameter par = null;
            if (trueModel.VisitTime.HasValue)
            {
                par = new SqlParameter("@VisitTime", trueModel.VisitTime.Value);
            }
            else
            {
                par = new SqlParameter("@VisitTime", DBNull.Value);
            }
            SqlParameter par1 = null;
            if (trueModel.CreateTime.HasValue)
            {
                par1 = new SqlParameter("@CreateTime", trueModel.CreateTime.Value);
            }
            else
            {
                par1 = new SqlParameter("@CreateTime", DBNull.Value);
            }
            SqlParameter par2 = null;
            if (trueModel.LastUpdateTime.HasValue)
            {
                par2 = new SqlParameter("@LastUpdateTime", trueModel.LastUpdateTime.Value);
            }
            else
            {
                par2 = new SqlParameter("@LastUpdateTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                     new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@CorporateClientsid",trueModel.CorporateClientsid),
                    new SqlParameter("@VisitTime",trueModel.VisitTime),
                    new SqlParameter("@Remark",trueModel.Remark),
                    new SqlParameter("@Creater",trueModel.Creater),
                    new SqlParameter("@CreateTime",trueModel.CreateTime),
                    new SqlParameter("@Updater",trueModel.Updater),
                    new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
            };
            try
            {
                _dataBase.Query("sp_OA_CorporateClientsVisit_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }



        /// <summary>
        /// 根据客户id查询拜访记录
        /// </summary>
        /// <param name="CustomId">编号</param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClientsVisit> SelectVisitListByCorId(Guid CustomId)
        {
            if (CustomId == null)
                return null;//错误数据返会空

            try
            {
                IList<OA_CorporateClientsVisit> result = _dataBase.SelectModel<OA_CorporateClientsVisit>("  a.*,b.[Name] as CreaterName ", "OA_CorporateClientsVisit a left join EC_MemberBasic b on a.Creater = b.MemberId ", string.Format(" CorporateClientsid = '{0}'  order by a.[VisitTime] desc", CustomId)) as IList<OA_CorporateClientsVisit>;//执行查询
                return result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertModelPrint(CY.Model.OA_CorporateClientsPrint trueModel)
        {
            //Model.OA_CorporateClients trueModel = model as Model.OA_CorporateClientsVist;
            if (trueModel == null)
            {
                return false;
            }

           

            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter(){ ParameterName ="@Keyid", SqlDbType=System.Data.SqlDbType.Int, DbType=System.Data.DbType.Int32, Direction=ParameterDirection.Output},
                    new SqlParameter("@BusinessManagers",trueModel.BusinessManagers),
                    new SqlParameter("@ManagersMobile",trueModel.@ManagersMobile),
                    new SqlParameter("@ManagersQQ",trueModel.ManagersQQ),

                     new SqlParameter("@Xinge",trueModel.Xinge),
                      new SqlParameter("@DocumentName",trueModel.DocumentName),
                       new SqlParameter("@UnitPrice",trueModel.UnitPrice),
                        new SqlParameter("@SumPrice",trueModel.SumPrice),
                         new SqlParameter("@Deliveryrequirements",trueModel.Deliveryrequirements),
                          new SqlParameter("@CorporateClientsid",trueModel.CorporateClientsid),

                    new SqlParameter("@Creater",trueModel.Creater),
                    new SqlParameter("@CreateTime",trueModel.CreateTime),
                    new SqlParameter("@Updater",trueModel.Updater),
                    new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),


            };
            try
            {
                _dataBase.Query("sp_OA_CorporateClientsPrint_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
                trueModel.Keyid = CY.Infrastructure.Common.MyConvert.ConvertToInt32(sqlParms[0].Value).Value;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModelPrint(CY.Model.OA_CorporateClientsPrint trueModel)
        {
            if (trueModel == null)
            {
                return false;
            }

           
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {     new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@BusinessManagers",trueModel.BusinessManagers),
                    new SqlParameter("@ManagersMobile",trueModel.@ManagersMobile),
                    new SqlParameter("@ManagersQQ",trueModel.ManagersQQ),

                     new SqlParameter("@Xinge",trueModel.Xinge),
                      new SqlParameter("@DocumentName",trueModel.DocumentName),
                       new SqlParameter("@UnitPrice",trueModel.UnitPrice),
                        new SqlParameter("@SumPrice",trueModel.SumPrice),
                         new SqlParameter("@Deliveryrequirements",trueModel.Deliveryrequirements),
                          new SqlParameter("@CorporateClientsid",trueModel.CorporateClientsid),

                    new SqlParameter("@Creater",trueModel.Creater),
                    new SqlParameter("@CreateTime",trueModel.CreateTime),
                    new SqlParameter("@Updater",trueModel.Updater),
                    new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),

                     
            };
            try
            {
                _dataBase.Query("sp_OA_CorporateClientsPrint_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }



        /// <summary>
        /// 根据客户id查询订单需求
        /// </summary>
        /// <param name="CustomId">编号</param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClientsPrint> SelectPrintListByCorId(Guid CustomId)
        {
            if (CustomId == null)
                return null;//错误数据返会空

            try
            {
                IList<OA_CorporateClientsPrint> result = _dataBase.SelectModel<OA_CorporateClientsPrint>("  a.*,b.[Name] as CreaterName,c.PrintParameter ", "OA_CorporateClientsPrint a left join EC_MemberBasic b on a.Creater = b.MemberId  left join OA_CorporateClientsParameter c on a.Keyid = c.TargetId  ", string.Format(" CorporateClientsid = '{0}'  order by a.[CreateTime] desc", CustomId)) as IList<OA_CorporateClientsPrint>;//执行查询
                return result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据客户id查询订单需求
        /// </summary>
        /// <param name="CustomId">编号</param>
        /// <returns></returns>
        public OA_CorporateClientsPrint SelectPrintListByPrintId(int Keyid)
        {
            if (Keyid == 0)
                new OA_CorporateClientsPrint();

            try
            {
                IList<OA_CorporateClientsPrint> result = _dataBase.SelectModel<OA_CorporateClientsPrint>("  a.*,b.[Name] as CreaterName,c.PrintParameter ", "OA_CorporateClientsPrint a left join EC_MemberBasic b on a.Creater = b.MemberId  left join OA_CorporateClientsParameter c on a.Keyid = c.TargetId  ", string.Format(" Keyid = '{0}'  ", Keyid)) as IList<OA_CorporateClientsPrint>;//执行查询
                if(result!=null&& result.Count > 0)
                {
                    return result[0];//返回结果

                }
                else
                {
                    new OA_CorporateClientsPrint();
                }
               
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return new OA_CorporateClientsPrint();
        }


        /// <summary>
        /// 添加订单印刷选项
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertModelParameter(CY.Model.OA_CorporateClientsParameter trueModel)
        {
            if (trueModel == null)
            {
                return false;
            }

            using (XmlTextReader rdr = new XmlTextReader(trueModel.PrintParameter, XmlNodeType.Document, null))
            {

                SqlXml sqlXml = new SqlXml(rdr);
                SqlParameter printPar = new SqlParameter("@PrintParameter", SqlDbType.Xml, sqlXml.Value.Length);
                printPar.Value = sqlXml;
                SqlParameter[] parameters = {
                    new SqlParameter("@TargetId", SqlDbType.Int,4),
                    printPar
                };
                parameters[0].Value = trueModel.TargetId;
                try
                {
                    _dataBase.Query("sp_OA_CorporateClientsParameter_Insert", CommandType.StoredProcedure, parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return true;
        }
        /// <summary>
        /// 修改订单印刷选项
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModelParameter(CY.Model.OA_CorporateClientsParameter trueModel)
        {
            
            if (trueModel == null)
            {
                return false;
            }

            using (XmlTextReader rdr = new XmlTextReader(trueModel.PrintParameter, XmlNodeType.Document, null))
            {

                SqlXml sqlXml = new SqlXml(rdr);
                SqlParameter printPar = new SqlParameter("@PrintParameter", SqlDbType.Xml, sqlXml.Value.Length);
                printPar.Value = sqlXml;
                SqlParameter[] parameters = {
                    new SqlParameter("@TargetId", SqlDbType.Int,4),
                    printPar
                };
                parameters[0].Value = trueModel.TargetId;
                try
                {
                    _dataBase.Query("sp_OA_CorporateClientsParameter_Update", CommandType.StoredProcedure, parameters);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return true;

        }


        /// <summary>
        /// 修改订单印刷选项
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InsertModelBiddingcompany(CY.Model.OA_CorporateClients trueModel)
        {

            if (trueModel == null)
            {
                return false;
            }


            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                       new SqlParameter("@FirmId",trueModel.FirmId),
                    new SqlParameter("@CompanyName",trueModel.CompanyName),
                    new SqlParameter("@CreateTime",trueModel.CreateTime),

            };

            try
            {
                _dataBase.ExecuteSql("INSERT INTO  [OA_CorporateClientsBiddingcompany]  ([FirmId] ,[CompanyName],[CreateTime]) VALUES (@FirmId,@CompanyName,@CreateTime)", sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return true;

        }


        /// <summary>
        /// 全部查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_CorporateClients> SelectBiddingcompanyByFirmId(Guid FirmId)
        {
            return _dataBase.SelectModel<Model.OA_CorporateClients>(" * ", " OA_CorporateClientsBiddingcompany ", string.Format(" FirmId = '{0}'  order by [CreateTime] ", FirmId)) as IList<Model.OA_CorporateClients>;//执行查询
        }



        /// <summary>
        /// 根据客户id查询拜访记录
        /// </summary>
        /// <param name="CustomId">编号</param>
        /// <returns></returns>
        public IEnumerable<OA_CorporateClientsVisit> SelectVisitListByBuzAndTime(int? BusinessManagerId, string chaxuntime)
        {
            if (BusinessManagerId == null || string.IsNullOrEmpty(chaxuntime))
                return null;//错误数据返会空

            try
            {
                IList<OA_CorporateClientsVisit> result = _dataBase.SelectModel<OA_CorporateClientsVisit>("  a.*,b.[Name] as CreaterName,oc.[CompanyName] as CorporateClientName ", " OA_CorporateClientsVisit a inner join OA_CorporateClients oc on a.[CorporateClientsid] = oc.Keyid  inner join [OA_Staff] os  on a.[Creater] = os.MemberId  left join EC_MemberBasic b on a.Creater = b.MemberId  ", " os.[Keyid] = '" + BusinessManagerId + "' and DATEDIFF(MONTH,a.[VisitTime],'" + chaxuntime + "') =0  order by oc.[CompanyName] asc ,a.[VisitTime] desc") as IList<OA_CorporateClientsVisit>;//执行查询
                return result;//返回结果
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}