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