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

$data = array();
$QFI_arr = array();
$QFI_label_arr = array();
$QFI1_arr = array();
$QFIOVERALL_arr = array();
$QFIOVERALL_label_arr = array();
$QFIX_arr = array();
$QFIX_label_arr = array();
$QFIX3_arr = array();
$QFIX3_label_arr = array();

$QFI_count = array(1,2,3,4);
$QFI_b = '';
$QFI_m = '';
$QFI_p = '';
$QFI2_b = '';
$QFI2_m = '';
$QFI2_p = '';
$QFIX_b = '';
$QFIX_t = '';
$QFIX3_b = '';
$QFIX3_t = '';

$QFIX_count = array(1,2);
$yes_no_array = array(1);
$QFIX3_count = array(1,2,3,4);


$condX = $_REQUEST['trendcondX'];
$conX = $_REQUEST['conX'];
$oemVal = $_REQUEST['oemVal'];
$timeVal = $_REQUEST['timeVal'];

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($timeVal as $key7 => $value7)
{
    foreach($brand_C as $key=> $value)
    {
        if($oemVal==0)
        {
            if($key==9)
            {
                foreach($QFI_count as $key2 => $value2)
                {
                    $QFI_b = $QFI_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `T_QFI1_'.$value2.'` != "" AND `Quarter` = '.$value7.' then record end) as T_QFI1_'.$value2.'_b'.$key.'_'.$value7.',';
                    $QFI_m = $QFI_m.' avg(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `T_QFI1_'.$value2.'` != "" AND T_QFI1_'.$value2.' IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then T_QFI1_'.$value2.' end) as T_QFI1_'.$value2.'_m'.$key.'_'.$value7.',';
                    $QFI_p = $QFI_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `T_QFI1_'.$value2.'` IN (9,10) AND `Quarter` = '.$value7.' then record end) as T_QFI1_'.$value2.'_p'.$key.'_'.$value7.',';
                }
    
                $QFI2_b = $QFI2_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QFI2` != "" AND `Quarter` = '.$value7.' then record end) as QFI2_b'.$key.'_'.$value7.',';
                $QFI2_m = $QFI2_m.' avg(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QFI2` != "" AND `QFI2` IN (1,2,3,4,5,6,7,8,9,10)  AND `Quarter` = '.$value7.' then QFI2 end) as QFI2_m'.$key.'_'.$value7.',';
                $QFI2_p = $QFI2_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QFI2` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QFI2_p'.$key.'_'.$value7.',';
    
                foreach($QFIX_count as $key3 => $value3)
                {
                    $QFIX_b = $QFIX_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QFIX'.$value3.'` != "" AND `Quarter` = '.$value7.' then record end) as QFIX'.$value3.'_b'.$key.'_'.$value7.',';
                    foreach($yes_no_array as $key4 => $value4)
                    {
                        $QFIX_t = $QFIX_t.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QFIX'.$value3.'` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QFIX'.$value3.'_t'.$key.'_'.$value7.',';
                    }
                }
    
                $QFIX3_b = $QFIX3_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QFIX3` != "" AND `Quarter` = '.$value7.' then record end) as QFIX3_b'.$key.'_'.$value7.',';
                foreach($QFIX3_count as $key4 => $value4)
                {
                    $QFIX3_t = $QFIX3_t.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QFIX3` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QFIX3_'.$value4.'_t'.$key.'_'.$value7.',';
                }
            }
            else if($key==10)
            {
                foreach($QFI_count as $key2 => $value2)
                {
                    $QFI_b = $QFI_b.' count(case when '.$column_name.' in (4,5) AND `T_QFI1_'.$value2.'` != "" AND `Quarter` = '.$value7.' then record end) as T_QFI1_'.$value2.'_b'.$key.'_'.$value7.',';
                    $QFI_m = $QFI_m.' avg(case when '.$column_name.' in (4,5) AND `T_QFI1_'.$value2.'` != "" AND T_QFI1_'.$value2.' IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then T_QFI1_'.$value2.' end) as T_QFI1_'.$value2.'_m'.$key.'_'.$value7.',';
                    $QFI_p = $QFI_p.' count(case when '.$column_name.' in (4,5) AND `T_QFI1_'.$value2.'` IN (9,10) AND `Quarter` = '.$value7.' then record end) as T_QFI1_'.$value2.'_p'.$key.'_'.$value7.',';
                }
            
                $QFI2_b = $QFI2_b.' count(case when '.$column_name.' in (4,5) AND `QFI2` != "" AND `Quarter` = '.$value7.' then record end) as QFI2_b'.$key.'_'.$value7.',';
                $QFI2_m = $QFI2_m.' avg(case when '.$column_name.' in (4,5) AND `QFI2` != "" AND `QFI2` IN (1,2,3,4,5,6,7,8,9,10)  AND `Quarter` = '.$value7.' then QFI2 end) as QFI2_m'.$key.'_'.$value7.',';
                $QFI2_p = $QFI2_p.' count(case when '.$column_name.' in (4,5) AND `QFI2` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QFI2_p'.$key.'_'.$value7.',';
            
                foreach($QFIX_count as $key3 => $value3)
                {
                    $QFIX_b = $QFIX_b.' count(case when '.$column_name.' in (4,5) AND `QFIX'.$value3.'` != "" AND `Quarter` = '.$value7.' then record end) as QFIX'.$value3.'_b'.$key.'_'.$value7.',';
                    foreach($yes_no_array as $key4 => $value4)
                    {
                        $QFIX_t = $QFIX_t.' count(case when '.$column_name.' in (4,5) AND `QFIX'.$value3.'` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QFIX'.$value3.'_t'.$key.'_'.$value7.',';
                    }
                }
            
                $QFIX3_b = $QFIX3_b.' count(case when '.$column_name.' in (4,5) AND `QFIX3` != "" AND `Quarter` = '.$value7.' then record end) as QFIX3_b'.$key.'_'.$value7.',';
                foreach($QFIX3_count as $key4 => $value4)
                {
                    $QFIX3_t = $QFIX3_t.' count(case when '.$column_name.' in (4,5) AND `QFIX3` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QFIX3_'.$value4.'_t'.$key.'_'.$value7.',';
                }
            }
            else
            {
                foreach($QFI_count as $key2 => $value2)
                {
                    $QFI_b = $QFI_b.' count(case when '.$column_name.' in ('.$key.') AND `T_QFI1_'.$value2.'` != "" AND `Quarter` = '.$value7.' then record end) as T_QFI1_'.$value2.'_b'.$key.'_'.$value7.',';
                    $QFI_m = $QFI_m.' avg(case when '.$column_name.' in ('.$key.') AND `T_QFI1_'.$value2.'` != "" AND T_QFI1_'.$value2.' IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then T_QFI1_'.$value2.' end) as T_QFI1_'.$value2.'_m'.$key.'_'.$value7.',';
                    $QFI_p = $QFI_p.' count(case when '.$column_name.' in ('.$key.') AND `T_QFI1_'.$value2.'` IN (9,10) AND `Quarter` = '.$value7.' then record end) as T_QFI1_'.$value2.'_p'.$key.'_'.$value7.',';
                }
    
                $QFI2_b = $QFI2_b.' count(case when '.$column_name.' in ('.$key.') AND `QFI2` != "" AND `Quarter` = '.$value7.' then record end) as QFI2_b'.$key.'_'.$value7.',';
                $QFI2_m = $QFI2_m.' avg(case when '.$column_name.' in ('.$key.') AND `QFI2` != "" AND `QFI2` IN (1,2,3,4,5,6,7,8,9,10)  AND `Quarter` = '.$value7.' then QFI2 end) as QFI2_m'.$key.'_'.$value7.',';
                $QFI2_p = $QFI2_p.' count(case when '.$column_name.' in ('.$key.') AND `QFI2` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QFI2_p'.$key.'_'.$value7.',';
    
                foreach($QFIX_count as $key3 => $value3)
                {
                    $QFIX_b = $QFIX_b.' count(case when '.$column_name.' in ('.$key.') AND `QFIX'.$value3.'` != "" AND `Quarter` = '.$value7.' then record end) as QFIX'.$value3.'_b'.$key.'_'.$value7.',';
                    foreach($yes_no_array as $key4 => $value4)
                    {
                        $QFIX_t = $QFIX_t.' count(case when '.$column_name.' in ('.$key.') AND `QFIX'.$value3.'` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QFIX'.$value3.'_t'.$key.'_'.$value7.',';
                    }
                }
    
                $QFIX3_b = $QFIX3_b.' count(case when '.$column_name.' in ('.$key.') AND `QFIX3` != "" AND `Quarter` = '.$value7.' then record end) as QFIX3_b'.$key.'_'.$value7.',';
                foreach($QFIX3_count as $key4 => $value4)
                {
                    $QFIX3_t = $QFIX3_t.' count(case when '.$column_name.' in ('.$key.') AND `QFIX3` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QFIX3_'.$value4.'_t'.$key.'_'.$value7.',';
                }
            }
        }
        else
        {
            foreach($QFI_count as $key2 => $value2)
            {
                $QFI_b = $QFI_b.' count(case when '.$column_name.' in ('.$key.') AND `T_QFI1_'.$value2.'` != "" AND `Quarter` = '.$value7.' then record end) as T_QFI1_'.$value2.'_b'.$key.'_'.$value7.',';
                $QFI_m = $QFI_m.' avg(case when '.$column_name.' in ('.$key.') AND `T_QFI1_'.$value2.'` != "" AND T_QFI1_'.$value2.' IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then T_QFI1_'.$value2.' end) as T_QFI1_'.$value2.'_m'.$key.'_'.$value7.',';
                $QFI_p = $QFI_p.' count(case when '.$column_name.' in ('.$key.') AND `T_QFI1_'.$value2.'` IN (9,10) AND `Quarter` = '.$value7.' then record end) as T_QFI1_'.$value2.'_p'.$key.'_'.$value7.',';
            }

            $QFI2_b = $QFI2_b.' count(case when '.$column_name.' in ('.$key.') AND `QFI2` != "" AND `Quarter` = '.$value7.' then record end) as QFI2_b'.$key.'_'.$value7.',';
            $QFI2_m = $QFI2_m.' avg(case when '.$column_name.' in ('.$key.') AND `QFI2` != "" AND `QFI2` IN (1,2,3,4,5,6,7,8,9,10)  AND `Quarter` = '.$value7.' then QFI2 end) as QFI2_m'.$key.'_'.$value7.',';
            $QFI2_p = $QFI2_p.' count(case when '.$column_name.' in ('.$key.') AND `QFI2` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QFI2_p'.$key.'_'.$value7.',';

            foreach($QFIX_count as $key3 => $value3)
            {
                $QFIX_b = $QFIX_b.' count(case when '.$column_name.' in ('.$key.') AND `QFIX'.$value3.'` != "" AND `Quarter` = '.$value7.' then record end) as QFIX'.$value3.'_b'.$key.'_'.$value7.',';
                foreach($yes_no_array as $key4 => $value4)
                {
                    $QFIX_t = $QFIX_t.' count(case when '.$column_name.' in ('.$key.') AND `QFIX'.$value3.'` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QFIX'.$value3.'_t'.$key.'_'.$value7.',';
                }
            }

            $QFIX3_b = $QFIX3_b.' count(case when '.$column_name.' in ('.$key.') AND `QFIX3` != "" AND `Quarter` = '.$value7.' then record end) as QFIX3_b'.$key.'_'.$value7.',';
            foreach($QFIX3_count as $key4 => $value4)
            {
                $QFIX3_t = $QFIX3_t.' count(case when '.$column_name.' in ('.$key.') AND `QFIX3` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QFIX3_'.$value4.'_t'.$key.'_'.$value7.',';
            }
        }
    }
}

$all = mysqli_query($conn,"SELECT $QFI_b $QFI_m $QFI_p $QFI2_b $QFI2_m $QFI2_p $QFIX_b $QFIX_t $QFIX3_b $QFIX3_t record FROM `cherry` WHERE $condX");
$ALL= mysqli_fetch_array($all);

foreach($timeVal as $key7 => $value7)
{
    $QFI1_arrXT = array();
    $QFI_label_arrXT = array();
    foreach($QFI_count as $key2 => $value2)
    {
        $QFI1_arrX = array();
        $QFI_label_arrX = array();
        foreach($brand_C as $key => $value)
        {
            $QFL1_arr2 = array();
            $QFI_b = $ALL['T_QFI1_'.$value2.'_b'.''.$key.'_'.$value7.''];
            if($QFI_b==0)
            {
                $QFI_b=1;
            }

            if(is_numeric($ALL['T_QFI1_'.$value2.'_m'.''.$key.'_'.$value7.'']))
            {
                $QFI_m = round($ALL['T_QFI1_'.$value2.'_m'.''.$key.'_'.$value7.'']);
            }
            else
            {
                $QFI_m = 0;
            }
            $QFI_p2 = $ALL['T_QFI1_'.$value2.'_p'.''.$key.'_'.$value7.''];
            $QFI_p = round(($QFI_p2 / $QFI_b ) * 100);
            array_push($QFL1_arr2,$QFI_m,$QFI_p,$QFI_b);
            array_push($QFI1_arrX,$QFL1_arr2);
            array_push($QFI_label_arrX,$value);
        }
        if(sizeof($QFI1_arrX)!=0)
        {
            array_push($QFI_label_arrXT,$QFI_label_arrX);
            array_push($QFI1_arrXT,$QFI1_arrX);
        }
    }
    array_push($QFI_label_arr,$QFI_label_arrXT);
    array_push($QFI1_arr,$QFI1_arrXT);
}


foreach($timeVal as $key7 => $value7)
{
    $QFIOVERALL_arrT = array();
    foreach($brand_C as $key => $value)
    {
        $QFLOVERALL_arrX = array();
        $QFI2_b = $ALL['QFI2_b'.''.$key.'_'.$value7.''];
        if($QFI2_b==0)
        {
            $QFI2_b=1;
        }

        if(is_numeric($ALL['QFI2_m'.''.$key.'_'.$value7.'']))
        {
            $QFI2_m = round($ALL['QFI2_m'.''.$key.'_'.$value7.'']);
        }
        else
        {
            $QFI2_m = 0;
        }
        $QFI2_p2 = $ALL['QFI2_p'.''.$key.'_'.$value7.''];
        $QFI2_p = round(($QFI2_p2 / $QFI2_b ) * 100);
        array_push($QFLOVERALL_arrX,$QFI2_m,$QFI2_p,$QFI2_b);
        array_push($QFIOVERALL_arrT,$QFLOVERALL_arrX);
        array_push($QFIOVERALL_label_arr,$value);
    }
    array_push($QFIOVERALL_arr,$QFIOVERALL_arrT);
}

foreach($timeVal as $key7 => $value7)
{   
    $QFIX_arr2T = array();
    $QFIX_label_arrXT = array();
    foreach($QFIX_count as $key3 => $value3)
    {
        $QFIX_arr2 = array();
        $QFIX_label_arrX = array();
        foreach($brand_C as $key => $value)
        {
            $QFIX_arr3 = array();
            $QFIX_b = $ALL['QFIX'.$value3.'_b'.''.$key.'_'.$value7.''];
            if($QFIX_b==0)
            {
                $QFIX_b=1;
            }

            foreach($yes_no_array as $key4 => $value4)
            {
                $QFIX_t2 = $ALL['QFIX'.$value3.'_t'.''.$key.'_'.$value7.''];
                $QFIX_t = round(($QFIX_t2/$QFIX_b)*100);
                array_push($QFIX_arr3,$QFIX_t);
                array_push($QFIX_label_arrX,$value);
            } 

            if(sizeof($QFIX_arr3)!=0)
            {
                array_push($QFIX_arr2,$QFIX_arr3);
            }
        }
        if(sizeof($QFIX_arr2)!=0)
        {
            array_push($QFIX_arr2T,$QFIX_arr2);
            array_push($QFIX_label_arrXT,$QFIX_label_arrX);
        }
    }
    array_push($QFIX_arr,$QFIX_arr2T);
    array_push($QFIX_label_arr,$QFIX_label_arrXT);
}

foreach($timeVal as $key7 => $value7)
{
    $QFIX3_arr2T = array();
    $QFIX3_label_arrXT = array();
    foreach($QFIX3_count as $key4 => $value4)
    {
        $QFIX3_arr2 = array();
        $QFIX3_label_arrX = array();
        foreach($brand_C as $key => $value)
        {
            $QFIX3_b = $ALL['QFIX3_b'.$key.'_'.$value7.''];
            if($QFIX3_b==0)
            {
                $QFIX3_b=1;
            }

            $QFIX3_t2 = $ALL['QFIX3_'.$value4.'_t'.$key.'_'.$value7.''];
            $QFIX3_t = round(($QFIX3_t2 / $QFIX3_b) *100);
            array_push($QFIX3_arr2,$QFIX3_t);
            array_push($QFIX3_label_arrX,$value);
        }
        if(sizeof($QFIX3_arr2)!=0)
        {
            array_push($QFIX3_arr2T,$QFIX3_arr2);
            array_push($QFIX3_label_arrXT,$QFIX3_label_arrX);
        }
    }
    array_push($QFIX3_arr,$QFIX3_arr2T);
    array_push($QFIX3_label_arr,$QFIX3_label_arrXT);
}
array_push($QFI_arr,$QFI1_arr);
array_push($QFI_arr,$QFIOVERALL_arr);

$data['QFI'] = $QFI_arr;
$data['QFIL'] = $QFI_label_arr;
$data['QFIOVERALLL'] = $QFIOVERALL_label_arr;
$data['QFIX'] = $QFIX_arr;
$data['QFIXL'] = $QFIX_label_arr;
$data['QFIX3'] = $QFIX3_arr;
$data['QFIX3L'] = $QFIX3_label_arr;

echo json_encode($data);

?>