public function prepare_items()
{
global $wpdb;
// number of items per page
$per_page = 20;
// define column headers
$columns = $this->get_columns();
$hidden = array();
$sortable = $this->get_sortable_columns();
$this->_column_headers = array($columns, $hidden, $sortable);
$data = \Podlove\Cache\TemplateCache::get_instance()->cache_for('podlove_analytics_downloads_table', function () {
global $wpdb;
// retrieve data
$subSQL = function ($start = null, $end = null) {
$strToMysqlDate = function ($s) {
return date('Y-m-d', strtotime($s));
};
if ($start && $end) {
$timerange = " AND di2.accessed_at BETWEEN '{$strToMysqlDate($start)}' AND '{$strToMysqlDate($end)}'";
} elseif ($start) {
$timerange = " AND DATE(di2.accessed_at) = '{$strToMysqlDate($start)}'";
} else {
$timerange = "";
}
return "\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tCOUNT(di2.id) downloads\n\t\t\t\t\tFROM\n\t\t\t\t\t\t" . Model\MediaFile::table_name() . " mf2\n\t\t\t\t\t\tLEFT JOIN " . Model\DownloadIntentClean::table_name() . " di2 ON di2.media_file_id = mf2.id\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tmf2.episode_id = e.id\n\t\t\t\t\t\t{$timerange}\n\t\t\t\t";
};
$sql = "\n\t\t\t\tSELECT\n\t\t\t\t\te.id,\n\t\t\t\t\tp.post_title title,\n\t\t\t\t\tp.post_date post_date,\n\t\t\t\t\tCOUNT(di.id) downloads,\n\t\t\t\t\t(" . $subSQL('28 days ago', 'now') . ") downloadsMonth,\n\t\t\t\t\t(" . $subSQL('7 days ago', 'now') . ") downloadsWeek,\n\t\t\t\t\t(" . $subSQL('1 day ago') . ") downloadsYesterday,\n\t\t\t\t\t(" . $subSQL('now') . ") downloadsToday\n\t\t\t\tFROM\n\t\t\t\t\t" . Model\Episode::table_name() . " e\n\t\t\t\t\tJOIN " . $wpdb->posts . " p ON e.post_id = p.ID\n\t\t\t\t\tJOIN " . Model\MediaFile::table_name() . " mf ON e.id = mf.episode_id\n\t\t\t\t\tLEFT JOIN " . Model\DownloadIntentClean::table_name() . " di ON di.media_file_id = mf.id\n\t\t\t\tWHERE\n\t\t\t\t\tp.post_status IN ('publish', 'private')\n\t\t\t\tGROUP BY\n\t\t\t\t\te.id\n\t\t\t";
return $wpdb->get_results($sql, ARRAY_A);
}, HOUR_IN_SECONDS);
$valid_order_keys = array('post_date', 'downloads', 'downloadsMonth', 'downloadsWeek', 'downloadsYesterday', 'downloadsToday');
// look for order options
if (isset($_GET['orderby']) && in_array($_GET['orderby'], $valid_order_keys)) {
$orderby = $_GET['orderby'];
} else {
$orderby = 'post_date';
}
// look how to sort
if (isset($_GET['order'])) {
$order = strtoupper($_GET['order']) == 'ASC' ? SORT_ASC : SORT_DESC;
} else {
$order = SORT_DESC;
}
array_multisort(\array_column($data, $orderby), $order, $data);
// get current page
$current_page = $this->get_pagenum();
// get total items
$total_items = count($data);
// extrage page for current page only
$data = array_slice($data, ($current_page - 1) * $per_page, $per_page);
// add items to table
$this->items = $data;
// register pagination options & calculations
$this->set_pagination_args(array('total_items' => $total_items, 'per_page' => $per_page, 'total_pages' => ceil($total_items / $per_page)));
}
function uninstall_for_current_blog()
{
global $wpdb;
Model\Feed::destroy();
Model\FileType::destroy();
Model\EpisodeAsset::destroy();
Model\MediaFile::destroy();
Model\Episode::destroy();
Model\Template::destroy();
Model\DownloadIntent::destroy();
Model\DownloadIntentClean::destroy();
Model\UserAgent::destroy();
Model\GeoArea::destroy();
Model\GeoAreaName::destroy();
do_action('podlove_uninstall_plugin');
// trash all episodes
$query = new \WP_Query(['post_type' => 'podcast']);
if ($query->have_posts()) {
while ($query->have_posts()) {
$query->the_post();
wp_trash_post(get_the_ID());
}
}
wp_reset_postdata();
// delete everything from wp_options
$wpdb->query('DELETE FROM `' . $wpdb->options . '` WHERE option_name LIKE "%podlove%"');
}