送货管理 增加查询 业务经理 客户名称 完成 查询送货安排的历史 完成

username@email.com

2025-03-07 2fee7b9b90c1acf7a17aef84ee22c2a31b801fe2

 CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs

@@ -880,6 +880,82 @@
            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>
        /// 修改
@@ -1352,7 +1428,7 @@

            fromSouce += @" 
                      
     select occ.Province+occ.City+occ.County as quyu   ,oc.CompanyName as BuyerName,os.Name as BusinessManager,oc.CreateTime,contracttype.Name as ContracttypeName,oc.Contractmoney
     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


@@ -1423,6 +1499,54 @@
            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;//返回结果
        }