}
$query = "SELECT referrals.id AS referral, currencies.symbol AS currency, referrals.total_token_reward, \n jobs.title, employers.name AS employer, \n referrals.referee AS candidate_id, referrals.employed_on, \n CONCAT(members.lastname, ', ', members.firstname) AS candidate, \n DATE_FORMAT(referrals.employed_on, '%e %b, %Y') AS formatted_employed_on, \n DATE_FORMAT(referrals.referee_confirmed_hired_on, '%e %b, %Y') AS formatted_referee_confirmed_on \n FROM referrals \n LEFT JOIN referral_token_rewards ON referral_token_rewards.referral = referrals.id \n LEFT JOIN jobs ON jobs.id = referrals.job \n LEFT JOIN members ON members.email_addr = referrals.member \n LEFT JOIN employers ON employers.id = jobs.employer \n LEFT JOIN currencies ON currencies.country_code = employers.country \n LEFT JOIN employees ON employers.registered_by = employees.id \n WHERE (referral_token_rewards.paid_on IS NULL OR referral_token_rewards.paid_on = '0000-00-00 00:00:00') AND \n (referrals.employed_on IS NOT NULL AND referrals.employed_on <> '0000-00-00 00:00:00') AND \n (referrals.member_confirmed_on IS NOT NULL AND referrals.member_confirmed_on <> '0000-00-00 00:00:00') AND \n (referrals.total_token_reward IS NOT NULL AND referrals.total_token_reward > 0) AND \n (referrals.employer_removed_on IS NULL OR referrals.employer_removed_on = '0000-00-00 00:00:00') AND \n (referrals.referee_rejected_on IS NULL OR referrals.referee_rejected_on = '0000-00-00 00:00:00') AND \n (referrals.replacement_authorized_on IS NULL OR referrals.replacement_authorized_on = '0000-00-00 00:00:00') AND \n (referrals.guarantee_expire_on <= CURDATE() OR referrals.guarantee_expire_on IS NULL) AND \n employees.branch = " . $_SESSION['yel']['employee']['branch']['id'] . " AND \n referrals.id NOT IN (SELECT referral FROM referral_token_rewards) \n GROUP BY referrals.id \n ORDER BY " . $order_by;
$mysqli = Database::connect();
$result = $mysqli->query($query);
if (count($result) <= 0 || is_null($result)) {
echo '0';
exit;
}
if (!$result) {
echo 'ko';
exit;
}
$i = 0;
$rewards = array();
foreach ($result as $row) {
$paid = ReferralReward::get_sum_paid_of_referral($row['referral']);
if ($paid[0]['amount'] <= 0 || is_null($paid)) {
//$row['member'] = htmlspecialchars_decode($row['member']);
$row['padded_invoice'] = pad($row['invoice'], 11, '0');
$row['total_token_reward'] = number_format($row['total_token_reward'], 2, '.', ', ');
$rewards[$i] = $row;
$i++;
}
}
if (count($rewards) <= 0 || is_null($rewards)) {
echo '0';
exit;
}
$response = array('rewards' => array('reward' => $rewards));
header('Content-type: text/xml');
echo $xml_dom->get_xml_from_array($response);
