using Furion.DatabaseAccessor;
using Furion.FriendlyException;
using iWare.Wms.Core.Enum;
using iWare.Wms.Core;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Xml.Linq;
using Admin.NET.Application;
using Mapster;
using Microsoft.EntityFrameworkCore;
using Furion.DynamicApiController;
using Furion.DependencyInjection;
using System.Data;
using System.Dynamic;
using Admin.NET.Core.Helper;

namespace iWare.Wms.Application
{
    /// <summary>
    /// 大屏统计服务
    /// </summary>
    [ApiDescriptionSettings("统计查询", Name = "ScreenDisplay", Order = 100)]
    [Route("api/[Controller]")]
    [DisableOpLog]
    public class ScreenDisplayService:IDynamicApiController, ITransient
    {
        private readonly IRepository<WmsPlace, MasterDbContextLocator> _wareLocation;
        private readonly IRepository<v_empty_location, MasterDbContextLocator> _v_EmptyLocation;
        private readonly IRepository<WareDeviceWarning, MasterDbContextLocator> _wareDeviceWarning;
        private readonly IRepository<WareTask, MasterDbContextLocator> _wareTask;
        //private readonly IRepository<WareEquipment, MasterDbContextLocator> _wareEquipment;
        private readonly IRepository<WareSite, MasterDbContextLocator> _wareSite;

        public ScreenDisplayService(
       IRepository<WmsPlace, MasterDbContextLocator> wareLocation,
       IRepository<v_empty_location, MasterDbContextLocator> v_EmptyLocation,
       IRepository<WareDeviceWarning, MasterDbContextLocator> wareDeviceWarning,
       IRepository<WareTask, MasterDbContextLocator> wareTask,
       //IRepository<WareEquipment, MasterDbContextLocator> wareEquipment,
       IRepository<WareSite, MasterDbContextLocator> wareSite)
        {
            _wareLocation = wareLocation;
            _v_EmptyLocation = v_EmptyLocation;
            _wareDeviceWarning = wareDeviceWarning;
            _wareTask = wareTask;
            //_wareEquipment = wareEquipment;
            _wareSite = wareSite;
        }

        /// <summary>
        /// 数量统计
        /// </summary>
        /// <returns></returns>
        [HttpGet("/ScreenDisplay/pagedetails")]
        [AllowAnonymous]
        [DisableOpLog]
        public async Task<Dictionary<string, List<dynamic>>> PageDetails()
        {
            string sql = this.GetStatisticsSql();
            //string sql2 = GetLocationCountSql();
            //string sql3 = GetSrmCoordinateSql();
            Dictionary<string, List<dynamic>> keyValues = new();

            List<dynamic> dynamics = (await _wareLocation.SqlQueryAsync(sql)).ToDynamicList();
            keyValues.Add("StatisticalAnalysis", dynamics);

            //List<dynamic> dynamics2 = (await _warehousOrderDetailRep.SqlQueryAsync(sql2)).ToDynamicList();
            //keyValues.Add("LocationCount", dynamics2);

            //List<dynamic> dynamics3 = (await _warehousOrderDetailRep.SqlQueryAsync(sql3)).ToDynamicList();
            //keyValues.Add("SrmCoordinate", dynamics3);

            return keyValues;
        }

        /// <summary>
        /// 设备报警
        /// </summary>
        /// <returns></returns>
        [HttpGet("/ScreenDisplay/pagewarning")]
        [AllowAnonymous]
        [DisableOpLog]
        public async Task<PageResult<DeviceWarningOutput>> PageWarning([FromQuery] QueryTaskInput input)
        {
            //string deviceName = "";
            //if (input.SiteCode == "101" || input.SiteCode == "102")
            //{
            //    deviceName = "一号堆垛机";
            //}
            //else if (input.SiteCode == "103" || input.SiteCode == "104")
            //{
            //    deviceName = "二号堆垛机";
            //}
            var deviceWarningOutputs = await _wareDeviceWarning.DetachedEntities
                .Where(u => u.Status == 0)
                  .OrderByDescending(u => u.Id)
                .Select(u => new { u.WarningTime, u.WarningContent, u.DeviceName })
                .ProjectToType<DeviceWarningOutput>().ToListAsync();
            //.ToADPagedListAsync(input.PageNo, input.PageSize);

            //if (!string.IsNullOrWhiteSpace(input.SiteCode))
            //{
            //    var vs = await _wareSite.DetachedEntities.Where(u => u.Code == input.SiteCode).FirstOrDefaultAsync();
            //    if (!string.IsNullOrWhiteSpace(vs.OperationRemarks))
            //    {
            //        deviceWarningOutputs.Add(new DeviceWarningOutput() { DeviceName = input.SiteCode, WarningContent = vs.OperationRemarks, WarningTime = DateTime.Now });
            //    }
            //}
            return deviceWarningOutputs.ToADPagedList(input.PageNo, input.PageSize);
        }

        /// <summary>
        /// 出库任务
        /// </summary>
        /// <returns></returns>
        [HttpGet("/ScreenDisplay/pageouttask")]
        [AllowAnonymous]
        [DisableOpLog]
        public async Task<PageResult<OutTaskOutput>> PageOutTask([FromQuery] PageInputCustomBase input)
        {
            var temps = await _wareTask.DetachedEntities.Where(u => u.TaskCategory == TaskCategoryEnum.出库任务)
                  .OrderByDescending(u => u.Id).ToListAsync();
 
            List<OutTaskOutput> outTaskOutputs = new List<OutTaskOutput>();
            OutTaskOutput outTaskOutput = null;
            foreach (var item in temps)
            {
                outTaskOutput = new OutTaskOutput();
                outTaskOutput.TaskState = Enum.Parse(typeof(TaskStateEnum), item.TaskState.ToString()).ToString();
                outTaskOutput.DeviceName = item.FromLocationCode;
                outTaskOutput.OrderNo = item.ToLocationCode;
                //outTaskOutput.TaskNo = item.TaskNo;
                outTaskOutput.PartNo = item.PartCode;
                outTaskOutputs.Add(outTaskOutput);
            }
            return outTaskOutputs.ToADPagedList(input.PageNo, input.PageSize);
        }

        /// <summary>
        /// 入库任务
        /// </summary>
        /// <returns></returns>
        [HttpGet("/ScreenDisplay/pageintask")]
        [AllowAnonymous]
        [DisableOpLog]
        public async Task<PageResult<OutTaskOutput>> PageInTask([FromQuery] PageInputCustomBase input)
        {
            var temps = await _wareTask.DetachedEntities.Where(u => u.TaskCategory == TaskCategoryEnum.入库任务)
                   .OrderByDescending(u => u.Id).ToListAsync();
            //.ToADPagedListAsync(input.PageNo, input.PageSize);
            List<OutTaskOutput> outTaskOutputs = new List<OutTaskOutput>();
            OutTaskOutput outTaskOutput = null;
            foreach (var item in temps)
            {
                outTaskOutput = new OutTaskOutput();
                outTaskOutput.TaskState = Enum.Parse(typeof(TaskStateEnum), item.TaskState.ToString()).ToString();
                outTaskOutput.DeviceName = item.FromLocationCode;
                outTaskOutput.OrderNo = item.ToLocationCode;
                //outTaskOutput.TaskNo = item.TaskNo;
                outTaskOutput.PartNo = item.PartCode;
                outTaskOutputs.Add(outTaskOutput);
            }
            return outTaskOutputs.ToADPagedList(input.PageNo, input.PageSize);
        }

        /// <summary>
        /// 数量统计
        /// </summary>
        /// <returns></returns>
        [HttpGet("/ScreenDisplay/page2details")]
        [AllowAnonymous]
        [DisableOpLog]
        public async Task<Dictionary<string, List<dynamic>>> Page2Details()
        {
            string sql = this.GetStatisticsSql2();
            string sql2 = GetStaffSql();
            string sql3 = GetWorkLoadSql();
            string sql4 = GetDeliveryTimeSql();
            Dictionary<string, List<dynamic>> keyValues = new();

            //List<dynamic> dynamics = (await _warehousOrderDetailRep.SqlQueryAsync(sql)).ToDynamicList();
            //keyValues.Add("StatisticalAnalysis", dynamics);

            //List<dynamic> dynamics2 = (await _warehousOrderDetailRep.SqlQueryAsync(sql2)).ToDynamicList();
            //keyValues.Add("Staffload", dynamics2);

            //List<dynamic> dynamics3 = (await _warehousOrderDetailRep.SqlQueryAsync(sql3)).ToDynamicList();
            //keyValues.Add("WorkLoad", dynamics3);

            //List<dynamic> dynamics4 = (await _warehousOrderDetailRep.SqlQueryAsync(sql4)).ToDynamicList();
            //keyValues.Add("DeliveryTime", dynamics4);

            return keyValues;
        }

        /// <summary>
        /// 大屏画面1统计分析 
        /// </summary>
        /// <returns></returns>
        private string GetStatisticsSql()
        {
            string sql = @$"  
             SELECT ISNULL(COUNT(1),0) value,'今日出入库数' [key] FROM dbo.ware_task_bak with(nolock) WHERE DATEDIFF(day,CreatedTime,GETDATE()) = 0
               UNION ALL 
			   SELECT ISNULL(COUNT(1),0) value,'本月出入库数' [key] FROM dbo.ware_task_bak with(nolock) WHERE DATEDIFF(MONTH,CreatedTime,GETDATE()) = 0
			      UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'本年出入库数' [key] FROM dbo.ware_task_bak with(nolock) WHERE DATEDIFF(YEAR,CreatedTime,GETDATE()) = 0
			      UNION ALL
               SELECT ISNULL(COUNT(1),0) value,'总库位数' [key] FROM dbo.wms_place with(nolock) WHERE IsDeleted = 0
			     UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'满库位数' [key] FROM dbo.ware_location_vs_container with(nolock)
			      UNION ALL
               SELECT ISNULL(CoordinateX,0) value,'一号堆垛机位置' [key] FROM dbo.ware_equipment with(nolock) WHERE Id = 1";

            return sql;
        }

        private string GetLocationCountSql()
        {
            string sql = @"SELECT ISNULL(COUNT(1),0) value,'总库位数' [key] FROM dbo.ware_location WHERE IsDeleted = 0
			     UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'空库位数' [key] FROM dbo.v_empty_location";

            return sql;
        }

        private string GetSrmCoordinateSql()
        {
            string sql = @"SELECT ISNULL(CoordinateX,0) value,'一号堆垛机位置' [key] FROM dbo.ware_equipment WHERE DeviceId = 1
			     UNION ALL
			   SELECT ISNULL(CoordinateX,0) value,'二号堆垛机位置' [key] FROM dbo.ware_equipment WHERE DeviceId = 2";

            return sql;
        }

        private string GetStatisticsSql2()
        {
            string sql = @"SELECT ISNULL(COUNT(1),0) value,'立库65cm库位总库位数' [key] FROM dbo.ware_location with(nolock) WHERE IsDeleted = 0 AND [High] = 650
			     UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'立库65cm库位空库位数' [key] FROM dbo.v_empty_location with(nolock) WHERE [High] = 650
			   UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'立库80cm库位总库位数' [key] FROM dbo.ware_location with(nolock) WHERE IsDeleted = 0 AND [High] = 800
			      UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'立库80cm库位空库位数' [key] FROM dbo.v_empty_location with(nolock) WHERE [High] = 800
			   UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'立库105cm库位总库位数' [key] FROM dbo.ware_location with(nolock) WHERE IsDeleted = 0 AND [High] = 1050
			      UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'立库105cm库位空库位数' [key] FROM dbo.v_empty_location with(nolock) WHERE [High] = 1050
                 UNION ALL
			   SELECT ISNULL(COUNT(1),0) value,'立库105cm库位空库位数' [key] FROM dbo.v_empty_location with(nolock) WHERE [High] = 1050
			     UNION ALL
			   select ISNULL(COUNT(1),0) as value,RIGHT(convert(varchar(7),CreatedTime,120),2) + '月入' as [key] from ware_orders_details with(nolock) where DATEDIFF(YEAR,CreatedTime,GETDATE()) = 0  group by convert(varchar(7),CreatedTime,120) 
			     UNION ALL
			   select ISNULL(COUNT(1),0) as value,RIGHT(convert(varchar(7),CreatedTime,120),2) + '月出' as [key] from ware_orders_details with(nolock) where DATEDIFF(YEAR,CreatedTime,GETDATE()) = 0  group by convert(varchar(7),CreatedTime,120)
			   	 UNION ALL
			   select ISNULL(COUNT(1),0) as value,'年度出入库量合计' as [key] from ware_orders_details with(nolock) where DATEDIFF(YEAR,CreatedTime,GETDATE()) = 0 
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'年度出库量合计' as [key] from ware_orders_details with(nolock) where DATEDIFF(YEAR,CreatedTime,GETDATE()) = 0 AND OutInFlag = 1
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'年度入库量合计' as [key] from ware_orders_details with(nolock) where DATEDIFF(YEAR,CreatedTime,GETDATE()) = 0 AND OutInFlag = 0
			    UNION ALL
			   select ISNULL(COUNT(1),0) as value,'立库未完成项合计' as [key] from ware_orders_details with(nolock) where StockFlag = 0 AND OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'平库未完成项合计' as [key] from ware_orders_details with(nolock) where StockFlag = 1 AND OrderStatus <> 2
			    UNION ALL
			   select ISNULL(COUNT(1),0) as value,'立库订单件出库' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 0 AND b.OrderType = 2 and a.OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'平库订单件出库' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 1 AND b.OrderType = 2 and a.OrderStatus <> 2
			    UNION ALL
			   select ISNULL(COUNT(1),0) as value,'立库订单件入库' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 0 AND b.OrderType = 1 and a.OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'平库订单件入库' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 1 AND b.OrderType = 1 and a.OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'立库领用单' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 0 AND b.OrderType = 10 and a.OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'平库领用单' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 1 AND b.OrderType = 10 and a.OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'立库调拨单' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 0 AND b.OrderType = 32 and a.OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'平库调拨单' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 1 AND b.OrderType = 32 and a.OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'立库产成品入库' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 0 AND b.OrderType = 18 and a.OrderStatus <> 2
			   UNION ALL
			   select ISNULL(COUNT(1),0) as value,'平库产成品入库' as [key] from ware_orders_details a with(nolock) left join ware_orders b with(nolock) ON a.OrdersId = b.Id where a.StockFlag = 1 AND b.OrderType = 18 and a.OrderStatus <> 2";

            return sql;
        }

        private string GetStaffSql()
        {
            string sql = @"SELECT ISNULL(COUNT(1),0) value,CreatedUserName [key] FROM dbo.ware_orders_details with(nolock) group by CreatedUserName";

            return sql;
        }

        private string GetWorkLoadSql()
        {
            string sql = @"SELECT ISNULL(COUNT(1),0) value,UpdatedUserName [key] FROM dbo.ware_sorting_details with(nolock) group by UpdatedUserName";

            return sql;
        }

        private string GetDeliveryTimeSql()
        {
            string sql = @"  --select ISNULL(COUNT(1),0) as value,RIGHT(convert(varchar(13),CreatedTime,120),2) + '点立库入' as [key] from ware_orders_details with(nolock) where DATEDIFF(day,CreatedTime,GETDATE()) = 0 AND OutInFlag = 0 AND StockFlag = 0 group by convert(varchar(13),CreatedTime,120)
 --UNION ALL
  select ISNULL(COUNT(1),0) as value,RIGHT(convert(varchar(13),CreatedTime,120),2) + '点立库出' as [key] from ware_orders_details with(nolock) where DATEDIFF(day,CreatedTime,GETDATE()) = 0 AND OutInFlag = 1 AND StockFlag = 0  group by convert(varchar(13),CreatedTime,120)
 -- UNION ALL
   --select ISNULL(COUNT(1),0) as value,RIGHT(convert(varchar(13),CreatedTime,120),2) + '点平库入' as [key] from ware_orders_details with(nolock) where DATEDIFF(day,CreatedTime,GETDATE()) = 0 AND OutInFlag = 0 AND StockFlag = 1 group by convert(varchar(13),CreatedTime,120)
 UNION ALL
  select ISNULL(COUNT(1),0) as value,RIGHT(convert(varchar(13),CreatedTime,120),2) + '点平库出' as [key] from ware_orders_details with(nolock) where DATEDIFF(day,CreatedTime,GETDATE()) = 0 AND OutInFlag = 1 AND StockFlag = 1  group by convert(varchar(13),CreatedTime,120)";

            return sql;
        }
    }
}