server/app/service/import.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 Excel = require('exceljs');
const moment = require('moment');
const importSetting = require('../public/importSetting');
const fs = require('fs');
// 通用导入
class ImportService extends CrudService {
  constructor(ctx) {
    super(ctx, 'import');
    this.http = this.ctx.service.util.httpUtil;
    this.spMark = this.ctx.service.special;
    this.prefix = '/db';
    this.temp = 'temp';
    this.fetchService = this.ctx.service.fetch;
    this.createService = this.ctx.service.create;
    this.updateService = this.ctx.service.update;
    this.deleteService = this.ctx.service.delete;
  }
  async getFile(uri) {
    const { domain } = this.ctx.app.config.import;
    const file = await this.ctx.curl(`${domain}${uri}`);
    if (!(file && file.data)) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '未找到指定文件');
    }
    return file;
  }

  /**
   * 导入:需要自行配置每个表的导入字段的映射关系
   * @param {Object} param0 {table} 表名
   * @param {Object} param1 {uri} 上传的文件地址
   */
  async index({ table }, { uri }) {
    assert(table, '缺少导入目标');
    const file = await this.getFile(uri);
    const workbook = new Excel.Workbook();
    await workbook.xlsx.load(file.data);
    const sheet = workbook.getWorksheet(1);
    let meta = importSetting[table];
    if (!meta) {
      throw new BusinessError(ErrorCode.SERVICE_FAULT, '未设置该表的映射关系');
    }
    // 获取表头
    const heads = sheet.getRow(1).values;
    for (const h of meta) {
      if (!_.get(h, 'key')) continue;
      // 直接找索引,下面也是用索引找
      const r = heads.findIndex(f => f === h.key);
      if (r >= 0) h.index = r;
    }
    // 将没有索引的踢出去,别影响下面,可能报异常会炸
    meta = meta.filter(i => _.get(i, 'index'));
    const arr = [];
    sheet.eachRow((row, ri) => {
      if (ri === 1) return;
      // 遍历meta,拼成数据
      const object = {};
      const excelValues = row.values;
      for (const i of meta) {
        const { index, column, turn } = i;
        if (_.get(excelValues, index) && column) {
          let value = _.get(excelValues, index);
          if (turn) value = this[turn](i);
          object[column] = value;
        }
      }
      arr.push(object);
    });
    const errorArray = [];
    for (const i of arr) {
      try {
        // try catch保证能一直往里插,把错误回收到一起统一显示
        // TODO:此处await应该可以取消,但是可能就呜呜呜呜一顿添加数据了.可能没有问题,也可能会爆炸,需要试试
        await this.ctx.service.create.index({ table }, i);
      } catch (error) {
        errorArray.push(i);
      }
    }
    if (errorArray.length > 0) return errorArray;
  }

  /**
   * 考试相关特殊导入
   * @param {Object} param {uri} 上传的文件地址
   */
  async updateExaminee({ uri }) {
    const file = await this.getFile(uri);
    const workbook = new Excel.Workbook();
    await workbook.xlsx.load(file.data);
    const sheet = workbook.getWorksheet(1);
    let meta = this.getExamineeMeta();
    // 获取表头
    const heads = sheet.getRow(1).values;
    for (const h of meta) {
      if (!_.get(h, 'key')) continue;
      // 直接找索引,下面也是用索引找
      const r = heads.findIndex(f => f === h.key);
      if (r >= 0) h.index = r;
    }
    // 将没有索引的踢出去,别影响下面,可能报异常会炸
    meta = meta.filter(i => _.get(i, 'index'));
    const arr = [];
    sheet.eachRow((row, ri) => {
      if (ri === 1) return;
      // 遍历meta,拼成数据
      const object = {};
      const excelValues = row.values;
      for (const i of meta) {
        const { index, column } = i;
        if (_.get(excelValues, index) && column) {
          const value = _.get(excelValues, index);
          if (column === 'exam_date') {
            const date = moment(value).format('YYYY-MM-DD');
            object[column] = date;
          } else {
            object[column] = value;
          }
        }
      }
      arr.push(object);
    });
    const errorList = [];
    // 去修改
    for (const i of arr) {
      const { card, testsite_num } = i;
      if (!card || !testsite_num) {
        errorList.push(i);
        continue;
      }
      const table = 'examination_examinee';
      const query = { card, testsite_num };
      const body = i;
      try {
        await this.ctx.service.update.index({ table }, query, body);
      } catch (error) {
        errorList.push(i);
      }
    }
    if (errorList.length > 0) return errorList;
    return 'ok';
  }

  getExamineeMeta() {
    return [
      { key: '姓名', column: 'name' },
      { key: '性别', column: 'gender' },
      { key: '身份证号', column: 'card' },
      { key: '联系电话', column: 'phone' },
      { key: '准考证号', column: 'exam_num' },
      { key: '公安机关', column: 'police_office' },
      { key: '考试成绩', column: 'exam_achieve' },
      { key: '体能', column: 'stamina' },
      { key: '考试等级', column: 'exam_grade' },
      { key: '考试日期', column: 'exam_date' },
      { key: '考试时间', column: 'exam_time' },
      { key: '考点编码', column: 'testsite_num' },
      { key: '考试地点名称', column: 'exam_addr' },
      { key: '座位号', column: 'seat_num' },
      { key: '场次', column: 'exam_play' },
      { key: '参考区县', column: 'district' },
      { key: '考试类型', column: 'exam_type' },
    ];
  }

  async staffBase({ uri }) {
    const file = await this.getFile(uri);
    const workbook = new Excel.Workbook();
    await workbook.xlsx.load(file.data);
    const sheet = workbook.getWorksheet(1);
    let meta = this.getStaffBase();
    // 获取表头
    const heads = sheet.getRow(1).values;
    for (const h of meta) {
      if (!_.get(h, 'key')) continue;
      // 直接找索引,下面也是用索引找
      const r = heads.findIndex(f => f === h.key);
      if (r >= 0) {
        h.index = r;
      }
    }
    // 将没有索引的踢出去,别影响下面,可能报异常会炸
    meta = meta.filter(i => _.get(i, 'index'));
    const arr = [];
    // 处理图片
    const sheetImageInfo = sheet.getImages();
    const imgs = _.compact(
      sheetImageInfo.map(i => {
        const { imageId, range } = i;
        const row = _.get(range, 'tl.nativeRow');
        const col = _.get(range, 'tl.nativeCol');
        // const column = _.get
        if (row && col) return { row, col: col + 1, imageId };
      })
    );
    sheet.eachRow((row, ri) => {
      if (ri === 1) return;
      // 遍历meta,拼成数据
      const object = {};
      const excelValues = row.values;
      for (const i of meta) {
        const { index, column } = i;
        if (_.get(excelValues, index) && column) {
          const value = _.get(excelValues, index);
          if (column === 'birth' || column === 'acceptance_date') {
            const date = moment(value).format('YYYY-MM-DD');
            object[column] = date;
          } else {
            object[column] = value;
          }
        }
      }
      arr.push(object);
    });

    // 上传前的准备工作.因为函数里有很多变量是这个函数中的全局变量,移出去的话会有很多参数,所以昨成立函数中的函数
    const toUpload = async (i, key, card) => {
      const rowIndex = i + 1;
      const metaObject = meta.find(f => f.column === key);
      if (!metaObject) return;
      const colIndex = metaObject.index;
      const imgObject = imgs.find(f => f.row === rowIndex && f.col === colIndex);
      const imgId = _.get(imgObject, 'imageId');
      if (imgId || imgId === 0) {
        const img = workbook.getImage(imgId);
        const uri = `baoan_staffBase_card/${card}/upload`;
        img.uri = uri;
        img.name = key === 'id_just' ? 'front' : 'back';
        const url = `http://127.0.0.1:${process.env.NODE_ENV === 'development' ? '9999' : '80'}/files/server/upload`;
        const res = await this.uploadImage(url, img);
        return res;
      }
    };
    const errorList = [];
    for (let i = 0; i < arr.length; i++) {
      const object = arr[i];
      const { name, card } = object;
      if (!name || !card) {
        // 缺少名和身份证号的不加
        errorList.push(object);
        continue;
      }
      const id_just = _.get(object, 'id_just');
      const id_back = _.get(object, 'id_back');
      if (!id_just) {
        // 没有,获取
        const result = await toUpload(i, 'id_just', card);
        object.id_just = result;
      }
      if (!id_back) {
        const result = await toUpload(i, 'id_back', card);
        object.id_back = result;
      }
      // 身份证后六位为密码
      const password = card.substring(12);
      if (password) object.password = password;
      // 加人
      let baseInfo;
      try {
        baseInfo = await this.ctx.service.create.index({ table: 'security_guard_base' }, object);
        if (baseInfo) baseInfo = baseInfo.data;
      } catch (error) {
        console.error('保安人员添加失败');
        this.ctx.logger.error('保安人员添加失败');
        errorList.push({ ...object, reason: '保安人员添加失败' });
        continue;
      }

      try {
        if (!_.get(baseInfo, 'id')) continue;
        await this.ctx.service.create.index(
          { table: 'security_guard_collect' },
          { name: _.get(baseInfo, 'name'), security_guard_id: _.get(baseInfo, 'id'), data: _.get(baseInfo, 'id_just'), type: '人像' }
        );
        await this.ctx.service.update.index({ table: 'security_guard_base' }, { id: _.get(baseInfo, 'id') }, { collect_photo: '已采集' });
      } catch (error) {
        console.error('保安人员采集信息添加失败');
        this.ctx.logger.error('保安人员采集信息添加失败');
        errorList.push({ ...object, reason: '保安人员采集信息添加失败' });
        if (_.get(baseInfo, 'id')) {
          await this.ctx.service.delete.index({ table: 'security_guard_base' }, { id: _.get(baseInfo, 'id') });
        }
      }
    }
    if (errorList.length > 0) return errorList;
    return 'ok';
  }
  getStaffBase() {
    return [
      { key: '是否上班', column: 'is_class' },
      { key: '主键', column: 'id' },
      { key: '姓名', column: 'name' },
      { key: '曾用名', column: 'beforeName' },
      { key: '身份证号', column: 'card' },
      { key: '性别', column: 'gender' },
      { key: '民族', column: 'nation' },
      { key: '出生日期', column: 'birth' },
      { key: '政治面貌', column: 'politics' },
      { key: '文化程度', column: 'education' },
      { key: '兵役状况', column: 'soldier' },
      { key: '婚姻状况', column: 'marriage' },
      { key: '血型', column: 'blood' },
      { key: '身高', column: 'height' },
      { key: '保安员等级', column: 'grade' },
      { key: '驾驶证号', column: 'drive_num' },
      { key: '准驾车型', column: 'drive_type' },
      { key: '健康状态', column: 'health' },
      { key: '户籍省市县', column: 'house_city' },
      { key: '户籍/居住证派出所名称', column: 'house_police' },
      { key: '微信公众号OpenID', column: 'gopenid' },
      { key: '微信统一id', column: 'unionid' },
      { key: '是否加入人才库', column: 'is_talent' },
      { key: '微信小程序绑定的用户OpenID', column: 'openid' },
      { key: '报名企业', column: 'sign_company' },
      { key: '培训机构编码', column: 'train_num' },
      { key: '现住地省市县', column: 'onhouse_city' },
      { key: '户籍地详址', column: 'house_address' },
      { key: '现住地详址', column: 'house_onaddress' },
      { key: '家庭主要成员', column: 'home_member' },
      { key: '教育经历', column: 'education_experience' },
      { key: '工作经历', column: 'work_experience' },
      { key: '注销人', column: 'cancel_personal' },
      { key: '注销原因', column: 'cancel_reason' },
      { key: '注销时间', column: 'cancel_date' },
      { key: '状态', column: 'status' },
      { key: '受理人', column: 'acceptance_personal' },
      { key: '受理公安机关', column: 'acceptance_police' },
      { key: '受理时间', column: 'acceptance_date' },
      { key: '身份证正面', column: 'id_just' },
      { key: '身份证反面', column: 'id_back' },
      { key: '联系电话', column: 'phone' },
      { key: '登录密码', column: 'password' },
      { key: '创建时间', column: 'create_time' },
      { key: '更新时间', column: 'update_time' },
      { key: '采集照片', column: 'collect_photo' },
      { key: '采集指纹', column: 'collect_fingerprint' },
      { key: '审核结果', column: 'examine_status' },
      { key: '审批结果', column: 'approve_status' },
      { key: '注销状态', column: 'cancel_status' },
    ];
  }
  /**
   * 上传图片(服务端=>服务端)
   * @param {String} uri 上传路径
   * @param {Object} img 从sheet取出的每项的图片object
   */
  async uploadImage(uri, img) {
    const base64 = this.turnImageToBase64(img);
    delete img.buffer;
    img.code = base64;
    const res = await this.http.$post(uri, img);
    if (res && res.uri) {
      return res.uri;
    }
  }

  /**
   * 转换图片为base64
   * @param {Object} object excel获取的图片object
   * @property extension  后缀,文件类型
   * @property buffer 图片内容,不含头部信息的,
   */
  turnImageToBase64(object = {}) {
    const { extension, buffer } = object;
    if (extension && buffer) {
      const suffix = object.extension;
      const ib = object.buffer.toString('base64');
      const base64 = `data:image/${suffix};base64,${ib}`;
      return base64;
    }
  }

  /**
   * 上传保安人员
   * @param {String} uri 文件路径
   */
  async securityGuard({ uri }) {
    assert(uri, '缺少文件地址,无法读取文件');
    const meta = require('../public/securityGuardImportMeta');
    const metaArr = [ ...meta.base, ...meta.cert ];
    const file = await this.getFile(uri);
    const workbook = new Excel.Workbook();
    await workbook.xlsx.load(file.data);
    const sheet = workbook.getWorksheet(1);
    const head = _.get(sheet.getRow(1), 'values', []);
    for (let i = 0; i < head.length; i++) {
      const e = head[i];
      if (!e) continue;
      const r = metaArr.find(f => f.zh === e);
      if (r) r.index = i;
    }
    // 需要整理出 必填字段,默认字段,检查token字段,需要执行函数来赋值的字段
    /**
     * 从excel获取数据的字段
     */
    const getFormExcelArray = metaArr.filter(f => f.index);
    /**
     * 默认值字段
     */
    const defArray = metaArr.filter(f => f.default);
    const defObject = {};
    for (const i of defArray) {
      const { key, default: def } = i;
      defObject[key] = def;
    }
    const user = this.ctx.user;
    if (!user) {
      new BusinessError(ErrorCode.NOT_LOGIN, '未找到操作人信息');
    }
    /**
     * checkToken部分处理
     */
    const ctArray = metaArr.filter(f => f.checkToken);
    for (const i of ctArray) {
      const { method, key } = i;
      defObject[key] = method(user);
    }
    /**
     * 错误列表
     */
    const errorList = [];
    /**
     * 数据列表
     */
    const dataList = [];
    /**
     * 获取excel的数据
     */
    sheet.eachRow(async (row, index) => {
      if (index !== 1) {
        const values = row.values;
        let obj = {};
        for (const m of getFormExcelArray) {
          const { required, key, index, zh, format } = m;
          const value = values[index];
          if (required && !value) {
            // 必填且没值的情况
            errorList.push({ message: `第${index}行数据,缺少必填项 ${zh};` });
            continue;
          }
          if (format) {
            if (_.isFunction(format)) obj[key] = format(value);
          } else obj[key] = value;
        }
        obj = Object.assign(obj, defObject);
        dataList.push(obj);
      }
    });
    if (errorList.length > 0) return errorList;
    /**
     * 保安员基础表字段
     */
    const baseMeta = meta.base.map(i => i.key);
    /**
     * 保安员证书表字段
     */
    const certMeta = meta.cert.map(i => i.key);
    for (const eData of dataList) {
      const baseObject = _.pick(eData, baseMeta);
      const certObject = _.pick(eData, certMeta);
      const rd = await this.fetchService.index({ table: 'security_guard_base' }, { card: baseObject.card });
      if (!rd.data) {
        // 创建
        /**
         * 公安机关机构
         */
        let policeDepartmentObject;
        /**
         * 创建后的保安人员基础信息
         */
        let securityBaseObject;
        /**
         * 创建后的保安证信息
         */
        let certificatesBaseObject;
        // 补全编码
        try {
          policeDepartmentObject = await this.ctx.service.securityGuard.base.getHousePoliceCode(eData);
          if (policeDepartmentObject) {
            baseObject.house_police_code = policeDepartmentObject.num;
          } else {
            // 业务逻辑错误.并非异常
            errorList.push({ message: `${baseObject.name}: 未找到 户籍/居住证派出所编码!请查询公安机构名称数据与表格数据是否一致;` });
            continue;
          }
        } catch (error) {
          errorList.push({ message: `${baseObject.name}: 补全 户籍/居住证派出所编码 失败!;` });
          continue;
        }
        // 创建保安员信息
        try {
          const baseResult = await this.createService.index({ table: 'security_guard_base' }, baseObject);
          if (baseResult) securityBaseObject = baseResult.data;
        } catch (error) {
          errorList.push({ message: `${baseObject.name}: 保安员信息创建 失败!;` });
          continue;
        }
        // 将保安员id放入证的数据中
        certObject.security_guard_id = securityBaseObject.id;
        // 创建保安证的信息;相关信息补全及创建
        // 如果是业务逻辑异常,则内部catch不需要向errorList输出信息.反之则需要输出带有 '失败' 的字样.提示是接口相关错误,而不是逻辑错误
        // 嵌套try...catch如果想到上一层try...catch中.只能使用通常异常,使用工具异常会直接跳出去
        try {
          /**
           * 保安证信息补全,企业查询.错误是否已经输出的判断变量
           */
          let companyHaveFault = false;
          try {
            if (certObject.company_name) {
              // 查询保安证所在企业的企业id,并赋值
              const companyResutl = await this.fetchService.index({ table: 'company_base' }, { name: certObject.company_name });
              if (companyResutl.data) {
                certObject.company_id = _.get(companyResutl, 'data.id');
              } else {
                // 业务逻辑错误.并非异常,但是需要抛出异常,触发删除
                errorList.push({ message: `${baseObject.name}补全保安证信息: 未找到 ${certObject.company_name} 相关信息!请查询是否有该企业信息;` });
                companyHaveFault = true;
                throw '接口错误';
              }
            }
          } catch (error) {
            if (!companyHaveFault) errorList.push({ message: `${baseObject.name}补全保安证信息: 查询 ${certObject.company_name} 相关信息 失败!;` });
            throw '接口错误';
          }
          // 创建保安证
          try {
            const certResult = await this.createService.index({ table: 'certificates_base' }, certObject);
            if (certResult) certificatesBaseObject = certResult.data;
          } catch (error) {
            errorList.push({ message: `${baseObject.name}: 保安证信息创建 失败!;` });
            throw '接口错误';
          }
        } catch (error) {
          // 删除保安员信息
          await this.deleteService.index({ table: 'security_guard_base' }, { id: securityBaseObject.id });
          continue;
        }
      } else {
        errorList.push(`已存在 ${baseObject.name} 保安员,不予处理`);
      }
    }
    return { errorList };
  }

  /**
   * excel批量办理入职
   * @param {String} uri 文件路径
   */
  async guardWork({ uri }) {
    assert(uri, '缺少文件地址,无法读取文件');
    const meta = require('../public/guardWorkMeta');
    const file = await this.getFile(uri);
    const workbook = new Excel.Workbook();
    await workbook.xlsx.load(file.data);
    const sheet = workbook.getWorksheet(1);
    const head = _.get(sheet.getRow(1), 'values', []);
    for (let i = 0; i < head.length; i++) {
      const e = head[i];
      if (!e) continue;
      const r = meta.find(f => f.zh === e);
      if (r) r.index = i;
    }
    /**
     * 判断当前用户是否是企业用户的变量
     */
    let is_company = false;
    /**
     * 从excel获取数据的字段
     */
    const getFormExcelArray = meta.filter(f => f.index);
    // 需要判断,如果当前用户不是企业,则必须要有 企业名称 列,否则直接提示错误
    if (this.ctx.user.table !== 'company_base') {
      const r = getFormExcelArray.find(f => f.key === 'company_name');
      if (!r) throw new BusinessError(ErrorCode.DATA_INVALID, '非企业用户,缺少企业名称会导致入职缺少企业相关信息!拒绝导入!');
    } else is_company = true;
    /**
     * 默认值字段
     */
    const defArray = meta.filter(f => f.default);
    /**
     * 查表处理
     */
    const tableArray = meta.filter(f => f.table);
    const errorList = [];
    const dataList = [];
    /**
     * 获取excel的数据
     */
    sheet.eachRow(async (row, index) => {
      if (index !== 1) {
        const values = row.values;
        const obj = {};
        for (const m of getFormExcelArray) {
          const { required, key, index, zh, format } = m;
          const value = values[index];
          if (required && !value) {
            // 必填且没值的情况
            errorList.push({ message: `第${index}行数据,缺少必填项 ${zh};` });
            continue;
          }
          if (format) {
            if (_.isFunction(format)) obj[key] = format(value);
          } else obj[key] = value;
        }
        dataList.push(obj);
      }
    });
    if (errorList.length > 0) return errorList;
    // 处理default值
    for (const d of dataList) {
      for (const m of defArray) {
        const { key, default: def } = m;
        if (_.isFunction(def)) d[key] = def(d[key]);
        else d[key] = def;
      }
    }

    // 取值函数,就这用,就不外面写了
    const getValue = (column, object) => {
      const { key, from } = column;
      const obj = { key };
      obj.value = object[from] || object[key];
      return obj;
    };

    // 补充数据,需要从别的表拽来的那些
    for (const d of dataList) {
      for (const object of tableArray) {
        const { query, table, columns, zh } = object;
        // 当前要处理的数据源
        let originData;
        if (table === 'company_base' && is_company) {
          // 补充企业信息,且当前用户为企业时:则不需要请求处理,直接从this.ctx.user中取出来就好
          d.company_name = this.ctx.user.name;
          originData = _.cloneDeep(this.ctx.user);
        } else {
          const q = {};
          for (const key in query) {
            const v = query[key];
            if (_.isFunction(v)) q[key] = v(d);
            else q[key] = v;
          }
          const r = await this.fetchService.index({ table }, q);
          if (r.data) originData = r.data;
          else {
            errorList.push({ message: `${d.name}: 未找到 ${zh} 信息` });
            continue;
          }
        }
        // 获取到了数据源,开始补值
        for (const column of columns) {
          const { key, value } = getValue(column, originData);
          d[key] = value;
        }
      }
    }
    if (errorList.length > 0) return { errorList };
    const res = await this.ctx.service.securityGuard.work.toWork({ data: dataList });
    return { errorList: res };
  }
}

module.exports = ImportService;