admin_report_handler.php

<?php
require_once 'includes/session_config.php';
include 'conn.php';

// Check if user is logged in as admin
if (!isset($_SESSION['logged_in']) || $_SESSION['logged_in'] !== true || $_SESSION['user_type'] !== 'admin') {
    echo json_encode(['success' => false, 'message' => 'Unauthorized access']);
    exit();
}

// Handle report generation
if (isset($_POST['action']) && $_POST['action'] === 'generate_report') {
    $start_date = $_POST['start_date'];
    $end_date = $_POST['end_date'];
    $report_type = $_POST['report_type'];
    
    // Add one day to end date to include the entire day
    $end_date_query = date('Y-m-d', strtotime($end_date . ' +1 day'));
    
    // Format date range for display
    $date_range = date('M d, Y', strtotime($start_date)) . ' - ' . date('M d, Y', strtotime($end_date));
    
    $response = [
        'success' => true,
        'date_range' => $date_range,
        'projects' => [],
        'users' => [],
        'payments' => []
    ];
    
    // Get projects data
    if ($report_type === 'all' || $report_type === 'projects') {
        // Total projects created in date range
        $total_projects_query = "SELECT COUNT(*) as count FROM projects WHERE created_at BETWEEN '$start_date' AND '$end_date_query'";
        $total_projects_result = $conn->query($total_projects_query);
        $total_projects = $total_projects_result->fetch_assoc()['count'];
        
        // Active projects in date range
        $active_projects_query = "SELECT COUNT(*) as count FROM projects WHERE status = 'Open' AND created_at BETWEEN '$start_date' AND '$end_date_query'";
        $active_projects_result = $conn->query($active_projects_query);
        $active_projects = $active_projects_result->fetch_assoc()['count'];
        
        // Completed projects in date range
        $completed_projects_query = "SELECT COUNT(*) as count FROM projects WHERE status = 'Closed' AND created_at BETWEEN '$start_date' AND '$end_date_query'";
        $completed_projects_result = $conn->query($completed_projects_query);
        $completed_projects = $completed_projects_result->fetch_assoc()['count'];
        
        // Average project budget in date range
        $avg_project_query = "SELECT AVG(budget) as avg_budget FROM projects WHERE created_at BETWEEN '$start_date' AND '$end_date_query'";
        $avg_project_result = $conn->query($avg_project_query);
        $avg_project_value = $avg_project_result->fetch_assoc()['avg_budget'];
        $avg_project_value = $avg_project_value ? number_format($avg_project_value, 2) : '0.00';
        
        $response['projects'] = [
            'total' => number_format($total_projects),
            'active' => number_format($active_projects),
            'completed' => number_format($completed_projects),
            'avg_value' => $avg_project_value
        ];
    }
    
    // Get users data
    if ($report_type === 'all' || $report_type === 'users' || $report_type === 'clients') {
        // New users in date range
        $new_users_query = "SELECT COUNT(*) as count FROM users WHERE created_at BETWEEN '$start_date' AND '$end_date_query'";
        $new_users_result = $conn->query($new_users_query);
        $new_users = $new_users_result->fetch_assoc()['count'];
        
        // New clients in date range
        $new_clients_query = "SELECT COUNT(*) as count FROM users WHERE user_type = 'client' AND created_at BETWEEN '$start_date' AND '$end_date_query'";
        $new_clients_result = $conn->query($new_clients_query);
        $new_clients = $new_clients_result->fetch_assoc()['count'];
        
        // New freelancers in date range
        $new_freelancers_query = "SELECT COUNT(*) as count FROM users WHERE user_type = 'freelancer' AND created_at BETWEEN '$start_date' AND '$end_date_query'";
        $new_freelancers_result = $conn->query($new_freelancers_query);
        $new_freelancers = $new_freelancers_result->fetch_assoc()['count'];
        
        // Calculate user growth percentage
        $previous_period_start = date('Y-m-d', strtotime($start_date . ' -' . (strtotime($end_date) - strtotime($start_date)) . ' seconds'));
        $previous_users_query = "SELECT COUNT(*) as count FROM users WHERE created_at BETWEEN '$previous_period_start' AND '$start_date'";
        $previous_users_result = $conn->query($previous_users_query);
        $previous_users = $previous_users_result->fetch_assoc()['count'];
        
        $growth = 0;
        if ($previous_users > 0) {
            $growth = round((($new_users - $previous_users) / $previous_users) * 100);
        } elseif ($new_users > 0) {
            $growth = 100; // If there were no users before but there are now, that's 100% growth
        }
        
        $response['users'] = [
            'total' => number_format($new_users),
            'clients' => number_format($new_clients),
            'freelancers' => number_format($new_freelancers),
            'growth' => $growth
        ];
    }
    
    // Get payments data
    if ($report_type === 'all' || $report_type === 'payments') {
        // Total transactions in date range
        $total_trans_query = "SELECT COUNT(*) as count FROM project_payments WHERE payment_date BETWEEN '$start_date' AND '$end_date_query'";
        $total_trans_result = $conn->query($total_trans_query);
        $total_transactions = $total_trans_result->fetch_assoc()['count'];
        
        // Total revenue in date range
        $total_revenue_query = "SELECT SUM(amount) as total FROM project_payments WHERE status = 'Completed' AND payment_date BETWEEN '$start_date' AND '$end_date_query'";
        $total_revenue_result = $conn->query($total_revenue_query);
        $total_revenue = $total_revenue_result->fetch_assoc()['total'];
        $total_revenue = $total_revenue ? number_format($total_revenue, 2) : '0.00';
        
        // Pending payments in date range
        $pending_payments_query = "SELECT SUM(amount) as total FROM project_payments WHERE status = 'pending' AND payment_date BETWEEN '$start_date' AND '$end_date_query'";
        $pending_payments_result = $conn->query($pending_payments_query);
        $pending_payments = $pending_payments_result->fetch_assoc()['total'];
        $pending_payments = $pending_payments ? number_format($pending_payments, 2) : '0.00';
        
        // Average transaction amount in date range
        $avg_trans_query = "SELECT AVG(amount) as avg_amount FROM project_payments WHERE status = 'Completed' AND payment_date BETWEEN '$start_date' AND '$end_date_query'";
        $avg_trans_result = $conn->query($avg_trans_query);
        $avg_transaction = $avg_trans_result->fetch_assoc()['avg_amount'];
        $avg_transaction = $avg_transaction ? number_format($avg_transaction, 2) : '0.00';
        
        $response['payments'] = [
            'total' => number_format($total_transactions),
            'revenue' => $total_revenue,
            'pending' => $pending_payments,
            'avg_amount' => $avg_transaction
        ];
    }
    
    echo json_encode($response);
    exit();
}

// If we get here, it's an invalid request
echo json_encode(['success' => false, 'message' => 'Invalid request']);
?>