import { Injectable, Logger } from "@nestjs/common" import { TripRepository } from "../../trips/repositories/trip.repository" import { successResponse } from "src/common/response/response" import { code } from "src/common/response/response.code" import { ExcelService } from "src/utils/xlsx" import { TeamMemberRepository } from "../../team-member/repositories/team_member.repository" import { RATING_TYPES } from "../../rating/constants/rating-type.constants" import { TripBasePricingRepository } from "../../trips/repositories/trip-base-price.repository" import { TripAddOnsPricingRepository } from "../../trips/repositories/trip-addons-pricing.repository" import { HospitalRepository } from "../../hospitals/repositories/hospital.repository" import { FleetManagementRepository } from "../../fleet-management/repositories/fleet-management.repository" import { PaymentRepository } from "../../payment/repositories/payment.repository" import { InvoiceRepository } from "../../invoices/repositories/invoice.repository" import { CustomerRepository } from "../../customers/repositories/customers.repository" import { STATUS } from "src/constants/trip.constant" import { isEmpty } from "src/utils/helpers" import moment from "moment" interface TripSummaryParams { start_date?: string end_date?: string driver_id?: number dispatcher_id?: number client_id?: number city_id?: number service_type?: string status?: string search?: string skip?: number limit?: number is_export?: boolean } interface TripData { trip_id: number trip_pickup_datetime: string trip_dropoff_datetime: string trip_pick_up_address: string trip_drop_off_address: string trip_drop_off_location: string driver_first_name: string driver_last_name: string vehicle_type_name: string trip_estimated_distance: number trip_estimated_time: number customer_name: string client_company_name: string prn_number: string trip_status: string cost?: number } @Injectable() export class ReportService { private readonly logger = new Logger(ReportService.name) constructor( private readonly tripRepository: TripRepository, private readonly excelService: ExcelService, private readonly teamMemberRepository: TeamMemberRepository, private readonly tripBasePricingRepository: TripBasePricingRepository, private readonly tripAddonsPricingRepository: TripAddOnsPricingRepository, private readonly hospitalRepository: HospitalRepository, private readonly fleetManagementRepository: FleetManagementRepository, private readonly paymentRepository: PaymentRepository, private readonly invoiceRepository: InvoiceRepository, private readonly customerRepository: CustomerRepository, ) {} // Helper function private formatDate(dateString: string): string { if (!dateString) return ''; const date = new Date(dateString); if (isNaN(date.getTime())) return dateString; const mm = String(date.getMonth() + 1).padStart(2, '0'); const dd = String(date.getDate()).padStart(2, '0'); const yyyy = date.getFullYear(); return `${mm}/${dd}/${yyyy}`; } private buildTripQueryBuilder() { return this.tripRepository["entity"] .createQueryBuilder("trip") .innerJoin("trip.customer", "customer") .innerJoin("trip.trip_type", "trip_type") .innerJoin("trip.assignments", "assignments") .innerJoin("assignments.driver", "driver") .leftJoin("trip.client", "client") .leftJoin("assignments.fleet", "fleet") .leftJoin("fleet.vehicle_type", "vehicle_type") .leftJoin( "hospitals", "hp", "hp.id = trip.pickup_hospital_id AND trip.pickup_location_type = 'hospital'", ) .leftJoin("cities", "cp", "cp.id = hp.city_id") .leftJoin("states", "sp", "sp.id = hp.state_id") .leftJoin( "hospitals", "hd", "hd.id = trip.dropoff_hospital_id AND trip.dropoff_location_type = 'hospital'", ) .leftJoin("cities", "cd", "cd.id = hd.city_id") .leftJoin("states", "sd", "sd.id = hd.state_id") .where("trip.deleted_at IS NULL") .select([ "trip.id AS trip_id", "trip.pickup_datetime AS trip_pickup_datetime", "trip.dropoff_datetime AS trip_dropoff_datetime", `CASE WHEN trip.pickup_location_type = 'hospital' AND hp.address IS NOT NULL THEN CONCAT_WS(', ', hp.address, cp.name, sp.name) WHEN trip.pickup_location_type = 'residence' THEN customer.primary_address ELSE trip.pick_up_location END AS trip_pick_up_address`, `CASE WHEN trip.dropoff_location_type = 'hospital' AND hd.address IS NOT NULL THEN CONCAT_WS(', ', hd.address, cd.name, sd.name) WHEN trip.dropoff_location_type = 'residence' THEN customer.primary_address ELSE trip.drop_off_location END AS trip_drop_off_address`, "trip.drop_off_location AS trip_drop_off_location", "trip.prn_number AS prn_number", "driver.first_name AS driver_first_name", "driver.last_name AS driver_last_name", "vehicle_type.name AS vehicle_type_name", "trip.estimated_distance AS trip_estimated_distance", "trip.estimated_time AS trip_estimated_time", "customer.customer_name AS customer_name", "trip_type.name AS service_type", "client.company_name AS client_company_name", "trip.status AS trip_status", ]) } async tripSummary(params: TripSummaryParams) { try { const { start_date, end_date, driver_id, dispatcher_id, client_id, city_id, service_type, status, search, skip = 0, limit = 10, is_export = false, } = params // Input validation if (start_date && end_date && new Date(start_date) > new Date(end_date)) { throw new Error("Start date cannot be after end date") } if (skip < 0 || limit < 0) { throw new Error("Skip and limit must be non-negative") } const queryBuilder = this.buildTripQueryBuilder() // Apply filters if (status) { queryBuilder.andWhere("trip.status = :status", { status }) } if (driver_id) { queryBuilder.andWhere("driver.id = :driver_id", { driver_id }) } if (dispatcher_id) { queryBuilder.andWhere("customer.dispatcher_id = :dispatcher_id", { dispatcher_id, }) } if (client_id) { queryBuilder.andWhere("client.id = :client_id", { client_id }) } if (city_id) { // Use trip's city_id (trip.city_id column) for filtering trips by city queryBuilder.andWhere("trip.city_id = :city_id", { city_id }) } if (service_type) { this.logger.warn( `Service type filter provided but not implemented: ${service_type}`, ) // TODO: Implement service_type filter if needed } if (search) { if (!isNaN(Number(search))) { queryBuilder.andWhere("CAST(trip.id AS TEXT) LIKE :search", { search: `%${search}%`, }) } else { queryBuilder.andWhere( "(customer.customer_name ILIKE :search OR client.company_name ILIKE :search)", { search: `%${search}%` }, ) } } if (start_date && end_date) { queryBuilder.andWhere( "trip.pickup_datetime BETWEEN :startDate AND :endDate", { startDate: start_date, endDate: end_date }, ) } // Get total count using cloned query before adding group by const countQuery = queryBuilder .clone() .select("COUNT(DISTINCT trip.id)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 // Add order by and group by for the main query queryBuilder .orderBy("trip.pickup_datetime", "DESC") .addGroupBy("trip.id") .addGroupBy("hp.id") .addGroupBy("hd.id") .addGroupBy("driver.id") .addGroupBy("vehicle_type.id") .addGroupBy("customer.id") .addGroupBy("trip_type.id") .addGroupBy("client.id") .addGroupBy("cp.id") .addGroupBy("sp.id") .addGroupBy("cd.id") .addGroupBy("sd.id") // Apply pagination only for listing if (!is_export) { queryBuilder.offset(skip).limit(limit) } const tripData: TripData[] = await queryBuilder.getRawMany() // Calculate costs with error handling const tripDataWithCost = await Promise.all( tripData.map(async (trip) => { try { const costData = await this.calculateTripTotalPrice(trip.trip_id); return { ...trip, trip_pickup_datetime: this.formatDate(trip.trip_pickup_datetime), trip_dropoff_datetime: this.formatDate(trip.trip_dropoff_datetime), cost: costData.totalTripPrice, }; } catch (error) { this.logger.error(`Error calculating cost for trip ${trip.trip_id}:`, error); return { ...trip, trip_pickup_datetime: this.formatDate(trip.trip_pickup_datetime), trip_dropoff_datetime: this.formatDate(trip.trip_dropoff_datetime), cost: 0, }; } }), ); if (is_export) { const excelData = this.formatTripDataForExcel(tripDataWithCost) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Trip data exported successfully", buffer, ) } return successResponse(code.SUCCESS, "Trip data retrieved successfully", { trip: tripDataWithCost, totalCount, }) } catch (error) { this.logger.error("Error in tripSummary:", error) throw error } } async customerTransportHistory({ search, skip = 0, limit = 10, is_export }) { const queryBuilder = this.customerRepository["entity"] .createQueryBuilder("customer") .leftJoin("customer.client_company", "client_company") .where("customer.deleted_at IS NULL") .select([ "customer.id AS customer_id", "customer.customer_name AS customer_name", "client_company.company_name AS client_name", `(SELECT COUNT(t.id) FROM trips t WHERE t.customer_id = customer.id AND t.deleted_at IS NULL) AS total_trips`, `(SELECT COUNT(t.id) FROM trips t WHERE t.customer_id = customer.id AND t.is_trip_open is true AND t.deleted_at IS NULL) AS total_requested_trips`, `(SELECT ROUND(AVG(r.rating), 1) FROM ratings r WHERE r.rated_id = customer.id AND r.rating_type = '${RATING_TYPES.DRIVER_TO_CUSTOMER}') AS average_rating`, ]) if (search) { queryBuilder.andWhere("customer.customer_name ILIKE :search", { search: `%${search}%`, }) } // Get total count const countQuery = this.customerRepository["entity"] .createQueryBuilder("customer") .leftJoin("customer.client_company", "client_company") .where("customer.deleted_at IS NULL") if (search) { countQuery.andWhere("customer.customer_name ILIKE :search", { search: `%${search}%`, }) } countQuery.select("COUNT(DISTINCT customer.id)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 // Add order by and group by queryBuilder .orderBy("customer.customer_name", "ASC") .addGroupBy("customer.id") .addGroupBy("client_company.id") if (!is_export) { queryBuilder.offset(+skip).limit(+limit) } const customerData = await queryBuilder.getRawMany() if (is_export) { const excelData = this.formatCustomerTransportHistoryForExcel(customerData) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Customer transport history data exported successfully", buffer, ) } return successResponse( code.SUCCESS, "Customer transport history data retrieved successfully", { customers: customerData, totalCount }, ) } async dispatcherPerformance({ start_date, end_date, dispatcher_id, search, skip = 0, limit = 10, is_export, }) { const queryBuilder = this.teamMemberRepository["entity"] .createQueryBuilder("team_member") .leftJoin("team_member.role", "role") .where("team_member.deleted_at IS NULL") .andWhere("role.name = :role", { role: "Dispatcher", }) .select([ "team_member.id AS dispatcher_id", "team_member.first_name AS dispatcher_first_name", "team_member.last_name AS dispatcher_last_name", "team_member.email AS dispatcher_email", "team_member.phone_number AS dispatcher_phone_number", "role.name AS dispatcher_role", `(SELECT ROUND(AVG(rating.rating), 1) FROM ratings rating WHERE rating.rated_id = team_member.id AND rating.rating_type = '${RATING_TYPES.DRIVER_TO_DISPATCHER}' ${start_date && end_date ? "AND rating.created_at BETWEEN :startDate AND :endDate" : ""}) AS driver_feedback`, `(SELECT ROUND(AVG(rating.rating), 1) FROM ratings rating WHERE rating.rated_id = team_member.id AND rating.rating_type = '${RATING_TYPES.CUSTOMER_TO_DISPATCHER}' ${start_date && end_date ? "AND rating.created_at BETWEEN :startDate AND :endDate" : ""}) AS customer_feedback`, `(SELECT COUNT(t.id) FROM trips t INNER JOIN customers c ON t.customer_id = c.id WHERE c.dispatcher_id = team_member.id ${start_date && end_date ? "AND t.pickup_datetime BETWEEN :startDate AND :endDate" : ""}) AS trips_assigned`, `(SELECT COUNT(t.id) FROM trips t INNER JOIN customers c ON t.customer_id = c.id WHERE c.dispatcher_id = team_member.id AND t.status = 'completed' ${start_date && end_date ? "AND t.pickup_datetime BETWEEN :startDate AND :endDate" : ""}) AS completed_trips`, "'Excellent' AS performance", //TODO: Add performance rating ]) if (dispatcher_id) { queryBuilder.andWhere("team_member.id = :dispatcher_id", { dispatcher_id, }) } if (search) { queryBuilder.andWhere( "(team_member.first_name ILIKE :search OR team_member.last_name ILIKE :search OR team_member.email ILIKE :search)", { search: `%${search}%` }, ) } if (start_date && end_date) { queryBuilder.setParameters({ startDate: start_date, endDate: end_date }) } // Get total count const countQuery = queryBuilder .clone() .select("COUNT(DISTINCT team_member.id)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 queryBuilder .orderBy("team_member.first_name", "ASC") .addGroupBy("team_member.id") .addGroupBy("role.id") if (!is_export) { queryBuilder.offset(+skip).limit(+limit) } let dispatcherData = await queryBuilder.getRawMany() dispatcherData = dispatcherData.map((dispatcher) => { const completionRate = dispatcher.trips_assigned > 0 ? ( (dispatcher.completed_trips / dispatcher.trips_assigned) * 100 ).toFixed(2) : "0.00" return { ...dispatcher, completion_rate: `${completionRate}%`, } }) if (is_export) { const excelData = this.formatDispatcherPerformanceForExcel(dispatcherData) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Dispatcher performance data exported successfully", buffer, ) } return successResponse( code.SUCCESS, "Dispatcher performance data retrieved successfully", { dispatchers: dispatcherData, totalCount }, ) } async driverPerformance({ start_date, end_date, driver_id, search, skip = 0, limit = 10, is_export, }) { const queryBuilder = this.teamMemberRepository["entity"] .createQueryBuilder("team_member") .leftJoin("team_member.role", "role") .where("team_member.deleted_at IS NULL") .andWhere("LOWER(role.name) = LOWER(:role)", { role: "driver", }) .select([ "team_member.id AS driver_id", "team_member.first_name AS driver_first_name", "team_member.last_name AS driver_last_name", `(SELECT COUNT(t.id) FROM trips t INNER JOIN trip_fleet_assignments ta ON t.id = ta.trip_id WHERE ta.driver_id = team_member.id AND t.status = '${STATUS.COMPLETED}' ${start_date && end_date ? "AND t.pickup_datetime BETWEEN :startDate AND :endDate" : ""}) AS completed_trips`, `(SELECT COUNT(t.id) FROM trips t INNER JOIN trip_fleet_assignments ta ON t.id = ta.trip_id WHERE ta.driver_id = team_member.id AND t.status = '${STATUS.CANCELLED}' ${start_date && end_date ? "AND t.pickup_datetime BETWEEN :startDate AND :endDate" : ""}) AS cancelled_trips`, `(SELECT ROUND(AVG(r.rating), 1) FROM ratings r WHERE r.rated_id = team_member.id AND r.rating_type = '${RATING_TYPES.CUSTOMER_TO_DRIVER}' ${start_date && end_date ? "AND r.created_at BETWEEN :startDate AND :endDate" : ""}) AS average_rating`, `(SELECT ROUND(AVG(CAST(t.estimated_time AS DECIMAL)), 1) FROM trips t INNER JOIN trip_fleet_assignments ta ON t.id = ta.trip_id WHERE ta.driver_id = team_member.id ${start_date && end_date ? "AND t.pickup_datetime BETWEEN :startDate AND :endDate" : ""}) AS average_trip_time`, ]) // Average trip time and average trip rating are now included in the query if (driver_id) { queryBuilder.andWhere("team_member.id = :driver_id", { driver_id, }) } if (search) { queryBuilder.andWhere( "(team_member.first_name ILIKE :search OR team_member.last_name ILIKE :search)", { search: `%${search}%` }, ) } if (start_date && end_date) { queryBuilder.setParameters({ startDate: start_date, endDate: end_date }) } // Get total count const countQuery = this.teamMemberRepository["entity"] .createQueryBuilder("team_member") .leftJoin("team_member.role", "role") .where("team_member.deleted_at IS NULL") .andWhere("LOWER(role.name) = LOWER(:role)", { role: "driver" }) if (driver_id) { countQuery.andWhere("team_member.id = :driver_id", { driver_id }) } if (search) { countQuery.andWhere( "(team_member.first_name ILIKE :search OR team_member.last_name ILIKE :search OR team_member.email ILIKE :search)", { search: `%${search}%` }, ) } countQuery.select("COUNT(DISTINCT team_member.id)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 // Add order by and group by queryBuilder .orderBy("team_member.first_name", "ASC") .addGroupBy("team_member.id") .addGroupBy("role.id") if (!is_export) { queryBuilder.offset(+skip).limit(+limit) } let driverData = await queryBuilder.getRawMany() // Calculate average trips per day driverData = driverData.map((driver) => { const totalTrips = driver.completed_trips + driver.cancelled_trips let averageTripsPerDay = null if (start_date && end_date) { const start = new Date(start_date) const end = new Date(end_date) const daysDiff = Math.ceil((end.getTime() - start.getTime()) / (1000 * 60 * 60 * 24)) + 1 // inclusive averageTripsPerDay = daysDiff > 0 ? (totalTrips / daysDiff).toFixed(2) : "0.00" } return { ...driver, average_trips_per_day: averageTripsPerDay, } }) if (is_export) { const excelData = this.formatDriverPerformanceForExcel(driverData) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Driver performance data exported successfully", buffer, ) } return successResponse( code.SUCCESS, "Driver performance data retrieved successfully", { drivers: driverData, totalCount }, ) } async hospitalReport({ search, state, skip = 0, limit = 10, is_export }) { const queryBuilder = this.hospitalRepository["entity"] .createQueryBuilder("hospital") .leftJoin("hospital.city", "city") .leftJoin("hospital.state", "state") .where("hospital.deleted_at IS NULL") .select([ "hospital.id AS hospital_id", "hospital.name AS hospital_name", `CONCAT_WS(', ', city.name, state.name) AS location`, `(SELECT COUNT(c.id) FROM customers c WHERE c.hospital_id = hospital.id AND c.deleted_at IS NULL) AS patients_referred`, `(SELECT COUNT(t.id) FROM trips t WHERE t.dropoff_hospital_id = hospital.id AND t.deleted_at IS NULL) AS total_trips`, `(SELECT t.appointment_type FROM trips t WHERE t.dropoff_hospital_id = hospital.id AND t.deleted_at IS NULL AND t.appointment_type IS NOT NULL GROUP BY t.appointment_type ORDER BY COUNT(*) DESC LIMIT 1) AS appointment_type`, `(SELECT ROUND(AVG(CAST(t.estimated_distance AS DECIMAL)), 1) FROM trips t WHERE t.dropoff_hospital_id = hospital.id AND t.deleted_at IS NULL) AS average_distance`, `(SELECT ROUND(AVG(CAST(t.estimated_time AS DECIMAL)), 1) FROM trips t WHERE t.dropoff_hospital_id = hospital.id AND t.deleted_at IS NULL) AS average_time`, ]) if (search) { queryBuilder.andWhere("hospital.name ILIKE :search", { search: `%${search}%`, }) } if (state) { queryBuilder.andWhere("LOWER(state.name) = LOWER(:state)", { state }) } // Get total count const countQuery = this.hospitalRepository["entity"] .createQueryBuilder("hospital") .leftJoin("hospital.city", "city") .leftJoin("hospital.state", "state") .where("hospital.deleted_at IS NULL") if (search) { countQuery.andWhere("hospital.name ILIKE :search", { search: `%${search}%`, }) } if (state) { countQuery.andWhere("LOWER(state.name) = LOWER(:state)", { state }) } countQuery.select("COUNT(DISTINCT hospital.id)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 // Add order by and group by queryBuilder .orderBy("hospital.name", "ASC") .addGroupBy("hospital.id") .addGroupBy("city.id") .addGroupBy("state.id") if (!is_export) { queryBuilder.offset(+skip).limit(+limit) } const hospitalData = await queryBuilder.getRawMany() if (is_export) { const excelData = this.formatHospitalReportForExcel(hospitalData) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Hospital report data exported successfully", buffer, ) } return successResponse( code.SUCCESS, "Hospital report data retrieved successfully", { hospitals: hospitalData, totalCount }, ) } async vehicleUtilization({ search, state, vehicle_type, skip = 0, limit = 10, is_export, }) { const queryBuilder = this.fleetManagementRepository["entity"] .createQueryBuilder("fleet") .leftJoin("fleet.vehicle_manufacture", "vehicleManufacture") .leftJoin("fleet.vehicleModel", "vehicleModel") .leftJoin("fleet.vehicle_type", "vehicleType") .leftJoin("fleet.state_details", "state") .where("fleet.deleted_at IS NULL") .select([ "fleet.id AS vehicle_id", "fleet.registration_number AS registration_number", "vehicleManufacture.name AS vehicle_manufacture", "vehicleModel.name AS vehicle_model", "vehicleType.name AS vehicle_type", "state.name AS state", `(SELECT COUNT(DISTINCT(ta.trip_id)) FROM trip_fleet_assignments ta INNER JOIN trips t ON ta.trip_id = t.id WHERE ta.fleet_id = fleet.id AND t.deleted_at IS NULL) AS total_trips`, `(SELECT MAX(t.pickup_datetime) FROM trip_fleet_assignments ta INNER JOIN trips t ON ta.trip_id = t.id WHERE ta.fleet_id = fleet.id AND t.deleted_at IS NULL) AS last_trip_date`, `(SELECT MAX(vm.maintenance_date) FROM vehicle_maintenances vm WHERE vm.fleet_id = fleet.id) AS last_maintenance`, `(SELECT COALESCE(SUM(CAST(REGEXP_REPLACE(t.estimated_distance, '[^0-9.]', '', 'g') AS NUMERIC)), 0) FROM trip_fleet_assignments ta INNER JOIN trips t ON ta.trip_id = t.id WHERE ta.fleet_id = fleet.id AND t.deleted_at IS NULL AND t.estimated_distance IS NOT NULL) AS total_distance`, ]) if (search) { queryBuilder.andWhere( "(fleet.registration_number ILIKE :search OR vehicleManufacture.name ILIKE :search OR vehicleModel.name ILIKE :search)", { search: `%${search}%` }, ) } if (state) { queryBuilder.andWhere("LOWER(state.name) = LOWER(:state)", { state }) } if (vehicle_type) { queryBuilder.andWhere("LOWER(vehicleType.name) = LOWER(:vehicle_type)", { vehicle_type, }) } // Get total count const countQuery = this.fleetManagementRepository["entity"] .createQueryBuilder("fleet") .leftJoin("fleet.vehicle_manufacture", "vehicleManufacture") .leftJoin("fleet.vehicleModel", "vehicleModel") .leftJoin("fleet.vehicle_type", "vehicleType") .leftJoin("fleet.state_details", "state") .where("fleet.deleted_at IS NULL") if (search) { countQuery.andWhere( "(fleet.registration_number ILIKE :search OR vehicleManufacture.name ILIKE :search OR vehicleModel.name ILIKE :search)", { search: `%${search}%` }, ) } if (state) { countQuery.andWhere("LOWER(state.name) = LOWER(:state)", { state }) } if (vehicle_type) { countQuery.andWhere("LOWER(vehicleType.name) = LOWER(:vehicle_type)", { vehicle_type, }) } countQuery.select("COUNT(DISTINCT fleet.id)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 // Add order by and group by queryBuilder .orderBy("fleet.registration_number", "ASC") .addGroupBy("fleet.id") .addGroupBy("vehicleManufacture.id") .addGroupBy("vehicleModel.id") .addGroupBy("vehicleType.id") .addGroupBy("state.id") if (!is_export) { queryBuilder.offset(+skip).limit(+limit) } const vehicleData = await queryBuilder.getRawMany() if (is_export) { const excelData = this.formatVehicleUtilizationForExcel(vehicleData) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Vehicle utilization data exported successfully", buffer, ) } return successResponse( code.SUCCESS, "Vehicle utilization data retrieved successfully", { vehicles: vehicleData, totalCount }, ) } async paymentCollectionSummary({ start_date, end_date, search, client_id, payment_method, skip = 0, limit = 10, is_export, }) { const queryBuilder = this.paymentRepository["entity"] .createQueryBuilder("payment") .leftJoin("payment.invoice", "invoice") .leftJoin("payment.client_company", "client_company") .leftJoin("invoice.customer", "customer") .where("payment.deleted_at IS NULL") .select([ "payment.id AS payment_id", "invoice.id AS invoice_number", "payment.payment_date AS date_of_collection", "payment.amount AS amount_collected", "customer.customer_name AS customer_name", "client_company.company_name AS client_name", "payment.payment_method AS payment_type", ]) if (start_date && end_date) { queryBuilder.andWhere( "payment.payment_date BETWEEN :startDate AND :endDate", { startDate: start_date, endDate: end_date }, ) } if (client_id) { queryBuilder.andWhere("client_company.id = :client_id", { client_id }) } if (payment_method) { queryBuilder.andWhere("payment.payment_method = :payment_method", { payment_method, }) } if (search) { queryBuilder.andWhere( "(customer.customer_name ILIKE :search OR client_company.company_name ILIKE :search OR payment.reference_number ILIKE :search)", { search: `%${search}%` }, ) } // Add order by and group by queryBuilder .orderBy("payment.payment_date", "DESC") .addGroupBy("payment.id") .addGroupBy("invoice.id") .addGroupBy("client_company.id") .addGroupBy("customer.id") // Get total count const countQuery = queryBuilder .clone() .select("COUNT(DISTINCT payment.id)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 if (!is_export) { queryBuilder.offset(+skip).limit(+limit) } const paymentData = await queryBuilder.getRawMany() if (is_export) { const excelData = this.formatPaymentCollectionSummaryForExcel(paymentData) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Payment collection summary data exported successfully", buffer, ) } return successResponse( code.SUCCESS, "Payment collection summary data retrieved successfully", { payments: paymentData, totalCount }, ) } async pendingPaymentsOutstandingReport({ start_date, end_date, search, client_id, skip = 0, limit = 10, is_export, }) { const queryBuilder = this.invoiceRepository["entity"] .createQueryBuilder("invoice") .leftJoin("invoice.customer", "customer") .leftJoin("invoice.client_company", "client_company") .where("invoice.deleted_at IS NULL") .andWhere( "(invoice.total_amount::numeric - COALESCE(invoice.payment_received_amount::numeric, 0)) > 0", ) .select([ "invoice.id AS invoice_number", "invoice.created_at AS invoice_date", "invoice.total_amount AS invoice_amount", "(invoice.total_amount::numeric - COALESCE(invoice.payment_received_amount::numeric, 0)) AS amount_due", "customer.customer_name AS customer_name", "client_company.company_name AS client_name", ]) if (start_date && end_date) { queryBuilder.andWhere( "invoice.created_at BETWEEN :startDate AND :endDate", { startDate: start_date, endDate: end_date }, ) } if (client_id) { queryBuilder.andWhere("client_company.id = :client_id", { client_id }) } if (search) { queryBuilder.andWhere( "(customer.customer_name ILIKE :search OR client_company.company_name ILIKE :search OR CAST(invoice.id AS TEXT) ILIKE :search)", { search: `%${search}%` }, ) } // Add order by and group by queryBuilder .orderBy("invoice.created_at", "DESC") .addGroupBy("invoice.id") .addGroupBy("customer.id") .addGroupBy("client_company.id") // Get total count const countQuery = queryBuilder .clone() .select("COUNT(DISTINCT invoice.id)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 if (!is_export) { queryBuilder.offset(+skip).limit(+limit) } const pendingData = await queryBuilder.getRawMany() if (is_export) { const excelData = this.formatPendingPaymentsOutstandingForExcel(pendingData) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Pending payments and outstanding report data exported successfully", buffer, ) } return successResponse( code.SUCCESS, "Pending payments and outstanding report data retrieved successfully", { invoices: pendingData, totalCount }, ) } async annualTransportBusinessSummary({ year, search, skip = 0, limit = 10, is_export, }) { const queryBuilder = this.tripRepository["entity"] .createQueryBuilder("trip") .leftJoin("trip.customer", "customer") .leftJoin("trip.client", "client") .leftJoin("trip.invoices", "invoice") .leftJoin("invoice.payments", "payment") .leftJoin("trip.ratings", "rating") .where("trip.deleted_at IS NULL") .andWhere( "rating.rating_type = :ratingType OR rating.rating_type IS NULL", { ratingType: "customer_to_driver", }, ) .select([ "DATE_TRUNC('year', trip.pickup_datetime) AS year", "COUNT(DISTINCT trip.id) AS total_trips", "COALESCE(SUM(payment.amount), 0) AS payment_received", "COALESCE(SUM(invoice.total_amount), 0) AS total_payment", "COALESCE(SUM(invoice.total_amount - invoice.payment_received_amount), 0) AS pending_payment", "COALESCE(AVG(rating.rating), 0) AS avg_rating", ]) .groupBy("DATE_TRUNC('year', trip.pickup_datetime)") .orderBy("year", "DESC") if (year) { queryBuilder.andWhere( "DATE_TRUNC('year', trip.pickup_datetime) = DATE_TRUNC('year', :year)", { year: `${year}-01-01`, }, ) } if (search) { queryBuilder.andWhere( "CAST(DATE_TRUNC('year', trip.pickup_datetime) AS TEXT) ILIKE :search", { search: `%${search}%`, }, ) } // Get total count const countQuery = queryBuilder.clone().select("COUNT(*)", "count") const totalCountResult = await countQuery.getRawOne() const totalCount = totalCountResult?.count || 0 if (!is_export) { queryBuilder.offset(+skip).limit(+limit) } const annualData = await queryBuilder.getRawMany() if (is_export) { const excelData = this.formatAnnualTransportBusinessSummaryForExcel(annualData) const buffer = await this.excelService.generateExcelBuffer(excelData) return successResponse( code.SUCCESS, "Annual transport business summary data exported successfully", buffer, ) } return successResponse( code.SUCCESS, "Annual transport business summary data retrieved successfully", { annual: annualData, totalCount }, ) } private formatTripDataForExcel(data: any[]) { const header = [ "Trip ID", "Date", "Pick Up Location", "Drop Off Location", "Driver", "Vehicle", "Service Type", "Distance", "Time", "Cost", "Customer", "PRN", "Client Name", "Status", ] const rows = data.map((trip) => [ trip.trip_id, trip.trip_pickup_datetime, trip.trip_pick_up_address, trip.trip_drop_off_address, `${trip.driver_first_name} ${trip.driver_last_name}`, trip.vehicle_type_name, trip?.service_type, trip.trip_estimated_distance ? `${Number(trip.trip_estimated_distance).toFixed(2)} miles` : "", trip.trip_estimated_time ? `${Number(trip.trip_estimated_time).toFixed(2)} mins` : "", trip.cost !== undefined && trip.cost !== null ? `$${trip.cost}` : "", trip.customer_name, trip.prn_number, trip.client_company_name, trip.trip_status, ]) return [header, ...rows] } private formatDispatcherPerformanceForExcel(data: any[]) { const header = [ "Dispatcher ID", "Name", "Email", "Phone Number", "Role", "Period", "Trips Assigned", "Driver Feedback", "Customer Feedback", "Completion Rate", "Performance Rating", ] const rows = data.map((dispatcher) => [ dispatcher.dispatcher_id, `${dispatcher.dispatcher_first_name} ${dispatcher.dispatcher_last_name}`, dispatcher.dispatcher_email, dispatcher.dispatcher_phone_number, dispatcher.dispatcher_role, dispatcher.period, dispatcher.trips_assigned, dispatcher.driver_feedback, dispatcher.customer_feedback, dispatcher.completion_rate, dispatcher.performance, ]) return [header, ...rows] } private formatDriverPerformanceForExcel(data: any[]) { const header = [ "Driver ID", "Driver Name", "Completed Trips", "Cancelled Trips", "Average Trips/Day", "Average Rating", "Average Trip Time", ] const rows = data.map((driver) => { const averageRating = !isEmpty(driver.average_rating) ? Number(driver.average_rating) : undefined const averageTripTime = !isEmpty(driver.average_trip_time) ? Number(driver.average_trip_time) : undefined return [ driver.driver_id, `${driver.driver_first_name} ${driver.driver_last_name}`, Number(driver.completed_trips) || 0, Number(driver.cancelled_trips) || 0, driver.average_trips_per_day ?? "N/A", !isNaN(averageRating) ? averageRating.toFixed(2) : "N/A", !isNaN(averageTripTime) ? averageTripTime.toFixed(2) : "N/A", ] }) return [header, ...rows] } private formatHospitalReportForExcel(data: any[]) { const header = [ "Hospital Name", "Location", "Patients Referred", "Total Trips", "Appointment Type", "Average Distance", "Average Time", ] const rows = data.map((hospital) => [ hospital.hospital_name, hospital.location, hospital.patients_referred, hospital.total_trips, hospital.appointment_type, hospital.average_distance, hospital.average_time, ]) return [header, ...rows] } private formatCustomerTransportHistoryForExcel(data: any[]) { const header = [ "Customer Name", "Client Name", "Total Trips", "Requested Trips", "Average Rating", ] const rows = data.map((customer) => { const averageRating = !isEmpty(customer?.average_rating) ? Number(customer?.average_rating) : undefined return [ customer.customer_name, customer.client_name, Number(customer.total_trips) || 0, Number(customer.total_requested_trips) || 0, !isNaN(averageRating) ? averageRating.toFixed(2) : "N/A", ] }) return [header, ...rows] } private formatVehicleUtilizationForExcel(data: any[]) { const header = [ "Vehicle ID", "Registration Number", "Vehicle Manufacture", "Vehicle Model", "Vehicle Type", "State", "Total Trips", "Total Distance", "Last Trip Date", "Last Maintenance", ] const rows = data.map((vehicle) => [ vehicle.vehicle_id, vehicle.registration_number, vehicle.vehicle_manufacture, vehicle.vehicle_model, vehicle.vehicle_type, vehicle.state, vehicle.total_trips, parseFloat(vehicle.total_distance || 0).toFixed(2), vehicle.last_trip_date, vehicle.last_maintenance, ]) return [header, ...rows] } private formatPaymentCollectionSummaryForExcel(data: any[]) { const header = [ "Payment ID", "Invoice Number", "Date of Collection", "Amount Collected", "Customer Name", "Client Name", "Payment Type", ] const rows = data.map((payment) => [ payment.payment_id, payment.invoice_number, moment(payment.date_of_collection).format("MM/DD/YYYY"), payment.amount_collected, payment.customer_name, payment.client_name, payment.payment_type, ]) return [header, ...rows] } private formatPendingPaymentsOutstandingForExcel(data: any[]) { const header = [ "Invoice Number", "Invoice Date", "Invoice Amount", "Amount Due", "Customer Name", "Client Name", ] const rows = data.map((invoice) => [ invoice.invoice_number, moment(invoice.invoice_date).format("MM/DD/YYYY"), invoice.invoice_amount, invoice.amount_due, invoice.customer_name, invoice.client_name, ]) return [header, ...rows] } private formatAnnualTransportBusinessSummaryForExcel(data: any[]) { const header = [ "Year", "Total Trips", "Payment Received", "Total Payment", "Pending Payment", "Average Rating", ] const rows = data.map((annual) => [ annual.year, annual.total_trips, annual.payment_received, annual.total_payment, annual.pending_payment, parseFloat(annual.avg_rating || 0).toFixed(2), ]) return [header, ...rows] } private async calculateTripTotalPrice(tripId: number) { const basePricing: any = await this.tripBasePricingRepository.getByParams({ where: { trip_id: tripId }, }) const addOnsPricing: any = await this.tripAddonsPricingRepository.getByParams({ where: { trip_id: tripId }, }) const baseTotal = basePricing.reduce( (sum, b) => sum + Number(b?.vehicle_total_price || 0) + Number(b?.meet_greet_price || 0), 0, ) const addonsTotal = addOnsPricing.reduce( (sum, a) => sum + Number(a?.total_price || 0), 0, ) // return baseTotal + addonsTotal const totalTripPrice = baseTotal + addonsTotal return { baseTotal, addonsTotal, totalTripPrice } } }