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

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 物料采购订单服务
/// </summary>
[ApiDescriptionSettings(Order = 309, Description = "物料采购订单")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class PurchaseOrderService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;

    public PurchaseOrderService(ISqlSugarClient db)
    {
        _db = db;
    }

    [DisplayName("物料采购订单列表")]
    [HttpGet("purchase-order/list")]
    public async Task<object> GetList([FromQuery] PurchaseOrderListInput input)
    {
        var page = input.Page <= 0 ? 1 : input.Page;
        var pageSize = input.PageSize <= 0 ? 10 : input.PageSize;
        var offset = (page - 1) * pageSize;

        var where = new List<string> { "1=1" };
        var pars = new List<SugarParameter>();

        if (!string.IsNullOrWhiteSpace(input.Cgdd))
        {
            where.Add("a.cgdd LIKE @Cgdd");
            pars.Add(new SugarParameter("@Cgdd", $"%{input.Cgdd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Wlbm))
        {
            where.Add("a.wlbm LIKE @Wlbm");
            pars.Add(new SugarParameter("@Wlbm", $"%{input.Wlbm.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Gysdm))
        {
            where.Add("a.gysdm LIKE @Gysdm");
            pars.Add(new SugarParameter("@Gysdm", $"%{input.Gysdm.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Sffbcx))
        {
            where.Add("(CASE WHEN b2.b2id IS NULL THEN 'X' ELSE 'V' END)=@Sffbcx");
            pars.Add(new SugarParameter("@Sffbcx", input.Sffbcx.Trim().ToUpperInvariant()));
        }

        var fromSql = $"""
            FROM (
                SELECT
                    a.id, a.wlbm, a.wlms, a.wlgg, a.cgdd, a.jhdsl, a.wjhsl, a.jhd, a.yjjhrq, a.gysdm, a.gysmc,
                    DATE_FORMAT(DATE(a.yjjhrq), '%Y-%m-%d') AS jhrq,
                    CASE
                        WHEN IFNULL(b.jqhf,'')='' AND IFNULL(b.qhdj,'')='' THEN 'wait'
                        WHEN IFNULL(b.jqhf,'')='' AND IFNULL(b.qhdj,'')<>'' THEN 'refuse'
                        WHEN b.jqhf IS NOT NULL AND DATEDIFF(DATE(a.yjjhrq), DATE(b.jqhf))>=0 THEN 'OK'
                        ELSE 'NO'
                    END AS sfyq,
                    b.jqhf, a.ddhh, a.th, a.bbh, a.StdCost, a.PurCost, a.PurCost * a.jhdsl AS je,
                    a.createtime, a.buyer, a.type AS ddlx, a.dw, a.payumconv, a.orddate
                FROM vscm_jhjh a
                LEFT JOIN (
                    SELECT glid, MAX(jqhf) AS jqhf, GROUP_CONCAT(qhdj SEPARATOR '') AS qhdj
                    FROM scm_jhjh_jq
                    WHERE flag = 0
                    GROUP BY glid
                ) b ON a.id = b.glid
            ) a
            LEFT JOIN (
                SELECT OrdNbr, OrdLine, SUM(ReceiptQty + yssl) AS zshl, SUM(yssl) AS rksl, SUM(QtyReturn) AS bhgsl, SUM(QtyReturned) AS thsl
                FROM vscm_cgshrk
                GROUP BY OrdNbr, OrdLine
            ) b ON a.cgdd = b.OrdNbr AND a.ddhh = b.OrdLine
            LEFT JOIN (
                SELECT po_bill, po_billline, SUM(sh_delivery_quantity) AS zfhl
                FROM scm_shdzb a
                INNER JOIN scm_shd b ON a.glid = b.id
                WHERE b.state = 0
                GROUP BY po_bill, po_billline
            ) c ON a.cgdd = c.po_bill AND a.ddhh = c.po_billline
            LEFT JOIN (
                SELECT cgdd, ddhh, MIN(id) AS b2id, MIN(hfsj) AS hfsj
                FROM scm_jhjh_jq
                WHERE flag = 2
                GROUP BY cgdd, ddhh
            ) b2 ON a.cgdd = b2.cgdd AND a.ddhh = b2.ddhh
            WHERE {string.Join(" AND ", where)}
            """;

        var orderBy = BuildOrderBy(input.SortField, input.SortOrder);
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var list = await _db.Ado.SqlQueryAsync<PurchaseOrderListRow>(
            $"""
            SELECT
                a.gysdm AS Gysdm,
                a.gysmc AS Gysmc,
                a.cgdd AS Cgdd,
                a.ddhh AS Ddhh,
                a.buyer AS Buyer,
                a.orddate AS Orddate,
                a.wlbm AS Wlbm,
                a.wlms AS Wlms,
                a.ddlx AS Ddlx,
                a.jhdsl AS Jhdsl,
                a.wjhsl AS Wjhsl,
                a.jhrq AS Jhrq,
                a.th AS Th,
                CASE WHEN b2.b2id IS NULL THEN 'X' ELSE DATE_FORMAT(b2.hfsj, '%Y.%m.%d') END AS Sffb,
                CASE WHEN b2.b2id IS NULL THEN 'X' ELSE 'V' END AS Sffbcx
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "gysdm" => $"a.gysdm {dir}",
            "gysmc" => $"a.gysmc {dir}",
            "cgdd" => $"a.cgdd {dir}",
            "ddhh" => $"a.ddhh {dir}",
            "buyer" => $"a.buyer {dir}",
            "orddate" => $"a.orddate {dir}",
            "wlbm" => $"a.wlbm {dir}",
            "wlms" => $"a.wlms {dir}",
            "ddlx" => $"a.ddlx {dir}",
            "jhdsl" => $"a.jhdsl {dir}",
            "wjhsl" => $"a.wjhsl {dir}",
            "jhrq" => $"a.jhrq {dir}",
            "th" => $"a.th {dir}",
            "sffb" => $"b2.hfsj {dir}",
            _ => "a.id DESC"
        };
    }

    private sealed class PurchaseOrderListRow
    {
        public string? Gysdm { get; set; }
        public string? Gysmc { get; set; }
        public string? Cgdd { get; set; }
        public string? Ddhh { get; set; }
        public string? Buyer { get; set; }
        public DateTime? Orddate { get; set; }
        public string? Wlbm { get; set; }
        public string? Wlms { get; set; }
        public string? Ddlx { get; set; }
        public decimal? Jhdsl { get; set; }
        public decimal? Wjhsl { get; set; }
        public string? Jhrq { get; set; }
        public string? Th { get; set; }
        public string? Sffb { get; set; }
        public string? Sffbcx { get; set; }
    }
}