<?php
require_once('connect.php');
switch($_POST['type']){
  case 'all':
  $qry = mysqli_query($dbC,"SELECT DISTINCT etairia FROM control_form");
  $data = Array();
  $companies = Array();
  $approved = Array();
  $declined = Array();
  while($row = mysqli_fetch_array($qry)){
      //selonda
      $q1 = "SELECT COUNT(*) FROM control_form WHERE etairia=\"".$row['etairia']."\" AND apodekth=0";
      $q2 = " UNION SELECT COUNT(*) FROM control_form WHERE etairia=\"".$row['etairia']."\" AND apodekth=1";
      if($_POST['from']!=''){
        $q1.= " AND hm_elegxou>='".$_POST['from']."'";
        $q2.= " AND hm_elegxou>='".$_POST['from']."'";
      }
      if($_POST['to']!=''){
        $q1.= " AND hm_elegxou<='".$_POST['to']."'";
        $q2.= " AND hm_elegxou<='".$_POST['to']."'";
      }
      // echo $q1.$q2;
      $q = mysqli_query($dbC,$q1.$q2);
      $res = mysqli_fetch_all($q);
      //selonda2
      $q21 = "SELECT COUNT(*) FROM control_form4 WHERE etairia=\"".$row['etairia']."\" AND apodekth=0";
      $q22 = " UNION SELECT COUNT(*) FROM control_form4 WHERE etairia=\"".$row['etairia']."\" AND apodekth=1";
      if($_POST['from']!=''){
        $q21.= " AND hm_elegxou>='".$_POST['from']."'";
        $q22.= " AND hm_elegxou>='".$_POST['from']."'";
      }
      if($_POST['to']!=''){
        $q21.= " AND hm_elegxou<='".$_POST['to']."'";
        $q22.= " AND hm_elegxou<='".$_POST['to']."'";
      }
      // echo $q1.$q2;
      $q2 = mysqli_query($dbC,$q21.$q22);
      $res2 = mysqli_fetch_all($q2);
      // $data[$row['etairia']] = ["approved" => $res[1][0], "declined" => $res[0][0]];
      array_push($companies, $row['etairia']);
      array_push($approved, $res[1][0]+$res2[1][0]);
      array_push($declined, $res[0][0]+$res2p[0][0]);
      // print_r($res);
  }

  echo json_encode(["comp"=>$companies, "app"=>$approved, "dec"=>$declined], JSON_UNESCAPED_UNICODE);
  // print_r($data);

  break;

  case 'ice':
    $qry = "SELECT DISTINCT kwd_sus FROM control_form";
    $qry.=($_POST['from']!='' ? " WHERE hm_elegxou>='".$_POST['from']."'" : '');
    $qry.=($_POST['to']!='' ? ($_POST['from']!='' ? " AND hm_elegxou<='".$_POST['to']."'" : " WHERE hm_elegxou<='".$_POST['to']."'") : '');
    $qry2 = " UNION SELECT DISTINCT kwd_sus FROM control_form4";
    $qry2.=($_POST['from']!='' ? " WHERE hm_elegxou>='".$_POST['from']."'" : '');
    $qry2.=($_POST['to']!='' ? ($_POST['from']!='' ? " AND hm_elegxou<='".$_POST['to']."'" : " WHERE hm_elegxou<='".$_POST['to']."'") : '');
    $qry = mysqli_query($dbC, $qry.$qry2." ORDER BY kwd_sus ASC");
    $susk = Array();
    $grn = Array();
    $orng  = Array();
    $rd = Array();
    While($row = mysqli_fetch_array($qry)){
        $red = 0;
        $green = 0;
        $orange = 0;
        $q = "SELECT posothta_pagou FROM control_form WHERE kwd_sus='".$row['kwd_sus']."'";
        $q.=($_POST['from']!='' ? " AND hm_elegxou>='".$_POST['from']."'" : '');
        $q.=($_POST['to']!='' ? " AND hm_elegxou<='".$_POST['to']."'" : '');
        $q2 = " UNION SELECT posothta_pagou FROM control_form4 WHERE kwd_sus='".$row['kwd_sus']."'";
        $q2.=($_POST['from']!='' ? " AND hm_elegxou>='".$_POST['from']."'" : '');
        $q2.=($_POST['to']!='' ? " AND hm_elegxou<='".$_POST['to']."'" : '');
        $q = mysqli_query($dbC, $q.$q2);
        $res = mysqli_fetch_all($q);
        foreach($res as $arr => $dataset){
          foreach(json_decode($dataset[0]) as $current_value){
            if($current_value>=80 && $current_value!=0){
              $green++;
            } elseif($current_value>=60 && $current_value<80 && $current_value!=0) {
              $orange++;
            } elseif($current_value<60 && $current_value!=0) {
              $red++;
            }
          }
        }
        array_push($susk, $row['kwd_sus']);
        array_push($grn, $green);
        array_push($orng, $orange);
        array_push($rd, $red);
    }
    echo json_encode(["susk"=>$susk,"green"=>$grn,"orange"=>$orng,"red"=>$rd]);
  break;

  case 'pack':
    $qry = mysqli_query($dbC, "SELECT DISTINCT kwd_sus FROM (SELECT DISTINCT kwd_sus FROM control_form union SELECT DISTINCT kwd_sus FROM control_form4) as cf ORDER BY kwd_sus ASC");
    echo json_encode(mysqli_fetch_all($qry));
  break;

  case 'getPack':
    $red = 0;
    $green = 0;
    $orange = 0;
    $q = "SELECT posothta_pagou FROM control_form WHERE kwd_sus='".$_POST['kwd_sus']."'";
    $q.=($_POST['from']!='' ? " AND hm_elegxou>='".$_POST['from']."'" : '');
    $q.=($_POST['to']!='' ? " AND hm_elegxou<='".$_POST['to']."'" : '');
    $q2 = " UNION SELECT posothta_pagou FROM control_form4 WHERE kwd_sus='".$_POST['kwd_sus']."'";
    $q2.=($_POST['from']!='' ? " AND hm_elegxou>='".$_POST['from']."'" : '');
    $q2.=($_POST['to']!='' ? " AND hm_elegxou<='".$_POST['to']."'" : '');
    $q = mysqli_query($dbC, $q.$q2);
    $res = mysqli_fetch_all($q);
    foreach($res as $arr => $dataset){
      foreach(json_decode($dataset[0]) as $current_value){
        if($current_value>=80 && $current_value!=0){
          $green++;
        } elseif($current_value>=60 && $current_value<80 && $current_value!=0) {
          $orange++;
        } elseif($current_value<60 && $current_value!=0) {
          $red++;
        }
      }
    }
    // array_push($susk, $row['kwd_sus']);
    echo json_encode(["green"=>$green, "orange"=>$orange,"red"=>$red]);
  break;

  case 'sizes':
    $q = "SELECT id, megethos, upomege8h, upermege8h, ar_psariwn from control_form";
    $q2 = " UNION SELECT id, megethos, upomege8h, upermege8h, ar_psariwn from control_form4";
    $qParam = '';
    foreach(['kwd_sus'=>'kwd_sus=','from'=>'hm_elegxou>=','to'=>'hm_elegxou<='] as $param => $col){
      if(isset($_POST[$param]) && $_POST[$param]!=''){
        $qParam .= ($qParam=='' ? " WHERE ${col}'{$_POST[$param]}'" : " AND ${col}'{$_POST[$param]}'");
      }
    }
    $qq = mysqli_query($dbC,$q.$qParam.$q2.$qParam);
    $headers = (!isset($_POST['megethos']) || $_POST['megethos']=='') ? Array("2/3","3/4","400/500","300/500","500/600","500/1000","4/6","6/8","8/1000","1000+","1500+","Άλλο") : Array($_POST['megethos']);
    $upom = array_fill(0, 12, 0);
    $uperm = array_fill(0, 12, 0);
    $sunola = array_fill(0, 12, 0);
    $ar_psar = array_fill(0, 12, 0);
    $total = 0;
    while($row = mysqli_fetch_array($qq)){
        $upomege8h = json_decode($row['upomege8h']);
        $upermege8h = json_decode($row['upermege8h']);
        $ar_psariwn = json_decode($row['ar_psariwn']);
        $megethos = json_decode($row['megethos']);
        foreach($megethos as $index => $megethos){
          if($_POST['megethos']!='' && $megethos != $_POST['megethos']){
            continue;
          }
          if($upomege8h[$index]!='' && $upomege8h[$index]!=0){
              $upom[array_search($megethos,$headers)]+=intval($upomege8h[$index]);
              $sunola[array_search($megethos,$headers)]+=intval($upomege8h[$index]);
          }
          if($upermege8h[$index]!='' && $upermege8h[$index]!=0){
              $uperm[array_search($megethos,$headers)]+=intval($upermege8h[$index]);
              $sunola[array_search($megethos,$headers)]+=intval($upermege8h[$index]);
          }
          if($ar_psariwn[$index]!='' && $ar_psariwn[$index]!=''){
              $ar_psar[array_search($megethos,$headers)] += intval($ar_psariwn[$index]);
          }        
        }
    }
    $total = array_map(function ($x, $y) {
      return $y-$x;
    }, $sunola, $ar_psar);
    echo json_encode(['headers'=>$headers, 'upermege8h'=>$uperm, 'upomege8h'=>$upom, 'ar_psar'=>$total]);
  break;
    /**
     * Green -> x <= v+1%, x >= v-0.17%  e 
     * Orange -> x <= v-0.17%, x >= v-0.5% e
     * yellow -> x >= v-1.5%, x <= v-0.5%
     * red -> x < v-1.5%
     * 
     * blue -> x >= v+1%, x <= v+1.67% e
     * BlackBlue => x > v+1.67%
     * 
     */
  case 'e':
    $cRes = ["green"=>0,"yellow"=>0,"orange"=>0,"red"=>0,"blue"=>0,"blueblack"=>0,"total"=>0];
    // $log = "";
    $headers = (!isset($_POST['megethos']) || $_POST['megethos'] == '') ? array("2/3", "3/4", "400/500", "300/500", "500/600", "500/1000", "4/6", "6/8", "8/1000", "1000+", "1500+", "Άλλο") : array($_POST['megethos']);
    $totals = array("2/3"=>0, "3/4"=>0, "400/500"=>0, "300/500"=>0, "500/600"=>0, "500/1000"=>0, "4/6"=>0, "6/8"=>0, "8/1000"=>0, "1000+"=>0, "1500+"=>0, "Άλλο"=>0);
    $q = "SELECT varos_kiv2 as varos_kiv, pinsum, megethos FROM control_form";
    $q2 = " UNION SELECT varos_kiv2 as varos_kiv, pinsum, megethos FROM control_form4";
    $qParam = '';
    foreach(['kwd_sus'=>'kwd_sus=','from'=>'hm_elegxou>=','to'=>'hm_elegxou<='] as $param => $col){
      if(isset($_POST[$param]) && $_POST[$param]!=''){
        $qParam .= ($qParam=='' ? " WHERE ${col}'{$_POST[$param]}'" : " AND ${col}'{$_POST[$param]}'");
      }
    }
    $res = mysqli_query($dbC, $q.$qParam.$q2.$qParam);
    while($row = mysqli_fetch_array($res)){
      $varoskiv = json_decode($row['varos_kiv']);
      $pinsum = json_decode($row['pinsum']);
      $megethos = json_decode($row['megethos']);
      if(!is_array($varoskiv)){continue;}
      foreach($varoskiv as $index => $varos){
        $varos = ($varos == "" ? 3000 : intval($varos) * 1000);
        $sum = $pinsum[$index];
        if(($_POST['varos']!='' && $varos/1000 != $_POST['varos']) || $sum==""){
          continue;
        }

        $totals[$megethos[$index]]++;
        if(($_POST['megethos']!='' && $megethos[$index] != $_POST['megethos'])){
          continue;
        }
        $cRes['total']++;
        
        switch($sum){
          case $sum > round($varos+($varos*(1.67/100))):
            // $log.="${sum} > ".($varos+($varos*(1.67/100)))."<br/>";
            $cRes['blueblack']++;
          break;

          case $sum <= round($varos+($varos * (1.67/100))) && $sum >= round($varos+($varos*(1/100))):
            // $log.="${sum} <= ".($varos+($varos * (1.67/100)))." && ${sum} >= ".($varos+($varos*(1/100)))."<br/>";
            $cRes['blue']++;
          break;

          case $sum <= round($varos+($varos * (1/100))) && $sum >= round($varos-($varos*(0.17/100))):
            // $log.="${sum} <= ".($varos+($varos * (1/100)))." && ${sum} >= ".($varos-($varos*(0.17/100)))."<br/>";
            $cRes['green']++;
          break;

          case $sum <= round($varos-($varos * (0.17/100))) && $sum >= round($varos-($varos*(0.5/100))):
            // $log.="${sum} <= ".($varos-($varos * (0.17/100)))." && ${sum} >= ".($varos-($varos*(0.5/100)))."<br/>";
            $cRes['yellow']++;
          break;

          case $sum <= round($varos-($varos * (0.5/100))) && $sum >= round($varos-($varos*(1.5/100))):
            // $log.="${sum} <= ".($varos-($varos * (0.5/100)))." && ${sum} >= ".($varos-($varos*(1.5/100)))."<br/>";
            $cRes['orange']++;
          break;

          case $sum < round($varos-($varos * (1.5/100))):
            // $log.="${sum} < ".($varos-($varos*(1.5/100)))."<br/>";
            $cRes['red']++;
          break;
        }
      }
    }
    echo json_encode(['chartData'=>$cRes,'totals'=>$totals]);
  break;
  
  case 'eikona': //Συνολική εικόνα
    $filter = ''; //Filter sting for use in query(date From/To, User)
    $mBoxes = $mControls = $mPhotos = $boxes = ['selonda'=>0,'selonda2'=>0,'medfrigo'=>0]; //Median-Boxes, Median-Controls, Median-Photos, boxes Counters
    foreach(['from'=>'hm_elegxou>=','to'=>'hm_elegxou<=','user'=>'elegkths='] as $param => $col){ //Filter query construct
      if(isset($_POST[$param]) && $_POST[$param]!=''){
        $filter .= ($filter=='' ? " WHERE ${col}'{$_POST[$param]}'" : " AND ${col}'{$_POST[$param]}'");
      }
    }
    $q = "SELECT COUNT(*) as selonda FROM control_form ${filter} union all SELECT COUNT(*) as medfrigo FROM control_form2 ${filter} union all SELECT COUNT(*) as selonda2 FROM control_form4 ${filter}";
    foreach(['selonda'=>'control_form','selonda2'=>'control_form4','medfrigo'=>'control_form2'] as $tag => $table){ //loop for each table
      $mTemp = ['Boxes'=>0,'Controls'=>0,'Photos'=>0,'monthDays'=>0,'months'=>0]; //Temp Counters
      $currentDay = "";
      $currentMonth = "";
      $qk = "select ar_psariwn, hm_elegxou, picture from ${table} ${filter} ORDER BY hm_elegxou ASC"; //Query for boxes enumeration
      $rk = mysqli_query($dbC, $qk);
      while($row = mysqli_fetch_assoc($rk)){
        //Median
        $day = date("d",strtotime($row['hm_elegxou']));
        $month = date("m",strtotime($row['hm_elegxou']));
        if($currentMonth==$month && ($row['hm_elegxou']!="-" && $row['hm_elegxou']!="0" && $row['hm_elegxou']!="")){
          $mTemp['Controls']++;
          if($day!=$currentDay){
            $currentDay=$day;
            $mTemp['monthDays']++;
          }
        } else {
          if($currentMonth!=""){
            // echo $mTemp['months'].".${tag}";
            $mTemp['months']++;
            //month median calculation
            $mBoxes[$tag] += $mTemp['Boxes']/$mTemp['monthDays']; 
            $mControls[$tag] += $mTemp['Controls']/$mTemp['monthDays'];
            $mPhotos[$tag] += $mTemp['Photos']/$mTemp['monthDays'];
            $mTemp = ['Boxes'=>0,'Controls'=>0,'Photos'=>0,'monthDays'=>0,'months'=>$mTemp['months']]; //Reset Counters
          }
          $currentMonth=$month;
          $mTemp['Controls']++;
        }
        $mTemp['Photos'] += sizeOf(json_decode($row['picture']));
        //Median - END
        $kivs = json_decode($row['ar_psariwn']);
        foreach($kivs as $kiv){
          if($kiv!="" && $kiv!=0){
            $boxes[$tag]++;
            $mTemp['Boxes']++;
          }
        }
      }
      $mTemp['months'] = $mTemp['months']==0 ? 1 : $mTemp['months'];
      //total median calculation
      $mBoxes[$tag] = $mBoxes[$tag]/$mTemp['months'];
      $mControls[$tag] = $mControls[$tag]/$mTemp['months'];
      // echo "months: {$mTemp['months']}, photos: {$mPhotos[$tag]}";
      $mPhotos[$tag] = $mPhotos[$tag]/$mTemp['months'];
      // echo "photos after calc: {$mPhotos[$tag]}";

    }
    $r = mysqli_query($dbC, $q);
    $res = mysqli_fetch_all($r);
    echo json_encode(
      [
        'selonda'=>        ["controls"=>$res[0][0],"boxes"=>$boxes['selonda'],"median"=>["boxes"=>round($mBoxes['selonda']),"controls"=>round($mControls['selonda']),"pictures"=>round($mPhotos['selonda'])]], 
        'medfrigo'=>       ["controls"=>$res[1][0],"boxes"=>$boxes['medfrigo'],"median"=>["boxes"=>round($mBoxes['medfrigo']),"controls"=>round($mControls['medfrigo']),"pictures"=>round($mPhotos['medfrigo'])]], 
        'selonda2'=>       ["controls"=>$res[2][0],"boxes"=>$boxes['selonda2'],"median"=>["boxes"=>round($mBoxes['selonda2']),"controls"=>round($mControls['selonda2']),"pictures"=>round($mPhotos['selonda2'])]]
      ]
    );
    // echo $q;
  break;

  case 'users':
    $out="";
    $q="select * from (select distinct elegkths from control_form where elegkths<>\"\" union select distinct elegkths from control_form2 where elegkths<>\"\" union select distinct elegkths from control_form4 where elegkths<>\"\") ttl order by elegkths ASC";
    $r = mysqli_query($dbC, $q);
    while($row = mysqli_fetch_assoc($r)){
      $out.="<option value=\"{$row['elegkths']}\">{$row['elegkths']}</option>";
    }
    echo $out;
  break;
}

?>
