<?php
include '../config.php';

$data2 = array();
$sql_base;

$condX = $_POST['condX'];
$conX = $_POST['conX'];
$time = $_POST['timeX'];
$tablecondX = $_POST['tablecondX'];
$timeZ = "('".implode("','",$time)."')";
$columnS=array("record","Q9g","Q9a_codes_1","Q9a_codes_2","Q9a_codes_3","Q9a_codes_4","Q9a_codes_5","Q9a_codes_6",$tablecondX);
$mq=query_marge($time,$columnS);

//$join_tbl = "`auto_tbl` RIGHT JOIN `auto_tbl3` ON auto_tbl.record = record";


foreach($concern2 as $key => $value)
{
    $data = array();
    $valX = array();
    foreach($column2 as $key2 => $value2)
    {
        if($conX==1)
        {    
            $condX="";   
            $q=mysqli_query($conn,"SELECT COUNT(record) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND t.`Q9a_codes_1` != '' ");
          //echo "SELECT COUNT(record) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND t.`Q6a_codes_1` != '' ";
            $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 $condX t.`Q9a_codes_1` != ''");
            
            $baseX=mysqli_fetch_array($q);   
            $base = $baseX[0];
        }    
        $sql_base=$base;
        if($sql_base==0)
        {
            $data['name'] = $value;
            $valX = array(0);
            $data['value'] = array_sum($valX);
        }
        else
        {
            $sql = mysqli_query($conn,"SELECT COUNT($value2) FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND $condX $value2 = $key");
            //echo "SELECT $value2 FROM ($mq) AS t WHERE t.`Q9g` IN $timeZ AND $condX $value2 = $key ";
            $sql2 = mysqli_fetch_array($sql);
            $sql3 = ($sql2[0]/$sql_base)*100;
            array_push($valX,round($sql3,1));
            $data['name'] = $value;
            $data['value'] = array_sum($valX);
        }
        
        
    }
    array_push($data2,$data);
    
}

echo json_encode($data2);

?>


<?php
// include '../config.php';

// $data2 = array();
// $sql_base = 0;

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

// // Combine the column array for querying
// $columnS = ["record", "Q9g", "Q9a_codes_1", "Q9a_codes_2", "Q9a_codes_3", "Q9a_codes_4", "Q9a_codes_5", "Q9a_codes_6", $tablecondX];
// $mq = query_marge($time, $columnS);

// // Precompute the base count once
// $base_condition = ($conX == 1) ? "" : "$condX 1=1 AND";
// $base_query = "
//     SELECT 
//         COUNT(record) as base_count 
//     FROM 
//         ($mq) as t 
//     WHERE 
//         t.`Q9g` IN $timeZ AND $base_condition t.`Q9a_codes_1` != ''";
// $base_result = mysqli_query($conn, $base_query);
// $baseX = mysqli_fetch_array($base_result);
// $sql_base = $baseX[0];

// // If base count is 0, skip processing and return 0 values
// if ($sql_base == 0) {
//     foreach ($concern2 as $value) {
//         $data = [];
//         $data['name'] = $value;
//         $data['value'] = 0;
//         array_push($data2, $data);
//     }
// } else {
//     // Build a single query for all columns and concerns
//     foreach ($concern2 as $key => $value) {
//         $data = [];
//         $valX = [];
        
//         // Use a dynamic SQL query to calculate all percentages at once
//         $query = "
//             SELECT 
//                 SUM(CASE WHEN t.`$value` = $key THEN 1 ELSE 0 END) as count_value
//             FROM 
//                 ($mq) as t
//             WHERE 
//                 t.`Q9g` IN $timeZ AND $base_condition t.`Q9a_codes_1` != '' 
//         ";

//         $result = mysqli_query($conn, $query);
        
//         // Store the results for each column
//         if ($row = mysqli_fetch_assoc($result)) {
//             foreach ($column2 as $value2) {
//                 $sql3 = ($row['count_value'] / $sql_base) * 100;
//                 array_push($valX, round($sql3, 1));
//             }
            
//             $data['name'] = $value;
//             $data['value'] = array_sum($valX);
//         }

//         array_push($data2, $data);
//     }
// }

// echo json_encode($data2);
?>
