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

import { Provide } from '@midwayjs/core';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { isArray } from 'lodash';
import { Repository } from 'typeorm';
import { Achievement } from '../../entity/platform/achievement.entity';
import { Demand } from '../../entity/platform/demand.entity';
import { Expert } from '../../entity/users/expert.entity';
import * as Excel from 'exceljs';
import * as path from 'path';
import { Tags } from '../../entity/system/tags.entity';
// 2024-06-11 导入
@Provide()
export class InitOneService {
  // 本次导入公共标签
  tags = ['20240611'];
  @InjectEntityModel(Tags)
  tagsModel: Repository<Tags>;
  @InjectEntityModel(Demand)
  demandModel: Repository<Demand>;
  @InjectEntityModel(Achievement)
  achievementModel: Repository<Achievement>;
  @InjectEntityModel(Expert)
  expertModel: Repository<Expert>;
  async initData() {
    console.log('in initData');
    // await this.importRequirementFromExcel();
    // await this.importAchieveFromExcel();
    // await this.importExpertFromExcel();
  }
  async dataToUse() {
    await this.achievementModel.update({}, { is_use: '0', status: '1' });
  }
  async addTags() {
    const list = [{ title: '20240611' }, { 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() {
    // 查询achievementModel和expertModel的所有数据,并为其添加标签
    const achieveTags: any = [...this.tags, '成果'];
    const expertTags: any = [...this.tags, '专家'];
    await this.achievementModel.update({}, { tags: achieveTags });
    await this.expertModel.update({}, { tags: expertTags });
    // 查询demandModel 2024-06-10 至 2024-06-12 (其实就是2024-06-11)的数据,添加标签,之后24号的数据在initTwo中添加
    const demandTags: any = [...this.tags, '需求'];
    await this.demandModel.createQueryBuilder().update().set({ tags: demandTags }).where('created_time BETWEEN :start AND :end', { start: '2024-06-10', end: '2024-06-12' }).execute();
  }
  async importRequirementFromExcel() {
    const p = path.resolve(__dirname, '../../../importData/20240611', '需求库.xlsx');
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(p);
    const sheet = wb.getWorksheet(1);
    const meta = this.requirementMeta();
    const allData = [];
    const cityArr = ['长春', '吉林', '通化', '白山', '辽源', '四平', '白城', '松原', '延边州', '延边'];
    const areaArr = [
      '五棵树经济开发区',
      '南关区',
      '宽城区',
      '朝阳区',
      '二道区',
      '绿园区',
      '双阳区',
      '九台区',
      '公主岭市',
      '榆树市',
      '德惠市',
      '农安县',
      '昌邑区',
      '龙潭区',
      '船营区',
      '丰满区',
      '蛟河市',
      '桦甸市',
      '舒兰市',
      '磐石市',
      '永吉县',
      '东昌区',
      '二道江区',
      '集安市',
      '梅河口市',
      '通化县',
      '辉南县',
      '柳河县',
      '浑江区',
      '江源区',
      '临江市',
      '抚松县',
      '靖宇县',
      '长白朝鲜族自治县',
      '龙山区',
      '西安区',
      '东丰县',
      '东辽县',
      '铁西区',
      '铁东区',
      '双辽市',
      '梨树县',
      '伊通满族自治县',
      '洮北区',
      '洮南市',
      '大安市',
      '镇赉县',
      '通榆县',
      '宁江区',
      '扶余市',
      '长岭县',
      '乾安县',
      '前郭尔罗斯蒙古族自治县',
      '延吉市',
      '图们市',
      '敦化市',
      '珲春市',
      '龙井市',
      '和龙市',
      '汪清县',
      '安图县',
    ];
    const specialList = [
      { e: '高新区', to: '高新技术产业开发区' },
      { e: '高新', to: '高新技术产业开发区' },
      { e: '经开', to: '经济技术开发区' },
      { e: '五棵树', to: '五棵树经济开发区' },
      { e: '北湖', to: '北湖科技开发区' },
      { e: '净月', to: '净月高新技术产业开发区' },
      { e: '绿园经济', to: '绿园经济开发区' },
    ];
    const ignoreArea = ['吉林', '长春', '白城'];
    const specialAreaList = [
      { e: '梅河口', to: ['吉林省', '通化市', '梅河口市'] },
      { e: '德惠', to: ['吉林省', '长春市', '德惠市'] },
      { e: '敦化', to: ['吉林省', '延边州', '敦化市'] },
      { e: '延吉', to: ['吉林省', '延边州', '延吉市'] },
      { e: '柳河', to: ['吉林省', '通化市', '柳河县'] },
    ];
    sheet.eachRow((row, ri) => {
      if (ri === 1 || ri === 2) {
        // 不处理
      } else {
        const obj = {};
        row.eachCell((cell, ci) => {
          const val = cell.value as string;
          const key = meta[ci];
          if (ci === 3) {
            let area = ['吉林省'];
            // 处理地区
            const res = cityArr.find(f => val.includes(f));
            if (res) {
              // 有市级关键词的, 延边州不需要处理
              if (res === '延边') area.push('延边州');
              else if (res !== '延边州') area.push(`${res}市`);
              else area.push(res);
              const elseStr = val.replace(res, '');
              if (elseStr !== '' && !ignoreArea.includes(elseStr)) {
                const areaVal = areaArr.find(f => f.includes(elseStr));
                if (areaVal) area.push(areaVal);
                else {
                  const rs = specialList.find(f => elseStr.includes(f.e));
                  if (rs) area.push(rs.to);
                }
              }
            } else {
              const res = specialAreaList.find(f => val.includes(f.e));
              if (res) area = res.to;
              else obj['no_area'] = true;
            }
            obj[key] = area;
          } else if (ci === 6) {
            // 分离人员和电话
            const strReg = /([\u4e00-\u9fa5]{3}|[\u4e00-\u9fa5]{2})/g;
            const strRes = val.match(strReg);
            const numRes = val.replace(strReg, '').replace('\n', '');
            if (isArray(strRes)) obj['contacts'] = strRes.join(';');
            if (isArray(numRes)) obj['tel'] = numRes.join(';');
            if (numRes) obj['tel'] = numRes;
          } else if (key) obj[key] = val;
        });
        allData.push(obj);
      }
    });
    const res = await this.demandModel.insert(allData);
    return res;
  }

  requirementMeta() {
    return {
      2: 'field',
      3: 'area',
      4: 'company',
      5: 'brief',
      6: 'contacts',
      7: 'year',
    };
  }

  async importAchieveFromExcel() {
    const p = path.resolve(__dirname, '../../../importData/20240611', '成果库.xlsx');
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(p);
    const sheet = wb.getWorksheet(1);
    const meta = this.achieveMeta();
    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);
      }
    });
    return await this.achievementModel.insert(allData);
  }
  achieveMeta() {
    return {
      2: 'field',
      3: 'name',
      4: 'brief',
      5: 'source',
      6: 'person',
      7: 'tel',
    };
  }

  async importExpertFromExcel() {
    const p = path.resolve(__dirname, '../../../importData/20240611', '专家库.xlsx');
    const wb = new Excel.Workbook();
    await wb.xlsx.readFile(p);
    const sheet = wb.getWorksheet(1);
    const rows = sheet.getRows(3, 200);
    const meta = this.exportsMeta();
    const allData = [];
    for (const row of rows) {
      const obj = {};
      row.eachCell((cell, index) => {
        let val = cell.value as string;
        if (index === 4) {
          const varr = val.split('').filter(f => f !== ' ');
          val = varr.join('');
        }
        const key = meta[index];
        obj[key] = val;
      });
      allData.push(obj);
    }
    const res = await this.expertModel.insert(allData);
    return res;
  }

  exportsMeta() {
    return {
      1: 'industry_type',
      2: 'industry',
      3: 'work_type',
      4: 'name',
      5: 'work',
      6: 'title',
    };
  }
}