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

using Yitter.IdGenerator;

namespace Admin.NET.Plugin.AiDOP.Supply;

/// <summary>
/// 物料采购申请服务
/// </summary>
[ApiDescriptionSettings(Order = 308, Description = "物料采购申请")]
[Route("api/Supply")]
[AllowAnonymous]
[NonUnify]
public class PurchaseRequestService : IDynamicApiController, ITransient
{
    private readonly ISqlSugarClient _db;
    private readonly UserManager _userManager;

    public PurchaseRequestService(ISqlSugarClient db, UserManager userManager)
    {
        _db = db;
        _userManager = userManager;
    }

    [DisplayName("物料采购申请列表")]
    [HttpGet("purchase-request/list")]
    public async Task<object> GetList([FromQuery] PurchaseRequestListInput 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(pm.IsDeleted,0)=0",
            "IFNULL(pm.state,0)<>0",
            "IFNULL(pm.analogcalcversion,'')=''"
        };

        if (_userManager.TenantId > 0)
        {
            where.Add("pm.tenant_id=@TenantId");
            pars.Add(new SugarParameter("@TenantId", _userManager.TenantId));
        }
        if (!string.IsNullOrWhiteSpace(input.PrBillNo))
        {
            where.Add("pm.pr_billno LIKE @PrBillNo");
            pars.Add(new SugarParameter("@PrBillNo", $"%{input.PrBillNo.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.ItemNumber))
        {
            where.Add("ic.number LIKE @ItemNumber");
            pars.Add(new SugarParameter("@ItemNumber", $"%{input.ItemNumber.Trim()}%"));
        }
        if (!string.IsNullOrWhiteSpace(input.SupplierName))
        {
            where.Add("pm.pr_purchasename LIKE @SupplierName");
            pars.Add(new SugarParameter("@SupplierName", $"%{input.SupplierName.Trim()}%"));
        }
        if (input.State.HasValue)
        {
            where.Add("pm.state=@State");
            pars.Add(new SugarParameter("@State", input.State.Value));
        }

        var orderBy = BuildOrderBy(input.SortField, input.SortOrder);
        var fromSql = $"""
            FROM srm_pr_main pm
            LEFT JOIN ic_item ic ON pm.icitem_id=ic.Id
            LEFT JOIN srm_purchase pur ON pm.icitem_id=pur.icitem_id AND pm.pr_purchaseid = pur.supplier_id AND pur.tenant_id=pm.tenant_id
            LEFT JOIN ic_item_stock its ON ic.Id=its.icitem_id
            LEFT JOIN PurOrdDetail pod ON (pm.pr_billno=pod.Req OR pm.SAP_pr_billno=pod.Req) AND pod.tenant_id=pm.tenant_id
            WHERE {string.Join(" AND ", where)}
            """;

        var total = await _db.Ado.GetIntAsync($"SELECT COUNT(1) FROM (SELECT DISTINCT pm.Id {fromSql}) t", pars);
        var list = await _db.Ado.SqlQueryAsync<PurchaseRequestListRow>(
            $"""
            SELECT
                pm.id AS Id,
                pm.pr_billno AS PrBillNo,
                pod.PurOrd AS PurOrd,
                pm.pr_type AS PrType,
                pm.supplier_type AS SupplierType,
                pm.IsRequireGoods AS IsRequireGoods,
                pm.state AS State,
                ic.number AS Number,
                ic.name AS Name,
                ic.model AS Model,
                pm.pr_unit AS PrUnit,
                pm.pr_purchasenumber AS PrPurchaseNumber,
                pm.pr_purchasename AS PrPurchaseName,
                pm.pr_sqty AS PrSqty,
                pm.pr_aqty AS PrAqty,
                its.sqty AS StockQty,
                pm.pr_ssend_date AS PrSsendDate,
                pm.pr_sarrive_date AS PrSarriveDate,
                pm.pr_purchaser AS PrPurchaser,
                pm.create_time AS CreateTime
            {fromSql}
            ORDER BY {orderBy}
            LIMIT {pageSize} OFFSET {offset}
            """,
            pars);

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

    [DisplayName("获取物料采购申请详情")]
    [HttpGet("purchase-request/{id:long}")]
    public async Task<object> GetDetail(long id)
    {
        var tenantId = _userManager.TenantId;
        var tenantWhere = tenantId > 0 ? " AND pm.tenant_id=@TenantId" : string.Empty;
        var pars = new List<SugarParameter> { new("@Id", id) };
        if (tenantId > 0) pars.Add(new SugarParameter("@TenantId", tenantId));

        var row = (await _db.Ado.SqlQueryAsync<PurchaseRequestDetailRow>(
            $"""
            SELECT
                pm.id AS Id,
                pm.pr_billno AS PrBillNo,
                CAST(pm.icitem_id AS CHAR(30)) AS IcitemId,
                pm.icitem_name AS IcitemName,
                pm.pr_unit AS PrUnit,
                pm.pr_purchasenumber AS PrPurchaseNumber,
                pm.pr_purchasename AS PrPurchaseName,
                pm.supplier_type AS SupplierType,
                pm.pr_aqty AS PrAqty,
                pm.pr_ssend_date AS PrSsendDate,
                pm.pr_sarrive_date AS PrSarriveDate,
                pm.pr_type AS PrType,
                pm.state AS State,
                pm.pr_purchaseid AS PrPurchaseId,
                pm.IsRequireGoods AS IsRequireGoods,
                pm.pr_purchaser_num AS PrPurchaserNum,
                pm.pr_purchaser AS PrPurchaser,
                pm.currencytype AS CurrencyType
            FROM srm_pr_main pm
            WHERE pm.Id=@Id AND IFNULL(pm.IsDeleted,0)=0{tenantWhere}
            LIMIT 1
            """,
            pars)).FirstOrDefault();

        return row ?? throw Oops.Oh("记录不存在");
    }

    [DisplayName("保存物料采购申请")]
    [ApiDescriptionSettings(Name = "SavePurchaseRequest"), HttpPost("purchase-request/save")]
    public async Task<object> Save([FromBody] PurchaseRequestSaveInput input)
    {
        // icitem_id 为雪花ID，前端以字符串传递避免 JS Number 精度丢失，这里解析回 long
        if (!long.TryParse(input.IcitemId?.Trim(), out var icitemId) || icitemId <= 0) throw Oops.Oh("请选择物料");
        if (input.PrPurchaseId is null or <= 0) throw Oops.Oh("请选择供应商");
        if (!input.PrAqty.HasValue || input.PrAqty.Value <= 0) throw Oops.Oh("申请数量必须大于0");

        var now = DateTime.Now;
        var aqty = input.PrAqty.Value;
        var prSsendDate = ParseDate(input.PrSsendDate) ?? now.Date;
        var prSarriveDate = ParseDate(input.PrSarriveDate);
        var prType = input.PrType ?? 3;
        var state = input.State ?? 1;
        var currency = input.CurrencyType ?? 1;
        var companyId = _userManager.OrgId > 0 ? _userManager.OrgId : 1000;

        if (input.Id is null or 0)
        {
            var newId = YitIdHelper.NextId();
            var billNo = string.IsNullOrWhiteSpace(input.PrBillNo) ? GenerateBillNo() : input.PrBillNo.Trim();

            await _db.Ado.ExecuteCommandAsync(
                """
                INSERT INTO srm_pr_main
                (Id,pr_billno,pr_purchaseid,pr_purchasenumber,pr_purchasename,pr_purchaser,pr_purchaser_num,
                 pr_rqty,pr_aqty,pr_sqty,icitem_id,icitem_name,pr_ssend_date,pr_sarrive_date,pr_unit,state,pr_type,currencytype,
                 create_by,create_by_name,create_time,update_by,update_by_name,update_time,tenant_id,factory_id,org_id,IsDeleted,company_id,IsRequireGoods,supplier_type)
                VALUES
                (@Id,@PrBillNo,@PrPurchaseId,@PrPurchaseNumber,@PrPurchaseName,@PrPurchaser,@PrPurchaserNum,
                 @PrRqty,@PrAqty,@PrSqty,@IcitemId,@IcitemName,@PrSsendDate,@PrSarriveDate,@PrUnit,@State,@PrType,@CurrencyType,
                 @CreateBy,@CreateByName,@CreateTime,@UpdateBy,@UpdateByName,@UpdateTime,@TenantId,@FactoryId,@OrgId,@IsDeleted,@CompanyId,@IsRequireGoods,@SupplierType)
                """,
                new SugarParameter("@Id", newId),
                new SugarParameter("@PrBillNo", billNo),
                new SugarParameter("@PrPurchaseId", input.PrPurchaseId!.Value),
                new SugarParameter("@PrPurchaseNumber", input.PrPurchaseNumber?.Trim()),
                new SugarParameter("@PrPurchaseName", input.PrPurchaseName?.Trim()),
                new SugarParameter("@PrPurchaser", input.PrPurchaser?.Trim()),
                new SugarParameter("@PrPurchaserNum", input.PrPurchaserNum?.Trim()),
                new SugarParameter("@PrRqty", aqty),
                new SugarParameter("@PrAqty", aqty),
                new SugarParameter("@PrSqty", aqty),
                new SugarParameter("@IcitemId", icitemId),
                new SugarParameter("@IcitemName", input.IcitemName?.Trim()),
                new SugarParameter("@PrSsendDate", prSsendDate),
                new SugarParameter("@PrSarriveDate", prSarriveDate),
                new SugarParameter("@PrUnit", input.PrUnit?.Trim()),
                new SugarParameter("@State", state),
                new SugarParameter("@PrType", prType),
                new SugarParameter("@CurrencyType", currency),
                new SugarParameter("@CreateBy", _userManager.UserId),
                new SugarParameter("@CreateByName", _userManager.Account),
                new SugarParameter("@CreateTime", now),
                new SugarParameter("@UpdateBy", _userManager.UserId),
                new SugarParameter("@UpdateByName", _userManager.Account),
                new SugarParameter("@UpdateTime", now),
                new SugarParameter("@TenantId", _userManager.TenantId),
                new SugarParameter("@FactoryId", _userManager.OrgId),
                new SugarParameter("@OrgId", _userManager.OrgId),
                new SugarParameter("@IsDeleted", 0),
                new SugarParameter("@CompanyId", companyId),
                new SugarParameter("@IsRequireGoods", input.IsRequireGoods ?? 0),
                new SugarParameter("@SupplierType", input.SupplierType?.Trim())
            );

            return new { id = newId, prBillNo = billNo, message = "新增成功" };
        }
        else
        {
            var tenantId = _userManager.TenantId;
            var tenantSuffix = tenantId > 0 ? " AND tenant_id=@TenantId" : string.Empty;
            var existPars = new List<SugarParameter> { new("@Id", input.Id.Value) };
            if (tenantId > 0) existPars.Add(new SugarParameter("@TenantId", tenantId));
            var exist = await _db.Ado.GetIntAsync(
                $"SELECT COUNT(1) FROM srm_pr_main WHERE Id=@Id AND IFNULL(IsDeleted,0)=0{tenantSuffix}",
                existPars);
            if (exist <= 0) throw Oops.Oh("记录不存在");

            await _db.Ado.ExecuteCommandAsync(
                $"""
                UPDATE srm_pr_main
                SET
                    pr_purchaseid=@PrPurchaseId,
                    pr_purchasenumber=@PrPurchaseNumber,
                    pr_purchasename=@PrPurchaseName,
                    pr_purchaser=@PrPurchaser,
                    pr_purchaser_num=@PrPurchaserNum,
                    pr_rqty=@PrRqty,
                    pr_aqty=@PrAqty,
                    pr_sqty=@PrSqty,
                    icitem_id=@IcitemId,
                    icitem_name=@IcitemName,
                    pr_ssend_date=@PrSsendDate,
                    pr_sarrive_date=@PrSarriveDate,
                    pr_unit=@PrUnit,
                    state=@State,
                    pr_type=@PrType,
                    currencytype=@CurrencyType,
                    update_by=@UpdateBy,
                    update_by_name=@UpdateByName,
                    update_time=@UpdateTime,
                    IsRequireGoods=@IsRequireGoods,
                    supplier_type=@SupplierType
                WHERE Id=@Id AND IFNULL(IsDeleted,0)=0{tenantSuffix}
                """,
                new SugarParameter("@Id", input.Id.Value),
                new SugarParameter("@PrPurchaseId", input.PrPurchaseId!.Value),
                new SugarParameter("@PrPurchaseNumber", input.PrPurchaseNumber?.Trim()),
                new SugarParameter("@PrPurchaseName", input.PrPurchaseName?.Trim()),
                new SugarParameter("@PrPurchaser", input.PrPurchaser?.Trim()),
                new SugarParameter("@PrPurchaserNum", input.PrPurchaserNum?.Trim()),
                new SugarParameter("@PrRqty", aqty),
                new SugarParameter("@PrAqty", aqty),
                new SugarParameter("@PrSqty", aqty),
                new SugarParameter("@IcitemId", icitemId),
                new SugarParameter("@IcitemName", input.IcitemName?.Trim()),
                new SugarParameter("@PrSsendDate", prSsendDate),
                new SugarParameter("@PrSarriveDate", prSarriveDate),
                new SugarParameter("@PrUnit", input.PrUnit?.Trim()),
                new SugarParameter("@State", state),
                new SugarParameter("@PrType", prType),
                new SugarParameter("@CurrencyType", currency),
                new SugarParameter("@UpdateBy", _userManager.UserId),
                new SugarParameter("@UpdateByName", _userManager.Account),
                new SugarParameter("@UpdateTime", now),
                new SugarParameter("@IsRequireGoods", input.IsRequireGoods ?? 0),
                new SugarParameter("@SupplierType", input.SupplierType?.Trim()),
                new SugarParameter("@TenantId", tenantId)
            );

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

    [DisplayName("删除物料采购申请")]
    [HttpPost("purchase-request/delete/{id:long}")]
    public async Task<object> Delete(long id)
    {
        var tenantId = _userManager.TenantId;
        var tenantSuffix = tenantId > 0 ? " AND tenant_id=@TenantId" : string.Empty;
        var pars = new List<SugarParameter>
        {
            new("@Id", id),
            new("@UpdateBy", _userManager.UserId),
            new("@UpdateByName", _userManager.Account),
            new("@UpdateTime", DateTime.Now)
        };
        if (tenantId > 0) pars.Add(new SugarParameter("@TenantId", tenantId));

        var affected = await _db.Ado.ExecuteCommandAsync(
            $"""
            UPDATE srm_pr_main
            SET IsDeleted=1,state=0,update_by=@UpdateBy,update_by_name=@UpdateByName,update_time=@UpdateTime
            WHERE Id=@Id AND IFNULL(IsDeleted,0)=0{tenantSuffix}
            """,
            pars);

        if (affected <= 0) throw Oops.Oh("记录不存在");
        return new { message = "删除成功" };
    }

    private static string BuildOrderBy(string? sortField, string? sortOrder)
    {
        var dir = string.Equals(sortOrder, "asc", StringComparison.OrdinalIgnoreCase) ? "ASC" : "DESC";
        return sortField?.ToLowerInvariant() switch
        {
            "prbillno" => $"pm.pr_billno {dir}",
            "purord" => $"pod.PurOrd {dir}",
            "prtype" => $"pm.pr_type {dir}",
            "state" => $"pm.state {dir}",
            "number" => $"ic.number {dir}",
            "name" => $"ic.name {dir}",
            "prpurchasename" => $"pm.pr_purchasename {dir}",
            "prsqty" => $"pm.pr_sqty {dir}",
            "praqty" => $"pm.pr_aqty {dir}",
            "stockqty" => $"its.sqty {dir}",
            "prssenddate" => $"pm.pr_ssend_date {dir}",
            "prsarrivedate" => $"pm.pr_sarrive_date {dir}",
            "createtime" => $"pm.create_time {dir}",
            _ => "pm.create_time DESC"
        };
    }

    private static DateTime? ParseDate(string? value) => DateTime.TryParse(value, out var dt) ? dt : null;

    private static string GenerateBillNo()
    {
        return $"PR{DateTime.Now:yyyyMMddHHmmssfff}";
    }

    private sealed class PurchaseRequestListRow
    {
        public long Id { get; set; }
        public string? PrBillNo { get; set; }
        public string? PurOrd { get; set; }
        public int? PrType { get; set; }
        public string? SupplierType { get; set; }
        public int? IsRequireGoods { get; set; }
        public int? State { get; set; }
        public string? Number { get; set; }
        public string? Name { get; set; }
        public string? Model { get; set; }
        public string? PrUnit { get; set; }
        public string? PrPurchaseNumber { get; set; }
        public string? PrPurchaseName { get; set; }
        public decimal? PrSqty { get; set; }
        public decimal? PrAqty { get; set; }
        public decimal? StockQty { get; set; }
        public DateTime? PrSsendDate { get; set; }
        public DateTime? PrSarriveDate { get; set; }
        public string? PrPurchaser { get; set; }
        public DateTime? CreateTime { get; set; }
    }

    private sealed class PurchaseRequestDetailRow
    {
        public long Id { get; set; }
        public string? PrBillNo { get; set; }
        public string? IcitemId { get; set; }
        public string? IcitemName { get; set; }
        public string? PrUnit { get; set; }
        public string? PrPurchaseNumber { get; set; }
        public string? PrPurchaseName { get; set; }
        public string? SupplierType { get; set; }
        public decimal? PrAqty { get; set; }
        public DateTime? PrSsendDate { get; set; }
        public DateTime? PrSarriveDate { get; set; }
        public int? PrType { get; set; }
        public int? State { get; set; }
        public long? PrPurchaseId { get; set; }
        public int? IsRequireGoods { get; set; }
        public string? PrPurchaserNum { get; set; }
        public string? PrPurchaser { get; set; }
        public long? CurrencyType { get; set; }
    }
}