add pdfjs and traderecord

wangzhibo

2026-07-28 920cec41cedfde27f89d7c19b78e03147b926ab5

add pdfjs and traderecord

 src/modules/basicdata/entity/iot.ts

@@ -6,7 +6,7 @@
 */
@Entity('t_basicdata_iot')
export class BasicdataIotEntity extends BaseEntity {
  @Index({ unique: true })
  @Index({ unique: false })
  @Column({ comment: '终端编码' })
  iotCode: string;


 src/modules/operation/controller/admin/traderecord.ts

New file
@@ -0,0 +1,55 @@
import { CoolController, BaseController } from '@cool-midway/core';
import { Body, Inject, Post } from '@midwayjs/core';
import { OperationTradeRecordEntity } from '../../entity/traderecord';
import { OperationTradeRecordService } from '../../service/traderecord';
import { BaseSysDepartmentEntity } from '../../../base/entity/sys/department';
import { WithDeptFilterWhere } from '../../../base/middleware/with-dept-filter-where'

/**
 * 交易记录
 */
@CoolController({
  api: ['add', 'delete', 'update', 'info', 'list', 'page'],
  entity: OperationTradeRecordEntity,
  service: OperationTradeRecordService,
  pageQueryOp: {
    keyWordLikeFields: ['a.goodsName', 'a.opponent'],
    fieldEq: [
      'a.transactionType',
      'a.incomeType',
      'a.payMethod'
    ],
    select: ['a.*', 'b.name AS departmentName'],
    join: [
      {
        entity: BaseSysDepartmentEntity,
        alias: 'b',
        condition: 'a.departmentId = b.id',
        type: 'leftJoin'
      }
    ],
    where: async (ctx) => {
      const { startTime, endTime } = ctx.request.body;
      const conditions: any[][] = [];

      if (startTime && endTime) {
        conditions.push(['a.transactionTime BETWEEN :startTime AND :endTime', { startTime, endTime }]);
      }
      
      conditions.push(...(await WithDeptFilterWhere(ctx, { alias: 'a', field: 'departmentId' })));
      return conditions;
    },
    addOrderBy: {
      transactionTime: 'desc'
    }
  }
})
export class AdminOperationTradeRecordController extends BaseController {
  @Inject()
  tradeRecordService: OperationTradeRecordService;

  @Post('/import', { summary: '导入账单' })
  async importData(@Body('list') list: any[]) {
    return this.ok(await this.tradeRecordService.importData(list));
  }
}

 src/modules/operation/entity/traderecord.ts

New file
@@ -0,0 +1,55 @@
import { BaseEntity } from '../../base/entity/base';
import { Column, Entity, Index } from 'typeorm';

/**
 * 交易记录
 */
@Entity('t_operation_trade_record')
export class OperationTradeRecordEntity extends BaseEntity {
  @Index()
  @Column({ comment: '交易时间', nullable: true })
  transactionTime: Date;

  @Column({ comment: '交易类型', nullable: true })
  transactionType: string;

  @Index()
  @Column({ comment: '交易对方', nullable: true })
  opponent: string;

  @Index()
  @Column({ comment: '商品名称', nullable: true })
  goodsName: string;

  @Column({ comment: '收支类型', dict: ['收入', '支出', '其他'], default: '收入' })
  incomeType: string;

  @Column({ comment: '金额', type: 'decimal', precision: 12, scale: 2, default: 0 })
  amount: number;

  @Column({ comment: '支付方式', nullable: true })
  payMethod: string;

  @Column({ comment: '当前状态', nullable: true })
  status: string;

  @Index()
  @Column({ comment: '交易单号', length: 100, nullable: true })
  transactionNo: string;

  @Index()
  @Column({ comment: '商户单号', length: 100, nullable: true })
  merchantNo: string;

  @Column({ comment: '备注', type: 'text', nullable: true })
  remark: string;

  @Column({ comment: '所属部门' })
  departmentId: number;

  departmentName: string;

  @Column({ comment: '原始文件路径', length: 500 })
  filepath: string;
  
}

 src/modules/operation/service/traderecord.ts

New file
@@ -0,0 +1,29 @@
import { Provide } from '@midwayjs/core';
import { BaseService, CoolCommException } from '@cool-midway/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository } from 'typeorm';
import { OperationTradeRecordEntity } from '../entity/traderecord';

/**
 * 交易记录服务
 */
@Provide()
export class OperationTradeRecordService extends BaseService {
  @InjectEntityModel(OperationTradeRecordEntity)
  tradeRecordEntity: Repository<OperationTradeRecordEntity>;


  /**
   * 导入Excel数据
   * @param fileInfo 文件信息
   * @param dataList 解析后的数组
   */
  async importData(list: any[]) {
    if (!list || list.length === 0) {
      throw new Error('导入数据不能为空');
    }
    // 使用 TypeORM 的 save 方法批量插入
    await this.tradeRecordEntity.save(list);

  }
}

 src/modules/plugin/hooks/upload/index.ts

@@ -156,7 +156,7 @@
  async upload(ctx: any) {
    const { domain } = this.pluginInfo.config;
    try {
      const { key } = ctx.fields;
      const key = ctx.fields?.key;
      const basePath = pUploadPath();
      const dateDir = moment().format('YYYYMMDD');


 src/modules/push/controller/admin/cameragps.ts

@@ -15,7 +15,7 @@
    fieldEq: ['a.status'],
    where: async (ctx) => {
      const { createDate, gpsTime } = ctx.request.body;
      const condition = [];
      const condition:any[][] = [];
      if (createDate && createDate.length === 2) {
        condition.push(['a.createDate BETWEEN :createStartDate AND :createEndDate', { createStartDate: createDate[0], createEndDate: createDate[1] }]);
      }

 src/modules/push/controller/app/open.ts

@@ -27,8 +27,8 @@
    @Body() data: WeightUploadDto,
  ) {

    const CLIENT_ID = String(await this.baseSysParamService.dataByKey('YAOHUA.WEIGHT.CLIENTID')); //'10001';
    const SECRET = String(await this.baseSysParamService.dataByKey('YAOHUA.WEIGHT.SECRET')); //'9001ac9676b8';
    const CLIENT_ID = String(await this.baseSysParamService.dataByKey('yaohua.weight.clientid')); //'10001';
    const SECRET = String(await this.baseSysParamService.dataByKey('yaohua.weight.secret')); //'9001ac9676b8';


    const clientId = headers['clientid'];

 src/modules/report/controller/admin/dailyvehicle.ts

New file
@@ -0,0 +1,34 @@
import { CoolController, BaseController } from '@cool-midway/core';
import { ReportDailyVehicleEntity } from '../../entity/dailyvehicle';
import { ReportDailyVehicleService } from '../../service/dailyvehicle';
import { BaseSysDepartmentEntity } from '../../../base/entity/sys/department'
import { WithDeptFilterWhere } from '../../../base/middleware/with-dept-filter-where'

/**
 * 每日站点汇总控制器
 */
@CoolController({
  api: ['add', 'delete', 'update', 'info', 'list', 'page'],
  entity: ReportDailyVehicleEntity,
  service: ReportDailyVehicleService,
  pageQueryOp: {
    keyWordLikeFields: ['a.vehicleNo'],
    fieldEq: ['a.date', 'a.vehicleNo'],
    select: ['a.*', 'b.name AS departmentName'],
        join: [
          {
            entity: BaseSysDepartmentEntity,
            alias: 'b',
            condition: 'a.departmentId = b.id',
            type: 'leftJoin',
          },
        ],

    where: async (ctx) => {
      const conditions: any[][] = [];
      conditions.push(...(await WithDeptFilterWhere(ctx, { alias: 'a', field: 'departmentId' })));
      return conditions;
    }
  }
})
export class AdminReportDailyVehicleController extends BaseController { }

 src/modules/report/entity/dailyvehicle.ts

New file
@@ -0,0 +1,78 @@
import { BaseEntity } from '../../base/entity/base';
import { Column, Entity, Index, Unique } from 'typeorm';

/**
 * 每日站点汇总
 */
@Entity('t_report_daily_vehicle')
@Unique('uk_date_vehicle', ['date', 'vehicleNo'])
export class ReportDailyVehicleEntity extends BaseEntity {

  @Index()
  @Column({ comment: '日期', type: 'date' })
  date: string;

  @Index()
  @Column({ comment: '车牌号', length: 32 })
  vehicleNo: string;

  @Index()
  @Column({ comment: '所属部门/学校/后勤组ID', type: 'int', nullable: true })
  departmentId: number;

  departmentName: string;

  // ==================== 2. 基础运行指标 ====================

  @Column({
    comment: '当日行驶里程(km)',
    type: 'decimal',
    precision: 10,
    scale: 2,
    default: 0.0,
  })
  totalKm: number;

  @Column({ comment: '未启动时间(分钟)', type: 'int', default: 0 })
  minutesOffline: number;

  @Column({ comment: '纯行驶时长(分钟)', type: 'int', default: 0 })
  minutesMoving: number;

  @Column({ comment: '停车时长(分钟)', type: 'int', default: 0 })
  minutesParked: number;

  @Column({ comment: '异常怠速时长(分钟)', type: 'int', default: 0 })
  minutesAbnormalIdle: number;

  @Column({
    comment: '平均行驶车速(km/h)',
    type: 'decimal',
    precision: 5,
    scale: 1,
    default: 0.0,
  })
  avgSpeed: number;

  // ==================== 3. 安全与驾驶行为指标 ====================

  @Column({ comment: '超速告警次数', type: 'int', default: 0 })
  overspeedCount: number;

  @Column({ comment: '超速累计时长(秒)', type: 'int', default: 0 })
  overspeedDurationSec: number;

  @Column({ comment: '急刹车次数', type: 'int', default: 0 })
  harshBrakeCount: number;

  @Column({ comment: '急加速次数', type: 'int', default: 0 })
  harshAccelCount: number;

  @Column({ comment: '急转弯次数', type: 'int', default: 0 })
  harshTurnCount: number;

  @Column({ comment: '非工作时段异动告警次数', type: 'int', default: 0 })
  offHoursMoveCount: number;


}

 src/modules/report/service/ExecutorSiteDeptDaily.ts

New file
@@ -0,0 +1,105 @@
import { Provide, Inject } from '@midwayjs/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository, In } from 'typeorm';
import { ReportJoblogEntity } from '../entity/joblog';
import { TaskServiceSite } from './taskservicesite'
import { TaskServiceDepartment } from './taskservicedepartment'



@Provide()
export class ExecutorSiteDeptDaily {

    @InjectEntityModel(ReportJoblogEntity)
    reportJoblogEntity: Repository<ReportJoblogEntity>;

    @Inject()
    taskServiceSite: TaskServiceSite;

    @Inject()
    taskServiceDepartment: TaskServiceDepartment;

    async execute() {
        // 捞出所有待处理任务
        const pendingSiteJobs = await this.reportJoblogEntity.find({
            where: {
                status: In(['INIT', 'FAILED']),
                jobName: In(['SITE_DAILY', 'DEPT_DAILY']),
            },
            order: {
                dataDate: 'ASC', // 先算历史，后算今天
                // 确保在同一天内，SITE_DAILY 先跑，DEPT_DAILY 后跑
                // 在 JS 数组中我们可以进一步控制排序，或者由业务逻辑严格控制依赖
            },
        });

        for (const job of pendingSiteJobs) {
            // 严格依赖检查：如果是部门日表任务，必须确保同天的站点日表任务已经是 SUCCESS
            if (job.jobName === 'DEPT_DAILY') {
                const siteJob = await this.reportJoblogEntity.findOneBy({
                    dataDate: job.dataDate,
                    jobName: 'SITE_DAILY',
                });
                if (!siteJob || siteJob.status !== 'SUCCESS') {
                    // 源头明细还没算好/或还在运行，部门表任务必须等待，跳过本次执行
                    continue;
                }
            }

            // 锁定任务状态为 RUNNING（轻量级乐观锁锁机制）
            const lockResult = await this.reportJoblogEntity.update(
                { id: job.id, status: job.status }, // 带有原始状态检查，防止被别的线程抢跑
                { status: 'RUNNING', startTime: new Date() }
            );

            if (lockResult.affected === 0) {
                continue; // 没抢到锁，说明别的定时器在跑它，跳过
            }

            // 真正开始跑数据
            try {
                let recordsProcessed = 0;

                if (job.jobName === 'SITE_DAILY') {
                    // 调用你写的 站点日表 提取逻辑
                    recordsProcessed = await this.taskServiceSite.aggregateDailyData(job.dataDate) || 0;
                } else if (job.jobName === 'DEPT_DAILY') {
                    // 调用你写的 部门树轰炸 提取逻辑
                    recordsProcessed = await this.taskServiceDepartment.aggregateDailyData(job.dataDate) || 0;
                }

                // 成功：更新状态
                await this.reportJoblogEntity.update(job.id, {
                    status: 'SUCCESS',
                    recordsProcessed: recordsProcessed,
                    endTime: new Date(),
                    errorMessage: '',
                });

            } catch (error) {
                // 失败：记录堆栈，等待下一小时重试

                var errorMessage = '';
                if (error instanceof Error) {
                    errorMessage = error.stack ?? error.message;
                }
                if (typeof error === 'string') {
                    errorMessage = error;
                }
                try {
                    errorMessage = JSON.stringify(error);
                } catch {
                    errorMessage = String(error);
                }

                await this.reportJoblogEntity.update(job.id, {
                    status: 'FAILED',
                    endTime: new Date(),
                    errorMessage: errorMessage,
                    failedCount: () => 'failedCount + 1',
                });
            }
        }
    }

}

 src/modules/report/service/ExecutorVehicleDaily.ts

New file
@@ -0,0 +1,84 @@
import { Provide, Inject } from '@midwayjs/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository, In } from 'typeorm';
import { ReportJoblogEntity } from '../entity/joblog';
import { TaskServiceVehicle } from './taskservicevehicle'



@Provide()
export class ExecutorVehicleDaily {

    @InjectEntityModel(ReportJoblogEntity)
    reportJoblogEntity: Repository<ReportJoblogEntity>;

    @Inject()
    taskServiceVehicle: TaskServiceVehicle;

    async execute() {
        // 捞出所有待处理任务
        const pendingSiteJobs = await this.reportJoblogEntity.find({
            where: {
                status: In(['INIT', 'FAILED']),
                jobName: In(['VEHICLE_DAILY']),
            },
            order: {
                dataDate: 'ASC',
            },
        });

        for (const job of pendingSiteJobs) {

            // 锁定任务状态为 RUNNING（轻量级乐观锁锁机制）
            const lockResult = await this.reportJoblogEntity.update(
                { id: job.id, status: job.status }, // 带有原始状态检查，防止被别的线程抢跑
                { status: 'RUNNING', startTime: new Date() }
            );

            if (lockResult.affected === 0) {
                continue; // 没抢到锁，说明别的定时器在跑它，跳过
            }

            // 真正开始跑数据
            try {
                let recordsProcessed = 0;


                recordsProcessed = await this.taskServiceVehicle.aggregateDailyData(job.dataDate) || 0;


                // 成功：更新状态
                await this.reportJoblogEntity.update(job.id, {
                    status: 'SUCCESS',
                    recordsProcessed: recordsProcessed,
                    endTime: new Date(),
                    errorMessage: '',
                });

            } catch (error) {
                // 失败：记录堆栈，等待下一小时重试

                var errorMessage = '';
                if (error instanceof Error) {
                    errorMessage = error.stack ?? error.message;
                }
                if (typeof error === 'string') {
                    errorMessage = error;
                }
                try {
                    errorMessage = JSON.stringify(error);
                } catch {
                    errorMessage = String(error);
                }

                await this.reportJoblogEntity.update(job.id, {
                    status: 'FAILED',
                    endTime: new Date(),
                    errorMessage: errorMessage,
                    failedCount: () => 'failedCount + 1',
                });
            }
        }
    }

}

 src/modules/report/service/dailyvehicle.ts

New file
@@ -0,0 +1,14 @@
import { Provide } from '@midwayjs/core';
import { BaseService } from '@cool-midway/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository } from 'typeorm';
import { ReportDailyVehicleEntity } from '../entity/dailyvehicle';

/**
 * 每日站点汇总服务
 */
@Provide()
export class ReportDailyVehicleService extends BaseService {
  @InjectEntityModel(ReportDailyVehicleEntity)
  reportDailyVehicleEntity: Repository<ReportDailyVehicleEntity>;
}

 src/modules/report/service/taskservice.ts

@@ -3,8 +3,8 @@
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository, In } from 'typeorm';
import { ReportJoblogEntity } from '../entity/joblog';
import { TaskSiteService } from './tasksiteservice'
import { TaskDepartmentService } from './taskdepartmentservice'
import { ExecutorSiteDeptDaily } from './ExecutorSiteDeptDaily'
import { ExecutorVehicleDaily } from './ExecutorVehicleDaily'

/**
 *  定时任务-汇总站点/各个单位的各类垃圾汇总数据
@@ -17,12 +17,13 @@
  @InjectEntityModel(ReportJoblogEntity)
  reportJoblogEntity: Repository<ReportJoblogEntity>;

  // 注入你具体的业务 Service，用于跑真正的汇总 SQL
  @Inject()
  taskSiteService: TaskSiteService;
  executorSiteDeptDaily: ExecutorSiteDeptDaily;

  @Inject()
  departmentCubeService: TaskDepartmentService;
  executorVehicleDaily: ExecutorVehicleDaily;



  /**
   * 外部定时任务（Crontab）每小时调用的总入口
@@ -32,10 +33,17 @@
    // 1. 自动看门狗：确保昨天和今天的任务实例已经在表里初始化了
    await this.ensureJobInstancesCreated();

    // 2. 执行引擎：捞出需要执行或重试的任务
    await this.executePendingJobs();
    // 2. 两类任务同时执行
    // 后续增加新的 task service 比如 更新 萤石云 token， 统计分拣员行为等等。

    return '任务执行成功:ReportTaskService';
    await Promise.all([
      this.executorSiteDeptDaily.execute(),
      this.executorVehicleDaily.execute()

    ]);


    return '任务执行:ReportTaskService';
  }

  /**
@@ -58,8 +66,12 @@
    const targets = [
      { date: yesterdayStr, type: 'SITE_DAILY',isToday: false},
      { date: yesterdayStr, type: 'DEPT_DAILY',isToday: false},
      { date: yesterdayStr, type: 'VEHICLE_DAILY', isToday: false },   // 新增

      { date: todayStr, type: 'SITE_DAILY' ,isToday: true},
      { date: todayStr, type: 'DEPT_DAILY' ,isToday: true},
      { date: todayStr, type: 'VEHICLE_DAILY', isToday: true },         // 新增

    ];

    for (const target of targets) {
@@ -87,88 +99,5 @@
    }
  }

  /**
   * 捞出 INIT 和 FAILED 的任务，并串行严格按顺序执行
   */
  private async executePendingJobs() {
    // 捞出所有待处理任务
    const pendingJobs = await this.reportJoblogEntity.find({
      where: {
        status: In(['INIT', 'FAILED']),
      },
      order: {
        dataDate: 'ASC', // 先算历史，后算今天
        // 确保在同一天内，SITE_DAILY 先跑，DEPT_DAILY 后跑
        // 在 JS 数组中我们可以进一步控制排序，或者由业务逻辑严格控制依赖
      },
    });

    for (const job of pendingJobs) {
      // 严格依赖检查：如果是部门日表任务，必须确保同天的站点日表任务已经是 SUCCESS
      if (job.jobName === 'DEPT_DAILY') {
        const siteJob = await this.reportJoblogEntity.findOneBy({
          dataDate: job.dataDate,
          jobName: 'SITE_DAILY',
        });
        if (!siteJob || siteJob.status !== 'SUCCESS') {
          // 源头明细还没算好/或还在运行，部门表任务必须等待，跳过本次执行
          continue;
        }
      }

      // 锁定任务状态为 RUNNING（轻量级乐观锁锁机制）
      const lockResult = await this.reportJoblogEntity.update(
        { id: job.id, status: job.status }, // 带有原始状态检查，防止被别的线程抢跑
        { status: 'RUNNING', startTime: new Date() }
      );

      if (lockResult.affected === 0) {
        continue; // 没抢到锁，说明别的定时器在跑它，跳过
      }

      // 真正开始跑数据
      try {
        let recordsProcessed = 0 ;

        if (job.jobName === 'SITE_DAILY') {
          // 调用你写的 站点日表 提取逻辑
          recordsProcessed = await this.taskSiteService.aggregateDailyData(job.dataDate) || 0;
        } else if (job.jobName === 'DEPT_DAILY') {
          // 调用你写的 部门树轰炸 提取逻辑
          recordsProcessed = await this.departmentCubeService.aggregateDailyData(job.dataDate) || 0;
        }

        // 成功：更新状态
        await this.reportJoblogEntity.update(job.id, {
            status: 'SUCCESS',
            recordsProcessed:recordsProcessed,
            endTime: new Date(),
            errorMessage: '',
          });

      } catch (error) {
        // 失败：记录堆栈，等待下一小时重试

        var errorMessage = '';
        if (error instanceof Error) {
          errorMessage = error.stack ?? error.message;
        }
        if (typeof error === 'string') {
          errorMessage = error;
        }
        try {
          errorMessage = JSON.stringify(error);
        } catch {
          errorMessage = String(error);
        }

        await this.reportJoblogEntity.update(job.id, {
          status: 'FAILED',
          endTime: new Date(),
          errorMessage: errorMessage,
          failedCount: () => 'failedCount + 1',
        });
      }
    }
  }
}

 src/modules/report/service/taskservicedepartment.ts

File was renamed from src/modules/report/service/taskdepartmentservice.ts
@@ -12,7 +12,7 @@
 *  定时任务-子任务 汇总部门的各类垃圾数据
 */
@Provide()
export class TaskDepartmentService extends BaseService {
export class TaskServiceDepartment extends BaseService {
  @InjectEntityModel(ReportDailysiteEntity)
  reportDailysiteEntity: Repository<ReportDailysiteEntity>;


 src/modules/report/service/taskservicesite.ts

File was renamed from src/modules/report/service/tasksiteservice.ts
@@ -7,7 +7,7 @@
import { BasicdataSiteEntity } from '../../basicdata/entity/site'

@Provide()
export class TaskSiteService extends BaseService {
export class TaskServiceSite extends BaseService {
    @InjectEntityModel(ReportDailysiteEntity)
    reportDailysiteEntity: Repository<ReportDailysiteEntity>;


 src/modules/report/service/taskservicevehicle.ts

New file
@@ -0,0 +1,313 @@
import { Provide, Inject } from '@midwayjs/core';
import { BaseService } from '@cool-midway/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository } from 'typeorm';
import { ReportDailyVehicleEntity } from '../entity/dailyvehicle';
import { PushVehicleGpsEntity } from '../../push/entity/vehiclegps';
import { BaseSysParamService } from '../../base/service/sys/param';



@Provide()
export class TaskServiceVehicle extends BaseService {
    @InjectEntityModel(ReportDailyVehicleEntity)
    reportDailyVehicleEntity: Repository<ReportDailyVehicleEntity>;

    @InjectEntityModel(PushVehicleGpsEntity)
    pushVehicleGpsEntity: Repository<PushVehicleGpsEntity>;

    @Inject()
    baseSysParamService: BaseSysParamService;

    async aggregateDailyData(targetDate: string) {
        const startTime = `${targetDate} 00:00:00`;
        const endTime = `${targetDate} 23:59:59`;

        let HOUR_STARTWORK = parseInt(await this.baseSysParamService.dataByKey('vehicle.hour.startwork')); //'6';
        let HOUR_OFFWORK = parseInt(await this.baseSysParamService.dataByKey('vehicle.hour.offwork')); //'22';
        let THRESHOLD_MOVING = parseInt(await this.baseSysParamService.dataByKey('vehicle.speed.threshold.moving')); //'2.0 km/h';
        let THRESHOLD_SPEEDING = parseInt(await this.baseSysParamService.dataByKey('vehicle.speed.threshold.speeding')); //'30 km/h';

        HOUR_STARTWORK = Number.isNaN(HOUR_STARTWORK) ? 6 : HOUR_STARTWORK;
        HOUR_OFFWORK = Number.isNaN(HOUR_OFFWORK) ? 22 : HOUR_OFFWORK;
        THRESHOLD_MOVING = Number.isNaN(THRESHOLD_MOVING) ? 2 : THRESHOLD_MOVING;
        THRESHOLD_SPEEDING = Number.isNaN(THRESHOLD_SPEEDING) ? 30 : THRESHOLD_SPEEDING;



        const rawGpsList = await this.pushVehicleGpsEntity.createQueryBuilder('gps')
            .select([
                'gps.vehicleNo AS vehicleNo',             // 拿最新的自增ID作为每日表的 siteId
                'gps.departmentId AS departmentId', // 永远拿当前后台最新的站点名称，防止设备传旧名称
                'gps.gpsTime AS gpsTime',
                'gps.longitude AS longitude',
                'gps.latitude AS latitude',
                'gps.speed AS speed',
                'gps.direction AS direction',
                'gps.elevation AS elevation'
            ])
            .where('gps.gpsTime BETWEEN :start AND :end', { start: startTime, end: endTime })
            .orderBy({
                'gps.vehicleNo': 'ASC',
                'gps.gpsTime': 'ASC',
            })
            .getRawMany();

        if (!rawGpsList || rawGpsList.length === 0) return;

        // 2. 按车牌号 vehicleNo 分组
        const groupedMap = new Map<string, PushVehicleGpsEntity[]>();
        for (const point of rawGpsList) {
            if (!groupedMap.has(point.vehicleNo)) {
                groupedMap.set(point.vehicleNo, []);
            }
            groupedMap.get(point.vehicleNo)?.push(point);
        }

        // 3. 计算截至当前（或全天）经历的总分钟数，用于倒推计算 offlineMinutes
        const startMs = new Date(`${targetDate}T00:00:00`).getTime();
        const endMs = new Date(`${targetDate}T23:59:59`).getTime();
        const nowMs = new Date().getTime();

        // 如果 targetDate 是今天，计算到当前时间；如果是过去/未来，上限算到 23:59:59
        const effectiveEndMs = nowMs < endMs ? nowMs : endMs;
        const totalElapsedMinutes = Math.max(1, Math.floor((effectiveEndMs - startMs) / (1000 * 60)));

        const summariesToUpsert: Partial<ReportDailyVehicleEntity>[] = [];

        // 4. 逐车按严格顺序计算各项指标
        for (const [vehicleNo, points] of groupedMap.entries()) {
            const summary = this.calculateVehicleMetrics(
                targetDate,
                vehicleNo,
                points,
                totalElapsedMinutes,
                HOUR_STARTWORK,
                HOUR_OFFWORK,
                THRESHOLD_MOVING,
                THRESHOLD_SPEEDING
            );
            summariesToUpsert.push(summary);
        }

        // 5. 执行 UPSERT 覆盖更新
        await this.upsertDailyReports(summariesToUpsert);


        return summariesToUpsert.length;
    }


    /**
       * 核心单车计算引擎（严格按照指标依赖逻辑顺序执行）
       */
    private calculateVehicleMetrics(
        targetDate: string,
        vehicleNo: string,
        points: PushVehicleGpsEntity[],
        totalElapsedMinutes: number,
        HOUR_STARTWORK: number,
        HOUR_OFFWORK: number,
        HHRESHOLD_MOVING: number,
        THRESHOLD_SPEEDING: number
    ): Partial<ReportDailyVehicleEntity> {
        const departmentId = points[0]?.departmentId ?? null;

        // 基础统计量
        let totalMeter = 0; // 累计行驶距离(米)
        let secondsMoving = 0; // 纯行驶时长(秒)
        let secondsParked = 0; // 静止停靠时长(秒)
        let secondsAbnormalIdle = 0; // 异常怠速时长(秒)

        // 安全驾驶统计量
        let overspeedCount = 0; // 超速次数
        let overspeedDurationSec = 0; // 超速总秒数
        let harshBrakeCount = 0; // 急刹车次数
        let harshAccelCount = 0; // 急加速次数
        let harshTurnCount = 0; // 急转弯次数
        let offHoursMoveCount = 0; // 非工作时段异动次数

        let isOverspeeding = false; // 超速状态标记

        for (let i = 0; i < points.length; i++) {
            const curr = points[i];
            const speed = Number(curr.speed) || 0;
            const currDate = new Date(curr.gpsTime);

            // ====================================================
            // 顺序 1：单点状态判断（只依赖当前点的数据）
            // ====================================================

            // A. 判断非工作时段异动 (23:00 - 05:00 且速度 > 2 km/h)
            const hour = currDate.getHours();
            if ((hour >= HOUR_OFFWORK || hour < HOUR_STARTWORK) && speed > HHRESHOLD_MOVING) {
                offHoursMoveCount++;
            }

            // B. 判断超速告警 (限速 30 km/h)
            if (speed > THRESHOLD_SPEEDING) {
                overspeedDurationSec += 1; // 按 1 秒/采样点粗略累加
                if (!isOverspeeding) {
                    overspeedCount++; // 刚跨过 20 时记录一次新告警
                    isOverspeeding = true;
                }
            } else {
                isOverspeeding = false;
            }

            // ====================================================
            // 顺序 2：相邻点微分与物理量计算（必须依赖前一点 i-1）
            // ====================================================
            if (i > 0) {
                const prev = points[i - 1];
                const prevDate = new Date(prev.gpsTime);

                // 计算相邻两点时间差（秒）
                const timeDiffSec = Math.floor((currDate.getTime() - prevDate.getTime()) / 1000);

                // 过滤网络断连/关机（时间差 > 120 秒 视为掉线，不参与物理微分计算）
                if (timeDiffSec > 0 && timeDiffSec <= 120) {

                    // ================================================
                    // 顺序 3：基于速度划分为【行驶】或【静止停靠】
                    // ================================================
                    if (speed > HHRESHOLD_MOVING) {
                        // 3.1 累加纯行驶时长
                        secondsMoving += timeDiffSec;

                        // 3.2 累加球面真实位移距离
                        totalMeter += this.getHaversineDistance(
                            Number(prev.latitude),
                            Number(prev.longitude),
                            Number(curr.latitude),
                            Number(curr.longitude),
                        );
                    } else {
                        // 3.3 累加静止停靠时长
                        secondsParked += timeDiffSec;

                        // 3.4 静止停靠超过 15 分钟（900秒）算作异常怠速
                        if (timeDiffSec > 900) {
                            secondsAbnormalIdle += timeDiffSec;
                        }
                    }

                    // ================================================
                    // 顺序 4：安全驾驶行为（加速度与转弯角微分计算）
                    // ================================================

                    // 4.1 加速度计算: a = (v2 - v1) / t (需要转换为 m/s)
                    const v1 = (Number(prev.speed) || 0) / 3.6;
                    const v2 = speed / 3.6;
                    const accel = (v2 - v1) / timeDiffSec;

                    if (accel > 3.0) {
                        harshAccelCount++; // 急加速 a > 3.0 m/s²
                    } else if (accel < -3.5) {
                        harshBrakeCount++; // 急刹车 a < -3.5 m/s²
                    }

                    // 4.2 急转弯计算: 2秒内航向角差 > 60° 且车速 > 15 km/h
                    const prevDir = Number(prev.direction) || 0;
                    const currDir = Number(curr.direction) || 0;
                    let dirDiff = Math.abs(currDir - prevDir);
                    if (dirDiff > 180) dirDiff = 360 - dirDiff; // 跨 0/360 度修正

                    if (timeDiffSec <= 2 && dirDiff > 60 && speed > 15) {
                        harshTurnCount++;
                    }
                }
            }
        }

        // ====================================================
        // 顺序 5：衍生指标倒推计算（必须放在最后）
        // ====================================================

        // 5.1 秒转化为分钟
        const minutesMoving = Math.round(secondsMoving / 60);
        const minutesParked = Math.round(secondsParked / 60);
        const minutesAbnormalIdle = Math.round(secondsAbnormalIdle / 60);
        const totalKm = Number((totalMeter / 1000).toFixed(2));

        // 5.2 倒推【未启动/离线时长】 = 当天已过总时长 - 行驶时长 - 停靠时长
        const minutesOffline = Math.max(
            0,
            totalElapsedMinutes - minutesMoving - minutesParked,
        );

        // 5.3 计算【平均行驶车速】 = 总行驶公里数 / 纯行驶小时数
        const hoursMoving = secondsMoving / 3600;
        const avgSpeed =
            hoursMoving > 0 ? Number((totalKm / hoursMoving).toFixed(1)) : 0.0;

        return {
            date: targetDate,
            vehicleNo,
            departmentId,
            totalKm,
            minutesMoving,
            minutesParked,
            minutesOffline,
            minutesAbnormalIdle,
            avgSpeed,
            overspeedCount,
            overspeedDurationSec,
            harshBrakeCount,
            harshAccelCount,
            harshTurnCount,
            offHoursMoveCount,
        };
    }

    /**
     * 数据库批量覆盖更新 (ON DUPLICATE KEY UPDATE)
     */
    private async upsertDailyReports(finalRecords: Partial<ReportDailyVehicleEntity>[]) {
        if (finalRecords.length === 0) return;

        // 1. 动态抓取所有的键，将主键、联合唯一键彻底从“待更新字段”中排除
        // 这样能确保 ON DUPLICATE KEY UPDATE 后面的赋值语句绝对干净
        const allUpdateFields = Object.keys(finalRecords[0]).filter(
            k => k !== 'date' && k !== 'vehicleNo' && k !== 'id'
        );

        // 2. 依然推荐采用 Chunk 分批，这是应对海量数据/多子字段最安全、执行效率最高的做法
        const chunkSize = 50;
        for (let i = 0; i < finalRecords.length; i += chunkSize) {
            const chunk = finalRecords.slice(i, i + chunkSize);

            await this.reportDailyVehicleEntity.createQueryBuilder()
                .insert()
                .values(chunk)
                // 💡 显式声明：当 ['date', 'siteId'] 发生冲突时，强行把 allUpdateFields 里的字段全部更新一遍
                .orUpdate(allUpdateFields, ['date', 'vehicleNo'])
                .execute();
        }

    }

    /**
     * 纯 JS 原生计算两点之间的球面距离 (单位: 米)
     */
    private getHaversineDistance(
        lat1: number,
        lon1: number,
        lat2: number,
        lon2: number,
    ): number {
        if (lat1 === lat2 && lon1 === lon2) return 0;
        const R = 6371000;
        const rad = Math.PI / 180;
        const dLat = (lat2 - lat1) * rad;
        const dLon = (lon2 - lon1) * rad;
        const a =
            Math.sin(dLat / 2) * Math.sin(dLat / 2) +
            Math.cos(lat1 * rad) *
            Math.cos(lat2 * rad) *
            Math.sin(dLon / 2) *
            Math.sin(dLon / 2);
        const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
        return R * c;
    }
}