/**  
* EC_OrderDAL.cs
*
* 功 能： 订单基本信息数据访问接口实现类
* 类 名： EC_OrderDAL
*
* Ver    变更日期             负责人  变更内容
* ───────────────────────────────────
* V0.01  2013-4-23 10:09    吴崎均    更改InsertModel以及UpdateModel以与存储相匹配
* V0.02  2013-4-23 16:43    吴崎均    增加新增订单方法
* V0.03  2013-4-24 11:03    吴崎均    增加修改订单信息方法，更新新增订单方法使之需要支付时直接调用订单支付方法
* V0.04  2013-4-26 9:53     吴崎均    修改NewOrder方法 增加订单收货方式参数的添加
* V0.05  2013-4-28 11:16    吴崎均    增加orderBasicDal几个方法的转调
* V0.06  2013-4-28 11:41    吴崎均    增加根据编号获取订单基本信息方法
* V0.07  2013-5-2 11:43     吴崎均    修改NewOrder方法增加秒杀订单编号使秒杀下单扣除秒杀数量能与下单同步进行，重载一般新建订单方法
* V0.08  2013-5-6 14:00     吴崎均    修改NewOrder方法，增加参数“订单印刷对象”
* V0.09  2013-5-21 14:26    吴崎均    修改NewOrder方法,在订单添加结束其它操作未处理前增加下单操作记录
*
*
*/
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.Model.Pay;
using System.Transactions;

namespace CY.SQLDAL
{
    /// <summary>
    /// 订单操作访问接口实现类
    /// </summary>
    public class EC_OrderDAL : IEC_OrderDAL
    {

        private Database _dataBase = null;
        EC_OrderBasicDAL _eC_OrderBasicDAL = null;
        EC_OrderExtendDAL _eC_OrderExtendDAL = null;
        Pay_TradingSingleDAL _pay_TradingSingleDAL = null;
        EC_AcceptWayByOrderDAL _eC_AcceptWayByOrderDAL = null;
        EC_SeckillBusinessDAL _eC_SeckillBusinessDAL = null;
        EC_OrderPrintParameterDAL _eC_OrderPrintParameterDAL = null;
        EC_OrderOperateDAL _eC_OrderOperateDAL = null;
        EC_MemberBasicDAL _eC_MemberBasicDAL = null;
        OA_CorporateClientsDAL _oA_CorporateClientsDAL = null;
        public EC_OrderDAL()
        {
            _dataBase = new Database();
            _eC_OrderBasicDAL = new EC_OrderBasicDAL(_dataBase);

        }

        /// <summary>
        /// 新建订单(全，含秒杀)
        /// </summary>
        /// <param name="orderBasic">订单基础信息</param>
        /// <param name="orderExtend">订单扩展信息</param>
        /// <param name="tradingSingle">交易单</param>
        /// <param name="acceptWayByOrder">订单收货方式</param>
        /// <param name="payRequest">支付请求(可空)</param>
        /// <param name="commission">订单受理佣金(传入值大于0时会调用受理订单进行受理)</param>
        /// <param name="seckillId">秒杀业务编号(传入值大于0时会减少秒杀数量1)</param>
        /// <param name="orderPrintParameter">印刷参数对象(秒杀订单不传入)</param>
        /// <returns></returns>
        public bool NewOrder(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, Pay_TradingSingle tradingSingle, EC_AcceptWayByOrder acceptWayByOrder, Pay_Request payRequest = null, double commission = 0, int seckillId = 0, EC_OrderPrintParameter orderPrintParameter = null)
        {
            try
            {
                _eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
                _pay_TradingSingleDAL = new Pay_TradingSingleDAL(_dataBase);
                _eC_AcceptWayByOrderDAL = new EC_AcceptWayByOrderDAL(_dataBase);
                _eC_OrderOperateDAL = new EC_OrderOperateDAL(_dataBase);
                _eC_MemberBasicDAL = new EC_MemberBasicDAL(_dataBase);
                _oA_CorporateClientsDAL = new OA_CorporateClientsDAL(_dataBase);
                using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
                {
                    bool isWin = false;
                    isWin = _eC_OrderBasicDAL.InserModel(orderBasic);
                    if (!isWin)
                    {
                        return false;
                    }
                    acceptWayByOrder.TargetId = tradingSingle.OrderId = orderExtend.Keyid = orderBasic.Keyid;//获取返回的订单唯一编号
                    isWin = _eC_OrderExtendDAL.InserModel(orderExtend);
                    if (!isWin)
                    {
                        return false;
                    }

                    isWin = _eC_AcceptWayByOrderDAL.InserModel(acceptWayByOrder);
                    if (!isWin)
                    {
                        return false;
                    }
                    isWin = _pay_TradingSingleDAL.InserModel(tradingSingle);
                    if (!isWin)
                    {
                        return false;
                    }

                    //增加下单操作记录
                    isWin = _eC_OrderOperateDAL.InserModel(new EC_OrderOperate()
                    {
                        OperateType = 1,
                        Operator = orderBasic.Operator,
                        OrderId = orderBasic.Keyid,
                        Remark = string.Empty
                    });
                    if (!isWin)
                    {
                        return false;
                    }

                    if (null != payRequest)//有支付请求则调用支付订单方法
                    {
                        //传入的payRequest.Subject必须为格式化器 格式例如:{0},{1},{2}
                        //0:Keyid,1:SellerOrderId,2:DocumentName
                        payRequest.Subject = string.Format(payRequest.Subject, orderBasic.Keyid, orderBasic.SellerOrderId, orderBasic.DocumentName);
                        isWin = _eC_OrderBasicDAL.PayOrder(orderBasic.Keyid.Value, payRequest, commission, 0);
                    }

                    if (0 < seckillId)
                    {
                        _eC_SeckillBusinessDAL = new EC_SeckillBusinessDAL(_dataBase);
                        //若有秒杀业务编号则扣除其秒杀数量1
                        isWin = _eC_SeckillBusinessDAL.DecreaseSeckillNum(seckillId, 1, orderBasic.BuyerId);
                    }
                    if (!isWin)
                    {
                        return false;
                    }
                    if (null != orderPrintParameter)
                    {
                        _eC_OrderPrintParameterDAL = new EC_OrderPrintParameterDAL(_dataBase);
                        orderPrintParameter.OrderId = orderBasic.Keyid.Value;
                        isWin = _eC_OrderPrintParameterDAL.InserModel(orderPrintParameter);
                    }

                    if (!isWin)
                    {
                        return false;
                    }
                    else
                        tran.Complete();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }


        /// <summary>
        /// 新建订单(全，含秒杀)批量
        /// </summary>
        /// <param name="orderBasic">订单基础信息</param>
        /// <param name="orderExtend">订单扩展信息</param>
        /// <param name="tradingSingle">交易单</param>
        /// <param name="acceptWayByOrder">订单收货方式</param>
        /// <param name="payRequest">支付请求(可空)</param>
        /// <param name="commission">订单受理佣金(传入值大于0时会调用受理订单进行受理)</param>
        /// <param name="seckillId">秒杀业务编号(传入值大于0时会减少秒杀数量1)</param>
        /// <param name="orderPrintParameter">印刷参数对象(秒杀订单不传入)</param>
        /// <returns></returns>
        public bool NewOrderpiliang(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, Pay_TradingSingle tradingSingle, EC_AcceptWayByOrder acceptWayByOrder, Pay_Request payRequest = null, double commission = 0, int seckillId = 0, EC_OrderPrintParameter orderPrintParameter = null)
        {
            try
            {
                _eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
                _pay_TradingSingleDAL = new Pay_TradingSingleDAL(_dataBase);
                _eC_AcceptWayByOrderDAL = new EC_AcceptWayByOrderDAL(_dataBase);
                _eC_OrderOperateDAL = new EC_OrderOperateDAL(_dataBase);
                _eC_MemberBasicDAL = new EC_MemberBasicDAL(_dataBase);
                _oA_CorporateClientsDAL = new OA_CorporateClientsDAL(_dataBase);
                //using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
                //{
                    bool isWin = false;
                    isWin = _eC_OrderBasicDAL.InserModel(orderBasic);
                    if (!isWin)
                    {
                        return false;
                    }
                    acceptWayByOrder.TargetId = tradingSingle.OrderId = orderExtend.Keyid = orderBasic.Keyid;//获取返回的订单唯一编号
                    isWin = _eC_OrderExtendDAL.InserModel(orderExtend);
                    if (!isWin)
                    {
                        return false;
                    }

                    isWin = _eC_AcceptWayByOrderDAL.InserModel(acceptWayByOrder);
                    if (!isWin)
                    {
                        return false;
                    }
                    isWin = _pay_TradingSingleDAL.InserModel(tradingSingle);
                    if (!isWin)
                    {
                        return false;
                    }

                    //增加下单操作记录
                    isWin = _eC_OrderOperateDAL.InserModel(new EC_OrderOperate()
                    {
                        OperateType = 1,
                        Operator = orderBasic.Operator,
                        OrderId = orderBasic.Keyid,
                        Remark = string.Empty
                    });
                    if (!isWin)
                    {
                        return false;
                    }

                    if (null != payRequest)//有支付请求则调用支付订单方法
                    {
                        //传入的payRequest.Subject必须为格式化器 格式例如:{0},{1},{2}
                        //0:Keyid,1:SellerOrderId,2:DocumentName
                        payRequest.Subject = string.Format(payRequest.Subject, orderBasic.Keyid, orderBasic.SellerOrderId, orderBasic.DocumentName);
                        isWin = _eC_OrderBasicDAL.PayOrder(orderBasic.Keyid.Value, payRequest, commission, 0);
                    }

                    if (0 < seckillId)
                    {
                        _eC_SeckillBusinessDAL = new EC_SeckillBusinessDAL(_dataBase);
                        //若有秒杀业务编号则扣除其秒杀数量1
                        isWin = _eC_SeckillBusinessDAL.DecreaseSeckillNum(seckillId, 1, orderBasic.BuyerId);
                    }
                    if (!isWin)
                    {
                        return false;
                    }
                    if (null != orderPrintParameter)
                    {
                        _eC_OrderPrintParameterDAL = new EC_OrderPrintParameterDAL(_dataBase);
                        orderPrintParameter.OrderId = orderBasic.Keyid.Value;
                        isWin = _eC_OrderPrintParameterDAL.InserModel(orderPrintParameter);
                    }

                    if (!isWin)
                    {
                        return false;
                    }
                //    else
                //        tran.Complete();
                //}

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 新建订单(一般)
        /// </summary>
        /// <param name="orderBasic">订单基础信息</param>
        /// <param name="orderExtend">订单扩展信息</param>
        /// <param name="tradingSingle">交易单</param>
        /// <param name="acceptWayByOrder">订单收货方式</param>
        /// <param name="orderPrintParameter">订单印刷参数对象</param>
        /// <returns></returns>
        public bool NewOrder(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, Pay_TradingSingle tradingSingle, EC_AcceptWayByOrder acceptWayByOrder, EC_OrderPrintParameter orderPrintParameter)
        {
            return NewOrder(orderBasic, orderExtend, tradingSingle, acceptWayByOrder, null, 0, 0, orderPrintParameter);
        }

        /// <summary>
        /// 修改订单信息
        /// </summary>
        /// <param name="orderBasic">订单主信息</param>
        /// <param name="orderExtend">订单扩展信息</param>
        /// <param name="orderParam">订单印刷选项</param>
        /// <returns></returns>
        public bool UpdateOrderInfo(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, EC_OrderPrintParameter orderParam = null)
        {
            try
            {
                _eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
                orderBasic.OrderExtend = orderExtend;
                return _eC_OrderExtendDAL.UpdateModel(orderBasic, orderParam);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 修改外协订单
        /// </summary>
        /// <param name="orderBasic"></param>
        /// <param name="orderExtend"></param>
        /// <param name="orderParam"></param>
        /// <returns></returns>
        public bool UpdateOutOrderInfo(EC_OrderBasic orderBasic, EC_OrderExtend orderExtend, EC_OrderPrintParameter orderParam = null)
        {
            try
            {
                _eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
                return _eC_OrderExtendDAL.UpdateOutOrderInfo(orderBasic, orderExtend, orderParam);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 后道，整单外协
        /// </summary>
        /// <param name="orderBasic"></param>
        /// <param name="acceptWayByOrder"></param>
        /// <param name="orderPrintParameter"></param>
        /// <param name="m_EC_OrderOperate"></param>
        /// <param name="RelationTypeid"></param>
        /// <returns></returns>
        public bool OutOrder(EC_OrderBasic orderBasic, EC_AcceptWayByOrder acceptWayByOrder, EC_OrderPrintParameter orderPrintParameter, EC_OrderOperate m_EC_OrderOperate, int? RelationTypeid, string orderIds)
        {
            try
            {
                _eC_OrderExtendDAL = new EC_OrderExtendDAL(_dataBase);
                _pay_TradingSingleDAL = new Pay_TradingSingleDAL(_dataBase);
                _eC_AcceptWayByOrderDAL = new EC_AcceptWayByOrderDAL(_dataBase);
                _eC_OrderOperateDAL = new EC_OrderOperateDAL(_dataBase);

                string[] orderIdArray = orderIds.Trim(',').Split(',');
                int? NewOrderId = 0;
                OA_OrderWorkListRelationDAL dal_OA_OrderWorkListRelationDAL = new OA_OrderWorkListRelationDAL(_dataBase);
                EC_SpecialForumsDAL dal_EC_SpecialForumsDAL = new EC_SpecialForumsDAL(_dataBase);

                EC_OrderExtend orderExtend = orderBasic.OrderExtend;
                Pay_TradingSingle tradingSingle = orderBasic.TradingSingle;

                using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
                {
                    bool isWin = false;
                    orderBasic.OutId = 0;
                    isWin = _eC_OrderBasicDAL.InserModel(orderBasic);
                    if (!isWin)
                    {
                        return false;
                    }
                    else
                    {
                        //无操作
                    }

                    NewOrderId = acceptWayByOrder.TargetId = tradingSingle.OrderId = orderExtend.Keyid = orderBasic.Keyid;//获取返回的订单唯一编号
                    isWin = _eC_OrderExtendDAL.InserModel(orderExtend);
                    if (!isWin)
                    {
                        return false;
                    }
                    else
                    {
                        //无操作
                    }
                    //isWin = _eC_OrderExtendDAL.InserModel(orderExtend);
                    //if (!isWin)
                    //{
                    //    return false;
                    //}
                    //else
                    //{
                    //    //无操作
                    //}

                    isWin = _eC_AcceptWayByOrderDAL.InserModel(acceptWayByOrder);
                    if (!isWin)
                    {
                        return false;
                    }
                    else
                    {
                        //无操作
                    }

                    isWin = _pay_TradingSingleDAL.InserModel(tradingSingle);
                    if (!isWin)
                    {
                        return false;
                    }
                    else
                    {
                        //无操作
                    }

                    //增加下单操作记录
                    isWin = _eC_OrderOperateDAL.InserModel(new EC_OrderOperate()
                    {
                        OperateType = 1,
                        Operator = orderBasic.Operator,
                        OrderId = orderBasic.Keyid,
                        Remark = string.Empty
                    });
                    if (!isWin)
                    {
                        return false;
                    }
                    else
                    {
                        //无操作
                    }
                    isWin = _eC_OrderExtendDAL.UpSurplusPrintNum(m_EC_OrderOperate.OrderId, -1, orderBasic.Operator);
                    if (!isWin)
                    {
                        return false;
                    }
                    else
                    {
                        //无操作
                    }
                    isWin = _eC_OrderBasicDAL.UpdateOrderStateUnit(m_EC_OrderOperate);
                    if (!isWin)
                    {
                        return false;
                    }
                    else
                    {
                        //无操作
                    }

                    OA_OrderWorkListRelation m_OA_OrderWorkListRelation = new OA_OrderWorkListRelation();
                    m_OA_OrderWorkListRelation.OrderId = m_EC_OrderOperate.OrderId;
                    m_OA_OrderWorkListRelation.RelationTypeId = RelationTypeid;
                    m_OA_OrderWorkListRelation.RelationTargetId = orderBasic.Keyid;
                    isWin = dal_OA_OrderWorkListRelationDAL.InserModel(m_OA_OrderWorkListRelation);
                    if (!isWin)
                    {
                        return false;
                    }
                    else
                    {
                        //无操作
                    }

                    if (null != orderPrintParameter)
                    {
                        _eC_OrderPrintParameterDAL = new EC_OrderPrintParameterDAL(_dataBase);
                        orderPrintParameter.OrderId = orderBasic.Keyid.Value;
                        isWin = _eC_OrderPrintParameterDAL.InserModel(orderPrintParameter);

                    }
                    else
                    {
                    }
                    if (orderIdArray != null && orderIdArray.Length > 0)
                    {
                        foreach (var item in orderIdArray)
                        {
                            EC_OrderBasic item_EC_OrderBasic = GetOrderById(int.Parse(item));
                            if (item_EC_OrderBasic != null)
                            {
                                item_EC_OrderBasic.OutId = -2;
                                isWin = _eC_OrderBasicDAL.UpdateModel(item_EC_OrderBasic);
                                if (!isWin)
                                {
                                    return false;
                                }
                                else
                                {
                                    //无操作
                                }
                            }
                        }
                    }


                    if (!isWin)
                    {
                        return false;
                    }
                    else
                        tran.Complete();
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        ///// <summary>
        ///// 修改订单状态
        ///// </summary>
        ///// <param name="stateId">状态编号</param>
        ///// <param name="orderIds">订单编号集合</param>
        ///// <returns></returns>
        //public bool UpdataOrderState(int stateId,string tOperator, params int[] orderIds)
        //{
        //    int maxLength = 0;
        //    if (null == orderIds || (maxLength = orderIds.Length) == 0)
        //    {
        //        return false;
        //    }
        //    else { }
        //    int i = -1;

        //    try
        //    {
        //        using (System.Transactions.TransactionScope tran = new System.Transactions.TransactionScope())
        //        {
        //            while (++i < maxLength)
        //            {

        //                if (!_eC_OrderBasicDAL.UpdateOrderState(new EC_OrderOperate() { OrderId = orderIds[i], Remark = "", OperateType = stateId, Operator = tOperator}, stateId))
        //                {
        //                    return false;
        //                }
        //                else
        //                {
        //                    //无操作
        //                }
        //            }
        //            tran.Complete();
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //       throw ex;
        //    }
        //    return true;

        //}

        /// <summary>
        /// 修改收货时间
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="day"></param>
        /// <returns></returns>
        public bool UpdataOrderDeliveryTime(int orderId, int day)
        {
            return false;
        }

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

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {

            return false;

        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
        {

            return false;

        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.EC_OrderBasic> SelectModelPage(Infrastructure.Query.Query query, Infrastructure.Query.Pagination pagination)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 单个查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.EC_OrderBasic> SelectAllModel(Infrastructure.Query.Query query)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 更新订单支付状态
        /// </summary>
        /// <param name="orderId">订单编号</param>
        /// <returns></returns>
        public bool UpdateOrderPayState(int orderId)
        {
            return _eC_OrderBasicDAL.UpdateOrderPayState(orderId);
        }

        /// <summary>
        /// 更新订单支付金额
        /// </summary>
        /// <param name="orderId">订单编号</param>
        /// <param name="payWay">支付方式(-1:收款、1:付款)</param>
        /// <param name="payRequest">支付请求</param>
        /// <returns></returns>
        public bool UpdateOrderPayedMoney(int orderId, int payWay, Pay_Request payRequest)
        {
            payRequest.UsedCredit = payRequest.UsedCredit.HasValue ? payRequest.UsedCredit : 0;
            return _eC_OrderBasicDAL.UpdateOrderPayedMoney(orderId, payWay, payRequest.Paymoney, payRequest.UsedCredit.Value);
        }

        /// <summary>
        /// 支付订单
        /// </summary>
        /// <param name="orderId">订单编号</param>
        /// <param name="payRequest">支付请求</param>
        /// <param name="commission">受理手续费</param>
        /// <param name="OutAllMoney">外协金额</param>
        /// <returns></returns>
        public bool PayOrder(int orderId, Pay_Request payRequest, double commission, decimal OutAllMoney = 0)
        {
            return _eC_OrderBasicDAL.PayOrder(orderId, payRequest, commission, OutAllMoney);
        }

        /// <summary>
        /// 订单退款
        /// </summary>
        /// <param name="orderOperate">要更新的订单状态</param>
        /// <param name="payRequests">支付请求</param>
        /// <returns></returns>
        public bool OrderRefund(EC_OrderOperate orderOperate, params Pay_Request[] payRequests)
        {
            if (null == payRequests || 0 == payRequests.Length)
                return false;
            else
                ;
            int i = -1;
            int maxLength = payRequests.Length;


            Pay_PaymentAccountDAL _pay_PaymentAccountDAL = new Pay_PaymentAccountDAL(_dataBase);

            bool isWin = false;
            using (TransactionScope tran = new TransactionScope())
            {

                while (++i < maxLength)
                {
                    isWin = _pay_PaymentAccountDAL.Pay(payRequests[i]);
                    if (isWin)
                        continue;
                    else
                        return isWin;
                }

                if (null != orderOperate && -4 == orderOperate.OperateType)//是退款状态时直接退款不改变订单状态
                {
                    isWin = _eC_OrderBasicDAL.UpdateOrderPayedMoney(orderOperate.OrderId.Value, 1, payRequests[0].Paymoney * -1);
                }
                else if (null != orderOperate && 0 == orderOperate.OperateType)
                    isWin = _eC_OrderBasicDAL.UpdateOrderStateUnit(orderOperate);


                if (!isWin)
                    return isWin;
                else
                    ;

                tran.Complete();

            }

            return isWin;
        }

        /// <summary>
        /// 受理订单
        /// </summary>
        /// <param name="orderId">订单编号</param>
        /// <param name="commission">受理佣金(受理订单时站方收取的服务费)</param>
        /// <returns></returns>
        public bool AcceptOrder(int orderId, double commission)
        {
            return _eC_OrderBasicDAL.AcceptOrder(orderId, commission);
        }

        /// <summary>
        /// 根据订单编号获取订单 
        /// </summary>
        /// <param name="orderId">订单编号</param>
        /// <returns></returns>
        public EC_OrderBasic GetOrderById(int orderId)
        {
            return _eC_OrderBasicDAL.SelectModelById(orderId);
            //IList<EC_OrderBasic> result = _dataBase.SelectModel<EC_OrderBasic>(" * ", "EC_OrderBasic ", string.Format(" Keyid = {0}", orderId));
            //return null == result || result.Count < 1 ? null : result[0];
        }


        /// <summary>
        /// 根据订单编号获取订单 
        /// </summary>
        /// <param name="orderIds">订单编号</param>
        /// <returns></returns>
        public IEnumerable<EC_OrderBasic> GetOrderByIds(string orderIds)
        {
            //return _eC_OrderBasicDAL.SelectModelById(orderId);
            IList<EC_OrderBasic> result = _dataBase.SelectModel<EC_OrderBasic>(" * ", "EC_OrderBasic ", string.Format(" Keyid in ({0})", orderIds));
            return null == result || result.Count < 1 ? null : result;
        }

        /// <summary>
        /// 获取已支付订单的明细
        /// </summary>
        /// <returns></returns>
        public IEnumerable<EC_OrderBasic> GetOrderListByFinish(Infrastructure.Query.Pagination pagination, string startDate, string endDate, string province, string city, string county)
        {
            string selectTarget = " a.*,b.OnlineMoney,b.UsedCredit,(b.OnlineMoney+b.UsedCredit) as ALLPayMoney,d.Name as OrderTypeName";
            string fromSouce = @" dbo.EC_OrderBasic a INNER JOIN dbo.Pay_TradingSingle b
                        ON a.Keyid=b.OrderId INNER JOIN dbo.EC_MemberBasic c ON b.PayerId=c.MemberId inner join Sys_Dictionary d on
                        a.OrderTypeId=d.MeanValue and d.DicType='EC_订单类型'
                        WHERE a.OrderState=-1 AND a.OrderTypeId<1 AND (b.OnlineMoney+b.UsedCredit)>0 ";

            if (!string.IsNullOrEmpty(startDate))
            {
                fromSouce += " and  a.CreateTime>='" + startDate + "'";
            }
            if (!string.IsNullOrEmpty(endDate))
            {
                fromSouce += " and  a.CreateTime<'" + endDate + "'";
            }
            if (!string.IsNullOrEmpty(province))
            {
                fromSouce += " and c.Province='" + province + "'";
            }
            if (!string.IsNullOrEmpty(city))
            {
                fromSouce += " and c.City='" + city + "'";
            }
            if (!string.IsNullOrEmpty(county))
            {
                fromSouce += " and c.County='" + county + "'";
            }

            var result = _dataBase.SelectModelPage<Model.EC_OrderBasic>(pagination, selectTarget, fromSouce, " a.LastUpdateTime DESC ", " LastUpdateTime DESC ", string.Empty);//执行查询
            return null == result ? null : result;//返回结果
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="orderId"></param>
        /// <returns></returns>
        public OA_OrderWorkListRelation GetModelById(int? orderId)
        {
            if (orderId == null)
                return null;//错误数据返会空 

            IList<OA_OrderWorkListRelation> result = _dataBase.SelectModel<OA_OrderWorkListRelation>("*", "OA_OrderWorkListRelation", string.Format(" OrderId='{0}' and RelationTypeId = 4", orderId)) as IList<OA_OrderWorkListRelation>;//执行查询

            return (null == result || result.Count == 0) ? null : result[0];//返回结果
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool OldUpdate(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter delayTime = null;
            if (trueModel.DelayTime.HasValue)
            {
                delayTime = new SqlParameter("@DelayTime", trueModel.DelayTime);
            }
            else
            {
                delayTime = new SqlParameter("@DelayTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                	new SqlParameter("@Keyid",trueModel.Keyid),
					new SqlParameter("@ProofreadWayId",trueModel.ProofreadWayId),
					new SqlParameter("@ExigencyCaseId",trueModel.ExigencyCaseId),
					new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId),
					new SqlParameter("@CustomerManagerId",trueModel.CustomerManagerId),
					new SqlParameter("@CustomerLevelId",trueModel.CustomerLevelId),
					new SqlParameter("@Contacts",trueModel.Contacts),
					new SqlParameter("@ContactPhone",trueModel.ContactPhone),
                    new SqlParameter("@SurplusPrintNum",trueModel.SurplusPrintNum),
					
                    //new SqlParameter("@SurplusPrintNum",trueModel.SurplusPrintNum),
					new SqlParameter("@Operate",trueModel.Operate),
					new SqlParameter("@Remark",trueModel.Remark), 
                    new SqlParameter("@BookNum",trueModel.BookNum),
                    new SqlParameter("@BookPrice",trueModel.BookPrice),
                    new SqlParameter("@NumPerBag",trueModel.NumPerBag),
                    new SqlParameter("@BagNum",trueModel.BagNum),
                        delayTime,
                      new SqlParameter("@Productionprogress",trueModel.Productionprogress),
            };
            try
            {
                _dataBase.Query("sp_EC_OrderExtendForSeal_Update", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool OldUpdateDelayTime(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter delayTime = null;
            if (trueModel.DelayTime.HasValue)
            {
                delayTime = new SqlParameter("@DelayTime", trueModel.DelayTime);
            }
            else
            {
                delayTime = new SqlParameter("@DelayTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
                        delayTime,
            };
            try
            {
                _dataBase.Query("sp_EC_OrderExtend_UpdateDelayTime", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool OldUpdateProductionprogress(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
                      new SqlParameter("@Productionprogress",trueModel.Productionprogress),
            };
            try
            {
                _dataBase.Query("sp_EC_OrderExtend_UpdateProductionprogress", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }


        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool OldUpdateKaipiaoshenqing(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter KaipiaoshenqingTime = null;
            if (trueModel.KaipiaoshenqingTime.HasValue)
            {
                KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", trueModel.KaipiaoshenqingTime);
            }
            else
            {
                KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
                        KaipiaoshenqingTime,
                          new SqlParameter("@Kaipiaoshenqing",trueModel.Kaipiaoshenqing),
                            new SqlParameter("@KaipiaoshenqingCreater",trueModel.KaipiaoshenqingCreater),
                                new SqlParameter("@KaipiaoshenqingId",trueModel.KaipiaoshenqingId),
            };
            string sql = " Update EC_OrderExtend Set KaipiaoshenqingTime=@KaipiaoshenqingTime ,Kaipiaoshenqing=@Kaipiaoshenqing ,KaipiaoshenqingCreater=@KaipiaoshenqingCreater  ,KaipiaoshenqingId=@KaipiaoshenqingId   Where Keyid =@keyid ";
            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }



        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool OldUpdateKaipiaoshenqings(EC_OrderExtend model,string ids)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter KaipiaoshenqingTime = null;
            if (trueModel.KaipiaoshenqingTime.HasValue)
            {
                KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", trueModel.KaipiaoshenqingTime);
            }
            else
            {
                KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    //new SqlParameter("@Keyid",trueModel.Keyid),
                        KaipiaoshenqingTime,
                          new SqlParameter("@Kaipiaoshenqing",trueModel.Kaipiaoshenqing),
                            new SqlParameter("@KaipiaoshenqingCreater",trueModel.KaipiaoshenqingCreater),
                                new SqlParameter("@KaipiaoshenqingId",trueModel.KaipiaoshenqingId),
            };
            string sql = " Update EC_OrderExtend Set KaipiaoshenqingTime=@KaipiaoshenqingTime ,Kaipiaoshenqing=@Kaipiaoshenqing ,KaipiaoshenqingCreater=@KaipiaoshenqingCreater  ,KaipiaoshenqingId=@KaipiaoshenqingId   Where Keyid in ("+ ids + ") ";//@keyid
            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool NewUpdateKaipiaoshenqings(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter KaipiaoshenqingTime = null;
            if (trueModel.KaipiaoshenqingTime.HasValue)
            {
                KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", trueModel.KaipiaoshenqingTime);
            }
            else
            {
                KaipiaoshenqingTime = new SqlParameter("@KaipiaoshenqingTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    //new SqlParameter("@Keyid",trueModel.Keyid),
                        KaipiaoshenqingTime,
                          new SqlParameter("@Kaipiaoshenqing",trueModel.Kaipiaoshenqing),
                            new SqlParameter("@KaipiaoshenqingCreater",trueModel.KaipiaoshenqingCreater),
                                new SqlParameter("@KaipiaoshenqingId",trueModel.KaipiaoshenqingId),
            };
            string sql = " Update EC_OrderExtend Set KaipiaoshenqingTime=@KaipiaoshenqingTime ,Kaipiaoshenqing=@Kaipiaoshenqing ,KaipiaoshenqingCreater=@KaipiaoshenqingCreater Where KaipiaoshenqingId=@KaipiaoshenqingId      ";//@keyid
            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool JujueKaipiaoshenqings(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
             
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                     
                                new SqlParameter("@KaipiaoshenqingId",trueModel.KaipiaoshenqingId),
            };
            string sql = " Update EC_OrderExtend Set KaipiaoshenqingTime=null ,Kaipiaoshenqing=2 ,KaipiaoshenqingCreater=null  ,KaipiaoshenqingId=null   Where KaipiaoshenqingId =@KaipiaoshenqingId ";//@keyid
            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool OldUpdateKaipiaoqueren(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter KaipiaoquerenTime = null;
            if (trueModel.KaipiaoquerenTime.HasValue)
            {
                KaipiaoquerenTime = new SqlParameter("@KaipiaoquerenTime", trueModel.KaipiaoquerenTime);
            }
            else
            {
                KaipiaoquerenTime = new SqlParameter("@KaipiaoquerenTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
                        KaipiaoquerenTime,
                          new SqlParameter("@Kaipiaoshenqing",trueModel.Kaipiaoshenqing),
                            new SqlParameter("@KaipiaoquerenCreater",trueModel.KaipiaoquerenCreater),
            };
            try
            {
                _dataBase.Query("sp_EC_OrderExtend_UpdateKaipiaoqueren", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }


        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool OldUpdateQuchudingdan(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter QuchudingdanTime = null;
            if (trueModel.QuchudingdanTime.HasValue)
            {
                QuchudingdanTime = new SqlParameter("@QuchudingdanTime", trueModel.QuchudingdanTime);
            }
            else
            {
                QuchudingdanTime = new SqlParameter("@QuchudingdanTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
                        QuchudingdanTime,
                          new SqlParameter("@Quchudingdan",trueModel.Quchudingdan),
                            new SqlParameter("@QuchudingdanCreater",trueModel.QuchudingdanCreater),
            };
            try
            {
                _dataBase.Query("sp_EC_OrderExtend_UpdateQuchudingdan", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }


        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool OldUpdateKehuqianshou(EC_OrderExtend model)
        {

            Model.EC_OrderExtend trueModel = model;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter KehuqianshouTime = null;
            if (trueModel.KehuqianshouTime.HasValue)
            {
                KehuqianshouTime = new SqlParameter("@KehuqianshouTime", trueModel.KehuqianshouTime);
            }
            else
            {
                KehuqianshouTime = new SqlParameter("@KehuqianshouTime", DBNull.Value);
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                    new SqlParameter("@Keyid",trueModel.Keyid),
                        KehuqianshouTime,
                          new SqlParameter("@Kehuqianshou",trueModel.Kehuqianshou),
                            new SqlParameter("@KehuqianshouCreater",trueModel.KehuqianshouCreater),
            };
            try
            {
                _dataBase.Query("sp_EC_OrderExtend_UpdateKehuqianshou", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }




        /// <summary>
        /// 获取已支付订单的明细
        /// </summary>
        /// <returns></returns>
        public IEnumerable<OA_DeliverPlan> GetshoukuantixingList(Infrastructure.Query.Pagination pagination,Guid FirmId,string province, string city, string county, string CustomerTypeId, string CompanyName, string BusinessManagerId, string AccountManagerId)
        {
            string selectTarget = " t.* ";
            string where = " and oc.[FirmId] ='" + FirmId + "'";

            if (!string.IsNullOrEmpty(province))
            {
                where += " and occ.Province='" + province + "'";
            }
            if (!string.IsNullOrEmpty(city))
            {
                where += " and occ.City='" + city + "'";
            }
            if (!string.IsNullOrEmpty(county))
            {
                where += " and occ.County='" + county + "'";
            }
            if (!string.IsNullOrEmpty(CustomerTypeId))
            {
                where += " and oc.CustomerTypeId='" + CustomerTypeId + "'";
            }
            if (!string.IsNullOrEmpty(CompanyName))
            {
                where += " and oc.CompanyName like '%" + CompanyName + "%'";
            }
            if (!string.IsNullOrEmpty(BusinessManagerId))
            {
                where += " and oc.BusinessManagerId='" + BusinessManagerId + "'";
            }
            if (!string.IsNullOrEmpty(AccountManagerId))
            {
                where += " and oc.AccountManagerId='" + AccountManagerId + "'";
            }
            string fromSouce = "( select t.*,oy.YujihuikuanTime,oy.Keyid  as  YujihuikuanKeyid  from  ( ";



              fromSouce += @" 
                       select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime
                       from  [OA_CorporateClients] oc 
                       left join OA_Staff os on oc.BusinessManagerId = os.Keyid
                        inner join  [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
                        inner join ( 
select  ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang,convert(char(7), ea.DeliverTime, 120) as shoukuanshijian
  from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid
  where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
  group by ob.BuyerId,convert(char(7), ea.DeliverTime, 120)
)  ob on (oc.Keyid=ob.BuyerId  )
  where oc.Paytime =1
                         " + where;

             fromSouce += @" 
                      UNION 

select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime

  from  [OA_CorporateClients] oc 
  left join OA_Staff os on oc.BusinessManagerId = os.Keyid
    inner join  [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
  inner join ( 
  
  select  ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang,DATENAME(year, ea.DeliverTime) +'第'+DATENAME(quarter, ea.DeliverTime)+'季度' as shoukuanshijian
  from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid
  where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
  group by ob.BuyerId,DATENAME(year, ea.DeliverTime) +'第'+DATENAME(quarter, ea.DeliverTime) 
  
)  ob on (oc.Keyid=ob.BuyerId  )
  where oc.Paytime =2
                         " + where;

            fromSouce += @" 
                      UNION 

select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime

  from  [OA_CorporateClients] oc 
  left join OA_Staff os on oc.BusinessManagerId = os.Keyid
    inner join  [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
  inner join ( 
  
  select  ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang, DATENAME(year, ea.DeliverTime) +'第'+CONVERT(varchar,(month(ea.DeliverTime)+5)/6)+'半年' as shoukuanshijian
  from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid
  where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
  group by ob.BuyerId,DATENAME(year, ea.DeliverTime) +'第'+CONVERT(varchar,(month(ea.DeliverTime)+5)/6)+'半年'
  
)  ob on (oc.Keyid=ob.BuyerId  )
  where oc.Paytime =3
                         " + where;

            fromSouce += @" 
                      UNION 

select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime

  from  [OA_CorporateClients] oc 
  left join OA_Staff os on oc.BusinessManagerId = os.Keyid
    inner join  [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
  inner join ( 
  
  select  ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang,DATENAME(year, ea.DeliverTime)+'年' as shoukuanshijian
  from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid
  where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
  group by ob.BuyerId,DATENAME(year, ea.DeliverTime)+'年'
  
)  ob on (oc.Keyid=ob.BuyerId  )
  where oc.Paytime =4
                         " + where;

            fromSouce += @" 
                      UNION 

select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,'履约完毕'shoukuanshijian,oc.Paytime

  from  [OA_CorporateClients] oc 
  left join OA_Staff os on oc.BusinessManagerId = os.Keyid
    inner join  [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
  inner join ( 
  
  select  ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang
  from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid
  where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
  group by ob.BuyerId
  
)  ob on (oc.Keyid=ob.BuyerId  )
  where oc.Paytime =6 and oc.Shifouwanjie=1
                         " + where;

            fromSouce += @" 
                      
      UNION 

select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,'交货后'+CONVERT(varchar,oc.Paydays)+'天结'shoukuanshijian,oc.Paytime

  from  [OA_CorporateClients] oc 
  left join OA_Staff os on oc.BusinessManagerId = os.Keyid
    inner join  [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
  inner join ( 
  
  select  ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang,ea.DeliverTime
  from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid
  where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
  group by ob.BuyerId,ea.DeliverTime
  
)  ob on (oc.Keyid=ob.BuyerId and DATEDIFF(day,ob.DeliverTime,GETDATE()) > oc.Paydays)
  where oc.Paytime =7 

                         " + where;

            fromSouce += @" 

 UNION 
                      select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.shuliang,ob.shoukuanshijian,oc.Paytime

  from  [OA_CorporateClients] oc 
  left join OA_Staff os on oc.BusinessManagerId = os.Keyid
    inner join  [OA_CustomerCommunications] occ on oc.Keyid = occ.Keyid
  inner join ( 
  
  select  ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang,  case when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-03-01')>0    then CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+ '上学期'
   when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-09-01')<=0    then CONVERT(varchar,YEAR(ea.DeliverTime))+'至'+CONVERT(varchar,YEAR(ea.DeliverTime)+1)+ '上学期'
  else CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+'下学期' end   as shoukuanshijian
  from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid
  where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )
  group by ob.BuyerId,  case when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-03-01')>0    then CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+ '上学期'
   when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-09-01')<=0    then CONVERT(varchar,YEAR(ea.DeliverTime))+'至'+CONVERT(varchar,YEAR(ea.DeliverTime)+1)+ '上学期'
  else CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+'下学期' end 


)  ob on (oc.Keyid=ob.BuyerId  )
  where oc.Paytime =5
                         " + where;

          

            fromSouce += " )as t left join OA_Yujihuikuan oy on t.BuyerId =oy.BuyerId and t.shoukuanshijian = oy.shoukuanshijian ) as t ";

            var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, " shoukuanshijian DESC ");//执行查询
            return null == result ? null : result;//返回结果
        }




        /// <summary>
        /// 获取未支付订单的明细
        /// </summary>
        /// <returns></returns>
        public IEnumerable<OA_DeliverPlan> GetshoukuantixingorderidList(Guid FirmId, string shoukuanshijian, string Paytime, string BuyerId)
        {
            string selectTarget = " t.* ";
            string where = " and oc.[FirmId] ='" + FirmId + "'";
            string fromSouce = " ( ";
            if (Paytime == "1")
            {
                fromSouce += " select ob.keyid as OrderId, ob.BuyerId,convert(char(7), ea.DeliverTime, 120) as shoukuanshijian "+
  " from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
" left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid" +
  " where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
" and  ob.BuyerId='"+ BuyerId + "' and convert(char(7), ea.DeliverTime, 120) ='"+ shoukuanshijian + "'";
}

            if (Paytime == "2")
            {
                fromSouce += " select ob.keyid as OrderId, ob.BuyerId,DATENAME(year, ea.DeliverTime) +'第'+DATENAME(quarter, ea.DeliverTime)+'季度'  as shoukuanshijian " +
  " from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
" left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid" +
  " where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
" and  ob.BuyerId='" + BuyerId + "' and DATENAME(year, ea.DeliverTime) +'第'+DATENAME(quarter, ea.DeliverTime)+'季度' ='" + shoukuanshijian + "'";
            }
            if (Paytime == "3")
            {
                fromSouce += " select ob.keyid as OrderId, ob.BuyerId, DATENAME(year, ea.DeliverTime) +'第'+CONVERT(varchar,(month(ea.DeliverTime)+5)/6)+'半年'  as shoukuanshijian " +
  " from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
" left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid" +
  " where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
" and  ob.BuyerId='" + BuyerId + "' and  DATENAME(year, ea.DeliverTime) +'第'+CONVERT(varchar,(month(ea.DeliverTime)+5)/6)+'半年' ='" + shoukuanshijian + "'";
            }
            if (Paytime == "4")
            {
                fromSouce += " select ob.keyid as OrderId, ob.BuyerId, DATENAME(year, ea.DeliverTime)+'年' as shoukuanshijian " +
  " from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
" left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid" +
  " where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
" and  ob.BuyerId='" + BuyerId + "' and  DATENAME(year, ea.DeliverTime)+'年' ='" + shoukuanshijian + "'";
            }
            if (Paytime == "5")
            {
                fromSouce += " select ob.keyid as OrderId, ob.BuyerId,  case when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-03-01')>0    then CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+ '上学期' " +
 "  when DATEDIFF(day, ea.DeliverTime, DATENAME(year, ea.DeliverTime)+'-09-01')<= 0    then CONVERT(varchar, YEAR(ea.DeliverTime))+'至' + CONVERT(varchar, YEAR(ea.DeliverTime) + 1) + '上学期' " +
 " else CONVERT(varchar, YEAR(ea.DeliverTime) - 1) + '至' + CONVERT(varchar, YEAR(ea.DeliverTime)) + '下学期' end as shoukuanshijian " +
  " from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
" left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid" +
  " where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
" and  ob.BuyerId='" + BuyerId + "' and case when DATEDIFF(day,ea.DeliverTime,DATENAME(year, ea.DeliverTime)+'-03-01')>0    then CONVERT(varchar,YEAR(ea.DeliverTime)-1)+'至'+CONVERT(varchar,YEAR(ea.DeliverTime))+ '上学期' " +
 "  when DATEDIFF(day, ea.DeliverTime, DATENAME(year, ea.DeliverTime)+'-09-01')<= 0    then CONVERT(varchar, YEAR(ea.DeliverTime))+'至' + CONVERT(varchar, YEAR(ea.DeliverTime) + 1) + '上学期' " +
 " else CONVERT(varchar, YEAR(ea.DeliverTime) - 1) + '至' + CONVERT(varchar, YEAR(ea.DeliverTime)) + '下学期' end  ='" + shoukuanshijian + "'";
            }

            if (Paytime == "6")
            {
                fromSouce += " select ob.keyid as OrderId, ob.BuyerId  " +
  " from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
" left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid " +
  " where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
" and  ob.BuyerId='" + BuyerId + "' ";
            }
            if (Paytime == "7")
            {
                fromSouce += " select ob.keyid as OrderId, ob.BuyerId, ea.DeliverTime as shoukuanshijian " +
  " from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)" +
" left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid " +
"  inner join [OA_CorporateClients] oc   on oc.Keyid=ob.BuyerId and DATEDIFF(day,ea.DeliverTime,GETDATE()) > oc.Paydays " +
  " where ob.PayState !=-1 and (ob.OrderState = 5 or ob.OrderState = 6 or ob.OrderState = -1 )" +
" and  ob.BuyerId='" + BuyerId + "' ";
            }
            fromSouce += " )as t order by    OrderId DESC ";



 
         



          
  

           
  



       

            var result = _dataBase.SelectModel<Model.OA_DeliverPlan>( selectTarget, fromSouce );//执行查询
            return null == result ? null : result;//返回结果
        }


        /// <summary>
        /// 获取已支付订单的明细
        /// </summary>
        /// <returns></returns>
        public IEnumerable<OA_DeliverPlan> GetxiaoshouchaxunList(Infrastructure.Query.Pagination pagination, Guid FirmId, string province, string city, string county, string DegreeImportanId, string CompanyName, string BusinessManagerId, string CreditId, string Shifouwanjie
             , string selLastOrderTime,  string selOrderCount, string selOrderMoney, string OrderCount, string OrderMoney, string LastOrderTime)
        {
            string selectTarget = " t.* ";
            string where = " where oc.[FirmId] ='" + FirmId + "'";

            if (!string.IsNullOrEmpty(province))
            {
                where += " and occ.Province='" + province + "'";
            }
            if (!string.IsNullOrEmpty(city))
            {
                where += " and occ.City='" + city + "'";
            }
            if (!string.IsNullOrEmpty(county))
            {
                where += " and occ.County='" + county + "'";
            }
            if (!string.IsNullOrEmpty(DegreeImportanId))
            {
                where += " and oc.DegreeImportanId='" + DegreeImportanId + "'";
            }
            if (!string.IsNullOrEmpty(CompanyName))
            {
                where += " and oc.CompanyName like '%" + CompanyName + "%'";
            }
            if (!string.IsNullOrEmpty(BusinessManagerId))
            {
                where += " and oc.BusinessManagerId='" + BusinessManagerId + "'";
            }
            if (!string.IsNullOrEmpty(CreditId))
            {
                where += " and oc.CreditId='" + CreditId + "'";
            }
            if (!string.IsNullOrEmpty(Shifouwanjie))
            {
                where += " and oc.Shifouwanjie='" + Shifouwanjie + "'";
            }
            if (!string.IsNullOrEmpty(selLastOrderTime) && !string.IsNullOrEmpty(LastOrderTime))
                where += string.Format(" and CAST(getdate()-{1} AS date) {0}  CAST(oc.LastOrderTime AS DATE)", selLastOrderTime, LastOrderTime);
            if (!string.IsNullOrEmpty(selOrderCount) && !string.IsNullOrEmpty(OrderCount))
                where += string.Format(" and oc.OrderCount{0}'{1}'", selOrderCount, OrderCount);

            if (!string.IsNullOrEmpty(selOrderMoney) && !string.IsNullOrEmpty(OrderMoney))
                where += string.Format(" and oc.OrderMoney{0}'{1}'", selOrderMoney, OrderMoney);

            string fromSouce = "(  ";



            fromSouce += @" 
                      
     select occ.City+occ.County as quyu   ,oc.CompanyName as BuyerName,os.Name as BusinessManager,oc.CreateTime,contracttype.Name as ContracttypeName,oc.Contractmoney
,expiretype.Name as ExpiretypeName,oc.ExpireTime,ob.SumPrice,  ob.PayedMoney,oc.CustomerId


from  [OA_CorporateClients] oc
inner join [OA_CustomerCommunications]  occ on oc.Keyid = occ.Keyid
left join  OA_Staff os  on oc.BusinessManagerId = os.Keyid
left join [Sys_Dictionary] contracttype on contracttype.DicType='合同金额' and contracttype.MeanValue = oc.Contracttype
left join [Sys_Dictionary] expiretype on expiretype.DicType='到期时间' and expiretype.MeanValue = oc.Expiretype
left join ( select  ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang
  from EC_OrderBasic ob Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid) Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)
  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid
   
  group by ob.BuyerId ) ob  on oc.Keyid = ob.BuyerId

                         " + where;


            fromSouce += " )as t   ";

            var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, " CreateTime DESC ");//执行查询
            return null == result ? null : result;//返回结果
        }



        /// <summary>
        /// 获取业务经理业绩
        /// </summary>
        /// <returns></returns>

        public IEnumerable<OA_DeliverPlan> GetxiaoshouyejiList(Infrastructure.Query.Pagination pagination, Guid FirmId, string BusinessManagerId, string chaxuntime)
        {
            string selectTarget = " t.* ";
            string where = " and  os.[FirmId] ='" + FirmId + "'";

            if (!string.IsNullOrEmpty(BusinessManagerId))
            {
                where += " and os.Keyid='" + BusinessManagerId + "'";
            }

            string fromSouce = "(  ";

              fromSouce += "   select os.DepartmentId,os.[Name] as BusinessManager ,os.[Keyid] as BusinessManagerId, os.FirmId,gendan.gendanshuliang,xinzeng.xinzengshuliang,wanjie.wanjieshuliang,xiadan.xiadanshuliang,ob.shuliang,ob.SumPrice,ob.PayedMoney,eob.kaipiaojine,huifang.huifangshuliang ,'"+ chaxuntime + "' as chaxuntime ";
            fromSouce += "  from [OA_Staff] os    ";
            fromSouce += "  left join  (  select  COUNT(*) gendanshuliang ,oc.BusinessManagerId from OA_CorporateClients oc   where ( oc.Shifouwanjie is null or oc.Shifouwanjie=2  ) and     DATEDIFF(MONTH,oc.CreateTime,'" + chaxuntime + "') >=0   group by oc.BusinessManagerId   ) gendan on gendan.BusinessManagerId = os.Keyid  ";
            fromSouce += "    left join  (  select  COUNT(*) xinzengshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where  DATEDIFF(MONTH,oc.CreateTime,'"+ chaxuntime + "') =0 group by oc.BusinessManagerId   ) xinzeng on xinzeng.BusinessManagerId = os.Keyid ";
            fromSouce += "    left join  (  select  COUNT(*) wanjieshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where  oc.Shifouwanjie=1 and   DATEDIFF(MONTH,oc.[LastUpdateTime],'" + chaxuntime + "') =0  group by oc.BusinessManagerId   ) wanjie on wanjie.BusinessManagerId = os.Keyid  ";
            fromSouce += "   left join  (  select COUNT( distinct oc.Keyid)  xiadanshuliang ,oc.BusinessManagerId from OA_CorporateClients oc  Inner Join EC_OrderBasic ob On oc.Keyid=ob.[BuyerId]  where DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "') =0   group by oc.BusinessManagerId   ) xiadan on xiadan.BusinessManagerId = os.Keyid  ";
            fromSouce += "   left join ( select  COUNT(*)  huifangshuliang,occ.Creater as BusinessManagerId  from  OA_CorporateClientsVisit occ   where DATEDIFF(MONTH,occ.[VisitTime],'" + chaxuntime + "') =0  group by occ.Creater  ) huifang on huifang.BusinessManagerId = os.MemberId ";
            fromSouce += "  left join ( select  oc.BusinessManagerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang  ";
            fromSouce += "  from EC_OrderBasic ob   ";
            fromSouce += "  Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid)   ";
            fromSouce += "  Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)  ";
            fromSouce += "  Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid)  ";
            fromSouce += "  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid    where DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "') =0   ";
            fromSouce += "  group by  oc.BusinessManagerId ) ob  on os.Keyid = ob.BusinessManagerId  ";
            fromSouce += " left join ( select  oc.BusinessManagerId,sum(ob.SumPrice) kaipiaojine    ";
            fromSouce += "  from EC_OrderBasic ob   ";
            fromSouce += "   Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid)   ";
            fromSouce += "  Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid)  ";
            fromSouce += "  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid  ";
            fromSouce += "  left join OA_Kaipiaoshenqing  ks  on ks.Keyid = oe.KaipiaoshenqingId  ";
            fromSouce += " where ks.Kaipiaoshenqing = 3   and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "') =0   ";
            fromSouce += "  group by  oc.BusinessManagerId ) eob  on os.Keyid = eob.BusinessManagerId ";
            fromSouce += "   where os.Status='在职'  And IsBusinessManager='true'       " + where;
            

            fromSouce += " )as t   ";

            var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, "  DepartmentId ASC");//执行查询

            foreach(var oA_DeliverPlan  in result)
            {
                StringBuilder from = new StringBuilder();
                var datetimenow = DateTime.Now.ToString("yyyy-MM-dd");
                from.Append(" ( ");
                from.Append("  select ob.*  from  OA_Staff os inner join [OA_CorporateClients] oc  on oc.BusinessManagerId = os.Keyid  and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + "  and oc.Paytime =1 ");
                from.Append(" inner join EC_OrderBasic ob on  ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2  and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0  and DATEDIFF(MONTH,ob.CreateTime,'"+ datetimenow + "') > 0  ");
                from.Append(" inner join EC_OrderExtend   oe on  ob.Keyid=oe.Keyid   and  oe.KaipiaoshenqingId is null   ");
                from.Append("  UNION ");
                from.Append(" select ob.*  from  OA_Staff os inner join [OA_CorporateClients] oc  on oc.BusinessManagerId = os.Keyid  and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + "  and oc.Paytime =2  ");
                from.Append("  inner join EC_OrderBasic ob on  ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2  and  DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0  and  DATENAME(year, ob.CreateTime) +'第'+DATENAME(quarter,  ob.CreateTime) <DATENAME(year, '"+ datetimenow + "') +'第'+DATENAME(quarter,  '"+ datetimenow + "') ");
                from.Append(" inner join EC_OrderExtend   oe on  ob.Keyid=oe.Keyid   and  oe.KaipiaoshenqingId is null   ");
                from.Append(" UNION  ");
                from.Append("  select  ob.*  from  OA_Staff os inner join [OA_CorporateClients] oc  on oc.BusinessManagerId = os.Keyid  and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + "  and oc.Paytime =3 ");
                from.Append("  inner join EC_OrderBasic ob on  ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2  and  DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0   and DATENAME(year, ob.CreateTime) +'第'+CONVERT(varchar,(month(ob.CreateTime)+5)/6)+'半年'<DATENAME(year,'"+ datetimenow + "') +'第'+CONVERT(varchar,(month('"+ datetimenow + "')+5)/6)+'半年' ");
                from.Append("  inner join EC_OrderExtend   oe on  ob.Keyid=oe.Keyid   and  oe.KaipiaoshenqingId is null  ");
                from.Append("  UNION ");
                from.Append("  select ob.*  from  OA_Staff os inner join [OA_CorporateClients] oc  on oc.BusinessManagerId = os.Keyid  and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + "  and oc.Paytime =4 ");
                from.Append("  inner join EC_OrderBasic ob on  ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2  and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0   and DATENAME(year, ob.CreateTime)+'年' <DATENAME(year, '"+ datetimenow + "')+'年'  ");
                from.Append("  inner join EC_OrderExtend   oe on  ob.Keyid=oe.Keyid   and  oe.KaipiaoshenqingId is null  ");
                from.Append("  UNION ");
                from.Append(" select ob.*  from  OA_Staff os inner join [OA_CorporateClients] oc  on oc.BusinessManagerId = os.Keyid  and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + "  and oc.Paytime =6 and oc.Shifouwanjie=1  ");
                from.Append(" inner join EC_OrderBasic ob on  ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2  and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0   ");
                from.Append(" inner join EC_OrderExtend   oe on  ob.Keyid=oe.Keyid   and  oe.KaipiaoshenqingId is null  ");
                from.Append("  UNION ");
                from.Append(" select ob.*  from  OA_Staff os inner join [OA_CorporateClients] oc  on oc.BusinessManagerId = os.Keyid  and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + "  and oc.Paytime =7  ");
                from.Append("  inner join EC_OrderBasic ob on  ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2  and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0   and DATEDIFF(day,ob.CreateTime,GETDATE()) > oc.Paydays ");
                from.Append("  inner join EC_OrderExtend   oe on  ob.Keyid=oe.Keyid   and  oe.KaipiaoshenqingId is null  ");
                from.Append("  UNION ");
                from.Append(" select   ob.*  from  OA_Staff os inner join [OA_CorporateClients] oc  on oc.BusinessManagerId = os.Keyid  and os.Keyid="+ oA_DeliverPlan.BusinessManagerId + "  and oc.Paytime =5  ");
                from.Append("  inner join EC_OrderBasic ob on  ob.BuyerId =oc.Keyid and ob.OrderState !=0 and ob.OrderState !=-2  and DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "')=0   and ");
                from.Append(" case when DATEDIFF(day,ob.CreateTime,DATENAME(year, ob.CreateTime)+'-03-01')>0    then CONVERT(varchar,YEAR(ob.CreateTime)-1)+'至'+CONVERT(varchar,YEAR(ob.CreateTime))+ '1学期'  ");
                from.Append("     when DATEDIFF(day,ob.CreateTime,DATENAME(year, ob.CreateTime)+'-09-01')<=0    then CONVERT(varchar,YEAR(ob.CreateTime))+'至'+CONVERT(varchar,YEAR(ob.CreateTime)+1)+ '1学期' ");
                from.Append("  else CONVERT(varchar,YEAR(ob.CreateTime)-1)+'至'+CONVERT(varchar,YEAR(ob.CreateTime))+'2学期' end  <  ");
                from.Append("   case when DATEDIFF(day,'"+ datetimenow + "',DATENAME(year, '"+ datetimenow + "')+'-03-01')>0    then CONVERT(varchar,YEAR('"+ datetimenow + "')-1)+'至'+CONVERT(varchar,YEAR('"+ datetimenow + "'))+ '1学期'  ");
                from.Append("    when DATEDIFF(day,'"+ datetimenow + "',DATENAME(year, '"+ datetimenow + "')+'-09-01')<=0    then CONVERT(varchar,YEAR('"+ datetimenow + "'))+'至'+CONVERT(varchar,YEAR('"+ datetimenow + "')+1)+ '1学期'  ");
                from.Append("   else CONVERT(varchar,YEAR('"+ datetimenow + "')-1)+'至'+CONVERT(varchar,YEAR('"+ datetimenow + "'))+'2学期' end  ");
                from.Append(" inner join EC_OrderExtend   oe on  ob.Keyid=oe.Keyid   and  oe.KaipiaoshenqingId is null   ");


                from.Append(" ) as t ");


                var eC_OrderBasics = _dataBase.SelectModel<Model.EC_OrderBasic>(" t.* ", from.ToString());
                oA_DeliverPlan.fapiaoshuliang = eC_OrderBasics.Count;
            }

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



        /// <summary>
        /// 获取业务经理业绩
        /// </summary>
        /// <returns></returns>

        public IEnumerable<OA_DeliverPlan> GetyewurenportList(Infrastructure.Query.Pagination pagination, Guid FirmId, string BusinessManagerId,  string chaxuntimestart, string chaxuntimeend)
        {
            string selectTarget = " t.* ";
            string where = " and  os.[FirmId] ='" + FirmId + "'";

            if (!string.IsNullOrEmpty(BusinessManagerId))
            {
                where += " and os.Keyid='" + BusinessManagerId + "'";
            }

            string fromSouce = "(  ";

            fromSouce += "   select os.DepartmentId,os.[Name] as BusinessManager ,os.[Keyid] as BusinessManagerId, os.FirmId,gendan.gendanshuliang,xinzeng.xinzengshuliang,wanjie.wanjieshuliang,xiadan.xiadanshuliang,ob.shuliang,ob.SumPrice,ob.PayedMoney,eob.kaipiaojine,huifang.huifangshuliang ,'" + chaxuntimestart + "' as chaxuntimestart  ,'" + chaxuntimeend + "' as chaxuntimeend ,obhuikuan.huikuankehu,obweihuikuan.weihuikuankehu ";
            fromSouce += "  from [OA_Staff] os    ";
            fromSouce += "  left join  (  select  COUNT(*) gendanshuliang ,oc.BusinessManagerId from OA_CorporateClients oc   where ( oc.Shifouwanjie is null or oc.Shifouwanjie=2  ) and     DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimestart + "') <=0 and     DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimeend + "') >=0  group by oc.BusinessManagerId   ) gendan on gendan.BusinessManagerId = os.Keyid  ";
            fromSouce += "    left join  (  select  COUNT(*) xinzengshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where  DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimestart + "') <=0 and  DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimeend + "') >=0 group by oc.BusinessManagerId   ) xinzeng on xinzeng.BusinessManagerId = os.Keyid ";
            fromSouce += "    left join  (  select  COUNT(*) wanjieshuliang ,oc.BusinessManagerId from OA_CorporateClients oc where  oc.Shifouwanjie=1 and   DATEDIFF(DAY,oc.[LastUpdateTime],'" + chaxuntimestart + "') <=0 and   DATEDIFF(DAY,oc.[LastUpdateTime],'" + chaxuntimeend + "') >=0  group by oc.BusinessManagerId   ) wanjie on wanjie.BusinessManagerId = os.Keyid  ";
            fromSouce += "   left join  (  select COUNT( distinct oc.Keyid)  xiadanshuliang ,oc.BusinessManagerId from OA_CorporateClients oc  Inner Join EC_OrderBasic ob On oc.Keyid=ob.[BuyerId]  where DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and  DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0   group by oc.BusinessManagerId   ) xiadan on xiadan.BusinessManagerId = os.Keyid  ";
            fromSouce += "   left join ( select  COUNT(*)  huifangshuliang,occ.Creater as BusinessManagerId  from  OA_CorporateClientsVisit occ   where DATEDIFF(DAY,occ.[VisitTime],'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,occ.[VisitTime],'" + chaxuntimeend + "') >=0  group by occ.Creater  ) huifang on huifang.BusinessManagerId = os.MemberId ";
            fromSouce += "  left join ( select  oc.BusinessManagerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney)  PayedMoney ,COUNT(*) as shuliang  ";
            fromSouce += "  from EC_OrderBasic ob   ";
            fromSouce += "  Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid)   ";
            fromSouce += "  Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)  ";
            fromSouce += "  Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid)  ";
            fromSouce += "  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid    where DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0  and  DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0  ";
            fromSouce += "  group by  oc.BusinessManagerId ) ob  on os.Keyid = ob.BusinessManagerId  ";

            fromSouce += "  left join ( select  oc.BusinessManagerId ,COUNT( distinct ob.BuyerId ) as huikuankehu  ";
            fromSouce += "  from EC_OrderBasic ob   ";
            fromSouce += "  Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid)   ";
            fromSouce += "  Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)  ";
            fromSouce += "  Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid)  ";
            fromSouce += "  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid    where DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0  and  DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0   and ob.PayState = -1 ";
            fromSouce += "  group by  oc.BusinessManagerId ) obhuikuan  on os.Keyid = obhuikuan.BusinessManagerId  ";


            fromSouce += "  left join ( select  oc.BusinessManagerId ,COUNT( distinct ob.BuyerId ) as weihuikuankehu  ";
            fromSouce += "  from EC_OrderBasic ob   ";
            fromSouce += "  Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid)   ";
            fromSouce += "  Inner Join Pay_TradingSingle as ts On(ob.Keyid = ts.OrderId)  ";
            fromSouce += "  Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid)  ";
            fromSouce += "  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid    where DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0  and  DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0   and ob.PayState != -1 ";
            fromSouce += "  group by  oc.BusinessManagerId ) obweihuikuan  on os.Keyid = obweihuikuan.BusinessManagerId  ";


            fromSouce += " left join ( select  oc.BusinessManagerId,sum(ob.SumPrice) kaipiaojine    ";
            fromSouce += "  from EC_OrderBasic ob   ";
            fromSouce += "   Inner Join EC_OrderExtend as oe On(ob.Keyid=oe.Keyid)   ";
            fromSouce += "  Inner Join OA_CorporateClients as oc On(ob.BuyerId = oc.Keyid)  ";
            fromSouce += "  left join EC_AwbInfo ea  on ob.Keyid = ea.Keyid  ";
            fromSouce += "  left join OA_Kaipiaoshenqing  ks  on ks.Keyid = oe.KaipiaoshenqingId  ";
            fromSouce += " where ks.Kaipiaoshenqing = 3   and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0   and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0  ";
            fromSouce += "  group by  oc.BusinessManagerId ) eob  on os.Keyid = eob.BusinessManagerId ";
            fromSouce += "   where os.Status='在职'  And IsBusinessManager='true'       " + where;


            fromSouce += " )as t   ";

            var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, "  DepartmentId ASC");//执行查询
            return null == result ? null : result;//返回结果
        }
        

    }
}