Files

962 lines
44 KiB
PHP
Raw Permalink Normal View History

2026-11-17 16:15:01 -05:00
<?php
class gdsrDB {
function filter_latest_votes($o, $user = 0) {
global $wpdb, $table_prefix;
$types = array();
$where = $user == 0 ? "" : " and l.user_id = ".$user;
$select = "l.id, l.vote_type, l.voted, l.vote, l.ip, l.user_id, u.display_name, u.user_email";
$from = sprintf("%sgdsr_votes_log l left join %s u on u.ID = l.user_id", $table_prefix, $wpdb->users);
if ($o["integrate_dashboard_latest_filter_thumb_std"] == 1) $types[] = "'artthumb'";
if ($o["integrate_dashboard_latest_filter_thumb_cmm"] == 1) $types[] = "'cmmthumb'";
if ($o["integrate_dashboard_latest_filter_stars_std"] == 1) $types[] = "'article'";
if ($o["integrate_dashboard_latest_filter_stars_cmm"] == 1) $types[] = "'comment'";
if ($o["integrate_dashboard_latest_filter_stars_mur"] == 1) {
$types[] = "'multis'";
$select.= ", l.object, m.stars, m.weight, m.name";
$from.= sprintf(" left join %sgdsr_multis m on m.multi_id = l.multi_id", $table_prefix);
}
if (count($types) == 0) return array();
$sql = sprintf("select %s from %s where vote_type in (%s)%s order by voted desc limit 0, %s",
$select, $from, join(", ", $types), $where, $o["integrate_dashboard_latest_count"]);
return $wpdb->get_results($sql);
}
}
class GDSRDatabase {
// categories
function add_category_defaults($ids, $ids_array, $items) {
global $wpdb, $table_prefix;
$sql = sprintf("select category_id from %sgdsr_data_category where category_id in %s", $table_prefix, $ids);
$cats = array();
$rows = $wpdb->get_results($sql, ARRAY_N);
if (is_array($rows)) foreach ($rows as $row) $cats[] = $row[0];
if (is_array($cats)) {
foreach ($ids_array as $id) {
if (!in_array($id, $cats)) {
GDSRDatabase::add_category_default($id, $items[$id] > 0);
}
}
}
}
function add_category_default($id, $parented = false) {
global $wpdb, $table_prefix;
$values = $parented ? "'P', 'P', 'P', 'P', 'P'" : "'A', 'A', 'N', 'N', 'N'";
$sql = sprintf("INSERT INTO %sgdsr_data_category (category_id, rules_articles, rules_comments, moderate_articles, moderate_comments, expiry_type, expiry_value, cmm_integration_set) VALUES (%s, %s, '', 0)",
$table_prefix, $id, $values);
$wpdb->query($sql);
}
function init_categories_data() {
$all_cats = GDSRDatabase::get_all_categories();
$categories = GDSRHelper::get_categories_hierarchy($all_cats);
$ids_array = $items = array();
foreach ($categories as $cat) {
$items[$cat->term_id] = $cat->parent;
$ids_array[] = $cat->term_id;
}
GDSRDatabase::add_category_defaults("(".join(", ", $ids_array).")", $ids_array, $items);
}
function get_all_categories() {
global $wpdb, $table_prefix;
$sql = sprintf("SELECT 0 as depth, x.term_id, t.name, t.slug, x.parent, x.count, ms.name as multi_set, d.* FROM %sterm_taxonomy x inner join %sterms t on x.term_id = t.term_id left join %sgdsr_data_category d on d.category_id = x.term_id left join %sgdsr_multis ms on ms.multi_id = d.cmm_integration_set where taxonomy = 'category' order by x.parent, t.name asc",
$table_prefix, $table_prefix, $table_prefix, $table_prefix);
return $wpdb->get_results($sql);
}
function get_categories_data($cats = array()) {
global $wpdb, $table_prefix;
$where = count($cats) > 0 ? sprintf(" and x.term_id in (%s)", join(", ", $cats)) : "";
$sql = sprintf("SELECT x.parent, d.* FROM %sterm_taxonomy x inner join %sterms t on x.term_id = t.term_id left join %sgdsr_data_category d on d.category_id = x.term_id left join %sgdsr_multis ms on ms.multi_id = d.cmm_integration_set where taxonomy = 'category'%s order by x.parent desc, d.cmm_integration_set desc, x.term_id asc",
$table_prefix, $table_prefix, $table_prefix, $table_prefix, $where);
return $wpdb->get_results($sql);
}
// categories
// save & update
function delete_votes($ids, $delete, $ids_array, $thumbs = false) {
global $wpdb, $table_prefix;
$cde_articles = $thumbs ? "artthumb" : "article";
$cde_comments = $thumbs ? "cmmthumb" : "comment";
$dbt_data_article = $table_prefix.'gdsr_data_article';
$dbt_data_comment = $table_prefix.'gdsr_data_comment';
$dbt_votes_log = $table_prefix.'gdsr_votes_log';
if ($delete == "") return;
$delstring = $dellog = "";
switch (substr($delete, 1, 1)) {
case "A":
$delstring = $thumbs ? "user_recc_plus = 0, user_recc_minus = 0, visitor_recc_plus = 0, visitor_recc_minus = 0" : "user_votes = 0, user_voters = 0, visitor_votes = 0, visitor_voters = 0";
break;
case "V":
$delstring = $thumbs ? "visitor_recc_plus = 0, visitor_recc_minus = 0" : "visitor_votes = 0, visitor_voters = 0";
$dellog = " and user_id = 0";
break;
case "U":
$delstring = $thumbs ? "user_recc_plus = 0, user_recc_minus = 0" : "user_votes = 0, user_voters = 0";
$dellog = " and user_id > 0";
break;
default:
return;
break;
}
if (substr($delete, 0, 1) == "A") {
$wpdb->query(sprintf("update %s set %s where post_id in %s", $dbt_data_article, $delstring, $ids));
$wpdb->query(sprintf("delete from %s where vote_type = '%s' and id in %s%s", $dbt_votes_log, $cde_articles, $ids, $dellog));
} else if (substr($delete, 0, 1) == "K") {
$wpdb->query(sprintf("update %s set %s where comment_id in %s", $dbt_data_comment, $delstring, $ids));
$wpdb->query(sprintf("delete from %s where vote_type = '%s' and id in %s%s", $dbt_votes_log, $cde_comments, $ids, $dellog));
} else if (substr($delete, 0, 1) == "C") {
$cids = GDSRDatabase::get_commentids_posts($ids);
$cm = array();
foreach ($cids as $cid)
$cm[] = $cid->comment_id;
$cms = "(".join(", ", $cm).")";
$wpdb->query(sprintf("update %s set %s where post_id in %s", $dbt_data_comment, $delstring, $ids));
$wpdb->query(sprintf("delete from %s where vote_type = '%s' and id in %s%s", $dbt_votes_log, $cde_comments, $cms, $dellog));
} else return;
}
function add_defaults($ids, $ids_array) {
global $wpdb, $table_prefix;
$rows = $wpdb->get_results(sprintf("select post_id from %sgdsr_data_article where post_id in %s", $table_prefix, $ids), ARRAY_N);
if (count($rows) == 0) $rows = array();
$found = array();
foreach ($rows as $r) $found[] = $r[0];
foreach ($ids_array as $id)
if (!in_array($id, $found)) GDSRDatabase::add_default_vote($id);
}
function save_comment_review($comment_id, $review) {
global $wpdb, $table_prefix;
$comments = $table_prefix.'gdsr_data_comment';
$sql = sprintf("update %s set review = %s where comment_id = %s",
$comments, $review, $comment_id);
$wpdb->query($sql);
}
function save_review($post_id, $rating, $old = true) {
global $wpdb, $table_prefix;
$articles = $table_prefix.'gdsr_data_article';
if ($rating < 0) $rating = -1;
if (!$old) GDSRDatabase::add_default_vote($post_id, '', $rating);
else $wpdb->query("update ".$articles." set review = ".$rating." where post_id = ".$post_id);
}
function save_comment_rules($post_id, $comment_vote, $comment_moderation, $recc_comment_vote, $recc_comment_moderation, $old = true) {
global $wpdb, $table_prefix;
$articles = $table_prefix.'gdsr_data_article';
if (!$old) GDSRDatabase::add_default_vote($post_id);
$sql = "update ".$articles." set rules_comments = '".$comment_vote."', moderate_comments = '".$comment_moderation."', recc_rules_comments = '".$recc_comment_vote."', recc_moderate_comments = '".$recc_comment_moderation."' where post_id = ".$post_id;
$wpdb->query($sql);
}
function save_article_rules($post_id, $article_vote, $article_moderation, $recc_article_vote, $recc_article_moderation, $old = true) {
global $wpdb, $table_prefix;
$articles = $table_prefix.'gdsr_data_article';
if (!$old) GDSRDatabase::add_default_vote($post_id);
$sql = "update ".$articles." set rules_articles = '".$article_vote."', moderate_articles = '".$article_moderation."', recc_rules_articles = '".$recc_article_vote."', recc_moderate_articles = '".$recc_article_moderation."' where post_id = ".$post_id;
$wpdb->query($sql);
}
function save_timer_rules($post_id, $timer_type, $timer_value, $old = true) {
global $wpdb, $table_prefix;
$articles = $table_prefix.'gdsr_data_article';
if (!$old) GDSRDatabase::add_default_vote($post_id);
$wpdb->query("update ".$articles." set expiry_type = '".$timer_type."', expiry_value = '".$timer_value."' where post_id = ".$post_id);
}
function save_timer_rules_thumbs($post_id, $timer_type, $timer_value, $old = true) {
global $wpdb, $table_prefix;
$articles = $table_prefix.'gdsr_data_article';
if (!$old) GDSRDatabase::add_default_vote($post_id);
$wpdb->query("update ".$articles." set recc_expiry_type = '".$timer_type."', recc_expiry_value = '".$timer_value."' where post_id = ".$post_id);
}
function rating_from_comment($comment_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select vote from %sgdsr_votes_log where vote_type = 'article' and comment_id = %s", $table_prefix, $comment_id);
return $wpdb->get_var($sql);
}
function delete_by_comment($comment_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_votes_log where vote_type = 'article' and comment_id = %s", $table_prefix, $comment_id);
$row = $wpdb->get_row($sql);
if (count($row) > 0) {
if ($row->user_id == 0) $delstring = sprintf("visitor_votes = visitor_votes - %s, visitor_voters = visitor_voters - 1", $row->vote);
else $delstring = sprintf("user_votes = user_votes - %s, user_voters = user_voters - 1", $row->vote);
$sql = sprintf("update %sgdsr_data_article set %s where post_id = %s", $table_prefix, $delstring, $row->id);
$wpdb->query($sql);
$sql = sprintf("delete from %sgdsr_votes_log where record_id = %s", $table_prefix, $row->record_id);
$wpdb->query($sql);
}
}
function add_default_vote($post_id, $is_page = '', $review = -1) {
global $wpdb, $table_prefix;
$options = get_option('gd-star-rating');
if ($is_page == '') $is_page = GDSRDatabase::get_post_type($post_id) == 'page' ? '1' : '0';
$vote_timer_value = $options["default_timer_type"] == "T" || $options["default_timer_type"] == "D" ? $options["default_timer_value"] : "";
$dbt_data_article = $table_prefix.'gdsr_data_article';
$sql = sprintf("INSERT INTO %s (post_id, rules_articles, rules_comments, moderate_articles, moderate_comments, recc_rules_articles, recc_rules_comments, recc_moderate_articles, recc_moderate_comments, is_page, user_voters, user_votes, visitor_voters, visitor_votes, review, expiry_type, expiry_value) VALUES (%s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', 0, 0, 0, 0, %s, '%s', '%s')",
$dbt_data_article, $post_id, $options["default_voterules_articles"], $options["default_voterules_comments"],
$options["default_moderation_articles"], $options["default_moderation_comments"], $options["recc_default_voterules_articles"], $options["recc_default_voterules_comments"],
$options["recc_default_moderation_articles"], $options["recc_default_moderation_comments"], $is_page, $review, $options["default_timer_type"], $vote_timer_value);
$wpdb->query($sql);
}
function add_empty_comment($comment_id, $post_id, $review = -1) {
global $wpdb, $table_prefix;
$sql = sprintf("INSERT INTO %sgdsr_data_comment (comment_id, post_id, is_locked, user_voters, user_votes, visitor_voters, visitor_votes, review) VALUES (%s, %s, '0', '0', '0', '0', '0', %s)",
$table_prefix, $comment_id, $post_id, $review);
$wpdb->query($sql);
}
function add_empty_comments($post_id) {
global $wpdb, $table_prefix;
$dbt_data_comment = $table_prefix.'gdsr_data_comment';
$sql = sprintf("select c.comment_ID from %s c left join %s g on c.comment_ID = g.comment_ID where (isnull(g.post_id) or g.post_id < 1) and c.comment_approved = 1 and c.comment_type = '' and c.comment_post_id = %s",
$wpdb->comments, $dbt_data_comment, $post_id);
$cmms = $wpdb->get_results($sql);
foreach ($cmms as $c)
GDSRDatabase::add_empty_comment($c->comment_ID, $post_id);
}
// save & update
// get
function get_post_data($post_id) {
global $wpdb, $table_prefix;
$sql = "SELECT * FROM ".$table_prefix."gdsr_data_article WHERE post_id = ".$post_id;
$results = $wpdb->get_row($sql, OBJECT);
return $results;
}
function get_comment_data($comment_id) {
global $wpdb, $table_prefix;
$sql = "SELECT * FROM ".$table_prefix."gdsr_data_comment WHERE comment_id = ".$comment_id;
$results = $wpdb->get_row($sql, OBJECT);
return $results;
}
function get_commentids_posts($post_ids) {
global $wpdb, $table_prefix;
$comments = $table_prefix.'gdsr_data_comment';
return $wpdb->get_results("select comment_id from ".$comments." where post_id in ".$post_ids, OBJECT);
}
function get_comment_review($comment_id) {
global $wpdb, $table_prefix;
$comments = $table_prefix.'gdsr_data_comment';
$sql = "select review from ".$comments." WHERE comment_id = ".$comment_id;
$results = $wpdb->get_row($sql, OBJECT);
if (count($results) == 0) return 0;
else return $results->review;
}
function get_post_edit($post_id) {
global $wpdb, $table_prefix;
$articles = $table_prefix.'gdsr_data_article';
$sql = "select * from ".$articles." WHERE post_id = ".$post_id;
$results = $wpdb->get_row($sql, OBJECT);
return $results;
}
function get_comments_count($post_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select count(*) from %s where comment_approved = 1 and comment_type = '' and comment_post_id = %s",
$wpdb->comments,
$post_id
);
return $wpdb->get_var($sql);
}
function get_comments($post_id) {
global $wpdb, $table_prefix;
$dbt_data_comment = $table_prefix.'gdsr_data_comment';
GDSRDatabase::add_empty_comments($post_id);
$sql = sprintf("select g.*, c.comment_content, c.comment_author, c.comment_author_email, c.comment_author_url, c.comment_date from %s c left join %s g on c.comment_ID = g.comment_ID where c.comment_approved = 1 and c.comment_type = '' and c.comment_post_id = %s order by c.comment_date desc",
$wpdb->comments,
$dbt_data_comment,
$post_id
);
return $sql;
}
function get_post_type($post_id) {
global $wpdb;
$sql = "SELECT post_type FROM $wpdb->posts WHERE ID = ".$post_id;
$r = $wpdb->get_row($sql);
return $r->post_type;
}
// get
}
class GDSRDBMulti {
function get_review_avg($multi_id, $post_id) {
global $wpdb, $table_prefix;
$articles = $table_prefix.'gdsr_multis_data';
$sql = "select average_review from ".$articles." WHERE multi_id = ".$multi_id." and post_id = ".$post_id;
$results = $wpdb->get_row($sql, OBJECT);
if (count($results) == 0) return -1;
else return $results->average_review;
}
function delete_votes($ids, $delete, $multi_id) {
global $wpdb, $table_prefix;
if ($delete == "") return;
$delstring = $dellog = "";
switch (substr($delete, 1, 1)) {
case "A":
$delstring = "md.average_rating_users = 0, md.total_votes_users = 0, mv.user_voters = 0, mv.user_votes = 0";
$delstring.= ", md.average_rating_visitors = 0, md.total_votes_visitors = 0, mv.visitor_voters = 0, mv.visitor_votes = 0";
break;
case "V":
$delstring = "md.average_rating_visitors = 0, md.total_votes_visitors = 0, mv.visitor_voters = 0, mv.visitor_votes = 0";
$dellog = " and user_id = 0";
break;
case "U":
$delstring = "md.average_rating_users = 0, md.total_votes_users = 0, mv.user_voters = 0, mv.user_votes = 0";
$dellog = " and user_id > 0";
break;
default:
return;
break;
}
$sql = sprintf("update %sgdsr_multis_data md inner join %sgdsr_multis_values mv on mv.id = md.id set %s where md.multi_id = %s and md.post_id in %s and mv.source = 'dta'",
$table_prefix, $table_prefix, $delstring, $multi_id, $ids);
$wpdb->query($sql);
$sql = sprintf("delete from %sgdsr_votes_log where id in %s and multi_id = %s%s", $table_prefix, $ids, $multi_id, $dellog);
$wpdb->query($sql);
}
function recalculate_multi_rating_log() {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_votes_log where vote_type = 'multis' and vote = 0", $table_prefix);
$rows = $wpdb->get_results($sql);
foreach ($rows as $row) {
$set = wp_gdget_multi_set($row->multi_id);
if (!is_null($set)) {
$vote = intval(10 * GDSRDBMulti::get_multi_rating_from_single_object($set, unserialize($row->object)));
$sql = sprintf("update %sgdsr_votes_log set `vote` = %s where record_id = %s", $table_prefix, $vote, $row->record_id);
$wpdb->query($sql);
}
}
}
function get_multi_rating_from_single_object($set, $object) {
$weighted = $i = 0;
$weight_norm = array_sum($set->weight);
$multi_data = $object;
foreach ($multi_data as $md) {
$weighted += (intval($md) * $set->weight[$i]) / $weight_norm;
$i++;
}
return number_format($weighted, 1);
}
function get_first_multi_set() {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_multis order by multi_id limit 0, 1", $table_prefix);
return $wpdb->get_row($sql);
}
function get_multisets_for_auto_insert() {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_multis where auto_insert != 'none'", $table_prefix);
return $wpdb->get_results($sql);
}
function recalculate_trend_averages($trend_id, $set) {
global $wpdb, $table_prefix;
$multi_data = GDSRDBMulti::get_trend_values($trend_id);
$weight_norm = array_sum($set->weight);
$total_users = $total_visitors = $total_votes = 0;
$user_weighted = $visitors_weighted = $weighted = 0;
$i = 0;
foreach ($multi_data as $md) {
$v_user = $v_visitor = $s_user = $s_visitor = $r_user = $r_visitor = 0;
$v_visitor = $md->visitor_voters;
$s_visitor = $md->visitor_votes;
$v_user = $md->user_voters;
$s_user = $md->user_votes;
if ($v_visitor > 0) $r_visitor = $s_visitor / $v_visitor;
if ($v_user > 0) $r_user = $s_user / $v_user;
if ($r_visitor > $set->stars) $r_visitor = $set->stars;
if ($r_user > $set->stars) $r_user = $set->stars;
$r_visitor = @number_format($r_visitor, 1);
$r_user = @number_format($r_user, 1);
$visitors_weighted += ($r_visitor * $set->weight[$i]) / $weight_norm;
$user_weighted += ($r_user * $set->weight[$i]) / $weight_norm;
$total_visitors += $v_visitor;
$total_users += $v_user;
$i++;
}
$rating_users = @number_format($user_weighted, 1);
$rating_visitors = @number_format($visitors_weighted, 1);
$total_users = @number_format($total_users / $i, 0);
$total_visitors = @number_format($total_visitors / $i, 0);
$sql = sprintf("update %sgdsr_multis_trend set average_rating_users = '%s', average_rating_visitors = '%s', total_votes_users = '%s', total_votes_visitors = '%s' where id = %s",
$table_prefix, $rating_users, $rating_visitors, $total_users, $total_visitors, $trend_id);
$wpdb->query($sql);
}
function recalculate_all_sets() {
global $wpdb, $table_prefix;
$set = null;
$prev_set = 0;
$sql = sprintf("select id, post_id, multi_id from %sgdsr_multis_data order by multi_id asc", $table_prefix);
$posts = $wpdb->get_results($sql);
foreach ($posts as $post) {
if ($prev_set != $post->multi_id) $set = gd_get_multi_set($post->multi_id);
GDSRDBMulti::recalculate_multi_averages($post->post_id, $post->multi_id, "", $set);
GDSRDBMulti::recalculate_multi_review_db($post->post_id, $post->id, $set);
}
$prev_set = 0;
$sql = sprintf("select id, multi_id from %sgdsr_multis_trend order by multi_id asc", $table_prefix);
$ids = $wpdb->get_results($sql);
foreach ($ids as $id) {
if ($prev_set != $id->multi_id) $set = gd_get_multi_set($id->multi_id);
foreach ($ids as $id) GDSRDBMulti::recalculate_trend_averages($id->id, $set);
}
}
function recalculate_set($set_id) {
global $wpdb, $table_prefix;
$set = gd_get_multi_set($set_id);
$sql = sprintf("select id, post_id from %sgdsr_multis_data where multi_id = %s", $table_prefix, $set_id);
$posts = $wpdb->get_results($sql);
foreach ($posts as $post) {
GDSRDBMulti::recalculate_multi_averages($post->post_id, $set_id, "", $set);
GDSRDBMulti::recalculate_multi_review_db($post->post_id, $post->id, $set);
}
$sql = sprintf("select id from %sgdsr_multis_trend where multi_id = %s", $table_prefix, $set_id);
$ids = $wpdb->get_results($sql);
foreach ($ids as $id) GDSRDBMulti::recalculate_trend_averages($id->id, $set);
}
function get_multi_rating_data($set_id, $post_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_multis_data where post_id = %s and multi_id = %s", $table_prefix, $post_id, $set_id);
return $wpdb->get_row($sql);
}
function recalculate_multi_review_db($post_id, $record_id, $set) {
global $wpdb, $table_prefix;
$multi_data = GDSRDBMulti::get_values($record_id, 'rvw');
if (count($multi_data) == 0) {
GDSRDBMulti::add_empty_review_values($record_id, count($set->object));
$multi_data = GDSRDBMulti::get_values($record_id, 'rvw');
}
$review = new GDSRArticleMultiReview($post_id);
$review->set = $set;
$i = 0;
$weighted = 0;
$weight_norm = array_sum($set->weight);
foreach ($multi_data as $md) {
$single_vote = array();
$single_vote["votes"] = 1;
$single_vote["score"] = $md->user_votes;
$single_vote["rating"] = $single_vote["score"];
$review->values = $single_vote;
$weighted += ( $single_vote["rating"] * $set->weight[$i] ) / $weight_norm;
$i++;
}
$review->rating = @number_format($weighted, 1);
$sql = sprintf("update %sgdsr_multis_data set average_review = '%s' where id = %s", $table_prefix, $review->rating, $record_id);
$wpdb->query($sql);
return $review;
}
function recalculate_multi_review($record_id, $values, $set) {
global $wpdb, $table_prefix;
$weight_norm = array_sum($set->weight);
$overall = 0;
for ($i = 0; $i < count($values); $i++) {
if ($weight_norm > 0) {
$overall += ($values[$i] * $set->weight[$i]) / $weight_norm;
}
}
$overall = @number_format($overall, 1);
$sql = sprintf("update %sgdsr_multis_data set average_review = '%s' where id = %s", $table_prefix, $overall, $record_id);
$wpdb->query($sql);
return $overall;
}
function recalculate_multi_vote($values, $set) {
$weight_norm = array_sum($set->weight);
$weight = $i = 0;
foreach ($values as $value) {
$weight+= ($value * $set->weight[$i]) / $weight_norm;
$i++;
}
$rating = @number_format($weight, 1);
return $rating;
}
function recalculate_multi_averages($post_id, $set_id, $rules = "", $set = null, $last_voted = false, $size = 20) {
global $wpdb, $table_prefix;
if ($set == null) $set = gd_get_multi_set($set_id);
$multi_data = GDSRDBMulti::get_values_join($post_id, $set_id);
$votes_js = array();
$weight_norm = array_sum($set->weight);
$total_users = $total_visitors = $total_votes = 0;
$user_weighted = $visitors_weighted = $weighted = 0;
$i = 0;
foreach ($multi_data as $md) {
$votes = $score = $rating = $v_user = $v_visitor = $s_user = $s_visitor = $r_user = $r_visitor = 0;
$v_visitor = $md->visitor_voters;
$s_visitor = $md->visitor_votes;
$v_user = $md->user_voters;
$s_user = $md->user_votes;
if ($v_visitor > 0) $r_visitor = $s_visitor / $v_visitor;
if ($v_user > 0) $r_user = $s_user / $v_user;
if ($r_visitor > $set->stars) $r_visitor = $set->stars;
if ($r_user > $set->stars) $r_user = $set->stars;
$r_visitor = @number_format($r_visitor, 1);
$r_user = @number_format($r_user, 1);
$visitors_weighted += ($r_visitor * $set->weight[$i]) / $weight_norm;
$user_weighted += ($r_user * $set->weight[$i]) / $weight_norm;
$total_visitors += $v_visitor;
$total_users += $v_user;
if ($rules != "") {
if ($rules == "A" || $rules == "N") {
$votes = $md->user_voters + $md->visitor_voters;
$score = $md->user_votes + $md->visitor_votes;
}
else if ($rules == "V") {
$votes = $md->visitor_voters;
$score = $md->visitor_votes;
}
else if ($rules == "U") {
$votes = $md->user_voters;
$score = $md->user_votes;
}
if ($votes > 0) $rating = $score / $votes;
if ($rating > $set->stars) $rating = $set->stars;
$rating = @number_format($rating, 1);
$votes_js[] = $rating * $size;
$weighted += ($rating * $set->weight[$i]) / $weight_norm;
$total_votes += $votes;
}
$i++;
}
$rating_users = @number_format($user_weighted, 1);
$rating_visitors = @number_format($visitors_weighted, 1);
$total_users = @number_format($total_users / $i, 0);
$total_visitors = @number_format($total_visitors / $i, 0);
if ($rules != "") {
$rating = @number_format($weighted, 1);
$total_votes = @number_format($total_votes / $i, 0);
$output["total"]["rating"] = $rating;
$output["total"]["votes"] = $total_votes;
$output["json"] = $votes_js;
}
$lastv = $last_voted ? ", last_voted = CURRENT_TIMESTAMP" : "";
$sql = sprintf("update %sgdsr_multis_data set average_rating_users = '%s', average_rating_visitors = '%s', total_votes_users = '%s', total_votes_visitors = '%s'%s where post_id = %s and multi_id = %s",
$table_prefix, $rating_users, $rating_visitors, $total_users, $total_visitors, $lastv, $post_id, $set_id);
$wpdb->query($sql);
$output["users"]["rating"] = $rating_users;
$output["users"]["votes"] = $total_users;
$output["visitors"]["rating"] = $rating_visitors;
$output["visitors"]["votes"] = $total_visitors;
return $output;
}
function clean_revision_articles() {
global $wpdb, $table_prefix;
$sql = sprintf("delete %s from %sgdsr_multis_data l inner join %sposts o on o.ID = l.post_id where o.post_type = 'revision'",
gdFunctionsGDSR::mysql_pre_4_1() ? sprintf("%sgdsr_multis_data", $table_prefix) : "l",
$table_prefix, $table_prefix);
$wpdb->query($sql);
$posts = $wpdb->rows_affected;
$sql = sprintf("delete %s from %sgdsr_multis_values l left join %sgdsr_multis_data o on o.id = l.id where o.id is null",
gdFunctionsGDSR::mysql_pre_4_1() ? sprintf("%sgdsr_multis_values", $table_prefix) : "l",
$table_prefix, $table_prefix);
$wpdb->query($sql);
return $posts;
}
function clean_dead_articles() {
global $wpdb, $table_prefix;
$sql = sprintf("delete %s from %sgdsr_multis_data l left join %sposts o on o.ID = l.post_id where o.ID is null",
gdFunctionsGDSR::mysql_pre_4_1() ? sprintf("%sgdsr_multis_data", $table_prefix) : "l",
$table_prefix, $table_prefix);
$wpdb->query($sql);
$posts = $wpdb->rows_affected;
$sql = sprintf("delete %s from %sgdsr_multis_values l left join %sgdsr_multis_data o on o.id = l.id where o.id is null",
gdFunctionsGDSR::mysql_pre_4_1() ? sprintf("%sgdsr_multis_values", $table_prefix) : "l",
$table_prefix, $table_prefix);
$wpdb->query($sql);
return $posts;
}
function delete_sets($ids) {
global $wpdb, $table_prefix;
$sql = sprintf("delete from %sgdsr_multis where multi_id in %s", $table_prefix, $ids);
$wpdb->query($sql);
}
function save_vote($post_id, $set_id, $user_id, $ip, $ua, $values, $post_data, $comment_id = 0) {
global $wpdb, $table_prefix;
$ua = str_replace("'", "''", $ua);
$ua = substr($ua, 0, 250);
if ($post_data->moderate_articles == "" || $post_data->moderate_articles == "N" || ($post_data->moderate_articles == "V" && $user > 0) || ($post_data->moderate_articles == "U" && $user == 0)) {
GDSRDBMulti::add_vote($post_id, $set_id, $user_id, $ip, $ua, $values);
GDSRDBMulti::add_to_log($post_id, $set_id, $user_id, $ip, $ua, $values, $comment_id);
}
else {
$modsql = sprintf("INSERT INTO %sgdsr_moderate (id, vote_type, multi_id, user_id, object, voted, ip, user_agent) VALUES (%s, 'multis', %s, %s, '%s', '%s', '%s', '%s')",
$table_prefix, $post_id, $set_id, $user_id, serialize($values), str_replace("'", "''", current_time('mysql')), $ip, $ua);
$wpdb->query($modsql);
}
}
function add_to_log($post_id, $set_id, $user_id, $ip, $ua, $values, $comment_id = 0) {
global $wpdb, $table_prefix;
$set = wp_gdget_multi_set($set_id);
$vote = intval(GDSRDBMulti::get_multi_rating_from_single_object($set, $values) * 10);
$modsql = sprintf("INSERT INTO %sgdsr_votes_log (id, vote_type, multi_id, user_id, vote, object, voted, ip, user_agent, comment_id) VALUES (%s, 'multis', %s, %s, %s, '%s', '%s', '%s', '%s', %s)",
$table_prefix, $post_id, $set_id, $user_id, $vote, serialize($values), str_replace("'", "''", current_time('mysql')), $ip, $ua, $comment_id);
wp_gdsr_dump("LOG", $modsql);
$wpdb->query($modsql);
}
function rating_from_comment($comment_id, $multi_set_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select object from %sgdsr_votes_log where vote_type = 'multis' and comment_id = %s and multi_id = %s", $table_prefix, $comment_id, $multi_set_id);
return $wpdb->get_var($sql);
}
function delete_by_comment($comment_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_votes_log where vote_type = 'multis' and comment_id = %s", $table_prefix, $comment_id);
$row = $wpdb->get_row($sql);
if (count($row) > 0) {
$votes = unserialize($row->object);
$sql = sprintf("select id from %sgdsr_multis_data where post_id = %s and multi_id = %s", $table_prefix, $row->id, $row->multi_id);
$id = $wpdb->get_var($sql);
if ($row->user_id == 0) $delstring = "visitor_votes = visitor_votes - %s, visitor_voters = visitor_voters - 1";
else $delstring = "user_votes = user_votes - %s, user_voters = user_voters - 1";
$i = 0;
foreach ($votes as $vote) {
$sql_set = sprintf($delstring, $vote);
$sql = sprintf("update %sgdsr_multis_values set %s where source = 'dta' and id = %s and item_id = %s", $table_prefix, $sql_set, $id, $i);
$wpdb->query($sql);
$i++;
}
$set = gd_get_multi_set($row->multi_id);
GDSRDBMulti::recalculate_multi_averages($row->id, $row->multi_id, "", $set, true);
$sql = sprintf("delete from %sgdsr_votes_log where record_id = %s", $table_prefix, $row->record_id);
$wpdb->query($sql);
}
}
function add_vote($post_id, $set_id, $user_id, $ip, $ua, $votes) {
global $wpdb, $table_prefix;
$set = gd_get_multi_set($set_id);
$data = $table_prefix.'gdsr_multis_data';
$trend = $table_prefix.'gdsr_multis_trend';
$trend_date = date("Y-m-d");
$sql_trend = sprintf("SELECT id FROM %s WHERE vote_date = '%s' and post_id = %s and multi_id = %s", $trend, $trend_date, $post_id, $set_id);
wp_gdsr_dump("TREND_CHECK", $sql_trend);
$trend_data = intval($wpdb->get_var($sql_trend));
wp_gdsr_dump("TREND_ID", $trend_data);
$trend_added = false;
if ($trend_data == 0) {
$trend_added = true;
$sql = sprintf("INSERT INTO %s (post_id, multi_id, vote_date) VALUES (%s, %s, '%s')", $trend, $post_id, $set_id, $trend_date);
$wpdb->query($sql);
$trend_id = $wpdb->insert_id;
}
else $trend_id = $trend_data;
GDSRDBMulti::add_values($trend_id, $user_id, $votes, "trd", $trend_added ? "add" : "edit");
GDSRDBMulti::recalculate_trend_averages($trend_id, $set);
$data_id = GDSRDBMulti::get_vote($post_id, $set_id, count($set->object));
GDSRDBMulti::add_values($data_id, $user_id, $votes);
}
function add_values($record_id, $user_id, $votes, $source = "dta", $operation = "edit") {
global $wpdb, $table_prefix;
$values = $table_prefix.'gdsr_multis_values';
$cl_voters = $user_id == 0 ? "visitor_voters" : "user_voters";
$cl_votes = $user_id == 0 ? "visitor_votes" : "user_votes";
if ($operation == "add")
$sql = sprintf("INSERT INTO %s (id, source, %s, %s, item_id) VALUES (%s, '%s', 1, %s, %s)",
$values, $cl_voters, $cl_votes, $record_id, $source, "%s", "%s");
else
$sql = sprintf("UPDATE %s SET %s = %s + 1, %s = %s + %s WHERE id = %s and item_id = %s and source = '%s'",
$values, $cl_voters, $cl_voters, $cl_votes, $cl_votes, "%s", $record_id, "%s", $source);
$i = 0;
foreach ($votes as $vote) {
$sql_insert = sprintf($sql, $vote, $i);
wp_gdsr_dump("SAVE_VOTE", $sql_insert);
$wpdb->query($sql_insert);
$i++;
}
}
function get_values($id, $source = 'dta') {
global $wpdb, $table_prefix;
$sql = sprintf("SELECT * FROM %sgdsr_multis_values WHERE source = '%s' and id = %s ORDER BY item_id ASC", $table_prefix, $source, $id);
return $wpdb->get_results($sql);
}
function add_empty_review_values($id, $values) {
global $wpdb, $table_prefix;
$values = intval($values);
for ($i = 0; $i < $values; $i++) {
$sql = sprintf("INSERT INTO %sgdsr_multis_values (id, source, item_id) VALUES (%s, 'rvw', %s)",
$table_prefix, $id, $i);
$wpdb->query($sql);
}
}
function get_trend_values($id) {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_multis_values where source = 'trd' and id = %s order by item_id asc",
$table_prefix, $id);
return $wpdb->get_results($sql);
}
function get_values_join($post_id, $set_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select v.* from %sgdsr_multis_values v inner join %sgdsr_multis_data d on d.id = v.id where v.source = 'dta' and d.post_id = %s and d.multi_id = %s order by v.item_id asc",
$table_prefix, $table_prefix, $post_id, $set_id);
return $wpdb->get_results($sql);
}
function get_averages($post_id, $set_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_multis_data where post_id = %s and multi_id = %s", $table_prefix, $post_id, $set_id);
return $wpdb->get_row($sql);
}
function get_vote($post_id, $set_id, $values) {
global $wpdb, $table_prefix;
$sql = sprintf("select id from %sgdsr_multis_data where post_id = %s and multi_id = %s", $table_prefix, $post_id, $set_id);
$record_id = intval($wpdb->get_var($sql));
if ($record_id == 0) {
$sql = sprintf("INSERT INTO %sgdsr_multis_data (post_id, multi_id) VALUES (%s, %s)", $table_prefix, $post_id, $set_id);
$wpdb->query($sql);
$record_id = $wpdb->insert_id;
for ($i = 0; $i < $values; $i++) {
$sql = sprintf("INSERT INTO %sgdsr_multis_values (id, source, item_id) VALUES (%s, 'dta', %s)", $table_prefix, $record_id, $i);
$wpdb->query($sql);
}
} else {
$sql = sprintf("SELECT count(*) FROM %sgdsr_multis_values WHERE id = %s AND source = 'dta'", $table_prefix, $record_id);
$counter = $wpdb->get_var($sql);
if ($counter == 0) {
for ($i = 0; $i < $values; $i++) {
$sql = sprintf("INSERT INTO %sgdsr_multis_values (id, source, item_id) VALUES (%s, 'dta', %s)", $table_prefix, $record_id, $i);
$wpdb->query($sql);
}
}
}
return $record_id;
}
function get_multi_review_average($record_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select average_review from %sgdsr_multis_data where id = %s", $table_prefix, $record_id);
return $wpdb->get_var($sql);
}
function save_review($record_id, $values = array()) {
global $wpdb, $table_prefix;
if (!is_array($values)) return;
$sql = sprintf("DELETE FROM %sgdsr_multis_values where id = %s and source = 'rvw'", $table_prefix, $record_id);
$wpdb->query($sql);
for ($i = 0; $i < count($values); $i++) {
$sql = sprintf("INSERT INTO %sgdsr_multis_values (id, source, item_id, user_voters, user_votes) VALUES (%s, 'rvw', %s, 1, '%s')",
$table_prefix, $record_id, $i, $values[$i]);
wp_gdsr_dump("INSERT", $sql);
$wpdb->query($sql);
}
}
function check_vote($id, $user, $set, $type, $ip, $mod_only = false, $mixed = false) {
$result = true;
if (!$mod_only)
$result = GDSRDBMulti::check_vote_logged($id, $user, $set, $type, $ip, $mixed);
if ($result)
$result = GDSRDBMulti::check_vote_moderated($id, $user, $set, $type, $ip, $mixed);
return $result;
}
function check_vote_logged($id, $user, $set, $type, $ip, $mixed = false) {
return GDSRDBMulti::check_vote_table('gdsr_votes_log', $id, $user, $set, $type, $ip, $mixed);
}
function check_vote_moderated($id, $user, $set, $type, $ip, $mixed = false) {
return GDSRDBMulti::check_vote_table('gdsr_moderate', $id, $user, $set, $type, $ip, $mixed);
}
function check_vote_table($table, $id, $user, $set, $type, $ip, $mixed = false) {
global $wpdb, $table_prefix;
if ($user > 0) {
$votes_sql = sprintf("SELECT count(*) FROM %s WHERE vote_type = '%s' and multi_id = %s and id = %s and user_id = %s", $table_prefix.$table, $type, $set, $id, $user);
$votes = $wpdb->get_var($votes_sql);
return $votes == 0;
}
else {
$votes_sql = sprintf("SELECT * FROM %s WHERE vote_type = '%s' and multi_id = %s and id = %s and ip = '%s'", $table_prefix.$table, $type, $set, $id, $ip);
$votes = $wpdb->get_var($votes_sql);
if ($votes > 0 && $mixed) {
$votes_sql = sprintf("SELECT * FROM %s WHERE vote_type = '%s' and user_id > 0 and multi_id = %s and id = %s and ip = '%s'", $table_prefix.$table, $type, $set, $id, $ip);
$votes_mixed = $wpdb->get_var($votes_sql);
if ($votes_mixed > 0) $votes = 0;
}
return $votes == 0;
}
}
function get_usage_count_posts($set_id) {
global $wpdb, $table_prefix;
return $wpdb->get_var(sprintf("select count(*) from %sgdsr_multis_data where (average_rating_visitors > 0 or average_rating_users > 0) and multi_id = %s", $table_prefix, $set_id));
}
function get_usage_count_post_reviews($set_id) {
global $wpdb, $table_prefix;
return $wpdb->get_var(sprintf("select count(*) from %sgdsr_multis_data where average_review > 0 and multi_id = %s", $table_prefix, $set_id));
}
function get_usage_count_voters($set_id) {
global $wpdb, $table_prefix;
$sql = sprintf("select count(*) from %sgdsr_votes_log where multi_id = %s and vote_type = 'multis'",
$table_prefix, $set_id);
return $wpdb->get_var($sql);
}
function get_multis_count() {
global $wpdb, $table_prefix;
return $wpdb->get_var(sprintf("select count(*) from %sgdsr_multis", $table_prefix));
}
function get_multis_tinymce() {
global $wpdb, $table_prefix;
$sql = sprintf("select multi_id as folder, name from %sgdsr_multis", $table_prefix);
return $wpdb->get_results($sql);
}
function get_multis($start = 0, $limit = 20) {
global $wpdb, $table_prefix;
$sql = sprintf("select * from %sgdsr_multis order by multi_id desc limit %s, %s", $table_prefix, $start, $limit);
return $wpdb->get_results($sql);
}
function get_multi_set($id = 0) {
global $wpdb, $table_prefix;
$where = $id > 0 ? " where multi_id = ".$id : "";
$sql = sprintf("select * from %sgdsr_multis%s limit 0, 1", $table_prefix, $where);
return $wpdb->get_row($sql);
}
function add_multi_set($set) {
global $wpdb, $table_prefix;
$sql = sprintf("insert into %sgdsr_multis (`name`, `description`, `stars`, `object`, `weight`, `auto_insert`, `auto_location`, `auto_categories`) values ('%s', '%s', %s, '%s', '%s', '%s', '%s', '%s')",
$table_prefix, $set->name, $set->description, $set->stars, serialize($set->object), serialize($set->weight), $set->auto_insert, $set->auto_location, $set->auto_categories
);
$wpdb->query($sql);
return $wpdb->insert_id;
}
function edit_multi_set($set) {
global $wpdb, $table_prefix;
$sql = sprintf("update %sgdsr_multis set `name` = '%s', `description` = '%s', `object` = '%s', `weight` = '%s', `auto_insert` = '%s', `auto_location` = '%s', `auto_categories` = '%s' where multi_id = %s",
$table_prefix, $set->name, $set->description, serialize($set->object), serialize($set->weight), $set->auto_insert, $set->auto_location, $set->auto_categories, $set->multi_id
);
$wpdb->query($sql);
}
}
?>