using System.Data;
using System.Data.SqlClient;
using Masuit.Tools;
using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
using SqlSugar;

namespace NewPdaSqlServer.service.Wom;

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)
    {
        return getDaa001(query);
    }

    public ProductionPickDto ScanCode(WarehouseQuery query)
    {
        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            if (query.userName.IsNullOrEmpty()) throw new Exception("用户名不允许为空");
            if (query.daa001.IsNullOrEmpty()) throw new Exception("领料单号不允许为空");
            if (query.barcode.IsNullOrEmpty()) throw new Exception("条码不允许为空");

            using (var cmd = new SqlCommand("[prc_pda_SCLL]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 300),
                        new("@outSum", SqlDbType.NVarChar, 300),
                        new("@barcode_num", SqlDbType.NVarChar, 300),
                        new("@split_num", SqlDbType.NVarChar, 300),
                        new("@c_User", query.userName),
                        new("@p_biLL_no", query.daa001),
                        new("@p_item_barcode", query.barcode)
                    };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    parameters[2].Direction = ParameterDirection.Output;
                    parameters[3].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                    _strMsg = parameters[0].Value.ToString();
                    _intSum = parameters[1].Value.ToString();

                    var barcodeNum = parameters[2].Value.ToString();
                    var splitNum = parameters[3].Value.ToString();

                    var result = Convert.ToInt32(_intSum);
                    if (result <= 0) throw new Exception(_strMsg);

                    var dto = new ProductionPickDto
                    {
                        daa001 = query.daa001,
                        barcodeNum = barcodeNum,
                        splitNum = splitNum,
                        barcode = query.barcode
                    };

                    return dto;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    //prC_pda_SCLL_CF
    public ProductionPickDto ScanCodeCF(WarehouseQuery query)
    {
        if (query.userName.IsNullOrEmpty()) throw new Exception("用户名不允许为空");
        if (query.daa001.IsNullOrEmpty()) throw new Exception("领料单号不允许为空");
        if (query.barcode.IsNullOrEmpty()) throw new Exception("条码不允许为空");

        if (query.Num is null or 0) throw new Exception("条码拆分数不允许为空或者为0");

        var _strMsg = "";
        var _intSum = "";
        using (var conn = new SqlConnection(DbHelperSQL.strConn))
        {
            using (var cmd = new SqlCommand("[prc_pda_SCLL_CF]", conn))
            {
                try
                {
                    conn.Open();
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter[] parameters =
                    {
                        new("@outMsg", SqlDbType.NVarChar, 300),
                        new("@outSum", SqlDbType.NVarChar, 300),
                        new("@c_User", query.userName),
                        new("@p_biLL_no", query.daa001),
                        new("@p_item_barcode", query.barcode),
                        new("@num", query.Num)
                    };
                    parameters[0].Direction = ParameterDirection.Output;
                    parameters[1].Direction = ParameterDirection.Output;
                    foreach (var parameter in parameters)
                        cmd.Parameters.Add(parameter);
                    cmd.ExecuteNonQuery();
                    _strMsg = parameters[0].Value.ToString();
                    _intSum = parameters[1].Value.ToString();


                    var result = Convert.ToInt32(_intSum);
                    if (result <= 0) throw new Exception(_strMsg);

                    var dto = new ProductionPickDto
                    {
                        daa001 = query.daa001,
                        barcode = query.barcode
                    };

                    return dto;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }

    private ProductionPickDto getDaa001(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
            {
                a.Daa001, a.CaaGuid
            }).First();

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

        var womdabs = Db.Queryable<Womdaa, Womdab, MesItems, Womcab>(
                (a, b, c, d) =>
                    new JoinQueryInfos(
                        JoinType.Left,
                        a.Guid == b.DaaGuid,
                        JoinType.Inner,
                        c.Id.ToString() ==
                        b.Dab003,
                        JoinType.Inner, b.Erpid == d.Erpid
                    ))
            .Where((a, b, c, d) =>
                a.Daa001 == query.daa001 && d.IssueType == "1")
            .Select((a, b, c, d) => 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();

        var list = womdabs.Where(s => s.wNum > 0).ToList();

        var mesInvItemOutCDetailsList = Db
            .Queryable<MesInvItemOutCDetails, MesItems, MesDepots>
            ((a, b, c) =>
                new JoinQueryInfos(
                    JoinType.Inner, a.ItemId == b.Id,
                    JoinType.Inner, c.DepotId == a.DepotId
                ))
            .Where((a, b, c) => a.WorkNo == query.daa001)
            .Select((a, b, c) => new MesInvItemOutCDetails
            {
                ItemName = b.ItemName,
                ItemNo = b.ItemNo,
                ItemId = a.ItemId,
                DepotId = a.DepotId,
                WorkNo = a.WorkNo,
                DepotName = c.DepotName,
                Quantity = a.Quantity
            })
            .ToList();

        var womcaa = Db.Queryable<Womcaa>().Where(s => s.Guid == womdaa.CaaGuid)
            .First();

        if (womcaa == null)
        {
            throw new Exception("生产任务单不存在");
        }

        var dto = new ProductionPickDto
        {
            daa001 = womdaa.Daa001,
            PlanNo = womcaa.Caa020,
            totals = womdabs,
            daisao = list,
            yisao = mesInvItemOutCDetailsList
        };

        return dto;
    }
}