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

$data = array();
$QA1_arr = array();
$QA2_arr = array();
$QA4_arr = array();
$QA2_label_arr = array();
$QA1_label_arr = array();
$QA4_label_arr = array();
$QA2_b = '';
$QA2_d = '';
$QA2_pa = '';
$QA2_p = '';
$QA1_b = '';
$QA1_m = '';
$QA1_p = '';
$QA4_b = '';
$QA4_m = '';
$QA4_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";   
    $q=mysqli_query($conn,"SELECT COUNT(`record`) FROM cherry");
    $baseX=mysqli_fetch_array($q);   
    $base = $baseX[0];
}
else
{
    $q=mysqli_query($conn,"SELECT COUNT(`record`) FROM cherry WHERE $condX");
    $baseX=mysqli_fetch_array($q);
    $base = $baseX[0];
}
$data['overall_base'] = $base;
$sql_base=$base;

foreach($timeVal as $key7 => $value7)
{
    foreach($brand_C as $key=> $value)
    {
        if($oemVal==0)
        {
            if($key==9)
            {
                $QA2_b = $QA2_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QA2` != "" AND `Quarter` = '.$value7.' then record end) as QA2_b'.$key.'_'.$value7.',';
                
                $QA2_d = $QA2_d.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QA2` IN (1,2,3,4,5,6,7) AND `Quarter` = '.$value7.' then record end) as QA2_d'.$key.'_'.$value7.',';
                $QA2_pa = $QA2_pa.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QA2` IN (8,9) AND `Quarter` = '.$value7.' then record end) as QA2_pa'.$key.'_'.$value7.',';
                $QA2_p = $QA2_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QA2` IN (10,11) AND `Quarter` = '.$value7.' then record end) as QA2_p'.$key.'_'.$value7.',';

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

                $QA4_b = $QA4_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QA4` != "" AND `Quarter` = '.$value7.' then record end) as QA4_b'.$key.'_'.$value7.',';
                $QA4_m = $QA4_m.' avg(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QA4` != "" AND `QA4` IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QA4 end) as QA4_m'.$key.'_'.$value7.',';
                $QA4_p = $QA4_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QA4` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QA4_p'.$key.'_'.$value7.',';
            }
            else if($key==10)
            {
                $QA2_b = $QA2_b.' count(case when '.$column_name.' in (4,5) AND `QA2` != "" AND `Quarter` = '.$value7.' then record end) as QA2_b'.$key.'_'.$value7.',';
                $QA2_d = $QA2_d.' count(case when '.$column_name.' in (4,5) AND `QA2` IN (1,2,3,4,5,6,7) AND `Quarter` = '.$value7.' then record end) as QA2_d'.$key.'_'.$value7.',';
                $QA2_pa = $QA2_pa.' count(case when '.$column_name.' in (4,5) AND `QA2` IN (8,9) AND `Quarter` = '.$value7.' then record end) as QA2_pa'.$key.'_'.$value7.',';
                $QA2_p = $QA2_p.' count(case when '.$column_name.' in (4,5) AND `QA2` IN (10,11) AND `Quarter` = '.$value7.' then record end) as QA2_p'.$key.'_'.$value7.',';

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

                $QA4_b = $QA4_b.' count(case when '.$column_name.' in (4,5) AND `QA4` != "" AND `Quarter` = '.$value7.' then record end) as QA4_b'.$key.'_'.$value7.',';
                $QA4_m = $QA4_m.' avg(case when '.$column_name.' in (4,5) AND `QA4` != "" AND `QA4` IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QA4 end) as QA4_m'.$key.'_'.$value7.',';
                $QA4_p = $QA4_p.' count(case when '.$column_name.' in (4,5) AND `QA4` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QA4_p'.$key.'_'.$value7.',';
            }
            else
            {
                $QA2_b = $QA2_b.' count(case when '.$column_name.' in ('.$key.') AND `QA2` != "" AND `Quarter` = '.$value7.' then record end) as QA2_b'.$key.'_'.$value7.',';
                $QA2_d = $QA2_d.' count(case when '.$column_name.' in ('.$key.') AND `QA2` IN (1,2,3,4,5,6,7) AND `Quarter` = '.$value7.' then record end) as QA2_d'.$key.'_'.$value7.',';
                $QA2_pa = $QA2_pa.' count(case when '.$column_name.' in ('.$key.') AND `QA2` IN (8,9) AND `Quarter` = '.$value7.' then record end) as QA2_pa'.$key.'_'.$value7.',';
                $QA2_p = $QA2_p.' count(case when '.$column_name.' in ('.$key.') AND `QA2` IN (10,11) AND `Quarter` = '.$value7.' then record end) as QA2_p'.$key.'_'.$value7.',';

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

                $QA4_b = $QA4_b.' count(case when '.$column_name.' in ('.$key.') AND `QA4` != "" AND `Quarter` = '.$value7.' then record end) as QA4_b'.$key.'_'.$value7.',';
                $QA4_m = $QA4_m.' avg(case when '.$column_name.' in ('.$key.') AND `QA4` != "" AND `QA4` IN (1,2,3,4,5,6,7,8,9,10) AND `Quarter` = '.$value7.' then QA4 end) as QA4_m'.$key.'_'.$value7.',';
                $QA4_p = $QA4_p.' count(case when '.$column_name.' in ('.$key.') AND `QA4` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QA4_p'.$key.'_'.$value7.',';
            }
        }
        else
        {
            $QA2_b = $QA2_b.' count(case when '.$column_name.' in ('.$key.') AND `QA2` != "" AND `Quarter` = '.$value7.' then record end) as QA2_b'.$key.'_'.$value7.',';
            $QA2_d = $QA2_d.' count(case when '.$column_name.' in ('.$key.') AND `QA2` IN (1,2,3,4,5,6,7) AND `Quarter` = '.$value7.' then record end) as QA2_d'.$key.'_'.$value7.',';
            $QA2_pa = $QA2_pa.' count(case when '.$column_name.' in ('.$key.') AND `QA2` IN (8,9) AND `Quarter` = '.$value7.' then record end) as QA2_pa'.$key.'_'.$value7.',';
            $QA2_p = $QA2_p.' count(case when '.$column_name.' in ('.$key.') AND `QA2` IN (10,11) AND `Quarter` = '.$value7.' then record end) as QA2_p'.$key.'_'.$value7.',';

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

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

$all = mysqli_query($conn,"SELECT $QA2_b $QA2_d $QA2_pa $QA2_p $QA1_b $QA1_m $QA1_p $QA4_b $QA4_m $QA4_p record FROM `cherry` WHERE $condX");
$ALL=mysqli_fetch_array($all);


foreach($timeVal as $key7 => $value7)
{
    $QA2_arr2T = array();
    $QA1_arr2T = array();
    $QA4_arr2T = array();
    foreach($brand_C as $key=> $value)
    {
        $QA2_arr2 = array();
        $QA1_arr2 = array();
        $QA4_arr2 = array();

        $QA2_b = $ALL['QA2_b'.''.$key.'_'.$value7.''];
        if($QA2_b==0)
        {
            $QA2_b=1;
        }
        $QA2_d2 = $ALL['QA2_d'.''.$key.'_'.$value7.''];
        $QA2_d = round(($QA2_d2 / $QA2_b ) * 100);
        $QA2_pa2 = $ALL['QA2_pa'.''.$key.'_'.$value7.''];
        $QA2_pa = round(($QA2_pa2 / $QA2_b ) * 100);
        $QA2_p2 = $ALL['QA2_p'.''.$key.'_'.$value7.''];
        $QA2_p = round(($QA2_p2 / $QA2_b ) * 100);
        $QA2_n = $QA2_p-$QA2_d;
        array_push($QA2_label_arr,$value);
        array_push($QA2_arr2,$QA2_d,$QA2_pa,$QA2_p,$QA2_n);
        array_push($QA2_arr2T,$QA2_arr2);

        $QA1_b = $ALL['QA1_b'.''.$key.'_'.$value7.''];
        if($QA1_b==0)
        {
            $QA1_b=1;
        }
        if(is_numeric($ALL['QA1_m'.''.$key.'_'.$value7.'']))
        {
            $QA1_m = round($ALL['QA1_m'.''.$key.'_'.$value7.'']);
        }
        else
        {
            $QA1_m = 0;
        }
        $QA1_p2 = $ALL['QA1_p'.''.$key.'_'.$value7.''];
        $QA1_p = round(($QA1_p2 / $QA1_b ) * 100);
        array_push($QA1_arr2,$QA1_m,$QA1_p);
        array_push($QA1_arr2T,$QA1_arr2);
        array_push($QA1_label_arr,$value);
        $QA4_b = $ALL['QA4_b'.''.$key.'_'.$value7.''];
        if($QA4_b==0)
        {
            $QA4_b=1;
        }

        if(is_numeric($ALL['QA1_m'.''.$key.'_'.$value7.'']))
        {
            $QA4_m = round($ALL['QA4_m'.''.$key.'_'.$value7.'']);
        }
        else
        {
            $QA4_m = 0;
        }
        $QA4_p2 = $ALL['QA4_p'.''.$key.'_'.$value7.''];
        $QA4_p = round(($QA4_p2 / $QA4_b ) * 100);
        array_push($QA4_arr2,$QA4_m,$QA4_p);
        array_push($QA4_arr2T,$QA4_arr2);
        array_push($QA4_label_arr,$value);
    }
    array_push($QA2_arr,$QA2_arr2T);
    array_push($QA1_arr,$QA1_arr2T);
    array_push($QA4_arr,$QA4_arr2T);
}


$data['QA1'] = $QA1_arr;
$data['QA2'] = $QA2_arr;
$data['QA4'] = $QA4_arr;
$data['QA2L'] = $QA2_label_arr;
$data['QA1L'] = $QA1_label_arr;
$data['QA4L'] = $QA4_label_arr;

echo json_encode($data);

?>