service-live/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/lib/service');
const { BusinessError, ErrorCode } = require('naf-core').Error;
const { trimData } = require('naf-core').Util;
const { ObjectId } = require('mongoose').Types;
const { has } = require('lodash');

// 专利信息
class PatentinfoService extends CrudService {
  constructor(ctx) {
    super(ctx, 'patentinfo');
    this.model = this.ctx.model.Patent.Patentinfo;
    this.personalModel = this.ctx.model.Personal;
    this.organizationModel = this.ctx.model.Organization;

    this.root_path = _.get(this.ctx.app.config.export, 'root_path');
    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';
    if (process.env.NODE_ENV === 'development') {
      this.domain = 'http://127.0.0.1:9999';
    }
    this.export_limit = 50;
  }

  async query(query, { skip = 0, limit = 0 }) {
    const newquery = await this.resetCode(query);
    const res = await this.model.find(newquery).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) {
    let newquery = _.cloneDeep(query);
    newquery = this.ctx.service.util.util.dealQuery(newquery);
    if (Object.keys(newquery).length <= 0) return newquery;
    // 2021-11-03 没有其他的类型,不需要该查询条件
    // const { type } = newquery;
    // if (type === 'else') {
    //   newquery.$and = [
    //     { type: { $ne: '发明申请' } },
    //     { type: { $ne: '实用新型' } },
    //   ];
    //   delete newquery.type;
    // }
    // 2021-11-03 添加新查询条件:
    /**
     * {String} 作为申请号(create_number)字段的 第5位 字符串的查询条件
     */
    const apply_type = _.get(newquery, 'apply_type');
    if (apply_type) {
      const str = `^\\w{4}${apply_type}`;
      const reg = new RegExp(str);
      newquery.create_number = reg;
      delete newquery.apply_type;
    }
    const { code, user_id } = newquery;
    let ids = [];
    if (code) {
      const plist = await this.personalModel.find({ code });
      ids = plist.map((i) => i._id);
      if (ids.length > 0) {
        newquery['inventor.user_id'] = { $in: ids };
        delete newquery.code;
      }
    } else if (user_id) {
      newquery['inventor.user_id'] = ObjectId(user_id);
      delete newquery.user_id;
    }
    return newquery;
  }

  async queryByOrg({ code, status, term, skip = 0, limit = 0 }) {
    assert(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 } };
    if (status) query.status = status;
    if (term) query.term = term;
    const data = await this.model.find(query).skip(parseInt(skip)).limit(parseInt(limit)).sort({ 'meta.createdAt': -1 });
    const total = await this.model.count(query);
    return { data, total };
  }

  async toImport({ uri, origin }) {
    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 };
      })
    );
    sheet.eachRow(async (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)),
          });
        }

        const create_number = row.getCell(2).value || undefined,
          create_date = moment(row.getCell(3).value).format('YYYY-MM-DD') || undefined,
          success_number = row.getCell(4).value || undefined,
          success_date = moment(row.getCell(5).value).format('YYYY-MM-DD') || undefined,
          inventor = row.getCell(6).value || undefined,
          agent = row.getCell(7).value || undefined,
          agent_personal = row.getCell(8).value || undefined,
          abstract = row.getCell(9).value || undefined,
          address = row.getCell(10).value || undefined,
          name = row.getCell(11).value || undefined,
          apply_personal = row.getCell(12).value || undefined,
          term = row.getCell(13).value || undefined,
          type = row.getCell(14).value || undefined,
          number = row.getCell(1).value || undefined,
          // 新增专利数据属性2021-09-06
          nationality = row.getCell(16).value || undefined,
          ipc_type = row.getCell(17).value || undefined,
          onlegal_status = row.getCell(18).value || undefined,
          legal_status = row.getCell(19).value || undefined,
          law_date = moment(row.getCell(20).value).format('YYYY-MM-DD') || undefined,
          on_obligee = row.getCell(21).value || undefined,
          apply_address = row.getCell(22).value || undefined,
          apply_other = row.getCell(23).value || undefined,
          law_num = row.getCell(24).value || undefined,
          first_opendate = moment(row.getCell(25).value).format('YYYY-MM-DD') || undefined,
          empower_date = moment(row.getCell(26).value).format('YYYY-MM-DD') || undefined,
          lose_date = moment(row.getCell(27).value).format('YYYY-MM-DD') || undefined,
          examine_date = moment(row.getCell(28).value).format('YYYY-MM-DD') || undefined,
          invention_design = row.getCell(29).value || undefined;
        // 2021-11-15 添加申请号的验证,如果有该申请号的专利,则不需要进库,也不需要报错
        const has_data = await this.model.count({ create_number });
        if (!has_data) {
          const obj = {
            create_number,
            create_date,
            success_number,
            success_date,
            inventor,
            agent,
            agent_personal,
            abstract,
            address,
            name,
            apply_personal,
            term,
            type,
            img_url,
            number,
            origin,
            user_id: [],
            // 新增专利数据属性2021-09-06
            nationality,
            ipc_type,
            onlegal_status,
            legal_status,
            law_date,
            on_obligee,
            apply_address,
            apply_other,
            law_num,
            first_opendate,
            empower_date,
            lose_date,
            examine_date,
            invention_design,
          };
          // 此处添加判断条件,不限制则不需要加,直接放过即可
          const { result, notice } = this.tocheckData(obj);
          if (result) {
            arr.push(obj);
          } else {
            allNotice.push(notice);
          }
        }
      }
    });
    if (allNotice.length > 0) return allNotice;

    let nameList = [];
    for (const i of arr) {
      const { inventor } = i;
      const midList = inventor.split(/[,;/]/);
      nameList = [...nameList, ...midList];
    }
    nameList = nameList.map((i) => _.trim(i));
    const l1 = await this.personalModel.find({ name: nameList });
    const l2 = await this.organizationModel.find({ name: nameList });
    // 查出来的所有人
    const nList = [...l1, ...l2];
    for (const i of arr) {
      const { inventor } = i;
      let midNameList = inventor.split(/[,;/]/);
      midNameList = midNameList.map((i) => _.trim(i));
      const iList = [];
      if (!_.isArray(i.user_id)) i.inventor = iList;
      for (const name of midNameList) {
        const rList = nList.filter((f) => f.name === name);
        if (rList && rList.length > 0) {
          for (const r of rList) {
            iList.push({ user_id: r._id, name: r.name });
          }
        } else {
          // 2021-11-09 修改:如果导入的人,没有账号信息.则不填写id,将姓名添加进去
          iList.push({ name });
        }
      }
      i.inventor = iList;
    }
    const res = await this.model.insertMany(arr);
    return res;
  }
  async toExport({ user }) {
    const data = {
      title: '专利导出',
      params: {
        project: 'market',
        service: 'patent.patentinfo',
        method: 'export',
      },
      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 }) {
    const nowDate = new Date().getTime();
    const filename = `导出结果-${nowDate}.xlsx`;
    const path = `${this.root_path}${this.file_type}${this.excel_path}`;
    if (!path) {
      throw new BusinessError(ErrorCode.BUSINESS, '服务端没有设置存储路径');
    }
    if (!fs.existsSync(path)) {
      // 如果不存在文件夹,就创建
      fs.mkdirSync(path);
    }
    const total = await this.model.count();
    let skip = 0;
    let downloadPath;
    // 将数据分割,否则容易直接把js堆栈干满了,服务就炸了
    for (let i = 0; i < total; i = i + this.export_limit) {
      const list = await this.model.find().skip(skip).limit(this.export_limit);
      skip = skip + this.export_limit;
      downloadPath = await this.asyncExport(list, filename, path, downloadPath);
      try {
        const data = {
          progress: _.ceil((skip / total) * 100),
          status: '1',
          id: missionid,
        };
        this.ctx.service.util.httpUtil.cpost('/api/mission/progress', 'mission', data);
      } catch (error) {
        this.logger.error(`任务id:${missionid},进度更新失败`);
      }
    }
    try {
      const data = {
        progress: 100,
        status: '2',
        uri: downloadPath,
      };
      await this.ctx.service.util.httpUtil.cpost(`/api/mission/update/${missionid}`, 'mission', data);
    } catch (error) {
      this.logger.error(`任务id:${missionid},已完成更新失败`);
    }
    return downloadPath;
  }

  async asyncExport(list, filename, path, downloadPath) {
    const workbook = new Excel.Workbook();
    let sheet;
    if (!downloadPath) {
      sheet = workbook.addWorksheet('sheet');
    } else {
      const file = await this.ctx.curl(`${this.domain}${downloadPath}`);
      if (!(file && file.data)) {
        throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '未找到指定文件');
      }
      await workbook.xlsx.load(file.data);
      sheet = workbook.getWorksheet('sheet');
    }
    const rowNumber = sheet.rowCount || 1;
    const meta = this.getHeader();
    sheet.columns = meta;
    sheet.addRows(list);
    for (let i = 0; i < list.length; i++) {
      const e = list[i];
      const { img_url, _id } = e;
      try {
        if (img_url) {
          const is_base64 = img_url.includes('base64,');
          let imgid;
          if (is_base64) {
            imgid = workbook.addImage({
              base64: img_url,
              extension: 'jpeg',
            });
          } else if (img_url.includes('/files')) {
            const prefix = `${this.root_path}`;
            const new_url = img_url.replace('/files', prefix);
            const suffix = Path.extname(img_url).substring(1);
            imgid = workbook.addImage({
              filename: new_url,
              extension: suffix,
            });
          } else {
            // 什么都不是,那就下一个
            continue;
          }

          sheet.addImage(imgid, {
            tl: { col: 13.2, row: i + rowNumber + 0.2 },
            br: { col: 14, row: i + rowNumber + 1 },
          });
        }
      } catch (error) {
        this.ctx.logger.error(`导出,id为:${_id}的图片处理出现错误!`);
      }
    }
    const filepath = `${path}${filename}`;
    if (list.length <= 0) return;
    await workbook.xlsx.writeFile(filepath);
    return `/files/excel/${filename}`;
  }

  /**
   * 检查数据是否没填 必填项
   * @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;
    }
  }

  getHeader() {
    const arr = [
      { header: '申请号', key: 'create_number', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '申请日', key: 'create_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '公开（公告）号', key: 'success_number', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '公开（公告）日', key: 'success_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '发明人', key: 'inventor', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '代理机构', key: 'agent', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '代理人', key: 'agent_personal', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '摘要', key: 'abstract', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '发明人地址', key: 'address', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '标题', key: 'name', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '申请人', key: 'apply_personal', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '专利有效性', key: 'term', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '专利类型', key: 'type', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '首页附图', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '公开国别', key: 'nationality', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: 'IPC主分类', key: 'ipc_type', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '当前法律状态', key: 'onlegal_status', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '法律状态', key: 'legal_status', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '法律文书日期', key: 'law_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '当前权利人', key: 'on_obligee', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '申请人地址(其他)', key: 'apply_address', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '申请人(其他)', key: 'apply_other', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '法律文书编号', key: 'law_num', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '首次公开日', key: 'first_opendate', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '授权公告日', key: 'empower_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '失效日', key: 'lose_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '实际审查失效日', key: 'examine_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
      { header: '发明人(设计)其他', key: 'invention_design', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
    ];
    return arr;
  }

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

module.exports = PatentinfoService;