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_DeliverPlanDAL: IOA_DeliverPlanDAL
    {

        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_DeliverPlan] od left join  [OA_CorporateClients] oe  on oe.Keyid = od.[BuyerId] " +
        // " inner join (select  count(*) as shuliang,eo.BuyerId,ISNULL(eoe.DelayTime,eo.DeliveryTime)  DeliveryTime from  [EC_OrderBasic] eo inner join [EC_OrderExtend] eoe on eo.Keyid=eoe.Keyid where eo.OrderState =4 group by eo.BuyerId,ISNULL(eoe.DelayTime,eo.DeliveryTime)  )   eo on oe.Keyid =eo.BuyerId " +  
             " left join (select  count(*) as shuliang,eoe.DeliveryOrderId  from  [EC_OrderBasic] eo  ,[EC_OrderExtend] eoe  where eoe.Keyid=eo.Keyid group by eoe.DeliveryOrderId  )   eo on od.Keyid =eo.DeliveryOrderId " + // eo.OrderState =4
           // " inner join [OA_CustomerCommunications] oc on oe.Keyid = oc.Keyid " +
        //"   left join [OA_DeliverPlan] 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   ";
        #endregion
        public OA_DeliverPlanDAL()
        {
            _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),
            };
            try
            {
                _dataBase.Query("sp_OA_DeliverPlan_Insert", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public bool UpdateModel(Infrastructure.Domain.IAggregateRoot model)
        {
            Model.OA_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",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),
                                  new SqlParameter("@LastUpdateTime",trueModel.LastUpdateTime.HasValue?trueModel.LastUpdateTime.Value:DateTime.MinValue),
                                    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",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),
            };
            try
            {
                _dataBase.Query("sp_OA_DeliverPlan_Update", CommandType.StoredProcedure, 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_DeliverPlan", 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_DeliverPlan ") 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_DeliverPlan ", " FirmId = '" + FirmId + "'") 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="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)
            };
            try
            {
                _dataBase.Query("sp_OA_DeliverPlan_DeleteRow", CommandType.StoredProcedure, sqlParms.ToArray<SqlParameter>());
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }
    }
}