销售退货修改为存储过程

快乐的昕的电脑

16 分钟以前 d4769b70611aeb95ebd26ba83a3851f693f88998

 service/Warehouse/MesItemBlManager.cs

@@ -5,6 +5,7 @@
using NewPdaSqlServer.entity.Base;
using NewPdaSqlServer.util;
using SqlSugar;
using System.Text;

namespace NewPdaSqlServer.service.Warehouse;

@@ -16,15 +17,22 @@
    ///     获取生产补料单号列表
    /// </summary>
    /// <returns>补料单号列表</returns>
    public List<string> GetSCBLBillNo(WarehouseQuery query)
    public List<string> GetSCBLBillNo(dynamic query, dynamic RequestInfo)
    {
        var list = Db.Queryable<MesItemBl>()
            .Where(s => (s.Bl018 ?? false) == true
                        //&& (s.Bl019 ?? false) == false
                        && s.Bl008 == query.Type)
            .OrderByDescending(s => s.Bl006)
            .Select(s => s.BlNo)
            .ToList();
        var orgId = RequestInfo.OrgId;
        var sqlParams = new List<SugarParameter>
        {
            new("@orgId", orgId),
            new("@type", query.type),
            new("@dateTime", query.dateTime == "" ? null : query.dateTime),
            new("@lineNo", query.lineNo)

        };
        var sql2 = new StringBuilder("exec prc_pda_itemBl_billNoList @type,@orgId,@dateTime,@lineNo");

        var LineInfo = Db.Ado.SqlQuery<dynamic>(sql2.ToString(), sqlParams);

        var list = LineInfo.Select(s => (string)s.bl_no).ToList();
        return list;
    }

@@ -68,7 +76,7 @@
        //    .ToList();

        //return (mesItemBl.Bl001, blDetails);
        var sql = string.Format(@"SELECT c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,A.BLD007 FQty,A.BLD008 SQty,A.BLD007 - A.BLD008 DSQty,
        var sql = string.Format(@"SELECT c.id as ItemId,c.item_no ItemNo,c.item_name ItemName,c.item_model ItemModel,A.BLD007 FQty,A.BLD008 SQty,A.BLD007 - A.BLD008 DSQty,
       dbo.F_QX_GETRECODEPOTSE(A.bld012,'','','') as RecoKw,D.daa001
fROM MES_ITEM_BL_DETAIL A
         LEFT JOIN MES_ITEM_BL B ON A.MID = B.ID
@@ -150,7 +158,7 @@
            .First();

        if (mesItemBl == null)
            throw new Exception($"申请单 {query.blNo} 已撤回！");
            throw new Exception($"申请单 {query.blNo} 不存在！");

        if (mesItemBl.Bl018 != true)
            throw new Exception($"申请单 {query.blNo} 未审核！");
@@ -288,7 +296,7 @@
            // 检查并更新出库单物料明细
            var itemCount = db.Queryable<MesInvItemOutItems>()
                .Where(i =>
                    i.ItemOutId == outId && i.ItemId == stockBarcode.ItemId && i.ItemOutId == outId && i.DepotId == stockBarcode.DepotsId.ToString())
                    i.ItemOutId == outId && i.ItemDabid == blDetail.Id && i.ItemId == stockBarcode.ItemId && i.DepotId == stockBarcode.DepotId.ToString())
                .Count();

            if (itemCount > 0)
@@ -297,7 +305,7 @@
                    .SetColumns(i =>
                        i.TlQty == i.TlQty + stockBarcode.Quantity)
                    .Where(i =>
                        i.ItemOutId == outId && i.ItemId == stockBarcode.ItemId && i.ItemOutId == outId && i.DepotId == stockBarcode.DepotsId.ToString())
                        i.ItemOutId == outId && i.ItemDabid == blDetail.Id && i.ItemId == stockBarcode.ItemId && i.DepotId == stockBarcode.DepotId.ToString())
                    .ExecuteCommand();
            else
                // 插入新的物料明细记录
@@ -306,7 +314,7 @@
                    Guid = Guid.NewGuid(),
                    ItemOutId = outId,
                    ItemNo = blDetail.Bld002,
                    Quantity = stockBarcode.Quantity,
                    Quantity = blDetail.Bld007,
                    TlQty = stockBarcode.Quantity,
                    CreateBy = query.userName,
                    CreateDate = DateTime.Now,
@@ -487,7 +495,7 @@
    ///     - Num: 发料数量(必填,必须大于0)
    ///     - blNo: 补料单号(必填)
    /// </remarks>
    public (bool success, List<MesItemBlDetail> pendingList) SplitBarcode(
    public ProductionPickDto SplitBarcode(
        WarehouseQuery query)
    {
        if (string.IsNullOrEmpty(query.userName))
@@ -543,134 +551,135 @@
            // 拆分条码
            if (totalQty > query.Num)
            {
                var mesItems = db.Queryable<MesItems>()
                    .Where(s => s.Id == stockBarcode.ItemId).First();
                newBarcode = BillNo.ExecuteBarcodeSplit(query.userName, query.barcode, query.Num);
                //var mesItems = db.Queryable<MesItems>()
                //    .Where(s => s.Id == stockBarcode.ItemId).First();

                // 生成新条码号
                newBarcode = BillNo.GetBillNo("TM(条码)", mesItems.ItemNo);
                //// 生成新条码号
                //newBarcode = BillNo.GetBillNo("TM(条码)", mesItems.ItemNo);

                // 写入新条码
                executeCommand += db.Insertable(new MesInvItemBarcodes
                {
                    Guid = Guid.NewGuid(),
                    ItemBarcode = newBarcode,
                    CustNo = stockBarcode.CustomerNo,
                    // ProductCode = stockBarcode.ProductCode, 
                    // ItemBarcode2 = stockBarcode.ItemBarcode2,
                    // ItemCode = stockBarcode.ItemCode,
                    ItemNo = stockBarcode.ItemNo,
                    LotNo = stockBarcode.LotNo,
                    Quantity = query.Num,
                    EpFlag = true,
                    TaskNo = stockBarcode.TaskNo,
                    CreateBy = query.userName,
                    CreateDate = DateTime.Now,
                    LastupdateBy = query.userName,
                    LastupdateDate = DateTime.Now,
                    OldItemBarcode = query.barcode,
                    // Mblnr = stockBarcode.Mblnr,
                    // Zeile = stockBarcode.Zeile,
                    // RohInId = stockBarcode.RohInId,
                    Barcodestatus = false,
                    Oldqty = query.Num as long?,
                    // Unit = stockBarcode.Unit,
                    // WeightUnit = stockBarcode.WeightUnit,
                    Factory = stockBarcode.Factory,
                    Company = stockBarcode.Company,
                    BillNo = stockBarcode.BillNo,
                    BoardStyle = stockBarcode.BoardStyle,
                    // ColorName = stockBarcode.ColorName,
                    WorkNo = stockBarcode.WorkNo,
                    WorkLine = stockBarcode.WorkLine,
                    // MemoBad = stockBarcode.MemoBad,
                    ComeFlg = 5,
                    // Memo = stockBarcode.Memo,
                    SuppId = stockBarcode.SuppId,
                    SuppNo = stockBarcode.SuppNo,
                    InsDate = stockBarcode.IndepDate, // Added InsDate
                    ItemId = stockBarcode.ItemId
                    // ItemUnit = stockBarcode.ItemUnit // Added ItemUnit
                }).IgnoreColumns(true).ExecuteCommand();
                //// 写入新条码
                //executeCommand += db.Insertable(new MesInvItemBarcodes
                //{
                //    Guid = Guid.NewGuid(),
                //    ItemBarcode = newBarcode,
                //    CustNo = stockBarcode.CustomerNo,
                //    // ProductCode = stockBarcode.ProductCode, 
                //    // ItemBarcode2 = stockBarcode.ItemBarcode2,
                //    // ItemCode = stockBarcode.ItemCode,
                //    ItemNo = stockBarcode.ItemNo,
                //    LotNo = stockBarcode.LotNo,
                //    Quantity = query.Num,
                //    EpFlag = true,
                //    TaskNo = stockBarcode.TaskNo,
                //    CreateBy = query.userName,
                //    CreateDate = DateTime.Now,
                //    LastupdateBy = query.userName,
                //    LastupdateDate = DateTime.Now,
                //    OldItemBarcode = query.barcode,
                //    // Mblnr = stockBarcode.Mblnr,
                //    // Zeile = stockBarcode.Zeile,
                //    // RohInId = stockBarcode.RohInId,
                //    Barcodestatus = false,
                //    Oldqty = query.Num as long?,
                //    // Unit = stockBarcode.Unit,
                //    // WeightUnit = stockBarcode.WeightUnit,
                //    Factory = stockBarcode.Factory,
                //    Company = stockBarcode.Company,
                //    BillNo = stockBarcode.BillNo,
                //    BoardStyle = stockBarcode.BoardStyle,
                //    // ColorName = stockBarcode.ColorName,
                //    WorkNo = stockBarcode.WorkNo,
                //    WorkLine = stockBarcode.WorkLine,
                //    // MemoBad = stockBarcode.MemoBad,
                //    ComeFlg = 5,
                //    // Memo = stockBarcode.Memo,
                //    SuppId = stockBarcode.SuppId,
                //    SuppNo = stockBarcode.SuppNo,
                //    InsDate = stockBarcode.IndepDate, // Added InsDate
                //    ItemId = stockBarcode.ItemId
                //    // ItemUnit = stockBarcode.ItemUnit // Added ItemUnit
                //}).IgnoreColumns(true).ExecuteCommand();

                // 更新原条码数量
                executeCommand += db.Updateable<MesInvItemBarcodes>()
                    .SetColumns(it => it.Quantity == it.Quantity - query.Num)
                    .Where(it => it.ItemBarcode == query.barcode)
                    .ExecuteCommand();

                // 更新原条码数量
                executeCommand += db.Updateable<MesInvItemStocks>()
                    .SetColumns(it => it.Quantity == it.Quantity - query.Num)
                    .Where(it => it.ItemBarcode == query.barcode)
                    .ExecuteCommand();

                //// 删除原条码库存记录
                //executeCommand += db.Deleteable<MesInvItemStocks>()
                //// 更新原条码数量
                //executeCommand += db.Updateable<MesInvItemBarcodes>()
                //    .SetColumns(it => it.Quantity == it.Quantity - query.Num)
                //    .Where(it => it.ItemBarcode == query.barcode)
                //    .ExecuteCommand();

                // 插入剩余条码数量的新库存记录
                executeCommand += db.Insertable(new MesInvItemStocks
                {
                    Guid = Guid.NewGuid(),
                    TaskNo = stockBarcode.TaskNo,
                    ItemBarcode = newBarcode,
                    ItemNo = stockBarcode.ItemNo,
                    LotNo = stockBarcode.LotNo,
                    Quantity = query.Num,
                    EpFlag = stockBarcode.EpFlag,
                    CustomerNo = stockBarcode.CustomerNo,
                    ItemWt = stockBarcode.ItemWt,
                    DepotsCode = stockBarcode.DepotsCode,
                    DepotsId = stockBarcode.DepotsId,
                    DepotSectionsCode = stockBarcode.DepotSectionsCode,
                    CheckDate = stockBarcode.CheckDate,
                    ItemType = stockBarcode.ItemType,
                    IndepDate = stockBarcode.IndepDate,
                    Factory = stockBarcode.Factory,
                    Company = stockBarcode.Company,
                    IqcStatus = stockBarcode.IqcStatus,
                    BoardStyle = stockBarcode.BoardStyle,
                    WorkNo = stockBarcode.WorkNo,
                    WorkLine = stockBarcode.WorkLine,
                    SuppNo = stockBarcode.SuppNo,
                    ItemId = stockBarcode.ItemId
                    // UnitId = stockBarcode.ItemUnit
                }).IgnoreColumns(true).ExecuteCommand();
                //// 更新原条码数量
                //executeCommand += db.Updateable<MesInvItemStocks>()
                //    .SetColumns(it => it.Quantity == it.Quantity - query.Num)
                //    .Where(it => it.ItemBarcode == query.barcode)
                //    .ExecuteCommand();

                // 写入新条码的交易记录
                executeCommand += db.Insertable(new MesInvBusiness2
                {
                    Guid = Guid.NewGuid(),
                    Status = 1,
                    BillTypeId = 200, // p_bill_type_id
                    TransactionCode = "209", // p_transaction_no
                    BusinessType = 0,
                    ItemBarcode = newBarcode,
                    ItemNo = stockBarcode.ItemNo,
                    LotNo = stockBarcode.LotNo,
                    EpFlag = true,
                    Quantity = query.Num,
                    FromInvDepotsCode = null,
                    FromInvDepotSectionsCode = null,
                    ToInvDepotsCode = stockBarcode.DepotsCode,
                    ToInvDepotSectionsCode = stockBarcode.DepotSectionsCode,
                    Description = query.Type + "拆分生成",
                    CreateBy = query.userName,
                    CreateDate = DateTime.Now,
                    LastupdateBy = query.userName,
                    LastupdateDate = DateTime.Now,
                    Factory = stockBarcode.Factory,
                    Company = stockBarcode.Company,
                    TaskNo = stockBarcode.TaskNo,
                    BillNo = stockBarcode.BillNo,
                    WorkNo = stockBarcode.WorkNo,
                    WorkLine = stockBarcode.WorkLine,
                    SuppNo = stockBarcode.SuppNo,
                    SuppId = stockBarcode.SuppId,
                    ItemId = stockBarcode.ItemId
                    // CkDepot = stockBarcode.DepotsId
                }).IgnoreColumns(true).ExecuteCommand();
                ////// 删除原条码库存记录
                ////executeCommand += db.Deleteable<MesInvItemStocks>()
                ////    .Where(it => it.ItemBarcode == query.barcode)
                ////    .ExecuteCommand();

                //// 插入剩余条码数量的新库存记录
                //executeCommand += db.Insertable(new MesInvItemStocks
                //{
                //    Guid = Guid.NewGuid(),
                //    TaskNo = stockBarcode.TaskNo,
                //    ItemBarcode = newBarcode,
                //    ItemNo = stockBarcode.ItemNo,
                //    LotNo = stockBarcode.LotNo,
                //    Quantity = query.Num,
                //    EpFlag = stockBarcode.EpFlag,
                //    CustomerNo = stockBarcode.CustomerNo,
                //    ItemWt = stockBarcode.ItemWt,
                //    DepotsCode = stockBarcode.DepotsCode,
                //    DepotsId = stockBarcode.DepotsId,
                //    DepotSectionsCode = stockBarcode.DepotSectionsCode,
                //    CheckDate = stockBarcode.CheckDate,
                //    ItemType = stockBarcode.ItemType,
                //    IndepDate = stockBarcode.IndepDate,
                //    Factory = stockBarcode.Factory,
                //    Company = stockBarcode.Company,
                //    IqcStatus = stockBarcode.IqcStatus,
                //    BoardStyle = stockBarcode.BoardStyle,
                //    WorkNo = stockBarcode.WorkNo,
                //    WorkLine = stockBarcode.WorkLine,
                //    SuppNo = stockBarcode.SuppNo,
                //    ItemId = stockBarcode.ItemId
                //    // UnitId = stockBarcode.ItemUnit
                //}).IgnoreColumns(true).ExecuteCommand();

                //// 写入新条码的交易记录
                //executeCommand += db.Insertable(new MesInvBusiness2
                //{
                //    Guid = Guid.NewGuid(),
                //    Status = 1,
                //    BillTypeId = 200, // p_bill_type_id
                //    TransactionCode = "209", // p_transaction_no
                //    BusinessType = 0,
                //    ItemBarcode = newBarcode,
                //    ItemNo = stockBarcode.ItemNo,
                //    LotNo = stockBarcode.LotNo,
                //    EpFlag = true,
                //    Quantity = query.Num,
                //    FromInvDepotsCode = null,
                //    FromInvDepotSectionsCode = null,
                //    ToInvDepotsCode = stockBarcode.DepotsCode,
                //    ToInvDepotSectionsCode = stockBarcode.DepotSectionsCode,
                //    Description = query.Type + "拆分生成",
                //    CreateBy = query.userName,
                //    CreateDate = DateTime.Now,
                //    LastupdateBy = query.userName,
                //    LastupdateDate = DateTime.Now,
                //    Factory = stockBarcode.Factory,
                //    Company = stockBarcode.Company,
                //    TaskNo = stockBarcode.TaskNo,
                //    BillNo = stockBarcode.BillNo,
                //    WorkNo = stockBarcode.WorkNo,
                //    WorkLine = stockBarcode.WorkLine,
                //    SuppNo = stockBarcode.SuppNo,
                //    SuppId = stockBarcode.SuppId,
                //    ItemId = stockBarcode.ItemId
                //    // CkDepot = stockBarcode.DepotsId
                //}).IgnoreColumns(true).ExecuteCommand();
            }
            else if (totalQty < query.Num)
            {
@@ -847,7 +856,7 @@
            {
                Guid = Guid.NewGuid(),
                ItemOutId = outId,
                ItemBarcode = newBarcode ?? query.barcode,
                ItemBarcode = newBarcode ,
                ItemNo = stockBarcode.ItemNo,
                LotNo = stockBarcode.LotNo,
                Quantity = query.Num,
@@ -882,7 +891,7 @@
                BillTypeId = 200, // p_bill_type_id
                TransactionCode = "209", // p_transaction_no 
                BusinessType = 1,
                ItemBarcode = newBarcode ?? query.barcode,
                ItemBarcode = newBarcode ,
                ItemNo = stockBarcode.ItemNo,
                LotNo = stockBarcode.LotNo,
                EpFlag = true,
@@ -961,7 +970,7 @@

            // 创建 插入日志
            var logService = new LogService();
            var LogMsg = "[PDA]" + query.Type + "。条码【" + query.barcode + "】 出库单号【" + outNo + "】";
            var LogMsg = "[PDA]" + query.Type + "。条码【" + newBarcode + "】 出库单号【" + outNo + "】";
            logService.CreateLog(db, query.userName, womdaa.Guid.ToString(), "WOMDAA", LogMsg, womdaa.Daa001);


@@ -984,9 +993,31 @@
            })
            .ToList();

        return (success, pendingList);
        var dto = new ProductionPickDto
        {
            daa001 = query.billNo,
            barcode = query.barcode,//原条码
            cfBarcode = newBarcode//拆分后条码
        };

        return dto;
    }

    // 获取产线列表
    public dynamic GetLineInfo(dynamic unity, dynamic RequestInfo)
    {
        var orgId = RequestInfo.OrgId;
        var sqlParams = new List<SugarParameter>
        {
            new("@orgId", orgId)
        };

        var sql2 = new StringBuilder("exec prc_pda_itemBl_lineInfo @orgId");

        var LineInfo = Db.Ado.SqlQuery<dynamic>(sql2.ToString(), sqlParams);

        return LineInfo;
    }
    #endregion

    #region 生产超领
@@ -1309,7 +1340,7 @@
                PbillNo = query.billNo,
                ItemId = blDetail.Bld012,
                Unit = blDetail.Bld009,
                DepotId = (int)stockBarcode.DepotsId,
                DepotId = (int)stockBarcode.DepotId,
                Dabid = womdab.Guid,
            }).IgnoreColumns(true).ExecuteCommand();

@@ -1500,7 +1531,7 @@
                    EpFlag = stockBarcode.EpFlag,
                    CustomerNo = stockBarcode.CustomerNo,
                    DepotsCode = stockBarcode.DepotsCode,
                    DepotsId = stockBarcode.DepotsId,
                    DepotsId = stockBarcode.DepotId,
                    DepotSectionsCode = stockBarcode.DepotSectionsCode,
                    CheckDate = stockBarcode.CheckDate,
                    ItemType = stockBarcode.ItemType,
@@ -1712,7 +1743,7 @@
                PbillNo = query.billNo,
                ItemId = blDetail.Bld012,
                Unit = blDetail.Bld009,
                DepotId = (int)stockBarcode.DepotsId,
                DepotId = (int)stockBarcode.DepotId,
                LineK3id = workOrderDetail.Erpid,
                EbelnK3id = workOrderDetail.Eid
            }).IgnoreColumns(true).ExecuteCommand();