From d8bbd1bc855990e908fc5df9594fc7b4e4628eed Mon Sep 17 00:00:00 2001
From: 啊鑫 <t2856754968@163.com>
Date: 星期三, 28 八月 2024 17:56:23 +0800
Subject: [PATCH] 生产领料单erp推送
---
MES.Service/service/Warehouse/MesInvItemOutsManager.cs | 325 +++++++++++++++++++++++++++++++++++++++++++++++++-----
1 files changed, 295 insertions(+), 30 deletions(-)
diff --git a/MES.Service/service/Warehouse/MesInvItemOutsManager.cs b/MES.Service/service/Warehouse/MesInvItemOutsManager.cs
index c576127..6b1d2ec 100644
--- a/MES.Service/service/Warehouse/MesInvItemOutsManager.cs
+++ b/MES.Service/service/Warehouse/MesInvItemOutsManager.cs
@@ -3,6 +3,7 @@
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
+using Newtonsoft.Json;
using SqlSugar;
using DbType = System.Data.DbType;
@@ -12,6 +13,114 @@
{
//褰撳墠绫诲凡缁忕户鎵夸簡 Repository 澧炪�佸垹銆佹煡銆佹敼鐨勬柟娉�
//杩欓噷闈㈠啓鐨勪唬鐮佷笉浼氱粰瑕嗙洊,濡傛灉瑕侀噸鏂扮敓鎴愯鍒犻櫎 MesInvItemOutsManager.cs
+
+ public List<MesInvItemOuts> GetProductionPickPage(WarehouseQuery query)
+ {
+ return Db.Queryable<MesInvItemOuts, MesDepots>((a, o) =>
+ new JoinQueryInfos(
+ JoinType.Left,
+ o.DepotCode == a.DepotCode && a.Company == o.Company &&
+ a.Factory == o.Factory
+ ))
+ .Where((a, o) => a.BillTypeId == 200 &&
+ a.TransactionNo == 201 &&
+ a.Factory == "1000" &&
+ a.Company == "1000" &&
+ a.IsVisual == 1)
+ .WhereIF(query.status != null, (a, o) => a.Status == query.status)
+ .WhereIF(query.id != null, (a, o) => a.Id == query.id)
+ .Select((a, o) => new MesInvItemOuts
+ {
+ Id = a.Id,
+ Sapstatus = a.Sapstatus,
+ OutDate = a.OutDate,
+ CheckDate = a.CheckDate,
+ BoardItem = a.BoardItem,
+ WorkNo = a.WorkNo,
+ OutPart = a.OutPart,
+ PbillNo = a.PbillNo,
+ OutType = a.OutType,
+ CreateBy = a.CreateBy,
+ Status = a.Status,
+ CheckUser = a.CheckUser,
+ ItemOutNo = a.ItemOutNo,
+ DepotName = o.DepotName // 闇�瑕佸姩鎬佹坊鍔犵殑瀛楁
+ })
+ .ToPageList(query.PageIndex, query.Limit);
+ }
+
+ public MaterialReceipt GetProductionPick(WarehouseQuery query)
+ {
+ var form = new MaterialReceipt();
+ if (query.id != null)
+ {
+ var mesInvItemOutsList = GetProductionPickPage(query);
+ if (mesInvItemOutsList.Count <= 0) return form;
+
+ form.ItemOuts = mesInvItemOutsList[0];
+ form.ItemsList = getProductionItemsList(query);
+ form.InvItemoutCDetails = GetProductionDetails(query);
+ }
+
+ return form;
+ }
+
+ private List<MesInvItemOutItems> getProductionItemsList(
+ WarehouseQuery query)
+ {
+ return Db.Queryable<MesInvItemOutItems, MesItems, MesDepTaskInfo>(
+ (c, s, f) => new JoinQueryInfos(
+ JoinType.Inner, c.ItemNo == s.ItemNo,
+ JoinType.Left, f.TaskNo == c.TaskNo && f.ItemNo == c.ItemNo
+ ))
+ .Where((c, s, f) => c.ItemOutId == query.id)
+ .OrderBy(c => c.Id)
+ .Select((c, s, f) => new MesInvItemOutItems
+ {
+ Id = c.Id,
+ ItemNo = c.ItemNo,
+ Quantity = c.Quantity,
+ TaskNo = c.TaskNo,
+ Remark = c.Remark,
+ ItemName = s.ItemName, // 鍔ㄦ�佸瓧娈�
+ ItemModel = s.ItemModel, // 鍔ㄦ�佸瓧娈�
+ ItemUnit = SqlFunc.Subqueryable<MesItems>()
+ .Where(si => si.ItemUnit == s.ItemUnit)
+ .Select(si =>
+ SqlFunc.MappingColumn<string>(
+ "F_GETUNITNAME(si.Item_Unit)")) // 鍔ㄦ�佸瓧娈�
+ })
+ .ToList();
+ }
+
+ private List<MesInvItemOutCDetails> GetProductionDetails(
+ WarehouseQuery query)
+ {
+ return Db
+ .Queryable<MesInvItemOutCDetails, MesItems, MesDepots>(
+ (b, c, d) => new JoinQueryInfos(
+ JoinType.Left,
+ b.ItemNo == c.ItemNo && b.Company == c.Company &&
+ b.Factory == c.Factory,
+ JoinType.Left,
+ d.DepotCode == b.DepotCode && b.Company == d.Company &&
+ b.Factory == d.Factory
+ ))
+ .Where((b, c, d) => b.ItemOutId == query.id)
+ .Select((b, c, d) => new MesInvItemOutCDetails
+ {
+ ForceOutFlag = b.ForceOutFlag,
+ DepotSectionCode = b.DepotSectionCode,
+ ItemNo = b.ItemNo,
+ Quantity = b.Quantity,
+ ItemBarcode = b.ItemBarcode,
+ ItemName = c.ItemName,
+ ItemModel = c.ItemModel,
+ DepotName = d.DepotName,
+ DepotCode = b.DepotCode
+ })
+ .ToList();
+ }
public bool ScanCode(WarehouseQuery query)
{
@@ -142,12 +251,12 @@
SELECT SUM(C.QUANTITY) AS SQ_QTY, SUM(D.QUANTITY_OK) AS OK_QTY
FROM MES_INV_ITEM_OUT_ITEMS C
LEFT JOIN (
- SELECT ITEM_OUT_ID, ITEM_NO,ITEM_ID, PBILL_NO, RK_NO, SUM(QUANTITY) AS QUANTITY_OK
+ SELECT ITEM_OUT_ID, ITEM_NO, PBILL_NO, RK_NO, SUM(QUANTITY) AS QUANTITY_OK
FROM MES_INV_ITEM_OUT_C_DETAILS
- GROUP BY ITEM_OUT_ID, ITEM_NO,ITEM_ID, PBILL_NO, RK_NO
+ GROUP BY ITEM_OUT_ID, ITEM_NO, PBILL_NO, RK_NO
) D
ON D.ITEM_OUT_ID = C.ITEM_OUT_ID
- AND D.ITEM_ID = C.ITEM_ID
+ AND D.ITEM_NO = C.ITEM_NO
AND D.RK_NO = C.RK_NO
AND NVL(C.PBILL_NO, '0') = NVL(D.PBILL_NO, '0')
WHERE C.ITEM_OUT_ID = {0}", mesInvItemOuts.Id);
@@ -227,24 +336,22 @@
FROM MES_INV_ITEM_OUT_ITEMS C
LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY,
ITEM_NO,
- ITEM_ID,
PBILL_NO,
RK_NO,
WORK_LINE
FROM MES_INV_ITEM_OUT_ITEMS
- GROUP BY ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE,RK_NO) U
- ON U.ITEM_ID = C.ITEM_ID
+ GROUP BY ITEM_NO, PBILL_NO, WORK_LINE,RK_NO) U
+ ON U.ITEM_NO = C.ITEM_NO
AND U.WORK_LINE = C.WORK_LINE
AND U.PBILL_NO = C.PBILL_NO
AND U.RK_NO = C.RK_NO
LEFT JOIN (SELECT SUM(QUANTITY) RK_QTY,
ITEM_NO,
- ITEM_ID,
EBELN,
WORK_LINE,BILL_NO
FROM MES_INV_ITEM_IN_C_ITEMS
GROUP BY ITEM_NO, EBELN, WORK_LINE,BILL_NO) s1
- ON S1.ITEM_ID = C.ITEM_ID
+ ON S1.ITEM_NO = C.ITEM_NO
AND S1.WORK_LINE = C.WORK_LINE
AND S1.EBELN = C.PBILL_NO
AND S1.BILL_NO = C.RK_NO
@@ -260,7 +367,7 @@
// 鍒ゆ柇琛屾槸鍚﹂噸澶�
var cf_Num = Db.Queryable<MesInvItemOutItems>()
- .Where(s1 => s1.ItemId == item.ItemId &&
+ .Where(s1 => s1.ItemNo == item.ItemNo &&
s1.WorkLine == item.WorkLine &&
s1.PbillNo == item.PbillNo &&
s1.ItemOutId == mesInvItemOuts.Id &&
@@ -431,7 +538,7 @@
(b, bar, c, d) => new
JoinQueryInfos(
JoinType.Left, b.ItemBarcode == bar.ItemBarcode,
- JoinType.Left, b.ItemId == c.Id &&
+ JoinType.Left, b.ItemNo == c.ItemNo &&
b.Company == c.Company &&
b.Factory == c.Factory,
JoinType.Left, b.DepotCode == d.DepotCode &&
@@ -465,16 +572,16 @@
NVL(s1.RK_QTY, 0) - NVL(KT_QTY, 0) KT_QTY,
R.DEPOT_SECTION_CODE, S.ITEM_MODEL, S.ITEM_NAME,
K.CGB014, Q.STAFF_NAME, R.DEPOT_CODE
- FROM MES_INV_ITEM_OUT_ITEMS C JOIN MES_ITEMS S ON C.ITEM_ID = S.ID
- LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE, RK_NO
+ FROM MES_INV_ITEM_OUT_ITEMS C JOIN MES_ITEMS S ON C.ITEM_NO = S.ITEM_NO
+ LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO, PBILL_NO, WORK_LINE, RK_NO
FROM MES_INV_ITEM_OUT_ITEMS
- GROUP BY ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE, RK_NO) U
- ON U.ITEM_ID = C.ITEM_ID AND U.WORK_LINE = C.WORK_LINE
+ GROUP BY ITEM_NO, PBILL_NO, WORK_LINE, RK_NO) U
+ ON U.ITEM_NO = C.ITEM_NO AND U.WORK_LINE = C.WORK_LINE
AND U.PBILL_NO = C.PBILL_NO AND U.RK_NO = C.RK_NO
- LEFT JOIN (SELECT SUM(QUANTITY) RK_QTY, ITEM_NO,ITEM_ID, EBELN, WORK_LINE, BILL_NO
+ LEFT JOIN (SELECT SUM(QUANTITY) RK_QTY, ITEM_NO, EBELN, WORK_LINE, BILL_NO
FROM mes_inv_item_in_c_details
- GROUP BY ITEM_NO,ITEM_ID, EBELN, WORK_LINE, BILL_NO) s1
- ON S1.ITEM_ID = C.ITEM_ID AND S1.WORK_LINE = C.WORK_LINE
+ GROUP BY ITEM_NO, EBELN, WORK_LINE, BILL_NO) s1
+ ON S1.ITEM_NO = C.ITEM_NO AND S1.WORK_LINE = C.WORK_LINE
AND S1.EBELN = C.PBILL_NO AND S1.BILL_NO = C.RK_NO
LEFT JOIN MES_ROH_IN_DATA K
ON K.BILL_NO = C.PBILL_NO AND C.WORK_LINE = K.PURCHASE_ORDER_LINE_NUMBER
@@ -482,10 +589,10 @@
left join MES_STAFF Q on H.PURCHASER = Q.STAFF_NAME
LEFT JOIN MES_SUPPLIER T ON H.SUPPLIER = T.ID
LEFT JOIN (select *
- from (SELECT row_number() over (partition by DEPOT_CODE,ITEM_ID, EBELN order by CREATE_DATE) rn, DEPOT_CODE, DEPOT_SECTION_CODE,ITEM_ID
+ from (SELECT row_number() over (partition by DEPOT_CODE, ITEM_NO, EBELN order by CREATE_DATE) rn, DEPOT_CODE, DEPOT_SECTION_CODE, ITEM_NO,
EBELN FROM mes_inv_item_in_c_details
where EBELN is not null)
- where rn = 1 and ROWNUM = 1) R ON R.ITEM_ID = C.ITEM_ID) where 1=1 {0}",
+ where rn = 1 and ROWNUM = 1) R ON R.ITEM_NO = C.ITEM_NO) where 1=1 {0}",
where);
var results = Db.Ado.SqlQuery<MESInvItemOutItems>(sql);
return results;
@@ -501,13 +608,13 @@
FROM (SELECT f_get_section_code2('1000', '1000', b.ITEM_NO,'{0}') DEPOT,
B.ITEM_NO, A.ITEM_MODEL,A.ITEM_NAME,to_char(nvl(b.QUANTITY, 0) - nvl(D.QUANTITY_OK, 0),'FM9999999990.00') QTY
FROM MES_INV_ITEM_OUT_ITEMS B LEFT JOIN mes_ITEMS A
- ON B.ITEM_ID = A.ID LEFT JOIN (SELECT ITEM_OUT_ID,
- ITEM_NO,ITEM_ID,PBILL_NO,SUM(QUANTITY) QUANTITY_OK,RK_NO
+ ON B.ITEM_NO = A.ITEM_NO LEFT JOIN (SELECT ITEM_OUT_ID,
+ ITEM_NO,PBILL_NO,SUM(QUANTITY) QUANTITY_OK,RK_NO
FROM MES_INV_ITEM_OUT_C_DETAILS
WHERE ITEM_OUT_ID = {1}
- GROUP BY ITEM_OUT_ID,ITEM_NO,ITEM_ID,PBILL_NO,RK_NO) D
+ GROUP BY ITEM_OUT_ID,ITEM_NO,PBILL_NO,RK_NO) D
ON D.ITEM_OUT_ID = B.ITEM_OUT_ID
- AND D.ITEM_ID = B.ITEM_ID
+ AND D.ITEM_NO = B.ITEM_NO
AND D.PBILL_NO = B.PBILL_NO
AND D.RK_NO = B.RK_NO
WHERE B.ITEM_OUT_ID = {1}
@@ -547,21 +654,21 @@
to_char(b.CREATE_DATE, 'yyyy-mm-dd') as CREATE_DATE
from mes_inv_item_in_c_details b
left join mes_inv_item_ins a on b.item_in_id = a.id
- LEFT JOIN (select s.item_no,s.item_id, s.work_line, s.Work_no,
+ LEFT JOIN (select s.item_no, s.work_line, s.Work_no,
sum(s.quantity) quantity, S.RK_NO
from mes_inv_item_out_c_details s
- group by s.item_no,s.item_id, s.work_line, s.work_no, S.RK_NO) s1
+ group by s.item_no, s.work_line, s.work_no, S.RK_NO) s1
ON NVL(B.work_LINE, '0') = NVL(s1.work_LINE, '0')
and B.work_no = s1.work_no
- and B.item_id = s1.item_id
+ and B.item_no = s1.item_no
AND B.BILL_NO = S1.RK_NO
- LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE, RK_NO
+ LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO, PBILL_NO, WORK_LINE, RK_NO
FROM MES_INV_ITEM_OUT_ITEMS
- GROUP BY ITEM_NO,ITEM_ID, PBILL_NO, WORK_LINE, RK_NO) U
- ON U.ITEM_ID = B.ITEM_ID
+ GROUP BY ITEM_NO, PBILL_NO, WORK_LINE, RK_NO) U
+ ON U.ITEM_NO = B.ITEM_NO
AND U.WORK_LINE = B.WORK_LINE
AND U.PBILL_NO = B.WORK_NO AND U.RK_NO = B.BILL_NO
- left join mes_items s2 on b.item_id = s2.id
+ left join mes_items s2 on b.item_no = s2.item_no
left join purdha da on da.dha001 = b.cbill_no
WHERE A.BILL_NO LIKE 'Q%'
AND A.CBILL_NO like 'L%'
@@ -705,4 +812,162 @@
return ItemOutNos;
}
+ //鐢熶骇棰嗘枡鍗曞鏍稿墠鏍￠獙
+
+
+ public MessageCenter SaveProductionMessageCenter(WarehouseQuery entity)
+ {
+ var message = ProductionMesToErpParam(entity);
+
+ var executeReturnIdentity =
+ Db.Insertable(message).ExecuteReturnIdentity();
+ if (executeReturnIdentity > 0)
+ {
+ message.Id = executeReturnIdentity;
+ message.Pid = executeReturnIdentity;
+ return message;
+ }
+
+ throw new Exception("鑾峰彇鏁版嵁澶辫触");
+ }
+
+ private MessageCenter ProductionMesToErpParam(WarehouseQuery query)
+ {
+ var erpParameters = "";
+ var title = "";
+ var tableName = "INV_ITEM_OUTS_" + query.Type;
+ if ("A".Equals(query.Type))
+ {
+ erpParameters =
+ GetProductionErpParameters(query.billNo, query.userName);
+ title = "鐢熶骇棰嗘枡鍗�" + query.billNo + "瀹℃牳";
+ }
+
+ var ErpUrl = AppsettingsUtility.Settings.ProductionErpUrl;
+ var message = new MessageCenter
+ {
+ TableName = tableName,
+ Url = ErpUrl,
+ Status = 1,
+ CreateBy = query.userName,
+ Route = query.billNo,
+ Title = title,
+ PageName = "Warehouse/ProductionPick/Add?id=" + query.id +
+ "&itemOutNo=" + query.billNo,
+ CreateDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
+ Method = "POST",
+ Seq = 1,
+ Data = erpParameters,
+ IsMessage = 0,
+ ContentType = "application/x-www-form-urlencoded"
+ };
+ return message;
+ }
+
+ private string GetProductionErpParameters(string? queryBillNo,
+ string? userName)
+ {
+ userName ??= "system";
+ var mesInvItemOuts = Db.Queryable<MesInvItemOuts>()
+ .Where(it => it.ItemOutNo == queryBillNo)
+ .First();
+
+ if (mesInvItemOuts == null) throw new Exception("棰嗘枡鍗曞彿涓嶅瓨鍦�");
+
+ var womcaa = Db.Queryable<Womcaa>()
+ .Where(it => it.Caa020 == mesInvItemOuts.WorkNo)
+ .First();
+
+ if (womcaa == null) throw new Exception("浠诲姟鍗曞彿涓嶅瓨鍦�");
+
+ var Departmentcode = Db.Queryable<SysDepartment, MesInvItemOuts>(
+ (a, b) =>
+ new JoinQueryInfos(JoinType.Left,
+ a.Departmentname == b.OutPart
+ )).Where((a, b) =>
+ b.OutPart == mesInvItemOuts.OutPart &&
+ b.ItemOutNo == queryBillNo).Select(a => a.Departmentcode)
+ .First();
+
+ if (string.IsNullOrEmpty(Departmentcode))
+ throw new Exception("閮ㄩ棬淇℃伅涓嶅瓨鍦�");
+
+ // 妫�鏌ラ鏂欏崟鐘舵��
+ if (mesInvItemOuts.Status == 1) throw new Exception("棰嗘枡鍗曞凡瀹℃牳锛屼笉鑳介噸澶嶆帹閫�");
+
+ var C_OUT_ITEMS = Db
+ .Queryable<MesInvItemOutItems, Womdab, Womdaa, Womcaa, Womcab>(
+ (c, b, d, e, f) =>
+ new JoinQueryInfos(
+ JoinType.Left, b.Id == c.ItemDabid,
+ JoinType.Left, d.Id == b.Pid,
+ JoinType.Left, e.Caa001 == d.Daa021,
+ JoinType.Left, f.Eid == e.Erpid
+ ))
+ .Where((c, b, d, e, f) => c.ItemOutId == mesInvItemOuts.Id)
+ .Select((c, b, d, e, f) => new
+ {
+ c.Id,
+ c.ItemNo,
+ c.Quantity,
+ c.DepotCode,
+ c.DepotSectionCode,
+ c.ItemId,
+ erpId = b.ErpId,
+ e.Caa015,
+ f.PositionNo
+ }).ToList();
+
+ var scllentryList = new List<dynamic>();
+
+ foreach (var item in C_OUT_ITEMS)
+ {
+ var sql =
+ "SELECT FNAME FROM MES_UNIT WHERE ID = (SELECT ITEM_UNIT FROM MES_ITEMS WHERE ID = '" +
+ item.ItemId + "')";
+ var C_ITEM_UNIT = Db.Ado.SqlQuerySingle<string>(sql);
+
+ var xsddh = item.Caa015 ?? "";
+ var wzh = item.PositionNo ?? "";
+ var qty = ((int)item.Quantity).ToString();
+ var erpid = ((int)item.erpId).ToString();
+ var id = ((int)item.Id).ToString();
+ scllentryList.Add(new
+ {
+ FMaterialId = item.ItemNo,
+ FUnitID = C_ITEM_UNIT,
+ FAppQty = qty,
+ FActualQty = qty,
+ FStockId = item.DepotCode,
+ FPPBomEntryId = erpid,
+ F_UNW_Text_xsddh = xsddh,
+ F_UNW_TEXT_WZH = wzh,
+ F_MES_ENTRYID = id
+ });
+ }
+
+ var dataJson = new
+ {
+ F_MES_ID = mesInvItemOuts.Id.ToString(),
+ FDate = DateTime.Now.ToString("yyyy-MM-dd"),
+ FPickerId = userName,
+ F_UNW_LargeText_BZ = " ",
+ F_UNW_KH = " ",
+ F_UNW_DDSL = womcaa.Caa012.ToString(),
+ F_UNW_Text_CZG = userName,
+ scllentry = scllentryList
+ };
+
+ return "taskname=SCLL&mesid=" + mesInvItemOuts.Id +
+ "&optype=create&datajson=" +
+ JsonConvert.SerializeObject(
+ dataJson);
+ }
+
+ public bool AuditProduction(WarehouseQuery query)
+ {
+ query.status = 1;
+ //瀹℃牳
+ return Update(query);
+ }
}
\ No newline at end of file
--
Gitblit v1.9.3