public function counterize_feed_most_used_browsers_collapsible($nb_parent_items = 10, $nb_child_items = 15, $header_override = '')
{
global $wpdb;
$nb_parent_items = (int) $wpdb->prepare("%d", $nb_parent_items);
$nb_child_items = (int) $wpdb->prepare("%d", $nb_child_items);
$sql_parents = "SELECT `browserName` AS `label`, `browserCode` AS `code`, `browserURL` as `url`, SUM( `count` ) AS `count` " . " FROM `" . counterize_agentsTable() . "`" . " GROUP BY `label` " . " ORDER BY `count` DESC , `label` ASC " . " LIMIT {$nb_parent_items} ";
$sql_children = "SELECT `browserVersion` AS `label`, SUM( `count` ) AS `count` " . " FROM `" . counterize_agentsTable() . "`" . " WHERE `browserName` = %s " . " GROUP BY `label` " . " ORDER BY `count` DESC , `label` ASC " . " LIMIT %d";
$parents_rows = $wpdb->get_results($sql_parents);
unset($sql_parents);
$title = __('Most used browsers ‪(click [+] for details)‬', COUNTERIZE_PLUGIN_BROWSERS_TD);
if (!empty($header_override)) {
$title = $header_override;
}
$parents_feed = new CounterizeFeed($title, __('Browsers', COUNTERIZE_PLUGIN_BROWSERS_TD));
foreach ($parents_rows as $parent_row) {
$src = counterize_get_image_url($parent_row->code);
$name = $parent_row->label;
if (empty($parent_row->code) || $parent_row->code == ' ') {
$parent_label = __('Unknown', COUNTERIZE_PLUGIN_BROWSERS_TD);
} else {
$parent_label = $name;
}
$children_rows = $wpdb->get_results($wpdb->prepare($sql_children, $parent_label, $nb_child_items));
$has_subitems = (count($children_rows) > 1 || count($children_rows) == 1 && $children_rows[0]->label != '') && $parent_row->code != 'unknown';
if ($has_subitems) {
$children_feed = new CounterizeFeed(__('Detailed statistics for this browser', COUNTERIZE_PLUGIN_BROWSERS_TD), sprintf(__('%s versions', COUNTERIZE_PLUGIN_BROWSERS_TD), $parent_label));
foreach ($children_rows as $child_row) {
if (empty($child_row->label) || $child_row->label == ' ') {
$child_label = __('(no version)', COUNTERIZE_PLUGIN_BROWSERS_TD);
} else {
$child_label = $child_row->label;
}
$children_feed->add_item_2($child_row->count, $child_label);
unset($child_label);
}
$children_feed->refresh_percentages();
}
unset($children_rows);
if (!empty($src)) {
$img = new CounterizeFeedImg($src, $parent_label, $parent_label, 'browsericon');
if ($has_subitems) {
$parents_feed->add_item_5($parent_row->count, $parent_label, $parent_row->url, $img, $children_feed);
} else {
$parents_feed->add_item_4($parent_row->count, $parent_label, $parent_row->url, $img);
}
} else {
if ($has_subitems) {
$parents_feed->add_item_5($parent_row->count, $parent_label, $parent_row->url, NULL, $children_feed);
} else {
$parents_feed->add_item_3($parent_row->count, $parent_label, $parent_row->url);
}
}
unset($src, $name, $parent_label);
}
unset($parents_rows, $sql_children);
$parents_feed->refresh_percentages();
return $parents_feed;
}
public function counterize_feed_most_searched_keywords24hrs($number = 10, $header_override = '')
{
global $wpdb;
$number = $wpdb->prepare("%d", $number);
$onedayago = date('Y-m-d H:i:s', time() - 86400);
$sql = "SELECT COUNT( 1 ) AS `count`, k.keyword AS `label` " . " FROM " . counterize_keywordTable() . " k, " . counterize_logTable() . " l, " . counterize_refererTable() . " r " . " WHERE r.`refererID` = l.`refererID` " . " AND r.`keywordID` = k.`keywordID` " . " AND k.`keywordID` <> 1 " . " AND l.`timestamp` >= '{$onedayago}' " . " GROUP BY k.`keyword` " . " ORDER BY `count` DESC " . " LIMIT {$number}";
$rows = $wpdb->get_results($sql);
$title = __('Most searched keywords for the last 24 hours', COUNTERIZE_PLUGIN_KEYWORDS_TD);
if (!empty($header_override)) {
$title = $header_override;
}
$feed = new CounterizeFeed($title, __('Keywords', COUNTERIZE_PLUGIN_KEYWORDS_TD));
foreach ($rows as $row) {
$feed->add_item_2($row->count, $row->label);
}
unset($rows, $sqm, $onedayago);
$feed->refresh_percentages();
return $feed;
}
public function counterize_feed_hourly_stats($only_today = false, $header_override = '')
{
global $wpdb;
$onedayago = date('Y-m-d H:i:s', time() - 86400);
$sql = "SELECT " . " \tHOUR( `timestamp` ) AS `label`, " . " \tCOUNT( 1 ) AS `count` " . " FROM `" . counterize_logTable() . "` ";
if ($only_today) {
$sql .= " WHERE `timestamp` >= '{$onedayago}'";
}
$sql .= " GROUP BY `label` ";
$rows = $wpdb->get_results($sql);
if ($only_today) {
$title = __('Hourly hits for the last 24 hours', COUNTERIZE_PLUGIN_TRAFFIC_TD);
if (!empty($header_override)) {
$title = $header_override;
}
$feed = new CounterizeFeed($title, __('Hour', COUNTERIZE_PLUGIN_TRAFFIC_TD));
} else {
$title = __('Hits based on hour of day', COUNTERIZE_PLUGIN_TRAFFIC_TD);
if (!empty($header_override)) {
$title = $header_override;
}
$feed = new CounterizeFeed($title, __('Hour', COUNTERIZE_PLUGIN_TRAFFIC_TD));
}
foreach ($rows as $row) {
$feed->add_item_2($row->count, $row->label);
}
unset($rows, $sql);
$feed->refresh_percentages();
return $feed;
}
public function counterize_feed_most_visiting_countries24hrs($number = 10, $header_override = '')
{
global $wpdb;
$number = $wpdb->prepare("%d", $number);
$onedayago = date('Y-m-d H:i:s', time() - 86400);
$sql = "SELECT COUNT( m.id ) AS `count`, c.code, c.code AS label " . " FROM `" . counterize_logTable() . "` m, `" . counterize_countryTable() . "` c " . " WHERE m.countryID = c.countryID" . " AND m.timestamp >= '{$onedayago}'" . " GROUP BY c.code " . " ORDER BY `count` DESC " . " LIMIT {$number}";
$rows = $wpdb->get_results($sql);
$title = __('Most visiting countries for the last 24 hours', COUNTERIZE_PLUGIN_COUNTRIES_TD);
if (!empty($header_override)) {
$title = $header_override;
}
$feed = new CounterizeFeed($title, __('Countries', COUNTERIZE_PLUGIN_COUNTRIES_TD));
foreach ($rows as $row) {
$src = counterize_get_flag_url($row->code);
$name = counterize_get_countryname($row->code);
if (empty($row->code) || $row->code == ' ' || $row->code == '00') {
$label = __('unknown', COUNTERIZE_PLUGIN_COUNTRIES_TD);
} else {
$label = $name;
}
if (!empty($src)) {
$img = new CounterizeFeedImg($src, sprintf(__('National flag of %s', COUNTERIZE_PLUGIN_COUNTRIES_TD), $name), counterize_get_countryname($row->code) . " ({$row->code})", 'countryflag');
$feed->add_item_4($row->count, $label, '', $img);
} else {
$feed->add_item_2($row->count, $label);
}
unset($src, $name, $label);
}
unset($rows, $sql);
$feed->refresh_percentages();
return $feed;
}
