public static function ajax_delete()
{
global $wpdb;
$send_response = function ($todo) use($wpdb) {
if (!$todo) {
// free disk space
$wpdb->query('OPTIMIZE TABLE ' . \Podlove\Model\DownloadIntent::table_name());
// clear caches
\Podlove\Cache\TemplateCache::get_instance()->setup_purge();
// mark migration as done
delete_option('podlove_tracking_delete_head_requests');
}
\Podlove\AJAX\Ajax::respond_with_json(array('todo' => $todo));
};
// get user agent IDs to delete
$sql = "\n\t\t\tSELECT\n\t\t\t\tid \n\t\t\tFROM\n\t\t\t\t" . \Podlove\Model\UserAgent::table_name() . " ua\n\t\t\tWHERE\n\t\t\t\tuser_agent LIKE \"libwww-perl/%\" \n\t\t\t\tOR user_agent LIKE \"curl/%\" \n\t\t\t\tOR user_agent LIKE \"PritTorrent/%\"\n\t\t";
$user_agent_ids = $wpdb->get_col($sql);
if (!count($user_agent_ids)) {
$send_response(0);
}
// delete
$sql = "\n\t\tDELETE\n\t\t\tFROM " . \Podlove\Model\DownloadIntent::table_name() . "\n\t\t\tWHERE user_agent_id IN (" . implode(",", $user_agent_ids) . ")\n\t\t\tLIMIT 25000\n\t\t";
$wpdb->query($sql);
$sql = "\n\t\tDELETE\n\t\t\tFROM " . \Podlove\Model\DownloadIntentClean::table_name() . "\n\t\t\tWHERE user_agent_id IN (" . implode(",", $user_agent_ids) . ")\n\t\t\tLIMIT 25000\n\t\t";
$wpdb->query($sql);
// see how much is left to delete
$sql = "\n\t\tSELECT\n\t\t\tCOUNT(*) \n\t\tFROM\n\t\t\t" . \Podlove\Model\DownloadIntent::table_name() . " \n\t\tWHERE\n\t\t\tuser_agent_id IN (" . implode(",", $user_agent_ids) . ")\n\t\t";
$send_response($wpdb->get_var($sql));
}
private static function get_downloads_per_hour_for_episode($episode_id)
{
global $wpdb;
$sql = "\n\t\t\tSELECT\n\t\t\t \tCOUNT(*) downloads, DATE_FORMAT(accessed_at, '%%Y-%%m-%%d %%H') AS access_hour\n\t\t\tFROM\t\t\n\t\t\t\t\t`" . \Podlove\Model\DownloadIntentClean::table_name() . "` di \n\t\t\t\t\tINNER JOIN `" . \Podlove\Model\MediaFile::table_name() . "` mf ON mf.id = di.media_file_id\n\t\t\t\t\tWHERE episode_id = %d\n\t\t\tGROUP BY access_hour\n\t\t\tORDER BY access_hour\n\t\t\tLIMIT %d\n\t\t";
$data = $wpdb->get_results($wpdb->prepare($sql, $episode_id, self::HOURS_TO_CALCULATE), ARRAY_A);
$release_date = $wpdb->get_var($wpdb->prepare("SELECT post_date FROM {$wpdb->posts} p JOIN " . \Podlove\Model\Episode::table_name() . " e ON e.post_id = p.ID WHERE e.id = %d", $episode_id));
if ($data) {
$missing_hours = self::add_missing_hours($data, $release_date);
array_splice($missing_hours, self::HOURS_TO_CALCULATE);
return array_column($missing_hours, 'downloads');
} else {
return array();
}
}
/**
* Delete bot-entries from "clean" DownloadIntents
*
* If a UserAgent is declared as bot "after" it has already been accepted as
* clean, it needs to be deleted.
*/
function podlove_delete_bots_from_clean_downloadintents()
{
global $wpdb;
$sql = "DELETE FROM `" . DownloadIntentClean::table_name() . "` WHERE `user_agent_id` IN (\n\t\tSELECT id FROM `" . UserAgent::table_name() . "` ua WHERE ua.bot\n\t)";
$wpdb->query($sql);
}
public function analytics_total_downloads_per_day()
{
$cache_key = 'podlove_analytics_tdphx';
$cache = \Podlove\Cache\TemplateCache::get_instance();
$content = $cache->cache_for($cache_key, function () {
global $wpdb;
$sql = "SELECT\n\t\t\t\t\t\tCOUNT(*) downloads,\n\t\t\t\t\t\tUNIX_TIMESTAMP(accessed_at) AS access_date,\n\t\t\t\t\t\tDATE_FORMAT(accessed_at, '%Y-%m-%d') AS date_day,\n\t\t\t\t\t\tmf.episode_asset_id asset_id,\n\t\t\t\t\t\tclient_name,\n\t\t\t\t\t\tos_name AS system,\n\t\t\t\t\t\tsource,\n\t\t\t\t\t\tcontext\n\t\t\t\t\tFROM\n\t\t\t\t\t\t" . Model\DownloadIntentClean::table_name() . " di\n\t\t\t\t\t\tINNER JOIN " . Model\MediaFile::table_name() . " mf ON mf.id = di.media_file_id\n\t\t\t\t\t\tINNER JOIN " . Model\UserAgent::table_name() . " ua ON ua.id = di.user_agent_id\n\t\t\t\t\tWHERE accessed_at >= STR_TO_DATE('" . date("Y-m-d", strtotime("-30 days")) . "','%Y-%m-%d')\n\t\t\t\t\tGROUP BY date_day, asset_id, client_name, system, source, context";
$results = $wpdb->get_results($sql, ARRAY_N);
$csv = '"downloads","date","asset_id","client","system","source","context"' . "\n";
foreach ($results as $row) {
$row[4] = '"' . $row[4] . '"';
$row[5] = '"' . $row[5] . '"';
$csv .= implode(",", $row) . "\n";
}
return $csv;
}, 3600);
$etag = md5($content);
header("Etag: {$etag}");
header("Last-Modified: " . gmdate("D, d M Y H:i:s", $cache->expiration_for($cache_key)) . " GMT");
$etagHeader = isset($_SERVER['HTTP_IF_NONE_MATCH']) ? trim($_SERVER['HTTP_IF_NONE_MATCH']) : false;
if ($etagHeader == $etag) {
header("HTTP/1.1 304 Not Modified");
exit;
}
\Podlove\Feeds\check_for_and_do_compression('text/plain');
echo $content;
ob_end_flush();
exit;
}
public static function get_last_insert_id()
{
global $wpdb;
$id = $wpdb->get_var("SELECT MAX(id) FROM " . Model\DownloadIntentClean::table_name());
return $id ? (int) $id : 0;
}
