function JB_list_jobs($list_mode)
{
if (func_num_args() > 1) {
// what kind of posts to show
$show = func_get_arg(1);
}
global $label;
global $post_count;
$post_count = null;
// reset post count.
#############################################
# Build the apporved SQL part
$approved_sql = " approved='Y' ";
if ($show == "WA") {
// waiting
$approved_sql = " approved='N' ";
$where_sql .= " AND `reason` ='' ";
} elseif ($show == "NA") {
// not approved
$approved_sql = " approved ='N' ";
$where_sql .= " AND `reason` !='' ";
} elseif ($show == "EX") {
// expired
$approved_sql = ' 1=1 ';
} elseif ($show == "EMP") {
// expired
$approved_sql = ' 1=1 ';
}
#############################################
# Build the ORDER BY part
$order = jb_alpha_numeric($_REQUEST['order_by']);
if ($_REQUEST['ord'] == 'asc') {
$ord = 'ASC';
} elseif ($_REQUEST['ord'] == 'desc') {
$ord = 'DESC';
} else {
$ord = 'DESC';
// sort descending by default
}
if ($order == '' || !JB_is_field_valid($order, 1)) {
// by default, order by the post_date, if the field is invalid
$order = " `post_date` ";
} elseif ($order == 'summary') {
// order by title instead
$order = JB_get_template_field_id('TITLE', 1);
} else {
$order = " `" . jb_escape_sql($order) . "` ";
}
############################################
# Search Posts
$where_sql .= JB_generate_search_sql(1);
############################################
# PREMIUM list mode
# To list only premium jobs, call like this: JB_list_jobs('PREMIUM')
if (!defined('JB_SHOW_PREMIUM_LIST')) {
// new setting since 3.4.13, may not be in config.php
JB_SHOW_PREMIUM_LIST == 'YES';
}
# Set $premium_sql
# This determines whether to:
# - include only premium posts to the list
# - include only standard posts to the list
# - do not show the premium list at all, return the call
if ($list_mode == 'PREMIUM') {
if (JB_SHOW_PREMIUM_LIST != 'YES') {
// PREMIUM list is turned off in Admin->Main Config
// do not show the premium list at all, return the call
return;
}
// - include only premium posts to the list
$premium_sql = "AND ( " . "post_mode " . "= 'premium'" . ") ";
$post_count = JB_get_post_count('PAP');
// PAP - Approved premium posts, not expired
} elseif (JB_DONT_REPEAT_PREMIUM == 'YES') {
// Premium posts are listed on top in a seperate list
// This ensures that when listing the standard posts, the premium
// posts are not repeated.
// If listing jobs on the front page, no search executed and the page is index.php
global $JB_HOME_PAGE, $JOB_LIST_PAGE;
if ($JB_HOME_PAGE | $JOB_LIST_PAGE && $list_mode == 'ALL' && JB_SHOW_PREMIUM_LIST == 'YES') {
// - include only standard posts to the list
$premium_sql .= "AND ( " . "post_mode " . "!= 'premium'" . ") ";
$post_count = JB_get_post_count('SAP');
// Approved, not premium, not expired
}
}
#############################################
# Show posts by employer?
$_REQUEST['show_emp'] = (int) $_REQUEST['show_emp'];
if ($_REQUEST['show_emp'] > 0) {
// is user_id > 0 ?
$show_emp_sql = " AND user_id='" . jb_escape_sql($_REQUEST['show_emp']) . "' ";
}
#############################################
# Get todays date (in GMT)
$now = gmdate("Y-m-d");
#############################################
# build the LIMIT part
$offset = (int) $_REQUEST['offset'];
if ($offset < 0) {
$offset = abs($offset);
}
$limit_sql = " LIMIT {$offset}, ";
if ($list_mode == 'PREMIUM') {
if (JB_PREMIUM_POSTS_LIMIT == 'YES') {
$limit_sql .= JB_PREMIUM_POSTS_PER_PAGE;
} else {
// there's no limit
$limit_sql = '';
}
} elseif ($list_mode == 'EMPLOYER') {
$limit_sql .= JB_MANAGER_POSTS_PER_PAGE;
} else {
$limit_sql .= JB_POSTS_PER_PAGE;
}
# Include a SQL_CALC_FOUND_ROWS option to count the number of posts returned
# See http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows
if ($where_sql != '' || $show_emp_sql != '' || $post_count === null) {
// If its not a search, or by listing employer, and the post count
// is unknown, we need to tell MySQL to count the posts returned without
// the LIMIT clause
$calc_found_rows_sql = 'SQL_CALC_FOUND_ROWS';
}
#############################################
# Glue the SQL query, basted on $list_mode
if ($list_mode == 'SAVED') {
$calc_found_rows_sql = 'SQL_CALC_FOUND_ROWS';
$sql = "SELECT {$calc_found_rows_sql} *, posts_table.user_id as user_id FROM `posts_table`, `saved_jobs` WHERE saved_jobs.user_id='" . jb_escape_sql($_SESSION['JB_ID']) . "' AND (saved_jobs.post_id=posts_table.post_id) AND expired='N' ORDER BY {$order} {$ord} {$limit_sql}";
} elseif ($list_mode == 'BY_CATEGORY' || $list_mode == "BY_CATEGORY_ADMIN") {
$calc_found_rows_sql = 'SQL_CALC_FOUND_ROWS';
$cat = JB_search_category_tree_for_posts();
$sql = "SELECT {$calc_found_rows_sql} * FROM posts_table where {$approved_sql} {$where_sql} {$show_emp_sql} AND expired='N' {$cat} ORDER BY ({$order}) {$ord} {$limit_sql}";
} elseif ($list_mode == 'EMPLOYER') {
// employer's post manager.
$calc_found_rows_sql = 'SQL_CALC_FOUND_ROWS';
if ($show == "OFFLINE") {
$date_range_sql = '';
// include posts that are expired.
$date_range_sql = "AND expired='Y' ";
$approved_sql = " OR (approved='N' AND user_id='" . jb_escape_sql($_SESSION['JB_ID']) . "') ";
} else {
// show current posts
$date_range_sql = "AND expired='N' ";
$approved_sql = " AND approved='Y' ";
}
$sql = "SELECT {$calc_found_rows_sql} * FROM posts_table where (1=1 {$where_sql} {$date_range_sql} AND user_id='" . jb_escape_sql($_SESSION['JB_ID']) . "') {$approved_sql} ORDER BY ({$order}) {$ord} {$limit_sql}";
} else {
if ($show == 'EX') {
// show expired?
$expired_sql = " AND expired='Y' ";
} else {
$expired_sql = " AND expired='N' ";
}
$sql = "SELECT {$calc_found_rows_sql} * FROM posts_table where {$approved_sql} {$expired_sql} {$premium_sql} {$where_sql} {$show_emp_sql} ORDER BY ({$order}) {$ord} {$limit_sql} ";
}
//echo '<hr>sql:'.$sql." where_sql:[$where_sql] show_emp:[$show_emp_sql] cat:[$cat] calc_found_rows_sql:[$calc_found_rows_sql] (LM: $list_mode)<br>";
// some debugging & performance test
//$result = JB_mysql_query("EXPLAIN ".$sql) or die ("[$sql]".mysql_error());
//$row = mysql_fetch_array($result, MYSQL_ASSOC);
//echo "<pre>";print_r($row);echo "</pre>";
//echo "<br>".$sql."<br>";
#################################
# Execute the SQL query
if (!JBPLUG_do_callback('job_list_custom_query', $result, $sql)) {
// A plugin can modify the result with a custom query
$result = JB_mysql_query($sql);
}
#################################
# Get the post_count
# If $calc_found_rows_sql was not used, then we assume that the post
# was is cashed in the database.
if ($calc_found_rows_sql == '') {
// MySQL did not count the number of posts
// that were returned, then get the cached number.
if ($list_mode == 'PREMIUM') {
$post_count = JB_get_post_count('PAP');
// premium approved
} elseif ($post_count == '') {
if ($show == 'NA') {
// not approved (admin)
$post_count = JB_get_post_count('NA');
// get non approved posts count, admin list
} elseif ($show == 'ALL') {
$post_count = JB_get_post_count('AP');
// AP - Approved (expired='N' AND approved='Y'), admin list
} elseif ($show == "WA") {
// waiting count, admin list
$post_count = JB_get_post_count('WA');
} elseif ($show == "EX") {
// expired count, admin list
$post_count = JB_get_post_count('EX');
} else {
// get all the count of all apporved and not expired
$post_count = JB_get_post_count('AP');
// AP - Approved (expired='N' AND approved='Y')
}
}
} else {
# Ask MySQL to get the number of rows from the last query
# Even though the last query had a LIMIT clause
$row = mysql_fetch_row(jb_mysql_query("SELECT FOUND_ROWS()"));
$post_count = $row[0];
}
JBPLUG_do_callback('job_list_set_count', $post_count, $list_mode);
// A plugin can modify the post count
########################################
# Print how many jobs returned
$PLM =& JB_get_PostListMarkupObject();
// load the ListMarkup Class
if ($post_count == 0) {
if ($list_mode == "PREMIUM") {
//echo "<p> </p>";
} elseif ($list_mode == "SAVED") {
//echo "<p> </p>";
} elseif ($list_mode == "BY_CATEGORY") {
//echo "<p> </p>";
} elseif ($list_mode == "EMPLOYER") {
$PLM->no_posts_employer();
} else {
$PLM->no_posts();
}
} else {
if ($list_mode == "PREMIUM") {
$PLM->sponsored_heading($post_count);
} elseif ($list_mode == "ALL") {
$label['post_list_count'] = str_replace("%COUNT%", $post_count, $label['post_list_count']);
$label['post_list_count'] = str_replace("%POSTS_DISPLAY_DAYS%", JB_POSTS_DISPLAY_DAYS, $label['post_list_count']);
$PLM->post_count($post_count);
} elseif ($list_mode == "BY_CATEGORY") {
$label['post_list_cat_count'] = str_replace("%COUNT%", $post_count, $label['post_list_cat_count']);
$label['post_list_cat_count'] = str_replace("%POSTS_DISPLAY_DAYS%", JB_POSTS_DISPLAY_DAYS, $label['post_list_cat_count']);
$PLM->post_count_category($post_count);
}
#################################################
JB_display_post_list($result, $list_mode, $show);
}
// end else if mysql num rows > 0
return $post_count;
}
function JB_echo_list_head_data($form_id, $admin)
{
global $q_string, $column_list, $column_info;
# HTML output for this function comes from ListMarkup Class
# include/themes/default/JBListMarkup.php
# Any HTML customizations should be done there.
# Please copy this class in to your custom theme directory, and
# customize form there
$LM =& JB_get_ListMarkupObject($form_id);
// load the ListMarkup Class
$LM->set_admin($admin);
if ($form_id == 1) {
global $JobListAttributes;
$q_string = $JobListAttributes->get_query_string('&');
}
$ord = strtolower($_REQUEST['ord']);
if ($ord == 'asc') {
$ord = 'desc';
} elseif ($ord == 'desc') {
$ord = 'asc';
} else {
$ord = 'asc';
}
$colspan = 0;
if (!($cached_list = jb_cache_get('column_info_' . $form_id))) {
$sql = "SELECT `template_tag`, `truncate_length`, `admin`, `linked`, `is_bold`, `no_wrap`, `clean_format`, `is_sortable`, `admin`, `field_type` FROM form_lists WHERE form_id='" . jb_escape_sql($form_id) . "' ORDER BY sort_order ASC ";
$result = JB_mysql_query($sql);
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$cached_list[] = $row;
}
jb_cache_set('column_info_' . $form_id, $cached_list);
}
foreach ($cached_list as $row) {
$colspan++;
$column_list[$row['template_tag']] = $row['template_tag'];
$column_info[$row['template_tag']]['field_type'] = $row['field_type'];
$column_info[$row['template_tag']]['trunc'] = $row['truncate_length'];
$column_info[$row['template_tag']]['admin'] = $row['admin'];
$column_info[$row['template_tag']]['link'] = $row['linked'];
$column_info[$row['template_tag']]['is_bold'] = $row['is_bold'];
$column_info[$row['template_tag']]['no_wrap'] = $row['no_wrap'];
$column_info[$row['template_tag']]['clean'] = $row['clean_format'];
$column_info[$row['template_tag']]['is_sortable'] = $row['is_sortable'];
if ($row['admin'] == 'Y' && !$admin) {
continue;
// do not render this column if not viewed by Admin.
}
$LM->list_head_cell_open($row['template_tag']);
if ($row['is_sortable'] == 'Y') {
// show column order by link?
$field_id = JB_get_template_field_id($row['template_tag'], $form_id);
if ($form_id == 1) {
// posts
// post summary is not sortable..
if ($field_id == 'summary') {
$field_id = JB_get_template_field_id('TITLE', 1);
// order by title instead!
}
}
if ($form_id == 4) {
// employers
// post count is not sortable.
if ($field_id == 'posts') {
$row['is_sortable'] = 'N';
}
// sort name by last name
if ($field_id == 'Name') {
$field_id = JB_get_template_field_id('LNAME', 4);
// order by title instead!
}
if ($field_id == 'has_profile') {
$row['is_sortable'] = 'N';
}
}
if ($form_id == 5) {
// candidates
// resume id is not sortable.
if ($field_id == 'resume_id') {
$row['is_sortable'] = 'N';
}
// sort name by last name
if ($field_id == 'Name') {
$field_id = JB_get_template_field_id('LNAME', 5);
// order by title instead!
}
}
$LM->list_head_open_link($field_id, $ord, $q_string);
// output the start of the link
}
$LM->list_head_cell_label($column_info[$row['template_tag']], $row['template_tag'], $form_id);
if ($row['is_sortable'] == 'Y') {
// show column order by link?
$LM->list_head_close_link();
}
$LM->list_head_cell_close();
}
$LM->set_column_list($column_list);
$LM->set_column_info($column_info);
return $colspan;
}
