From 7992fcc44d231e823736fd36483bf990a28b0536 Mon Sep 17 00:00:00 2001
From: CB2-20200827ONU\Administrator <liaoxujun@qq.com>
Date: 星期五, 08 十月 2021 09:24:29 +0800
Subject: [PATCH] Merge branch 'master' of http://47.108.235.38:8080/r/chuanyin-group-project
---
CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs | 70 +++++++++++++++++++++++++++++++++++
1 files changed, 70 insertions(+), 0 deletions(-)
diff --git a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
index a7c47f2..f2ddddf 100644
--- a/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
+++ b/CY_ECommercePlatform/CY.SQLDAL/EC/EC_OrderDAL.cs
@@ -1369,5 +1369,75 @@
return null == result ? null : result;//杩斿洖缁撴灉
}
+
+
+ /// <summary>
+ /// 鑾峰彇涓氬姟缁忕悊涓氱哗
+ /// </summary>
+ /// <returns></returns>
+
+ public IEnumerable<OA_DeliverPlan> GetyewurenportList(Infrastructure.Query.Pagination pagination, Guid FirmId, string BusinessManagerId, string chaxuntimestart, string chaxuntimeend)
+ {
+ string selectTarget = " t.* ";
+ string where = " and os.[FirmId] ='" + FirmId + "'";
+
+ if (!string.IsNullOrEmpty(BusinessManagerId))
+ {
+ where += " and os.Keyid='" + BusinessManagerId + "'";
+ }
+
+ 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 ,'" + chaxuntimestart + "' as chaxuntimestart ,'" + chaxuntimeend + "' as chaxuntimeend ,obhuikuan.huikuankehu,obweihuikuan.weihuikuankehu ";
+ 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(DAY,oc.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimeend + "') >=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(DAY,oc.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,oc.CreateTime,'" + chaxuntimeend + "') >=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(DAY,oc.[LastUpdateTime],'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,oc.[LastUpdateTime],'" + chaxuntimeend + "') >=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(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=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(DAY,occ.[VisitTime],'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,occ.[VisitTime],'" + chaxuntimeend + "') >=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 += " 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(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 ";
+ fromSouce += " group by oc.BusinessManagerId ) ob on os.Keyid = ob.BusinessManagerId ";
+
+ fromSouce += " left join ( select oc.BusinessManagerId ,COUNT( distinct ob.BuyerId ) as huikuankehu ";
+ 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(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 and ob.PayState = -1 ";
+ fromSouce += " group by oc.BusinessManagerId ) obhuikuan on os.Keyid = obhuikuan.BusinessManagerId ";
+
+
+ fromSouce += " left join ( select oc.BusinessManagerId ,COUNT( distinct ob.BuyerId ) as weihuikuankehu ";
+ 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(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 and ob.PayState != -1 ";
+ fromSouce += " group by oc.BusinessManagerId ) obweihuikuan on os.Keyid = obweihuikuan.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 += " where ks.Kaipiaoshenqing = 3 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimestart + "') <=0 and DATEDIFF(DAY,ob.CreateTime,'" + chaxuntimeend + "') >=0 ";
+ fromSouce += " group by oc.BusinessManagerId ) eob on os.Keyid = eob.BusinessManagerId ";
+ fromSouce += " where os.Status='鍦ㄨ亴' And IsBusinessManager='true' " + where;
+
+
+ fromSouce += " )as t ";
+
+ var result = _dataBase.SelectModelPage<Model.OA_DeliverPlan>(pagination, selectTarget, fromSouce, " DepartmentId ASC");//鎵ц鏌ヨ
+ return null == result ? null : result;//杩斿洖缁撴灉
+ }
+
+
}
}
--
Gitblit v1.9.1