王 垚
2021-02-28 3b819605e47e354eeafc696ea3bcb837b8eb063f
src/BLL/Demo.BillView/PRD/Pro_WorkBillPlatform.cs
@@ -164,13 +164,20 @@
        /// </summary>
        public void ExportExcel()
        {
            string[] Title = new string[] {"销售订单号", "生产订单号", "生产组织", "生产车间", "生产资源", "产品代码", "产品名称", "规格型号", "计量单位",
                "批次号","销售订单数量","生产订单数量","总齐套数量","交货日期","订单等级","T0","T1","T2","T3","T4","T5","T6","T7",
            List<string> Title = new List<string> {"销售订单号", "生产订单号*","生产组织*", "生产车间*", "生产资源", "产品代码*", "产品名称", "规格型号", "计量单位",
                "批次号","销售订单数量","生产订单数量","总齐套数量","交货日期*","订单等级*"};
            /*
             ,"T0","T1","T2","T3","T4","T5","T6","T7",
                "T8","T9","T10","T11","T12","T13","T14","T15","T16","T17",
                "T18","T19","T20","T21","T22","T23","T24","T25","T26","T27","T28","T29",
                "T30","T31","T32","T33","T34","T35","T36","T37","T38","T39","T40","T41","T42",
                "T43","T44","T45","T46","T47","T48","T49","T50","T51","T52","T53","T54","T55","T56","T57","T58","T59","T60"};
                "T43","T44","T45","T46","T47","T48","T49","T50","T51","T52","T53","T54","T55","T56","T57","T58","T59","T60"
             */
            DateTime date = DateTime.Now;
            for (int i = 0; i < 61; i++)
            {
                Title.Add(date.AddDays(i).ToString("yyyy-MM-dd"));
            }
            string sheetname = "日计划导入模板.xlsx";
            string file = "";
            file = @"D:\办公\K3Cloud\WebSite\TempfilePath\" + sheetname;
@@ -219,7 +226,7 @@
            //设置列名
            HSSFRow row = (HSSFRow)sheet.CreateRow(0);
            for (int i = 0; i < Title.Length; i++)
            for (int i = 0; i < Title.Count; i++)
            {
                ICell cell = (ICell)row.CreateCell(i);
                var rowName = Title[i];
@@ -229,7 +236,7 @@
            //单元格赋值
            //列宽自适应,只对英文和数字有效
            for (int i = 0; i <= Title.Length; i++)
            for (int i = 0; i <= Title.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
@@ -947,7 +954,7 @@
LEFT JOIN JIT_MOMaterReadysBill B ON T1.FStockOrgId = b.FHSTOCKORGID and T1.FMATERIALID =b.FHMATERID and a.FOwnerId = b.FOwnerId 
LEFT join T_ORG_Organizations c on a.FOWNERID = c.FORGID
--join t_BD_Stock d on a.FSTOCKID =d.FSTOCKID
--where d.FAvailableComplete<>0
where d.FAvailableComplete<>0
--where T1.FMATERIALID in (105773)
");
                ListInventory = ds.ToModelList<PRD_Inventory>();
@@ -1509,11 +1516,14 @@
            {
                string sql = string.Format(@"
/*dialect*/
select t1.FID,t1.FEntryID,t3.FMATERIALID,T5.FNUMBER,t1.FHMASTERDATE,t1.FHQTY,t4.FMATERIALID FMATERIALID2,T6.FNUMBER FNUMBER2,T7.FNUMBER FUNITNUMBER,FNumerator,FDenominator,convert(decimal(18,2),(FNumerator/FDenominator) *t1.FHQTY) FHQtyMust,T4.FUnitID,t8.FNUMBER FUNITNUMBER2,T3.FID FPPBOM,T4.FENTRYID,isnull(T9.FBillNo,'')FBillNo,t10.FNUMBER FORGNUMBER,t11.FNUMBER FHSOURCENUMBER,FSCRAPRATE,FFIXSCRAPQTY,T9.FID AS DayPlanFID
 select t1.FID,t1.FEntryID,t3.FMATERIALID,T5.FNUMBER,t1.FHMASTERDATE,t1.FHQTY,t4.FMATERIALID FMATERIALID2,T6.FNUMBER FNUMBER2,T7.FNUMBER FUNITNUMBER,FNumerator,FDenominator,convert(decimal(18,2),(FNumerator/FDenominator) *t1.FHQTY) FHQtyMust,T4.FUnitID,t8.FNUMBER FUNITNUMBER2,T3.FID FPPBOM,T4.FENTRYID FPPBOMENTRYID,isnull(T9.FBillNo,'')FBillNo,t10.FNUMBER FORGNUMBER,t11.FNUMBER FHSOURCENUMBER,FSCRAPRATE,FFIXSCRAPQTY,T9.FID AS DayPlanFID,t2.FSCORDERNO,
t3.FMOENTRYID,t3.FBILLNO FPPBOMBILLNO,T4.FSEQ,tc.FOWNERTYPEID,tc.FOWNERID,tc.FEntrustPickOrgId,torg.FNUMBER as FORGNUMBER,t3.FMOID,FIssueType
FROM Sc_WorkBillSortBillSub t1
JOIN Sc_WorkBillSortBillMAIN t2 on t1.FID = t2.FID
JOIN T_PRD_PPBOM T3 on T2.FPRDMOMAINID=t3.FMOID AND T2.FPRDMOENTYID=T3.FMOENTRYID
JOIN T_PRD_PPBOMENTRY T4 ON T3.FID=T4.FID
JOIN T_PRD_PPBOMENTRY_C Tc ON T4.FENTRYID=Tc.FENTRYID
join T_ORG_Organizations torg ON tc.FOWNERID = torg.FORGID
JOIN T_BD_MATERIAL T5 ON T3.FMATERIALID=T5.FMATERIALID
JOIN T_BD_MATERIAL T6 ON T4.FMATERIALID=T6.FMATERIALID
LEFT JOIN T_BD_UNIT T7 ON T3.FUNITID = T7.FUNITID
@@ -1548,12 +1558,21 @@
                            FHQtyMust = dr["FHQtyMust"].ToString(),
                            FHQtySub = dr["FHQtyMust"].ToString(),
                            FHPPBomInterID = dr["FPPBOM"].ToString(),
                            FHPPBomEntryID = dr["FENTRYID"].ToString(),
                            FHPPBomEntryID = dr["FPPBOMENTRYID"].ToString(),
                            FHSourceID = dr["FHSOURCENUMBER"].ToString(),
                            FNumerator = dr["FNumerator"].ToString(),
                            FDenominator = dr["FDenominator"].ToString(),
                            FFIXSCRAPQTY = dr["FFIXSCRAPQTY"].ToString(),
                            FHQtyScrap = dr["FSCRAPRATE"].ToString(),
                            FMOBILLNO = dr["FSCORDERNO"].ToString(),
                            FMOENTRYID = dr["FMOENTRYID"].ToString(),
                            FPPBOMBILLNO = dr["FPPBOMBILLNO"].ToString(),
                            FPPBOMFSEQ = dr["FSEQ"].ToString(),
                            FOwnerTypeId = dr["FOWNERTYPEID"].ToString(),
                            FOwnerID = dr["FORGNUMBER"].ToString(),
                            FEntrustPickOrgId = dr["FORGNUMBER"].ToString(),
                            FMOID = dr["FMOID"].ToString(),
                            FIssueType = dr["FIssueType"].ToString()
                        });
                    }
                    LogHelper.Info($"日计划用料清单,数据准备完成,保存到Model实体,总行数:{DayPlanMaterial.Count},耗时:" + sw.Elapsed);
@@ -1564,6 +1583,9 @@
                    {
                        var _DayPlanMaterial = DayPlanMaterial.Where(x => x.FEntryID == item).ToList();
                        JObject model = new JObject();
                        if (!string.IsNullOrEmpty(_DayPlanMaterial[0].FID))
                            model.Add("FID", _DayPlanMaterial[0].FID);
                        model.Add("FHDate", DateTime.Now);
                        model.Add("FOrgId", new JObject() { ["Fnumber"] = _DayPlanMaterial[0].FOrgId });//发料组织
                        model.Add("FHMaterID", new JObject() { ["Fnumber"] = _DayPlanMaterial[0].FHMaterID });//物料
@@ -1587,11 +1609,20 @@
                            ModelEnty.Add("FDwyl", Convert.ToDecimal(_item.FNumerator) / Convert.ToDecimal(_item.FDenominator));//单位用料
                            ModelEnty.Add("FFIXSCRAPQTY", _item.FFIXSCRAPQTY);//固定损耗
                            ModelEnty.Add("FHQtyScrap", _item.FHQtyScrap);//变动损耗率%
                            ModelEnty.Add("FMOBILLNO", _item.FMOBILLNO);//
                            ModelEnty.Add("FMOENTRYIDINT", _item.FMOENTRYID);//
                            ModelEnty.Add("FPPBOMBILLNO", _item.FPPBOMBILLNO);//
                            ModelEnty.Add("FPPBOMFSEQINT", _item.FPPBOMFSEQ);//
                            ModelEnty.Add("FOwnerTypeId", _item.FOwnerTypeId);//
                            ModelEnty.Add("FOwnerID", new JObject() { ["Fnumber"] = _item.FOwnerID });//
                            ModelEnty.Add("FEntrustPickOrgId", new JObject() { ["Fnumber"] = _item.FEntrustPickOrgId });//
                            ModelEnty.Add("FMOID", _item.FMOID);//
                            ModelEnty.Add("FISSUETYPE", _item.FIssueType);//
                            Entry.Add(ModelEnty);
                        }
                        model.Add("FEntity", Entry);
                        //if (!string.IsNullOrEmpty(_DayPlanMaterial[0].FID))
                        //    model.Add("FID", _DayPlanMaterial[0].FID);
                        Fentity.Add(model);
                        if ((i >= 20 || i == fidList.Count) && (i % 20 == 0 || i == fidList.Count))
                        {
@@ -1685,7 +1716,7 @@
", FDayPlanWorkID);
                sql = string.Format(@"
 /*dialect*/
SELECT T1.FID,FHMASTERDATE,t1.FEntryID,T1.FBILLNO, FNeedQty,(FLeadtime+FLeadTtime2)FFIXLEADTIME,t5.FMAXPOQTY,FJITBatch FMINPOQTY,FJITMATERIALGROUP,FJITMaterielDemand,FJITSafeStock,t1.FMATERIALID,t6.FNumber,NeedQty, FStockOrgId
SELECT T1.FID,FHMASTERDATE,t1.FEntryID,T1.FBILLNO, FNeedQty,(FLeadtime+FLeadTtime2)FFIXLEADTIME,t5.FMAXPOQTY,FJITBatch FMINPOQTY,FJITMATERIALGROUP,FJITMaterielDemand,FJITSafeStock,t1.FMATERIALID,t6.FNumber,NeedQty, FStockOrgId ,FErpClsID
FROM   (
select FHICMOINTERID FID,FHICMOENTRYID FEntryID,FPlanDate FHMASTERDATE,FLackCount NeedQty,FHMATERID FMATERIALID,FHStockOrgID FStockOrgId,FPRDBILLNO FBillNo,FSumPlanCount FNeedQty from JIT_MOMaterReadysBill a
right join (select max(FID)FID from JIT_MOMaterReadysBill GROUP BY FHICMOENTRYID,FHMATERID) b on a.FID = b.FID
@@ -1694,7 +1725,7 @@
join t_BD_MaterialBase t4 on T1.FMATERIALID = t4.FMATERIALID
join t_BD_MaterialPlan t5 on T1.FMATERIALID = t5.FMATERIALID
WHERE  DATEDIFF(D,GETDATE(),FHMASTERDATE) >=0
AND FERPCLSID=1
AND FERPCLSID in ('1','3')
AND T1.NeedQty>0
AND T1.FENTRYID not in (select distinct FHSOURCEENTRYID from CG_PODEMANDPLANBILLSUB where FHSOURCEINTERID IN ({0}))
AND T1.FID in ({0}) 
@@ -1781,6 +1812,7 @@
                                        FNumber = DayPlanPpbomls.FNumber,
                                        OLDDATE = DayPlanPpbomls.FHMASTERDATE,
                                        FFIXLEADTIME = FFIXLEADTIME,
                                        FErpClsID = DayPlanPpbomls.FErpClsID,
                                        PurchseFID = Purchase.FID,
                                        PurchseFentryID = Purchase.FENTRYID,
@@ -1833,6 +1865,7 @@
                                        FNumber = DayPlanPpbomls.FNumber,
                                        OLDDATE = DayPlanPpbomls.FHMASTERDATE,
                                        FFIXLEADTIME = FFIXLEADTIME,
                                        FErpClsID = DayPlanPpbomls.FErpClsID,
                                        PurchseFID = Purchase.FID,
                                        PurchseFentryID = Purchase.FENTRYID,
@@ -2007,6 +2040,7 @@
                        FentityModel.Add("FHPOOrderInterID", _item.PurchseFID);// 采购订单内码:FHPOOrderInterID
                        FentityModel.Add("FHPOOrderEntryID", _item.PurchseFentryID); //采购订单子内码:FHPOOrderEntryID
                        FentityModel.Add("FHPOOrderBillNo", _item.PurchseFBillNo); //采购订单号:FHPOOrderBillNo
                        FentityModel.Add("FERPCLSID", _item.FErpClsID); //发料方式
                        Fentity.Add(FentityModel);
                    }
                    model.Add("FEntity", Fentity);