patentinfo.js 21 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539
  1. 'use strict';
  2. const assert = require('assert');
  3. const moment = require('moment');
  4. const Excel = require('exceljs');
  5. const Path = require('path');
  6. const _ = require('lodash');
  7. const { sep } = require('path');
  8. const fs = require('fs');
  9. const { CrudService } = require('naf-framework-mongoose/lib/service');
  10. const { BusinessError, ErrorCode } = require('naf-core').Error;
  11. const { trimData } = require('naf-core').Util;
  12. const { ObjectId } = require('mongoose').Types;
  13. // 专利信息
  14. class PatentinfoService extends CrudService {
  15. constructor(ctx) {
  16. super(ctx, 'patentinfo');
  17. this.model = this.ctx.model.Patent.Patentinfo;
  18. this.personalModel = this.ctx.model.Personal;
  19. this.organizationModel = this.ctx.model.Organization;
  20. this.root_path = _.get(this.ctx.app.config.export, 'root_path');
  21. if (process.env.NODE_ENV === 'development') { this.root_path = 'E:\\exportFile\\'; }
  22. this.file_type = '';
  23. if (!fs.existsSync(`${this.root_path}${this.file_type}`)) {
  24. // 如果不存在文件夹,就创建
  25. fs.mkdirSync(`${this.root_path}${this.file_type}`);
  26. }
  27. this.excel_path = `${sep}excel${sep}`;
  28. this.domain = 'http://127.0.0.1';
  29. if (process.env.NODE_ENV === 'development') { this.domain = 'http://127.0.0.1:9999'; }
  30. this.export_limit = 50;
  31. }
  32. async query(query, { skip = 0, limit = 0 }) {
  33. const newquery = await this.resetCode(query);
  34. const res = await this.model
  35. .find(newquery)
  36. .skip(parseInt(skip))
  37. .limit(parseInt(limit))
  38. .sort({ 'meta.createdAt': -1 });
  39. return res;
  40. }
  41. async count(query) {
  42. const newquery = await this.resetCode(query);
  43. const res = await this.model.countDocuments(trimData(newquery)).exec();
  44. return res;
  45. }
  46. async resetCode(query) {
  47. let newquery = _.cloneDeep(query);
  48. newquery = this.ctx.service.util.util.dealQuery(newquery);
  49. const { type } = newquery;
  50. if (type === 'else') {
  51. newquery.$and = [
  52. { type: { $ne: '发明' } },
  53. { type: { $ne: '实用新型' } },
  54. ];
  55. delete newquery.type;
  56. }
  57. const { code, user_id } = newquery;
  58. let ids = [];
  59. if (code) {
  60. const plist = await this.personalModel.find({ code });
  61. ids = plist.map(i => i._id);
  62. if (ids.length > 0) {
  63. newquery.inventor.user_id = { $in: ids };
  64. delete newquery.code;
  65. }
  66. } else if (user_id) {
  67. newquery.inventor.user_id = { $in: [ ObjectId(user_id) ] };
  68. }
  69. return newquery;
  70. }
  71. async queryByOrg({ code, status, term, skip = 0, limit = 0 }) {
  72. assert(code, '缺少机构信息');
  73. let pids = await this.personalModel.find({ code }, { _id: 1 });
  74. if (pids.length <= 0) return { data: [], total: 0 };
  75. pids = pids.map(i => i._id);
  76. const query = { 'inventor.user_id': { $in: pids } };
  77. if (status) query.status = status;
  78. if (term) query.term = term;
  79. console.log(query);
  80. const data = await this.model
  81. .find(query)
  82. .skip(parseInt(skip))
  83. .limit(parseInt(limit))
  84. .sort({ 'meta.createdAt': -1 });
  85. const total = await this.model.count(query);
  86. return { data, total };
  87. }
  88. async toImport({ uri, origin }) {
  89. assert(uri, '未获取到文件地址');
  90. const file = await this.ctx.curl(`${this.domain}${uri}`);
  91. if (!(file && file.data)) {
  92. throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '未找到指定文件');
  93. }
  94. const workbook = new Excel.Workbook();
  95. await workbook.xlsx.load(file.data);
  96. const sheet = workbook.getWorksheet(1);
  97. const arr = [];
  98. const allNotice = [];
  99. const sheetImageInfo = sheet.getImages();
  100. const imgids = _.compact(
  101. sheetImageInfo.map(i => {
  102. const { imageId, range } = i;
  103. const row = _.get(range, 'tl.nativeRow');
  104. if (row) return { row, imageId };
  105. })
  106. );
  107. sheet.eachRow(async (row, rindex) => {
  108. if (rindex !== 1) {
  109. // 组织数据,图片的索引和行索引不一致,准确的说是:图片索引比行索引少1
  110. // 原因:图片在工作簿中获取,按照1,2,3...顺序排序,但是行的第一行是表头(当前文件),所以当前行数需要减掉表头那一行
  111. const imgid = imgids.find(f => f.row === rindex - 1);
  112. const img_url = [];
  113. if (imgid) {
  114. img_url.push({
  115. url: this.turnImageToBase64(workbook.getImage(imgid.imageId)),
  116. });
  117. }
  118. const create_number = row.getCell(2).value || undefined,
  119. create_date =
  120. moment(row.getCell(3).value).format('YYYY-MM-DD') || undefined,
  121. success_number = row.getCell(4).value || undefined,
  122. success_date =
  123. moment(row.getCell(5).value).format('YYYY-MM-DD') || undefined,
  124. inventor = row.getCell(6).value || undefined,
  125. agent = row.getCell(7).value || undefined,
  126. agent_personal = row.getCell(8).value || undefined,
  127. abstract = row.getCell(9).value || undefined,
  128. address = row.getCell(10).value || undefined,
  129. name = row.getCell(11).value || undefined,
  130. apply_personal = row.getCell(12).value || undefined,
  131. term = row.getCell(13).value || undefined,
  132. type = row.getCell(14).value || undefined,
  133. number = row.getCell(1).value || undefined,
  134. // 新增专利数据属性2021-09-06
  135. nationality = row.getCell(16).value || undefined,
  136. ipc_type = row.getCell(17).value || undefined,
  137. onlegal_status = row.getCell(18).value || undefined,
  138. legal_status = row.getCell(19).value || undefined,
  139. law_date =
  140. moment(row.getCell(20).value).format('YYYY-MM-DD') || undefined,
  141. on_obligee = row.getCell(21).value || undefined,
  142. apply_address = row.getCell(22).value || undefined,
  143. apply_other = row.getCell(23).value || undefined,
  144. law_num = row.getCell(24).value || undefined,
  145. first_opendate =
  146. moment(row.getCell(25).value).format('YYYY-MM-DD') || undefined,
  147. empower_date =
  148. moment(row.getCell(26).value).format('YYYY-MM-DD') || undefined,
  149. lose_date =
  150. moment(row.getCell(27).value).format('YYYY-MM-DD') || undefined,
  151. examine_date =
  152. moment(row.getCell(28).value).format('YYYY-MM-DD') || undefined,
  153. invention_design = row.getCell(29).value || undefined;
  154. const obj = {
  155. create_number,
  156. create_date,
  157. success_number,
  158. success_date,
  159. inventor,
  160. agent,
  161. agent_personal,
  162. abstract,
  163. address,
  164. name,
  165. apply_personal,
  166. term,
  167. type,
  168. img_url,
  169. number,
  170. origin,
  171. user_id: [],
  172. // 新增专利数据属性2021-09-06
  173. nationality,
  174. ipc_type,
  175. onlegal_status,
  176. legal_status,
  177. law_date,
  178. on_obligee,
  179. apply_address,
  180. apply_other,
  181. law_num,
  182. first_opendate,
  183. empower_date,
  184. lose_date,
  185. examine_date,
  186. invention_design,
  187. };
  188. // 此处添加判断条件,不限制则不需要加,直接放过即可
  189. const { result, notice } = this.tocheckData(obj);
  190. if (result) {
  191. arr.push(obj);
  192. } else {
  193. allNotice.push(notice);
  194. }
  195. }
  196. });
  197. if (allNotice.length > 0) return allNotice;
  198. let nameList = [];
  199. for (const i of arr) {
  200. const { inventor } = i;
  201. const midList = inventor.split(/[,;/]/);
  202. nameList = [ ...nameList, ...midList ];
  203. }
  204. nameList = nameList.map(i => _.trim(i));
  205. const l1 = await this.personalModel.find({ name: nameList });
  206. const l2 = await this.organizationModel.find({ name: nameList });
  207. // 查出来的所有人
  208. const nList = [ ...l1, ...l2 ];
  209. for (const i of arr) {
  210. const { inventor } = i;
  211. let midNameList = inventor.split(/[,;/]/);
  212. midNameList = midNameList.map(i => _.trim(i));
  213. const iList = [];
  214. if (!_.isArray(i.user_id)) i.inventor = iList;
  215. for (const name of midNameList) {
  216. const rList = nList.filter(f => f.name === name);
  217. if (rList && rList.length > 0) {
  218. for (const r of rList) {
  219. iList.push({ user_id: r._id, name: r.name });
  220. }
  221. }
  222. }
  223. i.inventor = iList;
  224. }
  225. const res = await this.model.insertMany(arr);
  226. return res;
  227. }
  228. async toExport({ user }) {
  229. const data = {
  230. title: '专利导出',
  231. params: {
  232. project: 'market',
  233. service: 'patent.patentinfo',
  234. method: 'export',
  235. },
  236. user,
  237. tenant: 'live',
  238. };
  239. try {
  240. await this.ctx.service.util.httpUtil.cpost(
  241. '/api/mission',
  242. 'mission',
  243. data
  244. );
  245. } catch (error) {
  246. console.log(error);
  247. throw new BusinessError(ErrorCode.SERVICE_FAULT, '任务创建失败');
  248. }
  249. }
  250. async export({ missionid }) {
  251. const nowDate = new Date().getTime();
  252. const filename = `导出结果-${nowDate}.xlsx`;
  253. const path = `${this.root_path}${this.file_type}${this.excel_path}`;
  254. if (!path) {
  255. throw new BusinessError(ErrorCode.BUSINESS, '服务端没有设置存储路径');
  256. }
  257. if (!fs.existsSync(path)) {
  258. // 如果不存在文件夹,就创建
  259. fs.mkdirSync(path);
  260. }
  261. const total = await this.model.count();
  262. let skip = 0;
  263. let downloadPath;
  264. // 将数据分割,否则容易直接把js堆栈干满了,服务就炸了
  265. for (let i = 0; i < total; i = i + this.export_limit) {
  266. const list = await this.model.find().skip(skip).limit(this.export_limit);
  267. skip = skip + this.export_limit;
  268. downloadPath = await this.asyncExport(list, filename, path, downloadPath);
  269. try {
  270. const data = {
  271. progress: _.ceil((skip / total) * 100),
  272. status: '1',
  273. id: missionid,
  274. };
  275. this.ctx.service.util.httpUtil.cpost(
  276. '/api/mission/progress',
  277. 'mission',
  278. data
  279. );
  280. } catch (error) {
  281. this.logger.error(`任务id:${missionid},进度更新失败`);
  282. }
  283. }
  284. try {
  285. const data = {
  286. progress: 100,
  287. status: '2',
  288. uri: downloadPath,
  289. };
  290. await this.ctx.service.util.httpUtil.cpost(
  291. `/api/mission/update/${missionid}`,
  292. 'mission',
  293. data
  294. );
  295. } catch (error) {
  296. this.logger.error(`任务id:${missionid},已完成更新失败`);
  297. }
  298. return downloadPath;
  299. }
  300. async asyncExport(list, filename, path, downloadPath) {
  301. const workbook = new Excel.Workbook();
  302. let sheet;
  303. if (!downloadPath) {
  304. sheet = workbook.addWorksheet('sheet');
  305. } else {
  306. const file = await this.ctx.curl(`${this.domain}${downloadPath}`);
  307. if (!(file && file.data)) {
  308. throw new BusinessError(ErrorCode.DATA_NOT_EXIST, '未找到指定文件');
  309. }
  310. await workbook.xlsx.load(file.data);
  311. sheet = workbook.getWorksheet('sheet');
  312. }
  313. const rowNumber = sheet.rowCount || 1;
  314. const meta = this.getHeader();
  315. sheet.columns = meta;
  316. sheet.addRows(list);
  317. for (let i = 0; i < list.length; i++) {
  318. const e = list[i];
  319. const { img_url, _id } = e;
  320. try {
  321. if (img_url) {
  322. const is_base64 = img_url.includes('base64,');
  323. let imgid;
  324. if (is_base64) {
  325. imgid = workbook.addImage({
  326. base64: img_url,
  327. extension: 'jpeg',
  328. });
  329. } else if (img_url.includes('/files')) {
  330. const prefix = `${this.root_path}`;
  331. const new_url = img_url.replace('/files', prefix);
  332. const suffix = Path.extname(img_url).substring(1);
  333. imgid = workbook.addImage({
  334. filename: new_url,
  335. extension: suffix,
  336. });
  337. } else {
  338. // 什么都不是,那就下一个
  339. continue;
  340. }
  341. sheet.addImage(imgid, {
  342. tl: { col: 13.2, row: i + rowNumber + 0.2 },
  343. br: { col: 14, row: i + rowNumber + 1 },
  344. });
  345. }
  346. } catch (error) {
  347. this.ctx.logger.error(`导出,id为:${_id}的图片处理出现错误!`);
  348. }
  349. }
  350. const filepath = `${path}${filename}`;
  351. if (list.length <= 0) return;
  352. await workbook.xlsx.writeFile(filepath);
  353. return `/files/excel/${filename}`;
  354. }
  355. /**
  356. * 检查数据是否没填 必填项
  357. * @param {Object} object 每行数据,已转换成model的字段名
  358. */
  359. tocheckData(object) {
  360. let result = true;
  361. const { number } = object;
  362. let notice;
  363. const arr = [
  364. { column: 'create_number', zh: '申请号' },
  365. { column: 'create_date', zh: '申请日' },
  366. { column: 'success_number', zh: '公开(公告)号' },
  367. { column: 'success_date', zh: '公开(公告)日' },
  368. { column: 'name', zh: '标题' },
  369. ];
  370. const word = [];
  371. for (const o of arr) {
  372. const { column, zh } = o;
  373. if (!_.get(object, column)) {
  374. result = false;
  375. word.push(`${zh}`);
  376. }
  377. }
  378. if (!result) {
  379. notice = `序号${number}缺少:${word.join(';')}`;
  380. }
  381. return { result, notice };
  382. }
  383. /**
  384. * 转换图片为base64
  385. * @param {Object} object excel获取的图片object
  386. * @property extension 后缀,文件类型
  387. * @property buffer 图片内容,不含头部信息的,
  388. */
  389. turnImageToBase64(object = {}) {
  390. const { extension, buffer } = object;
  391. if (extension && buffer) {
  392. const suffix = object.extension;
  393. const ib = object.buffer.toString('base64');
  394. const base64 = `data:image/${suffix};base64,${ib}`;
  395. return base64;
  396. }
  397. }
  398. getHeader() {
  399. const arr = [
  400. { header: '申请号', key: 'create_number', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  401. { header: '申请日', key: 'create_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  402. { header: '公开(公告)号', key: 'success_number', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  403. { header: '公开(公告)日', key: 'success_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  404. { header: '发明人', key: 'inventor', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  405. { header: '代理机构', key: 'agent', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  406. { header: '代理人', key: 'agent_personal', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  407. { header: '摘要', key: 'abstract', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  408. { header: '发明人地址', key: 'address', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  409. { header: '标题', key: 'name', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  410. { header: '申请人', key: 'apply_personal', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  411. { header: '专利有效性', key: 'term', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  412. { header: '专利类型', key: 'type', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  413. { header: '首页附图', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  414. { header: '公开国别', key: 'nationality', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  415. { header: 'IPC主分类', key: 'ipc_type', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  416. { header: '当前法律状态', key: 'onlegal_status', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  417. { header: '法律状态', key: 'legal_status', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  418. { header: '法律文书日期', key: 'law_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  419. { header: '当前权利人', key: 'on_obligee', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  420. { header: '申请人地址(其他)', key: 'apply_address', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  421. { header: '申请人(其他)', key: 'apply_other', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  422. { header: '法律文书编号', key: 'law_num', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  423. { header: '首次公开日', key: 'first_opendate', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  424. { header: '授权公告日', key: 'empower_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  425. { header: '失效日', key: 'lose_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  426. { header: '实际审查失效日', key: 'examine_date', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  427. { header: '发明人(设计)其他', key: 'invention_design', width: 30, style: { alignment: { wrapText: true, vertical: 'middle', horizontal: 'center' } } },
  428. ];
  429. return arr;
  430. }
  431. async getByCreateNumber({ create_number }) {
  432. const data = await this.model.findOne({ create_number });
  433. return data;
  434. }
  435. async mechQuery({ skip = 0, limit = 0, ...query }) {
  436. const code = _.get(query, 'code');
  437. assert(code, '缺少机构信息!');
  438. const { empower_sort, ...others } = query;
  439. const newQuery = await this.toSetQuery(others);
  440. let sort = 'desc';
  441. if (empower_sort === '0') {
  442. sort = 'asc';
  443. }
  444. const data = await this.model.find(newQuery).sort({ empower_date: sort }).skip(parseInt(skip))
  445. .limit(parseInt(limit));
  446. const total = await this.model.count(newQuery);
  447. return { data, total };
  448. }
  449. async toSetQuery(query) {
  450. let queryObject = {};
  451. for (const key in query) {
  452. if (key === 'code') {
  453. const cquery = await this.dealCode(query[key]);
  454. queryObject = { ...queryObject, ...cquery };
  455. } else if (key === 'empower_year') {
  456. queryObject.empower_date = this.dealYearRange(query[key]);
  457. } else if (key === 'apply_year') {
  458. queryObject.create_date = this.dealYearRange(query[key]);
  459. } else if (key === 'ipc_type') {
  460. queryObject.ipc_type = new RegExp(`^${query[key]}`, 'i');
  461. } else if (key === 'field') {
  462. queryObject.abstract = new RegExp(query[key]);
  463. } else if (key === 'create_number') {
  464. queryObject.create_number = new RegExp(query[key]);
  465. } else if (key === 'first_inventor') {
  466. queryObject.inventor = new RegExp(`^${query[key]}`, 'i');
  467. } else if (key === 'on_obligee') {
  468. queryObject.on_obligee = new RegExp(`^${query[key]}`, 'i');
  469. } else if (key === 'type') {
  470. queryObject.type = new RegExp(`^${query[key]}`, 'i');
  471. } else if (key === 'is_empower') {
  472. let r = '';
  473. if (query[key] === '0') r = true;
  474. else r = false;
  475. queryObject.empower_date = { $exists: r };
  476. } else if (key === 'term') {
  477. queryObject.term = new RegExp(query[key]);
  478. } else if (key === 'is_lose') {
  479. const toDay = moment().format('YYYY-MM-DD');
  480. let r = '';
  481. if (query[key] === '0') r = { $gt: toDay };
  482. else r = { $lte: toDay };
  483. queryObject.lose_date = r;
  484. }
  485. }
  486. return queryObject;
  487. }
  488. async dealCode(code) {
  489. let pids = await this.personalModel.find({ code }, { _id: 1 });
  490. if (pids.length <= 0) return { data: [], total: 0 };
  491. pids = pids.map(i => i._id);
  492. const query = { user_id: { $elemMatch: { $in: pids } } };
  493. return query;
  494. }
  495. async dealYearRange(year) {
  496. const start = `${year}-01-01`;
  497. const end = `${year}-12-31`;
  498. const obj = { $gte: start, $lte: end };
  499. return obj;
  500. }
  501. /**
  502. * 根据人名数组查询每个人的数据.组织数据返回
  503. * @param {Array[String]} nameList 名单
  504. */
  505. async toGetUser(nameList) {
  506. const res = await this.personalModel.find({ name: { $in: nameList } });
  507. const result = [];
  508. if (res && res.length > 0) {
  509. for (const i of res) {
  510. const { _id: id, name } = i;
  511. result.push({ id, name });
  512. }
  513. }
  514. return result;
  515. }
  516. }
  517. module.exports = PatentinfoService;