<?php
session_start();

$nivel_necessario = 6;

// Verifica se não há a variável da sessão que identifica o usuário
if (!isset($_SESSION['UsuarioID']) OR ( $_SESSION['UsuarioNivel'] > $nivel_necessario)) {
    // Destrói a sessão por segurança
    session_destroy();
    // Redireciona o visitante de volta pro login
    header("Location: index.php");
    exit;
}

require_once __DIR__ . DIRECTORY_SEPARATOR . "sealed" . DIRECTORY_SEPARATOR . "autoload.php";

require_once("db.php");


if (!empty($_POST['user'])) {
    $tablet_edit = $_POST['user'];
}

if (!empty($_POST['end'])) {
    $end_edit = $_POST['end'];
    $start_edit = $_POST['start'];
}

// Recuperamos a ação enviada pelo formulário

if (!empty($_POST['a']) && $_POST['a'] == 'pesquisar') {

    $nome = trim($_POST['user']);
    $start = fConvData($_POST['start']);
    $end = fConvData($_POST['end']);
    $sqlConcat = "";

   

    if ($_SESSION['UsuarioNivel'] == 1) {
         if ($nome) {
            $sqlConcat = "AND tablet.cliente = '$nome'";
            $apoio = userDAO:: getidApoio($nome);
            $apoios = FUNCOES::getApoios($apoio['id_apoio']);
           // $sqlConcat.= " AND tablet.id_apoio IN ( " . implode(',', $apoios) . ") ";
             //var_dump($nome);
        }

        $sql = "SELECT DISTINCT entradas.idprod,entrada, COALESCE(saida, 0) as saida \n"
                . "FROM tablet, ponto,\n"
                . "(SELECT SUM(valor) as entrada, idprod FROM transacoes WHERE data_hora  BETWEEN '$start 00:00:00' AND '$end 23:59:59' AND tipo = 1 GROUP BY idprod) entradas LEFT JOIN \n"
                . "(SELECT SUM(valor) as saida, idprod FROM transacoes WHERE data_hora  BETWEEN '$start 00:00:00' AND '$end 23:59:59' AND tipo = 2 GROUP BY idprod) saidas\n"
                . "ON entradas.idprod = saidas.idprod \n"
                . "WHERE entradas.idprod = tablet.idprod $sqlConcat ";

        $result = mysql_query($sql) or die(mysql_error());
    } elseif ($_SESSION['UsuarioNivel'] == 2) {

        if ($nome) {
            $sqlConcat = "AND tablet.cliente LIKE '%$nome%'";
        }
        $sql = "SELECT DISTINCT entradas.idprod,entrada, COALESCE(saida, 0) as saida \n"
                . "FROM tablet,user, ponto,\n"
                . "(SELECT SUM(valor) as entrada, idprod FROM transacoes WHERE data_hora  BETWEEN '$start 00:00:00' AND '$end 23:59:59' AND tipo = 1 GROUP BY idprod) entradas LEFT JOIN \n"
                . "(SELECT SUM(valor) as saida, idprod FROM transacoes WHERE data_hora  BETWEEN '$start 00:00:00' AND '$end 23:59:59' AND tipo = 2 GROUP BY idprod) saidas\n"
                . "ON entradas.idprod = saidas.idprod \n"
                . "WHERE entradas.idprod = tablet.idprod AND tablet.cliente = user.username AND user.id_apoio = " . $_SESSION['UsuarioID'] . " $sqlConcat ";

        $result = mysql_query($sql) or die(mysql_error());
    } elseif ($_SESSION['UsuarioNivel'] == 3) {
        $apoios = FUNCOES::getApoios($_SESSION['UsuarioID']);
        if ($nome) {
            $sqlConcat = "AND ponto.nome LIKE '%$nome%'";
        }
        $sql = "SELECT DISTINCT entradas.idprod,entrada, COALESCE(saida,0) as saida \n"
                . "FROM tablet, ponto,\n"
                . "(SELECT SUM(valor) as entrada, idprod FROM transacoes "
                . " WHERE data_hora  BETWEEN '$start 00:00:00' "
                . " AND '$end 23:59:59' AND tipo = 1 GROUP BY idprod) entradas LEFT JOIN\n"
                . "(SELECT SUM(valor) as saida, idprod "
                . " FROM transacoes WHERE data_hora  BETWEEN '$start 00:00:00' "
                . " AND '$end 23:59:59' AND tipo = 2 GROUP BY idprod) saidas\n"
                . " ON entradas.idprod = saidas.idprod \n"
                . " WHERE entradas.idprod = tablet.idprod "
                . " AND tablet.id_apoio IN ( " . implode(',', $apoios) . ") "
                . " AND tablet.id_ponto = ponto.id $sqlConcat ";

        $result = mysql_query($sql) or die(mysql_error());
    } elseif ($_SESSION['UsuarioNivel'] == 4) {

        if ($nome) {
            $sqlConcat = "AND ponto.nome LIKE '%$nome%'";
        }

        $sql = "SELECT DISTINCT entradas.idprod,entrada, COALESCE(saida,0) as saida \n"
                . "FROM tablet, ponto,\n"
                . "(SELECT SUM(valor) as entrada, idprod FROM transacoes WHERE data_hora  BETWEEN '$start 00:00:00' AND '$end 23:59:59' AND tipo = 1 GROUP BY idprod) entradas LEFT JOIN\n"
                . "(SELECT SUM(valor) as saida, idprod FROM transacoes WHERE data_hora  BETWEEN '$start 00:00:00' AND '$end 23:59:59' AND tipo = 2 GROUP BY idprod) saidas\n"
                . "ON entradas.idprod = saidas.idprod \n"
                . "WHERE entradas.idprod = tablet.idprod AND tablet.id_apoio = " . $_SESSION['UsuarioID'] . " AND tablet.id_ponto = ponto.id $sqlConcat ";

        $result = mysql_query($sql) or die(mysql_error());
    } elseif ($_SESSION['UsuarioNivel'] == 6) {

        if ($nome) {
            $sqlConcat = "AND transacoes.idprod LIKE '%$nome%'";
        }
        $result = mysql_query("SELECT tablet.idprod, valor, tipo,DATE_FORMAT(data_hora,'%d/%m/%Y') data_hora FROM transacoes, tablet WHERE transacoes.idprod = tablet.idprod AND tablet.id_apoio = " . $_SESSION['UsuarioID'] . " AND tipo = '$tipo' AND data_hora BETWEEN '$start 00:00:00' AND '$end 23:59:59' $sqlConcat ORDER BY transacoes.id DESC") or die(mysql_error());

        // if ($nome) {
        //     $sqlConcat = "AND ponto.nome LIKE '%$nome%'";
        // }

        $result = mysql_query("SELECT DISTINCT entradas.idprod,entrada, COALESCE(saida, 0) AS saida
		FROM tablet, user a, ponto, (
			SELECT SUM(valor) AS entrada, idprod
			FROM transacoes
			WHERE data_hora BETWEEN '$start 00:00:00' AND '$end 23:59:59' AND tipo = 1
			GROUP BY idprod
		) entradas
		LEFT JOIN (
			SELECT SUM(valor) AS saida, idprod
			FROM transacoes
			WHERE data_hora BETWEEN '$start 00:00:00' AND '$end 23:59:59' 
			AND tipo = 2
			GROUP BY idprod) saidas 
		ON entradas.idprod = saidas.idprod
		WHERE 
		entradas.idprod = tablet.idprod 
		AND tablet.id_ponto = ponto.id
		AND ponto.id = a.id_apoio
		AND a.id = " . $_SESSION['UsuarioID'] . "
		$sqlConcat")or die(mysql_error());
    } else {

        if ($nome) {
            $sqlConcat = "AND transacoes.idprod LIKE '%$nome%'";
        }

        $result = mysql_query("SELECT tablet.idprod, valor, tipo,DATE_FORMAT(data_hora,'%d/%m/%Y') data_hora FROM transacoes, tablet WHERE transacoes.idprod = tablet.idprod AND tablet.id_apoio = " . $_SESSION['UsuarioID'] . " AND tipo = '$tipo' AND data_hora BETWEEN '$start 00:00:00' AND '$end 23:59:59' $sqlConcat ORDER BY transacoes.id DESC") or die(mysql_error());
    }

//    $totalentrada = 0;
//    $totalsaida = 0;
//    $totalsaldo = 0;

    $numRegistros = mysql_num_rows($result);
    ?>

    <div class="panel-body">

        <?php if ($numRegistros != 0) { ?>

            <div class="table-responsive">
                <table class="table table-condensed">

                    <thead>
                        <tr>
                            <td><strong>Descrição da movimentação</strong></td>
                            <td class="text-center"><strong>Entrada</strong></td>
                            <td class="text-center"><strong>Saida</strong></td>
                            <td class="text-center"><strong>Saldo</strong></td>
                        </tr>
                    </thead>
                    <tbody>
                       
                        <?php
                        while ($row = mysql_fetch_array($result)) {
                          
                            $query = "SELECT  b.moeda_sigla,b.id_pais
					FROM user a INNER JOIN tablet t ON t.id_apoio = a.id
					LEFT JOIN regiao b ON (b.id_pais = a.id_pais AND b.status = 1)
					WHERE 
				        t.idprod = '" . $row['idprod'] . "'";
                            $result0 = mysql_query($query) or die(mysql_error());
                            $rowCifrao = mysql_fetch_array($result0);
                            $cifrao = $rowCifrao ? $rowCifrao['moeda_sigla'] : $_SESSION['cifraoMoeda'];

                            $idpais = $rowCifrao['id_pais'];
                            $totalentrada[$idpais][] = $row['entrada'];
                            $totalsaida[$idpais][] = $row['saida'];
                            $cifrao2[$idpais] = $cifrao;
                            ?>
                            <tr>
                                <td>Tablet <?php echo $row['idprod'] ?></td>
                                <td class="text-center"><?php echo $cifrao . " " . $row['entrada'] ?></td>
                                <td class="text-center"><?php echo $cifrao . " " . $row['saida'] ?></td>
                                <td class="text-center"><?php echo $cifrao . " " . ($row['entrada'] - $row['saida']) ?></td>
                            </tr>
                        <?php }
                       
                        ?>
                        <tr>
                            <td class="emptyrow"></td>
                            <td class="emptyrow"></td>
                            <td class="emptyrow"></td>
                            <td class="emptyrow"></td>
                        </tr>

                        <tr>
                            <td class="highrow"></td>
                            <td class="highrow"></td>
                            <td class="highrow"></td>
                            <td class="highrow"></td>
                        </tr>
                        <?php 
                         foreach($totalentrada  as $key => $value){
                            $totalentrada2 =  array_sum($totalentrada[$key]);
                            $totalsaida2  =  array_sum($totalsaida[$key]);
                            $totalsaldo = $totalentrada2 - $totalsaida2;
                            ?>
                        <tr>
                            <td class="emptyrow"></td>
                            <td class="emptyrow"></td>
                            <td class="emptyrow text-center"><strong>Total Entrada</strong></td>
                            <td class="emptyrow text-right"><?php echo $cifrao2[$key] . " " . $totalentrada2 ?></td>
                        </tr>
                        <tr>
                            <td class="emptyrow"></td>
                            <td class="emptyrow"></td>
                            <td class="emptyrow text-center"><strong>Total Saida</strong></td>
                            <td class="emptyrow text-right"><?php echo $cifrao2[$key] . " " . $totalsaida2 ?></td>
                        </tr>
                        <tr>
                            <td class="emptyrow"><i class="fa fa-barcode iconbig"></i></td>
                            <td class="emptyrow"></td>
                            <td class="emptyrow text-center"><strong>Saldo Total</strong></td>
                            <td class="emptyrow text-right"><?php echo $cifrao2[$key] . " " . $totalsaldo  ?></td>
                        </tr>
                        <?php 
                           $totalsaldo = 0;
                         }?>
                    </tbody>
                </table>
            </div>


        <?php } else {
            ?>

            <div class='alert alert-warning alert-dismissable'>
                <button type='button' class='close' data-dismiss='alert' aria-hidden='true'>&times;</button>
                Nenhuma movimentação foi encontrada! </div>
            <?php
        }
        ?>

    </div>
    <!-- /. Fatura -->

    <?php
}
?>