using NewPdaSqlServer.DB;
using NewPdaSqlServer.Dto.service;
using NewPdaSqlServer.entity;
using NewPdaSqlServer.util;
using SqlSugar;

namespace NewPdaSqlServer.service.Warehouse;

public class MesInvItemOutsManager : Repository<MesInvItemOuts>
{
    public List<MesInvItemOuts> GetPage(WarehouseQuery query)
    {
        var parsedGuid = Guid.Empty;
        if (!string.IsNullOrEmpty(query.id))
        {
            var isValid = Guid.TryParse(query.id, out parsedGuid);
            if (!isValid)
                throw new ApplicationException("GUID转换错误");
        }

        return
            Db.Queryable<MesInvItemOuts, MesDepots, MesSupplier, SysUser,
                    SysUser>((a, e, i, u1, u3) =>
                    new JoinQueryInfos(
                        JoinType.Left,
                        a.DepotId == e.DepotId,
                        JoinType.Left,
                        a.SuppId == i.Id,
                        JoinType.Left,
                        a.CreateBy == u1.Account,
                        JoinType.Left,
                        a.CheckUser == u3.Account))
                .WhereIF(UtilityHelper.CheckGuid(parsedGuid),
                    (a, e, i, u1, u3) => a.Guid == parsedGuid)
                .WhereIF(!string.IsNullOrEmpty(query.billNo),
                    (a, e, i, u1, u3) => a.ItemOutNo == query.billNo)
                .Select((a, e, i, u1, u3) => new MesInvItemOuts
                {
                    Guid = a.Guid,
                    DepotCode = e.DepotCode,
                    Fmrmode = a.Fmrmode,
                    CheckDate = a.CheckDate,
                    CheckUser = a.CheckUser,
                    States = a.States,
                    Reason = a.Reason,
                    Remark = a.Remark,
                    Nflag = a.Nflag,
                    FType = a.FType,
                    SuppNo = i.SuppNo,
                    DepotName = e.DepotName,
                    SuppName = i.SuppName,
                    CreateByFname = u1.Account,
                    CheckUserFname = u3.Account,
                    CreateDate = a.CreateDate,
                    CreateBy = a.CreateBy,
                    Status = a.Status,
                    ItemOutNo = a.ItemOutNo
                }).ToPageList(query.PageIndex, query.Limit);
    }

    public List<MesInvItemOutItems> GetItems(WarehouseQuery query)
    {
        var parsedGuid = Guid.Empty;
        if (string.IsNullOrEmpty(query.id)) return [];

        var isValid = Guid.TryParse(query.id, out parsedGuid);
        if (!isValid)
            throw new ApplicationException("GUID转换错误");

        var mesInvItemOutItemsList = Db.Queryable<MesInvItemOutItems, MesItems>(
                (c, s) => new object[]
                {
                    JoinType.Inner, c.ItemId == s.Id
                }).Where((c, s) => c.ItemOutId == parsedGuid)
            .Select<MesInvItemOutItems>((c, s) => new MesInvItemOutItems
            {
                Guid = c.Guid,
                TlQty = c.TlQty,
                WorkLine = c.WorkLine,
                PbillNo = c.PbillNo,
                LineK3id = c.LineK3id,
                DepotCode = c.DepotCode,
                Status = c.Status,
                Remark = c.Remark,
                EbelnK3id = c.EbelnK3id,
                Quantity = c.Quantity,
                ItemNo = s.ItemNo,
                ItemName = s.ItemName,
                ItemModel = s.ItemModel,
                ItemId = c.ItemId,
                RkNo = c.RkNo,
                WorkNo = c.WorkNo
            }).ToList();

        foreach (var mesInvItemOutItemse in mesInvItemOutItemsList)
        {
            var u = Db.Queryable<MesInvItemOutItems>()
                .GroupBy(it => new
                    { it.ItemId, it.PbillNo, it.WorkLine, it.RkNo })
                .Where(it => it.ItemId == mesInvItemOutItemse.ItemId
                             // && it.PbillNo == mesInvItemOutItemse.PbillNo
                             // && it.WorkLine == mesInvItemOutItemse.WorkLine
                             && it.RkNo == mesInvItemOutItemse.RkNo)
                .Select(it => new
                {
                    KtQty = SqlFunc.AggregateSum(it.Quantity ?? 0)
                }).First();

            var s1 = Db.Queryable<MesInvItemInCItems>()
                .GroupBy(it => new
                    { it.ItemId, it.Ebeln, it.WorkLine, it.BillNo })
                .Where(it => it.ItemId == mesInvItemOutItemse.ItemId
                             && it.CbillNo == mesInvItemOutItemse.PbillNo
                    // && it.WorkLine == mesInvItemOutItemse.WorkLine
                    // && it.BillNo == mesInvItemOutItemse.RkNo
                )
                .Select(it => new
                {
                    RkQty = SqlFunc.AggregateSum(it.Quantity ?? 0)
                }).First();

            mesInvItemOutItemse.KtQty = s1.RkQty - u.KtQty;
        }

        return mesInvItemOutItemsList;
    }

    public OutItemDto SaveCombination(OutItemDto dto)
    {
        var mesInvItemOuts = dto.Form;
        mesInvItemOuts.Status = 0;
        mesInvItemOuts.BoardFlag = false;
        mesInvItemOuts.Sapstatus = false;
        mesInvItemOuts.OutStatus = false;
        mesInvItemOuts.ItemFlag = false;
        mesInvItemOuts.IsVisual = true;
        mesInvItemOuts.TransactionNo = 203;
        mesInvItemOuts.Factory = "1000";
        mesInvItemOuts.Company = "1000";

        var newGuid = Guid.NewGuid();

        mesInvItemOuts.Guid = newGuid;

        //返回ID
        var command = Db.Insertable(mesInvItemOuts)
            .ExecuteCommand();

        if (command <= 0) throw new Exception("插入错误");

        dto.SaveItems.ForEach(s =>
        {
            s.ItemOutId = newGuid;
            s.Factory = "1000";
            s.Company = "1000";
            s.Status = 0;
            s.DepotCode = mesInvItemOuts.DepotCode;
            s.FType = mesInvItemOuts.FType;
            s.CreateBy = mesInvItemOuts.CreateBy;
            s.CreateDate = mesInvItemOuts.CreateDate;
        });

        var insertable = Db.Insertable(dto.SaveItems)
            .ExecuteCommand();

        if (insertable < 0) throw new Exception("插入错误");

        var query = new WarehouseQuery();
        query.id = newGuid.ToString();
        dto.SelectItems = GetItems(query);

        return dto;
    }


    public List<CgtlView> SelectMaterials(WarehouseQuery query)
    {
        return Db.Queryable<CgtlView>()
            .Where(s => s.SuppId == query.SuppId)
            .ToList();
    }

    private void Validate(WarehouseQuery query)
    {
        // 校验单号是否正确
        var mesInvItemOuts = Db.Queryable<MesInvItemOuts>()
            .Where(o => o.ItemOutNo == query.billNo)
            .Single();

        if (mesInvItemOuts == null)
            throw new Exception("没找到" + query.billNo + " 对应的采购退货单，请检查");

        // 校验子表数据
        var itemOutItems = Db.Queryable<MesInvItemOutItems>()
            .Where(o => o.ItemOutId == mesInvItemOuts.Guid)
            .ToList();

        var sql = string.Empty;
        var P_Msg = string.Empty;

        foreach (var item in itemOutItems)
        {
            // 判断申请数量是否大于剩余可退数量
            sql = string.Format(
                @"SELECT isnull(s1.RK_QTY, 0) - isnull(KT_QTY, 0) KT_QTY 
            FROM MES_INV_ITEM_OUT_ITEMS C
            LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY,
                              ITEM_NO,
                              PBILL_NO,
                              RK_NO,
                              WORK_LINE
                         FROM MES_INV_ITEM_OUT_ITEMS
                        GROUP BY ITEM_NO, PBILL_NO, WORK_LINE,RK_NO) U
              ON U.ITEM_NO = C.ITEM_NO
             AND U.WORK_LINE = C.WORK_LINE
             AND U.PBILL_NO = C.PBILL_NO
             AND U.RK_NO = C.RK_NO
            LEFT JOIN (SELECT SUM(QUANTITY) RK_QTY,
                              ITEM_NO,
                              EBELN,
                              WORK_LINE,BILL_NO
                         FROM MES_INV_ITEM_IN_C_ITEMS
                        GROUP BY ITEM_NO, EBELN, WORK_LINE,BILL_NO) s1
              ON S1.ITEM_NO = C.ITEM_NO
             AND S1.WORK_LINE = C.WORK_LINE
             AND S1.EBELN = C.PBILL_NO
                AND S1.BILL_NO = C.RK_NO
           WHERE C.guid = '{0}'", item.Guid);
            var sy_Qty = Db.Ado.SqlQuery<decimal>(sql);

            if (sy_Qty != null && sy_Qty[0] < 0)
            {
                P_Msg =
                    $"采购订单：{item.PbillNo}，行：{item.WorkLine}，物料：{item.ItemNo} 申请数量大于剩余可收数量";
                throw new Exception(P_Msg);
            }

            // 判断行是否重复
            var cf_Num = Db.Queryable<MesInvItemOutItems>()
                .Where(s1 => s1.ItemNo == item.ItemNo &&
                             s1.WorkLine == item.WorkLine &&
                             s1.PbillNo == item.PbillNo &&
                             s1.ItemOutId == mesInvItemOuts.Guid &&
                             s1.RkNo == item.RkNo)
                .Count();

            if (cf_Num <= 1) continue;
            P_Msg =
                $"采购订单：{item.PbillNo}，行：{item.WorkLine}，物料：{item.ItemNo}重复请删除其中一条数据";
            throw new Exception(P_Msg);
        }

        // 校验主表内容是否被删掉
        // 主表仓库编码
        if (string.IsNullOrEmpty(mesInvItemOuts.DepotCode))
        {
            P_Msg = "仓库编码为空，请检查";
            throw new Exception(P_Msg);
        }

        var supplierExists = Db.Queryable<MesSupplier>()
            .Any(supplier => supplier.SuppNo == mesInvItemOuts.SuppNo);

        if (!supplierExists)
        {
            P_Msg = $"供应商编码 {mesInvItemOuts.SuppNo} 在系统中不存在，请检查";
            throw new Exception(P_Msg);
        }

        // 子表行与主表符合性校验
        foreach (var j in itemOutItems)
        {
            // 校验子表对应采购订单的供应商编码
            var c_SuppNo = Db.Queryable<MesRohIn, MesSupplier>(
                    (roh, supp) => new JoinQueryInfos(
                        JoinType.Left,
                        roh.Supplier == supp.Id.ToString()
                    ))
                .Where(roh => roh.BillNo == j.WorkNo)
                .Select((roh, supp) => supp.SuppNo)
                .Single();

            if (c_SuppNo == null)
            {
                P_Msg = $"子表行供应商编码 {c_SuppNo} 在系统中未找到";
                throw new Exception(P_Msg);
            }

            if (mesInvItemOuts.SuppNo != c_SuppNo)
            {
                P_Msg =
                    $"子表采购订单行的供应商编码 {c_SuppNo} 与主表供应商编码 {mesInvItemOuts.SuppNo} 不一致，请检查";
                throw new Exception(P_Msg);
            }

            // 校验子表委外采购类型和主表类型是否一致
            if (j.FType != mesInvItemOuts.FType)
            {
                P_Msg =
                    $"子表委外类型：{j.FType}，与主表委外类型：{mesInvItemOuts.FType}，不一致，请检查";
                throw new Exception(P_Msg);
            }

            // 检查子表仓库是否和主表仓库一致
            if (j.DepotCode == mesInvItemOuts.DepotCode) continue;
            P_Msg =
                $"子表仓库编码：{j.DepotCode} 与主表仓库编码：{mesInvItemOuts.DepotCode}，不一致，请检查";
            throw new Exception(P_Msg);
        }
    }

    public bool DeApproveBefore(WarehouseQuery query)
    {
        var mesInvItemOuts = base.GetById(query.id);
        if (mesInvItemOuts == null) throw new Exception("出库单不存在");

        var nflag = mesInvItemOuts.Nflag ?? 0;

        if (nflag != 0) return true;

        return false;
    }

    public bool Audit(WarehouseQuery query)
    {
        // 审核前校验
        Validate(query);
        query.status = 1;
        //审核
        return Update(query);
    }

    private bool Update(WarehouseQuery entity)
    {
        var parsedGuid = Guid.Empty;
        if (!string.IsNullOrEmpty(entity.id))
        {
            var isValid = Guid.TryParse(entity.id, out parsedGuid);
            if (!isValid)
                throw new ApplicationException("GUID转换错误"); 
        }

        if (!UtilityHelper.CheckGuid(parsedGuid))
        {
            throw new  ApplicationException("更新错误，数据是不合法的");
        }
        
        var userName = entity.userName ?? "system";
        return Db.Updateable<MesInvItemOuts>()
            .SetColumns(s => s.Status == entity.status)
            .SetColumns(s => s.CheckUser == userName)
            .SetColumns(s => s.CheckDate == DateTime.Now)
            .Where(s => s.Guid == parsedGuid).ExecuteCommand() > 0;
    }


    public string GetMaxItemOutNo()
    {
        var date = DateTime.Now.ToString("yyyyMMdd");
        var par = "NTL" + date;
        var sql =
            $"select max(ITEM_OUT_NO) from MES_INV_ITEM_OUTS where ITEM_OUT_NO like '{par}%'";
        var maxBillNo = Db.Ado.SqlQuerySingle<string>(sql);

        var number = "0001";
        if (maxBillNo != null)
        {
            maxBillNo = maxBillNo.Substring(11);
            var no = Convert.ToInt32(maxBillNo);
            no++;
            number = no.ToString().PadLeft(4, '0');
        }

        return "NTL" + date + number;
    }

    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_OUTS_" + 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 = "Warehouse/PurchaseReturn/Add?id=" + query.id +
                       "&itemOutNo=" + 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;
    }
}