From 711fc30d62069784f461426d8c00a30dfd54e1a9 Mon Sep 17 00:00:00 2001
From: username@email.com <yzy2002yzy@163.com>
Date: 星期三, 17 十二月 2025 12:14:11 +0800
Subject: [PATCH] 20251217 逾期款项列表的“未收款金额”下面加个合计
---
CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs | 69 ++++++++++++++++++++++------------
1 files changed, 44 insertions(+), 25 deletions(-)
diff --git a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
index 7399928..682ae65 100644
--- a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
+++ b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
@@ -1371,12 +1371,12 @@
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
+ select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.OrAllUnPayedMoney,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
+select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,SUM( Case When ( (ob.SumPrice-ts.PayedMoney) <=0 or ob.PayState =-1) then 0 else (ob.SumPrice-ts.PayedMoney) end) OrAllUnPayedMoney,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 )
@@ -1388,14 +1388,14 @@
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
+select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.OrAllUnPayedMoney,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
+ select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,SUM( Case When ( (ob.SumPrice-ts.PayedMoney) <=0 or ob.PayState =-1) then 0 else (ob.SumPrice-ts.PayedMoney) end) OrAllUnPayedMoney,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 )
@@ -1408,14 +1408,14 @@
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
+select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.OrAllUnPayedMoney,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
+ select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,SUM( Case When ( (ob.SumPrice-ts.PayedMoney) <=0 or ob.PayState =-1) then 0 else (ob.SumPrice-ts.PayedMoney) end) OrAllUnPayedMoney,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 )
@@ -1428,14 +1428,14 @@
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
+select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.OrAllUnPayedMoney,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
+ select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,SUM( Case When ( (ob.SumPrice-ts.PayedMoney) <=0 or ob.PayState =-1) then 0 else (ob.SumPrice-ts.PayedMoney) end) OrAllUnPayedMoney,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 )
@@ -1448,14 +1448,14 @@
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
+select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.OrAllUnPayedMoney,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
+ select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,SUM( Case When ( (ob.SumPrice-ts.PayedMoney) <=0 or ob.PayState =-1) then 0 else (ob.SumPrice-ts.PayedMoney) end) OrAllUnPayedMoney,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 )
@@ -1469,14 +1469,14 @@
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
+select oc.keyid as BuyerId,oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.OrAllUnPayedMoney,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
+ select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,SUM( Case When ( (ob.SumPrice-ts.PayedMoney) <=0 or ob.PayState =-1) then 0 else (ob.SumPrice-ts.PayedMoney) end) OrAllUnPayedMoney,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 )
@@ -1490,14 +1490,14 @@
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
+ select oc.keyid as BuyerId, oc.[CompanyName] as BuyerName,oc.BusinessManagerId,os.Name as BusinessManager,ob.SumPrice,ob.PayedMoney,ob.OrAllUnPayedMoney,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))+ '涓婂鏈�'
+ select ob.BuyerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,SUM( Case When ( (ob.SumPrice-ts.PayedMoney) <=0 or ob.PayState =-1) then 0 else (ob.SumPrice-ts.PayedMoney) end) OrAllUnPayedMoney,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)
@@ -1515,6 +1515,15 @@
fromSouce += " )as t left join OA_Yujihuikuan oy on t.BuyerId =oy.BuyerId and t.shoukuanshijian = oy.shoukuanshijian ) as t ";
+
+
+ var ssss = _dataBase.SelectModel<Model.OA_DeliverPlan>(" sum(t.OrAllUnPayedMoney) OrAllUnPayedMoney ", fromSouce);
+ if (ssss.Count > 0)
+ {
+ var aaaaa = ssss.First();
+
+ pagination.heji1 = aaaaa.OrAllUnPayedMoney;
+ }
var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, " shoukuanshijian DESC ");//鎵ц鏌ヨ
return null == result ? null : result;//杩斿洖缁撴灉
@@ -1734,26 +1743,26 @@
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 += " 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,ob.OrAllUnPayedMoney ";
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 += " left join ( select oc.BusinessManagerId,sum(ob.SumPrice) SumPrice,sum(ts.PayedMoney) PayedMoney ,COUNT(*) as shuliang ,SUM( Case When ( (ob.SumPrice-ts.PayedMoney) <=0 or ob.PayState =-1) then 0 else (ob.SumPrice-ts.PayedMoney) end) OrAllUnPayedMoney ";
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 += " left join EC_AwbInfo ea on ob.Keyid = ea.Keyid where DATEDIFF(MONTH,ob.CreateTime,'" + chaxuntime + "') =0 and ob.OrderState !=0 and ob.OrderState !=-2 and ob.[SumPrice] >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 += " left join OA_Kaipiaoshenqing ks on ks.Keyid = oe.KaipiaoshenqingId and ob.OrderState !=0 and ob.OrderState !=-2 ";
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;
@@ -1770,27 +1779,28 @@
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(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and ob.PayState !=-1 and ob.[SellerId] = '" + FirmId + "' and ob.[SumPrice] >0 "
+ ); //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(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and ob.PayState !=-1 and ob.[SellerId] = '" + FirmId + "' and ob.[SumPrice] >0 ");
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(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and ob.PayState !=-1 and ob.[SellerId] = '" + FirmId + "' and ob.[SumPrice] >0 ");
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(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and ob.PayState !=-1 and ob.[SellerId] = '" + FirmId + "' and ob.[SumPrice] >0 ");
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(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and ob.PayState !=-1 and ob.[SellerId] = '" + FirmId + "' and ob.[SumPrice] >0 ");
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(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and ob.PayState !=-1 and ob.[SellerId] = '" + FirmId + "' and ob.[SumPrice] >0 ");
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 ");
@@ -1800,7 +1810,7 @@
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(" inner join EC_OrderExtend oe on ob.Keyid=oe.Keyid and ob.PayState !=-1 and ob.[SellerId] = '" + FirmId + "' and ob.[SumPrice] >0 ");
from.Append(" ) as t ");
@@ -1808,6 +1818,15 @@
var eC_OrderBasics = _dataBase.SelectModel<Model.EC_OrderBasic>(" t.* ", from.ToString());
oA_DeliverPlan.fapiaoshuliang = eC_OrderBasics.Count;
+ foreach(var eC_OrderBasic in eC_OrderBasics)
+ {
+ oA_DeliverPlan.fapiaoshuliangName += eC_OrderBasic.Keyid + ",";
+ }
+ if (!string.IsNullOrEmpty(oA_DeliverPlan.fapiaoshuliangName))
+ {
+ oA_DeliverPlan.fapiaoshuliangName = oA_DeliverPlan.fapiaoshuliangName.Trim(',');
+ }
+
}
return null == result ? null : result;//杩斿洖缁撴灉
--
Gitblit v1.9.1