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 System.Transactions;

namespace CY.SQLDAL
{
    public class OA_DeliverPlanPaicheDAL : IOA_DeliverPlanPaicheDAL
    {

        private Database _dataBase = null;
        #region 常量
        /// <summary>
        /// 查询目标 
        /// </summary>
        const string SELECTTARGET = " t.* ";
        /// <summary>
        /// 查询来源
        /// </summary>
        const string FROMSOUCEBEFORE = " ( select distinct od.*,d_Shifouwanjie.Name as TransferstatusName, " +
             "     od.CompanyName as BuyerName,eo.shuliang, os.Name as BusinessManager,od.Dizhi+' 联系人：'+od.Lianxiren+' 电话：'+od.Dianhua as  DizhiLianxirenDianhua,  " +
            "   ocar.[CarNumber] as CarName,oss.Name as DriverName " +
          " from [OA_DeliverPlanPaiche] od left join  [OA_CorporateClients] oe  on oe.Keyid = od.[BuyerId] " +

             " left join (select  count(*) as shuliang,eo.DeliverPlanPaicheid  from  [OA_DeliverPlan] eo    group by eo.DeliverPlanPaicheid  )   eo on od.Keyid =eo.[DeliverPlanPaicheid] " + // eo.OrderState =4
                                                                                                                                                                                                                               // " inner join [OA_CustomerCommunications] oc on oe.Keyid = oc.Keyid " +
                                                                                                                                                                                                                               //"   left join [OA_DeliverPlanPaiche] od on oe.Keyid = od.[BuyerId] and DATEDIFF(day,eo.[DeliveryTime], od.[DeliverTime])=0  " +
        " left join [OA_Staff] os on od.BusinessManagerId = os.Keyid " +
        " left join [OA_CarManage] ocar on od.[CarId] = ocar.Keyid " +
              " left join [OA_Staff] oss on od.[DriverId] = oss.Keyid " +
        " Left Join Sys_Dictionary as d_Shifouwanjie On(d_Shifouwanjie.DicType= '送货单移交状态' and od.[Transferstatus]= d_Shifouwanjie.MeanValue)  where 0=0  ";
        const string FROMSOUCEEND = ") as t  ";
        /// <summary>
        /// 分页默认排序字段
        /// </summary>
        const string ORDERBY = " case when Sort is null then 1 else 0 end asc,Sort asc   ";


        /// <summary>
        /// 查询目标 
        /// </summary>
        const string SELECTTARGET1 = " t.* ";
        /// <summary>
        /// 查询来源
        /// </summary>
        const string FROMSOUCEBEFORE1 = " ( select  od.*, " +
            "   oss.Name as DriverName, os.Name as ZerenrenName " +
          " from [OA_DeliverPlanPaiche] od  " +
        " left join [OA_Staff] os on od.Zerenren = os.Keyid " +
          " left join [OA_Staff] oss on od.[DriverId] = oss.Keyid " +

        "   where 0=0  ";
        const string FROMSOUCEEND1 = ") as t  ";
        /// <summary>
        /// 分页默认排序字段
        /// </summary>
        const string ORDERBY1 = " DeliveredTime desc   ";

        #endregion
        public OA_DeliverPlanPaicheDAL()
        {
            _dataBase = new Database();
        }

        /// <summary>
        /// 新增
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool InserModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_DeliverPlan trueModel = model as Model.OA_DeliverPlan;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter DeliveredTime = null;
            if (trueModel.DeliveredTime.HasValue)
            {
                DeliveredTime = new SqlParameter("@DeliveredTime", trueModel.DeliveredTime.Value);
            }
            else
            {
                DeliveredTime = new SqlParameter("@DeliveredTime", DBNull.Value);
            }

            SqlParameter TransfersTime = null;
            if (trueModel.TransfersTime.HasValue)
            {
                TransfersTime = new SqlParameter("@TransfersTime", trueModel.TransfersTime.Value);
            }
            else
            {
                TransfersTime = new SqlParameter("@TransfersTime", DBNull.Value);
            }

            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {

                 new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@FirmId",trueModel.FirmId),
                    new SqlParameter("@BuyerId",trueModel.BuyerId),
                    DeliveredTime,
                    new SqlParameter("@Zhuyishixiang",trueModel.Zhuyishixiang),
                    new SqlParameter("@Gongzuokaoping",trueModel.Gongzuokaoping),
                    new SqlParameter("@CarId",trueModel.CarId),
                      new SqlParameter("@DriverId",trueModel.DriverId),
                        new SqlParameter("@Transferstatus",trueModel.Transferstatus),
                          TransfersTime,
                            new SqlParameter("@Creater",trueModel.Creater),
                              new SqlParameter("@CreateTime",trueModel.CreateTime),
                                new SqlParameter("@Updater",trueModel.Updater),
                                  new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime),
                                    new SqlParameter("@Sort",trueModel.Sort),
                                    new SqlParameter("@DeliverTime",trueModel.DeliverTime),
                                      new SqlParameter("@wentifankui",trueModel.wentifankui),
                                       new SqlParameter("@Yichangchuli",trueModel.Yichangchuli),
                                        new SqlParameter("@Xingzhengzhuize",trueModel.Xingzhengzhuize),
                                         new SqlParameter("@SellerOrderId",trueModel.SellerOrderId),
                                          new SqlParameter("@Dianhua",trueModel.Dianhua),
                                           new SqlParameter("@Dizhi",trueModel.Dizhi),
                                            new SqlParameter("@Remark",trueModel.Remark),
                                             new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId),
                                              new SqlParameter("@Biddingcompany",trueModel.Biddingcompany),
                                               new SqlParameter("@shifouDelivery",trueModel.shifouDelivery),
                                                new SqlParameter("@Lianxiren",trueModel.Lianxiren),
                                                     new SqlParameter("@CompanyName",string.IsNullOrEmpty(trueModel.CompanyName)?"":trueModel.CompanyName),
                                                     new SqlParameter("@Deliveredstatus",trueModel.Deliveredstatus.HasValue?trueModel.Deliveredstatus.Value:0),
                                                     new SqlParameter("@TransferType",trueModel.TransferType.HasValue?trueModel.TransferType.Value:0),
                                                     new SqlParameter("@TransferName",string.IsNullOrEmpty(trueModel.TransferName)?"":trueModel.TransferName),
            };
            string sql = "Insert Into [OA_DeliverPlanPaiche] ([Keyid],[FirmId],[BuyerId],[DeliveredTime],[Zhuyishixiang],[Gongzuokaoping], [CarId], [DriverId],[Transferstatus], [TransfersTime], [Creater],[CreateTime],[Updater],[LastUpdateTime],[Sort],DeliverTime,wentifankui,Yichangchuli,Xingzhengzhuize,SellerOrderId,Dianhua,Dizhi,Remark,BusinessManagerId,Biddingcompany,shifouDelivery,Lianxiren,CompanyName,Deliveredstatus,TransferType,TransferName)"
                                             + " Values ( @Keyid,@FirmId,@BuyerId,@DeliveredTime, @Zhuyishixiang,@Gongzuokaoping, @CarId, @DriverId, @Transferstatus, @TransfersTime,  @Creater,@CreateTime,@Updater,@LastUpdateTime,@Sort,@DeliverTime,@wentifankui,@Yichangchuli,@Xingzhengzhuize,@SellerOrderId,@Dianhua,@Dizhi,@Remark,@BusinessManagerId,@Biddingcompany,@shifouDelivery,@Lianxiren,@CompanyName,@Deliveredstatus,@TransferType,@TransferName )";
            try
            {
                _dataBase.ExecuteSql(sql, 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_DeliverPlan trueModel = model as Model.OA_DeliverPlan;
            if (trueModel == null)
            {
                return false;
            }
            SqlParameter DeliveredTime = null;
            if (trueModel.DeliveredTime.HasValue)
            {
                DeliveredTime = new SqlParameter("@DeliveredTime", trueModel.DeliveredTime.Value);
            }
            else
            {
                DeliveredTime = new SqlParameter("@DeliveredTime", DBNull.Value);
            }

            SqlParameter TransfersTime = null;
            if (trueModel.TransfersTime.HasValue)
            {
                TransfersTime = new SqlParameter("@TransfersTime", trueModel.TransfersTime.Value);
            }
            else
            {
                TransfersTime = new SqlParameter("@TransfersTime", DBNull.Value);
            }

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

            SqlParameter WanjieTime = null;
            if (trueModel.WanjieTime.HasValue)
            {
                WanjieTime = new SqlParameter("@WanjieTime", trueModel.WanjieTime.Value);
            }
            else
            {
                WanjieTime = new SqlParameter("@WanjieTime", DBNull.Value);
            }
            SqlParameter ZerenTime = null;
            if (trueModel.ZerenTime.HasValue)
            {
                ZerenTime = new SqlParameter("@ZerenTime", trueModel.ZerenTime.Value);
            }
            else
            {
                ZerenTime = new SqlParameter("@ZerenTime", DBNull.Value);
            }

            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {

                 new SqlParameter("@Keyid",trueModel.Keyid),
                    new SqlParameter("@FirmId",trueModel.FirmId),
                    new SqlParameter("@BuyerId",trueModel.BuyerId),
                    DeliveredTime,
                    new SqlParameter("@Zhuyishixiang",string.IsNullOrEmpty(trueModel.Zhuyishixiang)?"":trueModel.Zhuyishixiang  ),
                    new SqlParameter("@Gongzuokaoping",string.IsNullOrEmpty(trueModel.Gongzuokaoping)?"":trueModel.Gongzuokaoping ),
                    new SqlParameter("@CarId",trueModel.CarId),
                      new SqlParameter("@DriverId",trueModel.DriverId),
                        new SqlParameter("@Transferstatus",trueModel.Transferstatus),
                          TransfersTime,
                            new SqlParameter("@Creater",trueModel.Creater),
                              new SqlParameter("@CreateTime",trueModel.CreateTime),
                                new SqlParameter("@Updater",trueModel.Updater.HasValue?trueModel.Updater.Value:Guid.Empty),
                                 LastUpdateTime,
                                    new SqlParameter("@Sort",trueModel.Sort),
                                        new SqlParameter("@DeliverTime",trueModel.DeliverTime),
                                         new SqlParameter("@wentifankui",string.IsNullOrEmpty(trueModel.wentifankui)?"":trueModel.wentifankui),
                                           new SqlParameter("@Yichangchuli",string.IsNullOrEmpty(trueModel.Yichangchuli)?"":trueModel.Yichangchuli),
                                        new SqlParameter("@Xingzhengzhuize",string.IsNullOrEmpty(trueModel.Xingzhengzhuize)?"":trueModel.Xingzhengzhuize),
                                        new SqlParameter("@SellerOrderId",string.IsNullOrEmpty(trueModel.SellerOrderId)?"":trueModel.SellerOrderId),
                                          new SqlParameter("@Dianhua",trueModel.Dianhua),
                                           new SqlParameter("@Dizhi",trueModel.Dizhi),
                                            new SqlParameter("@Remark",trueModel.Remark),
                                             new SqlParameter("@BusinessManagerId",trueModel.BusinessManagerId),
                                              new SqlParameter("@Biddingcompany",trueModel.Biddingcompany.HasValue?trueModel.Biddingcompany.Value:0),
                                               new SqlParameter("@shifouDelivery",trueModel.shifouDelivery),
                                                new SqlParameter("@Lianxiren",trueModel.Lianxiren),
                                                  new SqlParameter("@CompanyName",string.IsNullOrEmpty(trueModel.CompanyName)?"":trueModel.CompanyName),
                                                     new SqlParameter("@Deliveredstatus",trueModel.Deliveredstatus.HasValue?trueModel.Deliveredstatus.Value:0),
                                                     new SqlParameter("@TransferType",trueModel.TransferType.HasValue?trueModel.TransferType.Value:0),
                                                     new SqlParameter("@TransferName",string.IsNullOrEmpty(trueModel.TransferName)?"":trueModel.TransferName),

                                                     new SqlParameter("@Wanjiestatus",trueModel.Wanjiestatus.HasValue?trueModel.Wanjiestatus.Value:0),
                                                     WanjieTime,
                                                      new SqlParameter("@Wanjieren",string.IsNullOrEmpty(trueModel.Wanjieren)?"":trueModel.Wanjieren),
                                                      new SqlParameter("@Zerenren",trueModel.Zerenren.HasValue?trueModel.Zerenren.Value:0),
                                                      new SqlParameter("@Fakuan",trueModel.Fakuan.HasValue?trueModel.Fakuan.Value:0),
                                                      ZerenTime,
                                                      new SqlParameter("@Zerenoperater",string.IsNullOrEmpty(trueModel.Zerenoperater)?"":trueModel.Zerenoperater),



            };
            string sql = "Update OA_DeliverPlanPaiche Set [FirmId]=@FirmId,[BuyerId]=@BuyerId,[DeliveredTime]=@DeliveredTime,[Zhuyishixiang]=@Zhuyishixiang,Gongzuokaoping=@Gongzuokaoping, [CarId]=@CarId, [DriverId]=@DriverId,[Transferstatus]=@Transferstatus, [TransfersTime]=@TransfersTime, [Creater]=@Creater,[CreateTime]=@CreateTime,[Updater]=@Updater,[LastUpdateTime]=@LastUpdateTime,Sort=@Sort,DeliverTime=@DeliverTime,wentifankui=@wentifankui,Yichangchuli=@Yichangchuli,Xingzhengzhuize=@Xingzhengzhuize,SellerOrderId=@SellerOrderId,Dianhua=@Dianhua,Dizhi=@Dizhi,Remark=@Remark,BusinessManagerId=@BusinessManagerId,Biddingcompany=@Biddingcompany,shifouDelivery=@shifouDelivery,Lianxiren=@Lianxiren,CompanyName=@CompanyName,Deliveredstatus=@Deliveredstatus,TransferType=@TransferType,TransferName=@TransferName,Wanjiestatus=@Wanjiestatus,WanjieTime=@WanjieTime,Wanjieren=@Wanjieren,Zerenren=@Zerenren,Fakuan=@Fakuan,ZerenTime=@ZerenTime,Zerenoperater=@Zerenoperater where [Keyid] =@Keyid ";

            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 根据编号获得信息
        /// </summary>
        /// <param name="Keyid">编号</param>
        /// <returns></returns>
        public OA_DeliverPlan GetModelByKeyid(Guid? keyid, Guid? BuyerId, DateTime? DeliveryTime)
        {
            try
            {
                string condition = " BuyerId='" + BuyerId + "' and DATEDIFF(day,[DeliveredTime],'" + DeliveryTime + "')=0 ";
                if (!keyid.Equals(Guid.Parse("00000000-0000-0000-0000-000000000000")))
                {
                    condition = " Keyid='" + keyid + "'";
                }

                //  return null;//错误数据返会空 

                IList<OA_DeliverPlan> result = _dataBase.SelectModel<OA_DeliverPlan>("*", "OA_DeliverPlanPaiche", condition) as IList<OA_DeliverPlan>;//执行查询

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


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

        /// <summary>
        /// 全部查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_DeliverPlan> GetModelByformid(Guid FirmId)
        {
            return _dataBase.SelectModel<Model.OA_DeliverPlan>(" * ", " OA_DeliverPlanPaiche ", " FirmId = '" + FirmId + "'") as IList<Model.OA_DeliverPlan>;//执行查询
        }



        /// <summary>
        /// 全部查询
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_DeliverPlan> GetModelByDeliveredTime(Guid FirmId, DateTime DeliverTime)
        {
            return _dataBase.SelectModel<Model.OA_DeliverPlan>(" * ", " OA_DeliverPlanPaiche ", " FirmId = '" + FirmId + "' and  DATEDIFF(day,[DeliverTime],'" + DeliverTime + "')=0 ") as IList<Model.OA_DeliverPlan>;//执行查询
        }



        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_DeliverPlan> 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 = query.Criteria[maxParamIndex].Value.ToString();//= 1 == orderbys.Length ? resultOrderBy : orderbys[1];
            }
            string fromSouce = string.Format("{0}{1}{2}", FROMSOUCEBEFORE, query.Criteria[0].Value, FROMSOUCEEND);//拼装条件
            return _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, SELECTTARGET, fromSouce, orderbys[0], resultOrderBy);
        }



        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="query"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public IEnumerable<Model.OA_DeliverPlan> SelectModelPageWenti(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[] { ORDERBY1 };
            string resultOrderBy = "";//结果集排序方式

            if ("@orderBy".Equals(query.Criteria[maxParamIndex].PropertyName))
            {
                orderbys = string.Format("{0}", query.Criteria[maxParamIndex].Value).Split(',');
                resultOrderBy = query.Criteria[maxParamIndex].Value.ToString();//= 1 == orderbys.Length ? resultOrderBy : orderbys[1];
            }
            string fromSouce = string.Format("{0}{1}{2}", FROMSOUCEBEFORE1, query.Criteria[0].Value, FROMSOUCEEND1);//拼装条件
            return _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, SELECTTARGET1, fromSouce, orderbys[0], resultOrderBy);
        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool DeleteModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_DeliverPlan trueModel = model as Model.OA_DeliverPlan;
            if (trueModel == null)
            {
                return false;
            }
            IList<SqlParameter> sqlParms = new List<SqlParameter>()
            {
                new SqlParameter("@Keyid",trueModel.Keyid)
            };
            string sql = "Delete [OA_DeliverPlanPaiche] Where [Keyid] = @Keyid ";


            try
            {
                _dataBase.ExecuteSql(sql, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
    }
}