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; }