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