using HH.WCS.Mobox3.YNJT_BZP_PT.models;
using HH.WCS.Mobox3.YNJT_PT.api;
using HH.WCS.Mobox3.YNJT_PT.dispatch;
using HH.WCS.Mobox3.YNJT_PT.models;
using HH.WCS.Mobox3.YNJT_PT.models.other;
using HH.WCS.Mobox3.YNJT_PT.util;
using Newtonsoft.Json;
using NLog.Fluent;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Runtime.Remoting.Messaging;
using System.Text;
using System.Threading.Tasks;
using static HH.WCS.Mobox3.YNJT_PT.api.ApiModel;

namespace HH.WCS.Mobox3.YNJT_PT.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 GenerateTaskGroupNo() {
            var id = SYSHelper.GetSerialNumber("任务组", "GP");
            var date = DateTime.Now.ToString("yyMMdd");
            return $"GP{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 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 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).First();
            }
            else {
                result = db.Queryable<WMSTask>().Where(a => a.S_CNTR_CODE.Contains(cntr)).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 }).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 WMSTask GetWmsTask(string code) {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<WMSTask>().Where(a => a.S_CODE == code).First();
        }
    
        /// <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) {
            Location end = null;
            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 && a.C_ENABLE == "Y").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;
        }


        // ----------------------------------------------- 印尼佳通 - 硫化--------------------------------------------

        /// <summary>
        /// 添加成型机下线记录
        /// </summary>
        /// <param name="record"></param>
        /// <returns></returns>
        public static bool addOffLineRecord(OffLineRecord record) 
        {
            bool result = false;
            var db = new SqlHelper<object>().GetInstance();
            var offLineRecord = db.Queryable<OffLineRecord>().Where(a => a.S_RFID == record.S_RFID).First();
            if (offLineRecord != null)
            {
                offLineRecord.S_DEVICE_NO = record.S_DEVICE_NO;
                offLineRecord.N_IS_URGENT = record.N_IS_URGENT;
                offLineRecord.T_OFF_TIME = record.T_OFF_TIME;
                offLineRecord.N_IS_FULL = record.N_IS_FULL;
                result = db.Updateable(offLineRecord).ExecuteCommand() > 0;
            }
            else 
            {
                result = db.Insertable(record).ExecuteCommand() > 0;
            }
            return result;
        }

        /// <summary>
        /// 查询成型机下线记录
        /// </summary>
        /// <param name="rfid"></param>
        /// <returns></returns>
        public static OffLineRecord getOffLineRecord(string rfid) 
        {
            OffLineRecord offLineRecord = null;
            var db = new SqlHelper<object>().GetInstance();
            if (rfid != null)
            {
                offLineRecord = db.Queryable<OffLineRecord>().Where(a => a.S_RFID == rfid).First();
            }
            return offLineRecord;
        }

        /// <summary>
        /// 查询物料条码信息
        /// </summary>
        /// <param name="barcode"></param>
        /// <returns></returns>
        public static GreenTireInformation GetGreenTireInformation(string barcode) 
        {
            GreenTireInformation greenTireInformation = null;
            var db = new SqlHelper<object>().GetInstance();
            if (barcode != null)
            {
                greenTireInformation = db.Queryable<GreenTireInformation>().Where(a => a.BARCODE == barcode).First();
            }
            return greenTireInformation;
        }

        /// <summary>
        /// 查询入库终点货位
        /// </summary>
        /// <param name="locNum">1.单货位 2.双货位</param>
        /// <returns></returns>
        public static EndLocGroup getInStockEndLoc(int locNum) 
        {
            var db = new SqlHelper<object>().GetInstance();
            EndLocGroup endLocGroup = new EndLocGroup();
            List<Location> locations = new List<Location>();
            // 1.按容积率从大到小，对巷道进行排序
            var roadwayOrderList = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y")
                .GroupBy(a => a.N_ROADWAY)
                .Select(a => new { roadway = a.N_ROADWAY ,  num = SqlFunc.AggregateCount(a.S_CODE)})
                .OrderByDescending(a => a.num)
                .ToList();

            // 查询单货位
            foreach (var order in roadwayOrderList)
            {
                if (locNum == 1) 
                {
                    /*// 查询所有相同物料货位
                    var sameItemLocList = db.Queryable<Location>()
                       .LeftJoin<LocCntrRel>((a, b) => a.S_CODE == b.S_LOC_CODE)
                       .LeftJoin<CntrItemRel>((a, b, c) => b.S_CNTR_CODE == c.S_CNTR_CODE)
                       .Where((a, b, c) => a.S_AREA_CODE == Settings.stockArea
                                       && a.N_ROADWAY == order.roadway
                                       && a.N_CURRENT_NUM == 1
                                       && a.N_LOCK_STATE == 0
                                       && a.C_ENABLE == "Y"
                                       && b.S_CNTR_CODE != null
                                       && c.S_ITEM_CODE == itemCode
                                       )
                       .OrderBy((a, b, c) => new { a.N_LAYER, a.N_COL })
                       .ToList();

                    // 查询相同物料的左右是否有空货位
                    foreach (var loc in sameItemLocList)
                    {
                        var leftLoc = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_ROADWAY == order.roadway && a.N_ROW == loc.N_ROW && a.N_COL == loc.N_COL - 1 && a.N_LAYER == loc.N_LAYER && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y").First();
                        if (leftLoc != null)
                        {
                            locations.Add(leftLoc);
                            break;
                        }

                        var rightLoc = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_ROADWAY == order.roadway && a.N_ROW == loc.N_ROW && a.N_COL == loc.N_COL + 1 && a.N_LAYER == loc.N_LAYER && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y").First();
                        if (rightLoc != null)
                        {
                            locations.Add(leftLoc);
                            break;
                        }
                    }*/

                    // 查询空货位
                    var emptyLoc = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_ROADWAY == order.roadway && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y").OrderBy(a => new { a.N_LAYER, a.N_COL }).First();
                    if (emptyLoc != null) 
                    {
                        locations.Add(emptyLoc);
                        endLocGroup.endLocList = locations;
                        return endLocGroup;
                    }
                }

                if (locNum == 2) 
                {
                    var emptyLocList = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_ROADWAY == order.roadway && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y").OrderBy(a => new { a.N_LAYER, a.N_COL }).ToList();

                    if (emptyLocList.Count > 0)
                    {
                        // 查询双拖空货位
                        if (locations.Count == 0) 
                        {
                            foreach (var loc in emptyLocList)
                            {
                                var leftLoc = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_ROADWAY == order.roadway && a.N_ROW == loc.N_ROW && a.N_COL == loc.N_COL - 1 && a.N_LAYER == loc.N_LAYER && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y").First();
                                if (leftLoc != null)
                                {
                                    locations.Add(loc);
                                    locations.Add(leftLoc);
                                    break;
                                }

                                var rightLoc = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_ROADWAY == order.roadway && a.N_ROW == loc.N_ROW && a.N_COL == loc.N_COL + 1 && a.N_LAYER == loc.N_LAYER && a.N_CURRENT_NUM == 0 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y").First();
                                if (rightLoc != null)
                                {
                                    locations.Add(loc);
                                    locations.Add(leftLoc);
                                    break;
                                }
                            }

                            if (locations.Count == locNum)
                            {
                                endLocGroup.groupNo = WMSHelper.GenerateTaskGroupNo();
                                endLocGroup.endLocList = locations;
                                return endLocGroup;
                            }
                        }

                        // 查询单拖货位
                        foreach (var loc in emptyLocList)
                        {
                            locations.Add(loc);
                            if (locations.Count == locNum)
                            {
                                endLocGroup.endLocList = locations;
                                return endLocGroup;
                            }
                        }
                    }
                } 
            }
            return endLocGroup;
        }

        public class EndLocGroup
        {
            public string groupNo { get; set; }
            public List<Location> endLocList { get; set; }
        }

        /// <summary>
        /// 查询出库开始货位
        /// 计算（1.巷道不报警、2.物料状态OK、3.小于失效时间 大于等于生效时间 4.加急料先出、5.先入先出（生产时间））出库物料
        /// </summary>
        /// <param name="itemCode"></param>
        /// <param name="locNum"></param>
        public static StartLocGroup getOutStockStartLoc(string itemCode, int locNum ) 
        {
            var db = new SqlHelper<object>().GetInstance();
            StartLocGroup startLocGroup = new StartLocGroup();

            // 1.查询（物料状态OK ，且小于失效时间，大于等于生效时间）出库物料，并按加急料先出，先入先出（生产时间）的原则进行排序
            var query = db.Queryable<Location>()
               .LeftJoin<LocCntrRel>((a, b) => a.S_CODE == b.S_LOC_CODE)
               .LeftJoin<CntrItemRel>((a, b, c) => b.S_CNTR_CODE == c.S_CNTR_CODE)
               .Where((a, b, c) => a.S_AREA_CODE == Settings.stockArea
                               && a.N_CURRENT_NUM == 1
                               && a.N_LOCK_STATE == 0
                               && a.C_ENABLE == "Y"
                               && b.S_CNTR_CODE != null
                               );

            if (itemCode != null)
            {
                query = query.Where((a, b, c) => c.S_ITEM_CODE == itemCode
                               && c.S_ITEM_STATE == "OK"
                               && SqlFunc.ToDate(c.S_EFFECTIVE_TIME) <= SqlFunc.GetDate()  // 生效时间早于当前时间
                               && SqlFunc.ToDate(c.S_EXPIRATION_TIME) >= SqlFunc.GetDate() // 失效时间晚于当前时间
                               && c.S_EFFECTIVE_TIME != null
                               && c.S_EXPIRATION_TIME != null)
                            .OrderByDescending((a, b, c) => c.N_URGENT_FLAG)
                            .OrderBy((a, b, c) => c.S_TXNDATE);
            }
            else 
            {
                query = query.Where((a, b, c) => c.S_ITEM_CODE == itemCode);
            }
            var outLocList = query.ToList();

            if (outLocList.Count > 0)
            {
                foreach (var loc in outLocList)
                {
                    // 查询巷道是否正常，异常跳过
                    var stackerStates = WCSDispatch.getStackerState(loc.N_ROADWAY);
                    if (stackerStates.Count == 0 || stackerStates[0].roadway_state == "0" || stackerStates[0].roadway_state == "3") 
                    {
                        continue;
                    }

                    var cntrItemRel = db.Queryable<CntrItemRel>().LeftJoin<LocCntrRel>((a, b) => a.S_CNTR_CODE == b.S_CNTR_CODE).Where((a, b) => b.S_LOC_CODE == loc.S_CODE).First();
                    if (cntrItemRel != null) 
                    {
                        startLocGroup.startLocList.Add(loc);
                        if (locNum == startLocGroup.startLocList.Count) 
                        {
                            break;
                        }

                        var leftLoc = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_ROADWAY == loc.N_ROADWAY && a.N_ROW == loc.N_ROW && a.N_COL == loc.N_COL - 1 && a.N_LAYER == loc.N_LAYER && a.N_CURRENT_NUM == 1 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y").First();
                        if (leftLoc != null)
                        {
                            var locCntrRel = db.Queryable<LocCntrRel>()
                               .LeftJoin<CntrItemRel>((a, b) => a.S_CNTR_CODE == b.S_CNTR_CODE)
                               .Where((a, b) => a.S_LOC_CODE == leftLoc.S_CODE && b.S_ITEM_CODE == itemCode)
                               .First();
                            if (locCntrRel != null)
                            {
                                startLocGroup.groupNo = WMSHelper.GenerateTaskGroupNo();
                                startLocGroup.startLocList.Add(leftLoc);
                                break;
                            }
                        }

                        var rightLoc = db.Queryable<Location>().Where(a => a.S_AREA_CODE == Settings.stockArea && a.N_ROADWAY == loc.N_ROADWAY && a.N_ROW == loc.N_ROW && a.N_COL == loc.N_COL + 1 && a.N_LAYER == loc.N_LAYER && a.N_CURRENT_NUM == 1 && a.N_LOCK_STATE == 0 && a.C_ENABLE == "Y").First();
                        if (rightLoc != null && startLocGroup.startLocList.Count == 0)
                        {
                            var locCntrRel = db.Queryable<LocCntrRel>()
                                .LeftJoin<CntrItemRel>((a, b) => a.S_CNTR_CODE == b.S_CNTR_CODE)
                                .Where((a, b) => a.S_LOC_CODE == rightLoc.S_CODE && b.S_ITEM_CODE == itemCode)
                                .First();
                            if (locCntrRel != null)
                            {
                                startLocGroup.groupNo = WMSHelper.GenerateTaskGroupNo();
                                startLocGroup.startLocList.Add(rightLoc);
                                break;
                            }
                        }
                    }
                }
            }
            return startLocGroup;

        }

        public class StartLocGroup
        {
            public string groupNo {  get; set; }
            public List<Location> startLocList { get; set; }
        }

        /// <summary>
        /// 查询异常区空货位
        /// </summary>
        /// <param name="areaCode"></param>
        public static Location getAbnormalAreaEmptyLoc(string areaCode) 
        {
            Location location = new Location();
            var db = new SqlHelper<object>().GetInstance();
            location = db.Queryable<Location>().Where(a => a.S_AREA_CODE == areaCode && a.N_CURRENT_NUM == 0 && a.C_ENABLE == "Y" && a.N_LOCK_STATE == 0).First();
            return location;
        }

        /// <summary>
        /// 查询巷道任务最少的接驳位
        /// </summary>
        /// <returns></returns>
        public static Location getMinTaskMiddleLoc(int type) 
        {
            Location location = new Location();
            var db = new SqlHelper<object>().GetInstance();

            var roadwayGroup = db.Queryable<Location>()
                    .LeftJoin<WCSTask>((a, b) => a.S_CODE == b.S_END_LOC && b.N_B_STATE < 3)
                    .Where((a, b) => a.S_AREA_CODE == Settings.stockArea)
                    .GroupBy((a, b) => a.N_ROADWAY)
                    .Select((a, b) => new { roadway = a.N_ROADWAY, num = SqlFunc.AggregateDistinctCount(b.S_CODE != null) })
                    .OrderBy(a => a.num)
                    .ToList();

            foreach (var item in roadwayGroup)
            {
                // 1.查询巷道内的堆垛机状态是否正常

                // 2.查询接驳位
                var connectLoc = Settings.connectLocList.Where(a => a.type == type && a.roadway == item.roadway).FirstOrDefault();
                if (connectLoc != null) 
                {
                    location = LocationHelper.GetLoc(connectLoc.locCode);
                    break;
                }
            }
            return location;
        }

        /// <summary>
        /// 查询物料存放时间配置信息
        /// </summary>
        /// <param name="bc_entried"></param>
        /// <returns></returns>
        public static Overage getOverage(string bc_entried)
        {

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

            // 直接执行 SQL（参数化查询）
            var sql = "SELECT get_ovg_bar(@barcode, @mcngrp) AS overage_value";
            var sql1 = "SELECT get_minhour_bar(@barcode, @mcngrp) AS overage_value";

            // 使用匿名对象传递参数
            var ovg_bar = db.Ado.SqlQuery<int>(sql, new { barcode = bc_entried, mcngrp = "0" });
            var minhour_bar = db.Ado.SqlQuery<float>(sql1, new { barcode = bc_entried, mcngrp = "0" });
            if (ovg_bar.Count > 0 && minhour_bar.Count > 0)
            {
                Overage overage = new Overage()
                {
                    MINHOUR = minhour_bar[0],
                    OVERAGE = ovg_bar[0]
                };
                return overage;
            }
            return null;
        }

        /// <summary>
        /// 绑定条码物料信息
        /// </summary>
        /// <param name="cntrCode"></param>
        /// <param name="greenTireInformation"></param>
        /// <returns></returns>
        public static bool bindBarcodeItemInfo( string cntrCode,GreenTireInformation greenTireInformation) 
        {
            bool result = false;
            var db = new SqlHelper<object>().GetInstance();
            var cntrItemRel = db.Queryable<CntrItemRel>().Where(a => a.S_CNTR_CODE == cntrCode).First();
            if (cntrItemRel == null)
            {
                var overage = getOverage(greenTireInformation.BARCODE);
                DateTime txndate = DateTime.Parse(greenTireInformation.TXNDATE);
                DateTime minTime = txndate.AddHours(overage.MINHOUR);
                DateTime maxTime = txndate.AddDays(overage.OVERAGE);
                cntrItemRel = new CntrItemRel()
                {
                    S_CG_ID = greenTireInformation.BARCODE,
                    S_ITEM_CODE = greenTireInformation.ITEMCODE,
                    S_CNTR_CODE = cntrCode,
                    S_CELL_NO = greenTireInformation.TIRECODE,
                    F_QTY = greenTireInformation.QTY,
                    S_MCN = greenTireInformation.MCN,
                    S_OPR = greenTireInformation.OPR,
                    S_OPR02 = greenTireInformation.OPR02,
                    S_OPR03 = greenTireInformation.OPR03,
                    S_OPR04 = greenTireInformation.OPR04,
                    S_WINDUP = greenTireInformation.WINDUP,
                    S_TXNDATE = greenTireInformation.TXNDATE,
                    S_DATE_SHIFT = greenTireInformation.DATE_SHIFT,
                    S_EFFECTIVE_TIME = minTime.ToString("yyyy-MM-dd HH:mm:ss"),
                    S_EXPIRATION_TIME = maxTime.ToString("yyyy-MM-dd HH:mm:ss"),
                };
                result = db.Insertable(cntrItemRel).ExecuteCommand() > 0;
            }
            else 
            {
                var overage = getOverage(greenTireInformation.BARCODE);
                DateTime txndate = DateTime.Parse(greenTireInformation.TXNDATE);
                DateTime minTime = txndate.AddHours(overage.MINHOUR);
                DateTime maxTime = txndate.AddDays(overage.OVERAGE);
                cntrItemRel.S_CG_ID = greenTireInformation.BARCODE;
                cntrItemRel.S_ITEM_CODE = greenTireInformation.ITEMCODE;
                cntrItemRel.S_CNTR_CODE = cntrCode;
                cntrItemRel.S_CELL_NO = greenTireInformation.TIRECODE;
                cntrItemRel.F_QTY = greenTireInformation.QTY;
                cntrItemRel.S_MCN = greenTireInformation.MCN;
                cntrItemRel.S_OPR = greenTireInformation.OPR;
                cntrItemRel.S_OPR02 = greenTireInformation.OPR02;
                cntrItemRel.S_OPR03 = greenTireInformation.OPR03;
                cntrItemRel.S_OPR04 = greenTireInformation.OPR04;
                cntrItemRel.S_WINDUP = greenTireInformation.WINDUP;
                cntrItemRel.S_TXNDATE = greenTireInformation.TXNDATE;
                cntrItemRel.S_DATE_SHIFT = greenTireInformation.DATE_SHIFT;
                cntrItemRel.S_EFFECTIVE_TIME = minTime.ToString("yyyy-MM-dd HH:mm:ss");
                cntrItemRel.S_EXPIRATION_TIME = maxTime.ToString("yyyy-MM-dd HH:mm:ss");
                result = db.Updateable(cntrItemRel).ExecuteCommand() > 0;
            }
            return result;
        }

        /// <summary>
        /// 查询硫化工单
        /// </summary>
        /// <param name="dateShift"></param>
        /// <param name="mcn"></param>
        /// <param name="shift"></param>
        /// <returns></returns>
        public static ProductionShedule getProductionShedule(string dateShift ,string mcn ,string shift) 
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<ProductionShedule>().Where(a => a.DATESHIFT == dateShift && a.MCN == mcn && a.SHIFT == shift).First();
        }

        /// <summary>
        /// 查询胚胎完成硫化的数量
        /// </summary>
        /// <returns></returns>
        public static int getEmbryoFinishNum(string dateShift, string mcn, string shift) 
        {
            var db = new SqlHelper<object>().GetInstance();
            return db.Queryable<EmbryoFinishRecord>().Where(a => a.DATE_SHIFT == dateShift && a.CUR_MCN == mcn && a.CUR_SHIFT == shift).Count();
        }
    }    
}