function testMakeBooleanSQLWhere() { $tests = array(array('java', '((field REGEXP \'[[:<:]]java[[:>:]]\'))'), array('java sql', '((field REGEXP \'[[:<:]]java[[:>:]]\') AND (field REGEXP \'[[:<:]]sql[[:>:]]\'))'), array('java | sql', '((field REGEXP \'[[:<:]]java[[:>:]]\') OR (field REGEXP \'[[:<:]]sql[[:>:]]\'))'), array('java,sql', '((field REGEXP \'[[:<:]]java[[:>:]]\') OR (field REGEXP \'[[:<:]]sql[[:>:]]\'))'), array('java, ,,sql', '((field REGEXP \'[[:<:]]java[[:>:]]\') OR (field REGEXP \'[[:<:]]sql[[:>:]]\'))'), array('java -sql', '((field REGEXP \'[[:<:]]java[[:>:]]\') AND NOT (field REGEXP \'[[:<:]]sql[[:>:]]\'))'), array('java !sql', '((field REGEXP \'[[:<:]]java[[:>:]]\') AND NOT (field REGEXP \'[[:<:]]sql[[:>:]]\'))'), array('java*', '((field LIKE \'%java%\'))'), array('java* sql*', '((field LIKE \'%java%\') AND (field LIKE \'%sql%\'))'), array('java (', '0'), array('java) (', '0'), array('java ()', '((field REGEXP \'[[:<:]]java[[:>:]]\'))')); $db = DatabaseConnection::getInstance(); foreach ($tests as $test) { $this->assertIdentical(DatabaseSearch::makeBooleanSQLWhere($test[0], $db, 'field'), $test[1]); } }
/** * Returns all job orders with titles matching $wildCardString. If * activeOnly is true, only Active/OnHold/Full job orders will be shown. * * @param string wildcard match string * @param boolean return active job orders only * @return array job orders data */ public function byTitle($wildCardString, $sortBy, $sortDirection, $activeOnly) { if ($activeOnly) { //FIXME: Remove session dependancy. if ($_SESSION['CATS']->isFree()) { $activeCriterion = "AND joborder.status = 'Active'"; } else { $activeCriterion = "AND (joborder.status IN ('Active', 'OnHold', 'Full'))"; } } else { $activeCriterion = ""; } $WHERE = DatabaseSearch::makeBooleanSQLWhere($wildCardString, $this->_db, 'joborder.title'); $sql = sprintf("SELECT\r\n company.company_id AS companyID,\r\n company.name AS companyName,\r\n joborder.joborder_id AS jobOrderID,\r\n joborder.title AS title,\r\n joborder.type AS type,\r\n joborder.is_hot AS isHot,\r\n joborder.duration AS duration,\r\n joborder.rate_max AS maxRate,\r\n joborder.salary AS salary,\r\n joborder.status AS status,\r\n recruiter_user.first_name AS recruiterFirstName,\r\n recruiter_user.last_name AS recruiterLastName,\r\n owner_user.first_name AS ownerFirstName,\r\n owner_user.last_name AS ownerLastName,\r\n DATE_FORMAT(\r\n joborder.start_date, '%%m-%%d-%%y'\r\n ) AS startDate,\r\n DATE_FORMAT(\r\n joborder.date_created, '%%m-%%d-%%y'\r\n ) AS dateCreated,\r\n DATE_FORMAT(\r\n joborder.date_modified, '%%m-%%d-%%y'\r\n ) AS dateModified\r\n FROM\r\n company\r\n LEFT JOIN joborder\r\n ON company.company_id = joborder.company_id\r\n LEFT JOIN user AS recruiter_user\r\n ON joborder.recruiter = recruiter_user.user_id\r\n LEFT JOIN user AS owner_user\r\n ON joborder.owner = owner_user.user_id\r\n WHERE\r\n %s\r\n %s\r\n AND\r\n joborder.is_admin_hidden = 0\r\n AND\r\n joborder.site_id = %s\r\n ORDER BY\r\n %s %s", $WHERE, $activeCriterion, $this->_siteID, $sortBy, $sortDirection); if (!eval(Hooks::get('JO_SEARCH_SQL'))) { return; } if (!eval(Hooks::get('JO_SEARCH_BY_TITLE'))) { return; } return $this->_db->getAllAssoc($sql); }
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); }
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); }