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;

        if (await ShouldUseDwdAsync())
        {
            return await GetDwdListAsync(input, page, pageSize, offset);
        }

        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 async Task<bool> ShouldUseDwdAsync()
    {
        return await _db.Ado.GetIntAsync("SELECT COUNT(1) FROM dwd_supplier_delivery LIMIT 1") > 0;
    }

    private async Task<object> GetDwdListAsync(PurchaseOrderListInput input, int page, int pageSize, int offset)
    {
        var where = new List<string> { "1=1" };
        var pars = new List<SugarParameter>();

        if (!string.IsNullOrWhiteSpace(input.Cgdd))
        {
            where.Add("a.po_no LIKE @Cgdd");
            pars.Add(new SugarParameter("@Cgdd", $"%{input.Cgdd.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Wlbm))
        {
            where.Add("a.item_code LIKE @Wlbm");
            pars.Add(new SugarParameter("@Wlbm", $"%{input.Wlbm.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Gysdm))
        {
            where.Add("a.supplier_code LIKE @Gysdm");
            pars.Add(new SugarParameter("@Gysdm", $"%{input.Gysdm.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.Sffbcx))
        {
            where.Add("CASE WHEN IFNULL(a.delivery_status,'')='OPEN' THEN 'X' ELSE 'V' END = @Sffbcx");
            pars.Add(new SugarParameter("@Sffbcx", input.Sffbcx.Trim().ToUpperInvariant()));
        }

        var fromSql = $"FROM dwd_supplier_delivery a WHERE {string.Join(" AND ", where)}";
        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) {fromSql}", pars);
        var orderBy = BuildDwdOrderBy(input.SortField, input.SortOrder);
        var list = await _db.Ado.SqlQueryAsync<PurchaseOrderListRow>(
            $"""
            SELECT
                a.supplier_code AS Gysdm,
                a.supplier_name AS Gysmc,
                a.po_no AS Cgdd,
                a.po_line AS Ddhh,
                NULL AS Buyer,
                NULL AS Orddate,
                a.item_code AS Wlbm,
                a.item_name AS Wlms,
                a.po_type AS Ddlx,
                a.order_qty AS Jhdsl,
                a.remaining_qty AS Wjhsl,
                DATE_FORMAT(DATE(COALESCE(a.need_date, a.due_date)), '%Y-%m-%d') AS Jhrq,
                NULL AS Th,
                CASE WHEN IFNULL(a.delivery_status,'')='OPEN' THEN 'X' ELSE 'V' END AS Sffb,
                CASE WHEN IFNULL(a.delivery_status,'')='OPEN' THEN 'X' ELSE 'V' END AS Sffbcx
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    private static string BuildDwdOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "gysdm" => $"a.supplier_code {dir}",
            "gysmc" => $"a.supplier_name {dir}",
            "cgdd" => $"a.po_no {dir}",
            "ddhh" => $"a.po_line {dir}",
            "orddate" => $"a.stat_date {dir}",
            "wlbm" => $"a.item_code {dir}",
            "wlms" => $"a.item_name {dir}",
            "ddlx" => $"a.po_type {dir}",
            "jhdsl" => $"a.order_qty {dir}",
            "wjhsl" => $"a.remaining_qty {dir}",
            "jhrq" => $"a.need_date {dir}",
            _ => "a.stat_date DESC, a.po_no DESC, a.po_line ASC"
        };
    }

    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; }
    }
}