<?php
$servername = "localhost:3306";
$username = "root";
$password = "root";
$dbname = "fsharkc_efetasks";

$itemId = $_REQUEST['itemId'];

//Classes
class HorasFenix {
    public $dataLancada= '';
    public $horasTrabalhadas= '';
}

class Fenix {
    public $horasFenix = '';
    public $etc = '';
}

class DataPlanejamento {
    public $data = '';
}
//Classes - FIM

// Instâncicas
$fenix      = new Fenix;
$horasFenix = new HorasFenix;
$dataPlanejamento  = new DataPlanejamento;

// Variaveis
$createDate   = '';
$deadLineDate = '';

//MySQL==============================================================================================================================
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

//=====================================SQL PARA PEGAR INFORMAÇÕES DA TABELA everis_hours_fenix
$sqlEverisHours = "SELECT itemId,date,incurred,etc FROM everis_hours_fenix WHERE itemId = $itemId";

//=====================================SQL PARA PEGAR O ETC DA DATA MAIS RECENTE
$sqlMaxEtc = "SELECT etc
FROM everis_hours_fenix AS a
WHERE date = (
    SELECT MAX(date)
    FROM everis_hours_fenix AS b
    WHERE a.itemId = $itemId
)";

$resultEverisHours = mysqli_query($conn, $sqlEverisHours);
$resultMaxEtc      = mysqli_query($conn, $sqlMaxEtc);

// Cria array
$arrayHF = array();
$countLancamento;

// LÓGICA EVERIS HOURS
if (mysqli_num_rows($resultEverisHours) > 0) {

    // output data of each row
    while($row = mysqli_fetch_assoc($resultEverisHours)) {

        // Objeto horasFenix
        $horasFenix = "";
        $horasFenix->dataLancada      = date("d-m-Y", strtotime($row["date"]));
        $horasFenix->horasTrabalhadas = $row["incurred"];

        // Insere no array
        $countLancamento++;
        array_push($arrayHF, $horasFenix);
    }
} else {
   //echo "0 results - SELECT EVERIS HOURS";
}

// LÓGICA MAX DATE ETC
if (mysqli_num_rows($resultMaxEtc) > 0) {

    // output data of each row
    while($row = mysqli_fetch_assoc($resultMaxEtc)) {

        $fenix->etc = $row["etc"];
    }
} else {
    //echo "0 results - SELECT MAX DATE ETC";
}
//=====================================SQL PARA PEGAR createDate e deadLineDate
$sqlCreateDeadLineDate = "SELECT a.deadlineDate, b.statusDate
FROM fsharkc_efetasks.frk_item a,
	 fsharkc_efetasks.frk_itemstatus b
where a.itemId = b.itemId
and   a.itemId = $itemId
and   b.statusKey = 0";

$resultCreateDeadLineDates = mysqli_query($conn, $sqlCreateDeadLineDate);

if (mysqli_num_rows($resultCreateDeadLineDates) > 0) {

    // output data of each row
    while($row = mysqli_fetch_assoc($resultCreateDeadLineDates)) {
        $deadLineDate = $row["deadlineDate"];
        $createDate   = date("Y-m-d", strtotime($row["statusDate"]));
    }

    if ($deadLineDate == '9999-00-00') {
        $dt = new DateTime();
        $dtFmt = $dt->format('Y-m-d');
        $deadLineDate = date('Y-m-d',strtotime($dtFmt . "+1 days"));
    }
} else {
    //echo "0 results";
}

//=====================================SQL PARA PEGAR TODAS AS DATAS DO RANGE $createDate e $deadLineDate
$sqlRangeDates = "select * from
(select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date from
 (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
 (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
 (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
 (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
 (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between \"".$createDate."\" and \"".$deadLineDate."\" ";

$resultRangeDates = mysqli_query($conn, $sqlRangeDates);

// Cria array
$arrayRangeDt = array();
$countDataPlanejamento;

// LÓGICA TRAZER TODAS AS DATAS DO RANGE
if (mysqli_num_rows($resultRangeDates) > 0) {

    // output data of each row
    while($row = mysqli_fetch_assoc($resultRangeDates)) {

        // Insere no array
        $countDataPlanejamento++;
        array_push($arrayRangeDt, date("d-m-Y", strtotime($row["selected_date"])));
    }
} else {
    //echo "0 results";
}
// fecha conexão mysql
mysqli_close($conn);
//MySQL - FIM========================================================================================================================

if ($countDataPlanejamento > $countLancamento) {

    $diferenca = ($countDataPlanejamento - $countLancamento);

    $x = 1;

    while($x <= $diferenca) {
      $horasFenix = "";
      $horasFenix->dataLancada      = "";
      $horasFenix->horasTrabalhadas = "";
      array_push($arrayHF, $horasFenix);
      $x++;
    }
}

// Objeto fenix
$fenix->horasFenix       = $arrayHF;
$fenix->dataPlanejamento = $arrayRangeDt;

$myJSON = json_encode($fenix);
echo $myJSON;
?>