<?php
include "../src/config.php";

if (!auth::user()) {
    endrequest("You dont have access to this api");
}
$response['input'] = $_GET;

if (!isset($_GET['year']) || !isset($_GET['month']) || !isset($_GET['day'])) {
    endrequest("not enough inputs");
}

$sql = "SELECT 
    l.*,
    DATE_FORMAT(l.timestamp, '%H:%i') as time,
    DATE_FORMAT(l.date, '%y %b %d') as date,
    u.name as user_name, 
    u.id as user_id,
    IF(s.name IS NULL, l.sender2, s.name) as sender_name,
    f.codename as forwarded_codename,
    f.shortname as forwarded_shortname,
    a.shortname as assigned_shortname,
    a.code as assigned_code,
    src.name as source_name
FROM 
    mis_letters as l
INNER JOIN users as u on l.user = u.id
INNER JOIN mis_letter_sources as src on l.source = src.id
LEFT JOIN institutions as s on l.sender = s.id
LEFT JOIN institutions as f on l.forwarded = f.id
LEFT JOIN users as a on l.assigned = a.id
WHERE 
    year(l.received) = :y and
    month(l.received) = :m and
    day(l.received) = :d
    order by l.id";

$response['sql'] = $sql;

$data['y'] = $_GET['year'];
$data['m'] = $_GET['month'];
$data['d'] = $_GET['day'];

$response = datatable::query($sql, $data);

echo json_encode($response);
