<?php
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Staff Attendance Report</title>
<style>
table {
width: 100%;
border-collapse: collapse;
}
th, td {
border: 1px solid black;
padding: 8px;
text-align: center;
}
th {
background-color: #f2f2f2;
}
.approval-sections {
display: flex;
justify-content: space-between;
margin-top: 40px;
}
.sign-off-section {
width: 48%;
}
.sign-off {
margin-top: 20px;
}
.sign-off label {
display: block;
margin-bottom: 5px;
}
.sign-off input {
width: 100%;
padding: 8px;
margin-bottom: 10px;
box-sizing: border-box;
}
.signature-pad {
border: 1px solid #000;
width: 100%;
height: 200px;
}
.clear-btn {
margin-top: 10px;
}
</style>
</head>
<body>
<img width = "350px" height = "80px" src = "/Images/IES_Houtbay.jpg"/>
<h2 style="text-align: center;">STAFF ATTENDANCE REPORT</h2>
<h2>Week Ranging from :</h2>
<p>
<?php
function getWeekStartEndDates() {
// Get the current date
$currentDate = new DateTime();
// Get the Monday of the current week
$monday = clone $currentDate;
$monday->modify('Monday this week');
// Get the Friday of the current week
$friday = clone $monday;
$friday->modify('+4 days');
return [$monday->format('l, Y-m-d'), $friday->format('l, Y-m-d')];
}
// Get the Monday and Friday dates
list($mondayDate, $fridayDate) = getWeekStartEndDates();
?>
<?php echo "$mondayDate - $fridayDate"; ?>
</p>
<table>
<thead>
<tr>
<th rowspan="2">#</th>
<th rowspan="2">Staff Member</th>
<th rowspan="2">Department</th>
<th colspan="2">Monday</th>
<th colspan="2">Tuesday</th>
<th colspan="2">Wednesday</th>
<th colspan="2">Thursday</th>
<th colspan="2">Friday</th>
</tr>
<tr>
<th>Clock-in</th>
<th>Clock-out</th>
<th>Clock-in</th>
<th>Clock-out</th>
<th>Clock-in</th>
<th>Clock-out</th>
<th>Clock-in</th>
<th>Clock-out</th>
<th>Clock-in</th>
<th>Clock-out</th>
</tr>
</thead>
<tbody>
<?php
// Database connection details
$serverName = "41.193.49.82:1433"; // Use comma (,) instead of colon (:) for port
$connectionOptions = array(
"Database" => "TJ_Main_Data_HBIS",
"UID" => "edadmin",
"PWD" => "1@m!Known",
"CharacterSet" => "UTF-8"
);
// Connect to SQL Server
$conn = mssql_connect($serverName, $connectionOptions);
if (!$conn) {
die("Connection failed: " . print_r(sqlsrv_errors(), true));
}
// Get start and end of the week (Monday - Friday)
$startOfWeek = date('Y-m-d', strtotime('monday this week'));
$endOfWeek = date('Y-m-d', strtotime('friday this week'));
// SQL Query for Attendance Report
$sql = "
WITH RankedEntries AS (
SELECT
PersonID AS [Biometric Staff Code],
Per_Name AS [Staff Member],
Department,
CAST(AttStamp AS DATE) AS [Date],
CAST(AttStamp AS TIME) AS [Time],
CASE WHEN AttdirectionBio = 1 THEN 'IN' ELSE 'OUT' END AS [IN/OUT],
ROW_NUMBER() OVER (PARTITION BY PersonID, CAST(AttStamp AS DATE) ORDER BY AttStamp ASC) AS RN_First,
ROW_NUMBER() OVER (PARTITION BY PersonID, CAST(AttStamp AS DATE) ORDER BY AttStamp DESC) AS RN_Last
FROM GKAPP
WHERE AttStamp >= ? AND AttStamp <= ?
)
SELECT
[Biometric Staff Code],
[Staff Member],
Department,
MIN(CASE WHEN [Date] = ? AND [IN/OUT] = 'IN' THEN [Time] END) AS [Monday Clock-in],
MAX(CASE WHEN [Date] = ? AND [IN/OUT] = 'OUT' THEN [Time] END) AS [Monday Clock-out],
MIN(CASE WHEN [Date] = DATEADD(DAY, 1, ?) AND [IN/OUT] = 'IN' THEN [Time] END) AS [Tuesday Clock-in],
MAX(CASE WHEN [Date] = DATEADD(DAY, 1, ?) AND [IN/OUT] = 'OUT' THEN [Time] END) AS [Tuesday Clock-out],
MIN(CASE WHEN [Date] = DATEADD(DAY, 2, ?) AND [IN/OUT] = 'IN' THEN [Time] END) AS [Wednesday Clock-in],
MAX(CASE WHEN [Date] = DATEADD(DAY, 2, ?) AND [IN/OUT] = 'OUT' THEN [Time] END) AS [Wednesday Clock-out],
MIN(CASE WHEN [Date] = DATEADD(DAY, 3, ?) AND [IN/OUT] = 'IN' THEN [Time] END) AS [Thursday Clock-in],
MAX(CASE WHEN [Date] = DATEADD(DAY, 3, ?) AND [IN/OUT] = 'OUT' THEN [Time] END) AS [Thursday Clock-out],
MIN(CASE WHEN [Date] = DATEADD(DAY, 4, ?) AND [IN/OUT] = 'IN' THEN [Time] END) AS [Friday Clock-in],
MAX(CASE WHEN [Date] = DATEADD(DAY, 4, ?) AND [IN/OUT] = 'OUT' THEN [Time] END) AS [Friday Clock-out]
FROM RankedEntries
GROUP BY [Biometric Staff Code], [Staff Member], Department
ORDER BY [Biometric Staff Code];";
// Execute the query with parameters
$params = array($startOfWeek, $endOfWeek, $startOfWeek, $startOfWeek, $startOfWeek, $startOfWeek, $startOfWeek, $startOfWeek, $startOfWeek, $startOfWeek, $startOfWeek, $startOfWeek);
$stmt = sqlsrv_query($conn, $sql, $params);
if ($stmt === false) {
die("SQL Error: " . print_r(sqlsrv_errors(), true));
}
// Display results in table
$count = 1;
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo "<tr>";
echo "<td>" . $count++ . "</td>";
echo "<td>" . htmlspecialchars($row["Staff Member"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Department"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Monday Clock-in"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Monday Clock-out"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Tuesday Clock-in"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Tuesday Clock-out"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Wednesday Clock-in"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Wednesday Clock-out"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Thursday Clock-in"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Thursday Clock-out"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Friday Clock-in"]) . "</td>";
echo "<td>" . htmlspecialchars($row["Friday Clock-out"]) . "</td>";
echo "</tr>";
}
// Close connection
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>
</tbody>
</table>
<div class="container">
<h2>Document Approval Form</h2>
<p>This document requires the approval of the Principal and the IES Controller. Please provide your details and signatures below.</p>
<div class="approval-sections">
<div class="sign-off-section">
<h3>Principal Approval</h3>
<div class="sign-off">
<label for="principal-name">Name:</label>
<input type="text" id="principal-name" name="principal-name">
<label for="principal-signature">Signature:</label>
<canvas id="principal-signature-pad" class="signature-pad"></canvas>
<button class="clear-btn" onclick="clearSignature('principal-signature-pad')">Clear Signature</button>
<label for="principal-date">Date:</label>
<input type="date" id="principal-date" name="principal-date">
</div>
</div>
<div class="sign-off-section">
<h3>IES Controller Approval</h3>
<div class="sign-off">
<label for="ies-name">Name:</label>
<input type="text" id="ies-name" name="ies-name">
<label for="ies-signature">Signature:</label>
<canvas id="ies-signature-pad" class="signature-pad"></canvas>
<button class="clear-btn" onclick="clearSignature('ies-signature-pad')">Clear Signature</button>
<label for="ies-date">Date:</label>
<input type="date" id="ies-date" name="ies-date">
</div>
</div>
</div>
<!-- Print Button -->
<button class="print-btn" onclick="window.print()">Print this page</button>
</div>
<!-- Include the Signature Pad library -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/signature_pad/1.3.5/signature_pad.min.js"></script>
<script>
// Initialize Signature Pads
const principalCanvas = document.getElementById('principal-signature-pad');
const principalSignaturePad = new SignaturePad(principalCanvas);
const iesCanvas = document.getElementById('ies-signature-pad');
const iesSignaturePad = new SignaturePad(iesCanvas);
// Function to clear the signature pad
function clearSignature(padId) {
const canvas = document.getElementById(padId);
const signaturePad = new SignaturePad(canvas);
signaturePad.clear();
}
</script>
</body>
</html>