223 lines
6.7 KiB
PHP
223 lines
6.7 KiB
PHP
<?php
|
|
include('includes/config/config.php');
|
|
include('includes/functions.php');
|
|
|
|
$ohc = $_POST['rr_ohc'];
|
|
$year = $_POST['rr_year'];
|
|
$type = isset($_GET['type']) ? $_GET['type'] : ''; // Get the type parameter
|
|
error_log("Check_type : " . $type);
|
|
if ($type === 'excel') {
|
|
header("Content-Type: application/vnd.ms-excel");
|
|
header("Content-Disposition: attachment; filename=retention_data_report.xls");
|
|
header("Pragma: no-cache");
|
|
header("Expires: 0");
|
|
}
|
|
?>
|
|
<link href="includes/css-js/admin.css" rel="stylesheet" type="text/css" />
|
|
<style>
|
|
@page {
|
|
margin: 15px;
|
|
}
|
|
|
|
body {
|
|
padding: 10px;
|
|
font-family: Arial, sans-serif;
|
|
background-color: #f9f9f9;
|
|
}
|
|
|
|
.btn {
|
|
background-color: #4CAF50;
|
|
border-radius: 5%;
|
|
border: none;
|
|
color: white;
|
|
padding: 5px 8px;
|
|
text-align: center;
|
|
text-decoration: none;
|
|
display: inline-block;
|
|
font-size: 12px;
|
|
margin: 4px 2px;
|
|
cursor: pointer;
|
|
}
|
|
|
|
@media print {
|
|
#printPageButton {
|
|
display: none;
|
|
}
|
|
}
|
|
|
|
h2 {
|
|
font-size: 24px;
|
|
margin: 0;
|
|
}
|
|
|
|
table {
|
|
width: 100%;
|
|
border-collapse: collapse;
|
|
margin-top: 20px;
|
|
background-color: #fff;
|
|
}
|
|
|
|
th,
|
|
td {
|
|
padding: 10px;
|
|
text-align: left;
|
|
}
|
|
|
|
th {
|
|
background-color: #eeeeee;
|
|
font-size: 16px;
|
|
}
|
|
|
|
td {
|
|
font-size: 14px;
|
|
}
|
|
|
|
.highlight {
|
|
background-color: #e9f5ff;
|
|
border-radius: 5px;
|
|
padding: 5px;
|
|
}
|
|
|
|
table:last-of-type {
|
|
border: 1px solid #ddd;
|
|
}
|
|
|
|
table:last-of-type th,
|
|
table:last-of-type td {
|
|
border: 1px solid #ddd;
|
|
}
|
|
</style>
|
|
|
|
|
|
<body>
|
|
<?php
|
|
|
|
if ($type === 'excel') {
|
|
} else {
|
|
include('pdf_current_ohc_header.php'); }
|
|
?>
|
|
<div style="text-align: center; margin-bottom: 20px;">
|
|
<h2>Retention Report</h2>
|
|
</div>
|
|
<table border="0">
|
|
<tr>
|
|
<td>
|
|
<strong>Year: <?= htmlspecialchars($year . '-' . ($year + 1)) ?></strong>
|
|
</td>
|
|
<td style="text-align: right;">
|
|
<strong>Selected Location: <?= htmlspecialchars(getFieldFromTable('ohc_type_name', 'ohc_type', 'ohc_type_id', $ohc)) ?></strong>
|
|
</td>
|
|
</tr>
|
|
</table>
|
|
|
|
<table border="1">
|
|
<thead>
|
|
<tr>
|
|
<th>Sr.No</th>
|
|
<th>Batch Name</th>
|
|
<th>Closing Date</th>
|
|
<th>Total Trainees</th>
|
|
<th>Placed</th>
|
|
<th>% Placed</th>
|
|
<th>Retention</th>
|
|
<th>Unplaced</th>
|
|
<th>Not Retained</th>
|
|
<th>% Not Retained</th>
|
|
<th>Not Retained + Unplaced</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php
|
|
$sql = "SELECT * FROM training_batch_master WHERE YEAR(start_date) >= '$year' AND YEAR(end_date) <= '$year' AND ohc_type_id = '$ohc'";
|
|
$result = mysqli_query($conn, $sql);
|
|
$count = 1;
|
|
|
|
while ($row = mysqli_fetch_assoc($result)) {
|
|
$batchId = $row['batch_id'];
|
|
$total = getCount('training_batch_enrollment_beneficiary', 'batch_id', $batchId, '');
|
|
$placed = getCount('placement_records', 'batch_name', $batchId, 'Placed');
|
|
$unplaced = getCount('placement_records', 'batch_name', $batchId, 'Unplaced');
|
|
$not_retained = getCount2('placement_feedback', 'batch_id', $batchId, 'follow_up_routine', 'First Follow Up', 'promotion', 'Not Retained');
|
|
|
|
// Calculate values
|
|
$notRetainedPlusUnplaced = $not_retained + $unplaced;
|
|
$retention = $placed; // Assuming retention is equal to the number placed
|
|
// Percentage Calculations
|
|
$placedPercentage = $total > 0 ? ($placed / $total) * 100 : 0;
|
|
$notRetainedPercentage = $total > 0 ? ($not_retained / $total) * 100 : 0;
|
|
|
|
echo "<tr>
|
|
<td>" . $count++ . "</td>
|
|
<td>" . htmlspecialchars($row['batch_name']) . "</td>
|
|
<td>" . htmlspecialchars(date('d-m-Y', strtotime($row['end_date']))) . "</td>
|
|
<td>$total</td>
|
|
<td>$placed</td>
|
|
<td>" . round($placedPercentage) . "%</td>
|
|
<td>$retention</td>
|
|
<td>$unplaced</td>
|
|
<td>$not_retained</td>
|
|
<td>" . round($notRetainedPercentage) . "%</td>
|
|
<td>$notRetainedPlusUnplaced</td>
|
|
</tr>";
|
|
}
|
|
?>
|
|
</tbody>
|
|
</table>
|
|
|
|
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
|
|
<script>
|
|
// Uncomment below to enable print on load
|
|
// $(document).ready(function() {
|
|
// window.print();
|
|
// });
|
|
</script>
|
|
</body>
|
|
|
|
<?php
|
|
function getCount($table, $idfield, $id, $status)
|
|
{
|
|
global $conn;
|
|
|
|
$table = mysqli_real_escape_string($conn, $table);
|
|
$id = mysqli_real_escape_string($conn, $id);
|
|
$idfield = mysqli_real_escape_string($conn, $idfield);
|
|
$status = mysqli_real_escape_string($conn, $status);
|
|
|
|
$sql = "SELECT COUNT(*) AS count FROM $table WHERE $idfield = '$id'" . ($status ? " AND status = '$status'" : "");
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
return $result ? (int)mysqli_fetch_assoc($result)['count'] : 0;
|
|
}
|
|
|
|
function getCount2($table, $idfield1, $id1, $idfield2, $id2, $idfield3, $id3)
|
|
{
|
|
global $conn;
|
|
|
|
// Escape inputs to prevent SQL injection
|
|
$table = mysqli_real_escape_string($conn, $table);
|
|
$id1 = mysqli_real_escape_string($conn, $id1);
|
|
$idfield1 = mysqli_real_escape_string($conn, $idfield1);
|
|
$id2 = mysqli_real_escape_string($conn, $id2);
|
|
$idfield2 = mysqli_real_escape_string($conn, $idfield2);
|
|
$idfield3 = mysqli_real_escape_string($conn, $idfield3);
|
|
$id3 = mysqli_real_escape_string($conn, $id3);
|
|
|
|
// Check if $id3 is empty using the correct comparison operator
|
|
if ($id3 == '') {
|
|
$sql = "SELECT COUNT(*) AS count FROM $table WHERE $idfield1 = '$id1' AND $idfield2 = '$id2'";
|
|
} else {
|
|
$sql = "SELECT COUNT(*) AS count FROM $table WHERE $idfield1 = '$id1' AND $idfield2 = '$id2' AND $idfield3 = '$id3'";
|
|
}
|
|
|
|
error_log("check_get_count2_function_query: " . $sql);
|
|
|
|
// Execute the query and handle potential errors
|
|
$result = mysqli_query($conn, $sql);
|
|
if (!$result) {
|
|
error_log("Query error: " . mysqli_error($conn));
|
|
return 0; // Return 0 if there's an error
|
|
}
|
|
|
|
return (int)mysqli_fetch_assoc($result)['count'];
|
|
}
|
|
?>
|