<?php
// include '../config.php';
// $ssi_arr = array();
// $dataX = array();
// $dealers_arr = array();
// $condX = $_REQUEST['condX'];
// $conX = $_REQUEST['conX'];
// $time = $_POST['timeX'];
// $timeZ = "('".implode("','",$time)."')";
// $tablecondX = $_POST['tablecondX'];
// $columnS=array("record","Q9g","Dealer_name",$tablecondX);
// $mq=query_marge($time,$columnS);

// $dealers = mysqli_query($conn,"SELECT DISTINCT `Dealer_name` FROM ($mq) as t WHERE $condX 1=1");
// if(mysqli_num_rows($dealers) > 0){
//     while($row = mysqli_fetch_array($dealers)){
//         $var1 = $row['Dealer_name']; 
//         array_push($dealers_arr,$var1);
//     }
// }

// $nameX = array();
// $codeX = array();
// foreach($dealers_arr as $key => $value)
// {
//     $pieces = explode("(", $value);
//     array_push($nameX,$pieces[0]);
//     array_push($codeX,trim($pieces[1],")"));
// }

// foreach($dealers_arr as $key => $value)
// {
//     $columnS=array("record","Q9g","Dealer_name",$tablecondX);
//     $mq=query_marge($time,$columnS);
//     if($conX==1)
//     {    
//         $condX="";   
//     	$q=mysqli_query($conn,"SELECT COUNT(`record`) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND t.`Dealer_name` = '$value'");
//     	$baseX=mysqli_fetch_array($q);   
//     	$base = $baseX[0];
//     }

//     if($conX==2)
//     {
//       $q=mysqli_query($conn,"SELECT COUNT(`record`) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND t.`Dealer_name` = '$value' AND $condX 1=1");
//       $baseX=mysqli_fetch_array($q);
//       $base = $baseX[0];
//     }
//     $sql_base=$base;
//     if($sql_base==0)
// 	{
// 		$data['ssi'] = 0;
// 		$data['Q4'] = 0;
// 		$data['Q4_1'] = 0;
// 		$data['Q4_2'] = 0;
// 		$data['Q5_1'] = 0;
// 		$data['Q5_2'] = 0;
// 		$data['Q5_3'] = 0;
// 		$data['Q5_NPS'] = 0;
// 		$data['Q8'] = 0;
// 		$data['Q8_1'] = 0;
// 		$data['Q8_2'] = 0;
// 		$data['Q9_1'] = 0;
// 		$data['Q9_2'] = 0;
// 		$data['Q9_3'] = 0;
// 		$data['Q9_NPS'] = 0;
// 	}
	
// 	else
// 	{
// 	    /*SSI script start*/
//             $columnS=array("record","Q9g","Dealer_name","ssi_v",$tablecondX);
//             $mq=query_marge($time,$columnS);
//     		$ssi=mysqli_query($conn,"SELECT avg(`ssi_v`) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND t.`Dealer_name` = '$value' AND $condX 1=1");
//     		//echo $ssi;
//     		if(mysqli_num_rows($ssi) > 0)
//     		{
//                 while($row = mysqli_fetch_array($ssi))
//                 {
//                     //print_r($row);
//                     $ssi_arr[$key]['nameX'] = $nameX[$key];
//                     $ssi_arr[$key]['codeX'] = $codeX[$key];
//                     $ssi_arr[$key]['score'] = round($row[0]);
                    
//                 }
//             }
//         /*SSI script end*/
//         /*Q9 script start*/
//             $columnS=array("record","Q9g","Dealer_name","Q9",$tablecondX);
//             $mq=query_marge($time,$columnS);
//             $q9_1=mysqli_query($conn,"SELECT COUNT(`record`) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND $condX `Dealer_Name` = '$value' AND t.Q9 in (1,2,3,4,5,6,7) ");
//             //echo "SELECT COUNT(`record`) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND $condX `Dealer_Name` = '$value' AND Q9 in (1,2,3,4,5,6,7)";
//             $Q9_1=mysqli_fetch_array($q9_1); 
//             $dataD = ($Q9_1[0] / $sql_base ) * 100;
    
//             $q9_3=mysqli_query($conn,"SELECT COUNT(`record`) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND $condX `Dealer_Name` = '$value' AND t.Q9 in (10,11) ");
//             $Q9_3=mysqli_fetch_array($q9_3); 
//             $dataPr = ($Q9_3[0] / $sql_base ) * 100;
    
//             $Q9_NPS = $dataPr - $dataD;
//             $Q9_NPSV = round($Q9_NPS,1);
//             $ssi_arr[$key]['nps'] = $Q9_NPSV;
//         /*Q9 script end*/
        
//         /*Q5 script start*/
//             $columnS=array("record","Q9g","Dealer_name","Q5",$tablecondX);
//             $mq=query_marge($time,$columnS);
//             $q5_1=mysqli_query($conn,"SELECT COUNT(`record`) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND $condX t.`Dealer_Name` = '$value' AND t.Q5 in (0,1,2,3,4,5,6,7) ");
//             $Q5_1=mysqli_fetch_array($q5_1); 
//             $dataD2 = ($Q5_1[0] / $sql_base ) * 100;

//             $q5_3=mysqli_query($conn,"SELECT COUNT(`record`) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND $condX t.`Dealer_Name` = '$value' AND t.Q5 in (10,11) ");
//             $Q5_3=mysqli_fetch_array($q5_3); 
//             $dataPr2 = ($Q5_3[0] / $sql_base ) * 100;

//             $Q5_NPS = $dataPr2 - $dataD2;
//             $Q5_NPSV = round($Q5_NPS,1);
//             $ssi_arr[$key]['nps2'] = $Q5_NPSV;
//         /*Q5 script end*/
// 	}
// }

// echo json_encode($ssi_arr);


include '../config.php';

$ssi_arr = [];
$dataX = [];
$dealers_arr = [];

$condX = $_REQUEST['condX'];
$conX = $_REQUEST['conX'];
$time = $_POST['timeX'];
$timeZ = "('".implode("','", $time)."')";
$tablecondX = $_POST['tablecondX'];

if($conX==1){    
	
   $condX="";   
   
}
if($conX==2){
  
}

// Columns to be selected in the query_marge
$columnS = ["record", "Q9g", "Dealer_name", $tablecondX, "ssi_v", "Q9", "Q5"];

// Perform the query merge once to avoid repeated calls in the loop
$mq = query_marge($time, $columnS);

// Fetch distinct dealers
$dealers_result = mysqli_query($conn, "SELECT DISTINCT `Dealer_name` FROM ($mq) as t WHERE $condX 1=1");
while ($row = mysqli_fetch_assoc($dealers_result)) {
    $dealers_arr[] = $row['Dealer_name'];
}

$nameX = [];
$codeX = [];
foreach ($dealers_arr as $dealer) {
    $pieces = explode("(", $dealer);
    $nameX[] = $pieces[0];
    $codeX[] = trim($pieces[1], ")");
}

// Precompute conditions based on conX
$base_condition = ($conX == 1) ? "" : "$condX 1=1 AND";

// Build a single query that fetches all necessary data in one go
$dealer_list = "'" . implode("','", $dealers_arr) . "'";
$query = "
    SELECT 
        `Dealer_name`,
        COUNT(`record`) as base,
        AVG(`ssi_v`) as avg_ssi_v,
        SUM(CASE WHEN `Q9` IN (1,2,3,4,5,6,7) THEN 1 ELSE 0 END) as Q9_detractors,
        SUM(CASE WHEN `Q9` IN (10,11) THEN 1 ELSE 0 END) as Q9_promoters,
        SUM(CASE WHEN `Q5` IN (0,1,2,3,4,5,6,7) THEN 1 ELSE 0 END) as Q5_detractors,
        SUM(CASE WHEN `Q5` IN (10,11) THEN 1 ELSE 0 END) as Q5_promoters
    FROM 
        ($mq) AS t
    WHERE 
        t.`Q9g` IN $timeZ AND $base_condition `Dealer_name` IN ($dealer_list)
    GROUP BY 
        `Dealer_name`";

$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_assoc($result)) {
    $dealer = $row['Dealer_name'];
    $key = array_search($dealer, $dealers_arr);

    $base = $row['base'];
    if ($base == 0) {
        $ssi_arr[$key] = [
            'nameX' => $nameX[$key],
            'codeX' => $codeX[$key],
            'score' => 0,
            'nps' => 0,
            'nps2' => 0
        ];
    } else {
        $ssi_arr[$key]['nameX'] = $nameX[$key];
        $ssi_arr[$key]['codeX'] = $codeX[$key];
        // SSI score
        $ssi_arr[$key]['score'] = round($row['avg_ssi_v']);

        // Q9 NPS score calculation
        $Q9_detractors = ($row['Q9_detractors'] / $base) * 100;
        $Q9_promoters = ($row['Q9_promoters'] / $base) * 100;
        $Q9_NPS = $Q9_promoters - $Q9_detractors;
        $ssi_arr[$key]['nps'] = round($Q9_NPS, 1);

        // Q5 NPS score calculation
        $Q5_detractors = ($row['Q5_detractors'] / $base) * 100;
        $Q5_promoters = ($row['Q5_promoters'] / $base) * 100;
        $Q5_NPS = $Q5_promoters - $Q5_detractors;
        $ssi_arr[$key]['nps2'] = round($Q5_NPS, 1);
    }
}

echo json_encode($ssi_arr);

?>
