<?php
$mysqli=new MySQLi("localhost","rishtadepot_new","Sailor@#321","rishtadepot_live");  //Live Rishta DB


// Database connection parameters
$dsn = 'mysql:host=localhost;dbname=rishtadepot_live';
$username = 'rishtadepot_new';
$password = 'Sailor@#321';

try {
    // Connect to the database
    $dbh = new PDO($dsn, $username, $password);

    // SQL query
    $sql = "
                   SELECT source.name,
                   COUNT(rishta_leadmaster.disposition) AS total_count,
                   SUM(CASE WHEN rishta_leadmaster.lead_status = 'Fully Paid' THEN 1 ELSE 0 END) AS fully_paid_count
            FROM source
            LEFT JOIN rishta_leadmaster ON source.name = rishta_leadmaster.disposition
            GROUP BY source.name;

    ";

    // Prepare and execute the query
    $stmt = $dbh->prepare($sql);
    $stmt->execute();

    // Fetch all rows
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    // Loop through the rows and print the data
    foreach ($rows as $row) {
        echo "Name: " . $row['name'] . "\n";
        echo "Total Count: " . $row['total_count'] . "\n";
        echo "Fully Paid Count: " . $row['fully_paid_count'] . "\n\n";
    }

} catch (PDOException $e) {
    // Handle database connection errors
    echo "Error: " . $e->getMessage();
}
