using MES.Service.DB;
using MES.Service.Dto.service;
using MES.Service.Modes;
using SqlSugar;

namespace MES.Service.service;

public class WomdaaManager : Repository<Womdaa>
{
    //当前类已经继承了 Repository 增、删、查、改的方法
    //这里面写的代码不会给覆盖,如果要重新生成请删除 WomdaaManager.cs

    //获取工单号
    public List<string> GetProductionPickDaa001(WarehouseQuery query)
    {
        var sql =
            "SELECT DAA001 FROM WOMDAA A LEFT JOIN (SELECT COUNT(1) UN_NUM, PID                     FROM WOMDAB                    WHERE DAB006 > DAB007                   GROUP BY PID) B ON A.ID = B.PID WHERE DAA001 LIKE '%" +
            query.daa001 +
            "%' and DAA018 != '完工' AND DAA022 = 1 AND UN_NUM > 0 AND ROWNUM <= 10 order by a.id desc";

        return Db.Ado.SqlQuery<string>(sql);
    }

    //根据工单号返回产品型号和待领物料
    public ProductionPickDto GetItemsByDaa001(WarehouseQuery query)
    {
        if (string.IsNullOrEmpty(query.daa001)) throw new Exception("工单号为空");

        var womdaa = Db.Queryable<Womdaa, MesItems>((a, i) =>
                new JoinQueryInfos(JoinType.Left,
                    a.Daa002 == i.ItemId.ToString()))
            .Where((a, i) => a.Daa001 == query.daa001)
            .Select((a, i) => new
            {
                i.ItemModel
            }).First();

        if (womdaa?.ItemModel == null) throw new Exception("工单号不存在");

        var dto = new ProductionPickDto
        {
            ItemModel = womdaa.ItemModel
        };

        var womdabs = Db.Queryable<Womdaa, Womdab, MesItems>((a, b, c) =>
                new JoinQueryInfos(
                    JoinType.Left,
                    a.Id == b.Pid, // LEFT JOIN WOMDAB B ON A.ID = B.PID
                    JoinType.Inner,
                    c.Id.ToString() ==
                    b.Dab003 // INNER JOIN MES_ITEMS C ON C.ID = B.DAB003
                ))
            .Where((a, b, c) =>
                a.Daa001 == query.daa001) // WHERE DAA001 = 'SCDD003681-1'
            .Select((a, b, c) => new Womdab
            {
                Pid = b.Pid,
                Dab003 = b.Dab003,
                Dab006 = b.Dab006,
                Dab007 = b.Dab007,
                wNum = b.Dab006 - b.Dab007, // 计算字段 W_NUM
                ItemName = c.ItemName, // 动态字段 ITEM_NAME
                ItemNo = c.ItemNo // 动态字段 ITEM_NO
            })
            .ToList();

        dto.Womdabs = womdabs;
        return dto;
    }
}