using AutoMapper;
using Kean.Infrastructure.Database;
using Kean.Infrastructure.Database.Repository.Default;
using Kean.Infrastructure.Database.Repository.Default.Entities;
using Kean.Infrastructure.Database.Repository.Default.Entities.Interface;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Kean.Infrastructure.Repository
{
    /// <summary>
    /// 库房仓库
    /// </summary>
    public class WarehouseRepository :
        Domain.Stock.Repositories.IWarehouseRepository,
        Domain.Task.Repositories.IWarehouseRepository
    {
        private readonly IMapper _mapper; // 模型映射
        private readonly IDefaultDb _database; // 默认数据库

        /// <summary>
        /// 依赖注入
        /// </summary>
        public WarehouseRepository(
            IMapper mapper,
            IDefaultDb database)
        {
            _mapper = mapper;
            _database = database;
        }

        /*
         * 实现 Kean.Domain.Stock.Repositories.IWarehouseRepository.IsCell 方法
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.IsCell 方法
         */
        public async Task<bool?> IsCell(int id)
        {
            var type = (await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_ID == id)
                .Single(c => new { c.CELL_TYPE }))?.CELL_TYPE;
            return type == null ? null : type == nameof(Domain.Task.Models.Cell);
        }

        /*
         * 实现 Kean.Domain.Stock.Repositories.IWarehouseRepository.UpdateCell 方法
         */
        public async Task UpdateCell(int id, Domain.Stock.Enums.CellState state)
        {
            await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_ID == id)
                .Update(new
                {
                    CELL_STATUS = state.ToString(),
                    UPDATE_TIME = DateTime.Now
                });
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.UpdateCell 方法
         */
        public async Task UpdateCell(int id, string cellState)
        {
            await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_ID == id)
                .Update(new
                {
                    CELL_STATUS = cellState,
                    UPDATE_TIME = DateTime.Now
                });
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetStationById 方法
         */
        public async Task<Domain.Task.Models.Station> GetStationById(int id)
        {
            return _mapper.Map<Domain.Task.Models.Station>(await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_ID == id && c.CELL_TYPE == nameof(Domain.Task.Models.Station))
                .Single());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetStationByName 方法
         */
        public async Task<Domain.Task.Models.Station> GetStationByName(string name, int warehouse)
        {
            return _mapper.Map<Domain.Task.Models.Station>(await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_NAME == name && c.WAREHOUSE_ID == warehouse && c.CELL_TYPE == nameof(Domain.Task.Models.Station))
                .Single());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetStationByDevice 方法
         */
        public async Task<Domain.Task.Models.Station> GetStationByDevice(string device, int warehouse)
        {
            return _mapper.Map<Domain.Task.Models.Station>(await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_CODE == device && c.CELL_TYPE == nameof(Domain.Task.Models.Station))
                .Single());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetCellById 方法
         */
        public async Task<Domain.Task.Models.Cell> GetCellById(int id)
        {
            return _mapper.Map<Domain.Task.Models.Cell>(await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_ID == id && c.CELL_TYPE == nameof(Domain.Task.Models.Cell))
                .Single());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetCellByName 方法
         */
        public async Task<Domain.Task.Models.Cell> GetCellByName(string name, int warehouse)
        {
            return _mapper.Map<Domain.Task.Models.Cell>(await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_NAME == name && c.WAREHOUSE_ID == warehouse && c.CELL_TYPE == nameof(Domain.Task.Models.Cell))
                .Single());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetCellByDevice 方法
         */
        public async Task<Domain.Task.Models.Cell> GetCellByDevice(string device, int warehouse)
        {
            return _mapper.Map<Domain.Task.Models.Cell>(await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_CODE == device && c.WAREHOUSE_ID == warehouse)
                .Single());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetFreeCell 方法
         */
        public async Task<IEnumerable<Domain.Task.Models.Cell>> GetFreeCell(int laneway, int? spec, string pallet, int? goodsid)
        {
            var lsSelectedCell = _database.From<T_WH_CELL>()
                .Where(c => c.CELL_LANEWAY == laneway
                && c.CELL_TYPE == nameof(Domain.Task.Models.Cell)
                //&& c.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
                && c.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Selected))
                .Query(r => r.CELL_ID);

            int flag = 1;

            var goodType = await _database.From<T_GOODS_MAIN>().Where(r => r.GOODS_ID == goodsid).Single();
            //@=@ 固定值 pallet 5,9
            //if (goodsid == 5 || goodsid == 9 || goodsid == 57 || goodsid == 58 || goodsid == 60 || goodsid == 61 || goodsid == 62)
            if(goodType.TYPE_ID == "4")
            {
                flag = 0;
            }

            var schema = _database.From<T_WH_CELL>()
                .Where(c => c.CELL_LANEWAY == laneway
                        && c.CELL_TYPE == nameof(Domain.Task.Models.Cell)
                        && c.CELL_FLAG == true
                        && c.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
                        && c.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled)
                        && (c.CELL_PALLET == null || c.CELL_PALLET.Contains(pallet))
                         && c.CELL_DEEP > 0);
               // .OrderBy(r => r.CELL_DEEP, flag == 1 ? Infrastructure.Database.Order.Descending : Infrastructure.Database.Order.Ascending);

            var cell = await schema.Where(c => (c.CELL_INNER == null || !lsSelectedCell.Contains(c.CELL_INNER))
                        && (c.CELL_OUTER == null || !lsSelectedCell.Contains(c.CELL_OUTER)))
                .Select();
            return _mapper.Map<IEnumerable<Domain.Task.Models.Cell>>(cell);
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetFreeCell 方法
         */
        public async Task<IEnumerable<Domain.Task.Models.Cell>> GetFreeCell(IEnumerable<int> area, int? spec, string pallet)
        {
            pallet = $"{pallet}";
            var schema = _database.From<T_WH_CELL>()
                .Where(c => area.Contains(c.AREA_ID)
                        && c.CELL_TYPE == nameof(Domain.Task.Models.Cell)
                        && c.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
                        && c.CELL_FLAG == true
                        && c.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled)
                        && (c.CELL_PALLET == null || c.CELL_PALLET.Contains(pallet)));
            if (spec.HasValue)
            {
                schema = schema.Where(c => c.CELL_SPEC == null || c.CELL_SPEC >= spec);
            }
            return _mapper.Map<IEnumerable<Domain.Task.Models.Cell>>(await schema.Select());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetInnerCell 方法
         */
        public async Task<Domain.Task.Models.Cell> GetInnerCell(int id)
        {
            return _mapper.Map<Domain.Task.Models.Cell>(await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_OUTER == id)
                .Single());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetOuterCell 方法
         */
        public async Task<Domain.Task.Models.Cell> GetOuterCell(int id)
        {
            return _mapper.Map<Domain.Task.Models.Cell>(await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_INNER == id)
                .Single());
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.LockCell 方法
         */
        public async Task LockCell(params int[] id)
        {
            await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_TYPE == nameof(Domain.Task.Models.Cell) && id.Contains(c.CELL_ID))
                .Update(new
                {
                    RUN_STATUS = nameof(Domain.Task.Enums.CellState.Selected),
                    UPDATE_TIME = DateTime.Now
                });
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.ReleaseCell 方法
         */
        public async Task ReleaseCell(params int[] id)
        {
            await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_TYPE == nameof(Domain.Task.Models.Cell) && id.Contains(c.CELL_ID))
                .Update(new
                {
                    RUN_STATUS = nameof(Domain.Task.Enums.CellState.Enabled),
                    CELL_TAG = "",
                    UPDATE_TIME = DateTime.Now
                });
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.DisableCell 方法
         */
        public async Task DisableCell(string celltag, params int[] id)
        {
            await _database.From<T_WH_CELL>()
                .Where(c => c.CELL_TYPE == nameof(Domain.Task.Models.Cell) && id.Contains(c.CELL_ID))
                .Update(new
                {
                    RUN_STATUS = nameof(Domain.Task.Enums.CellState.Disabled),
                    CELL_TAG = celltag,
                    UPDATE_TIME = DateTime.Now
                });
        }

        ////////////////////////////////////////////////////////
        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetFreeCell 方法
         */
        public async Task<IEnumerable<Domain.Task.Models.Cell>> GetFreeCell(string startDevice, int? spec, string pallet, int? goodsid)
        {
            pallet = $"{pallet}";

            var lanewayRouteQuery = _database.From<IO_CONTROL_ROUTE>()
                .Where(r => r.START_DEVICE == startDevice && r.CONTROL_ROUTE_STATUS == 1 && r.CONTROL_ROUTE_TYPE == 1)
                .Query(r => r.END_DEVICE);

            var lanewayRoute = (await _database.From<IO_CONTROL_ROUTE>()
               .Where(r => r.START_DEVICE == startDevice && r.CONTROL_ROUTE_STATUS == 1 && r.CONTROL_ROUTE_TYPE == 1)
               .Select())
               .Select(r=>new { CELL_LANEWAY = Convert.ToInt32(r.END_DEVICE)});

            var lanewayManageCount = (await _database.From<V_MANAGE_MAIN>()
                          .Where(r => r.MANAGE_TYPE_CODE == "Infeed" && lanewayRouteQuery.Contains(r.LANEWAY))
                          .Select())
                          .GroupBy(r => r.LANEWAY).Select(r => new {
                              CELL_LANEWAY = r.Key,
                              LANEWAY_MANAGE_COUNT = r.Count() / 2
                          });

            var schemaStorage = _database.From<V_STORAGE_LIST>();
            if (goodsid.HasValue)
            {
                schemaStorage.Where(r=>r.GOODS_ID == goodsid.Value);
            }
            var lanewayStorageCount = (await schemaStorage
                .Where(r => lanewayRouteQuery.Contains(r.CELL_LANEWAY))
                .Select())
                .GroupBy(r => r.CELL_LANEWAY).Select(r => new {
                    CELL_LANEWAY = r.Key,
                    LANEWAY_STORAGE_COUNT = r.Count()
                });

            var lanewayCellCount = (await _database.From<T_WH_CELL>()
              .Where(r => lanewayRouteQuery.Contains(r.CELL_LANEWAY)
                 && r.CELL_TYPE == nameof(Domain.Task.Models.Cell)
                 && r.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
                 && r.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled)
                                                && r.CELL_FLAG == true
                 && (r.CELL_PALLET == null || r.CELL_PALLET.Contains(pallet)))
              .Select())
              .GroupBy(r => r.CELL_LANEWAY).Select(r => new {
                  CELL_LANEWAY = r.Key,
                  LANEWAY_CELL_COUNT = r.Count()
              });

            var lanewayCellDeepCount = (await _database.From<T_WH_CELL>()
              .Where(r => lanewayRouteQuery.Contains(r.CELL_LANEWAY)
                 && r.CELL_TYPE == nameof(Domain.Task.Models.Cell)
                 && r.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
                 && r.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled)
                  && r.CELL_DEEP >0
                               && r.CELL_FLAG == true
                 && (r.CELL_PALLET == null || r.CELL_PALLET.Contains(pallet)))
              .Select())
              .GroupBy(r => r.CELL_LANEWAY).Select(r => new {
                  CELL_LANEWAY = r.Key,
                  LANEWAY_CELL_COUNT = r.Count()
              });

            List<dynamic> lsLaneway = new List<dynamic>();
            foreach (var m in lanewayRoute)
            {
                int cell_laneway = m.CELL_LANEWAY;
                int laneway_manage_count = 0;
                int laneway_storage_count = 0;
                int laneway_cell_count = 0;
                int laneway_cellDeep_count = 0;

                var mc = lanewayManageCount.Where(r => r.CELL_LANEWAY == m.CELL_LANEWAY);
                if (mc.Count() > 0)
                {
                    laneway_manage_count = mc.First().LANEWAY_MANAGE_COUNT;
                }
                var sc = lanewayStorageCount.Where(r => r.CELL_LANEWAY == m.CELL_LANEWAY);
                if (sc.Count() > 0)
                {
                    laneway_storage_count = sc.First().LANEWAY_STORAGE_COUNT;
                }

                var cc = lanewayCellCount.Where(r => r.CELL_LANEWAY == m.CELL_LANEWAY);
                if (cc.Count() > 0)
                {
                    laneway_cell_count = cc.First().LANEWAY_CELL_COUNT;
                }

                var cdc = lanewayCellDeepCount.Where(r => r.CELL_LANEWAY == m.CELL_LANEWAY);
                if (cdc.Count() > 0)
                {
                    laneway_cellDeep_count = cdc.First().LANEWAY_CELL_COUNT;
                }

                lsLaneway.Add(new
                {
                    CELL_LANEWAY= cell_laneway,
                    LANEWAY_MANAGE_COUNT = laneway_manage_count,
                    LANEWAY_STORAGE_COUNT = laneway_storage_count,
                    LANEWAY_CELL_COUNT = laneway_cell_count,
                    LANEWAY_CELL_DEEP_COUNT = laneway_cellDeep_count
                });
            }

            var query = from t in lsLaneway
                        orderby t.LANEWAY_MANAGE_COUNT, t.LANEWAY_STORAGE_COUNT, t.LANEWAY_CELL_COUNT descending
                        select t;
            //outcell innercell 都是空的+空闲
            foreach (var m in query)
            {
                int laneway = m.CELL_LANEWAY;

                var lsInnerOuterCellEnable = _database.From<T_WH_CELL>()
               .Where(c => c.CELL_LANEWAY == laneway
               && c.CELL_TYPE == nameof(Domain.Task.Models.Cell)
               && c.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
               && c.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled))
                .Query(r => r.CELL_ID);

                int flag = 1;

                var goodType = await _database.From<T_GOODS_MAIN>().Where(r => r.GOODS_ID == goodsid).Single();

                //@=@ 固定值 pallet5,9
                //if (goodsid == 5 || goodsid == 9 || goodsid == 57 || goodsid == 58 || goodsid == 60 || goodsid == 61 || goodsid == 62)
                if(goodType.TYPE_ID == "4")
                {
                    flag = 0;
                }

                if (m.LANEWAY_CELL_DEEP_COUNT < 11)
                {
                    flag = 0;
                }

                var schema = _database.From<T_WH_CELL, V_WH_CELL>()
                    .Join(Join.Left, (m, n) => m.CELL_OUTER == n.CELL_ID)
               .Where((c, n) => c.CELL_LANEWAY == laneway
                && c.CELL_TYPE == nameof(Domain.Task.Models.Cell)
                && c.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
                && c.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled)
                && c.CELL_FLAG == true
                && (c.CELL_PALLET == null || c.CELL_PALLET.Contains(pallet))
                && (c.CELL_INNER == null || lsInnerOuterCellEnable.Contains(c.CELL_INNER))
                && (c.CELL_OUTER == null || lsInnerOuterCellEnable.Contains(c.CELL_OUTER))
                )
               .OrderBy((r, n) => r.CELL_DEEP, flag == 1 ? Infrastructure.Database.Order.Descending : Infrastructure.Database.Order.Ascending)
               .OrderBy((r, n) => n.CELL_STATUS, Infrastructure.Database.Order.Ascending)
               .OrderBy((r, n) => r.CELL_Y, Infrastructure.Database.Order.Ascending)
               .OrderBy((r, n) => r.CELL_X, Infrastructure.Database.Order.Ascending);

                //if (spec.HasValue)
                //{
                //    schema = schema.Where(c => c.CELL_SPEC == null || c.CELL_SPEC >= spec);
                //}

                var cellId = await schema.Select((m,n)=>new { 
                    m.CELL_ID
                });


                if (cellId.Count()>0)
                {
                    int id = cellId.First().CELL_ID;
                    var cell = await _database.From<T_WH_CELL>()
                        .Where(r => r.CELL_ID == id)
                        .Select();
                    return _mapper.Map<IEnumerable<Domain.Task.Models.Cell>>(cell);
                }
            }

            //outcell innercell 都是只是空闲
            foreach (var m in query)
            {
                int laneway = m.CELL_LANEWAY;

                var lsInnerOuterCellEnable = _database.From<T_WH_CELL>()
               .Where(c => c.CELL_LANEWAY == laneway
               && c.CELL_TYPE == nameof(Domain.Task.Models.Cell)
               //&& c.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
               && c.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled))
                .Query(r => r.CELL_ID);

                int flag = 1;

                var goodType = await _database.From<T_GOODS_MAIN>().Where(r => r.GOODS_ID == goodsid).Single();

                //@=@ 固定值 pallet5,9
                //if (goodsid == 5 || goodsid == 9 || goodsid == 57 || goodsid == 58 || goodsid == 60 || goodsid == 61 || goodsid == 62)
                if (goodType.TYPE_ID == "4")
                {
                    flag = 0;
                }

                if (m.LANEWAY_CELL_DEEP_COUNT < 11)
                {
                    flag = 0;
                }

                var schema = _database.From<T_WH_CELL, V_WH_CELL>()
                    .Join(Join.Left, (m, n) => m.CELL_OUTER == n.CELL_ID)
               .Where((c, n) => c.CELL_LANEWAY == laneway
                && c.CELL_TYPE == nameof(Domain.Task.Models.Cell)
                && c.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
                && c.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled)
                && c.CELL_FLAG == true
                && (c.CELL_PALLET == null || c.CELL_PALLET.Contains(pallet))
                && (c.CELL_INNER == null || lsInnerOuterCellEnable.Contains(c.CELL_INNER))
                && (c.CELL_OUTER == null || lsInnerOuterCellEnable.Contains(c.CELL_OUTER))
                )
               .OrderBy((r, n) => r.CELL_DEEP, flag == 1 ? Infrastructure.Database.Order.Descending : Infrastructure.Database.Order.Ascending)
               .OrderBy((r, n) => n.CELL_STATUS, Infrastructure.Database.Order.Ascending)
               .OrderBy((r, n) => r.CELL_Y, Infrastructure.Database.Order.Ascending)
               .OrderBy((r, n) => r.CELL_X, Infrastructure.Database.Order.Ascending);

                //if (spec.HasValue)
                //{
                //    schema = schema.Where(c => c.CELL_SPEC == null || c.CELL_SPEC >= spec);
                //}

                var cellId = await schema.Select((m, n) => new {
                    m.CELL_ID
                });


                if (cellId.Count() > 0)
                {
                    int id = cellId.First().CELL_ID;
                    var cell = await _database.From<T_WH_CELL>()
                        .Where(r => r.CELL_ID == id)
                        .Select();
                    return _mapper.Map<IEnumerable<Domain.Task.Models.Cell>>(cell);
                }
            }
            return null;

        }

        /*
         * 实现 Kean.Domain.Stock.Repositories.IWarehouseRepository.GetFreeCell 方法
         */
        public async Task<string> GetInStartDevice(int original, int? spec, string pallet, int? goodsid, string barcode)
        {
            var startCache = await _database.From<T_WH_CELL>()
                .Where(r => r.CELL_ID == original)
                .Single();

            var startStations = _database.From<T_WH_CELL>()
                .Where(r => r.AREA_ID == startCache.AREA_ID && r.CELL_IN == true)
                .Query(r => r.CELL_CODE);

            //应急模式由out变为In 校验此站台出库任务没有，终点货位没有 才可以入库
            var manageStations = await _database.From<V_MANAGE_MAIN>()
                .Where(r => r.END_AREA_ID == startCache.AREA_ID)
                .Select();

            //var emergencyStationModel = await _database.From<IO_STATION_MODEL>()
            //    .Where(r => r.INOUT == "1" && r.IS_FULL == "1")
            //    .Select();

            pallet = $"{pallet}";

            var lanewayRouteQuery = _database.From<IO_CONTROL_ROUTE>()
                .Where(r => startStations.Contains(r.START_DEVICE) 
                && r.CONTROL_ROUTE_STATUS == 1 
                && r.CONTROL_ROUTE_TYPE == 1 
                )
                .Query(r => r.END_DEVICE);

            var lanewayRoute = (await _database.From<IO_CONTROL_ROUTE>()
               .Where(r => startStations.Contains(r.START_DEVICE) 
               && r.CONTROL_ROUTE_STATUS == 1 
               && r.CONTROL_ROUTE_TYPE == 1
               )
               .Select())
               .Select(r => new { CELL_LANEWAY = Convert.ToInt32(r.END_DEVICE), START_DEVICE= r.START_DEVICE });

            var lanewayManageCount = (await _database.From<V_MANAGE_MAIN>()
                          .Where(r => lanewayRouteQuery.Contains(r.LANEWAY))
                          .Select())
                          .GroupBy(r => r.LANEWAY).Select(r => new {
                              CELL_LANEWAY = r.Key,
                              LANEWAY_MANAGE_COUNT = r.Count() % 4
                          });

            var schemaStorage = _database.From<V_STORAGE_LIST>();
            if (goodsid.HasValue)
            {
                schemaStorage.Where(r => r.GOODS_ID == goodsid.Value);
            }
            var lanewayStorageCount = (await schemaStorage
                .Where(r => lanewayRouteQuery.Contains(r.CELL_LANEWAY))
                .Select())
                .GroupBy(r => r.CELL_LANEWAY).Select(r => new {
                    CELL_LANEWAY = r.Key,
                    LANEWAY_STORAGE_COUNT = r.Count()
                });

            var lanewayAGVCount = (await _database.From<T_STORAGE_MAIN, T_WH_CELL>()
                 .Join(Join.Inner, (r, t) => r.CELL_ID == t.CELL_ID && t.CELL_TYPE != "Cell")
                 .Select((r,t) => r.APPLYIN_LANEWAY))
                 .GroupBy(r=>r.APPLYIN_LANEWAY)
                 .Select(r => new {
                     CELL_LANEWAY = r.Key,
                     LANEWAY_AGV_COUNT = r.Count()
                 });


            var lanewayCellCount = (await _database.From<T_WH_CELL>()
              .Where(r => lanewayRouteQuery.Contains(r.CELL_LANEWAY)
                 && r.CELL_TYPE == nameof(Domain.Task.Models.Cell)
                 && r.CELL_STATUS == nameof(Domain.Stock.Enums.CellState.Empty)
                 && r.RUN_STATUS == nameof(Domain.Task.Enums.CellState.Enabled)
                 && (r.CELL_PALLET == null || r.CELL_PALLET.Contains(pallet)))
              .Select())
              .GroupBy(r => r.CELL_LANEWAY).Select(r => new {
                  CELL_LANEWAY = r.Key,
                  LANEWAY_CELL_COUNT = r.Count()
              });

            List<dynamic> lsLaneway = new List<dynamic>();
            foreach (var m in lanewayRoute)
            {
                //if (emergencyStationModel.Where(r=>Convert.ToInt32(r.STATION_CODE)-1 == Convert.ToInt32(m.START_DEVICE)).Count() > 0)
                //{
                //    continue;
                //}
                if (manageStations.Where(r => r.END_CELL_ID - 1 == Convert.ToInt32(m.START_DEVICE)).Count() > 0)
                {
                    continue;
                }

                int cell_laneway = m.CELL_LANEWAY;
                int laneway_manage_count = 0;
                int laneway_storage_count = 0;
                int laneway_cell_count = 0;
                int laneway_agv_count = 0;

                var mc = lanewayManageCount.Where(r => r.CELL_LANEWAY == m.CELL_LANEWAY);
                if (mc.Count() > 0)
                {
                    laneway_manage_count = mc.First().LANEWAY_MANAGE_COUNT;
                }
                var sc = lanewayStorageCount.Where(r => r.CELL_LANEWAY == m.CELL_LANEWAY);
                if (sc.Count() > 0)
                {
                    laneway_storage_count = sc.First().LANEWAY_STORAGE_COUNT;
                }

                var cc = lanewayCellCount.Where(r => r.CELL_LANEWAY == m.CELL_LANEWAY);
                if (cc.Count() > 0)
                {
                    laneway_cell_count = cc.First().LANEWAY_CELL_COUNT;
                }

                var zc = lanewayAGVCount.Where(r => r.CELL_LANEWAY == m.CELL_LANEWAY);
                if (zc.Count() > 0)
                {
                    laneway_agv_count = zc.First().LANEWAY_AGV_COUNT;
                }

                lsLaneway.Add(new
                {
                    START_DEVICE = m.START_DEVICE,
                    CELL_LANEWAY = cell_laneway,
                    LANEWAY_MANAGE_COUNT = laneway_manage_count,
                    LANEWAY_STORAGE_COUNT = laneway_storage_count,
                    LANEWAY_CELL_COUNT = laneway_cell_count,
                    LANEWAY_AGV_COUNT = laneway_agv_count
                });
            }

            var query = from t in lsLaneway
                        where t.LANEWAY_CELL_COUNT > 0
                        orderby t.LANEWAY_AGV_COUNT, t.LANEWAY_MANAGE_COUNT, t.LANEWAY_STORAGE_COUNT, t.LANEWAY_CELL_COUNT descending
                        select t;

            if (query.Count() == 0)
            {
                return null;
            }

            //记录只产生库存，没有入库的数量
            await _database.From<T_STORAGE_MAIN>()
               .Where(m => m.STOCK_BARCODE == barcode)
               .Update(new
               {
                   APPLYIN_LANEWAY = query.First().CELL_LANEWAY,
                   UPDATE_TIME = DateTime.Now
               });

            return query.First().START_DEVICE;

        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetCacheByArea 方法
         */
        public async Task<Domain.Task.Models.Station> GetCacheByArea(int id)
        {
            var station = await _database.From<T_WH_CELL>()
                .Where(c => c.AREA_ID == id && c.CELL_TYPE == "Cache")
                .Single();
            return _mapper.Map<Domain.Task.Models.Station>(station);
        }

        /*
        * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetOutStationByCell 方法
        */
        public async Task<Domain.Task.Models.Station> GetOutStationByCell(int cellid,int areaid)
        {
            var cell = _database.From<T_WH_CELL>()
                .Where(r => r.CELL_ID == cellid)
                .Query(r => r.CELL_LANEWAY);

            var cellPallet = _database.From<T_WH_CELL>()
                .Where(r => r.CELL_ID == cellid)
                .Query(r => r.CELL_PALLET);

            var route = _database.From<IO_CONTROL_ROUTE>()
                 .Where(r => r.CONTROL_ROUTE_STATUS == 1 && r.CONTROL_ROUTE_TYPE == 2
                 && cell.Contains(r.START_DEVICE))
                 .Query(r=>r.END_DEVICE);

            var stationsQuery = _database.From<T_WH_CELL>()
                .Where(r => r.CELL_TYPE == "Station"
                && r.CELL_OUT == true
                && r.AREA_ID == areaid
                && cellPallet.Contains(r.CELL_PALLET)
                && route.Contains(r.CELL_CODE))
                .Query(r=>r.CELL_ID);

            var stationsManage = (await _database.From<T_MANAGE_MAIN>()
                .Where(r => stationsQuery.Contains(r.END_CELL_ID))
                .Select())
                .GroupBy(r => r.END_CELL_ID).Select(r => new
                {
                    END_CELL_ID = r.Key,
                    COUNT = r.Count()
                });

            int endCellId = 0;

            var stations = await _database.From<T_WH_CELL>()
                .Where(r => r.CELL_TYPE == "Station"
                && r.CELL_OUT == true
                && r.AREA_ID == areaid
                && cellPallet.Contains(r.CELL_PALLET)
                && route.Contains(r.CELL_CODE))
                .Select();

            if (stations.Count() == 0)
            {
                return null;
            }

            List<dynamic> lsStation = new List<dynamic>();
            foreach (var s in stations)
            {
                int manage_count = 0;

                var mc = stationsManage.Where(r => r.END_CELL_ID == s.CELL_ID);
                if (mc.Count() > 0)
                {
                    manage_count = mc.First().COUNT;
                }

                lsStation.Add(new
                {
                    END_CELL_ID = s.CELL_ID,
                    MANAGE_COUNT = manage_count
                });
            }
            var query = from t in lsStation
                        orderby t.MANAGE_COUNT
                        select t;

            var station = stations.First();
            if (query.Count() > 1)
            {
                station = stations.Where(r => r.CELL_ID == query.First().END_CELL_ID).Single();
            }

            return _mapper.Map<Domain.Task.Models.Station>(station);
        }

        /*
       * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetOutStationByCellIgnorePath 方法
       */
        public async Task<Domain.Task.Models.Station> GetOutStationByCellIgnorePath(int cellid, int areaid)
        {
            var cell = _database.From<T_WH_CELL>()
                .Where(r => r.CELL_ID == cellid)
                .Query(r => r.CELL_LANEWAY);

            //var cellTemp = await _database.From<T_WH_CELL>()
            //   .Where(r => r.CELL_ID == cellid)
            //   .Select(r => r.CELL_LANEWAY);

            var route = _database.From<IO_CONTROL_ROUTE>()
                 .Where(r => r.CONTROL_ROUTE_TYPE == 2
                 && cell.Contains(r.START_DEVICE))
                 .Query(r => r.END_DEVICE);

            //var routeTemp = await _database.From<IO_CONTROL_ROUTE>()
            //   .Where(r => r.CONTROL_ROUTE_STATUS == 1 && r.CONTROL_ROUTE_TYPE == 2
            //   && cell.Contains(r.START_DEVICE))
            //   .Select(r => r.END_DEVICE);

            var station = await _database.From<T_WH_CELL>()
                .Where(r => r.CELL_TYPE == "Station"
                && r.CELL_OUT == true
                && r.AREA_ID == areaid
                && route.Contains(r.CELL_CODE))
                .Single();

            return _mapper.Map<Domain.Task.Models.Station>(station);
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetBypassStationByCell 方法
         */
        public async Task<Domain.Task.Models.Station> GetBypassStationByCell(int cellid, int areaid,string palletType)
        {
            var route = _database.From<IO_CONTROL_ROUTE>()
                 //.Where(r => r.CONTROL_ROUTE_STATUS == 1 && r.CONTROL_ROUTE_TYPE == 4
                 .Where(r => r.CONTROL_ROUTE_TYPE == 4
                 && r.START_DEVICE == cellid.ToString())
                 .Query(r => r.END_DEVICE);

            //var routeTemp = await _database.From<IO_CONTROL_ROUTE>()
            //   .Where(r => r.CONTROL_ROUTE_STATUS == 1 && r.CONTROL_ROUTE_TYPE == 2
            //   && cell.Contains(r.START_DEVICE))
            //   .Select(r => r.END_DEVICE);

            var stationModel = _database.From<IO_STATION_MODEL>()
                .Where(r => r.INOUT == "2")
                .Query(r => r.STATION_CODE);

            var schema = _database.From<T_WH_CELL>()
                  .Where(r => r.CELL_TYPE == "Station"
                && r.CELL_OUT == true
                && r.AREA_ID == areaid
                && r.CELL_PALLET == palletType
                && route.Contains(r.CELL_CODE));

            if (areaid == 2 || areaid == 7)
            {
                schema = schema.Where(r => stationModel.Contains(r.CELL_CODE));
            }

            var station = await schema.Single();


            //var manageCount = (await _database.From<V_MANAGE_MAIN>()
            //  .Where(r => route.Contains(r.END_CELL_CODE))
            //  .Select())
            //  .GroupBy(r => r.END_CELL_CODE).Select(r => new {
            //      CELL_CODE = r.Key,
            //      MANAGE_COUNT = r.Count()
            //  });


            return _mapper.Map<Domain.Task.Models.Station>(station);
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetOutStationByArea 方法
         */
        public async Task<Domain.Task.Models.Station> GetOutStationByArea(int id)
        {
            var station = await _database.From<T_WH_CELL>()
                .Where(c => c.AREA_ID == id && c.CELL_TYPE == "Cache")
                .Single();
            return _mapper.Map<Domain.Task.Models.Station>(station);
        }

        /*
        * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetWarehouse 方法
        */
        public async Task<Domain.Task.Models.Warehouse> GetWarehouse(int id)
        {
            var warehouse = await _database.From<T_WH_WAREHOUSE>()
                .Where(r => r.WAREHOUSE_ID == id)
                .Single();

            return _mapper.Map<Domain.Task.Models.Warehouse>(warehouse);
        }
        /*
         * 实现 Kean.Domain.Stock.Repositories.IWarehouseRepository.GetWarehouseForStock 方法
         */
        public async Task<Domain.Stock.Models.Warehouse> GetWarehouseForStock(int id)
        {
            var cell = await _database.From<T_WH_CELL>()
                .Where(r => r.CELL_ID == id)
                .Single();
            var warehouse = await _database.From<T_WH_WAREHOUSE>()
                .Where(r => r.WAREHOUSE_ID == cell.WAREHOUSE_ID)
                .Single();

            return _mapper.Map<Domain.Stock.Models.Warehouse>(warehouse);
        }
        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.SetEmergency 方法
         */
        public async Task SetEmergency(string stationCode, string stationModel, string stationStatus)
        {

            string rallStation = stationCode;
            //@=@ 固定值 12006-12005 12012-12011 12018-12017 12024-12023
            //22006-22005 22012-22011 22018-22017 22024-22023
            switch (stationCode)
            {
                case "12006":
                    rallStation = "12005";
                    break;
                case "12012":
                    rallStation = "12011";
                    break;
                case "12018":
                    rallStation = "12017";
                    break;
                case "12024":
                    rallStation = "12023";
                    break;
                case "22006":
                    rallStation = "22005";
                    break;
                case "22012":
                    rallStation = "22011";
                    break;
                case "22018":
                    rallStation = "22017";
                    break;
                case "22024":
                    rallStation = "22023";
                    break;

            }

            var rall = await _database.From<T_WH_CELL>()
                    .Where(r => r.CELL_CODE == rallStation)
                    .Single();

            if (stationModel == "0")
            {
                await _database.From<IO_STATION_MODEL>()
    .Where(r => r.STATION_CODE == stationCode)
    .Update(new
    {
        STATION_MODEL = stationModel,
        STATION_STATUS = "1",
        INOUT = rall.CELL_LANEWAY == 2 ? "1" : "2"
    });
                ;

                await _database.From<T_WH_CELL>()
                    .Where(r => r.CELL_CODE == rallStation)
                    .Update(new
                    {
                        CELL_IN = "false",
                        CELL_OUT = "false"
                    });

                await _database.From<IO_CONTROL_ROUTE>()
                   .Where(r => r.CONTROL_ROUTE_ID == int.Parse(rallStation))
                   .Update(new
                   {
                       CONTROL_ROUTE_REMARK = "",
                       CONTROL_ROUTE_STATUS = 0
                   });
            }
            if (stationModel == "1")
            {
                await _database.From<IO_STATION_MODEL>()
.Where(r => r.STATION_CODE == stationCode)
.Update(new
{
STATION_MODEL = stationModel,
STATION_STATUS = "1",
INOUT = rall.CELL_LANEWAY
});

                var station= await _database.From<T_WH_CELL>()
                    .Where(r => r.CELL_CODE == rallStation)
                    .Single();
                if (station.CELL_LANEWAY == 2)
                {
                    await _database.From<T_WH_CELL>()
                        .Where(r => r.CELL_CODE == rallStation)
                        .Update(new
                        {
                            CELL_OUT = "true"
                        });

                }
                if (station.CELL_LANEWAY == 1)
                {
                    await _database.From<T_WH_CELL>()
                        .Where(r => r.CELL_CODE == rallStation)
                        .Update(new
                        {
                             CELL_IN = "true"
                        });
                }

                var route = await _database.From<IO_CONTROL_ROUTE>()
                    .Where(r => (r.CONTROL_ROUTE_TYPE == 1 || r.CONTROL_ROUTE_TYPE == 2) && (r.START_DEVICE == rallStation || r.END_DEVICE == rallStation))
                    .Single();

                await _database.From<IO_CONTROL_ROUTE>()
                    .Where(r => r.CONTROL_ROUTE_ID == int.Parse(rallStation))
                    .Update(new
                    {
                        CONTROL_ROUTE_REMARK = rallStation,
                        CONTROL_ROUTE_STATUS = route.CONTROL_ROUTE_STATUS
                    });
            }

        }

        public async Task<string> GetStationModel(string stationCode)
        {
            var stationModel = await _database.From<IO_STATION_MODEL>().Where(r => r.STATION_CODE == stationCode).Select();
            if (stationModel == null || stationModel.Count() == 0)
            {
                return null;
            }

            return stationModel.FirstOrDefault().INOUT;
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetCacheByCode 方法
         */
        public async Task<Domain.Task.Models.Station> GetCacheByCode(string areaCode)
        {
            var area = _database.From<T_WH_AREA>()
                .Where(r => r.AREA_CODE == areaCode)
                .Query(r=>r.AREA_ID);

            var station = await _database.From<T_WH_CELL>()
                .Where(r => area.Contains(r.AREA_ID) && r.CELL_TYPE == "Cache")
                .Single();
            return _mapper.Map<Domain.Task.Models.Station>(station);
        }

        /*
         * 实现 Kean.Domain.Task.Repositories.IWarehouseRepository.GetPassMoveEndDevice 方法
         */
        public async Task<string> GetPassMoveEndDevice(string device)
        {
            var endDevice = await _database.From<IO_CONTROL_ROUTE>()
                .Where(r => r.CONTROL_ROUTE_TYPE == 4 
                && r.CONTROL_ROUTE_REMARK == "Pass"
                && r.START_DEVICE == device)
                .Single();
            return endDevice?.END_DEVICE;
        }
    }
}