cxyy-service/src/service/initData/initTwo.service.ts

import { Provide } from '@midwayjs/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import * as Excel from 'exceljs';
import * as path from 'path';
import { And, Equal, Like, Not, Repository } from 'typeorm';
import { Region } from '../../entity/system/region.entity';
import { Company } from '../../entity/users/company.entity';
import { DictData } from '../../entity/system/dictData.entity';
import { Supply } from '../../entity/platform/supply.entity';
import { differenceWith, get, isEqual, isObject, isString, last, take, trim } from 'lodash';
import { Demand } from '../../entity/platform/demand.entity';
import _ = require('lodash');
import { Project } from '../../entity/platform/project.entity';
import { Tags } from '../../entity/system/tags.entity';
// 2024-06-24 导入
@Provide()
export class initTwoService {
  tags = ['20240624'];
  @InjectEntityModel(Tags)
  tagsModel: Repository<Tags>;
  @InjectEntityModel(Region)
  regionModel: Repository<Region>;
  @InjectEntityModel(Company)
  companyModel: Repository<Company>;
  @InjectEntityModel(DictData)
  dictDataModel: Repository<DictData>;
  @InjectEntityModel(Supply)
  supplyModel: Repository<Supply>;
  @InjectEntityModel(Demand)
  demandModel: Repository<Demand>;
  @InjectEntityModel(Project)
  projectModel: Repository<Project>;

  async initData() {
    // return await this.importCompany();
    // return await this.importSupply();
    // return await this.importDemand();
    // return await this.importProject();
  }
  async addTags() {
    const list = [{ title: '20240624' }, { title: '企业' }, { title: '供给' }, { title: '项目' }, { title: '需求' }];
    for (const i of list) {
      const num = await this.tagsModel.createQueryBuilder().where('title = :title', { title: i.title }).getCount();
      if (num <= 0) {
        await this.tagsModel.insert(i);
      }
    }
  }

  async addImportDataTags() {
    // company,supply,project 直接全表更新tags
    const ctags: any = [...this.tags, '企业'];
    await this.companyModel.update({}, { tags: ctags });
    const stags: any = [...this.tags, '供给'];
    await this.supplyModel.update({}, { tags: stags });
    const ptags: any = [...this.tags, '项目'];
    await this.projectModel.update({}, { tags: ptags });
    // demand 2024-06-23 至2024-06-25内的所有数据加标签
    const demandTags: any = [...this.tags, '需求'];
    await this.demandModel.createQueryBuilder().update().set({ tags: demandTags }).where('created_time BETWEEN :start AND :end', { start: '2024-06-23', end: '2024-06-25' }).execute();
  }

  /**
   * 获取excel表头对应的字段
   * @returns 表格列对应字段对象
   */
  companyMeta() {
    return {
      2: 'area1',
      3: 'area2',
      4: 'name',
      5: 'products',
      6: 'type',
      7: 'person',
    };
  }
  async importCompany() {
    const p = path.resolve(__dirname, '../../../importData/20240624', '重点企业库.xlsx');
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(p);
    const sheet = wb.getWorksheet('总表');
    if (!sheet) return;
    const meta = this.companyMeta();
    const specialDeal = {
      新区: '新区',
      高新区: '长春高新技术产业开发区',
      经开: '长春经济技术开发区',
      中韩: '中韩国际合作示范区',
      汽开: '长春汽车经济技术开发区',
      // 医药高新区: '',
      // 中新食品区: '',
    };
    const allData = [];
    sheet.eachRow((row, ri) => {
      if (ri === 1 || ri === 2) {
        // 不处理
      } else {
        const obj = {};
        row.eachCell((cell, ci) => {
          const val = cell.value;
          const key = meta[ci];
          if (key) obj[key] = val;
        });
        allData.push(obj);
      }
    });
    // 需要处理下 area1和area2,这俩是需要放到 area中,在region中找到合适的数据后放到area []里
    for (const i of allData) {
      // #region 地区处理
      i.area = ['吉林省'];
      if (i.area2 === '医药高新区') {
        i.area.push('通化市', '医药高新区');
      } else if (i.area2 === '中新食品区') {
        i.area.push('吉林市', '医药高新区');
      } else {
        const builder = this.regionModel.createQueryBuilder();
        builder.where(`name like '%${i.area1}%'`);
        // builder.andWhere(`name Not Like '%省%'`);
        builder.andWhere(`level = 'city'`);
        builder.andWhere(`code like '22%'`);
        const r1 = await builder.getOne();
        const r2 = await this.regionModel.findOne({ where: { name: Like(`%${i.area2}%`), code: Like('22%'), level: Equal('area') } });
        if (r1) i.area.push(r1.name);
        if (r2) i.area.push(r2.name);
        else {
          // 需要特殊处理的
          const str = specialDeal[i.area2];
          if (str) i.area.push(str);
        }
      }
      delete i.area1;
      delete i.area2;
      // #endregion
      // #region 字典处理
      if (i.type === '农林牧渔业') {
        i.type = '0';
      } else if (i.type === '卫生和社会工作') {
        i.type = '16';
      } else {
        const typeDict = await this.dictDataModel.findOne({ where: { label: i.type } });
        if (typeDict) {
          i.type = typeDict.value;
        }
      }
      // #endregion
    }
    await this.companyModel.insert(allData);
  }

  supplyMeta() {
    return {
      3: 'industry',
      4: 'field',
      5: 'name',
      6: 'brief',
      7: 'source',
    };
  }

  async importSupply() {
    const p = path.resolve(__dirname, '../../../importData/20240624', '供应库.xlsx');
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(p);
    const sheet = wb.getWorksheet('总库');
    if (!sheet) return;
    const meta = this.supplyMeta();
    const allData = [];
    sheet.eachRow((row, ri) => {
      if (ri === 1 || ri === 2) {
        // 不处理
      } else {
        const obj = {};
        row.eachCell((cell, ci) => {
          const val = cell.value;
          const key = meta[ci];
          if (key) obj[key] = val;
        });
        allData.push(obj);
      }
    });
    // TODO:将产业,领域换成字典表

    // return allData;
    await this.supplyModel.insert(allData);
  }

  demandMeta() {
    return {
      4: 'industry',
      5: 'field',
      6: 'area1',
      7: 'area2',
      8: 'company',
      9: 'tec_name',
      10: 'company_brief',
      11: 'question',
      12: 'brief',
      13: 'year',
      14: 'month',
      15: 'contacts',
      16: 'tel',
    };
  }
  async importDemand() {
    const p = path.resolve(__dirname, '../../../importData/20240624', '需求库.xlsx');
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(p);
    const sheet = wb.getWorksheet('3-技术需求库-打印');
    if (!sheet) return;
    const meta = this.demandMeta();
    const specialArea1Deal = {
      延边州: '延边朝鲜族自治州',
    };
    const specialArea2Deal = {
      新区: '新区',
      高新区: '长春高新技术产业开发区',
      经开: '长春经济技术开发区',
      中韩: '中韩国际合作示范区',
      汽开: '长春汽车经济技术开发区',
      汽开区: '长春汽车经济技术开发区',
      // 医药高新区: '',
      // 中新食品区: '',
    };
    const allData = [];
    sheet.eachRow((row, ri) => {
      if (ri === 1 || ri === 2 || ri === 3) {
        // 不处理
      } else {
        const obj = {};
        row.eachCell((cell, ci) => {
          const val = cell.value;
          const key = meta[ci];
          if (key) obj[key] = val;
        });
        if (get(obj, 'company') && trim(get(obj, 'company')) !== '') allData.push(obj);
      }
    });
    for (const i of allData) {
      // #region 地区处理
      i.area = ['吉林省'];
      if (i.area2 === '医药高新区') {
        i.area.push('通化市', '医药高新区');
      } else if (i.area2 === '中新食品区') {
        i.area.push('吉林市', '医药高新区');
      } else {
        const builder = this.regionModel.createQueryBuilder();
        builder.where(`name like '%${i.area1}%'`);
        // builder.andWhere(`name Not Like '%省%'`);
        builder.andWhere(`level = 'city'`);
        builder.andWhere(`code like '22%'`);
        const r1 = await builder.getOne();
        const r2 = await this.regionModel.findOne({ where: { name: Like(`%${i.area2}%`), code: Like('22%'), level: Equal('area') } });
        if (r1) {
          i.area.push(r1.name);
          delete i.area1;
        } else {
          const area = specialArea1Deal[i.area1];
          if (area) {
            i.area.push(area);
            delete i.area1;
          }
        }
        if (r2) {
          i.area.push(r2.name);
          delete i.area2;
        } else {
          // 需要特殊处理的
          const area = specialArea2Deal[i.area2];
          if (area) {
            i.area.push(area);
          }
          // 还有一种情况就是省市都写的一个.就不需要第二个了,所以最后一定删除
          delete i.area2;
        }
      }
      // #endregion
    }
    // return allData.filter(f => f.area1 || f.area2);
    await this.demandModel.insert(allData);
  }

  projectMeta() {
    return {
      3: 'name',
      4: 'brief',
      5: 'main',
      6: 'progress',
      7: 'track_unit',
      8: 'source',
    };
  }
  projectIndustryMeta() {
    return [
      { size: 33, value: '汽车电子及新型汽车零部件' },
      { size: 30, value: '光电子及智能传感器' },
      { size: 24, value: '精密仪器及先进装备' },
      { size: 60, value: '生物医药及先进医疗器械' },
      { size: 4, value: '车规级芯片及功率半导体器件' },
      { size: 13, value: '人工智能及智能机器人' },
      { size: 24, value: '生物基及高性能新材料' },
      { size: 25, value: '新能源及动力电池' },
      { size: 10, value: '碳纤维及复合材料' },
      { size: 5, value: '遥感卫星及航空航天' },
      { size: 10, value: '精细化工及天然气化工' },
      { size: 20, value: '农产品加工及绿色食品' },
      { size: 5, value: '孵化平台' },
      { size: 9, value: '产业技术创新平台' },
    ];
  }
  async importProject() {
    const p = path.resolve(__dirname, '../../../importData/20240624', '项目库.xlsx');
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(p);
    const sheet = wb.getWorksheet('统计表');
    if (!sheet) return;
    const meta = this.projectMeta();
    let allData = [];
    const notDealRows = [1, 2, 3, 4, 5];
    const industryMeta = this.projectIndustryMeta();
    sheet.eachRow((row, ri) => {
      if (notDealRows.includes(ri)) {
        // 不处理
      } else {
        const obj = {};
        row.eachCell((cell, ci) => {
          const val = cell.value;
          const key = meta[ci];
          if (key) {
            obj[key] = val;
          }
        });
        const res = industryMeta.find(f => f.value === get(obj, 'name'));
        if (!res) {
          for (const key in obj) {
            if (!isString(obj[key])) {
              const rtobj = get(obj[key], 'richText');
              let text = '';
              if (rtobj) {
                for (const i of rtobj) {
                  text = `${text}${i.text}`;
                }
              }
              obj[key] = text;
            }
          }
          allData.push(obj);
        }
      }
    });
    // 分组,然后给加上产业字段
    const newData = [];
    for (const meta of industryMeta) {
      const { size, value } = meta;
      const nds = take(allData, size);
      for (const i of nds) {
        i.industry = value;
      }
      newData.push(...nds);
      // 删除提取出来的数据
      allData = differenceWith(allData, nds, isEqual);
    }
    await this.projectModel.insert(newData);
    // return newData;
  }
}