function get_trend_data($ids, $grouping = "post", $type = "article", $period = "over", $last = 1, $over = 30, $multi_id = 0)
{
global $wpdb, $table_prefix;
$strtodate = gdFunctionsGDSR::mysql_version();
$strtodate = $strtodate == 4 ? $strtodate = "date_add(d.vote_date, interval 0 day)" : ($strtodate = "str_to_date(d.vote_date, '%Y-%m-%d')");
if ($period == "over") {
$where = sprintf("%s BETWEEN DATE_SUB(NOW(), INTERVAL %s DAY) AND DATE_SUB(NOW(), INTERVAL %s DAY)", $strtodate, $last + $over, $last);
} else {
$where = sprintf("%s BETWEEN DATE_SUB(NOW(), INTERVAL %s DAY) AND NOW()", $strtodate, $last);
}
$from = $join = $sql = "";
switch ($grouping) {
case "post":
$select = $type == "multis" ? "d.post_id" : "d.id";
break;
case "user":
$select = "u.id";
$join = "p.id = d.id and p.post_status = 'publish' and u.id = p.post_author and ";
break;
case "category":
$select = "t.term_id";
$join = "p.id = d.id and p.post_status = 'publish' and t.term_taxonomy_id = r.term_taxonomy_id and r.object_id = p.id and t.taxonomy = 'category' and t.term_id = x.term_id and ";
break;
}
if ($type == "multis") {
switch ($grouping) {
case "post":
$from = sprintf("%sgdsr_multis_trend d", $table_prefix);
break;
case "user":
$from = sprintf("%s u, %sposts p, %sgdsr_multis_trend d", $wpdb->users, $table_prefix, $table_prefix);
break;
case "category":
$from = sprintf("%sterm_taxonomy t, %sterm_relationships r, %sterms x, %sposts p, %sgdsr_multis_trend d", $table_prefix, $table_prefix, $table_prefix, $table_prefix, $table_prefix);
break;
}
$sql = sprintf("SELECT %s as id, sum(d.total_votes_users) as user_voters, sum(d.average_rating_users * d.total_votes_users) as user_votes, sum(d.total_votes_visitors) as visitor_voters, sum(d.average_rating_visitors * d.total_votes_visitors) as visitor_votes FROM %s WHERE %s%s and %s in (%s) and multi_id = %s group by %s order by %s asc", $select, $from, $join, $where, $select, $ids, $multi_id, $select, $select);
} else {
switch ($grouping) {
case "post":
$from = sprintf("%sgdsr_votes_trend d", $table_prefix);
break;
case "user":
$from = sprintf("%s u, %sposts p, %sgdsr_votes_trend d", $wpdb->users, $table_prefix, $table_prefix);
break;
case "category":
$from = sprintf("%sterm_taxonomy t, %sterm_relationships r, %sterms x, %sposts p, %sgdsr_votes_trend d", $table_prefix, $table_prefix, $table_prefix, $table_prefix, $table_prefix);
break;
}
$sql = sprintf("SELECT %s as id, sum(d.user_voters) as user_voters, sum(d.user_votes) as user_votes, sum(d.visitor_voters) as visitor_voters, sum(d.visitor_votes) as visitor_votes FROM %s WHERE %s%s and d.vote_type = '%s' AND %s IN (%s) GROUP BY %s ORDER BY %s asc", $select, $from, $join, $where, $type == "thumbs" ? "artthumb" : "article", $select, $ids, $select, $select);
}
return $wpdb->get_results($sql);
}
function trends_daily($id, $vote_type = 'article', $show = "", $days = 30)
{
global $wpdb, $table_prefix;
$mysql4_strtodate = "date_add(vote_date, interval 0 day)";
$mysql5_strtodate = "str_to_date(vote_date, '%Y-%m-%d')";
$strtodate = "";
$voters = $votes = 0;
switch (gdFunctionsGDSR::mysql_version()) {
case "4":
$strtodate = $mysql4_strtodate;
break;
case "5":
default:
$strtodate = $mysql5_strtodate;
break;
}
$sql = sprintf("SELECT user_voters, user_votes, visitor_voters, visitor_votes, %s as vote_date FROM %sgdsr_votes_trend where vote_type = '%s' and id = %s and %s between DATE_SUB(NOW(), INTERVAL %s DAY) AND NOW() order by vote_date asc", $strtodate, $table_prefix, $vote_type, $id, $strtodate, $days);
$results = $wpdb->get_results($sql);
$data = array();
for ($i = $days; $i > 0; $i--) {
$day = date('Y-m-d', mktime(0, 0, 0, date("m"), date("d") - $i, date("Y")));
$data[$day] = array("votes" => 0, "rating" => 0);
}
foreach ($results as $row) {
if ($show == "user") {
$voters = $row->user_voters;
$votes = $row->user_votes;
} else {
if ($show == "visitor") {
$voters = $row->visitor_voters;
$votes = $row->visitor_votes;
} else {
$voters = $row->visitor_voters + $row->user_voters;
$votes = $row->visitor_votes + $row->user_votes;
}
}
$data[$row->vote_date]["rating"] = $voters > 0 ? number_format($votes / $voters, 1) : 0;
$data[$row->vote_date]["votes"] = $voters;
}
return $data;
}
