rentease-backend/controllers/statisticsController.js

const { Room, Rental, Apartment, Expense } = require('../models');
const { Op } = require('sequelize');

// 租金统计
const getRentStatistics = async (req, res) => {
  try {
    // 计算过去12个月的开始日期
    const now = new Date();
    const startDate = new Date(now.getFullYear(), now.getMonth() - 11, 1); // 12个月前的第一天
    
    // 初始化过去12个月的数据
    const monthlyRent = {};
    for (let i = 0; i < 12; i++) {
      const date = new Date(now.getFullYear(), now.getMonth() - i, 1);
      const monthKey = `${date.getFullYear()}-${(date.getMonth() + 1).toString().padStart(2, '0')}`;
      monthlyRent[monthKey] = {
        amount: 0,
        depositReceived: 0,
        depositRefunded: 0,
        expense: 0
      };
    }
    
    // 从数据库查询过去12个月的租赁记录（排除已删除的）
    const rentals = await Rental.findAll({
      where: {
        createTime: {
          [Op.gte]: startDate
        },
        isDeleted: 0
      }
    });
    
    // 按月份统计已收租金和押金
    rentals.forEach(rental => {
      if (rental.createTime) {
        // 解析创建时间
        const createDate = new Date(rental.createTime);
        const monthKey = `${createDate.getFullYear()}-${(createDate.getMonth() + 1).toString().padStart(2, '0')}`;
        
        // 如果该月份在我们的统计范围内
        if (monthlyRent.hasOwnProperty(monthKey)) {
          // 统计租金
          const rentAmount = parseFloat(rental.rent) || 0;
          if (rentAmount > 0) {
            monthlyRent[monthKey].amount += rentAmount;
          }
          
          // 统计已收押金（创建租赁记录时的押金）
          const depositReceived = parseFloat(rental.deposit) || 0;
          if (depositReceived > 0) {
            monthlyRent[monthKey].depositReceived += depositReceived;
          }
          
          // 统计已退押金
          const depositRefunded = parseFloat(rental.refundedDeposit) || 0;
          if (depositRefunded > 0) {
            monthlyRent[monthKey].depositRefunded += depositRefunded;
          }
        }
      }
    });
    
    // 从数据库查询过去12个月的费用支出记录（排除已删除的）
    const expenses = await Expense.findAll({
      where: {
        date: {
          [Op.gte]: startDate
        },
        isDeleted: 0
      }
    });
    
    // 按月份统计费用支出
    expenses.forEach(expense => {
      if (expense.date) {
        // 解析费用日期
        const expenseDate = new Date(expense.date);
        const monthKey = `${expenseDate.getFullYear()}-${(expenseDate.getMonth() + 1).toString().padStart(2, '0')}`;
        
        // 如果该月份在我们的统计范围内
        if (monthlyRent.hasOwnProperty(monthKey)) {
          // 统计费用支出
          const expenseAmount = parseFloat(expense.amount) || 0;
          if (expenseAmount > 0) {
            monthlyRent[monthKey].expense += expenseAmount;
          }
        }
      }
    });
    
    // 转换为数组格式并按月份倒序排序
    const rentStatistics = Object.entries(monthlyRent)
      .map(([month, data]) => ({
        month,
        amount: Math.round(data.amount * 100) / 100, // 保留两位小数
        depositReceived: Math.round(data.depositReceived * 100) / 100, // 保留两位小数
        depositRefunded: Math.round(data.depositRefunded * 100) / 100, // 保留两位小数
        expense: Math.round(data.expense * 100) / 100 // 保留两位小数
      }))
      .sort((a, b) => b.month.localeCompare(a.month));
    
    res.status(200).json(rentStatistics);
  } catch (error) {
    console.error('获取租金统计数据时出错:', error);
    res.status(500).json({ error: error.message });
  }
};

// 房间状态统计
const getRoomStatusStatistics = async (req, res) => {
  try {
    // 获取所有房间（排除已删除的）
    const rooms = await Room.findAll({ where: { isDeleted: 0 } });
    
    // 初始化统计数据
    let empty = 0;
    let reserved = 0;
    let rented = 0;
    let soon_expire = 0;
    let expired = 0;
    let cleaning = 0;
    let maintenance = 0;
    
    // 统计各状态房间数量
    rooms.forEach(room => {
      if (room.status === 'empty') {
        empty++;
      } else if (room.status === 'reserved') {
        reserved++;
      } else if (room.status === 'rented') {
        rented++;
        // 统计附属状态
        if (room.subStatus === 'soon_expire') {
          soon_expire++;
        } else if (room.subStatus === 'expired') {
          expired++;
        }
        // 统计其他状态
        if (room.otherStatus === 'cleaning') {
          cleaning++;
        } else if (room.otherStatus === 'maintenance') {
          maintenance++;
        }
      }
    });
    
    const roomStatusStatistics = [
      { status: '空房', count: empty },
      { status: '预订', count: reserved },
      { status: '在租', count: rented },
      { status: '即将到期', count: soon_expire },
      { status: '到期', count: expired },
      { status: '打扫中', count: cleaning },
      { status: '维修中', count: maintenance }
    ];
    
    res.status(200).json(roomStatusStatistics);
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
};

// Dashboard统计数据
const getDashboardStatistics = async (req, res) => {
  try {
    // 导入所有需要的模型
    const { Apartment, Room, WaterBill, Rental } = require('../models');
    
    // 并行查询所有统计数据
    const [apartmentCount, roomCount, emptyRoomCount, reservedRoomCount, rentedRoomCount, soonExpireRoomCount, expiredRoomCount, collectedRentAmount, collectedWaterAmount] = await Promise.all([
      Apartment.count({ where: { isDeleted: 0 } }),
      Room.count({ where: { isDeleted: 0 } }),
      Room.count({ where: { status: 'empty', isDeleted: 0 } }),
      Room.count({ where: { status: 'reserved', isDeleted: 0 } }),
      Room.count({ where: { status: 'rented', isDeleted: 0 } }),
      Room.count({ where: { status: 'rented', subStatus: 'soon_expire', isDeleted: 0 } }),
      Room.count({ where: { status: 'rented', subStatus: 'expired', isDeleted: 0 } }),
      // 统计已收租金（排除已删除的）
      Rental.sum('rent', { where: { isDeleted: 0 } }),
      // 统计已收水费（排除已删除的）
      WaterBill.sum('amount', { where: { status: 'paid', isDeleted: 0 } })
    ]);
    
    // 构造响应数据
    const dashboardStatistics = {
      apartmentCount,
      roomCount,
      emptyRoomCount,
      reservedRoomCount,
      rentedRoomCount,
      soonExpireRoomCount,
      expiredRoomCount,
      collectedRentAmount: parseFloat(collectedRentAmount) || 0,
      collectedWaterAmount: parseFloat(collectedWaterAmount) || 0
    };
    
    res.status(200).json(dashboardStatistics);
  } catch (error) {
    console.error('获取Dashboard统计数据时出错:', error);
    res.status(500).json({ error: error.message });
  }
};

// 公寓房间状态分布统计
const getApartmentRoomStatusStatistics = async (req, res) => {
  try {
    // 获取所有公寓（排除已删除的）
    const apartments = await Apartment.findAll({ where: { isDeleted: 0 } });
    
    // 获取所有房间（排除已删除的）
    const rooms = await Room.findAll({ where: { isDeleted: 0 } });
    
    // 构建公寓房间状态分布数据
    const apartmentRoomStatusStatistics = apartments.map(apartment => {
      const apartmentRooms = rooms.filter(room => room.apartmentId === apartment.id);
      
      let empty = 0;
      let reserved = 0;
      let rented = 0;
      let soon_expire = 0;
      let expired = 0;
      let cleaning = 0;
      let maintenance = 0;
      
      apartmentRooms.forEach(room => {
        if (room.status === 'empty') {
          empty++;
        } else if (room.status === 'reserved') {
          reserved++;
        } else if (room.status === 'rented') {
          rented++;
          if (room.subStatus === 'soon_expire') {
            soon_expire++;
          } else if (room.subStatus === 'expired') {
            expired++;
          }
          if (room.otherStatus === 'cleaning') {
            cleaning++;
          } else if (room.otherStatus === 'maintenance') {
            maintenance++;
          }
        }
      });
      
      return {
        apartmentId: apartment.id,
        apartment: apartment.name,
        empty,
        reserved,
        rented,
        soon_expire,
        expired,
        cleaning,
        maintenance,
        total: empty + reserved + rented
      };
    });
    
    res.status(200).json(apartmentRoomStatusStatistics);
  } catch (error) {
    console.error('获取公寓房间状态分布数据时出错:', error);
    res.status(500).json({ error: error.message });
  }
};

// 空房分布统计（按公寓分组）
const getEmptyRoomsByApartment = async (req, res) => {
  try {
    // 获取所有公寓（排除已删除的）
    const apartments = await Apartment.findAll({ where: { isDeleted: 0 } });
    
    // 获取所有空房（排除已删除的）
    const emptyRooms = await Room.findAll({ 
      where: { 
        status: 'empty', 
        isDeleted: 0 
      } 
    });
    
    // 构建按公寓分组的空房数据
    const emptyRoomsByApartment = apartments.map(apartment => {
      const apartmentEmptyRooms = emptyRooms
        .filter(room => room.apartmentId === apartment.id)
        .map(room => ({
          id: room.id,
          roomNumber: room.roomNumber,
          type: room.type,
          area: room.area
        }));
      
      return {
        apartmentId: apartment.id,
        apartmentName: apartment.name,
        emptyRooms: apartmentEmptyRooms
      };
    });
    
    res.status(200).json(emptyRoomsByApartment);
  } catch (error) {
    console.error('获取空房分布数据时出错:', error);
    res.status(500).json({ error: error.message });
  }
};

// 在租分布统计（按公寓分组）
const getRentedRoomsByApartment = async (req, res) => {
  try {
    // 获取所有公寓（排除已删除的）
    const apartments = await Apartment.findAll({ where: { isDeleted: 0 } });
    
    // 获取所有在租房（排除已删除的）
    const rentedRooms = await Room.findAll({ 
      where: { 
        status: 'rented', 
        isDeleted: 0 
      } 
    });
    
    // 构建按公寓分组的在租数据
    const rentedRoomsByApartment = apartments.map(apartment => {
      const apartmentRentedRooms = rentedRooms
        .filter(room => room.apartmentId === apartment.id)
        .map(room => ({
          id: room.id,
          roomNumber: room.roomNumber,
          type: room.type,
          area: room.area
        }));
      
      return {
        apartmentId: apartment.id,
        apartmentName: apartment.name,
        rentedRooms: apartmentRentedRooms
      };
    });
    
    res.status(200).json(rentedRoomsByApartment);
  } catch (error) {
    console.error('获取在租分布数据时出错:', error);
    res.status(500).json({ error: error.message });
  }
};

// 租客在租统计
const getTenantRentalStats = async (req, res) => {
  try {
    // 获取所有在租的租赁记录（排除已删除的）
    const rentals = await Rental.findAll({
      where: {
        status: 'active',
        isDeleted: 0
      },
      include: [{
        model: Room,
        where: { isDeleted: 0 }
      }]
    });
    
    // 获取所有公寓（排除已删除的）
    const apartments = await Apartment.findAll({ where: { isDeleted: 0 } });
    
    // 按租客分组
    const tenantMap = new Map();
    
    rentals.forEach(rental => {
      const tenantName = rental.tenantName;
      const room = rental.Room;
      
      if (!tenantMap.has(tenantName)) {
        tenantMap.set(tenantName, {
          tenantName,
          count: 0,
          apartments: []
        });
      }
      
      const tenantData = tenantMap.get(tenantName);
      tenantData.count++;
      
      // 查找房间所属的公寓
      const apartment = apartments.find(apt => apt.id === room.apartmentId);
      if (apartment) {
        let apartmentData = tenantData.apartments.find(apt => apt.apartmentId === apartment.id);
        if (!apartmentData) {
          apartmentData = {
            apartmentId: apartment.id,
            apartmentName: apartment.name,
            rooms: []
          };
          tenantData.apartments.push(apartmentData);
        }
        apartmentData.rooms.push({
          id: room.id,
          roomNumber: room.roomNumber,
          type: room.type,
          area: room.area
        });
      }
    });
    
    // 转换为数组
    const tenantRentalStats = Array.from(tenantMap.values());
    
    res.status(200).json(tenantRentalStats);
  } catch (error) {
    console.error('获取租客在租统计数据时出错:', error);
    res.status(500).json({ error: error.message });
  }
};

// 即将到期房间分布统计（按公寓分组）
const getSoonExpireRoomsByApartment = async (req, res) => {
  try {
    // 获取所有公寓（排除已删除的）
    const apartments = await Apartment.findAll({ where: { isDeleted: 0 } });
    
    // 获取所有即将到期的房间（排除已删除的）
    const soonExpireRooms = await Room.findAll({ 
      where: { 
        status: 'rented',
        subStatus: 'soon_expire',
        isDeleted: 0 
      } 
    });
    
    // 构建按公寓分组的即将到期房间数据
    const soonExpireRoomsByApartment = apartments.map(apartment => {
      const apartmentSoonExpireRooms = soonExpireRooms
        .filter(room => room.apartmentId === apartment.id)
        .map(room => ({
          id: room.id,
          roomNumber: room.roomNumber,
          type: room.type,
          area: room.area
        }));
      
      return {
        apartmentId: apartment.id,
        apartmentName: apartment.name,
        soonExpireRooms: apartmentSoonExpireRooms
      };
    });
    
    res.status(200).json(soonExpireRoomsByApartment);
  } catch (error) {
    console.error('获取即将到期分布数据时出错:', error);
    res.status(500).json({ error: error.message });
  }
};

// 已到期房间分布统计（按公寓分组）
const getExpiredRoomsByApartment = async (req, res) => {
  try {
    // 获取所有公寓（排除已删除的）
    const apartments = await Apartment.findAll({ where: { isDeleted: 0 } });
    
    // 获取所有已到期的房间（排除已删除的）
    const expiredRooms = await Room.findAll({ 
      where: { 
        status: 'rented',
        subStatus: 'expired',
        isDeleted: 0 
      } 
    });
    
    // 构建按公寓分组的已到期房间数据
    const expiredRoomsByApartment = apartments.map(apartment => {
      const apartmentExpiredRooms = expiredRooms
        .filter(room => room.apartmentId === apartment.id)
        .map(room => ({
          id: room.id,
          roomNumber: room.roomNumber,
          type: room.type,
          area: room.area
        }));
      
      return {
        apartmentId: apartment.id,
        apartmentName: apartment.name,
        expiredRooms: apartmentExpiredRooms
      };
    });
    
    res.status(200).json(expiredRoomsByApartment);
  } catch (error) {
    console.error('获取已到期分布数据时出错:', error);
    res.status(500).json({ error: error.message });
  }
};

module.exports = {
  getRentStatistics,
  getRoomStatusStatistics,
  getDashboardStatistics,
  getApartmentRoomStatusStatistics,
  getEmptyRoomsByApartment,
  getRentedRoomsByApartment,
  getSoonExpireRoomsByApartment,
  getExpiredRoomsByApartment,
  getTenantRentalStats
};