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 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;
  }

  async stuimport(data) {
    const { filepath, termid, schid } = 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 planid = plan.id;
    const planyearid = plan.planyearid;
    // 取得excle中数据
    const _filepath = this.ctx.app.config.baseUrl + filepath;
    console.log(_filepath);
    const studatas = await this.getImportXLSXData(_filepath, termid, schid, planid, planyearid);
    console.log(studatas);
    // 将得到的数据校验
    const datacheck = await this.datacheck(studatas);
    if (datacheck.errorcode === '1') {
      return datacheck;
    }
    // 将数据存入数据库中
    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;
  }

  // 获取导入的XLSX文件中的数据
  async getImportXLSXData(filepath, termid, schid, planid, planyearid) {
    console.log(filepath);
    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: data[theadRule[1]],
        gender: data[theadRule[2]],
        nation: data[theadRule[3]],
        id_number: data[theadRule[4]],
        school_name: data[theadRule[5]],
        faculty: data[theadRule[6]],
        major: data[theadRule[7]],
        entry_year: data[theadRule[8]],
        finish_year: data[theadRule[9]],
        school_job: data[theadRule[10]],
        phone: data[theadRule[11]],
        qq: data[theadRule[12]],
        family_place: data[theadRule[13]],
        family_is_hard: data[theadRule[14]],
        have_grant: data[theadRule[15]],
        edua_level: data[theadRule[16]],
        edua_system: data[theadRule[17]],
        diy: diy_,
        termid,
        schid,
        planid,
        planyearid,
      });
    }
    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 + '性别不允许为空，';
      }
      if (!data.id_number) {
        errorcode = '1';
        data.msg = data.msg + '身份证号不允许为空，';
      }
      if (!data.school_name) {
        errorcode = '1';
        data.msg = data.msg + '学校名称不允许为空，';
      }
      if (!data.phone) {
        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 };
  }
}

module.exports = SchoolService;