<?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['trendcondX'];
$conX = $_REQUEST['conX'];
$oemVal = $_REQUEST['oemVal'];
$timeVal = $_REQUEST['timeVal'];

$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($timeVal as $key7 => $value7)
{
    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.'` != "" AND `Quarter` = '.$value7.' then record end) as T_QTD4_'.$value2.'_b'.$key.'_'.$value7.',';
                    $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) AND `Quarter` = '.$value7.' then T_QTD4_'.$value2.' end) as T_QTD4_'.$value2.'_m'.$key.'_'.$value7.',';
                    $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) AND `Quarter` = '.$value7.' then record end) as T_QTD4_'.$value2.'_p'.$key.'_'.$value7.',';
                }

                $QTD5_b = $QTD5_b.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QTD5` != "" AND `Quarter` = '.$value7.' then record end) as QTD5_b'.$key.'_'.$value7.',';
                $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) AND `Quarter` = '.$value7.' then QTD5 end) as QTD5_m'.$key.'_'.$value7.',';
                $QTD5_p = $QTD5_p.' count(case when '.$column_name.' in (1,2,3,4,5,6,7,8) AND `QTD5` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QTD5_p'.$key.'_'.$value7.',';

                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.'` != "" AND `Quarter` = '.$value7.' then record end) as QTD'.$value3.'_b'.$key.'_'.$value7.',';
                    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.' AND `Quarter` = '.$value7.' then record end) as QTD'.$value3.'_'.$value4.'_t'.$key.'_'.$value7.',';
                    }
                }
            }
            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.'` != "" AND `Quarter` = '.$value7.' then record end) as T_QTD4_'.$value2.'_b'.$key.'_'.$value7.',';
                    $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) AND `Quarter` = '.$value7.' then T_QTD4_'.$value2.' end) as T_QTD4_'.$value2.'_m'.$key.'_'.$value7.',';
                    $QTD_p = $QTD_p.' count(case when '.$column_name.' in (4,5) AND `T_QTD4_'.$value2.'` IN (9,10) AND `Quarter` = '.$value7.' then record end) as T_QTD4_'.$value2.'_p'.$key.'_'.$value7.',';
                }

                $QTD5_b = $QTD5_b.' count(case when '.$column_name.' in (4,5) AND `QTD5` != "" AND `Quarter` = '.$value7.' then record end) as QTD5_b'.$key.'_'.$value7.',';
                $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) AND `Quarter` = '.$value7.' then QTD5 end) as QTD5_m'.$key.'_'.$value7.',';
                $QTD5_p = $QTD5_p.' count(case when '.$column_name.' in (4,5) AND `QTD5` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QTD5_p'.$key.'_'.$value7.',';

                foreach($QTDD_count as $key3 => $value3)
                {
                    $QTDD_b = $QTDD_b.' count(case when '.$column_name.' in (4,5) AND `QTD'.$value3.'` != "" AND `Quarter` = '.$value7.' then record end) as QTD'.$value3.'_b'.$key.'_'.$value7.',';
                    foreach($yes_no_array as $key4 => $value4)
                    {
                        $QTDD_t = $QTDD_t.' count(case when '.$column_name.' in (4,5) AND `QTD'.$value3.'` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QTD'.$value3.'_'.$value4.'_t'.$key.'_'.$value7.',';
                    }
                }
            }
            else
            {
                foreach($QTD_count as $key2 => $value2)
                {
                    $QTD_b = $QTD_b.' count(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` != "" AND `Quarter` = '.$value7.' then record end) as T_QTD4_'.$value2.'_b'.$key.'_'.$value7.',';
                    $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) AND `Quarter` = '.$value7.' then T_QTD4_'.$value2.' end) as T_QTD4_'.$value2.'_m'.$key.'_'.$value7.',';
                    $QTD_p = $QTD_p.' count(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` IN (9,10) AND `Quarter` = '.$value7.' then record end) as T_QTD4_'.$value2.'_p'.$key.'_'.$value7.',';
                }

                $QTD5_b = $QTD5_b.' count(case when '.$column_name.' in ('.$key.') AND `QTD5` != "" AND `Quarter` = '.$value7.' then record end) as QTD5_b'.$key.'_'.$value7.',';
                $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) AND `Quarter` = '.$value7.' then QTD5 end) as QTD5_m'.$key.'_'.$value7.',';
                $QTD5_p = $QTD5_p.' count(case when '.$column_name.' in ('.$key.') AND `QTD5` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QTD5_p'.$key.'_'.$value7.',';

                foreach($QTDD_count as $key3 => $value3)
                {
                    $QTDD_b = $QTDD_b.' count(case when '.$column_name.' in ('.$key.') AND `QTD'.$value3.'` != "" AND `Quarter` = '.$value7.' then record end) as QTD'.$value3.'_b'.$key.'_'.$value7.',';
                    foreach($yes_no_array as $key4 => $value4)
                    {
                        $QTDD_t = $QTDD_t.' count(case when '.$column_name.' in ('.$key.') AND `QTD'.$value3.'` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QTD'.$value3.'_'.$value4.'_t'.$key.'_'.$value7.',';
                    }
                }
            }
        }
        else
        {
            foreach($QTD_count as $key2 => $value2)
            {
                $QTD_b = $QTD_b.' count(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` != "" AND `Quarter` = '.$value7.' then record end) as T_QTD4_'.$value2.'_b'.$key.'_'.$value7.',';
                $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) AND `Quarter` = '.$value7.' then T_QTD4_'.$value2.' end) as T_QTD4_'.$value2.'_m'.$key.'_'.$value7.',';
                $QTD_p = $QTD_p.' count(case when '.$column_name.' in ('.$key.') AND `T_QTD4_'.$value2.'` IN (9,10) AND `Quarter` = '.$value7.' then record end) as T_QTD4_'.$value2.'_p'.$key.'_'.$value7.',';
            }

            $QTD5_b = $QTD5_b.' count(case when '.$column_name.' in ('.$key.') AND `QTD5` != "" AND `Quarter` = '.$value7.' then record end) as QTD5_b'.$key.'_'.$value7.',';
            $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) AND `Quarter` = '.$value7.' then QTD5 end) as QTD5_m'.$key.'_'.$value7.',';
            $QTD5_p = $QTD5_p.' count(case when '.$column_name.' in ('.$key.') AND `QTD5` IN (9,10) AND `Quarter` = '.$value7.' then record end) as QTD5_p'.$key.'_'.$value7.',';

            foreach($QTDD_count as $key3 => $value3)
            {
                $QTDD_b = $QTDD_b.' count(case when '.$column_name.' in ('.$key.') AND `QTD'.$value3.'` != "" AND `Quarter` = '.$value7.' then record end) as QTD'.$value3.'_b'.$key.'_'.$value7.',';
                foreach($yes_no_array as $key4 => $value4)
                {
                    $QTDD_t = $QTDD_t.' count(case when '.$column_name.' in ('.$key.') AND `QTD'.$value3.'` = '.$value4.' AND `Quarter` = '.$value7.' then record end) as QTD'.$value3.'_'.$value4.'_t'.$key.'_'.$value7.',';
                }
            }
        }
        
    }
}
$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($timeVal as $key7 => $value7)
{
    $QTD1_arrXT = array();
    $QTD1_label_arrXT = array();
    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.'_'.$value7.''];
            if($QTD_b==0)
            {
                $QTD_b=1;
            }

            if(is_numeric($ALL['T_QTD4_'.$value2.'_m'.$key.'_'.$value7.'']))
            {
                $QTD_m = round($ALL['T_QTD4_'.$value2.'_m'.$key.'_'.$value7.'']);
            }
            else
            {
                $QTD_m = 0;
            }
            $QTD_p2 = $ALL['T_QTD4_'.$value2.'_p'.$key.'_'.$value7.''];
            $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_arrXT,$QTD1_arrX);
            array_push($QTD1_label_arrXT,$QTD1_label_arrX);
        }
    }
    array_push($QTD1_arr,$QTD1_arrXT);
    array_push($QTD1_label_arr,$QTD1_label_arrXT);
}

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

        if(is_numeric($ALL['QTD5_m'.''.$key.'_'.$value7.'']))
        {
            $QTD5_m = round($ALL['QTD5_m'.''.$key.'_'.$value7.'']);
        }
        else
        {
            $QTD5_m = 0;
        }
        $QTD5_p2 = $ALL['QTD5_p'.''.$key.'_'.$value7.''];
        $QTD5_p = round(($QTD5_p2 / $QTD5_b ) * 100);
        array_push($QTDOVERALL_arrX,$QTD5_m,$QTD5_p,$QTD5_b);
        array_push($QTDOVERALL_arrXT,$QTDOVERALL_arrX);
        array_push($QTDOVERALL_label_arr,$value);
    }
    array_push($QTDOVERALL_arr,$QTDOVERALL_arrXT);
}

foreach($timeVal as $key7 => $value7)
{
    $QTDD_arr2T = array();
    $QTDD_label_arrXT = array();
    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.'_'.$value7.''];
            if($QTD_b==0)
            {
                $QTD_b=1;
            }
            foreach($yes_no_array as $key4 => $value4)
            {
                $QTD_t2 = $ALL['QTD'.$value3.'_'.$value4.'_t'.''.$key.'_'.$value7.''];
                $QTD_t = round(($QTD_t2/$QTD_b)*100);
                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_arr2T,$QTDD_arr2);
            array_push($QTDD_label_arrXT,$QTDD_label_arrX);
        }
    }
    array_push($QTDD_arr,$QTDD_arr2T);
    array_push($QTDD_label_arr,$QTDD_label_arrXT);
}

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);

?>