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 MovesDto ScanInBarcode(WarehouseQuery query)
    {
        if (string.IsNullOrEmpty(query.sectionCode))
        {
            throw new Exception("请扫库位条码！");
        }

        int pBillTypeId = 300;
        int pTransctionNo = 301;

        var depotQuery = Db.Queryable<MesDepotSections, MesDepots>((a, b) =>
                new JoinQueryInfos(
                    JoinType.Inner, a.Zuid.ToString() == b.Zuid))
            .Where((a, b) => a.DepotSectionCode == query.sectionCode)
            .Select((a, b) => new { b.DepotCode })
            .First();

        if (depotQuery == null)
        {
            throw new Exception($"002[库位编码 {query.sectionCode} 不存在，请确认！");
        }

        var cDepotCode = depotQuery.DepotCode;

        // 查询调拔入库信息
        var itemMoveQuery = Db
            .Queryable<MesInvItemMoves, MesInvItemMovesCDetails>((a, b) =>
                new JoinQueryInfos(
                    JoinType.Inner, a.Id == b.ItemMoveId))
            .Where((a, b) => b.ItemBarcode == query.barcode &&
                             a.BillTypeId == pBillTypeId &&
                             a.TransactionNo == pTransctionNo &&
                             a.Status == 1 && SqlFunc.IsNull(b.MoveOk, 0) != 1)
            .Select((a, b) => new { a.Id, a.BillNo, a.InvDepotsCode })
            .First();

        if (itemMoveQuery == null)
        {
            throw new Exception("条码未做调拔出库扫码，请核对！");
        }

        var cBillNo = itemMoveQuery.BillNo;
        var iDepotCode = itemMoveQuery.InvDepotsCode;

        // 验证库区与仓库
        var depotValidationQuery = Db.Queryable<MesDepotSections, MesDepots>(
                (a, b) => new JoinQueryInfos(
                    JoinType.Inner, a.Zuid.ToString() == b.Zuid))
            .Where((a, b) => a.DepotSectionCode == query.sectionCode &&
                             b.DepotCode == iDepotCode)
            .Select((a, b) => new { b.DepotCode })
            .First();

        if (depotValidationQuery == null)
        {
            throw new Exception($"002[库位编码 {query.sectionCode} 不存在，请确认！");
        }

        if (iDepotCode != depotValidationQuery.DepotCode)
        {
            throw new Exception("实际仓库与申请调入仓库不符，请核对！");
        }

        var barcodeCount = Db.Queryable<MesInvItemMovesCDetails>()
            .Where(b =>
                b.ItemBarcode == query.barcode &&
                b.ItemMoveId == itemMoveQuery.Id && b.MoveOk == 1)
            .Count();

        if (barcodeCount > 0)
        {
            throw new Exception("条码调拔已完成，请核对！");
        }

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

        if (barcodeInfo == null)
        {
            throw new Exception("条码不存在，请核对！");
        }

        var isAudit = UseTransaction(db =>
        {
            // 更新业务、库存和条码数据
            db.Updateable<MesInvBusiness2>()
                .SetColumns(b => new MesInvBusiness2
                {
                    ToInvDepotsCode = cDepotCode,
                    ToInvDepotSectionsCode = query.sectionCode
                })
                .Where(b =>
                    b.BillNo == cBillNo && b.BillTypeId == pBillTypeId &&
                    b.TransactionCode == pTransctionNo.ToString() &&
                    b.ItemBarcode == query.barcode)
                .ExecuteCommand();

            db.Updateable<MesInvItemStocks>()
                .SetColumns(b => new MesInvItemStocks
                {
                    DepotsCode = cDepotCode,
                    DepotSectionsCode = query.sectionCode
                })
                .Where(b => b.ItemBarcode == query.barcode)
                .ExecuteCommand();

            db.Updateable<MesInvItemMovesCDetails>()
                .SetColumns(b => new MesInvItemMovesCDetails
                {
                    MoveOk = 1,
                    InvDepotsCode = cDepotCode,
                    InvDepotSectionsCode = query.sectionCode
                })
                .Where(b =>
                    b.ItemBarcode == query.barcode &&
                    b.ItemMoveId == itemMoveQuery.Id)
                .ExecuteCommand();

            // 检查是否所有条码已扫码移库
            var totalQuantity = db.Queryable<MesInvItemOutItems>()
                .Where(a => a.ItemOutId == itemMoveQuery.Id)
                .Sum(a => SqlFunc.IsNull(a.Quantity, 0));

            var scannedQuantity = db.Queryable<MesInvItemMovesCDetails>()
                .Where(a => a.ItemMoveId == itemMoveQuery.Id && a.MoveOk == 1)
                .Sum(a => SqlFunc.IsNull(a.Quantity, 0));

            if (totalQuantity == scannedQuantity)
            {
                return 1;
            }

            return 0;
        });

        MovesDto dto = new MovesDto();
        dto.InAudit = isAudit == 1;
        dto.BarcodesInfo = barcodeInfo;
        return dto;
    }


    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 List<MesInvItemMoves> GetPage(WarehouseQuery query)
    {
        return Db.Queryable<MesInvItemMoves>()
            .WhereIF(!string.IsNullOrEmpty(query.billNo),
                s => s.BillNo == query.billNo)
            .OrderByDescending(s => s.Id)
            .ToPageList(query.PageIndex, query.Limit);
    }

    public MovesDto GetItems(WarehouseQuery query)
    {
        MovesDto dto = new MovesDto();

        dto.BarcodeList = GetItemMovesCDetails(query);
        dto.OutItems = GetItemOutItems(query);

        return dto;
    }

    private List<MesInvItemOutItems> GetItemOutItems(WarehouseQuery query)
    {
        var sql = string.Format(
            "SELECT C.ITEM_NO, C.QUANTITY, C.REMARK,         C.REMARK,         S.ITEM_NAME, S.ITEM_MODEL, U.FNAME ITEM_UNIT, D.QUANTITY_OK,         NVL(ST.QUANTITY, 0)      WLKC  FROM MES_INV_ITEM_OUT_ITEMS C           JOIN MES_ITEMS S ON C.ITEM_ID = S.ID           LEFT JOIN MES_UNIT U                     ON U.ID = S.ITEM_UNIT           LEFT JOIN MES_DEPOTS P ON P.DEPOT_CODE = C.DEPOT_CODE           LEFT JOIN MES_DEPOT_SECTIONS E ON E.DEPOT_SECTION_CODE = C.DEPOT_SECTION_CODE           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) D ON D.ITEM_MOVE_ID = C.ITEM_OUT_ID AND D.ITEM_NO = C.ITEM_NO         LEFT JOIN MES_INV_ITEM_MOVES A         ON A.ID = C.ITEM_OUT_ID       left join (SELECT ITEM_ID, SUM(QUANTITY) QUANTITY, DEPOTS_CODE                 FROM MES_INV_ITEM_STOCKS GROUP BY ITEM_ID, DEPOTS_CODE) ST ON C.ITEM_ID = ST.ITEM_ID AND ST.DEPOTS_CODE = A.FROM_DEPOTS_CODE where C.ITEM_OUT_ID = {0}",
            query.id);

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

    private List<MesInvItemMovesCDetails> GetItemMovesCDetails(
        WarehouseQuery query)
    {
        return Db.Queryable<MesInvItemMovesCDetails, MesItems, MesSupplier>(
                (b, c, f) => new JoinQueryInfos(
                    JoinType.Left,
                    b.ItemNo == c.ItemNo && b.Company == c.Company &&
                    b.Factory == c.Factory,
                    JoinType.Left, b.SuppNo == f.SuppNo
                ))
            .Where((b, c, f) => b.ItemMoveId == query.id)
            .Select((b, c, f) => new MesInvItemMovesCDetails
            {
                FromDepotSectionsCode = b.FromDepotSectionsCode,
                BoardStyle = b.BoardStyle,
                FromDepotsCode = b.FromDepotsCode,
                InvDepotSectionsCode = b.InvDepotSectionsCode,
                InvDepotsCode = b.InvDepotsCode,
                Quantity = b.Quantity,
                LotNo = b.LotNo,
                ItemNo = b.ItemNo,
                ItemBarcode = b.ItemBarcode,
                ItemName = c.ItemName,
                ItemModel = c.ItemModel,
                ItemUnit = c.ItemUnit,
                SuppName = f.SuppName,
                MoveOk =
                    SqlFunc.IsNull(b.MoveOk, 0) // 使用 SqlFunc.IsNull 实现 NVL 的效果
            }).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 + "，请核对！");
        
        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();

            var 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
    private 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;
    }

    public bool Audit(WarehouseQuery query)
    {
        return Db.Updateable<MesInvItemMoves>()
            .SetColumns(a => new MesInvItemMoves { Ts = 1 })
            .Where(a => a.Id == query.id)
            .ExecuteCommand() > 0;
    }

    public MessageCenter SaveMessageCenter(WarehouseQuery query)
    {
        var message = MesToErpParam(query);

        var executeReturnIdentity =
            Db.Insertable(message).ExecuteReturnIdentity();
        if (executeReturnIdentity > 0)
        {
            message.Id = executeReturnIdentity;
            message.Pid = executeReturnIdentity;
            return message;
        }

        throw new Exception("获取数据失败");
    }

    public MessageCenter MesToErpParam(WarehouseQuery query)
    {
        var erpParameters = "";
        var title = "";
        var tableName = "MES_INV_ITEM_MOVES_" + query.Type;
        if ("A".Equals(query.Type))
        {
            erpParameters = GetErpParameters(query.billNo);
            title = "调拨入库单" + query.billNo + "审核";
        }
        else if ("B".Equals(query.Type))
        {
            erpParameters = GetDeApprovePam(query.id);
            title = "调拨入库单" + query.billNo + "反审核";
        }

        var ErpUrl = AppsettingsUtility.Settings.ProductionErpUrl;
        var message = new MessageCenter
        {
            TableName = tableName,
            Url = ErpUrl,
            Status = 1,
            CreateBy = query.userName,
            Route = query.billNo,
            Title = title,
            PageName = "Allocation/Add?id=" + query.id +
                       "&billNo=" + query.billNo,
            CreateDate = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"),
            Method = "POST",
            Seq = 1,
            Data = erpParameters,
            IsMessage = 0,
            ContentType = "application/x-www-form-urlencoded"
        };
        return message;
    }

    private string GetErpParameters(string? billNo)
    {
        // var invItemIns = Db.Queryable<MesInvItemOuts>()
        //     .Single(x => x.ItemOutNo == billNo);
        //
        // //调用function函数
        // var sql =
        //     $"SELECT F_GENERATE_DATA_INSERTED('{billNo}') FROM DUAL;";
        // var jsonString = Db.Ado.SqlQuerySingle<string>(sql);
        //
        // var encodedUrl = "taskname=CGTL&mesid=" + invItemIns.Id +
        //                  "&optype=create&datajson=" + jsonString;
        //
        // return encodedUrl;
        return null;
    }

    private string GetDeApprovePam(decimal? id)
    {
        // var sid = (int)id;
        // var encodedUrl = "taskname=CGTL&mesid=" + sid +
        //                  "&optype=delete&datajson={}";
        //
        // return encodedUrl;
        return null;
    }
}