import { Inject, Provide } from '@midwayjs/core';
|
import { BaseService } from '@cool-midway/core';
|
import { InjectEntityModel } from '@midwayjs/typeorm';
|
import { Repository, SelectQueryBuilder } from 'typeorm';
|
import { Context } from '@midwayjs/koa';
|
import * as moment from 'moment';
|
import { ReportDailysiteEntity } from '../entity/dailysite';
|
import { ReportDailyVehicleEntity } from '../entity/dailyvehicle';
|
import { ReportDailySorterEntity } from '../entity/dailysorter';
|
import { BasicdataWastetypeEntity } from '../../basicdata/entity/wastetype';
|
import { BasicdataSorterEntity } from '../../basicdata/entity/sorter';
|
import { BasicdataVehicleEntity } from '../../basicdata/entity/vehicle';
|
import { BasicdataSiteEntity } from '../../basicdata/entity/site';
|
import { RecycleOrderEntity } from '../../shop/entity/recycleorder';
|
import { RecycleAppointmentEntity } from '../../shop/entity/recycleappointment';
|
import { RecycleTransactionEntity } from '../../shop/entity/transaction';
|
import { UserInfoEntity } from '../../user/entity/info';
|
import { PushVehicleGpsEntity } from '../../push/entity/vehiclegps';
|
import { BaseSysParamEntity } from '../../base/entity/sys/param';
|
|
const WASTE_COLORS: Record<string, string> = {
|
SW62: '#16a34a',
|
SW64: '#64748b',
|
SW63: '#f59e0b',
|
SW61: '#ef4444',
|
SW60: '#06b6d4',
|
};
|
|
const ITEM_COLORS = ['#3b82f6', '#22c55e', '#f59e0b', '#a855f7', '#06b6d4', '#ef4444', '#94a3b8'];
|
|
type OrdersByType = {
|
axis: string[];
|
'现场回收': number[];
|
'预约上门': number[];
|
'大屏投递': number[];
|
'活动回收': number[];
|
};
|
|
function n(v: any, d = 0) {
|
const x = Number(v);
|
return Number.isFinite(x) ? x : d;
|
}
|
|
function round(v: number, d = 2) {
|
const p = 10 ** d;
|
return Math.round(n(v) * p) / p;
|
}
|
|
@Provide()
|
export class ReportScreenService extends BaseService {
|
@Inject()
|
ctx: Context;
|
|
@InjectEntityModel(ReportDailysiteEntity)
|
dailySite: Repository<ReportDailysiteEntity>;
|
|
@InjectEntityModel(ReportDailyVehicleEntity)
|
dailyVehicle: Repository<ReportDailyVehicleEntity>;
|
|
@InjectEntityModel(ReportDailySorterEntity)
|
dailySorter: Repository<ReportDailySorterEntity>;
|
|
@InjectEntityModel(BaseSysParamEntity)
|
paramRepo: Repository<BaseSysParamEntity>;
|
|
@InjectEntityModel(BasicdataWastetypeEntity)
|
wasteTypeRepo: Repository<BasicdataWastetypeEntity>;
|
|
@InjectEntityModel(BasicdataSorterEntity)
|
sorterRepo: Repository<BasicdataSorterEntity>;
|
|
@InjectEntityModel(BasicdataVehicleEntity)
|
vehicleRepo: Repository<BasicdataVehicleEntity>;
|
|
@InjectEntityModel(BasicdataSiteEntity)
|
siteRepo: Repository<BasicdataSiteEntity>;
|
|
@InjectEntityModel(RecycleOrderEntity)
|
orderRepo: Repository<RecycleOrderEntity>;
|
|
@InjectEntityModel(RecycleAppointmentEntity)
|
appointmentRepo: Repository<RecycleAppointmentEntity>;
|
|
@InjectEntityModel(RecycleTransactionEntity)
|
transRepo: Repository<RecycleTransactionEntity>;
|
|
@InjectEntityModel(UserInfoEntity)
|
userRepo: Repository<UserInfoEntity>;
|
|
@InjectEntityModel(PushVehicleGpsEntity)
|
vehicleGpsRepo: Repository<PushVehicleGpsEntity>;
|
|
async query(range: string) {
|
const unavailable: string[] = [];
|
const dates = this.getRange(range || 'month');
|
const today = moment().format('YYYY-MM-DD');
|
const yearStart = moment().startOf('year').format('YYYY-MM-DD');
|
const yearEnd = moment().format('YYYY-MM-DD');
|
|
const deptIds = this.deptIds();
|
if (deptIds && deptIds.length === 0) {
|
unavailable.push('当前账号没有部门数据权限');
|
}
|
|
const safe = async <T>(label: string, fn: () => Promise<T>, fallback: T): Promise<T> => {
|
try {
|
return await fn();
|
} catch (e: any) {
|
unavailable.push(`${label}：${e?.message || '查询失败'}`);
|
return fallback;
|
}
|
};
|
|
const emptySum = {};
|
const [period, todayRow, totalRow, yearRow] = await Promise.all([
|
safe('区间站点日汇总', () => this.sumSite(dates.start, dates.end), emptySum),
|
safe('今日站点日汇总', () => this.sumSite(today, today), emptySum),
|
safe('累计站点日汇总', () => this.sumSite('1970-01-01', today), emptySum),
|
safe('年度站点日汇总', () => this.sumSite(yearStart, yearEnd), emptySum),
|
]);
|
|
const wasteNames = await safe('垃圾种类名称', () => this.wasteNameMap(), new Map<string, string>());
|
const wasteMix = this.buildWasteMix(period, wasteNames);
|
const recycleItems = this.buildRecycleItems(period, wasteNames);
|
const { axis, trend } = await safe(
|
'回收趋势',
|
() => this.buildTrend(range || 'month', dates),
|
{ axis: [], trend: { weight: [], value: [], carbon: [] } }
|
);
|
const yearCarbon = await safe('年度碳减排', () => this.buildYearCarbon(), Array.from({ length: 12 }, () => 0));
|
const ranks = await safe(
|
'班级/专业/院系排行',
|
() => this.buildOrgRanks(dates.startTime, dates.endTime, unavailable),
|
{ classRank: [], majorRank: [], collegeRank: [] }
|
);
|
const users = await safe(
|
'师生参与与碳积分',
|
() => this.buildUserPoints(range || 'month', dates, axis, unavailable),
|
{
|
totalParticipants: 0,
|
newParticipants: 0,
|
participateRate: 0,
|
greenActions: 0,
|
issued: 0,
|
consumed: 0,
|
transfer: 0,
|
donate: 0,
|
mall: 0,
|
balance: 0,
|
participateTrend: axis.map(() => 0),
|
deliverTrend: axis.map(() => 0),
|
}
|
);
|
const ops = await safe('运营监控', () => this.buildOperation(today, dates, unavailable), {
|
sorterOnline: 0,
|
sorterTotal: 0,
|
vehicleOnline: 0,
|
vehicleTotal: 0,
|
siteOnline: 0,
|
siteTotal: 0,
|
todayOrders: 0,
|
todayAppointments: 0,
|
pendingAppointments: 0,
|
alerts: 0,
|
healthAlerts: 0,
|
ordersByType: {
|
axis: [],
|
'现场回收': [],
|
'预约上门': [],
|
'大屏投递': [],
|
'活动回收': [],
|
} as OrdersByType,
|
appointmentFlow: [],
|
sorterStatus: [],
|
vehicles: [],
|
sites: [],
|
});
|
|
const weightKg = n(period.weightTotal);
|
const recyclableKg = n(period.weightSw62);
|
const carbonKg = n(period.carbonTotal);
|
const totalCarbonKg = n(totalRow.carbonTotal);
|
|
return {
|
range: range || 'month',
|
axis,
|
kpis: {
|
todayWeightKg: round(n(todayRow.weightTotal), 1),
|
periodWeightTon: round(weightKg / 1000, 2),
|
resourceRate: weightKg > 0 ? round((recyclableKg / weightKg) * 100, 1) : 0,
|
periodValue: round(n(period.estimatedSalesSw62), 0),
|
periodCarbonTon: round(carbonKg / 1000, 2),
|
totalWeightTon: round(n(totalRow.weightTotal) / 1000, 1),
|
totalParticipants: users.totalParticipants,
|
totalPoints: users.balance,
|
recyclableTon: round(recyclableKg / 1000, 2),
|
bulkTon: round(n(period.weightSw63) / 1000, 2),
|
elecTon: round(n(period.weightSw62006) / 1000, 2),
|
fabricTon: round(n(period.weightSw62004) / 1000, 2),
|
yearCarbonTon: round(n(yearRow.carbonTotal) / 1000, 1),
|
monthCarbonTon: round(carbonKg / 1000, 2),
|
totalCarbonTon: round(totalCarbonKg / 1000, 1),
|
greenActions: users.greenActions,
|
newParticipants: users.newParticipants,
|
participateRate: users.participateRate,
|
pointsIssued: users.issued,
|
pointsConsumed: users.consumed,
|
pointsBalance: users.balance,
|
sorterOnline: ops.sorterOnline,
|
sorterTotal: ops.sorterTotal,
|
vehicleOnline: ops.vehicleOnline,
|
vehicleTotal: ops.vehicleTotal,
|
siteOnline: ops.siteOnline,
|
siteTotal: ops.siteTotal,
|
todayOrders: ops.todayOrders,
|
todayAppointments: ops.todayAppointments,
|
pendingAppointments: ops.pendingAppointments,
|
alerts: ops.alerts,
|
healthAlerts: ops.healthAlerts,
|
},
|
wasteMix,
|
recycleItems,
|
trend,
|
yearCarbon,
|
deptRank: ranks.collegeRank,
|
classRank: ranks.classRank,
|
majorRank: ranks.majorRank,
|
pointFlow: {
|
issued: users.issued,
|
transfer: users.transfer,
|
donate: users.donate,
|
mall: users.mall,
|
balance: users.balance,
|
},
|
participateTrend: users.participateTrend,
|
deliverTrend: users.deliverTrend,
|
ordersByType: ops.ordersByType,
|
appointmentFlow: ops.appointmentFlow,
|
sorterStatus: ops.sorterStatus,
|
vehicles: ops.vehicles,
|
sites: ops.sites,
|
unavailable,
|
};
|
}
|
|
private deptIds(): number[] | null {
|
const admin = this.ctx.admin;
|
if (!admin || admin.username === 'admin') return null;
|
return Array.isArray(admin.departmentIds) ? admin.departmentIds.map(Number) : [];
|
}
|
|
private applyDept(qb: SelectQueryBuilder<any>, alias: string) {
|
const ids = this.deptIds();
|
if (ids === null) return qb;
|
if (!ids.length) {
|
qb.andWhere('1 = 0');
|
return qb;
|
}
|
qb.andWhere(`${alias}.departmentId IN (:...deptIds)`, { deptIds: ids });
|
return qb;
|
}
|
|
/** 编制人数：base_sys_param.keyName = student.count.{departmentId} */
|
private async studentCount(unavailable: string[]) {
|
const ids = this.deptIds();
|
if (ids !== null && ids.length === 0) return 0;
|
const keys =
|
ids === null
|
? (
|
await this.paramRepo
|
.createQueryBuilder('p')
|
.select('p.keyName', 'keyName')
|
.addSelect('p.data', 'data')
|
.where("p.keyName LIKE :prefix", { prefix: 'student.count.%' })
|
.getRawMany()
|
).map(r => ({ key: r.keyName, data: r.data }))
|
: await Promise.all(
|
ids.map(async id => {
|
const row = await this.paramRepo.findOneBy({ keyName: `student.count.${id}` });
|
return { key: `student.count.${id}`, data: row?.data };
|
})
|
);
|
|
const missing = keys.filter(e => e.data === undefined || e.data === null || e.data === '');
|
const total = keys.reduce((s, e) => s + n(e.data), 0);
|
if (!keys.length || missing.length === keys.length) {
|
unavailable.push('未配置编制人数参数 student.count.{departmentId}，参与率暂为 0');
|
return 0;
|
}
|
if (missing.length) {
|
unavailable.push(`缺少编制人数参数：${missing.map(e => e.key).join('、')}`);
|
}
|
return total;
|
}
|
|
private getRange(range: string) {
|
const now = moment();
|
let startM = now.clone().startOf('month');
|
if (range === 'today') startM = now.clone().startOf('day');
|
else if (range === 'week') startM = now.clone().startOf('isoWeek');
|
else if (range === 'quarter') startM = now.clone().startOf('quarter' as any);
|
else if (range === 'year') startM = now.clone().startOf('year');
|
|
return {
|
start: startM.format('YYYY-MM-DD'),
|
end: now.format('YYYY-MM-DD'),
|
startTime: startM.format('YYYY-MM-DD HH:mm:ss'),
|
endTime: now.clone().endOf('day').format('YYYY-MM-DD HH:mm:ss'),
|
};
|
}
|
|
private async sumSite(start: string, end: string) {
|
const qb = this.dailySite
|
.createQueryBuilder('a')
|
.select('SUM(a.weightTotal)', 'weightTotal')
|
.addSelect('SUM(a.weightSw60)', 'weightSw60')
|
.addSelect('SUM(a.weightSw61)', 'weightSw61')
|
.addSelect('SUM(a.weightSw62)', 'weightSw62')
|
.addSelect('SUM(a.weightSw63)', 'weightSw63')
|
.addSelect('SUM(a.weightSw64)', 'weightSw64')
|
.addSelect('SUM(a.timeTotal)', 'timeTotal')
|
.addSelect('SUM(a.timeSw60)', 'timeSw60')
|
.addSelect('SUM(a.timeSw61)', 'timeSw61')
|
.addSelect('SUM(a.timeSw62)', 'timeSw62')
|
.addSelect('SUM(a.timeSw63)', 'timeSw63')
|
.addSelect('SUM(a.timeSw64)', 'timeSw64')
|
.addSelect('SUM(a.carbonTotal)', 'carbonTotal')
|
.addSelect('SUM(a.estimatedSalesSw62)', 'estimatedSalesSw62');
|
|
for (const i of ['001', '002', '003', '004', '005', '006', '007']) {
|
qb.addSelect(`SUM(a.weightSw62${i})`, `weightSw62${i}`);
|
qb.addSelect(`SUM(a.timeSw62${i})`, `timeSw62${i}`);
|
qb.addSelect(`SUM(a.carbonSw62${i})`, `carbonSw62${i}`);
|
qb.addSelect(`SUM(a.estimatedSalesSw62${i})`, `estimatedSalesSw62${i}`);
|
}
|
|
qb.where('a.date BETWEEN :start AND :end', { start, end });
|
this.applyDept(qb, 'a');
|
return (await qb.getRawOne()) || {};
|
}
|
|
private async wasteNameMap() {
|
const list = await this.wasteTypeRepo.find({ select: ['code', 'name'] });
|
const map = new Map<string, string>();
|
list.forEach(e => map.set(String(e.code).toUpperCase(), e.name));
|
return map;
|
}
|
|
private buildWasteMix(row: any, names: Map<string, string>) {
|
const items = [
|
{ code: 'SW62', key: 'Sw62' },
|
{ code: 'SW64', key: 'Sw64' },
|
{ code: 'SW63', key: 'Sw63' },
|
{ code: 'SW61', key: 'Sw61' },
|
{ code: 'SW60', key: 'Sw60' },
|
];
|
return items.map(e => ({
|
name: names.get(e.code) || this.defaultWasteName(e.code),
|
code: e.code,
|
weight: round(n(row[`weight${e.key}`]), 1),
|
value: e.code === 'SW62' ? round(n(row.estimatedSalesSw62), 0) : 0,
|
count: round(n(row[`time${e.key}`]), 0),
|
color: WASTE_COLORS[e.code],
|
}));
|
}
|
|
private defaultWasteName(code: string) {
|
return (
|
{
|
SW60: '湿垃圾',
|
SW61: '有害垃圾',
|
SW62: '可回收物',
|
SW63: '大件垃圾',
|
SW64: '其他垃圾',
|
}[code] || code
|
);
|
}
|
|
private buildRecycleItems(row: any, names: Map<string, string>) {
|
const codes = [
|
'900-001-S62',
|
'900-002-S62',
|
'900-003-S62',
|
'900-004-S62',
|
'900-005-S62',
|
'900-006-S62',
|
'900-007-S62',
|
];
|
const fallback = ['废纸', '塑料', '金属', '织物', '玻璃', '电子产品', '其他可回收'];
|
return codes.map((code, i) => {
|
const idx = String(i + 1).padStart(3, '0');
|
return {
|
name: names.get(code.toUpperCase()) || names.get(code) || fallback[i],
|
code,
|
weight: round(n(row[`weightSw62${idx}`]), 1),
|
value: round(n(row[`estimatedSalesSw62${idx}`]), 0),
|
carbon: round(n(row[`carbonSw62${idx}`]), 1),
|
color: ITEM_COLORS[i],
|
};
|
});
|
}
|
|
private async buildTrend(range: string, dates: { start: string; end: string; startTime: string; endTime: string }) {
|
const trendStart =
|
range === 'today' ? moment().subtract(6, 'day').format('YYYY-MM-DD') : dates.start;
|
const trendEnd = dates.end;
|
|
const qb = this.dailySite
|
.createQueryBuilder('a')
|
.select('a.date', 'date')
|
.addSelect('SUM(a.weightTotal)', 'weight')
|
.addSelect('SUM(a.estimatedSalesSw62)', 'value')
|
.addSelect('SUM(a.carbonTotal)', 'carbon')
|
.where('a.date BETWEEN :start AND :end', { start: trendStart, end: trendEnd })
|
.groupBy('a.date')
|
.orderBy('a.date', 'ASC');
|
this.applyDept(qb, 'a');
|
const rows = await qb.getRawMany();
|
const map = new Map(
|
rows.map(r => [
|
moment(r.date).format('YYYY-MM-DD'),
|
{ weight: n(r.weight), value: n(r.value), carbon: n(r.carbon) },
|
])
|
);
|
|
const axis: string[] = [];
|
const weight: number[] = [];
|
const value: number[] = [];
|
const carbon: number[] = [];
|
|
if (range === 'year') {
|
for (let m = 1; m <= 12; m++) {
|
axis.push(`${m}月`);
|
const prefix = `${moment().year()}-${String(m).padStart(2, '0')}`;
|
let w = 0,
|
v = 0,
|
c = 0;
|
map.forEach((item, key) => {
|
if (key.startsWith(prefix)) {
|
w += item.weight;
|
v += item.value;
|
c += item.carbon;
|
}
|
});
|
weight.push(round(w, 1));
|
value.push(round(v, 0));
|
carbon.push(round(c, 1));
|
}
|
} else {
|
const cursor = moment(trendStart);
|
const end = moment(trendEnd);
|
while (cursor.isSameOrBefore(end, 'day')) {
|
const key = cursor.format('YYYY-MM-DD');
|
axis.push(cursor.format('MM-DD'));
|
const item = map.get(key) || { weight: 0, value: 0, carbon: 0 };
|
weight.push(round(item.weight, 1));
|
value.push(round(item.value, 0));
|
carbon.push(round(item.carbon, 1));
|
cursor.add(1, 'day');
|
}
|
}
|
|
return { axis, trend: { weight, value, carbon } };
|
}
|
|
private async buildYearCarbon() {
|
const start = moment().startOf('year').format('YYYY-MM-DD');
|
const end = moment().endOf('year').format('YYYY-MM-DD');
|
const qb = this.dailySite
|
.createQueryBuilder('a')
|
.select('MONTH(a.date)', 'm')
|
.addSelect('SUM(a.carbonTotal)', 'carbon')
|
.where('a.date BETWEEN :start AND :end', { start, end })
|
.groupBy('MONTH(a.date)');
|
this.applyDept(qb, 'a');
|
const rows = await qb.getRawMany();
|
const map = new Map(rows.map(r => [Number(r.m), n(r.carbon)]));
|
return Array.from({ length: 12 }, (_, i) => round((map.get(i + 1) || 0) / 1000, 3));
|
}
|
|
private async buildOrgRanks(startTime: string, endTime: string, unavailable: string[]) {
|
const empty = { classRank: [], majorRank: [], collegeRank: [] };
|
try {
|
const joinOrder = () => {
|
const qb = this.orderRepo
|
.createQueryBuilder('o')
|
.innerJoin(UserInfoEntity, 'u', 'o.campusUserId = u.unionid')
|
.where('o.status = :status', { status: '已完成' })
|
.andWhere('o.finishTime BETWEEN :start AND :end', { start: startTime, end: endTime });
|
this.applyDept(qb, 'o');
|
return qb;
|
};
|
|
const metrics = (qb: any) =>
|
qb
|
.addSelect('COUNT(DISTINCT o.campusUserId)', 'people')
|
.addSelect('SUM(o.totalWeight)', 'weight')
|
.addSelect('SUM(o.totalCarbonPoint)', 'points');
|
|
const classQb = metrics(
|
joinOrder()
|
.select('u.className', 'className')
|
.addSelect('u.collegeName', 'collegeName')
|
.andWhere("u.className IS NOT NULL AND u.className <> ''")
|
.groupBy('u.classId')
|
.addGroupBy('u.className')
|
.addGroupBy('u.collegeName')
|
.orderBy('people', 'DESC')
|
.limit(10)
|
);
|
|
const majorQb = metrics(
|
joinOrder()
|
.select('u.majorName', 'name')
|
.andWhere("u.majorName IS NOT NULL AND u.majorName <> ''")
|
.groupBy('u.majorId')
|
.addGroupBy('u.majorName')
|
.orderBy('people', 'DESC')
|
.limit(10)
|
);
|
|
const collegeQb = metrics(
|
joinOrder()
|
.select('u.collegeName', 'name')
|
.andWhere("u.collegeName IS NOT NULL AND u.collegeName <> ''")
|
.groupBy('u.collegeName')
|
.orderBy('people', 'DESC')
|
.limit(10)
|
);
|
|
const [classRows, majorRows, collegeRows] = await Promise.all([
|
classQb.getRawMany(),
|
majorQb.getRawMany(),
|
collegeQb.getRawMany(),
|
]);
|
|
if (!classRows.length) {
|
unavailable.push('班级排行暂无数据（className 为空或订单未关联用户）');
|
}
|
if (!majorRows.length) {
|
unavailable.push('专业排行暂无数据（majorName 为空或订单未关联用户）');
|
}
|
if (!collegeRows.length) {
|
unavailable.push('院系排行暂无数据（collegeName 为空或订单未关联用户）');
|
}
|
|
const toRank = (name: string, r: any, org?: string) => {
|
const people = n(r.people) || 1;
|
const weight = n(r.weight);
|
const points = n(r.points);
|
return {
|
name,
|
org,
|
weight: round(weight, 1),
|
people: n(r.people),
|
rate: 0,
|
points: round(points, 0),
|
carbon: round(weight, 1),
|
size: n(r.people),
|
weightPerCapita: round(weight / people, 2),
|
pointPerCapita: round(points / people, 1),
|
};
|
};
|
|
return {
|
classRank: classRows.map(r => toRank(r.className, r, r.collegeName)),
|
majorRank: majorRows.map(r => toRank(r.name, r)),
|
collegeRank: collegeRows.map(r => toRank(r.name, r)),
|
};
|
} catch (e) {
|
unavailable.push('师生组织排行查询失败，请检查回收订单与校园用户表');
|
return empty;
|
}
|
}
|
|
private async buildUserPoints(
|
range: string,
|
dates: { start: string; end: string; startTime: string; endTime: string },
|
axis: string[],
|
unavailable: string[]
|
) {
|
const result = {
|
totalParticipants: 0,
|
newParticipants: 0,
|
participateRate: 0,
|
greenActions: 0,
|
issued: 0,
|
consumed: 0,
|
transfer: 0,
|
donate: 0,
|
mall: 0,
|
balance: 0,
|
participateTrend: [] as number[],
|
deliverTrend: [] as number[],
|
};
|
|
try {
|
const partQb = this.orderRepo
|
.createQueryBuilder('o')
|
.select('COUNT(DISTINCT o.campusUserId)', 'c')
|
.where('o.status = :status', { status: '已完成' });
|
this.applyDept(partQb, 'o');
|
result.totalParticipants = n((await partQb.getRawOne())?.c);
|
|
const newQb = partQb.clone().andWhere('o.finishTime BETWEEN :start AND :end', {
|
start: dates.startTime,
|
end: dates.endTime,
|
});
|
result.newParticipants = n((await newQb.getRawOne())?.c);
|
|
const userTotal = await this.studentCount(unavailable);
|
result.participateRate = userTotal > 0 ? round((result.totalParticipants / userTotal) * 100, 1) : 0;
|
|
const actQb = this.orderRepo
|
.createQueryBuilder('o')
|
.select('COUNT(o.id)', 'c')
|
.where('o.status = :status', { status: '已完成' });
|
this.applyDept(actQb, 'o');
|
result.greenActions = n((await actQb.getRawOne())?.c);
|
|
const transQb = this.transRepo
|
.createQueryBuilder('t')
|
.select('t.sourceType', 'sourceType')
|
.addSelect('t.type', 'type')
|
.addSelect('SUM(t.amount)', 'amount')
|
.where('t.createTime BETWEEN :start AND :end', { start: dates.startTime, end: dates.endTime })
|
.groupBy('t.sourceType')
|
.addGroupBy('t.type');
|
this.applyDept(transQb, 't');
|
const transRows = await transQb.getRawMany();
|
transRows.forEach(r => {
|
const amount = n(r.amount);
|
if (r.sourceType === '回收' || r.sourceType === '签到') result.issued += amount;
|
if (r.sourceType === '商城') result.mall += amount;
|
if (r.sourceType === '赠送') result.transfer += amount;
|
if (r.sourceType === '捐赠') result.donate += amount;
|
if (r.type === '支出') result.consumed += amount;
|
});
|
|
const balQb = this.userRepo.createQueryBuilder('u').select('SUM(u.carbonBalance)', 'b');
|
this.applyDept(balQb, 'u');
|
result.balance = round(n((await balQb.getRawOne())?.b), 0);
|
|
const trendStart =
|
range === 'today'
|
? moment().subtract(6, 'day').startOf('day').format('YYYY-MM-DD HH:mm:ss')
|
: dates.startTime;
|
const trendQb = this.orderRepo
|
.createQueryBuilder('o')
|
.select(range === 'year' ? 'MONTH(o.finishTime)' : 'DATE(o.finishTime)', 'd')
|
.addSelect('COUNT(DISTINCT o.campusUserId)', 'people')
|
.addSelect('COUNT(o.id)', 'times')
|
.where('o.status = :status', { status: '已完成' })
|
.andWhere('o.finishTime BETWEEN :start AND :end', {
|
start: trendStart,
|
end: dates.endTime,
|
})
|
.groupBy(range === 'year' ? 'MONTH(o.finishTime)' : 'DATE(o.finishTime)')
|
.orderBy('d', 'ASC');
|
this.applyDept(trendQb, 'o');
|
const trendRows = await trendQb.getRawMany();
|
const peopleMap = new Map<string, number>();
|
const timesMap = new Map<string, number>();
|
trendRows.forEach(r => {
|
const key = range === 'year' ? `${Number(r.d)}月` : moment(r.d).format('MM-DD');
|
peopleMap.set(key, n(r.people));
|
timesMap.set(key, n(r.times));
|
});
|
result.participateTrend = axis.map(k => peopleMap.get(k) || 0);
|
result.deliverTrend = axis.map(k => timesMap.get(k) || 0);
|
} catch (e) {
|
unavailable.push('师生参与 / 碳积分无法从用户、订单、流水表汇总');
|
}
|
|
return result;
|
}
|
|
private async buildOperation(today: string, _dates: any, unavailable: string[]) {
|
const start15 = moment().subtract(15, 'minute').format('YYYY-MM-DD HH:mm:ss');
|
const todayStart = moment().startOf('day').format('YYYY-MM-DD HH:mm:ss');
|
const todayEnd = moment().endOf('day').format('YYYY-MM-DD HH:mm:ss');
|
|
const sorterQb = this.sorterRepo.createQueryBuilder('a').select('COUNT(a.id)', 'c');
|
this.applyDept(sorterQb, 'a');
|
const sorterTotal = n((await sorterQb.getRawOne())?.c);
|
|
const dsQb = this.dailySorter.createQueryBuilder('a').where('a.date = :today', { today });
|
this.applyDept(dsQb, 'a');
|
const dsRows = await dsQb.getMany();
|
if (!dsRows.length) {
|
unavailable.push('分拣员日表暂无当日数据，状态按离线统计');
|
}
|
|
let working = 0;
|
let resting = 0;
|
let abnormal = 0;
|
let offlineFromTable = 0;
|
let alerts = 0;
|
let healthAlerts = 0;
|
dsRows.forEach(r => {
|
alerts += n(r.unhandledAlarmCount);
|
healthAlerts += n(r.healthAbnormalCount);
|
if (n(r.healthAbnormalCount) > 0 || n(r.unhandledAlarmCount) > 0) {
|
abnormal += 1;
|
} else if (r.checkInTime && !r.checkOutTime) {
|
working += 1;
|
} else if (r.checkInTime && r.checkOutTime) {
|
resting += 1;
|
} else {
|
offlineFromTable += 1;
|
}
|
});
|
const sorterOnline = working;
|
const offline = offlineFromTable + Math.max(sorterTotal - dsRows.length, 0);
|
const sorterStatus = [
|
{ name: '工作中', value: working, color: '#22c55e' },
|
{ name: '休息', value: resting, color: '#f59e0b' },
|
{ name: '异常', value: abnormal, color: '#ef4444' },
|
{ name: '离线', value: offline, color: '#94a3b8' },
|
];
|
|
const vehicleQb = this.vehicleRepo.createQueryBuilder('a').select('COUNT(a.id)', 'c');
|
this.applyDept(vehicleQb, 'a');
|
const vehicleTotal = n((await vehicleQb.getRawOne())?.c);
|
|
const vGpsQb = this.vehicleGpsRepo
|
.createQueryBuilder('a')
|
.select('COUNT(DISTINCT a.vehicleNo)', 'c')
|
.where('a.gpsTime >= :start15', { start15 });
|
this.applyDept(vGpsQb, 'a');
|
const vehicleOnline = n((await vGpsQb.getRawOne())?.c);
|
|
const siteQb = this.siteRepo
|
.createQueryBuilder('a')
|
.select('COUNT(a.id)', 'c')
|
.where("a.siteType = '收集点'");
|
this.applyDept(siteQb, 'a');
|
const siteTotal = n((await siteQb.getRawOne())?.c);
|
const siteOnlineQb = siteQb.clone().andWhere('a.status = :st', { st: 1 });
|
const siteOnline = n((await siteOnlineQb.getRawOne())?.c);
|
|
const orderQb = this.orderRepo
|
.createQueryBuilder('o')
|
.select('COUNT(o.id)', 'c')
|
.where('o.createTime BETWEEN :start AND :end', { start: todayStart, end: todayEnd });
|
this.applyDept(orderQb, 'o');
|
const todayOrders = n((await orderQb.getRawOne())?.c);
|
|
const apptQb = this.appointmentRepo
|
.createQueryBuilder('a')
|
.select('a.status', 'status')
|
.addSelect('COUNT(a.id)', 'c')
|
.where('a.appointmentDate = :today', { today })
|
.groupBy('a.status');
|
this.applyDept(apptQb, 'a');
|
const apptRows = await apptQb.getRawMany();
|
const apptMap = new Map(apptRows.map(r => [r.status, n(r.c)]));
|
const todayAppointments = apptRows.reduce((s, r) => s + n(r.c), 0);
|
const pendingAppointments =
|
n(apptMap.get('新增')) +
|
n(apptMap.get('待审核')) +
|
n(apptMap.get('待接单')) +
|
n(apptMap.get('已确认'));
|
|
const appointmentFlow = [
|
{ name: '预约', value: n(apptMap.get('新增')) + n(apptMap.get('待审核')) },
|
{ name: '接单', value: n(apptMap.get('已确认')) + n(apptMap.get('已接单')) },
|
{ name: '完成', value: n(apptMap.get('已完成')) },
|
];
|
|
const typeQb = this.orderRepo
|
.createQueryBuilder('o')
|
.select('DATE(o.createTime)', 'd')
|
.addSelect('o.orderType', 'orderType')
|
.addSelect('COUNT(o.id)', 'c')
|
.where('o.createTime BETWEEN :start AND :end', {
|
start: moment().subtract(6, 'day').startOf('day').format('YYYY-MM-DD HH:mm:ss'),
|
end: todayEnd,
|
})
|
.groupBy('DATE(o.createTime)')
|
.addGroupBy('o.orderType');
|
this.applyDept(typeQb, 'o');
|
const typeRows = await typeQb.getRawMany();
|
const days = Array.from({ length: 7 }, (_, i) => moment().subtract(6 - i, 'day').format('YYYY-MM-DD'));
|
const typeMap: Omit<OrdersByType, 'axis'> = {
|
'现场回收': days.map(() => 0),
|
'预约上门': days.map(() => 0),
|
'大屏投递': days.map(() => 0),
|
'活动回收': days.map(() => 0),
|
};
|
const typeAlias: Record<string, keyof Omit<OrdersByType, 'axis'>> = {
|
'现场': '现场回收',
|
'预约': '预约上门',
|
'大屏': '大屏投递',
|
'活动': '活动回收',
|
};
|
typeRows.forEach(r => {
|
const key = typeAlias[r.orderType];
|
const idx = days.indexOf(moment(r.d).format('YYYY-MM-DD'));
|
if (idx >= 0 && key) typeMap[key][idx] = n(r.c);
|
});
|
|
const vQb = this.dailyVehicle
|
.createQueryBuilder('a')
|
.select('a.vehicleNo', 'vehicleNo')
|
.addSelect('a.totalKm', 'km')
|
.addSelect('a.minutesAbnormalIdle', 'idle')
|
.addSelect('a.overspeedCount', 'overspeed')
|
.where('a.date = :today', { today })
|
.orderBy('a.totalKm', 'DESC')
|
.limit(8);
|
this.applyDept(vQb, 'a');
|
const vRows = await vQb.getRawMany();
|
const onlineSet = new Set(
|
(
|
await this.vehicleGpsRepo
|
.createQueryBuilder('a')
|
.select('DISTINCT a.vehicleNo', 'vehicleNo')
|
.where('a.gpsTime >= :start15', { start15 })
|
.getRawMany()
|
).map(r => r.vehicleNo)
|
);
|
const vehicles = vRows.map(r => ({
|
no: r.vehicleNo,
|
km: round(n(r.km), 1),
|
idle: n(r.idle),
|
overspeed: n(r.overspeed),
|
status: onlineSet.has(r.vehicleNo) ? '在线' : '离线',
|
}));
|
|
const sQb = this.dailySite
|
.createQueryBuilder('a')
|
.select('a.siteName', 'name')
|
.addSelect('SUM(a.weightTotal)', 'weight')
|
.where('a.date = :today', { today })
|
.groupBy('a.siteName')
|
.orderBy('weight', 'DESC')
|
.limit(8);
|
this.applyDept(sQb, 'a');
|
const sRows = await sQb.getRawMany();
|
const sites = sRows.map(r => ({
|
name: r.name,
|
weight: round(n(r.weight), 1),
|
online: true,
|
}));
|
|
return {
|
sorterOnline,
|
sorterTotal,
|
vehicleOnline,
|
vehicleTotal,
|
siteOnline,
|
siteTotal,
|
todayOrders,
|
todayAppointments,
|
pendingAppointments,
|
alerts,
|
healthAlerts,
|
ordersByType: {
|
axis: days.map(d => d.slice(5)),
|
...typeMap,
|
} as OrdersByType,
|
appointmentFlow,
|
sorterStatus,
|
vehicles,
|
sites,
|
};
|
}
|
}
|
