修改销售订单接口和销售退货接口

如洲 陈

2025-10-24 3e420f0a8da362be4ab54406d7bc7c1309e074da

 MES.Service/service/Warehouse/MesReturnwareManager.cs

@@ -4,6 +4,7 @@
using MES.Service.DB;
using MES.Service.Modes;
using MES.Service.Dto.webApi;
using System.Data;

namespace MES.Service.service
{
@@ -92,7 +93,12 @@
            var orUpdate = base.Insert(mesReturnware);
            var baOrUpdate = ReturnwareDetailsManager.InsertRange(mesReturnwareDetails);

            if (orUpdate && baOrUpdate) return true;
            if (orUpdate && baOrUpdate)
            {
                // 保存成功后，调用存储过程生成检验单
                GenerateInspectionOrder(db, mesReturnware, mesReturnwareDetails);
                return true;
            }
            throw new NotImplementedException("插入或更新失败");


@@ -117,7 +123,12 @@
            var insertOrUpdate = db.Deleteable<MesReturnwareDetails>().Where(it => mesReturnwareDetail.Any(p => p.ReturnwareNo == it.ReturnNo && p.ReturnwareType == it.ReturnType)).ExecuteCommand() > 0;


            if (update && insertOrUpdate) return true;
            if (update && insertOrUpdate)
            {
                // 更新成功后，调用存储过程生成检验单
                GenerateInspectionOrder(db, mesReturnware, mesReturnwareDetails);
                return true;
            }
            throw new NotImplementedException("更新失败");
        }

@@ -187,27 +198,312 @@

        public bool Delete(YFDelete data)
        {


            return UseTransaction(db =>
            {
                var update = db.Deleteable<MesReturnware>()
                       .Where(it => it.ReturnNo == data.FBillNo &&
                                    it.ReturnType == data.FBillTypeID)
                       .ExecuteCommand() > 0;
                // 在删除前，先获取要删除的退货通知单明细，用于后续重新计算销售订单状态
                var returnDetailsToDelete = db.Queryable<MesReturnwareDetails>()
                    .Where(it => it.ReturnNo == data.FBillNo && it.ReturnType == data.FBillTypeID)
                    .ToList();

                var insertOrUpdate = db.Deleteable<MesReturnwareDetails>()
                // 先删除退货通知单明细
                var detailsDeleted = db.Deleteable<MesReturnwareDetails>()
                      .Where(it => it.ReturnNo == data.FBillNo &&
                                   it.ReturnType == data.FBillTypeID)
                      .ExecuteCommand() > 0;

                // 再删除退货通知单主表
                var mainDeleted = db.Deleteable<MesReturnware>()
                       .Where(it => it.ReturnNo == data.FBillNo &&
                                    it.ReturnType == data.FBillTypeID)
                       .ExecuteCommand() > 0;


                if (update && insertOrUpdate) return 1;
                if (detailsDeleted && mainDeleted)
                {
                    // 删除成功后，调用存储过程删除对应的检验单
                    DeleteInspectionOrder(db, data.FBillTypeID, data.FBillNo);
                    
                    // 重新计算并更新相关销售订单的over状态（与生成时逻辑相同）
                    if (returnDetailsToDelete.Any())
                    {
                        UpdateSalesOrderOverStatusAfterDelete(db, returnDetailsToDelete);
                    }
                    
                    return 1;
                }
                
                throw new NotImplementedException("删除失败");
            }) > 0;
        }

        /// <summary>
        /// 调用存储过程生成检验单
        /// </summary>
        /// <param name="mesReturnware">退货通知单主表</param>
        /// <param name="mesReturnwareDetails">退货通知单明细表</param>
        private void GenerateInspectionOrder(SqlSugarScope db, MesReturnware mesReturnware, List<MesReturnwareDetails> mesReturnwareDetails)
        {
            try
            {
                // 定义输入参数
                var inputParam1 = new SugarParameter("P_RETURN_TYPE", mesReturnware.ReturnType ?? "");
                var inputParam2 = new SugarParameter("P_RETURN_NO", mesReturnware.ReturnNo ?? "");
                
                // 定义输出参数
                var outParam1 = new SugarParameter("P_RESULT", null, true);
                var outParam2 = new SugarParameter("P_MESSAGE", null, true);

                // 使用 SqlSugar 执行存储过程
                db.Ado.ExecuteCommand("BEGIN SP_GEN_RETURN_INSP(:P_RETURN_TYPE,:P_RETURN_NO,:P_RESULT,:P_MESSAGE); END;", 
                    inputParam1, inputParam2, outParam1, outParam2);

                // 获取输出参数的值
                int result = int.Parse((string)outParam1.Value);
                string message = outParam2.Value == DBNull.Value ? string.Empty : (string)outParam2.Value;

                if (result != 1)
                {
                    // 记录警告日志，但不影响退货通知单的保存
                    Console.WriteLine($"生成检验单警告: {message}");
                }
                else
                {
                    Console.WriteLine($"生成检验单成功: {message}");
                    
                    // 生成检验单成功后，更新相关销售订单的over字段
                    UpdateSalesOrderOverStatus(db, mesReturnwareDetails);
                }
            }
            catch (Exception ex)
            {
                // 记录错误日志，但不影响退货通知单的保存
                Console.WriteLine($"生成检验单时发生错误: {ex.Message}");
            }
        }

        /// <summary>
        /// 更新销售订单的over状态
        /// 使用统一逻辑：ORDER_QUANTITY + GIFT_QUANTITY + 退货数量 = DELIVERED_QUANTITY + DELIVERED_GIFT_QUANTITY + XTCH
        /// </summary>
        /// <param name="db">数据库连接</param>
        /// <param name="mesReturnwareDetails">退货通知单明细列表</param>
        private void UpdateSalesOrderOverStatus(SqlSugarScope db, List<MesReturnwareDetails> mesReturnwareDetails)
        {
            try
            {
                // 获取所有需要更新的订单信息（去重）
                var orderUpdates = mesReturnwareDetails
                    .Where(detail => !string.IsNullOrEmpty(detail.OrderNo) && !string.IsNullOrEmpty(detail.OrderType))
                    .Select(detail => new { detail.OrderNo, detail.OrderType })
                    .Distinct()
                    .ToList();

                foreach (var orderInfo in orderUpdates)
                {
                    // 获取该订单的所有明细
                    var orderDetails = db.Queryable<MesSalesOrderDetail>()
                        .Where(it => it.OrderId == orderInfo.OrderNo && it.OrderType == orderInfo.OrderType)
                        .ToList();

                    if (!orderDetails.Any())
                    {
                        Console.WriteLine($"未找到销售订单明细 {orderInfo.OrderType}-{orderInfo.OrderNo}，跳过更新");
                        continue;
                    }

                    // 检查每个订单明细是否完成
                    bool allCompleted = true;

                    foreach (var orderDetail in orderDetails)
                    {
                        // 查找匹配的退货明细：通过ORDER_TYPE, ORDER_ID, LINE_NUMBER, PRODUCT_CODE进行匹配
                        var matchingReturnDetails = mesReturnwareDetails
                            .Where(detail => detail.OrderType == orderDetail.OrderType && 
                                           detail.OrderNo == orderDetail.OrderId && 
                                           detail.OrderNumber == orderDetail.OrderNumber && 
                                           detail.ItemNo == orderDetail.ProductCode)
                            .ToList();

                        // 计算匹配的退货数量
                        var returnQuantity = matchingReturnDetails.Sum(x => (x.Quantity ?? 0) + (x.ActualQuantity ?? 0));

                        // 计算订单总数量：ORDER_QUANTITY + GIFT_QUANTITY + 销售退货通知单中的数量
                        var orderTotal = (orderDetail.OrderQuantity ?? 0) + 
                                       (orderDetail.GiftQuantity ?? 0) + 
                                       returnQuantity;

                        // 计算已交付总数量：DELIVERED_QUANTITY + DELIVERED_GIFT_QUANTITY + XTCH(销退出货数量)
                        var deliveredTotal = (orderDetail.DeliveredQuantity ?? 0) + 
                                           (orderDetail.DeliveredGiftQuantity ?? 0) + 
                                           (orderDetail.Xtch ?? 0);

                        // 如果订单总数量不等于已交付总数量，则该明细未完成
                        if (orderTotal != deliveredTotal)
                        {
                            allCompleted = false;
                            Console.WriteLine($"订单明细 {orderDetail.ProductCode} 未完成: 订单总数量={orderTotal}, 已交付总数量={deliveredTotal} (订单:{orderDetail.OrderQuantity ?? 0} + 赠品:{orderDetail.GiftQuantity ?? 0} + 退货:{returnQuantity} = 已交付:{orderDetail.DeliveredQuantity ?? 0} + 已交付赠品:{orderDetail.DeliveredGiftQuantity ?? 0} + 销退出货:{orderDetail.Xtch ?? 0})");
                            break;
                        }
                    }

                    // 根据是否全部完成设置over值
                    decimal overValue = allCompleted ? 1 : 0;

                    // 更新MES_SALES_ORDER表的over字段
                    var updateCount = db.Updateable<MesSalesOrder>()
                        .SetColumns(it => it.Over == overValue)
                        .Where(it => it.OrderId == orderInfo.OrderNo && it.OrderType == orderInfo.OrderType)
                        .ExecuteCommand();

                    if (updateCount > 0)
                    {
                        Console.WriteLine($"成功更新销售订单 {orderInfo.OrderType}-{orderInfo.OrderNo} 的over状态为{overValue} ({(allCompleted ? "已完成" : "未完成")})");
                    }
                    else
                    {
                        Console.WriteLine($"未找到销售订单 {orderInfo.OrderType}-{orderInfo.OrderNo}，跳过更新");
                    }
                }
            }
            catch (Exception ex)
            {
                // 记录错误日志，但不影响退货通知单的保存
                Console.WriteLine($"更新销售订单over状态时发生错误: {ex.Message}");
            }
        }

        /// <summary>
        /// 删除退货通知单后重新计算并更新相关销售订单的over状态
        /// 使用与生成时相同的逻辑：ORDER_QUANTITY + GIFT_QUANTITY + 退货数量 = DELIVERED_QUANTITY + DELIVERED_GIFT_QUANTITY + XTCH
        /// </summary>
        /// <param name="db">数据库连接</param>
        /// <param name="deletedReturnDetails">已删除的退货通知单明细列表</param>
        private void UpdateSalesOrderOverStatusAfterDelete(SqlSugarScope db, List<MesReturnwareDetails> deletedReturnDetails)
        {
            try
            {
                // 获取所有需要重新计算的订单信息（去重）
                var orderUpdates = deletedReturnDetails
                    .Where(detail => !string.IsNullOrEmpty(detail.OrderNo) && !string.IsNullOrEmpty(detail.OrderType))
                    .Select(detail => new { detail.OrderNo, detail.OrderType })
                    .Distinct()
                    .ToList();

                foreach (var orderInfo in orderUpdates)
                {
                    // 获取该订单的所有明细
                    var orderDetails = db.Queryable<MesSalesOrderDetail>()
                        .Where(it => it.OrderId == orderInfo.OrderNo && it.OrderType == orderInfo.OrderType)
                        .ToList();

                    if (!orderDetails.Any())
                    {
                        Console.WriteLine($"未找到销售订单明细 {orderInfo.OrderType}-{orderInfo.OrderNo}，跳过更新");
                        continue;
                    }

                    // 检查每个订单明细是否完成
                    bool allCompleted = true;

                    foreach (var orderDetail in orderDetails)
                    {
                        // 查找所有匹配的退货明细（包括其他未删除的退货通知单）
                        // 通过ORDER_TYPE, ORDER_ID, LINE_NUMBER, PRODUCT_CODE进行匹配
                        var matchingReturnDetails = db.Queryable<MesReturnwareDetails>()
                            .Where(detail => detail.OrderType == orderDetail.OrderType && 
                                           detail.OrderNo == orderDetail.OrderId && 
                                           detail.OrderNumber == orderDetail.OrderNumber && 
                                           detail.ItemNo == orderDetail.ProductCode)
                            .ToList();

                        // 计算匹配的退货数量
                        var returnQuantity = matchingReturnDetails.Sum(x => (x.Quantity ?? 0) + (x.ActualQuantity ?? 0));

                        // 计算订单总数量：ORDER_QUANTITY + GIFT_QUANTITY + 销售退货通知单中的数量
                        var orderTotal = (orderDetail.OrderQuantity ?? 0) + 
                                       (orderDetail.GiftQuantity ?? 0) + 
                                       returnQuantity;

                        // 计算已交付总数量：DELIVERED_QUANTITY + DELIVERED_GIFT_QUANTITY + XTCH(销退出货数量)
                        var deliveredTotal = (orderDetail.DeliveredQuantity ?? 0) + 
                                           (orderDetail.DeliveredGiftQuantity ?? 0) + 
                                           (orderDetail.Xtch ?? 0);

                        // 如果订单总数量不等于已交付总数量，则该明细未完成
                        if (orderTotal != deliveredTotal)
                        {
                            allCompleted = false;
                            Console.WriteLine($"删除退货通知单后，订单明细 {orderDetail.ProductCode} 未完成: 订单总数量={orderTotal}, 已交付总数量={deliveredTotal} (订单:{orderDetail.OrderQuantity ?? 0} + 赠品:{orderDetail.GiftQuantity ?? 0} + 退货:{returnQuantity} = 已交付:{orderDetail.DeliveredQuantity ?? 0} + 已交付赠品:{orderDetail.DeliveredGiftQuantity ?? 0} + 销退出货:{orderDetail.Xtch ?? 0})");
                            break;
                        }
                    }

                    // 根据是否全部完成设置over值
                    decimal overValue = allCompleted ? 1 : 0;

                    // 更新MES_SALES_ORDER表的over字段
                    var updateCount = db.Updateable<MesSalesOrder>()
                        .SetColumns(it => it.Over == overValue)
                        .Where(it => it.OrderId == orderInfo.OrderNo && it.OrderType == orderInfo.OrderType)
                        .ExecuteCommand();

                    if (updateCount > 0)
                    {
                        Console.WriteLine($"删除退货通知单后，成功更新销售订单 {orderInfo.OrderType}-{orderInfo.OrderNo} 的over状态为{overValue} ({(allCompleted ? "已完成" : "未完成")})");
                    }
                    else
                    {
                        Console.WriteLine($"删除退货通知单后，未找到销售订单 {orderInfo.OrderType}-{orderInfo.OrderNo}，跳过更新");
                    }
                }
            }
            catch (Exception ex)
            {
                // 记录错误日志，但不影响退货通知单的删除
                Console.WriteLine($"删除退货通知单后更新销售订单over状态时发生错误: {ex.Message}");
            }
        }

        /// <summary>
        /// 调用存储过程删除检验单
        /// </summary>
        /// <param name="db">数据库连接</param>
        /// <param name="returnType">退货单别</param>
        /// <param name="returnNo">退货单号</param>
        private void DeleteInspectionOrder(SqlSugarScope db, string returnType, string returnNo)
        {
            try
            {
                // 定义输入参数
                var inputParam1 = new SugarParameter("P_RETURN_TYPE", returnType ?? "");
                var inputParam2 = new SugarParameter("P_RETURN_NO", returnNo ?? "");
                
                // 定义输出参数
                var outParam1 = new SugarParameter("P_RESULT", null, true);
                var outParam2 = new SugarParameter("P_MESSAGE", null, true);

                // 使用 SqlSugar 执行存储过程
                db.Ado.ExecuteCommand("BEGIN SP_DEL_RETURN_INSP(:P_RETURN_TYPE,:P_RETURN_NO,:P_RESULT,:P_MESSAGE); END;", 
                    inputParam1, inputParam2, outParam1, outParam2);

                // 获取输出参数的值
                int result = int.Parse((string)outParam1.Value);
                string message = outParam2.Value == DBNull.Value ? string.Empty : (string)outParam2.Value;

                if (result != 1)
                {
                    // 记录警告日志，但不影响退货通知单的删除
                    Console.WriteLine($"删除检验单警告: {message}");
                }
                else
                {
                    Console.WriteLine($"删除检验单成功: {message}");
                }
            }
            catch (Exception ex)
            {
                // 记录错误日志，但不影响退货通知单的删除
                Console.WriteLine($"删除检验单时发生错误: {ex.Message}");
            }
        }