AiDOPWarehouse/server/Plugins/Admin.NET.Plugin.AiDOP/Order/SeOrderService.cs

using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Order;

/// <summary>
/// 销售订单评审服务 🗂️
/// 路由前缀：/api/Order/seorder/...
/// </summary>
[ApiDescriptionSettings(Order = 300, Description = "销售订单评审")]
[Route("api/Order")]
[AllowAnonymous]
[NonUnify]
public class SeOrderService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly SqlSugarRepository<SeOrder> _seOrderRep;
    private readonly SqlSugarRepository<SeOrderEntry> _seOrderEntryRep;
    private readonly SqlSugarRepository<SeOrderChange> _seOrderChangeRep;
    private readonly UserManager _userManager;
    private readonly SeOrderOperationLogService _orderOpLog;

    public SeOrderService(
        ISqlSugarClient db,
        SqlSugarRepository<SeOrder> seOrderRep,
        SqlSugarRepository<SeOrderEntry> seOrderEntryRep,
        SqlSugarRepository<SeOrderChange> seOrderChangeRep,
        UserManager userManager,
        SeOrderOperationLogService orderOpLog)
    {
        _db = db;
        _seOrderRep = seOrderRep;
        _seOrderEntryRep = seOrderEntryRep;
        _seOrderChangeRep = seOrderChangeRep;
        _userManager = userManager;
        _orderOpLog = orderOpLog;
    }

    // ══════════════════════════════════════════════════════════════
    // 订单列表 GET /api/Order/seorder/list
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取订单评审分页列表 🗂️</summary>
    [DisplayName("获取订单评审列表")]
    [HttpGet("seorder/list")]
    public async Task<object> GetSeOrderList([FromQuery] SeOrderListInput input)
    {
        var tenantId = _userManager.TenantId;
        var pars = new List<SugarParameter> { new("@TenantId", tenantId) };
        var innerConditions = new List<string> { "o.IsDeleted = 0", "o.tenant_id = @TenantId" };

        if (!string.IsNullOrWhiteSpace(input.BillNo))
        {
            innerConditions.Add("o.bill_no LIKE @BillNo");
            pars.Add(new SugarParameter("@BillNo", $"%{input.BillNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.CustomNo))
        {
            innerConditions.Add("o.custom_no LIKE @CustomNo");
            pars.Add(new SugarParameter("@CustomNo", $"%{input.CustomNo.Trim()}%"));
        }
        if (input.OrderType.HasValue)
        {
            innerConditions.Add("o.order_type = @OrderType");
            pars.Add(new SugarParameter("@OrderType", input.OrderType.Value));
        }

        var baseSql = BuildListBaseSql(string.Join(" AND ", innerConditions));

        // state 是 CASE 计算列，需包一层再过滤
        var outerWhere = "";
        if (!string.IsNullOrWhiteSpace(input.State))
        {
            outerWhere = "WHERE t.State = @State";
            pars.Add(new SugarParameter("@State", input.State.Trim()));
        }

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM ({baseSql}) AS t {outerWhere}", pars);
        var list = await _db.Ado.SqlQueryAsync<SeOrderListRow>(
            $"SELECT * FROM ({baseSql}) AS t {outerWhere} ORDER BY t.Id DESC LIMIT {input.PageSize} OFFSET {offset}", pars);

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

    private static string BuildListBaseSql(string innerWhere) => $"""
        SELECT
            o.Id               AS Id,
            o.bill_no          AS BillNo,
            o.order_type       AS OrderType,
            o.custom_no        AS CustomNo,
            o.custom_name      AS SortName,
            o.rdate            AS RDate,
            o.flowstate        AS FlowState,
            COALESCE(NULLIF(e.min_progress, ''), '1') AS Progress,
            CASE
                WHEN COALESCE(NULLIF(e.min_progress, ''), '1') = '1' THEN '新建'
                WHEN COALESCE(NULLIF(e.min_progress, ''), '1') = '2' THEN '评审'
                WHEN COALESCE(NULLIF(e.min_progress, ''), '1') = '0' THEN '再评审'
                WHEN COALESCE(NULLIF(e.min_progress, ''), '1') = '3' THEN '确认'
                ELSE '确认'
            END                AS State,
            chg.change_content AS ChangeContent,
            cN.changeNum       AS ChangeNum
        FROM crm_seorder o
        LEFT JOIN (
            SELECT seorder_id, MIN(COALESCE(NULLIF(progress, ''), '1')) AS min_progress
            FROM crm_seorderentry
            WHERE IsDeleted = 0
            GROUP BY seorder_id
        ) e ON e.seorder_id = o.Id
        LEFT JOIN (
            SELECT order_no, change_content
            FROM (
                SELECT JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.bill_no')) AS order_no,
                       JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.change_content')) AS change_content,
                       sync_time,
                       ROW_NUMBER() OVER (PARTITION BY JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.bill_no')) ORDER BY sync_time DESC, id DESC) AS RowNum
                FROM mdp_stg_so
                WHERE source_table='crm_seorder_change'
                  AND JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.bill_no')) IS NOT NULL
            ) ranked
            WHERE RowNum = 1
        ) chg ON chg.order_no = o.bill_no
        LEFT JOIN (
            SELECT JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.bill_no')) AS order_no, COUNT(*) AS changeNum
            FROM mdp_stg_so
            WHERE source_table='crm_seorder_change'
              AND JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.bill_no')) IS NOT NULL
            GROUP BY JSON_UNQUOTE(JSON_EXTRACT(raw_data,'$.bill_no'))
        ) cN ON cN.order_no = o.bill_no
        WHERE {innerWhere}
        """;

    // ══════════════════════════════════════════════════════════════
    // 客户列表 GET /api/Order/seorder/customers
    // 注：字面量路由优先于 {id} 参数路由，无需额外排序
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取客户选择列表 🗂️</summary>
    [DisplayName("获取客户列表")]
    [HttpGet("seorder/customers")]
    public async Task<object> GetCustomers([FromQuery] KeywordPageInput input)
    {
        var pars = new List<SugarParameter>();
        var where = "1=1";
        if (!string.IsNullOrWhiteSpace(input.Keyword))
        {
            where = "(Cust LIKE @Keyword OR SortName LIKE @Keyword)";
            pars.Add(new SugarParameter("@Keyword", $"%{input.Keyword.Trim()}%"));
        }

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM CustMaster WHERE {where}", pars);
        var list = await _db.Ado.SqlQueryAsync<SimpleKvRow>(
            $"SELECT Cust AS Value, SortName AS Label, NULL AS Extra, NULL AS Id FROM CustMaster WHERE {where} ORDER BY Cust LIMIT {input.PageSize} OFFSET {offset}",
            pars);

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

    // ══════════════════════════════════════════════════════════════
    // 物料列表 GET /api/Order/seorder/items
    // TODO: 将 bd_material 替换为实际物料主数据表名
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取物料选择列表 🗂️</summary>
    [DisplayName("获取物料列表")]
    [HttpGet("seorder/items")]
    public async Task<object> GetItems([FromQuery] KeywordPageInput input)
    {
        var pars = new List<SugarParameter>();
        var where = "IsDeleted = 0";
        if (!string.IsNullOrWhiteSpace(input.Keyword))
        {
            where += " AND (item_number LIKE @Keyword OR item_name LIKE @Keyword)";
            pars.Add(new SugarParameter("@Keyword", $"%{input.Keyword.Trim()}%"));
        }

        var offset = (input.Page - 1) * input.PageSize;
        var total = await _db.Ado.GetIntAsync(
            $"SELECT COUNT(*) FROM bd_material WHERE {where}", pars);
        var list = await _db.Ado.SqlQueryAsync<SimpleKvRow>(
            $"SELECT item_number AS Value, item_name AS Label, CONCAT(IFNULL(specification,''),'|',IFNULL(unit,'')) AS Extra, NULL AS Id FROM bd_material WHERE {where} ORDER BY item_number LIMIT {input.PageSize} OFFSET {offset}",
            pars);

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

    // ══════════════════════════════════════════════════════════════
    // 订单详情 GET /api/Order/seorder/{id}
    // ══════════════════════════════════════════════════════════════
    /// <summary>获取订单详情（含明细）🗂️</summary>
    [DisplayName("获取订单详情")]
    [HttpGet("seorder/{id}")]
    public async Task<object> GetSeOrderDetail(long id)
    {
        const string orderSql = """
            SELECT Id, bill_no AS BillNo, order_type AS OrderType,
                   custom_id AS CustomId, custom_no AS CustomNo, custom_name AS CustomName,
                   date AS Date, custom_level AS CustomLevel, urgent AS Urgent,
                   bill_from AS BillFrom, country AS Country, rdate AS RDate, flowstate AS FlowState
            FROM crm_seorder
            WHERE Id = @Id AND IsDeleted = 0
            LIMIT 1
            """;
        var order = (await _db.Ado.SqlQueryAsync<SeOrderDetailRow>(orderSql, new { Id = id }))
                    .FirstOrDefault()
                    ?? throw Oops.Oh("订单不存在");

        const string entrySql = """
            SELECT Id, entry_seq AS EntrySeq, company_id AS CompanyId, item_number AS ItemNumber, item_name AS ItemName,
                   specification AS Specification, qty AS Qty, plan_date AS PlanDate,
                   sys_capacity_date AS SysCapacityDate, date AS Date, remark AS Remark,
                   unit AS Unit, deliver_count AS DeliverCount, progress AS Progress
            FROM crm_seorderentry
            WHERE seorder_id = @Id AND IsDeleted = 0
            ORDER BY entry_seq
            """;
        var entries = await _db.Ado.SqlQueryAsync<SeOrderEntryRow>(entrySql, new { Id = id });

        return new
        {
            order.Id, order.BillNo, order.OrderType,
            order.CustomId, order.CustomNo, order.CustomName,
            order.Date, order.CustomLevel, order.Urgent,
            order.BillFrom, order.Country, order.RDate, order.FlowState,
            entries
        };
    }

    // ══════════════════════════════════════════════════════════════
    // 新增 / 编辑 POST /api/Order/seorder/save
    // ══════════════════════════════════════════════════════════════
    /// <summary>保存销售订单（新增或编辑）🗂️</summary>
    [DisplayName("保存销售订单")]
    [ApiDescriptionSettings(Name = "SaveSeOrder"), HttpPost("seorder/save")]
    public async Task<object> SaveSeOrder([FromBody] SeOrderSaveInput input)
    {
        if (input.Id is null or 0)
        {
            // ── 新增：参照 SysJobService.AddJobDetail ──
            var tenantId = _userManager.TenantId;

            // 如果未填写订单编号，自动生成：SO + yyyyMMdd + 4位流水号
            if (string.IsNullOrWhiteSpace(input.BillNo))
            {
                input.BillNo = await GenerateBillNoAsync(tenantId);
            }

            var isExist = await _seOrderRep.IsAnyAsync(u => u.BillNo == input.BillNo && u.IsDeleted == 0 && u.TenantId == tenantId);
            if (isExist) throw Oops.Oh("订单编号已存在");

            var entity = input.Adapt<SeOrder>();
            entity.Id = YitIdHelper.NextId();
            entity.IsDeleted = 0;
            entity.Closed = 0;
            entity.CreateTime = DateTime.Now;
            entity.TenantId = tenantId;
            await _seOrderRep.InsertAsync(entity);
            await SaveEntriesAsync(entity.Id, input.BillNo, input.Entries);

            var orderAfter = await _seOrderRep.GetFirstAsync(u => u.Id == entity.Id && u.IsDeleted == 0 && u.TenantId == tenantId);
            var entriesAfter = await _seOrderEntryRep.GetListAsync(u => u.SeOrderId == entity.Id && u.IsDeleted == 0 && u.TenantId == tenantId);
            var afterJson = SeOrderAuditHelper.BuildCombinedSnapshotJson(orderAfter!, entriesAfter);
            await _orderOpLog.WriteAsync(new SeOrderOperationLogDto
            {
                UserId = _userManager.UserId,
                Action = "新增订单",
                TableName = "crm_seorder",
                KeyValue = entity.Id.ToString(),
                Content = $"新增订单，订单编号：{entity.BillNo}，订单主键：{entity.Id}。",
                BeforeJson = null,
                AfterJson = afterJson
            });

            return new { id = entity.Id, message = "新增成功" };
        }
        else
        {
            // ── 编辑：参照 SysJobService.UpdateJobDetail ──
            var tenantId = _userManager.TenantId;
            var entity = await _seOrderRep.GetFirstAsync(u => u.Id == input.Id.Value && u.IsDeleted == 0)
                         ?? throw Oops.Oh("订单不存在");

            var beforeEntries = await _seOrderEntryRep.GetListAsync(u => u.SeOrderId == input.Id.Value && u.IsDeleted == 0);
            var orderBefore = entity.Adapt<SeOrder>();
            var entriesBefore = beforeEntries.Select(e => e.Adapt<SeOrderEntry>()).ToList();

            input.Adapt(entity);
            entity.UpdateTime = DateTime.Now;
            await _seOrderRep.UpdateAsync(entity);

            await SaveEntriesAsync(input.Id.Value, input.BillNo, input.Entries);

            var afterOrder = await _seOrderRep.GetFirstAsync(u => u.Id == input.Id.Value && u.IsDeleted == 0 && u.TenantId == tenantId);
            var afterEntries = await _seOrderEntryRep.GetListAsync(u => u.SeOrderId == input.Id.Value && u.IsDeleted == 0 && u.TenantId == tenantId);
            var (content, beforeJson, afterJson) = SeOrderAuditHelper.BuildUpdateDiff(
                orderBefore!, entriesBefore, afterOrder!, afterEntries);

            await _orderOpLog.WriteAsync(new SeOrderOperationLogDto
            {
                UserId = _userManager.UserId,
                Action = "修改订单",
                TableName = "crm_seorder",
                KeyValue = input.Id.Value.ToString(),
                Content = content,
                BeforeJson = beforeJson,
                AfterJson = afterJson
            });

            return new { id = input.Id, message = "编辑成功" };
        }
    }

    /// <summary>
    /// 明细三路合并：
    /// ① DB有且入参有（按 Id 匹配）→ 更新
    /// ② DB无但入参有              → 新增（补填 bill_no / progress=0 / create_by）
    /// ③ DB有但入参无              → 删除
    /// </summary>
    private async Task SaveEntriesAsync(long orderId, string billNo, List<SeOrderEntryInput> entries)
    {
        var tenantId = _userManager.TenantId;
        // 取 DB 现有明细，以 Id 为 key 建索引
        var dbEntries = await _seOrderEntryRep.GetListAsync(u => u.SeOrderId == orderId && u.IsDeleted == 0 && u.TenantId == tenantId);
        var dbById = dbEntries.ToDictionary(u => u.Id);

        // 入参中携带 Id 的集合（用于判断 DB 行是否需要删除）
        var inputIds = new HashSet<long>(entries.Where(e => e.Id > 0).Select(e => e.Id!.Value));

        for (var i = 0; i < entries.Count; i++)
        {
            var e = entries[i];
            var seq = e.EntrySeq ?? (i + 1);

            if (e.Id > 0 && dbById.TryGetValue(e.Id.Value, out var existing))
            {
                // ① DB有、参数有 → 更新（参照 SysJobService.UpdateJobDetail）
                var originalCompanyId = existing.CompanyId;
                e.Adapt(existing);
                existing.CompanyId = e.CompanyId ?? originalCompanyId;
                existing.EntrySeq = seq;
                existing.UpdateTime = DateTime.Now;
                await _seOrderEntryRep.UpdateAsync(existing);
            }
            else
            {
                // ② DB无、参数有 → 新增（参照 SysJobService.AddJobDetail）
                var entry = e.Adapt<SeOrderEntry>();
                entry.Id = YitIdHelper.NextId();
                entry.SeOrderId = orderId;
                entry.BillNo = billNo;
                entry.EntrySeq = seq;
                entry.CompanyId ??= 1000;
                entry.Progress ??= "0";
                entry.CreateBy = _userManager.UserId;
                entry.IsDeleted = 0;
                entry.CreateTime = DateTime.Now;
                entry.TenantId = tenantId;
                await _seOrderEntryRep.InsertAsync(entry);
            }
        }

        // ③ DB有、参数无 → 删除（参照 SysJobService.DeleteJobDetail）
        foreach (var toDelete in dbEntries.Where(u => !inputIds.Contains(u.Id)))
        {
            await _seOrderEntryRep.DeleteAsync(u => u.Id == toDelete.Id);
        }
    }

    /// <summary>
    /// 生成订单编号：SO + yyyyMMdd + 4位流水号，例如 SO202605210001
    /// </summary>
    private async Task<string> GenerateBillNoAsync(long tenantId)
    {
        var today = DateTime.Now.ToString("yyyyMMdd");
        var prefix = $"SO{today}";

        // 查询当天已有的最大订单编号
        var maxBillNo = await _db.Queryable<SeOrder>()
            .Where(u => u.BillNo!.StartsWith(prefix) && u.IsDeleted == 0 && u.TenantId == tenantId)
            .OrderBy(u => u.BillNo, OrderByType.Desc)
            .Select(u => u.BillNo)
            .FirstAsync();

        if (string.IsNullOrEmpty(maxBillNo))
        {
            return $"{prefix}0001";
        }

        // 取后4位转为数字并加1，不足4位补零
        var last4 = maxBillNo.Length >= 4 ? maxBillNo[^4..] : maxBillNo;
        if (int.TryParse(last4, out var serial))
        {
            return $"{prefix}{(serial + 1).ToString().PadLeft(4, '0')}";
        }

        return $"{prefix}0001";
    }

    // ══════════════════════════════════════════════════════════════
    // 订单评审 / 交期确认 — 已改为前端直接调用外部接口，此处仅保留存根
    // ══════════════════════════════════════════════════════════════

    // ══════════════════════════════════════════════════════════════
    // 资源解锁 POST /api/Order/seorder/{id}/unlock
    // ══════════════════════════════════════════════════════════════
    /// <summary>资源解锁 🔓</summary>
    [DisplayName("资源解锁")]
    [ApiDescriptionSettings(Name = "UnlockSeOrder"), HttpPost("seorder/{id}/unlock")]
    public async Task<object> UnlockSeOrder(long id)
    {
        return new { message = "解锁成功" };
    }

    // ══════════════════════════════════════════════════════════════
    // 变更申请 POST /api/Order/seorder/{id}/change
    // ══════════════════════════════════════════════════════════════
    /// <summary>提交订单变更申请 🔖</summary>
    [DisplayName("提交订单变更申请")]
    [ApiDescriptionSettings(Name = "CreateSeOrderChange"), HttpPost("seorder/{id}/change")]
    public async Task<object> CreateChange(long id, [FromBody] SeOrderChangeSaveInput input)
    {
        var tenantId = _userManager.TenantId;
        // 参照 SysJobService.AddJobDetail：Adapt → 设主键与审计字段 → InsertAsync
        var entity = input.Adapt<SeOrderChange>();
        entity.Id = YitIdHelper.NextId();
        entity.SeOrderId = id;
        entity.IsDeleted = 0;
        entity.CreateTime = DateTime.Now;
        entity.TenantId = tenantId;
        await _seOrderChangeRep.InsertAsync(entity);
        return new { id = entity.Id, message = "变更申请已保存" };
    }

    // ──────────────── 内部查询结果映射类 ────────────────

    private sealed class SeOrderListRow
    {
        public long Id { get; set; }
        public string? BillNo { get; set; }
        public int? OrderType { get; set; }
        public string? CustomNo { get; set; }
        public string? SortName { get; set; }
        public DateTime? RDate { get; set; }
        public string? Progress { get; set; }
        public string? State { get; set; }
        public string? ChangeContent { get; set; }
        public int? ChangeNum { get; set; }
        public string? FlowState { get; set; }
    }

    private sealed class SeOrderDetailRow
    {
        public long Id { get; set; }
        public string? BillNo { get; set; }
        public int? OrderType { get; set; }
        public long? CustomId { get; set; }
        public string? CustomNo { get; set; }
        public string? CustomName { get; set; }
        public DateTime? Date { get; set; }
        public int? CustomLevel { get; set; }
        public int? Urgent { get; set; }
        public string? BillFrom { get; set; }
        public string? Country { get; set; }
        public DateTime? RDate { get; set; }
        public string? FlowState { get; set; }
    }

    private sealed class SeOrderEntryRow
    {
        public long Id { get; set; }
        public int? EntrySeq { get; set; }
        public long? CompanyId { get; set; }
        public string? ItemNumber { get; set; }
        public string? ItemName { get; set; }
        public string? Specification { get; set; }
        public decimal? Qty { get; set; }
        public DateTime? PlanDate { get; set; }
        public DateTime? SysCapacityDate { get; set; }
        public DateTime? Date { get; set; }
        public string? Remark { get; set; }
        public string? Unit { get; set; }
        public decimal? DeliverCount { get; set; }
        public string? Progress { get; set; }
    }

    private sealed class SimpleKvRow
    {
        public string? Value { get; set; }
        public string? Label { get; set; }
        public string? Extra { get; set; }
        public long? Id { get; set; }
    }

    // ══════════════════════════════════════════════════════════════
    // 提交订单评审审批
    // ══════════════════════════════════════════════════════════════

    /// <summary>提交订单评审审批流程</summary>
    [DisplayName("提交订单评审审批")]
    [HttpPost("seorder/submitReview")]
    public async Task<object> SubmitOrderReview([FromBody] SubmitReviewInput input)
    {
        var order = await _seOrderRep.GetByIdAsync(input.OrderId)
            ?? throw Oops.Oh("订单不存在");
        var engine = App.GetRequiredService<Admin.NET.Plugin.ApprovalFlow.Service.FlowEngineService>();
        var instanceId = await engine.StartFlow(new Admin.NET.Plugin.ApprovalFlow.Service.StartFlowInput
        {
            BizType = "ORDER_REVIEW",
            BizId = order.Id,
            BizNo = order.BillNo,
            Title = $"订单评审-{order.BillNo}",
            Comment = input.Comment,
        });
        return new { instanceId };
    }

    public class SubmitReviewInput
    {
        public long OrderId { get; set; }
        public string? Comment { get; set; }
    }
}