生产领料单erp推送

啊鑫

2024-08-28 d8bbd1bc855990e908fc5df9594fc7b4e4628eed

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