1

啊鑫

2024-11-29 48675bbf98749545c2b5b45e4a13f74993626a95

 service/Warehouse/MesInvItemOutsManager.cs

@@ -144,7 +144,7 @@

        //返回ID
        var command = Db.Insertable(mesInvItemOuts)
            .ExecuteCommand();
            .IgnoreColumns(true).ExecuteCommand();

        if (command <= 0) throw new Exception("插入错误");

@@ -161,7 +161,7 @@
        });

        var insertable = Db.Insertable(dto.SaveItems)
            .ExecuteCommand();
            .IgnoreColumns(true).ExecuteCommand();

        if (insertable < 0) throw new Exception("插入错误");

@@ -316,9 +316,7 @@

        var nflag = mesInvItemOuts.Nflag ?? 0;

        if (nflag != 0) return true;

        return false;
        return nflag != 0;
    }

    public bool Audit(WarehouseQuery query)
@@ -337,14 +335,14 @@
        {
            var isValid = Guid.TryParse(entity.id, out parsedGuid);
            if (!isValid)
                throw new ApplicationException("GUID转换错误"); 
                throw new ApplicationException("GUID转换错误");
        }

        if (!UtilityHelper.CheckGuid(parsedGuid))
        {
            throw new  ApplicationException("更新错误，数据是不合法的");
            throw new ApplicationException("更新错误，数据是不合法的");
        }
        

        var userName = entity.userName ?? "system";
        return Db.Updateable<MesInvItemOuts>()
            .SetColumns(s => s.Status == entity.status)
@@ -390,6 +388,75 @@
        throw new Exception("获取数据失败");
    }

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

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

        return ItemOutNos;
    }

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


        var dto = new OutItemDto();
        dto.SumItem = GetItems(query);

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

        if (mesInvItemStocks == null) return dto;

        dto.ItemNo = mesInvItemStocks.ItemNo;
        dto.Quantity = mesInvItemStocks.Quantity;

        return dto;
    }

    public List<MesInvItemOutCDetails> GetScanBarcode(WarehouseQuery query)
    {
        //string转guid
        var parsedGuid = Guid.Empty;
        if (string.IsNullOrEmpty(query.id)) return [];

        var isValid = Guid.TryParse(query.id, out parsedGuid);
        if (!isValid)
            throw new ApplicationException("GUID转换错误");

        var mesInvItemOutCDetailsList = Db
            .Queryable<MesInvItemOutCDetails, MesInvItemBarcodes, MesItems,
                MesDepots>(
                (b, bar, c, d) => new
                    JoinQueryInfos(
                        JoinType.Left, b.ItemBarcode == bar.ItemBarcode,
                        JoinType.Left, b.ItemId == c.Id,
                        JoinType.Left, b.DepotId == d.DepotId
                    ))
            .Where((b, bar, c, d) => b.ItemOutId == parsedGuid)
            .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();

        return mesInvItemOutCDetailsList;
    }

    public MessageCenter MesToErpParam(WarehouseQuery query)
    {
        var erpParameters = "";
@@ -426,4 +493,392 @@
        };
        return message;
    }

    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 isnull(SUM(S.QUANTITY),0) - isnull(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.GUID
                 where d.item_out_no = '{0}'
                   and d.bill_type_id = {1}
                   and d.transaction_no = {2}
                   AND S.item_ID = '{3}'
                   and isnull(d.status, 0) = 1
                   and isnull(d.nflag, 0) = 0", query.billNo, billTypeId,
            transactionNo,
            mesInvItemStocks.ItemId);

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

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

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

        //调用存储过程 
        var prcRfPdaBarcodeCgth = PrcRfPdaBarcodeCgth(query);
        if (!prcRfPdaBarcodeCgth)
        {
            throw new Exception("执行失败");
        }

        // 变量用于保存查询结果
        decimal? C_SQ_QTY = 0;
        decimal? C_OK_QTY = 0;
        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 isnull(C.PBILL_NO, '0') = isnull(D.PBILL_NO, '0')
                WHERE C.ITEM_OUT_ID = '{0}'", mesInvItemOuts.Guid);

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

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

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

        return C_SQ_QTY == C_OK_QTY;
    }

    private bool PrcRfPdaBarcodeCgth(WarehouseQuery query)
    {
        var c_user = query.userName;
        var p_bill_no = query.billNo;
        var p_item_barcode = query.barcode;
        var p_bill_type_id = 200;
        var p_transaction_no = 203;
        int c_num;
        decimal C_STOCKNUM, C_SQTY, C_OKQTY, C_CQTY;

        var commit = UseTransaction(db =>
        {
            if (string.IsNullOrEmpty(p_bill_no))
            {
                throw new Exception("请选取单据号！");
            }

            // Retrieve mes_inv_item_outs
            var c_mes_inv_item_outs = db.Queryable<MesInvItemOuts>()
                .Where(d => d.ItemOutNo == p_bill_no)
                .First();

            if (c_mes_inv_item_outs == null)
            {
                throw new Exception("请选择或扫描正确的采购退货单！");
            }

            if (c_mes_inv_item_outs.Status == 0)
            {
                throw new Exception("采购退货申请单未审核！");
            }

            if (c_mes_inv_item_outs.Nflag == 1)
            {
                throw new Exception("采购退货单已完结推送ERP,不能操作！");
            }

            // Check for duplicate barcode scan
            c_num = db.Queryable<MesInvItemOutCDetails>()
                .Where(b =>
                    b.ItemBarcode == p_item_barcode &&
                    b.ItemOutId == c_mes_inv_item_outs.Guid)
                .Count();

            if (c_num > 0)
            {
                throw new Exception("此条码已采购退货扫码完成！");
            }

            // Query item stock info
            var c_mes_inv_item_stocks = db.Queryable<MesInvItemStocks>()
                .Where(t => t.ItemBarcode == p_item_barcode && t.Quantity > 0)
                .First();

            if (c_mes_inv_item_stocks == null)
            {
                throw new Exception($"库存中无此条码，请核对！{p_item_barcode}");
            }

            // Validate depot codes
            if (c_mes_inv_item_outs.DepotCode !=
                c_mes_inv_item_stocks.DepotsCode)
            {
                throw new Exception(
                    $"此条码属于仓库 {c_mes_inv_item_stocks.DepotsCode} 与采购退货申请仓库 {c_mes_inv_item_outs.DepotCode} 不一致，请核对！");
            }

            C_STOCKNUM = c_mes_inv_item_stocks.Quantity.Value;

            // Calculate the total and completed quantities for the item
            var quantities = db.Queryable<MesInvItemOutItems, MesInvItemOuts>(
                    (s, d) => new object[]
                    {
                        JoinType.Left, s.ItemOutId == d.Guid,
                    })
                .Where((s, d) =>
                    d.ItemOutNo == p_bill_no &&
                    s.ItemId == c_mes_inv_item_stocks.ItemId
                    && d.Status == 1
                    && (d.Nflag == 0 || d.Nflag == null))
                .Select((s, d) => new
                {
                    TotalQuantity = SqlFunc.AggregateSum(s.Quantity),
                    CompletedQuantity = SqlFunc.AggregateSum(s.TlQty)
                })
                .First();

            if (quantities == null)
            {
                throw new Exception("采购退料单 '" + p_bill_no + "'未审核");
            }

            C_SQTY = quantities.TotalQuantity ?? 0;
            C_OKQTY = quantities.CompletedQuantity ?? 0;
            C_CQTY = C_SQTY - C_OKQTY;

            if (C_CQTY < C_STOCKNUM) return 1;

            // Handle barcode reduction
            decimal p_number2 = C_STOCKNUM;
            var itemOutItems = db.Queryable<MesInvItemOutItems>()
                .Where(s =>
                    s.ItemOutId == c_mes_inv_item_outs.Guid &&
                    s.ItemId == c_mes_inv_item_stocks.ItemId)
                .ToList();

            itemOutItems = itemOutItems
                .Where(s => s.Quantity - (s.TlQty ?? 0) > 0).ToList();

            foreach (var CMI in itemOutItems)
            {
                if (p_number2 == 0)
                    break;

                var c_mes_inv_item_arn_detail = db
                    .Queryable<MesInvItemArnDetail>()
                    .Where(t =>
                        t.CbillNo == CMI.PbillNo
                        && t.Ebeln == CMI.WorkNo &&
                        //t.WorkLine == CMI.WorkLine &&
                        t.ItemId == CMI.ItemId)
                    .First();

                if (c_mes_inv_item_arn_detail == null)
                {
                    throw new Exception("未找到采购退料来源单据");
                }

                if (CMI.Quantity - CMI.TlQty <= p_number2)
                {
                    db.Updateable<MesInvItemOutItems>()
                        .SetColumns(it => new MesInvItemOutItems
                            { TlQty = (int)it.Quantity, Status = 1 })
                        .Where(it => it.Guid == CMI.Guid)
                        .ExecuteCommand();

                    p_number2 -= CMI.Quantity ?? 0 - CMI.TlQty ?? 0;

                    db.Insertable(new MesInvItemOutCDetails
                    {
                        ItemOutId = c_mes_inv_item_outs.Guid,
                        ItemBarcode = p_item_barcode,
                        ItemNo = c_mes_inv_item_stocks.ItemNo,
                        LotNo = c_mes_inv_item_stocks.LotNo,
                        Quantity = CMI.Quantity - CMI.TlQty,
                        ForceOutFlag = 0,
                        CreateBy = c_user,
                        CreateDate = DateTime.Now,
                        LastupdateBy = c_user,
                        LastupdateDate = DateTime.Now,
                        DepotCode = c_mes_inv_item_stocks.DepotsCode,
                        DepotSectionCode =
                            c_mes_inv_item_stocks.DepotSectionsCode,
                        WorkNo = CMI.WorkNo,
                        WorkLine = CMI.WorkLine,
                        SuppNo = c_mes_inv_item_outs.SuppNo,
                        PbillNo = CMI.PbillNo,
                        ItemId = c_mes_inv_item_stocks.ItemId,
                        EbelnK3id = c_mes_inv_item_stocks.EbelnK3id,
                        LineK3id = c_mes_inv_item_stocks.LineK3id,
                        DepotId = c_mes_inv_item_outs.DepotId
                    }).IgnoreColumns(true).ExecuteCommand();

                    db.Insertable(new MesInvBusiness2
                    {
                        Status = 1,
                        BillTypeId = p_bill_type_id,
                        TransactionCode = p_transaction_no.ToString(),
                        BusinessType = 1,
                        ItemBarcode = p_item_barcode,
                        ItemNo = c_mes_inv_item_stocks.ItemNo,
                        LotNo = c_mes_inv_item_stocks.LotNo,
                        EpFlag = true,
                        Quantity = CMI.Quantity - CMI.TlQty,
                        FromInvDepotsCode = c_mes_inv_item_stocks.DepotsCode,
                        FromInvDepotSectionsCode =
                            c_mes_inv_item_stocks.DepotSectionsCode,
                        ToInvDepotsCode = null,
                        ToInvDepotSectionsCode = null,
                        Description = "采购退货",
                        CreateBy = c_user,
                        CreateDate = DateTime.Now,
                        LastupdateBy = c_user,
                        LastupdateDate = DateTime.Now,
                        TaskNo = CMI.WorkNo,
                        ItemId = c_mes_inv_item_stocks.ItemId,
                        EbelnK3id = c_mes_inv_item_stocks.EbelnK3id,
                        LineK3id = c_mes_inv_item_stocks.LineK3id,
                    }).IgnoreColumns(true).ExecuteCommand();

                    if (p_number2 == 0)
                    {
                        db.Deleteable<MesInvItemStocks>()
                            .Where(s => s.Guid == c_mes_inv_item_outs.Guid)
                            .ExecuteCommand();
                    }
                }
                else
                {
                    var mesInvItemOutItems = db.Queryable<MesInvItemOutItems>()
                        .Where(s => s.Guid == CMI.Guid).First();

                    db.Updateable<MesInvItemOutItems>()
                        .SetColumns(i =>
                            i.TlQty == p_number2 +
                            (mesInvItemOutItems.TlQty ?? 0))
                        .Where(i => i.Guid == CMI.Guid)
                        .ExecuteCommand();

                    db.Insertable(new MesInvItemOutCDetails
                    {
                        ItemOutId = c_mes_inv_item_outs.Guid,
                        ItemBarcode = p_item_barcode,
                        ItemNo = c_mes_inv_item_stocks.ItemNo,
                        LotNo = c_mes_inv_item_stocks.LotNo,
                        Quantity = p_number2,
                        ForceOutFlag = 0,
                        CreateBy = c_user,
                        CreateDate = DateTime.Now,
                        LastupdateBy = c_user,
                        LastupdateDate = DateTime.Now,
                        DepotCode = c_mes_inv_item_stocks.DepotsCode,
                        DepotSectionCode =
                            c_mes_inv_item_stocks.DepotSectionsCode,
                        WorkNo = CMI.WorkNo,
                        WorkLine = CMI.WorkLine,
                        SuppNo = c_mes_inv_item_outs.SuppNo,
                        ItemId = c_mes_inv_item_stocks.ItemId,
                        EbelnK3id = c_mes_inv_item_stocks.EbelnK3id,
                        LineK3id = c_mes_inv_item_stocks.LineK3id,
                        DepotId = c_mes_inv_item_outs.DepotId
                    }).IgnoreColumns(true).ExecuteCommand();

                    db.Insertable(new MesInvBusiness2
                    {
                        Status = 1,
                        BillTypeId = p_bill_type_id,
                        TransactionCode = p_transaction_no.ToString(),
                        BusinessType = 1,
                        ItemBarcode = p_item_barcode,
                        ItemNo = c_mes_inv_item_stocks.ItemNo,
                        LotNo = c_mes_inv_item_stocks.LotNo,
                        EpFlag = true,
                        Quantity = p_number2,
                        FromInvDepotsCode = c_mes_inv_item_stocks.DepotsCode,
                        FromInvDepotSectionsCode =
                            c_mes_inv_item_stocks.DepotSectionsCode,
                        Description = "采购退货",
                        CreateBy = c_user,
                        CreateDate = DateTime.Now,
                        LastupdateBy = c_user,
                        LastupdateDate = DateTime.Now,
                        TaskNo = CMI.PbillNo,
                        BillNo = p_bill_no,
                        WorkNo = CMI.WorkNo,
                        WorkLine = CMI.WorkLine,
                        SuppNo = c_mes_inv_item_outs.SuppNo,
                        SuppId = c_mes_inv_item_outs.SuppId.ToString(),
                        ItemId = c_mes_inv_item_stocks.ItemId,
                        EbelnK3id = c_mes_inv_item_stocks.EbelnK3id,
                        LineK3id = c_mes_inv_item_stocks.LineK3id,
                    }).IgnoreColumns(true).ExecuteCommand();
                    p_number2 = 0;
                }
            }

            if (p_number2 > 0)
            {
                throw new Exception("物料数量将超采购退料申请单数量,无法出库");
            }

            return 1;
        });

        return commit > 0;
    }
}