mini-starter/allin-packages/statistics-module/src/services/statistics.service.ts

import { DataSource, Repository } from 'typeorm';
import { DisabledPerson } from '@d8d/allin-disability-module/entities';
import { OrderPerson } from '@d8d/allin-order-module/entities';
import { EmploymentOrder } from '@d8d/allin-order-module/entities';
import { AgeGroup, SalaryRange, StatItem, HouseholdStatItem } from '../schemas/statistics.schema';

export class StatisticsService {
  private readonly disabledPersonRepository: Repository<DisabledPerson>;
  private readonly orderPersonRepository: Repository<OrderPerson>;
  private readonly employmentOrderRepository: Repository<EmploymentOrder>;

  constructor(dataSource: DataSource) {
    this.disabledPersonRepository = dataSource.getRepository(DisabledPerson);
    this.orderPersonRepository = dataSource.getRepository(OrderPerson);
    this.employmentOrderRepository = dataSource.getRepository(EmploymentOrder);
  }

  /**
   * 获取企业关联的残疾人员ID列表（用于数据隔离）
   * @param companyId 企业ID
   * @returns 残疾人员ID数组
   */
  private async getCompanyDisabledPersonIds(companyId: number): Promise<number[]> {
    const query = this.disabledPersonRepository
      .createQueryBuilder('dp')
      .innerJoin('dp.orderPersons', 'op')
      .innerJoin('op.order', 'order')
      .where('order.companyId = :companyId', { companyId })
      .select('dp.id', 'id');

    const result = await query.getRawMany();
    return result.map(item => item.id);
  }

  /**
   * 获取残疾类型分布统计
   * @param companyId 企业ID
   * @returns 残疾类型分布统计结果
   */
  async getDisabilityTypeDistribution(companyId: number): Promise<{
    companyId: number;
    stats: StatItem[];
    total: number;
  }> {
    const personIds = await this.getCompanyDisabledPersonIds(companyId);

    if (personIds.length === 0) {
      return {
        companyId,
        stats: [],
        total: 0
      };
    }

    const query = this.disabledPersonRepository
      .createQueryBuilder('dp')
      .select('dp.disabilityType', 'key')
      .addSelect('COUNT(dp.id)', 'value')
      .where('dp.id IN (:...personIds)', { personIds })
      .andWhere('dp.disabilityType IS NOT NULL')
      .groupBy('dp.disabilityType');

    const rawStats = await query.getRawMany();
    const total = rawStats.reduce((sum, item) => sum + parseInt(item.value), 0);

    const stats = rawStats.map(item => ({
      key: item.key,
      value: parseInt(item.value),
      percentage: total > 0 ? (parseInt(item.value) / total) * 100 : 0
    }));

    return {
      companyId,
      stats,
      total
    };
  }

  /**
   * 获取性别分布统计
   * @param companyId 企业ID
   * @returns 性别分布统计结果
   */
  async getGenderDistribution(companyId: number): Promise<{
    companyId: number;
    stats: StatItem[];
    total: number;
  }> {
    const personIds = await this.getCompanyDisabledPersonIds(companyId);

    if (personIds.length === 0) {
      return {
        companyId,
        stats: [],
        total: 0
      };
    }

    const query = this.disabledPersonRepository
      .createQueryBuilder('dp')
      .select('dp.gender', 'key')
      .addSelect('COUNT(dp.id)', 'value')
      .where('dp.id IN (:...personIds)', { personIds })
      .andWhere('dp.gender IS NOT NULL')
      .groupBy('dp.gender');

    const rawStats = await query.getRawMany();
    const total = rawStats.reduce((sum, item) => sum + parseInt(item.value), 0);

    const stats = rawStats.map(item => ({
      key: item.key,
      value: parseInt(item.value),
      percentage: total > 0 ? (parseInt(item.value) / total) * 100 : 0
    }));

    return {
      companyId,
      stats,
      total
    };
  }

  /**
   * 获取年龄分布统计
   * @param companyId 企业ID
   * @returns 年龄分布统计结果
   */
  async getAgeDistribution(companyId: number): Promise<{
    companyId: number;
    stats: StatItem[];
    total: number;
  }> {
    const personIds = await this.getCompanyDisabledPersonIds(companyId);

    if (personIds.length === 0) {
      return {
        companyId,
        stats: [],
        total: 0
      };
    }

    // 使用CTE计算年龄分组
    const ageQuery = this.disabledPersonRepository
      .createQueryBuilder('dp')
      .select('dp.id', 'id')
      .addSelect(`
        CASE
          WHEN EXTRACT(YEAR FROM AGE(dp.birth_date)) BETWEEN 18 AND 25 THEN '18-25'
          WHEN EXTRACT(YEAR FROM AGE(dp.birth_date)) BETWEEN 26 AND 35 THEN '26-35'
          WHEN EXTRACT(YEAR FROM AGE(dp.birth_date)) BETWEEN 36 AND 45 THEN '36-45'
          ELSE '46+'
        END`, 'age_group'
      )
      .where('dp.id IN (:...personIds)', { personIds })
      .andWhere('dp.birth_date IS NOT NULL');

    const rawAgeData = await ageQuery.getRawMany();

    // 统计年龄分组
    const ageGroups = ['18-25', '26-35', '36-45', '46+'] as const;
    const ageStats: Record<typeof ageGroups[number], number> = {
      '18-25': 0,
      '26-35': 0,
      '36-45': 0,
      '46+': 0
    };

    rawAgeData.forEach(item => {
      if (item.age_group && ageStats.hasOwnProperty(item.age_group)) {
        ageStats[item.age_group as typeof ageGroups[number]]++;
      }
    });

    const total = rawAgeData.length;
    const stats = ageGroups.map(group => ({
      key: group,
      value: ageStats[group],
      percentage: total > 0 ? (ageStats[group] / total) * 100 : 0
    })).filter(item => item.value > 0);

    return {
      companyId,
      stats,
      total
    };
  }

  /**
   * 获取户籍分布统计
   * @param companyId 企业ID
   * @returns 户籍分布统计结果
   */
  async getHouseholdDistribution(companyId: number): Promise<{
    companyId: number;
    stats: HouseholdStatItem[];
    total: number;
  }> {
    const personIds = await this.getCompanyDisabledPersonIds(companyId);

    if (personIds.length === 0) {
      return {
        companyId,
        stats: [],
        total: 0
      };
    }

    const query = this.disabledPersonRepository
      .createQueryBuilder('dp')
      .select('dp.householdProvince', 'province')
      .addSelect('dp.householdCity', 'city')
      .addSelect('COUNT(dp.id)', 'value')
      .where('dp.id IN (:...personIds)', { personIds })
      .andWhere('dp.householdProvince IS NOT NULL')
      .groupBy('dp.householdProvince, dp.householdCity');

    const rawStats = await query.getRawMany();
    const total = rawStats.reduce((sum, item) => sum + parseInt(item.value), 0);

    const stats = rawStats.map(item => ({
      key: `${item.province}${item.city ? `-${item.city}` : ''}`,
      value: parseInt(item.value),
      percentage: total > 0 ? (parseInt(item.value) / total) * 100 : 0,
      province: item.province,
      city: item.city || undefined
    }));

    return {
      companyId,
      stats,
      total
    };
  }

  /**
   * 获取在职状态分布统计
   * @param companyId 企业ID
   * @returns 在职状态分布统计结果
   */
  async getJobStatusDistribution(companyId: number): Promise<{
    companyId: number;
    stats: StatItem[];
    total: number;
  }> {
    const personIds = await this.getCompanyDisabledPersonIds(companyId);

    if (personIds.length === 0) {
      return {
        companyId,
        stats: [],
        total: 0
      };
    }

    const query = this.disabledPersonRepository
      .createQueryBuilder('dp')
      .select('dp.jobStatus', 'key')
      .addSelect('COUNT(dp.id)', 'value')
      .where('dp.id IN (:...personIds)', { personIds })
      .andWhere('dp.jobStatus IS NOT NULL')
      .groupBy('dp.jobStatus');

    const rawStats = await query.getRawMany();
    const total = rawStats.reduce((sum, item) => sum + parseInt(item.value), 0);

    const stats = rawStats.map(item => ({
      key: item.key,
      value: parseInt(item.value),
      percentage: total > 0 ? (parseInt(item.value) / total) * 100 : 0
    }));

    return {
      companyId,
      stats,
      total
    };
  }

  /**
   * 获取薪资分布统计
   * @param companyId 企业ID
   * @returns 薪资分布统计结果
   */
  async getSalaryDistribution(companyId: number): Promise<{
    companyId: number;
    stats: StatItem[];
    total: number;
  }> {
    // 获取企业关联的订单人员薪资数据
    const query = this.orderPersonRepository
      .createQueryBuilder('op')
      .innerJoin('op.order', 'order')
      .select('op.salaryDetail', 'salary')
      .where('order.companyId = :companyId', { companyId })
      .andWhere('op.salaryDetail IS NOT NULL')
      .andWhere('op.salaryDetail > 0');

    const rawSalaries = await query.getRawMany();

    if (rawSalaries.length === 0) {
      return {
        companyId,
        stats: [],
        total: 0
      };
    }

    // 定义薪资范围
    const salaryRanges: Array<{ key: SalaryRange; min: number; max: number | null }> = [
      { key: '<3000', min: 0, max: 3000 },
      { key: '3000-5000', min: 3000, max: 5000 },
      { key: '5000-8000', min: 5000, max: 8000 },
      { key: '8000-12000', min: 8000, max: 12000 },
      { key: '12000+', min: 12000, max: null }
    ];

    // 统计各薪资范围人数
    const salaryStats: Record<SalaryRange, number> = {
      '<3000': 0,
      '3000-5000': 0,
      '5000-8000': 0,
      '8000-12000': 0,
      '12000+': 0
    };

    rawSalaries.forEach(item => {
      const salary = parseFloat(item.salary);
      for (const range of salaryRanges) {
        if (range.max === null) {
          if (salary >= range.min) {
            salaryStats[range.key]++;
            break;
          }
        } else if (salary >= range.min && salary < range.max) {
          salaryStats[range.key]++;
          break;
        }
      }
    });

    const total = rawSalaries.length;
    const stats = salaryRanges
      .map(range => ({
        key: range.key,
        value: salaryStats[range.key],
        percentage: total > 0 ? (salaryStats[range.key] / total) * 100 : 0
      }))
      .filter(item => item.value > 0);

    return {
      companyId,
      stats,
      total
    };
  }
}