// AJAX request to get love sent to an user include "../config.php"; include "../class.session_handler.php"; include "helper/check_session.php"; include "../functions.php"; $con = mysql_connect(DB_SERVER, DB_USER, DB_PASSWORD); mysql_select_db(DB_NAME, $con); if (empty($_REQUEST['id'])) { echo 'error: args'; return; } if (empty($_REQUEST['period_id'])) { echo 'error: args'; return; } // From user $fromUser = new User(); $fromUser->findUserById($_SESSION['userid']); $fromUsername = mysql_real_escape_string($fromUser->getUsername()); // Sent to user $user = new User(); $user->findUserById($_REQUEST['id']); $username = mysql_real_escape_string($user->getUsername()); //Get review period start date and end date $rewarder = new Rewarder(); $period = $rewarder->getPeriod($_REQUEST['period_id']); $start_date = $period['start_date']; $end_date = $period['end_date']; $love = getUserLove($username, $fromUsername, $start_date, $end_date); $total_love = getUserLove($username, "", $start_date, $end_date); echo json_encode(array($love, $total_love));
public static function getResultsForPeriodForCampaign($paid_date, $period_id = -1) { $period = Rewarder::getPeriod($period_id); $date_filter = " `period_id` = '{$period_id}' "; // get the users list of the team members $query = "CREATE TEMPORARY TABLE teamLoves\n SELECT DISTINCT " . LOVE_USERS . ".username, " . LOVE_USERS . ".nickname AS nickname, " . LOVE_USERS . ".id AS id \n FROM " . LOVE_USERS . " \n INNER JOIN " . REVIEW_REWARDER . " ON " . LOVE_USERS . ".id =" . REVIEW_REWARDER . ".receiver_id \n WHERE " . LOVE_USERS . ".username IS NOT NULL \n AND " . REVIEW_REWARDER . ".period_id = {$period['id']};\n "; $rt = mysql_query($query) or die("Couldn't execute query." . mysql_error()); $subQuery = "SELECT " . REVIEW_REWARDER . ".receiver_id AS receiver_id, IFNULL(COUNT(DISTINCT " . LOVE_LOVE . ".giver),0) AS givers " . " FROM " . LOVE_USERS . " INNER JOIN " . REVIEW_REWARDER . " ON " . LOVE_USERS . ".id =" . REVIEW_REWARDER . ".receiver_id " . " LEFT JOIN " . LOVE_LOVE . " ON username="******".receiver " . " AND " . LOVE_LOVE . ".at > '{$period['start_date']}' " . " AND " . LOVE_LOVE . ".at < '{$period['end_date']}' " . " WHERE " . LOVE_USERS . ".username IS NOT NULL " . " AND " . REVIEW_REWARDER . ".period_id = " . $period_id . " GROUP BY receiver_id "; $teamSubQuery = "SELECT " . REVIEW_REWARDER . ".receiver_id AS team_receiver_id, IFNULL(COUNT(DISTINCT " . LOVE_LOVE . ".giver),0) AS team_givers " . " FROM " . LOVE_USERS . " INNER JOIN " . REVIEW_REWARDER . " ON " . LOVE_USERS . ".id =" . REVIEW_REWARDER . ".receiver_id " . " LEFT JOIN " . LOVE_LOVE . " ON username="******".receiver " . " AND " . LOVE_LOVE . ".at > '{$period['start_date']}' " . " AND " . LOVE_LOVE . ".at < '{$period['end_date']}' " . " AND " . LOVE_LOVE . ".giver IN (SELECT username FROM teamLoves) " . " WHERE " . LOVE_USERS . ".username IS NOT NULL " . " AND " . REVIEW_REWARDER . ".period_id = " . $period_id . " GROUP BY receiver_id "; $query = "SELECT `" . REVIEW_REWARDER . "`.`receiver_id`, \n SUM(`" . REVIEW_REWARDER . "`.`rewarder_points`) AS `received_points`, \n SUM(`" . REVIEW_REWARDER . "`.`rewarded_percentage`) AS `received_percentage`, \n `givers`, `team_givers`, `username`, `nickname` \n FROM `" . REVIEW_REWARDER . "` \n LEFT JOIN " . REVIEW_USERS . " ON " . REVIEW_USERS . ".`id` = `" . REVIEW_REWARDER . "`.`receiver_id` \n LEFT JOIN ( " . $subQuery . " ) \n AS `givers_table` ON `givers_table`.`receiver_id` = `" . REVIEW_REWARDER . "`.`receiver_id` \n LEFT JOIN ( " . $teamSubQuery . " ) \n AS `team_givers_table` ON `team_givers_table`.`team_receiver_id` = `" . REVIEW_REWARDER . "`.`receiver_id` \n WHERE " . $date_filter . " GROUP BY `receiver_id` ORDER BY `received_points` DESC, givers DESC, team_givers DESC"; $rt = mysql_query($query) or die("Couldn't execute query." . mysql_error()); $receivers = array(); while ($row = mysql_fetch_assoc($rt)) { $receivers[] = $row; } return $receivers; }