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');

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 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 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 isOutOfDate = this.outOfDate(plan, termid, batchid);
    if (!isOutOfDate) {
      throw new BusinessError(
        ErrorCode.BUSINESS,
        '已经超过上报时间,不允许上报名单'
      );
    }
    // 取得学校预计人数
    const num_ = await this.getschnum(plan, type, schid, termid, batchid);
    console.log('*******************');
    console.log(num_);
    console.log('*******************');
    // 检查学校是否上传过学生
    const schstu = await this.smodel.count({ schid, batchid });
    if (schstu && schstu > 0) throw new BusinessError(ErrorCode.BUSINESS, '该批次已经上传过学生,无需重复上传,若人员有变化,请联系中心负责人');
    const planid = plan.id;
    const planyearid = plan.planyearid;
    // 取得excle中数据
    const _filepath = 'http://127.0.0.1' + filepath; // this.ctx.app.config.baseUrl http://127.0.0.1 http://jytz.jilinjobs.cn
    const studatas = await this.getImportXLSXData(
      _filepath,
      termid,
      schid,
      planid,
      planyearid,
      type,
      batchid
    );
    // 将得到的数据校验
    const datacheck = await this.datacheck(studatas);
    if (datacheck.errorcode === '1') {
      return datacheck;
    }
    const school_ = await this.model.findOne({ code: schid });
    let schname = '';
    if (school_) {
      schname = school_.name;
    }
    const trem_ = await plan.termnum.id(termid);
    if (!trem_) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '期信息不存在');
    }
    const nowtime = moment().locale('zh-cn').format('YYYY-MM-DD HH:mm:ss');
    if (studatas.length > num_) {
      const jobdata = {
        code: schid,
        name: schname,
        planid: plan.id,
        termid,
        term: trem_.term,
        batchid,
        filepath,
        studs: JSON.stringify(studatas),
        plannum: num_,
        schnum: studatas.length,
        isstore: '0',
        createtime: nowtime,
        type,
        reason: '学校上传人数超过预期人数,请联系中心管理员',
      };
      await this.jmodel.create(jobdata);
      throw new BusinessError(
        ErrorCode.SERVICE_FAULT,
        '学校上传人数超过预期人数,请联系中心管理员'
      );
    } else if (studatas.length < num_) {
      const jobdata = {
        code: schid,
        name: schname,
        planid: plan.id,
        termid,
        term: trem_.term,
        batchid,
        filepath,
        studs: JSON.stringify(studatas),
        plannum: num_,
        schnum: studatas.length,
        isstore: '0',
        createtime: nowtime,
        type,
        reason: '学校上传人数少于预期人数,请联系中心管理员',
      };
      await this.jmodel.create(jobdata);
      throw new BusinessError(
        ErrorCode.SERVICE_FAULT,
        '学校上传人数少于预期人数,请联系中心管理员'
      );
    }
    // 将数据存入数据库中
    for (const stu of studatas) {
      const res = await this.smodel.create(stu);
      // if (res) {
      //   const newdata = { name: stu.name, mobile: stu.phone, type: '4', uid: res.id };
      //   newdata.passwd = { secret: '12345678' };
      //   await this.umodel.create(newdata);
      // }
    }
    return datacheck;
  }

  // 取得学校预计人数
  async getschnum(plan, type, schid, termid, 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);
    // const { termnum } = plan;
    // arrange = _.groupBy(arrange, 'termid');
    // const keys = Object.keys(arrange);
    // let arr = keys.map(key => {
    //   const rt = termnum.find(f => ObjectId(key).equals(f._id));
    //   let ar = arrange[key];
    //   ar = ar.map(a => {
    //     const rb = rt.batchnum.find(f => ObjectId(a.batchid).equals(f._id));
    //     if (rb) {
    //       const bh = _.head(rb.class);
    //       const { type } = bh;
    //       a.type = type;
    //       return a;
    //     }
    //   });
    //   let garr = _.groupBy(ar, 'type');
    //   const gks = Object.keys(garr);
    //   garr = gks.map(gk => {
    //     const { term, termid } = _.head(garr[gk]);
    //     const number = garr[gk].reduce((p, n) => p + n.number * 1, 0);
    //     return { term, termid, number, type: gk };
    //   });
    //   return garr;
    // });
    // arr = arr.flat();
    // const obj_ = _.find(arr, { termid, type });
    return obj_.number;
  }

  // 获取导入的XLSX文件中的数据
  async getImportXLSXData(
    filepath,
    termid,
    schid,
    planid,
    planyearid,
    type,
    batchid
  ) {
    const file = await this.ctx.curl(filepath);
    const workbook = XLSX.read(file.data);
    // 读取内容
    let exceldata = [];
    const sheetNames = workbook.SheetNames; // 获取表名
    const sheet = workbook.Sheets[sheetNames[0]]; // 通过表名得到表对象

    // 遍历26个字母
    const theadRule = [];
    const range = XLSX.utils.decode_range(sheet['!ref']);
    const col_start = range.s.c;
    const col_end = range.e.c;
    for (let i = col_start; i <= col_end; i++) {
      const addr = XLSX.utils.encode_col(i) + XLSX.utils.encode_row(0);
      theadRule.push(sheet[addr].v);
    }
    // const theadRule = [ sheet.A1.v, sheet.B1.v, sheet.C1.v, sheet.D1.v, sheet.E1.v, sheet.F1.v, sheet.G1.v, sheet.H1.v, sheet.I1.v, sheet.J1.v, sheet.K1.v, sheet.L1.v, sheet.M1.v, sheet.N1.v, sheet.O1.v, sheet.P1.v, sheet.Q1.v, sheet.R1.v ];
    const params = XLSX.utils.sheet_to_json(sheet); // 通过工具将表对象的数据读出来并转成json
    // const theadRule = [ '序号', '姓名', '性别', '民族', '身份证号', '学校名称', '院系', '专业', '入学年份', '毕业年份', '在校曾担任何种职务', '手机号', 'QQ号', '家庭所在地', '家庭是否困难', '是否获得过助学金' ];
    if (!params) return [];
    let i = 0;
    const length = params.length;
    const _datas = [];
    let data = {};
    for (i; i < length; i++) {
      data = params[i];
      const diy_ = [];
      if (theadRule.length > 18) {
        for (let j = 18; j < theadRule.length; j++) {
          const newdata = {
            itemname: theadRule[j],
            itemvalue: data[theadRule[j]],
          };
          diy_.push(newdata);
        }
      }
      _datas.push({
        name: _.trim(data[theadRule[1]]),
        gender: _.trim(data[theadRule[2]]),
        nation: _.trim(data[theadRule[3]]),
        id_number: _.trim(data[theadRule[4]]),
        school_name: _.trim(data[theadRule[5]]),
        faculty: _.trim(data[theadRule[6]]),
        major: _.trim(data[theadRule[7]]),
        entry_year: _.trim(data[theadRule[8]]),
        finish_year: _.trim(data[theadRule[9]]),
        school_job: _.trim(data[theadRule[10]]),
        phone: _.trim(data[theadRule[11]]),
        qq: _.trim(data[theadRule[12]]),
        family_place: _.trim(data[theadRule[13]]),
        family_is_hard: _.trim(data[theadRule[14]]),
        have_grant: _.trim(data[theadRule[15]]),
        edua_level: _.trim(data[theadRule[16]]),
        edua_system: _.trim(data[theadRule[17]]),
        diy: diy_,
        termid,
        batchid,
        schid,
        planid,
        planyearid,
        type,
      });
    }
    exceldata = [ ...exceldata, ..._datas ];

    return exceldata;
  }

  // 获取导入的XLSX文件中的数据
  async datacheck(studatas) {
    let errorcode = '0';
    const errormsg = [];
    for (const data of studatas) {
      // 判断是否为空
      if (!data.name) {
        errorcode = '1';
        data.msg = (data.msg || '') + '姓名不允许为空;';
      }
      if (!data.gender) {
        errorcode = '1';
        data.msg = (data.msg || '') + '性别不允许为空;';
      }
      // 判断性别是否是1个字
      if (data.gender.length !== 1) {
        errorcode = '1';
        data.msg(data.msg || '') + '性别内容超出1个字.可能含有空格;';
      }
      if (!data.nation) {
        errorcode = '1';
        data.msg = (data.msg || '') + '民族不允许为空;';
      }
      if (!data.id_number) {
        errorcode = '1';
        data.msg = (data.msg || '') + '身份证号不允许为空;';
      } else {
        const { pass, msg } = this.idCodeValid(data.id_number);
        if (!pass) {
          errorcode = '1';
          data.msg = (data.msg || '') + `${msg},`;
        }
      }
      if (!data.school_name) {
        errorcode = '1';
        data.msg = (data.msg || '') + '学校名称不允许为空;';
      }
      if (!data.phone) {
        errorcode = '1';
        data.msg = (data.msg || '') + '手机号不允许为空;';
      }
      if (!data.faculty) {
        errorcode = '1';
        data.msg = (data.msg || '') + '院系不允许为空;';
      }
      if (!data.major) {
        errorcode = '1';
        data.msg = (data.msg || '') + '专业不允许为空;';
      } else {
        // 限制专业字段中不能含有 '专业' 字样
        if (data.major.includes('专业')) {
          errorcode = '1';
          data.msg = (data.msg || '') + '专业列不能含有"专业"二字;';
        }
      }

      if (!data.entry_year) {
        errorcode = '1';
        data.msg = (data.msg || '') + '入学年份不允许为空;';
      } else {
        const m = /^\w{4}$/;
        if (!data.entry_year.match(m)) {
          errorcode = '1';
          data.msg = (data.msg || '') + '入学年份格式不正确,只填写4位数字即可;';
        }
      }
      // 限制是4位数字
      if (!data.finish_year) {
        errorcode = '1';
        data.msg = (data.msg || '') + '毕业年份不允许为空;';
      } else {
        const m = /^\w{4}$/;
        if (!data.finish_year.match(m)) {
          errorcode = '1';
          data.msg = (data.msg || '') + '毕业年份格式不正确,只填写4位数字即可;';
        }
      }
      if (!data.school_job) {
        errorcode = '1';
        data.msg = (data.msg || '') + '职务不允许为空;';
      }
      if (!data.qq) {
        errorcode = '1';
        data.msg = (data.msg || '') + 'QQ号不允许为空;';
      }
      if (!data.family_place) {
        errorcode = '1';
        data.msg = (data.msg || '') + '家庭所在地不允许为空;';
      }
      if (!data.family_is_hard) {
        errorcode = '1';
        data.msg = (data.msg || '') + '家庭是否困难不允许为空;';
      }
      if (!data.have_grant) {
        errorcode = '1';
        data.msg = (data.msg || '') + '是否获得过助学金不允许为空;';
      }
      if (!/^\d{11}$/i.test(data.phone)) {
        errorcode = '1';
        data.msg = (data.msg || '') + '手机号不正确;';
      }
      const res = await this.smodel.findOne({ id_number: data.id_number });
      if (res) {
        errorcode = '1';
        data.msg = (data.msg || '') + '学生已经存在请检查;';
      }
      if (errorcode === '1') {
        errormsg.push(data);
      }
    }
    return { errorcode, errormsg };
  }

  // 导出学校名单
  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();
    const path =
      'D:\\wwwroot\\service\\service-file\\upload\\train\\' + nowDate + '.xlsx';
    const respath =
      'http://free.liaoningdoupo.com:80/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;
  }
  // 判断是否超出该期前3天
  outOfDate(plan, termid, batchid) {
    const term = plan.termnum.find(f => ObjectId(termid).equals(f._id));
    const { batchnum } = term;
    if (!batchnum) throw new BusinessError(ErrorCode.BUSINESS, '没有找到该期下的批次');
    const batch = batchnum.find(f => ObjectId(batchid).equals(f._id));
    if (!batch) throw new BusinessError(ErrorCode.BUSINESS, '没有找到该批次');
    // let startList = batchnum.map(i => ({ start: i.startdate }));
    // startList = _.orderBy(startList, [ 'start' ], [ 'asc' ]);
    // const start = _.get(_.head(startList), 'start');
    const start = _.get(batch, 'startdate');
    if (!start) throw new BusinessError(ErrorCode.BUSINESS, '没有找到开始日期');
    const limit = moment(start).subtract(3, 'days').format('YYYY-MM-DD');
    const now = moment().format('YYYY-MM-DD');
    const res = moment(now).isBefore(limit);
    return res;
  }
}

module.exports = SchoolService;