代码上传

王 垚

2020-12-28 3fea7b751b0fe095dd05ce772f364e55bf1f7fdb

代码上传

 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;

 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

 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; }
        

    }
}