<?php
require("../connect.php");

$postvalue = json_decode(file_get_contents('php://input'));

$queryAttendanceReport = "SELECT DISTINCT emp.field_309 AS EMP, FROM_UNIXTIME(attin.date_added, '%Y-%m-%d') AS DATEADDED, field_1585 AS LOCATION, 
field_1591 AS FILENAME, CONCAT(FROM_UNIXTIME(attin.field_1570, '%Y-%m-%d'), ' ', RIGHT(SEC_TO_TIME(attin.field_1586), 5)) AS ATTDATE,
(SELECT `name` FROM app_global_lists_choices WHERE id = (SELECT field_653 FROM app_entity_27 WHERE parent_item_id = attin.parent_item_id AND FROM_UNIXTIME(field_243, '%Y-%m-%d') = '".$postvalue->attdate."' ORDER BY id DESC LIMIT 1)) AS ATTCODE,
'ABSEN MASUK' AS ATTTYPE FROM app_entity_81 attin INNER JOIN app_entity_31 emp ON attin.parent_item_id = emp.id 
WHERE FROM_UNIXTIME(attin.field_1570, '%Y-%m-%d') = '".$postvalue->attdate."' AND attin.field_1577 = '".$postvalue->section."'

UNION ALL

SELECT DISTINCT emp.field_309 AS EMP, FROM_UNIXTIME(attout.date_added, '%Y-%m-%d') AS DATEADDED, field_1582 AS LOCATION, 
field_1592 AS FILENAME, CONCAT(FROM_UNIXTIME(attout.field_1570, '%Y-%m-%d'), ' ', RIGHT(SEC_TO_TIME(attout.field_1587), 5)) AS ATTDATE,
(SELECT `name` FROM app_global_lists_choices WHERE id = (SELECT field_838 FROM app_entity_60 WHERE parent_item_id = attout.parent_item_id AND FROM_UNIXTIME(field_828, '%Y-%m-%d') = '".$postvalue->attdate."' ORDER BY id DESC LIMIT 1)) AS ATTCODE,
'ABSEN PULANG' AS ATTTYPE FROM app_entity_81 attout INNER JOIN app_entity_31 emp ON attout.parent_item_id = emp.id 
WHERE FROM_UNIXTIME(attout.field_1570, '%Y-%m-%d') = '".$postvalue->attdate."'  AND attout.field_1577 = '".$postvalue->section."';";

// Data Transport Receipt
$stmtAttendanceReport = mysqli_query($conn, $queryAttendanceReport);

$dataAttendance = array();
while ($rowAttendanceReport = mysqli_fetch_assoc($stmtAttendanceReport)) {
    
    $imageURL = "../../../sls/uploads/attachments/" // URL 
        .substr($rowAttendanceReport['DATEADDED'], 0, 4). "/" // Year
        .substr($rowAttendanceReport['DATEADDED'], 5, 2). "/" // Month
        .substr($rowAttendanceReport['DATEADDED'], 8, 2). "/"; // File Name
    
    $imgAttendance = file_get_contents($imageURL.$rowAttendanceReport['FILENAME']);
    
	$nestedAtt[] = array_map('utf8_encode', array(
    	"EMP" => $rowAttendanceReport['EMP'],
    	"LOCATION" => $rowAttendanceReport['LOCATION'], 
    	"ATTDATE" => $rowAttendanceReport['ATTDATE'],
    	"ATTCODE" => $rowAttendanceReport['ATTCODE'],
    	"ATTTYPE" => $rowAttendanceReport['ATTTYPE'],
    	"EMPIMG" => base64_encode($imgAttendance)
	));

	$dataAttendance = array("ATTENDANCEREPORT" => $nestedAtt);
    
}

echo json_encode($dataAttendance);

?>