From 3fea7b751b0fe095dd05ce772f364e55bf1f7fdb Mon Sep 17 00:00:00 2001
From: 王 垚 <1402714037@qq.com>
Date: 星期一, 28 十二月 2020 17:41:19 +0800
Subject: [PATCH] 代码上传
---
src/BLL/Demo.Report/PRD/prd_ExtractionPlanDetail.cs | 57 ++++++++++++++++++++++------
src/Model/Demo.Model/Model/PODemandPlan/DayPlanPpbom.cs | 14 +++++++
src/BLL/Demo.BillView/PRD/Pro_WorkBillPlatform.cs | 47 ++++++++++-------------
3 files changed, 79 insertions(+), 39 deletions(-)
diff --git a/src/BLL/Demo.BillView/PRD/Pro_WorkBillPlatform.cs b/src/BLL/Demo.BillView/PRD/Pro_WorkBillPlatform.cs
index 75a9132..c9d38b4 100644
--- a/src/BLL/Demo.BillView/PRD/Pro_WorkBillPlatform.cs
+++ b/src/BLL/Demo.BillView/PRD/Pro_WorkBillPlatform.cs
@@ -1212,7 +1212,7 @@
sql = string.Format(@"
/*dialect*/
- SELECT T1.FID,FHMATERID,FHMASTERDATE,FHQTY,FCOMPLETECOUNT,FPRDMOMAINID,T2.FENTRYID,T1.FBILLNO, FNeedQty,t5.FFIXLEADTIME,t5.FMAXPOQTY,t5.FMINPOQTY,t3.FMATERIALID,t6.FNumber,FHQTY*FNeedQty NeedQty,FHPRDORGID FStockOrgId FROM SC_WORKBILLSORTBILLMAIN T1
+ SELECT T1.FID,FHMATERID,FHMASTERDATE,FHQTY,FCOMPLETECOUNT,FPRDMOMAINID,T2.FENTRYID,T1.FBILLNO, FNeedQty,(FLeadtime+FLeadTtime2)FFIXLEADTIME,t5.FMAXPOQTY,FJITBatch FMINPOQTY,FJITMATERIALGROUP,FJITMaterielDemand,FJITSafeStock,t3.FMATERIALID,t6.FNumber,FHQTY*FNeedQty NeedQty,FHPRDORGID FStockOrgId FROM SC_WORKBILLSORTBILLMAIN T1
JOIN SC_WORKBILLSORTBILLSUB T2 ON T1.FID=T2.FID
LEFT JOIN(SELECT T1.FID,max(t2.FMATERIALID)FMATERIALIDMAIN, max(t2.FMoId)FMoId,MAX(FENTRYID) FENTRYID, SUM(convert(decimal(18, 2), FBASENUMERATOR / FBASEDENOMINATOR))FNeedQty, T1.FMATERIALID from T_PRD_PPBOMENTRY T1
JOIN T_PRD_PPBOM T2 on t1.FID = t2.FID
@@ -1223,7 +1223,7 @@
WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0
AND FDayPlanQuantity>FCOMPLETECOUNT
AND FERPCLSID=1
-AND T2.FENTRYID not in (select distinct FHSOURCEENTRYID from CG_PODEMANDPLANBILLSUB where FHSOURCEINTERID IN ({0}))
+ AND T2.FENTRYID not in (select distinct FHSOURCEENTRYID from CG_PODEMANDPLANBILLSUB where FHSOURCEINTERID IN ({0}))
AND T1.FID in ({0})
order by FHMASTERDATE
", FDayPlanWorkID);
@@ -1659,6 +1659,7 @@
/// </summary>
public void DayPlanPPBomBill()
{
+ //閿佸畾鐨勬棩璁″垝涓嶇敓鎴�
try
{
string sql = string.Format(@"
@@ -1803,15 +1804,19 @@
sql = string.Format(@"
/*dialect*/
- select T1.*,t5.FFIXLEADTIME,t5.FMAXPOQTY,t5.FMINPOQTY,t6.FNumber from (
-select FHICMOINTERID FID,FHICMOENTRYID FEntryID,FPlanDate FHMASTERDATE,FLackCount NeedQty,FHMATERID FMATERIALID,FHStockOrgID FStockOrgId,FPRDBILLNO FBillNo from JIT_MOMaterReadysBill
-)T1
-join t_BD_MaterialBase t4 on T1.FMATERIALID = t4.FMATERIALID
-join t_BD_MaterialPlan t5 on T1.FMATERIALID = t5.FMATERIALID
-JOIN T_BD_MATERIAL T6 ON T1.FMATERIALID = T6.FMATERIALID
-WHERE T1.NeedQty >0 AND FERPCLSID=1
-AND T1.FENTRYID not in (select distinct FHSOURCEENTRYID from CG_PODEMANDPLANBILLSUB where FHSOURCEINTERID IN ({0}))
-AND T1.FID in ({0})
+ SELECT T1.FID,FHMATERID,FHMASTERDATE,FHQTY,FCOMPLETECOUNT,FPRDMOMAINID,T2.FENTRYID,T1.FBILLNO, FNeedQty,(FLeadtime+FLeadTtime2)FFIXLEADTIME,t5.FMAXPOQTY,FJITBatch FMINPOQTY,FJITMATERIALGROUP,FJITMaterielDemand,FJITSafeStock,t3.FMATERIALID,t6.FNumber,FHQTY*FNeedQty NeedQty,FHPRDORGID FStockOrgId FROM SC_WORKBILLSORTBILLMAIN T1
+JOIN SC_WORKBILLSORTBILLSUB T2 ON T1.FID=T2.FID
+LEFT JOIN(SELECT T1.FID,max(t2.FMATERIALID)FMATERIALIDMAIN, max(t2.FMoId)FMoId,MAX(FENTRYID) FENTRYID, SUM(convert(decimal(18, 2), FBASENUMERATOR / FBASEDENOMINATOR))FNeedQty, T1.FMATERIALID from T_PRD_PPBOMENTRY T1
+JOIN T_PRD_PPBOM T2 on t1.FID = t2.FID
+group by T1.FMATERIALID, T1.FID) T3 on T1.FPRDMOMAINID = t3.FMoId and T1.FHMATERID = t3.FMATERIALIDMAIN
+JOIN T_BD_MATERIAL T6 ON T3.FMATERIALID = T6.FMATERIALID
+join t_BD_MaterialBase t4 on t3.FMATERIALID = t4.FMATERIALID
+join t_BD_MaterialPlan t5 on t3.FMATERIALID = t5.FMATERIALID
+WHERE DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0
+AND FDayPlanQuantity>FCOMPLETECOUNT
+AND FERPCLSID=1
+ AND T2.FENTRYID not in (select distinct FHSOURCEENTRYID from CG_PODEMANDPLANBILLSUB where FHSOURCEINTERID IN ({0}))
+AND T1.FID in ({0})
order by FHMASTERDATE
", FDayPlanWorkID);
LogHelper.Info("鎻愭枡鏁版嵁" + sql);
@@ -1835,6 +1840,9 @@
decimal FMINPOQTY = _DayPlanPpbom.FirstOrDefault().FMINPOQTY;//鏈�灏忚捣璁㈤噺
double FFIXLEADTIME = _DayPlanPpbom.FirstOrDefault().FFIXLEADTIME;//鎻愬墠鏈�
+ string FJITmaterialGroup = _DayPlanPpbom.FirstOrDefault().FJITmaterialGroup;//jit鐗╂枡鍒嗙被
+ string FJITMaterielDemand = _DayPlanPpbom.FirstOrDefault().FJITMaterielDemand; //JIT鐗╂枡闇�姹備緵璐у懆鏈�
+ string FJITSafeStock = _DayPlanPpbom.FirstOrDefault().FJITSafeStock; //JIT瀹夊叏搴撳瓨
decimal NeedQty = 0;
DateTime DATE = _DayPlanPpbom.FirstOrDefault().FHMASTERDATE;
int i = 1;
@@ -1842,13 +1850,12 @@
List<int> FEntryIdList = new List<int>();
foreach (var _item in _DayPlanPpbom)
{
-
if (NeedQty == 0)
DATE = Convert.ToDateTime(_item.FHMASTERDATE).AddDays(-FFIXLEADTIME);
-
FEntryIdList.Add(_item.FENTRYID);
-
NeedQty += _item.NeedQty;
+ if ((FJITmaterialGroup == "鎬婚噺鎺у埗瑙勬牸绫�" || FJITmaterialGroup == "璁㈠崟涓撶敤涓�х被") && FEntryIdList.Count < Convert.ToInt32(FJITMaterielDemand)) //璁㈠崟涓撶敤涓�х被
+ continue;
if (NeedQty >= FMINPOQTY)
{
List<PurchaseInventory> _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).ToList();
@@ -1857,12 +1864,10 @@
NeedQty = 0;
break;
}
-
foreach (var Purchase in _PurchaseInventory)
{
LogHelper.Info("Purchase");
LogHelper.Info("鐗╂枡锛�" + Purchase.FMATERIALID + ",鏁伴噺锛�" + Purchase.FQTY);
-
if (Purchase.FQTY >= NeedQty)
{
List<int> mmm = new List<int>();
@@ -1890,7 +1895,6 @@
PurchseFqty = Purchase.FQTY,
FStockOrgId = Purchase.FStockOrgId, //閲囪喘缁勭粐
FORGNumber = Purchase.FORGNumber
-
});
//鎵ｉ櫎鏃ヨ鍒掓槑缁嗗凡琚垎閰嶇殑鏁伴噺
DayPlanPpbomls.NeedQty = 0;
@@ -1910,7 +1914,6 @@
List<int> mmm = new List<int>();
foreach (int id in FEntryIdList)
{
-
var DayPlanPpbomls = DayPlanPpbom.Where(c => c.FENTRYID == id && c.FMATERIALID == _item.FMATERIALID).FirstOrDefault();
decimal Qty = DayPlanPpbomls.NeedQty;
if (Qty > _NeedQty)
@@ -1928,7 +1931,6 @@
OLDDATE = DayPlanPpbomls.FHMASTERDATE,
FFIXLEADTIME = FFIXLEADTIME,
-
PurchseFID = Purchase.FID,
PurchseFentryID = Purchase.FENTRYID,
FSUPPLIERID = Purchase.FSUPPLIERID,
@@ -1937,18 +1939,13 @@
PurchseFqty = Purchase.FQTY,
FStockOrgId = Purchase.FStockOrgId, //閲囪喘缁勭粐
FORGNumber = Purchase.FORGNumber
-
-
});
DayPlanPpbomls.NeedQty -= Qty;
-
//鏇存柊閲囪喘璁㈠崟
sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount = isnull(FPODemandPlanCount,0)+ '{Qty}' where FENTRYID = {Purchase.FENTRYID}");
-
//鏇存柊Model
var ls = PurchaseInventory.Where(c => c.FENTRYID == Purchase.FENTRYID).FirstOrDefault();
ls.FQTY -= Qty;
-
if (_NeedQty < 0)
{
foreach (var mm in mmm)
@@ -1957,14 +1954,12 @@
}
break;
}
-
mmm.Add(id);
}
foreach (var mm in mmm)
{
FEntryIdList.Remove(mm);
}
-
}
}
NeedQty = 0;
diff --git a/src/BLL/Demo.Report/PRD/prd_ExtractionPlanDetail.cs b/src/BLL/Demo.Report/PRD/prd_ExtractionPlanDetail.cs
index c0f1621..3310e1a 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,13 @@
//鍒涘缓涓存椂鎶ヨ〃
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 +191,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
diff --git a/src/Model/Demo.Model/Model/PODemandPlan/DayPlanPpbom.cs b/src/Model/Demo.Model/Model/PODemandPlan/DayPlanPpbom.cs
index 0f2bfaa..f5203fb 100644
--- a/src/Model/Demo.Model/Model/PODemandPlan/DayPlanPpbom.cs
+++ b/src/Model/Demo.Model/Model/PODemandPlan/DayPlanPpbom.cs
@@ -71,5 +71,19 @@
/// 搴撳瓨缁勭粐
/// </summary>
public double FStockOrgId { get; set; }
+ /// <summary>
+ /// jit鐗╂枡鍒嗙被
+ /// </summary>
+ public string FJITmaterialGroup { get; set; }
+ /// <summary>
+ /// JIT鐗╂枡闇�姹備緵璐у懆鏈�
+ /// </summary>
+ public string FJITMaterielDemand { get; set; }
+ /// <summary>
+ /// JIT瀹夊叏搴撳瓨
+ /// </summary>
+ public string FJITSafeStock { get; set; }
+
+
}
}
--
Gitblit v1.9.1