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

if (!auth::user()) {
    endrequest("You dont have access to this api");
}

$data["user"] = auth::user_id();
$data['y'] = $_GET['year'];
$data['m'] = $_GET['month'];

$response['input'] = $_GET;

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

$sql = "SELECT 
    l.*,
    DATE_FORMAT(l.timestamp, '%H:%i') as time,
    DATE_FORMAT(l.date, '%y %b %d') as date,
    DATE_FORMAT(l.received, '%b') as received_month,
    DATE_FORMAT(l.received, '%d') as received_date,
    u.name as user_name, 
    u.id as user_id,
    IF(s.name IS NULL, l.sender2, s.name) as sender_name,
    f.number as file_number,
    f.name as file_name,
    src.name as source_name,
    states.name as state_name,
    l.state as state
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 mis_files as f on l.file = f.id
LEFT JOIN mis_letter_states as states on l.state = states.id
WHERE 
    year(l.received) = :y and
    month(l.received) = :m and
    l.assigned = :user
    order by l.id desc ";

$response['sql'] = $sql;


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

foreach ($response["data"] as $key => $value) {
    $response["data"][$key]["notes"] = notes::byletter($value["id"]);
}

echo json_encode($response);
