} $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);