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

namespace MES.Service.service.Warehouse;

public class MesInvItemOutsManager : Repository<MesInvItemOuts>
{
    //当前类已经继承了 Repository 增、删、查、改的方法
    //这里面写的代码不会给覆盖,如果要重新生成请删除 MesInvItemOutsManager.cs

    public bool Audit(WarehouseQuery query)
    {
        // 审核前校验
        Validate(query);
        //审核
        return Db.Updateable<MesInvItemOuts>()
            .SetColumns(s => s.Status == 1)
            .SetColumns(s => s.CheckUser == query.userName)
            .SetColumns(s => s.CheckDate == DateTime.Now)
            .Where(s => s.Id == query.id).ExecuteCommand() > 0;
    }


    public 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.Id)
            .ToList();

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

        foreach (var item in itemOutItems)
        {
            // 判断申请数量是否大于剩余可退数量
            sql = string.Format(
                @"SELECT NVL(s1.RK_QTY, 0) - NVL(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.ID = {0}", item.Id);
            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.Id &&
                             s1.RkNo == item.RkNo)
                .Count();

            if (cf_Num > 1)
            {
                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.PbillNo)
                .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)
            {
                P_Msg =
                    $"子表仓库编码：{j.DepotCode} 与主表仓库编码：{mesInvItemOuts.DepotCode}，不一致，请检查";
                throw new Exception(P_Msg);
            }
        }
    }

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

        //返回ID
        var id = Db.Insertable(mesInvItemOuts)
            .ExecuteReturnIdentity();

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

        mesInvItemOuts.Id = id;
        dto.SaveItems.ForEach(s =>
        {
            s.ItemOutId = id;
            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 = id;
        dto.SelectItems = GetItems(query);

        return dto;
    }

    public List<MesInvItemOuts> GetPage(WarehouseQuery query)
    {
        return
            Db.Queryable<MesInvItemOuts, MesDepots, MesSupplier, SysUser,
                    SysUser>((a, e, i, u1, u3) =>
                    new JoinQueryInfos(
                        JoinType.Left,
                        a.DepotCode == e.DepotCode
                        && e.Factory == a.Factory
                        && e.Company == a.Company,
                        JoinType.Left,
                        a.SuppNo == i.SuppNo,
                        JoinType.Left,
                        a.CreateBy == u1.Fcode,
                        JoinType.Left,
                        a.CheckUser == u3.Fcode))
                .WhereIF(query.id > 0,
                    (a, e, i, u1, u3) => a.Id == query.id)
                .Select((a, e, i, u1, u3) => new MesInvItemOuts
                {
                    Id = a.Id,
                    DepotCode = a.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 = a.SuppNo,
                    DepotName = e.DepotName,
                    SuppName = i.SuppName,
                    CreateByFname = u1.Fname,
                    CheckUserFname = u3.Fname,
                    CreateDate = a.CreateDate,
                    CreateBy = a.CreateBy,
                    Status = a.Status,
                    ItemOutNo = a.ItemOutNo
                }).ToPageList(query.PageIndex, query.Limit);
    }

    //MESInvItemOutItems
    public List<MESInvItemOutItems> GetItems(WarehouseQuery query)
    {
        var where = "and 1=1";
        if (query.id != null) where = "and ITEM_OUT_ID = '" + query.id + "'";

        var sql = string.Format(@"select * from (
        SELECT C.REMARK, C.PBILL_NO, C.ITEM_OUT_ID, C.RK_QTY,
               C.TL_QTY, C.WORK_LINE, C.QUANTITY, C.ITEM_NO,
               NVL(s1.RK_QTY, 0) - NVL(KT_QTY, 0) KT_QTY,
               R.DEPOT_SECTION_CODE, S.ITEM_MODEL, S.ITEM_NAME,
               K.CGB014, Q.STAFF_NAME, R.DEPOT_CODE
        FROM MES_INV_ITEM_OUT_ITEMS C JOIN MES_ITEMS S ON C.ITEM_NO = S.ITEM_NO
                 LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO, PBILL_NO, WORK_LINE, RK_NO
                            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_details
                            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
                 LEFT JOIN MES_ROH_IN_DATA K
                           ON K.BILL_NO = C.PBILL_NO AND C.WORK_LINE = K.PURCHASE_ORDER_LINE_NUMBER
                 left join MES_ROH_IN H on H.BILL_NO = K.BILL_NO
                 left join MES_STAFF Q on H.PURCHASER = Q.STAFF_NAME
                 LEFT JOIN MES_SUPPLIER T ON H.SUPPLIER = T.ID
                 LEFT JOIN (select *
                            from (SELECT row_number() over (partition by DEPOT_CODE, ITEM_NO, EBELN order by CREATE_DATE) rn, DEPOT_CODE, DEPOT_SECTION_CODE, ITEM_NO,
                                         EBELN FROM mes_inv_item_in_c_details
                                  where EBELN is not null)
                            where rn = 1 and ROWNUM = 1) R ON R.ITEM_NO = C.ITEM_NO) where 1=1 {0}",
            where);
        var results = Db.Ado.SqlQuery<MESInvItemOutItems>(sql);
        return results;
    }

    public List<InventoryItem> GetSumItem(WarehouseQuery query)
    {
        var sql = string.Format(@"SELECT DEPOT ,ITEM_NO,ITEM_MODEL,
           ITEM_NAME,QTY FROM (SELECT SUM(QTY) QTY, DEPOT, ITEM_NO, ITEM_MODEL, ITEM_NAME
              FROM (SELECT f_get_section_code2('1000', '1000', b.ITEM_NO,'{0}') DEPOT,
                           B.ITEM_NO, A.ITEM_MODEL,A.ITEM_NAME,to_char(nvl(b.QUANTITY, 0) - nvl(D.QUANTITY_OK, 0),'FM9999999990.00') QTY
                      FROM MES_INV_ITEM_OUT_ITEMS B LEFT JOIN mes_ITEMS A
                        ON B.ITEM_NO = A.ITEM_NO LEFT JOIN (SELECT ITEM_OUT_ID,
                                       ITEM_NO,PBILL_NO,SUM(QUANTITY) QUANTITY_OK,RK_NO
                                  FROM MES_INV_ITEM_OUT_C_DETAILS
                                 WHERE ITEM_OUT_ID = {1}
                                 GROUP BY ITEM_OUT_ID,ITEM_NO,PBILL_NO,RK_NO) D
                        ON D.ITEM_OUT_ID = B.ITEM_OUT_ID
                       AND D.ITEM_NO = B.ITEM_NO
                       AND D.PBILL_NO = B.PBILL_NO
                       AND D.RK_NO = B.RK_NO
                     WHERE B.ITEM_OUT_ID = {1}
                       AND nvl(b.QUANTITY, 0) - nvl(D.QUANTITY_OK, 0) > 0
                     ORDER BY f_get_section_code2('1000','1000',b.ITEM_NO,'{0}'),
                              A.ITEM_MODEL,A.ITEM_NAME)
             GROUP BY DEPOT, ITEM_NO, ITEM_MODEL, ITEM_NAME)", query.DepotCode,
            query.id);

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

    public List<InventoryItemDetail> SelectMaterials(WarehouseQuery query)
    {
        var sql = string.Format(@"select B.WORK_NO, b.work_line,
                               b.item_no, s2.item_name, s2.item_model,
                               sum(b.quantity)                         sum_Quantity,
                               b.unit, b.supp_no,
                               nvl(s1.quantity, 0)                     quantity,
                               sum(b.quantity) - nvl(U.KT_QTY, 0)      Refundable_Quantity,
                               B.cbill_no, B.task_no, A.BILL_NO,
                               to_char(b.CREATE_DATE, 'yyyy-mm-dd') as CREATE_DATE
                        from mes_inv_item_in_c_details b
                                 left join mes_inv_item_ins a on b.item_in_id = a.id
                                 LEFT JOIN (select s.item_no, s.work_line, s.Work_no,
                                                   sum(s.quantity) quantity, S.RK_NO
                                            from mes_inv_item_out_c_details s
                                            group by s.item_no, s.work_line, s.work_no, S.RK_NO) s1
                                           ON NVL(B.work_LINE, '0') = NVL(s1.work_LINE, '0')
                                               and B.work_no = s1.work_no
                                               and B.item_no = s1.item_no
                                               AND B.BILL_NO = S1.RK_NO
                                 LEFT JOIN (SELECT SUM(QUANTITY) KT_QTY, ITEM_NO, PBILL_NO, WORK_LINE, RK_NO
                                            FROM MES_INV_ITEM_OUT_ITEMS
                                            GROUP BY ITEM_NO, PBILL_NO, WORK_LINE, RK_NO) U
                                           ON U.ITEM_NO = B.ITEM_NO
                                               AND U.WORK_LINE = B.WORK_LINE
                                               AND U.PBILL_NO = B.WORK_NO AND U.RK_NO = B.BILL_NO
                                 left join mes_items s2 on b.item_no = s2.item_no
                                 left join purdha da on da.dha001 = b.cbill_no
                        WHERE A.BILL_NO LIKE 'Q%'
                          AND A.CBILL_NO like 'L%'
                        group by b.item_no, b.work_line, b.supp_no, b.remark, s1.quantity, b.unit,
                                 s2.item_name, s2.item_model, da.dha003, B.WORK_NO, B.cbill_no, B.task_no,
                                 to_char(b.CREATE_DATE, 'yyyy-mm-dd'), A.BILL_NO, U.KT_QTY, A.CBILL_NO
                        having b.remark = '采购入库'
                           and sum(b.quantity) - nvl(U.KT_QTY, 0) > 0
                           and b.supp_no = '{0}'
                        order by to_char(b.CREATE_DATE, 'yyyy-mm-dd')",
            query.SuppNo);

        var results = Db.Ado.SqlQuery<InventoryItemDetail>(sql);
        return results;
    }

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