From 6442fbe375c6b7abdc4b4b1dda7b990685bd02db Mon Sep 17 00:00:00 2001
From: username@email.com <yzy2002yzy@163.com>
Date: 星期三, 25 九月 2024 16:31:37 +0800
Subject: [PATCH] 逾期发票张数
---
CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs | 50 +++++++++++++++++++++++++++++++++++++++++++++++++-
1 files changed, 49 insertions(+), 1 deletions(-)
diff --git a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
index 4f5b643..62147cb 100644
--- a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
+++ b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
@@ -1352,7 +1352,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 +1423,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;//杩斿洖缁撴灉
}
--
Gitblit v1.9.1