server-front/app/service/patent/patentinfo.js

'use strict';
const assert = require('assert');
const moment = require('moment');
const Excel = require('exceljs');
const Path = require('path');
const _ = require('lodash');
const { sep } = require('path');
const fs = require('fs');
const { CrudService } = require('naf-framework-mongoose-free/lib/service');
const { BusinessError, ErrorCode } = require('naf-core').Error;
const { trimData } = require('naf-core').Util;
const { ObjectId } = require('mongoose').Types;
const compressing = require('compressing');

// 专利信息
class PatentinfoService extends CrudService {
  constructor(ctx) {
    super(ctx, 'patentinfo');
    this.model = this.ctx.model.Patent.Patentinfo;
    this.personalModel = this.ctx.model.User.Personal;
    this.adminModel = this.ctx.model.User.Admin;
    this.organizationModel = this.ctx.model.User.Organization;
    this.root_path = _.get(this.ctx.app.config.export, 'root_path');
    this.patentInfo_dir = _.get(this.ctx.app.config.export, 'patentInfo_dir');
    this.export_dir = _.get(this.ctx.app.config.export, 'export_dir');
    // if (process.env.NODE_ENV === 'development') {
    //   this.root_path = 'S:\\workspace\\exportFile\\';
    // }

    this.file_type = '';
    if (!fs.existsSync(`${this.root_path}${this.file_type}`)) {
      // 如果不存在文件夹,就创建
      fs.mkdirSync(`${this.root_path}${this.file_type}`);
    }
    this.excel_path = `${sep}excel${sep}`;
    this.domain = 'http://127.0.0.1';
    this.export_limit = 500;
  }

  async query(query, { skip = 0, limit = 0, projection }) {
    const newquery = await this.resetCode(query);
    const res = await this.model.find(newquery, projection).skip(parseInt(skip)).limit(parseInt(limit))
      .sort({ create_date: -1 });
    return res;
  }
  async count(query) {
    const newquery = await this.resetCode(query);
    const res = await this.model.countDocuments(trimData(newquery)).exec();
    return res;
  }
  async resetCode(query) {
    query = this.dealKey(query);
    let newquery = _.cloneDeep(query);
    newquery = this.ctx.service.util.util.dealQuery(newquery);
    if (Object.keys(newquery).length <= 0) return newquery;
    const { pid, user_id, key_word, single_inventor } = newquery;
    let arr = [];
    if (pid) {
      const plist = await this.adminModel.find({ pid, role: '2' }, { _id: 1, deptname: 1 });
      arr = plist.map(i => i.deptname);
      if (arr.length > 0) {
        newquery.first_apply = arr;
        delete newquery.pid;
      }
    }
    if (user_id) {
      newquery['user_id.user_id'] = user_id;
      delete newquery.user_id;
    }
    if (key_word) {
      newquery.$or = [{ name: new RegExp(key_word) }, { abstract: new RegExp(key_word) }, { first_ask: new RegExp(key_word) }];
      delete newquery.key_word;
    }
    if (single_inventor) {
      newquery['inventor.name'] = { $in: [ single_inventor ] };
      delete newquery.single_inventor;
    }
    return newquery;
  }
  /**
   * 处理模糊查询
   * @param {Object} query 查询条件
   */
  dealKey(query) {
    const vagueList = [
      'inventor',
      'agent',
      'agent_personal',
      'abstract',
      'address',
      'name',
      'apply_personal',
      'nationality',
      'ipc_type',
      'onlegal_status',
      'legal_status',
      'on_obligee',
      'apply_address',
      'apply_other',
      'law_num',
      'invention_design',
      'incopat_link',
      'first_ask',
      'first_apply',
      'apply_city',
      'business_code',
      'business_address',
      'first_inventor',
      'shared_value',
      'techol_stable',
      'techol_advanced',
      'pct_apply',
      'pct_publish',
    ];
    const keys = Object.keys(query);
    for (const key of keys) {
      if (vagueList.includes(key)) {
        query[`%${key}%`] = query[key];
        delete query[key];
      }
    }
    return query;
  }
  async toImport({ uri, code }) {
    assert(uri, '未获取到文件地址');
    const file = await this.ctx.curl(`${this.domain}${uri}`);
    if (!(file && file.data)) {
      throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '未找到指定文件');
    }
    const workbook = new Excel.Workbook();
    await workbook.xlsx.load(file.data);
    const sheet = workbook.getWorksheet(1);
    const arr = [];
    const allNotice = [];
    const sheetImageInfo = sheet.getImages();
    const imgids = _.compact(
      sheetImageInfo.map(i => {
        const { imageId, range } = i;
        const row = _.get(range, 'tl.nativeRow');
        if (row) return { row, imageId };
      })
    );
    // 2021-11-18 修改导入,根据model文件(和excel顺序一致)的顺序进行取字段
    const models = _.get(this.model, 'schema.obj');
    const meta = Object.keys(models).map((i, index) => ({
      key: i,
      index: index + 2,
    }));
    sheet.eachRow((row, rindex) => {
      if (rindex !== 1) {
        // 组织数据,图片的索引和行索引不一致,准确的说是:图片索引比行索引少1
        // 原因:图片在工作簿中获取,按照1,2,3...顺序排序,但是行的第一行是表头(当前文件),所以当前行数需要减掉表头那一行
        const imgid = imgids.find(f => f.row === rindex - 1);
        const img_url = [];
        if (imgid) {
          img_url.push({
            url: this.turnImageToBase64(workbook.getImage(imgid.imageId)),
          });
        }
        // 根据meta整理数据
        // 需要日期转换的字段
        const dateColumnArray = [ 'create_date', 'success_date', 'law_date', 'first_opendate', 'empower_date', 'lose_date', 'examine_date' ];
        let obj = { img_url };
        for (const m of meta) {
          const { key, index } = m;
          if (dateColumnArray.includes(key)) {
            obj[key] = _.get(row.getCell(index), 'value') ? moment(_.get(row.getCell(index), 'value')).format('YYYY-MM-DD') : '';
          } else if (key === 'img_url') {
            // 上面默认处理了
            continue;
          } else if (key === 'incopat_link') {
            obj[key] = this.getUrl(_.get(row.getCell(index), 'value'));
          } else obj[key] = _.get(row.getCell(index), 'value');
        }
        obj = _.pickBy(obj, _.identity);
        arr.push(obj);
      }
    });
    for (const obj of arr) {
      const { result, notice } = this.tocheckData(obj);
      if (!result) {
        allNotice.push(notice);
      }
    }
    if (allNotice.length > 0) return allNotice;
    // 有选择机构,创建用户
    if (code) {
      for (const obj of arr) {
        const { first_inventor, create_number } = obj;
        try {
          let person = await this.personalModel.findOne({ code, phone: first_inventor });
          const user_id = {};
          // 有用户,直接取id,没有用户;创建用户,取id;
          if (person) {
            user_id.user_id = person._id || person.id;
            user_id.name = person.name;
          } else {
            const personObject = {
              name: first_inventor,
              phone: first_inventor,
              status: '1',
              code,
            };
            try {
              person = await this.ctx.service.users.personal.create(personObject);
            } catch (error) {
              allNotice.push(`${create_number} 的 第一发明人用户,已有机构所属的个人用户使用该手机号!`);
              continue;
            }
            if (person) {
              user_id.user_id = person._id || person.id;
              user_id.name = person.name;
            }
          }
          obj.user_id = [ JSON.parse(JSON.stringify(user_id)) ];
        } catch (error) {
          allNotice.push(`${create_number} 的 第一发明人用户 创建失败!`);
          continue;
        }
      }
    }
    if (allNotice.length > 0) return allNotice;
    // 根据申请号做添加/修改
    for (const obj of arr) {
      try {
        const has_data = await this.model.count({
          create_number: obj.create_number,
        });
        let res;
        if (!has_data) {
          res = await this.model.create(obj);
        } else {
          await this.model.updateOne({ create_number: obj.create_number }, obj);
          res = await this.model.find({ create_number: obj.create_number });
        }
        // 处理警告
        if (res.trem === '有效') this.ctx.service.patent.patentearly.dealData([ res ]);
      } catch (error) {
        allNotice.push(`申请号 为${create_number} 的 专利信息 创建失败!`);
        continue;
      }
    }
  }
  async toExport({ user, query }) {
    const data = {
      title: '专利导出',
      params: {
        project: 'market',
        service: 'patent.patentinfo',
        method: 'export',
        query,
      },
      user,
      tenant: 'live',
    };
    try {
      await this.ctx.service.util.httpUtil.cpost('/api/mission', 'mission', data);
    } catch (error) {
      console.log(error);
      throw new BusinessError(ErrorCode.SERVICE_FAULT, '任务创建失败');
    }
  }
  async export({ missionid, query = {} } = {}) {
    const path = `${this.root_path}${sep}${this.file_type}${sep}${this.patentInfo_dir}${new Date().getTime()}`;
    if (!path) {
      throw new BusinessError(ErrorCode.BUSINESS, '服务端没有设置存储路径');
    }
    if (!fs.existsSync(path)) {
      // 如果不存在文件夹,就创建
      fs.mkdirSync(path);
    }
    const total = await this.count(_.cloneDeep(query));
    let skip = 0;
    const getHeader = require('../../public/patent/infoExport');
    const meta = getHeader();
    const projection = {};
    for (const m of meta) {
      const { key } = m;
      projection[key] = 1;
    }
    // 将数据分割,否则容易直接把js堆栈干满了,服务就炸了
    const head = meta.map(i => i.header);
    for (let i = 0; i < total; i = i + this.export_limit) {
      try {
        const workbook = new Excel.Workbook();
        const sheet = workbook.addWorksheet('sheet');
        sheet.addRow(head);
        const list = await this.query(query, { skip, limit: this.export_limit }, projection);
        for (let k = 0; k < list.length; k++) {
          const d = list[k];
          const arr = [];
          for (const m of meta) {
            const { key } = m;
            if (key === 'img_url') {
              const value = d[key];
              // 需要图片占列,否则会窜位置
              arr.push('');
              if (_.isArray(value)) {
                const img = _.get(_.head(value), 'url');
                if (img && img.includes('image/png')) {
                  const imgId = workbook.addImage({
                    base64: img,
                    extension: 'png',
                  });
                  const sheetRange = {
                    tl: { col: 13, row: k + 1 },
                    ext: { width: 60, height: 60 },
                    editAs: 'oneCell',
                  };
                  if (_.isFunction(sheet.addImage)) sheet.addImage(imgId, sheetRange);
                }
              }
            } else arr.push(d[key]);
          }
          sheet.addRow(arr);
        }
        skip = skip + this.export_limit;
        // 生成excel
        const filename = `专利信息导出结果(${i + 1}-${i + this.export_limit}).xlsx`;
        const filePath = `${path}${sep}${filename}`;
        console.log(filePath);
        await workbook.xlsx.writeFile(filePath);

        try {
          let progress = _.ceil((skip / total) * 100);
          if (progress > 95) progress = 95;
          const data = {
            progress,
            status: '1',
            id: missionid,
            remark: '组织数据生成excel中...',
          };
          await this.sendToMQ(data);
        } catch (error) {
          this.ctx.logger.error('更新进度失败');
        }
      } catch (error) {
        const data = {
          progress: 0,
          status: '-1',
          id: missionid,
          remark: '组织数据进入excel失败',
        };
        this.sendToMQ(data);
      }
    }

    try {
      const data = {
        progress: 98,
        status: '1',
        id: missionid,
        remark: '正在打包',
      };
      this.sendToMQ(data);
    } catch (error) {
      this.ctx.logger.error('正在打包');
    }

    try {
      const nowDate = new Date().getTime();
      const zipName = `导出专利信息-${nowDate}.zip`;
      const exportPath = `${this.root_path}${sep}${this.export_dir}`;
      if (!fs.existsSync(exportPath)) {
        // 如果不存在文件夹,就创建
        fs.mkdirSync(exportPath);
      }
      await compressing.zip.compressDir(path, `${exportPath}${sep}${zipName}`);
      const downloadPath = `/files/${this.export_dir}/${zipName}`;
      const data = {
        progress: 100,
        status: '2',
        uri: downloadPath,
        id: missionid,
        remark: '打包成功',
      };
      this.sendToMQ(data);
    } catch (error) {
      const data = {
        progress: 0,
        status: '-2',
        id: missionid,
        remark: '打包失败',
      };
      this.sendToMQ(data);
      this.ctx.logger.error('打包失败');
      this.ctx.logger.error(error);
    }
    this.dirDelete(path);
  }

  /**
   * 删除路径下的所有内容
   * @param {String} filePath 路径
   */
  async dirDelete(filePath) {
    if (fs.existsSync(filePath)) {
      const files = fs.readdirSync(filePath);
      for (const file of files) {
        const curPath = `${filePath}${sep}${file}`;
        if (fs.statSync(curPath).isDirectory()) {
          // recurse
          this.dirDelete(curPath);
        } else {
          // delete file
          fs.unlinkSync(curPath);
        }
      }
      fs.rmdirSync(filePath);
    }
  }

  async sendToMQ(data) {
    if (_.get(data, 'progress') && _.isNumber(parseFloat(_.get(data, 'progress'))) && parseFloat(_.get(data, 'progress')) > 100) {
      data.progress = 100;
    }
    console.log(`${_.get(data, 'progress') || '失败'} %`);
    const ch = await this.ctx.mq.conn.createChannel();
    const queue = 'mission/return';
    await ch.assertQueue(queue, { durable: false });
    await ch.sendToQueue(queue, Buffer.from(JSON.stringify(data)));
    await ch.close();
  }

  /**
   * 检查数据是否没填 必填项
   * @param {Object} object 每行数据,已转换成model的字段名
   */
  tocheckData(object) {
    let result = true;
    const { number } = object;
    let notice;
    const arr = [
      { column: 'create_number', zh: '申请号' },
      { column: 'create_date', zh: '申请日' },
      // { column: 'success_number', zh: '公开（公告）号' },
      // { column: 'success_date', zh: '公开（公告）日' },
      { column: 'name', zh: '标题' },
    ];
    const word = [];
    for (const o of arr) {
      const { column, zh } = o;
      if (!_.get(object, column)) {
        result = false;
        word.push(`${zh}`);
      }
    }
    if (!result) {
      notice = `序号${number}缺少:${word.join(';')}`;
    }
    return { result, notice };
  }
  /**
   * 转换图片为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;
    }
  }

  /**
   * 将excel的超链接对象中的url
   * @param {Object} object excel中的超链接对象
   */
  getUrl(object) {
    let urlStr = _.get(object, 'formula');
    let url = '';
    if (urlStr) {
      const s = urlStr.indexOf('(');
      urlStr = urlStr.substring(s);
      urlStr = _.trim(_.trim(urlStr, '('), ')');
      const midArr = urlStr.split(',');
      url = _.trim(_.head(midArr), '"');
    }
    return url;
  }

  async getByCreateNumber({ create_number }) {
    const data = await this.model.findOne({ create_number });
    return data;
  }

  async mechQuery({ skip = 0, limit = 0, ...query }) {
    const code = _.get(query, 'code');
    assert(code, '缺少机构信息!');
    const { empower_sort, ...others } = query;
    const newQuery = await this.toSetQuery(others);
    let sort = 'desc';
    if (empower_sort === '0') {
      sort = 'asc';
    }
    const data = await this.model.find(newQuery).sort({ empower_date: sort }).skip(parseInt(skip))
      .limit(parseInt(limit));
    const total = await this.model.count(newQuery);
    return { data, total };
  }

  async toSetQuery(query) {
    let queryObject = {};
    for (const key in query) {
      if (key === 'code') {
        const cquery = await this.dealCode(query[key]);
        queryObject = { ...queryObject, ...cquery };
      } else if (key === 'empower_year') {
        queryObject.empower_date = this.dealYearRange(query[key]);
      } else if (key === 'apply_year') {
        queryObject.create_date = this.dealYearRange(query[key]);
      } else if (key === 'ipc_type') {
        queryObject.ipc_type = new RegExp(`^${query[key]}`, 'i');
      } else if (key === 'field') {
        queryObject.abstract = new RegExp(query[key]);
      } else if (key === 'create_number') {
        queryObject.create_number = new RegExp(query[key]);
      } else if (key === 'first_inventor') {
        queryObject.inventor = new RegExp(`^${query[key]}`, 'i');
      } else if (key === 'on_obligee') {
        queryObject.on_obligee = new RegExp(`^${query[key]}`, 'i');
      } else if (key === 'type') {
        queryObject.type = new RegExp(`^${query[key]}`, 'i');
      } else if (key === 'is_empower') {
        let r = '';
        if (query[key] === '0') r = true;
        else r = false;
        queryObject.empower_date = { $exists: r };
      } else if (key === 'term') {
        queryObject.term = new RegExp(query[key]);
      } else if (key === 'is_lose') {
        const toDay = moment().format('YYYY-MM-DD');
        let r = '';
        if (query[key] === '0') r = { $gt: toDay };
        else r = { $lte: toDay };
        queryObject.lose_date = r;
      }
    }
    return queryObject;
  }
  async dealCode(code) {
    let pids = await this.personalModel.find({ code }, { _id: 1 });
    if (pids.length <= 0) return { data: [], total: 0 };
    pids = pids.map(i => i._id);
    const query = { 'inventor.user_id': { $in: pids } };
    return query;
  }
  async dealYearRange(year) {
    const start = `${year}-01-01`;
    const end = `${year}-12-31`;
    const obj = { $gte: start, $lte: end };
    return obj;
  }

  /**
   * 根据人名数组查询每个人的数据.组织数据返回
   * @param {Array[String]} nameList 名单
   */
  async toGetUser(nameList) {
    const res = await this.personalModel.find({ name: { $in: nameList } });
    const result = [];
    if (res && res.length > 0) {
      for (const i of res) {
        const { _id: id, name } = i;
        result.push({ id, name });
      }
    }
    return result;
  }

  async updateUser({ patentData, user_id }) {
    if (!patentData || !_.isArray(patentData)) {
      throw new BusinessError(ErrorCode.DATA_INVALID, '专利信息参数错误');
    }
    if (!user_id || !_.isArray(user_id)) {
      throw new BusinessError(ErrorCode.DATA_INVALID, '用户信息参数错误');
    }
    const data = await this.model.find({ _id: patentData.map(i => ObjectId(i)) });
    for (const i of data) {
      i.user_id = _.uniqBy([ ...i.user_id, ...user_id ], 'user_id');
      await i.save();
    }
  }

  /**
   * 批量删除
   * @param {Object} query 参数,查询范围
   */
  async deleteMany(query) {
    const newquery = await this.resetCode(query);
    const res = await this.model.deleteMany(newquery);
    return res;
  }
}

module.exports = PatentinfoService;