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