hpr_hub/cms/schedule.php

165 lines
5.6 KiB
PHP
Raw Permalink Normal View History

2023-07-02 14:47:44 +00:00
<?php
header('Content-Type: application/json');
require "/home/hpr/php/include.php";
$pos = strpos($_SERVER['REQUEST_URI'], '?');
if ( ! $pos === false) {
header("Status: 412 Precondition Failed");
include '412.shtml';
file_put_contents($naughtyfile, getUserIP(), FILE_APPEND | LOCK_EX );
exit;
}
// --------------------------------------------
// Clean up stale reservations
$ip = $_SERVER["REMOTE_ADDR"];
# Remove any stale requests.
# This should be enough to deter attackers while been short enough to allow real hosts to request a show.
$query_delete_old = "DELETE
FROM
reservations
WHERE
reservations.timestamp + INTERVAL 1 HOUR <= UTC_TIMESTAMP()
AND reservations.status = 'REQUEST_UNVERIFIED'";
$result_delete_old = @mysqli_query($connection, $query_delete_old);
logextra( "Deleting requests older than 1 hour" . $result_delete_old );
# Remove stale requests from this IP Address after 15 minutes.
# This should be enough to deter attackers while been short enough to allow real hosts to request a show.
$query_delete = "DELETE FROM reservations WHERE reservations.ip = '$ip' AND reservations.timestamp + INTERVAL 15 MINUTE <= UTC_TIMESTAMP() AND reservations.status = 'REQUEST_UNVERIFIED'";
$result_delete = @mysqli_query($connection, $query_delete);
logextra( "Remove stale requests from this \"${ip}\" IP Address after 15 minutes. " . $result_delete_old );
$query = mysqli_query($connection, "SELECT max(date), max(id) from eps WHERE eps.date <= UTC_DATE()");
$current_episode_array = mysqli_fetch_row($query);
$current_episode_date = $current_episode_array[0];
$current_episode_number = $current_episode_array[1];
// --------------------------------------------
// Populate array with future shows and reservations
$show_array = array ();
// REQUEST_UNVERIFIED → SHOW_SUBMITTED → METADATA_PROCESSED → SHOW_POSTED → MEDIA_TRANSCODED → UPLOADED_TO_IA → UPLOADED_TO_RSYNC_NET
$ep_retrieve = "SELECT hosts.host, eps.id, eps.title, eps.date FROM eps, hosts WHERE eps.valid=1 AND eps.hostid = hosts.hostid AND eps.date >= '$current_episode_date' ORDER BY date DESC";
$ep_retrieve = "SELECT
hosts.host,
eps.id,
eps.title,
eps.date,
COUNT( assets.extension) AS numfiles
FROM
eps
LEFT JOIN hosts ON eps.hostid = hosts.hostid
LEFT JOIN assets ON eps.id = assets.episode_id
WHERE
eps.valid = 1
AND eps.date >= '$current_episode_date'
GROUP BY eps.id;";
if ($result = mysqli_query($connection, $ep_retrieve)) {
while ($row = mysqli_fetch_array($result)) {
$id = $row['id'];
$date = $row['date'];
$title = $row['title'];
$host = $row['host'];
$numfiles = $row['numfiles'];
if( isset($numfiles) and $numfiles >= 3 ) {
$status = "Finished";
}
else {
$status = "Reserved";
}
$show_array[$id] = array ( "date" => date('Y-m-d', strtotime($date) ),
"title" => $title,
"host" => $host,
"status" => $status
);
}
}
// REQUEST_UNVERIFIED → SHOW_SUBMITTED → METADATA_PROCESSED → SHOW_POSTED → MEDIA_TRANSCODED → UPLOADED_TO_IA → UPLOADED_TO_RSYNC_NET
// Populate array with currently processing shows EMAIL_LINK_CLICKED
$ep_retrieve = "
SELECT
reservations.ep_num,
reservations.ep_date,
reservations.status
FROM
reservations
WHERE
reservations.verified = 1
AND reservations.ep_date >= '$current_episode_date'
ORDER BY
reservations.ep_date DESC";
if ($result = mysqli_query($connection, $ep_retrieve)) {
while ($row = mysqli_fetch_array($result)) {
$id = $row['ep_num'];
$date = $row['ep_date'];
$status = $row['status'];
$show_array[$id] = array ( "date" => date('Y-m-d', strtotime($date) ),
"title" => $status,
"host" => "Unverified",
"status" => "Processing"
);
}
}
// Populate array with temporary reservations.
$ep_retrieve = "SELECT r.ep_num, r.ep_date, r.timestamp + INTERVAL 1 HOUR - UTC_TIMESTAMP() AS seconds_to_expiration FROM reservations r
WHERE r.timestamp + INTERVAL 1 HOUR > UTC_TIMESTAMP() AND r.verified =0 AND r.ep_date >= '$current_episode_date' ORDER BY r.ep_date DESC";
if ($result = mysqli_query($connection, $ep_retrieve)) {
while ($row = mysqli_fetch_array($result)) {
$id = $row['ep_num'];
$date = $row['ep_date'];
$seconds_to_expiration = $row['seconds_to_expiration'];
$minutes = floor($seconds_to_expiration / 60) + 1;
$show_array[$id] = array ( "date" => date('Y-m-d', strtotime($date) ),
"title" => " Available again in $minutes minutes",
"host" => "Unverified",
"status" => "Locked"
);
}
}
$ep_retrieve = "SELECT
hosts.host,
eps.id,
eps.title,
eps.date
FROM
eps,
hosts,
assets
WHERE
eps.valid = 1
AND eps.hostid = hosts.hostid
AND eps.id = assets.episode_id
AND assets.extension = 'ogg'
AND eps.date >= '$current_episode_date'
ORDER BY
date DESC";
if ($result = mysqli_query($connection, $ep_retrieve)) {
while ($row = mysqli_fetch_array($result)) {
$id = $row['id'];
$date = $row['date'];
$title = $row['title'];
$host = $row['host'];
$show_array[$id] = array ( "date" => date('Y-m-d', strtotime($date) ),
"title" => $title,
"host" => $host,
"status" => "Finished"
);
}
}
echo json_encode($show_array, JSON_FORCE_OBJECT);
?>