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`
            (
              `id`,`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`,`glid1`
            )
            SELECT
              UUID(),
              CAST(d.RecID AS CHAR(50)) AS glid,
              d.ItemNum AS wlbm,
              i.Descr AS wlms,
              i.Descr1 AS wlgg,
              p.PurOrd AS cgdd,
              CAST(d.QtyOrded AS CHAR(20)) AS jhdsl,
              (d.QtyOrded - d.RctQty - d.ReceiptQty + d.QtyReturned) AS wjhsl,
              '' AS jhd,
              DATE_FORMAT(DATE(d.DueDate), '%Y-%m-%d') AS yjjhrq,
              '' AS jqhf,
              p.Potype AS `type`,
              2 AS flag,
              '' AS scrq,
              '' AS scrid,
              '' AS scrxm,
              p.Supp AS gysdm,
              s.SortName AS gysmc,
              @hfrid AS hfrid,
              @hfrxm AS hfrxm,
              DATE_FORMAT(NOW(), '%Y-%m-%d') AS hfsj,
              '' AS qhdj,
              d.Line AS ddhh,
              d.UM AS dw,
              d.StdPackQty AS bzsl,
              '2' AS ly,
              CAST(IFNULL(ds.id, d.RecID) AS CHAR(50)) AS glid1
            FROM PurOrdMaster p
            LEFT JOIN PurOrdDetail d
              ON p.Domain = d.Domain
             AND p.Potype = d.Potype
             AND p.PurOrd = d.PurOrd
            LEFT JOIN SuppMaster s
              ON p.Domain = s.Domain
             AND p.Supp = s.Supp
            LEFT JOIN ItemMaster i
              ON d.Domain = i.Domain
             AND d.ItemNum = i.ItemNum
            LEFT JOIN srm_polist_ds ds
              ON p.PurOrd = ds.ponumber
             AND p.Line = ds.poline
            WHERE p.IsActive = 1
              AND IFNULL(p.Status, '') <> 'C'
              AND IFNULL(d.Status, '') <> 'C'
              AND FIND_IN_SET(CAST(IFNULL(ds.id, d.RecID) AS CHAR(50)), REPLACE(IFNULL(@ids, ''), ' ', '')) > 0
              AND NOT EXISTS (
                    SELECT 1 FROM scm_jhjh_jq x
                    WHERE x.flag = 2
                      AND x.glid1 = CAST(IFNULL(ds.id, d.RecID) AS CHAR(50))
              );
            """;
        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/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; }
    }
}