2 /***********************************************************************/
4 /***********************************************************************/
5 /* Copyright (c) 2002-2009 */
6 /* Adaptive Technology Resource Centre / Inclusive Design Institute */
9 /* This program is free software. You can redistribute it and/or */
10 /* modify it under the terms of the GNU General Public License */
11 /* as published by the Free Software Foundation. */
12 /***********************************************************************/
16 var $categories; //list of available categories
17 var $cols; //sortable columns
20 $this->categories = $this->getCategories();
22 $cols['title'] = 'title';
23 $cols['created_date'] = 'created_date';
24 $cols['closing_date'] = 'closing_date';
29 * Add a job posting to the database.
30 * @param string job title
31 * @param string description
32 * @param Array categories id
33 * @param int 1 if public; 0 otherwise.
34 * @param string Closing date for this job post, mysql TIMESTAMP format
35 * @precondition ATutor Mailer class imported.
37 function addJob($title, $description, $categories, $is_public, $closing_date){
38 require(AT_INCLUDE_PATH . 'classes/phpmailer/atutormailer.class.php');
39 global $addslashes, $db, $msg, $_config, $_base_href;
41 if($_SESSION['jb_employer_id']<1){
42 $msg->addError(); //authentication error
45 include(AT_JB_INCLUDE.'Employer.class.php');
46 $employer = new Employer($_SESSION['jb_employer_id']);
47 $employer_id = $employer->getId();
50 $title = $addslashes($title);
51 $description = $addslashes($description);
52 $is_public = (isset($is_public))?1:0;
53 $closing_date = $addslashes($closing_date);
54 $approval_state = ($_config['jb_posting_approval']==1)?AT_JB_POSTING_STATUS_UNCONFIRMED:AT_JB_POSTING_STATUS_CONFIRMED;
55 $sql = 'INSERT INTO '.TABLE_PREFIX."jb_postings (employer_id, title, description, is_public, closing_date, created_date, revised_date, approval_state) VALUES ($employer_id, '$title', '$description', $is_public, '$closing_date', NOW(), NOW(), $approval_state)";
56 $result = mysql_query($sql, $db);
57 $posting_id = mysql_insert_id();
59 //add to posting category table
60 if (!empty($categories)){
61 foreach($categories as $id => $category){
62 $category = intval($category);
63 $sql = 'INSERT INTO '.TABLE_PREFIX."jb_posting_categories (posting_id, category_id) VALUES ($posting_id, $category)";
64 mysql_query($sql, $db);
66 //send out notification if the person is subscribed to the category.
67 $sql = 'SELECT m.member_id, m.email FROM '.TABLE_PREFIX.'jb_category_subscribes cs LEFT JOIN '.TABLE_PREFIX."members m ON cs.member_id=m.member_id WHERE category_id=$category";
68 $result = mysql_query($sql, $db);
69 $post_link = $_base_href . AT_JB_BASENAME . 'view_post.php?jid='.$posting_id;
71 while($row = mysql_fetch_assoc($result)){
72 $mail = new ATutorMailer;
73 $mail->AddAddress($row['email'], get_display_name($row['member_id']));
74 $body = _AT('jb_subscription_msg', $title, $this->getCategoryNameById($category), $post_link);
76 $body .= _AT('jb_posted_by').": ".htmlentities_utf8($employer->getCompany())."\n";
77 $mail->FromName = $_config['site_name'];
78 $mail->From = $_config['contact_email'];
79 $mail->Subject = _AT('jb_subscription_mail_subject');
83 $msg->addError('SENDING_ERROR');
93 //TODO: db error message
99 * Add a category, used by Admin only.
100 * @param string name of the category
102 function addCategory($name){
103 global $addslashes, $db, $msg;
105 $name = $addslashes(trim($name));
107 //don't update if it's empty.
109 $msg->addError('JB_CATEGORY_NAME_CANNOT_BE_EMPTY');
113 $sql = 'INSERT INTO '.TABLE_PREFIX."jb_categories (name) VALUES ('$name')";
114 $result = mysql_query($sql, $db);
117 //TODO: db error message
121 //add this category to the category list.
122 $row['id'] = mysql_insert_id();;
123 $row['name'] = $name;
124 $this->categories[] = $row;
125 $msg->addFeedback('JB_CATEGORY_ADDED_SUCCESSFULLY');
129 * Add an employer from the registration page.
130 * @param string username
131 * @param string password for the login
132 * @param string employer name
133 * @param string employer's email
134 * @param string the company that this employer represents
135 * @param string a brief description of the company, useful for admin approval.
136 * @param string Requested date in the format of mysql TIMESTAMP, (yyyy-mm-dd hh:mm:ss)
137 * @param string company main website.
138 * @return the ID of this employer.
140 function addEmployerRequest ($username, $password, $employer_name, $email, $company, $description, $requested_date, $website=""){
141 global $addslashes, $db, $msg;
143 $username = $addslashes($username);
144 $password = $addslashes($password);
145 $employer_name = $addslashes($employer_name);
146 $email = $addslashes($email);
147 $company = $addslashes($company);
148 $description = $addslashes($description);
149 $requested_date = $addslashes($requested_date);
150 $website = $addslashes($website);
151 $approval_status = AT_JB_STATUS_UNCONFIRMED;
153 $sql = 'INSERT INTO '.TABLE_PREFIX."jb_employers (username, password, employer_name, email, company, description, website, requested_date, approval_state) VALUES ('$username', '$password', '$employer_name', '$email', '$company', '$description', '$website', '$requested_date', $approval_status)";
154 $result = mysql_query($sql, $db);
156 //TODO: db error message
159 return mysql_insert_id();
165 * @param int ATutor's member_id
169 function addToJobCart($member_id, $job_id){
172 $member_id = intval($member_id);
173 $job_id = intval($job_id);
175 $sql = 'INSERT INTO '.TABLE_PREFIX."jb_jobcart (member_id, job_id, created_date) VALUES ($member_id, $job_id, NOW())";
176 $result = mysql_query($sql, $db);
179 //TODO: db error message
185 * Update the job posting
186 * @param int Job's id
187 * @param string job title
188 * @param string description
189 * @param Array categories id
190 * @param int 1 if public; 0 otherwise.
191 * @param string Closing date for this job post, mysql TIMESTAMP format
192 * @param int Check job_board/include/constants.inc.php
194 function updateJob($id, $title, $description, $categories, $is_public, $closing_date, $approval_state){
195 global $addslashes, $db, $msg;
198 $title = $addslashes($title);
199 $description = $addslashes($description);
200 $is_public = (isset($is_public))?1:0;
201 $closing_date = $addslashes($closing_date);
202 $approval_state = intval($approval_state);
204 $sql = 'UPDATE '.TABLE_PREFIX."jb_postings SET title='$title', description='$description', is_public=$is_public, closing_date='$closing_date', approval_state=$approval_state WHERE id=$id";
205 mysql_query($sql, $db);
207 //update to posting category table
208 if (!empty($categories)){
210 $sql = 'DELETE FROM '.TABLE_PREFIX."jb_posting_categories WHERE posting_id=$id";
211 mysql_query($sql, $db);
213 foreach($categories as $category){
214 $category = intval($category);
215 //add all the categories back.
216 $sql = 'INSERT INTO '.TABLE_PREFIX."jb_posting_categories (posting_id, category_id) VALUES ($id, $category)";
217 mysql_query($sql, $db);
223 * Update category name, used by admin only.
224 * @param int category id
225 * @param string category name
228 function updateCategory($id, $name){
229 global $addslashes, $db, $msg;
232 $name = $addslashes(trim($name));
234 //don't update if it's empty.
236 $msg->addError('JB_CATEGORY_NAME_CANNOT_BE_EMPTY');
240 $sql = 'UPDATE '.TABLE_PREFIX."jb_categories SET name='$name' WHERE id=$id";
241 $result = mysql_query($sql, $db);
248 function updateEmployer($employer_id, $company, $note){}
251 * Remove this job posting entry from the database
252 * @param int job posting id
254 function removeJob($job_id){
256 $job_id = intval($job_id);
258 //Delete all associated posting_categories
259 $sql = 'DELETE FROM '.TABLE_PREFIX."jb_posting_categories WHERE posting_id=$job_id";
260 mysql_query($sql, $db);
262 //Delete job cart posting entries
263 $sql = 'DELETE FROM '.TABLE_PREFIX."jb_jobcart WHERE job_id=$job_id";
264 mysql_query($sql, $db);
267 $sql = 'DELETE FROM '.TABLE_PREFIX."jb_postings WHERE id=$job_id";
268 mysql_query($sql, $db);
272 * Remove the category
273 * @param int category id.
275 function removeCategory($cat_id){
278 $cat_id = intval($cat_id);
283 //Remove all categories entries with this category id
284 $sql = 'DELETE FROM '.TABLE_PREFIX."jb_posting_categories WHERE category_id=$cat_id";
285 mysql_query($sql, $db);
288 $sql = 'DELETE FROM '.TABLE_PREFIX."jb_categories WHERE id=$cat_id";
289 mysql_query($sql, $db);
292 function removeEmployer($member_id){}
295 * Remove the job bookmark
296 * @param int member id
297 * @param int job posting id
300 function removeFromJobCart($member_id, $job_id){
302 $member_id = intval($member_id);
303 $job_id = intval($job_id);
305 $sql = 'DELETE FROM '.TABLE_PREFIX."jb_jobcart WHERE member_id=$member_id AND job_id=$job_id";
306 mysql_query($sql, $db);
311 * Return all the values of a single job
312 * @param int The id of the job
313 * @return Array row value of the job entry.
315 function getJob($job_id){
316 global $addslashes, $db, $msg;
317 $job_id = intval($job_id);
319 $sql = 'SELECT * FROM '.TABLE_PREFIX."jb_postings WHERE id=$job_id";
320 $rs = mysql_query($sql, $db);
322 $row = mysql_fetch_assoc($rs);
323 $row['categories'] = $this->getPostingCategories($row['id']);
331 * @param string sortable columns: title, created_date, closing_date
332 * @param string asc for ascending, else descending
333 * @param boolean true if this is an admin. If set to true. will return all
334 * entries even if it's not approved. Default is false
335 * @return Array job posts that will be shown on the given page.
336 * Return empty array if no entries.
338 function getAllJobs($col, $order, $is_admin=false){
339 global $addslashes, $db, $msg;
342 //if not admin, filter only the ones that's approved.
344 $now = date('Y-m-d H:i:s');
345 $filter_sql = "WHERE closing_date >= '$now' AND approval_state=".AT_JB_POSTING_STATUS_CONFIRMED;
351 $col = isset($this->cols[$col])?$this->cols[$col]:$this->cols['created_date'];
352 $order = ($order=='ASC')?'ASC':'DESC';
354 $sql = 'SELECT * FROM '.TABLE_PREFIX."jb_postings $filter_sql ORDER BY $col $order";
355 $rs = mysql_query($sql, $db);
357 while($row = mysql_fetch_assoc($rs)){
358 $row['categories'] = $this->getPostingCategories($row['id']);
359 $result[$row['id']] = $row;
366 * Returns a list of jobs that's created by the currented logged in employer
367 * @param string sortable columns: title, created_date, closing_date
368 * @param string asc for ascending, else descending
369 * @return Array job posts that will be shown on the given page.
371 function getMyJobs($col, $order){
372 global $addslashes, $db, $msg;
376 $col = isset($this->cols[$col])?$this->cols[$col]:$this->cols['created_date'];
377 $order = ($order=='ASC')?'ASC':'DESC';
379 $sql = 'SELECT * FROM '.TABLE_PREFIX.'jb_postings WHERE employer_id='.$_SESSION['jb_employer_id']." ORDER BY $col $order";
380 $rs = mysql_query($sql, $db);
382 while($row = mysql_fetch_assoc($rs)){
383 $row['categories'] = $this->getPostingCategories($row['id']);
384 $result[$row['id']] = $row;
392 * Returns a list of jobs that are bookmarked.
393 * @return Array job posts that are bookmarked by the ATutor user
395 function getBookmarkJobs(){
397 $member_id = $_SESSION['member_id'];
400 $sql = 'SELECT * FROM '.TABLE_PREFIX."jb_jobcart WHERE member_id=$member_id";
401 $rs = mysql_query($sql, $db);
403 while($row=mysql_fetch_assoc($rs)){
404 $result[] = $row['job_id'];
410 //returns the list of categories.
411 function getCategories(){
412 global $addslashes, $db, $msg;
415 //If this instance already have the categories, don't run the query.
416 if(!empty($this->categories)){
417 return $this->categories;
419 $sql = 'SELECT * FROM '.TABLE_PREFIX.'jb_categories order by name';
420 $rs = mysql_query($sql, $db);
423 while($row = mysql_fetch_assoc($rs)){
424 $result[$row['id']] = $row;
431 * Match the category id to its name
432 * @param int Category ID
433 * @return string the name of the category.
435 function getCategoryNameById($id){
436 foreach($this->categories as $category){
437 if ($category['id']==$id){
438 return $category['name'];
441 //if it can't find any category, then return 'no category'
442 return _AT('jb_no_category');
446 * Get the categories by the given posting id
447 * @param int posting id
448 * @return Array Array of categories integers. Null if input is an empty string.
451 function getPostingCategories($pid){
452 global $addslashes, $db;
455 $sql = 'SELECT * FROM '.TABLE_PREFIX."jb_posting_categories WHERE posting_id=$pid";
456 $rs = mysql_query($sql, $db);
459 while($row = mysql_fetch_assoc($rs)){
460 $result[] = $row['category_id'];
467 * Get the list of categories that this member is subscribed to.
468 * @param int member id
469 * @return Array list of categories
471 function getSubscribedCategories($member_id){
474 $member_id = intval($member_id);
477 $sql = 'SELECT category_id FROM '.TABLE_PREFIX."jb_category_subscribes WHERE member_id=$member_id";
478 $rs = mysql_query($sql, $db);
481 while($row = mysql_fetch_array($rs)){
489 * Perform a search with the given filters.
490 * @param Array [field]=>[input]. Format must be the following:
491 * [title] =>[string] *no longer in use
492 * [categories] =>Array(integer)
493 * [email] =>[string] *no longer in use
494 * [description]=>[string] *no longer in use
495 * [bookmark] =>[string] (on/off)
496 * [archive] =>[string] (on/off)
497 * @param string sortable columns: title, created_date, closing_date
498 * @param string asc for ascending, else descending
499 * @return Array matched entries
501 function search($input, $col, $order){
502 global $addslashes, $db;
504 //If input is not an array, quit right away.
505 if (!is_array($input)){
509 //get the search fields
510 $general = $addslashes($input['general']);
511 // $title = $addslashes($input['title']);
512 // $email = $addslashes($input['email']);
513 // $description = $addslashes($input['description']);
514 $categories = $input['categories'];
515 $bookmark = $input['bookmark'];
516 $archive = $input['archive'];
518 //create sub sql for general search
520 $general_sql = "`title` LIKE '%$general%' OR `description` LIKE '%$general%' OR ";
523 //create sub sql for the search fields.
524 //*merged with general search
526 $title_bits = explode(' ', $input['title']);
528 //concat all the title search fields together.
529 foreach($title_bits as $v){
530 $title_sql .= "`title` LIKE '%$v%' OR ";
535 * Not sure if this is actually useful.
537 $email_bits = explode(' ', $input['email']);
539 //concat all the email search fields together.
540 foreach($email_bits as $v){
541 $email_sql .= "`email` LIKE '%$v%' OR ";
546 if ($description!=''){
547 $description_bits = explode(' ', $input['description']);
548 $description_sql = '';
549 //concat all the description search fields together.
550 foreach($description_bits as $v){
551 $description_sql .= "`description` LIKE '%$v%' OR ";
555 if (is_array($categories) && !empty($categories)){
556 foreach($categories as $k=>$category_id){
557 //if 'any' is selected, use all category
558 if ($category_id==0){
559 $categories = $this->getCategories();
560 foreach ($categories as $k2=>$v2){
561 $categories[$k2] = intval($v2['id']);
565 $categories[$k] = intval($category_id);
567 $categories = '('. implode(',', $categories) . ')';
568 $categories_sql = 'RIGHT JOIN (SELECT DISTINCT posting_id FROM '.TABLE_PREFIX."jb_posting_categories WHERE category_id IN $categories) AS pc ON p.id=pc.posting_id ";
572 $bookmark_jobs = $this->getBookmarkJobs();
573 $bookmarks = '('. implode(',', $bookmark_jobs) . ')';
574 $bookmark_sql = "`id` IN $bookmarks OR ";
577 //load entries with expired closing date
579 $now = date('Y-m-d H:i:s');
580 $closing_sql = "closing_date >= '$now' AND ";
583 //only closed time and approved state
584 //this sql must go first
585 $approval_closing_sql = "($closing_sql approval_state=".AT_JB_POSTING_STATUS_CONFIRMED.')';
587 $sql_wc = $general_sql . $title_sql . $email_sql . $description_sql . $bookmark_sql; //where clause
589 $sql_wc = substr($sql_wc, 0, -3);
590 $sql_wc = ' AND ('. $sql_wc . ')';
594 $col = isset($this->cols[$col])?$this->cols[$col]:$this->cols['created_date'];
595 $order = ($order=='ASC')?'ASC':'DESC';
597 //compose the search query
598 $sql = 'SELECT p.* FROM '.TABLE_PREFIX."jb_postings AS p $categories_sql WHERE $approval_closing_sql $sql_wc ORDER BY $col $order";
599 $rs = mysql_query($sql, $db);
601 while ($row = mysql_fetch_assoc($rs)){
602 $row['categories'] = $this->getPostingCategories($row['id']);
609 function approveEmployer($member_id){}
611 function disapproveEmployer($member_id){}
615 * Update subscription for the categories. Remove existing entries first, then re-insert new ones.
616 * @param int Member id
617 * @param Array Categories IDs. [index]=>[category_id]
619 function subscribeCategories ($member_id, $categories){
622 $member_id = intval($member_id);
624 //remove old subscriptions
625 $sql = 'DELETE FROM '.TABLE_PREFIX."jb_category_subscribes WHERE member_id=$member_id";
626 mysql_query($sql, $db);
628 if (!empty($categories)){
629 foreach($categories as $category){
630 $category = intval($category);
635 //add new subscription
636 $sql = 'INSERT INTO '.TABLE_PREFIX."jb_category_subscribes (member_id, category_id) VALUES ($member_id, $category)";
637 mysql_query($sql, $db);