From a05018727c284e1d24967e0cf120a934796191b8 Mon Sep 17 00:00:00 2001
From: 王 垚 <1402714037@qq.com>
Date: 星期二, 02 三月 2021 16:02:05 +0800
Subject: [PATCH] 更改
---
src/BLL/Demo.Report/PRD/prd_ExtractionPlanDetail.cs | 57 +++++++++++++++++++++++++++++++++++++++++++++------------
1 files changed, 45 insertions(+), 12 deletions(-)
diff --git a/src/BLL/Demo.Report/PRD/prd_ExtractionPlanDetail.cs b/src/BLL/Demo.Report/PRD/prd_ExtractionPlanDetail.cs
index c0f1621..7295123 100644
--- a/src/BLL/Demo.Report/PRD/prd_ExtractionPlanDetail.cs
+++ b/src/BLL/Demo.Report/PRD/prd_ExtractionPlanDetail.cs
@@ -24,6 +24,7 @@
public class prd_ExtractionPlanDetail : SysReportBaseService
{
string date = "";
+ string TableName = "";
//鍒濆鍖�
public override void Initialize()
{
@@ -108,6 +109,33 @@
//璁剧疆鍗曟嵁鍒�
public override ReportHeader GetReportHeaders(IRptParams filter)
{
+
+ //鏇存柊鎬绘嫋鏈熸暟閲� 鍜� 鎬绘彁鏂欐暟閲�
+ List<string> sqlList = new List<string>();
+ string sql = string.Format(@"/*dialect*/ update a set a.TQSum = b.FHQTY from {0} a
+ join (
+ select SUM(b.FHQTY)FHQTY, FIDENTITYID from {0} c
+ join Cg_PODemandPlanBillMain a on c.FSUPPLIERID = a.FSUPPLIERID and c.FHPURCHASEORGID = a.FHPURCHASEORGID
+ join(select SUM(FHQTY)FHQTY, FHMATERID, FID from Cg_PODemandPlanBillSub GROUP BY FHMATERID, FID) b on a.FID = B.FID and c.FHMATERID = b.FHMATERID
+ where {1} and datediff(d, FHDATE, getdate())> 0
+ group by FIDENTITYID
+ ) b on a.FIDENTITYID = b.FIDENTITYID",
+ TableName, date);
+ //DBServiceHelper.Execute(Context, sql);
+ sqlList.Add(sql);
+ sql = string.Format(@"/*dialect*/ update a set a.PlanSum = b.FHQTY from {0} a
+ join (
+ select SUM(b.FHQTY)FHQTY,FIDENTITYID from {0} c
+ join Cg_PODemandPlanBillMain a on c.FSUPPLIERID = a.FSUPPLIERID and c.FHPURCHASEORGID = a.FHPURCHASEORGID
+ join (select SUM(FHQTY)FHQTY, FHMATERID, FID from Cg_PODemandPlanBillSub GROUP BY FHMATERID,FID) b on a.FID = B.FID and c.FHMATERID = b.FHMATERID
+ where {1} and datediff(d,FHDATE,getdate())<=0
+ group by FIDENTITYID
+ ) b on a.FIDENTITYID=b.FIDENTITYID",
+ TableName, date);
+ //DBServiceHelper.Execute(Context, sql);
+ sqlList.Add(sql);
+ DBServiceHelper.Execute(Context,string.Join(";", sqlList));
+
ReportHeader header = new ReportHeader();
header.AddChild("FRogNUMBER", new LocaleValue("渚涘簲鍟嗕俊鎭�&閲囪喘缁勭粐", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
header.AddChild("FNAME", new LocaleValue("渚涘簲鍟嗕俊鎭�&渚涘簲鍟�", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
@@ -120,11 +148,19 @@
header.AddChild("FQTY", new LocaleValue("鐗╂枡淇℃伅&搴撳瓨鏁伴噺", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
header.AddChild("FTRANSITQTY", new LocaleValue("鐗╂枡淇℃伅&鍦ㄩ�旀暟閲�", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
- //header.AddChild("FNUMBER", new LocaleValue("鎷栨湡鏄庣粏&鎷栨湡鏁伴噺", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
-
- //header.AddChild("FNUMBER", new LocaleValue("璁″垝鏄庣粏&鍚堣", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
- DataSet ds = DBServiceHelper.ExecuteDataSet(Context, "/*dialect*/select distinct convert(nvarchar(50),FHDATE,111) FHDATE from Cg_PODemandPlanBillMain");
+ header.AddChild("TQSum", new LocaleValue("鎻愭枡鎷栨湡鏄庣粏&鎻愭枡鎷栨湡鏁伴噺", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
+ DataSet ds = DBServiceHelper.ExecuteDataSet(Context, "/*dialect*/select distinct convert(nvarchar(50),FHDATE,23) FHDATE from Cg_PODemandPlanBillMain where " + date + " and datediff(d,FHDATE,getdate())>0 ");
DataTable dt = ds.Tables[0];
+ if (dt.Rows.Count > 0)
+ {
+ foreach (DataRow dr in dt.Rows)
+ {
+ header.AddChild(dr[0].ToString(), new LocaleValue("鎻愭枡鎷栨湡鏄庣粏&" + dr[0].ToString() + "", this.Context.UserLocale.LCID), SqlStorageType.SqlDecimal);
+ }
+ }
+ header.AddChild("PlanSum", new LocaleValue("璁″垝鏄庣粏&鍚堣", this.Context.UserLocale.LCID), SqlStorageType.Sqlvarchar);
+ DataSet _ds = DBServiceHelper.ExecuteDataSet(Context, "/*dialect*/select distinct convert(nvarchar(50),FHDATE,23) FHDATE from Cg_PODemandPlanBillMain where " + date + " and datediff(d,FHDATE,getdate())<=0");
+ dt = _ds.Tables[0];
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
@@ -137,18 +173,15 @@
//鍒涘缓涓存椂鎶ヨ〃
public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
{
+ TableName = tableName;
string Filter = GetFilterWhere(filter);
string seqFld = string.Format(base.KSQL_SEQ, OrderColumn(filter));
+
// 鍙栨暟SQL
// FID, FEntryId, 缂栧彿銆佺姸鎬併�佺墿鏂欍�佹暟閲忋�佸崟浣嶃�佸崟浣嶇簿搴︺�佸崟浠枫�佷环绋庡悎璁� ,t1M_L.FNAME as FMaterialName
- string sql = string.Format(@"/*dialect*/
- exec 鎻愭枡璁″垝鎶ヨ〃 '{0}','{1}','{2}'
- ",
- seqFld,
- tableName
- , Filter
- );
+ string sql = string.Format(@"/*dialect*/exec 鎻愭枡璁″垝鎶ヨ〃 '{0}','{1}'", tableName, Filter.Trim());
DBUtils.ExecuteDynamicObject(this.Context, sql);
+
}
////鑾峰彇杩囨护鏉′欢
private string GetFilterWhere(IRptParams filter)
@@ -160,7 +193,7 @@
string startValue = (customFilter["FBeginConveyDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FBeginConveyDate"]).ToString("yyyy/MM/d");
string endValue = (customFilter["FEndConveyDate"] == null) ? string.Empty : Convert.ToDateTime(customFilter["FEndConveyDate"]).ToString("yyyy/MM/d");
strwhere.AppendLine(string.Format(" AND FHDate>=''{0}'' and FHDate<=''{1}'' ", startValue, endValue));
- date = string.Format(" FHDate>=''{0}'' and FHDate<=''{1}'' ", startValue, endValue);
+ date = string.Format(" FHDate>='{0}' and FHDate<='{1}' ", startValue, endValue);
//鐗╂枡
var baseDataNameValue = customFilter["FMATERIALID"];
string MaterailName = baseDataNameValue == null
--
Gitblit v1.9.1