SDLC_WMS/Server/Wms.Core.Application.Query/Implements/StatisticsService.cs

using AutoMapper;
using Kean.Application.Query.Interfaces;
using Kean.Application.Query.Sockets;
using Kean.Infrastructure.Database;
using Kean.Infrastructure.Database.Repository.Default;
using Kean.Infrastructure.Database.Repository.Default.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Kean.Application.Query.Implements
{
    /// <summary>
    /// TV显示信息查询服务
    /// </summary>
    public sealed class StatisticsService : IStatisticsService
    {
        private readonly IMapper _mapper; // 模型映射
        private readonly IDefaultDb _database; // 默认数据库
        //private readonly IOnlineSocket _onlineSocket; // 连接管道

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

        /*
         * 实现 Kean.Application.Query.Interfaces.IStatisticsService.GetTvStatistics 方法
         */
        public async Task<object> GetTvStatistics(
            int? area)
        {
            int tempArae = 0;
            if (area.HasValue)
            {
                tempArae = area.Value;
                if (area == 8 || area == 7)
                {
                    tempArae = 6;
                }
                if (area == 3 || area == 2)
                {
                    tempArae = 1;
                }
            }


            ////统计 库存-货期(过期 警告 正常)
            var stockAgeSchema = _database.From<V_SYS_ITEM_LIST, V_STORAGE_LIST>();
            if (area.HasValue)
            {
                if (area == 2 || area == 7)
                {

                    stockAgeSchema.Join(Join.Left, (m, n) => n.AGE_STATUS == m.ITEM_LIST_CODE && n.AREA_ID == tempArae && (n.CLASS_ID == 2 || n.CLASS_ID == 3));
                }
                else
                {
                    stockAgeSchema.Join(Join.Left, (m, n) => n.AGE_STATUS == m.ITEM_LIST_CODE && (n.AREA_ID == 1 || n.AREA_ID == 6) && (n.CLASS_ID == 2 || n.CLASS_ID == 3));
                }
                    
            }
            else
            {
                stockAgeSchema.Join(Join.Left, (m, n) => n.AGE_STATUS == m.ITEM_LIST_CODE && (n.AREA_ID == 1 || n.AREA_ID == 6) && (n.CLASS_ID == 2 || n.CLASS_ID == 3));
            }

            var stockAge = await stockAgeSchema
                .Where((m, n) => m.ITEM_CODE == "goodsAgeStatus")
                .GroupBy((m, n) => m.ITEM_LIST_NAME)
                .GroupBy((m, n) => m.ITEM_LIST_CODE)
                .OrderBy((m, n) => m.ITEM_LIST_CODE, Infrastructure.Database.Order.Descending)
                .Select((m, n) => new
                {
                    name = m.ITEM_LIST_NAME,
                    value = Function.Count(n.STORAGE_ID)
                });

            ////统计 库存-膜卷编码
            var stockTypeSchema = _database.From<V_STORAGE_LIST>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                if (area == 2 || area == 7)
                {
                    stockTypeSchema.Where(r => r.AREA_ID == tempArae);
                }

            }
            var stockType = await stockTypeSchema
                .Where(r=>(r.CLASS_ID == 2 || r.CLASS_ID == 3) && (r.AREA_ID == 1 || r.AREA_ID == 6))
                .GroupBy(r => r.GOODS_NAME)
                .OrderBy(r => r.GOODS_NAME, Infrastructure.Database.Order.Ascending)
                .Select(r=>new {
                    name = r.GOODS_NAME,
                    value = Function.Count(r.GOODS_NAME)
                });

            ////统计 货位使用率
            var stockCellRateMaxSchema = _database.From<T_WH_CELL>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                stockCellRateMaxSchema.Where(r => r.AREA_ID == tempArae);
            }
            var stockCellRateMax = await stockCellRateMaxSchema
                .Where(r => r.CELL_TYPE == "Cell" && r.CELL_FLAG == true && r.RUN_STATUS != "Disabled")
                .GroupBy(r => r.CELL_LANEWAY)
                .OrderBy(r => r.CELL_LANEWAY, Infrastructure.Database.Order.Ascending)
                .Select(r => new {
                    name = r.CELL_LANEWAY,
                    max = Function.Count(r.CELL_LANEWAY)
                }) ;

            //select CELL_LANEWAY, count(0)  from T_WH_CELL with (nolock) where CELL_TYPE = 'Cell' and CELL_STATUS <> 'Empty' group by CELL_LANEWAY order by CELL_LANEWAY
            var stockCellRateValueSchema = _database.From<T_WH_CELL>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                stockCellRateValueSchema.Where(r => r.AREA_ID == tempArae);
            }
            var stockCellRateValueTemp = await stockCellRateValueSchema
                .Where(r => r.CELL_TYPE == "Cell" && r.CELL_STATUS != "Empty" && r.CELL_FLAG == true && r.RUN_STATUS != "Disabled")
                .GroupBy(r => r.CELL_LANEWAY)
                .OrderBy(r => r.CELL_LANEWAY, Infrastructure.Database.Order.Ascending)
                .Select(r => new {
                    name = r.CELL_LANEWAY,
                    value = Function.Count(r.CELL_LANEWAY)
                });


            List<int[]> lsStockCellRateValue = new List<int[]>();
            List<int> stockCellRateValue = new List<int>();

            int cellSummaryMax = 0;
            int cellSummaryFull = 0;
            foreach (var m in stockCellRateMax)
            {
                cellSummaryMax += m.max;
                var temp = stockCellRateValueTemp.Where(r => r.name == m.name);
                if (temp.Count() > 0)
                {
                    cellSummaryFull += temp.First().value;
                    stockCellRateValue.Add(temp.First().value);
                }
                else
                {
                    stockCellRateValue.Add(0);
                }
            }
            lsStockCellRateValue.Add(stockCellRateValue.ToArray());

            //货位使用率文字信息
            var stockCellRateSummary = new
            {
                max = cellSummaryMax,
                full = cellSummaryFull
            };

            ////汇总信息-今日入库 今日出库 在途入库 在途出库 剩余货位 在库膜卷
            //今日入库
            var todayRecordInCountSchema = _database.From<V_RECORD>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                todayRecordInCountSchema.Where(r => r.START_AREA_ID == area);
            }
            var todayRecordInCount = (await todayRecordInCountSchema
                .Where(r => r.BEGIN_TIME >= System.DateTime.Now.Date && r.BEGIN_TIME <= DateTime.Now.Date.AddDays(1))
                .Where(r => r.RECORD_TYPE == "Infeed")
                .Single(r => new { Count = Function.Count(r.RECORD_ID) }))
                .Count();
            //今日出库
            var todayRecordOutCountSchema = _database.From<V_RECORD>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                todayRecordOutCountSchema.Where(r => r.END_AREA_ID == area);
            }
            var todayRecordOutCount = (await todayRecordOutCountSchema
    .Where(r => r.BEGIN_TIME >= System.DateTime.Now.Date && r.BEGIN_TIME <= DateTime.Now.Date.AddDays(1))
    .Where(r => (r.RECORD_TYPE == "ApplyOut" || r.RECORD_TYPE == "PalletOut"))
    .Single(r => new { Count = Function.Count(r.RECORD_ID) }))
    .Count();
            //在途入库
            var todayManageInCountSchema = _database.From<V_MANAGE_MAIN>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                todayManageInCountSchema.Where(r => r.START_AREA_ID == area);
            }
            var todayManageInCount = (await todayManageInCountSchema
                .Where(r => r.BEGIN_TIME >= DateTime.Now.Date && r.BEGIN_TIME <= DateTime.Now.Date.AddDays(1))
                .Where(r => r.END_AREA_ID == 1 || r.END_AREA_ID == 6)
                .Single(r => new { Count = Function.Count(r.MANAGE_ID) }))
                .Count();

            var todayStorageCacheCountSchema = _database.From<V_STORAGE>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                todayStorageCacheCountSchema.Where(r=>r.APPLYIN_AREA == area);
            }
            var todayStorageCacheCount = (await todayStorageCacheCountSchema
                .Where(r => r.CREATE_TIME >= DateTime.Now.Date && r.CREATE_TIME <= DateTime.Now.Date.AddDays(1))
                .Single(r => new { Count = Function.Count(r.STORAGE_ID) }))
                .Count();
            todayManageInCount = (todayManageInCount + todayStorageCacheCount);
            //在途出库
            var todayManageOutCountSchema = _database.From<V_MANAGE_MAIN>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                todayManageOutCountSchema.Where(r => r.END_AREA_ID == area);
            }
            var todayManageOutCount = (await todayManageInCountSchema
                .Where(r => r.START_AREA_ID == 1 || r.START_AREA_ID == 6)
                .Single(r => new { Count = Function.Count(r.MANAGE_ID) }))
                .Count();

            //剩余货位
            var emptyCellCount = cellSummaryMax - cellSummaryFull;
            //在库膜卷
            var productionCount = 0;
            foreach (var r in stockType)
            {
                productionCount += r.value;
            }

            ////历史任务-图表
            //manage_his_xAxis_data
            var manage_his_xAxis_data = new string[] {System.DateTime.Now.Date.AddDays(-6).ToString("dd")
            ,System.DateTime.Now.Date.AddDays(-5).ToString("dd")
            ,System.DateTime.Now.Date.AddDays(-4).ToString("dd")
            ,System.DateTime.Now.Date.AddDays(-3).ToString("dd")
            ,System.DateTime.Now.Date.AddDays(-2).ToString("dd")
            ,System.DateTime.Now.Date.AddDays(-1).ToString("dd")
            ,System.DateTime.Now.Date.ToString("dd")};

            //manage_his_in_data
            var manageHisInDataSchema = _database.From<V_RECORD_LIST>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                manageHisInDataSchema.Where(r => r.START_AREA_ID == area);
            }
            var manageHisInData = await manageHisInDataSchema
                .Where(r => r.BEGIN_TIME >= System.DateTime.Now.Date.AddDays(-6) && r.BEGIN_TIME <= DateTime.Now.Date.AddDays(1))
                .Where(r => r.RECORD_TYPE == "Infeed")
                .GroupBy(r => r.BEGIN_DATE)
                .OrderBy(r => r.BEGIN_DATE, Infrastructure.Database.Order.Ascending)
                .Select(r => new
                {
                    name = r.BEGIN_DATE,
                    value = Function.Sum(r.QTY)
                });
            //manage_his_out_data
            var manageHisOutDataSchema = _database.From<V_RECORD_LIST>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                manageHisOutDataSchema.Where(r => r.END_AREA_ID == area);
            }
            var manageHisOutData = await manageHisInDataSchema
                .Where(r => r.BEGIN_TIME >= System.DateTime.Now.Date.AddDays(-6) && r.BEGIN_TIME <= DateTime.Now.Date.AddDays(1))
                .Where(r => r.RECORD_TYPE == "ApplyOut" || r.RECORD_TYPE == "PalletOut")
                .GroupBy(r => r.BEGIN_DATE)
                 .OrderBy(r => r.BEGIN_DATE, Infrastructure.Database.Order.Ascending)
                .Select(r => new
                {
                    name = r.BEGIN_DATE,
                    value = Function.Sum(r.QTY)
                });

            decimal[] manage_his_in_data = new decimal[manage_his_xAxis_data.Length];
            decimal[] manage_his_out_data = new decimal[manage_his_xAxis_data.Length];

            for (int i=0;i< manage_his_xAxis_data.Length;i++)
            {
                var tempIn = manageHisInData.Where(m => m.name.Substring(8) == manage_his_xAxis_data[i]);
                if (tempIn.Count() > 0)
                {
                    try
                    {
                        manage_his_in_data[i] = tempIn.First().value / 2;
                    }
                    catch 
                    {
                        manage_his_in_data[i] = 0;
                    }

                }

                var tempOut = manageHisOutData.Where(m => m.name.Substring(8) == manage_his_xAxis_data[i]);
                if (tempOut.Count() > 0)
                {
                    try
                    {
                        manage_his_out_data[i] = tempOut.First().value / 2;
                    }
                    catch 
                    {
                        manage_his_out_data[i] = 0;
                    }

                }

            }

            //库存统计
            var stockListSchema = _database.From<V_STORAGE_LIST>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                if (area == 2 || area == 7)
                {
                    stockListSchema.Where(r => r.AREA_ID == tempArae);
                }
            }
            var stockList = await stockListSchema
                .Where(r => (r.CLASS_ID == 2 || r.CLASS_ID == 3) && (r.AREA_ID == 1 || r.AREA_ID == 6))
                .GroupBy(r => r.GOODS_NAME)
                .GroupBy(r => r.GOODS_CODE)
                .OrderBy(r => r.GOODS_NAME, Infrastructure.Database.Order.Ascending)
                .Select(r => new {
                    name = r.GOODS_NAME,
                    code = r.GOODS_CODE,
                    value = Function.Count(r.GOODS_NAME)
                });

            //当前任务
            var magageListSchema = _database.From<V_MANAGE_MAIN>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                magageListSchema.Where(r => (r.START_AREA_ID == area || r.END_AREA_ID == area));
            }
            var magageList = await magageListSchema
                //.Where(r => r.CLASS_ID == 2 || r.CLASS_ID == 3)
                .OrderBy(r => r.BEGIN_TIME, Infrastructure.Database.Order.Ascending)
                .Select(r => new {
                    STOCK_BARCODE = r.STOCK_BARCODE,
                    END_CELL_NAME = r.END_CELL_CODE,
                    GOODS_NAME = r.GOODS_NAME,
                    REQUEST_NO = (string.IsNullOrEmpty(r.REQUEST_NO)? "": r.REQUEST_NO)
                });

            //报警统计
            var magageAlarmSchema = _database.From<V_MANAGE_MAIN>().Lock(Lock.Nolock);
            if (area.HasValue)
            {
                magageListSchema.Where(r => (r.START_AREA_ID == area || r.END_AREA_ID == area));
            }
            var magageAlarm = (await magageAlarmSchema
                .Where(r => (r.MANAGE_STATUS == "Blocked" || r.MANAGE_STATUS == "WaiteWmsResult"
                || r.MANAGE_STATUS == "UploadError" || r.MANAGE_STATUS == "WmsResultFail"
                || r.MANAGE_STATUS == "ScanError"
                || r.MANAGE_STATUS == "AgvFail"))
                .OrderBy(r => r.BEGIN_TIME, Infrastructure.Database.Order.Ascending)
                .Select())
                .Select(r => new
                {
                    alarmInfo = $"{r.STOCK_BARCODE}-{r.START_CELL_NAME}-{ r.MANAGE_REMARK}"
                });

            //await _onlineSocket.Statistics(new
            //{
            //    stock_age = stockAge,
            //    stock_type = stockType,
            //    stock_cellRate_max = stockCellRateMax,
            //    stock_cellRate_value = lsStockCellRateValue,
            //    stock_cellRate_summary = stockCellRateSummary,
            //    summary_data = new int[] { todayRecordInCount, todayRecordOutCount, todayManageInCount, todayManageOutCount, emptyCellCount, productionCount },
            //    manage_his_xAxis_data,
            //    manage_his_in_data,
            //    manage_his_out_data,
            //    storage_list = stockList,
            //    manage_list = magageList,
            //    alarm = new
            //    {
            //        storage_type = "报警",
            //        count = magageAlarm.Count()
            //    },
            //    alarm_list = magageAlarm

            //});

            return new
            {
                stock_age = stockAge,
                stock_type = stockType,
                stock_cellRate_max = stockCellRateMax,
                stock_cellRate_value = lsStockCellRateValue,
                stock_cellRate_summary = stockCellRateSummary,
                summary_data = new int[] { todayRecordInCount, todayRecordOutCount, todayManageInCount, todayManageOutCount, emptyCellCount, productionCount },
                manage_his_xAxis_data,
                manage_his_in_data,
                manage_his_out_data,
                storage_list = stockList,
                manage_list = magageList,
                alarm = new
                {
                    storage_type = "报警",
                    count = magageAlarm.Count()
                },
                alarm_list = magageAlarm

            };
        }


        /*
         * 实现 Kean.Application.Query.Interfaces.IStatisticsService.GetTVLedInfo 方法
         */
        public async Task<object> GetTVLedInfo(string ip)
        {
            //当前任务
            var ledMainSchema = _database.From<T_LED_MAIN>().Lock(Lock.Nolock)
                .Where(r=>r.IS_SEND == "0" && r.LED_STATUS == "1");
            if (!string.IsNullOrEmpty(ip))
            {
                ledMainSchema.Where(r => r.LED_IP == ip);
            }
            var ledMains = await ledMainSchema.Select();

            List<T_LED_LIST> ledListInfo = new List<T_LED_LIST>();

            foreach (var m in ledMains)
            {
                var ledList = await _database.From<T_LED_LIST>().Lock(Lock.Nolock)
                    .Where(r => r.LED_ID == m.LED_ID && r.LINE_NO == "1")
                    .Select();
                foreach (var n in ledList)
                {
                    ledListInfo.Add((T_LED_LIST)n);
                }

            }

            return new
            {
                manage_list = ledListInfo
            };
        }

        /*
        * 实现  Kean.Application.Query.Interfaces.IStatisticsService.StockStatistics 方法
        */
        public async Task<object> StockStatistics(bool bQC)
        {
            List<string> xDates = new List<string>();
            xDates.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            //库存
            var stockSchema = _database.From<V_STORAGE_LIST>().Lock(Lock.Nolock)
                .Where(r => r.AREA_ID != 300000)
                .Where(r => r.GOODS_ID != 5 && r.GOODS_ID != 9)
                .GroupBy(r => r.GOODS_NAME)
                .OrderBy(r => r.GOODS_NAME, Infrastructure.Database.Order.Ascending);
                

            if (bQC)
            {
                stockSchema = stockSchema.GroupBy(r => r.GOODS_STATUS);
                stockSchema = stockSchema.OrderBy(r => r.GOODS_STATUS, Infrastructure.Database.Order.Ascending);

            }

            object stockData = null;
            if (bQC)
            {
                stockData = (await stockSchema.Select(r => new
                {
                    r.GOODS_NAME,
                    r.GOODS_STATUS,
                    value = Function.Sum(r.QTY)
                })).Select(r => new
                {
                    name = ((string)r.GOODS_NAME).Contains("托盘") ? $"{r.GOODS_NAME}数量" : $"{r.GOODS_STATUS}{r.GOODS_NAME}EA",
                    value = r.value
                });
            }
            else
            {
                stockData = (await stockSchema.Select(r => new
                {
                    r.GOODS_NAME,
                    value = Function.Sum(r.QTY)
                })).Select(r => new
                {
                    name = ((string)r.GOODS_NAME).Contains("托盘") ? $"{r.GOODS_NAME}数量" : $"{r.GOODS_NAME}EA",
                    value = r.value
                });
            }

            List<StockStatisticsData> dataList = new List<StockStatisticsData>();
            foreach (var n in (IEnumerable<dynamic>)stockData)
            {
                StockStatisticsData data = new StockStatisticsData();
                data.Name = n.name;
                data.Data = new string[1];

                data.Data[0] = Convert.ToString(n.value);

                dataList.Add(data);
            }

            return new
            {
                xDates = xDates.ToArray(),
                dataList
            };
        }

        /*
  * 实现  Kean.Application.Query.Interfaces.IStatisticsService.StockPalletStatistics 方法
  */
        public async Task<object> StockPalletStatistics(bool bQC)
        {
            List<string> xDates = new List<string>();
            xDates.Add(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

            //库存
            var stockSchema = _database.From<V_STORAGE_LIST>().Lock(Lock.Nolock)
                .Where(r => r.AREA_ID != 300000)
                .Where(r => r.GOODS_ID != 5 && r.GOODS_ID != 9)
                .GroupBy(r => r.STOCK_BARCODE)
                .GroupBy(r => r.GOODS_NAME)
                .OrderBy(r => r.GOODS_NAME, Infrastructure.Database.Order.Ascending);

            if (bQC)
            {
                stockSchema = stockSchema.GroupBy(r => r.GOODS_STATUS);
                stockSchema = stockSchema.OrderBy(r => r.GOODS_STATUS, Infrastructure.Database.Order.Ascending);
            }

            object stockData = null;
            if (bQC)
            {
                var preStockData = (await stockSchema.Select(r => new
                {
                   r.GOODS_STATUS,
                   r.GOODS_NAME
                }));

                List<PreData> preDataList = new List<PreData>();
                foreach (var n in preStockData)
                {
                    if (preDataList.Where(r => r.name == $"{n.GOODS_STATUS}{n.GOODS_NAME}托盘数").Count() == 0)
                    {
                        PreData preData = new PreData();
                        preData.name = $"{n.GOODS_STATUS}{n.GOODS_NAME}托盘数";
                        preData.value = preStockData.Where(r => r.GOODS_NAME == n.GOODS_NAME && r.GOODS_STATUS == n.GOODS_STATUS).Count();

                        preDataList.Add(preData);
                    }
                }

                stockData = preDataList.ToArray();
            }
            else
            {
                var preStockData = (await stockSchema.Select(r => new
                {
                    r.GOODS_NAME
                }));

                List<PreData> preDataList = new List<PreData>();
                foreach (var n in preStockData)
                {
                    if (preDataList.Where(r => r.name == $"{n.GOODS_NAME}托盘数").Count() == 0)
                    {
                        PreData preData = new PreData();
                        preData.name = $"{n.GOODS_NAME}托盘数";
                        preData.value = preStockData.Where(r => r.GOODS_NAME == n.GOODS_NAME).Count();

                        preDataList.Add(preData);
                    }
                }

                stockData = preDataList.ToArray();
            }

            List<StockStatisticsData> dataList = new List<StockStatisticsData>();
            foreach (var n in (IEnumerable<dynamic>)stockData)
            {
                StockStatisticsData data = new StockStatisticsData();
                data.Name = n.name;
                data.Data = new string[1];

                data.Data[0] = Convert.ToString(n.value);

                dataList.Add(data);
            }

            return new
            {
                xDates = xDates.ToArray(),
                dataList
            };
        }


        private class StockStatisticsData
        {
            public string Name
            {
                get; set;
            }
            public string[] Data
            {
                get; set;
            }
        }

        private class PreData
        {
            public string name
            {
                get; set;
            }
            public int value
            {
                get; set;
            }
        }

    }
}