采购退料，优化代码

啊鑫

2024-08-10 fd6838164120ff30f76780f6478dcb0e04983ee9

 MES.Service/service/Warehouse/MesInvItemOutsManager.cs

@@ -1,7 +1,10 @@
using MES.Service.DB;
using System.Data;
using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using MES.Service.util;
using SqlSugar;
using DbType = System.Data.DbType;

namespace MES.Service.service.Warehouse;

@@ -10,16 +13,190 @@
    //当前类已经继承了 Repository 增、删、查、改的方法
    //这里面写的代码不会给覆盖,如果要重新生成请删除 MesInvItemOutsManager.cs

    public bool ScanCode(WarehouseQuery query)
    {
        decimal billTypeId = 200;
        decimal transactionNo = 203;

        var count = Db.Queryable<MesInvItemOutCDetails>()
            .Where(a =>
                a.ItemBarcode == query.barcode && a.TaskNo == query.billNo)
            .Count();

        if (count > 0) throw new Exception("条码重复扫描，请核对！");

        //验证条码
        var mesInvItemStocks = Db.Queryable<MesInvItemStocks>()
            .Where(a => a.ItemBarcode == query.barcode
                        && a.Quantity > 0).Single();

        if (mesInvItemStocks == null)
            throw new Exception(query.barcode + "库存中无此条码，请核对！");

        //验证出库单mes_inv_item_outs
        var mesInvItemOuts = Db.Queryable<MesInvItemOuts>()
            .Where(d => d.ItemOutNo == query.billNo
                        && d.BillTypeId == billTypeId &&
                        d.TransactionNo == transactionNo).Single();

        //出库单的校验
        if (mesInvItemOuts == null)
            throw new Exception("出库单  " + query.billNo + " 不存在，请确认！");

        //未审核的不允许继续
        if (mesInvItemOuts.Status == null || mesInvItemOuts.Status == 0)
            throw new Exception("出库单  " + query.billNo + "未审核");

        //已推送的不允许继续
        if (mesInvItemOuts.Nflag == 1)
            throw new Exception("出库单  " + query.billNo + "已经推送，无法重复推送");

        var depotCodeOut = mesInvItemOuts.DepotCode ?? "0";
        var depotsCodeStock = mesInvItemStocks.DepotsCode ?? "0";

        if (!depotCodeOut.Equals(depotsCodeStock))
        {
            // 创建错误消息
            var errorMessage =
                $"002[条码仓库{depotsCodeStock}与申请仓库{depotCodeOut}不一致，请核对！";

            throw new Exception(errorMessage);
        }

        var StocksNum = mesInvItemStocks.Quantity;

        var sql = string.Format(
            @"select nvl(SUM(S.QUANTITY),0) - nvl(SUM(S.TL_QTY),0)
                  from MES_INV_ITEM_OUT_ITEMS S
                  LEFT JOIN mes_inv_item_outs d
                    ON S.ITEM_OUT_ID = D.ID
                 where d.item_out_no = '{0}'
                   and d.bill_type_id = {1}
                   and d.transaction_no = {2}
                   AND S.item_no = '{3}'
                   and nvl(d.status, 0) = 1
                   and nvl(d.nflag, 0) = 0", query.billNo, billTypeId,
            transactionNo,
            mesInvItemStocks.ItemNo);

        var cqty = Db.Ado.SqlQuerySingle<decimal>(sql);

        if (cqty == null)
            throw new Exception("物料" + mesInvItemStocks.ItemNo +
                                "的辅助属性与条码不一致！请检查");

        if (cqty < StocksNum) throw new Exception("请确认发料数量");

        //调用存储过程
        try
        {
            // 定义输出参数
            var outputResult = new SugarParameter("C_RESULT", null,
                DbType.Int32, ParameterDirection.Output,
                4000);

            var outputMessage = new SugarParameter("MSG", null, DbType.String,
                ParameterDirection.Output, 4000);

            // 定义输入参数
            var parameters = new List<SugarParameter>
            {
                new("p_item_barcode", query.barcode,
                    DbType.String, ParameterDirection.Input),
                new("p_bill_no", query.billNo, DbType.String,
                    ParameterDirection.Input),
                new("pi_factory", "1000", DbType.String,
                    ParameterDirection.Input),
                new("pi_company", "1000", DbType.String,
                    ParameterDirection.Input),
                new("c_user", query.userName, DbType.String,
                    ParameterDirection.Input),
                outputResult,
                outputMessage
            };

            // 使用 SqlSugar 执行存储过程
            Db.Ado.ExecuteCommand(
                "BEGIN MES_CG_UPDATE_BARCODES(:p_item_barcode, :p_bill_no, :pi_factory, :pi_company, :c_user, :C_RESULT, :MSG); END;",
                parameters.ToArray());

            // 获取输出参数的值
            var resultValue = outputResult.Value?.ToString();
            var messageValue = outputMessage.Value?.ToString();

            if ("1".Equals(resultValue)) throw new Exception("条码扣除失败");
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

        // 变量用于保存查询结果
        decimal? C_SQ_QTY = 0;
        decimal? C_OK_QTY = 0;
        var c_result = string.Empty;
        var C_COUNT = 0;


        sql = string.Format(@"
                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, PBILL_NO, RK_NO, SUM(QUANTITY) AS QUANTITY_OK
                    FROM MES_INV_ITEM_OUT_C_DETAILS
                    GROUP BY ITEM_OUT_ID, ITEM_NO, PBILL_NO, RK_NO
                ) D
                ON D.ITEM_OUT_ID = C.ITEM_OUT_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);

        var queryResult = Db.Ado.SqlQuerySingle<dynamic>(sql);

        C_SQ_QTY = queryResult?.SQ_QTY;
        C_OK_QTY = queryResult?.OK_QTY;

        // 判断逻辑
        if (C_SQ_QTY < C_OK_QTY) throw new Exception("扫描数量超过申请数量,请重新扫描！");

        if (C_SQ_QTY == C_OK_QTY) return true;

        return false;
    }


    public bool Audit(WarehouseQuery query)
    {
        // 审核前校验
        Validate(query);
        query.status = 1;
        //审核
        return Update(query);
    }

    public bool SetNFlag(WarehouseQuery query)
    {
        return Db.Updateable<MesInvItemOuts>()
            .SetColumns(s => s.Status == 1)
            .SetColumns(s => s.CheckUser == query.userName)
            .SetColumns(s => s.Nflag == 1)
            .Where(s => s.ItemOutNo == query.billNo)
            .ExecuteCommand() > 0;
    }

    public bool DeApprove(WarehouseQuery query)
    {
        query.date = null;
        query.status = 0;
        return Update(query);
    }

    private bool Update(WarehouseQuery entity)
    {
        return Db.Updateable<MesInvItemOuts>()
            .SetColumns(s => s.Status == entity.status)
            .SetColumns(s => s.CheckUser == entity.userName)
            .SetColumns(s => s.CheckDate == DateTime.Now)
            .Where(s => s.Id == query.id).ExecuteCommand() > 0;
            .Where(s => s.Id == entity.id).ExecuteCommand() > 0;
    }


@@ -218,6 +395,8 @@
                        a.CheckUser == u3.Fcode))
                .WhereIF(query.id > 0,
                    (a, e, i, u1, u3) => a.Id == query.id)
                .WhereIF(!string.IsNullOrEmpty(query.billNo),
                    (a, e, i, u1, u3) => a.ItemOutNo == query.billNo)
                .Select((a, e, i, u1, u3) => new MesInvItemOuts
                {
                    Id = a.Id,
@@ -240,6 +419,35 @@
                    Status = a.Status,
                    ItemOutNo = a.ItemOutNo
                }).ToPageList(query.PageIndex, query.Limit);
    }

    public List<MesInvItemOutCDetails> GetScanBarcode(WarehouseQuery query)
    {
        return Db.Queryable<MesInvItemOutCDetails, MesInvItemBarcodes, MesItems,
                MesDepots>(
                (b, bar, c, d) => new
                    JoinQueryInfos(
                        JoinType.Left, b.ItemBarcode == bar.ItemBarcode,
                        JoinType.Left, b.ItemNo == c.ItemNo &&
                                       b.Company == c.Company &&
                                       b.Factory == c.Factory,
                        JoinType.Left, b.DepotCode == d.DepotCode &&
                                       b.Company == d.Company &&
                                       b.Factory == d.Factory
                    ))
            .Where((b, bar, c, d) => b.ItemOutId == query.id)
            .Select((b, bar, c, d) => new MesInvItemOutCDetails
            {
                Quantity = b.Quantity,
                DepotSectionCode = b.DepotSectionCode,
                DepotCode = b.DepotCode,
                ItemNo = b.ItemNo,
                ItemBarcode = b.ItemBarcode,
                ItemName = c.ItemName,
                ItemModel = c.ItemModel,
                DepotName = d.DepotName,
                Unit = bar.Unit
            }).ToList();
    }

    //MESInvItemOutItems
@@ -280,8 +488,11 @@
        return results;
    }

    public List<InventoryItem> GetSumItem(WarehouseQuery query)
    public OutItemDto GetSumItem(WarehouseQuery query)
    {
        var mesInvItemOuts = base.GetSingle(it => it.ItemOutNo == query.billNo);
        if (mesInvItemOuts == null) throw new Exception("采购退货单不存在");

        var sql = string.Format(@"SELECT DEPOT ,ITEM_NO,ITEM_MODEL,
           ITEM_NAME,QTY FROM (SELECT SUM(QTY) QTY, DEPOT, ITEM_NO, ITEM_MODEL, ITEM_NAME
              FROM (SELECT f_get_section_code2('1000', '1000', b.ITEM_NO,'{0}') DEPOT,
@@ -300,11 +511,25 @@
                       AND nvl(b.QUANTITY, 0) - nvl(D.QUANTITY_OK, 0) > 0
                     ORDER BY f_get_section_code2('1000','1000',b.ITEM_NO,'{0}'),
                              A.ITEM_MODEL,A.ITEM_NAME)
             GROUP BY DEPOT, ITEM_NO, ITEM_MODEL, ITEM_NAME)", query.DepotCode,
            query.id);
             GROUP BY DEPOT, ITEM_NO, ITEM_MODEL, ITEM_NAME)",
            mesInvItemOuts.DepotCode,
            mesInvItemOuts.Id);

        var results = Db.Ado.SqlQuery<InventoryItem>(sql);
        return results;

        var mesInvItemStocks = Db.Queryable<MesInvItemStocks>()
            .Where(a => a.ItemBarcode == query.barcode).Single();

        var dto = new OutItemDto();
        dto.SumItem = results;

        if (mesInvItemStocks != null)
        {
            dto.ItemNo = mesInvItemStocks.ItemNo;
            dto.Quantity = mesInvItemStocks.Quantity;
        }

        return dto;
    }

    public List<InventoryItemDetail> SelectMaterials(WarehouseQuery query)
@@ -369,4 +594,112 @@

        return "NTL" + date + number;
    }

    public MessageCenter SaveMessageCenter(WarehouseQuery query)
    {
        var message = MesToErpParam(query);

        var executeReturnIdentity =
            Db.Insertable(message).ExecuteReturnIdentity();
        if (executeReturnIdentity > 0)
        {
            message.Id = executeReturnIdentity;
            message.Pid = executeReturnIdentity;
            return message;
        }

        throw new Exception("获取数据失败");
    }


    public MessageCenter MesToErpParam(WarehouseQuery query)
    {
        var erpParameters = "";
        var title = "";
        var tableName = "MES_INV_ITEM_OUTS_" + query.Type;
        if ("A".Equals(query.Type))
        {
            erpParameters = GetErpParameters(query.billNo);
            title = "采购退货单" + query.billNo + "审核";
        }
        else if ("B".Equals(query.Type))
        {
            erpParameters = GetDeApprovePam(query.id);
            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/PurchaseReturn/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;
    }

    public bool DeApproveBefore(WarehouseQuery query)
    {
        var mesInvItemOuts = base.GetById(query.id);
        if (mesInvItemOuts == null) throw new Exception("出库单不存在");

        var nflag = mesInvItemOuts.Nflag ?? 0;

        if (nflag != 0) return true;

        return false;
    }

    private string GetDeApprovePam(decimal? id)
    {
        var sid = (int)id;
        var encodedUrl = "taskname=CGTL&mesid=" + sid +
                         "&optype=delete&datajson={}";

        return encodedUrl;
    }

    private string GetErpParameters(string? billNo)
    {
        var invItemIns = Db.Queryable<MesInvItemOuts>()
            .Single(x => x.ItemOutNo == billNo);

        //调用function函数
        var sql =
            $"SELECT F_GENERATE_DATA_INSERTED('{billNo}') FROM DUAL;";
        var jsonString = Db.Ado.SqlQuerySingle<string>(sql);

        var encodedUrl = "taskname=CGTL&mesid=" + invItemIns.Id +
                         "&optype=create&datajson=" + jsonString;

        return encodedUrl;
    }

    public List<string> GetItemOutNo()
    {
        var data5 = Db.Queryable<MesInvItemOuts>().Where(it =>
                it.BillTypeId == 200 && it.TransactionNo == 203 &&
                it.Status == 1)
            .OrderBy(it => it.Id, OrderByType.Desc)
            .ToPageList(1, 20);

        var ItemOutNos = data5.FindAll(a => a.Nflag == null || a.Nflag == 0)
            .Select(s => s.ItemOutNo).ToList();
        //
        // var ItemOutNos = data5
        //     .Select(s => s.ItemOutNo).ToList();

        return ItemOutNos;
    }
}