cxyy-service/src/service/asyncExport.service.ts

import { Config, Inject, Provide } from '@midwayjs/core';
import { Demand } from '../entity/platform/demand.entity';
import { Admin } from '../entity/system/admin.entity';
import { Dept } from '../entity/system/dept.entity';
import { DictData } from '../entity/system/dictData.entity';
import { DictType } from '../entity/system/dictType.entity';
import { Menus } from '../entity/system/menus.entity';
import { Message } from '../entity/system/message.entity';
import { Region } from '../entity/system/region.entity';
import { Role } from '../entity/system/role.entity';
import { Tags } from '../entity/system/tags.entity';
import { User } from '../entity/system/user.entity';
import { UserMenus } from '../entity/system/userMenus.entity';
import { ApplyCompany } from '../entity/users/applyCompany.entity';
import { Association } from '../entity/users/association.entity';
import { Cirelation } from '../entity/users/cirelation.entity';
import { Company } from '../entity/users/company.entity';
import { CompanyYear } from '../entity/users/companyYear.entity';
import { Competition } from '../entity/users/competition.entity';
import { ContactApply } from '../entity/users/contactApply.entity';
import { Expert } from '../entity/users/expert.entity';
import { Incubator } from '../entity/users/incubator.entity';
import { IncubatorYear } from '../entity/users/incubatorYear.entity';
import { Investment } from '../entity/users/investment.entity';
import { School } from '../entity/users/school.entity';
import { State } from '../entity/users/state.entity';
import { Unit } from '../entity/users/unit.entity';
import { Achievement } from '../entity/platform/achievement.entity';
import { Collection } from '../entity/platform/collection.entity';
import { Design } from '../entity/platform/design.entity';
import { Directory } from '../entity/platform/directory.entity';
import { Footplate } from '../entity/platform/footplate.entity';
import { Friend } from '../entity/platform/friend.entity';
import { Journal } from '../entity/platform/journal.entity';
import { Match } from '../entity/platform/match.entity';
import { MatchPath } from '../entity/platform/matchPath.entity';
import { News } from '../entity/platform/news.entity';
import { Notes } from '../entity/platform/notes.entity';
import { Project } from '../entity/platform/project.entity';
import { Score } from '../entity/platform/score.entity';
import { Sector } from '../entity/platform/sector.entity';
import { Sign } from '../entity/platform/sign.entity';
import { Supply } from '../entity/platform/supply.entity';
import { Support } from '../entity/platform/support.entity';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { Repository } from 'typeorm';
import { ExportMission } from '../entity/exportMission.entity';
import { divide, get, isFinite, isObject, isString, upperFirst } from 'lodash';
import { ExportConfigService } from './exportConfig.service';
import { completeBuilderCondition } from '../frame/conditionBuilder';
import * as dayjs from 'dayjs';
import * as Excel from 'exceljs';
import * as Path from 'path';
import * as fs from 'fs';
import { ErrorCode, ServiceError } from '../error/service.error';
import { ExportConfig } from '../entity/exportConfig.entity';

@Provide()
export class AsyncExportService {
  @InjectEntityModel(ExportMission)
  model: Repository<ExportMission>;
  @InjectEntityModel(ExportConfig)
  ecService: Repository<ExportConfig>;
  //数据步进值
  limit = 50;

  async checkHaveDataInQuery(table, query = {}) {
    const model: any = get(this, `_model_${table}`);
    // 抛出异常: 该数据未经过导出设置,无法导出
    if (!model) throw new ServiceError(ErrorCode.NO_EXPORT_SETTING);
    const builder = model.createQueryBuilder();
    const nq = this.checkColumnInTable(model, query);
    completeBuilderCondition(builder, nq);
    const total = await builder.getCount();
    // 抛出异常: 未查询到可导出的数据
    if (total <= 0) throw new ServiceError(ErrorCode.NO_DATA_IN_EXPORT_QUERY);
  }

  async execute(id) {
    const mission = await this.model.createQueryBuilder().where('"id" = :id', { id }).getOne();
    // 没有任务,不需要执行
    if (!mission) return false;
    // 没有配置,不需要执行
    const config = get(mission, 'config');
    if (!config) return;
    /**表名 */
    let table: string = get(config, 'table');
    // 没有表,不需要执行
    if (!table) return;
    table = upperFirst(table);
    /**用户选择的字段 */
    const cc: Array<string> = get(config, 'config');
    // 没有导出配置,不需要执行
    if (!cc) return;
    /**根据表名找到完整的配置 */
    const configData = await this.ecService.createQueryBuilder().where('"table" =:table', { table }).getOne();
    // 需要通过cc换正常的配置
    // 未找到配置,不需要执行
    if (!configData) return;
    const colConfigs: Array<any> = get(configData, 'config');
    if (!colConfigs) return;
    /**任务存储的导出字段完整配置 */
    const selectCols = colConfigs.filter(f => cc.includes(f.column));
    // 计算数据总量
    /**查询范围 */
    const query = get(config, 'query', {});
    const model: any = get(this, `_model_${table}`);
    // 没找到model,不需要执行
    if (!model) return;
    const builder = model.createQueryBuilder();
    const nq = this.checkColumnInTable(model, query);
    completeBuilderCondition(builder, nq);
    /**数据总数,计算进度用 */
    const total = await builder.getCount();
    // 找数据,处理数据,写入数据
    let skip = 0;
    const fileName = `${table}-${dayjs().format('YYYYMMDDHHmmss')}.xlsx`;
    let downloadPath;
    /**循环标志 */
    let whileContinue = true;
    const head = selectCols.map(i => i.zh);
    while (whileContinue) {
      const writeInExcel = [];
      if (skip === 0) writeInExcel.push(head);
      const list = await builder.skip(skip).take(this.limit).getMany();
      if (list.length <= 0) {
        // 没有数据就不需要继续循环了
        whileContinue = false;
        break;
      }
      skip = skip + this.limit;
      /**处理数据 */
      const excelData = await this.dealData(list, selectCols);
      writeInExcel.push(...excelData);

      /**写入数据 */
      downloadPath = await this.writeExcel(fileName, writeInExcel);
      try {
        // 计算,更新进度
        let per = Math.ceil(divide(skip, total) * 100);
        if (per > 100) per = 100;
        this.model.update({ id }, { progress: per });
      } catch (error) {
        console.log(`mission id:${id}; skip:${skip}`);
      }
    }
    await this.model.update({ id }, { progress: 100, uri: downloadPath });
  }

  async dealData(list, cols) {
    const returnData = [];
    for (const i of list) {
      const excelData = [];
      for (const c of cols) {
        const column = get(c, 'column');
        // 取值
        let value = get(i, column);
        // 空值下一个
        if (!value || value === null) {
          // 空字符串站位,否则列对不上
          excelData.push('');
          continue;
        }
        // 数据类型验证
        const type = get(c, 'type');
        value = this.checkValueType(value, type);
        // 验证失败或空字符串,直接下一个
        if (value === '') {
          excelData.push(value);
          continue;
        }
        const source = get(c, 'source');
        const from = get(c, 'from');
        const from_column = get(c, 'from_column');
        value = await this.getValueFromSource(value, source, from, from_column);
        excelData.push(value);
      }
      returnData.push(excelData);
    }
    return returnData;
  }
  /**
   * 验证数据是否匹配数据类型,不匹配的返回空字符串
   * @param value 数据库中的值
   * @param type 数据类型
   * @returns 空字符串 ''
   */
  checkValueType(value, type) {
    let returnData = '';
    switch (type) {
      case 'string':
        if (isString(value)) returnData = value;
        break;
      case 'integer':
        if (isFinite(value)) returnData = value;
        break;
      case 'text':
        if (isString(value)) returnData = value;
        break;
      case 'date':
        if (dayjs(value).isValid()) returnData = value;
        break;
      case 'jsonb':
        if (isObject(value)) returnData = JSON.stringify(value);
        break;
      default:
        break;
    }
    return returnData;
  }

  /**
   * 根据来源,转换值
   * @param value 数据库中的值
   * @param source 数据来源
   * @param from 字典/关联表
   * @param from_column 具体使用的字段
   */
  async getValueFromSource(value, source, from, from_column) {
    if (source === 'dict') {
      // 查出字典数据
      const dictData = await this._model_DictData.createQueryBuilder().where('"code" =:code', { code: from }).getMany();
      // 没有字典数据,直接返回原值
      if (dictData.length <= 0) return value;
      let dict_column = from_column;
      if (!dict_column) dict_column = 'value';
      const dict = dictData.find(f => get(f, dict_column) === value);
      // 没找到,返回原值
      if (!dict) return value;
      // 找到了,返回label
      return get(dict, 'label');
    } else if (source === 'relation') {
      const table = upperFirst(from);
      const model = this[`_model_${table}`];
      if (!model) return value;
      const relationData = await model.createQueryBuilder().where('"id" =:id', { id: value }).getOne();
      // 没找到关联的数据
      if (!relationData) return value;
      // 找到了,就取指定字段
      return get(relationData, from_column);
    } else return value;
  }

  getModelName(name) {
    const model = this[`_model_${upperFirst(name)}`];
    if (!model) return;
    return get(model, 'metadata.comment');
  }
  getDict_tables() {
    const keys = Object.keys(this).filter(f => f.includes('_model_'));
    const tableList = keys.map(i => {
      const model = this[i];
      const label = get(model, 'metadata.comment');
      const table = get(model, 'metadata.name');
      return { label, table };
    });
    return tableList;
  }

  @Config('PathConfig.path')
  path;

  async writeExcel(fileName, data) {
    const path = this.path;
    if (!path) {
      throw new ServiceError('服务端没有设置存储路径');
    }
    if (!fs.existsSync(path)) {
      // 如果不存在文件夹,就创建
      this.mkdir(path);
    }
    const fullPath = Path.resolve(path, fileName);
    const has_file = fs.existsSync(fullPath);
    const workbook = new Excel.Workbook();
    let sheet;
    if (!has_file) {
      // 没有文件,第一次,需要生成
      sheet = workbook.addWorksheet('sheet');
    } else {
      await workbook.xlsx.readFile(fullPath);
      sheet = workbook.getWorksheet('sheet');
    }
    sheet.addRows(data);
    await workbook.xlsx.writeFile(fullPath);
    return `/files/cxyy/export/${fileName}`;
  }

  // 创建文件夹
  mkdir(dirname) {
    if (fs.existsSync(dirname)) {
      return true;
    }
    if (this.mkdir(Path.dirname(dirname))) {
      fs.mkdirSync(dirname);
      return true;
    }
  }
  /**检查查询条件是否在表中有字段 */
  checkColumnInTable(model, query) {
    const columns = get(model, 'metadata.columns', []);
    const nq = {};
    for (const key in query) {
      const column = columns.find(f => get(f, 'propertyName') === key);
      if (!column) continue;
      const val = query[key];
      nq[key] = val;
    }
    return nq;
  }

  @InjectEntityModel(Admin)
  _model_Admin: Repository<Admin>;
  @InjectEntityModel(Dept)
  _model_Dept: Repository<Dept>;
  @InjectEntityModel(DictData)
  _model_DictData: Repository<DictData>;
  @InjectEntityModel(DictType)
  _model_DictType: Repository<DictType>;
  @InjectEntityModel(Menus)
  _model_Menus: Repository<Menus>;
  @InjectEntityModel(Message)
  _model_Message: Repository<Message>;
  @InjectEntityModel(Region)
  _model_Region: Repository<Region>;
  @InjectEntityModel(Role)
  _model_Role: Repository<Role>;
  @InjectEntityModel(Tags)
  _model_Tags: Repository<Tags>;
  @InjectEntityModel(User)
  _model_User: Repository<User>;
  @InjectEntityModel(UserMenus)
  _model_UserMenus: Repository<UserMenus>;
  @InjectEntityModel(ApplyCompany)
  _model_ApplyCompany: Repository<ApplyCompany>;
  @InjectEntityModel(Association)
  _model_Association: Repository<Association>;
  @InjectEntityModel(Cirelation)
  _model_Cirelation: Repository<Cirelation>;
  @InjectEntityModel(Company)
  _model_Company: Repository<Company>;
  @InjectEntityModel(CompanyYear)
  _model_CompanyYear: Repository<CompanyYear>;
  @InjectEntityModel(Competition)
  _model_Competition: Repository<Competition>;
  @InjectEntityModel(ContactApply)
  _model_ContactApply: Repository<ContactApply>;
  @InjectEntityModel(Expert)
  _model_Expert: Repository<Expert>;
  @InjectEntityModel(Incubator)
  _model_Incubator: Repository<Incubator>;
  @InjectEntityModel(IncubatorYear)
  _model_IncubatorYear: Repository<IncubatorYear>;

  @InjectEntityModel(Investment)
  _model_Investment: Repository<Investment>;
  @InjectEntityModel(School)
  _model_School: Repository<School>;
  @InjectEntityModel(State)
  _model_State: Repository<State>;
  @InjectEntityModel(Unit)
  _model_Unit: Repository<Unit>;
  @InjectEntityModel(Achievement)
  _model_Achievement: Repository<Achievement>;
  @InjectEntityModel(Collection)
  _model_Collection: Repository<Collection>;
  @InjectEntityModel(Demand)
  _model_Demand: Repository<Demand>;
  @InjectEntityModel(Design)
  _model_Design: Repository<Design>;
  @InjectEntityModel(Directory)
  _model_Directory: Repository<Directory>;
  @InjectEntityModel(Footplate)
  _model_Footplate: Repository<Footplate>;
  @InjectEntityModel(Friend)
  _model_Friend: Repository<Friend>;
  @InjectEntityModel(Journal)
  _model_Journal: Repository<Journal>;
  @InjectEntityModel(Match)
  _model_Match: Repository<Match>;
  @InjectEntityModel(MatchPath)
  _model_MatchPath: Repository<MatchPath>;
  @InjectEntityModel(News)
  _model_News: Repository<News>;
  @InjectEntityModel(Notes)
  _model_Notes: Repository<Notes>;
  @InjectEntityModel(Project)
  _model_Project: Repository<Project>;
  @InjectEntityModel(Score)
  _model_Score: Repository<Score>;
  @InjectEntityModel(Sector)
  _model_Sector: Repository<Sector>;
  @InjectEntityModel(Sign)
  _model_Sign: Repository<Sign>;
  @InjectEntityModel(Supply)
  _model_Supply: Repository<Supply>;
  @InjectEntityModel(Support)
  _model_Support: Repository<Support>;
}