/** * Returns the entire job orders list. * * @param flag job order status flag * @param integer assigned-to owner/recruiter user ID (optional) * @param integer assigned-to company ID (optional) * @param integer assigned-to contact ID (optional) * @param boolean only hot job orders * @return array job orders data */ public function getAllByProject($projectID, $status = 'Active') { switch ($status) { case JOBORDERS_STATUS_ACTIVE: $statusCriterion = "AND joborder.status = 'Active'"; break; case JOBORDERS_STATUS_ONHOLDFULL: $statusCriterion = "AND joborder.status IN ('OnHold', 'Full')"; break; case JOBORDERS_STATUS_ACTIVEONHOLDFULL: $statusCriterion = "AND joborder.status IN ('Active', 'OnHold', 'Full')"; break; case JOBORDERS_STATUS_CLOSED: $statusCriterion = "AND joborder.status = 'Closed'"; break; case JOBORDERS_STATUS_ALL: default: $statusCriterion = ''; break; } $objProjectsJoborderFrom = new ClsAuieoSQLFrom(); $objSQL = new ClsAuieoSQL(); $objJoborderFrom = $objSQL->addFrom("joborder"); $joinJoborder = $objJoborderFrom->addJoinField("joborder_id"); $objProjectsJoborderFrom = $objSQL->addFrom("auieo_projects_joborder"); $joinProjectJoborder = $objProjectsJoborderFrom->addJoinField("joborderid"); $objProjectsJoborderFrom->setJoinWith($objJoborderFrom, $joinJoborder, $joinProjectJoborder); $objSQL->addSelect($objJoborderFrom, "joborder_id", "id"); $objSQL->addSelect($objJoborderFrom, "title", "joborder"); $objSQL->addSelect($objProjectsJoborderFrom, "startdate"); $objSQL->addSelect($objProjectsJoborderFrom, "targetenddate"); $objSQL->addWhere($objProjectsJoborderFrom, "projectsid", $projectID); $sql = $objSQL->render(); if (!eval(Hooks::get('JO_GET_ALL_SQL'))) { return; } return $this->_db->getAllAssoc($sql); }
/** * Returns all relevent candidate information for a given candidate ID. * * @param integer Candidate ID. * @return array Associative result set array of candidate data, or array() * if no records were returned. */ public function get($candidateID) { $objSQL = new ClsAuieoSQL(); $objFromCandidate = $objSQL->addFrom("auieo_fields"); $objSQL->addWhere($objFromCandidate, "data_item_type", 100); $objSQL->addWhere($objFromCandidate, "site_id", $this->_siteID); $objSQL->addOrderBy("sequence", false); $sql = $objSQL->render(); $arrField = $this->_db->getAllAssoc($sql); $objSQL = new ClsAuieoSQL(); $objFromCandidate = $objSQL->addFrom("candidate"); $joinIDCandidateID = $objFromCandidate->addJoinField("candidate_id"); $joinIDCandidateEnteredBy = $objFromCandidate->addJoinField("entered_by"); $joinIDCandidateOwner = $objFromCandidate->addJoinField("owner"); $joinIDCandidateEEO = $objFromCandidate->addJoinField("eeo_ethnic_type_id"); $joinIDCandidateEEOVeteran = $objFromCandidate->addJoinField("eeo_veteran_type_id"); $objFromUserAssigned = $objSQL->addFrom("user", "entered_by_user"); $joinIDEnteredUser = $objFromUserAssigned->addJoinField("user_id"); $objFromUserOwner = $objSQL->addFrom("user", "owner_user"); $joinIDOwnerUser = $objFromUserOwner->addJoinField("user_id"); $objFromCandidateJoborder = $objSQL->addFrom("candidate_joborder"); $joinIDCandidateJoborderCandidateID = $objFromCandidateJoborder->addJoinField("candidate_id"); $objFromEEO = $objSQL->addFrom("eeo_ethnic_type"); $joinIDEEOID = $objFromEEO->addJoinField("eeo_ethnic_type_id"); $objFromEEOVeteran = $objSQL->addFrom("eeo_veteran_type"); $joinIDEEOVeteranID = $objFromEEOVeteran->addJoinField("eeo_veteran_type_id"); $objFromUserAssigned->setJoinWith($objFromCandidate, $joinIDCandidateEnteredBy, $joinIDEnteredUser); $objFromUserOwner->setJoinWith($objFromCandidate, $joinIDCandidateOwner, $joinIDOwnerUser); $objFromCandidateJoborder->setJoinWith($objFromCandidate, $joinIDCandidateID, $joinIDCandidateJoborderCandidateID); $objFromEEO->setJoinWith($objFromCandidate, $joinIDCandidateEEO, $joinIDEEOID); $objFromEEOVeteran->setJoinWith($objFromCandidate, $joinIDCandidateEEOVeteran, $joinIDEEOVeteranID); $objSQL->addWhere($objFromCandidate, "candidate_id", $this->_db->makeQueryInteger($candidateID)); $objSQL->addWhere($objFromCandidate, "site_id", $this->_siteID); $objSQL->addGroupBy($objFromCandidate, "candidate_id"); $objSQL->addSelect($objFromCandidate, "candidate_id"); $objSQL->addSelect($objFromCandidate, "ownertype"); foreach ($arrField as $ind => $field) { //$alias=getAliasNameFromField($field["fieldname"]); $objSQL->addSelect($objFromCandidate, $field["fieldname"]); } //trace($objSQL->render()); /*$objSQL->addSelect($objFromCandidate, "candidate_id", "candidateID"); $objSQL->addSelect($objFromCandidate, "is_active", "isActive"); $objSQL->addSelect($objFromCandidate, "first_name", "firstName"); $objSQL->addSelect($objFromCandidate, "middle_name", "middleName"); $objSQL->addSelect($objFromCandidate, "last_name", "lastName"); $objSQL->addSelect($objFromCandidate, "email1", "email1"); $objSQL->addSelect($objFromCandidate, "email2", "email2"); $objSQL->addSelect($objFromCandidate, "phone_home", "phoneHome"); $objSQL->addSelect($objFromCandidate, "phone_work", "phoneWork"); $objSQL->addSelect($objFromCandidate, "phone_cell", "phoneCell"); $objSQL->addSelect($objFromCandidate, "address", "address"); $objSQL->addSelect($objFromCandidate, "city", "city"); $objSQL->addSelect($objFromCandidate, "state", "state"); $objSQL->addSelect($objFromCandidate, "zip", "zip"); $objSQL->addSelect($objFromCandidate, "source", "source"); $objSQL->addSelect($objFromCandidate, "key_skills", "keySkills"); $objSQL->addSelect($objFromCandidate, "current_employer", "currentEmployer"); $objSQL->addSelect($objFromCandidate, "current_pay", "currentPay"); $objSQL->addSelect($objFromCandidate, "desired_pay", "desiredPay"); $objSQL->addSelect($objFromCandidate, "notes", "notes"); $objSQL->addSelect($objFromCandidate, "owner", "owner"); $objSQL->addSelect($objFromCandidate, "can_relocate", "canRelocate"); $objSQL->addSelect($objFromCandidate, "web_site", "webSite"); $objSQL->addSelect($objFromCandidate, "best_time_to_call", "bestTimeToCall"); $objSQL->addSelect($objFromCandidate, "is_hot", "isHot"); $objSQL->addSelect($objFromCandidate, "is_admin_hidden", "isAdminHidden");*/ $objSQL->addSelectCustom("DATE_FORMAT(\n candidate.date_created, '%m-%d-%y (%h:%i %p)'\n )", "dateCreated"); $objSQL->addSelectCustom("DATE_FORMAT(\n candidate.date_modified, '%m-%d-%y (%h:%i %p)'\n )", "dateModified"); $objSQL->addSelectCustom("COUNT(\n candidate_joborder.joborder_id\n )", "pipeline"); $objSQL->addSelectCustom("(\n SELECT\n COUNT(*)\n FROM\n candidate_joborder_status_history\n WHERE\n candidate_id = " . $this->_db->makeQueryInteger($candidateID) . "\n AND\n status_to = " . PIPELINE_STATUS_SUBMITTED . "\n AND\n site_id = {$this->_siteID}\n )", "submitted"); $objSQL->addSelectCustom("CONCAT(\n candidate.first_name, ' ', candidate.last_name\n )", "candidateFullName"); $objSQL->addSelectCustom("CONCAT(\n entered_by_user.first_name, ' ', entered_by_user.last_name\n )", "enteredByFullName"); $objSQL->addSelectCustom("CONCAT(\n owner_user.first_name, ' ', owner_user.last_name\n )", "ownerFullName"); $objSQL->addSelect($objFromUserOwner, "email", "owner_email"); $objSQL->addSelectCustom("DATE_FORMAT(\n candidate.date_available, '%m-%d-%y'\n )", "dateAvailable"); $objSQL->addSelect($objFromEEO, "type", "eeoEthnicType"); $objSQL->addSelect($objFromEEOVeteran, "type", "eeoVeteranType"); $objSQL->addSelect($objFromCandidate, "eeo_disability_status", "eeoDisabilityStatus"); $objSQL->addSelect($objFromCandidate, "eeo_gender", "eeoGender"); $objSQL->addSelectCustom("IF (candidate.eeo_gender = 'm',\n 'Male',\n IF (candidate.eeo_gender = 'f',\n 'Female',\n ''))", "eeoGenderText"); $sql = $objSQL->render(); /** * */ /*trace(); $sql = sprintf( "SELECT candidate.candidate_id AS candidateID,candidate.is_active AS isActive, candidate.first_name AS firstName,candidate.middle_name AS middleName, candidate.last_name AS lastName,candidate.email1 AS email1, candidate.email2 AS email2,candidate.phone_home AS phoneHome, candidate.phone_work AS phoneWork,candidate.phone_cell AS phoneCell, candidate.address AS address,candidate.city AS city, candidate.state AS state,candidate.zip AS zip, candidate.source AS source,candidate.key_skills AS keySkills, candidate.current_employer AS currentEmployer,candidate.current_pay AS currentPay, candidate.desired_pay AS desiredPay,candidate.notes AS notes, candidate.owner AS owner,candidate.can_relocate AS canRelocate, candidate.web_site AS webSite,candidate.best_time_to_call AS bestTimeToCall, candidate.is_hot AS isHot,candidate.is_admin_hidden AS isAdminHidden, DATE_FORMAT( candidate.date_created, '%%m-%%d-%%y (%%h:%%i %%p)' ) AS dateCreated, DATE_FORMAT( candidate.date_modified, '%%m-%%d-%%y (%%h:%%i %%p)' ) AS dateModified, COUNT( candidate_joborder.joborder_id ) AS pipeline, ( SELECT COUNT(*) FROM candidate_joborder_status_history WHERE candidate_id = %s AND status_to = %s AND site_id = %s ) AS submitted, CONCAT( candidate.first_name, ' ', candidate.last_name ) AS candidateFullName, CONCAT( entered_by_user.first_name, ' ', entered_by_user.last_name ) AS enteredByFullName, CONCAT( owner_user.first_name, ' ', owner_user.last_name ) AS ownerFullName, owner_user.email AS owner_email, DATE_FORMAT( candidate.date_available, '%%m-%%d-%%y' ) AS dateAvailable, eeo_ethnic_type.type AS eeoEthnicType, eeo_veteran_type.type AS eeoVeteranType, candidate.eeo_disability_status AS eeoDisabilityStatus, candidate.eeo_gender AS eeoGender, IF (candidate.eeo_gender = 'm', 'Male', IF (candidate.eeo_gender = 'f', 'Female', '')) AS eeoGenderText FROM candidate LEFT JOIN user AS entered_by_user ON candidate.entered_by = entered_by_user.user_id LEFT JOIN user AS owner_user ON candidate.owner = owner_user.user_id LEFT JOIN candidate_joborder ON candidate.candidate_id = candidate_joborder.candidate_id LEFT JOIN eeo_ethnic_type ON eeo_ethnic_type.eeo_ethnic_type_id = candidate.eeo_ethnic_type_id LEFT JOIN eeo_veteran_type ON eeo_veteran_type.eeo_veteran_type_id = candidate.eeo_veteran_type_id WHERE candidate.candidate_id = %s AND candidate.site_id = %s GROUP BY candidate.candidate_id", $this->_db->makeQueryInteger($candidateID), PIPELINE_STATUS_SUBMITTED, $this->_siteID, $this->_db->makeQueryInteger($candidateID), $this->_siteID );*/ return $this->_db->getAssoc($sql); }
public function buildFilter(ClsAuieoSQL &$objSQL) { $objFromCandidate=$objSQL->getFromObjectByTableName($this->table); $arrWhere=array(); $objRequest=ClsNaanalRequest::getInstance(); $arrFieldFilter=$objRequest->getData("fldfilter"); if($arrFieldFilter) { $data=$objRequest->getData("data"); $condition=$objRequest->getData("condition"); $boolean=$objRequest->getData("boolean"); $group=$objRequest->getData("boolean"); foreach($arrFieldFilter as $ind=>$fldFilter) { if(empty($data[$ind])) continue; $arrWhere[]=array("field"=>$fldFilter,"data"=>$data[$ind],"condition"=>$condition[$ind],"boolean"=>$boolean[$ind],"group"=>$group[$ind]==1?true:false); } } $where=""; if($arrWhere) foreach($arrWhere as $ind=>$whr) { if(is_numeric($whr["field"])) { $sql="select * from extra_field_settings where extra_field_settings_id={$whr["field"]}"; $db = DatabaseConnection::getInstance(); $arrRow=$db->getAllAssoc($sql); $field_name=$arrRow[0]["field_name"]; $ctable="STABLE{$ind}"; $cfield="`STABLE{$ind}`.`{$field_name}`"; $cquery="select {$this->table}.{$this->table}_id as `parent_table_id{$ind}`,`ext{$ind}`.`value` AS `{$field_name}` from {$this->table} left join `extra_field` AS `ext{$ind}` ON {$this->table}.{$this->table}_id = ext{$ind}.data_item_id AND ext{$ind}.field_name='{$field_name}'"; $cfrom=$objSQL->addQuery($cquery,$ctable); $cjoin=$cfrom->addJoinField("parent_table_id{$ind}"); $ccandidate=new ClsAuieoSQLFrom(); $ccandidate->setTable("candidate"); $ccjoin=$ccandidate->addJoinField("candidate_id");//trace($cjoin); $cfrom->setJoinWith($ccandidate,$ccjoin, $cjoin); $objSQL->addSelect($cfrom, $field_name); $objWhere=$objSQL->addWhere($cfrom, $field_name, $whr["data"]); } else { $objWhere=$objSQL->addWhere($objFromCandidate, $whr["field"], $whr["data"]); $boolean=$arrWhere[$ind]["boolean"]; $objWhere->setBoolean($boolean); } if($whr["group"]==1) { $objWhere->setGroup(); } if(empty($where)) { if($whr["condition"]=="equals") { } else { $objWhere->setCondition("like"); } } else { if($whr["condition"]=="equals") { } else { $objWhere->setCondition("like"); } } } }