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, is_sign: 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, '[]'));
      let total = 0;
      let is_signed = 0;
      for (const lc of list) {
        if (lc.is_sign === '1') is_signed++;
        total++;
      }
      obj.total = total;
      obj.is_sign = is_signed;
      obj.value = `${_.floor(_.divide(is_signed, total) * 100) || 0}%`;
      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', 'age');
    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, 'sstudent_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) - 退款(至余额,2) 且 is_pay 为 1
   * m:当前这个月; 3m: 往前推3个月; 6m:往前推6个月; 1y:当前年
   * @param {Object} query 查询条件
   * @property {String} school_id 学校id
   * @property {String} time 时间 m:月;3m:3月;6m:6个月; 1y:1年
   */
  async schoolTotalIn({ school_id, time }) {
    assert(school_id, '缺少羽校信息');
    assert(time, '缺少时间范围');
    let query = { is_pay: '1', $or: [{ type: '-1' }, { type: '-2' }, { type: '2' }], school_id };
    const { year, month, lastDate } = this.getPartsOfNow();
    const timeQuery = (start, end) => ({ $and: [{ time: { $gte: start } }, { time: { $lte: end } }] });
    let xList;
    const yList = [];
    if (time === 'm') {
      // 这个月,按天查
      const start = `${year}-${month}-01`;
      const end = `${year}-${month}-${lastDate}`;
      query = { ...query, ...timeQuery(start, end) };
      xList = this.getEachDay(start, end);
    } else if ([ '3m', '6m' ].includes(time)) {
      const ms = _.head(time.split('')); // 月份数量 3/6/...
      xList = this.getMonthList(ms);
      const start = _.last(xList);
      const { year, month, lastDate } = this.getPartsOfNow(_.head(xList));
      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`;
      xList = this.getMonthList(12, end);
      query = { ...query, ...timeQuery(start, end) };
    }

    let billList = await this.billModel.find(query, { pay_for: 1, from_id: 1, type: 1, money: 1, time: 1, payer_id: 1 });
    if (billList.length > 0) billList = JSON.parse(JSON.stringify(billList));
    for (const i of billList) {
      const { time } = i;
      const date = this.getDate(time);
      i.date = date;
    }
    if (time === 'm') {
      for (const x of xList) {
        const date = x;
        const list = billList.filter(f => f.date === date);
        const inList = list.filter(f => f.type !== '2');
        const outList = list.filter(f => f.type === '2');
        const inTotal = inList.reduce((p, n) => p + (n.money || 0), 0);
        const outTotal = outList.reduce((p, n) => p + (n.money || 0), 0);
        const total = inTotal - outTotal;
        yList.push(total);
      }
      xList = xList.map(i => {
        const { date } = this.getPartsOfNow(i);
        return `${date}日`;
      });
    } else if ([ '3m', '6m', '1y' ].includes(time)) {
      for (const x of xList) {
        const start = x;
        const { year, month, lastDate } = this.getPartsOfNow(x);
        const end = `${year}-${month}-${lastDate}`;
        const list = billList.filter(f => moment(f.date).isBetween(start, end, null, '[]'));
        const inList = list.filter(f => f.type !== '2');
        const outList = list.filter(f => f.type === '2');
        const inTotal = inList.reduce((p, n) => p + (n.money || 0), 0);
        const outTotal = outList.reduce((p, n) => p + (n.money || 0), 0);
        const total = inTotal - outTotal;
        yList.unshift(total);
      }
      xList = xList.map(i => {
        const { month } = this.getPartsOfNow(i);
        return `${parseInt(month)}月`;
      });
      xList = _.reverse(xList);
    }
    console.log('line 189 in function:');
    return { x: xList, y: yList };
  }
  // 获取现在时间的各个部分 年月日时分秒 和 当月最后一天是几号
  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;