134 lines
3.9 KiB
PHP
134 lines
3.9 KiB
PHP
<?php
|
|
include('includes/config/config.php');
|
|
include('includes/functions.php');
|
|
|
|
header("Content-type: application/octet-stream");
|
|
header("Content-Disposition: attachment; filename=batch_attendence_excel.xls");
|
|
header("Pragma: no-cache");
|
|
header("Expires: 0");
|
|
error_reporting(E_ERROR | E_PARSE);
|
|
|
|
$startDate = date('Y-m-d', strtotime($_POST['startDate_enroll']));
|
|
$endDate = date('Y-m-d', strtotime($_POST['endDate_enroll']));
|
|
$ohc = $_SESSION['current_ohcttype'];
|
|
|
|
$sql = "SELECT pm.*, eb.beneficiary_id, te.batch_id
|
|
FROM patient_master pm
|
|
INNER JOIN training_batch_enrollment_beneficiary eb ON pm.id = eb.beneficiary_id
|
|
INNER JOIN training_batch_enrollment te ON eb.batch_pri_tbl_id = te.id
|
|
WHERE DATE(te.batch_start_date) BETWEEN STR_TO_DATE('2024-01-01', '%Y-%m-%d') AND STR_TO_DATE('2024-12-31', '%Y-%m-%d')
|
|
AND te.ohc_type_id=$ohc";
|
|
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
$batch_id = $_POST['batch_name'];
|
|
$month = $_POST['month'];
|
|
$year = $_POST['year'];
|
|
$months = [
|
|
1 => 'January',
|
|
2 => 'February',
|
|
3 => 'March',
|
|
4 => 'April',
|
|
5 => 'May',
|
|
6 => 'June',
|
|
7 => 'July',
|
|
8 => 'August',
|
|
9 => 'September',
|
|
10 => 'October',
|
|
11 => 'November',
|
|
12 => 'December'
|
|
];
|
|
|
|
$batch_name = getFieldFromTable('batch_name', 'training_batch_master', 'batch_id', $batch_id);
|
|
|
|
echo '<style>
|
|
table {
|
|
border-collapse: collapse;
|
|
width: 100%;
|
|
}
|
|
th, td {
|
|
border: 1px solid black;
|
|
padding: 8px;
|
|
text-align: center;
|
|
}
|
|
th {
|
|
background-color: #A5C3A4;
|
|
}
|
|
</style>';
|
|
|
|
echo '<table width="100%">
|
|
<tr>
|
|
<td width="50%"><strong>Batch Name:</strong> ' . htmlspecialchars($batch_name) . '</td>
|
|
<td width="50%"><strong>Date:</strong> ' . htmlspecialchars($months[$month] . "-" . $year) . '</td>
|
|
</tr>
|
|
</table><br>';
|
|
|
|
echo '<table border="1" cellspacing="0" style="text-align: center; font-size: 12px;">';
|
|
echo '<tr>
|
|
<th>Serial No.</th>
|
|
<th>Name</th>';
|
|
|
|
for ($i = 1; $i <= cal_days_in_month(CAL_GREGORIAN, $month, $year); $i++) {
|
|
echo '<th>' . $i . '</th>';
|
|
}
|
|
echo '<th>T.P</th>
|
|
<th>T.A</th>
|
|
<th>T.L</th>
|
|
<th>A%</th>
|
|
</tr>';
|
|
|
|
$j = 1;
|
|
$p = 0; $a = 0; $l = 0;
|
|
|
|
$result1 = mysqli_query($conn, "SELECT * FROM training_batch_enrollment_beneficiary WHERE batch_id = '$batch_id'");
|
|
|
|
while ($row1 = mysqli_fetch_assoc($result1)) {
|
|
$beneficiary_id = intval($row1['beneficiary_id']);
|
|
$beneficiary_name = getFieldFromTable('patient_name', 'patient_master', 'id', $beneficiary_id);
|
|
|
|
echo '<tr>';
|
|
echo '<td>' . $j . '</td>';
|
|
echo '<td>' . htmlspecialchars($beneficiary_name) . '</td>';
|
|
|
|
$ta = 0; $tp = 0; $tl = 0;
|
|
|
|
for ($k = 1; $k <= 31; $k++) {
|
|
$sql3 = "SELECT a.*, b.*
|
|
FROM beneficiary_attendence a
|
|
LEFT JOIN attendance_batch b ON a.pti_id = b.id
|
|
WHERE MONTH(b.attendence_date) = $month
|
|
AND YEAR(b.attendence_date) = $year
|
|
AND DAY(b.attendence_date) = $k
|
|
AND b.batch_id = $batch_id
|
|
AND a.beneficiary_id = $beneficiary_id";
|
|
|
|
$result3 = mysqli_query($conn, $sql3);
|
|
$row3 = mysqli_fetch_assoc($result3);
|
|
|
|
$status = '';
|
|
if ($row3) {
|
|
$status = $row3['status'] == "Present" ? 'P' : ($row3['status'] == "Absent" ? 'A' : ($row3['status'] == "Leave" ? 'L' : ''));
|
|
if ($row3['status'] == 'Present') {
|
|
$tp++; $p++;
|
|
} elseif ($row3['status'] == 'Absent') {
|
|
$ta++; $a++;
|
|
} elseif ($row3['status'] == 'Leave') {
|
|
$tl++; $l++;
|
|
}
|
|
}
|
|
|
|
echo '<td>' . htmlspecialchars($status) . '</td>';
|
|
}
|
|
|
|
echo '<td>' . $tp . '</td>';
|
|
echo '<td>' . $ta . '</td>';
|
|
echo '<td>' . $tl . '</td>';
|
|
echo '<td>' . number_format(($tp / 31) * 100, 2) . '%</td>';
|
|
echo '</tr>';
|
|
|
|
$j++;
|
|
}
|
|
|
|
echo '</table>';
|
|
?>
|