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 MesInvItemMovesManager : Repository<MesInvItemMoves>
{
    //当前类已经继承了 Repository 增、删、查、改的方法

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

    public List<MesInvItemMoves> GetBillNo(WarehouseQuery query)
    {
        query.Factory = "1000";
        query.Company = "1000";

        return Db.Queryable<MesInvItemMoves>()
            .Where(it =>
                SqlFunc.IsNull(it.Status, 0) == 1 &&
                SqlFunc.IsNull(it.Ts, 0) != 1)
            .Where(it =>
                it.Factory == query.Factory && it.Company == query.Company)
            .OrderBy(it => it.Id, OrderByType.Desc).ToList();
    }


    public OutItemDto ScanMoveBarcode(WarehouseQuery query)
    {
        var BillTypeId = 300;
        var TransctionNo = 301;
        query.Factory = "1000";
        query.Company = "1000";

        if (string.IsNullOrEmpty(query.billNo)) throw new Exception("请选单据号！");

        var mesInvItemStocks = Db.Queryable<MesInvItemStocks>()
            .Where(s => s.ItemBarcode == query.barcode &&
                        s.Quantity > 0 && s.DepotsCode != null).First();
        if (mesInvItemStocks == null)
            throw new Exception("库存中无此条码，请核对！" + query.barcode);

        var mesInvItemMoves = Db.Queryable<MesInvItemMoves>()
            .Where(d => d.TransactionNo == TransctionNo
                        && SqlFunc.IsNull(d.Status, 0) == 1).First();

        if (mesInvItemMoves == null)
            throw new Exception("出库单 " + query.billNo + "不存在，请确认！");

        var count = Db.Queryable<MesInvItemMoves, MesInvItemMovesCDetails>(
                (a, b) =>
                    new JoinQueryInfos(JoinType.Inner, a.Id == b.ItemMoveId))
            .Where((a, b) => b.ItemBarcode == query.barcode
                             && a.TransactionNo == TransctionNo &&
                             SqlFunc.IsNull(a.Status, 0) == 0).Count();
        if (count > 0) throw new Exception("条码重复扫描，请核对！");

        count = Db.Queryable<MesInvItemMoves, MesInvItemMovesCDetails>((a, b) =>
                new JoinQueryInfos(JoinType.Inner, a.Id == b.ItemMoveId))
            .Where((a, b) => b.ItemBarcode == query.barcode
                             && a.TransactionNo == TransctionNo &&
                             SqlFunc.IsNull(b.MoveOk, 0) == 0).Count();

        if (count > 0) throw new Exception("条码重复扫描，请核对！");

        if (mesInvItemMoves.FromDepotsCode != mesInvItemStocks.DepotsCode)
            throw new Exception("条码现存仓库与申请出货仓库不一致，请核对！");

        var quantity = Db.Queryable<MesInvItemOutItems>()
            .Where(b => b.ItemNo == mesInvItemStocks.ItemNo
                        && b.ItemOutId == mesInvItemMoves.Id)
            .Sum(b => b.Quantity);

        if (quantity == null)
            throw new Exception("扫码物料非本次申请物料，请核对！" + query.barcode);

        var sum = Db.Queryable<MesInvItemMovesCDetails>()
            .Where(b => b.ItemNo == mesInvItemStocks.ItemNo
                        && b.ItemMoveId == mesInvItemMoves.Id)
            .Sum(b => b.Quantity) ?? 0;

        var sumqty = sum + (mesInvItemStocks.Quantity ?? 0);

        if (sumqty > quantity)
            throw new Exception(
                "已扫条码数量或本次扫码数量：" + sumqty + " 大于申请数量：" + quantity + "，请核对！");

        List<InventoryItem> scanOutShowDb = null;

        UseTransaction(db =>
        {
            SaveMesInvItemMovesCDetails(db, query, mesInvItemMoves,
                mesInvItemStocks);

            // Insert into mes_inv_business2
            SaveMesInvBusiness2(db, query, BillTypeId, TransctionNo,
                mesInvItemStocks, mesInvItemMoves);

            if (TransctionNo == 303)
                db.Updateable<MesInvItemStocks>()
                    .SetColumns(s =>
                        s.DepotSectionsCode == mesInvItemMoves.InvDepotsCode)
                    .SetColumns(s =>
                        s.DepotsCode == mesInvItemMoves.InvDepotsCode)
                    .Where(s => s.Id == mesInvItemStocks.Id)
                    .ExecuteCommand();
            else
                db.Updateable<MesInvItemStocks>()
                    .SetColumns(s => s.DepotSectionsCode == null)
                    .SetColumns(s => s.DepotsCode == null)
                    .Where(s => s.Id == mesInvItemStocks.Id)
                    .ExecuteCommand();

            scanOutShowDb = ScanOutShowDb(query);
            if (CollectionUtil.IsNullOrEmpty(scanOutShowDb))
                db.Updateable<MesInvItemMoves>()
                    .SetColumns(s => s.Status == 1)
                    .SetColumns(s => s.Checkdate == DateTime.Now)
                    .SetColumns(s => s.Checkuser == query.userName)
                    .Where(s => s.BillNo == query.billNo).ExecuteCommand();

            return 1;
        });

        var itemDto = new OutItemDto();
        itemDto.SumItem = scanOutShowDb;

        if (TransctionNo == 201) itemDto.Quantity = mesInvItemStocks.Quantity;

        itemDto.ItemNo = mesInvItemStocks.ItemNo;

        return itemDto;
    }

    private void SaveMesInvBusiness2(SqlSugarScope Db, WarehouseQuery query,
        int BillTypeId,
        int TransctionNo, MesInvItemStocks mesInvItemStocks,
        MesInvItemMoves mesInvItemMoves)
    {
        var executeCommand = Db.Insertable(new MesInvBusiness2
        {
            Status = 1,
            BillTypeId = BillTypeId,
            TransactionCode = TransctionNo.ToString(),
            BusinessType = 1,
            ItemBarcode = query.barcode,
            ItemNo = mesInvItemStocks.ItemNo,
            LotNo = mesInvItemStocks.LotNo,
            EpFlag = 1,
            Quantity = mesInvItemStocks.Quantity,
            FromInvDepotsCode = mesInvItemStocks.DepotsCode,
            FromInvDepotSectionsCode = mesInvItemStocks.DepotSectionsCode,
            ToInvDepotsCode = TransctionNo == 303
                ? mesInvItemMoves.InvDepotsCode
                : null,
            ToInvDepotSectionsCode = TransctionNo == 303
                ? mesInvItemMoves.InvDepotsCode
                : null,
            CreateBy = query.userName,
            CreateDate = DateTime.Now,
            LastupdateBy = query.userName,
            LastupdateDate = DateTime.Now,
            Factory = query.Factory,
            Company = query.Company,
            TaskNo = mesInvItemStocks.TaskNo,
            BillNo = query.billNo,
            WorkNo = mesInvItemStocks.WorkNo,
            WorkLine = mesInvItemStocks.WorkLine,
            SuppNo = mesInvItemStocks.SuppNo
        }).ExecuteCommand();

        if (executeCommand <= 0) throw new Exception("写入MesInvBusiness2表失败");
    }

    private void SaveMesInvItemMovesCDetails(SqlSugarScope Db,
        WarehouseQuery query,
        MesInvItemMoves mesInvItemMoves, MesInvItemStocks mesInvItemStocks)
    {
        var executeCommand = Db.Insertable(new MesInvItemMovesCDetails
        {
            ItemMoveId = mesInvItemMoves.Id,
            ItemBarcode = query.barcode,
            CItemCode = mesInvItemStocks.CItemCode,
            ItemNo = mesInvItemStocks.ItemNo,
            LotNo = mesInvItemStocks.LotNo,
            Quantity = mesInvItemStocks.Quantity,
            EpFlag = mesInvItemStocks.EpFlag,
            CreateBy = query.userName,
            CreateDate = DateTime.Now,
            LastupdateBy = query.userName,
            LastupdateDate = DateTime.Now,
            CustNo = mesInvItemStocks.CustomerNo,
            TaskNo = mesInvItemStocks.TaskNo,
            FromDepotsCode = mesInvItemStocks.DepotsCode,
            FromDepotSectionsCode = mesInvItemStocks.DepotSectionsCode,
            Factory = mesInvItemStocks.Factory,
            Company = mesInvItemStocks.Company,
            IqcStatus = mesInvItemStocks.IqcStatus,
            Fcar = mesInvItemStocks.Fcar,
            IndepDate = mesInvItemStocks.IndepDate,
            VisableSubmit = mesInvItemStocks.VisableSubmit,
            VisableSubmitBy = mesInvItemStocks.VisableSubmitBy,
            VisableSubmitDate = mesInvItemStocks.VisableSubmitDate,
            BoardStyle = mesInvItemStocks.BoardStyle,
            WorkNo = mesInvItemStocks.WorkNo,
            WorkLine = mesInvItemStocks.WorkLine,
            SuppNo = mesInvItemStocks.SuppNo
        }).ExecuteCommand();

        if (executeCommand <= 0)
            throw new Exception("写入MesInvItemMovesCDetails表失败");
    }

    //scan_out_show_DB
    public List<InventoryItem> ScanOutShowDb(WarehouseQuery query)
    {
        var BillTypeId = 300;
        var TransctionNo = 301;
        query.Factory = "1000";
        query.Company = "1000";

        if (string.IsNullOrEmpty(query.billNo)) throw new Exception("请选单据号！");

        var sql = string.Format(
            "select f_get_sections_code({0}, {1}, b.item_no)                       sections_code , s.erp_item_no , S.ITEM_MODEL , to_char(nvl(b.quantity, 0) - nvl(quantity_ok, 0), 'FM9999999990.00') flist from mes_inv_item_moves a join mes_inv_item_out_items b on b.item_out_id = a.id join mes_items s on b.item_no = s.item_no left join (select ITEM_MOVE_ID, item_no, sum(quantity) quantity_ok             from MES_INV_ITEM_MOVES_C_DETAILS group by ITEM_MOVE_ID, item_no) c                    on c.ITEM_MOVE_ID = a.id and b.item_no = c.item_no where 1 = 1 and a.bill_type_id = {2} and a.transaction_no = {3} and a.bill_no = {4} and nvl(b.quantity, 0) - nvl(quantity_ok, 0) > 0   and rownum < 500   AND A.STATUS = 1 order by f_get_sections_code({0}, {1}, b.item_no), s.erp_item_no ",
            query.Factory, query.Company, BillTypeId, TransctionNo,
            query.billNo);

        var results = Db.Ado.SqlQuery<InventoryItem>(sql);

        return results;
    }
}