cxyy-service/src/service/util.service.ts

import { Inject, Provide, Config } from '@midwayjs/core';
import { get, uniq } from 'lodash';
import { Context } from '@midwayjs/koa';
import { InjectEntityModel } from '@midwayjs/typeorm';
import { ServiceError, ErrorCode } from '../error/service.error';
import dayjs = require('dayjs');
import { DictDataService } from './system/dictData.service';
import { Equal, Repository, IsNull } from 'typeorm';
import { Achievement } from '../entity/platform/achievement.entity';
import { Company } from '../entity/users/company.entity';
import { Supply } from '../entity/platform/supply.entity';
import { Project } from '../entity/platform/project.entity';
import { Demand } from '../entity/platform/demand.entity';
import { UserService } from './system/user.service';
import * as mappings from '../public/importMapping';
import * as Excel from 'exceljs';
import * as Path from 'path';
import * as fs from 'fs';
import { User } from '../entity/system/user.entity';
import { CompanyYear } from '../entity/users/companyYear.entity';
import { Expert } from '../entity/users/expert.entity';
import { RoleService } from './system/role.service';
import { Incubator } from '../entity/users/incubator.entity';
import { SectorService } from './platform/sector.service';
/**
 * 工具类服务,为其他地方提供一些通用的函数
 */
@Provide()
export class UtilService {
  @Inject()
  ctx: Context;
  @Inject()
  userService: UserService;
  @InjectEntityModel(User)
  userModel: Repository<User>;
  @InjectEntityModel(Achievement)
  achievementModel: Repository<Achievement>;
  @InjectEntityModel(Project)
  projectModel: Repository<Project>;
  @InjectEntityModel(Demand)
  demandModel: Repository<Demand>;
  @InjectEntityModel(Supply)
  supplyModel: Repository<Supply>;
  @InjectEntityModel(Company)
  companyModel: Repository<Company>;
  @InjectEntityModel(Expert)
  expertModel: Repository<Expert>;
  @InjectEntityModel(Incubator)
  incubatorModel: Repository<Incubator>;
  @InjectEntityModel(CompanyYear)
  yModel: Repository<CompanyYear>;
  @Inject()
  dictDataService: DictDataService;
  @Inject()
  sectorService: SectorService;
  @Inject()
  roleService: RoleService;

  @Config('PathConfig.path')
  path;

  // 管理端数据看板
  async oneStatistics(type) {
    let list = [];
    // 企业统计数量
    if (type === 'company') {
      list = [
        { name: '企业总数', data: [], percentage: 1 },
        { name: '高新技术企业', data: [], percentage: 1 },
        { name: '专精特新企业', data: [], percentage: 1 },
        { name: '审核成功', data: [], percentage: 1 },
      ];
      for (const [index, val] of list.entries()) {
        let count = 0;
        if (index === 0) {
          count = await this.companyModel.count();
          val.data.push(count);
        } else if (index === 1) {
          count = await this.companyModel.count({ where: { is_tech: '0' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.companyModel.count());
        } else if (index === 2) {
          count = await this.companyModel.count({ where: { is_new: '0' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.companyModel.count());
        } else {
          count = await this.companyModel.count({ where: { status: '1' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.companyModel.count());
        }
      }
    }
    // 专家统计数量
    if (type === 'expert') {
      list = [
        { name: '专家总数', data: [], percentage: 1 },
        { name: '高校、科研院所类', data: [], percentage: 1 },
        { name: '企业类', data: [], percentage: 1 },
        { name: '审核成功', data: [], percentage: 1 },
      ];
      for (const [index, val] of list.entries()) {
        let count = 0;
        if (index === 0) {
          count = await this.expertModel.count();
          val.data.push(count);
        } else if (index === 1) {
          count = await this.expertModel.count({ where: { work_type: '高校、科研院所' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.expertModel.count());
        } else if (index === 2) {
          count = await this.expertModel.count({ where: { work_type: '企业' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.expertModel.count());
        } else {
          count = await this.expertModel.count({ where: { status: '1' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.expertModel.count());
        }
      }
    }
    // 需求统计数量
    if (type === 'demand') {
      list = [
        { name: '需求总数', data: [], percentage: 1 },
        { name: '没有联系方式', data: [], percentage: 1 },
        { name: '是否公开', data: [], percentage: 1 },
        { name: '审核成功', data: [], percentage: 1 },
      ];
      for (const [index, val] of list.entries()) {
        let count = 0;
        if (index === 0) {
          count = await this.demandModel.count();
          val.data.push(count);
        } else if (index === 1) {
          count = await this.demandModel.count({ where: { tel: IsNull() } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.demandModel.count());
        } else if (index === 2) {
          count = await this.demandModel.count({ where: { is_use: '0' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.demandModel.count());
        } else {
          count = await this.demandModel.count({ where: { status: '1' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.demandModel.count());
        }
      }
    }
    // 供给统计数量
    if (type === 'supply') {
      list = [
        { name: '供给总数', data: [], percentage: 1 },
        { name: '没有价格', data: [], percentage: 1 },
        { name: '是否公开', data: [], percentage: 1 },
        { name: '审核成功', data: [], percentage: 1 },
      ];
      for (const [index, val] of list.entries()) {
        let count = 0;
        if (index === 0) {
          count = await this.supplyModel.count();
          val.data.push(count);
        } else if (index === 1) {
          count = await this.supplyModel.count({ where: { money: IsNull() } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.supplyModel.count());
        } else if (index === 2) {
          count = await this.supplyModel.count({ where: { is_use: '0' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.supplyModel.count());
        } else {
          count = await this.supplyModel.count({ where: { status: '1' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.supplyModel.count());
        }
      }
    }
    // 成果统计数量
    if (type === 'achievement') {
      list = [
        { name: '成果总数', data: [], percentage: 1 },
        { name: '没有联系方式', data: [], percentage: 1 },
        { name: '是否公开', data: [], percentage: 1 },
        { name: '审核成功', data: [], percentage: 1 },
      ];
      for (const [index, val] of list.entries()) {
        let count = 0;
        if (index === 0) {
          count = await this.achievementModel.count();
          val.data.push(count);
        } else if (index === 1) {
          count = await this.achievementModel.count({ where: { tel: IsNull() } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.achievementModel.count());
        } else if (index === 2) {
          count = await this.achievementModel.count({ where: { is_use: '0' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.achievementModel.count());
        } else {
          count = await this.achievementModel.count({ where: { status: '1' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.achievementModel.count());
        }
      }
    }
    // 项目统计数量
    if (type === 'project') {
      list = [
        { name: '项目总数', data: [], percentage: 1 },
        { name: '工研院跟踪单位', data: [], percentage: 1 },
        { name: '是否公开', data: [], percentage: 1 },
        { name: '审核成功', data: [], percentage: 1 },
      ];
      for (const [index, val] of list.entries()) {
        let count = 0;
        if (index === 0) {
          count = await this.projectModel.count();
          val.data.push(count);
        } else if (index === 1) {
          count = await this.projectModel.count({ where: { track_unit: '工研院' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.projectModel.count());
        } else if (index === 2) {
          count = await this.projectModel.count({ where: { is_use: '0' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.projectModel.count());
        } else {
          count = await this.projectModel.count({ where: { status: '1' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.projectModel.count());
        }
      }
    }
    //孵化器统计数量
    if (type === 'incubate') {
      list = [
        { name: '孵化基地总数', data: [], percentage: 1 },
        { name: '是否具备中试场地', data: [], percentage: 1 },
        { name: '是否和平台合作标识', data: [], percentage: 1 },
        { name: '审核成功', data: [], percentage: 1 },
      ];
      for (const [index, val] of list.entries()) {
        let count = 0;
        if (index === 0) {
          count = await this.incubatorModel.count();
          val.data.push(count);
        } else if (index === 1) {
          count = await this.incubatorModel.count({ where: { is_have: '0' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.incubatorModel.count());
        } else if (index === 2) {
          count = await this.incubatorModel.count({ where: { cooperate: '0' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.incubatorModel.count());
        } else {
          count = await this.incubatorModel.count({ where: { status: '1' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.incubatorModel.count());
        }
      }
    }
    // 用户统计数量
    if (type === 'user') {
      list = [
        { name: '用户总数', data: [], percentage: 1 },
        { name: '未微信绑定', data: [], percentage: 1 },
        { name: '未选所属产业', data: [], percentage: 1 },
        { name: '审核成功', data: [], percentage: 1 },
      ];
      for (const [index, val] of list.entries()) {
        let count = 0;
        if (index === 0) {
          count = await this.userModel.count();
          val.data.push(count);
        } else if (index === 1) {
          count = await this.userModel.count({ where: { openid: IsNull() } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.userModel.count());
        } else if (index === 2) {
          count = await this.userModel.count({ where: { industry: IsNull() } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.userModel.count());
        } else {
          count = await this.userModel.count({ where: { status: '1' } });
          val.data.push(count);
          val.percentage = await this.calculatePercentage(count, await this.userModel.count());
        }
      }
    }
    return list;
  }

  async twoStatistics(type) {
    let res;
    let req;
    const nameList = [];
    const list = [];
    // 企业按规模统计
    if (type === 'company') {
      res = await this.companyModel.createQueryBuilder('company').select('company.pattern', 'pattern').addSelect('COUNT(company.id)', 'total').groupBy('company.pattern').getRawMany();
      req = await this.dictDataService.query({ code: 'companyType', is_use: '0' }, {});
      if (req.data) {
        for (const val of req.data) {
          nameList.push(val.label);
          const select = res.find(f => f.pattern === val.value);
          if (select) list.push(select.total);
          else list.push(0);
        }
      }
    }
    // 专家按职称统计
    if (type === 'expert') {
      res = await this.expertModel.createQueryBuilder('expert').select('expert.title', 'title').addSelect('COUNT(expert.id)', 'total').groupBy('expert.title').getRawMany();
      for (const val of res) {
        nameList.push(val.title);
        list.push(val.total);
      }
    }
    // 需求按行业领域统计
    if (type === 'demand') {
      res = await this.demandModel.createQueryBuilder('demand').select('demand.field', 'field').addSelect('COUNT(demand.id)', 'total').groupBy('demand.field').getRawMany();
      for (const val of res) {
        if (val.field) {
          nameList.push(val.field);
          list.push(val.total);
        }
      }
    }
    // 供给按行业领域统计
    if (type === 'supply') {
      res = await this.supplyModel.createQueryBuilder('supply').select('supply.field', 'field').addSelect('COUNT(supply.id)', 'total').groupBy('supply.field').getRawMany();
      for (const val of res) {
        if (val.field) {
          nameList.push(val.field);
          list.push(val.total);
        }
      }
    }
    // 项目按来源统计
    if (type === 'project') {
      res = await this.projectModel.createQueryBuilder('project').select('project.source', 'source').addSelect('COUNT(project.id)', 'total').groupBy('project.source').getRawMany();
      for (const val of res) {
        if (val.source) {
          nameList.push(val.source);
          list.push(val.total);
        }
      }
    }
    // 成果按来源统计
    if (type === 'achievement') {
      res = await this.achievementModel.createQueryBuilder('achievement').select('achievement.source', 'source').addSelect('COUNT(achievement.id)', 'total').groupBy('achievement.source').getRawMany();
      for (const val of res) {
        if (val.source) {
          nameList.push(val.source);
          list.push(val.total);
        }
      }
    }
    //孵化器入驻数量
    if (type === 'incubate') {
      res = await this.incubatorModel.createQueryBuilder('incubator').getMany();
      for (const val of res) {
        nameList.push(val.name);
        list.push(parseInt(val.company_num || 0));
      }
    }
    //用户所属产业包含总数
    if (type === 'user') {
      req = await this.sectorService.query({ is_use: '0' }, {});
      if (req.data) {
        for (const val of req.data) {
          const total = await this.userModel
            .createQueryBuilder()
            .where('industry IS NOT NULL') // 确保username字段不为空
            .andWhere('JSONB_EXISTS(industry, :column)', { column: val.title }) // 使用数组作为查询条件
            .getCount(); // 获取总数
          nameList.push(val.title);
          list.push({ name: val.title, value: total || 0 });
        }
      }
    }
    return { nameList, list };
  }

  async thrStatistics(type) {
    let nameList = [];
    let list = [];
    let res;
    let req;
    // 企业行业领域统计
    if (type === 'company') {
      res = await this.companyModel.createQueryBuilder('company').select('company.type', 'type').addSelect('COUNT(company.id)', 'total').groupBy('company.type').getRawMany();
      req = await this.dictDataService.query({ code: 'companyIndustry', is_use: '0' }, {});
      for (const val of res) {
        if (req.data) {
          const select = req.data.find(f => f.value === val.type);
          if (select) {
            nameList.push(select.label);
            list.push({ name: select.label, value: val.total });
          }
        }
      }
    }
    // 专家行业领域统计
    if (type === 'expert') {
      res = await this.expertModel.createQueryBuilder('expert').select('expert.industry_type', 'industry_type').addSelect('COUNT(expert.id)', 'total').groupBy('expert.industry_type').getRawMany();
      for (const val of res) {
        if (val.industry_type) {
          nameList.push(val.industry_type);
          list.push({ name: val.industry_type, value: val.total });
        }
      }
    }
    // 需求所属产业统计
    if (type === 'demand') {
      res = await this.demandModel.createQueryBuilder('demand').select('demand.industry', 'industry').addSelect('COUNT(demand.id)', 'total').groupBy('demand.industry').getRawMany();
      for (const val of res) {
        if (val.industry) {
          nameList.push(val.industry);
          list.push({ name: val.industry, value: val.total });
        }
      }
    }
    // 供给所属产业统计
    if (type === 'supply') {
      res = await this.supplyModel.createQueryBuilder('supply').select('supply.industry', 'industry').addSelect('COUNT(supply.id)', 'total').groupBy('supply.industry').getRawMany();
      for (const val of res) {
        if (val.industry) {
          nameList.push(val.industry);
          list.push({ name: val.industry, value: val.total });
        }
      }
    }
    // 项目项目进展统计
    if (type === 'project') {
      res = await this.projectModel.createQueryBuilder('project').select('project.progress', 'progress').addSelect('COUNT(project.id)', 'total').groupBy('project.progress').getRawMany();
      for (const val of res) {
        if (val.progress) {
          nameList.push(val.progress);
          list.push({ name: val.progress, value: val.total });
        }
      }
    }
    // 成果行业领域统计
    if (type === 'achievement') {
      res = await this.achievementModel.createQueryBuilder('achievement').select('achievement.field', 'field').addSelect('COUNT(achievement.id)', 'total').groupBy('achievement.field').getRawMany();
      for (const val of res) {
        if (val.field) {
          nameList.push(val.field);
          list.push({ name: val.field, value: val.total });
        }
      }
    }
    // 孵化器市级以上活动数统计数量
    if (type === 'incubate') {
      res = await this.incubatorModel.createQueryBuilder('incubator').getMany();
      for (const val of res) {
        nameList.push(val.name);
        list.push({ name: val.name, value: parseInt(val.actCity_num) || 0 });
      }
    }
    // 用户角色统计
    if (type === 'user') {
      res = await this.userModel.createQueryBuilder('expert').select('expert.role', 'role').addSelect('COUNT(expert.id)', 'total').groupBy('expert.role').getRawMany();
      req = await this.roleService.query({ is_use: '0' }, {});
      for (const val of res) {
        if (req.data) {
          for (const ass of val.role) {
            if (ass !== 'User') {
              const select = req.data.find(f => f.code === ass);
              if (select) {
                nameList.push(select.name);
                list.push({ name: select.name, value: parseInt(val.total) });
              }
            }
          }
        }
      }
      nameList = uniq(nameList);
      list = list.reduce((acc, obj) => {
        const existingObj = acc.find(item => item.name === obj.name);
        if (existingObj) {
          existingObj.value += obj.value;
        } else {
          acc.push({ ...obj });
        }
        return acc;
      }, []);
    }
    return { nameList, list };
  }

  async fourStatistics(type) {
    let list = [];
    let res;
    // 企业地区分布
    if (type === 'company') {
      res = await this.companyModel.createQueryBuilder('company').select('company.area', 'area').addSelect('COUNT(company.id)', 'total').groupBy('company.area').getRawMany();
      for (const val of res) {
        if (val.area && val.area.length > 1) {
          const area = val.area[1];
          list.push({ name: area, value: parseInt(val.total) });
        }
      }
      list = list.reduce((acc, obj) => {
        const existingObj = acc.find(item => item.name === obj.name);
        if (existingObj) {
          existingObj.value += obj.value;
        } else {
          acc.push({ ...obj });
        }
        return acc;
      }, []);
    }
    // 专家地区分布
    if (type === 'expert') {
      res = await this.expertModel.createQueryBuilder('expert').select('expert.area', 'area').addSelect('COUNT(expert.id)', 'total').groupBy('expert.area').getRawMany();
      for (const val of res) {
        if (val.area && val.area.length > 1) {
          const area = val.area[1];
          list.push({ name: area, value: parseInt(val.total) });
        }
      }
      list = list.reduce((acc, obj) => {
        const existingObj = acc.find(item => item.name === obj.name);
        if (existingObj) {
          existingObj.value += obj.value;
        } else {
          acc.push({ ...obj });
        }
        return acc;
      }, []);
    }
    // 需求地区分布
    if (type === 'demand') {
      res = await this.demandModel.createQueryBuilder('demand').select('demand.area', 'area').addSelect('COUNT(demand.id)', 'total').groupBy('demand.area').getRawMany();
      for (const val of res) {
        if (val.area && val.area.length > 1) {
          const area = val.area[1];
          list.push({ name: area, value: parseInt(val.total) });
        }
      }
      list = list.reduce((acc, obj) => {
        const existingObj = acc.find(item => item.name === obj.name);
        if (existingObj) {
          existingObj.value += obj.value;
        } else {
          acc.push({ ...obj });
        }
        return acc;
      }, []);
    }
    // 供给地区分布
    if (type === 'supply') {
      res = await this.supplyModel.createQueryBuilder('supply').select('supply.area', 'area').addSelect('COUNT(supply.id)', 'total').groupBy('supply.area').getRawMany();
      for (const val of res) {
        if (val.area && val.area.length > 1) {
          const area = val.area[1];
          list.push({ name: area, value: parseInt(val.total) });
        }
      }
      list = list.reduce((acc, obj) => {
        const existingObj = acc.find(item => item.name === obj.name);
        if (existingObj) {
          existingObj.value += obj.value;
        } else {
          acc.push({ ...obj });
        }
        return acc;
      }, []);
    }
    // 成果地区分布
    if (type === 'achievement') {
      res = await this.achievementModel.createQueryBuilder('achievement').select('achievement.area', 'area').addSelect('COUNT(achievement.id)', 'total').groupBy('achievement.area').getRawMany();
      for (const val of res) {
        if (val.area && val.area.length > 1) {
          const area = val.area[1];
          list.push({ name: area, value: parseInt(val.total) });
        }
      }
      list = list.reduce((acc, obj) => {
        const existingObj = acc.find(item => item.name === obj.name);
        if (existingObj) {
          existingObj.value += obj.value;
        } else {
          acc.push({ ...obj });
        }
        return acc;
      }, []);
    }
    // 项目地区分布
    if (type === 'project') {
      res = await this.projectModel.createQueryBuilder('project').select('project.area', 'area').addSelect('COUNT(project.id)', 'total').groupBy('project.area').getRawMany();
      for (const val of res) {
        if (val.area && val.area.length > 1) {
          const area = val.area[1];
          list.push({ name: area, value: parseInt(val.total) });
        }
      }
      list = list.reduce((acc, obj) => {
        const existingObj = acc.find(item => item.name === obj.name);
        if (existingObj) {
          existingObj.value += obj.value;
        } else {
          acc.push({ ...obj });
        }
        return acc;
      }, []);
    }
    // 孵化基地地区分布
    if (type === 'incubate') {
      res = await this.incubatorModel.createQueryBuilder('incubator').select('incubator.area', 'area').addSelect('COUNT(incubator.id)', 'total').groupBy('incubator.area').getRawMany();
      for (const val of res) {
        if (val.area && val.area.length > 1) {
          const area = val.area[1];
          list.push({ name: area, value: parseInt(val.total) });
        }
      }
      list = list.reduce((acc, obj) => {
        const existingObj = acc.find(item => item.name === obj.name);
        if (existingObj) {
          existingObj.value += obj.value;
        } else {
          acc.push({ ...obj });
        }
        return acc;
      }, []);
    }
    return list;
  }

  async fiveStatistics(type) {
    let res;
    let req;
    const nameList = [];
    const list = [];
    //用户所属产业包含总数
    if (type === 'user') {
      req = await this.sectorService.query({ is_use: '0' }, {});
      if (req.data) {
        for (const val of req.data) {
          const total = await this.userModel
            .createQueryBuilder()
            .where('industry IS NOT NULL') // 确保username字段不为空
            .andWhere('JSONB_EXISTS(industry, :column)', { column: val.title }) // 使用数组作为查询条件
            .getCount(); // 获取总数
          nameList.push(val.title);
          list.push({ name: val.title, value: total || 0 });
        }
      }
    }
    return { nameList, list };
  }

  async sixStatistics(type) {
    let res;
    let req;
    const nameList = [];
    const list = [];
    let max = [];
    // 企业按员工人数排序取前8个企业
    if (type === 'company') {
      res = await this.companyModel.createQueryBuilder('company').orderBy('company.person', 'DESC').limit(8).getMany();
      for (const val of res) {
        nameList.push(val.name);
        list.push(parseInt(val.person || 0));
      }
      max = [1000, 1000, 1000, 1000, 1000, 1000, 1000, 1000];
    }
    // 专家按职称统计
    if (type === 'expert') {
      res = await this.expertModel.createQueryBuilder('expert').select('expert.title', 'title').addSelect('COUNT(expert.id)', 'total').groupBy('expert.title').getRawMany();
      res.sort((a, b) => b.total - a.total);
      for (const val of res.slice(0, 9)) {
        nameList.push(val.title);
        list.push(parseInt(val.total || 0));
      }
      max = [50, 50, 50, 50, 50, 50, 50, 50];
    }
    // 需求按行业领域统计
    if (type === 'demand') {
      res = await this.demandModel.createQueryBuilder('demand').select('demand.field', 'field').addSelect('COUNT(demand.id)', 'total').groupBy('demand.field').getRawMany();
      res.sort((a, b) => b.total - a.total);
      for (const val of res.slice(0, 9)) {
        if (val.field) {
          nameList.push(val.field);
          list.push({ name: val.field, value: val.total });
        }
      }
      max = [100, 100, 100, 100, 100, 100, 100, 100];
    }
    // 供给按行业领域统计
    if (type === 'supply') {
      res = await this.supplyModel.createQueryBuilder('supply').select('supply.field', 'field').addSelect('COUNT(supply.id)', 'total').groupBy('supply.field').getRawMany();
      res.sort((a, b) => b.total - a.total);
      for (const val of res.slice(0, 9)) {
        if (val.field) {
          nameList.push(val.field);
          list.push({ name: val.field, value: val.total });
        }
      }
      max = [100, 100, 100, 100, 100, 100, 100, 100];
    }
    // 项目按来源统计
    if (type === 'project') {
      res = await this.projectModel.createQueryBuilder('project').select('project.source', 'source').addSelect('COUNT(project.id)', 'total').groupBy('project.source').getRawMany();
      res.sort((a, b) => b.total - a.total);
      for (const val of res.slice(0, 9)) {
        if (val.source) {
          nameList.push(val.source);
          list.push({ name: val.source, value: val.total });
        }
      }
      max = [100, 100, 100, 100, 100, 100, 100, 100];
    }
    // 成果按来源统计
    if (type === 'achievement') {
      res = await this.achievementModel.createQueryBuilder('achievement').select('achievement.source', 'source').addSelect('COUNT(achievement.id)', 'total').groupBy('achievement.source').getRawMany();
      res.sort((a, b) => b.total - a.total);
      for (const val of res.slice(0, 9)) {
        if (val.source) {
          nameList.push(val.source);
          list.push({ name: val.source, value: val.total });
        }
      }
      max = [100, 100, 100, 100, 100, 100, 100, 100];
    }
    //孵化器省级以上导师数排名
    if (type === 'incubate') {
      res = await this.incubatorModel.createQueryBuilder('incubator').getMany();
      for (const val of res) {
        nameList.push(val.name);
        list.push({ name: val.name, value: parseInt(val.teacher_num) });
      }
      list.sort((a, b) => b.value - a.value);
      max = [100, 100, 100, 100, 100, 100, 100, 100];
    }
    //用户男女比例排名
    if (type === 'user') {
      res = await this.userModel.createQueryBuilder('user').select('user.gender', 'gender').addSelect('COUNT(user.id)', 'total').groupBy('user.gender').getRawMany();
      req = await this.dictDataService.query({ code: 'gender', is_use: '0' }, {});
      res.sort((a, b) => b.total - a.total);
      for (const val of res) {
        if (req.data) {
          const select = req.data.find(f => f.value === val.gender);
          if (select) {
            nameList.push(select.label);
            list.push({ name: select.label, value: val.total });
          }
        }
      }
      max = [100, 100, 100, 100, 100, 100, 100, 100];
    }
    return { nameList, list, max };
  }
  // 计算百分比
  calculatePercentage(numerator, denominator) {
    if (denominator === 0 || isNaN(numerator) || isNaN(denominator)) {
      return 0;
    }
    return (numerator / denominator).toFixed(1);
  }
  // 统计总数
  async toTotal() {
    const companyTotal = await this.companyModel.count();
    const supplyTotal = await this.supplyModel.count();
    const demandTotal = await this.demandModel.count();
    const projectTotal = await this.projectModel.count();
    const achievementTotal = await this.achievementModel.count();
    const data = [
      { id: '1', name: '企业', num: companyTotal, unit: '家' },
      { id: '2', name: '供给', num: supplyTotal, unit: '个' },
      { id: '3', name: '需求', num: demandTotal, unit: '个' },
      { id: '4', name: '项目', num: projectTotal, unit: '个' },
      { id: '5', name: '成果', num: achievementTotal, unit: '个' },
    ];
    return data;
  }
  // 企业行业领域统计
  async Companystatistics() {
    let res;
    const nameList1 = [];
    const list1 = [];
    const nameList2 = [];
    const list2 = [];
    let req;
    res = await this.companyModel.createQueryBuilder('company').select('company.type', 'type').addSelect('COUNT(company.id)', 'total').groupBy('company.type').getRawMany();
    req = await this.dictDataService.query({ code: 'companyIndustry', is_use: '0' }, {});
    for (const val of res) {
      if (req.data) {
        const select = req.data.find(f => f.value === val.type);
        if (select) {
          nameList1.push(select.label);
          list1.push({ name: select.label, value: val.total });
        }
      }
    }
    res = await this.companyModel.createQueryBuilder('company').select('company.pattern', 'pattern').addSelect('COUNT(company.id)', 'total').groupBy('company.pattern').getRawMany();
    req = await this.dictDataService.query({ code: 'companyType', is_use: '0' }, {});
    if (req.data) {
      for (const val of req.data) {
        nameList2.push(val.label);
        const select = res.find(f => f.pattern === val.value);
        if (select) list2.push(select.total);
        else list2.push(0);
      }
    }
    return { one: { nameList: nameList1, list: list1 }, two: { nameList: nameList2, list: list2 } };
  }
  // 供给行业统计
  async Supplystatistics() {
    let res;
    const nameList1 = [];
    const list1 = [];
    const nameList2 = [];
    const list2 = [];
    res = await this.supplyModel.createQueryBuilder('supply').select('supply.industry', 'industry').addSelect('COUNT(supply.id)', 'total').groupBy('supply.industry').getRawMany();
    for (const val of res) {
      if (val.industry) {
        nameList1.push(val.industry);
        list1.push({ name: val.industry, value: val.total });
      }
    }
    res = await this.supplyModel.createQueryBuilder('supply').select('supply.source', 'source').addSelect('COUNT(supply.id)', 'total').groupBy('supply.source').getRawMany();
    for (const val of res) {
      if (val.source) {
        nameList2.push(val.source);
        list2.push(val.total);
      }
    }
    return { one: { nameList: nameList1, list: list1 }, two: { nameList: nameList2, list: list2 } };
  }
  // 需求行业统计
  async Demandstatistics() {
    let res;
    const nameList1 = [];
    const list1 = [];
    const nameList2 = [];
    const list2 = [];
    res = await this.demandModel.createQueryBuilder('demand').select('demand.industry', 'industry').addSelect('COUNT(demand.id)', 'total').groupBy('demand.industry').getRawMany();
    for (const val of res) {
      if (val.industry) {
        nameList1.push(val.industry);
        list1.push({ name: val.industry, value: val.total });
      }
    }
    res = await this.demandModel.createQueryBuilder('demand').select('demand.year', 'year').addSelect('COUNT(demand.id)', 'total').groupBy('demand.year').getRawMany();
    for (const val of res) {
      if (val.year) {
        nameList2.push(val.year);
        list2.push(val.total);
      }
    }
    return { one: { nameList: nameList1, list: list1 }, two: { nameList: nameList2, list: list2 } };
  }
  // 项目行业统计
  async Projectstatistics() {
    let res;
    const nameList1 = [];
    const list1 = [];
    const nameList2 = [];
    const list2 = [];
    res = await this.projectModel.createQueryBuilder('project').select('project.industry', 'industry').addSelect('COUNT(project.id)', 'total').groupBy('project.industry').getRawMany();
    for (const val of res) {
      if (val.industry) {
        nameList1.push(val.industry);
        list1.push({ name: val.industry, value: val.total });
      }
    }
    res = await this.projectModel.createQueryBuilder('project').select('project.progress', 'progress').addSelect('COUNT(project.id)', 'total').groupBy('project.progress').getRawMany();
    for (const val of res) {
      if (val.progress) {
        nameList2.push(val.progress);
        list2.push(val.total);
      }
    }
    return { one: { nameList: nameList1, list: list1 }, two: { nameList: nameList2, list: list2 } };
  }
  // 成果行业统计
  async Achievementstatistics() {
    let res;
    const nameList1 = [];
    const list1 = [];
    const nameList2 = [];
    const list2 = [];
    res = await this.achievementModel.createQueryBuilder('achievement').select('achievement.field', 'field').addSelect('COUNT(achievement.id)', 'total').groupBy('achievement.field').getRawMany();
    for (const val of res) {
      if (val.field) {
        nameList1.push(val.field);
        list1.push({ name: val.field, value: val.total });
      }
    }
    res = await this.achievementModel.createQueryBuilder('achievement').select('achievement.source', 'source').addSelect('COUNT(achievement.id)', 'total').groupBy('achievement.source').getRawMany();
    for (const val of res) {
      if (val.source) {
        nameList2.push(val.source);
        list2.push(val.total);
      }
    }
    return { one: { nameList: nameList1, list: list1 }, two: { nameList: nameList2, list: list2 } };
  }
  // 导出
  async toExport(query) {
    const { table, config, user } = query;
    const nowDate = new Date().getTime();
    const filename = `产学研用导出-${table}-${nowDate}.xlsx`;
    const path = this.path;
    if (!path) {
      throw new ServiceError('服务端没有设置存储路径');
    }
    if (!fs.existsSync(path)) {
      // 如果不存在文件夹,就创建
      this.mkdir(path);
    }
    try {
      let info = {};
      if (user) info = { where: { user: Equal(user) } };
      const targetModel = this[`${table}Model`];
      const data = await targetModel.find(info);
      const workbook = new Excel.Workbook();
      const sheet = workbook.addWorksheet('sheet1');
      // 根据前端传回来的设置和顺序,将表头先塞进去
      const meta = config.map(i => i.label);
      sheet.addRow(meta);
      let arr = [];
      for (const d of data) {
        arr = [];
        for (const c of config) {
          const { mark, model, code } = c;
          if (mark === 'tags') {
            if (d[model]) d[model] = arr.push(d[model].join(','));
            else arr.push(d[model]);
          } else if (mark === 'area') {
            if (d[model]) d[model] = arr.push(d[model].join('-'));
            else arr.push(d[model]);
          } else if (mark === 'dict') {
            const req = await this.dictDataService.query({ code, is_use: '0' }, {});
            if (req.data) {
              const selected = req.data.find(f => f.value === d[model]);
              if (selected) {
                d[model] = get(selected, 'label');
                arr.push(d[model]);
              } else arr.push(d[model]);
            } else arr.push(d[model]);
          } else if (mark === 'time') {
            if (d.start_time && d.end_time) {
              arr.push((d[model] = `${d.start_time}-${d.end_time}`));
            } else arr.push(d[model]);
          } else arr.push(d[model]);
        }
        sheet.addRow(arr);
      }
      // 生成excel
      const filepath = `${path}${filename}`;
      if (data.length <= 0) return;
      await workbook.xlsx.writeFile(filepath);
      return `/files/cxyy/export/${filename}`;
    } catch (error) {
      console.log(error);
    }
  }
  // 创建文件夹
  mkdir(dirname) {
    if (fs.existsSync(dirname)) {
      return true;
    }
    if (this.mkdir(Path.dirname(dirname))) {
      fs.mkdirSync(dirname);
      return true;
    }
  }

  randomStr(len = 6) {
    return Math.random().toString(36).slice(-len);
  }

  /**
   * 成果
   * @param {Object} rows excel数据
   */
  async achievement(rows) {
    const mappingList = mappings.achievement;
    const result = await this.dealRows(rows, mappingList);
    const errorList = [];
    // 需要查看是否有人,有人更新数据,没人添加数据
    const user_id = this.ctx.user.id;
    for (const [index, i] of result.entries()) {
      // 根据 成果名称 查重
      const { name } = i;
      const data = await this.achievementModel.findOne({ where: { user: Equal(user_id), name: Equal(name) } });
      if (data && data.id) {
        try {
          i.id = data.id;
          await this.achievementModel.update({ id: data.id }, i);
        } catch (error) {
          errorList.push(`修改第${index + 1}条${name}出现错误！！！`);
        }
      } else {
        try {
          await this.achievementModel.insert({ ...i, user: user_id, status: '0' });
        } catch (error) {
          const namek = Object.keys(error.errors)[0];
          const mapping = mappingList.find(i => i.field === namek);
          errorList.push(`第${index + 1}条${mapping.zh}列${name}字段出现错误！！！`);
        }
      }
    }
    return { result: result.length, errorList };
  }

  /**
   * 项目
   * @param {Object} rows excel数据
   */
  async project(rows) {
    const mappingList = mappings.project;
    const result = await this.dealRows(rows, mappingList);
    const errorList = [];
    // 需要查看是否有人,有人更新数据,没人添加数据
    const user_id = this.ctx.user.id;
    for (const [index, i] of result.entries()) {
      // 根据 项目名称 查重
      const { name } = i;
      const data = await this.projectModel.findOne({ where: { user: Equal(user_id), name: Equal(name) } });
      if (data && data.id) {
        try {
          i.id = data.id;
          await this.projectModel.update({ id: data.id }, i);
        } catch (error) {
          errorList.push(`修改第${index + 1}条${name}字段出现错误！！！`);
        }
      } else {
        try {
          await this.projectModel.insert({ ...i, user: user_id, status: '0' });
        } catch (error) {
          const namek = Object.keys(error.errors)[0];
          const mapping = mappingList.find(i => i.field === namek);
          errorList.push(`第${index + 1}条${mapping.zh}列${name}字段出现错误！！！`);
        }
      }
    }
    return { result: result.length, errorList };
  }
  /**
   * 需求
   * @param {Object} rows excel数据
   */
  async demand(rows) {
    const mappingList = mappings.demand;
    const result = await this.dealRows(rows, mappingList);
    const errorList = [];
    // 需要查看是否有人,有人更新数据,没人添加数据
    const user_id = this.ctx.user.id;
    for (const [index, i] of result.entries()) {
      // 根据 需求名称 查重
      const { name } = i;
      const data = await this.demandModel.findOne({ where: { user: Equal(user_id), name: Equal(name) } });
      if (data && data.id) {
        try {
          i.id = data.id;
          await this.demandModel.update({ id: data.id }, i);
        } catch (error) {
          errorList.push(`修改第${index + 1}条${name}出现错误！！！`);
        }
      } else {
        try {
          await this.demandModel.insert({ ...i, user: user_id, status: '0' });
        } catch (error) {
          const namek = Object.keys(error.errors)[0];
          const mapping = mappingList.find(i => i.field === namek);
          errorList.push(`第${index + 1}条${mapping.zh}列${name}字段出现错误！！！`);
        }
      }
    }
    return { result: result.length, errorList };
  }
  /**
   * 供给
   * @param {Object} rows excel数据
   */
  async supply(rows) {
    const mappingList = mappings.supply;
    const result = await this.dealRows(rows, mappingList);
    const errorList = [];
    // 需要查看是否有人,有人更新数据,没人添加数据
    const user_id = this.ctx.user.id;
    for (const [index, i] of result.entries()) {
      // 根据 供给名称 查重
      const { name } = i;
      const data = await this.supplyModel.findOne({ where: { user: Equal(user_id), name: Equal(name) } });
      if (data && data.id) {
        try {
          i.id = data.id;
          await this.supplyModel.update({ id: data.id }, i);
        } catch (error) {
          errorList.push(`修改第${index + 1}条${name}出现错误！！！`);
        }
      } else {
        try {
          await this.supplyModel.insert({ ...i, user: user_id, status: '0' });
        } catch (error) {
          const namek = Object.keys(error.errors)[0];
          const mapping = mappingList.find(i => i.field === namek);
          errorList.push(`第${index + 1}条${mapping.zh}列${name}字段出现错误！！！`);
        }
      }
    }
    return { result: result.length, errorList };
  }

  /**
   * 企业
   * @param {Object} rows excel数据
   */
  async company(rows) {
    const mappingList = mappings.company;
    const result = await this.dealRows(rows, mappingList);
    const errorList = [];
    // 需要查看是否有人,有人更新数据,没人添加数据
    for (const [index, i] of result.entries()) {
      // 根据 企业名称 查重
      const { name } = i;
      const data = await this.companyModel.findOne({ where: { name: Equal(name) } });
      if (data && data.id) {
        try {
          i.id = data.id;
          await this.companyModel.update({ id: data.id }, { ...i, status: '1' });
        } catch (error) {
          errorList.push(`修改第${index + 1}条${name}出现错误！！！`);
        }
      } else {
        try {
          await this.companyModel.insert({ ...i, status: '1', is_show: '1' });
        } catch (error) {
          const namek = Object.keys(error.errors)[0];
          const mapping = mappingList.find(i => i.field === namek);
          errorList.push(`第${index + 1}条${mapping.zh}列${name}字段出现错误！！！`);
        }
      }
    }
    return { result: result.length, errorList };
  }

  /**
   * 企业年度
   * @param {Object} rows excel数据
   */
  async companyYear(rows) {
    const mappingList = mappings.companyYear;
    const result = await this.dealRows(rows, mappingList);
    const errorList = [];
    // 需要查看是否有人,有人更新数据,没人添加数据
    for (const [index, i] of result.entries()) {
      const { company, year } = i;
      const data = await this.yModel.findOne({ where: { company: Equal(company), year: Equal(year) } });
      if (data && data.id) {
        try {
          i.id = data.id;
          await this.yModel.update({ id: data.id }, i);
        } catch (error) {
          errorList.push(`修改第${index + 1}条${company}出现错误！！！`);
        }
      } else {
        try {
          const companyInfo = await this.companyModel.findOne({ where: { id: Equal(company) } });
          const userInfo = await this.userService.fetch({ id: companyInfo.user });
          const id = get(userInfo, 'id');
          await this.yModel.insert({ ...i, user: id, status: '1', is_use: '0' });
        } catch (error) {
          const namek = Object.keys(error.errors)[0];
          const mapping = mappingList.find(i => i.field === namek);
          errorList.push(`第${index + 1}条${mapping.zh}列${company}字段出现错误！！！`);
        }
      }
    }
    return { result: result.length, errorList };
  }
  /**
   * 处理excel传来的数据
   * @param {Array} rows excel数据,二维数组
   * @param {Object} mappingList 设置的某个映射
   * @return {Array} result 数据
   */
  async dealRows(rows, mappingList) {
    const result = [];
    const user = this.ctx.user;
    if (!user) throw new ServiceError(ErrorCode.USER_NOT_FOUND);
    for (let i = 0; i < rows.length; i++) {
      const rowData = rows[i];
      const obj = { user: user.id };
      for (let k = 0; k < rowData.length; k++) {
        let val = rowData[k];
        if (val === null) continue;
        if (typeof val === 'object' && val !== null) {
          const richText = get(val, 'richText');
          if (richText) {
            let texts = '';
            // 使用map提取name字段，然后检查是否为undefined
            // eslint-disable-next-line array-callback-return
            const mappedNames = richText.map(item => {
              // 判断字段是否存在
              if ('text' in item) {
                return item.text;
              }
            });
            // 过滤掉null或undefined的值
            const filteredNames = mappedNames.filter(text => text !== null && text !== undefined);
            // 拼接字符串
            texts = filteredNames.join('');
            val = texts;
          }
        }
        const mapping = mappingList.find(f => f.index === k);
        if (mapping) {
          const { field, type } = mapping;
          if (field !== 'tags') {
            if (type) val = await this.dealTypeValue(mapping, val);
            obj[field] = val;
          } else {
            if(val) {
              const arr = val.split(';')
              obj[field] = arr;
            } 
          }
        }
      }
      result.push(obj);
    }
    return result;
  }

  /**
   * 转换数据
   * @param {Object} mapping 当前要处理的映射
   * @param {any} val excel值
   * @return {any} result 转换后的数据
   */
  async dealTypeValue(mapping, val) {
    const { type, code } = mapping;
    let result;
    if (type === 'date') {
      // 判断日期对不对,经过moment处理下
      const mobj = dayjs(val);
      if (mobj.isValid()) {
        result = mobj.format('YYYY-MM-DD');
      }
    } else if (type === 'dict') {
      const req = await this.dictDataService.query({ code }, {});
      if (req.data) {
        const selected = req.data.find(f => f.label === val);
        if (selected) result = get(selected, 'value');
      }
    } else if (type === 'area') {
      if (val) result = val.split('-');
    } else if (type === 'number') {
      if (val === 0) {
        result = val;
      } else {
        if (val) result = parseInt(val) || 0;
      }
    } else if (type === 'id') {
      if (val) {
        const company = await this.companyModel.findOne({ where: { name: Equal(val) } });
        if (company) result = get(company, 'id');
        else result = val;
      } else {
        result = val;
      }
    }
    return result;
  }

  nameToFunction(name) {
    const obj = {
      成果: 'achievement',
      项目: 'project',
      需求: 'demand',
      供给: 'supply',
      企业: 'company',
      企业年度: 'companyYear',
    };
    return get(obj, name);
  }
}