using PadApplication.DB;
|
using PadApplication.Entites.DbModels;
|
using PadApplication.Entites.Dto;
|
using SqlSugar;
|
|
namespace PadApplication.Services;
|
|
/// <summary>
|
/// Womdaa数据管理类,负责工单相关数据的查询和处理
|
/// </summary>
|
public class WomdaaManager : Repository<Womdaa>
|
{
|
/// <summary>
|
/// 根据工程编号和工单状态获取工单列表
|
/// </summary>
|
/// <param name="query">包含机器编号和工单状态的查询条件</param>
|
/// <returns>工单列表</returns>
|
public List<VOrderBycl> GetWomdaasByEngineeringNo(OrderMachineDto query)
|
{
|
var list = VOrders(query);
|
|
// 按工单状态筛选
|
if (query.OrderStatus != null && query.OrderStatus.Any())
|
{
|
list = list.Where(p => p.Daa018 != null && query.OrderStatus.Contains(p.Daa018)).ToList();
|
}
|
|
// 按工单ID分组，只保留每组中最早报工的一条
|
list = list
|
.GroupBy(p => p.ID)
|
.Select(g => g.OrderBy(x => x.BgDate).First())
|
.OrderBy(x => x.BgDate)
|
.ToList();
|
|
return list;
|
}
|
|
/// <summary>
|
/// 获取工单基础数据
|
/// </summary>
|
/// <param name="query">包含机器编号的查询条件</param>
|
/// <returns>合并后的工单列表</returns>
|
private List<VOrderBycl> VOrders(OrderMachineDto query)
|
{
|
var list = Db.Queryable<VOrderBycl>()
|
.Where(s => s.Daa018 != "完工" &&
|
s.MachineNo == query.machineNo)
|
.ToList();
|
|
var orders = Db.Queryable<VOrderABycl>()
|
.Where(s => s.MachineNo == query.machineNo)
|
.Select<VOrderBycl>()
|
.ToList();
|
|
list.AddRange(orders);
|
return list;
|
}
|
|
/// <summary>
|
/// 根据机器编号和工程编号获取工单列表
|
/// </summary>
|
/// <param name="query">
|
/// 包含机器编号（machineNo）和工程编号（engineeringNo）的查询条件
|
/// </param>
|
/// <returns>
|
/// 返回符合条件的工单（VOrdreBycl）列表
|
/// </returns>
|
public List<VOrderBycl> GetWomdaasByMachine(OrderMachineDto query)
|
{
|
// 获取当前日期字符串，格式为 yyyy-MM-dd
|
var date = DateTime.Now.ToString("yyyy-MM-dd");
|
|
// 获取指定机器下未完工且实际数量不等于计划数量的工单（合并VOrder和VOrderA）
|
var list = VOrders(query);
|
// 根据工程编号（engineeringNo）筛选出对应的工单（moldId == engineeringNo）
|
var vOrders = list.Where(s => s.cutterId == query.engineeringNo).ToList();
|
// 获取筛选后工单的ID集合
|
var ids = vOrders.Select(s => s.ID).ToArray();
|
|
// 查询V_ORDER视图，筛选条件：
|
// 1. 机台编号等于传入的machineNo
|
// 2. 编辑日期为今天或为空
|
// 3. 工单ID在ids集合中
|
return Db.Queryable<VOrderBycl>()
|
.Where(s =>
|
s.MachineNo == query.machineNo
|
&& (s.EditDate == date || s.EditDate == null)
|
&& ids.Contains(s.ID))
|
.ToList();
|
}
|
|
/// <summary>
|
/// 获取需要显示的工单列表
|
/// </summary>
|
/// <param name="query">包含机器编号的查询条件</param>
|
/// <returns>需要显示的工单列表</returns>
|
public List<VOrderBycl> GetWomdaasByShow(OrderMachineDto query)
|
{
|
// 查询需要显示的工单ID
|
var ids = Db.Queryable<MesOrderSelect>()
|
.Where(b => b.MachineNo == query.machineNo && b.IsShow == 0)
|
.Select(s => s.OrderId)
|
.ToList();
|
|
// 去重
|
var decimals = ids.Distinct().ToList();
|
var date = DateTime.Now.ToString("yyyy-MM-dd");
|
|
// 如果没有工单ID，直接返回空列表
|
if (decimals.Count == 0)
|
return new List<VOrderBycl>();
|
|
// 查询齿轮数采表
|
var numericalList = Db.Queryable<MesNumericalBycl>()
|
.Where(x => ids.Contains(x.OrderId ?? 0))
|
.OrderBy(x => x.CjTiem)
|
.ToList();
|
|
var vOrderBycl = new VOrderBycl();
|
if (numericalList.Count > 0)
|
{
|
// 按采集时间升序排序
|
var sortedList = numericalList
|
.OrderBy(x => DateTime.TryParse(x.CjTiem, out var dt) ? dt : DateTime.MinValue)
|
.ToList();
|
|
// 开工数采：最早一条
|
vOrderBycl.StartCjNum = vOrderBycl.preStartCjNum ?? (sortedList.First().CjNum ?? 0);
|
// 当前数采：最新一条
|
vOrderBycl.CurrentCjNum = sortedList.Last().CjNum ?? 0;
|
}
|
else
|
{
|
vOrderBycl.StartCjNum = 0;
|
vOrderBycl.CurrentCjNum = 0;
|
}
|
|
// 查询报工记录，获取最后一条报工记录
|
var lastReport = Db.Queryable<MesReporting>()
|
.AS("MES_REPORTING") // 强制表名
|
.Where(r => r.BillNo == query.orderNo)
|
.OrderBy(r => r.BgDate, OrderByType.Desc)
|
.First();
|
|
if (lastReport == null || !lastReport.BgDate.HasValue)
|
{
|
if (string.IsNullOrEmpty(vOrderBycl.Daa016)) // 没有开工时间
|
{
|
// 没有报工记录且没有开工时间，初始采集数为调机时间前一条的采集数量
|
vOrderBycl.InitCjNum = vOrderBycl.preStartTjNum;
|
}
|
else
|
{
|
// 没有报工记录，初始采集数为开工时间前一条的采集数量
|
vOrderBycl.InitCjNum = vOrderBycl.preStartCjNum;
|
}
|
|
//// 没有报工记录，初始采集数为开工采数
|
//VOrdrerBycl.InitCjNum = VOrdrerBycl.StartCjNum;
|
}
|
else
|
{
|
// 有报工记录，初始采集数为报工时间点前（含）最近一条齿轮数采表的采集数量
|
var beforeOrEqualReportNum = numericalList
|
.Select(x => new { Item = x, ParsedTime = DateTime.TryParse(x.CjTiem, out var dt) ? dt : (DateTime?)null })
|
.Where(x => x.ParsedTime.HasValue && x.ParsedTime.Value <= lastReport.BgDate.Value)
|
.OrderByDescending(x => x.ParsedTime) // 降序，取第一条
|
.Select(x => x.Item)
|
.FirstOrDefault();
|
|
vOrderBycl.InitCjNum = beforeOrEqualReportNum?.CjNum ?? vOrderBycl.StartCjNum;
|
}
|
|
var startCjNum = vOrderBycl.StartCjNum;
|
var currentCjNum = vOrderBycl.CurrentCjNum;
|
var initCjNum = vOrderBycl.InitCjNum;
|
|
// 查询工单视图，筛选出需要显示的工单，只取最新一条
|
var orders = Db.Queryable<VOrderBycl>()
|
.Where(c =>
|
decimals.Contains(c.ID) &&
|
(c.EditDate == date || c.EditDate == null))
|
.OrderBy(c => c.BgDate, OrderByType.Desc)
|
.Take(1)
|
.ToList();
|
|
foreach (var order in orders)
|
{
|
order.StartCjNum = startCjNum;
|
order.CurrentCjNum = currentCjNum;
|
order.InitCjNum = initCjNum;
|
}
|
|
return orders;
|
}
|
|
/// <summary>
|
/// 根据工单ID获取工单详细信息
|
/// </summary>
|
/// <param name="query">包含工单ID和工单号的查询条件</param>
|
/// <returns>工单详细信息</returns>
|
public VOrderBycl GetWomdaaById(OrderMachineDto query)
|
{
|
var date = DateTime.Now.ToString("yyyy-MM-dd");
|
|
var count = Db.Queryable<DeviceStatusBycl>()
|
.Where(b => b.OrderNo == query.orderNo && b.EditDate == date)
|
.Count();
|
|
var VOrdrerBycl = Db.Queryable<VOrderBycl>()
|
.Where(s => s.ID == query.OrderId)
|
.WhereIF(count > 0, s => s.EditDate == date)
|
.OrderByDescending(s => s.workStartDate).First();
|
|
if (VOrdrerBycl == null) return null;
|
|
VOrdrerBycl.YJQTY ??= 0;
|
|
var mesOrderSelect = Db.Queryable<MesOrderSelect>()
|
.Where(a => a.OrderId == VOrdrerBycl.ID
|
&& a.IsShow == 0).First();
|
|
// 保留视图里的 todayRunTime 和 JDL，不做任何赋值和转换
|
// VOrdrerBycl.todayRunTime 保持原值
|
// VOrdrerBycl.JDL 保持原值
|
//VOrdrerBycl.TodayFaultNum = 0;
|
|
if (mesOrderSelect != null)
|
{
|
VOrdrerBycl.TodayFaultNum = mesOrderSelect.TjCount;
|
}
|
|
VOrdrerBycl.wjQty = VOrdrerBycl.Daa008 - VOrdrerBycl.Daa011;
|
|
var devMachine = Db.Queryable<DevMacBycl>()
|
.Where(s => s.MachineNo == VOrdrerBycl.MachineNo)
|
.First();
|
|
var sql = string.Format(
|
"select FINAL_RESULT from MES_EQ_SPOT_CHECKS where department_no = 'XS0101' and EQ_NO = '{0}' and substr(SUBMIT_DATE, 1, 10) = to_char(sysdate, 'yyyy-mm-dd')",
|
devMachine.UniqueBarcode);
|
|
var sqlQuerySingle = Db.Ado.SqlQuerySingle<string>(sql);
|
VOrdrerBycl.finalResult = sqlQuerySingle;
|
|
// 查询MES_NUMERICAL_BYCL表，获取开工数采和当前数采
|
var numericalList = Db.Queryable<MesNumericalBycl>()
|
.Where(x => x.OrderId == VOrdrerBycl.ID)
|
.OrderBy(x => x.CjTiem)
|
.ToList();
|
|
if (numericalList.Count > 0)
|
{
|
// 先按采集时间升序排序
|
var sortedList = numericalList
|
.OrderBy(x => DateTime.TryParse(x.CjTiem, out var dt) ? dt : DateTime.MinValue)
|
.ToList();
|
|
// 开工数采：最早一条
|
VOrdrerBycl.StartCjNum = VOrdrerBycl.preStartCjNum ?? (sortedList.First().CjNum ?? 0);
|
// 当前数采：最新一条
|
VOrdrerBycl.CurrentCjNum = sortedList.Last().CjNum ?? 0;
|
}
|
else
|
{
|
VOrdrerBycl.StartCjNum = 0;
|
VOrdrerBycl.CurrentCjNum = 0;
|
}
|
|
// 计算当天停机次数
|
//VOrdrerBycl.TodayDowntimeCount = GetTodayDowntimeCount(numericalList);
|
|
//计算日停机次数、todayOnlineTime（总在线时长）、todayRunTime（实际运行时长）、稼动率（百分比，保留两位小数）
|
VOrdrerBycl.TodayDowntimeCount = GetTodayDowntimeStats(numericalList).downtimeCount;
|
VOrdrerBycl.TodayOnlineTime = GetTodayDowntimeStats(numericalList).todayOnlineTime;
|
VOrdrerBycl.todayRunTime = GetTodayDowntimeStats(numericalList).todayRunTime;
|
VOrdrerBycl.JDL = GetTodayDowntimeStats(numericalList).utilizationRate;
|
|
Console.WriteLine($"orderNo: [{query.orderNo}]");
|
|
// 查询报工记录，获取最后一条报工记录
|
var lastReport = Db.Queryable<MesReporting>()
|
.Where(r => r.BillNo.Trim().ToUpper() == query.orderNo.Trim().ToUpper())
|
.OrderBy(r => r.BgDate, OrderByType.Desc)
|
.First();
|
|
if (lastReport == null || !lastReport.BgDate.HasValue)//没有报工记录
|
{
|
if (string.IsNullOrEmpty(VOrdrerBycl.Daa016)) // 没有开工时间
|
{
|
// 没有报工记录且没有开工时间，初始采集数为调机时间前一条的采集数量
|
VOrdrerBycl.InitCjNum = VOrdrerBycl.preStartTjNum;
|
}
|
else
|
{
|
// 没有报工记录，初始采集数为开工时间前一条的采集数量
|
VOrdrerBycl.InitCjNum = VOrdrerBycl.preStartCjNum;
|
|
//// 没有报工记录，初始采集数为开工采数
|
//VOrdrerBycl.InitCjNum = VOrdrerBycl.StartCjNum;
|
}
|
}
|
else
|
{
|
// 有报工记录，初始采集数为报工时间点前（含）最近一条齿轮数采表的采集数量
|
var beforeOrEqualReportNum = numericalList
|
.Select(x => new { Item = x, ParsedTime = DateTime.TryParse(x.CjTiem, out var dt) ? dt : (DateTime?)null })
|
.Where(x => x.ParsedTime.HasValue && x.ParsedTime.Value <= lastReport.BgDate.Value)
|
.OrderByDescending(x => x.ParsedTime) // 降序，取第一条
|
.Select(x => x.Item)
|
.FirstOrDefault();
|
|
VOrdrerBycl.InitCjNum = beforeOrEqualReportNum?.CjNum ?? VOrdrerBycl.StartCjNum;
|
}
|
|
return VOrdrerBycl;
|
}
|
|
/// <summary>
|
/// 计算日停机次数
|
/// </summary>
|
/// <param name="numericalList"></param>
|
/// <returns></returns>
|
public int GetTodayDowntimeCount(List<MesNumericalBycl> numericalList)
|
{
|
var today = DateTime.Now.Date;
|
// 1. 只保留当天数据，并转换为 DateTime
|
var todayTimes = numericalList
|
.Where(x => DateTime.TryParse(x.CjTiem, out var dt) && dt.Date == today)
|
.Select(x => DateTime.Parse(x.CjTiem))
|
.OrderBy(x => x)
|
.ToList();
|
|
if (todayTimes.Count < 2)
|
return 0;
|
|
int downtimeCount = 0;
|
for (int i = 1; i < todayTimes.Count; i++)
|
{
|
var diff = (todayTimes[i] - todayTimes[i - 1]).TotalMinutes;
|
if (diff > 3)
|
downtimeCount++;
|
}
|
return downtimeCount;
|
}
|
|
/// <summary>
|
/// 计算日停机次数、todayOnlineTime（总在线时长）、todayRunTime（实际运行时长）、稼动率（百分比，保留两位小数）
|
/// </summary>
|
public (int downtimeCount, double todayOnlineTime, double todayRunTime, double utilizationRate) GetTodayDowntimeStats(List<MesNumericalBycl> numericalList)
|
{
|
var today = DateTime.Now.Date;
|
var todayTimes = numericalList
|
.Where(x => DateTime.TryParse(x.CjTiem, out var dt) && dt.Date == today)
|
.Select(x => DateTime.Parse(x.CjTiem))
|
.OrderBy(x => x)
|
.ToList();
|
|
int downtimeCount = 0;
|
double downtimeMinutes = 0;
|
double todayOnlineTime = 0;
|
double todayRunTime = 0;
|
double utilizationRate = 0;
|
|
if (todayTimes.Count < 2)
|
return (0, 0, 0, 0);
|
|
for (int i = 1; i < todayTimes.Count; i++)
|
{
|
var diff = (todayTimes[i] - todayTimes[i - 1]).TotalMinutes;
|
if (diff > 3)
|
{
|
downtimeCount++;
|
downtimeMinutes += diff;
|
}
|
}
|
|
todayOnlineTime = (todayTimes.Last() - todayTimes.First()).TotalMinutes;
|
todayRunTime = todayOnlineTime - downtimeMinutes;
|
if (todayOnlineTime > 0)
|
utilizationRate = Math.Round(todayRunTime / todayOnlineTime * 100, 2);
|
|
return (downtimeCount, todayOnlineTime, todayRunTime, utilizationRate);
|
}
|
|
/// <summary>
|
/// 根据工单ID获取报工信息
|
/// </summary>
|
/// <param name="query">包含工单ID的查询条件</param>
|
/// <returns>工单报工信息</returns>
|
public VPrint GetWomdaaPrintById(OrderMachineDto query)
|
{
|
var vPrint = Db.Queryable<VPrint>()
|
.Where(s => s.Id == query.OrderId)
|
.First();
|
|
if (vPrint == null)
|
return null;
|
|
// 防止除零和空值异常
|
var bqty = vPrint.Bqty ?? 0;
|
var sQuantity = vPrint.SQuantity ?? 0;
|
var qqty = vPrint.Qqty ?? 0;
|
|
if (qqty == 0)
|
{
|
vPrint.RmiPrQty = 0;
|
}
|
else
|
{
|
// 剩余可打印张数 = (可打印总数量 - 已打印数量) / 标准包装数，向上取整
|
var remain = bqty - sQuantity;
|
vPrint.RmiPrQty = remain > 0 ? (int)Math.Ceiling((double)remain / (double)qqty) : 0;
|
}
|
|
return vPrint;
|
}
|
|
/// <summary>
|
/// 报工记录表
|
/// </summary>
|
/// <param name="billNo"></param>
|
/// <param name="machineNo"></param>
|
/// <param name="from"></param>
|
/// <param name="to"></param>
|
/// <param name="pageIndex"></param>
|
/// <param name="pageSize"></param>
|
/// <returns></returns>
|
public (List<MesReportingBgDto> tbBillList, int totalCount) GetByBillNoBG(
|
string billNo, string machineNo, DateTime? from, DateTime? to, int pageIndex, int pageSize)
|
{
|
var query = Db.Queryable<MesReporting>()
|
// 关联员工信息表，使用报工人编码匹配员工编码
|
.LeftJoin<MesStaff>((r, s) => r.BgPerson == s.StaffNo)
|
.Where((r, s) => r.BillNo == billNo);
|
|
if (!string.IsNullOrWhiteSpace(machineNo))
|
query = query.Where((r, s) => r.MachineNo == machineNo);
|
|
if (from.HasValue)
|
query = query.Where((r, s) => r.BgDate >= from.Value);
|
|
if (to.HasValue)
|
query = query.Where((r, s) => r.BgDate < to.Value.AddDays(1));
|
|
var totalCount = query.Count();
|
|
var tbBillList = query.OrderBy((r, s) => r.BgDate, OrderByType.Desc)
|
.Select((r, s) => new MesReportingBgDto
|
{
|
Id = (long)r.Id,
|
BillNo = r.BillNo,
|
ItemNo = r.ItemNo,
|
MachineNo = r.MachineNo,
|
StaffNo = r.BgPerson,//报工人编号
|
StaffName = SqlFunc.IsNullOrEmpty(s.StaffName) ? r.BgPerson : s.StaffName, // 如有独立姓名字段可替换
|
BgDate = r.BgDate,//报工时间
|
CsQty = SqlFunc.ToInt32(r.CsQty),//初始采集数
|
CjQty = SqlFunc.ToInt32(r.CjQty),//报工时采集数
|
BfQty = SqlFunc.ToInt32(r.BfQty),//不良数量
|
OkQty = SqlFunc.ToInt32(r.OkQty),//良品数量
|
REMARK = r.Remark //备注
|
})
|
.ToPageList((pageIndex < 1 ? 1 : pageIndex),
|
(pageSize < 1 ? 200 : (pageSize > 1000 ? 1000 : pageSize)), ref totalCount);
|
|
return (tbBillList, totalCount);
|
}
|
}
|
