service-center/app/service/school.js

'use strict';

const assert = require('assert');
const _ = require('lodash');
const { ObjectId } = require('mongoose').Types;
const { CrudService } = require('naf-framework-mongoose/lib/service');
const { BusinessError, ErrorCode } = require('naf-core').Error;
const moment = require('moment');
const XLSX = require('xlsx');
const Excel = require('exceljs');
const { sep } = require('path');

class SchoolService extends CrudService {
  constructor(ctx) {
    super(ctx, 'schoolctrl');
    this.model = this.ctx.model.School;
    this.smodel = this.ctx.model.Student;
    this.umodel = this.ctx.model.User;
    this.tmodel = this.ctx.model.Trainplan;
    this.jmodel = this.ctx.model.Job;
    this.schmodel = this.ctx.model.Schtime;
  }
  async findByCodes({ code }) {
    if (!_.isArray(code) || code.length <= 0) return [];
    const res = await this.model.find({ code }, { name: 1, code: 1, level: 1, hascar: 1, address: 1 });
    return res;
  }
  async create(data) {
    const { code, name } = data;
    assert(code, '缺少学校代码');
    assert(name, '缺少学校名称');
    const res = await this.model.create(data);
    if (res) {
      const obj = {
        mobile: code,
        name,
        type: '2',
        uid: res._id,
        passwd: { secret: '12345678' },
      };
      await this.umodel.create(obj);
    }
    return res;
  }
  async delete({ id }) {
    await this.model.findByIdAndDelete(id);
    await this.umodel.deleteOne({ uid: id, type: '2' });
    return 'deleted';
  }

  async query({ name, ...data }, { skip, limit }) {
    const query = { ...data };
    if (name) {
      query.name = { $regex: name };
    }
    let res = await this.model.find(query).skip(parseInt(skip)).limit(parseInt(limit));
    if (res && res.length > 0) {
      res = JSON.parse(JSON.stringify(res));
      const ids = res.map((i) => i._id);
      const users = await this.umodel.find({ uid: { $in: ids } }, '+passwd');
      for (const tea of res) {
        const r = users.find((f) => f.uid === tea._id);
        if (r) {
          const passwd = _.get(r.passwd, 'secret');
          if (passwd) tea.passwd = passwd;
        }
      }
    }
    return res;
  }

  async count({ name, ...data } = {}) {
    const query = { ...data };
    if (name) {
      query.name = { $regex: name };
    }
    return await this.model.count(query);
  }

  // 去重查询学校
  async findSchool({ name, skip, limit, ...info }) {
    let data = [];
    const query = { ...info };
    if (name) {
      query.name = { $regex: name };
    }
    const AggregateInfo = [
      // 这里可以添加其他查询条件，例如过滤特定字段等
      { $match: query },
      // 去除重复数据
      { $group: { _id: '$code', uniqueData: { $first: '$$ROOT' } } },
      { $replaceRoot: { newRoot: '$uniqueData' } },
      { $addFields: { id: '$_id' } },
      { $sort: { code: 1 } },
      // 分页查询
      { $skip: parseInt(skip) },
    ];
    if (limit) AggregateInfo.push({ $limit: parseInt(limit) });
    const res = await this.model.aggregate(AggregateInfo);
    if (res && res.length > 0) {
      data = JSON.parse(JSON.stringify(res));
      const ids = res.map((i) => i._id);
      const users = await this.umodel.find({ uid: { $in: ids } }, '+passwd').lean();
      for (const tea of data) {
        const r = users.find((f) => f.uid === tea._id);
        if (r) {
          const passwd = _.get(r.passwd, 'secret');
          if (passwd) tea.passwd = passwd;
        }
      }
    }
    const count = await this.model.aggregate([{ $match: query }, { $group: { _id: '$code' } }, { $count: 'distinctCount' }]);
    const total = _.get(count[0], 'distinctCount') || 0;
    return { total, data };
  }

  async stuimport(data) {
    const { filepath, termid, schid, type, batchid } = data;
    assert(filepath, 'filepath不能为空');
    assert(termid, 'termid不能为空');
    assert(schid, 'schid不能为空');
    // 根据termid取得计划信息
    const plan = await this.tmodel.findOne({ 'termnum._id': ObjectId(termid) });
    if (!plan) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '计划信息不存在');
    }
    const term = plan.termnum.id(termid);
    const planid = plan.id;
    const planyearid = plan.planyearid;
    // 检查这个范围的学生是否存在,存在的话是否更改过(classid,bedroomid这两项存不存在可以放过,但凡有一个人,就不行了)
    let dbStuList = await this.ctx.model.Student.find({ termid, batchid, schid });
    if (dbStuList.length > 0) {
      // 查这个学校的这期学生是否修改过班级 或 寝室
      const is_change = dbStuList.find((f) => f.classid ); // || 2024-07-05 修改:f.bedroomid去掉,因为现在不在中心培训了,不管寝室了
      if (is_change) {
        throw new BusinessError(
          ErrorCode.BUSINESS,
          '上报过的学生已经安排班级!若需要替换学生,让同性别的学生直接来和班主任说,修改信息即可.若还是有疑问,请和中心负责人联系(最好联系下)', // 或寝室
        );
      }
    }
    // 2021-06-07 如果学生已经绑定,那也不允许修改名单了
    const countOpenid = await this.ctx.model.Student.count({ termid, batchid, schid, openid: { $exists: true } });
    if (countOpenid > 0) throw new BusinessError(ErrorCode.BUSINESS, '已有学生绑定账号,名单无法修改.若有问题请联系中心负责人!');
    // 获取学校名称
    let school_name;
    const sch = await this.ctx.model.School.findOne({ code: schid });
    if (sch) school_name = sch.name;
    let domain = 'http://127.0.0.1';
    if (process.env.NODE_ENV === 'development') domain = 'http://jytz.jilinjobs.cn';
    const fullUrl = domain + filepath; // this.ctx.app.config.baseUrl http://127.0.0.1 http://jytz.jilinjobs.cn
    let studentList = await this.getDataFromExcel(fullUrl);
    const checkRes = await this.checkData(studentList);
    const { errorcode } = checkRes;
    if (errorcode === '1') {
      return checkRes;
    }
    // 2021-05-26 添加与数据库的对比,如果数据库里已经有这个身份证号,就需要提示
    const countStudent = await this.countStudent(studentList, planid);
    const { errorcode: csec } = countStudent;
    if (csec === '1') {
      return countStudent;
    }
    // 整理数据
    studentList = this.lastSetData(studentList, {
      planyearid,
      planid,
      batchid,
      termid,
      type,
      schid,
      school_name,
    });
    const num = await this.getschnum(plan, schid, batchid);
    // 查看要求人数和整理完最后的人数能不能对上
    if (studentList.length !== num) {
      const res = await this.jmodel.findOne({ code: schid, batchid });
      const reason = `学校上传人数${studentList.length > num ? '多于' : '少于'}预期人数,请联系中心管理员`;
      if (res) {
        res.reason = reason;
        res.filepath = filepath;
        await res.save();
      } else {
        const job = {
          code: schid,
          name: school_name,
          planid,
          termid,
          term: term.term,
          batchid,
          filepath,
          studs: JSON.stringify(studentList),
          plannum: num,
          schnum: studentList.length,
          isstore: '0',
          createtime: moment().format('YYYY-MM-DD HH:SS:mm'),
          type,
        };
        job.reason = reason;
        await this.jmodel.create(job);
      }

      throw new BusinessError(ErrorCode.SERVICE_FAULT, reason);
    } else {
      // 复制,删除,添加
      if (dbStuList.length > 0) {
        dbStuList = JSON.parse(JSON.stringify(dbStuList));
        dbStuList = dbStuList.map((i) => {
          delete i.meta;
          i.studentid = _.clone(i._id);
          delete i.id;
          delete i._id;
          return i;
        });
        await this.smodel.deleteMany({ termid, batchid, schid });
        await this.ctx.model.Dstudent.insertMany(dbStuList);
      }

      await this.smodel.insertMany(studentList);
    }
    return 'ok';
  }

  /**
   * 检查学生是否参加过这个计划以外的计划,参加过就不让来了
   * @param {Array} studentList 学生列表
   * @param {String} planid 计划id
   */
  async countStudent(studentList, planid) {
    let errorcode = '0';
    const errormsg = [];
    for (const stu of studentList) {
      const { name, id_number } = stu;
      let error = false;
      let msg = '';
      const count = await this.smodel.count({ id_number, planid: { $ne: planid } });
      if (count > 0) {
        error = true;
        msg = `${msg}${name}已经参加过培训`;
      }
      if (error) {
        errorcode = '1';
        stu.msg = msg;
        errormsg.push(stu);
      }
    }

    return { errorcode, errormsg };
  }

  // 取得学校预计人数
  async getschnum(plan, schid, batchid) {
    const schtime = await this.schmodel.findOne({ schid, planid: plan.id });
    const { arrange } = schtime;
    const r = arrange.find((f) => f.batchid === batchid);
    if (!r) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '没有找到该学校的计划');
    }
    const { number } = r;
    return parseInt(number);
  }
  // 整理excel数据
  async getDataFromExcel(url) {
    // 请求文件
    const file = await this.ctx.curl(`${url}`);
    if (!(file && file.data)) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '未找到上传的名单');
    }
    const workbook = new Excel.Workbook();
    // 读取文件
    await workbook.xlsx.load(file.data);
    const worksheet = workbook.getWorksheet(1);
    if (!worksheet) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '未发现excel中有工作表');
    }
    // 获取表头,通过方法的返回值,将写死的表头数组返回 回来
    const cols = this.getStucolumn();
    // 第一行(表头)
    const headRow = worksheet.getRow(1);
    // 设置,检查表头
    headRow.eachCell((cell, coli) => {
      console.log(cell.value);
      if (cell.value !== '序号') {
        const r = cols.find((f) => f.key === cell.value);
        if (r) {
          const ri = cols.findIndex((f) => f.key === cell.value);
          // 表头符合要求,做上标记
          r.colIndex = coli;
          cols[ri] = r;
        } else {
          throw new BusinessError(`模板中"${cell.value}"列错误,请检查excel!`);
        }
      }
    });
    // 检查表头结果,如果有没有 colIndex,说明表头里有不符合要求的,退回去
    const excelIsRigth = cols.every((f) => f.colIndex);
    if (!excelIsRigth) throw new BusinessError(ErrorCode.DATA_INVALID, 'Excel表格格式不正确,请使用系统提供的模板,或重新下载模板!');
    // 删除掉第一行 表头行,这不是数据
    worksheet.spliceRows(0, 1);
    const stuList = [];
    const noWhite = (str) => str.replace(/\s*/g, '');
    // 整理数据,根据检查合格的表头行,获取每个格子的数据,制成[object]格式
    worksheet.eachRow((row) => {
      const stu = {};
      for (let i = 0; i < cols.length; i++) {
        const col = cols[i];
        if (!col) break;
        let val = _.trim(row.getCell(col.colIndex));
        if (col.column === 'id_number') val = val.toUpperCase();
        if (val && val !== '') val = noWhite(val);
        stu[col.column] = val;
      }
      stuList.push(stu);
    });
    return stuList;
  }
  // 数据校验
  async checkData(stuList) {
    const cols = this.getStucolumn();
    let errorcode = '0';
    const errormsg = [];
    for (const stu of stuList) {
      const { name } = stu;
      let error = false;
      let msg = '';
      // 各个字段检查,最低为非空检查
      for (const col of cols) {
        const { key, column } = col;
        if (!column) throw new BusinessError(ErrorCode.SERVICE_FAULT, '未找到导出的字段名');
        const val = _.get(stu, column);
        // 空校验
        if (!val || val === '') {
          error = true;
          msg = `${msg}"${key}"不能为空;`;
          continue;
        }
        // 性别校验
        if (column === 'gender') {
          if (!(val.includes('男') || val.includes('女'))) {
            error = true;
            msg = `${msg}性别错误;`;
          }
          continue;
        }
        // 身份证号校验
        if (column === 'id_number') {
          // 因为删除再添加的流程导致此处 不能 校验数据库中是否有这个身份证号
          // const res = await this.ctx.model.Student.findOne({ id_number: val });
          // if (!res) {
          const { pass, msg: idmsg } = this.ctx.service.school.idCodeValid(val);
          if (!pass) {
            error = true;
            msg = `${msg}${idmsg};`;
          }
          // } else {
          //   error = true;
          //   msg = `${msg}学生已存在`;
          // }
          const have_same = stuList.filter((f) => f.id_number === val && f.name !== name);
          if (have_same.length > 0) {
            error = true;
            const h = _.head(have_same);
            const num = have_same.length;
            if (num === 1) {
              msg = `${msg}身份证号与本次名单的"${h.name}"重复;`;
            } else msg = `${msg}身份证号与本次名单中"${h.name}"等${num}人重复;`;
          }
          continue;
        }
        // 手机号校验
        if (column === 'phone') {
          // 因为删除再添加的流程导致此处 不能 校验数据库中是否有这个手机号
          // const res = await this.ctx.model.Student.findOne({ phone: val });
          // if (!res) {
          if (!/^\d{11}$/i.test(val)) {
            error = true;
            msg = `${msg}手机号位数不正确;`;
          }
          // } else {
          //   error = true;
          //   msg = `${msg}学生库中已有该手机号,请检查手机号是否正确,若无误,请联系中心负责人`;
          // }
          const have_same = stuList.filter((f) => f.phone === val && f.name !== name);
          if (have_same.length > 0) {
            error = true;
            const h = _.head(have_same);
            const num = have_same.length;
            if (num === 1) {
              msg = `${msg}手机号与本次名单的"${h.name}"重复;`;
            } else msg = `${msg}手机号与本次名单中"${h.name}"等${num}人重复;`;
          }
          continue;
        }
        // 专业校验
        if (column === 'major') {
          if (val.includes('专业')) {
            error = true;
            msg = `${msg}专业列不能含有"专业"二字;`;
          }
          continue;
        }
        // 入学年份
        if (column === 'entry_year') {
          const m = /^\w{4}$/;
          if (!val.match(m)) {
            error = true;
            msg = `${msg}入学年份格式不正确,只填写4位数字;`;
          }
          continue;
        }
        // 毕业年份
        if (column === 'finish_year') {
          const m = /^\w{4}$/;
          if (!val.match(m)) {
            error = true;
            msg = `${msg}毕业年份格式不正确,只填写4位数字;`;
          }
          continue;
        }
        // 双困检查
        if (column === 'family_is_hard') {
          if (!(val.includes('是') || val.includes('否'))) {
            error = true;
            msg = `${msg}家庭是否困难填写"是"或"否";`;
          }
          continue;
        }
        if (column === 'have_grant') {
          if (!(val.includes('是') || val.includes('否'))) {
            error = true;
            msg = `${msg}是否获得过助学金填写"是"或"否";`;
          }
          continue;
        }
      }
      if (error) {
        errorcode = '1';
        stu.msg = msg;
        errormsg.push(stu);
      }
    }
    return { errorcode, errormsg };
  }

  // 最后整合数据
  lastSetData(stuList, data) {
    const cols = this.getStucolumn();
    const needChange = cols.filter((f) => f.change);
    stuList = stuList.map((i) => {
      const d = { ...i, ...data };
      for (const col of needChange) {
        const { column, change } = col;
        if (!column && change && _.isArray(change)) continue;
        const val = _.get(d, column);
        if (!val) continue;
        const r = change.find((f) => f.key === val);
        if (!r) continue;
        const { value } = r;
        d[column] = value;
      }
      return d;
    });
    return stuList;
  }
  // excel中学生字段
  getStucolumn() {
    const arr = [
      { key: '姓名', column: 'name' },
      { key: '性别', column: 'gender' },
      { key: '民族', column: 'nation' },
      { key: '身份证号', column: 'id_number' },
      { key: '学校名称', column: 'school_name' },
      { key: '学历层次', column: 'edua_level' },
      { key: '学制', column: 'edua_system' },
      { key: '院（系）', column: 'faculty' },
      { key: '专业', column: 'major' },
      { key: '入学年份', column: 'entry_year' },
      { key: '毕业年份', column: 'finish_year' },
      { key: '在校曾担任何种职务', column: 'school_job' },
      { key: '手机号', column: 'phone' },
      { key: 'QQ号', column: 'qq' },
      { key: '家庭所在地', column: 'family_place' },
      {
        key: '家庭是否困难',
        column: 'family_is_hard',
        change: [
          { key: '否', value: '0' },
          { key: '是', value: '1' },
        ],
      },
      {
        key: '是否获得过助学金',
        column: 'have_grant',
        change: [
          { key: '否', value: '0' },
          { key: '是', value: '1' },
        ],
      },
    ];
    return arr;
  }

  // 导出学校名单
  async exportSchool({ trainplanId }) {
    // 批次期次都在这里面
    const trainplan = await this.tmodel.find({ _id: trainplanId });
    const _headers = [{ key: 'title', title: '计划标题' }];
    // 需要打出的列表
    const _data = trainplan;
    const headers = _headers
      .map(({ title }) => title)
      .map((v, i) => Object.assign({}, { v, position: String.fromCharCode(65 + i) + 1 }))
      .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { v: next.v } }), {});

    const data = _data
      .map((v, i) => _headers.map(({ key }, j) => Object.assign({}, { v: v[key], position: String.fromCharCode(65 + j) + (i + 2) })))
      .reduce((prev, next) => prev.concat(next))
      .reduce((prev, next) => Object.assign({}, prev, { [next.position]: { v: next.v } }), {});

    // 合并 headers 和 data
    const output = Object.assign({}, headers, data);

    // 获取所有单元格的位置
    const outputPos = Object.keys(output);

    // 计算出范围
    const ref = outputPos[0] + ':' + outputPos[outputPos.length - 1];

    // 构建 workbook 对象
    const nowDate = new Date().getTime();
    // 地址有问题,需要引用config变量
    const { repos_root_path: rp } = this.ctx.app.config.cdn;
    const { baseUrl: bu } = this.ctx.app.config;
    const path = `${rp}${sep}train${sep}${nowDate}.xlsx`;
    const respath = `${bu}/files/train/${nowDate}.xlsx`;
    const wb = {
      SheetNames: ['sheet0'],
      Sheets: { sheet0: Object.assign({}, output, { '!ref': ref }) },
    };
    // 导出 Excel
    XLSX.writeFile(wb, path);
    return respath;
  }

  async updateclass({ trainplanid, classid, rightHeader }) {
    assert(trainplanid && classid && rightHeader, '缺少参数项');
    // 根据全年计划表id查出对应的全年计划详细信息
    const trainplan = await this.model.findById(trainplanid);
    if (!trainplan) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '全年计划信息不存在');
    }
    for (const term of trainplan.termnum) {
      for (const batch of term.batchnum) {
        const class_ = await batch.class.id(classid);
        if (class_) {
          class_.headteacherid = rightHeader;
        }
      }
    }
    return await trainplan.save();
  }

  async updatereteacher({ trainplanid, termid, reteacher }) {
    assert(trainplanid && termid && reteacher, '缺少参数项');
    // 根据全年计划表id查出对应的全年计划详细信息
    const trainplan = await this.model.findById(trainplanid);
    if (!trainplan) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '全年计划信息不存在');
    }
    const term = await trainplan.termnum.id(termid);
    if (term) {
      term.reteacher = reteacher;
    }
    return await trainplan.save();
  }
  // 身份证验证
  idCodeValid(code) {
    // 身份证号合法性验证
    // 支持15位和18位身份证号
    // 支持地址编码、出生日期、校验位验证
    const city = {
      11: '北京',
      12: '天津',
      13: '河北',
      14: '山西',
      15: '内蒙古',
      21: '辽宁',
      22: '吉林',
      23: '黑龙江 ',
      31: '上海',
      32: '江苏',
      33: '浙江',
      34: '安徽',
      35: '福建',
      36: '江西',
      37: '山东',
      41: '河南',
      42: '湖北 ',
      43: '湖南',
      44: '广东',
      45: '广西',
      46: '海南',
      50: '重庆',
      51: '四川',
      52: '贵州',
      53: '云南',
      54: '西藏 ',
      61: '陕西',
      62: '甘肃',
      63: '青海',
      64: '宁夏',
      65: '新疆',
      71: '台湾',
      81: '香港',
      82: '澳门',
      91: '国外 ',
    };
    let row = {
      pass: true,
      msg: '验证成功',
    };
    if (!code || !/^\d{6}(18|19|20)?\d{2}(0[1-9]|1[012])(0[1-9]|[12]\d|3[01])\d{3}(\d|[xX])$/.test(code)) {
      row = {
        pass: false,
        msg: '身份证号格式错误',
      };
    } else if (!city[code.substr(0, 2)]) {
      row = {
        pass: false,
        msg: '身份证号地址编码错误',
      };
    } else {
      // 18位身份证需要验证最后一位校验位
      if (code.length === 18) {
        code = code.split('');
        // ∑(ai×Wi)(mod 11)
        // 加权因子
        const factor = [7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2];
        // 校验位
        const parity = [1, 0, 'X', 9, 8, 7, 6, 5, 4, 3, 2];
        let sum = 0;
        let ai = 0;
        let wi = 0;
        for (let i = 0; i < 17; i++) {
          ai = code[i];
          wi = factor[i];
          sum += ai * wi;
        }
        if (parity[sum % 11] != code[17].toUpperCase()) {
          row = {
            pass: false,
            msg: '身份证号校验位错误',
          };
        }
      }
    }
    return row;
  }
}

module.exports = SchoolService;