Reports

This document describes the Reports feature in the Web/Admin application and how it maps to the backend Report Module. Use this as a guide for product behavior, UI flows, and the underlying API interactions with critical business logic for data aggregation, analytics calculations, and export generation.

The admin/web app provides operational tooling for:

• Viewing aggregated analytics and KPIs with real-time data processing
• Generating tabular reports with advanced filtering and grouping capabilities
• Exporting data in multiple formats (CSV, Excel, PDF) with business rule validation
• Analyzing trends and patterns with statistical calculations and comparative metrics

The backend reference for all rules and endpoints is documented in the backend guide: "Report Module." The web layer consumes these via the SDK/GraphQL and/or HTTP endpoints exposed by the backend.

Core Concepts (from Backend)

• Data Aggregation: Real-time calculations of counts, sums, averages, and percentages with caching strategies
• Filtering & Grouping: Multi-dimensional filtering by date, entity, status with dynamic grouping capabilities
• Export Generation: Server-side export processing with data validation and format optimization
• Analytics Calculations: Statistical analysis including success rates, trends, and comparative metrics
• Access Control: Role-based data access with organization-level scoping and privacy compliance
• Performance Optimization: Cached aggregations, pagination, and lazy loading for large datasets

Web/Admin Capabilities

1) Filters & Analytics

Filtering Business Rules:

• Date Range Filtering: Global date range (Today, 7D, 30D, Custom) with timezone handling
• Entity Filtering: Multi-select filtering by therapist, child, appointment type, and status
• Real-time Updates: Live data refresh with cached aggregations for performance
• Access Control: Role-based filtering with organization-level data scoping

Analytics Calculation Logic:

// KPI calculation with business rules
const calculateKPIs = (rows, dateRange, filters) => {
  // Apply filters
  const filteredData = applyFilters(rows, filters);

  // Calculate core metrics
  const totalAppointments = filteredData.length;
  const completed = filteredData.filter((r) => r.status === 'COMPLETED').length;
  const cancelled = filteredData.filter((r) => r.status === 'CANCELLED').length;
  const inProgress = filteredData.filter(
    (r) => r.status === 'IN_PROGRESS',
  ).length;

  // Revenue calculations with payment status validation
  const successfulPayments = filteredData.filter(
    (r) => r.paymentStatus === 'SUCCESSFUL',
  );
  const totalRevenue = successfulPayments.reduce((sum, r) => sum + r.amount, 0);
  const netRevenue = successfulPayments.reduce(
    (sum, r) => sum + r.net_amount,
    0,
  );

  // Success rate calculation
  const successRate =
    totalAppointments > 0
      ? Math.round((completed / totalAppointments) * 100)
      : 0;

  // Cancellation rate calculation
  const cancellationRate =
    totalAppointments > 0
      ? Math.round((cancelled / totalAppointments) * 100)
      : 0;

  // Average session duration
  const completedSessions = filteredData.filter(
    (r) => r.status === 'COMPLETED' && r.duration,
  );
  const avgDuration =
    completedSessions.length > 0
      ? Math.round(
          completedSessions.reduce((sum, r) => sum + r.duration, 0) /
            completedSessions.length,
        )
      : 0;

  // Revenue per appointment
  const revenuePerAppointment =
    completed > 0 ? Math.round(totalRevenue / completed) : 0;

  return {
    total_appointments: totalAppointments,
    completed: completed,
    cancelled: cancelled,
    in_progress: inProgress,
    total_revenue: totalRevenue,
    net_revenue: netRevenue,
    success_rate: successRate,
    cancellation_rate: cancellationRate,
    avg_duration: avgDuration,
    revenue_per_appointment: revenuePerAppointment,
    date_range: dateRange,
    filters_applied: filters,
  };
};

// Advanced filtering logic
const applyFilters = (data, filters) => {
  let filtered = [...data];

  // Date range filtering
  if (filters.dateRange) {
    const { start, end } = filters.dateRange;
    filtered = filtered.filter((item) => {
      const itemDate = new Date(item.date || item.start_time);
      return itemDate >= start && itemDate <= end;
    });
  }

  // Therapist filtering
  if (filters.therapistIds && filters.therapistIds.length > 0) {
    filtered = filtered.filter(
      (item) =>
        filters.therapistIds.includes(item.therapist_id) ||
        (item.therapist_ids &&
          item.therapist_ids.some((id) => filters.therapistIds.includes(id))),
    );
  }

  // Status filtering
  if (filters.statuses && filters.statuses.length > 0) {
    filtered = filtered.filter((item) =>
      filters.statuses.includes(item.status),
    );
  }

  // Payment status filtering
  if (filters.paymentStatuses && filters.paymentStatuses.length > 0) {
    filtered = filtered.filter((item) =>
      filters.paymentStatuses.includes(item.paymentStatus),
    );
  }

  // Appointment type filtering
  if (filters.appointmentTypes && filters.appointmentTypes.length > 0) {
    filtered = filtered.filter((item) =>
      filters.appointmentTypes.includes(item.type),
    );
  }

  return filtered;
};

// Trend analysis calculation
const calculateTrends = (currentData, previousData, metric) => {
  const currentValue = currentData[metric] || 0;
  const previousValue = previousData[metric] || 0;

  if (previousValue === 0) {
    return {
      value: currentValue,
      change: 0,
      change_percentage: 0,
      trend: 'new',
    };
  }

  const change = currentValue - previousValue;
  const changePercentage = Math.round((change / previousValue) * 100);
  const trend = change > 0 ? 'up' : change < 0 ? 'down' : 'stable';

  return {
    value: currentValue,
    change: change,
    change_percentage: changePercentage,
    trend: trend,
  };
};

Implementation:

// React component with KPI calculations
const ReportsDashboard = () => {
  const [data, setData] = useState([]);
  const [filters, setFilters] = useState({});
  const [dateRange, setDateRange] = useState({ start: null, end: null });

  // Calculate KPIs with business rules
  const kpis = useMemo(() => {
    return calculateKPIs(data, dateRange, filters);
  }, [data, dateRange, filters]);

  // Calculate trends (compare with previous period)
  const trends = useMemo(() => {
    const previousData = getPreviousPeriodData(data, dateRange);
    return {
      appointments: calculateTrends(kpis, previousData, 'total_appointments'),
      revenue: calculateTrends(kpis, previousData, 'total_revenue'),
      success_rate: calculateTrends(kpis, previousData, 'success_rate'),
    };
  }, [kpis, data, dateRange]);

  return (
    <div className="reports-dashboard">
      <div className="kpi-grid">
        <KPICard
          title="Total Appointments"
          value={kpis.total_appointments}
          trend={trends.appointments}
        />
        <KPICard
          title="Success Rate"
          value={`${kpis.success_rate}%`}
          trend={trends.success_rate}
        />
        <KPICard
          title="Total Revenue"
          value={`₹${kpis.total_revenue.toLocaleString()}`}
          trend={trends.revenue}
        />
      </div>
    </div>
  );
};

2) Tabular Reports

Report Generation Business Rules:

• Dynamic Grouping: Group by therapist, child, day, week, month with hierarchical drill-down
• Sorting & Pagination: Multi-column sorting with server-side pagination for performance
• Data Aggregation: Real-time calculations of sums, averages, and counts with caching
• Export Integration: Direct export capabilities from tabular views with applied filters

Report Generation Logic:

// Advanced grouping and aggregation logic
const generateTabularReport = (data, groupBy, aggregations, filters) => {
  // Apply filters first
  const filteredData = applyFilters(data, filters);

  // Group data based on selected dimension
  const groupedData = groupData(filteredData, groupBy);

  // Apply aggregations to each group
  const aggregatedData = applyAggregations(groupedData, aggregations);

  // Sort and paginate results
  return sortAndPaginate(aggregatedData, filters.sorting, filters.pagination);
};

// Dynamic grouping logic
const groupData = (data, groupBy) => {
  const groups = new Map();

  data.forEach((item) => {
    let groupKey;

    switch (groupBy) {
      case 'therapist':
        groupKey = item.therapist_id || item.primary_therapist_id;
        break;
      case 'child':
        groupKey = item.child_id;
        break;
      case 'day':
        groupKey = new Date(item.start_time).toISOString().split('T')[0];
        break;
      case 'week':
        const weekStart = getWeekStart(new Date(item.start_time));
        groupKey = weekStart.toISOString().split('T')[0];
        break;
      case 'month':
        groupKey = new Date(item.start_time).toISOString().substring(0, 7); // YYYY-MM
        break;
      case 'appointment_type':
        groupKey = item.type;
        break;
      case 'status':
        groupKey = item.status;
        break;
      default:
        groupKey = 'all';
    }

    if (!groups.has(groupKey)) {
      groups.set(groupKey, []);
    }
    groups.get(groupKey).push(item);
  });

  return groups;
};

// Apply aggregations to grouped data
const applyAggregations = (groupedData, aggregations) => {
  const results = [];

  for (const [groupKey, items] of groupedData) {
    const aggregated = {
      group_key: groupKey,
      group_label: getGroupLabel(groupKey),
      item_count: items.length,
      ...calculateAggregations(items, aggregations),
    };
    results.push(aggregated);
  }

  return results;
};

// Calculate various aggregations
const calculateAggregations = (items, aggregations) => {
  const result = {};

  aggregations.forEach((agg) => {
    switch (agg.type) {
      case 'sum':
        result[agg.field] = items.reduce(
          (sum, item) => sum + (item[agg.field] || 0),
          0,
        );
        break;
      case 'average':
        const sum = items.reduce(
          (sum, item) => sum + (item[agg.field] || 0),
          0,
        );
        result[agg.field] =
          items.length > 0 ? Math.round(sum / items.length) : 0;
        break;
      case 'count':
        result[agg.field] = items.filter(
          (item) => item[agg.field] !== null && item[agg.field] !== undefined,
        ).length;
        break;
      case 'percentage':
        const total = items.length;
        const matching = items.filter(
          (item) => item[agg.field] === agg.value,
        ).length;
        result[agg.field] =
          total > 0 ? Math.round((matching / total) * 100) : 0;
        break;
      case 'min':
        result[agg.field] = Math.min(
          ...items.map((item) => item[agg.field] || Infinity),
        );
        break;
      case 'max':
        result[agg.field] = Math.max(
          ...items.map((item) => item[agg.field] || -Infinity),
        );
        break;
    }
  });

  return result;
};

// Revenue breakdown calculation
const calculateRevenueBreakdown = (items) => {
  const breakdown = {
    total_revenue: 0,
    consultation_fees: 0,
    platform_fees: 0,
    taxes: 0,
    net_revenue: 0,
    successful_payments: 0,
    failed_payments: 0,
  };

  items.forEach((item) => {
    if (item.paymentStatus === 'SUCCESSFUL') {
      breakdown.total_revenue += item.amount || 0;
      breakdown.consultation_fees += item.consultation_fee || 0;
      breakdown.platform_fees += item.platform_fee || 0;
      breakdown.taxes += item.taxes || 0;
      breakdown.net_revenue += item.net_amount || 0;
      breakdown.successful_payments++;
    } else if (item.paymentStatus === 'FAILED') {
      breakdown.failed_payments++;
    }
  });

  return breakdown;
};

// Performance metrics calculation
const calculatePerformanceMetrics = (items) => {
  const completed = items.filter((item) => item.status === 'COMPLETED');
  const cancelled = items.filter((item) => item.status === 'CANCELLED');
  const total = items.length;

  return {
    completion_rate:
      total > 0 ? Math.round((completed.length / total) * 100) : 0,
    cancellation_rate:
      total > 0 ? Math.round((cancelled.length / total) * 100) : 0,
    avg_session_duration:
      completed.length > 0
        ? Math.round(
            completed.reduce((sum, item) => sum + (item.duration || 0), 0) /
              completed.length,
          )
        : 0,
    total_sessions: completed.length,
    total_cancellations: cancelled.length,
  };
};

Implementation:

// React component for tabular reports
const TabularReport = ({ data, groupBy, aggregations, filters }) => {
  const [reportData, setReportData] = useState([]);
  const [loading, setLoading] = useState(false);

  useEffect(() => {
    setLoading(true);
    const report = generateTabularReport(data, groupBy, aggregations, filters);
    setReportData(report);
    setLoading(false);
  }, [data, groupBy, aggregations, filters]);

  const columns = [
    { key: 'group_label', title: getGroupTitle(groupBy), sortable: true },
    { key: 'item_count', title: 'Count', sortable: true },
    ...aggregations.map((agg) => ({
      key: agg.field,
      title: agg.title,
      sortable: true,
      formatter: agg.formatter,
    })),
  ];

  return (
    <div className="tabular-report">
      <ReportTable
        data={reportData}
        columns={columns}
        loading={loading}
        onSort={handleSort}
        onPageChange={handlePageChange}
      />
    </div>
  );
};

3) Export & Data Generation

Export Business Rules:

• Format Support: CSV, Excel, PDF with server-side generation for large datasets
• Data Validation: Export data validation with business rule compliance
• Performance Optimization: Streaming exports for large datasets with progress tracking
• Access Control: Role-based export permissions with audit logging

Export Generation Logic:

// Advanced export generation with business rules
const generateExport = async (data, format, filters, options) => {
  // Validate export permissions
  const hasPermission = await validateExportPermission(
    options.userRole,
    format,
  );
  if (!hasPermission) {
    throw new Error('Insufficient permissions for export');
  }

  // Apply business rules and filters
  const filteredData = applyExportFilters(data, filters);
  const validatedData = validateExportData(filteredData);

  // Generate export based on format
  switch (format) {
    case 'csv':
      return generateCSVExport(validatedData, options);
    case 'excel':
      return generateExcelExport(validatedData, options);
    case 'pdf':
      return generatePDFExport(validatedData, options);
    default:
      throw new Error(`Unsupported export format: ${format}`);
  }
};

// CSV export with business rule validation
const generateCSVExport = (data, options) => {
  const {
    includeHeaders = true,
    delimiter = ',',
    encoding = 'utf-8',
  } = options;

  // Sanitize data for CSV export
  const sanitizedData = data.map((item) => sanitizeCSVRow(item));

  // Generate headers
  const headers = includeHeaders ? Object.keys(sanitizedData[0] || {}) : [];

  // Generate CSV content
  const csvContent = [
    ...(includeHeaders ? [headers.join(delimiter)] : []),
    ...sanitizedData.map((row) =>
      headers
        .map((header) => {
          const value = row[header];
          // Escape CSV values
          if (
            typeof value === 'string' &&
            (value.includes(',') || value.includes('"') || value.includes('\n'))
          ) {
            return `"${value.replace(/"/g, '""')}"`;
          }
          return value || '';
        })
        .join(delimiter),
    ),
  ].join('\n');

  // Add BOM for UTF-8 encoding
  const bom = encoding === 'utf-8' ? '\uFEFF' : '';

  return {
    content: bom + csvContent,
    mimeType: 'text/csv',
    filename: generateExportFilename('csv', options.dateRange),
  };
};

// Excel export with multiple sheets
const generateExcelExport = (data, options) => {
  const { includeCharts = false, sheetNames = ['Data'] } = options;

  // Group data by different dimensions for multiple sheets
  const sheets = {
    Summary: generateSummarySheet(data),
    'Detailed Data': generateDetailedSheet(data),
    'Revenue Breakdown': generateRevenueSheet(data),
    'Performance Metrics': generatePerformanceSheet(data),
  };

  // Generate Excel workbook
  const workbook = {
    sheets: sheets,
    metadata: {
      title: 'Appointment Report',
      author: options.userName,
      created: new Date().toISOString(),
      filters: options.filters,
    },
  };

  return {
    workbook: workbook,
    mimeType:
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
    filename: generateExportFilename('xlsx', options.dateRange),
  };
};

// PDF export with business formatting
const generatePDFExport = (data, options) => {
  const { includeCharts = true, includeSummary = true } = options;

  // Generate PDF content
  const pdfContent = {
    title: 'Appointment Report',
    generatedAt: new Date().toISOString(),
    dateRange: options.dateRange,
    summary: includeSummary ? generateReportSummary(data) : null,
    charts: includeCharts ? generateReportCharts(data) : null,
    tables: generateReportTables(data),
    footer: {
      generatedBy: options.userName,
      pageNumbers: true,
      confidentiality: 'CONFIDENTIAL - Internal Use Only',
    },
  };

  return {
    content: pdfContent,
    mimeType: 'application/pdf',
    filename: generateExportFilename('pdf', options.dateRange),
  };
};

// Data validation for exports
const validateExportData = (data) => {
  return data.map((item) => {
    // Remove sensitive information based on user role
    const sanitized = { ...item };

    // Remove PII if user doesn't have permission
    if (!hasPIIPermission()) {
      delete sanitized.child_name;
      delete sanitized.parent_phone;
      delete sanitized.parent_email;
    }

    // Format monetary values
    if (sanitized.amount) {
      sanitized.amount = formatCurrency(sanitized.amount);
    }

    // Format dates
    if (sanitized.start_time) {
      sanitized.start_time = formatDate(sanitized.start_time);
    }

    return sanitized;
  });
};

// Export progress tracking
const trackExportProgress = (exportId, progress) => {
  // Update export status in real-time
  updateExportStatus(exportId, {
    status: progress.status,
    progress: progress.percentage,
    message: progress.message,
    updated_at: new Date().toISOString(),
  });

  // Notify user of progress
  if (progress.percentage % 25 === 0) {
    // Every 25%
    sendNotification({
      type: 'EXPORT_PROGRESS',
      userId: progress.userId,
      data: { exportId, progress: progress.percentage },
    });
  }
};

GraphQL Implementation:

import { gql, ApolloClient } from '@apollo/client';

const GENERATE_EXPORT = gql`
  mutation GenerateExport($input: GenerateExportInput!) {
    generateExport(input: $input) {
      success
      message
      data {
        export_id
        status
        download_url
        expires_at
        file_size
        format
      }
    }
  }
`;

const GET_EXPORT_STATUS = gql`
  query GetExportStatus($exportId: String!) {
    exportStatus(exportId: $exportId) {
      status
      progress
      message
      download_url
      error_message
    }
  }
`;

export async function generateExport(
  client: ApolloClient<unknown>,
  data: any[],
  format: 'csv' | 'excel' | 'pdf',
  filters: any,
  options: any,
) {
  // Pre-validate export request
  const validation = await validateExportRequest(data, format, options);
  if (!validation.valid) {
    throw new Error(
      `Export validation failed: ${validation.errors.join(', ')}`,
    );
  }

  const { data: result } = await client.mutate({
    mutation: GENERATE_EXPORT,
    variables: {
      input: {
        data: data,
        format: format,
        filters: filters,
        options: options,
      },
    },
  });

  if (!result?.generateExport?.success) {
    throw new Error(
      result?.generateExport?.message || 'Export generation failed',
    );
  }

  return result.generateExport.data;
}

export async function downloadExport(
  client: ApolloClient<unknown>,
  exportId: string,
) {
  const { data } = await client.query({
    query: GET_EXPORT_STATUS,
    variables: { exportId },
  });

  if (data.exportStatus.status === 'COMPLETED') {
    // Trigger download
    const link = document.createElement('a');
    link.href = data.exportStatus.download_url;
    link.download = '';
    link.click();
  } else if (data.exportStatus.status === 'FAILED') {
    throw new Error(data.exportStatus.error_message || 'Export failed');
  }

  return data.exportStatus;
}

Data Flow (Web ↔ Backend)

Critical Business Logic Flow:

1. Data Aggregation: Real-time data collection → Business rule validation → Cached aggregation → Performance optimization
2. Report Generation: Filter application → Grouping logic → Statistical calculations → Format optimization
3. Export Processing: Permission validation → Data sanitization → Format generation → Progress tracking
4. Analytics Calculation: Trend analysis → Comparative metrics → Performance indicators → Real-time updates

GraphQL Operations with Business Rules:

Action	GraphQL Operation	Business Rules Applied
Fetch Report Summary	reportSummary(input)	Date range validation, access control, cached aggregations
Generate Tabular Report	generateTabularReport(input)	Dynamic grouping, statistical calculations, pagination
Create Export	generateExport(input)	Permission validation, data sanitization, format optimization
Get Export Status	exportStatus(input)	Progress tracking, download management, expiry handling

GraphQL Implementation:

import { gql, ApolloClient } from '@apollo/client';

const REPORT_SUMMARY = gql`
  query ReportSummary($input: ReportSummaryInput!) {
    reportSummary(input: $input) {
      success
      message
      data {
        total_appointments
        completed
        cancelled
        in_progress
        total_revenue
        net_revenue
        success_rate
        cancellation_rate
        avg_duration
        revenue_per_appointment
        trends {
          appointments
          revenue
          success_rate
        }
        date_range {
          start
          end
        }
        filters_applied
      }
    }
  }
`;

const GENERATE_TABULAR_REPORT = gql`
  query GenerateTabularReport($input: TabularReportInput!) {
    generateTabularReport(input: $input) {
      success
      message
      data {
        report_data {
          group_key
          group_label
          item_count
          aggregations
        }
        pagination {
          total_count
          page_size
          current_page
          total_pages
        }
        sorting {
          field
          direction
        }
        metadata {
          generated_at
          filters_applied
          grouping_dimension
        }
      }
    }
  }
`;

export async function fetchReportSummary(
  client: ApolloClient<unknown>,
  dateRange: { start: string; end: string },
  filters: any,
) {
  // Pre-validate report request
  const validation = await validateReportRequest(dateRange, filters);
  if (!validation.valid) {
    throw new Error(
      `Report validation failed: ${validation.errors.join(', ')}`,
    );
  }

  const { data } = await client.query({
    query: REPORT_SUMMARY,
    variables: {
      input: {
        date_range: dateRange,
        filters: filters,
        include_trends: true,
        cache_duration: 300, // 5 minutes
      },
    },
  });

  if (!data?.reportSummary?.success) {
    throw new Error(data?.reportSummary?.message || 'Report fetch failed');
  }

  return data.reportSummary.data;
}

export async function generateTabularReport(
  client: ApolloClient<unknown>,
  groupBy: string,
  aggregations: any[],
  filters: any,
) {
  const { data } = await client.query({
    query: GENERATE_TABULAR_REPORT,
    variables: {
      input: {
        group_by: groupBy,
        aggregations: aggregations,
        filters: filters,
        pagination: {
          page: 1,
          page_size: 50,
        },
        sorting: {
          field: 'item_count',
          direction: 'DESC',
        },
      },
    },
  });

  if (!data?.generateTabularReport?.success) {
    throw new Error(
      data?.generateTabularReport?.message ||
        'Tabular report generation failed',
    );
  }

  return data.generateTabularReport.data;
}

Error Handling & Validation:

• Input Validation: All report requests validated against business rules and access permissions
• Data Integrity: Real-time data validation with fallback to cached aggregations
• Performance Monitoring: Query performance tracking with automatic optimization
• Error Recovery: Graceful degradation with partial data display and retry mechanisms

Security & Access Control:

• Role-Based Access: Admin-only access with organization-level data scoping
• Data Privacy: PII filtering based on user permissions and compliance requirements
• Audit Logging: Complete audit trail for all report generation and export activities
• Rate Limiting: Export generation rate limiting with queue management for large datasets

Performance Optimization:

• Cached Aggregations: Server-side caching with intelligent invalidation strategies
• Lazy Loading: Progressive data loading with pagination and virtual scrolling
• Background Processing: Asynchronous export generation with progress tracking
• Query Optimization: Database query optimization with indexing and query planning