/** * 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); }