/**
* Retrieves all occupation details against a specific Enquiry.
*
* @param string $enquiryId
* The unique external Enquiry identifier.
*
* @return mixed
* An array of Model_Referencing_Occupation objects, or null if no
* occuapations are found.
*
* @todo
* Occupation referee details are not yet captured and stored.
*/
public function getAllByEnquiry($enquiryId)
{
$returnArray = array();
//First check if the reference subject is a student or unemployed - neither of these
//occupations are stored in the employment table.
$enquiryDatasource = new Datasource_ReferencingLegacy_Enquiry();
$legacyEmploymentType = $enquiryDatasource->getLegacyEmploymentType($enquiryId);
if ($enquiryDatasource->getIdentifierType($enquiryId) == Model_Referencing_ReferenceKeyTypes::EXTERNAL) {
$referenceId = $enquiryDatasource->getInternalIdentifier($enquiryId);
} else {
$referenceId = $enquiryId;
}
if ($legacyEmploymentType == 'Unemployed') {
$occupation = new Model_Referencing_Occupation();
$occupation->referenceId = $referenceId;
$occupation->importance = Model_Referencing_OccupationImportance::FIRST;
$occupation->chronology = Model_Referencing_OccupationChronology::CURRENT;
$occupation->type = Model_Referencing_OccupationTypes::UNEMPLOYMENT;
$occupation->isPermanent = true;
$occupation->income = new Zend_Currency(array('precision' => 0, 'value' => 0));
//Referees and referencing are not applicable for this occupation status.
$occupation->refereeDetails = null;
$occupation->referencingDetails = null;
$returnArray[] = $occupation;
} else {
if ($legacyEmploymentType == 'Student') {
$occupation = new Model_Referencing_Occupation();
$occupation->referenceId = $referenceId;
$occupation->importance = Model_Referencing_OccupationImportance::FIRST;
$occupation->chronology = Model_Referencing_OccupationChronology::CURRENT;
$occupation->type = Model_Referencing_OccupationTypes::STUDENT;
$occupation->isPermanent = true;
$occupation->income = new Zend_Currency(array('precision' => 0, 'value' => 0));
//Referees and referencing are not applicable for this occupation status.
$occupation->refereeDetails = null;
$occupation->referencingDetails = null;
$returnArray[] = $occupation;
}
}
//Now identify the occupations recorded in the employment table.
$select = $this->select();
$select->where('enquiryRefno = ? ', (string) $enquiryId);
$occupationsArray = $this->fetchAll($select);
if (!empty($occupationsArray)) {
foreach ($occupationsArray as $occupationRow) {
$occupation = new Model_Referencing_Occupation();
$occupation->id = $occupationRow->refno;
$occupation->referenceId = $referenceId;
$occupation->importance = $this->_getOccupationimportance($occupationRow->Jobstatus);
$occupation->chronology = $this->_getOccupationalChronology($occupationRow->Jobstatus);
//The $occupationRow->type refers to the occupational confirmation type, i.e. the
//person or means by which the reference will be provided. Therefore it needs to be
//translated into a type.
$occupation->type = $this->_getOccupationType($occupationRow->type);
if ($occupationRow->permanent == 'Yes') {
$occupation->isPermanent = true;
} else {
$occupation->isPermanent = false;
}
if (!empty($occupationRow->salary)) {
$occupation->income = new Zend_Currency(array('precision' => 0, 'value' => $occupationRow->salary));
}
if ($occupationRow->startdate != '0000-00-00') {
$occupation->startDate = new Zend_Date($occupationRow->startdate, Zend_Date::ISO_8601);
}
if ($occupationRow->complete == 1) {
$occupation->isComplete = true;
} else {
$occupation->isComplete = false;
}
//Assign the occupation variables, if applicable.
$variables = array();
if ($occupationRow->enddate != '0000-00-00') {
$endDate = new Zend_Date($occupationRow->enddate, Zend_Date::ISO_8601);
$variables[Model_Referencing_OccupationVariables::ENDDATE] = $endDate;
}
$position = $occupationRow->positionheld;
if (!empty($position)) {
$variables[Model_Referencing_OccupationVariables::POSITION] = $position;
}
$payrollNo = $occupationRow->payrollno;
if (!empty($payrollNo)) {
$variables[Model_Referencing_OccupationVariables::PAYROLL_NUMBER] = $payrollNo;
$variables[Model_Referencing_OccupationVariables::PENSION_NUMBER] = $payrollNo;
}
if (!empty($variables)) {
$occupation->variables = $variables;
}
//Add the referee details and reference details.
$occupationalReferees = new Datasource_ReferencingLegacy_OccupationalReferees();
$occupation->refereeDetails = $occupationalReferees->getOccupationalReferee($occupationRow->refno);
$occupationalReferences = new Datasource_ReferencingLegacy_OccupationalReferences();
$occupation->referencingDetails = $occupationalReferences->getByEnquiry($enquiryId, $occupation->chronology, $occupation->importance);
$returnArray[] = $occupation;
}
}
return $returnArray;
}
/**
* Search references in the legacy datasource.
*
* @param mixed $agentschemeno
* The agent scheme number.
*
* @param array $criteria
* The array of search criteria.
*
* @param string $orderBy
* Must correspond to one of the consts exposed by the Model_Referencing_SearchResult class.
*
* @param integer $pageNumber
* The current search result page number.
*
* @param integer $rowLimit
* The number of results to display on each page.
*
* @return Model_Referencing_SearchResult
* Encapsulates the search results.
*/
public function searchReferences($agentschemeno, $criteria, $orderBy, $pageNumber, $rowLimit, $offset = null)
{
$select = $this->select();
$select->setIntegrityCheck(false);
$select->from($this->_name);
$select->joinLeft('Tenant', 'Tenant.id = Enquiry.TenantID');
$select->joinLeft('progress', 'progress.refno = Enquiry.RefNo');
$select->joinLeft('Product', 'Product.ID = Enquiry.ProductID');
$select->joinLeft('property', 'property.refno = Enquiry.proprefno');
//Mandatory search items.
$select->where('AgentID = ? ', $agentschemeno);
$select->where('nochange != ? ', 'cancelled');
$select->where('proprefno != ? ', '');
// Don't look up any duplicates that may have been pushed in by Finance or MI events
$select->where('Enquiry.RefNo NOT LIKE ?', 'HLT%');
// Added during dev build and testing to remove orphan records todo: Remove for non-dev:
//$select->where('progress.start_time IS NOT NULL', '');
if (empty($criteria['refno'])) {
//Optional search items.
if (!empty($criteria['proprefno'])) {
$where = $this->quoteInto('Enquiry.proprefno = ? ', $criteria['proprefno']);
$select->where($where);
}
if (!empty($criteria['firstname']) || !empty($criteria['lastname'])) {
// Use a sub select for the join to the tenants table - approximately 1000% quicker
if (!empty($criteria['firstname']) && !empty($criteria['lastname'])) {
$where = $this->quoteInto('firstname LIKE ? AND lastname LIKE ?', $criteria['firstname'] . '%', $criteria['lastname'] . '%');
} elseif (!empty($criteria['firstname'])) {
$where = $this->quoteInto('firstname LIKE ? ', $criteria['firstname'] . '%');
} else {
$where = $this->quoteInto('lastname LIKE ?', $criteria['lastname'] . '%');
}
$select->where($where);
}
if (!empty($criteria['address'])) {
$like = '%' . $criteria['address'] . '%';
$where = $this->quoteInto('property.address1 LIKE ? ', $like);
$select->where($where);
}
if (!empty($criteria['town'])) {
$like = '%' . $criteria['town'] . '%';
$where = $this->quoteInto('property.town LIKE ? ', $like);
$select->where($where);
}
if (!empty($criteria['postcode'])) {
$like = '%' . $criteria['postcode'] . '%';
$where = $this->quoteInto('property.postcode LIKE ? ', $like);
$select->where($where);
}
if (!empty($criteria['state']) && preg_match("/complete|incomplete/i", $criteria['state'])) {
$select->where('progress.resulttx = ? ', $criteria['state']);
}
if (!empty($criteria['type'])) {
//Add product type(s) to the query.
$expandedSearchTerms = $this->_expandProductTypes($criteria['type']);
if (count($expandedSearchTerms) == 1) {
//The search term has not been expanded, so use it 'as-is'.
$where = $this->quoteInto('Product.Name = ? ', $criteria['type']);
} else {
//The search term is now two or more terms, so accommodate this.
$placeHolderString = '';
for ($i = 0; $i < count($expandedSearchTerms); $i++) {
if (empty($placeHolderString)) {
$placeHolderString = '?';
} else {
$placeHolderString .= ',?';
}
}
$where = $this->quoteInto("Product.Name IN ({$placeHolderString}) ", $expandedSearchTerms);
}
$select->where($where);
}
//Order by.
if (empty($orderBy)) {
//Apply the default orderby clause.
$select->order(array('progress.start_time DESC', 'Tenant.lastname ASC'));
} else {
//Apply the user-selected orderby clause.
switch ($orderBy) {
case Model_Referencing_SearchResult::REFNO_ASC:
$select->order('Enquiry.Refno ASC');
break;
case Model_Referencing_SearchResult::REFNO_DESC:
$select->order('Enquiry.Refno DESC');
break;
case Model_Referencing_SearchResult::FIRSTNAME_ASC:
$select->order('Tenant.firstname ASC');
break;
case Model_Referencing_SearchResult::FIRSTNAME_DESC:
$select->order('Tenant.firstname DESC');
break;
case Model_Referencing_SearchResult::LASTNAME_ASC:
$select->order('Tenant.lastname ASC');
break;
case Model_Referencing_SearchResult::LASTNAME_DESC:
$select->order('Tenant.lastname DESC');
break;
case Model_Referencing_SearchResult::ADDRESS1_ASC:
$select->order('property.address1 ASC');
break;
case Model_Referencing_SearchResult::ADDRESS1_DESC:
$select->order('property.address1 DESC');
break;
case Model_Referencing_SearchResult::STARTDATE_ASC:
$select->order('progress.start_time ASC');
break;
case Model_Referencing_SearchResult::STARTDATE_DESC:
$select->order('progress.start_time DESC');
break;
}
}
} else {
//Prepare the query based on the reference identifier only.
$eds = new Datasource_ReferencingLegacy_Enquiry();
switch ($eds->getIdentifierType($criteria['refno'])) {
case Model_Referencing_ReferenceKeyTypes::EXTERNAL:
$select->where('Enquiry.RefNo = ? ', (string) $criteria['refno']);
break;
case Model_Referencing_ReferenceKeyTypes::INTERNAL:
$select->where('Enquiry.ID = ? ', $criteria['refno']);
break;
}
}
// If offset supplied, add to SQL
if (null !== $offset) {
// If the offset is zero and row limit is zero, present only count
if (0 == $offset && 0 == $rowLimit) {
$select->reset(Zend_Db_Select::COLUMNS);
$select->columns('COUNT(`Enquiry`.`ID`) AS enquiryCount');
$select->limit($rowLimit, $offset);
$rowSet = $this->fetchAll($select);
//Encapsulate the results and return.
$searchResult = new Model_Referencing_SearchResult();
$searchResult->results = array();
$searchResult->totalNumberOfResults = $rowSet[0]['enquiryCount'];
return $searchResult;
} else {
$select->limit($rowLimit, $offset);
$rowSet = $this->fetchAll($select);
//Encapsulate the results and return.
$searchResult = new Model_Referencing_SearchResult();
$searchResult->results = $rowSet->toArray();
return $searchResult;
}
}
//Grouping
$select->group('Enquiry.RefNo');
//Run the query.
$rowSet = $this->fetchAll($select);
$totalNoOfResults = $rowSet->count();
//Apply resultset limits.
switch ($rowLimit) {
case Model_Referencing_SearchResult::TEN_ROWS:
case Model_Referencing_SearchResult::TWENTY_FIVE_ROWS:
case Model_Referencing_SearchResult::FIFTY_ROWS:
case Model_Referencing_SearchResult::ONE_HUNDRED_ROWS:
case Model_Referencing_SearchResult::ALL_ROWS:
if ($rowLimit == Model_Referencing_SearchResult::ALL_ROWS) {
$rowLimit = Model_Referencing_SearchResult::ONE_HUNDRED_ROWS;
}
//Calculate the offset.
if ($pageNumber == 1) {
$offset = 0;
} else {
$offset = $pageNumber - 1;
$offset *= $rowLimit;
}
break;
default:
throw new Zend_Exception('Invalid row limit specified.');
}
//Prepare the resultset.
$results = array();
$loopCounter = 0;
for ($i = 0; $i < $totalNoOfResults; $i++) {
if ($i < $offset) {
continue;
}
$currentRow = $rowSet->getRow($i);
$results[$loopCounter] = $currentRow->toArray();
//The following are used on the reference summary page
$results[$loopCounter]['applicantType'] = $this->_getApplicantTypeString($currentRow);
$results[$loopCounter]['refStatus'] = $this->_getReferenceStatusString($currentRow);
$loopCounter++;
if ($loopCounter == $rowLimit) {
break;
}
}
//Prepare the SearchResult object.
if ($pageNumber > 1) {
$previousPageNumber = $pageNumber - 1;
} else {
$previousPageNumber = null;
}
if ($totalNoOfResults > $pageNumber * $rowLimit) {
$nextPageNumber = $pageNumber + 1;
} else {
$nextPageNumber = null;
}
//Encapsulate the results and return.
$searchResult = new Model_Referencing_SearchResult();
$searchResult->results = $results;
$searchResult->resultsPerPage = $rowLimit;
$searchResult->previousPageNumber = $previousPageNumber;
$searchResult->currentPageNumber = $pageNumber;
$searchResult->nextPageNumber = $nextPageNumber;
$searchResult->maxPageNumber = ceil($totalNoOfResults / $rowLimit);
$searchResult->totalNumberOfResults = $totalNoOfResults;
return $searchResult;
}
