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