派生自 MES-WEB-API
WebAPI/Controllers/SCGL/Èռƻ®¹ÜÀí/JIT_DayPlanPlatFormBillController.cs
@@ -213,8 +213,7 @@ HBillNo = DBUtility.ClsPub.CreateBillCode_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo, true);//è·å¾ä¸ä¸ªæ°çåæ®å· HInterID = DBUtility.ClsPub.CreateBillID_Prod(BillType, ref DBUtility.ClsPub.sExeReturnInfo);//è·å¾ä¸ä¸ªæ°çid //主表 oCN.RunProc("insert into Sc_WorkBillSortBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType," + string sql = $"insert into Sc_WorkBillSortBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType," + "HBillSubType,HDate,HBillStatus,HMaker,HMakeDate,HICMOBillNo,HOrderType," + "HICMOEntrySEQ,HWorkShopID,HSourceID, HYX, HProdORGID," + " HMaterID, HMaterName, HMaterModel, HUnitID, HSeOrderBillQty," + @@ -227,7 +226,11 @@ $" {(HMaterID == "" ? 0.ToString() : HMaterID)}, '{HMaterName}', '{HMaterModel}', {(HUnitID == "" ? 0.ToString() : HUnitID)}, {(HSeOrderBillQty == "" ? 0.ToString() : HSeOrderBillQty)}," + $" 0, {(HSplitQty == "" ? 0.ToString() : HSplitQty)}, {(HDayPlanSumQty == "" ? 0.ToString() : HDayPlanSumQty)},'{HPlanBeginDate}'," + $"'{HSeOrderBillNo}','{HICMOBillType}',{(HSourceStockInQty == "" ? 0.ToString() : HSourceStockInQty)},{(HLeftPlanQty == "" ? 0.ToString() : HLeftPlanQty)},'{HOrderLev}',getdate()," + $"{HMainSourceInterID},{HMainSourceEntryID},{HMainSourceInterID},{HMainSourceEntryID},{HPlanQty})"); $"{HMainSourceInterID},{HMainSourceEntryID},{HMainSourceInterID},{HMainSourceEntryID},{HPlanQty})"; //LogService.Write("sql:" + sql); //主表 oCN.RunProc(sql); } else { @@ -762,14 +765,14 @@ ( select distinct a.HProdORGID FStockOrgId,T2.FMATERIALID,FNumber from Sc_WorkBillSortBillMain a join Sc_WorkBillSortBillSub b on a.HInterID = b.HInterID join AIS20210811135644..T_PRD_PPBOM T1 on a.HICMOInterID_Sec = t1.FMoId and a.HICMOEntryID_Sec = t1.FMOENTRYID LEFT JOIN AIS20210811135644..T_PRD_PPBOMENTRY T2 on T1.FID = t2.FID join AIS20210811135644..T_BD_MATERIAL T3 on T2.FMATERIALID = T3.FMATERIALID join AIS20230129011339..T_PRD_PPBOM T1 on a.HICMOInterID_Sec = t1.FMoId and a.HICMOEntryID_Sec = t1.FMOENTRYID LEFT JOIN AIS20230129011339..T_PRD_PPBOMENTRY T2 on T1.FID = t2.FID join AIS20230129011339..T_BD_MATERIAL T3 on T2.FMATERIALID = T3.FMATERIALID ) T1 JOIN AIS20210811135644..T_BD_MATERIAL b on t1.FNUMBER = b.FNUMBER LEFT JOIN AIS20210811135644..T_STK_Inventory a on b.FMATERIALID = a.FMATERIALID LEFT join (select distinct fitemid,fformid,FNUMBER from AIS20210811135644..V_ITEMCLASS_OWNER) c on a.FOwnerTypeId = c.fformid and a.FOWNERID = c.fitemid join AIS20210811135644..t_BD_Stock d on a.FSTOCKID =d.FSTOCKID JOIN AIS20230129011339..T_BD_MATERIAL b on t1.FNUMBER = b.FNUMBER LEFT JOIN AIS20230129011339..T_STK_Inventory a on b.FMATERIALID = a.FMATERIALID LEFT join (select distinct fitemid,fformid,FNUMBER from AIS20230129011339..V_ITEMCLASS_OWNER) c on a.FOwnerTypeId = c.fformid and a.FOWNERID = c.fitemid join AIS20230129011339..t_BD_Stock d on a.FSTOCKID =d.FSTOCKID where a.FBASEQTY>0 ", "SC_WORKBILLSORTBILLMAIN"); ListInventory = ds.ToModelList<PRD_Inventory>(); @@ -795,10 +798,10 @@ where a.HInterID IN ({0}) group by HICMOEntryID_Sec ) a join AIS20210811135644..T_PRD_PPBOM T1 on a.FPRDMOENTYID = t1.FMOENTRYID LEFT JOIN AIS20210811135644..T_PRD_PPBOMENTRY t2 on T1.FID = T2.FID LEFT JOIN AIS20210811135644..T_PRD_PPBOMENTRY_Q t3 on t2.FENTRYID = T3.FENTRYID LEFT JOIN AIS20210811135644..T_BD_MATERIAL T4 ON T2.FMATERIALID = T4.FMATERIALID join AIS20230129011339..T_PRD_PPBOM T1 on a.FPRDMOENTYID = t1.FMOENTRYID LEFT JOIN AIS20230129011339..T_PRD_PPBOMENTRY t2 on T1.FID = T2.FID LEFT JOIN AIS20230129011339..T_PRD_PPBOMENTRY_Q t3 on t2.FENTRYID = T3.FENTRYID LEFT JOIN AIS20230129011339..T_BD_MATERIAL T4 ON T2.FMATERIALID = T4.FMATERIALID ) t1 group by FPRDMOENTYID,FMATERIALID ", fDayPlanFids); @@ -1156,6 +1159,827 @@ } #endregion #region ç产æ¥è®¡åå¹³å° ææè¿ç® #region æ¥è®¡åå±å¼æ°æ®æç» public class DayPlanPpbom { /// <summary> /// æ¥è®¡åå·¥å主ID /// </summary> public long FID { get; set; } /// <summary> /// ç产订åå· /// </summary> public string FBILLNO { get; set; } /// <summary> /// æ¥è®¡åæç»æ¥æ /// </summary> public DateTime FHMASTERDATE { get; set; } /// <summary> /// æ¥è®¡åæç»æ°é /// </summary> public decimal FHQTY { get; set; } /// <summary> /// é½å¥æ° /// </summary> public decimal FCOMPLETECOUNT { get; set; } /// <summary> /// ç产订åå·¥åID /// </summary> public long FPRDMOMAINID { get; set; } /// <summary> /// å表ID /// </summary> public int FENTRYID { get; set; } /// <summary> /// åä½ç¨æ /// </summary> public decimal FNeedQty { get; set; } /// <summary> /// ç©æID /// </summary> public string FMATERIALID { get; set; } /// <summary> /// æåå¤©æ° /// </summary> public int FFIXLEADTIME { get; set; } /// <summary> ///æ大å è£ /// </summary> public decimal FMAXPOQTY { get; set; } /// <summary> /// æå°å è£ é /// </summary> public decimal FMINPOQTY { get; set; } /// <summary> /// ç©æç¼ç /// </summary> public string FNumber { get; set; } /// <summary> /// å®é éæ± /// </summary> public decimal NeedQty { get; set; } /// <summary> /// åºåç»ç» /// </summary> public long 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; } /// <summary> /// ç©æå±æ§ /// </summary> public string FErpClsID { get; set; } /// <summary> /// éæ±åå·ï¼éå®è®¢åå·ï¼ /// </summary> public string FXQD { get; set; } /// <summary> /// æ¥è®¡åå·¥ååå· /// </summary> public string FDAYPLANBILLNO { get; set; } /// <summary> /// ç产车é´ç¼ç /// </summary> public string FWORKSHOPNUMBER { get; set; } } #endregion #region éè´è®¢åç©æåºå public class PurchaseInventory { /// <summary> /// éè´FID /// </summary> public long FID { get; set; } //éè´è®¢ååå· public string FBillNo { get; set; } /// <summary> /// éè´å表æ è¯ /// </summary> public long FENTRYID { get; set; } /// <summary> /// ä¾åºåid /// </summary> public long FSUPPLIERID { get; set; } /// <summary> /// ä¾åºåå称 /// </summary> public string FNUMBER { get; set; } /// <summary> /// ç©æID /// </summary> public string FMATERIALID { get; set; } /// <summary> /// ç©æç¼ç /// </summary> public string FMATERIALNUMBER { get; set; } /// <summary> /// éè´æ°é /// </summary> public decimal FQTY { get; set; } /// <summary> /// éè´ç»ç» /// </summary> public long FStockOrgId { get; set; } /// <summary> /// ç»ç»ç¼ç /// </summary> public string FORGNumber { get; set; } /// <summary> /// æåæ /// </summary> public int FFIXLEADTIME { get; set; } /// <summary> /// éæ±åå· /// </summary> public string FXQD { get; set; } /// <summary> /// éè´è®¢åä¸çå建人 /// </summary> public string FCREATORID { get; set; } /// <summary> /// éè´è®¢åä¸çå建人å称 /// </summary> public string FName { get; set; } /// <summary> /// éè´è®¢åä¸çéè´æ¥æ /// </summary> public DateTime FDate { get; set; } /// <summary> /// éè´è®¢åä¸ç交货æ¥æ /// </summary> public DateTime FDeliveryDate { get; set; } /// <summary> /// éè´è®¢å表头æè¦ /// </summary> public string F_QIMB_NOTE { get; set; } /// <summary> /// éè´è®¢å表ä½å¤æ³¨ /// </summary> public string FNOTE { get; set; } //éè´é¨é¨ public string FPURCHASEDEPTNUMBER { get; set; } /// <summary> /// æ¶æä½æ¯æ²¡å ¥åºé¨å éè¦ä¼å å»æ£é¤ /// </summary> public decimal FReceiveNotInQty { get; set; } /// <summary> /// éè´è®¢åæ°é /// </summary> public decimal FPURCHASEQTY { get; set; } /// <summary> /// éè´è®¢åå ³èæ°é /// </summary> public decimal FJOINQTY { get; set; } /// <summary> /// æ¶æåæ°é /// </summary> public decimal FReceiveQty { get; set; } } #endregion #region ææ计å临æ¶å¨å public class PODemandPlanTemp { /// <summary> /// æ¥è®¡åå·¥å主ID /// </summary> public long FID { get; set; } /// <summary> /// æ¥è®¡åå·¥åç¼å· /// </summary> public string FBILLNO { get; set; } /// <summary> /// æ¥è®¡åæç»æ¥æ /// </summary> public DateTime FHMASTERDATE { get; set; } /// <summary> /// æ¥è®¡åæç»æ°é /// </summary> public decimal FHQTY { get; set; } /// <summary> /// ç产订åå·¥åID /// </summary> public long FPRDMOMAINID { get; set; } /// <summary> /// å表ID /// </summary> public int FENTRYID { get; set; } /// <summary> /// æ°é /// </summary> public decimal FQty { get; set; } /// <summary> /// ç©æID /// </summary> public string FMATERIALID { get; set; } /// <summary> /// ç©æç¼ç /// </summary> public string FNumber { get; set; } /// <summary> /// éè´FID /// </summary> public long PurchseFID { get; set; } /// <summary> /// éè´è®¢ååå· /// </summary> public string PurchseFBillNo { get; set; } /// <summary> /// éè´å表æ è¯ /// </summary> public long PurchseFentryID { get; set; } /// <summary> /// ä¾åºåid /// </summary> public long FSUPPLIERID { get; set; } /// <summary> /// ä¾åºåå称 /// </summary> public string PurchseFNUMBER { get; set; } /// <summary> /// æ¥è®¡åæ¥æ /// </summary> public DateTime OLDDATE { get; set; } /// <summary> /// éè´è®¢åæ°é /// </summary> public decimal PurchseFqty { get; set; } /// <summary> /// æåæ /// </summary> public double FFIXLEADTIME { get; set; } /// <summary> /// éè´ç»ç» /// </summary> public long FStockOrgId { get; set; } /// <summary> /// ç»ç»ç¼ç /// </summary> public string FORGNumber { get; set; } /// <summary> /// ç©æå±æ§ /// </summary> public string FErpClsID { get; set; } /// <summary> /// éæ±åå· /// </summary> public string FXQD { get; set; } /// <summary> /// éè´è®¢åä¸çå建人 /// </summary> public string FCREATORID { get; set; } /// <summary> /// éè´è®¢åä¸çå建人å称 /// </summary> public string FName { get; set; } /// <summary> /// éè´è®¢åä¸çéè´æ¥æ /// </summary> public DateTime FDate { get; set; } /// <summary> /// éè´è®¢åä¸ç交货æ¥æ /// </summary> public DateTime FDeliveryDate { get; set; } /// <summary> /// éè´è®¢å表头æè¦ /// </summary> public string F_QIMB_NOTE { get; set; } /// <summary> /// éè´è®¢å表ä½å¤æ³¨ /// </summary> public string FNOTE { get; set; } /// <summary> /// éè´é¨é¨ /// </summary> public string FPURCHASEDEPTNUMBER { get; set; } /// <summary> /// æ¥è®¡åå·¥ååå· /// </summary> public string FDAYPLANBILLNO { get; set; } /// <summary> /// ç产车é´ç¼ç /// </summary> public string FWORKSHOPNUMBER { get; set; } /// <summary> /// éè´è®¢åæ°é /// </summary> public decimal FPURCHASEQTY { get; set; } /// <summary> /// éè´è®¢åå ³èæ°é /// </summary> public decimal FJOINQTY { get; set; } } #endregion [Route("JIT_DayPlanPlatFormBill/JIT_TLYS")] [HttpGet] public object JIT_TLYS(string sWhere, string user) { try { omdelMian = JsonConvert.DeserializeObject<Sc_WorkBillSortBillMain>(sWhere); DataSet DaSet = oCN.RunProcReturn($"select * from JIT_MOMaterReadysBill where HWorkShopID={omdelMian.HWorkShopID} and HProdORGID={omdelMian.HProdORGID}", "JIT_MOMaterReadysBill"); if (DaSet.Tables[0].Rows.Count == 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "没ææ°æ®,请å é½å¥åæ!"; objJsonResult.data = null; return objJsonResult; } string fProWorkShopId = omdelMian.HWorkShopID.ToString(); var fPrdOrgId = omdelMian.HProdORGID; LogService.Write($"ææ计åéç® è½¦é´ï¼{fProWorkShopId}ï¼ç»ç»ï¼{fPrdOrgId}"); Stopwatch sw = new Stopwatch(); sw.Start();//å¼å§è®¡æ¶ //ææ计åé¢å¤ç æ¸ çææ计ååæ°æ® æ´æ°éè´è®¢åææ计åæ°é 并ä¸æ¥è¯¢åºéè¦è¿è¡è¿ç®çæ°æ® oCN.BeginTran(); //å¼å§äºå¡ ds = oCN.RunProcReturn($"exec h_p_PROC_JIT_TLPLANYCL '{fProWorkShopId}','{fPrdOrgId}'", "h_p_PROC_JIT_TLPLANYCL"); List<DayPlanPpbom> DayPlanPpbom = ds.ToModelList<DayPlanPpbom>(); if (DayPlanPpbom.Count == 0) { oCN.RollBack();//åæ»äºå¡ objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "æ æ°æ®è¿ç®!"; objJsonResult.data = null; return objJsonResult; } //éè´è®¢åæ°æ® ï¼å©ä½æ¶ææ°éï¼ //t2.FXQD t2.FSTOCKINQTY,t2.FReceiveQty æ¬å°æ²¡æè¿äºå段 string sql = string.Format(@" /*dialect*/ select t4.FSTOCKINQTY,t4.FReceiveQty,t1.FID,t1.FBillNo,t2.FENTRYID,t1.FSUPPLIERID,t3.fnumber,t2.FMATERIALID,(t4.FREMAINRECEIVEQTY-t2.FPODemandPlanCount)FQTY ,FPurchaseOrgId FStockOrgId,t5.FNUMBER FORGNumber,FCloseStatus,FMRPCLOSESTATUS,FPURCHASEDEPTID,t7.FNUMBER as FPURCHASEDEPTNUMBER ,t1.FCREATORID,t8.FName ,t1.FDate,tD.FDeliveryDate,t2.FNOTE,(t4.FReceiveQty - t4.FSTOCKINQTY) as FReceiveNotInQty,'' FXQD,T2.FQTY AS FPURCHASEQTY,FJOINQTY, isnull((select top 1 HLeadTime + HLeadTime_Sec from Cg_PODemandPlanConfigBillMain a join Cg_PODemandPlanConfigBillSub b on a.HInterID =b.HInterID left join Xt_ORGANIZATIONS o on a.HOrgID=o.HItemID left join Gy_Supplier s on b.HSupplierID=s.HItemID where o.HNumber = t5.FNUMBER and s.HNumber = t3.FNUMBER), 0)FFIXLEADTIME from AIS20230129011339..t_PUR_POOrder t1 join AIS20230129011339..t_PUR_POOrderEntry t2 on t1.FID = t2.FID join AIS20230129011339..t_BD_Supplier t3 on t1.FSUPPLIERID = t3.FSUPPLIERID join AIS20230129011339..T_PUR_POORDERENTRY_R t4 on t2.FENTRYID = t4.FENTRYID join AIS20230129011339..T_PUR_POORDERENTRY_D tD on t2.FENTRYID = tD.FENTRYID join AIS20230129011339..T_ORG_Organizations t5 on t1.FPurchaseOrgId = t5.FORGID join AIS20230129011339..T_SEC_user t8 on t1.FCREATORID = t8.FuserId join AIS20230129011339..T_BD_DEPARTMENT T7 ON t1.FPURCHASEDEPTID = T7.FDEPTID where t4.FREMAINRECEIVEQTY-t2.FPODemandPlanCount>0 and FCloseStatus in('A') and FMRPCLOSESTATUS in('A') and t1.FBillTypeID in('83d822ca3e374b4ab01e5dd46a0062bd','6d01d059713d42a28bb976c90a121142') and FPurchaseOrgId = '{0}' ", fPrdOrgId); ds = oCN.RunProcReturn(sql, "PurchaseInventoryList"); if (ds.Tables[0].Rows.Count == 0) { oCN.RollBack();//åæ»äºå¡ objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "æ¥æ éè´è®¢åæ°æ®!"; objJsonResult.data = null; return objJsonResult; } List<PurchaseInventory> PurchaseInventory = ds.ToModelList<PurchaseInventory>(); //ç©æ+åºåç»ç»åç±» var MaterialIDList = DayPlanPpbom.GroupBy(p => new { p.FMATERIALID, p.FStockOrgId }).Select(x => new PODemandPlanTemp { FMATERIALID = x.Key.FMATERIALID, FStockOrgId = x.Key.FStockOrgId }).ToList(); //ææ计åæ°æ®é临æ¶åå¨éå List<PODemandPlanTemp> PODemandPlanTemp = new List<PODemandPlanTemp>(); //sqléå æ´æ°éè´è®¢åå ç¨çææ计åæ°é List<string> sqlList = new List<string>(); foreach (var item in MaterialIDList) { //this.View.Session["ProcessRateValue"] = Convert.ToInt32((Convert.ToDecimal(75) / MaterialIDList.Count) * i); //å½åç©æååºåç»ç»å¯¹åºçéè¿è¡ææ计åçæ°æ® List<DayPlanPpbom> _DayPlanPpbom = DayPlanPpbom.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId).ToList(); decimal NeedQty = 0; foreach (var _item in _DayPlanPpbom) { //æ¥è¯¢æ没æå¯ä»¥ç¨äºææçéè´è®¢åæ°æ® var _PurchaseInventory = PurchaseInventory.Where(x => x.FMATERIALID == item.FMATERIALID && x.FStockOrgId == item.FStockOrgId && x.FQTY > 0).OrderBy(x => x.FENTRYID).ToList(); if (_PurchaseInventory.Count == 0) { //æ éè´è®¢åç´æ¥ä¸ä¸ä¸ªç©æ sql = $"INSERT INTO JIT_TLPLANERROR(HROGID,HUSERID,HWORKSHOPID,HDAYPLANID,HDAYPLANENTRYID,HMATERIALID,HQTY,HTYPE) VALUES({fPrdOrgId},{user},{fProWorkShopId},{_item.FID},{_item.FENTRYID},{_item.FMATERIALID},{_item.NeedQty},0) "; sqlList.Add(sql); break; } //éæ±æ°é NeedQty = _item.NeedQty; //å¢å ä¸ä¸ªéå®è®¢åå·å¹é çé»è¾ ä¼å å¾ªç¯ var PurchaseInventoryBySalOrder = _PurchaseInventory.Where(x => x.FXQD == _item.FXQD).OrderBy(x => x.FENTRYID).ToList(); if (PurchaseInventoryBySalOrder.Count > 0) { foreach (var Purchase in PurchaseInventoryBySalOrder) { double _FFIXLEADTIME = Purchase.FFIXLEADTIME;//æåæ DateTime _DATE = Convert.ToDateTime(_item.FHMASTERDATE).AddDays(-_FFIXLEADTIME); if (Purchase.FQTY >= NeedQty) { PODemandPlanTemp.Add(new PODemandPlanTemp { FID = _item.FID, FHMASTERDATE = _DATE, FQty = NeedQty, FBILLNO = _item.FBILLNO, FENTRYID = _item.FENTRYID, FMATERIALID = _item.FMATERIALID, FNumber = _item.FNumber, OLDDATE = _item.FHMASTERDATE, FFIXLEADTIME = _FFIXLEADTIME, FErpClsID = _item.FErpClsID, FWORKSHOPNUMBER = _item.FWORKSHOPNUMBER, FDAYPLANBILLNO = _item.FDAYPLANBILLNO, PurchseFentryID = Purchase.FENTRYID, FSUPPLIERID = Purchase.FSUPPLIERID, PurchseFNUMBER = Purchase.FNUMBER, PurchseFBillNo = Purchase.FBillNo, PurchseFqty = Purchase.FQTY, FStockOrgId = Purchase.FStockOrgId, //éè´ç»ç» FORGNumber = Purchase.FORGNumber, FPURCHASEDEPTNUMBER = Purchase.FPURCHASEDEPTNUMBER, FXQD = _item.FXQD, FCREATORID = Purchase.FCREATORID, FName = Purchase.FName, FDate = Purchase.FDate, FDeliveryDate = Purchase.FDeliveryDate, F_QIMB_NOTE = Purchase.F_QIMB_NOTE, FNOTE = Purchase.FNOTE, FPURCHASEQTY = Purchase.FPURCHASEQTY, FJOINQTY = Purchase.FJOINQTY }); //æ£é¤æ¥è®¡åæç»å·²è¢«åé çæ°é _item.NeedQty = 0; //æ£é¤å½åè¡å¯¹åºéè´è®¢åæ°é Purchase.FQTY -= NeedQty; //æ´æ°éè´è®¢å //sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount =isnull(FPODemandPlanCount,0)+ '{NeedQty}' where FENTRYID = {Purchase.FENTRYID}"); //éæ±æ°éå为0 NeedQty = 0; break; } else//éè´è®¢åæ°æ®ä¸è½æ»¡è¶³è®¢åæéæ°é { //éè´è®¢åæ°é decimal _NeedQty = Purchase.FQTY; PODemandPlanTemp.Add(new PODemandPlanTemp { FID = _item.FID, FHMASTERDATE = _DATE, FQty = _NeedQty, FBILLNO = _item.FBILLNO, FENTRYID = _item.FENTRYID, FMATERIALID = _item.FMATERIALID, FNumber = _item.FNumber, OLDDATE = _item.FHMASTERDATE, FFIXLEADTIME = _FFIXLEADTIME, FErpClsID = _item.FErpClsID, FWORKSHOPNUMBER = _item.FWORKSHOPNUMBER, FDAYPLANBILLNO = _item.FDAYPLANBILLNO, PurchseFentryID = Purchase.FENTRYID, FSUPPLIERID = Purchase.FSUPPLIERID, PurchseFNUMBER = Purchase.FNUMBER, PurchseFBillNo = Purchase.FBillNo, PurchseFqty = Purchase.FQTY, FStockOrgId = Purchase.FStockOrgId, //éè´ç»ç» FORGNumber = Purchase.FORGNumber, FPURCHASEDEPTNUMBER = Purchase.FPURCHASEDEPTNUMBER, FXQD = _item.FXQD, FCREATORID = Purchase.FCREATORID, FName = Purchase.FName, FDate = Purchase.FDate, FDeliveryDate = Purchase.FDeliveryDate, F_QIMB_NOTE = Purchase.F_QIMB_NOTE, FNOTE = Purchase.FNOTE, FPURCHASEQTY = Purchase.FPURCHASEQTY, FJOINQTY = Purchase.FJOINQTY }); //æ´æ°éè´è®¢å //sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount = isnull(FPODemandPlanCount,0)+ '{Purchase.FQTY}' where FENTRYID = {Purchase.FENTRYID}"); //æ´æ°è®¢åéæ±æ°é NeedQty = NeedQty - Purchase.FQTY; //æ£é¤æ¥è®¡åæç»å·²è¢«åé çæ°é _item.NeedQty -= _NeedQty; //æ£é¤å½åè¡å¯¹åºéè´è®¢åæ°é Purchase.FQTY = 0; } } } if (NeedQty == 0) continue; var PurchaseInventoryElse = _PurchaseInventory.Where(x => x.FXQD != _item.FXQD).OrderBy(x => x.FENTRYID).ToList(); foreach (var Purchase in PurchaseInventoryElse) { double _FFIXLEADTIME = Purchase.FFIXLEADTIME;//æåæ DateTime _DATE = Convert.ToDateTime(_item.FHMASTERDATE).AddDays(-_FFIXLEADTIME); if (Purchase.FQTY >= NeedQty) { PODemandPlanTemp.Add(new PODemandPlanTemp { FID = _item.FID, FHMASTERDATE = _DATE, FQty = _item.NeedQty, FBILLNO = _item.FBILLNO, FENTRYID = _item.FENTRYID, FMATERIALID = _item.FMATERIALID, FNumber = _item.FNumber, OLDDATE = _item.FHMASTERDATE, FFIXLEADTIME = _FFIXLEADTIME, FErpClsID = _item.FErpClsID, FWORKSHOPNUMBER = _item.FWORKSHOPNUMBER, FDAYPLANBILLNO = _item.FDAYPLANBILLNO, PurchseFentryID = Purchase.FENTRYID, FSUPPLIERID = Purchase.FSUPPLIERID, PurchseFNUMBER = Purchase.FNUMBER, PurchseFBillNo = Purchase.FBillNo, PurchseFqty = Purchase.FQTY, FStockOrgId = Purchase.FStockOrgId, //éè´ç»ç» FORGNumber = Purchase.FORGNumber, FPURCHASEDEPTNUMBER = Purchase.FPURCHASEDEPTNUMBER, FXQD = _item.FXQD, FCREATORID = Purchase.FCREATORID, FName = Purchase.FName, FDate = Purchase.FDate, FDeliveryDate = Purchase.FDeliveryDate, F_QIMB_NOTE = Purchase.F_QIMB_NOTE, FNOTE = Purchase.FNOTE, FPURCHASEQTY = Purchase.FPURCHASEQTY, FJOINQTY = Purchase.FJOINQTY }); //æ´æ°éè´è®¢å //sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount =isnull(FPODemandPlanCount,0)+ '{NeedQty}' where FENTRYID = {Purchase.FENTRYID}"); //æ£é¤æ¥è®¡åæç»å·²è¢«åé çæ°é _item.NeedQty = 0; //æ£é¤å½åè¡å¯¹åºéè´è®¢åæ°é Purchase.FQTY -= NeedQty; //éæ±æ°éå为0 NeedQty = 0; break; } else//éè´è®¢åæ°æ®ä¸è½æ»¡è¶³è®¢åæéæ°é { //æ´æ°æ»éæ±æ°é NeedQty = NeedQty - Purchase.FQTY; //éè´è®¢åæ°é decimal _NeedQty = Purchase.FQTY; PODemandPlanTemp.Add(new PODemandPlanTemp { FID = _item.FID, FHMASTERDATE = _DATE, FQty = _NeedQty, FBILLNO = _item.FBILLNO, FENTRYID = _item.FENTRYID, FMATERIALID = _item.FMATERIALID, FNumber = _item.FNumber, OLDDATE = _item.FHMASTERDATE, FFIXLEADTIME = _FFIXLEADTIME, FErpClsID = _item.FErpClsID, FWORKSHOPNUMBER = _item.FWORKSHOPNUMBER, FDAYPLANBILLNO = _item.FDAYPLANBILLNO, PurchseFentryID = Purchase.FENTRYID, FSUPPLIERID = Purchase.FSUPPLIERID, PurchseFNUMBER = Purchase.FNUMBER, PurchseFBillNo = Purchase.FBillNo, PurchseFqty = Purchase.FQTY, FStockOrgId = Purchase.FStockOrgId, //éè´ç»ç» FORGNumber = Purchase.FORGNumber, FPURCHASEDEPTNUMBER = Purchase.FPURCHASEDEPTNUMBER, FXQD = _item.FXQD, FCREATORID = Purchase.FCREATORID, FName = Purchase.FName, FDate = Purchase.FDate, FDeliveryDate = Purchase.FDeliveryDate, F_QIMB_NOTE = Purchase.F_QIMB_NOTE, FNOTE = Purchase.FNOTE, FPURCHASEQTY = Purchase.FPURCHASEQTY, FJOINQTY = Purchase.FJOINQTY }); //æ´æ°éè´è®¢å //sqlList.Add($"/*dialect*/ update t_PUR_POOrderEntry set FPODemandPlanCount = isnull(FPODemandPlanCount,0)+ '{Purchase.FQTY}' where FENTRYID = {Purchase.FENTRYID}"); //æ£é¤æ¥è®¡åæç»å·²è¢«åé çæ°é _item.NeedQty -= _NeedQty; //æ£é¤å½åè¡å¯¹åºéè´è®¢åæ°é Purchase.FQTY = 0; } } if (NeedQty > 0) { sql = $"INSERT INTO JIT_TLPLANERROR(HROGID,HUSERID,HWORKSHOPID,HDAYPLANID,HDAYPLANENTRYID,HMATERIALID,HQTY,HTYPE,HERRORTYPE) VALUES({fPrdOrgId},{user},{fProWorkShopId},{_item.FID},{_item.FENTRYID},{_item.FMATERIALID},{NeedQty},0,1) "; sqlList.Add(sql); } } } //ä¾åºåï¼æææ¥æï¼ç产车é´ï¼éè´å åç» var PODemandPlanList = PODemandPlanTemp.GroupBy(p => new { p.PurchseFNUMBER, p.FHMASTERDATE, p.FWORKSHOPNUMBER, p.FCREATORID }).Select(x => new PODemandPlanTemp { PurchseFNUMBER = x.Key.PurchseFNUMBER, FHMASTERDATE = x.Key.FHMASTERDATE, FWORKSHOPNUMBER = x.Key.FWORKSHOPNUMBER, FCREATORID = x.Key.FCREATORID }).ToList(); LogService.Write("ææ计åéç® ï¼æ°æ®åå¤å®æï¼ä¿åå°Modelå®ä½ ç¨æ¶ï¼" + sw.Elapsed + "æ»è¡æ°ï¼" + PODemandPlanTemp.Count); int tlmmm = 0; JArray FinalyResult = new JArray(); foreach (var item in PODemandPlanList) { tlmmm++; DateTime date = item.FHMASTERDATE; long HSettleCurrId = 1;//è´§å¸ é»è®¤æ¬å°äººæ°å¸ var dataSup= oCN.RunProcReturn(@"select * from Gy_Supplier p inner join AIS20230129011339..t_BD_Supplier fp on p.HNumber=fp.FNUMBER and p.HUSEORGID=fp.FUSEORGID where FNUMBER='" + item.PurchseFNUMBER + "' and HUSEORGID ='"+ fPrdOrgId + "'", "Gy_Supplier"); long HSupplierID = dataSup.Tables[0].Rows.Count == 0 ? 0 : long.Parse(dataSup.Tables[0].Rows[0]["HItemID"].ToString()); //ä¾åºå var dataUser = oCN.RunProcReturn("select Czymc from gy_czygl where Czybm = '" + user + "'", "gy_czygl"); string HMaker = dataUser.Tables[0].Rows.Count == 0 ? "" : dataUser.Tables[0].Rows[0][0].ToString();//å¶å人 string HUpDater = dataUser.Tables[0].Rows.Count == 0 ? "" : dataUser.Tables[0].Rows[0][0].ToString();//ä¿®æ¹äºº var dataOrg= oCN.RunProcReturn(@"select HItemID from Xt_ORGANIZATIONS o inner join AIS20230129011339..T_ORG_ORGANIZATIONS fo on o.HNumber=fo.FNUMBER where FNUMBER='" + PODemandPlanTemp[0].FORGNumber + "' and HItemID ='" + fPrdOrgId + "'", "Xt_ORGANIZATIONS"); long HPURCHASEORGID = dataOrg.Tables[0].Rows.Count == 0 ? 0 : long.Parse(dataOrg.Tables[0].Rows[0]["HItemID"].ToString()); //éè´ç»ç» string PurchseFNUMBER = item.PurchseFNUMBER; string FWorkshopNumber = item.FWORKSHOPNUMBER; string FCreatorID = item.FCREATORID; var HInterID= DBUtility.ClsPub.CreateBillID("4608", ref DBUtility.ClsPub.sExeReturnInfo); var HBillNo = DBUtility.ClsPub.CreateBillCode("4608", ref DBUtility.ClsPub.sExeReturnInfo, true); //主表 oCN.RunProc("insert into JIT_Cg_PODemandPlanBillMain(HInterID,HBillNo,HYear,HPeriod,HBillType" + ",HBillSubType,HDate,HBillStatus,HMaker,HMakeDate" + ",HSubORGID ,HAddress,HPURCHASEORGID,HSupplierID ,HSettleCurrId" + ",HRemark,HExplanation,HUpDater,HUpDateDate)" + $"values({HInterID}, '{HBillNo}', {DateTime.Now.Year}, {DateTime.Now.Month}, '4608'" + $", '4608','{date}',1, '{HMaker}',GETDATE()," + $"{HPURCHASEORGID},'',{HPURCHASEORGID}, {HSupplierID}, {HSettleCurrId}" + $",'','','{HUpDater}',getdate())"); JArray Fentity = new JArray(); List<PODemandPlanTemp> _PODemandPlanList = PODemandPlanTemp.Where(x => x.FHMASTERDATE == date && x.PurchseFNUMBER == PurchseFNUMBER && x.FWORKSHOPNUMBER == FWorkshopNumber && x.FCREATORID == FCreatorID).ToList(); int i = 0; foreach (var _item in _PODemandPlanList) { i++; var dataSet = oCN.RunProcReturn(@"select m.* from Gy_Material m inner join AIS20230129011339..T_BD_MATERIAL fm on m.HNumber = fm.FNUMBER and m.HUSEORGID=fm.FUSEORGID where FNUMBER='" + _item.FNumber + "' and HUSEORGID ='" + fPrdOrgId + "'", "Gy_Material"); string HMaterID = "0"; //ç©æ string HMaterModel = ""; //ç©æ代ç string HMaterName = ""; //ç©æå称 if (dataSet.Tables[0].Rows.Count != 0) { HMaterID = dataSet.Tables[0].Rows[0]["HItemID"].ToString(); //ç©æ HMaterModel = dataSet.Tables[0].Rows[0]["HNumber"].ToString(); //ç©æ代ç HMaterName = dataSet.Tables[0].Rows[0]["HName"].ToString(); //ç©æå称 } decimal HQty = _item.FQty; //æ°é long HSourceInterID = _item.FID; //æ¥è®¡åå·¥åFID long HSourceEntryID = _item.FENTRYID;//æ¥è®¡åå·¥åFENTRYID string HSourceBillNo = _item.FBILLNO; //ç产订åå· string HSourceBillType = ""; //æ¥è®¡åå·¥åç±»å DateTime HDayPlanDate = _item.OLDDATE; // å·¥åç产æ¥æ decimal HRelationQty = _item.PurchseFqty; // å ³èæ°é double HFixleadTime = _item.FFIXLEADTIME; // æåæ dataOrg = oCN.RunProcReturn(@"select HItemID from Xt_ORGANIZATIONS o inner join AIS20230129011339..T_ORG_ORGANIZATIONS fo on o.HNumber=fo.FNUMBER where FNUMBER='" + _item.FORGNumber + "' and HItemID ='" + fPrdOrgId + "'", "Xt_ORGANIZATIONS"); long HPURCHASEORGIDSub = dataOrg.Tables[0].Rows.Count == 0 ? 0 : long.Parse(dataOrg.Tables[0].Rows[0]["HItemID"].ToString()); // éè´ç»ç» long HPOOrderInterID = _item.PurchseFID; // éè´è®¢åå ç long HPOOrderEntryID = _item.PurchseFentryID; // éè´è®¢ååå ç string HPOOrderBillNo = _item.PurchseFBillNo; // éè´è®¢åå· string HErpClsID = _item.FErpClsID; // ç©æå±æ§ long HUnitID = 10101; // 计éåä½ å æç §æ¬å°æ¥ é»è®¤ Pcs string HSeOrderBillNo = _item.FXQD; // éå®è®¢åå· éæ±åå· string HOrderEmpID = _item.FCREATORID; // éè´å DateTime HPOOrderBillDate = _item.FDate; // éè´è®¢ååæ®æ¥æ DateTime HPOOrderBillDeliveryDate = _item.FDeliveryDate; // éè´è®¢å交货æ¥æ string HPOOrderBillRemark = _item.F_QIMB_NOTE; // éè´è®¢åæè¦ string HPOOrderBillNote = _item.FNOTE; // éè´è®¢å表ä½å¤æ³¨ string HDayPlanBillNo = _item.FDAYPLANBILLNO; // æ¥è®¡åå·¥åå· dataSet = oCN.RunProcReturn(@"select HItemID from Gy_Department d inner join AIS20230129011339..T_BD_DEPARTMENT fd on d.HNumber = fd.FNUMBER where FNUMBER='" + _item.FWORKSHOPNUMBER + "' and HUSEORGID ='" + fPrdOrgId + "'", "Gy_Department"); string HWorkShopID = dataSet.Tables[0].Rows.Count == 0 ? "0" : dataSet.Tables[0].Rows[0][0].ToString(); // çäº§è½¦é´ dataSet = oCN.RunProcReturn(@"select HItemID from Gy_Department d inner join AIS20230129011339..T_BD_DEPARTMENT fd on d.HNumber = fd.FNUMBER where FNUMBER='" + _item.FPURCHASEDEPTNUMBER + "' and HUSEORGID ='" + fPrdOrgId + "'", "Gy_Department"); string HPurchaseDeptID = dataSet.Tables[0].Rows.Count == 0 ? "0" : dataSet.Tables[0].Rows[0][0].ToString(); // éè´é¨é¨ decimal HPURCHASEQTY = _item.FPURCHASEQTY; // éè´è®¢åæ°é decimal HJoinQty = _item.FJOINQTY; // éè´è®¢åå ³èæ°é oCN.RunProc("insert into JIT_Cg_PODemandPlanBillSub(HInterID,HEntryID,HLaterReason,HMaterID,HMaterName" + ", HMaterModel, HUnitID, HErpClsID, HQty, HRemark" + ", HPURCHASEORGID, HStatus, HFixleadTime, HDayPlanBillNo, HSourceInterID" + ", HSourceEntryID, HSourceBillNo, HSourceBillType, HWorkShopID, HDayPlanDate" + ", HRelationQty1, HPOOrderInterID, HPOOrderEntryID, HPOOrderBillNo, HPOOrderBillSEQ" + ", HWWOrderInterID, HWWOrderEntryID, HWWOrderBillNo, HPurchaseDeptID, HCloseTypeSub" + ", HCloseMan, HEntryCloseDate, HSendQty, HReciveQty, HCloseStatus" + ", HRemoveQty, HSeOrderBillNo, HUnReciveQty, HPOOrderBillDate, HPOOrderBillDeliveryDate" + ", HPOOrderBillRemark, HPOOrderBillNote)" + $"values({HInterID}, {i}, '', {(HMaterID == null ? "0" : HMaterID)}, '{HMaterName}'" + $", '{HMaterModel}', {HUnitID}, '{HErpClsID}', {HQty}, ''" + $", {HPURCHASEORGIDSub}, '', {HFixleadTime}, '{HDayPlanBillNo}', {HSourceInterID}" + $", {HSourceEntryID}, '{HSourceBillNo}', '{HSourceBillType}', {HWorkShopID}, '{HDayPlanDate}'" + $", {HRelationQty}, {HPOOrderInterID},{HPOOrderEntryID}, '{HPOOrderBillNo}',0" + $",0,0, '',{HPurchaseDeptID}, '0'" + $", '','',0,0, '0'" + $",0, '{HSeOrderBillNo}',0, '{HPOOrderBillDate}', '{HPOOrderBillDeliveryDate}'" + $", '{HPOOrderBillRemark}', '{HPOOrderBillNote}')"); } } if (tlmmm == PODemandPlanList.Count) { oCN.Commit(); objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "è¿ç®æå!"; objJsonResult.data = null; return objJsonResult; } else { oCN.RollBack();//äºå¡åæ» objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "è¿ç®å¤±è´¥!"; objJsonResult.data = null; return objJsonResult; } } catch (Exception e) { LogService.Write("æä½å¤±è´¥ï¼" + e.Message.ToString()); oCN.RollBack();//äºå¡åæ» objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "Exceptionï¼" + e.Message.ToString(); objJsonResult.data = null; return objJsonResult; } } #endregion #region ç产æ¥è®¡åå¹³å° éå® [Route("JIT_DayPlanPlatFormBill/JIT_DayPlanPlatFormBillHLockedDay")] [HttpGet] @@ -1217,31 +2041,67 @@ #region ç产æ¥è®¡åå¹³å° ç产订åæ¥è¯¢ [Route("JIT_DayPlanPlatFormBill/JIT_ICMOBillList")] [HttpGet] public object JIT_ICMOBillList(string hmainid, string HEntryID) public object JIT_ICMOBillList(string hmainid) { try { DataSet dt = new DataSet(); oCN.RunProc("exec h_p_ICMOBillList_PrimarySubTable"); ds = oCN.RunProcReturn($"select * from ##ICMOBillList where æºå主å ç in({hmainid}) and æºååå ç in({HEntryID})", "##ICMOBillList"); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) var list = hmainid.Split(','); List<DataTable> listdt = new List<DataTable>(); DataTable dtable = new DataTable("dt"); for (int i = 0; i < list.Length; i++) { dt = oCN.RunProcReturn($"select * from h_v_JIT_Sc_WorkBillSortBillList where æºå主å ç in({ds.Tables[0].Rows[i]["æºå主å ç "].ToString()}) and æºååå ç in({ds.Tables[0].Rows[i]["æºååå ç "].ToString()})", "h_v_JIT_Sc_WorkBillSortBillList"); var listEvery = list[i].Split('_'); ds = oCN.RunProcReturn($"exec h_p_ICMOBillList_PrimarySubTable '{listEvery[0]}','{listEvery[1]}'", "h_p_ICMOBillList_PrimarySubTable"); var dt = oCN.RunProcReturn($"select * from h_v_JIT_Sc_WorkBillSortBillList where æºå主å ç in({ds.Tables[0].Rows[0]["æºå主å ç "].ToString()}) and æºååå ç in({ds.Tables[0].Rows[0]["æºååå ç "].ToString()})", "h_v_JIT_Sc_WorkBillSortBillList"); if (dt.Tables[0].Rows.Count > 0) { objJsonResult.code = "0"; objJsonResult.count = 0; objJsonResult.Message = "ç产æç»è¡éå¤,éå¤çç产订åå·:"+ ds.Tables[0].Rows[i]["ç产订åå·"].ToString()+",æç»è¡å·:"+ ds.Tables[0].Rows[i]["ç产订åæç»è¡å·"].ToString(); objJsonResult.Message = "ç产æç»è¡éå¤,éå¤çç产订åå·:" + ds.Tables[0].Rows[i]["ç产订åå·"].ToString() + ",æç»è¡å·:" + ds.Tables[0].Rows[i]["ç产订åæç»è¡å·"].ToString(); objJsonResult.data = null; return objJsonResult; } if (i == 0) { //æ·»å åå for (int j = 0; j < ds.Tables[0].Columns.Count; j++) { dtable.Columns.Add(ds.Tables[0].Columns[j].ToString()); } } listdt.Add(ds.Tables[0]); } //è·åéåæ°æ® for (int i = 0; i < listdt.Count; i++) { DataRow row = dtable.NewRow(); for (int j = 0; j < listdt[i].Columns.Count; j++) { if (listdt[i].Columns[j].ColumnName == "计åæ°é") { row[j] = double.Parse(listdt[i].Rows[0][j].ToString()).ToString(); } else { row[j] = listdt[i].Rows[0][j].ToString(); } } dtable.Rows.Add(row); } //æ·»å å¨ææ¥æ for (int j = 1; j <= 31; j++) { dtable.Columns.Add(DateTime.Now.AddDays(j - 1).ToString("yyyy-MM-dd"), typeof(Int32)); } objJsonResult.code = "1"; objJsonResult.count = 1; objJsonResult.Message = "Sucessï¼"; objJsonResult.data = ds.Tables[0]; objJsonResult.data = dtable; return objJsonResult; } catch (Exception e)
