public function __construct($rowsPerPage, $currentPage, $siteID, $wildCardString, $sortBy, $sortDirection) { $this->_db = DatabaseConnection::getInstance(); $this->_siteID = $siteID; $this->_sortByFields = array('firstName', 'lastName', 'city', 'state', 'dateModifiedSort', 'dateCreatedSort', 'ownerSort'); if (ENABLE_SPHINX) { /* Sphinx API likes to throw PHP errors *AND* use it's own error * handling. */ assert_options(ASSERT_WARNING, 0); $sphinx = new SphinxClient(); $sphinx->SetServer(SPHINX_HOST, SPHINX_PORT); $sphinx->SetWeights(array(0, 100, 0, 0, 50)); $sphinx->SetMatchMode(SPH_MATCH_EXTENDED); $sphinx->SetLimits(0, 1000); $sphinx->SetSortMode(SPH_SORT_TIME_SEGMENTS, 'date_added'); // FIXME: This can be sped up a bit by actually grouping ranges of // site IDs into their own index's. Maybe every 500 or so at // least on the Hosted system. $sphinx->SetFilter('site_id', array($this->_siteID)); /* Create the Sphinx query string. */ $wildCardString = DatabaseSearch::humanToSphinxBoolean($wildCardString); /* Execute the Sphinx query. Sphinx can ask us to retry if its * maxed out. Retry up to 5 times. */ $tries = 0; do { /* Wait for one second if this isn't out first attempt. */ if (++$tries > 1) { sleep(1); } $results = $sphinx->Query($wildCardString, SPHINX_INDEX); $errorMessage = $sphinx->GetLastError(); } while ($results === false && strpos($errorMessage, 'server maxed out, retry') !== false && $tries <= 5); /* Throw a fatal error if Sphinx errors occurred. */ if ($results === false) { $this->fatal('Sphinx Error: ' . ucfirst($errorMessage) . '.'); } /* Throw a fatal error (for now) if Sphinx warnings occurred. */ $lastWarning = $sphinx->GetLastWarning(); if (!empty($lastWarning)) { // FIXME: Just display a warning, and notify dev team. $this->fatal('Sphinx Warning: ' . ucfirst($lastWarning) . '.'); } /* Show warnings for assert()s again. */ assert_options(ASSERT_WARNING, 1); if (empty($results['matches'])) { $this->_WHERE = '0'; } else { $attachmentIDs = implode(',', array_keys($results['matches'])); $this->_WHERE = 'attachment.attachment_id IN(' . $attachmentIDs . ')'; } } else { $this->_WHERE = DatabaseSearch::makeBooleanSQLWhere(DatabaseSearch::fulltextEncode($wildCardString), $this->_db, 'attachment.text'); } /* How many companies do we have? */ $sql = sprintf("SELECT\n COUNT(*) AS count\n FROM\n attachment\n LEFT JOIN candidate\n ON attachment.data_item_id = candidate.candidate_id\n AND attachment.data_item_type = %s\n AND attachment.site_id = candidate.site_id\n LEFT JOIN user AS owner_user\n ON candidate.owner = owner_user.user_id\n WHERE\n resume = 1\n AND\n %s\n AND\n (ISNULL(candidate.is_admin_hidden) OR (candidate.is_admin_hidden = 0))\n AND\n (ISNULL(candidate.is_active) OR (candidate.is_active = 1))\n AND\n attachment.site_id = %s", DATA_ITEM_CANDIDATE, $this->_WHERE, $this->_siteID); $rs = $this->_db->getAssoc($sql); /* Pass "Search By Resume"-specific parameters to Pager constructor. */ parent::__construct($rs['count'], $rowsPerPage, $currentPage); }
/** * Returns any attachments for a Data Item with the specified file size * and md5sum. If $text is specified, the text of attachments are matched * instead of the raw file contents * * @param flag Data Item type flag. * @param integer Data Item ID. * @param integer File size in kB. * @param string File md5sum value. * @param string Extracted text. * @return resultset Found attachments data. */ public function getMatching($dataItemType, $dataItemID, $fileSize, $md5sum, $text = '') { if (empty($text)) { $md5Criterion = sprintf('AND md5_sum = %s AND md5_sum != \'\'', $this->_db->makeQueryString($md5sum)); } else { $md5 = md5(DatabaseSearch::fulltextEncode($text)); $md5Criterion = sprintf('AND md5_sum_text = %s AND md5_sum_text != \'\'', $this->_db->makeQueryString($md5)); } $sql = sprintf("SELECT\n attachment_id AS attachmentID,\n original_filename AS originalFilename,\n directory_name AS directoryName\n FROM\n attachment\n WHERE\n data_item_id = %s\n AND\n data_item_type = %s\n AND\n file_size_kb = %s\n AND\n file_size_kb > 0\n AND\n site_id = %s\n %s", $this->_db->makeQueryInteger($dataItemID), $this->_db->makeQueryInteger($dataItemType), $this->_db->makeQueryInteger($fileSize), $this->_siteID, $md5Criterion); return $this->_db->getAllAssoc($sql); }
/** * Highlights keywords in text for a resume preview and preforms CATS * fulltext decoding. * * @param array keywords to highlight * @param string resume text * @return string highlighted preview text */ public static function makePreview($keywords, $text) { if (empty($keywords)) { return DatabaseSearch::fulltextDecode($text); } /* CATS fulltext encode the search string. */ $keywords = DatabaseSearch::fulltextEncode($keywords); /* Create an array of keywords to highlight. */ $keywords = self::makeKeywordsArray($keywords); /* Highlight wildcards differently. */ $keywordsWild = array(); foreach ($keywords as $keyOffset => $key) { if (strpos($key, '*') !== false) { $keywordsWild[] = str_replace('*', '', $key); unset($keywords[$keyOffset]); } } $keywords = array_merge($keywords); if (!empty($keywordsWild)) { $regex = implode('|', array_map(create_function('$string', 'return preg_quote($string, \'/\');'), $keywordsWild)); $text = preg_replace('/(' . $regex . ')/i', '<span style="background-color: #ffff99">\\1</span>', $text); } if (!empty($keywords)) { $regex = implode('|', array_map(create_function('$string', 'return preg_quote($string, \'/\');'), $keywords)); $text = preg_replace('/\\b(' . $regex . ')\\b/i', '<span style="background-color: #ffff99">\\1</span>', $text); } return DatabaseSearch::fulltextDecode($text); }
public function __construct($rowsPerPage, $currentPage, $siteID, $wildCardString, $sortBy, $sortDirection) { $this->_db = DatabaseConnection::getInstance(); $this->_siteID = $siteID; $this->_sortByFields = array('firstName', 'lastName', 'city', 'state', 'dateModifiedSort', 'dateCreatedSort', 'ownerSort'); if (ENABLE_SPHINX) { /* Sphinx API likes to throw PHP errors *AND* use it's own error * handling. */ assert_options(ASSERT_WARNING, 0); $sphinx = new SphinxClient(); $sphinx->SetServer(SPHINX_HOST, SPHINX_PORT); $sphinx->SetWeights(array(0, 100, 0, 0, 50)); $sphinx->SetMatchMode(SPH_MATCH_EXTENDED); $sphinx->SetLimits(0, 1000); $sphinx->SetSortMode(SPH_SORT_TIME_SEGMENTS, 'date_added'); // FIXME: This can be sped up a bit by actually grouping ranges of // site IDs into their own index's. Maybe every 500 or so at // least on the Hosted system. $sphinx->SetFilter('site_id', array($this->_siteID)); /* Create the Sphinx query string. */ $wildCardString = DatabaseSearch::humanToSphinxBoolean($wildCardString); /* Execute the Sphinx query. Sphinx can ask us to retry if its * maxed out. Retry up to 5 times. */ $tries = 0; do { /* Wait for one second if this isn't out first attempt. */ if (++$tries > 1) { sleep(1); } $results = $sphinx->Query($wildCardString, SPHINX_INDEX); $errorMessage = $sphinx->GetLastError(); } while ($results === false && strpos($errorMessage, 'server maxed out, retry') !== false && $tries <= 5); /* Throw a fatal error if Sphinx errors occurred. */ if ($results === false) { $this->fatal('Sphinx Error: ' . ucfirst($errorMessage) . '.'); } /* Throw a fatal error (for now) if Sphinx warnings occurred. */ $lastWarning = $sphinx->GetLastWarning(); if (!empty($lastWarning)) { // FIXME: Just display a warning, and notify dev team. $this->fatal('Sphinx Warning: ' . ucfirst($lastWarning) . '.'); } /* Show warnings for assert()s again. */ assert_options(ASSERT_WARNING, 1); if (empty($results['matches'])) { $this->_WHERE = '0'; } else { $attachmentIDs = implode(',', array_keys($results['matches'])); $this->_WHERE = 'attachment.attachment_id IN(' . $attachmentIDs . ')'; } } else { $tmp = DatabaseSearch::makeBooleanSQLWhere(DatabaseSearch::fulltextEncode($wildCardString), $this->_db, 'attachment.text'); $this->_WHERE = str_replace(") (", ") AND (", $tmp); /*trace($tmp); $matches=array(); preg_match_all('/"(?:\\\\.|[^\\\\"])*"|\S+/', $wildCardString, $matches); $arrWhere=array(); $arrNew=array(); for($m=0;$m<count($matches[0]);$m++) { $match=$matches[0][$m]; if($match[0]=='"') { $arrNew[]=$match; } else { $tmp=""; for($n=$m;$n<count($matches[0]);$n++) { $match=$matches[0][$n]; if($match[0]=='"' || strtolower($match)=="and" || strtolower($match)=="or") { $m=$n-1; break; } if($tmp==="") { $tmp=$match; } else { $tmp=$tmp." ".$match; } } $tmp='"'.$tmp.'"'; $arrNew[]=$tmp; } } foreach($arrNew as $match) { $arrWhere[] = DatabaseSearch::makeBooleanSQLWhere( DatabaseSearch::fulltextEncode($match), $this->_db, 'attachment.text' ); } $this->_WHERE=implode(" AND ",$arrWhere);*/ } $sbase = new SearchBase(); $arrFilter = $sbase->buildFilter(); $filter = $arrFilter["where"]; $column = $arrFilter["extra_column"]; $join = $arrFilter["extra_join"]; $isSearchAttachment = isset($_REQUEST["bulk_resume"]) && $_REQUEST["bulk_resume"]; if ($isSearchAttachment) { /* How many companies do we have? */ $sql = sprintf("SELECT\r\n count(*) AS count\r\n FROM\r\n attachment\r\n LEFT JOIN candidate\r\n ON attachment.data_item_id = candidate.candidate_id\r\n AND attachment.site_id = candidate.site_id\r\n LEFT JOIN user AS owner_user\r\n ON candidate.owner = owner_user.user_id\r\n %s\r\n WHERE\r\n resume = 1\r\n AND\r\n %s\r\n AND\r\n (attachment.data_item_type = %s OR attachment.data_item_type = %s)\r\n AND\r\n attachment.site_id = %s\r\n AND\r\n (ISNULL(candidate.is_admin_hidden) OR (candidate.is_admin_hidden = 0))\r\n AND\r\n (ISNULL(candidate.is_active) OR (candidate.is_active = 1))\r\n %s\r\n", $join, $this->_WHERE, DATA_ITEM_CANDIDATE, DATA_ITEM_BULKRESUME, $this->_siteID, $filter); } else { $sql = sprintf("SELECT\r\n count(*) AS count\r\n FROM\r\n attachment\r\n LEFT JOIN candidate\r\n ON attachment.data_item_id = candidate.candidate_id\r\n AND attachment.site_id = candidate.site_id\r\n LEFT JOIN user AS owner_user\r\n ON candidate.owner = owner_user.user_id\r\n %s\r\n WHERE\r\n resume = 1\r\n AND\r\n %s\r\n AND\r\n (attachment.data_item_type = %s)\r\n AND\r\n attachment.site_id = %s\r\n AND\r\n (ISNULL(candidate.is_admin_hidden) OR (candidate.is_admin_hidden = 0))\r\n AND\r\n (ISNULL(candidate.is_active) OR (candidate.is_active = 1))\r\n %s\r\n", $join, $this->_WHERE, DATA_ITEM_CANDIDATE, $this->_siteID, $filter); } $rs = $this->_db->getAssoc($sql); /* Pass "Search By Resume"-specific parameters to Pager constructor. */ parent::__construct($rs['count'], $rowsPerPage, $currentPage); }