using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMS.Core._02Entity;
using WMS.Info;
using WMS.Util;

namespace WMS.Core.ServiceCore
{
    public class FxTransferOrderCore
    {
        /// <summary>
        /// 获取分页数据
        /// <summary>
        /// <returns></returns>
        public IEnumerable<dynamic> GetPageList(Pagination pagination, string queryJson)
        {
            SqlSugarClient client = null;
            try
            {
                client = SysDbCore.GetDbCtx();
                int count = 0;
                var queryParam = queryJson.ToJObject();

                var db = client.Queryable<BILL_TRANSFERORDER>();

                //单据日期
                if (!queryParam["Btime"].IsEmpty() && !queryParam["Etime"].IsEmpty())
                {
                    db.Where(ord => ord.F_editTime >= queryParam["Btime"].ToDate() && ord.F_editTime <= queryParam["Etime"].ToDate());
                }
                if (!queryParam["keyword"].IsEmpty())
                {
                    db.Where(ord => ord.F_no.Contains(queryParam["keyword"].ToString()));
                }
                //if (!queryParam["F_trayNo"].IsEmpty())
                //{
                //    string trayNo = queryParam["trayNo"].ToString();
                //    db.Where(ord => ord.F_fromLoc.Contains(trayNo) || ord.F_toLoc.Contains(trayNo));
                //}
                //if (!queryParam["F_matNo"].IsEmpty())
                //{
                //    string matNo = queryParam["F_matNo"].ToString();
                //    db.Where(ord => ord.F_toPart.Contains(matNo) || ord.F_toPart.Contains(matNo));
                //}
                //if (!queryParam["F_matType"].IsEmpty())
                //{
                //    string matType = queryParam["F_matType"].ToString();
                //    db.Where(ord => ord.F_matType == FuncStr.NullToInt(matType));
                //}
                if (pagination.sord.ToUpper() != "ASC")
                {
                    pagination.sidx = pagination.sidx + " DESC";
                }
                // (case when regexp_substr(ord.F_NO,'[^_]+',1,2) is null then ord.F_NO else regexp_substr(ord.F_NO,'[^_]+',1,2) end)
                var list = db.OrderBy(pagination.sidx).Select<BILL_TRANSFERORDER>(@"
                                                               [F_no],
       [F_relatedOrderNo],
       [F_orderStatus],
       [F_totalOrderLines],
       [F_addTime],
       [F_addUserNo],
       [F_editTime],
       [F_editUserNo]").ToPageList(pagination.page, pagination.rows, ref count);
                pagination.records = count;
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                client.Dispose();
            }
        }


        /// <summary>
        /// 获取单据数据
        /// <summary>
        /// <returns></returns>
        public IEnumerable<BILL_TRANSFERORDERLINE> GeOrderItem(string OrderNo)
        {
            try
            {
                var lst = SysDbCore.GetDbCtx().Queryable<BILL_TRANSFERORDERLINE>().Where(it => it.F_pNo == OrderNo);
                var list = lst.Select<BILL_TRANSFERORDERLINE>(@"   [F_no],
       [F_pNo],
       [F_fromLoc],
       [F_fromPart],
       [F_fromLot],
       [F_FromRef],
       [F_toLoc],
       [F_toPart],
       [F_toLot],
       [F_toRef],
       [F_trType],
       [F_task],
       [F_wLocGp],
       [F_issNbr],
       [F_qtyExp],
       [F_addTime],
       [F_addUserNo],
       [F_editTime],
       [F_editUserNo]").ToList();
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 获取分页数据
        /// <summary>
        /// <returns></returns>
        public List<FX_TranD> GetHaveTask(string queryJson)
        {
            try
            {
                var queryParam = queryJson.ToJObject();
                StringBuilder sb = new StringBuilder();

                //单据日期
                if (!queryParam["Btime"].IsEmpty() && !queryParam["Etime"].IsEmpty())
                {
                    sb.Append($" and createDate>'{queryParam["Btime"]}' and createDate<'{queryParam["Etime"]}'");
                }
                if (!queryParam["F_matNo"].IsEmpty())
                {
                    string matNo = queryParam["F_matNo"].ToString();
                    sb.Append($"  and FromPart='{matNo}'");
                }
                var trands = FxDbCore.GetData<FX_TranD>($"select  * from pub.TranD where  FromSite='SUZ' and Task='TRANSFER' {sb}");
                trands.ForEach(t =>
                {
                    t.pt_prod_line = CacheFacade.GetMatInfo(t.FromPart, string.IsNullOrEmpty(t.FromRef) ? EMatType.Product : EMatType.Mat)?.F_projectNo;
                });
                return trands;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 无任务转仓保存
        /// <summary>
        /// <returns></returns>
        public void NotHavaTaskSave(StockOutRequest request, List<BILL_INVENTORY> invs, LoginUserInfo loginUserInfo)
        {
            //同步出库单明细
            using (var ctx = SysDbCore.GetDbCtx())
            {
                try
                {
                    if (!invs.Any()) throw SysExCore.ThrowFailException($"请选择转仓数据行！");
                    if (string.IsNullOrWhiteSpace(request.PickPort)) throw SysExCore.ThrowFailException($"请选择拣货口！");
                    ctx.BeginTran();
                    var order = new BILL_TRANSFERORDER
                    {
                        F_addTime = DateTime.Now,
                        F_addUserNo = loginUserInfo.UserNo,
                        F_no = SerialNumberProvider.Instance.OrderNumber("wms", "ZC"),
                        F_orderStatus = (int)EOrderState.New,
                        //F_relatedOrderNo = string.Join(",", trands.Select(s => s.IssNbr)),
                        F_totalOrderLines = invs.Count,
                    };
                    var stockOut = new BILL_STOCKOUT
                    {
                        F_no = SerialNumberProvider.Instance.OrderNumber("wms", "CK"),
                        F_editTime = DateTime.Now,
                        F_addTime = DateTime.Now,
                        F_addUserNo = loginUserInfo.UserNo,
                        F_orderStatus = (int)EOrderState.New,
                        F_orderType = (int)EStockOutOrderType.StockOutOrderTypeTransfer,
                        F_relatedOrderNo = order.F_no,
                        F_warehouseNo = SysSetCore.GetSysSet().DefaultWarehouseNo,
                        F_totalOrderLines = invs.Count
                    };
                    order.F_relatedOrderNo = stockOut.F_no;
                    ctx.Insertable(order).ExecuteCommand();
                    ctx.Insertable(stockOut).ExecuteCommand();
                    //转仓明细
                    var dicPort = new FxStockOutOrderCore().GetPickPort(ctx, request.PickPort);
                    var stockOutDetailLst = new List<BILL_STOCKOUTLINEDETAIL>();
                    foreach (var orderLine in invs)
                    {
                        var transferOrder = ctx.Queryable<BILL_TRANSFERORDER, BILL_TRANSFERORDERLINE>((ord, info) => new object[] { JoinType.Inner, ord.F_no == info.F_pNo })
                              .Where((ord, info) => info.F_fromPart == orderLine.F_matNo && ord.F_orderStatus != (int)EOrderState.Executed).Select<BILL_TRANSFERORDER>();
                        if (transferOrder.Any())
                            throw SysExCore.ThrowFailException($"转仓物料{orderLine.F_matNo}已生成过转仓任务！");
                        var line = new BILL_TRANSFERORDERLINE
                        {
                            F_addTime = DateTime.Now,
                            F_editTime = DateTime.Now,
                            F_fromLoc = orderLine.F_trayNo,
                            F_fromPart = orderLine.F_matNo,
                            F_FromRef = orderLine.F_uid,
                            F_qtyExp = orderLine.F_quantity,
                            F_pNo = order.F_no,
                            F_addUserNo = loginUserInfo.UserNo
                        };
                        ctx.Insertable(line).ExecuteCommand();
                        var location = ctx.Queryable<BASE_LOCATION>().First(f => f.F_trayNo == orderLine.F_trayNo && f.F_status == (int)EWareCellState.Stored);
                        if (location == null)
                            throw SysExCore.ThrowFailException($"托盘{ orderLine.F_trayNo}货位数据异常！");
                        ctx.Insertable(new BILL_STOCKOUTLINEDETAIL
                        {
                            F_addTime = DateTime.Now,
                            F_addUserNo = loginUserInfo.UserNo,
                            F_boxNo = orderLine.F_boxNo,
                            F_locationNo = location.F_no,
                            F_trayNo = orderLine.F_trayNo,
                            F_matName = orderLine.F_matName,
                            F_matType = orderLine.F_matType,
                            F_matNo = orderLine.F_matNo,
                            F_pNo = stockOut.F_no,
                            F_quantity = orderLine.F_quantity,
                        }).ExecuteCommand();
                    }
                    //出库计划明细
                    var matInfoGrp = invs.GroupBy(g => new { g.F_matNo, g.F_matType, g.F_matName }).Select(s => new { s.Key.F_matNo, s.Key.F_matType, s.Key.F_matName, qty = s.Sum(c => c.F_quantity) }).ToList();
                    foreach (var orderLine in matInfoGrp)
                    {
                        var stockOutline = new BILL_STOCKOUTLINE
                        {
                            F_matName = orderLine.F_matName,
                            F_addTime = DateTime.Now,
                            F_matNo = orderLine.F_matNo,
                            F_planQty = orderLine.qty,
                            F_addUserNo = loginUserInfo.UserNo,
                            F_matType = orderLine.F_matType,
                            F_editTime = DateTime.Now,
                            F_pNo = stockOut.F_no,
                            F_orderLineNo = matInfoGrp.IndexOf(orderLine) + 1,
                            F_warehouseNo = SysSetCore.GetSysSet().DefaultWarehouseNo
                        };
                        ctx.Insertable(stockOutline).ExecuteCommand();
                    }
                    //生成托盘任务
                    var trayGroup = invs.GroupBy(g => new { g.F_trayNo }).Select(s => new { s.Key.F_trayNo, qty = s.Sum(c => c.F_quantity) }).ToList();
                    trayGroup.ForEach(tray =>
                    {
                        var location = ctx.Queryable<BASE_LOCATION>().First(f => f.F_trayNo == tray.F_trayNo && f.F_status == (int)EWareCellState.Stored);
                        var task = new WMS_TASK
                        {
                            F_addTime = DateTime.Now,
                            F_addUserNo = loginUserInfo.UserNo,
                            F_trayNo = tray.F_trayNo,
                            F_priority = request.F_priority,
                            F_taskStatus = (int)ETaskStatus.NotIssued,
                            F_taskType = (int)ETaskComType.OutStock,
                            F_orderType = (int)ETaskOrderType.StockOutOrderTypeTransfer,
                            F_posidFrom = location.F_no,
                            F_isBonded = location.F_isBonded,
                            F_orderNo = order.F_no,
                            F_posidcur = location.F_no,
                            //F_posidNext = ctx.Queryable<BASE_POINT>().First(c => c.F_type == (int)EPointType.CKXYGDZ).F_no,
                            F_EquipmentType = (int)EEquipmentType.RGV
                        };
                        //锁定出库库存
                        ctx.Updateable<BASE_LOCATION>().SetColumns(it =>
                        new BASE_LOCATION()
                        {
                            F_addTime = DateTime.Now,
                            F_editUserNo = loginUserInfo.UserNo,
                            F_status = (int)EWareCellState.Out,
                        }).Where(w => w.F_no == location.F_no).ExecuteCommand();
                        //分配出料口
                        var curMatInv = ctx.Queryable<BILL_INVENTORY>().Where(c => c.F_trayNo == tray.F_trayNo);
                        var matInvQty = curMatInv.Sum(s => s.F_quantity);
                        if (tray.qty == matInvQty)
                        {
                            //当前托盘数量等于指定数量则整盘出
                            task.F_posidNext = new AppCoreBLL().GetFreePointInfoByType((int)EPointType.ZPKCK).F_no;
                            task.F_posidTo = new AppCoreBLL().GetFreePointInfoByType((int)EPointType.CPAGV).F_no;
                        }
                        else
                        {
                            var kvPort = dicPort.OrderBy(o => o.Value).FirstOrDefault();
                            dicPort[kvPort.Key] += 1;
                            task.F_posidTo = kvPort.Key;
                            task.F_posidNext = kvPort.Key;
                            task.F_pickPort = kvPort.Key;
                        }
                        ctx.Insertable(task).ExecuteCommand();
                    });
                    ctx.CommitTran();
                }
                catch (Exception ex)
                {
                    ctx.RollbackTran();
                    throw ex;
                }
            }
        }

        /// <summary>
        /// 有任务转仓保存
        /// <summary>
        /// <returns></returns>
        public void HavaTaskSave(StockOutRequest orderRequest, List<FX_TranD> sod_det, LoginUserInfo loginUserInfo)
        {
            //同步出库单明细
            using (var ctx = SysDbCore.GetDbCtx())
            {
                try
                {
                    if (!sod_det.Any()) throw SysExCore.ThrowFailException($"明细不能为空！");
                    if (ctx.Queryable<BILL_STOCKOUT>().Any(c => c.F_relatedOrderNo == orderRequest.F_no))
                        throw SysExCore.ThrowFailException($"工单{orderRequest.F_no}已生成出库任务！");
                    if (sod_det.Any(c => string.IsNullOrWhiteSpace(c.FromLoc))) throw SysExCore.ThrowFailException($"明细托盘号不能为空！");
                    ctx.BeginTran();
                    var bILL_STOCKOUT = new BILL_STOCKOUT
                    {
                        F_addTime = DateTime.Now,
                        F_addUserNo = loginUserInfo.UserNo,
                        F_no = SerialNumberProvider.Instance.OrderNumber("wms", "CK"),
                        F_orderStatus = (int)EOrderState.New,
                        F_orderType = (int)EStockOutOrderType.StockOutOrderTypeMaterial,
                        F_relatedOrderNo = sod_det.FirstOrDefault().IssNbr,
                        F_totalOrderLines = sod_det.Count,
                        F_warehouseNo = SysSetCore.GetSysSet().DefaultWarehouseNo
                    };
                    ctx.Insertable(bILL_STOCKOUT).ExecuteCommand();
                    //出库明细
                    var matGrp = sod_det.GroupBy(g => new { g.FromPart, g.IssNbr }).Select(s => new FX_TranD { IssNbr = s.Key.IssNbr, FromPart = s.Key.FromPart, QtyExp = s.Sum(k => k.QtyExp) }).ToList();
                    var dicPort = new FxStockOutOrderCore().GetPickPort(ctx, orderRequest.PickPort);
                    foreach (var orderLine in matGrp)
                    {
                        var matInfo = ctx.Queryable<BASE_ITEM>().First(f => f.F_matNo == orderLine.FromPart);
                        var line = new BILL_STOCKOUTLINE
                        {
                            F_addTime = DateTime.Now,
                            //F_actualQty = orderLine.sod_qty_ship,
                            F_planQty = orderLine.QtyExp,
                            F_matNo = orderLine.FromPart,
                            F_matName = matInfo.F_matName,
                            F_matType = (int)EMatType.Product,
                            F_orderLineNo = matGrp.IndexOf(orderLine) + 1,
                            F_pNo = bILL_STOCKOUT.F_no,
                            F_warehouseNo = bILL_STOCKOUT.F_warehouseNo,
                            F_addUserNo = loginUserInfo.UserNo,
                            F_editTime = DateTime.Now
                        };
                        ctx.Insertable(line).ExecuteCommand();
                    }
                    //出库实际明细
                    var matInfoGrp = sod_det.GroupBy(g => new { g.FromPart, g.IssNbr, g.FromLoc }).Select(s => new FX_TranD { IssNbr = s.Key.IssNbr, FromPart = s.Key.FromPart, FromLoc = s.Key.FromLoc, QtyExp = s.Sum(k => k.QtyExp) }).ToList();
                    foreach (var orderLine in matInfoGrp)
                    {
                        var inv = ctx.Queryable<BILL_INVENTORY>().First(f => f.F_trayNo == orderLine.FromLoc && f.F_matNo == orderLine.FromPart);
                        if (inv == null || inv.F_quantity < orderLine.QtyExp)
                            throw SysExCore.ThrowFailException($"托盘{ orderLine.FromLoc}物料{orderLine.FromPart}库存不够！");
                        var location = ctx.Queryable<BASE_LOCATION>().First(f => f.F_trayNo == orderLine.FromLoc && f.F_status == (int)EWareCellState.Stored);
                        if (location == null)
                            throw SysExCore.ThrowFailException($"托盘{ orderLine.FromLoc}货位数据异常！");

                        ctx.Insertable(new BILL_STOCKOUTLINEDETAIL
                        {
                            F_addTime = DateTime.Now,
                            F_addUserNo = loginUserInfo.UserNo,
                            //F_boxNo = item.F_boxNo,
                            F_locationNo = location.F_no,
                            F_matNo = orderLine.FromPart,
                            F_matName = inv.F_matName,
                            F_matType = inv.F_matType,
                            F_pNo = bILL_STOCKOUT.F_no,
                            F_quantity = orderLine.QtyExp,
                            F_trayNo = orderLine.FromLoc,
                            F_boxNo = inv.F_boxNo
                        }).ExecuteCommand();
                        var task = new WMS_TASK
                        {
                            F_addTime = DateTime.Now,
                            F_addUserNo = loginUserInfo.UserNo,
                            F_trayNo = orderLine.FromLoc,
                            F_priority = orderRequest.F_priority,
                            F_taskStatus = (int)ETaskStatus.NotIssued,
                            F_taskType = (int)ETaskComType.OutStock,
                            F_orderType = (int)ETaskOrderType.StockOutOrderTypeMaterial,
                            F_posidFrom = location.F_no,
                            F_isBonded = location.F_isBonded,
                            F_orderNo = bILL_STOCKOUT.F_no,
                            F_posidcur = location.F_no,
                            //F_posidNext = ctx.Queryable<BASE_POINT>().First(c => c.F_type == (int)EPointType.CKXYGDZ).F_no,
                            F_EquipmentType = (int)EEquipmentType.RGV
                        };
                        //分配出料口
                        var curMatInv = ctx.Queryable<BILL_INVENTORY>().Where(c => c.F_trayNo == orderLine.FromLoc);
                        var matInvQty = curMatInv.Sum(s => s.F_quantity);
                        if (orderLine.QtyExp == matInvQty)
                        {
                            //当前托盘数量等于指定数量则整盘出
                            task.F_posidNext = new AppCoreBLL().GetFreePointInfoByType((int)EPointType.ZPKCK).F_no;
                            task.F_posidTo = new AppCoreBLL().GetFreePointInfoByType((int)EPointType.CPAGV).F_no;
                        }
                        else
                        {
                            var kvPort = dicPort.OrderBy(o => o.Value).FirstOrDefault();
                            dicPort[kvPort.Key] += 1;
                            task.F_posidTo = kvPort.Key;
                            task.F_posidNext = kvPort.Key;
                            task.F_pickPort = kvPort.Key;
                        }
                        ctx.Insertable(task).ExecuteCommand();
                        //锁定出库库存
                        ctx.Updateable<BASE_LOCATION>().SetColumns(it =>
                        new BASE_LOCATION()
                        {
                            F_addTime = DateTime.Now,
                            F_editUserNo = loginUserInfo.UserNo,
                            F_status = (int)EWareCellState.Out,
                        }).Where(w => w.F_no == location.F_no).ExecuteCommand();
                    }
                    sod_det.ForEach(det =>
                    {
                        det.AddTime = DateTime.Now;
                        det.AddUserNo = loginUserInfo.UserNo;
                    });
                    ctx.Insertable(sod_det).ExecuteCommand();
                    ctx.CommitTran();
                }
                catch (Exception ex)
                {
                    ctx.RollbackTran();
                    throw ex;
                }
            }
        }
    }
}