using HH.WCS.Mobox3.HD.api;
using HH.WCS.Mobox3.HD.models;
using HH.WCS.Mobox3.HD.models.other;
using HH.WCS.Mobox3.HD.util;
using Newtonsoft.Json.Linq;
using NLog.Fluent;
using SqlSugar;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Runtime.Remoting.Messaging;
using System.Text;
using System.Threading.Tasks;
using static HH.WCS.Mobox3.HD.api.ApiModel;
using static HH.WCS.Mobox3.HD.api.DigitHelper;
using static HH.WCS.Mobox3.HD.api.WmsController;
using static HH.WCS.Mobox3.HD.util.ExpressionHelper;
using static HH.WCS.Mobox3.HD.util.Settings;

namespace HH.WCS.Mobox3.HD.wms {
    /// <summary>
    /// wms管到作业
    /// </summary>
    internal class WMSHelper
    {
        internal static string GenerateTaskNo()
        {
            var id = SYSHelper.GetSerialNumber("作业号", "OP");
            var date = DateTime.Now.ToString("yyMMdd");
            return $"OP{date}{id.ToString().PadLeft(4, '0')}";
        }
        internal static string GenerateSortingNo()
        {
            var id = SYSHelper.GetSerialNumber("分拣单", "SO");
            var date = DateTime.Now.ToString("yyMMdd");
            return $"SO{date}{id.ToString().PadLeft(4, '0')}";
        }

        internal static string GenerateStockRecordNo()
        {
            var id = SYSHelper.GetSerialNumber("库存记录单", "SR");
            var date = DateTime.Now.ToString("yyMMdd");
            return $"SR{date}{id.ToString().PadLeft(4, '0')}";
        }

        internal static List<WMSTask> GetOperationListByState(string state)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<WMSTask>().Where(a => a.S_B_STATE == state).ToList();
        }
        internal static List<WMSTask> GetOperationListByState(int state)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<WMSTask>().Where(a => a.N_B_STATE == state).ToList();
        }
        internal static List<WMSTask> GetWaitingOperationList()
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<WMSTask>().Where(a => a.N_B_STATE == 0 || a.N_B_STATE == 3).ToList();
        }
        internal static PutawayOrder GetPutawayOrder(string no)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<PutawayOrder>().Where(a => a.S_NO == no).First();
        }
        internal static bool CreatePutawayOrder(PutawayOrder model)
        {
            var db = new SqlHelper<object>().GetInstance();
            var result = db.Insertable<PutawayOrder>(model).ExecuteCommand() > 0;
            db.Insertable<PutawayDetail>(model.Details).ExecuteCommand();
            return result;
        }

        internal static PutawayDetail GetPutawayOrderDetail(string no, string item_code)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<PutawayDetail>().Where(a => a.S_PUTAWAY_NO == no && a.S_ITEM_CODE == item_code).First();
        }
        internal static PutawayDetail GetPutawayOrderDetail(string item_code)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<PutawayDetail>().Where(a => a.S_ITEM_CODE == item_code && a.F_QTY - a.F_ACC_B_QTY > 0).OrderByDescending(a => a.T_CREATE).First();
        }

        internal static void UpdatePutawayOrderDetailQty(PutawayDetail model)
        {
            var db = new SqlHelper<object>().GetInstance();
            db.Updateable(model).UpdateColumns(it => new { it.F_ACC_B_QTY }).ExecuteCommand();
        }


        internal static ShippingOrder GetShippingOrder(string no)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<ShippingOrder>().Includes(a => a.Details).Where(a => a.S_NO == no).First();
        }
        internal static bool CreateShippingOrder(ShippingOrder model)
        {
            var db = new SqlHelper<object>().GetInstance();
            var result = db.Insertable<ShippingOrder>(model).ExecuteCommand() > 0;
            db.Insertable<ShippingDetail>(model.Details).ExecuteCommand();
            return result;
        }

        internal static bool CreateSortingOrder(List<string> list)
        {
            var res = false;
            //遍历获取发货单，然后判断库存，如果全都没库存，则不生成分拣单，如果有生成分拣单
            //更新波次单，即使只有一个发货单也更新波次单
            var db = new SqlHelper<object>().GetInstance();
            var sortingOrderNo = "";
            SortingOrder sortingOrder = null;
            try
            {
                db.BeginTran();
                list.ForEach(a =>
                {
                    var so = db.Queryable<ShippingOrder>().Includes(s => s.Details).Where(s => s.S_NO == a).First();
                    //判断库存，只有已经入库的才可以计算，码盘的不算，入库后出库途中的也要计算，码盘后默认叫待入库，入库后叫正常
                    //生成出库任务，只有托盘位置在立库才需要创建出库任务（先写死立库）
                    //只有全部分拣完成才允许回库（一个托盘可能对应多个分拣明细）

                    //查找仓库量表和库区量表，出库单需要定位仓库和库区了，出库定位物理库区就行，入库可能物料库区还要区分逻辑库区

                    //暂时只查仓库量表（量表如何重置，查找所有查找C_ENABLE=N的，判断如果在仓库中，改成Y，然后统计）

                    //生成分拣单时候增加仓库量表分配量，生成分拣单明细时候，增加库区量表分配量
                    if (so != null && so.Details.Count > 0)
                    {
                        var fail = true;
                        for (int i = 0; i < so.Details.Count; i++)
                        {
                            var whi = db.Queryable<WHInventory>().Where(w => w.S_ITEM_CODE == so.Details[i].S_ITEM_CODE).First();
                            if (whi != null && whi.F_QTY - whi.F_ALLOC_QTY > 0)
                            {
                                //有货就出
                                float qty = whi.F_QTY - whi.F_ALLOC_QTY > so.Details[i].F_QTY ? so.Details[i].F_QTY : whi.F_QTY - whi.F_ALLOC_QTY;
                                fail = false;
                                //有可用库存，生成分拣单
                                if (sortingOrderNo == "")
                                {
                                    sortingOrderNo = GenerateSortingNo();
                                    sortingOrder = new SortingOrder
                                    {
                                        S_NO = sortingOrderNo,
                                        S_SHIPPING_NO = so.S_NO,
                                        Composes = new List<SortingCompose>()
                                    };
                                    //创建分拣单
                                    db.Insertable(sortingOrder).ExecuteCommand();
                                }
                                else
                                {
                                    //获取最新分拣单
                                    sortingOrder = db.Queryable<SortingOrder>().Includes(s => s.Composes).Where(s => s.S_NO == sortingOrderNo).First();
                                    //更新分拣单中的发货单单号
                                    sortingOrder.S_SHIPPING_NO = sortingOrder.S_SHIPPING_NO + ";" + so.S_NO;
                                    sortingOrder.T_MODIFY = DateTime.Now;
                                    db.Updateable(sortingOrder).UpdateColumns(it => new { it.S_SHIPPING_NO, it.T_MODIFY }).ExecuteCommand();
                                }
                                //查询分拣单子表（正常情况还需要增加批次判断）
                                var soc = db.Queryable<SortingCompose>().Where(s => s.S_ITEM_CODE == so.Details[i].S_ITEM_CODE && s.S_SORTING_NO == sortingOrder.S_NO).First();
                                if (soc == null)
                                {
                                    soc = new SortingCompose
                                    {
                                        S_ITEM_CODE = so.Details[i].S_ITEM_CODE,
                                        S_SORTING_NO = sortingOrder.S_NO,
                                        F_QTY = qty,
                                        N_ROW_NO = sortingOrder.Composes.Count() + 1,
                                        S_BATCH_NO = so.Details[i].S_BATCH_NO
                                    };

                                    //创建分拣单子表
                                    db.Insertable(soc).ExecuteCommand();
                                }
                                else
                                {
                                    soc.F_QTY += qty;
                                    soc.T_MODIFY = DateTime.Now;
                                    //更新分拣单子表
                                    db.Updateable(soc).UpdateColumns(it => new { it.F_QTY, it.T_MODIFY }).ExecuteCommand();
                                }
                                //更新仓库量表分配量
                                whi.F_ALLOC_QTY += qty;
                                whi.T_MODIFY = DateTime.Now;
                                db.Updateable(whi).UpdateColumns(it => new { it.F_ALLOC_QTY, it.T_MODIFY }).ExecuteCommand();
                                //跟新发货单子表配货数量
                                so.Details[i].F_ACC_D_QTY += qty;
                                so.Details[i].T_MODIFY = DateTime.Now;
                                db.Updateable(so.Details[i]).UpdateColumns(it => new { it.F_ACC_D_QTY, it.T_MODIFY }).ExecuteCommand();
                            }

                        }
                        //更新发货单状态
                        so.N_B_STATE = fail ? 5 : 1;
                        if (fail)
                        {
                            so.S_NOTE = "没有库存";
                        }
                        so.S_WAVE_CODE = sortingOrderNo;
                        so.T_MODIFY = DateTime.Now;
                        db.Updateable(so).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY, it.S_WAVE_CODE, it.S_NOTE }).ExecuteCommand();

                    }


                    //查找库区内托盘生成托盘明细（包括分拣中）怎么判断库内？查找货位绑定？还是托盘上加标识比较好，如果是整托出的，分拣确认后去掉分拣标识。
                    //1、只有码盘入库后的才需要加标识，分拣回的不用，分拣回的标识还在，没有变。写死码盘入的完成后加标识，整托分拣的去掉标识，防止后面重新码盘脏数据
                    //2、或者关联查询库内的，只给分拣出的加标识，每次分拣回再把标识清除了（如果不清除带标识的会很多，还不如全部加标识），整托的清除。
                    //综合选择还是方案1，这样创建分拣明细，只需要托盘物料两表查询就能定位托盘（如果信息不准，可以重置），方案2需要货位、托盘、物料三表联查

                    //暂时不计库区，标识用容器表 C_ENABLE 来判断，表示能不能出库

                    //执行分拣创建任务，遍历分拣明细中的托盘，如果在仓库就出库，如果不在就不用出库


                });
                //全部分拣单生成之后将分拣单状态设置为开始配货,波次号为发货单
                sortingOrder = db.Queryable<SortingOrder>().Where(s => s.S_NO == sortingOrderNo).First();
                sortingOrder.N_B_STATE = 1;
                sortingOrder.T_MODIFY = DateTime.Now;
                db.Updateable(sortingOrder).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
                db.CommitTran();
                res = true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                db.RollbackTran();
            }
            return res;

        }

        internal static List<SortingDetail> GetSortingDetailByCntr(string cntr)
        {
            var db = new SqlHelper<object>().GetInstance();
            var result = db.Queryable<SortingDetail>().Where(a => a.S_CNTR_CODE == cntr && a.N_B_STATE != 2).ToList();
            return result;
        }

        internal static bool CreateWmsTask(WMSTask wmsTask)
        {
            try
            {
                var db = new SqlHelper<object>().GetInstance();
                return db.Insertable<WMSTask>(wmsTask).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
        }

        internal static bool DeleteWmsTask(string taskNo)
        {
            try
            {
                var db = new SqlHelper<object>().GetInstance();
                return db.Deleteable<WMSTask>().Where(a => a.S_CODE == taskNo).ExecuteCommand() > 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                throw;
            }
        }

        internal static bool BatchCreateWmsTask(List<WMSTask> wmsTasks)
        {
            bool result = true;
            var db = new SqlHelper<object>().GetInstance();
            try
            {
                db.BeginTran();
                foreach (var task in wmsTasks)
                {
                    db.Insertable<WMSTask>(task).ExecuteCommand();
                    LocationHelper.LockLoc(task.S_START_LOC, 2);
                    LocationHelper.LockLoc(task.S_END_LOC, 1);
                }
                db.CommitTran();
            }
            catch (Exception ex)
            {
                db.RollbackTran();
                result = false;
                Console.WriteLine(ex.Message);
                throw;
            }
            return result;  
        }

        internal static WMSTask GetWmsTaskByCntr(string cntr, bool active = true)
        {
            WMSTask result = null;
            var db = new SqlHelper<object>().GetInstance();
            if (active)
            {
                result = db.Queryable<WMSTask>().Where(a => a.S_CNTR_CODE.Contains(cntr) && a.N_B_STATE < 2).OrderByDescending(a => a.T_CREATE).First();
            }
            else
            {
                result = db.Queryable<WMSTask>().Where(a => a.S_CNTR_CODE.Contains(cntr)).OrderByDescending(a => a.T_CREATE).First();
            }
            return result;
        }
       
        internal static Location GetEnd(WMSTask a)
        {
            throw new NotImplementedException();
        }

        internal static Location GetStart(WMSTask a)
        {
            throw new NotImplementedException();
        }

        internal static void UpdateTaskState(WMSTask task)
        {
            var db = new SqlHelper<object>().GetInstance();
            task.T_MODIFY = DateTime.Now;
            task.S_B_STATE = WMSTask.GetStateStr(task.N_B_STATE);
            db.Updateable<WMSTask>(task).UpdateColumns(a => new { a.N_B_STATE, a.S_B_STATE, a.T_MODIFY ,a.T_END_TIME}).ExecuteCommand();
        }

        internal static bool UpdateTaskEnd(WMSTask a)
        {
            var db = new SqlHelper<object>().GetInstance();
            a.T_MODIFY = DateTime.Now;
            return db.Updateable<WMSTask>(a).UpdateColumns(it => new { it.S_END_LOC, it.T_MODIFY }).ExecuteCommand() > 0;
        }

        internal static void UpdateTask(WMSTask task)
        {
            var db = new SqlHelper<object>().GetInstance();
            task.T_MODIFY = DateTime.Now;
            db.Updateable<WMSTask>(task).ExecuteCommand();
        }

        internal static WMSTask GetWmsTask(string code)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<WMSTask>().Where(a => a.S_CODE == code).First();
        }
        internal static WMSTask GetWmsTaskBySrc(string code)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<WMSTask>().Where(a => a.S_OP_DEF_CODE == code).OrderByDescending(a => a.T_CREATE).First();
        }
        internal static WMSTask GetWmsTaskByEnd(string endLoc)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<WMSTask>().Where(a => a.S_END_LOC == endLoc && a.N_B_STATE < 2).First();
        }

        
        /// <summary>
        /// 获取开始配货的分拣单，一次性生成分拣明细，避免生成一半再生成，所以创建分拣明细的时候加上事务
        /// </summary>
        /// <returns></returns>
        internal static List<SortingOrder> GetWaitingSortingOrderList()
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<SortingOrder>().Includes(a => a.Composes).Where(a => a.N_B_STATE == 1 || a.N_B_STATE == 20).ToList();
        }
        /// <summary>
        /// 获取配货完成的分拣单，每个分拣单单独创建分拣作业
        /// </summary>
        /// <returns></returns>
        internal static List<SortingOrder> GetWaitingSortingOperationList()
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<SortingOrder>().Includes(a => a.Details).Where(a => a.N_B_STATE == 2 || a.N_B_STATE == 3).ToList();
        }

        /// <summary>
        ///单个分拣单的分拣明细创建作业
        /// </summary>
        internal static void CreateSortingOperation(SortingOrder so)
        {
            var list = so.Details.Where(a => a.N_B_STATE == 0).ToList();
            var db = new SqlHelper<object>().GetInstance();
            if (list.Count > 0)
            {
                try
                {
                    db.BeginTran();
                    list.GroupBy(g => g.S_CNTR_CODE).ToList().ForEach(g =>
                    {
                        var cntr = g.Key;
                        var sdList = g.ToList();
                        //查询托盘货位，查到了创建任务，查不到说明在别的分拣作业中，不创建任务，需要回库后重新创建
                        var lcr = db.Queryable<LocCntrRel>().Where(c => c.S_CNTR_CODE == cntr).First();
                        if (lcr != null)
                        {
                            //判断托盘是否已经创建任务了，可能多个分拣明细是同一个托盘，如果创建任务了，其它分拣的要稍后，只出一次人工会搞不清楚是哪个分拣单的
                            var wmsTask = db.Queryable<WMSTask>().Where(op => op.S_CNTR_CODE.Contains(cntr) && op.N_B_STATE < 2).First();
                            if (wmsTask == null)
                            {
                                wmsTask = new WMSTask
                                {
                                    S_CNTR_CODE = cntr,
                                    S_CODE = WMSHelper.GenerateTaskNo(),
                                    S_START_LOC = lcr.S_LOC_CODE,
                                    S_END_LOC = "",
                                    N_TYPE = 2,
                                    S_TYPE = WMSTask.GetTypeStr(2),
                                    S_OP_DEF_CODE = "",
                                    S_OP_DEF_NAME = "分拣出立库"
                                };
                                if (db.Insertable(wmsTask).ExecuteCommand() > 0)
                                {
                                    LocationHelper.LockLoc(lcr.S_LOC_CODE, 2);
                                }

                                sdList.ForEach(a =>
                                {
                                    a.N_B_STATE = 1;
                                    a.T_MODIFY = DateTime.Now;
                                    db.Updateable(a).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();
                                });
                            }
                        }
                        //修改分拣单状态为开始作业（不代表作业全部创建了，因为有的托盘可能已经被占用了）
                        so.N_B_STATE = 3;
                        //如果所有的作业都已经创建了，就设置为作业已经创建
                        if (so.Details.Count(s => s.N_B_STATE == 0) == 0)
                        {
                            so.N_B_STATE = 4;
                        }
                        so.T_MODIFY = DateTime.Now;
                        db.Updateable(so).UpdateColumns(it => new { it.N_B_STATE, it.T_MODIFY }).ExecuteCommand();

                    });
                    db.CommitTran();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    db.RollbackTran();
                }

            }
        }

        /// <summary>
        /// 类似农夫堆叠
        /// </summary>
        /// <param name="item"></param>
        /// <param name="endArea"></param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        internal static Location GetInstockEnd(string item, string endArea)
        {
            var end = GetLocation4InAnySrc(endArea, item);
            if (end == null)
            {
                end = GetLocation4InEmptyRow(endArea);
            }
            return end;
        }
        public static Location GetLocation4InAnySrc(string area, string itemCode)
        {
            Location result = null;

            try
            {
                Console.WriteLine("成品满托入库  GetLocation4InAnySrc:" + area);

                //1.0 获取每一排最大的列
                //1.1 判断当前数量是不是满的，如果是满的并且是最大列，需要排除
                var listMaxCol = new SqlHelper<Location>().GetInstance().Queryable<Location>().Where(a => a.S_AREA_CODE == area).OrderByDescending(a => a.N_COL).Take(1).PartitionBy(a => a.N_ROW).ToList();
                //1.1 查到所有有托盘的排 
                Console.WriteLine("查到所有有托盘的排 ");
                var db = new SqlHelper<object>().GetInstance();
                var list = db.Queryable<Location>().Where(a => a.N_CURRENT_NUM > 0 && a.S_AREA_CODE == area).Includes(a => a.LocCntrRel, a => a.CntrItemRel).OrderByDescending(a => a.N_COL).Take(1).PartitionBy(a => a.N_ROW).ToList();
                if (list.Count > 0)
                {
                    //1.2 查找其它尺寸有托盘或者锁定的排
                    for (int i = list.Count - 1; i >= 0; i--)
                    {
                        //排除已经锁定的货位 和 放满了且是最大列的货位
                        if (list[i].N_LOCK_STATE != 0 || (list[i].N_CURRENT_NUM == list[i].N_CAPACITY && listMaxCol.Count(a => a.S_CODE == list[i].S_CODE) > 0))
                        {
                            Console.WriteLine($"排除已经锁定的货位 和 放满了且是最大列的货位 排{list[i].N_ROW}");
                            list.Remove(list[i]);
                        }
                        else
                        {
                            //排有锁也排除
                            var other = db.Queryable<Location>().Where(a => a.S_AREA_CODE == area && a.N_ROW == list[i].N_ROW && a.S_LOCK_STATE != "无" && a.N_LOCK_STATE != 0).First();
                            if (other != null)
                            {
                                Console.WriteLine($"排除有锁的排{list[i].N_ROW}");
                                list.Remove(list[i]);
                            }
                        }

                    }
                    Console.WriteLine($"有托盘排数为{list.Count}");
                    if (list.Count > 0)
                    {

                        //1.3 遍历判断物料类型是否相同
                        Console.WriteLine("遍历判断物料类型是否相同");
                        for (int i = 0; i < list.Count; i++)
                        {
                            //todo 还需要判断锁
                            if (list[i].LocCntrRel != null && list[i].LocCntrRel.CntrItemRel != null)
                            {
                                Console.WriteLine($"货位{list[i].S_CODE} 物料{list[i].LocCntrRel.CntrItemRel.S_ITEM_CODE}");
                                if (list[i].LocCntrRel.CntrItemRel.S_ITEM_CODE == itemCode)
                                {
                                    if (list[i].N_CURRENT_NUM < list[i].N_CAPACITY)
                                    {
                                        result = list[i];
                                    }
                                    else
                                    {
                                        Console.WriteLine("选择后面空位");
                                        result = db.Queryable<Location>().Where(a => a.S_AREA_CODE == area && a.N_ROW == list[i].N_ROW && a.N_COL > list[i].N_COL).OrderBy(a => a.N_COL).First();
                                    }

                                    if (result != null)
                                    {
                                        break;
                                    }
                                }
                            }

                        }
                    }
                }

                if (result != null && (!string.IsNullOrEmpty(result.C_ENABLE) && (result.C_ENABLE == "禁用" || result.C_ENABLE == "N")))
                {
                    //禁用了选择后面一个货位
                    //Console.WriteLine("禁用了选择后面一个货位");
                    result = db.Queryable<Location>().OrderBy(a => a.N_COL).Where(a => a.S_AREA_CODE == area && a.N_ROW == result.N_ROW && (string.IsNullOrEmpty(a.C_ENABLE) || (a.C_ENABLE != "禁用" && a.C_ENABLE != "N")) && a.N_COL > result.N_COL).First();
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error("GetLocation4InFinish:" + ex.Message, ex);
            }
            return result;
        }
        public static Location GetLocation4InEmptyRow(string area)
        {
            Location result = null;
            try
            {
                var db = new SqlHelper<object>().GetInstance();

                #region 查找所有数量是空的排
                //Console.WriteLine("查找所有数量是空的排");
                //2.0 简化查询只查每一排第一列
                var list = db.Queryable<Location>().Where(a => a.S_AREA_CODE == area).OrderBy(a => a.N_COL).Take(1).PartitionBy(a => a.N_ROW).ToList().Where(a => a.N_CURRENT_NUM == 0).ToList();
                //2.1 选一个空排
                for (int i = 0; i < list.Count; i++)
                {
                    var other = db.Queryable<Location>().Where(a => a.S_AREA_CODE == area && a.N_ROW == list[i].N_ROW && a.S_LOCK_STATE != "无").First();
                    if (list[i].N_LOCK_STATE == 0 && other == null)
                    {
                        //二次校验当前排所有货位都是空的，防止系统数据错乱
                        var rowSumInfo = db.Queryable<Location>().Where(l => l.S_AREA_CODE == area && l.N_ROW == list[i].N_ROW && l.N_CURRENT_NUM != 0).Count();
                        if (rowSumInfo == 0)
                        {
                            result = list[i];
                            break;
                        }
                    }
                }
                #endregion

                if (result != null && (!string.IsNullOrEmpty(result.C_ENABLE) && (result.C_ENABLE == "禁用" || result.C_ENABLE == "N")))
                {
                    //禁用了选择后面一个货位
                    Console.WriteLine("禁用了选择后面一个货位");
                    result = db.Queryable<Location>().OrderBy(a => a.N_COL).Where(a => a.S_AREA_CODE == area && a.N_ROW == result.N_ROW && (string.IsNullOrEmpty(a.C_ENABLE) || (a.C_ENABLE != "禁用" && a.C_ENABLE != "N")) && a.N_COL > result.N_COL).First();
                }
            }
            catch (Exception ex)
            {

                LogHelper.Error("GetLocation4InEmptyRow:" + ex.Message, ex);
            }
            return result;
        }

        public static Location GetShiftStockStart(string item, string area, int row)
        {
            Location result = null;
            try
            {
                // 1、查询当前库区-排-物料 有托盘的货位
                var db = new SqlHelper<object>().GetInstance();
                result = db.Queryable<Location>()
                            .LeftJoin<LocCntrRel>((l, lc) => l.S_CODE == lc.S_LOC_CODE)
                            .LeftJoin<CntrItemRel>((l, lc, ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE && ci.S_ITEM_CODE == item)
                    .Where(l => l.N_CURRENT_NUM > 0 && l.S_AREA_CODE == area && l.N_ROW == row).Includes(l => l.LocCntrRel, l => l.CntrItemRel).OrderByDescending(l => l.N_COL).First();
            }
            catch (Exception ex)
            {
                LogHelper.Error("GetShiftStockStart:" + ex.Message, ex);
            }
            return result;
        }

        public static Location GetShiftStockEnd(string item, string area, int row)
        {
            Location result = null;
            try
            {
                // 1、查询当前库区-排 空货位
                var db = new SqlHelper<object>().GetInstance();
                result = db.Queryable<Location>().Where(a => a.N_CURRENT_NUM < 2 && a.S_AREA_CODE == area && a.N_ROW == row).Includes(a => a.LocCntrRel, a => a.CntrItemRel).OrderBy(a => a.N_COL).First();
            }
            catch (Exception ex)
            {
                LogHelper.Error("GetShiftStockEnd:" + ex.Message, ex);
            }
            return result;
        }

        public static List<Location> GetLocationList(string area, int row)
        {
            List<Location> result = new List<Location>();
            try
            {
                var db = new SqlHelper<object>().GetInstance();
                result = db.Queryable<Location>().Where(a => a.S_AREA_CODE == area && a.N_ROW == row).Includes(a => a.LocCntrRel, a => a.CntrItemRel).ToList();
            }
            catch (Exception ex)
            {
                LogHelper.Error("GetLocationList:" + ex.Message, ex);
            }
            return result;
        }

        /// <summary>
        /// 查询备货区终点货位(成品)
        /// </summary>
        /// <param name="areaCode"></param>
        /// <returns></returns>
        public static Location GetDeliverEndLocation(string areaCode)
        {
            Location result = null;
            try
            {
                var db = new SqlHelper<object>().GetInstance();
                result = db.Queryable<Location>().Where(a => a.S_AREA_CODE == areaCode && a.N_LOCK_STATE == 0 && a.N_CURRENT_NUM == 0).OrderBy(a => a.N_COL).First();
            }
            catch (Exception ex)
            {
                LogHelper.Error("查询备货区终点货位,错误:" + ex.Message, ex);
            }
            return result;
        }

        // 入库排顺序 1.顺序 2.倒叙
        private static int rowOrder = 1;

        /// <summary>
        /// 查询入库终点货位(成品、半成品)
        /// 入库策略：优先入相同物料的外侧
        /// row 接驳位所在排
        /// </summary>
        /// <param name="areaCode"></param>
        /// <param name="itemCode"></param>
        /// <param name="roadway">接驳位所在巷道</param>
        /// <param name="row">接驳位所在排</param>
        /// <param name="thresholdValue">库容阈值</param>
        /// <returns></returns>
        public static Location GetEndLocation(string areaCode, string itemCode ,int roadway ,int row ,int thresholdValue = 1)
        {
            Location loc = null;
            try
            {
                var db = new SqlHelper<object>().GetInstance();

                // 【1.1】查询所有未上锁的空货位
                var query = db.Queryable<Location>().Where(a => a.S_AREA_CODE == areaCode && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.N_ROADWAY == roadway && (a.C_ENABLE != "禁用" && a.C_ENABLE != "N"));
                if (rowOrder == 1)
                {
                    query = query.OrderBy(a => a.N_ROW);
                    rowOrder++;
                }
                else {
                    query = query.OrderByDescending(a => a.N_ROW);
                    rowOrder--;
                }
                
                // 优先入靠近接驳位的物料（row 接驳位所在排）
                if (row == 1)
                {
                    query.OrderBy(a => new { a.N_COL , a.N_LAYER });
                }
                else if (row == 2) {
                    query.OrderByDescending(a => a.N_COL).OrderBy(a => a.N_LAYER);
                }
                var locList = query.ToList();

                if (locList != null)
                {
                    // 判断库区类型
                    List<string> areaCodes = Settings.getStoreAreaCodes(2, 1);
                    if (areaCodes.Contains(areaCode))
                    {
                        // 查询正在该巷道相关未完成的任务数量
                        StoreAreaInfo storeAreaInfo = Settings.storeAreaInfos.Where(a => a.areaCode == areaCode).FirstOrDefault();
                        var taskNum = db.Queryable<WMSTask>()
                                .LeftJoin<WCSTask>((a,b) => a.S_CODE == b.S_OP_CODE)
                              .LeftJoin<Location>((a, b ,c) => b.S_END_LOC == c.S_CODE)
                              .Where((a, b, c) => b.S_END_AREA == storeAreaInfo.accessArea && c.N_ROADWAY == roadway && a.N_B_STATE < 2 && b.S_TYPE.Contains("入库")).Count();
                        if (locList.Count - thresholdValue > taskNum)
                        {
                            // 【1.2】查询深度 N_DEEP = 2 ,位置 N_POS = 1 且 物料相同 的货位
                            var sameItemLocList = db.Queryable<Location>()
                                        .LeftJoin<LocCntrRel>((l, lc) => l.S_CODE == lc.S_LOC_CODE)
                                        .LeftJoin<CntrItemRel>((l, lc, ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE)
                                        .Where((l, lc, ci) => l.S_AREA_CODE == areaCode && l.N_CURRENT_NUM > 0 && ci.S_ITEM_CODE == itemCode && l.N_DEEP == 2 && l.N_POS == 1 && l.N_LOCK_STATE == 0)
                                        .ToList();

                            if (sameItemLocList != null && sameItemLocList.Count > 0)
                            {
                                foreach (var item in sameItemLocList)
                                {
                                    // 优先放入同物料的货位
                                    loc = locList.Where(a => a.S_AREA_CODE == areaCode && (a.N_ROW == item.N_ROW + 1 || a.N_ROW == item.N_ROW - 1) && a.N_COL == item.N_COL && a.N_LAYER == item.N_LAYER && a.N_DEEP == 2 && a.N_POS == 2).FirstOrDefault();
                                    if (loc != null)
                                    {
                                        return loc;
                                    }
                                }

                                if (loc == null)
                                {
                                    // 相同物料外侧没有空余货位，优先入里侧货位
                                    loc = locList.Where(a => a.N_DEEP == 2 && a.N_POS == 1 || a.N_DEEP == 1).FirstOrDefault();
                                }
                            }
                            else
                            {
                                // 没有相同物料，优先入里侧货位
                                loc = locList.Where(a => a.N_DEEP == 2 && a.N_POS == 1 || a.N_DEEP == 1).FirstOrDefault();
                            }
                            // 里侧没有货位，则随机入外侧货位
                            if (loc == null)
                            {
                                loc = locList.Where(a => a.N_DEEP == 2 && a.N_POS == 2).FirstOrDefault();
                            }
                        }
                    }
                    else
                    {
                        return locList[0];
                    }
                }
            }
            catch (Exception ex) {
                LogHelper.Info("获取入库终点报错，错误信息:" + ex.Message,"WMS");
            }
            return loc;
        }

        /// <summary>
        /// 查询空托出库货位
        /// </summary>
        /// <param name="locCode"></param>
        /// <returns></returns>
        public static Location GetEmptyTrayStartLocation(string locCode )
        {
            Location location = null;

            Dictionary<string, string> pairs = new Dictionary<string, string>();

            foreach (var item in Settings.sortAreaInfos)
            {
                pairs.Add(item.inLocCode, item.relateArea);
            }

            var db = new SqlHelper<object>().GetInstance();

            // 查看分拣区对应的库区的空盘垛
            string areaCode = "";
            if (pairs.ContainsKey(locCode))
            {
                pairs.TryGetValue(locCode, out areaCode);
                var locList = db.Queryable<Location>()
                    .LeftJoin<LocCntrRel>((l,lc) => l.S_CODE == lc.S_LOC_CODE)
                    .LeftJoin<CntrItemRel>((l,lc,ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE)
                    .Where((l, lc, ci) => l.S_AREA_CODE == areaCode && l.N_LOCK_STATE == 0 && l.N_CURRENT_NUM == 1 && lc.S_CNTR_CODE != null && ci.S_ITEM_CODE == null).ToList();
                if (locList != null && locList.Count > 0)
                {
                    foreach (var loc in locList)
                    {
                        // 2.判断该货位是否可取
                        if (loc.N_DEEP == 1 || (loc.N_DEEP == 2 && loc.N_POS == 2))
                        {
                            return loc;
                        }
                        else if (loc.N_DEEP == 2 && loc.N_POS == 1)
                        {
                            var outerLoc = locList.Where(it => it.N_COL == loc.N_COL && it.N_LAYER == loc.N_LAYER && it.N_ROW_GROUP == loc.N_ROW_GROUP && it.N_POS == 2).FirstOrDefault();
                            if (outerLoc == null || outerLoc.N_CURRENT_NUM == 0)
                            {
                                return loc;
                            }
                        }
                    }
                }
            }
            if (location == null) {
                pairs.Remove(locCode);
                foreach (var item in pairs)
                {
                    pairs.TryGetValue(item.Key, out areaCode);
                    var locList = db.Queryable<Location>()
                         .LeftJoin<LocCntrRel>((l, lc) => l.S_CODE == lc.S_LOC_CODE)
                         .LeftJoin<CntrItemRel>((l, lc, ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE)
                         .Where((l, lc, ci) => l.S_AREA_CODE == areaCode && l.N_LOCK_STATE == 0 && l.N_CURRENT_NUM == 1 && lc.S_CNTR_CODE != null && ci.S_ITEM_CODE == null).ToList();
                    if (locList != null && locList.Count > 0)
                    {
                        foreach (var loc in locList)
                        {
                            // 2.判断该货位是否可取
                            if (loc.N_DEEP == 1 || (loc.N_DEEP == 2 && loc.N_POS == 2))
                            {
                                return loc;
                            }
                            else if (loc.N_DEEP == 2 && loc.N_POS == 1)
                            {
                                var outerLoc = locList.Where(it => it.N_COL == loc.N_COL && it.N_LAYER == loc.N_LAYER && it.N_ROW_GROUP == loc.N_ROW_GROUP && it.N_POS == 2).FirstOrDefault();
                                if (outerLoc == null || outerLoc.N_CURRENT_NUM == 0)
                                {
                                    return loc;
                                }
                            }
                        }
                    }
                }
            }
            return location;
        }

        /// <summary>
        /// 查询物料出库开始货位(半成品/成品)
        /// 库区：LTK01、LTK02、LTK03
        /// 出库策略：先进先出、优先出外侧物料
        /// </summary>
        /// <param name="itemCode">物料编码</param>
        /// <param name="partTaskId">零件任务号</param>
        /// <param name="workNo">零件任务号</param>
        /// <param name="priorityArea">优先库区</param>
        /// <param name="descArea">目标库区</param>
        /// <returns></returns>
        public static ItemStartLoc GetStartLocation(string itemCode , string partTaskId ,string workNo = null, string priorityArea = null ,string descArea = null)
        {
            ItemStartLoc itemStartLoc = new ItemStartLoc();
            try
            {
                var db = new SqlHelper<object>().GetInstance();
                // 1.按入库时间顺序查询该物料的所有货位
                List<Location> locList = new List<Location>();
                List<string> areaCodes = Settings.getStoreAreaCodes(0, 1);
                var query = db.Queryable<Location>()
                        .LeftJoin<LocCntrRel>((l, lc) => l.S_CODE == lc.S_LOC_CODE)
                        .LeftJoin<CntrItemRel>((l, lc, ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE)
                        .Where((l, lc, ci) => areaCodes.Contains(l.S_AREA_CODE) && l.N_CURRENT_NUM > 0 && ci.S_ITEM_CODE == itemCode && l.N_LOCK_STATE == 0 && (l.C_ENABLE != "禁用" && l.C_ENABLE != "N"));

                if (partTaskId != null)
                {
                    query = query.Where((l, lc, ci) => ci.S_PART_TASK_ID == partTaskId);
                }

                if (workNo != null) {
                    query = query.Where((l, lc, ci) => ci.S_WORK_NO == workNo);
                }
                locList = query.OrderBy((l, lc, ci) => ci.T_IN_STOCK).OrderBy((l, lc, ci) => ci.S_PART_TASK_ID).ToList();

                // 当零件号不等于null ，查询结果为 null，则不匹配零件号
                if ((locList == null || locList.Count == 0) && partTaskId != null)
                {
                    var query1 = db.Queryable<Location>()
                       .LeftJoin<LocCntrRel>((l, lc) => l.S_CODE == lc.S_LOC_CODE)
                       .LeftJoin<CntrItemRel>((l, lc, ci) => lc.S_CNTR_CODE == ci.S_CNTR_CODE)
                       .Where((l, lc, ci) => areaCodes.Contains(l.S_AREA_CODE) && l.N_CURRENT_NUM > 0 && ci.S_ITEM_CODE == itemCode && l.N_LOCK_STATE == 0);

                    if (workNo != null)
                    {
                        query1 = query1.Where((l, lc, ci) => ci.S_WORK_NO == workNo);
                    }

                    locList = query1.OrderBy((l, lc, ci) => ci.T_IN_STOCK).ToList();
                }

                if (locList == null || locList.Count == 0)
                {
                    itemStartLoc.type = 0;
                    return itemStartLoc;
                }

                if (priorityArea != null && locList.Count > 0)
                {
                    var locList1 = locList.Where(a => a.S_AREA_CODE == priorityArea).ToList();
                    if (locList1 != null && locList1.Count > 0) {
                        locList = locList1;
                    }
                }

                ItemStartLoc prepItemStartLoc = new ItemStartLoc();
                if (locList != null && locList.Count > 0)
                {
                    foreach (var loc in locList)
                    {
                        // 1.判断该物料货架类型 封闭式立库、开放式货架
                        itemStartLoc.type = 1;
                        itemStartLoc.loc = null;
                        itemStartLoc.outLoc = null;
                        List<string> FBAreaCodes = Settings.getStoreAreaCodes(2, 1);
                        if (FBAreaCodes.Contains(loc.S_AREA_CODE))
                        {
                            // 2.判断该货位是否被阻塞
                            if (loc.N_DEEP == 1 || (loc.N_DEEP == 2 && loc.N_POS == 2))
                            {
                                itemStartLoc.loc = loc;
                            }
                            else if (loc.N_DEEP == 2 && loc.N_POS == 1)
                            {
                                var outerLoc = db.Queryable<Location>().Where(it => it.S_AREA_CODE == loc.S_AREA_CODE && (it.N_ROW == loc.N_ROW + 1 || it.N_ROW == loc.N_ROW - 1) && it.N_COL == loc.N_COL && it.N_LAYER == loc.N_LAYER && it.N_ROADWAY == loc.N_ROADWAY && it.N_POS == 2).First();
                                if (outerLoc == null || outerLoc.N_CURRENT_NUM == 0)
                                {
                                    itemStartLoc.loc = loc;
                                }
                                else
                                {
                                    if (outerLoc.N_LOCK_STATE == 0)
                                    {
                                        var locCntrRel = ContainerHelper.getLocCntrByLoc(outerLoc.S_CODE);
                                        if (locCntrRel != null)
                                        {
                                            string outCntrCode = locCntrRel.S_CNTR_CODE;
                                            var cntrItemRels = ItemHelper.GetCntrItemByCntrCode(outCntrCode);
                                            string outTrayItemCode = null;
                                            if (cntrItemRels != null && cntrItemRels.Count > 0)
                                            {
                                                outTrayItemCode = cntrItemRels[0].S_ITEM_CODE;
                                            }
                                            Location inLoc = WMSHelper.GetEndLocation(outerLoc.S_AREA_CODE, outTrayItemCode, outerLoc.N_ROADWAY, 1);
                                            if (inLoc != null)
                                            {
                                                // 如该物料被阻塞，则需进行移库
                                                itemStartLoc.type = 2;
                                                itemStartLoc.loc = loc;
                                                itemStartLoc.outLoc = outerLoc;
                                            }
                                        }
                                    }
                                }
                            }

                            // 判断是否有可用的出库接驳位
                            if (itemStartLoc.loc != null)
                            {
                                var connectLocs = LocationHelper.GetConnectLocation(itemStartLoc.loc.S_AREA_CODE, 2, itemStartLoc.loc.N_ROADWAY, 1, descArea);
                                if (connectLocs.Count > 0)
                                {
                                    itemStartLoc.connectloc = LocationHelper.GetLoc(connectLocs[0]);
                                    break;
                                }
                            }
                        }
                        else {
                            itemStartLoc.loc = loc;
                            break;
                        }
                    }
                }
            }
            catch (BusinessException be) 
            {
                throw be;
            }
            catch (Exception ex)
            {
                LogHelper.Info("查询物料出库开始货位(半成品/成品)-GetStartLocation 报错:" + ex.Message, "Mobox");
            }
            return itemStartLoc;
        }

        /// <summary>
        /// 根据托盘码查询开始货位
        /// </summary>
        /// <param name="trayCode">托盘码</param>
        /// <param name="descArea">目标库区</param>
        /// <returns></returns>
        public static ItemStartLoc GetStartLocationByTrayCode(string trayCode , string descArea = null)
        {
            ItemStartLoc itemStartLoc = new ItemStartLoc();
            try
            {
                var db = new SqlHelper<object>().GetInstance();
                var loc = db.Queryable<Location>()
                    .LeftJoin<LocCntrRel>((a, b) => a.S_CODE == b.S_LOC_CODE)
                    .Where((a, b) => b.S_CNTR_CODE == trayCode && a.N_LOCK_STATE == 0 && (a.C_ENABLE != "禁用" && a.C_ENABLE != "N"))
                    .First();

                if (loc != null )
                {
                    // 1.判断该物料货架类型 封闭式立库、开放式货架
                    itemStartLoc.type = 1;
                    itemStartLoc.loc = null;
                    itemStartLoc.outLoc = null;
                    List<string> FBAreaCodes = Settings.getStoreAreaCodes(2, 1);
                    if (FBAreaCodes.Contains(loc.S_AREA_CODE))
                    {
                        // 2.判断该货位是否被阻塞
                        if (loc.N_DEEP == 1 || (loc.N_DEEP == 2 && loc.N_POS == 2))
                        {
                            itemStartLoc.loc = loc;
                        }
                        else if (loc.N_DEEP == 2 && loc.N_POS == 1)
                        {
                            var outerLoc = db.Queryable<Location>().Where(it => it.S_AREA_CODE == loc.S_AREA_CODE && (it.N_ROW == loc.N_ROW + 1 || it.N_ROW == loc.N_ROW - 1) && it.N_COL == loc.N_COL && it.N_LAYER == loc.N_LAYER && it.N_ROADWAY == loc.N_ROADWAY && it.N_POS == 2).First();
                            if (outerLoc == null || outerLoc.N_CURRENT_NUM == 0)
                            {
                                itemStartLoc.loc = loc;
                            }
                            else
                            {
                                if (outerLoc.N_LOCK_STATE == 0)
                                {
                                    // 如该物料被阻塞，则需进行移库
                                    itemStartLoc.type = 2;
                                    itemStartLoc.loc = loc;
                                    itemStartLoc.outLoc = outerLoc;
                                }
                            }
                        }

                        // 判断是否有可用的出库接驳位
                        if (itemStartLoc.loc != null)
                        {
                            var connectLocs = LocationHelper.GetConnectLocation(itemStartLoc.loc.S_AREA_CODE, 2, itemStartLoc.loc.N_ROADWAY, 1, descArea);
                            if (connectLocs.Count > 0)
                            {
                                itemStartLoc.connectloc = LocationHelper.GetLoc(connectLocs[0]);
                            }
                        }
                    }
                    else
                    {
                        itemStartLoc.loc = loc;
                       
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error("GetStartLocationByTrayCode:" + ex.Message, ex);
            }
            return itemStartLoc;
        }

        public class ItemStartLoc {
            public int type { get; set; } //0、没有物料货位 1.物料可取货位 2.物料不可取货位（不可取货位需进行移库）3.没有接驳位
            public Location loc { get; set; }
            public Location outLoc { get; set; }
            public Location connectloc { get; set; }
        }

        /// <summary>
        /// 查询入库终点货位(钢卷)
        /// 入库策略：
        /// </summary>
        /// <param name="weight"></param>
        /// <returns></returns>
        public static Location GetGJEndLocation(float weight)
        {
            Location loc = null;
            try
            {
                var db = new SqlHelper<object>().GetInstance();
                List<string> gjkAreaCode = Settings.getStoreAreaCodes(1, 2);
                
                if (weight < 3000)
                {
                    loc = db.Queryable<Location>().Where(a => gjkAreaCode.Contains(a.S_AREA_CODE) && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && (a.N_LAYER == 3 || a.N_LAYER == 2) && (a.C_ENABLE != "禁用" && a.C_ENABLE != "N")).OrderBy(a => a.N_ROW).OrderByDescending(a => a.N_LAYER).First();
                }
                else if (weight < 4500)
                {
                    loc = db.Queryable<Location>().Where(a => gjkAreaCode.Contains(a.S_AREA_CODE) && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && (a.N_LAYER == 2 || a.N_LAYER == 1) && (a.C_ENABLE != "禁用" && a.C_ENABLE != "N")).OrderBy(a => a.N_ROW).OrderByDescending(a => a.N_LAYER).First();
                }
                else {
                    loc = db.Queryable<Location>().Where(a => gjkAreaCode.Contains(a.S_AREA_CODE) && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.N_LAYER == 1 && (a.C_ENABLE != "禁用" && a.C_ENABLE != "N")).OrderBy(a => a.N_ROW).First();
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error("GetGJEndLocation:" + ex.Message, ex);
            }
            return loc;
        }

        /// <summary>
        /// 查询物料出库开始货位(钢卷)
        /// 出库策略：先进先出
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="coilNo"></param>
        /// <param name="workNo"></param>
        /// <param name="weight"></param>
        /// <param name="width"></param>
        /// <returns></returns>
        public static Location GetGJStartLocation(string itemCode,string coilNo,string workNo ,string weight ,string width)
        {
            Location location = null;
            try
            {
                location = GetGreaterThanGJWeightLocation(itemCode, coilNo, workNo, weight, width);
                if (location == null) {
                    location = GetLessThanGJWeightLocation(itemCode, coilNo, workNo, weight, width);
                }
            }
            catch (Exception ex)
            {
                LogHelper.Error("GetGJStartLocation:" + ex.Message, ex);
            }
            return location;
        }

        /// <summary>
        /// 获取大于需求重量的钢卷货位（最接近）
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="coilNo"></param>
        /// <param name="workNo"></param>
        /// <param name="weight"></param>
        /// <param name="width"></param>
        /// <returns></returns>
        public static Location GetGreaterThanGJWeightLocation(string itemCode, string coilNo, string workNo, string weight, string width) {
            Location location = null;
            var db = new SqlHelper<object>().GetInstance();
            List<string> areaCodes = Settings.getStoreAreaCodes(1, 2);
            var query = db.Queryable<Location>().Where(it => areaCodes.Contains(it.S_AREA_CODE) && it.N_LOCK_STATE == 0 && it.C_ENABLE == "Y");

            if (itemCode != null)
            {
                query = query.Where(it => it.LocCntrRel.CntrItemRel.S_ITEM_CODE == itemCode);
            }
            if (coilNo != null)
            {
                query = query.Where(it => it.LocCntrRel.CntrItemRel.S_COIL_NO.Contains(coilNo) );
            }
            if (workNo != null)
            {
                query = query.Where(it => it.LocCntrRel.CntrItemRel.S_WORK_NO == workNo);
            }
            if (weight != null)
            {
                float f_weight = float.Parse(weight);
                query = query.Where(it => it.LocCntrRel.CntrItemRel.F_WEIGHT > f_weight);
            }
            if (width != null)
            {
                query = query.Where(it => it.LocCntrRel.CntrItemRel.S_WIDTH == width);
            }

            // 在数据库查询中应用表达式  
            List<Location> locList = query.Includes(a => a.LocCntrRel, a => a.CntrItemRel).OrderBy(it => it.T_FULL_TIME).ToList();

            // 2.查询重量差异最小的钢卷
            if (locList != null && locList.Count > 0)
            {
                float value = locList[0].LocCntrRel.CntrItemRel.F_WEIGHT;
                foreach (var loc in locList)
                {
                    float wg = loc.LocCntrRel.CntrItemRel.F_WEIGHT;
                    if (value >= wg)
                    {
                        value = wg;
                        location = loc;
                    }
                }
            }
            return location;
        }
        /// <summary>
        /// 获取小于需求重量的钢卷货位（最接近）
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="coilNo"></param>
        /// <param name="workNo"></param>
        /// <param name="weight"></param>
        /// <param name="width"></param>
        /// <returns></returns>
        public static Location GetLessThanGJWeightLocation(string itemCode, string coilNo, string workNo, string weight, string width)
        {
            var db = new SqlHelper<object>().GetInstance();
            List<string> areaCodes = Settings.getStoreAreaCodes(1, 2);
            Location location = null;
            if (location == null)
            {
                var query1 = db.Queryable<Location>().Where(it => areaCodes.Contains(it.S_AREA_CODE) && it.N_LOCK_STATE == 0 && it.C_ENABLE == "Y");
                if (itemCode != null)
                {
                    query1 = query1.Where(it => it.LocCntrRel.CntrItemRel.S_ITEM_CODE == itemCode);
                }
                if (coilNo != null)
                {
                    query1 = query1.Where(it => it.LocCntrRel.CntrItemRel.S_COIL_NO.Contains(coilNo));
                }
                if (workNo != null)
                {
                    query1 = query1.Where(it => it.LocCntrRel.CntrItemRel.S_WORK_NO == workNo);
                }
                if (weight != null)
                {
                    float f_weight = float.Parse(weight);
                    query1 = query1.Where(it => it.LocCntrRel.CntrItemRel.F_WEIGHT < f_weight);
                }
                if (width != null)
                {
                    query1 = query1.Where(it => it.LocCntrRel.CntrItemRel.S_WIDTH == width);
                }

                List<Location> locList1 = query1.Includes(a => a.LocCntrRel, a => a.CntrItemRel).ToList();
                if (locList1 != null && locList1.Count > 0)
                {
                    float value = locList1[0].LocCntrRel.CntrItemRel.F_WEIGHT;
                    foreach (var loc in locList1)
                    {
                        float wg = loc.LocCntrRel.CntrItemRel.F_WEIGHT;
                        if (value <= wg)
                        {
                            value = wg;
                            location = loc;
                        }
                    }
                }
            }
            return location;
        }

        internal static bool AddStockRecord(WMSTask task, CntrItemRel cntrItem) {
            StockRecord record = new StockRecord() {
                S_CODE = WMSHelper.GenerateStockRecordNo(),
            };
            if (cntrItem != null)
            {
                record.S_CNTR_CODE = cntrItem.S_CNTR_CODE;
                record.S_ITEM_CODE = cntrItem.S_ITEM_CODE;
                record.S_ITEM_NAME = cntrItem.S_ITEM_NAME;
                record.S_ITEM_TYPE = cntrItem.S_ITEM_TYPE;
                record.S_WORK_NO = cntrItem.S_WORK_NO;
                record.S_WORK_NAME = cntrItem.S_WORK_NAME;
                record.S_DEPART_NO = cntrItem.S_DEPART_NO;
                record.S_PARTDRAW_NO = cntrItem.S_PARTDRAW_NO;
                record.S_PART_NAME = cntrItem.S_PART_NAME;
                record.S_OPER_NO = cntrItem.S_OPER_NO;
                record.S_OPER_NAME = cntrItem.S_OPER_NAME;
                record.S_COIL_NO = cntrItem.S_COIL_NO;
                record.F_WEIGHT = cntrItem.F_WEIGHT;
                record.F_QTY = cntrItem.F_QTY;
                record.S_SOURCE_NO = task.S_CODE;
            }
            else
            {
                record.S_CNTR_CODE = task.S_CNTR_CODE;
                record.S_ITEM_TYPE = "空托盘";
            }

            if (task.S_TYPE.Contains("入库"))
            {
                record.S_ACT_TYPE = "入库";
                record.S_AREA_CODE = task.S_END_AREA;
                record.S_LOC_CODE = task.S_END_LOC;
                record.T_IN_TIME = DateTime.Now;
            }
            else if (task.S_TYPE.Contains("出库"))
            {
                record.S_ACT_TYPE = "出库";
                record.S_AREA_CODE = task.S_START_AREA;
                record.S_LOC_CODE = task.S_START_LOC;
                record.T_OUT_TIME = DateTime.Now;
            }
            var db = new SqlHelper<object>().GetInstance();
            return db.Insertable(record).ExecuteCommand() > 0;
        }

        internal static AreaRelevance getAreaRelevance(string areaCode, int actType, string conAreaCode )
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<AreaRelevance>().Where(a => a.S_AREA_CODE == areaCode && a.N_ACT_TYPE == actType && a.S_CON_AREACODE == conAreaCode).First();   
        }

        internal static LocMouldCntr getLocMouldCntr(string trayCode)
        {
            var db = new SqlHelper<object>().GetInstance();
            // 查询空托盘(未绑定模具)货位
            var locMouldCntr = db.Queryable<LocMouldCntr>()
                .Where(a => a.S_CNTR_CODE == trayCode)
                .OrderByDescending(a => a.T_CREATE)
                .First();
            return locMouldCntr;
        }

        /// <summary>
        /// 添加货位模具容器关系
        /// </summary>
        /// <param name="locMouldCntr"></param>
        /// <returns></returns>
        internal static bool addLocMouldCntr(LocMouldCntr locMouldCntr)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Insertable(locMouldCntr).ExecuteCommand() > 0;
        }

        /// <summary>
        /// 添加发货记录
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        internal static bool addReleaseRecord(ReleaseRecord record)
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Insertable(record).ExecuteCommand() > 0;
        }
        
    } 
}