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

$data = array();
$QPS2_arr = array();
$QPS3_arr = array();
$QPS4_arr = array();
$QPS2_label_arr = array();
$QPS3_label_arr = array();
$QPS4_label_arr = array();
$QPS2_b = '';
$QPS2_m = '';
$QPS2_p = '';
$QPS3_b = '';
$QPS3_m = '';
$QPS3_p = '';
$QPS4_b = '';
$QPS4_m = '';
$QPS4_p = '';

$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)
            {
                $QPS2_b = $QPS2_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS2` != "" AND `Quarter` = '.$value7.' then record end) as QPS2_b'.$key.'_'.$value7.',';
                $QPS2_m = $QPS2_m.' avg(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS2` != "" AND QPS2 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS2 end) as QPS2_m'.$key.'_'.$value7.',';
                $QPS2_p = $QPS2_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS2` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS2_p'.$key.'_'.$value7.',';
    
                $QPS3_b = $QPS3_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS3` != "" AND `Quarter` = '.$value7.' then record end) as QPS3_b'.$key.'_'.$value7.',';
                $QPS3_m = $QPS3_m.' avg(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS3` != "" AND QPS3 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS3 end) as QPS3_m'.$key.'_'.$value7.',';
                $QPS3_p = $QPS3_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS3` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS3_p'.$key.'_'.$value7.',';
    
                $QPS4_b = $QPS4_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS4` != "" AND `Quarter` = '.$value7.' then record end) as QPS4_b'.$key.'_'.$value7.',';
                $QPS4_m = $QPS4_m.' avg(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS4` != "" AND QPS4 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS4 end) as QPS4_m'.$key.'_'.$value7.',';
                $QPS4_p = $QPS4_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QPS4` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS4_p'.$key.'_'.$value7.',';
            }
            else if($key==10)
            {
                $QPS2_b = $QPS2_b.' count(case when '.$column_name.' in (4,5) AND `QPS2` != "" AND `Quarter` = '.$value7.' then record end) as QPS2_b'.$key.'_'.$value7.',';
                $QPS2_m = $QPS2_m.' avg(case when '.$column_name.' in (4,5) AND `QPS2` != "" AND QPS2 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS2 end) as QPS2_m'.$key.'_'.$value7.',';
                $QPS2_p = $QPS2_p.' count(case when '.$column_name.' in (4,5) AND `QPS2` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS2_p'.$key.'_'.$value7.',';
    
                $QPS3_b = $QPS3_b.' count(case when '.$column_name.' in (4,5) AND `QPS3` != "" AND `Quarter` = '.$value7.' then record end) as QPS3_b'.$key.'_'.$value7.',';
                $QPS3_m = $QPS3_m.' avg(case when '.$column_name.' in (4,5) AND `QPS3` != "" AND QPS3 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS3 end) as QPS3_m'.$key.'_'.$value7.',';
                $QPS3_p = $QPS3_p.' count(case when '.$column_name.' in (4,5) AND `QPS3` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS3_p'.$key.'_'.$value7.',';
    
                $QPS4_b = $QPS4_b.' count(case when '.$column_name.' in (4,5) AND `QPS4` != "" AND `Quarter` = '.$value7.' then record end) as QPS4_b'.$key.'_'.$value7.',';
                $QPS4_m = $QPS4_m.' avg(case when '.$column_name.' in (4,5) AND `QPS4` != "" AND QPS4 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS4 end) as QPS4_m'.$key.'_'.$value7.',';
                $QPS4_p = $QPS4_p.' count(case when '.$column_name.' in (4,5) AND `QPS4` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS4_p'.$key.'_'.$value7.',';
            }
            else
            {
                $QPS2_b = $QPS2_b.' count(case when '.$column_name.' in ('.$key.') AND `QPS2` != "" AND `Quarter` = '.$value7.' then record end) as QPS2_b'.$key.'_'.$value7.',';
                $QPS2_m = $QPS2_m.' avg(case when '.$column_name.' in ('.$key.') AND `QPS2` != "" AND QPS2 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS2 end) as QPS2_m'.$key.'_'.$value7.',';
                $QPS2_p = $QPS2_p.' count(case when '.$column_name.' in ('.$key.') AND `QPS2` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS2_p'.$key.'_'.$value7.',';
    
                $QPS3_b = $QPS3_b.' count(case when '.$column_name.' in ('.$key.') AND `QPS3` != "" AND `Quarter` = '.$value7.' then record end) as QPS3_b'.$key.'_'.$value7.',';
                $QPS3_m = $QPS3_m.' avg(case when '.$column_name.' in ('.$key.') AND `QPS3` != "" AND QPS3 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS3 end) as QPS3_m'.$key.'_'.$value7.',';
                $QPS3_p = $QPS3_p.' count(case when '.$column_name.' in ('.$key.') AND `QPS3` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS3_p'.$key.'_'.$value7.',';
    
                $QPS4_b = $QPS4_b.' count(case when '.$column_name.' in ('.$key.') AND `QPS4` != "" AND `Quarter` = '.$value7.' then record end) as QPS4_b'.$key.'_'.$value7.',';
                $QPS4_m = $QPS4_m.' avg(case when '.$column_name.' in ('.$key.') AND `QPS4` != "" AND QPS4 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS4 end) as QPS4_m'.$key.'_'.$value7.',';
                $QPS4_p = $QPS4_p.' count(case when '.$column_name.' in ('.$key.') AND `QPS4` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS4_p'.$key.'_'.$value7.',';
            }
        }
        else
        {
            $QPS2_b = $QPS2_b.' count(case when '.$column_name.' in ('.$key.') AND `QPS2` != "" AND `Quarter` = '.$value7.' then record end) as QPS2_b'.$key.'_'.$value7.',';
            $QPS2_m = $QPS2_m.' avg(case when '.$column_name.' in ('.$key.') AND `QPS2` != "" AND QPS2 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS2 end) as QPS2_m'.$key.'_'.$value7.',';
            $QPS2_p = $QPS2_p.' count(case when '.$column_name.' in ('.$key.') AND `QPS2` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS2_p'.$key.'_'.$value7.',';

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

            $QPS4_b = $QPS4_b.' count(case when '.$column_name.' in ('.$key.') AND `QPS4` != "" AND `Quarter` = '.$value7.' then record end) as QPS4_b'.$key.'_'.$value7.',';
            $QPS4_m = $QPS4_m.' avg(case when '.$column_name.' in ('.$key.') AND `QPS4` != "" AND QPS4 IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QPS4 end) as QPS4_m'.$key.'_'.$value7.',';
            $QPS4_p = $QPS4_p.' count(case when '.$column_name.' in ('.$key.') AND `QPS4` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QPS4_p'.$key.'_'.$value7.',';
        }
    }
}
$all = mysqli_query($conn,"SELECT $QPS2_b $QPS2_m $QPS2_p $QPS3_b $QPS3_m $QPS3_p $QPS4_b $QPS4_m $QPS4_p record FROM `cherry` WHERE $condX");
$ALL=mysqli_fetch_array($all);

foreach($timeVal as $key7 => $value7)
{   
    $QPS2_arr2T = array();
    $QPS3_arr2T = array();
    $QPS4_arr2T = array();
    foreach($brand_C as $key=> $value)
    {
        $QPS2_arr2 = array();
        $QPS3_arr2 = array();
        $QPS4_arr2 = array();

        $QPS2_b = $ALL['QPS2_b'.''.$key.'_'.$value7.''];
        if($QPS2_b==0)
        {
            $QPS2_b=1;
        }

        if(is_numeric($ALL['QPS2_m'.''.$key.'_'.$value7.'']))
        {
            $QPS2_m = round($ALL['QPS2_m'.''.$key.'_'.$value7.'']);
        }
        else
        {
            $QPS2_m = 0;
        }
        $QPS2_p2 = $ALL['QPS2_p'.''.$key.'_'.$value7.''];
        $QPS2_p = round(($QPS2_p2 / $QPS2_b ) * 100);
        array_push($QPS2_arr2,$QPS2_m,$QPS2_p);
        array_push($QPS2_arr2T,$QPS2_arr2);
        array_push($QPS2_label_arr,$value);
        
        $QPS3_b = $ALL['QPS3_b'.''.$key.'_'.$value7.''];
        if($QPS3_b==0)
        {
            $QPS3_b=1;
        }

        if(is_numeric($ALL['QPS3_m'.''.$key.'_'.$value7.'']))
        {
            $QPS3_m = round($ALL['QPS3_m'.''.$key.'_'.$value7.'']);
        }
        else
        {
            $QPS3_m = 0;
        }
        $QPS3_p2 = $ALL['QPS3_p'.''.$key.'_'.$value7.''];
        $QPS3_p = round(($QPS3_p2 / $QPS3_b ) * 100);
        array_push($QPS3_arr2,$QPS3_m,$QPS3_p);
        array_push($QPS3_arr2T,$QPS3_arr2);
        array_push($QPS3_label_arr,$value);
        
        $QPS4_b = $ALL['QPS4_b'.''.$key.'_'.$value7.''];
        if($QPS4_b==0)
        {
            $QPS4_b=1;
        }

        if(is_numeric($ALL['QPS4_m'.''.$key.'_'.$value7.'']))
        {
            $QPS4_m = round($ALL['QPS4_m'.''.$key.'_'.$value7.'']);
        }
        else
        {
            $QPS4_m = 0;
        }
        
        $QPS4_p2 = $ALL['QPS4_p'.''.$key.'_'.$value7.''];
        $QPS4_p = round(($QPS4_p2 / $QPS4_b ) * 100);
        array_push($QPS4_arr2,$QPS4_m,$QPS4_p);
        array_push($QPS4_arr2T,$QPS4_arr2);
        array_push($QPS4_label_arr,$value);
    }
    array_push($QPS2_arr,$QPS2_arr2T);
    array_push($QPS3_arr,$QPS3_arr2T);
    array_push($QPS4_arr,$QPS4_arr2T);
}

$data['QPS2'] = $QPS2_arr;
$data['QPS2L'] = $QPS2_label_arr;
$data['QPS3'] = $QPS3_arr;
$data['QPS3L'] = $QPS3_label_arr;
$data['QPS4'] = $QPS4_arr;
$data['QPS4L'] = $QPS4_label_arr;

echo json_encode($data);

?>