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;
|
}
|
}
|
}
|
