public function show()
{
$this->begin();
$this->top_search('Home');
$this->menu('member', 'home');
$this->howitworks();
$country = $this->member->getCountry();
$currency = Currency::getSymbolFromCountryCode($country);
$completeness_raw = $this->get_completeness();
$completeness_percent = 0;
$next_step = '';
$total = 0;
foreach ($completeness_raw as $key => $value) {
$total += $value;
$completeness_percent = $total / count($completeness_raw) * 100;
if ($completeness_percent < 100 && empty($next_step)) {
switch ($key) {
case 'seeking':
$next_step = 'Fill in Current Job Responsibilities / Experiences.';
break;
case 'current_salary':
$next_step = 'Fill in Current Salary range.';
break;
case 'expected_salary':
$next_step = 'Fill in Expected Salary range.';
break;
case 'preferred_job_location':
$next_step = 'Select your preferred job locations.';
break;
case 'can_travel_relocate':
$next_step = 'Fill in Willing to travel / relocate.';
break;
case 'reason_for_leaving':
$next_step = 'Fill in Reason for Leaving.';
break;
case 'notice_period':
$next_step = 'Fill in Notice Period.';
break;
case 'has_job_profiles':
$next_step = 'Add a Present & Past Position.';
break;
}
}
}
if ($completeness_percent >= 100) {
$next_step = 'Career Profile is complete.';
}
$answers = $this->get_answers();
$is_active = $answers['is_active_seeking_job'] == '1' ? true : false;
$job_profiles = $this->get_job_profiles();
$page = file_get_contents(dirname(__FILE__) . '/../../../html/member_home_page.html');
$page = str_replace('%root%', $this->url_root, $page);
$page = str_replace('%country_code%', $country, $page);
$page = str_replace('%country%', Country::getCountryFrom($country), $page);
$page = str_replace('%currency%', $currency, $page);
if (isset($_SESSION['yel']['member']['linkedin_id'])) {
if (!empty($_SESSION['yel']['member']['linkedin_id'])) {
$page = str_replace('%linkedin_copy_display%', 'block', $page);
} else {
$page = str_replace('%linkedin_copy_display%', 'none', $page);
}
} else {
$page = str_replace('%linkedin_copy_display%', 'none', $page);
}
// if (!empty($this->error_message)) {
// $page = str_replace('%error_message%', $this->error_message, $page);
// } else {
// $page = str_replace('%error_message%', '', $page);
// }
// completeness
$progress_percent = $completeness_percent;
if ($completeness_percent >= 100) {
// this is to make sure the progress bar does not touch the right edge
$progress_percent -= 1;
}
$page = str_replace('%completeness_percent%', $progress_percent, $page);
$page = str_replace('%lbl_completeness_percent%', $completeness_percent, $page);
$page = str_replace('%next_step%', $next_step, $page);
// photo
$photo_html = '<div style="text-align: center; margin: auto;">Max resolution: 200x220 pixels<br/>Max size: 150KB</div>';
if ($this->member->hasPhoto()) {
$photo_html = '<img id="photo_image" class="photo_image" src="candidate_photo.php?id=' . $this->member->getId() . '" />';
}
$page = str_replace('%photo_html%', $photo_html, $page);
// resumes
$resumes = $this->get_resumes();
$page = str_replace('%member_email%', $this->member->getId(), $page);
if (empty($resumes)) {
$page = str_replace('%no_resumes%', 'block', $page);
$page = str_replace('%resumes_table%', '', $page);
} else {
$page = str_replace('%no_resumes%', 'none', $page);
$resumes_table = new HTMLTable('resumes_table', 'resumes');
$resumes_table->set(0, 0, "Updated On", '', 'header');
$resumes_table->set(0, 1, "File Name", '', 'header');
$resumes_table->set(0, 2, " ", '', 'header actions');
foreach ($resumes as $i => $resume) {
$resumes_table->set($i + 1, 0, $resume['formatted_modified_on'], '', 'cell');
$resumes_table->set($i + 1, 1, '<a href="resume.php?id=' . $resume['id'] . '">' . $resume['file_name'] . '</a>', '', 'cell');
$resumes_table->set($i + 1, 2, '<a class="no_link" onClick="update_resume(' . $resume['id'] . ');">Update</a>', '', 'cell actions');
//$resumes_table->set($i+1, 2, '<a class="no_link" onClick="delete_resume('. $resume['id']. ');">Delete</a> | <a class="no_link" onClick="update_resume('. $resume['id']. ');">Update</a>', '', 'cell actions');
}
$page = str_replace('%resumes_table%', $resumes_table->get_html(), $page);
}
// career profile
$criteria = array('columns' => "DATE_FORMAT(imported_on, '%e %b, %Y') AS formatted_last_imported_on", 'match' => "email_addr = '" . $this->member->getId() . "'");
$result = $this->member->find($criteria);
if (!is_null($result[0]['formatted_last_imported_on']) || !empty($result[0]['formatted_last_imported_on'])) {
$page = str_replace('%last_imported_on%', 'Last imported on ' . $result[0]['formatted_last_imported_on'], $page);
} else {
$page = str_replace('%last_imported_on%', 'Not yet imported', $page);
}
$is_active_str = 'No';
if ($is_active) {
$is_active_str = 'Yes';
}
if ($answers['contact_me_for_opportunities'] == '1') {
if ($is_active_str == 'No') {
$is_active_str .= ', but ';
} else {
$is_active_str .= '; also ';
}
$is_active_str .= 'contact me if opportunities are available.';
$page = str_replace('%contact_me%', 'checked', $page);
} else {
$page = str_replace('%contact_me%', '', $page);
}
$page = str_replace('%is_active%', $is_active_str, $page);
$seeking_txt = str_replace(array("\r\n", "\r", "\n"), '<br/>', $answers['seeking']);
$page = str_replace('%seeking%', $seeking_txt, $page);
$page = str_replace('%expected_salary_currency%', $answers['expected_salary_currency'], $page);
$exp_sal = $answers['expected_salary'] <= 0 ? '(None provided)' : $answers['expected_salary'];
$page = str_replace('%expected_salary%', $exp_sal, $page);
$exp_total = $answers['expected_total_annual_package'] <= 0 ? '(None provided)' : $answers['expected_total_annual_package'];
$page = str_replace('%expected_total_annual_package%', $exp_total, $page);
$page = str_replace('%current_salary_currency%', $answers['current_salary_currency'], $page);
$cur_sal = $answers['current_salary'] <= 0 ? '(None provided)' : $answers['current_salary'];
$page = str_replace('%current_salary%', $cur_sal, $page);
$cur_total = $answers['current_total_annual_package'] <= 0 ? '(None provided)' : $answers['current_total_annual_package'];
$page = str_replace('%current_total_annual_package%', $cur_total, $page);
$page = str_replace('%pref_job_loc_1%', $answers['pref_job_location_1'], $page);
$page = str_replace('%pref_job_loc_2%', $answers['pref_job_location_2'], $page);
if ($answers['can_travel_relocate'] == 'Y') {
$page = str_replace('%can_travel%', 'Yes', $page);
} else {
$page = str_replace('%can_travel%', 'No', $page);
}
$reason_for_leaving_txt = str_replace(array("\r\n", "\r", "\n"), '<br/>', $answers['reason_for_leaving']);
$page = str_replace('%reason_for_leaving%', $reason_for_leaving_txt, $page);
$page = str_replace('%notice_period%', $answers['notice_period'], $page);
// job profiles
if (empty($job_profiles)) {
$page = str_replace('%no_positions%', 'block', $page);
$page = str_replace('%job_profiles_table%', '', $page);
} else {
$page = str_replace('%no_positions%', 'none', $page);
$job_profiles_table = new HTMLTable('job_profiles_table', 'job_profiles');
$job_profiles_table->set(0, 0, ' ', '', 'header action');
$job_profiles_table->set(0, 1, 'From', '', 'header');
$job_profiles_table->set(0, 2, 'To', '', 'header');
$job_profiles_table->set(0, 3, 'Employer', '', 'header');
$job_profiles_table->set(0, 4, 'Position', '', 'header');
$job_profiles_table->set(0, 5, ' ', '', 'header action');
foreach ($job_profiles as $i => $job_profile) {
$job_profiles_table->set($i + 1, 0, '<a class="no_link" onClick="delete_job_profile(' . $job_profile['id'] . ')">delete</a>', '', 'cell action');
$job_profiles_table->set($i + 1, 1, $job_profile['formatted_work_from'], '', 'cell');
$work_to = $job_profile['formatted_work_to'];
if (is_null($work_to) || empty($work_to) || $work_to == '0000-00-00') {
$work_to = 'Present';
}
$job_profiles_table->set($i + 1, 2, $work_to, '', 'cell');
$emp = htmlspecialchars_decode(stripslashes($job_profile['employer']));
$emp .= '<br/><span class="mini_spec">' . $job_profile['employer_specialization'] . '</span>';
$job_profiles_table->set($i + 1, 3, $emp, '', 'cell');
$pos = htmlspecialchars_decode(stripslashes($job_profile['position_title']));
$pos .= '<br/><span class="mini_spec">reporting to</span><br/>';
if (is_null($job_profile['position_superior_title']) || empty($job_profile['position_superior_title'])) {
$pos .= '<a class="no_link" onClick="show_job_profile_popup(' . $job_profile['id'] . ')">edit reporting structure</a>';
} else {
$pos .= '<span class="mini_superior">' . $job_profile['position_superior_title'] . '</span>';
}
$job_profiles_table->set($i + 1, 4, $pos, '', 'cell');
$edit = '<a class="no_link" onClick="show_job_profile_popup(' . $job_profile['id'] . ')">finish incomplete profile</a>';
if (!is_null($job_profile['summary']) && !empty($job_profile['summary']) && !is_null($job_profile['position_superior_title']) && !empty($job_profile['position_superior_title']) && !is_null($job_profile['employer_specialization']) && !empty($job_profile['employer_specialization'])) {
$edit = '<a class="no_link" onClick="show_job_profile_popup(' . $job_profile['id'] . ')">update</a>';
}
$job_profiles_table->set($i + 1, 5, $edit, '', 'cell action');
}
$page = str_replace('%job_profiles_table%', $job_profiles_table->get_html(), $page);
}
// popup windows
// photo upload
$page = str_replace('%upload_photo_member_id%', $this->member->getId(), $page);
// career summary editor
if ($is_active) {
$page = str_replace('%is_active_yes_option%', 'selected', $page);
$page = str_replace('%is_active_no_option%', '', $page);
} else {
$page = str_replace('%is_active_yes_option%', '', $page);
$page = str_replace('%is_active_no_option%', 'selected', $page);
}
$page = str_replace('%seeking_txt%', str_replace('<br/>', "\r\n", $answers['seeking']), $page);
$exp_currency_options_str = '';
foreach ($GLOBALS['currencies'] as $i => $currency) {
if ($currency == $answers['expected_salary_currency']) {
$exp_currency_options_str .= '<option value="' . $currency . '" selected>' . $currency . '</option>' . "\n";
} else {
$exp_currency_options_str .= '<option value="' . $currency . '">' . $currency . '</option>' . "\n";
}
}
$page = str_replace('%expected_salary_currency_options%', $exp_currency_options_str, $page);
$page = str_replace('%expected_salary_txt%', $answers['expected_salary'], $page);
$page = str_replace('%expected_total_txt%', $answers['expected_total_annual_package'], $page);
$cur_currency_options_str = '';
foreach ($GLOBALS['currencies'] as $i => $currency) {
if ($currency == $answers['currenty_salary_currency']) {
$cur_currency_options_str .= '<option value="' . $currency . '" selected>' . $currency . '</option>' . "\n";
} else {
$cur_currency_options_str .= '<option value="' . $currency . '">' . $currency . '</option>' . "\n";
}
}
$page = str_replace('%current_salary_currency_options%', $cur_currency_options_str, $page);
$page = str_replace('%current_salary_txt%', $answers['current_salary'], $page);
$page = str_replace('%current_total_txt%', $answers['current_total_annual_package'], $page);
$page = str_replace('%pref_job_loc_1_select%', $this->generate_countries($answers['pref_job_loc_1'], 'pref_job_loc_1'), $page);
$page = str_replace('%pref_job_loc_2_select%', $this->generate_countries($answers['pref_job_loc_2'], 'pref_job_loc_2'), $page);
if ($answers['can_travel_relocate'] == 'Y') {
$page = str_replace('%can_travel_yes', 'selected', $page);
$page = str_replace('%can_travel_no', '', $page);
} else {
$page = str_replace('%can_travel_yes', '', $page);
$page = str_replace('%can_travel_no', 'selected', $page);
}
$page = str_replace('%reason_for_leaving_txt%', str_replace('<br/>', "\r\n", $answers['reason_for_leaving']), $page);
$page = str_replace('%notice_period_txt%', $answers['notice_period'], $page);
// job profile
$page = str_replace('%work_from_month_select%', generate_month_dropdown('work_from_month', ''), $page);
$page = str_replace('%work_to_month_select%', generate_month_dropdown('work_to_month', ''), $page);
$page = str_replace('%emp_desc_select%', $this->generate_employer_description('emp_desc', -1), $page);
$page = str_replace('%industry_select%', $this->generate_industries('emp_specialization', array()), $page);
// present page
echo $page;
}
}
if ($_POST['action'] == 'resend') {
$invoice = Invoice::get($_POST['id']);
$invoice[0]['items'] = Invoice::getItems($_POST['id']);
$employer = new Employer($invoice[0]['employer']);
$recipients = $employer->getEmailAddress();
if (isset($_POST['recipients'])) {
if (!empty($_POST['recipients'])) {
$recipients = str_replace(';', ',', $_POST['recipients']);
}
}
$branch = $employer->getAssociatedBranch();
$sales = 'sales.' . strtolower($branch[0]['country']) . '@yellowelevator.com';
$branch[0]['address'] = str_replace(array("\r\n", "\r"), "\n", $branch[0]['address']);
$branch['address_lines'] = explode("\n", $branch[0]['address']);
$currency = Currency::getSymbolFromCountryCode($branch[0]['country']);
$amount_payable = 0.0;
foreach ($invoice[0]['items'] as $i => $item) {
$amount_payable += $item['amount'];
$items[$i]['amount'] = number_format($item['amount'], 2, '.', ', ');
}
$amount_payable = number_format($amount_payable, 2, '.', ', ');
// generate pdf
$pdf = new GeneralInvoice();
$pdf->AliasNbPages();
$pdf->SetAuthor('Yellow Elevator. This invoice was automatically generated. Signature is not required.');
$pdf->SetTitle($GLOBALS['COMPANYNAME'] . ' - Invoice ' . pad($invoice[0]['id'], 11, '0'));
$pdf->SetInvoiceType($invoice[0]['type'], 'Invoice');
$pdf->SetCurrency($currency);
$pdf->SetBranch($branch);
$pdf->AddPage();
public function show()
{
$this->begin();
$branch = $this->employee->getBranch();
if ($this->is_new) {
$this->top('Employer - ' . $branch[0]['country'] . ' - New Employer');
} else {
$this->top('Employer - ' . $branch[0]['country'] . ' - ' . htmlspecialchars_decode(stripslashes($this->employer->getName())));
}
$this->menu_employee('employers');
$subscriptions_rates = $GLOBALS['subscriptions_rates'];
$available_subscriptions = $subscriptions_rates[Currency::getSymbolFromCountryCode($branch[0]['country'])];
$raw_data = array();
$profile = array();
$fees = array();
$jobs = array();
if (!$this->is_new) {
// get profile
$raw_data = $this->employer->get();
$profile = $raw_data[0];
// get fees
$fees = $this->employer->getFees();
// get jobs
$jobs = $this->employer->getJobs();
} else {
if ($this->is_new && !is_null($this->employer)) {
// get profile
$raw_data = $this->employer->get();
$profile = $raw_data[0];
} else {
$profile = array('license_num' => '', 'working_months' => '12', 'payment_terms_days' => '30', 'email_addr' => '', 'contact_person' => '', 'name' => '', 'website_url' => '', 'phone_num' => '', 'address' => '', 'state' => '', 'zip' => '', 'country' => $branch[0]['country']);
}
}
?>
<!-- submenu -->
<div class="menu">
<?php
$style = 'background-color: #CCCCCC;';
?>
<ul class="menu">
<li id="item_profile" style="<?php
echo $this->current_page == 'profile' ? $style : '';
?>
"><a class="menu" onClick="show_profile();">Profile</a></li>
<?php
if (!$this->is_new) {
?>
<li id="item_fees" style="<?php
echo $this->current_page == 'fees' ? $style : '';
?>
"><a class="menu" onClick="show_fees();">Fees</a></li>
<li id="item_subscriptions" style="<?php
echo $this->current_page == 'subscriptions' ? $style : '';
?>
"><a class="menu" onClick="show_subscriptions();">Subscriptions</a></li>
<li id="item_jobs" style="<?php
echo $this->current_page == 'jobs' ? $style : '';
?>
"><a class="menu" onClick="show_jobs();">Jobs</a></li>
<?php
}
?>
</ul>
</div>
<!-- end submenu -->
<div id="div_status" class="status">
<span id="span_status" class="status"></span>
</div>
<div id="employer_profile">
<form id="profile" method="post" onSubmit="return false;">
<table class="profile_form">
<tr>
<td class="buttons_bar" colspan="2"><input type="button" onClick="save_profile();" value="Save & Update Profile" /></td>
</tr>
<tr>
<td class="label"><label for="business_license">Company/Business Registration No.:</label></td>
<td class="field"><input class="field" type="text" id="business_license" name="business_license" value="<?php
echo $profile['license_num'];
?>
" /></td>
</tr>
<tr>
<td class="title" colspan="2">Sign In Details</td>
</tr>
<tr>
<td class="label">User ID:</td>
<td class="field">
<?php
if ($this->is_new) {
?>
<input class="field" type="text" id="user_id" value="" maxlength="10" />
<?php
} else {
echo $profile['id'];
}
?>
</td>
</tr>
<tr>
<td class="label"><label for="password">Password:</label></td>
<td class="field">
<?php
if ($this->is_new) {
?>
<input type="button" value="Reset Password" onClick="reset_password();" disabled />
<?php
} else {
?>
<input type="button" value="Reset Password" onClick="reset_password();" />
<?php
}
?>
</td>
</tr>
<tr>
<td class="title" colspan="2">Contact Details</td>
</tr>
<tr>
<td class="label"><label for="email">E-mail Address:</label></td>
<td class="field"><input class="field" type="text" id="email" name="email" value="<?php
echo $profile['email_addr'];
?>
" /></td>
</tr>
<tr>
<td class="label"><label for="name">Business Name:</label></td>
<td class="field"><input class="field" type="text" id="name" name="name" value="<?php
echo htmlspecialchars_decode(stripslashes($profile['name']));
?>
" /></td>
</tr>
<tr>
<td class="label"><label for="contact_person">HR Contacts:</label></td>
<td class="field"><input class="field" type="text" id="contact_person" name="contact_person" value="<?php
echo htmlspecialchars_decode(stripslashes($profile['contact_person']));
?>
" /></td>
</tr>
<tr>
<td class="label"><label for="hr_contacts">HR Contact Emails:</label></td>
<td class="field"><input class="field" type="text" id="hr_contacts" name="hr_contacts" value="<?php
echo htmlspecialchars_decode(stripslashes($profile['hr_contacts']));
?>
" /></td>
</tr>
<tr>
<td class="label"><label for="phone_num">Telephone Numbers:</label></td>
<td class="field"><input class="field" type="text" id="phone_num" name="phone_num" value="<?php
echo $profile['phone_num'];
?>
" /></td>
</tr>
<tr>
<td class="label"><label for="fax_num">Fax Number:</label></td>
<td class="field"><input class="field" type="text" id="fax_num" name="fax_num" value="<?php
echo $profile['fax_num'];
?>
" /></td>
</tr>
<tr>
<td class="label"><label for="address">Mailing Address:</label></td>
<td class="field"><textarea id="address" name="address" ><?php
echo htmlspecialchars_decode(stripslashes($profile['address']));
?>
</textarea></td>
</tr>
<tr>
<td class="label"><label for="state">State/Province/Area:</label></td>
<td class="field"><input class="field" type="text" id="state" name="state" value="<?php
echo $profile['state'];
?>
" /></td>
</tr>
<tr>
<td class="label"><label for="zip">Zip/Postal Code:</label></td>
<td class="field"><input class="field" type="text" id="zip" name="zip" value="<?php
echo $profile['zip'];
?>
" /></td>
</tr>
<tr>
<td class="label"><label for="country">Country:</label></td>
<td class="field">
<?php
echo $this->generate_countries($profile['country']);
?>
</</td>
</tr>
<tr>
<td class="label"><label for="website_url">Web-site URL:</label></td>
<td class="field"><input class="field" type="text" id="website_url" name="website_url" value="<?php
echo htmlspecialchars_decode(stripslashes($profile['website_url']));
?>
" /></td>
</tr>
<tr>
<td class="buttons_bar" colspan="2"><input type="button" onClick="save_profile();" value="Save & Update Profile" /></td>
</tr>
</table>
</form>
</div>
<div id="employer_fees">
<div class="payment_terms">
<table class="payment_terms_table">
<tr>
<td class="label"><label for="working_months">Working Months:</label></td>
<td class="field"><input class="field_number" type="text" id="working_months" name="working_months" value="<?php
echo $profile['working_months'];
?>
" maxlength="2" /></td>
</tr>
<tr>
<td class="label"><label for="payment_terms_days">Payment Terms:</label></td>
<td class="field">
<?php
$is_selected = array('30' => '', '60' => '', '90' => '');
$is_selected[$profile['payment_terms_days']] = 'selected';
?>
<select id="payment_terms_days" name="payment_terms_days" >
<option value="30" <?php
echo $is_selected['30'];
?>
>30 days</option>
<option value="60" <?php
echo $is_selected['60'];
?>
>60 days</option>
<option value="90" <?php
echo $is_selected['90'];
?>
>90 days</option>
</select>
</td>
</tr>
<tr>
<td colspan="2" style="text-align: right; border-top: 1px solid #CCCCCC;">
<input class="button" type="button" value="Save" onClick="save_payment_terms();" />
</td>
</tr>
</table>
</div>
<div class="buttons_bar">
<input class="button" type="button" value="Add" onClick="add_new_fee();" />
</div>
<div id="fees" class="fees">
<?php
if (is_null($fees) || empty($fees) || $fees === false) {
?>
<div class="empty_results">There is no fee structure set for this employer yet.</div>
<?php
} else {
$fees_table = new HTMLTable('fees_table', 'fees_table');
$fees_table->set(0, 0, "Annual Salary From", '', 'header');
$fees_table->set(0, 1, "Annual Salary Until", '', 'header');
$fees_table->set(0, 2, "Guaranteed Period (in months)", '', 'header');
$fees_table->set(0, 3, "Service Fee (%)", '', 'header');
$fees_table->set(0, 4, "Reward (%)", '', 'header');
$fees_table->set(0, 5, " ", '', 'header action');
foreach ($fees as $i => $fee) {
$fees_table->set($i + 1, 0, number_format($fee['salary_start'], 2, '.', ','), '', 'cell');
$fees_table->set($i + 1, 1, number_format($fee['salary_end'], 2, '.', ','), '', 'cell');
$fees_table->set($i + 1, 2, $fee['guarantee_months'], '', 'cell center');
$fees_table->set($i + 1, 3, $fee['service_fee'], '', 'cell center');
$fees_table->set($i + 1, 4, $fee['reward_percentage'], '', 'cell center');
$actions = '<input type="button" value="Delete" onClick="delete_fee(' . $fee['id'] . ');" />';
$actions .= '<input type="button" value="Update" onClick="show_fee_window(' . $fee['id'] . ', \'' . number_format($fee['salary_start'], 2, '.', ',') . '\', \'' . number_format($fee['salary_end'], 2, '.', ',') . '\', \'' . $fee['guarantee_months'] . '\', \'' . $fee['service_fee'] . '\', \'' . $fee['reward_percentage'] . '\');" />';
$fees_table->set($i + 1, 5, $actions, '', 'cell action');
}
echo $fees_table->get_html();
}
?>
</div>
<div class="buttons_bar">
<input class="button" type="button" value="Add" onClick="add_new_fee();" />
</div>
</div>
<div id="employer_subscriptions">
<table class="subscription_form">
<tr>
<td class="label"><label for="subscription_period">Subscription:</label></td>
<td class="field">
<?php
$expiry = $profile['formatted_subscription_expire_on'];
if (empty($expiry) || is_null($expiry)) {
$expiry = 'No subscription purchased.';
}
$expired = '';
if ($profile['is_expired'] > 0) {
$expired = 'color: #ff0000;';
}
?>
<div>Expires On: <span id="expiry" style="<?php
echo $expired;
?>
"><?php
echo $expiry;
?>
</span></div>
<div>Purchase:
<select id="subscription_period" name="subscription_period" >
<option value="0" selected>None</option>
<option value="0" disabled> </option>
<option value="1">1 month</option>
<?php
foreach ($available_subscriptions as $month => $price) {
?>
<option value="<?php
echo $month;
?>
"><?php
echo $month;
?>
months</option>
<?php
}
?>
</select>
</div>
</td>
</tr>
<tr>
<td class="label"><label for="free_postings">Free Job Postings:</label></td>
<td class="field">
<span id="free_postings_label"><?php
echo $profile['free_postings_left'];
?>
</span>
Add:
<input class="field_number" type="text" id="free_postings" name="free_postings" value="0" maxlength="2" />
</td>
</tr>
<tr>
<td class="label"><label for="paid_postings">Paid Job Postings:</label></td>
<td class="field">
<span id="paid_postings_label"><?php
echo $profile['paid_postings_left'];
?>
</span>
Add:
<input class="field_number" type="text" id="paid_postings" name="paid_postings" value="0" maxlength="2" />
</td>
</tr>
</table>
<div class="buttons_bar">
<input class="button" type="button" value="Save" onClick="save_subscriptions();" />
</div>
</div>
<div id="employer_jobs">
<div class="buttons_bar">
<input class="button" type="button" value="Add" onClick="show_job_form_with();" />
</div>
<div id="jobs" class="jobs">
<?php
if (is_null($jobs) || empty($jobs) || $jobs === false) {
?>
<div class="empty_results">There is no job added for this employer yet.</div>
<?php
} else {
$jobs_table = new HTMLTable('jobs_table', 'jobs_table');
$jobs_table->set(0, 0, "<a class=\"sortable\" onClick=\"sort_by('jobs', 'created_on');\">Created On</a>", '', 'header');
$jobs_table->set(0, 1, "<a class=\"sortable\" onClick=\"sort_by('jobs', 'title');\">Job</a>", '', 'header');
$jobs_table->set(0, 2, "<a class=\"sortable\" onClick=\"sort_by('jobs', 'expire_on');\">Expire On</a>", '', 'header');
$jobs_table->set(0, 3, " ", '', 'header action');
foreach ($jobs as $i => $job) {
$jobs_table->set($i + 1, 0, $job['formatted_created_on'], '', 'cell');
$jobs_table->set($i + 1, 1, '<a class="no_link" onClick="show_job_form_with(' . $job['id'] . ');">' . htmlspecialchars_decode(stripslashes($job['title'])) . '</a>', '', 'cell');
$expiry = $job['formatted_expire_on'];
if ($job['expired'] <= 0) {
$expiry = '<span style="font-weight: bold; color: #ff0000;">' . $expiry . '</span>';
}
$jobs_table->set($i + 1, 2, $expiry, '', 'cell');
$actions = '<input type="button" value="Delete" onClick="delete_job(' . $job['id'] . ');" />';
$actions .= '<input type="button" value="Extend" onClick="extend_job(' . $job['id'] . ');" />';
$jobs_table->set($i + 1, 3, $actions, '', 'cell action');
}
echo $jobs_table->get_html();
}
?>
</div>
<div class="buttons_bar">
<input class="button" type="button" value="Add" onClick="show_job_form_with();" />
</div>
</div>
<div id="job">
<form method="post"onSubmit="return false;">
<input type="hidden" id="job_id" value="0" />
<table id="job_form" class="job_form">
<tr>
<td class="label"><label for="job.owner">Consultant:</label></td>
<td class="field">
<?php
$this->generate_employees();
?>
</td>
</tr>
<tr>
<td class="label"><label for="job.is_exec">Executive Search Only:</label></td>
<td class="field"><input type="checkbox" id="job.is_exec" name="is_exec" /></td>
</tr>
<tr>
<td class="label"><label for="job.title">Title:</label></td>
<td class="field"><input class="field" type="text" id="job.title" name="title" /></td>
</tr>
<tr>
<td class="label"><label for="job.alternate_employer">Alternate Employer:</label></td>
<td class="field"><input class="field" type="text" id="job.alternate_employer" name="alternate_employer" /></td>
</tr>
<tr>
<td class="label"><label for="job.contact_carbon_copy">Extra Contacts:</label></td>
<td class="field"><input class="field" type="text" id="job.contact_carbon_copy" name="contact_carbon_copy" /></td>
</tr>
<tr>
<td class="label"><label for="job.industry">Industry:</label></td>
<td class="field"><?php
$this->generate_industries('', 'job.industry');
?>
</td>
</tr>
<tr>
<td class="label"><label for="job.country">Country:</label></td>
<td class="field"><?php
$this->generate_countries('', 'job.country');
?>
</td>
</tr>
<tr>
<td class="label"><label for="job.state">State/Province/Area:</label></td>
<td class="field"><input type="text" class="field" id="job.state" name="state" /></td>
</tr>
<tr>
<td class="label"><label for="job.salary">Monthly Salary:</label></td>
<td class="field">
<?php
echo $branch[0]['currency'];
?>
$ <input class="salary" type="text" id="job.salary" name="salary" /> - <input class="salary" type="text" id="job.salary_end" name="salary_end" /><br>
<input type="checkbox" id="job.salary_negotiable" name="salary_negotiable" /> <label for="job.salary_negotiable">Negotiable</label><br/>
<p class="small_notes">This account allows you to create job ads with salary in <span id="job.employer.currency_2"></span> only. If you wish to create job ads with salary in other currencies, please log into the relevant accounts.</p>
</td>
</tr>
<tr>
<td class="label center" colspan="2"><label for="job.description">Description:</label></td>
</tr>
<tr>
<td class="field center" colspan="2">
<div class="toolbar">
<input type="button" value="Bold" onClick="insert('bold');" />
<input type="button" value="Italic" onClick="insert('italic');" />
<input type="button" value="Underline" onClick="insert('underline');" />
<input type="button" value="Highlight" onClick="insert('highlight');" />
<input type="button" value="List" onClick="insert('list');" />
<input type="button" value="Numbered List" onClick="insert('nlist');" />
<input type="button" value="List Item" onClick="insert('item');" />
</div>
<textarea id="job.description" name="description" class="job_description"></textarea>
</td>
</tr>
</table>
<div class="buttons_bar">
<input class="button" type="button" value="Cancel" onClick="show_jobs();" />
<input class="button" type="button" value="Preview" onClick="show_preview_window();" />
<input class="button" type="button" value="Publish" onClick="save_job();" />
</div>
</form>
</div>
<!-- popup windows goes here -->
<div id="fee_window" class="popup_window">
<div class="popup_window_title">Service Fee</div>
<div class="popup_fee">
<div class="note">NOTE: Enter 0 to represent ∞ for Annual Salary Until.</div>
<form id="service_fee_form" method="post" onSubmit="return false;">
<input type="hidden" id="id" name="id" value="0" />
<table class="service_fee_form">
<tr>
<td class="label"><label for="salary_start">Annual Salary Start:</label></td>
<td class="field"><input class="field" type="text" id="salary_start" name="salary_start" value="1.00" /></td>
</tr>
<tr>
<td class="label"><label for="salary_end">Annual Salary Until:</label></td>
<td class="field"><input class="field" type="text" id="salary_end" name="salary_end" value="0.00" /></td>
</tr>
<tr>
<td class="label"><label for="Guaranteed Months">Guaranteed Months:</label></td>
<td class="field"><input class="field" type="text" id="guarantee_months" name="guarantee_months" value="1" maxlength="2" /></td>
</tr>
<tr>
<td class="label"><label for="service_fee">Service Fee (%):</label></td>
<td class="field"><input class="field" type="text" id="service_fee" name="service_fee" value="" /></td>
</tr>
<tr>
<td class="label"><label for="reward_percentage">Reward (%):</label></td>
<td class="field"><input class="field" type="text" id="reward_percentage" name="reward_percentage" value="25.00" /></td>
</tr>
</table>
</form>
</div>
<div class="popup_window_buttons_bar">
<input type="button" value="Save & Close" onClick="close_fee_window(true);" />
<input type="button" value="Close" onClick="close_fee_window(false);" />
</div>
</div>
<div id="preview_window" class="popup_window">
<div class="popup_window_title">Job Description Preview</div>
<div id="preview_area"></div>
<div class="popup_window_buttons_bar">
<input type="button" value="Close" onClick="close_preview_window();" />
</div>
</div>
<?php
}
function send_invoice(&$_employer, $_paid_postings, $_subscription_period)
{
if (!is_a($_employer, 'Employer')) {
return false;
}
$today = date('Y-m-d');
$branch = $_employer->getAssociatedBranch();
$sales = 'sales.' . strtolower($branch[0]['country']) . '@yellowelevator.com';
$branch[0]['address'] = str_replace(array("\r\n", "\r"), "\n", $branch[0]['address']);
$branch['address_lines'] = explode("\n", $branch[0]['address']);
$currency = Currency::getSymbolFromCountryCode($branch[0]['country']);
if ($_paid_postings > 0) {
// 0. get the job postings pricing and currency
$posting_rates = $GLOBALS['postings_rates'];
$price = $posting_rates[$currency];
// 1. generate invoice in the system
$data = array();
$data['issued_on'] = $today;
$data['type'] = 'P';
$data['employer'] = $_POST['id'];
$data['payable_by'] = sql_date_add($today, $_employer->getPaymentTermsInDays(), 'day');
$invoice = Invoice::create($data);
if ($invoice === false) {
echo 'ko';
exit;
}
$amount = $price * $_paid_postings;
$desc = $_paid_postings . ' Job Posting(s) @ ' . $currency . ' $' . $price;
$item_added = Invoice::addItem($invoice, $amount, '1', $desc);
$items = array();
$items[0]['itemdesc'] = $desc;
$items[0]['amount'] = number_format($amount, '2', '.', ', ');
// 2. generate the invoice as PDF file
$pdf = new PaidPostingsInvoice();
$pdf->AliasNbPages();
$pdf->SetAuthor('Yellow Elevator. This invoice was automatically generated. Signature is not required.');
$pdf->SetTitle($GLOBALS['COMPANYNAME'] . ' - Invoice ' . pad($invoice, 11, '0'));
$pdf->SetCurrency($currency);
$pdf->SetBranch($branch);
$pdf->AddPage();
$pdf->SetFont('Arial', '', 10);
$pdf->SetTextColor(255, 255, 255);
$pdf->SetFillColor(54, 54, 54);
$pdf->Cell(60, 5, "Invoice Number", 1, 0, 'C', 1);
$pdf->Cell(1);
$pdf->Cell(33, 5, "Issuance Date", 1, 0, 'C', 1);
$pdf->Cell(1);
$pdf->Cell(33, 5, "Payable By", 1, 0, 'C', 1);
$pdf->Cell(1);
$pdf->Cell(0, 5, "Amount Payable (" . $currency . ")", 1, 0, 'C', 1);
$pdf->Ln(6);
$pdf->SetTextColor(0, 0, 0);
$pdf->Cell(60, 5, pad($invoice, 11, '0'), 1, 0, 'C');
$pdf->Cell(1);
$pdf->Cell(33, 5, sql_date_format($data['issued_on']), 1, 0, 'C');
$pdf->Cell(1);
$pdf->Cell(33, 5, sql_date_format($data['payable_by']), 1, 0, 'C');
$pdf->Cell(1);
$pdf->Cell(0, 5, number_format($amount, '2', '.', ', '), 1, 0, 'C');
$pdf->Ln(6);
$pdf->SetTextColor(255, 255, 255);
$pdf->Cell(60, 5, "User ID", 1, 0, 'C', 1);
$pdf->Cell(1);
$pdf->Cell(0, 5, "Employer Name", 1, 0, 'C', 1);
$pdf->Ln(6);
$pdf->SetTextColor(0, 0, 0);
$pdf->Cell(60, 5, $_employer->getId(), 1, 0, 'C');
$pdf->Cell(1);
$pdf->Cell(0, 5, $_employer->getName(), 1, 0, 'C');
$pdf->Ln(10);
$table_header = array("No.", "Item", "Amount (" . $currency . ")");
$pdf->FancyTable($table_header, $items, number_format($amount, '2', '.', ', '));
$pdf->Ln(13);
$pdf->SetFont('', 'I');
$pdf->Cell(0, 0, "This invoice was automatically generated. Signature is not required.", 0, 0, 'C');
$pdf->Ln(6);
$pdf->Cell(0, 5, "Payment Notice", 'LTR', 0, 'C');
$pdf->Ln();
$pdf->Cell(0, 5, "- Payment shall be made payable to " . $branch[0]['branch'] . ".", 'LR', 0, 'C');
$pdf->Ln();
$pdf->Cell(0, 5, "- To facilitate the processing of the payment, please write down the invoice number(s) on your cheque(s)/payment slip(s)", 'LBR', 0, 'C');
$pdf->Ln(10);
$pdf->Cell(0, 0, "E. & O. E.", 0, 0, 'C');
$pdf->Close();
$pdf->Output($GLOBALS['data_path'] . '/subscription_invoices/' . $invoice . '.pdf', 'F');
// 3. sends it as an email
$attachment = chunk_split(base64_encode(file_get_contents($GLOBALS['data_path'] . '/subscription_invoices/' . $invoice . '.pdf')));
$subject = "Subscription Invoice " . pad($invoice, 11, '0');
$headers = 'From: YellowElevator.com <*****@*****.**>' . "\n";
$headers .= 'Bcc: ' . $sales . "\n";
$headers .= 'MIME-Version: 1.0' . "\n";
$headers .= 'Content-Type: multipart/mixed; boundary="yel_mail_sep_' . $invoice . '";' . "\n\n";
$body = '--yel_mail_sep_' . $invoice . "\n";
$body .= 'Content-Type: multipart/alternative; boundary="yel_mail_sep_alt_' . $invoice . '"' . "\n";
$body .= '--yel_mail_sep_alt_' . $invoice . "\n";
$body .= 'Content-Type: text/plain; charset="iso-8859-1"' . "\n";
$body .= 'Content-Transfer-Encoding: 7bit"' . "\n";
$mail_lines = file('../private/mail/employer_posting_invoice.txt');
$message = '';
foreach ($mail_lines as $line) {
$message .= $line;
}
$message = str_replace('%employer%', $_employer->getName(), $message);
$message = str_replace('%postings%', $_POST['paid_postings'], $message);
$message = str_replace('%price%', $price, $message);
$message = str_replace('%currency%', $currency, $message);
$message = str_replace('%amount%', number_format($amount, 2, '.', ', '), $message);
$issued_date = explode('-', $data['issued_on']);
$issued_timestamp = $issued_date[0] . $issued_date[1] . $issued_date[2];
$message = str_replace('%purchased_on%', date('j M', $issued_timestamp) . ', ' . $issued_date[0], $message);
$body .= $message . "\n";
$body .= '--yel_mail_sep_alt_' . $invoice . "--\n\n";
$body .= '--yel_mail_sep_' . $invoice . "\n";
$body .= 'Content-Type: application/pdf; name="yel_invoice_' . pad($invoice, 11, '0') . '.pdf"' . "\n";
$body .= 'Content-Transfer-Encoding: base64' . "\n";
$body .= 'Content-Disposition: attachment' . "\n";
$body .= $attachment . "\n";
$body .= '--yel_mail_sep_' . $invoice . "--\n\n";
mail($_employer->getEmailAddress(), $subject, $body, $headers);
// $handle = fopen('/tmp/email_to_'. $_employer->getEmailAddress(). '.txt', 'w');
// fwrite($handle, 'Subject: '. $subject. "\n\n");
// fwrite($handle, $body);
// fclose($handle);
unlink($GLOBALS['data_path'] . '/subscription_invoices/' . $invoice . '.pdf');
}
if ($_subscription_period > 0) {
$single_subscription_prices = array('MYR' => 1000, 'SGD' => 1000);
// 0. get the job postings pricing and currency
$subscriptions_rates = $GLOBALS['subscriptions_rates'];
$amount = $subscriptions_rates[$currency][$_subscription_period];
if ($_subscription_period == 1) {
$amount = $single_subscription_prices[$currency];
}
$admin_fee = 0.05 * $amount;
$total = $admin_fee + $amount;
// 1. generate invoice in the system
$data = array();
$data['issued_on'] = $today;
$data['type'] = 'J';
$data['employer'] = $_employer->getId();
$data['payable_by'] = sql_date_add($today, $_employer->getPaymentTermsInDays(), 'day');
$invoice = Invoice::create($data);
if ($invoice === false) {
echo 'ko';
exit;
}
$desc = $_subscription_period . ' month(s) of subscription';
$item_added = Invoice::addItem($invoice, $total, '1', $desc);
$items = array();
$items[0]['itemdesc'] = $desc;
$items[0]['amount'] = number_format($amount, '2', '.', ', ');
$items[1]['itemdesc'] = 'Administration Fee';
$items[1]['amount'] = number_format($admin_fee, '2', '.', ', ');
// 2. generate the PDF version to be attached to sales.xx and the employer
$pdf = new SubscriptionInvoice();
$pdf->AliasNbPages();
$pdf->SetAuthor('Yellow Elevator. This invoice was automatically generated. Signature is not required.');
$pdf->SetTitle($GLOBALS['COMPANYNAME'] . ' - Invoice ' . pad($invoice, 11, '0'));
$pdf->SetCurrency($currency);
$pdf->SetBranch($branch);
$pdf->AddPage();
$pdf->SetFont('Arial', '', 10);
$pdf->SetTextColor(255, 255, 255);
$pdf->SetFillColor(54, 54, 54);
$pdf->Cell(60, 5, "Invoice Number", 1, 0, 'C', 1);
$pdf->Cell(1);
$pdf->Cell(33, 5, "Issuance Date", 1, 0, 'C', 1);
$pdf->Cell(1);
$pdf->Cell(33, 5, "Payable By", 1, 0, 'C', 1);
$pdf->Cell(1);
$pdf->Cell(0, 5, "Amount Payable (" . $currency . ")", 1, 0, 'C', 1);
$pdf->Ln(6);
$pdf->SetTextColor(0, 0, 0);
$pdf->Cell(60, 5, pad($invoice, 11, '0'), 1, 0, 'C');
$pdf->Cell(1);
$pdf->Cell(33, 5, sql_date_format($data['issued_on']), 1, 0, 'C');
$pdf->Cell(1);
$pdf->Cell(33, 5, sql_date_format($data['payable_by']), 1, 0, 'C');
$pdf->Cell(1);
$pdf->Cell(0, 5, number_format($total, '2', '.', ', '), 1, 0, 'C');
$pdf->Ln(6);
$pdf->SetTextColor(255, 255, 255);
$pdf->Cell(60, 5, "User ID", 1, 0, 'C', 1);
$pdf->Cell(1);
$pdf->Cell(0, 5, "Employer Name", 1, 0, 'C', 1);
$pdf->Ln(6);
$pdf->SetTextColor(0, 0, 0);
$pdf->Cell(60, 5, $_employer->getId(), 1, 0, 'C');
$pdf->Cell(1);
$pdf->Cell(0, 5, $_employer->getName(), 1, 0, 'C');
$pdf->Ln(10);
$table_header = array("No.", "Item", "Amount (" . $currency . ")");
$pdf->FancyTable($table_header, $items, number_format($total, '2', '.', ', '));
$pdf->Ln(13);
$pdf->SetFont('', 'I');
$pdf->Cell(0, 0, "This invoice was automatically generated. Signature is not required.", 0, 0, 'C');
$pdf->Ln(6);
$pdf->Cell(0, 5, "Payment Notice", 'LTR', 0, 'C');
$pdf->Ln();
$pdf->Cell(0, 5, "- Payment shall be made payable to " . $branch[0]['branch'] . ".", 'LR', 0, 'C');
$pdf->Ln();
$pdf->Cell(0, 5, "- To facilitate the processing of the payment, please write down the invoice number(s) on your cheque(s)/payment slip(s)", 'LBR', 0, 'C');
$pdf->Ln(10);
$pdf->Cell(0, 0, "E. & O. E.", 0, 0, 'C');
$pdf->Close();
$pdf->Output($GLOBALS['data_path'] . '/subscription_invoices/' . $invoice . '.pdf', 'F');
// 3. attach it to email
$attachment = chunk_split(base64_encode(file_get_contents($GLOBALS['data_path'] . '/subscription_invoices/' . $invoice . '.pdf')));
$subject = "Subscription Invoice " . pad($invoice, 11, '0');
$headers = 'From: YellowElevator.com <*****@*****.**>' . "\n";
$headers .= 'Bcc: ' . $sales . "\n";
$headers .= 'MIME-Version: 1.0' . "\n";
$headers .= 'Content-Type: multipart/mixed; boundary="yel_mail_sep_' . $invoice . '";' . "\n\n";
$body = '--yel_mail_sep_' . $invoice . "\n";
$body .= 'Content-Type: multipart/alternative; boundary="yel_mail_sep_alt_' . $invoice . '"' . "\n";
$body .= '--yel_mail_sep_alt_' . $invoice . "\n";
$body .= 'Content-Type: text/plain; charset="iso-8859-1"' . "\n";
$body .= 'Content-Transfer-Encoding: 7bit"' . "\n";
$mail_lines = file('../private/mail/employer_subscription_invoice.txt');
$message = '';
foreach ($mail_lines as $line) {
$message .= $line;
}
$message = str_replace('%employer%', $_employer->getName(), $message);
$message = str_replace('%period%', $_subscription_period, $message);
$message = str_replace('%currency%', $currency, $message);
$message = str_replace('%amount%', number_format($total, 2, '.', ', '), $message);
$issued_date = explode('-', $data['issued_on']);
$issued_timestamp = $issued_date[0] . $issued_date[1] . $issued_date[2];
$message = str_replace('%purchased_on%', date('j M', $issued_timestamp) . ', ' . $issued_date[0], $message);
$body .= $message . "\n";
$body .= '--yel_mail_sep_alt_' . $invoice . "--\n\n";
$body .= '--yel_mail_sep_' . $invoice . "\n";
$body .= 'Content-Type: application/pdf; name="yel_invoice_' . pad($invoice, 11, '0') . '.pdf"' . "\n";
$body .= 'Content-Transfer-Encoding: base64' . "\n";
$body .= 'Content-Disposition: attachment' . "\n";
$body .= $attachment . "\n";
$body .= '--yel_mail_sep_' . $invoice . "--\n\n";
mail($_employer->getEmailAddress(), $subject, $body, $headers);
/*$handle = fopen('/tmp/email_to_'. $_employer->getEmailAddress(). '.txt', 'w');
fwrite($handle, 'Subject: '. $subject. "\n\n");
fwrite($handle, $body);
fclose($handle);*/
unlink($GLOBALS['data_path'] . '/subscription_invoices/' . $invoice . '.pdf');
}
return true;
}
$member = new Member($_POST['member']);
$to_currency = Currency::getSymbolFromCountryCode($member->get_country_code());
$total = 0.0;
foreach ($result as $row) {
$from_currency = $row['currency'];
$amount = $row['total_reward'];
$total += Currency::convert_amount_from_to($from_currency, $to_currency, $amount);
}
$response = array('reward_earned' => number_format($total, 2, '.', ', '));
header('Content-type: text/xml');
echo $xml_dom->get_xml_from_array($response);
exit;
}
if ($_POST['action'] == 'get_currency_symbol') {
$member = new Member($_POST['member']);
if ($symbol = Currency::getSymbolFromCountryCode($member->get_country_code())) {
header('Content-type: text/xml');
echo $xml_dom->get_xml_from_array(array('symbol' => $symbol));
exit;
}
echo "ko";
exit;
}
if ($_POST['action'] == 'get_candidate_histories') {
$order_by = 'referred_on desc';
if (isset($_POST['order_by'])) {
$order_by = $_POST['order_by'];
}
$query = "SELECT referrals.id, jobs.title, employers.name, branches.currency, \n DATE_FORMAT(referrals.referred_on, '%e %b, %Y') AS formatted_referred_on, \n DATE_FORMAT(referrals.referee_acknowledged_on, '%e %b, %Y') AS formatted_referee_acknowledged_on, \n DATE_FORMAT(referrals.referee_acknowledged_others_on, '%e %b, %Y') AS formatted_referee_acknowledged_others_on, \n DATE_FORMAT(referrals.employed_on, '%e %b, %Y') AS formatted_employed_on, \n DATE_FORMAT(referrals.work_commence_on, '%e %b, %Y') AS formatted_work_commence_on, \n referrals.total_reward, SUM(referral_rewards.reward) AS paid_reward \n FROM referrals \n LEFT JOIN referral_rewards ON referral_rewards.referral = referrals.id \n LEFT JOIN members ON members.email_addr = referrals.referee \n LEFT JOIN jobs ON jobs.id = referrals.job \n LEFT JOIN employers ON employers.id = jobs.employer \n LEFT JOIN branches ON branches.id = employers.branch \n LEFT JOIN member_referees ON referrals.member = member_referees.member AND \n referrals.referee = member_referees.referee\n WHERE member_referees.id = " . $_POST['id'] . " AND \n (referrals.replacement_authorized_on IS NULL OR referrals.replacement_authorized_on = '0000-00-00 00:00:00') \n GROUP BY referrals.id \n ORDER BY " . $order_by;
$mysqli = Database::connect();
$result = $mysqli->query($query);