AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Supply/DeliveryScheduleService.cs

using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 物料交货计划服务
/// </summary>
[ApiDescriptionSettings(Order = 306, Description = "物料交货计划")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class DeliveryScheduleService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly SqlSugarRepository<PolistDeliverySchedule> _rep;
    private readonly UserManager _userManager;
    private readonly NumberRuleService _numberRuleService;

    public DeliveryScheduleService(
        ISqlSugarClient db,
        SqlSugarRepository<PolistDeliverySchedule> rep,
        UserManager userManager,
        NumberRuleService numberRuleService)
    {
        _db = db;
        _rep = rep;
        _userManager = userManager;
        _numberRuleService = numberRuleService;
    }

    [DisplayName("物料交货计划列表")]
    [HttpGet("delivery-schedule/list")]
    public async Task<object> GetList([FromQuery] DeliveryScheduleListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        if (await ShouldUseStdAsync())
        {
            return await GetStdListAsync(input, page, pageSize, offset);
        }

        var pars = new List<SugarParameter>();
        var where = new List<string> { "ss.isactive = 1" };
        if (_userManager.TenantId > 0)
        {
            where.Add("ss.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.PoNumber))
        {
            where.Add("ss.ponumber LIKE @PoNumber");
            pars.Add(new SugarParameter("@PoNumber", $"%{input.PoNumber.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.DsNum))
        {
            where.Add("ss.dsnum LIKE @DsNum");
            pars.Add(new SugarParameter("@DsNum", $"%{input.DsNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("ss.itemnum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Supplier))
        {
            where.Add("(ss.suppliercode LIKE @Supplier OR ss.supplier LIKE @Supplier)");
            pars.Add(new SugarParameter("@Supplier", $"%{input.Supplier.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Status))
        {
            where.Add("ss.status = @Status");
            pars.Add(new SugarParameter("@Status", input.Status.Trim()));
        }

        var orderBy = BuildListOrderBy(input.SortField, input.SortOrder);
        var fromSql = $"""
            FROM srm_polist_ds ss
            LEFT JOIN ItemMaster im ON ss.domain = im.Domain AND ss.itemnum = im.ItemNum
            WHERE {string.Join(" AND ", where)}
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<DeliveryScheduleListRow>(
            $"""
            SELECT
                ss.Id AS Id,
                ss.domain AS Domain,
                ss.icdsid AS IcdsId,
                ss.dsnum AS DsNum,
                ss.status AS Status,
                ss.itemnum AS ItemNum,
                im.Descr AS Descr,
                ss.um AS Um,
                ss.purgroup AS PurGroup,
                ss.suppliercode AS SupplierCode,
                ss.supplier AS Supplier,
                ss.submitdate AS SubmitDate,
                ss.requestdate AS RequestDate,
                ss.needdate AS NeedDate,
                ss.ponumber AS PoNumber,
                ss.poline AS PoLine,
                ss.schedqty AS SchedQty,
                ss.lastsentdate AS LastSentDate,
                ss.lastsentqty AS LastSentQty,
                ss.sentqty AS SentQty,
                ss.restqty AS RestQty
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list };
    }

    private async Task<bool> ShouldUseStdAsync()
    {
        return await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM mdp_std_delivery_schedule LIMIT 1") > 0;
    }

    private async Task<object> GetStdListAsync(DeliveryScheduleListInput input, int page, int pageSize, int offset)
    {
        var pars = new List<SugarParameter>();
        var where = new List<string> { "1=1" };
        if (_userManager.TenantId > 0)
        {
            where.Add("ss.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.PoNumber))
        {
            where.Add("ss.po_no LIKE @PoNumber");
            pars.Add(new SugarParameter("@PoNumber", $"%{input.PoNumber.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.DsNum))
        {
            where.Add("ss.delivery_plan_no LIKE @DsNum");
            pars.Add(new SugarParameter("@DsNum", $"%{input.DsNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("ss.item_code LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Supplier))
        {
            where.Add("(ss.supplier_code LIKE @Supplier OR ss.supplier_name LIKE @Supplier)");
            pars.Add(new SugarParameter("@Supplier", $"%{input.Supplier.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Status))
        {
            where.Add("ss.status = @Status");
            pars.Add(new SugarParameter("@Status", input.Status.Trim()));
        }

        var orderBy = BuildStdListOrderBy(input.SortField, input.SortOrder);
        var fromSql = $"FROM mdp_std_delivery_schedule ss LEFT JOIN mdp_std_item im ON ss.tenant_id=im.tenant_id AND ss.item_code=im.item_code WHERE {string.Join(" AND ", where)}";
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<DeliveryScheduleListRow>(
            $"""
            SELECT
                ss.id AS Id,
                NULL AS Domain,
                NULL AS IcdsId,
                ss.delivery_plan_no AS DsNum,
                ss.status AS Status,
                ss.item_code AS ItemNum,
                im.item_name AS Descr,
                NULL AS Um,
                NULL AS PurGroup,
                ss.supplier_code AS SupplierCode,
                ss.supplier_name AS Supplier,
                ss.submit_date AS SubmitDate,
                ss.request_date AS RequestDate,
                ss.need_date AS NeedDate,
                ss.po_no AS PoNumber,
                CAST(ss.po_line AS SIGNED) AS PoLine,
                ss.schedule_qty AS SchedQty,
                ss.last_sent_date AS LastSentDate,
                ss.sent_qty AS LastSentQty,
                ss.sent_qty AS SentQty,
                ss.rest_qty AS RestQty
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list, source = "mdp_std_delivery_schedule" };
    }

    private static string BuildStdListOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "dsnum" => $"ss.delivery_plan_no {dir}",
            "status" => $"ss.status {dir}",
            "itemnum" => $"ss.item_code {dir}",
            "suppliercode" => $"ss.supplier_code {dir}",
            "supplier" => $"ss.supplier_name {dir}",
            "submitdate" => $"ss.submit_date {dir}",
            "requestdate" => $"ss.request_date {dir}",
            "needdate" => $"ss.need_date {dir}",
            "ponumber" => $"ss.po_no {dir}",
            "poline" => $"ss.po_line {dir}",
            "schedqty" => $"ss.schedule_qty {dir}",
            "sentqty" => $"ss.sent_qty {dir}",
            "restqty" => $"ss.rest_qty {dir}",
            _ => "ss.need_date DESC, ss.delivery_plan_no DESC"
        };
    }

    [DisplayName("生成交货单")]
    [HttpPost("delivery-schedule/generate")]
    public async Task<object> Generate()
    {
        return await GenerateDeliverySchedulesAsync(string.Empty);
    }

    [DisplayName("批量添加交货单")]
    [HttpPost("delivery-schedule/batch-generate")]
    public async Task<object> BatchGenerate([FromBody] DeliveryScheduleGenerateInput input)
    {
        if (string.IsNullOrWhiteSpace(input.PoNumber))
            throw Oops.Oh("采购单号不能为空");

        return await GenerateDeliverySchedulesAsync(input.PoNumber.Trim());
    }

    [DisplayName("发布交货单")]
    [HttpPost("delivery-schedule/publish")]
    public async Task<object> Publish([FromBody] DeliveryScheduleBatchIdsInput input)
    {
        var ids = ParseIds(input.Ids);
        if (!ids.Any()) throw Oops.Oh("请勾选要发布的数据");

        var account = _userManager.Account ?? "system";
        var affected = await _db.Updateable<PolistDeliverySchedule>()
            .SetColumns(x => new PolistDeliverySchedule
            {
                Status = "P",
                SubmitDate = DateTime.Now,
                UpdateUser = account,
                UpdateTime = DateTime.Now
            })
            .Where(x => ids.Contains(x.Id) && x.IsActive == 1 && x.Status == "N")
            .ExecuteCommandAsync();
        return new { affected, message = affected > 0 ? "发布成功" : "无可发布数据" };
    }

    [DisplayName("取消发布交货单")]
    [HttpPost("delivery-schedule/unpublish/{id:long}")]
    public async Task<object> UnPublish(long id)
    {
        var row = await _rep.GetFirstAsync(x => x.Id == id && x.IsActive == 1) ?? throw Oops.Oh("交货单不存在");
        row.Status = "N";
        row.UpdateUser = _userManager.Account;
        row.UpdateTime = DateTime.Now;
        await _rep.UpdateAsync(row);
        return new { message = "取消发布成功" };
    }

    [DisplayName("作废交货单")]
    [HttpPost("delivery-schedule/cancel/{id:long}")]
    public async Task<object> Cancel(long id)
    {
        var row = await _rep.GetFirstAsync(x => x.Id == id && x.IsActive == 1) ?? throw Oops.Oh("交货单不存在");
        row.Status = "C";
        row.UpdateUser = _userManager.Account;
        row.UpdateTime = DateTime.Now;
        await _rep.UpdateAsync(row);
        return new { message = "作废成功" };
    }

    [DisplayName("选择采购单分页")]
    [HttpGet("delivery-schedule/purchase-order/page")]
    public async Task<object> GetPurchaseOrderPage([FromQuery] PurchaseOrderSelectInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var pars = new List<SugarParameter>();
        var where = new List<string>
        {
            "IFNULL(m.Status,'') <> 'C'",
            "IFNULL(d.Status,'') <> 'C'",
            "(IFNULL(ds.dsnum, '') = '' OR (ds.status = 'C' AND d.QtyOrded - d.RctQty > 0))"
        };
        if (!string.IsNullOrWhiteSpace(input.PurOrd))
        {
            where.Add("m.PurOrd LIKE @PurOrd");
            pars.Add(new SugarParameter("@PurOrd", $"%{input.PurOrd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNum))
        {
            where.Add("d.ItemNum LIKE @ItemNum");
            pars.Add(new SugarParameter("@ItemNum", $"%{input.ItemNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Supp))
        {
            where.Add("(m.Supp LIKE @Supp OR s.Name LIKE @Supp)");
            pars.Add(new SugarParameter("@Supp", $"%{input.Supp.Trim()}%"));
        }

        var orderBy = BuildPurchaseOrderOrderBy(input.SortField, input.SortOrder);
        var fromSql = $"""
            FROM PurOrdMaster m
            INNER JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
            LEFT JOIN srm_polist_ds ds ON d.PurOrd = ds.ponumber AND d.Line = ds.poline AND ds.isactive = 1
            LEFT JOIN ConsigneeAddressMaster s ON m.Domain = s.Domain AND m.Supp = s.Address AND s.Typed = 'Supp'
            WHERE {string.Join(" AND ", where)}
            """;
        var distinctSql = $"SELECT DISTINCT m.PurOrd, d.Line, d.ItemNum {fromSql}";

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM ({distinctSql}) t", pars);
        var list = await _db.Ado.SqlQueryAsync<PurchaseOrderSelectRow>(
            $"""
            SELECT DISTINCT
                m.PurOrd AS PurOrd,
                d.Line AS Line,
                d.ItemNum AS ItemNum,
                d.UM AS Um,
                m.Supp AS Supp,
                s.Name AS Name,
                d.DueDate AS DueDate,
                d.QtyOrded AS QtyOrded,
                m.Buyer AS Buyer
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

        return new { total, page, pageSize, list };
    }

    private async Task<DeliveryScheduleGenerateResult> GenerateDeliverySchedulesAsync(string poNumber)
    {
        var account = _userManager.Account ?? "system";
        var pars = new List<SugarParameter>();
        var where = new List<string>
        {
            "IFNULL(m.Status,'') <> 'C'",
            "IFNULL(d.Status,'') <> 'C'",
            "(IFNULL(d.QtyOrded,0) - IFNULL(d.RctQty,0)) > 0",
            """
            NOT EXISTS (
                SELECT 1
                FROM srm_polist_ds ds
                WHERE ds.ponumber = d.PurOrd
                  AND ds.poline = d.Line
                  AND ds.isactive = 1
                  AND IFNULL(ds.status,'') <> 'C'
            )
            """
        };
        if (_userManager.TenantId > 0)
        {
            where.Add("m.tenant_id = @TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(poNumber))
        {
            where.Add("m.PurOrd = @PoNumber");
            pars.Add(new SugarParameter("@PoNumber", poNumber.Trim()));
        }

        var candidates = await _db.Ado.SqlQueryAsync<DeliveryScheduleCandidateRow>(
            $"""
            SELECT
                IFNULL(TRIM(m.Domain),'') AS Domain,
                d.RecID AS IcdsId,
                IFNULL(TRIM(d.PurOrd),'') AS PoNumber,
                IFNULL(d.Line,0) AS PoLine,
                IFNULL(TRIM(d.ItemNum),'') AS ItemNum,
                IFNULL(TRIM(d.UM),'') AS Um,
                IFNULL(TRIM(m.Buyer),'') AS PurGroup,
                IFNULL(TRIM(m.Supp),'') AS SupplierCode,
                IFNULL(TRIM(s.Name),'') AS Supplier,
                d.DueDate AS NeedDate,
                (IFNULL(d.QtyOrded,0) - IFNULL(d.RctQty,0)) AS RestQty,
                m.tenant_id AS TenantId
            FROM PurOrdMaster m
            INNER JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
            LEFT JOIN ConsigneeAddressMaster s ON m.Domain = s.Domain AND m.Supp = s.Address AND s.Typed = 'Supp'
            WHERE {string.Join(" AND ", where)}
            ORDER BY m.PurOrd, d.Line
            """,
            pars);
        if (candidates.Count == 0)
        {
            return new DeliveryScheduleGenerateResult
            {
                CandidateCount = 0,
                CreatedCount = 0,
                Message = string.IsNullOrWhiteSpace(poNumber)
                    ? "无可生成交货单的采购行"
                    : "当前采购单无可生成交货单的采购行"
            };
        }

        try
        {
            _db.Ado.BeginTran();
            var dsNumbersByKey = new Dictionary<string, string>();
            foreach (var group in candidates.GroupBy(x => x.Domain ?? string.Empty))
            {
                var groupRows = group.ToList();
                var dsNumbers = await _numberRuleService.NextBatchInCurrentTransactionAsync("M8", group.Key, groupRows.Count, account);
                if (dsNumbers.Count < groupRows.Count || dsNumbers.Any(string.IsNullOrWhiteSpace))
                    throw Oops.Oh($"当前交货计划号生成失败，请检查交货计划编号规则维护。Domain={group.Key}");

                for (var index = 0; index < groupRows.Count; index++)
                {
                    dsNumbersByKey[BuildCandidateKey(groupRows[index])] = dsNumbers[index].Trim();
                }
            }

            var now = DateTime.Now;
            var schedules = candidates.Select(row => new PolistDeliverySchedule
            {
                Id = YitIdHelper.NextId(),
                Domain = row.Domain ?? string.Empty,
                IcdsId = row.IcdsId,
                DsNum = dsNumbersByKey[BuildCandidateKey(row)],
                Status = "N",
                ItemNum = row.ItemNum ?? string.Empty,
                Um = row.Um ?? string.Empty,
                PurGroup = row.PurGroup ?? string.Empty,
                SupplierCode = row.SupplierCode ?? string.Empty,
                Supplier = row.Supplier ?? string.Empty,
                RequestDate = now,
                NeedDate = row.NeedDate,
                PoNumber = row.PoNumber ?? string.Empty,
                PoLine = row.PoLine,
                SchedQty = row.RestQty,
                LastSentQty = 0,
                SentQty = 0,
                RestQty = row.RestQty,
                CreateUser = account,
                UpdateUser = account,
                CreateTime = now,
                UpdateTime = now,
                Remarks = "由系统按采购行自动生成",
                IsActive = 1,
                ReturnQty = 0,
                TenantId = _userManager.TenantId > 0 ? _userManager.TenantId : row.TenantId
            }).ToList();

            var createdCount = await _db.Insertable(schedules).ExecuteCommandAsync();
            await UpsertDeliveryScheduleMdpAsync(schedules, now);
            _db.Ado.CommitTran();
            return new DeliveryScheduleGenerateResult
            {
                CandidateCount = candidates.Count,
                CreatedCount = createdCount,
                DsNums = schedules.Select(x => x.DsNum).Take(20).ToList(),
                Message = createdCount > 0
                    ? $"生成交货单成功，共生成 {createdCount} 条"
                    : "未生成交货单，请检查采购行是否已存在有效交货计划"
            };
        }
        catch
        {
            _db.Ado.RollbackTran();
            throw;
        }
    }

    private async Task UpsertDeliveryScheduleMdpAsync(List<PolistDeliverySchedule> schedules, DateTime now)
    {
        if (schedules.Count == 0) return;

        var batchId = $"S3_DELIVERY_{now:yyyyMMddHHmmss}";
        foreach (var schedule in schedules)
        {
            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO mdp_std_delivery_schedule
                (tenant_id, source_system, delivery_plan_no, po_no, po_line, item_code, supplier_code, supplier_name, schedule_qty, sent_qty, rest_qty, return_qty, request_date, need_date, submit_date, last_sent_date, status, source_biz_key, sync_batch_id, sync_time)
                VALUES
                (@TenantId, 'AIDOP', @DsNum, @PoNumber, @PoLine, @ItemNum, @SupplierCode, @Supplier, @SchedQty, @SentQty, @RestQty, @ReturnQty, @RequestDate, @NeedDate, @SubmitDate, @LastSentDate, @Status, @DsNum, @BatchId, @Now)
                ON DUPLICATE KEY UPDATE
                    po_no=VALUES(po_no),
                    po_line=VALUES(po_line),
                    item_code=VALUES(item_code),
                    supplier_code=VALUES(supplier_code),
                    supplier_name=VALUES(supplier_name),
                    schedule_qty=VALUES(schedule_qty),
                    sent_qty=VALUES(sent_qty),
                    rest_qty=VALUES(rest_qty),
                    return_qty=VALUES(return_qty),
                    request_date=VALUES(request_date),
                    need_date=VALUES(need_date),
                    submit_date=VALUES(submit_date),
                    last_sent_date=VALUES(last_sent_date),
                    status=VALUES(status),
                    sync_batch_id=VALUES(sync_batch_id),
                    sync_time=VALUES(sync_time),
                    update_time=CURRENT_TIMESTAMP
                """,
                new SugarParameter("@TenantId", schedule.TenantId ?? 0),
                new SugarParameter("@DsNum", schedule.DsNum),
                new SugarParameter("@PoNumber", schedule.PoNumber),
                new SugarParameter("@PoLine", schedule.PoLine.ToString()),
                new SugarParameter("@ItemNum", schedule.ItemNum),
                new SugarParameter("@SupplierCode", schedule.SupplierCode),
                new SugarParameter("@Supplier", schedule.Supplier),
                new SugarParameter("@SchedQty", schedule.SchedQty),
                new SugarParameter("@SentQty", schedule.SentQty),
                new SugarParameter("@RestQty", schedule.RestQty),
                new SugarParameter("@ReturnQty", schedule.ReturnQty),
                new SugarParameter("@RequestDate", schedule.RequestDate),
                new SugarParameter("@NeedDate", schedule.NeedDate),
                new SugarParameter("@SubmitDate", schedule.SubmitDate),
                new SugarParameter("@LastSentDate", schedule.LastSentDate),
                new SugarParameter("@Status", schedule.Status),
                new SugarParameter("@BatchId", batchId),
                new SugarParameter("@Now", now));

            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO dwd_supplier_delivery
                (tenant_id, stat_date, po_no, po_line, po_type, supplier_code, supplier_name, item_code, item_name, order_qty, schedule_qty, delivery_qty, receipt_qty, return_qty, remaining_qty, due_date, need_date, last_delivery_date, delivery_status, source_system, sync_batch_id, calc_time)
                SELECT
                    @TenantId,
                    @StatDate,
                    IFNULL(m.PurOrd, @PoNumber),
                    CAST(IFNULL(d.Line, @PoLineInt) AS CHAR),
                    IFNULL(m.Potype, ''),
                    IFNULL(m.Supp, @SupplierCode),
                    @Supplier,
                    IFNULL(d.ItemNum, @ItemNum),
                    IFNULL(i.Descr, ''),
                    IFNULL(d.QtyOrded, @SchedQty),
                    @SchedQty,
                    @SentQty,
                    IFNULL(d.QtyReceived, IFNULL(d.RctQty, 0)),
                    @ReturnQty,
                    @RestQty,
                    d.DueDate,
                    @NeedDate,
                    @LastSentDate,
                    CASE
                        WHEN IFNULL(@Status, '') = 'C' THEN 'CANCELLED'
                        WHEN IFNULL(@RestQty, 0) <= 0 THEN 'CLOSED'
                        WHEN IFNULL(@SentQty, 0) > 0 THEN 'PARTIAL'
                        ELSE 'OPEN'
                    END,
                    'AIDOP',
                    @BatchId,
                    @Now
                FROM PurOrdMaster m
                JOIN PurOrdDetail d ON m.RecID = d.PurOrdRecID
                LEFT JOIN ItemMaster i ON d.ItemNum = i.ItemNum
                WHERE m.PurOrd = @PoNumber AND d.Line = @PoLineInt
                LIMIT 1
                ON DUPLICATE KEY UPDATE
                    supplier_code=VALUES(supplier_code),
                    supplier_name=VALUES(supplier_name),
                    item_code=VALUES(item_code),
                    item_name=VALUES(item_name),
                    order_qty=VALUES(order_qty),
                    schedule_qty=VALUES(schedule_qty),
                    delivery_qty=VALUES(delivery_qty),
                    receipt_qty=VALUES(receipt_qty),
                    return_qty=VALUES(return_qty),
                    remaining_qty=VALUES(remaining_qty),
                    due_date=VALUES(due_date),
                    need_date=VALUES(need_date),
                    last_delivery_date=VALUES(last_delivery_date),
                    delivery_status=VALUES(delivery_status),
                    sync_batch_id=VALUES(sync_batch_id),
                    calc_time=VALUES(calc_time),
                    update_time=CURRENT_TIMESTAMP
                """,
                new SugarParameter("@TenantId", schedule.TenantId ?? 0),
                new SugarParameter("@StatDate", now.Date),
                new SugarParameter("@PoNumber", schedule.PoNumber),
                new SugarParameter("@PoLineInt", schedule.PoLine),
                new SugarParameter("@SupplierCode", schedule.SupplierCode),
                new SugarParameter("@Supplier", schedule.Supplier),
                new SugarParameter("@ItemNum", schedule.ItemNum),
                new SugarParameter("@SchedQty", schedule.SchedQty),
                new SugarParameter("@SentQty", schedule.SentQty),
                new SugarParameter("@ReturnQty", schedule.ReturnQty),
                new SugarParameter("@RestQty", schedule.RestQty),
                new SugarParameter("@NeedDate", schedule.NeedDate),
                new SugarParameter("@LastSentDate", schedule.LastSentDate),
                new SugarParameter("@Status", schedule.Status),
                new SugarParameter("@BatchId", batchId),
                new SugarParameter("@Now", now));
        }
    }

    private static List<long> ParseIds(string ids)
    {
        return ids.Split(',', StringSplitOptions.RemoveEmptyEntries)
            .Select(x => long.TryParse(x.Trim(), out var id) ? id : 0)
            .Where(x => x > 0)
            .Distinct()
            .ToList();
    }

    private static string BuildCandidateKey(DeliveryScheduleCandidateRow row)
    {
        return $"{row.Domain}|{row.PoNumber}|{row.PoLine}|{row.IcdsId}";
    }

    private static string BuildListOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "dsnum" => $"ss.dsnum {dir}",
            "status" => $"ss.status {dir}",
            "itemnum" => $"ss.itemnum {dir}",
            "requestdate" => $"ss.requestdate {dir}",
            "needdate" => $"ss.needdate {dir}",
            "schedqty" => $"ss.schedqty {dir}",
            "sentqty" => $"ss.sentqty {dir}",
            "restqty" => $"ss.restqty {dir}",
            "submitdate" => $"ss.submitdate {dir}",
            "ponumber" => $"ss.ponumber {dir}",
            _ => "ss.Id DESC"
        };
    }

    private static string BuildPurchaseOrderOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "purord" => $"m.PurOrd {dir}",
            "line" => $"d.Line {dir}",
            "itemnum" => $"d.ItemNum {dir}",
            "supp" => $"m.Supp {dir}",
            "name" => $"s.Name {dir}",
            "duedate" => $"d.DueDate {dir}",
            "qtyorded" => $"d.QtyOrded {dir}",
            "buyer" => $"m.Buyer {dir}",
            _ => "m.PurOrd DESC, d.Line ASC"
        };
    }

    private sealed class DeliveryScheduleListRow
    {
        public long Id { get; set; }
        public string? Domain { get; set; }
        public long? IcdsId { get; set; }
        public string? DsNum { get; set; }
        public string? Status { get; set; }
        public string? ItemNum { get; set; }
        public string? Descr { get; set; }
        public string? Um { get; set; }
        public string? PurGroup { get; set; }
        public string? SupplierCode { get; set; }
        public string? Supplier { get; set; }
        public DateTime? SubmitDate { get; set; }
        public DateTime? RequestDate { get; set; }
        public DateTime? NeedDate { get; set; }
        public string? PoNumber { get; set; }
        public int? PoLine { get; set; }
        public decimal? SchedQty { get; set; }
        public DateTime? LastSentDate { get; set; }
        public decimal? LastSentQty { get; set; }
        public decimal? SentQty { get; set; }
        public decimal? RestQty { get; set; }
    }

    private sealed class PurchaseOrderSelectRow
    {
        public string? PurOrd { get; set; }
        public int? Line { get; set; }
        public string? ItemNum { get; set; }
        public string? Um { get; set; }
        public string? Supp { get; set; }
        public string? Name { get; set; }
        public DateTime? DueDate { get; set; }
        public decimal? QtyOrded { get; set; }
        public string? Buyer { get; set; }
    }

    private sealed class DeliveryScheduleGenerateResult
    {
        public int CandidateCount { get; set; }
        public int CreatedCount { get; set; }
        public List<string> DsNums { get; set; } = new();
        public string Message { get; set; } = string.Empty;
    }

    private sealed class DeliveryScheduleCandidateRow
    {
        public string? Domain { get; set; }
        public long IcdsId { get; set; }
        public string? PoNumber { get; set; }
        public int PoLine { get; set; }
        public string? ItemNum { get; set; }
        public string? Um { get; set; }
        public string? PurGroup { get; set; }
        public string? SupplierCode { get; set; }
        public string? Supplier { get; set; }
        public DateTime? NeedDate { get; set; }
        public decimal RestQty { get; set; }
        public long? TenantId { get; set; }
    }
}