chart-admin/app/extend/helper.js

'use strict';
const moment = require('moment');

// 实销用户id
exports.saledRoleId = 90;// '90'
// 远控行为ID
exports.rcBehaviorId = 6016;
exports.rcResult = {
  FAILED: { $in: [ 'false', 'FAILED' ] },
  SUCCEED: { $in: [ 'true', 'SUCCEED' ] },
};
exports.rcDict = {
  车门解锁: { op: 'UNLOCK', behavior_id: 60160001 },
  车门上锁: { op: 'LOCK', behavior_id: 60160001 },
  车辆启动: { op: 'OPEN', behavior_id: 60160002 },
  车辆熄火: { op: 'CLOSE', behavior_id: 60160002 },
  空调开启: { op: 'OPEN', behavior_id: 60160003 },
  空调关闭: { op: 'CLOSE', behavior_id: 60160003 },
  空调温度控制: { behavior_id: 60160004 },
  空调风量档位设置: { behavior_id: 60160005 },
  前除霜模式开启: { op: 'OPEN', behavior_id: 60160006 },
  前除霜模式关闭: { op: 'CLOSE', behavior_id: 60160006 },
  后风窗加热开启: { op: 'OPEN', behavior_id: 60160007 },
  后风窗加热关闭: { op: 'CLOSE', behavior_id: 60160007 },
  空调运行时长设置: { behavior_id: 60160008 },
  一键调温: { behavior_id: 60160009 },
  主驾座椅加热开启: { behavior_id: 60160010, op: 'OPEN', opType: 'HOSTSEAT_HEAT' },
  主驾座椅加热关闭: { behavior_id: 60160010, op: 'CLOSE', opType: 'HOSTSEAT_HEAT' },
  副驾座椅加热开启: { behavior_id: 60160010, op: 'OPEN', opType: 'VICESEAT_HEAT' },
  副驾座椅加热关闭: { behavior_id: 60160010, op: 'CLOSE', opType: 'VICESEAT_HEAT' },
  左后座椅加热开启: { behavior_id: 60160010, op: 'OPEN', opType: 'LEFT_REAR_HEAT' },
  左后座椅加热关闭: { behavior_id: 60160010, op: 'CLOSE', opType: 'LEFT_REAR_HEAT' },
  右后座椅加热开启: { behavior_id: 60160010, op: 'OPEN', opType: 'RIGHT_REAR_HEAT' },
  右后座椅加热关闭: { behavior_id: 60160010, op: 'CLOSE', opType: 'RIGHT_REAR_HEAT' },
  空调预约: { behavior_id: 60160011 },
  方向盘加热开启: { op: 'OPEN', behavior_id: 60160012 },
  方向盘加热关闭: { op: 'CLOSE', behavior_id: 60160012 },
  通风开启: { op: 'OPEN', behavior_id: 60160013 },
  通风关闭: { op: 'CLOSE', behavior_id: 60160013 },
  天窗开启: { op: 'OPEN', behavior_id: 60160014 },
  天窗关闭: { op: 'CLOSE', behavior_id: 60160014 },
  车灯开启: { op: 'OPEN', behavior_id: 60160015 },
  车灯关闭: { op: 'CLOSE', behavior_id: 60160015 },
  远程寻车: { behavior_id: 60160016 },
  设置充电限值: { behavior_id: 60160017 },
  设置放电电量限值: { behavior_id: 60160018 },
  开始充电: { behavior_id: 60160019 },
  结束充电: { behavior_id: 60160020 },
  定时充电: { behavior_id: 60160021 },
  充电完成提醒开启: { op: 'OPEN', behavior_id: 60160022 },
  充电完成提醒关闭: { op: 'CLOSE', behavior_id: 60160022 },
  充电状态查询: { behavior_id: 60160023 },
  远程监控开启: { behavior_id: 60160024 },
  远程监控正常关闭: { closeType: 'NORMAL', behavior_id: 60160025 },
  远程监控异常关闭: { closeType: 'EXCEPTION', behavior_id: 60160025 },
  '控制充电桩（基于蓝牙）TBD': { behavior_id: 60160026 },
  代客泊车: { behavior_id: 60160027 },
  代客模式: { behavior_id: 60160028 },
};

exports.drivingSafetyScoreProject = {
  acceleration_score: 1,
  decelerate_score: 1,
  speeding_score: 1,
  fa_score: 1,
  fd_score: 1,
  turn_score: 1,
  clu_score: 1,
  flc_score: 1,
  io_score: 1,
  hs_turn_score: 1,
  at_night_drive_score: 1,
  un_tie_sb_score: 1,
  leave_sw_score: 1,
  dsm_score: 1,
  fcw_score: 1,
  bsd_score: 1,
  lka_score: 1,
  ldw_score: 1,
};
exports.energyConservationScoreProject = {
  energyConservationScore: 1,
  acceleration_score: 1,
  decelerate_score: 1,
  idling_score: 1,
  drive_anticipation_score: 1,
  oil_score: 1,
  sa_score: 1,
  sd_score: 1,
  steady_score: 1,
};

// 格式化时间
exports.formatTime = time => moment(time).format('YYYY-MM-DD HH:mm:ss');
// 获取昨日0：0：0
exports.yesterday = () => moment().subtract(1, 'days').set('hour', 0)
  .set('minute', 0)
  .set('second', 0)
  .set('millisecond', 0)
  .valueOf();
// 获取今日0：0：0
exports.today = () => moment().set('hour', 0)
  .set('minute', 0)
  .set('second', 0)
  .set('millisecond', 0)
  .valueOf();
// 解析时间字符串YYYY-MM-DD为时间戳
exports.parse = str => moment(str, 'YYYY-MM-DD').valueOf();
// 解析时间字符串为moment对象
exports.momentDate = str => moment(str, 'YYYY-MM-DD');
// 判断是否是今日
exports.isToday = time => moment(new Date()).isSame(moment(time));
// 当前年  今年
exports.nowYear = () => moment().set('month', 0)
  .set('date', 1)
  .set('hour', 0)
  .set('minute', 0)
  .set('second', 0)
  .set('millisecond', 0)
  .valueOf();
// 获取月头
exports.getMonthTop = time => moment(time).set('date', 1)
  .set('hour', 0)
  .set('minute', 0)
  .set('second', 0)
  .set('millisecond', 0)
  .valueOf();
// 上个月
exports.preMonth = time => moment(time).subtract(1, 'months').set('date', 1)
  .set('hour', 0)
  .set('minute', 0)
  .set('second', 0)
  .set('millisecond', 0)
  .valueOf();

// 获取转换时间戳对象project 默认转换字段$create_date
exports.getTimeGenProject = (type, dateStr = 'create_date') => {
  // dateString: { $dateToString: { format: '%Y-%m-%d', date: { $toDate: dateStr } } },
  switch (type) {
    case '0':
      return {
        day: { $dayOfMonth: { $toDate: `$${dateStr}` } },
        month: { $month: { $toDate: `$${dateStr}` } },
        year: { $year: { $toDate: `$${dateStr}` } },
      };
    case '1':
      return {
        month: { $month: { $toDate: `$${dateStr}` } },
        year: { $year: { $toDate: `$${dateStr}` } },
      };
    case '2':
      return {
        year: { $year: { $toDate: `$${dateStr}` } },
      };
    default:
      break;
  }
};

// 获取转换时间戳范围match 默认字段$create_date
exports.getTimeRangMatch = (startTime, endTime, dateStr = 'create_date') => {
  const match = {};
  match[dateStr] = { $gte: startTime, $lt: endTime };
  return match;
};


// 获取活跃car的条件
exports.getCarActiveCond = ({ startTime, endTime }) => {
  return [
    { $lookup: {
      from: 'driving_behavior_info',
      let: { vin: '$vin' },
      pipeline: [{ $match: { $expr: { $and:
                [
                  { $eq: [ '$vin', '$$vin' ] },
                  { $gte: [ '$start_time', this.getMonthTop(startTime) ] },
                  { $lt: [ '$start_time', endTime ] },
                ] } } }], as: 'drive',
    } },
    { $unwind: { path: '$drive', preserveNullAndEmptyArrays: true } },
    { $group: {
      _id: '$vin',
      mileage: { $sum: { $cond: [ '$drive.mileage', { $toDouble: '$drive.mileage' }, 0 ] } },
      pro_code: { $first: '$pro_code' }, city_code: { $first: '$city_code' },
      is_saled_car: { $first: '$is_saled_car' }, sale_date: { $first: '$sale_date' },
      user_id: { $first: '$user_id' },
      series_code: { $first: '$series_code' }, model_code: { $first: '$model_code' },
    } },
  ];
};


// 获取区域分组语句  (之前的前置语句或者结果必须存在 元素属性:city_name,area_name,provice_name)
exports.getLocationMongo = () => {
  return [
    { $group: { _id: { area_name: '$area_name', provice_name: '$provice_name', city_name: '$city_name' },
      count: { $sum: 1 } } },
    { $group: { _id: { area_name: '$_id.area_name', provice_name: '$_id.provice_name' },
      cities: { $push: { city_name: '$_id.city_name', count: '$count' } },
      count: { $sum: '$count' } } },
    { $group: { _id: { area_name: '$_id.area_name' },
      provinces: { $push: { provice_name: '$_id.provice_name', cities: '$cities', count: '$count' } },
      count: { $sum: '$count' } } },
    { $group: { _id: null,
      areas: { $push: { area_name: '$_id.area_name', provinces: '$provinces', count: '$count' } },
      count: { $sum: '$count' } } },
  ];
};



// 获取年龄和性别分组语句  (之前的前置语句或者结果必须存在 元素属性: id_card，gender)
exports.getAggAndSexMongo = () => {
  return [
    {
      $bucket: {
        groupBy: { $cond: [ '$id_card',
          { $subtract: [{ $year: { $toDate: new Date() } },
            { $convert: {
              input: { $substr: [ '$id_card', 6, 4 ] },
              to: 'int',
              onError: { $add: [{ $year: { $toDate: new Date() } }, 1 ] } } },
          ] }, -1 ] },
        boundaries: [ 18, 25, 30, 35, 40, 200 ],
        default: 'Other',
        output: {
          mCount: { $sum: {
            $cond: [{ $eq: [ '$gender', 'M' ] }, 1, 0 ],
          } },
          fCount: { $sum: {
            $cond: [{ $eq: [ '$gender', 'F' ] }, 1, 0 ],
          } },
          count: { $sum: 1 },
        },
      },
    },
  ];
};

exports.unionArray = (a, b) => {
  return [ ...new Set([ ...a, ...b ]) ];
};
// 获取消息类型 时间分组语句
exports.getMsgTimeGroupMongo = type => {
  switch (type) {
    case '0':
      return [
        { $group: { _id: { year: '$year', month: '$month', day: '$day', msgType: '$msgType._id' },
          count: { $sum: '$msgType.count' } } },
        { $group: { _id: { year: '$_id.year', month: '$_id.month', day: '$_id.day' },
          msgType: { $push: { _id: '$_id.msgType', count: '$count' } } } },
      ];
    case '1':
      return [
        { $group: { _id: { year: '$year', month: '$month', msgType: '$msgType._id' },
          count: { $sum: '$msgType.count' } } },
        { $group: { _id: { year: '$_id.year', month: '$_id.month' },
          msgType: { $push: { _id: '$_id.msgType', count: '$count' } } } },
      ];
    case '2':
      return [
        { $group: { _id: { year: '$year', msgType: '$msgType._id' },
          count: { $sum: '$msgType.count' } } },
        { $group: { _id: { year: '$_id.year' },
          msgType: { $push: { _id: '$_id.msgType', count: '$count' } } } },
      ];
    default:
      return [];
  }
};

// 获取车辆出行和疲劳次数分布 时间分组语句
exports.getCarDSMTimeGroupMongo = type => {
  switch (type) {
    case '0':
      return [
        { $group: {
          _id: { year: '$year', month: '$month', day: '$day', _id: '$mileageStartTimeAndDsm._id._id' },
          count: { $sum: '$mileageStartTimeAndDsm.count' }, dsmCount: { $sum: '$mileageStartTimeAndDsm.dsmCount' } } },
        { $group: { _id: { year: '$_id.year', month: '$_id.month', day: '$_id.day' },
          data: { $push: { _id: '$_id._id', count: '$count', dsmCount: '$dsmCount' } } } },
      ];
    case '1':
      return [
        { $group: { _id: { year: '$year', month: '$month', _id: '$mileageStartTimeAndDsm._id._id' },
          count: { $sum: '$mileageStartTimeAndDsm.count' }, dsmCount: { $sum: '$mileageStartTimeAndDsm.dsmCount' } } },
        { $group: { _id: { year: '$_id.year', month: '$_id.month' },
          data: { $push: { _id: '$_id._id', count: '$count', dsmCount: '$dsmCount' } } } },
      ];
    case '2':
      return [
        { $group: { _id: { year: '$year', _id: '$mileageStartTimeAndDsm._id._id' },
          count: { $sum: '$mileageStartTimeAndDsm.count' }, dsmCount: { $sum: '$mileageStartTimeAndDsm.dsmCount' } } },
        { $group: { _id: { year: '$_id.year' },
          data: { $push: { _id: '$_id._id', count: '$count', dsmCount: '$dsmCount' } } } },
      ];
    default:
      return [];
  }
};

// 获取车辆根据 某个维度字段求和 进行时间分组语句
exports.getCarTimeGroupMongo = (type, value) => {
  switch (type) {
    case '0':
      return [
        { $group: {
          _id: { year: '$year', month: '$month', day: '$day', _id: `$${value}._id._id` },
          count: { $sum: `$${value}.count` } } },
        { $group: { _id: { year: '$_id.year', month: '$_id.month', day: '$_id.day' },
          data: { $push: { _id: '$_id._id', count: '$count' } } } },
      ];
    case '1':
      return [
        { $group: { _id: { year: '$year', month: '$month', _id: `$${value}._id._id` },
          count: { $sum: `$${value}.count` } } },
        { $group: { _id: { year: '$_id.year', month: '$_id.month' },
          data: { $push: { _id: '$_id._id', count: '$count' } } } },
      ];
    case '2':
      return [
        { $group: { _id: { year: '$year', _id: `$${value}._id._id` },
          count: { $sum: `$${value}.count` } } },
        { $group: { _id: { year: '$_id.year' },
          data: { $push: { _id: '$_id._id', count: '$count' } } } },
      ];
    default:
      return [];
  }
};
// 获取车辆根据 某个维度字段求平均值 进行时间分组语句
exports.getCarAvgTimeGroupMongo = (type, value) => {
  switch (type) {
    case '0':
      return [
        { $group: {
          _id: { year: '$year', month: '$month', day: '$day', _id: `$${value}._id._id` },
          count: { $avg: `$${value}.count` } } },
        { $group: { _id: { year: '$_id.year', month: '$_id.month', day: '$_id.day' },
          data: { $push: { _id: '$_id._id', count: '$count' } } } },
      ];
    case '1':
      return [
        { $group: { _id: { year: '$year', month: '$month', _id: `$${value}._id._id` },
          count: { $avg: `$${value}.count` } } },
        { $group: { _id: { year: '$_id.year', month: '$_id.month' },
          data: { $push: { _id: '$_id._id', count: '$count' } } } },
      ];
    case '2':
      return [
        { $group: { _id: { year: '$year', _id: `$${value}._id._id` },
          count: { $avg: `$${value}.count` } } },
        { $group: { _id: { year: '$_id.year' },
          data: { $push: { _id: '$_id._id', count: '$count' } } } },
      ];
    default:
      return [];
  }
};

// 获取分页语句
exports.getPageMongo = (pageNumber, pageSize) => {
  return [
    { $skip: (pageNumber - 1) * pageSize },
    { $limit: pageSize },
  ];
};

// 获取根据某个维度字段进行桶分组语句 -（区分车型车系）
exports.getBucketMongo = (group, boundaries, sum = { $sum: 1 }, bucketF) => {
  return [
    {
      $bucket: {
        groupBy: group,
        boundaries,
        default: 'Other',
        output: {
          v: { $push: { vin: '$vin', ...bucketF } },
        },
      },
    },
    { $unwind: '$v' },
    { $lookup: { from: 't_vehicle_record', localField: 'v.vin', foreignField: 'vin', as: 'car' } },
    { $unwind: '$car' },
    { $group: {
      _id: { series_code: '$car.series_code', model_code: '$car.model_code', _id: '$_id' },
      count: sum,
    } },
  ];
};

// 获取时间分组  type   0日  1月 2年
exports.getTimeGroup = (type, baseCond = { count: { $sum: 1 } }) => {
  switch (type) {
    case '0':
      return {
        ...baseCond, _id: { year: '$year', month: '$month', day: '$day' },
      };
    case '1':
      return {
        ...baseCond, _id: { year: '$year', month: '$month' },
      };
    case '2':
      return {
        ...baseCond, _id: { year: '$year' },
      };
    default:
      break;
  }
};

// 通用的时间范围匹配 + 时间分组求和
exports.getCommonAggSum = ({ type, startTime, endTime, value }) => {
  return [
    { $match: this.getTimeRangMatch(startTime, endTime) },
    { $group: this.getTimeGroup(type, { count: { $sum: `$${value}` } }) },
  ];
};
// 通用的时间范围匹配 + 时间分组平均值
exports.getCommonAggAvg = ({ type, startTime, endTime, value }) => {
  return [
    { $match: this.getTimeRangMatch(startTime, endTime) },
    { $group: this.getTimeGroup(type, { count: { $avg: `$${value}` } }) },
  ];
};
// 通用的时间范围匹配 + 时间分组求最大（最近）时间数据
exports.getCommonAggMax = ({ type, startTime, endTime, value }) => {
  return [
    { $match: this.getTimeRangMatch(startTime, endTime) },
    { $sort: { create_date: -1 } },
    { $group: this.getTimeGroup(type, { count: { $first: `$${value}` } }) },
  ];
};
// 车辆基本信息 字典字段映射
exports.statusInfoDict = {
  statsAcceCnt: { k: 'stats_acce_cnt', num: 10, v: 'ar=', mode: [ 'dr_mode_auto', 'dr_mode_eco', 'dr_mode_sport', 'dr_mode_confort' ] },
  statsSpRange: { k: 'stats_sp_range', num: 13, v: 'range=_cnt' },
  statsDece: { k: 'stats_dece', num: 10, v: 'dece=' },
  statsRotate: { k: 'stats_rotate', num: 8, v: 'rotate=' },
  statsSpRotate: { k: 'stats_sp_rotate', num: 8, v: 'rotate=', mode: [ 'sp_1_cnt', 'sp_2_cnt', 'sp_3_cnt' ] },
  statsHighSp: { k: 'stats_high_sp', num: 3, v: 'high_sp=' },
  statsStartSp: { k: 'stats_start_sp', num: 14, v: 'start_sp=' },

  statsSpDeceCnt: { k: 'stats_sp_dece_cnt', num: 10, superNum: 7, v: 'sp_=_cnt.dece.dece=',
    mode: [ 'dr_mode_auto', 'dr_mode_eco', 'dr_mode_sport', 'dr_mode_confort' ] },

  statsSpAcceCnt: {
    multi: {
      acce: { k: 'stats_sp_acce_cnt', num: 10, superNum: 6, v: 'sp_=_cnt.acce.acce=' },
      as: { k: 'stats_sp_acce_cnt', num: 12, superNum: 6, v: 'sp_=_cnt.as.as=' },
    },
  },
};
// 根据车辆基本信息 字典字段映射 获取计算数组
exports.getStatusInfoArr = () => {
  const arr = [];
  Object.keys(this.statusInfoDict).forEach(item => {
    const obj = this.statusInfoDict[item];
    if (obj.multi) {
      Object.keys(obj.multi).forEach(m => {
        const multiObj = obj.multi[m];
        if (multiObj.mode) {
          multiObj.mode.forEach(mode => {
            arr.push({ dbStr: `${multiObj.k}.${mode}.${multiObj.v}`, num: multiObj.num, superNum: multiObj.superNum });
          });
        } else {
          arr.push({ dbStr: `${multiObj.k}.${multiObj.v}`, num: multiObj.num, superNum: multiObj.superNum });
        }
      });
    } else {
      if (obj.mode) {
        obj.mode.forEach(mode => {
          arr.push({ dbStr: `${obj.k}.${mode}.${obj.v}`, num: obj.num, superNum: obj.superNum });
        });
      } else {
        arr.push({ dbStr: `${obj.k}.${obj.v}`, num: obj.num, superNum: obj.superNum });
      }
    }
  });
  return arr;
};


// 弃用  来源唯一
exports.statusInfo = [
  [
    { dbStr: 'stats_acce_cnt.dr_mode_auto.ar=', num: 10 },
    { dbStr: 'stats_acce_cnt.dr_mode_eco.ar=', num: 10 },
    { dbStr: 'stats_acce_cnt.dr_mode_sport.ar=', num: 10 },
    { dbStr: 'stats_acce_cnt.dr_mode_confort.ar=', num: 10 },
    { dbStr: 'stats_sp_range.range=_cnt', num: 13 },
    { dbStr: 'stats_dece.dece=', num: 10 },
    { dbStr: 'stats_rotate.rotate=', num: 8 },
    { dbStr: 'stats_sp_rotate.sp_1_cnt.rotate=', num: 8 },
    { dbStr: 'stats_sp_rotate.sp_2_cnt.rotate=', num: 8 },
    { dbStr: 'stats_sp_rotate.sp_3_cnt.rotate=', num: 8 },
    { dbStr: 'stats_high_sp.high_sp=', num: 3 },
    { dbStr: 'stats_start_sp.start_sp=', num: 14 },

    { dbStr: 'stats_sp_dece_cnt.dr_mode_auto.sp_=_cnt.dece.dece=', superNum: 7, num: 10 },
    { dbStr: 'stats_sp_dece_cnt.dr_mode_eco.sp_=_cnt.dece.dece=', superNum: 7, num: 10 },
    { dbStr: 'stats_sp_dece_cnt.dr_mode_sport.sp_=_cnt.dece.dece=', superNum: 7, num: 10 },
    { dbStr: 'stats_sp_dece_cnt.dr_mode_confort.sp_=_cnt.dece.dece=', superNum: 7, num: 10 },

    { dbStr: 'stats_sp_acce_cnt.sp_=_cnt.acce.acce=', superNum: 6, num: 10 },
    { dbStr: 'stats_sp_acce_cnt.sp_=_cnt.as.as=', superNum: 6, num: 12 },

  ],
];

// 获取活跃app的条件
exports.getAppActiveCond = ({ startTime, endTime }) => {
  return [
    { $match: { ...this.getTimeRangMatch(startTime, endTime, 'login_time'), login_state: 1 } },
    { $sort: { login_time: 1 } },
    { $group: {
        _id: '$user_Id',
        login_count: { $sum: 1 },
        provice_name: { $first: '$provice_name' }, city_name: { $first: '$city_name' },
      } },
    { $lookup: { from: 't_sync_county', localField: 'provice_name', foreignField: 'province_name', as: 'area' } },
    { $project: {
        login_count: 1,
        area_name: { $arrayElemAt: [ '$area.area_name', 0 ] },
        provice_name: '$provice_name',
        city_name: '$city_name',
      } },
    { $lookup:
          {
            from: 'app_behavior_record',
            let: { user_id: '$_id' },
            pipeline: [{ $match: { $expr: { $and:
                      [
                        { $eq: [ '$user_id', '$$user_id' ] },
                        { $gte: [ '$create_time', startTime ] },
                        { $lt: [ '$create_time', endTime ] },
                      ] } } }], as: 'behaviors',
          },
    },
    { $unwind: { path: '$behaviors', preserveNullAndEmptyArrays: true } },
    { $group: {
        _id: '$_id', login_count: { $first: '$login_count' },
        use_duration: { $sum: { $cond: [ '$behaviors.use_duration', '$behaviors.use_duration', 0 ] } },
        area_name: { $first: '$area_name' }, provice_name: { $first: '$provice_name' }, city_name: { $first: '$city_name' },
      } },
    { $match: { $or: [{ use_duration: { $gt: 30 * 60 * 1000 } }, { login_count: { $gte: 3 } }] } },
  ];
};