<?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","Q6a_codes_1","Q6a_codes_2","Q6a_codes_3","Q6a_codes_4","Q6a_codes_5","Q6a_codes_6","Q6a_codes_7",$tablecondX);
// $mq=query_marge($time,$columnS);

// foreach($concern as $key => $value)
// {
//     $data = array();
//     $valX = array();
//     foreach($column 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.`Q6a_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.`Q6a_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 = [];
$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", "Q6a_codes_1", "Q6a_codes_2", "Q6a_codes_3", "Q6a_codes_4", "Q6a_codes_5", "Q6a_codes_6", "Q6a_codes_7", $tablecondX];
$mq = query_marge($time, $columnS);

// Precompute the base count for all columns and concerns
$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.`Q6a_codes_1` != ''";
$base_result = mysqli_query($conn, $base_query);
$baseX = mysqli_fetch_array($base_result);
$sql_base = $baseX[0];

if ($sql_base == 0) {
    // If the base count is 0, pre-fill the data with 0 values
    foreach ($concern as $value) {
        $data = [];
        $data['name'] = $value;
        $data['value'] = 0;
        array_push($data2, $data);
    }
} else {
    // Build a single query to calculate all percentages at once for each concern and column
    $query = "
        SELECT 
            t.`Q6a_codes_1`,
            SUM(CASE WHEN t.`$value2` = $key THEN 1 ELSE 0 END) as count_value
        FROM 
            ($mq) as t
        WHERE 
            t.`Q9g` IN $timeZ AND $base_condition t.`Q6a_codes_1` != ''
        GROUP BY 
            t.`Q6a_codes_1`
    ";

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

    while ($row = mysqli_fetch_assoc($result)) {
        $data = [];
        $valX = [];
        $data['name'] = $row['Q6a_codes_1'];

        foreach ($column as $value2) {
            $sql3 = ($row['count_value'] / $sql_base) * 100;
            array_push($valX, round($sql3, 1));
        }

        $data['value'] = array_sum($valX);
        array_push($data2, $data);
    }
}

echo json_encode($data2);

?>
