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

$data = array();
$QTD_arr = array();
$QTD1_arr = array();
$QTD1_label_arr = array();
$QTDOVERALL_arr = array();
$QTDOVERALL_label_arr = array();
$QTD_count = array(1,2,3,4);
$QTDD_label_arr = array();

$QTDD_arr = array();
$QTDD_count = array(1);
$yes_no_array = array(1);

$condX = $_REQUEST['condX'];
$conX = $_REQUEST['conX'];
$oemVal = $_POST['oemVal'];

$QTD_b = '';
$QTD_m = '';
$QTD_p = '';

$QTD5_b = '';
$QTD5_m = '';
$QTD5_p = '';

$QTDD_b = '';
$QTDD_t = '';

if($oemVal==0)
{
    $brand_C = $brand;
    $column_name = 'Q13b';
}
else
{
    $brand_C = $model[$oemVal-1];
    $column_name = 'Q13';
}

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

}

foreach($brand_C as $key=> $value)
{
    if($oemVal==0)
    {
        if($key==9)
        {
            foreach($QTD_count as $key2 => $value2)
            {
                $QTD_b = $QTD_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `T_QTD4_'.$value2.'` != "" then record end) as T_QTD4_'.$value2.'_b'.$key.',';
                $QTD_m = $QTD_m.' avg(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `T_QTD4_'.$value2.'` != "" AND `T_QTD4_'.$value2.'` IN (1,2,3,4,5,6,7,8,9,10) then T_QTD4_'.$value2.' end) as T_QTD4_'.$value2.'_m'.$key.',';
                $QTD_p = $QTD_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `T_QTD4_'.$value2.'` IN (9,10) then record end) as T_QTD4_'.$value2.'_p'.$key.',';
            }

            $QTD5_b = $QTD5_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QTD5` != "" then record end) as QTD5_b'.$key.',';
            $QTD5_m = $QTD5_m.' avg(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QTD5` != "" AND `QTD5` IN (1,2,3,4,5,6,7,8,9,10) then QTD5 end) as QTD5_m'.$key.',';
            $QTD5_p = $QTD5_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QTD5` IN (9,10) then record end) as QTD5_p'.$key.',';

            foreach($QTDD_count as $key3 => $value3)
            {
                $QTDD_b = $QTDD_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QTD'.$value3.'` != "" then record end) as QTD'.$value3.'_b'.$key.',';
                foreach($yes_no_array as $key4 => $value4)
                {
                    $QTDD_t = $QTDD_t.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QTD'.$value3.'` = '.$value4.' then record end) as QTD'.$value3.'_'.$value4.'_t'.$key.',';
                }
            }
        }
        else if($key==10)
        {
            foreach($QTD_count as $key2 => $value2)
            {
                $QTD_b = $QTD_b.' count(case when '.$column_name.' in (4,5) AND `T_QTD4_'.$value2.'` != "" then record end) as T_QTD4_'.$value2.'_b'.$key.',';
                $QTD_m = $QTD_m.' avg(case when '.$column_name.' in (4,5) AND `T_QTD4_'.$value2.'` != "" AND `T_QTD4_'.$value2.'` IN (1,2,3,4,5,6,7,8,9,10) then T_QTD4_'.$value2.' end) as T_QTD4_'.$value2.'_m'.$key.',';
                $QTD_p = $QTD_p.' count(case when '.$column_name.' in (4,5) AND `T_QTD4_'.$value2.'` IN (9,10) then record end) as T_QTD4_'.$value2.'_p'.$key.',';
            }

            $QTD5_b = $QTD5_b.' count(case when '.$column_name.' in (4,5) AND `QTD5` != "" then record end) as QTD5_b'.$key.',';
            $QTD5_m = $QTD5_m.' avg(case when '.$column_name.' in (4,5) AND `QTD5` != "" AND `QTD5` IN (1,2,3,4,5,6,7,8,9,10) then QTD5 end) as QTD5_m'.$key.',';
            $QTD5_p = $QTD5_p.' count(case when '.$column_name.' in (4,5) AND `QTD5` IN (9,10) then record end) as QTD5_p'.$key.',';

            foreach($QTDD_count as $key3 => $value3)
            {
                $QTDD_b = $QTDD_b.' count(case when '.$column_name.' in (4,5) AND `QTD'.$value3.'` != "" then record end) as QTD'.$value3.'_b'.$key.',';
                foreach($yes_no_array as $key4 => $value4)
                {
                    $QTDD_t = $QTDD_t.' count(case when '.$column_name.' in (4,5) AND `QTD'.$value3.'` = '.$value4.' then record end) as QTD'.$value3.'_'.$value4.'_t'.$key.',';
                }
            }
        }
        else
        {
            foreach($QTD_count as $key2 => $value2)
            {
                $QTD_b = $QTD_b.' count(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` != "" then record end) as T_QTD4_'.$value2.'_b'.$key.',';
                $QTD_m = $QTD_m.' avg(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` != "" AND `T_QTD4_'.$value2.'` IN (1,2,3,4,5,6,7,8,9,10) then T_QTD4_'.$value2.' end) as T_QTD4_'.$value2.'_m'.$key.',';
                $QTD_p = $QTD_p.' count(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` IN (9,10) then record end) as T_QTD4_'.$value2.'_p'.$key.',';
            }

            $QTD5_b = $QTD5_b.' count(case when '.$column_name.' in ('.$key.') AND `QTD5` != "" then record end) as QTD5_b'.$key.',';
            $QTD5_m = $QTD5_m.' avg(case when '.$column_name.' in ('.$key.') AND `QTD5` != "" AND `QTD5` IN (1,2,3,4,5,6,7,8,9,10) then QTD5 end) as QTD5_m'.$key.',';
            $QTD5_p = $QTD5_p.' count(case when '.$column_name.' in ('.$key.') AND `QTD5` IN (9,10) then record end) as QTD5_p'.$key.',';

            foreach($QTDD_count as $key3 => $value3)
            {
                $QTDD_b = $QTDD_b.' count(case when '.$column_name.' in ('.$key.') AND `QTD'.$value3.'` != "" then record end) as QTD'.$value3.'_b'.$key.',';
                foreach($yes_no_array as $key4 => $value4)
                {
                    $QTDD_t = $QTDD_t.' count(case when '.$column_name.' in ('.$key.') AND `QTD'.$value3.'` = '.$value4.' then record end) as QTD'.$value3.'_'.$value4.'_t'.$key.',';
                }
            }
        }
    }
    else
    {
        foreach($QTD_count as $key2 => $value2)
        {
            $QTD_b = $QTD_b.' count(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` != "" then record end) as T_QTD4_'.$value2.'_b'.$key.',';
            $QTD_m = $QTD_m.' avg(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` != "" AND `T_QTD4_'.$value2.'` IN (1,2,3,4,5,6,7,8,9,10) then T_QTD4_'.$value2.' end) as T_QTD4_'.$value2.'_m'.$key.',';
            $QTD_p = $QTD_p.' count(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` IN (9,10) then record end) as T_QTD4_'.$value2.'_p'.$key.',';
        }

        $QTD5_b = $QTD5_b.' count(case when '.$column_name.' in ('.$key.') AND `QTD5` != "" then record end) as QTD5_b'.$key.',';
        $QTD5_m = $QTD5_m.' avg(case when '.$column_name.' in ('.$key.') AND `QTD5` != "" AND `QTD5` IN (1,2,3,4,5,6,7,8,9,10) then QTD5 end) as QTD5_m'.$key.',';
        $QTD5_p = $QTD5_p.' count(case when '.$column_name.' in ('.$key.') AND `QTD5` IN (9,10) then record end) as QTD5_p'.$key.',';

        foreach($QTDD_count as $key3 => $value3)
        {
            $QTDD_b = $QTDD_b.' count(case when '.$column_name.' in ('.$key.') AND `QTD'.$value3.'` != "" then record end) as QTD'.$value3.'_b'.$key.',';
            foreach($yes_no_array as $key4 => $value4)
            {
                $QTDD_t = $QTDD_t.' count(case when '.$column_name.' in ('.$key.') AND `QTD'.$value3.'` = '.$value4.' then record end) as QTD'.$value3.'_'.$value4.'_t'.$key.',';
            }
        }
    }
    
}

$all = mysqli_query($conn,"SELECT $QTD_b $QTD_m $QTD_p $QTD5_b $QTD5_m $QTD5_p $QTDD_b $QTDD_t record FROM `cherry` WHERE $condX");
$ALL= mysqli_fetch_array($all);

foreach($QTD_count as $key2 => $value2)
{
    $QTD1_arrX = array();
    $QTD1_label_arrX = array();

    foreach($brand_C as $key => $value)
    {
        $QTD1_arr2 = array();
        $QTD_b = $ALL['T_QTD4_'.$value2.'_b'.$key.''];
        if($QTD_b==0)
        {
            $QTD_b=1;
        }
        if(is_numeric($ALL['T_QTD4_'.$value2.'_m'.$key.'']))
        {
            $QTD_m = round($ALL['T_QTD4_'.$value2.'_m'.$key.'']);
        }
        else
        {
            $QTD_m = 0;
        }
        $QTD_p2 = $ALL['T_QTD4_'.$value2.'_p'.$key.''];
        $QTD_p = round(($QTD_p2 / $QTD_b ) * 100);
        array_push($QTD1_arr2,$QTD_m,$QTD_p,$QTD_b);
        array_push($QTD1_arrX,$QTD1_arr2);
        array_push($QTD1_label_arrX,$value);
    }
    if(sizeof($QTD1_arrX)!=0)
    {
        array_push($QTD1_arr,$QTD1_arrX);
        array_push($QTD1_label_arr,$QTD1_label_arrX);
    }
}
foreach($brand_C as $key => $value)
{
    $QTDOVERALL_arrX = array();
    $QTD5_b = $ALL['QTD5_b'.''.$key.''];
    if($QTD5_b==0)
    {
        $QTD5_b=1;
    }
    if(is_numeric($ALL['QTD5_m'.''.$key.'']))
    {
        $QTD5_m = round($ALL['QTD5_m'.''.$key.'']);
    }
    else
    {
        $QTD5_m = 0;
    }
    $QTD5_p2 = $ALL['QTD5_p'.''.$key.''];
    $QTD5_p = round(($QTD5_p2 / $QTD5_b ) * 100);
    array_push($QTDOVERALL_arrX,$QTD5_m,$QTD5_p,$QTD5_b);
    array_push($QTDOVERALL_arr,$QTDOVERALL_arrX);
    array_push($QTDOVERALL_label_arr,$value);
}

foreach($QTDD_count as $key3 => $value3)
{
    $QTDD_arr2 = array();
    $QTDD_label_arrX = array();
    foreach($brand_C as $key => $value)
    {
        $QTDD_arr3 = array();
        $QTD_b = $ALL['QTD'.$value3.'_b'.''.$key.''];
        if($QTD_b==0)
        {
            $QTD_b=1;
        }
        foreach($yes_no_array as $key4 => $value4)
        {
            $QTD_t2 = $ALL['QTD'.$value3.'_'.$value4.'_t'.''.$key.''];
            $QTD_t = round(($QTD_t2/$QTD_b)*100);
            // if($QTD_t!=0)
            // {
                array_push($QTDD_arr3,$QTD_t);
                array_push($QTDD_label_arrX,$value);
            // }
        } 
        if(sizeof($QTDD_arr3)!=0)
        {
            array_push($QTDD_arr2,$QTDD_arr3);
        }
    }
    if(sizeof($QTDD_arr2)!=0)
    {
        array_push($QTDD_arr,$QTDD_arr2);
        array_push($QTDD_label_arr,$QTDD_label_arrX);
    }
}


array_push($QTD_arr,$QTD1_arr);
array_push($QTD_arr,$QTDOVERALL_arr);
$data['QTD'] = $QTD_arr;
$data['QTD1L'] = $QTD1_label_arr;
$data['QTDOVERALLL'] = $QTDOVERALL_label_arr;
$data['QTDD'] = $QTDD_arr;
$data['QTDDL'] = $QTDD_label_arr;


echo json_encode($data);

?>