<?php
header('Content-Type: application/json');

include_once("../../cfg/db.php");
include_once("../../api/controllers/utility.php");

$utility = new Utility();

 $granted_company_ids = $utility->isNotEmpty($_SESSION['user']['company_permissions_implode']) ?  $_SESSION['user']['company_permissions_implode']  : '-1' ;
 $start_date = isset($_REQUEST['startDate']) ? $db->escape($_REQUEST['startDate']) : '' ;
        $end_date = isset($_REQUEST['endDate']) ? $db->escape($_REQUEST['endDate']) : '' ;
        $company = isset($_REQUEST['company']) ? $db->escape($_REQUEST['company']) : '0' ;

    $company_filter = (intval($company) === 0) ? $granted_company_ids : $company ;  
        $date_filter = ($start_date !== '' && $end_date !== '') ? " AND ci.date_uploaded BETWEEN DATE_FORMAT('$start_date', '%Y-%m-%d 00:00:00') AND DATE_FORMAT('$end_date', '%Y-%m-%d 23:59:00') " : '' ;   
         
// $dms_count_query = "SELECT name,
// (
//     (SELECT count(1) FROM 
//     	(SELECT cd.dms_id FROM customer ci
//      	INNER JOIN customer_dms cd ON ci.id = cd.customer_record_id
//         INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id
// 		INNER JOIN source_company sc ON scd.company_id=sc.id
//      	WHERE ci.status = 1  AND scd.company_id IN (".$granted_company_ids.") GROUP BY ci.id,sc.id 
//     	) as t2 WHERE t2.dms_id = source_dms.id 
//      )  
// ) 
// as total FROM `source_dms` WHERE status = 1";

$dms_count_query = "SELECT sd.id,sd.name,
(
    (SELECT count(1) FROM 
    	(SELECT sc.dms_id FROM customer ci
     	INNER JOIN customer_dms cd ON ci.id = cd.customer_record_id
        INNER JOIN source_company_dealer scd ON cd.company_dealer_id = scd.id
		INNER JOIN source_company sc ON scd.company_id=sc.id
     	WHERE ci.status = 1  AND scd.company_id IN (".$company_filter.") $date_filter GROUP BY ci.id,sc.id 
    	) as t2 WHERE t2.dms_id = sd.id 
     )  
) 
as total FROM `source_dms` sd
INNER JOIN source_company sc 
ON sd.id = sc.dms_id
WHERE sd.status = 1
AND sc.id IN (".$company_filter.")
GROUP BY sd.id
"; 
 
$dms_count = $db -> return_result($dms_count_query); 

$data = array(); 
foreach($dms_count as $row){
	$data[] = $row; 
}

 

 
echo json_encode($data);
?>