server-v2/app/service/statistics.js

'use strict';
const { CrudService } = require('naf-framework-mongoose-free/lib/service');
const { BusinessError, ErrorCode } = require('naf-core').Error;
const _ = require('lodash');
const assert = require('assert');
const moment = require('moment');
const { ObjectId } = require('mongoose').Types;

//
class StatisticsService extends CrudService {
  constructor(ctx) {
    super(ctx, 'statistics');
    this.billModel = this.ctx.model.Business.Bill;
    this.rssModel = this.ctx.model.Relation.RelationStudentSchool;
    this.rcsModel = this.ctx.model.Relation.RelationCoachSchool;
    this.lessonCoachModel = this.ctx.model.Business.LessonCoach;
    this.lessonStudentModel = this.ctx.model.Business.LessonStudent;
    this.lessonModel = this.ctx.model.Business.Lesson;
  }
  // 教练统计,学员情况
  async coachStudentLesson({ school_id, coach_id }) {
    assert(school_id, '缺少学校信息');
    assert(coach_id, '缺少教练信息');
    const query = { school_id, coach_id };
    const { year } = this.getPartsOfNow();
    const yearEnd = `${year}-12-01`;
    let monthList = this.getMonthList(12, yearEnd);
    monthList = monthList.map(i => {
      const start = moment(i).startOf('month').format('YYYY-MM-DD HH:mm:ss');
      const end = moment(i).endOf('month').format('YYYY-MM-DD HH:mm:ss');
      return [ start, end ];
    });
    query.$and = [{ 'meta.createdAt': { $gte: `${year}-01-01 00:00:00` } }, { 'meta.createdAt': { $lte: `${year}-12-31 23:59:59` } }];
    const lcList = await this.lessonCoachModel.find(query).populate('lesson_id');
    const lessonList = lcList.map(f => f.lesson_id);
    const arr = [];
    for (const months of monthList) {
      const s = _.head(months);
      const l = _.last(months);
      const m = moment(s).month() + 1;
      const list = lessonList.filter(f => moment(_.get(f, 'meta.createdAt')).isBetween(s, l, null, '[]'));
      const lesson_ids = list.map(i => i._id);
      const studentNumber = await this.lessonStudentModel.count({ lesson_id: lesson_ids, is_pay: '1' });
      arr.push({ name: `${m}月`, value: studentNumber });
    }
    return arr;
  }

  // 教练统计, 授课情况
  async coachLesson({ coach_id, school_id }) {
    assert(school_id, '缺少学校信息');
    assert(coach_id, '缺少教练信息');
    const query = { school_id, coach_id };
    const { year } = this.getPartsOfNow();
    const yearEnd = `${year}-12-01`;
    let monthList = this.getMonthList(12, yearEnd);
    monthList = monthList.map(i => {
      const start = moment(i).startOf('month').format('YYYY-MM-DD HH:mm:ss');
      const end = moment(i).endOf('month').format('YYYY-MM-DD HH:mm:ss');
      return [ start, end ];
    });
    query.$and = [{ 'meta.createdAt': { $gte: `${year}-01-01 00:00:00` } }, { 'meta.createdAt': { $lte: `${year}-12-31 23:59:59` } }];
    const lcList = await this.lessonCoachModel.find(query).populate('lesson_id');
    const lessonList = lcList.filter(f => f.lesson_id && f.lesson_id.status === '4');
    const arr = [];
    for (const months of monthList) {
      const s = _.head(months);
      const l = _.last(months);
      const m = moment(s).month() + 1;
      const list = lessonList.filter(f => moment(_.get(f, 'meta.createdAt')).isBetween(s, l, null, '[]'));
      arr.push({ name: `${m}月`, value: list.length });
    }
    return arr;
  }

  // 学员统计,付费情况
  async studentPay({ school_id, student_id }) {
    assert(school_id, '缺少学校信息');
    assert(student_id, '缺少学生信息');
    const query = { school_id, payer_id: student_id, is_pay: '1', type: [ '-1', '-2', '2' ] };
    const { year } = this.getPartsOfNow();
    const yearEnd = `${year}-12-01`;
    let monthList = this.getMonthList(12, yearEnd);
    monthList = monthList.map(i => {
      const start = moment(i).startOf('month').format('YYYY-MM-DD HH:mm:ss');
      const end = moment(i).endOf('month').format('YYYY-MM-DD HH:mm:ss');
      return [ start, end ];
    });
    query.$and = [{ 'meta.createdAt': { $gte: `${year}-01-01 00:00:00` } }, { 'meta.createdAt': { $lte: `${year}-12-31 23:59:59` } }];
    const billList = await this.billModel.find(query);
    const arr = [];
    for (const months of monthList) {
      const s = _.head(months);
      const l = _.last(months);
      const m = moment(s).month() + 1;
      const list = billList.filter(f => moment(_.get(f, 'meta.createdAt')).isBetween(s, l, null, '[]'));
      const payList = list.filter(f => f.type !== '2');
      const returnList = list.filter(f => f.type === '2');
      const pay = payList.reduce((p, n) => p + (n.money || 0), 0);
      const ret = returnList.reduce((p, n) => p + (n.money || 0), 0);
      const total = pay - ret;
      arr.push({ name: `${m}月`, value: total });
    }
    return arr;
  }

  // 学员统计,学员上课时长及签到
  async studentLearning({ school_id, student_id }) {
    assert(school_id, '缺少学校信息');
    assert(student_id, '缺少学生信息');
    const query = { school_id, student_id, is_pay: '1' };
    const { year } = this.getPartsOfNow();
    const yearEnd = `${year}-12-01`;
    let monthList = this.getMonthList(12, yearEnd);
    monthList = monthList.map(i => {
      const start = moment(i).startOf('month').format('YYYY-MM-DD HH:mm:ss');
      const end = moment(i).endOf('month').format('YYYY-MM-DD HH:mm:ss');
      return [ start, end ];
    });
    query.$and = [{ 'meta.createdAt': { $gte: `${year}-01-01 00:00:00` } }, { 'meta.createdAt': { $lte: `${year}-12-31 23:59:59` } }];
    const lsList = await this.lessonStudentModel.find(query).populate('lesson_id');
    const lessonList = lsList.map(i => i.lesson_id).map(i => _.pick(i, [ '_id', 'type', 'title', 'meta', 'status', 'time_start', 'time_end' ]));
    const minutes = [];
    const signs = [];
    for (const months of monthList) {
      const s = _.head(months);
      const l = _.last(months);
      const m = moment(s).month() + 1;
      const list = lessonList.filter(f => moment(_.get(f, 'meta.createdAt')).isBetween(s, l, null, '[]') && f.status === '4');
      let minute = 0;
      let sign = 0;
      for (const lesson of list) {
        const { time_start, time_end, _id: lesson_id } = lesson;
        minute += moment(time_end).diff(time_start, 'minutes');
        const r = lsList.find(f => ObjectId(f.lesson_id._id).equals(lesson_id));
        if (r && _.get(r, 'is_sign') === '1') sign++;
      }
      minutes.push(minute);
      signs.push(sign);
    }
    return { minutes, signs };
  }

  // 学校统计,教练收入
  async schoolCoachIn({ school_id, coach_id }) {
    assert(school_id, '缺少学校信息');
    assert(coach_id, '缺少教练信息');
    // 教练收入分为私教课和公开课,公开课是根据给教练设置的钱计算,
    const query = { school_id, coach_id };
    const { year } = this.getPartsOfNow();
    const yearEnd = `${year}-12-01`;
    let monthList = this.getMonthList(12, yearEnd);
    monthList = monthList.map(i => {
      const start = moment(i).startOf('month').format('YYYY-MM-DD HH:mm:ss');
      const end = moment(i).endOf('month').format('YYYY-MM-DD HH:mm:ss');
      return [ start, end ];
    });
    query.$and = [{ 'meta.createdAt': { $gte: `${year}-01-01 00:00:00` } }, { 'meta.createdAt': { $lte: `${year}-12-31 23:59:59` } }];
    const lcList = await this.lessonCoachModel.find(query).populate('lesson_id');
    const lessonList = lcList.map(i => i.lesson_id).map(i => _.pick(i, [ '_id', 'type', 'title', 'meta' ]));
    const arr = [];
    for (const months of monthList) {
      const s = _.head(months);
      const l = _.last(months);
      const m = moment(s).month() + 1;
      const obj = { m, total: 0 };
      const list = lessonList.filter(f => moment(_.get(f, 'meta.createdAt')).isBetween(s, l, null, '[]'));
      // console.log(m);
      const privateLesson = list.filter(f => f.type === '1');
      const publicLesson = list.filter(f => f.type === '0');
      // 先算公开课
      for (const pl of publicLesson) {
        // 找到该教练的价格,这节公开课的人头数 算出教练应得金额
        const { _id: lesson_id } = pl;
        const lessonCoach = lcList.find(f => ObjectId(f.lesson_id._id).equals(lesson_id));
        if (!lessonCoach) continue;
        const studentNumber = await this.lessonStudentModel.count({ lesson_id, is_pay: '1' });
        const { money = 0 } = lessonCoach;
        const total = money * studentNumber;
        obj.total += total;
      }
      for (const pl of privateLesson) {
        // 私教课,lessonStudent中这个课学生交的钱
        const { _id: lesson_id } = pl;
        const studentList = await this.lessonStudentModel.find({ lesson_id, is_pay: '1' });
        const total = studentList.reduce((p, n) => p + (n.money || 0), 0);
        obj.total += total;
      }
      arr.push(obj);
    }
    return arr;
  }

  // 学校统计 每月上课次数
  async schoolSignCoach({ school_id, coach_id }) {
    assert(school_id, '缺少学校信息');
    assert(coach_id, '缺少教练信息');
    // 查出这个学校下面的教练
    const query = { school_id, coach_id };
    const { year } = this.getPartsOfNow();
    const yearEnd = `${year}-12-01`;
    query.$and = [{ 'meta.createdAt': { $gte: `${year}-01-01 00:00:00` } }, { 'meta.createdAt': { $lte: `${year}-12-31 23:59:59` } }];
    let monthList = this.getMonthList(12, yearEnd);
    monthList = monthList.map(i => {
      const start = moment(i).startOf('month').format('YYYY-MM-DD HH:mm:ss');
      const end = moment(i).endOf('month').format('YYYY-MM-DD HH:mm:ss');
      return [ start, end ];
    });
    let lcList = await this.lessonCoachModel.find(query, { coach_id: 1, meta: 1 });
    if (lcList.length > 0) lcList = JSON.parse(JSON.stringify(lcList));
    const arr = [];
    for (const months of monthList) {
      const s = _.head(months);
      const l = _.last(months);
      const m = moment(s).month() + 1;
      const obj = { m };
      const list = lcList.filter(f => moment(_.get(f, 'meta.createdAt')).isBetween(s, l, null, '[]'));
      obj.value = list.length;
      arr.push(obj);
    }

    return arr;
  }

  // 学校统计:学员按岁数区间
  async schoolStudentAge({ school_id }) {
    assert(school_id, '缺少学校信息');
    // 年龄组
    const ageList = [
      [ null, 6 ],
      [ 7, 9 ],
      [ 10, 12 ],
      [ 12, 18 ],
      [ 19, 30 ],
      [ 31, null ],
    ];
    const data = [];
    let list = await this.rssModel.find({ school_id }).populate('student_id', 'birth');
    if (list.length > 0) list = JSON.parse(JSON.stringify(list));
    for (const a of ageList) {
      const start = _.head(a);
      const end = _.last(a);
      const obj = {};
      if (start && end) obj.name = `${start}-${end}岁`;
      else if (!start) obj.name = `${end}岁以下`;
      else if (!end) obj.name = `${start}岁以上`;
      const l = list.filter(f => {
        const birth = _.get(f, 'student_id.birth');
        const age = moment().diff(birth, 'years');
        if (start && end) return age >= start && age <= end;
        else if (!start) return age <= end;
        else if (!end) return age >= start;
        return false;
      });
      obj.value = l.length;
      data.push(obj);
    }
    return data;
  }

  /**
   * 羽校总收入
   * 统计账单的 收入(类型为-1/-2) 且 is_pay 不为 0
   * m:当前这个月; 3m: 往前推3个月; 6m:往前推6个月; 1y:当前年
   * @param {Object} query 查询条件
   * @property {String} school_id 学校id
   * @property {String} skip 分页
   * @property {String} limit 分页
   */
  async schoolTotalIn({ school_id }) {
    assert(school_id, '缺少羽校信息');
    const query = { is_pay: { $ne: '0' }, school_id, type: [ '-1', '-2' ] };
    const projection = { pay_for: 1, from_id: 1, type: 1, money: 1, time: 1, payer_id: 1 };
    const qm = this.resetQuery('m');
    const bm = await this.billModel.find({ ...qm, ...query }, projection);
    const mm = bm.reduce((p, n) => this.ctx.plus(p, n.money), 0);
    const q3m = this.resetQuery('3m');
    const b3m = await this.billModel.find({ ...q3m, ...query }, projection);
    const m3m = b3m.reduce((p, n) => this.ctx.plus(p, n.money), 0);
    const q6m = this.resetQuery('6m');
    const b6m = await this.billModel.find({ ...q6m, ...query }, projection);
    const m6m = b6m.reduce((p, n) => this.ctx.plus(p, n.money), 0);
    const qy = this.resetQuery('1y');
    const by = await this.billModel.find({ ...qy, ...query }, projection);
    const my = by.reduce((p, n) => this.ctx.plus(p, n.money), 0);
    return { mm, m3m, m6m, my };
  }

  async studentSign({ school_id, lesson_id, student_id, range, skip, limit }) {
    const pipeline = [];
    const baseCol = { time_start: 1, title: 1 };
    const studentQuery = {};
    // 返回数据:课程,上课时间,学生,是否签到,是否缴费
    let $match = { school_id, lesson_id };
    if (_.isArray(range)) {
      const start = _.head(range);
      const end = _.last(range);
      const timeQuery = (start, end) => ({ $and: [{ time_start: { $gte: start } }, { time_start: { $lte: end } }] });
      const tq = timeQuery(start, end);
      $match = { ...$match, ...tq };
    }
    pipeline.push({ $match });
    pipeline.push({ $addFields: { lesson_id: { $toString: '$_id' } } });
    if (student_id) {
      studentQuery.student_id = student_id;
    }
    // #region 正式学生
    pipeline.push({
      $lookup: {
        from: 'lessonStudent',
        localField: 'lesson_id',
        foreignField: 'lesson_id',
        pipeline: [
          { $match: studentQuery },
          { $project: { is_sign: 1, is_pay: 1, soid: { $toObjectId: '$student_id' } } },
          {
            $lookup: {
              from: 'student',
              localField: 'soid',
              foreignField: '_id',
              as: 'si',
            },
          },
          { $project: { is_sign: 1, is_pay: 1, name: { $first: '$si.name' }, icon: { $first: '$si.icon' } } },
        ],
        as: 'zStudent',
      },
    });
    // #endregion
    // #region 临时学生
    pipeline.push({
      $lookup: {
        from: 'tempLessonApply',
        localField: 'lesson_id',
        foreignField: 'lesson_id',
        pipeline: [
          { $match: studentQuery },
          { $addFields: { soid: { $toObjectId: '$student_id' } } },
          {
            $lookup: {
              from: 'student',
              localField: 'soid',
              foreignField: '_id',
              as: 'si',
            },
          },
          { $project: { name: 1, is_sign: 1, is_pay: 1, icon: { $ifNull: [{ $first: '$si.icon' }, []] } } },
        ],
        as: 'lStudent',
      },
    });
    // #endregion
    pipeline.push({ $project: { ...baseCol, studentList: { $concatArrays: [ '$zStudent', '$lStudent' ] } } });
    pipeline.push({ $unwind: '$studentList' });
    pipeline.push({ $project: { ...baseCol, name: '$studentList.name', is_pay: '$studentList.is_pay', is_sign: '$studentList.is_sign', icon: '$studentList.icon' } });
    const qp = _.cloneDeep(pipeline);
    if (parseInt(skip)) qp.push({ $skip: parseInt(skip) });
    if (parseInt(limit)) qp.push({ $limit: parseInt(limit) });
    const data = await this.lessonModel.aggregate(qp);
    const tp = _.cloneDeep(pipeline);
    tp.push(this.totalPip());
    const tr = await this.lessonModel.aggregate(tp);
    const total = this.getTotal(tr);
    return { data, total };
  }

  async schoolInByLesson({ school_id, time = 'm', skip = 0, limit }) {
    const tq = this.resetQuery(time, 'time_start');
    const query = { ...tq, school_id };
    const pipeline = [{ $match: query }];
    const baseCol = { time_start: 1, title: 1 };
    // #region 整理课程数据
    pipeline.push({
      $project: {
        ...baseCol,
        money: { $toDouble: '$money' },
      },
    });
    baseCol.money = 1;
    // #endregion
    // #region 正式学员
    pipeline.push({ $addFields: { lesson_id: { $toString: '$_id' } } });
    baseCol.lesson_id = 1;
    pipeline.push({
      $lookup: {
        from: 'lessonStudent',
        localField: 'lesson_id',
        foreignField: 'lesson_id',
        pipeline: [{ $group: { _id: '$lesson_id', num: { $sum: 1 } } }],
        as: 'zStudent',
      },
    });
    pipeline.push({ $set: { zStudent: { $sum: '$zStudent.num' } } });
    pipeline.push({ $project: { ...baseCol, zStudent: { $toDouble: '$zStudent' } } });
    baseCol.zStudent = 1;
    // #endregion
    // #region 临时学员
    pipeline.push({
      $lookup: {
        from: 'tempLessonApply',
        localField: 'lesson_id',
        foreignField: 'lesson_id',
        pipeline: [{ $group: { _id: '$lesson_id', num: { $sum: 1 } } }],
        as: 'lStudent',
      },
    });
    pipeline.push({ $set: { lStudent: { $sum: '$lStudent.num' } } });
    pipeline.push({ $project: { ...baseCol, lStudent: { $toDouble: '$lStudent' } } });
    baseCol.lStudent = 1;
    pipeline.push({ $set: { total: { $multiply: [ '$money', { $add: [ '$zStudent', '$lStudent' ] }] } } });
    // #endregion
    const qp = _.cloneDeep(pipeline);
    if (parseInt(skip)) qp.push({ $skip: parseInt(skip) });
    if (parseInt(limit)) qp.push({ $limit: parseInt(limit) });
    const data = await this.lessonModel.aggregate(qp);
    const tp = _.cloneDeep(pipeline);
    tp.push(this.totalPip());
    const tr = await this.lessonModel.aggregate(tp);
    const total = this.getTotal(tr);
    return { data, total };
  }

  // 聚合总数管道
  totalPip() {
    return {
      $count: 'total',
    };
  }
  /**
   * 取出聚合查询的总数
   * @param {Array} data 聚合查询总数的结果($count)
   * @param {String} key 总数的字段
   * @return {Number} 返回总数
   */
  getTotal(data, key = 'total') {
    return _.get(_.head(data), key, 0);
  }

  resetQuery(time, key = 'time') {
    let query = {};
    const { year, month, lastDate } = this.getPartsOfNow();
    const timeQuery = (start, end) => ({ $and: [{ [key]: { $gte: start } }, { [key]: { $lte: end } }] });
    if (time === 'm') {
      const start = `${year}-${month}-01`;
      const end = `${year}-${month}-${lastDate}`;
      query = { ...query, ...timeQuery(start, end) };
    } else if ([ '3m', '6m' ].includes(time)) {
      const ms = _.head(time.split('')); // 月份数量 3/6/...
      const start = moment().subtract(ms, 'months').format('YYYY-MM-01');
      const { year, month, lastDate } = this.getPartsOfNow();
      const end = `${year}-${month}-${lastDate}`;
      query = { ...query, ...timeQuery(start, end) };
    } else if (time === '1y') {
      const { year } = this.getPartsOfNow();
      const start = `${year}-01-01`;
      const end = `${year}-12-31`;
      query = { ...query, ...timeQuery(start, end) };
    }
    return query;
  }

  // 获取现在时间的各个部分 年月日时分秒 和 当月最后一天是几号
  getPartsOfNow(time = new Date()) {
    const year = moment(time).year();
    let month = moment(time).month() + 1;
    if (month < 10) month = `0${month}`;
    const date = moment(time).date();
    const hour = moment(time).hour();
    const minute = moment(time).minute();
    const second = moment(time).second();
    const lastDate = moment(`${year}-${month}-01`).add(1, 'months').subtract(1, 'days')
      .date();
    return { year, month, date, hour, minute, second, lastDate };
  }
  // 获取两个时间点内的每天
  getEachDay(start, end) {
    const arr = [];
    let i = 0;
    const addDay = (s, i) => moment(s).add(i, 'days').format('YYYY-MM-DD');
    while (!moment(addDay(start, i)).isAfter(end, 'day')) {
      arr.push(addDay(start, i));
      i++;
    }
    return arr;
  }
  // 根据数据获取日期
  getDate(time) {
    const year = moment(time).year();
    let month = moment(time).month() + 1;
    if (month < 10) month = `0${month}`;
    const date = moment(time).date();
    return `${year}-${month}-${date}`;
  }
  // 获取月份第一天的列表, 往前推 pushNum 个月
  getMonthList(pushNum, date) {
    if (!_.isNumber(pushNum)) pushNum = parseInt(pushNum);
    const arr = [];
    const { year, month } = this.getPartsOfNow(date);
    const sDate = `${year}-${month}-01`;
    for (let i = 0; i < pushNum; i++) {
      const { year, month } = this.getPartsOfNow(moment(sDate).subtract(i, 'months'));
      arr.push(`${year}-${month}-01`);
    }
    return arr;
  }
}

module.exports = StatisticsService;