<?php if (Session::get("tipo_usuario") != "1"){ show_403();} ?>
<?php import_file(DIR_TEMPLATES."header.php", ["title" => "Relatório de Cadastro de Usuário", "active_page" => "meu-time"]); ?>
<?php import_file(DIR_MODELS."Model.class.php"); ?>

<?php 

	$model = new Model();

	$data_inicio_mes  = date("Y-m-01");
	$data_termino_mes = date("Y-m-t");

	$data_atual = date("Y-m-d");

	$data_7dias = date("Y-m-d", strtotime("-7 day", strtotime($data_atual)));

	$result = $model->query("
		SELECT 
			(
				SELECT 
					COUNT(*) AS total
				FROM usuario usr 
				WHERE 
					DATE_FORMAT(usr.datahora_cadastro_usuario, '%Y-%m-%d') BETWEEN :data_inicio_mes AND :data_termino_mes
			) AS quantidade_cadastro_30_dias,
			(
				SELECT 
					COUNT(*) AS total
				FROM usuario usr 
				WHERE 
					DATE_FORMAT(usr.datahora_cadastro_usuario, '%Y-%m-%d') BETWEEN :data_7dias AND :data_atual
			) AS quantidade_cadastro_07_dias,
			(
				SELECT 
					COUNT(*) AS total
				FROM usuario usr 
				WHERE 
					DATE_FORMAT(usr.datahora_cadastro_usuario, '%Y-%m-%d') = :data_atual
			) AS quantidade_cadastro_hoje
	", [
		":data_inicio_mes" => $data_inicio_mes,
		":data_termino_mes" => $data_termino_mes,
		":data_atual" => $data_atual,
		":data_7dias" => $data_7dias
	])->fetch();

?>

<h4 class="mb-3">
	<i class="fa-regular fa-file-alt"></i> Relatório de Cadastro de Usuário
</h4>

<div class="form-row">
	<div class="col-md-4">
		<div class="content-box mb-3">
			<h3 class="text-center">
				<small>Nº Cadastro 30 dias</small><br>
				<?php echo intval($result->quantidade_cadastro_30_dias); ?>
			</h3>
		</div>
	</div>

	<div class="col-md-4">
		<div class="content-box mb-3">
			<h3 class="text-center">
				<small>Nº Cadastro 7 dias</small><br>
				<?php echo intval($result->quantidade_cadastro_07_dias); ?>
			</h3>
		</div>
	</div>

	<div class="col-md-4">
		<div class="content-box mb-3">
			<h3 class="text-center">
				<small>Nº Cadastro Hoje</small><br>
				<?php echo intval($result->quantidade_cadastro_hoje); ?>
			</h3>
		</div>
	</div>
</div>

<div class="content-box">
	<?php 

		$page = !empty($_GET["page"]) ? (int) filter_input(INPUT_GET, "page", FILTER_SANITIZE_NUMBER_INT) : 1;

		$length = 20;
		$start = intval(max(($page - 1), 0) * $length);

		$result = $model->query("
			SELECT * FROM (
				SELECT 
					COUNT(*) AS quatidade,
					usr2.*
				FROM usuario usr 
				INNER JOIN usuario usr2 ON (usr.id_indicador = usr2.id_usuario)
				GROUP BY
					usr.id_indicador 
			) tbv 
			ORDER BY
				tbv.quatidade DESC,
				tbv.datahora_cadastro_usuario ASC
			LIMIT 
				:length OFFSET :start
		", [
			":start" => $start,
			":length" => $length
		]);

		$count = ($start + 1);

	?>

	<h5 class="mb-3">Top 20 Cadastradores</h5>

	<div class="table-responsive">
		<table class="table table-sm table-striped table-hover">
			<thead>
				<tr>
					<th class="text-center align-middle" style="width: 5%">#</th>
					<th class="text-center align-middle" style="width: 10%">ID</th>
					<th class="text-left align-middle" style="width: 75%">Usuário</th>
					<th class="text-center align-middle" style="width: 10%">Quantidade Cadastrada</th>
				</tr>
			</thead>
			<tbody>
				<?php foreach ($result->fetchAll() as $i): ?>
					<tr>
						<td class="text-center align-middle"><?php echo $count++; ?></td>
						<td class="text-center align-middle"><?php echo $i->id_usuario; ?></td>
						<td class="text-left align-middle">
							<?php 

								echo "
									{$i->nome_usuario}<br>
									<small>{$i->cpf_usuario}</small><br>
									<small>{$i->email_usuario} | {$i->telefone_usuario}</small>
								"; 

							?>
						</td>
						<td class="text-center align-middle"><?php echo $i->quatidade; ?></td>
					</tr>
				<?php endforeach; ?>
			</tbody>
		</table>
	</div>

	<ul class="pagination justify-content-center" style="margin-bottom: -15px;">
		<?php

			$result = $model->query("
				SELECT 
					COUNT(*) AS total
				FROM (
					SELECT 
						COUNT(*) AS quatidade,
						usr2.*
					FROM usuario usr 
					INNER JOIN usuario usr2 ON (usr.id_indicador = usr2.id_usuario)
					GROUP BY
						usr.id_indicador 
				) tbv 
			")->fetch();

			$total = $result->total;

			$total_pages = ceil($total/$length);
						   
			if ($page <= $total_pages and $page > 1) {
 				echo '
 					<li class="page-item">
 						<a href="?page='.($page-1).'" class="page-link">
 							<span aria-hidden="true">&laquo;</span>
 						</a>
 					</li>
 				';
			}

			echo '
				<li class="page-item active">
					<p class="page-link">'.$page.'</p>
				</li>
			';

			if ($page < $total_pages) {	
				echo '
					<li class="page-item">
						<a href="?page='.($page+1).'" class="page-link">
							<span aria-hidden="true">&raquo;</span>
						</a>
					</li>
				';
			}
			
		?>
	</ul>
</div>

<?php import_file(DIR_TEMPLATES."footer.php"); ?>