| | |
| | | /// </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; |
| | |
| | | |
| | | //设置列名 |
| | | 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]; |
| | |
| | | |
| | | //单元格赋值 |
| | | //列宽自适应,只对英文和数字有效 |
| | | for (int i = 0; i <= Title.Length; i++) |
| | | for (int i = 0; i <= Title.Count; i++) |
| | | { |
| | | sheet.AutoSizeColumn(i); |
| | | } |
| | |
| | | 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>(); |
| | |
| | | { |
| | | 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 |
| | |
| | | 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); |
| | |
| | | { |
| | | 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 });//物料 |
| | |
| | | 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)) |
| | | { |
| | |
| | | ", 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 |
| | |
| | | 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}) |
| | |
| | | FNumber = DayPlanPpbomls.FNumber, |
| | | OLDDATE = DayPlanPpbomls.FHMASTERDATE, |
| | | FFIXLEADTIME = FFIXLEADTIME, |
| | | FErpClsID = DayPlanPpbomls.FErpClsID, |
| | | |
| | | PurchseFID = Purchase.FID, |
| | | PurchseFentryID = Purchase.FENTRYID, |
| | |
| | | FNumber = DayPlanPpbomls.FNumber, |
| | | OLDDATE = DayPlanPpbomls.FHMASTERDATE, |
| | | FFIXLEADTIME = FFIXLEADTIME, |
| | | FErpClsID = DayPlanPpbomls.FErpClsID, |
| | | |
| | | PurchseFID = Purchase.FID, |
| | | PurchseFentryID = Purchase.FENTRYID, |
| | |
| | | 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); |