public function get_industries()
{
$industries = array();
$main_industries = Industry::getMain(true);
$i = 0;
foreach ($main_industries as $main) {
$industries[$i]['id'] = $main['id'];
$industries[$i]['name'] = $main['industry'];
$industries[$i]['job_count'] = $main['job_count'];
$industries[$i]['is_main'] = true;
$subs = Industry::getSubIndustriesOf($main['id'], true);
foreach ($subs as $sub) {
$i++;
$industries[$i]['id'] = $sub['id'];
$industries[$i]['name'] = $sub['industry'];
$industries[$i]['job_count'] = $sub['job_count'];
$industries[$i]['is_main'] = false;
}
$i++;
}
return $industries;
}
private function generate_industries($_selected, $_name = 'industry')
{
$industries = array();
$main_industries = Industry::getMain();
$i = 0;
foreach ($main_industries as $main) {
$industries[$i]['id'] = $main['id'];
$industries[$i]['name'] = $main['industry'];
$industries[$i]['is_main'] = true;
$subs = Industry::getSubIndustriesOf($main['id']);
foreach ($subs as $sub) {
$i++;
$industries[$i]['id'] = $sub['id'];
$industries[$i]['name'] = $sub['industry'];
$industries[$i]['is_main'] = false;
}
$i++;
}
echo '<select class="field" id="' . $_name . '" name="' . $_name . '">' . "\n";
if (empty($_selected) || is_null($_selected)) {
echo '<option value="0" selected>Any Specialization</option>' . "\n";
echo '<option value="0" disabled> </option>' . "\n";
}
foreach ($industries as $industry) {
$selected = '';
if ($industry['id'] == $_selected) {
$selected = 'selected';
}
if ($industry['is_main']) {
echo '<option value="' . $industry['id'] . '" class="main_industry" ' . $selected . '>';
echo $industry['name'];
} else {
echo '<option value="' . $industry['id'] . '"' . $selected . '>';
echo ' ' . $industry['name'];
}
echo '</option>' . "\n";
}
echo '</select>' . "\n";
}
private function make_query($with_limit = false)
{
$is_union_buffer = false;
// 1. work out how many match_against needed
$match_against = array();
// // resume keywords
// if (!empty($this->resume_keywords['keywords'])) {
// $match_against['resume'] = array();
//
// $keywords_str = $this->resume_keywords['keywords'];
// $mode = " WITH QUERY EXPANSION";
// if ($this->resume_keywords['is_boolean']) {
// $mode = " IN BOOLEAN MODE";
//
// if ($this->resume_keywords['is_use_all_words']) {
// $keywords_str = '+'. str_replace(' ', ' +', $this->keywords['keywords']);
// }
// }
//
// $match_against['resume']['member'] = "MATCH (resume_index.file_text)
// AGAINST ('". $keywords_str. "'". $mode. ")";
// $match_against['resume']['buffer'] = "MATCH (referral_buffers.resume_file_text)
// AGAINST ('". $keywords_str. "'". $mode. ")";
// $is_union_buffer = true;
// }
// // notes keywords
// if (!empty($this->notes_keywords['keywords'])) {
// $match_against['notes'] = array();
//
// $keywords_str = $this->notes_keywords['keywords'];
// $mode = " WITH QUERY EXPANSION";
// if ($this->notes_keywords['is_boolean']) {
// $mode = " IN BOOLEAN MODE";
//
// if ($this->notes_keywords['is_use_all_words']) {
// $keywords_str = '+'. str_replace(' ', ' +', $this->keywords['keywords']);
// }
// }
//
// $match_against['notes']['member'] = "MATCH (member_index.notes)
// AGAINST ('". $keywords_str. "'". $mode. ")";
// $match_against['notes']['buffer'] = "MATCH (referral_buffers.notes)
// AGAINST ('". $keywords_str. "'". $mode. ")";
// $is_union_buffer = true;
// }
// seeking keywords (members only)
if (!empty($this->seeking_keywords['keywords'])) {
$match_against['seeking'] = array();
$keywords_str = $this->seeking_keywords['keywords'];
$mode = " WITH QUERY EXPANSION";
if ($this->seeking_keywords['is_boolean']) {
$mode = " IN BOOLEAN MODE";
if ($this->seeking_keywords['is_use_all_words']) {
$keywords_str = '+' . str_replace(' ', ' +', $this->keywords['keywords']);
}
}
$match_against['seeking']['member'] = "MATCH (member_index.seeking) \n AGAINST ('" . $keywords_str . "'" . $mode . ")";
}
// 1.5 If filter for buffer only is turned on, then bypass the rest.
$is_bypassed = false;
// if ($this->filter == trim('members_only')) {
// $is_union_buffer = false;
// } elseif ($this->filter == trim('buffer_only')) {
// $is_union_buffer = false;
// $is_bypassed = true;
// }
$salaries = array();
$query_others = "(members.email_addr NOT LIKE '*****@*****.**' AND \n members.email_addr <> '*****@*****.**')";
if (!$is_bypassed) {
// 2. salaries
// expected
if ($this->expected_salary['start'] > 0) {
$salaries['expected'] = "members.expected_salary <= " . $this->expected_salary['start'];
if ($this->expected_salary['end'] > 0) {
$salaries['expected'] .= " AND members.expected_salary_end >= " . $this->expected_salary['end'];
}
if (!empty($this->expected_salary['currency'])) {
$salaries['expected'] .= " AND members.expected_salary_currency = '" . $this->expected_salary['currency'] . "'";
}
$salaries['expected'] = "(" . $salaries['expected'] . ")";
}
// 3. others
if (!empty($this->email_addr)) {
$query_others .= " AND members.email_addr = '" . $this->email_addr . "'";
}
if (!empty($this->name)) {
$query_others .= " AND (members.firstname LIKE '%" . $this->name . "%' OR ";
$query_others .= "members.lastname LIKE '%" . $this->name . "%')";
}
if (!empty($this->position)) {
$query_others .= " AND member_job_profiles.position_title LIKE '%" . $this->position . "%'";
}
if (!empty($this->employer)) {
$query_others .= " AND member_job_profiles.employer LIKE '%" . $this->employer . "%'";
}
if ($this->specialization > 0) {
$sub_industries = Industry::getSubIndustriesOf($this->specialization);
if (empty($sub_industries) || is_null($sub_industries)) {
$query_others .= " AND member_job_profiles.specialization = " . $this->specialization;
} else {
$this->specialization .= ', ';
foreach ($sub_industries as $i => $sub_industry) {
$this->specialization .= $sub_industry['id'];
if ($i < count($sub_industries) - 1) {
$this->specialization .= ', ';
}
}
$query_others .= " AND member_job_profiles.specialization IN (" . $this->specialization . ")";
}
}
if ($this->emp_specialization > 0) {
$sub_industries = Industry::getSubIndustriesOf($this->specialization);
if (empty($sub_industries) || is_null($sub_industries)) {
$query_others .= " AND member_job_profiles.employer_specialization = " . $this->emp_specialization;
} else {
$this->emp_specialization .= ', ';
foreach ($sub_industries as $i => $sub_industry) {
$this->emp_specialization .= $sub_industry['id'];
if ($i < count($sub_industries) - 1) {
$this->emp_specialization .= ', ';
}
}
$query_others .= " AND member_job_profiles.employer_specialization IN (" . $this->emp_specialization . ")";
}
}
if ($this->emp_desc > 0) {
$query_others .= " AND member_job_profiles.employer_description = '" . $this->emp_desc . "'";
}
if ($this->notice_period > 0) {
$query_others .= " AND members.notice_period >= " . $this->notice_period;
}
if ($this->total_work_years > 0) {
$query_others .= " AND members.total_work_years >= " . $this->total_work_years;
}
}
// 4. setup columns and joins
// $columns = array();
// if (!$is_bypassed) {
// $columns['member'] = "'0' AS buffer_id, members.email_addr, members.phone_num,
// CONCAT(members.lastname, ', ', members.firstname) AS member_name,
// resumes.name AS resume_name, resumes.file_hash, resumes.id AS resume_id";
// }
//
// if ($is_union_buffer || ($is_union_buffer === false && $is_bypassed)) {
// $columns['buffer'] = "referral_buffers.id, referral_buffers.candidate_email,
// referral_buffers.candidate_phone, referral_buffers.candidate_name,
// referral_buffers.resume_file_name, referral_buffers.resume_file_hash, '0'";
// if ($is_union_buffer === false && $is_bypassed) {
// $columns['buffer'] = "referral_buffers.id AS buffer_id,
// referral_buffers.candidate_email AS email_addr,
// referral_buffers.candidate_phone, referral_buffers.candidate_name AS member_name,
// referral_buffers.resume_file_name AS resume_name,
// referral_buffers.resume_file_hash AS file_hash,
// '0' AS resume_id";
//
// }
// }
//
// if (array_key_exists('resume', $match_against)) {
// $columns['member'] .= ", ". $match_against['resume']['member']. " AS resume_score";
//
// if ($is_union_buffer) {
// $columns['buffer'] .= ", ". $match_against['resume']['buffer'];
// }
//
// if ($is_union_buffer === false && $is_bypassed) {
// $columns['buffer'] .= ", ". $match_against['resume']['buffer']. " AS resume_score";
// }
// }
//
// if (array_key_exists('notes', $match_against)) {
// $columns['member'] .= ", ". $match_against['notes']['member']. " AS notes_score";
//
// if ($is_union_buffer) {
// $columns['buffer'] .= ", ". $match_against['notes']['buffer'];
// }
//
// if ($is_union_buffer === false && $is_bypassed) {
// $columns['buffer'] .= ", ". $match_against['notes']['buffer']. " AS notes_score";
// }
// }
//
// $joins = array();
// if (!$is_bypassed) {
// if (array_key_exists('seeking', $match_against)) {
// $columns['member'] .= ", ". $match_against['seeking']['member']. " AS seeking_score";
//
// if ($is_union_buffer) {
// $columns['buffer'] .= ", '0'";
// }
// }
//
// $joins['member'] = "LEFT JOIN resumes ON resumes.member = members.email_addr
// LEFT JOIN resume_index ON resume_index.resume = resumes.id";
// if (array_key_exists('seeking', $match_against) ||
// array_key_exists('notes', $match_against)) {
// $joins['member'] .= " LEFT JOIN member_index ON members.email_addr = member_index.member";
// }
// }
$columns = "members.email_addr, members.phone_num, members.active, \n CONCAT(members.lastname, ', ', members.firstname) AS member_name, \n DATE_FORMAT(members.updated_on, '%e %b, %Y') AS formatted_updated_on, \n members.is_active_seeking_job, COUNT(DISTINCT member_jobs.id) AS num_jobs_applied,\n NULL AS position_title, NULL AS employer, \n NULL AS formatted_work_from, NULL AS formatted_work_to ";
// if (array_key_exists('seeking', $match_against)) {
// $columns .= ", ". $match_against['seeking']['member']. " AS seeking_score";
// }
$joins = "LEFT JOIN member_jobs ON member_jobs.member = members.email_addr";
if (!empty($this->position) || !empty($this->employer) || $this->specialization > 0 || $this->emp_specialization > 0 || $this->emp_desc > 0) {
$joins .= " LEFT JOIN member_job_profiles ON member_job_profiles.member = members.email_addr";
}
if (array_key_exists('seeking', $match_against)) {
$joins .= " LEFT JOIN member_index ON members.email_addr = member_index.member";
}
// 5. setup query
$query = "";
$query = "SELECT DISTINCT " . $columns . " \n FROM members \n " . $joins . " \n WHERE ";
if (!empty($match_against)) {
$query .= $match_against['seeking']['member'] . " ";
}
if (!empty($salaries)) {
if (!empty($match_against)) {
$query .= "AND ";
}
$i = 0;
foreach ($salaries as $criteria) {
$query .= $criteria . " ";
if ($i < count($salaries) - 1) {
$query .= "AND ";
}
$i++;
}
}
if (!empty($query_others)) {
if (!empty($match_against) || !empty($salaries)) {
$query .= "AND ";
}
$query .= $query_others;
}
// if (!$is_bypassed) {
// $query = "SELECT ". $columns['member']. "
// FROM members
// ". $joins['member']. "
// WHERE ";
// if (!empty($match_against)) {
// $sub_query_array = array();
// foreach ($match_against as $table) {
// if (isset($table['member'])) {
// $sub_query_array[] = $table['member'];
// }
// }
// $query .= implode(" AND ", $sub_query_array);
// $query .= " ";
// }
//
// if (!empty($salaries)) {
// if (!empty($match_against)) {
// $query .= "AND ";
// }
//
// $i = 0;
// foreach ($salaries as $criteria) {
// $query .= $criteria. " ";
//
// if ($i < count($salaries)-1) {
// $query .= "AND ";
// }
//
// $i++;
// }
// }
//
// if (!empty($query_others)) {
// if (!empty($match_against) || !empty($salaries)) {
// $query .= "AND ";
// }
//
// $query .= $query_others;
// }
// }
// 6. setup union, if any
// if ($is_union_buffer || ($is_union_buffer === false && $is_bypassed)) {
// if (!$is_bypassed) {
// $query .= " UNION ";
// }
// $query .= "SELECT ". $columns['buffer']. "
// FROM referral_buffers
// WHERE ";
// if (!empty($match_against)) {
// $sub_query_array = array();
// foreach ($match_against as $table) {
// if (isset($table['buffer'])) {
// $sub_query_array[] = $table['buffer'];
// }
// }
// $query .= implode(" AND ", $sub_query_array);
// $query .= " ";
// }
// }
// 7. setup query order, limit and offset
// if (substr(trim($this->order_by), 0, 5) == 'score') {
// return $query;
// }
$query .= " GROUP BY members.email_addr";
$query .= " ORDER BY " . $this->order_by;
$limit = "";
if ($with_limit) {
$limit = $this->offset . ", " . $this->limit;
return $query . " LIMIT " . $limit;
}
return $query;
}
private function make_query($with_limit = false)
{
$this->log_search_criteria();
$boolean_mode = '';
// $match_against = "MATCH (job_index.title,
// job_index.description,
// job_index.state)
// AGAINST ('". $this->keywords. "' IN BOOLEAN MODE)";
$match_against = "MATCH (job_index.title) \n AGAINST ('+" . str_replace(' ', ' +', $this->keywords) . "' IN BOOLEAN MODE)";
$filter_job_status = "jobs.closed = 'N' AND jobs.deleted = FALSE AND jobs.expire_on >= CURDATE()";
$filter_employer = "jobs.employer IS NOT NULL";
if (!empty($this->employer)) {
$filter_employer = "jobs.employer = '" . $this->employer . "'";
}
$filter_industry = "jobs.industry <> 0";
if ($this->industry > 0) {
$children = Industry::getSubIndustriesOf($this->industry);
$industries = '(' . $this->industry;
if (count($children) > 0) {
$industries .= ', ';
}
$i = 0;
foreach ($children as $child) {
$industries .= $child['id'];
if ($i < count($children) - 1) {
$industries .= ', ';
}
$i++;
}
$industries .= ')';
$filter_industry = "jobs.industry IN " . $industries;
}
$filter_country = "jobs.country LIKE '%'";
if (!empty($this->country_code) && !is_null($this->country_code)) {
$filter_country = "jobs.country = '" . $this->country_code . "'";
}
$filter_salary = "";
if ($this->salary > 0) {
$filter_salary = "jobs.salary >= " . $this->salary;
if ($this->salary_end > 0) {
$filter_salary = "(jobs.salary BETWEEN " . $this->salary . " AND " . $this->salary_end . ")";
}
}
$filter_latest = "";
if ($this->special == 'latest') {
$filter_latest = "jobs.created_on BETWEEN date_add(CURDATE(), INTERVAL -5 DAY) AND CURDATE() ";
$this->offset = 0;
$this->limit = 10;
$with_limit = true;
} else {
if ($this->special == 'top') {
$this->order_by = "jobs.potential_reward DESC";
$this->offset = 0;
$this->limit = 10;
$with_limit = true;
}
}
$columns = "jobs.id, jobs.title, jobs.state, jobs.salary, jobs.salary_end, jobs.description, \n jobs.potential_reward, branches.currency, jobs.alternate_employer, \n jobs.employer AS employer_id, employers.name AS employer, \n industries.industry, industries.id AS industry_id, \n countries.country, countries.country_code, \n DATE_FORMAT(jobs.expire_on, '%e %b %Y') AS formatted_expire_on";
$joins = "job_index ON job_index.job = jobs.id, \n employers ON employers.id = jobs.employer, \n employees ON employees.id = employers.registered_by, \n branches ON branches.id = employees.branch, \n industries ON industries.id = jobs.industry, \n countries ON countries.country_code = jobs.country";
$match = "";
if (!is_null($this->keywords) && !empty($this->keywords)) {
$match .= $match_against . " AND ";
}
$match .= "jobs.deleted = FALSE \n AND " . $filter_job_status . " \n AND " . $filter_industry . " \n AND " . $filter_country . " \n AND " . $filter_employer . " ";
if (!empty($filter_salary)) {
$match .= "AND " . $filter_salary . " ";
}
if (!empty($filter_latest)) {
$match .= "AND " . $filter_latest . " ";
}
$order = $this->order_by;
$limit = "";
if ($with_limit) {
$limit = $this->offset . ", " . $this->limit;
return array('columns' => $columns, 'joins' => $joins, 'match' => $match, 'order' => $order, 'limit' => $limit);
}
return array('columns' => $columns, 'joins' => $joins, 'match' => $match, 'order' => $order);
}