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

namespace MES.Service.service.Warehouse;

public class MesInvItemBarcodesManager : Repository<MesInvItemBarcodes>
{
    //当前类已经继承了 Repository 增、删、查、改的方法

    //这里面写的代码不会给覆盖,如果要重新生成请删除 MesInvItemBarcodesManager.cs

    #region 生产领料条码验证

    //生产领料条码验证合理性
    private bool PdaProdPickItemCkBar(string cBarcode, string cDaa001)
    {
        try
        {
            // 1. 检查条码是否为空
            if (string.IsNullOrEmpty(cBarcode))
            {
                throw new Exception("请输入条码！");
            }

            // 2. 检查条码是否存在于 MesInvItemBarcodes 表中
            int tempNum = Db.Queryable<MesInvItemBarcodes>()
                .Where(it => it.ItemBarcode == cBarcode)
                .Count();
            if (tempNum == 0)
            {
                throw new Exception("条码不存在！");
            }

            // 3. 检查条码是否存在于 MesInvItemStocks 表中
            tempNum = Db.Queryable<MesInvItemStocks>()
                .Where(it => it.ItemBarcode == cBarcode)
                .Count();
            if (tempNum == 0)
            {
                throw new Exception("库存中无此条码！");
            }

            // 4. 获取 MesInvItemBarcodes 的数据
            var mesInvItemBarcodes = Db.Queryable<MesInvItemBarcodes>()
                .Where(it => it.ItemBarcode == cBarcode)
                .First();
            if (mesInvItemBarcodes == null)
            {
                throw new Exception("条码不存在！");
            }

            // 5. 检查条码数量是否为 0
            if (mesInvItemBarcodes.Quantity == 0)
            {
                throw new Exception("此条码数量为0！");
            }

            // 6. 检查条码状态
            if (mesInvItemBarcodes.Barcodestatus == 1 ||
                mesInvItemBarcodes.Hbdytm == 1)
            {
                throw new Exception("无效条码！");
            }

            // 7. 检查条码是否被冻结
            if (mesInvItemBarcodes.Visable == 1)
            {
                throw new Exception("条码冻结！");
            }

            // 8. 检查条码是否作废
            if (mesInvItemBarcodes.Location == 2)
            {
                throw new Exception("条码作废！");
            }

            // 9. 检查投料单是否需要此物料
            tempNum = Db.Queryable<Womdab>()
                .Where(it =>
                    it.Dab001 == cDaa001 && it.Dab003 ==
                    mesInvItemBarcodes.ItemId.ToString())
                .Count();
            if (tempNum == 0)
            {
                throw new Exception("投料单不需要此物料！");
            }

            string sql =
                "SELECT COUNT(1) FROM WOMDAB WHERE DAB001='" + cDaa001 +
                "' AND  DAB003='" + mesInvItemBarcodes.ItemId +
                "' AND DAB017=(SELECT DEPOTS_CODE    FROM MES_INV_ITEM_STOCKS WHERE ITEM_BARCODE='" +
                cBarcode + "')";

            // 10. 检查发料仓库是否正确
            tempNum = Db.Ado.SqlQuerySingle<int>(sql);
            if (tempNum == 0)
            {
                throw new Exception("发料仓库有误！");
            }

            // 11. 成功，返回结果
            return true;
        }
        catch (Exception ex)
        {
            // 捕获异常并重新抛出
            throw new Exception($"报错: {ex.Message}");
        }
    }

    public decimal? GetBarCodesQuan(WarehouseQuery query)
    {
        if (!PdaProdPickItemCkBar(query.barcode, query.daa001))
        {
            return null;
        }

        var invItemBarcodes = Db.Queryable<MesInvItemBarcodes>()
            .Where(s => s.ItemBarcode == query.barcode).First();

        if (invItemBarcodes == null)
        {
            throw new Exception("条码不存在！");
        }

        return invItemBarcodes.Quantity;
    }

    //生产领料保存
    public string PrcRfPdaProdPickItem3(WarehouseQuery query)
    {
        var (factory, company) = UserUtil.GetFactory(query.userName);
        var param = new ProdPickParams
        {
            CUser = query.userName,
            CBarcode = query.barcode,
            CDaa001 = query.daa001,
            CNum = query.Num,
            PiFactory = factory,
            PiCompany = company
        };

        // 检查发料数量是否合法
        if (param.CNum <= 0)
        {
            throw new Exception("发料数量不能小于等于0！");
        }

        // 检查条码是否存在
        param.CInvItemBarcodes = Db.Queryable<MesInvItemBarcodes>()
            .Where(it => it.ItemBarcode == param.CBarcode)
            .First();
        if (param.CInvItemBarcodes == null)
        {
            throw new Exception("条码不存在！");
        }

        // 检查条码是否已入库
        param.CInvItemStocks = Db.Queryable<MesInvItemStocks>()
            .Where(it => it.ItemBarcode == param.CBarcode)
            .First();
        if (param.CInvItemStocks == null)
        {
            throw new Exception("条码未入库！");
        }

        // 检查工单号是否存在
        param.CWomdaa = Db.Queryable<Womdaa>()
            .Where(it => it.Daa001 == param.CDaa001)
            .First();
        if (param.CWomdaa == null)
        {
            throw new Exception("不存在此工单！");
        }

        // 检查发料数量是否超出条码数量
        if (param.CNum > param.CInvItemBarcodes.Quantity)
        {
            throw new Exception("发料数量不能大于条码数量！");
        }

        // 检查条码合法性
        if (!PdaProdPickItemCkBar(param.CBarcode, param.CDaa001))
        {
            return null;
        }

        // 计算剩余可发数量
        decimal? tempNum = CalculateRemainingQuantity(param.CDaa001,
            param.CInvItemBarcodes.ItemId);
        if (param.CNum > tempNum)
        {
            throw new Exception("发料数量不能大于对应物料的未发料数量！");
        }

        // 工单部门名称
        param.CDepart = Db.Queryable<SysDepartment>()
            .Where(dep => dep.Id.ToString() == param.CWomdaa.Daa013)
            .Select(dep => dep.Departmentname)
            .First();

        // 生成新条码
        param.NewBarcode = GenerateNewBarcode(param.CBarcode);

        UseTransaction(db =>
        {
            // 对出库单的操作
            var (outId, outBill) = HandleInventoryOut(db, param);
            param.COutId = outId;
            param.OutBill = outBill;

            // 更新条码数据
            UpdateBarcodeData(db, param);

            // 生成打印条码并插入到 mes_rf_prnbarcode
            GenerateAndInsertPrintBarcode(db, param);

            return 1;
        });

        return "001";
    }

    private void UpdateBarcodeData(SqlSugarScope db, ProdPickParams param)
    {
        // 更新条码数据
        db.Updateable<MesInvItemBarcodes>()
            .SetColumns(it => new MesInvItemBarcodes
                { Oldqty = it.Quantity, Quantity = it.Quantity - param.CNum })
            .Where(it => it.ItemBarcode == param.CBarcode)
            .ExecuteCommand();

        // 插入新的条码记录
        db.Insertable(new MesInvItemBarcodes
        {
            ItemNo = param.CInvItemBarcodes.ItemNo,
            ItemId = param.CInvItemBarcodes.ItemId,
            CreateBy = param.CUser,
            CreateDate = DateTime.Now,
            TaskNo = param.CDaa001,
            WorkNo = param.CWomdaa.Daa021,
            SuppNo = param.CInvItemBarcodes.SuppNo,
            Mblnr = param.CInvItemBarcodes.Mblnr,
            Zeile = param.CInvItemBarcodes.Zeile,
            Factory = param.CInvItemBarcodes.Factory,
            Company = param.CInvItemBarcodes.Company,
            EbelnK3id = param.CInvItemBarcodes.EbelnK3id,
            LineK3id = param.CInvItemBarcodes.LineK3id,
            Quantity = param.CNum,
            Oldqty = param.CNum,
            ItemBarcode = param.NewBarcode,
            Unit = param.CInvItemBarcodes.Unit,
            LotDate = param.CInvItemBarcodes.LotDate,
            Memo = "生产领料",
            ItemSname = param.CInvItemBarcodes.ItemSname,
            TrLotno = param.CInvItemBarcodes.TrLotno,
            BillNo = param.CInvItemBarcodes.BillNo,
            InsDate = param.CInvItemBarcodes.InsDate,
            WorkLine = param.CInvItemBarcodes.WorkLine,
            ComeFlg = 6,
            OldItemBarcode = param.CBarcode
        }).ExecuteCommand();

        // 更新条码库存
        db.Updateable<MesInvItemStocks>()
            .SetColumns(it => new MesInvItemStocks
                { Quantity = it.Quantity - param.CNum })
            .Where(it => it.ItemBarcode == param.CBarcode)
            .ExecuteCommand();

        // 插入出库条码明细表
        db.Insertable(new MesInvItemOutCDetails
        {
            ItemOutId = param.COutId,
            ItemNo = param.CInvItemBarcodes.ItemNo,
            ItemId = param.CInvItemBarcodes.ItemId,
            CreateBy = param.CUser,
            CreateDate = DateTime.Now,
            DepotCode = param.CInvItemStocks.DepotsCode,
            DepotSectionCode = param.CInvItemStocks.DepotSectionsCode,
            TaskNo = param.CDaa001,
            WorkNo = param.CWomdaa.Daa021,
            SuppNo = param.CInvItemBarcodes.SuppNo,
            PbillNo = param.OutBill,
            Factory = param.PiFactory,
            Company = param.PiCompany,
            EbelnK3id = param.CInvItemStocks.EbelnK3id,
            LineK3id = param.CInvItemStocks.LineK3id,
            Quantity = param.CNum,
            ItemBarcode = param.NewBarcode
        }).ExecuteCommand();

        // 插入到 MesInvBusiness2 表
        db.Insertable(new MesInvBusiness2
        {
            Status = 1,
            BillTypeId = 200,
            ItemNo = param.CInvItemBarcodes.ItemNo,
            ItemId = param.CInvItemBarcodes.ItemId,
            CreateBy = param.CUser,
            CreateDate = DateTime.Now,
            TaskNo = param.CDaa001,
            WorkNo = param.CWomdaa.Daa021,
            SuppNo = param.CInvItemBarcodes.SuppNo,
            Factory = param.PiFactory,
            Company = param.PiCompany,
            EbelnK3id = param.CInvItemStocks.EbelnK3id,
            LineK3id = param.CInvItemStocks.LineK3id,
            Quantity = param.CNum,
            ItemBarcode = param.CBarcode
        }).ExecuteCommand();
    }

    private void GenerateAndInsertPrintBarcode(SqlSugarScope db,
        ProdPickParams param)
    {
        string cSupName = null;
        if (!string.IsNullOrEmpty(param.CInvItemBarcodes.SuppNo))
        {
            cSupName = db.Queryable<MesSupplier>()
                .Where(it => it.SuppNo == param.CInvItemBarcodes.SuppNo)
                .Select(it => it.SuppName)
                .First();
        }

        int cId =
            db.Ado.GetInt(
                "SELECT F_GETSEQNEXTVALUE('MES_RF_PRNBARCODE') FROM dual");

        // 插入打印条码记录
        db.Insertable(new MesRfPrnbarcode
        {
            Id = cId,
            Forder = "1",
            Fno = "1",
            CreateDate = DateTime.Now,
            Strp1 = param.CBarcode,
            Strp2 = cSupName ?? " ",
            Strp3 = param.CInvItemBarcodes.ItemNo,
            Strp4 = GetItemName(param.CInvItemBarcodes.ItemId) ?? " ",
            Strp5 =
                $"{param.CInvItemBarcodes.Quantity - param.CNum} {GetUnitName(param.CInvItemBarcodes.Unit)}",
            Strp6 = DateTime.Now.ToString("yyyy-MM-dd"),
            Strp7 = GetItemModel(param.CInvItemBarcodes.ItemId) ?? " ",
            Strp8 = param.CBarcode,
            Strp9 = GetItemModel(param.CInvItemBarcodes.ItemId) ?? " ",
            Strp10 = param.CBarcode,
            Strp11 = param.CBarcode,
            Strp12 = param.CBarcode
        }).ExecuteCommand();

        db.Insertable(new MesRfPrnbarcode
        {
            Id = cId,
            Forder = "1",
            Fno = "1",
            CreateDate = DateTime.Now,
            Strp1 = param.NewBarcode,
            Strp2 = cSupName ?? " ",
            Strp3 = param.CInvItemBarcodes.ItemNo,
            Strp4 = GetItemName(param.CInvItemBarcodes.ItemId) ?? " ",
            Strp5 = $"{param.CNum} {GetUnitName(param.CInvItemBarcodes.Unit)}",
            Strp6 = DateTime.Now.ToString("yyyy-MM-dd"),
            Strp7 = GetItemModel(param.CInvItemBarcodes.ItemId) ?? " ",
            Strp8 = param.NewBarcode,
            Strp9 = GetItemModel(param.CInvItemBarcodes.ItemId) ?? " ",
            Strp10 = param.NewBarcode,
            Strp11 = param.NewBarcode,
            Strp12 = param.NewBarcode
        }).ExecuteCommand();
    }

    private (int OutId, string OutBill) HandleInventoryOut(SqlSugarScope db,
        ProdPickParams param)
    {
        // 处理出库单的插入或更新逻辑
        var tempNum = db.Queryable<MesInvItemOuts>()
            .Where(it => it.TaskNo == param.CDaa001 && it.Status == 0)
            .Count();

        int cOutId = 0;
        string cOutBill;

        if (tempNum == 0)
        {
            string sql =
                "SELECT COUNT(1) FROM MES_INV_ITEM_OUTS WHERE TRUNC(CREATE_DATE)=TRUNC(SYSDATE);";
            int todayCount = db.Ado.SqlQuerySingle<int>(sql);

            cOutBill = $"P{DateTime.Now:yyyyMMdd}{(todayCount + 1):D4}";

            // 插入新的出库单
            db.Insertable(new MesInvItemOuts
            {
                ItemOutNo = cOutBill,
                TaskNo = param.CDaa001,
                Status = 0,
                CreateBy = param.CUser,
                CreateDate = DateTime.Now,
                WorkNo = param.CWomdaa.Daa021,
                OutType = "生产领料",
                BoardItem = param.CWomdaa.Daa002,
                PbillNo = param.CDaa001,
                BillTypeId = 200,
                TransactionNo = 201,
                Company = param.PiCompany,
                Factory = param.PiFactory,
                Remark = "生产领料",
                DepotCode = param.CInvItemStocks.DepotsCode,
                OutDate = DateTime.Now,
                OutPart = param.CDepart
            }).ExecuteCommand();
        }
        else
        {
            // 获取现有出库单号
            var outItem = db.Queryable<MesInvItemOuts>()
                .Where(it => it.TaskNo == param.CDaa001 && it.Status == 0)
                .Select(it => new { it.ItemOutNo, it.Id })
                .First();

            cOutBill = outItem.ItemOutNo;
            cOutId = Convert.ToInt32(outItem.Id);
        }

        // 插入或更新从表
        InsertOrUpdateOutItems(db, cOutId, param);

        return (cOutId, cOutBill);
    }

    private void InsertOrUpdateOutItems(SqlSugarScope db, int cOutId,
        ProdPickParams param)
    {
        var tempNum = db.Queryable<MesInvItemOutItems>()
            .Where(it =>
                it.ItemId == param.CInvItemBarcodes.ItemId &&
                it.ItemOutId == cOutId)
            .Count();

        decimal? cNumTemp = param.CNum;

        string sql =
            "SELECT B.Id, NVL(A.YF, 0) yfl, B.DAB006 - NVL(A.YF, 0) Qty FROM WOMDAB B LEFT JOIN (SELECT SUM(QUANTITY) YF, ITEM_DABID FROM MES_INV_ITEM_OUT_ITEMS                GROUP BY ITEM_DABID) A ON A.ITEM_DABID = B.ID WHERE DAB003 = '" +
            param.CInvItemBarcodes.ItemId + "' AND DAB001 = '" + param.CDaa001 +
            "' ORDER BY TO_NUMBER(DAB002)";

        var womdabList = db.Ado.SqlQuery<DabResult>(sql);

        foreach (var item in womdabList)
        {
            if (item.Qty <= 0) continue;
            var itemDabid = item.Id;
            if (cNumTemp <= item.Qty)
            {
                if (tempNum == 0)
                {
                    // 插入新的发料记录
                    db.Insertable(new MesInvItemOutItems
                    {
                        ItemOutId = cOutId,
                        Quantity = cNumTemp,
                        ItemDabid = itemDabid,
                        ItemId = param.CInvItemBarcodes.ItemId,
                        ItemNo = param.CInvItemBarcodes.ItemNo,
                        CreateBy = param.CUser,
                        CreateDate = DateTime.Now,
                        Factory = param.PiFactory,
                        Company = param.PiCompany,
                        DepotCode = param.CInvItemStocks.DepotsCode,
                        TaskNo = param.CDaa001,
                        EbelnK3id = param.CInvItemBarcodes.EbelnK3id,
                        LineK3id = param.CInvItemBarcodes.LineK3id,
                        PbillNo = param.CDaa001,
                        WorkNo = param.CInvItemStocks.WorkNo,
                        WorkLine = param.CInvItemStocks.WorkLine,
                        DepotSectionCode =
                            param.CInvItemStocks.DepotSectionsCode
                    }).ExecuteCommand();
                }
                else
                {
                    // 更新现有发料记录的数量
                    db.Updateable<MesInvItemOutItems>()
                        .SetColumns(it => new MesInvItemOutItems
                            { Quantity = it.Quantity + cNumTemp })
                        .Where(it =>
                            it.ItemOutId == cOutId &&
                            it.ItemDabid == itemDabid)
                        .ExecuteCommand();
                }

                // 更新 WOMDAB 表的发料数量
                db.Updateable<Womdab>()
                    .SetColumns(it => new Womdab
                        { Dab007 = item.Yfl + cNumTemp })
                    .Where(it => it.Id == itemDabid)
                    .ExecuteCommand();

                break;
            }

            // 插入剩余的发料记录
            db.Insertable(new MesInvItemOutItems
            {
                ItemOutId = cOutId,
                Quantity = item.Qty,
                ItemDabid = itemDabid,
                ItemId = param.CInvItemBarcodes.ItemId,
                ItemNo = param.CInvItemBarcodes.ItemNo,
                CreateBy = param.CUser,
                CreateDate = DateTime.Now,
                Factory = param.PiFactory,
                Company = param.PiCompany,
                DepotCode = param.CInvItemStocks.DepotsCode,
                TaskNo = param.CDaa001,
                EbelnK3id = param.CInvItemBarcodes.EbelnK3id,
                LineK3id = param.CInvItemBarcodes.LineK3id,
                PbillNo = param.CDaa001,
                WorkNo = param.CInvItemStocks.WorkNo,
                WorkLine = param.CInvItemStocks.WorkLine,
                DepotSectionCode = param.CInvItemStocks.DepotSectionsCode
            }).ExecuteCommand();

            cNumTemp -= item.Qty;

            // 更新 WOMDAB 表的发料数量
            db.Updateable<Womdab>()
                .SetColumns(it => new Womdab { Dab007 = it.Dab006 })
                .Where(it => it.Id == itemDabid)
                .ExecuteCommand();
        }
    }

    private string GenerateNewBarcode(string oldBarcode)
    {
        // 生成新的条码逻辑
        int count = Db.Queryable<MesInvItemBarcodes>()
            .Where(b => b.OldItemBarcode == oldBarcode).Count();
        return $"{oldBarcode}-{count + 1}";
    }

    private decimal? CalculateRemainingQuantity(
        string cDaa001, decimal? itemId)
    {
        // 计算剩余可发数量
        var womdab = Db.Queryable<Womdab, Womdaa>((b, a) => new JoinQueryInfos(
                JoinType.Left, b.Pid == a.Id))
            .Where((b, a) =>
                a.Daa001 == cDaa001 && b.Dab003 == itemId.ToString())
            .Select(b => new
            {
                TotalQuantity = SqlFunc.AggregateMax(b.Dab006),
                IssuedQuantity = SqlFunc.AggregateMax(b.Dab007)
            })
            .First();

        return womdab.TotalQuantity - womdab.IssuedQuantity;
    }

    private static string GetItemName(decimal? pId)
    {
        try
        {
            if (pId == null)
            {
                return null;
            }

            // 查询物料名称
            string itemName = Db.Queryable<MesItems>()
                .Where(it => it.Id == pId)
                .Select(it => it.ItemName)
                .First();

            return itemName;
        }
        catch (Exception)
        {
            throw new Exception("未找到物料名称，物料id号为" + pId);
        }
    }

    private string GetUnitName(string? pId)
    {
        try
        {
            if (string.IsNullOrEmpty(pId))
            {
                return null;
            }

            // 查询单位名称是否存在
            int tempNum = Db.Queryable<MesUnit>()
                .Where(it => it.Id.ToString() == pId)
                .Count();

            string unitName;

            if (tempNum != 0)
            {
                // 如果在 MES_UNIT 表中找到单位名称
                unitName = Db.Queryable<MesUnit>()
                    .Where(it => it.Id.ToString() == pId)
                    .Select(it => it.Fname)
                    .First();
            }
            else
            {
                // 如果在 MES_UNIT 表中找不到，在 MES_ITEMS 表中查找相关联的单位名称
                unitName = Db.Queryable<MesItems, MesUnit>((i, u) =>
                        new JoinQueryInfos(
                            JoinType.Left, i.ItemUnit == u.Id.ToString()))
                    .Where((i, u) => i.Id.ToString() == pId)
                    .Select((i, u) => u.Fname)
                    .First();
            }

            return unitName;
        }
        catch (Exception)
        {
            throw new Exception("单位名称未找到，物料id号为" + pId);
        }
    }

    private string GetItemModel(decimal? pId)
    {
        try
        {
            if (pId == null)
            {
                return null;
            }

            // 查询物料规格
            string itemModel = Db.Queryable<MesItems>()
                .Where(it => it.Id == pId)
                .Select(it => it.ItemModel)
                .First();

            return itemModel;
        }
        catch (Exception)
        {
            throw new Exception("未找到物料型号，物料id号为" + pId);
        }
    }

    #endregion
}

public class ProdPickParams
{
    public string? CUser { get; set; }
    public string? CBarcode { get; set; }
    public string? CDaa001 { get; set; }
    public decimal? CNum { get; set; }
    public MesInvItemBarcodes? CInvItemBarcodes { get; set; }
    public MesInvItemStocks? CInvItemStocks { get; set; }
    public Womdaa? CWomdaa { get; set; }
    public string? NewBarcode { get; set; }
    public string? PiCompany { get; set; }
    public string? PiFactory { get; set; }
    public string? CDepart { get; set; }
    public int? COutId { get; set; }
    public string? OutBill { get; set; }
}

public class DabResult
{
    public decimal? Id { get; set; }
    public decimal? Yfl { get; set; }
    public decimal? Qty { get; set; }
}