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