chart-admin/app/service/tVehicleRecordService2.js

'use strict';
const moment = require('moment');
const Service = require('egg').Service;

class TVehicleRecordService2 extends Service {

  async register(cond) {
    const { ctx } = this;
    const agg = ctx.helper.getCommonAggSum({ ...cond, value: 'saledNewUser' });
    return await ctx.model.Local.TVehicleRecordModel.aggregateFix(agg);
  }

  // 如果在用户表计算 无法算到过去的实销用户数量
  async saledUser(cond) {
    const { ctx } = this;
    const agg = ctx.helper.getCommonAggMax({ ...cond, value: 'saledUser' });
    return await ctx.model.Local.TVehicleRecordModel.aggregateFix(agg);
  }

  async saledExt() {
    const { ctx } = this;
    // 最高哪天  最高是哪月  当前总数   当前总数app   当前总数ivi
    // const max = await ctx.model.Local.TRegisterInfoModel.findOne().sort({ total: -1 });
    // let maxDay = {};
    // if (max) {
    //   maxDay = { _id: { year: max.year, month: max.month, day: max.day }, count: max.total };
    // }
    // const agg = [
    //   { $group: { _id: { year: '$year', month: '$month' }, count: { $sum: '$total' } } },
    //   { $sort: { count: -1 } },
    //   { $group: { _id: null, data: { $first: '$$ROOT' } } },
    //   // 或者
    //   // { $group: { _id: null, data: { $max: { count: '$count', _id: { year: '$_id.year', month: '$_id.month' } } } } },
    // ];
    // const result = await ctx.model.Local.TRegisterInfoModel.aggregateNGroup(agg);
    // const maxMonth = result.data || {};
    const user = await ctx.model.Local.TRbacUserModel.findOne({}, {
      appTotal: 1, iviTotal: 1,
      saledTotal: 1, saledAppTotal: 1, saledIviTotal: 1,
    }).sort({ create_date: -1 });
    let appTotal = 0;
    let iviTotal = 0;
    let saledTotal = 0;
    let saledAppTotal = 0;
    let saledIviTotal = 0;
    if (user) {
      appTotal = user.appTotal;
      iviTotal = user.iviTotal;
      saledTotal = user.saledTotal;
      saledAppTotal = user.saledAppTotal;
      saledIviTotal = user.saledIviTotal;
    }
    return { saledTotal, saledAppTotal, saledIviTotal, appTotal, iviTotal };
  }


  // 实销车辆统计分析 实销车辆百分比统计分析 入网车辆总数统计分析
  async index({ type, startTime, endTime, seriesCode, modelCode }) {
    const { ctx } = this;
    const cond = [{ $match: {} }];
    if (seriesCode) {
      cond[0].$match['car._id.series_code'] = seriesCode;
    }

    if (modelCode) {
      cond[0].$match['car._id.model_code'] = modelCode;
    }

    const agg = [
      { $match: ctx.helper.getTimeRangMatch(startTime, endTime) },
      { $sort: { create_date: -1 } },
      { $group: ctx.helper.getTimeGroup(type, { car: { $first: '$car' } }) },
      { $unwind: '$car' },
      ...cond,
      { $group: { _id: '$_id', count: { $sum: '$car.count' },
        saledCount: { $sum: '$car.saledCount' },
      } },
    ];

    return await ctx.model.Local.TVehicleRecordModel.aggregateFix(agg);
  }

  async increment({ type, startTime, endTime, seriesCode, modelCode }) {
    const { ctx } = this;
    const cond = [{ $match: {} }];
    if (seriesCode) {
      cond[0].$match['car._id.series_code'] = seriesCode;
    }

    if (modelCode) {
      cond[0].$match['car._id.model_code'] = modelCode;
    }

    const agg = [
      { $match: ctx.helper.getTimeRangMatch(startTime, endTime) },
      { $unwind: '$car' },
      ...cond,
      { $group: ctx.helper.getTimeGroup(type, { count: { $sum: '$car.saledNewTotal' } }) },
    ];

    return await ctx.model.Local.TVehicleRecordModel.aggregateFix(agg);
  }

  // 在线车
  async online({ type, startTime, endTime, seriesCode, modelCode }) {
    const { ctx } = this;
    const cond = [{ $match: {} }];
    if (seriesCode) {
      cond[0].$match['onlineCar._id.series_code'] = seriesCode;
    }

    if (modelCode) {
      cond[0].$match['onlineCar._id.model_code'] = modelCode;
    }

    const agg = [
      { $match: ctx.helper.getTimeRangMatch(startTime, endTime) },
      { $unwind: '$onlineCar' },
      ...cond,
      { $group: ctx.helper.getTimeGroup(type, { count: { $sum: '$onlineCar.count' } }) },
    ];

    return await ctx.model.Local.TVehicleRecordModel.aggregateFix(agg);
  }

  // TODO 暂时即时查询
  async saled({ seriesCode, modelCode }) {
    const { ctx } = this;
    const cond = [{ $match: { is_saled_car: 1, user_id: { $ne: null } } }];
    if (seriesCode) {
      cond[0].$match.series_code = seriesCode;
    }

    if (modelCode) {
      cond[0].$match.model_code = modelCode;
    }

    const agg = [
      ...cond,
      { $group: { _id: '$pro_code', count: { $sum: 1 } } },
      { $lookup: { from: 't_sync_province', localField: '_id', foreignField: 'out_pro_code', as: 'pro' } },
      { $unwind: { path: '$pro', preserveNullAndEmptyArrays: true } },
      { $lookup: { from: 't_sync_county', localField: '_id', foreignField: 'out_pro_code', as: 'area' } },
      { $project: {
        area_name: { $arrayElemAt: [ '$area.area_name', 0 ] },
        provice_name: '$pro.province_name',
        count: '$count',
      } },

      { $group: { _id: { area_name: '$area_name' },
        provinces: { $push: { provice_name: '$provice_name', count: '$count' } },
        count: { $sum: '$count' } } },
      { $group: { _id: null,
        areas: { $push: { area_name: '$_id.area_name', provinces: '$provinces', count: '$count' } },
        count: { $sum: '$count' } } },
    ];

    return await ctx.model.TVehicleRecordModel.aggregateNGroup(agg);
  }

  // TODO 暂时即时查询
  async saledCity({ seriesCode, modelCode }) {
    const { ctx } = this;
    const cond = [{ $match: { is_saled_car: 1, user_id: { $ne: null },
      pro_code: { $exists: true }, city_code: { $exists: true } } }];
    if (seriesCode) {
      cond[0].$match.series_code = seriesCode;
    }

    if (modelCode) {
      cond[0].$match.model_code = modelCode;
    }

    const agg = [
      ...cond,
      { $group: { _id: '$city_code', count: { $sum: 1 }, pro_code: { $first: '$pro_code' }, city_code: { $first: '$city_code' } } },
      { $sort: { count: -1 } },
      { $limit: 3 },
      { $lookup: { from: 't_sync_province', localField: 'pro_code', foreignField: 'out_pro_code', as: 'pro' } },
      { $unwind: '$pro' },
      { $lookup: {
        from: 't_sync_city',
        let: { city_code: '$city_code', province_id: '$pro.province_id' },
        pipeline: [{ $match: { $expr: { $and:
                    [
                      { $eq: [ '$out_city_code', '$$city_code' ] },
                      { $eq: [ '$province_id', '$$province_id' ] },
                    ] } } }], as: 'city',
      } },
      { $unwind: '$city' },
      { $project: {
        _id: 0,
        count: 1,
        provice_name: '$pro.province_name',
        city_name: '$city.city_name',
      } },
    ];

    return await ctx.model.TVehicleRecordModel.aggregate(agg).allowDiskUse(true);
  }

  // TODO 暂时即时查询
  async activeLocation({ startTime, endTime, seriesCode, modelCode }) {
    const { ctx } = this;
    // const onlineAgg = [
    //   { $match: { ...ctx.helper.getTimeRangMatch(ctx.helper.getMonthTop(startTime), endTime,
    //     'online_time') } },
    //   { $group: {
    //     _id: '$vin',
    //     login_count: { $sum: 1 },
    //   } },
    //   { $match: { login_count: { $gt: 3 } } },
    // ];
    // const driveAgg = [
    //   { $match: ctx.helper.getTimeRangMatch(ctx.helper.getMonthTop(startTime), endTime,
    //     'start_time') },
    //   { $group: { _id: '$vin',
    //     mileage: { $sum: { $cond: [ '$mileage', { $toDouble: '$mileage' }, 0 ] } } } },
    //   { $match: { mileage: { $gt: 50 } } },
    // ];
    // const result1 = await ctx.model.TBoxOnlineModel.aggregateFix(onlineAgg);
    // const result2 = await ctx.model.DrivingBehaviorInfoModel.aggregateFix(driveAgg);
    // const unionArray = ctx.helper.unionArray(result1.map(item => item._id), result2.map(item => item._id));
    const result = await ctx.model.Local.TVehicleRecordModel.findOne(
      { year: moment(startTime).year(), month: moment(startTime).month() + 1 }, { activeVin: 1 }
    ).sort({ create_date: -1 });
    let unionArray;
    if (result) {
      unionArray = result.activeVin || [];
    } else {
      unionArray = [];
    }
    const cond = { };
    if (seriesCode) {
      cond.series_code = seriesCode;
    }

    if (modelCode) {
      cond.model_code = modelCode;
    }
    const agg = [
      { $match: { vin: { $in: unionArray }, ...cond } },
      { $lookup: { from: 't_sync_province', localField: 'pro_code', foreignField: 'out_pro_code', as: 'pro' } },
      { $unwind: '$pro' },
      { $lookup: {
        from: 't_sync_city',
        let: { city_code: '$city_code', province_id: '$pro.province_id' },
        pipeline: [{ $match: { $expr: { $and:
                  [
                    { $eq: [ '$out_city_code', '$$city_code' ] },
                    { $eq: [ '$province_id', '$$province_id' ] },
                  ] } } }], as: 'city',
      } },
      { $unwind: '$city' },
      { $lookup: { from: 't_sync_county', localField: 'pro_code', foreignField: 'out_pro_code', as: 'area' } },
      { $project: {
        area_name: { $arrayElemAt: [ '$area.area_name', 0 ] },
        provice_name: '$pro.province_name',
        city_name: '$city.city_name',
      } },
      { $addFields: {
        count: 1,
      } },
      { $match: { area_name: { $exists: true } } },
      ...ctx.helper.getLocationMongo(),
    ];
    const reuslt = await ctx.model.TVehicleRecordModel.aggregateNGroup(agg);
    return reuslt;
  }

  // 活跃车辆
  async active({ type, startTime, endTime, seriesCode, modelCode }) {
    const { ctx } = this;
    const cond = [{ $match: {} }];
    if (seriesCode) {
      cond[0].$match['car._id.series_code'] = seriesCode;
    }

    if (modelCode) {
      cond[0].$match['car._id.model_code'] = modelCode;
    }
    let agg = [];
    if (type == 1) {
      agg = [
        { $match: ctx.helper.getTimeRangMatch(startTime, endTime) },
        { $sort: { create_date: -1 } },
        { $group: ctx.helper.getTimeGroup(type, { car: { $first: '$car' } }) },
        { $unwind: '$car' },
        ...cond,
        { $group: { _id: '$_id', count: { $sum: '$car.count' },
          activeCount: { $sum: '$car.activeCount' } } },
      ];
    } else if (type == 2) {
      agg = [
        { $match: ctx.helper.getTimeRangMatch(startTime, endTime) },
        { $sort: { create_date: -1 } },
        { $group: ctx.helper.getTimeGroup('1', { car: { $first: '$car' } }) },
        { $unwind: '$car' },
        ...cond,
        { $group: { _id: { year: '$_id.year' }, count: { $sum: '$car.count' },
          activeCount: { $sum: '$car.activeCount' } } },
      ];
    }
    return await ctx.model.Local.TVehicleRecordModel.aggregateFix(agg);
  }

  // 活跃车辆 扩展数据
  async activeExt({ seriesCode, modelCode }) {
    const { ctx } = this;
    const cond = [{ $match: {} }];
    if (seriesCode) {
      cond[0].$match['car._id.series_code'] = seriesCode;
    }

    if (modelCode) {
      cond[0].$match['car._id.model_code'] = modelCode;
    }
    const maxMonthAgg = [
      { $sort: { create_date: -1 } },
      { $group: ctx.helper.getTimeGroup('1', { car: { $first: '$car' } }) },
      { $unwind: '$car' },
      ...cond,
      { $group: { _id: '$_id', activeCount: { $sum: '$car.activeCount' } } },
      { $sort: { activeCount: -1 } },
      { $group: { _id: null, data: { $first: '$$ROOT' } } },
    ];
    const maxYearAgg = [
      { $sort: { create_date: -1 } },
      { $group: ctx.helper.getTimeGroup('1', { car: { $first: '$car' } }) },
      { $unwind: '$car' },
      ...cond,
      { $group: { _id: { year: '$_id.year' }, activeCount: { $sum: '$car.activeCount' } } },
      { $sort: { activeCount: -1 } },
      { $group: { _id: null, data: { $first: '$$ROOT' } } },
    ];
    const maxMonthResult = await ctx.model.Local.TVehicleRecordModel.aggregateNGroup(maxMonthAgg);
    const maxYearResult = await ctx.model.Local.TVehicleRecordModel.aggregateNGroup(maxYearAgg);
    const maxMonth = maxMonthResult.data || {};
    const maxYear = maxYearResult.data || {};
    return { maxMonth, maxYear };
  }


  // ———————————————————清洗数据———————————————————————————————————————————————————————————————————————————————————————————————————————————————————

  async statistics({ timeRangData, initData, isForceUpdate }) {
    const { ctx } = this;
    const hasData = await ctx.service.statisticsService.saveBefore(ctx.model.Local.TVehicleRecordModel,
      { ...initData });
    if (hasData && !isForceUpdate) {
      return;
    }
    initData.start_time = new Date();
    const result = await this.group(timeRangData);
    ctx.logger.info('任务进行group');
    const { activeVin, aResult } = await this.activeCount(timeRangData);
    ctx.logger.info('任务进行activeCount');
    const onlineCar = await this.onlineCount(timeRangData);
    const car = result.car || [];
    aResult.forEach(c => {
      const obj = car.find(item => item._id.series_code == c._id.series_code && item._id.model_code == c._id.model_code);
      if (obj) {
        obj.activeCount = c.activeCount;
      }
    });
    ctx.logger.info('任务进行onlineCount');
    const saledUser = await this.saledUserCount(timeRangData);
    ctx.logger.info('任务进行saledUserCount');
    const saledNewUser = await this.saledNewUserCount(timeRangData, saledUser);
    ctx.logger.info('任务进行saledNewUserCount');

    await ctx.service.statisticsService.save(ctx.model.Local.TVehicleRecordModel,
      { ...initData, car, onlineCar, saledNewUser, saledUser, activeVin }, isForceUpdate);
  }


  async group({ startTime, endTime }) {
    const { ctx } = this;
    const agg = [
      // { $match: { sale_date: { $lt: endTime } } },
      { $group: { _id: { series_code: '$series_code', model_code: '$model_code' },
        series_name: { $first: '$series_name' }, model_name: { $first: '$model_name' },
        count: { $sum: 1 },
        saledCount: { $sum: {
          $cond: [{ $and: [{ $eq: [ '$is_saled_car', 1 ] }, { $lt: [ '$sale_date', endTime ] },
            { $ne: [{ $ifNull: [ '$user_id', null ] }, null ] }] }, 1, 0 ],
        } },
        saledNewTotal: { $sum: {
          $cond: [{ $and: [{ $eq: [ '$is_saled_car', 1 ] },
            { $gte: [ '$sale_date', startTime ] }, { $lt: [ '$sale_date', endTime ] },
            { $ne: [{ $ifNull: [ '$user_id', null ] }, null ] }] }, 1, 0 ],
        } },
      } },
      { $group: { _id: null, car: { $push: '$$ROOT' }, saledTotal: { $sum: '$saledCount' } } },
    ];
    return await ctx.model.TVehicleRecordModel.aggregateNGroup(agg);
  }


  // TODO 本js里活跃的计算方式
  async activeCount({ startTime, endTime }) {
    const { ctx } = this;
    const onlineAgg = [
      { $match: { ...ctx.helper.getTimeRangMatch(ctx.helper.getMonthTop(startTime), endTime,
        'online_time') } },
      { $group: {
        _id: '$vin',
        login_count: { $sum: 1 },
      } },
      { $match: { login_count: { $gt: 3 } } },
    ];
    const driveAgg = [
      { $match: ctx.helper.getTimeRangMatch(ctx.helper.getMonthTop(startTime), endTime,
        'start_time') },
      { $group: { _id: '$vin',
        mileage: { $sum: { $cond: [ '$mileage', { $toDouble: '$mileage' }, 0 ] } } } },
      { $match: { mileage: { $gt: 50 } } },
    ];
    const result1 = await ctx.model.TBoxOnlineModel.aggregateFix(onlineAgg);
    ctx.logger.info('任务进行onlineAgg');
    const result2 = await ctx.model.DrivingBehaviorInfoModel.aggregateFix(driveAgg);
    ctx.logger.info('任务进行driveAgg');
    const unionArray = ctx.helper.unionArray(result1.map(item => item._id), result2.map(item => item._id));
    ctx.logger.info('任务进行unionArray');
    const agg = [
      { $match: { create_time: { $lt: endTime }, vin: { $in: unionArray } } },
      { $group: { _id: { series_code: '$series_code', model_code: '$model_code' },
        activeCount: { $sum: 1 },
      } },
    ];
    const aResult = await ctx.model.TVehicleRecordModel.aggregateFix(agg);
    ctx.logger.info('任务进行agg');
    return { activeVin: unionArray, aResult };
  }


  async onlineCount({ startTime, endTime }) {
    const { ctx } = this;
    const agg = [
      { $match: ctx.helper.getTimeRangMatch(startTime, endTime, 'online_time') },
      { $group: { _id: '$vin', count: { $sum: 1 } } },
      { $lookup: { from: 't_vehicle_record', localField: '_id', foreignField: 'vin', as: 'car' } },
      { $unwind: { path: '$car', preserveNullAndEmptyArrays: true } },
      { $group: { _id: { series_code: '$car.series_code', model_code: '$car.model_code' },
        count: { $sum: 1 } } },
    ];
    return await ctx.model.TBoxOnlineModel.aggregateFix(agg);
  }

  async saledNewUserCount({ startTime, endTime }, saledUser) {
    const { ctx } = this;

    // const beforeR = await ctx.model.Local.TVehicleRecordModel.findOne(
    //   { }, { saledUser: 1 }
    // ).sort({ create_date: -1 });
    // if (beforeR) {
    //   return saledUser - beforeR.saledUser;
    // }
    const agg = [
      { $match: { is_saled_car: 1, sale_date: { $gte: startTime, $lt: endTime }, user_id: { $ne: null } } },
      { $group: {
        _id: '$user_id',
      } },
    ];
    const result = await ctx.model.TVehicleRecordModel.aggregateFix(agg);
    return await ctx.model.TRbacUserModel.find({ role_id: ctx.helper.saledRoleId,
      user_id: { $in: result.map(item => item._id) } }).countDocuments();


  }

  async saledUserCount({ endTime }) {
    const { ctx } = this;
    const agg = [
      { $match: { is_saled_car: 1, sale_date: { $lt: endTime }, user_id: { $ne: null } } },
      { $group: {
        _id: '$user_id',
      } },
    ];
    const result = await ctx.model.TVehicleRecordModel.aggregateFix(agg);
    return await ctx.model.TRbacUserModel.find({ role_id: ctx.helper.saledRoleId,
      user_id: { $in: result.map(item => item._id) } }).countDocuments();
  }
}

module.exports = TVehicleRecordService2;