AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/ProcurementExecution/SupplierDeliveryManagementService.cs

using Admin.NET.Plugin.AiDOP.ProcurementExecution.Dto;

namespace Admin.NET.Plugin.AiDOP.ProcurementExecution;

/// <summary>
/// S4 供应商交货管理
/// </summary>
[ApiDescriptionSettings(Order = 320, Description = "S4供应商交货管理")]
[Route("api/ProcurementExecution")]
[AllowAnonymous]
[NonUnify]
public class SupplierDeliveryManagementService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

    public SupplierDeliveryManagementService(ISqlSugarClient db, UserManager userManager)
    {
        _db = db;
        _userManager = userManager;
    }

    [DisplayName("供应商交货管理列表")]
    [HttpGet("supplier-delivery/list")]
    public async Task<object> GetList([FromQuery] SupplierDeliveryListInput input)
    {
        var pars = new List<SugarParameter>();
        var conditions = new List<string>();
        if (!string.IsNullOrWhiteSpace(input.Cgdd))
        {
            conditions.Add("v.cgdd LIKE @cgdd");
            pars.Add(new SugarParameter("@cgdd", $"%{input.Cgdd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.DsNum))
        {
            conditions.Add("v.dsnum LIKE @dsnum");
            pars.Add(new SugarParameter("@dsnum", $"%{input.DsNum.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Wlbm))
        {
            conditions.Add("v.wlbm LIKE @wlbm");
            pars.Add(new SugarParameter("@wlbm", $"%{input.Wlbm.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Gys))
        {
            conditions.Add("(v.gysdm LIKE @gys OR v.gysmc LIKE @gys)");
            pars.Add(new SugarParameter("@gys", $"%{input.Gys.Trim()}%"));
        }

        var where = conditions.Count > 0 ? $" WHERE {string.Join(" AND ", conditions)} " : string.Empty;
        var orderBy = BuildOrderBy(input.SortField, input.SortOrder);
        var offset = (input.Page - 1) * input.PageSize;
        var wrapped = $"{BuildBaseSql()} {where}";

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(*) FROM ({wrapped}) t", pars);
        var list = await _db.Ado.SqlQueryAsync<SupplierDeliveryListRow>(
            $"SELECT * FROM ({wrapped}) t {orderBy} LIMIT {input.PageSize} OFFSET {offset}", pars);

        return new { total, page = input.Page, pageSize = input.PageSize, list };
    }

    [DisplayName("供应商交货管理发布")]
    [HttpPost("supplier-delivery/publish")]
    public async Task<object> Publish([FromBody] SupplierDeliveryBatchInput input)
    {
        var userId = _userManager.UserId.ToString();
        var userName = _userManager.Account ?? "system";
        var sql = """
            INSERT INTO `scm_jhjh_jq`
            (
              `glid1`,
              `wlbm`, `wlms`, `wlgg`, `cgdd`,
              `jhdsl`, `wjhsl`, `jhd`, `yjjhrq`, `jqhf`,
              `type`, `flag`, `scrq`, `scrid`, `scrxm`,
              `gysdm`, `gysmc`,
              `hfrid`, `hfrxm`, `hfsj`,
              `qhdj`, `ddhh`, `dw`, `bzsl`, `ly`
            )
            SELECT
              ds.id AS glid1,
              ds.itemnum AS wlbm,
              im.Descr AS wlms,
              im.Descr1 AS wlgg,
              ds.ponumber AS cgdd,
              ds.schedqty AS jhdsl,
              a.wjhsl AS wjhsl,
              ds.dsnum AS jhd,
              a.yjjhrq AS yjjhrq,
              DATE_FORMAT(DATE(a.yjjhrq), '%Y-%m-%d') AS jqhf,
              a.`type` AS `type`,
              2 AS flag,
              a.scrq AS scrq,
              a.scrid AS scrid,
              a.scrxm AS scrxm,
              a.gysdm AS gysdm,
              a.gysmc AS gysmc,
              @hfrid AS hfrid,
              @hfrxm AS hfrxm,
              DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS hfsj,
              a.qhdj AS qhdj,
              a.ddhh AS ddhh,
              a.dw AS dw,
              a.bzsl AS bzsl,
              a.ly AS ly
            FROM srm_polist_ds ds
            LEFT JOIN ItemMaster im
              ON ds.itemnum = im.ItemNum
            LEFT JOIN vscm_jhjh a
              ON a.cgdd = ds.ponumber
             AND a.ddhh = ds.poline
            WHERE FIND_IN_SET(CAST(ds.id AS CHAR(50)), REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
              AND NOT EXISTS (
                SELECT 1 FROM scm_jhjh_jq x
                WHERE x.glid1 = ds.id
                  AND FIND_IN_SET(CAST(x.glid1 AS CHAR(50)), REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
              );
            """;
        await _db.Ado.ExecuteCommandAsync(sql, new List<SugarParameter>
        {
            new("@ids", input.Ids),
            new("@hfrid", userId),
            new("@hfrxm", userName)
        });
        return new { message = "发布成功" };
    }

    [DisplayName("供应商交货管理按计划日期回复")]
    [HttpPost("supplier-delivery/reply-by-plan-date")]
    public async Task<object> ReplyByPlanDate([FromBody] SupplierDeliveryBatchInput input)
    {
        if (string.IsNullOrWhiteSpace(input.Ids))
            throw Oops.Oh("缺少勾选行ID（ids）");

        var userId = _userManager.UserId.ToString();
        var userName = _userManager.Account ?? "system";

        // 说明：
        // - 需求来自 SQLServer 写法：STRING_SPLIT(@ids, ',')
        // - MySQL 使用 FIND_IN_SET 判断逗号分隔集合
        var sql = """
            INSERT INTO `scm_jhjh_jq`
            (
              `glid`, `wlbm`, `wlms`, `wlgg`, `cgdd`, `jhdsl`, `wjhsl`,
              `jhd`, `yjjhrq`, `jqhf`, `type`, `flag`,
              `scrq`, `scrid`, `scrxm`,
              `gysdm`, `gysmc`,
              `hfrid`, `hfrxm`, `hfsj`,
              `qhdj`, `ddhh`, `dw`, `bzsl`, `ly`
            )
            SELECT
              x.id AS glid,
              x.wlbm,
              x.wlms,
              x.wlgg,
              x.cgdd,
              x.jhdsl,
              x.wjhsl,
              x.jhd,
              x.yjjhrq,
              x.jqhf,
              x.type,
              0 AS flag,
              x.scrq,
              x.scrid,
              x.scrxm,
              x.gysdm,
              x.gysmc,
              @userid AS hfrid,
              @username AS hfrxm,
              DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS hfsj,
              x.qhdj,
              x.ddhh,
              x.dw,
              x.bzsl,
              x.ly
            FROM (
              SELECT
                CAST(IFNULL(ds.id, a.id) AS CHAR(50)) AS id,
                a.wlbm,
                a.wlms,
                a.wlgg,
                a.cgdd,
                a.jhdsl,
                a.wjhsl,
                a.jhd,
                a.yjjhrq,
                DATE_FORMAT(DATE(a.yjjhrq), '%Y-%m-%d') AS jqhf,
                a.type,
                a.scrq,
                a.scrid,
                a.scrxm,
                a.gysdm,
                a.gysmc,
                a.qhdj,
                a.ddhh,
                a.dw,
                a.bzsl,
                a.ly
              FROM vscm_jhjh a
              LEFT JOIN (
                SELECT * FROM srm_polist_ds
                WHERE status = 'P' AND isactive = 1 AND restQTY > 0
              ) ds
                ON a.cgdd = ds.ponumber AND a.ddhh = ds.poline
            ) x
            WHERE FIND_IN_SET(x.id, REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
              AND NOT EXISTS (
                SELECT 1 FROM scm_jhjh_jq t
                WHERE t.flag = 0 AND t.glid = x.id
              );
            """;

        await _db.Ado.ExecuteCommandAsync(sql, new List<SugarParameter>
        {
            new("@ids", input.Ids),
            new("@userid", userId),
            new("@username", userName),
        });

        return new { message = "按计划日期回复成功" };
    }

    [DisplayName("供应商交货管理回复交期")]
    [HttpPost("supplier-delivery/reply-due-date")]
    public async Task<object> ReplyDueDate([FromBody] SupplierDeliveryReplyDueDateInput input)
    {
        if (string.IsNullOrWhiteSpace(input.Ids))
            throw Oops.Oh("缺少勾选行ID（ids）");
        if (string.IsNullOrWhiteSpace(input.Jqhf))
            throw Oops.Oh("缺少交期回复日期（jqhf）");

        var userId = _userManager.UserId.ToString();
        var userName = _userManager.Account ?? "system";

        // 需求 SQLServer 写法：STRING_SPLIT(@ids, ',')
        // MySQL 使用 FIND_IN_SET 判断逗号分隔集合
        var sql = """
            INSERT INTO `scm_jhjh_jq`
            (
              `glid`, `wlbm`, `wlms`, `wlgg`, `cgdd`,
              `jhdsl`, `wjhsl`, `jhd`, `yjjhrq`, `jqhf`,
              `type`, `flag`,
              `scrq`, `scrid`, `scrxm`,
              `gysdm`, `gysmc`,
              `hfrid`, `hfrxm`, `hfsj`,
              `qhdj`, `ddhh`, `dw`, `bzsl`, `ly`
            )
            SELECT
              x.id AS glid,
              x.wlbm,
              x.wlms,
              x.wlgg,
              x.cgdd,
              x.jhdsl,
              x.wjhsl,
              x.jhd,
              x.yjjhrq,
              @jqhf AS jqhf,
              x.type,
              0 AS flag,
              x.scrq,
              x.scrid,
              x.scrxm,
              x.gysdm,
              x.gysmc,
              @userid AS hfrid,
              @username AS hfrxm,
              DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS hfsj,
              x.qhdj,
              x.ddhh,
              x.dw,
              x.bzsl,
              x.ly
            FROM (
              SELECT
                CAST(IFNULL(ds.id, a.id) AS CHAR(50)) AS id,
                a.wlbm,
                a.wlms,
                a.wlgg,
                a.cgdd,
                a.jhdsl,
                a.wjhsl,
                a.jhd,
                a.yjjhrq,
                a.type,
                a.scrq,
                a.scrid,
                a.scrxm,
                a.gysdm,
                a.gysmc,
                a.qhdj,
                a.ddhh,
                a.dw,
                a.bzsl,
                a.ly
              FROM vscm_jhjh a
              LEFT JOIN (
                SELECT * FROM srm_polist_ds
                WHERE status='P' AND isactive=1 AND restQTY>0
              ) ds ON a.cgdd = ds.ponumber AND a.ddhh = ds.poline
            ) x
            WHERE FIND_IN_SET(x.id, REPLACE(IFNULL(@ids, ''), ' ', '')) > 0;
            """;

        await _db.Ado.ExecuteCommandAsync(sql, new List<SugarParameter>
        {
            new("@ids", input.Ids),
            new("@jqhf", input.Jqhf.Trim()),
            new("@userid", userId),
            new("@username", userName),
        });

        return new { message = "回复交期成功" };
    }

    [DisplayName("交货单关闭")]
    [HttpPost("supplier-delivery/close")]
    public async Task<object> CloseDelivery([FromBody] DeliveryCloseInput input)
    {
        await _db.Ado.ExecuteCommandAsync(
            "UPDATE srm_polist_ds SET status='C' WHERE DSNum=@jhdbh",
            new List<SugarParameter> { new("@jhdbh", input.DsNum) });
        return new { message = "交货单关闭成功" };
    }

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var map = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase)
        {
            ["sffb"] = "t.sffb",
            ["wlbm"] = "t.wlbm",
            ["wlms"] = "t.wlms",
            ["buyer"] = "t.buyer",
            ["gysdm"] = "t.gysdm",
            ["gysmc"] = "t.gysmc",
            ["cgdd"] = "t.cgdd",
            ["ddhh"] = "t.ddhh",
            ["jhdsl"] = "t.jhdsl",
            ["dfhsl"] = "t.dfhsl",
            ["dsnum"] = "t.dsnum",
            ["requestdate"] = "t.requestdate",
            ["schedqty"] = "t.schedqty",
            ["jqhfnew"] = "t.jqhfnew",
            ["ztsl"] = "t.ztsl",
            ["zsl1"] = "t.zsl1",
            ["rksl"] = "t.rksl",
            ["bhgsl"] = "t.bhgsl",
            ["thsl"] = "t.thsl",
            ["bz"] = "t.bz"
        };
        var field = map.TryGetValue(sortField ?? string.Empty, out var sqlField) ? sqlField : "t.requestdate";
        var order = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return $" ORDER BY {field} {order} ";
    }

    private static string BuildBaseSql() => """
        SELECT
            v.*,
            CASE
                WHEN v.jhdsl - IFNULL(v.zfhl, 0) + IFNULL(v.thsl, 0) > v.wjhsl THEN v.wjhsl
                ELSE v.jhdsl - IFNULL(v.zfhl, 0) + IFNULL(v.thsl, 0)
            END AS dfhsl,
            CASE
                WHEN IFNULL(v.zsl18, 0) - IFNULL(v.thsl, 0) <= IFNULL(v.zshl, 0) THEN 0
                ELSE IFNULL(v.zsl18, 0) - IFNULL(v.thsl, 0) - IFNULL(v.zshl, 0)
            END AS ztsl,
            IFNULL(v.zsl, 0) AS zsl1,
            v.thsl AS bhgsl,
            CONCAT(
                CASE
                    WHEN v.sfyqnew = 'wait' OR v.sfyqnew = 'NO' THEN 'jqhfnew:yellow'
                    WHEN v.sfyqnew = 'refuse' THEN 'jqhfnew:red'
                    WHEN v.sfyqnew = 'OK' THEN 'jqhfnew:#99FF00'
                    ELSE ''
                END,
                v.sffbgrdnew
            ) AS background,
            CASE
                WHEN v.jhdsl - IFNULL(v.zfhl, 0) + IFNULL(v.thsl, 0) <= 0 THEN '完成'
                ELSE '待交'
            END AS yjzt
        FROM
        (
            SELECT
                p.PurOrd,
                p.Line,
                p.ItemNum,
                s.fhsl - IFNULL(dor.thsl, 0) AS zfhl,
                p.ReceiptQty + p.RctQty AS zshl,
                i.zjsl AS zsl,
                CASE WHEN p.PurOrd LIKE 'DO%' THEN p.RctQty ELSE p.RctQty - IFNULL(i.zjsl, 0) END AS rksl,
                CASE
                    WHEN p.PurOrd LIKE 'DO%' THEN IF(p.QtyReturned > 0, p.QtyReturned, dor.thsl)
                    ELSE IF(p.QtyReturned > 0, p.QtyReturned, p.QtyReturned + dor.thsl)
                END AS thsl,
                s.fhsl AS zsl18,
                sh.jhdyj,
                jy.jhdzj,
                IFNULL(ds.SchedQty, 0) - IFNULL(sh.jhdyj, 0) AS jhddj,
                p.QtyOrded - p.RctQty - p.ReceiptQty + p.QtyReturned AS wjhsl,
                p.QtyOrded AS jhdsl,
                ds.SchedQty,
                ds.DSNum,
                CASE WHEN b2.id IS NULL THEN 'X' ELSE b2.hfsj END AS sffb,
                p.ItemNum AS wlbm,
                im.Descr AS wlms,
                ds.supplier AS gysmc,
                p.PurOrd AS cgdd,
                p.Line AS ddhh,
                pm.Buyer AS buyer,
                IFNULL(ds.requestDate, p.DueDate) AS requestdate,
                IFNULL(b.jqhf, '') AS jqhfnew,
                IFNULL(b.fpjh, '') AS fpjhnew,
                ds.SentQty,
                IFNULL(im.Drawing, p.Drawing) AS th,
                IFNULL(im.Rev, p.Rev) AS bbh,
                IFNULL(ds.id, p.RecID) AS id,
                '' AS jhd,
                ds.suppliercode AS gysdm,
                CASE WHEN IFNULL(ds.id, '') = '' THEN p.Remarks ELSE ds.Remarks END AS bz,
                '' AS shd,
                ds.id AS jhdid,
                p.DueDate AS jhrq,
                p.RecID AS polid,
                im.Descr1 AS wlgg,
                CASE WHEN b2.id IS NULL THEN '' ELSE ';sffb:#99FF00' END AS sffbgrdnew,
                CASE
                    WHEN IFNULL(b.jqhf, '') = '' THEN 'wait'
                    WHEN TIMESTAMPDIFF(DAY, IFNULL(ds.requestDate, p.DueDate), b.jqhf) <= 2 THEN 'OK'
                    WHEN TIMESTAMPDIFF(DAY, IFNULL(ds.requestDate, p.DueDate), b.jqhf) > 2 THEN 'NO'
                    WHEN b.jqhf = '' AND IFNULL(b.qhdj, '') <> '' THEN 'refuse'
                    ELSE 'wait'
                END AS sfyqnew
            FROM PurOrdDetail p
            LEFT JOIN PurOrdMaster pm ON p.PurOrd = pm.PurOrd
            LEFT JOIN (
                SELECT SUM(sh_delivery_quantity) AS fhsl, po_bill, po_billline
                FROM scm_shdzb
                GROUP BY po_bill, po_billline
            ) s ON s.po_bill = p.PurOrd AND s.po_billline = p.Line
            LEFT JOIN (
                SELECT SUM(Qty) AS zjsl, PurOrd, PurLine
                FROM MissedPrint
                WHERE Status = 'I'
                GROUP BY PurOrd, PurLine
            ) i ON i.PurOrd = p.PurOrd AND i.PurLine = p.Line
            LEFT JOIN (
                SELECT SUM(QtyReturn) AS thsl, OrdNbr, OrdLine
                FROM PurOrdRctDetail
                WHERE rcttype IN ('pt', 'temp')
                GROUP BY OrdNbr, OrdLine
            ) dor ON dor.OrdNbr = p.PurOrd AND dor.OrdLine = p.Line
            LEFT JOIN srm_polist_ds ds ON p.PurOrd = ds.ponumber AND p.Line = ds.poline
            LEFT JOIN (
                SELECT SUM(sh_delivery_quantity) AS jhdyj, jhdbh
                FROM scm_shdzb
                GROUP BY jhdbh
            ) sh ON ds.dsnum = sh.jhdbh
            LEFT JOIN (
                SELECT SUM(IFNULL(Qty, 0)) AS jhdzj, PurOrdDetBatchNbr
                FROM MissedPrint
                WHERE Status = 'I'
                GROUP BY PurOrdDetBatchNbr
            ) jy ON ds.dsnum = jy.PurOrdDetBatchNbr
            LEFT JOIN (
                SELECT glid1, MIN(id) AS id, MIN(DATE_FORMAT(hfsj, '%Y.%m.%d')) AS hfsj
                FROM scm_jhjh_jq
                WHERE flag = 2
                GROUP BY glid1
            ) b2 ON ds.id = b2.glid1
            LEFT JOIN ItemMaster im ON p.ItemNum = im.ItemNum
            LEFT JOIN (
                SELECT
                    glid,
                    GROUP_CONCAT(CONCAT(CAST(jhdsl AS CHAR(10)), '(', DATE_FORMAT(jqhf, '%Y-%m-%d'), ')') SEPARATOR '<br>') AS fpjh,
                    GROUP_CONCAT(qhdj SEPARATOR '') AS qhdj,
                    MAX(jqhf) AS jqhf
                FROM scm_jhjh_jq
                WHERE flag = 0
                GROUP BY glid
            ) b ON ds.id = b.glid
            WHERE p.Status <> 'C'
              AND ds.schedqty >= 0
              AND ds.isactive = 1
              AND ds.status = 'P'
        ) v
        """;

    private sealed class SupplierDeliveryListRow
    {
        public string? Sffb { get; set; }
        public string? Wlbm { get; set; }
        public string? Wlms { get; set; }
        public string? Buyer { get; set; }
        public string? Gysdm { get; set; }
        public string? Gysmc { get; set; }
        public string? Cgdd { get; set; }
        public int? Ddhh { get; set; }
        public decimal? Jhdsl { get; set; }
        public decimal? Dfhsl { get; set; }
        public string? Dsnum { get; set; }
        public DateTime? Requestdate { get; set; }
        public decimal? Schedqty { get; set; }
        public string? Jqhfnew { get; set; }
        public decimal? Ztsl { get; set; }
        public decimal? Zsl1 { get; set; }
        public decimal? Rksl { get; set; }
        public decimal? Bhgsl { get; set; }
        public decimal? Thsl { get; set; }
        public string? Bz { get; set; }
        public string? Id { get; set; }
        public decimal? Wjhsl { get; set; }
        public decimal? Zfhl { get; set; }
    }
}