<?php
/**
 * Created by PhpStorm.
 * User: pejot
 * Date: 2015-10-16
 * Time: 04:19
 */
//header('Content-Type: text/html; charset=utf-8');
?>
<!doctype html>
<html lang="en">
<head>

    <script type="text/javascript" src="../../../js/jquery-1.9.1.min.js"></script>
    <script type="text/javascript" src="../../../js/jquery-ui-1.10.2.custom.min.js"></script>

    <link rel="stylesheet" href="../../../css/bootstrap.min_pejot.css?<?php echo time(); ?>">
    <script src="../../../js/bootstrap.min.js" type="text/javascript"></script>
</head>
    <body>
    <div class="panel panel-default">
        <!-- Default panel contents -->
        <div class="panel-heading">RAPORT</div>
        <div class="panel-body">
        </div>

<?
global $db_handle;

//header("Expires: Sat, 01 Jan 2000 00:00:00 GMT");
//header("Last-Modified: ".gmdate("D, d M Y H:i:s")." GMT");
//header("Cache-Control: post-check=0, pre-check=0",false);

require_once('../../../vendor/autoload.php');
require_once('../../../func_pejot.php');

use League\Csv\Reader;

$reader = Reader::createFromPath('../quota.txt', 'r');
$reader->setDelimiter("\t");
$fileData=$reader->fetchAssoc(0);
$quota=[];
foreach ($fileData as $lp=>$dataRow) {
    $quota[$dataRow['NRANK']]=$dataRow;
    unset($quota[$dataRow['NRANK']]['NRANK']);
}




$token=$_GET['token'];
if ($token!="weeszd2727xdzszadfsBSDAA") {print "Invalid pass token"; exit;}

$definitionsJSON=file_get_contents('definitions.json');
$defs=json_decode($definitionsJSON,true);

//new dBug($quota);
$nrAnkArr=array_keys($quota);
$nrAnkArr=array_combine($nrAnkArr,$nrAnkArr);
//new dBug($nrAnkArr);

//$defs['NRANK']['vallabs']=

$defs['NRANK']['vallabs']=array_filter($nrAnkArr);
//new dBug($defs);



$tableName='GARAGE';
$whereAlways=[
    'finished=1',
    'lp>1',
    'duration>4',
    'NRANK > 0',
    'screenout is null'
    ];

$statArr=[];
$where=[];
$toCount=['MIASTOPR','WIEKPR','PLECPR','PROD1PR','PROD2PR','NRANK'];
foreach ($toCount as $id) {
    $where[$id]['where']=genWhere($id);
    $where[$id]['title']=$defs[$id]['varlab'];
}

$where=crossVars($where,'KOORDYNATOR','WIEKPR');
//new dBug($where);

function crossVars($whereArr,$var1,$var2) {
    global $defs;
    foreach ($whereArr[$var1]['where'] as $lab=>$where) {
        foreach ($whereArr[$var2]['where'] as $lab2=>$where2) {
            $whereArr[$var1." x ".$var2]['where'][$lab." x ".$lab2]=$where." AND ".$where2;
        }
        $whereArr[$var1." x ".$var2]['title']=$defs[$var1]['varlab']." x ".$defs[$var2]['varlab'];
    }
    return $whereArr;
}


$sqls=$where;
foreach ($where as $id=>$data) {
    foreach ($data['where'] as $lab=>$where) {
        $sqls[$id]['where'][$lab]="SELECT COUNT(*) as COUNTS, AVG(duration) as MEAN, MIN(duration) as MIN, MAX(duration) as MAX FROM $tableName where $where AND ".implode(" AND ",$whereAlways);
        $sqls[$id]['where2'][$lab]="SELECT duration FROM $tableName where $where AND ".implode(" AND ",$whereAlways)." ORDER BY duration";
    }
}


//BUILD REPORT

$db_handle=polacz();

foreach ($sqls as $id=>$data) {
    $statArr[$id]['title']=$sqls[$id]['title'];
    foreach ($data['where'] as $lab=>$sql) {
        $result= mysql_query($sql,$db_handle) or die ("Error in query: $sql. ".mysql_error());
        while($row = mysql_fetch_array($result))
        {
            foreach ($row as $name=>$value) {
                if (is_numeric($name)) {continue;}
                $statArr[$id]['STATS'][$lab][$name]=$row[$name];
            }
        }
    }
    foreach ($data['where2'] as $lab=>$sql) {
        $result= mysql_query($sql,$db_handle) or die ("Error in query: $sql. ".mysql_error());
        $answers=[];
        while($row = mysql_fetch_array($result))
        {
            $answers[]=$row['duration'];
        }
        $statArr[$id]['STATS'][$lab]['SD']=getSD($answers);
        $statArr[$id]['STATS'][$lab]['MEDIAN']=getMedian($answers);
    }
}

//new dBug($sqls);



//new dBug($statArr);

mysql_free_result($result);
rozlacz();




//new dBug($statArr);
foreach ($statArr as $id=>$arr) {
    print(arrToTable($arr));
}

function arrToTable($arr) {
    $outArr=[];
    $outArr[]="<table class='table-condensed table-striped'>";
    $outArr[]="<thead>";
    $outArr[]="<tr>";

    $outArr[]="<th colspan='".count($arr)."'>".$arr['title']."</th>";

    $outArr[]="</tr>";
    $outArr[]="<tr><th>Labelka</th>";
    foreach ($arr['STATS'] as $label=>$statData) {
        foreach ($statData as $statLabel=>$statValue) {
            if ($statLabel===1) {continue;}
            $outArr[]="<th>".$statLabel."</th>";
        }
        break;
    }
    $outArr[]="</tr>";

    //$outArr[]="<th>Liczebność</th><th>Średnia</th><th>Mediana</th></tr>";
    $outArr[]="</thead>";
    $outArr[]="<tbody>";

    foreach ($arr['STATS'] as $label=>$statData) {
        $outArr[]="<tr>";
        $outArr[]="<td>".$label."</td>";
        foreach ($statData as $statLabel=>$statValue) {
            if ($statLabel===1) {continue;}
            $outArr[]="<td>".$statValue."</td>";
        }
        $outArr[]="</tr>";
    }
    $outArr[]="</tbody>";
    $outArr[]="</table>";
    return implode("\n\r",$outArr);
}

function genWhere($id) {
    global $defs;
    $outWhere=[];
    foreach ($defs[$id]['vallabs'] as $code=>$lab) {
        $outWhere[$lab]=$id.'='.$code;
    }
    $outWhere['TOTAL']="1=1";
    return $outWhere;
}


function getMedian($arr) {
    //Make sure it's an array.
    if(!is_array($arr)){
        throw new Exception('$arr must be an array!');
    }
    //If it's an empty array, return FALSE.
    if(empty($arr)){
        return false;
    }
    //Count how many elements are in the array.
    $num = count($arr);
    //Determine the middle value of the array.
    $middleVal = floor(($num - 1) / 2);
    //If the size of the array is an odd number,
    //then the middle value is the median.
    if($num % 2) {
        return $arr[$middleVal];
    }
    //If the size of the array is an even number, then we
    //have to get the two middle values and get their
    //average
    else {
        //The $middleVal var will be the low
        //end of the middle
        $lowMid = $arr[$middleVal];
        $highMid = $arr[$middleVal + 1];
        //Return the average of the low and high.
        return (($lowMid + $highMid) / 2);
    }
}

function getSD($arr) {
    $num_of_elements = count($arr);
    $variance = 0.0;
    // calculating mean using array_sum() method
    $average = array_sum($arr)/$num_of_elements;
    foreach($arr as $i)
    {
        // sum of squares of differences between
        // all numbers and means.
        $variance += pow(($i - $average), 2);
    }
    return (float)sqrt($variance/$num_of_elements);
}

?>
    </div>
    </body>
</html>
