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.WebForm/Pages/financial/UnpayOrderList.aspx.designer.cs | 18 +++++++++
CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.cs | 24 +++++++++++
CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs | 37 +++++++++++-------
CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx | 27 ++++++++++++-
4 files changed, 89 insertions(+), 17 deletions(-)
diff --git a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
index 7ddd3d8..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)
@@ -1516,6 +1516,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;//杩斿洖缁撴灉
}
diff --git a/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx b/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx
index 7b22559..7466c61 100644
--- a/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx
+++ b/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx
@@ -182,7 +182,7 @@
</td>
<td>
- <%#Eval("UnPayedMoney", "{0:F}")%>
+ <%#Eval("OrAllUnPayedMoney", "{0:F}")%>
</td>
<td >
@@ -205,7 +205,30 @@
</tr>
</itemtemplate>
</asp:repeater>
-
+ <tr class="StatisticsMoney">
+ <td colspan="4" class="ali03">
+ 褰撳墠椤电粺璁★細
+ </td>
+ <td class="ali01">
+ <span id="NowPageAllMoney" runat="server"></span>
+ </td>
+
+
+ </td>
+ <td class="ali01" colspan="3">
+ </td>
+ </tr>
+ <tr class="StatisticsMoney">
+ <td colspan="4" class="ali03">
+ 鏌ヨ椤圭粺璁★細
+ </td>
+ <td class="ali01">
+ <span id="AllMoney" runat="server"></span>
+ </td>
+
+ <td class="ali01" colspan="3">
+ </td>
+ </tr>
</tbody>
</table>
<uc:ucpager id="UCPager1" runat="server" />
diff --git a/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.cs b/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.cs
index 81446b5..b194184 100644
--- a/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.cs
+++ b/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.cs
@@ -47,7 +47,7 @@
protected void Page_Load(object sender, EventArgs e)
{
-
+ UCPager1.AspNetPager.PageChanged += AspNetPager_PageChanged;
if (!IsPostBack)
{
@@ -93,6 +93,7 @@
//鏌ヨ浜嬩欢
protected void btn_Search_Click(object sender, EventArgs e)
{
+ UCPager1.AspNetPager.CurrentPageIndex = 1;//閲嶇疆椤垫暟
BindList();
}
@@ -127,7 +128,17 @@
oA_DeliverPlan.Cuikuanjilu = ddd.ToString();
}
+ if (pa.heji1.HasValue)
+ {
+ this.AllMoney.InnerText = pa.heji1.Value.ToString("0.00");
+ }
+ var sss = oA_DeliverPlans1.Sum(x => x.OrAllUnPayedMoney);
+ if (sss.HasValue)
+ {
+ this.NowPageAllMoney.InnerText = sss.Value.ToString("0.00");
+ }
+
this.RepClientList.DataSource = oA_DeliverPlans1;
@@ -138,6 +149,17 @@
}
+ /// <summary>
+ /// 鍒嗛〉浜嬩欢
+ /// </summary>
+ /// <param name="sender"></param>
+ /// <param name="e"></param>
+ private void AspNetPager_PageChanged(object sender, EventArgs e)
+ {
+ BindList();
+ }
+
+
//缁戝畾鐪�
protected void BindProvince()
{
diff --git a/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.designer.cs b/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.designer.cs
index f9baec6..6fc6a94 100644
--- a/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.designer.cs
+++ b/CY_ECommercePlatform/CY.WebForm/Pages/financial/UnpayOrderList.aspx.designer.cs
@@ -123,6 +123,24 @@
protected global::System.Web.UI.WebControls.Repeater RepClientList;
/// <summary>
+ /// NowPageAllMoney 鎺т欢銆�
+ /// </summary>
+ /// <remarks>
+ /// 鑷姩鐢熸垚鐨勫瓧娈点��
+ /// 鑻ヨ杩涜淇敼锛岃灏嗗瓧娈靛０鏄庝粠璁捐鍣ㄦ枃浠剁Щ鍒颁唬鐮侀殣钘忔枃浠躲��
+ /// </remarks>
+ protected global::System.Web.UI.HtmlControls.HtmlGenericControl NowPageAllMoney;
+
+ /// <summary>
+ /// AllMoney 鎺т欢銆�
+ /// </summary>
+ /// <remarks>
+ /// 鑷姩鐢熸垚鐨勫瓧娈点��
+ /// 鑻ヨ杩涜淇敼锛岃灏嗗瓧娈靛０鏄庝粠璁捐鍣ㄦ枃浠剁Щ鍒颁唬鐮侀殣钘忔枃浠躲��
+ /// </remarks>
+ protected global::System.Web.UI.HtmlControls.HtmlGenericControl AllMoney;
+
+ /// <summary>
/// UCPager1 鎺т欢銆�
/// </summary>
/// <remarks>
--
Gitblit v1.9.1