<?php
$msg = '';
include("include/settings.php");
include("checksession.php");

$where = '';
$condition = '';

$skip = 0;
if (isset($_POST['page_skip'])) {
    $skip = $_POST['page_skip'] * 10 - 10;
} else {
    $_POST['page_skip'] = 0;
}

$pass = 0;

if (isset($_POST['ddlUserId']) && trim($_POST['ddlUserId']) != 0) {
    $pass = 1;
} else if (isset($_POST['ddlLeadType']) && trim($_POST['ddlLeadType']) != 0) {
    $pass = 1;
} else if (isset($_POST['txtFromDate']) && trim($_POST['txtFromDate']) != null) {
    $pass = 1;
} else if (isset($_POST['txtToDate']) && trim($_POST['txtToDate']) != null) {
    $pass = 1;
} else if (isset($_POST['txtSearch']) && trim($_POST['txtSearch']) != null) {
    $pass = 1;
} else if (isset($_POST['countryId']) && trim($_POST['countryId']) != null) {
    $pass = 1;
} else if (isset($_POST['stateId']) && trim($_POST['stateId']) != null) {
    $pass = 1;
} else if (isset($_POST['cityId']) && trim($_POST['cityId']) != null) {
    $pass = 1;
} else if (isset($_POST['searchGender']) && trim($_POST['searchGender']) != null) {
    $pass = 1;
} else if (isset($_POST['searchMemberStatus']) && trim($_POST['searchMemberStatus']) != null) {
    $pass = 1;
} else if (isset($_POST['searchCaste']) && trim($_POST['searchCaste']) != null) {
    $pass = 1;
} else if (isset($_POST['searchResidencyStatus']) && trim($_POST['searchResidencyStatus']) != null) {
    $pass = 1;
} else if (isset($_POST['searchCurrency']) && trim($_POST['searchCurrency']) != null) {
    $pass = 1;
} else if (isset($_POST['searchDisposition']) && trim($_POST['searchDisposition']) != null) {
    $pass = 1;
} else if (isset($_POST['searchMinAge']) && trim($_POST['searchMinAge']) != null) {
    $pass = 1;
} else if (isset($_POST['searchMaxAge']) && trim($_POST['searchMaxAge']) != null) {
    $pass = 1;
} else if (isset($_POST['searchMinFootHeight']) && trim($_POST['searchMinFootHeight']) != null) {
    $pass = 1;
} else if (isset($_POST['searchMinInchHeight']) && trim($_POST['searchMinInchHeight']) != null) {
    $pass = 1;
} else if (isset($_POST['searchMaxFootHeight']) && trim($_POST['searchMaxFootHeight']) != null) {
    $pass = 1;
} else if (isset($_POST['searchMaxInchHeight']) && trim($_POST['searchMaxInchHeight']) != null) {
    $pass = 1;
}else if (isset($_POST['religion']) && trim($_POST['religion']) != null) {
    $pass = 1;
} 
else {
    $pass = 0;
}


if ($pass == 1) {

    $where = 'where';

    if (trim($_POST['txtFromDate']) != null) {
        $fromdate = $_POST['txtFromDate'];
        $condition .= trim($condition) != null ? ' and ' : ' ';

        if (trim($_POST['txtFromDate']) != null) {
            $condition .= " lead_created >= '{$fromdate}' ";
        }
    }

    if (trim($_POST['txtToDate']) != null) {
        $todate = $_POST['txtToDate'];
        $condition .= trim($condition) != null ? ' and ' : ' ';

        if (trim($_POST['txtToDate']) != null) {
            $condition .= " lead_created <= '{$todate}' ";
        }
    }
    
     if (trim($_POST['ddlUserId']) != 0) {
        $condition .= trim($condition) != null ? ' and ' : ' ';
        $condition .= " rishta_leadmaster.lead_allocated_to = " . $_POST['ddlUserId'];
    }  

}




$memberdetailmaster = $mysqli->prepare("select * from cdi_usermaster where user_id=?");
$memberdetailmaster->bind_param("i", $_GET['userid']);
$memberdetailmaster->execute();
$memberdetailmaster = $memberdetailmaster->get_result();
$userrow = mysqli_fetch_object($memberdetailmaster);

$extensions = $userrow->extension;

$extension =$_POST['ddlUserId'];


$duration='07:00';

//$query = "select *,(select count(*) from rishta_new_api where lead_id = rishta_leadmaster.lead_id) as followcount  from rishta_leadmaster " . $where . " " . $condition . " order by lead_created desc limit 10 offset " . $skip;

//$query = ("select * from rishta_new_api where (destination = '$extension') OR (source = '$extension') OR (dst_channel LIKE '%$extension%') AND  ($condition) ");

$usermaster=$mysqli->prepare("select *,(select count(*) from rishta_leadmaster where source='website' AND  $condition) as website_count 
,(select count(*) from rishta_leadmaster where source='walk-in' AND  $condition) as walk
,(select count(*) from rishta_leadmaster where source='social_media' AND  $condition) as social
,(select count(*) from rishta_leadmaster where source='events' AND  $condition) as events
,(select count(*) from rishta_leadmaster where source='530 AM Punjabi Lehran Navi Sidhu Canada' AND  $condition) as Punjabi
,(select count(*) from rishta_leadmaster where source='530 AM Des Pardes Tajinder Sidhu Canada'AND  $condition) as sidhu
,(select count(*) from rishta_leadmaster where source='1610 AM Gaunda Punjab Joginder Bassi Canada' AND  $condition) as Gaunda
,(select count(*) from rishta_leadmaster where source='1320 AM Fulkari Raj &amp; Harjot Canada'AND  $condition) as Fulkari
,(select count(*) from rishta_leadmaster where source='1320 AM Parvasi Rajinder Saini Canada' AND  $condition) as Parvasi
,(select count(*) from rishta_leadmaster where source='810 AM Radio Dhamaal Bhupinder Toor Canada' AND  $condition) as Dhamaal
,(select count(*) from rishta_leadmaster where source='Channel Y TV Yudhvir Jaiswal Canada' AND  $condition) as Jaiswal
,(select count(*) from rishta_leadmaster where source='Red FM Vancouver Canada' AND  $condition) as Red
,(select count(*) from rishta_leadmaster where source='1550 AM Vancouver Canada' AND  $condition) as Vancouver
,(select count(*) from rishta_leadmaster where source='101.3 FM Asian Connections Ranbir Chuhan Canada' AND  $condition) as Asian
,(select count(*) from rishta_leadmaster where source='101.7 World FM Edmonton Canada' AND  $condition) as Edmonton
,(select count(*) from rishta_leadmaster where source='Desh Punjab Radio Edmonton Canada' AND  $condition) as Radio
,(select count(*) from rishta_leadmaster where source='1610 AM Radio Humsafar Montreal Canada' AND  $condition) as Humsafar
,(select count(*) from rishta_leadmaster where source='Radio Awaz Winnipeg Canada' AND  $condition) as Awaz
,(select count(*) from rishta_leadmaster where source='Red FM Calgary Canada' AND  $condition) as Calgary
,(select count(*) from rishta_leadmaster where source='Radio Punjab USA' AND  $condition) as Punjab
,(select count(*) from rishta_leadmaster where source='Panjab Radio UK' AND  $condition) as UK
,(select count(*) from rishta_leadmaster where source='Raaj FM UK' AND  $condition) as Raaj
,(select count(*) from rishta_leadmaster where source='Kohinoor Radio UK' AND  $condition) as Kohinoor
,(select count(*) from rishta_leadmaster where source='Haanji Radio Australia' AND  $condition) as Haanji
,(select count(*) from rishta_leadmaster where source='Lesiter Radio' AND  $condition) as Lesiter
,(select count(*) from rishta_leadmaster where source='Hayes Mela' AND  $condition) as Hayes
,(select count(*) from rishta_leadmaster where source='Zee London Mela' AND  $condition) as Mela
,(select count(*) from rishta_leadmaster where source='Tehalka TV' AND  $condition) as TV
,(select count(*) from rishta_leadmaster where source='Tehalka Radio' AND  $condition) as Tehalka
,(select count(*) from rishta_leadmaster where source='Jus Punjab' AND  $condition) as Jus
,(select count(*) from rishta_leadmaster where source='Prime Asia Tv' AND  $condition) as Prime
,(select count(*) from rishta_leadmaster where source='PTC Punjabi' AND  $condition) as PTC
,(select count(*) from rishta_leadmaster where source='Sign Board'AND  $condition) as Sign
,(select count(*) from rishta_leadmaster where source='94.3 MY FM Amritsar' AND  $condition) as Amritsar
,(select count(*) from rishta_leadmaster where source='94.3 MY FM Jalandhar' AND  $condition) as Jalandhar
,(select count(*) from rishta_leadmaster where source='94.3 MY FM Chandigarh' AND  $condition) as Chandigarh


from rishta_leadmaster where lead_type='rishta_lead'");
$usermaster->execute();
$userresult=$usermaster->get_result();
$userrow=mysqli_fetch_object($userresult);



$usermasters=$mysqli->prepare("select *,(select count(*) from rishta_leadmaster where source='website' AND disposition='Fully Paid' AND  $condition) as website_count 
,(select count(*) from rishta_leadmaster where source='walk-in' AND disposition='Fully Paid' AND  $condition) as walk
,(select count(*) from rishta_leadmaster where source='social_media' AND disposition='Fully Paid' AND  $condition) as social
,(select count(*) from rishta_leadmaster where source='events' AND disposition='Fully Paid' AND  $condition) as events
,(select count(*) from rishta_leadmaster where source='530 AM Punjabi Lehran Navi Sidhu Canada' AND disposition='Fully Paid' AND  $condition) as Punjabi
,(select count(*) from rishta_leadmaster where source='530 AM Des Pardes Tajinder Sidhu Canada' AND disposition='Fully Paid' AND  $condition) as sidhu
,(select count(*) from rishta_leadmaster where source='1610 AM Gaunda Punjab Joginder Bassi Canada' AND disposition='Fully Paid' AND  $condition) as Gaunda
,(select count(*) from rishta_leadmaster where source='1320 AM Fulkari Raj &amp; Harjot Canada' AND disposition='Fully Paid' AND  $condition) as Fulkari
,(select count(*) from rishta_leadmaster where source='1320 AM Parvasi Rajinder Saini Canada' AND disposition='Fully Paid' AND  $condition) as Parvasi
,(select count(*) from rishta_leadmaster where source='810 AM Radio Dhamaal Bhupinder Toor Canada' AND disposition='Fully Paid' AND  $condition) as Dhamaal
,(select count(*) from rishta_leadmaster where source='Channel Y TV Yudhvir Jaiswal Canada' AND disposition='Fully Paid' AND  $condition) as Jaiswal
,(select count(*) from rishta_leadmaster where source='Red FM Vancouver Canada' AND disposition='Fully Paid' AND  $condition) as Red
,(select count(*) from rishta_leadmaster where source='1550 AM Vancouver Canada' AND disposition='Fully Paid' AND  $condition) as Vancouver
,(select count(*) from rishta_leadmaster where source='101.3 FM Asian Connections Ranbir Chuhan Canada' AND disposition='Fully Paid' AND  $condition) as Asian
,(select count(*) from rishta_leadmaster where source='101.7 World FM Edmonton Canada' AND disposition='Fully Paid' AND  $condition) as Edmonton
,(select count(*) from rishta_leadmaster where source='Desh Punjab Radio Edmonton Canada' AND disposition='Fully Paid' AND  $condition) as Radio
,(select count(*) from rishta_leadmaster where source='1610 AM Radio Humsafar Montreal Canada' AND disposition='Fully Paid' AND  $condition) as Humsafar
,(select count(*) from rishta_leadmaster where source='Radio Awaz Winnipeg Canada' AND disposition='Fully Paid' AND  $condition) as Awaz
,(select count(*) from rishta_leadmaster where source='Red FM Calgary Canada' AND disposition='Fully Paid' AND  $condition) as Calgary
,(select count(*) from rishta_leadmaster where source='Radio Punjab USA' AND disposition='Fully Paid' AND  $condition) as Punjab
,(select count(*) from rishta_leadmaster where source='Panjab Radio UK' AND disposition='Fully Paid' AND  $condition) as UK
,(select count(*) from rishta_leadmaster where source='Raaj FM UK' AND disposition='Fully Paid' AND  $condition) as Raaj
,(select count(*) from rishta_leadmaster where source='Kohinoor Radio UK' AND disposition='Fully Paid' AND  $condition) as Kohinoor
,(select count(*) from rishta_leadmaster where source='Haanji Radio Australia' AND disposition='Fully Paid' AND  $condition) as Haanji
,(select count(*) from rishta_leadmaster where source='Lesiter Radio' AND disposition='Fully Paid' AND  $condition) as Lesiter
,(select count(*) from rishta_leadmaster where source='Hayes Mela' AND disposition='Fully Paid' AND  $condition) as Hayes
,(select count(*) from rishta_leadmaster where source='Zee London Mela' AND disposition='Fully Paid' AND  $condition) as Mela
,(select count(*) from rishta_leadmaster where source='Tehalka TV' AND disposition='Fully Paid' AND  $condition) as TV
,(select count(*) from rishta_leadmaster where source='Tehalka Radio' AND disposition='Fully Paid' AND  $condition) as Radio
,(select count(*) from rishta_leadmaster where source='Jus Punjab' AND disposition='Fully Paid' AND  $condition) as Jus
,(select count(*) from rishta_leadmaster where source='Prime Asia Tv' AND disposition='Fully Paid' AND  $condition) as Prime
,(select count(*) from rishta_leadmaster where source='PTC Punjabi' AND disposition='Fully Paid' AND  $condition) as PTC
,(select count(*) from rishta_leadmaster where source='Sign Board' AND disposition='Fully Paid' AND  $condition) as Sign
,(select count(*) from rishta_leadmaster where source='94.3 MY FM Amritsar' AND disposition='Fully Paid' AND  $condition) as Amritsar
,(select count(*) from rishta_leadmaster where source='94.3 MY FM Jalandhar' AND disposition='Fully Paid' AND  $condition) as Jalandhar
,(select count(*) from rishta_leadmaster where source='94.3 MY FM Chandigarh' AND disposition='Fully Paid' AND  $condition) as Chandigarh


from rishta_leadmaster where lead_type='rishta_lead'");
$usermasters->execute();
$userresults=$usermasters->get_result();
$userrows=mysqli_fetch_object($userresults);




$conn=new MySQLi("localhost","rishtadepot_new","Sailor@#321","rishtadepot_live");


$salesmaster = $mysqli->prepare("select * from cdi_usermaster where user_type='sales_exec' order by user_creationdate desc");
$salesmaster->execute();
$salesresult = $salesmaster->get_result();


// Fetch records
$sql = "SELECT * FROM source";
$result = mysqli_query($conn, $sql);


// Prepare the SQL query
$query = "
    SELECT s.name AS source_name,
           COUNT(*) AS total_count,
           COUNT(CASE WHEN l.disposition = 'Fully Paid' THEN 1 END) AND  {$condition} AS fully_paid_count
    FROM source s
    LEFT JOIN rishta_leadmaster l ON s.name = l.source AND l.lead_type = 'rishta_lead'
    GROUP BY s.name
";

// Execute the query
$result = mysqli_query($conn, $query);


?>

<div class="row mtop20">
    <div class="col-md-12">
         
  
    
 

</div>
<div class="row mtop10">
    <div class="col-md-12">
  

						  <table  class="table table-bordered table-striped">
						<thead>
							<tr>
								<th>Sr. No</th>
								<th>Lead Source</th>
								<th>Total</th>
								<th>Conversion Count</th>
							</tr>
						</thead>
								<tbody>
											        
													
			<?php										              
// Check if there are any results
if ($result) {
    // Fetch and display the results
    $i = 0;
    while ($row = mysqli_fetch_assoc($result)) {
        $i++;
        ?>
        <tr class="text-center">
            <td><?php echo $i; ?></td>
            <td><?php echo $row['source_name']; ?></td>
            <td><?php echo $row['total_count']; ?></td>
            <td><?php echo $row['fully_paid_count']; ?></td>
        </tr>
        <?php
    }
} else {
    // Handle query execution failure
    echo "Error executing the query: " . mysqli_error($conn);
}
?>
														
												</tbody>
				
					  </table>

         
         
    </div>
</div>