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

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;

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

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

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

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

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

        await ExecuteGenerateProcedure(input.PoNumber.Trim());
        return new { message = "批量添加成功" };
    }

    [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 ExecuteGenerateProcedure(string poNumber)
    {
        var account = _userManager.Account ?? "system";
        var orgNo = _userManager.OrgId.ToString();

        await _db.Ado.ExecuteCommandAsync(
            "CALL pr_MES_GenerateJHD(@PoNumber, @OrgNo, @Account)",
            new SugarParameter("@PoNumber", poNumber),
            new SugarParameter("@OrgNo", orgNo),
            new SugarParameter("@Account", account));
    }

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