service-center/app/service/util.js

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

class UtilService extends CrudService {
  constructor(ctx) {
    super(ctx);
    this.mq = this.ctx.mq;
    this.sModel = this.ctx.model.School;
  }
  async updatedate() {
    let date = new Date();
    date = moment(date).format('YYYY-MM-DD HH:mm:ss');
    return date;
  }

  async sendMail(email, subject, text, html) {
    const setting = await this.ctx.model.Setting.findOne();
    let user_email = this.ctx.app.config.user_email;
    let auth_code = this.ctx.app.config.auth_code;
    if (setting) {
      user_email = setting.user_email;
      auth_code = setting.auth_code;
    }
    const transporter = nodemailer.createTransport({
      host: 'smtp.exmail.qq.com',
      secureConnection: true,
      port: 465,
      auth: {
        user: user_email, // 账号
        pass: auth_code, // 授权码
      },
    });
    if (process.env.NODE_ENV === 'development') email = '402788946@qq.com';
    const mailOptions = {
      from: user_email, // 发送者,与上面的user一致
      to: email, // 接收者,可以同时发送多个,以逗号隔开
      subject, // 标题
      text, // 文本
      html,
    };

    try {
      await transporter.sendMail(mailOptions);
      return true;
    } catch (err) {
      return false;
    }
  }

  async findone({ modelname }, data) {
    // 查询单条
    const _model = _.capitalize(modelname);
    const res = await this.ctx.model[_model].findOne({ ...data });
    return res;
  }

  async findbyids({ modelname }, { data }) {
    // 共通批量查询方法
    const _model = _.capitalize(modelname);
    const res = [];
    for (const elm of data) {
      const result = await this.ctx.model[_model].findById(elm);
      res.push(result);
    }
    return res;
  }
  async findmodel({ modelname }) {
    const _model = _.capitalize(modelname);
    const data = this.ctx.model[_model].prototype.schema.obj;
    const keys = Object.keys(data);
    const res = {};
    for (const k of keys) {
      const obj = data[k];
      if (_.get(obj, 'zh')) res[k] = obj;
    }
    return res;
  }
  async utilMethod(query, body) {
    const crypto = require('crypto');
    // const { publicKey, privateKey } = crypto.generateKeyPairSync('rsa', {
    //   modulusLength: 2048,
    //   publicKeyEncoding: {
    //     type: 'spki',
    //     format: 'pem',
    //   },
    //   privateKeyEncoding: {
    //     type: 'pkcs8',
    //     format: 'pem',
    //     cipher: 'aes-256-cbc',
    //     passphrase: 'sks_secret',
    //   },
    // });
    // console.log(publicKey);
    // console.log('-------------------');
    // console.log(privateKey);
    const privateKey = `-----BEGIN ENCRYPTED PRIVATE KEY-----
MIIFLTBXBgkqhkiG9w0BBQ0wSjApBgkqhkiG9w0BBQwwHAQIDrmClOodJAACAggA
MAwGCCqGSIb3DQIJBQAwHQYJYIZIAWUDBAEqBBDFUSMr1yfAK9Lt9r4Idi5EBIIE
0JmkwwNWz+ya4IffVvzQS2fEbIRoAcxoBEKaZjLd2cCBQbE/0BODdlEWcJ7Z4Z3k
1NfDaqQToh+k9sE2C108q2jCETLiLPWodTMAMO0pVHBiiJeh2ZOoDxwyDWsyHYcA
6S7EhuQPOY7Fd8WKWeYcH1m8TLyL40fmWq0cseCoyvP1M0LRQ5OFKD7+uOYJBQhq
7pPwuJouorKMUHbxpribrOmr8fspfkglhP6+TZ7p7XzAs6e92mNz1SfiflmChoPH
sqcJb0SJaHCdzblMT/yCF1Gm+h91gpU9L3sJUbivBtjFkX4LsE8DMs15dkPIbWHq
YTJx+2QSOpGp6cK1D7LS8LInptHxI/UGRVYH3FLP1oUJuuFj+Wl7PKvOpZcc5uk1
u5tbHmUkhpJeklqsCWi6brQpPxiQAiCeiqMiTOQLlwiRNTT51waOqrvb60onDHAw
bEWfb0FQ0o3kb1yEQd/qPTJXWPZ96WhM3/7VNGoEDKwCzv5m8G1JbO4ct79iPF5x
Y7rx0uKJFPbekNACRmb9U0f3DpXm8hdgCKVKmbkgG2ZZnnmJg2lzmT72lwQfdcnv
O6IjXVLoDLjAYqjGe6HXQchi5rBHgmoguyHP6nGJ67n50hQ+upecVyp7k7LvuXi/
jHF8/zJfDZaEvjf6CwkePYk9XbOnvyls+RWZM1ZwX4xbk6nbONzo71uRrI0E8sKS
kWFCtvdbNJ//PaugrQNRMTs5z8Q3qMIyXRPXrxPFM/3lECaIEYvRrPyEYfdANqod
JrMru+hbHvutTPhDuOgJ+RVgirwfZpxPQS5aHHXQu8bMps9DEL15i6xKOVX5lqvL
lEL0YmqmRo7Idb9D+l0pyPAg9X1AwTziION/1cOPvWmjGVZqqaBv+jsMVlB+1o0o
BrJBy9SEzw5ya7sYi1BCwO6k7qAsB1qSmnu8Gg/RXAIz6D9nJiJbB5kwusVQretn
RqTj8ldBOgo+1la3hHzW8INTAVrlnmZjNMEGN4olYcJv6vHK9XTk7ruIkteJYD8Y
2jIFU3evsPTs4ugYxArEmzYsr1ZJR91aWf7naKcUlsap9k2zgHVw7Ks31D8T1OT3
mXQZdkY9hx+0kdh8iSYOkCSa237usV/TbYg12ncB8xSVsMQMNnEQHWIcgFUBeZoo
KQ27ijLmy5YYx8KYDAm7UJZ3Z00OrFa1D5qjippXkrDKbBwM3SrDSSVBtfKMGxyi
epF9hMBs6ecBBSIF9sZg0LHfMlbOIWyNLklHUtdBQ4+vavzbG1V66p0pB7HJp6rp
YC3moNRhHXyUFpvkIis7gFQ4xdE6FMEJsrHeZQHuOzRIB4XlwiCiAIg+IDmnP0hd
8Sxv0drpFU+zjR2aMr4bfhYdBG35NE/xfOp+ba25yCWCilljCIj46+jD0E2UvSrw
9iDtf75OXF2njqaLwmnFtHyAOA54IcXi9Y3NaXUXFxmK9iif5UbKa/dR+8rmOCF2
cozCs4Ujribcpgs3cqjkVX0Z12B7YdCZM1Tpj7BgPOLjpi0h0s5wbP9QMm3yb2S2
SKteoIypVcUXdpPqSwXtsUOUSuj7MeNXW2mkmQsH+W/n2Zn5OxgFKZ0JZ4z/OHei
hK1UFYPQ9BEBMzsJ2ttBLwak//hAMaUJDT8EKxEoBdqb
-----END ENCRYPTED PRIVATE KEY-----`;
    const publicKey = `-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAqIvXeEZJ2kx0xZxgcWeC
rRQ3+JQjgt7UOUN45PKnw5XNuRCxCbEvnjjLJwJZcrXo0qO54f5rafbPqYoSA5ed
Wt07dY5dVJ02OUl56uIwhoFArJpUzMywFq94BvTaO5Gz58uVUDKgFRYYx9z4/t10
6AliNHb3KEbovwMqIpGYLIr5JmImfyaCi7eAqwHsLH/HALqGFLHcr6oFaquA9abP
J/6wp465qmQuIUhqR9Nf3HIa/hjcF6ZTBlZhV//tzFH1wDIsUeMn8vS9Gi9vpwYa
AQsB8qxp2yXhZrEbMdD+E27te67SxedRK3IZ13xAeuQ+XB0kVDIPkfRw8eUg3mK2
XQIDAQAB
-----END PUBLIC KEY-----`;

    const data = this.ctx.request.body;
    const enc = crypto.publicEncrypt(
      {
        key: publicKey,
        padding: crypto.constants.RSA_PKCS1_PADDING,
      },
      Buffer.from(JSON.stringify(data))
    );
    const str = enc.toString('base64');
    // const sb = Buffer.from(str, 'base64');
    // const decpub = crypto.privateDecrypt(
    //   {
    //     key: privateKey,
    //     passphrase: 'sks_secret',
    //   },
    //   sb
    // );
    return enc.toString('base64');
    // for (const trainPlan of allTranPlan) {
    //   const { termnum = [] } = trainPlan;
    //   const termids = termnum.map(f => ObjectId(f._id).toString());
    //   // 查出该期所有的申请
    //   const termApplys = await applyModel.find({ termid: termids }).lean();
    //   const needDeletes = termApplys.filter(f => !(allSubject.find(s => s._id === f.subid) && allTeacher.find(t => t._id === f.teacherid)));
    //   const deleteIds = needDeletes.map(i => i._id);
    //   console.log(deleteIds.length);
    //   // await applyModel.deleteMany({ _id: deleteIds });
    // }
    // // // 重置班级
    // // 找到要删除的学生
    // const res = await this.ctx.model.Student.find({ school_name: '吉林职业技术学院', planid: '60769b703cead37068645fb2', termid: { $ne: '60779c0b2ec7ac704ce301ab' } });
    // // const test = res.map(f => {
    // //   return { name: f.name, openid: f.openid, id: f._id };
    // // });
    // const ids = res.map(i => i._id);
    // await this.ctx.model.User.deleteMany({ uid: ids });
    // await this.ctx.model.Student.deleteMany({ school_name: '吉林职业技术学院', planid: '60769b703cead37068645fb2', termid: { $ne: '60779c0b2ec7ac704ce301ab' } });
    // const ids = res.map(i => i.openid);
    // // console.log(ids);
    //
    // console.log(userRes.length);

    // const { planid, termid, batchid, classid } = query;
    // const filters = {};
    // if (classid) filters.classid = classid;
    // else if (batchid)filters.batchid = batchid;
    // else if (termid)filters.termid = termid;
    // else if (planid)filters.planid = planid;
    // else throw new BusinessError(ErrorCode.BADPARAM, '需要重置范围');
    // await this.ctx.model.Student.updateMany(filters, { classid: undefined });
    // // 重置班级结束
  }
  async getQueryOptions({ skip, limit, sort, desc } = {}) {
    if (sort && _.isString(sort)) {
      sort = { [sort]: desc ? -1 : 1 };
    } else if (sort && _.isArray(sort)) {
      sort = sort.map(f => ({ [f]: desc ? -1 : 1 })).reduce((p, c) => ({ ...p, ...c }), {});
    }
    return { skip, limit, sort };
  }

  /**
   * 更新进度,状态 不存在/完成以外(不是2的值)的数值, 不添加参数,只更新进度
   * @param {String} missionid 任务id
   * @param {String} progress 进度(人为划分)
   * @param {String} status 状态
   * @param {Object} params 参数
   */
  async updateProcess(missionid, progress, status, params) {
    try {
      if (!missionid) return;
      const { baseUrl } = _.get(this.ctx.app.config, 'mission');
      let url = `${baseUrl}`;
      let data;
      if (!baseUrl) return;
      if (!status || status !== '2') {
        url = `${url}/api/mission/progress`;
        data = { id: missionid, progress };
      } else if (status === '3') {
        url = `${url}/api/mission/update/${missionid}`;
        data = { status };
      } else {
        url = `${url}/api/mission/update/${missionid}`;
        data = { progress: '100', params, status };
      }
      await this.ctx.curl(url, {
        method: 'post',
        headers: {
          'content-type': 'application/json',
        },
        data,
        dataType: 'json',
      });
    } catch (error) {
      console.error(`任务更新进度报错,missionid:${missionid}\n`);
    }
  }

  async teacherImport() {
    // const filepath = './teacherlist.xlsx';
    // const workbook = new Excel.Workbook();
    // await workbook.xlsx.readFile(filepath);
    // const worksheet = workbook.getWorksheet(1);
    // if (!worksheet) return;
    // let arr = [];
    // worksheet.eachRow((row, ri) => {
    //   if (ri !== 1) {
    //     const obj = {};
    //     obj.name = row.getCell(3).value || undefined;
    //     obj.department = row.getCell(4).value || undefined;
    //     if (row.getCell(5).value) obj.job = row.getCell(5).value;
    //     obj.phone = row.getCell(6).value || undefined;
    //     obj.status = '4';
    //     arr.push(obj);
    //   }
    // });
    // // 检查谁生成过了, user表和teacher表
    // let ur = await this.ctx.model.User.find({ mobile: { $in: arr.map(i => i.phone) }, type: '3' });
    // let tr = await this.ctx.model.Teacher.find({ phone: { $in: arr.map(i => i.phone) } });
    // // 将有的老师过滤出去
    // if (ur) {
    //   ur = JSON.parse(JSON.stringify(ur));
    //   arr = arr.filter(f => !ur.find(uf => `${uf.mobile}` === `${f.phone}`));
    // }
    // if (tr) {
    //   tr = JSON.parse(JSON.stringify(tr));
    //   arr = arr.filter(f => !(tr.find(tf => `${tf.phone}` === `${f.phone}`)));
    // }
    // for (const tea of arr) {
    //   const ctr = await this.ctx.model.Teacher.create(tea);
    //   if (ctr) {
    //     const obj = { name: tea.name, mobile: tea.phone, type: '3', uid: ctr._id };
    //     const cur = await this.ctx.model.User.create(obj);
    //   }
    // }
    // const user = await this.ctx.model.User.find({ passwd: { $exists: false } });
    // for (const u of user) {
    //   u.passwd = { secret: '12345678' };
    //   u.save();
    // }
  }
  /**
   * 导出excel
   * @param {Array} dataList 数据集合
   * @param {Array} meta 表头(可以没有)
   * @param {String} fn 文件名
   * @param {Array} opera 单元格操作
   */
  async toExcel(dataList, meta, fn = '导出结果', opera) {
    // 导出excel
    const { app } = this;
    const nowDate = new Date().getTime();
    const filename = `${fn}-${nowDate}.xlsx`;
    // 取出预设存储地址
    const rootPath = `${app.config.cdn.repos_root_path}`;
    const rooturl = `${app.config.cdn.repos_root_url_excel}`;
    const path = `${rootPath}${rooturl}`;
    if (!path) {
      throw new BusinessError(ErrorCode.BUSINESS, '服务端没有设置存储路径');
    }
    // if (process.env.NODE_ENV === 'development') path = 'E:\\exportFile\\';
    if (!fs.existsSync(path)) {
      // 如果不存在文件夹,就创建
      fs.mkdirSync(path);
    }
    // 生成文件
    const filepath = `${path}${filename}`;
    fs.createWriteStream(filepath);
    const workbook = new Excel.Workbook();
    const sheet = workbook.addWorksheet('sheet');
    if (meta) sheet.columns = meta;
    sheet.addRows(dataList);
    if (_.isArray(opera)) {
      for (const o of opera) {
        const { startRow, startCol, endRow, endCol } = o;
        sheet.mergeCells(startRow, startCol, endRow, endCol);
      }
    }
    // 垂直居中
    const length = dataList.length;
    const alignment = { vertical: 'middle', horizontal: 'center' };
    for (let i = 1; i <= length; i++) {
      sheet.getRow(i).alignment = alignment;
    }
    await workbook.xlsx.writeFile(filepath);
    return `/files/excel/${filename}`;
  }

  /**
   * 创建/重复写入excel
   * @param {Array} dataList 数据
   * @param {String} fn 文件名, 第一次进入时,只是单纯的名,第二次开始,有后缀与时间戳
   * @param {String} downloadPath 下载文件路径
   * @param {String} type 方向,没有默认横向
   */
  async toAsyncExcel(dataList, fn = '导出结果', downloadPath, type) {
    const { app } = this;
    const workbook = new Excel.Workbook();
    let sheet;
    // 取出预设存储地址
    const rootPath = `${app.config.cdn.repos_root_path}`;
    const rooturl = `${app.config.cdn.repos_root_url_excel}`;
    let path = `${rootPath}${rooturl}`;
    let filepath = '';

    if (!path) {
      throw new BusinessError(ErrorCode.BUSINESS, '服务端没有设置存储路径');
    }
    if (process.env.NODE_ENV === 'development') path = 'D:\\temp\\upload\\excel\\';
    if (!fs.existsSync(path)) {
      // 如果不存在文件夹,就创建
      fs.mkdirSync(path);
    }

    if (!downloadPath) {
      // 第一次进入,文件还未生成
      const nowDate = new Date().getTime();
      fn = `${fn}-${nowDate}.xlsx`;
      sheet = workbook.addWorksheet('sheet');
    } else {
      const domain = 'http://127.0.0.1';
      const file = await this.ctx.curl(`${domain}${downloadPath}`);
      if (!(file && file.data)) {
        throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '未找导出的excel');
      }
      // 读取文件
      await workbook.xlsx.load(file.data);
      sheet = workbook.getWorksheet('sheet');
    }
    if (!type || type === 'horizontal') sheet.addRows(dataList);
    else if (type === 'vertical') {
      for (let i = 1; i <= dataList.length; i++) {
        const element = dataList[i - 1];
        const rows = sheet.getRow(i);
        if (rows.values.length <= 0) rows.values = element;
        else rows.values = rows.values.concat(element);
        rows.commit();
      }
    }
    filepath = `${path}${fn}`;
    await workbook.xlsx.writeFile(filepath);
    return { downloadPath: `/files/excel/${fn}`, fn };
  }

  /**
   * 导出docx
   * @param {Array} data 数据[{title,content([]),author}]
   * @param {String} fn 文件名
   */
  async toDocx(data, fn = '培训心得') {
    const { Document, Packer, Paragraph, TextRun, HeadingLevel, AlignmentType } = docx;
    const doc = new Document();
    const children = [];
    // 整理数据
    for (let i = 0; i < data.length; i++) {
      const obj = data[i];
      const { title, content, author } = obj;
      const c = [];
      if (title) {
        const tit = new Paragraph({
          children: [ new TextRun({ text: title, bold: true }) ],
          heading: HeadingLevel.TITLE,
          alignment: AlignmentType.CENTER,
        });
        c.push(tit);
      }
      if (author) {
        const auth = new Paragraph({
          children: [ new TextRun({ color: '#000000', text: author }) ],
          heading: HeadingLevel.HEADING_2,
          alignment: AlignmentType.RIGHT,
        });
        c.push(auth);
      }
      if (content && _.isArray(content) && content.length > 0) {
        for (const cont of content) {
          const p = new Paragraph({
            children: [ new TextRun({ text: cont, bold: true }) ],
          });
          c.push(p);
        }
      }
      if (i !== data.length - 1) {
        // 换页
        const last = new Paragraph({
          pageBreakBefore: true,
        });
        c.push(last);
      }
      if (c.length > 0) children.push(...c);
    }
    doc.addSection({
      properties: {},
      children,
    });

    const { app } = this;
    const rootPath = `${app.config.cdn.repos_root_path}`;
    const rooturl = `${app.config.cdn.repos_root_url_experience}`;
    const path = `${rootPath}${rooturl}`;
    // 如果不存在文件夹,就创建
    // if (process.env.NODE_ENV === 'development') path = 'E:\\exportFile\\';
    if (!fs.existsSync(path)) {
      fs.mkdirSync(path);
    }
    const num = new Date().getTime();
    const buffer = await Packer.toBuffer(doc);
    fs.writeFileSync(`${path}${fn}-${num}.docx`, buffer);
    // Packer.toBuffer(doc).then(buffer => {
    //   fs.writeFileSync(`${path}${fn}-${num}.docx`, buffer);
    // });
    return `/files${rooturl}${fn}-${num}.docx`;
  }

  /**
   * 将选择的文件导出到zip压缩包中,提供下载
   * @param {*} fileList 需要导入到zip中的列表,格式有2中: [{url:""}]或[String]
   * @param {*} fn 文件名,默认为 "导出结果"
   */
  async toZip(fileList, fn = '导出结果') {
    if (!_.isArray(fileList)) {
      throw new BusinessError(ErrorCode.DATA_INVALID, '需要压缩的文件数据格式错误');
    }
    fn = `${fn}.zip`;
    // zip文件夹创建
    const { app } = this;
    const rootPath = `${app.config.cdn.repos_root_path}`;
    const zipPath = `${app.config.cdn.repos_root_url_zip}`;
    let path = `${rootPath}${zipPath}`;
    if (process.env.NODE_ENV === 'development') path = 'E:\\exportFile\\';
    if (!fs.existsSync(path)) {
      fs.mkdirSync(path);
    }
    // 文件请求后将数据整理到这里
    const resetFileList = [];
    for (const file of fileList) {
      let uri = '';
      let filename = '';
      let prefixs;
      if (_.isString(file)) {
        uri = file;
        const arr = file.split('/');
        const last = _.last(arr);
        if (last) filename = last;
      } else if (_.isObject(file)) {
        const { uri: furi, url: furl, name, prefix } = file;
        if (furi) uri = furi;
        else if (furl) uri = furl;
        if (name) filename = name;
        else {
          const arr = uri.split('/');
          const last = _.last(arr);
          if (last) filename = last;
        }
        if (prefix) prefixs = prefix;
      }
      const obj = {};
      if (uri) obj.uri = uri;
      if (filename) obj.filename = filename;
      if (prefixs) obj.prefix = prefixs;
      resetFileList.push(obj);
    }
    // 导出
    const output = fs.createWriteStream(`${path}${fn}`);
    const archive = archiver('zip', {
      zlib: { level: 9 },
    });
    archive.pipe(output);
    // 请求文件,追加进压缩包
    for (const file of resetFileList) {
      const { uri, filename, prefix } = file;
      const res = await this.ctx.curl(`http://127.0.0.1${uri}`);
      if (res && res.data) {
        const options = {};
        if (filename) options.name = filename;
        if (prefix) options.prefix = prefix;
        if (filename) {
          archive.append(res.data, options);
        }
      }
    }
    await archive.finalize();
    return `/files${zipPath}${fn}`;
  }

  // 学校计划人数导出
  async schoolDownload() {
    const { app } = this;
    const headList = [
      { key: 'name', header: '学校', width: 50, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { key: 'code', header: '学校代码', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { key: 'class_special', header: '特殊班', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { key: 'class_common', header: '普通班', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
    ];
    const nowDate = new Date().getTime();
    const filename = `学校计划人数信息导出-${nowDate}.xlsx`;
    const rootPath = `${app.config.cdn.repos_root_path}`;
    const rooturl = `${app.config.cdn.repos_root_url_experience}`;
    const path = `${rootPath}${rooturl}`;
    if (!path) {
      throw new BusinessError(ErrorCode.BUSINESS, '服务端没有设置存储路径');
    }
    if (!fs.existsSync(path)) {
      // 如果不存在文件夹,就创建
      this.mkdir(path);
    }
    const AggregateInfo = [
      // 去除重复数据
      { $group: { _id: '$code', uniqueData: { $first: '$$ROOT' } } },
      { $replaceRoot: { newRoot: '$uniqueData' } },
      { $addFields: { id: '$_id' } },
    ];
    const list = await this.sModel.aggregate(AggregateInfo);
    const workbook = new Excel.Workbook();
    const sheet = workbook.addWorksheet('sheet');
    const meta = headList;
    sheet.columns = meta;
    sheet.addRows(list);
    // 生成excel
    const filepath = `${path}${filename}`;
    if (list.length <= 0) return;
    await workbook.xlsx.writeFile(filepath);
    return `/files/excel/${filename}`;
  }

  // 学校计划人数导入
  async schoolImport(data) {
    const { filepath } = data;
    assert(filepath, 'filepath不能为空');
    // 取得excle中数据
    const _filepath = this.ctx.app.config.baseUrl + filepath;
    const teadatas = await this.getImportXLSXData(_filepath);
    const school = [];
    for (const val of teadatas) {
      const schoolInfo = await this.sModel.findOne({ name: val.name }).lean();
      const classnum = [];
      let num;
      if (val.class_special) classnum.push({ number: val.class_special, name: '特殊班', code: '1' });
      if (val.class_common) classnum.push({ number: val.class_common, name: '普通班', code: '0' });
      if (val.class_special && val.class_common) num = parseInt(val.class_special) || 0 + parseInt(val.class_common) || 0;
      school.push({
        _id: schoolInfo._id.toString(),
        num,
        code: val.code,
        classnum,
      });
    }
    return { school };
  }
  // 获取导入的XLSX文件中的数据
  async getImportXLSXData(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 params = XLSX.utils.sheet_to_json(sheet); // 通过工具将表对象的数据读出来并转成json
    if (!params) return [];
    const length = params.length;
    const _datas = [];
    let data = {};
    for (let i = 0; i < length; i++) {
      data = params[i];
      _datas.push({
        name: data[theadRule[0]],
        code: data[theadRule[1]],
        class_special: data[theadRule[2]],
        class_common: data[theadRule[3]],
      });
    }
    exceldata = [...exceldata, ..._datas];
    return exceldata;
  }
  // 创建文件夹
  mkdir(dirname) {
    if (fs.existsSync(dirname)) {
      return true;
    }
    if (this.mkdir(Path.dirname(dirname))) {
      fs.mkdirSync(dirname);
      return true;
    }
  }
  // 学生参培名单导入
  async stuimport(data) {
    const { filepath, planid, termid, batchid } = data;
    assert(filepath, '文件不能为空');
    assert(planid, '计划不能为空');
    assert(termid, '期不能为空');
    assert(batchid, '批次不能为空');
    // 根据termid取得计划信息
    const plan = await this.ctx.model.Trainplan.findOne({ 'termnum._id': ObjectId(termid) });
    if (!plan) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '计划信息不存在');
    }
    const term = plan.termnum.id(termid);
    let type = '0';
    if (term.batchnum && term.batchnum.length > 0 && term.batchnum[0].class && term.batchnum[0].class.length > 0) type = term.batchnum[0].class[0].type;
    const planyearid = plan.planyearid;
    // 检查这个范围的学生是否存在,存在的话是否更改过(classid,bedroomid这两项存不存在可以放过,但凡有一个人,就不行了)
    let dbStuList = await this.ctx.model.Student.find({ planid, termid, batchid });
    if (dbStuList.length > 0) {
      // 查这个学校的这期学生是否修改过班级 或 寝室
      const is_change = dbStuList.find(f => f.classid || f.bedroomid);
      if (is_change) {
        throw new BusinessError(
          ErrorCode.BUSINESS,
          '该计划该期该批次有学生已经安排班级或寝室 无法进行导入,请对该批次学生进行修改重新导入!'
        );
      }
    }
    // 2021-06-07 如果学生已经绑定,那也不允许修改名单了
    const countOpenid = await this.ctx.model.Student.count({ planid, termid, batchid, openid: { $exists: true } });
    if (countOpenid > 0) throw new BusinessError(ErrorCode.BUSINESS, '已有学生绑定账号,名单无法修改!');
    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 = await this.lastSetData(studentList, {
      planyearid,
      planid,
      batchid,
      termid,
      type,
    });
    // 复制,删除,添加
    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.ctx.model.Student.deleteMany({ planid, termid, batchid });
      await this.ctx.model.Dstudent.insertMany(dbStuList);
    }
    await this.ctx.model.Student.insertMany(studentList);
    return 'ok';
  }
  // 整理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 === 'school_name') {
          const school = await this.sModel.findOne({ name: { $regex: val } });
          if (!school) {
            error = true;
            msg = `${msg}系统中无此学校;`;
          }
          continue;
        }
        // 身份证号校验
        if (column === 'id_number') {
          const { pass, msg: idmsg } = this.ctx.service.school.idCodeValid(val);
          if (!pass) {
            error = true;
            msg = `${msg}${idmsg};`;
          }
          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') {
          if (!/^\d{11}$/i.test(val)) {
            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 };
  }
  // 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 lastSetData(stuList, data) {
    const cols = this.getStucolumn();
    const needChange = cols.filter(f => f.change);
    const studentList = [];
    for (const i of stuList) {
      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;
      }
      if (d.school_name) {
        const school = await this.sModel.findOne({ name: { $regex: d.school_name } }).lean();
        if (school) d.schid = school.code || '';
      }
      studentList.push(d);
    }
    return studentList;
  }
  // 身份证验证
  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;
  }
  /**
   * 检查学生是否参加过这个计划以外的计划,参加过就不让来了
   * @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.ctx.model.Student.count({ name, 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 };
  }
}

module.exports = UtilService;