import { Service } from "egg";
export default class ExportAutoMeasureModule extends Service{
public async getMachineTime(){
console.log("measuremachinetime被调用");
const date = new Date();
date.setDate(date.getDate() - 1);
const yesterdayStr = this.formatDate(date);
//console.log("请求", this.ctx.request)
const data = this.ctx.request.body;
console.log("请求负载", data);
const { formdata } = data;
const datePicker = formdata._value.datePicker || [];
//console.log(datePicker[0])
const startdatestr = datePicker.length > 0 ? this.formatDate(new Date(datePicker[0])) : yesterdayStr;
const enddatestr = datePicker.length > 1 ? this.formatDate(new Date(datePicker[1])) : yesterdayStr;
const machineGroups = formdata._value.filterMachineGroup || [];
const measureRawData = await this.getMeasureRawData(machineGroups, startdatestr, enddatestr);
const measureData = this.processMeasureTimeData(measureRawData);
//console.log("measureData", measureData)
const aggmethod = formdata._value.filterAggreMethod || '';
const result = this.aggregateAndCalculate(aggmethod, measureData);
//console.log(result)
return result;
}
private async getMeasureRawData(machineGroups: string[], startdatestr: string, enddatestr: string){
let measureMachineCondition = `
where t.machinename = q.machinename
and t.machinetype = 'Measure'
and (t.PE != 'YE1' and t.PE != 'BR' and t.PE != 'OQA')
and (t.machinegroupname != 'SNPG' and t.machinegroupname != 'SVOC' and t.machinegroupname != 'MRSM' and t.machinegroupname != 'LPUI' and
t.machinegroupname != 'IBFT' and t.machinegroupname != 'ITMW' and t.machinegroupname != 'IWBA' and t.machinegroupname != 'LAMO' and t.machinegroupname != 'LMDI')
`
if(Array.isArray(machineGroups) && machineGroups.length > 0){
const quoteGroups = machineGroups.map(g => `'${g}'`).join(',');
measureMachineCondition += ` and t.machinegroupname in (${quoteGroups})`;
}
const sql = `
with MeasureMachine as (
select t.machinename, t.description, t.machinegroupname, q.machinecapabilityname
from RPT.BASE_MACHINE t, RPT.BASE_MACHINECAPABILITY q
${measureMachineCondition}
order by t.machinename),
YesterdayData as (
select d.factorydate, d.eqpid,
TO_CHAR(to_date(d.factorydate, 'yyyyMMdd'), 'yyyy') || 'WW'|| TO_CHAR(to_date(d.factorydate, 'yyyyMMdd'), 'iW') as week,
TO_CHAR(to_date(d.factorydate, 'yyyyMMdd'), 'MM') as month,
d.tottime,
round(d.uptime, 3) as uptime, round(d.downtime, 3) as downtime, round(d.idle, 3) as idletime, round(d.mon_rou, 3) as mon_rou from RPT.EQP_EVENT_HISTORY d
where d.factorydate between '${startdatestr}' and '${enddatestr}'
)
select a.*, b.* from MeasureMachine a join YesterdayData b on a.machinename = b.eqpid order by a.machinename, b.factorydate
`
//console.log("查询", sql)
const result = await this.ctx.service.oracleService.executeQuery(
"FA",
sql
);
return result;
}
private processMeasureTimeData(rawdata: any[]){
return rawdata.map(item => {
const { DOWNTIME, ...rest} = item;
const processedDowntime = item.DOWNTIME - item.MON_ROU;
const productionTime = 24 - item.IDLETIME - item.MON_ROU - processedDowntime;
return {
...rest,
DOWNTIME: processedDowntime,
PRODUCTIONTIME: productionTime
};
});
}
private aggregateAndCalculate(aggmethod: string, data: any[]){
// console.log("aggmethod", aggmethod)
const result: any[] = [];
const fieldsToCopy = ['MACHINENAME', 'FACTORYDATE', 'WEEK', 'MONTH', 'TOTTIME', 'DOWNTIME', 'IDLETIME', 'MON_ROU', 'PRODUCTIONTIME'];
// 聚合方式(Daily/Weekly/Monthly)
const method = (aggmethod ? aggmethod : 'Daily').toLowerCase();
//console.log("method", method)
const roundTo3Decimals = (num: number) => {
const fixedNum = Math.round(num * 1000) / 1000;
return fixedNum.toFixed(3)
}
if (method === 'daily') {
data.forEach(item => {
const monRatio = item.TOTTIME ? ((item.MON_ROU / item.TOTTIME) * 100).toFixed(2) : '0.00';
const productionRatio = item.TOTTIME ? ((item.PRODUCTIONTIME / item.TOTTIME) * 100).toFixed(2) : '0.00';
// 提取除fieldsToCopy外其他字段
const otherFields = Object.keys(item).reduce((acc, key) => {
if (!fieldsToCopy.includes(key.toUpperCase())) {
acc[key] = item[key];
}
return acc;
}, {} as Record<string, any>);
result.push({
MACHINENAME: item.MACHINENAME,
FACTORYDATE: item.FACTORYDATE,
WEEK: item.WEEK,
MONTH: item.MONTH,
TOTTIME: item.TOTTIME,
DOWNTIME: roundTo3Decimals(item.DOWNTIME),
IDLETIME: roundTo3Decimals(item.IDLETIME),
MON_ROU: roundTo3Decimals(item.MON_ROU),
PRODUCTIONTIME: roundTo3Decimals(item.PRODUCTIONTIME),
MON_RATIO: `${parseFloat(monRatio)}%`,
PRODUCTION_RATIO: `${parseFloat(productionRatio)}%`,
...otherFields
});
})
} else {
const groupedData: { [key: string]: any } = {};
data.forEach(item => {
let key: string;
if (method === 'weekly') {
key = `${item.MACHINENAME}-${item.WEEK}`;
} else if (method === 'monthly') {
key = `${item.MACHINENAME}-${item.MONTH}`;
} else {
return;
}
if (!groupedData[key]) {
groupedData[key] = {
MACHINENAME: item.MACHINENAME,
WEEK: method === 'weekly' ? item.WEEK : undefined,
MONTH: method === 'monthly' ? item.MONTH : undefined,
TOTTIME: 0,
DOWNTIME: 0,
IDLETIME: 0,
MON_ROU: 0,
PRODUCTIONTIME: 0,
otherFields: {}
};
}
groupedData[key].TOTTIME += item.TOTTIME;
groupedData[key].DOWNTIME += item.DOWNTIME;
groupedData[key].IDLETIME += item.IDLETIME;
groupedData[key].MON_ROU += item.MON_ROU;
groupedData[key].PRODUCTIONTIME += item.PRODUCTIONTIME;
// 只保留第一条记录的其他字段
if (Object.keys(groupedData[key].otherFields).length === 0) {
Object.keys(item).forEach(k => {
if (!fieldsToCopy.includes(k.toUpperCase())) {
groupedData[key].otherFields[k] = item[k];
}
});
}
});
for (const key in groupedData){
const item = groupedData[key];
const monRatio = item.TOTTIME ? ((item.MON_ROU / item.TOTTIME) * 100).toFixed(2) : '0.00';
const productionRatio = item.TOTTIME ? ((item.PRODUCTIONTIME / item.TOTTIME) * 100).toFixed(2) : '0.00';
result.push({
MACHINENAME: item.MACHINENAME,
WEEK: item.WEEK,
MONTH: item.MONTH,
TOTTIME: item.TOTTIME,
DOWNTIME: roundTo3Decimals(item.DOWNTIME),
IDLETIME: roundTo3Decimals(item.IDLETIME),
MON_ROU: roundTo3Decimals(item.MON_ROU),
PRODUCTIONTIME: roundTo3Decimals(item.PRODUCTIONTIME),
MON_RATIO: `${parseFloat(monRatio)}%`,
PRODUCTION_RATIO: `${parseFloat(productionRatio)}%`,
...item.otherFields
});
}
}
return result;
}
private formatDate(date: Date):string{
const year = date.getFullYear();
const month = String(date.getMonth() + 1).padStart(2, '0');
const day = String(date.getDate()).padStart(2, '0');
return `${year}${month}${day}`;
}
public async getMachineGroupOptions(){
console.log("getMachineGroupOptions被调用");
const result = await this.getMachineGroups();
return result;
}
private async getMachineGroups(){
const sql = `
select distinct t.machinegroupname from RPT.BASE_MACHINE t
where t.machinetype = 'Measure'
and (t.PE != 'YE1' and t.PE != 'BR' and t.PE != 'OQA')
and (t.machinegroupname != 'SNPG' and t.machinegroupname != 'SVOC' and t.machinegroupname != 'MRSM' and t.machinegroupname != 'LPUI' and
t.machinegroupname != 'IBFT' and t.machinegroupname != 'ITMW' and t.machinegroupname != 'IWBA' and t.machinegroupname != 'LAMO' and t.machinegroupname != 'LMDI')
order by t.machinegroupname
`
const result = await this.ctx.service.oracleService.executeQuery(
"FA",
sql
);
return result;
}
public async getMachineCapability(){
}
}
请帮我分析这段service代码
最新发布
扫一扫
1529
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
