<?php
/**
 * CATS
 * Search Library
 *
 * Copyright (C) 2005 - 2007 Cognizo Technologies, Inc.
 *
 *
 * The contents of this file are subject to the CATS Public License
 * Version 1.1a (the "License"); you may not use this file except in
 * compliance with the License. You may obtain a copy of the License at
 * http://www.catsone.com/.
 *
 * Software distributed under the License is distributed on an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific language governing rights and limitations
 * under the License.
 *
 * The Original Code is "CATS Standard Edition".
 *
 * The Initial Developer of the Original Code is Cognizo Technologies, Inc.
 * Portions created by the Initial Developer are Copyright (C) 2005 - 2007
 * (or from the year in which this file was created to the year 2007) by
 * Cognizo Technologies, Inc. All Rights Reserved.
 *
 *
 * @package    CATS
 * @subpackage Library
 * @copyright Copyright (C) 2005 - 2007 Cognizo Technologies, Inc.
 * @version    $Id: Search.php 3587 2007-11-13 03:55:57Z will $
 */

include_once('./lib/Pager.php');
include_once('./lib/DatabaseSearch.php');

if (ENABLE_SPHINX)
{
    include_once(SPHINX_API);
}

/**
 *	Search Utility Library
 *	@package    CATS
 *	@subpackage Library
 */
class SearchUtility
{
    /**
     * Returns an excerpt of text based on incidence of keys.
     *
     * @param keys string wildcard terms
     * @param text string result text
     * @return string excerpt
     */
    public static function searchExcerpt($keywords, $text)
    {
        /* CATS fulltext encode the search string. */
        $keywords = DatabaseSearch::fulltextEncode($keywords);

        /* Create an array of keywords to highlight. */
        $keywords = self::makeKeywordsArray($keywords);

        /* Make a copy of the keywords array for manupulating below. */
        $workingKeys = $keywords;

        /* Extract a fragment per keyword, for at most 4 keywords.
         * First we collect ranges of text around each keyword, starting/ending
         * at spaces. If the sum of all fragments is too short, we look for
         * second occurrences.
         */
        $ranges = array();
        $included = array();
        $length = 0;
        while ($length < SEARCH_EXCERPT_LENGTH && count($workingKeys))
        {
            foreach ($workingKeys as $keyOffset => $key)
            {
                if ($length >= SEARCH_EXCERPT_LENGTH)
                {
                    break;
                }

                /* Escape the key for use with preg_*(). */
                $key = preg_quote($key, '/');

                /* Remember occurrence of key so we can skip over it if more occurrnces
                 * are desired.
                 */
                if (!isset($included[$key]))
                {
                    $included[$key] = 0;
                }

                $regExPass = false;

                /* Check for wildcards */
                if (strpos($key, '*') !== false)
                {
                    $newKey = str_replace('\*', '', $key);
                    $regExPass = preg_match(
                        '/' . $newKey . '/i', $text, $matches,
                        PREG_OFFSET_CAPTURE, $included[$key]
                    );
                }
                else
                {
                    $regExPass = preg_match(
                        '/\b' . $key . '\b/i', $text, $matches,
                        PREG_OFFSET_CAPTURE, $included[$key]
                    );
                }

                if ($regExPass)
                {
                    $firstMatchOffset = $matches[0][1];

                    $firstSpaceInRange = strpos($text, ' ', max(0, $firstMatchOffset - 60));
                    if ($firstSpaceInRange !== false)
                    {
                        $end = substr($text, $firstMatchOffset, 80);
                        $lastSpaceInRange = strrpos($end, ' ');

                        if ($lastSpaceInRange !== false)
                        {
                            $ranges[$firstSpaceInRange] = $firstMatchOffset + $lastSpaceInRange;
                            $length += $firstMatchOffset + $lastSpaceInRange - $firstSpaceInRange;
                            $included[$key] = $firstMatchOffset + 1;
                        }
                        else
                        {
                            unset($workingKeys[$keyOffset]);
                        }
                    }
                    else
                    {
                        unset($workingKeys[$keyOffset]);
                    }
                }
                else
                {
                    unset($workingKeys[$keyOffset]);
                }
            }
        }

        /* If we didn't find anything, return the beginning of the text up to
         * SEARCH_EXCERPT_LENGTH.
         */
        if (sizeof($ranges) == 0)
        {

            $text = DatabaseSearch::fulltextDecode($text);
            return substr($text, 0, SEARCH_EXCERPT_LENGTH);
        }

        /* Sort the text ranges by starting position. */
        ksort($ranges);

        /* For each range, in the $ranges array, compare to every other range
         * and test for overlapping ranges. Merge overlapping ranges togeather.
         * The ksort()ing makes this O(n).
         */
        $newRanges = array();
        foreach ($ranges as $rangeFrom => $rangeTo)
        {
            /* On the first loop, set the 'base range' to the first range's
             * limits and continue on to the next loop.
             */
            if (!isset($baseRangeFrom))
            {
                $baseRangeFrom = $rangeFrom;
                $baseRangeTo = $rangeTo;

                continue;
            }

            /* If the start of the current range is before the end of the
             * previous range, make the 'base range' include the new range as
             * well. Otherwise, start the 'base range' over at the limits for
             * the current range.
             */
            if ($rangeFrom <= $baseRangeTo)
            {
                $baseRangeTo = max($baseRangeTo, $rangeTo);
            }
            else
            {
                /* Every time we start the 'base range' over, store the
                 * previous combined range that we just calculated in the
                 * 'new ranges' array.
                 */
                $newRanges[$baseRangeFrom] = $baseRangeTo;

                $baseRangeFrom = $rangeFrom;
                $baseRangeTo = $rangeTo;
            }
        }

        /* Store the last combined range that we just calculated in the 'new
         * ranges' array.
         */
        $newRanges[$baseRangeFrom] = $baseRangeTo;

        /* Fetch text. */
        $out = array();
        foreach ($newRanges as $from => $to)
        {
            $out[] = substr($text, $from, $to - $from);
        }

        $text = implode(' ... ', $out);

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

        if (isset($newRanges[0]))
        {
            $text = $text . ' ...';
        }
        else
        {
            $text = '... ' . $text . ' ...';
        }


        /* Remove AntiWord 'table bars' */
        $text = str_replace('|', '', $text);

        return DatabaseSearch::fulltextDecode($text);
    }

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

    // FIXME: Document me.
    private static function makeKeywordsArray($string)
    {
        /* Mark up quoted strings with filler characters (no white space). */
        $string = DatabaseSearch::markUpQuotes($string);

        /* Split keywords into an array by "words" and fix quotes. */
        $keywords = explode(' ', $string);
        $keywords = array_map(
            array('DatabaseSearch', 'unMarkUpQuotes'), $keywords
        );

        /* Escape special regex characters in keys, and filter out boolean words. */
        foreach ($keywords as $index => $keyword)
        {
            $keywords[$index] = str_replace(
                array('(', ')'), '', $keywords[$index]
            );

            if (strtoupper($keyword) == 'AND' ||
                strtoupper($keyword) == 'OR' ||
                strtoupper($keyword) == 'NOT')
            {
                unset($keywords[$index]);
                continue;
            }
        }

        return array_merge($keywords);
    }
}


/**
 *	Candidates Search Library
 *	@package    CATS
 *	@subpackage Library
 */
class SearchCandidates
{
    private $_db;
    private $_siteID;
    protected $_userID = -1;


    public function __construct($siteID)
    {
        $this->_siteID = $siteID;
        $this->_db = DatabaseConnection::getInstance();
        //FIXME: Library code Session dependencies suck.
        $this->_userID = $_SESSION['CATS']->getUserID();
    }
    
	public function get_attachment_ids_str($keywordstr)
    {
        $this->_db = DatabaseConnection::getInstance();
        $this->_siteID = $siteID;

		$result = "0";
		$bypass = false;
		
        if (ENABLE_SPHINX && strlen($keywordstr)>0)
        {
            /* Sphinx API likes to throw PHP errors *AND* use it's own error
             * handling.
             */
			//die("<p>SPHINX-".$keywordstr."-</p>");
            
			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_PHRASE);
            $sphinx->SetLimits(0, 30000);
			$sphinx->SetArrayResult(false);
            $sphinx->SetSortMode(SPH_SORT_TIME_SEGMENTS, 'date_added');

            //$sphinx->SetFilter('site_id', array($this->_siteID));

			$wildCardString = '"'.DatabaseSearch::humanToSphinxBoolean($keywordstr).'"';
            
			
            $tries = 0;
            do
            {
                /* Wait for one second if this isn't out first attempt. */
                if (++$tries > 1)
                {
                    sleep(1);
                }
                //$wildCardString = '"php"';
                $results = $sphinx->Query($wildCardString, SPHINX_INDEX);
				//print_r($results);
				//die("-");
				
                $errorMessage = $sphinx->GetLastError();
            }
            while (
                $results === false &&
                strpos($errorMessage, 'server maxed out, retry') !== false &&
                $tries <= 5
            );
			//echo("<p>".SPHINX_HOST.", ".SPHINX_PORT."</p>");
			//die($errorMessage);
			//die("1) --->[".($results===false)."]");
			
            /* 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);
			
			//$wildCardString = $wildCardString_orig;
            if (empty($results['matches']))
            {
				//empty, do nothing
				//echo "<p>(no results)".print_r($results, true)."</p>";
            }
            else
            {
				//die(print_r($results['matches']));
                $attachmentIDs = implode(',', array_keys($results['matches']));
                $result = 'attachment.attachment_id IN(' . $attachmentIDs . ')';				
            }
			
        }
        return $result;
    }
	
	public function byState($wildCardString, $sortBy, $sortDirection)
    {
        $wildCardString = strtolower('%' . str_replace('*', '%', $wildCardString) . '%');
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                candidate.candidate_id AS candidateID,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.city AS city,
                candidate.state AS state,
                candidate.phone_home AS phoneHome,
                candidate.phone_cell AS phoneCell,
                candidate.key_skills AS keySkills,
                candidate.email1 AS email1,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                candidate
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
            (
                LOWER(candidate.state) LIKE %s                
            )
            AND
                candidate.is_admin_hidden = 0
            AND
                candidate.site_id = %s
            ORDER BY
                %s %s",            
            $wildCardString,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );
        return $this->_db->getAllAssoc($sql);
    }
	
	public function get_inclusive_zipcodes($distance, $distance_unit, $zip_code) {		 
		 $sql = sprintf(
            "SELECT
                latitude, longitude
            FROM
                geoip_details            
            WHERE
                LOWER(geoip_details.postalcode) = '%s'",                        
            strtolower($zip_code)
         );
		 

		 $rs = $this->_db->getAllAssoc($sql);
		 
		 if($rs) {
			foreach ($rs as $rowIndex => $row) {
				$latitude = $row["latitude"];
				$longitude = $row["longitude"];
			    switch (strtolower($distance_unit)) {		
					case 'miles': /*** miles ***/
						$unit = 3963;
						break;		
					case 'nauticalmiles': /*** nautical miles ***/
						$unit = 3444;
						break;
					case 'kilometers':
					default: /*** kilometers ***/			
						$unit = 6371;
				}				
				$sql_zip = "SELECT postalcode, 
		                           (".$unit." * ACOS( COS( RADIANS(".$latitude.") ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS(".$longitude.") ) + SIN( RADIANS(".$latitude.") ) * SIN( RADIANS( latitude ) ) ) ) AS distance 
		                    FROM geoip_details 
				            HAVING distance < ".$distance."";	
				$rs_zips = $this->_db->getAllAssoc($sql_zip);
				return $rs_zips;
				break;	
			}
		 }
		return false;
	}
	
	function get_countries() {
		/*
		$sql = sprintf(
            "SELECT
                abbrev as value, 
				country as text 
            FROM
                geoip_countries            
            ORDER BY
                country");		
        return $this->_db->getAllAssoc($sql);
		*/
		$result = array(
					"1"	=> array("value"	=> "US", "text"	=> "United States"),
					"0"	=> array("value"	=> "CA", "text"	=> "Canada")										
				  );
		return $result;              
	}
	
	function get_zip_codes($country, $state, $city) {
		$sql = sprintf(
            "SELECT DISTINCT
                postalcode as value, 
				UPPER(postalcode) as text 
            FROM
                geoip_details       
			WHERE
				country = '%s' 
				AND
				region = '%s'
				AND
				city = '%s'  
            ORDER BY
                postalcode",
				$country,
				$state,
				$city);
        return $this->_db->getAllAssoc($sql);
	}
	
	function get_cities($country, $state) {
		$sql = sprintf(
            "SELECT DISTINCT 
                city as value, 
				city as text 
            FROM
                geoip_details       
			WHERE
				country = '%s' 
				AND
				region = '%s'				
            ORDER BY
                city",
				$country,
				$state);
        return $this->_db->getAllAssoc($sql);		
	}
	
	function get_states($country) {
		$sql = sprintf(
            "SELECT DISTINCT
                abbrev as value, 
				state as text 
             FROM
                geoip_states       
			 WHERE
				country = '%s' 							
             ORDER BY state",
				$country);
		return $this->_db->getAllAssoc($sql);		
	}
	
	public function byMultiple($wildCardString, $sortBy, $sortDirection, $return_sql = false)
    {
        //$wildCardString = strtolower('%' . str_replace('*', '%', $wildCardString) . '%');
        //$wildCardString = $this->_db->makeQueryString($wildCardString);
		$wildCardString = str_replace('%', '%%', $wildCardString);
        $sql = sprintf(
            "SELECT
                candidate.candidate_id AS candidateID,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.city AS city,
                candidate.state AS state,
                candidate.phone_home AS phoneHome,
                candidate.phone_cell AS phoneCell,
                candidate.key_skills AS keySkills,
                candidate.email1 AS email1,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                candidate
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
            ( ".$wildCardString." )
            AND
                candidate.is_admin_hidden = 0
            AND
                candidate.site_id = %s
            ORDER BY
                %s %s
			LIMIT 1000",            
            $this->_siteID,
            $sortBy,
            $sortDirection
        );
		//die($sql);
		if($return_sql == true)
			return $sql;
		else
        	return $this->_db->getAllAssoc($sql);
    }
	
	public function byCity($wildCardString, $sortBy, $sortDirection)
    {
        $wildCardString = strtolower('%' . str_replace('*', '%', $wildCardString) . '%');
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                candidate.candidate_id AS candidateID,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.city AS city,
                candidate.state AS state,
                candidate.phone_home AS phoneHome,
                candidate.phone_cell AS phoneCell,
                candidate.key_skills AS keySkills,
                candidate.email1 AS email1,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                candidate
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
            (
                LOWER(candidate.city) LIKE %s                
            )
            AND
                candidate.is_admin_hidden = 0
            AND
                candidate.site_id = %s
            ORDER BY
                %s %s",            
            $wildCardString,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );
        return $this->_db->getAllAssoc($sql);
    }
    
    /**
     * Returns all candidates with full names matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array candidates data
     */
    public function byFullName($wildCardString, $sortBy, $sortDirection)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                candidate.candidate_id AS candidateID,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.city AS city,
                candidate.state AS state,
                candidate.phone_home AS phoneHome,
                candidate.phone_cell AS phoneCell,
                candidate.key_skills AS keySkills,
                candidate.email1 AS email1,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                candidate
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
            (
                CONCAT(candidate.first_name, ' ', candidate.last_name) LIKE %s
                OR CONCAT(candidate.last_name, ' ', candidate.first_name) LIKE %s
                OR CONCAT(candidate.last_name, ', ', candidate.first_name) LIKE %s
            )
            AND
                candidate.is_admin_hidden = 0
            AND
                candidate.site_id = %s
            ORDER BY
                %s %s",
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );

        return $this->_db->getAllAssoc($sql);
    }

    /**
     * Returns all candidates with key skills matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array candidates data
     */
    public function byKeySkills($wildCardString, $sortBy, $sortDirection)
    {
        $WHERE = DatabaseSearch::makeBooleanSQLWhere(
            $wildCardString, $this->_db, 'candidate.key_skills'
        );

        $sql = sprintf(
            "SELECT
                candidate.candidate_id AS candidateID,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.city AS city,
                candidate.state AS state,
                candidate.phone_home AS phoneHome,
                candidate.phone_cell AS phoneCell,
                candidate.key_skills AS keySkills,
                candidate.email1 AS email1,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                candidate
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
                %s
            AND
                candidate.is_admin_hidden = 0
            AND
                candidate.site_id = %s
            AND
                candidate.is_active = 1
            ORDER BY
                %s %s",
            $WHERE,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );

        return $this->_db->getAllAssoc($sql);
    }

    /**
     * Returns all candidates with E-Mail addresses matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array candidates data
     */
    public function byEmail($wildCardString, $sortBy = 'firstName', $sortDirection = 'ASC')
    {
        $wildCardString = str_replace('*', '%', $wildCardString);
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                candidate.candidate_id AS candidateID,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.city AS city,
                candidate.state AS state,
                candidate.phone_home AS phoneHome,
                candidate.phone_cell AS phoneCell,
                candidate.key_skills AS keySkills,
                candidate.email1 AS email1,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                candidate
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
                candidate.email1 LIKE %s
            AND
                candidate.is_admin_hidden = 0
            AND
                candidate.site_id = %s
            ORDER BY
                %s %s",
            $wildCardString,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );

        return $this->_db->getAllAssoc($sql);
    }

    /**
     * Returns all candidates with phone numbers matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array candidates data
     */
    public function byPhone($wildCardString, $sortBy, $sortDirection)
    {
        $wildCardString = str_replace(
            array('.', '-', '(', ')'),
            '',
            $wildCardString
        );
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                candidate.candidate_id AS candidateID,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.city AS city,
                candidate.state AS state,
                candidate.phone_home AS phoneHome,
                candidate.phone_cell AS phoneCell,
                candidate.key_skills AS keySkills,
                candidate.email1 AS email1,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                candidate
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
            (
                REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(candidate.phone_home, '-', ''),
                        '.', ''),
                    ')', ''),
                '(', '') LIKE %s
                OR REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(candidate.phone_cell, '-', ''),
                        '.', ''),
                    ')', ''),
                '(', '') LIKE %s
            )
            AND
                candidate.is_admin_hidden = 0
            AND
                candidate.site_id = %s
            ORDER BY
                %s %s",
            $wildCardString,
            $wildCardString,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );

        return $this->_db->getAllAssoc($sql);
    }
}


/**
 *	Companies Search Library
 *	@package    CATS
 *	@subpackage Library
 */
class SearchCompanies
{
    private $_db;
    private $_siteID;
    protected $_userID = -1;


    public function __construct($siteID)
    {
        $this->_siteID = $siteID;
        $this->_db = DatabaseConnection::getInstance();
        //FIXME: Library code Session dependencies suck.
        $this->_userID = $_SESSION['CATS']->getUserID();
    }
    
    public function get_inclusive_zipcodes($distance, $distance_unit, $zip_code) {		 
		 $sql = sprintf(
            "SELECT
                latitude, longitude
            FROM
                geoip_details            
            WHERE
                LOWER(geoip_details.postalcode) = '%s'",                        
            strtolower($zip_code)
         );
		 

		 $rs = $this->_db->getAllAssoc($sql);
		 
		 if($rs) {
			foreach ($rs as $rowIndex => $row) {
				$latitude = $row["latitude"];
				$longitude = $row["longitude"];
			    switch (strtolower($distance_unit)) {		
					case 'miles': /*** miles ***/
						$unit = 3963;
						break;		
					case 'nauticalmiles': /*** nautical miles ***/
						$unit = 3444;
						break;
					case 'kilometers':
					default: /*** kilometers ***/			
						$unit = 6371;
				}				
				$sql_zip = "SELECT postalcode, 
		                           (".$unit." * ACOS( COS( RADIANS(".$latitude.") ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS(".$longitude.") ) + SIN( RADIANS(".$latitude.") ) * SIN( RADIANS( latitude ) ) ) ) AS distance 
		                    FROM geoip_details 
				            HAVING distance < ".$distance."";	
				$rs_zips = $this->_db->getAllAssoc($sql_zip);
				return $rs_zips;
				break;	
			}
		 }
		return false;
	}
	
    /**
     * Returns all companies with names matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array companies data
     */
	public function byMultiple($wildCardString, $sortBy, $sortDirection)
    {
        //$wildCardString = str_replace('*', '%', $wildCardString) . '%';
        //$wildCardString = $this->_db->makeQueryString($wildCardString);
		$wildCardString = str_replace('%', '%%', $wildCardString);
		
        $sql = sprintf(
            "SELECT
                company.company_id AS companyID,
                company.name AS name,
                company.city AS city,
                company.state AS state,
                company.phone1 AS phone1,
                company.url AS url,
                company.key_technologies AS keyTechnologies,
                company.is_hot AS isHot,
                DATE_FORMAT(
                    company.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    company.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName
            FROM
                company
            LEFT JOIN user AS owner_user
                ON company.owner = owner_user.user_id
            WHERE
                (".$wildCardString.")
            AND
                company.site_id = %s
            ORDER BY
                %s %s
			LIMIT 3000",
            $this->_siteID,
            $sortBy,
            $sortDirection
        );
		//die($sql);
		
        return $this->_db->getAllAssoc($sql);
    }
	
    public function byName($wildCardString, $sortBy, $sortDirection)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                company.company_id AS companyID,
                company.name AS name,
                company.city AS city,
                company.state AS state,
                company.phone1 AS phone1,
                company.url AS url,
                company.key_technologies AS keyTechnologies,
                company.is_hot AS isHot,
                DATE_FORMAT(
                    company.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    company.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName
            FROM
                company
            LEFT JOIN user AS owner_user
                ON company.owner = owner_user.user_id
            WHERE
                company.name LIKE %s
            AND
                company.site_id = %s
            ORDER BY
                %s %s",
            $wildCardString,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );
		
        return $this->_db->getAllAssoc($sql);
    }

    /**
     * Returns all companies with key technologies matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array candidates data
     */
    public function byKeyTechnologies($wildCardString)
    {
        $WHERE = DatabaseSearch::makeBooleanSQLWhere(
            $wildCardString, $this->_db, 'company.key_technologies'
        );

        $sql = sprintf(
            "SELECT
                company.company_id AS companyID,
                company.name AS name,
                company.city AS city,
                company.state AS state,
                company.phone1 AS phone1,
                company.url AS url,
                company.key_technologies AS keyTechnologies,
                company.is_hot AS isHot,
                DATE_FORMAT(
                    company.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    company.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName
            FROM
                company
            LEFT JOIN user AS owner_user
                ON company.owner = owner_user.user_id
            WHERE
                %s
            AND
                company.site_id = %s
            ORDER BY
                company.name ASC",
            $WHERE,
            $this->_siteID
        );

        return $this->_db->getAllAssoc($sql);
    }
}

/**
 *	Job Orders Search Library
 *	@package    CATS
 *	@subpackage Library
 */
class SearchJobOrders
{
    private $_db;
    private $_siteID;
    protected $_userID = -1;


    public function __construct($siteID)
    {
        $this->_siteID = $siteID;
        $this->_db = DatabaseConnection::getInstance();
        //FIXME: Library code Session dependencies suck.
        $this->_userID = $_SESSION['CATS']->getUserID();
    }
    
    
    /**
     * 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
                company.company_id AS companyID,
                company.name AS companyName,
                joborder.joborder_id AS jobOrderID,
                joborder.title AS title,
                joborder.type AS type,
                joborder.is_hot AS isHot,
                joborder.duration AS duration,
                joborder.rate_max AS maxRate,
                joborder.salary AS salary,
                joborder.status AS status,
                recruiter_user.first_name AS recruiterFirstName,
                recruiter_user.last_name AS recruiterLastName,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    joborder.start_date, '%%m-%%d-%%y'
                ) AS startDate,
                DATE_FORMAT(
                    joborder.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    joborder.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                company
            LEFT JOIN joborder
                ON company.company_id = joborder.company_id
            LEFT JOIN user AS recruiter_user
                ON joborder.recruiter = recruiter_user.user_id
            LEFT JOIN user AS owner_user
                ON joborder.owner = owner_user.user_id
            WHERE
                %s
            %s
            AND
                joborder.is_admin_hidden = 0
            AND
                joborder.site_id = %s
            ORDER BY
                %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);
    }

    /**
     * Returns all job orders with company names 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 byCompanyName($wildCardString, $sortBy, $sortDirection, $activeOnly)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        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 = "";
        }

        $sql = sprintf(
            "SELECT
                company.company_id AS companyID,
                company.name AS companyName,
                joborder.joborder_id AS jobOrderID,
                joborder.title AS title,
                joborder.type AS type,
                joborder.is_hot AS isHot,
                joborder.duration AS duration,
                joborder.rate_max AS maxRate,
                joborder.salary AS salary,
                joborder.status AS status,
                recruiter_user.first_name AS recruiterFirstName,
                recruiter_user.last_name AS recruiterLastName,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    joborder.start_date, '%%m-%%d-%%y'
                ) AS startDate,
                DATE_FORMAT(
                    joborder.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    joborder.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                company
            LEFT JOIN joborder
                ON company.company_id = joborder.company_id
            LEFT JOIN user AS recruiter_user
                ON joborder.recruiter = recruiter_user.user_id
            LEFT JOIN user AS owner_user
                ON joborder.owner = owner_user.user_id
            WHERE
                company.name LIKE %s
            %s
            AND
                joborder.is_admin_hidden = 0
            AND
                joborder.site_id = %s
            AND
                company.site_id = %s
            ORDER BY
                %s %s",
            $wildCardString,
            $activeCriterion,
            $this->_siteID,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );

        if (!eval(Hooks::get('JO_SEARCH_SQL'))) return;
        if (!eval(Hooks::get('JO_SEARCH_BY_CLIENT_NAME'))) return;

        return $this->_db->getAllAssoc($sql);
    }
    
    /**
     * Returns all recently modified job orders. If activeOnly is true, 
     * only Active/OnHold/Full job orders will be shown.
     *
     * @param boolean return active job orders only
     * @return array job orders data
     */
    public function recentlyModified($sortDirection, $activeOnly, $limit)
    {
        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 = "";
        }

        $sql = sprintf(
            "SELECT
                company.company_id AS companyID,
                company.name AS companyName,
                joborder.joborder_id AS jobOrderID,
                joborder.title AS title,
                joborder.type AS type,
                joborder.is_hot AS isHot,
                joborder.duration AS duration,
                joborder.rate_max AS maxRate,
                joborder.salary AS salary,
                joborder.status AS status,
                recruiter_user.first_name AS recruiterFirstName,
                recruiter_user.last_name AS recruiterLastName,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    joborder.start_date, '%%m-%%d-%%y'
                ) AS startDate,
                DATE_FORMAT(
                    joborder.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    joborder.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                joborder.date_modified AS dateModifiedSort
            FROM
                company
            LEFT JOIN joborder
                ON company.company_id = joborder.company_id
            LEFT JOIN user AS recruiter_user
                ON joborder.recruiter = recruiter_user.user_id
            LEFT JOIN user AS owner_user
                ON joborder.owner = owner_user.user_id
            WHERE
                joborder.site_id = %s
                %s
            AND
                company.site_id = %s
            AND
                joborder.is_admin_hidden = 0
            ORDER BY
                dateModifiedSort %s
            LIMIT 0, %s",
            $this->_siteID,
            $activeCriterion,
            $this->_siteID,
            $sortDirection,
            $limit
        );

        if (!eval(Hooks::get('JO_SEARCH_SQL'))) return;

        return $this->_db->getAllAssoc($sql);
    }
}


/**
 *	Contacts Search Library
 *	@package    CATS
 *	@subpackage Library
 */
class ContactsSearch
{
    private $_db;
    private $_siteID;
    protected $_userID = -1;


    public function __construct($siteID)
    {
        $this->_siteID = $siteID;
        $this->_db = DatabaseConnection::getInstance();
        //FIXME: Library code Session dependencies suck.
        $this->_userID = $_SESSION['CATS']->getUserID();
    }
    
	
    /**
     * Returns all contacts with full names matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array contacts data
     */
    public function byFullName($wildCardString, $sortBy, $sortDirection)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                contact.contact_id AS contactID,
                contact.company_id AS companyID,
                contact.last_name AS lastName,
                contact.first_name AS firstName,
                contact.title AS title,
                contact.phone_work AS phoneWork,
                contact.phone_cell AS phoneCell,
                contact.phone_other AS phoneOther,
                contact.email1 AS email1,
                contact.email2 AS email2,
                contact.is_hot AS isHotContact,
                contact.left_company AS leftCompany,
                DATE_FORMAT(
                    contact.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    contact.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                company.name AS companyName,
                company.is_hot AS isHotCompany
            FROM
                contact
            LEFT JOIN company
                ON contact.company_id = company.company_id
            LEFT JOIN user AS owner_user
                ON contact.owner = owner_user.user_id
            WHERE
            (
                CONCAT(contact.first_name, ' ', contact.last_name) LIKE %s
                OR CONCAT(contact.last_name, ' ', contact.first_name) LIKE %s
                OR CONCAT(contact.last_name, ', ', contact.first_name) LIKE %s
            )
            AND
                contact.site_id = %s
            AND
                company.site_id = %s
            ORDER BY
                %s %s",
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $this->_siteID,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );

        return $this->_db->getAllAssoc($sql);
    }

    /**
     * Returns all contacts with company names matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array contacts data
     */
    public function byCompanyName($wildCardString, $sortBy,
        $sortDirection)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                contact.contact_id AS contactID,
                contact.company_id AS companyID,
                contact.last_name AS lastName,
                contact.first_name AS firstName,
                contact.title AS title,
                contact.phone_work AS phoneWork,
                contact.phone_cell AS phoneCell,
                contact.phone_other AS phoneOther,
                contact.email1 AS email1,
                contact.email2 AS email2,
                contact.is_hot AS isHotContact,
                contact.left_company AS leftCompany,
                DATE_FORMAT(
                    contact.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    contact.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                company.name AS companyName,
                company.is_hot AS isHotCompany
            FROM
                contact
            LEFT JOIN company
                ON contact.company_id = company.company_id
            LEFT JOIN user AS owner_user
                ON contact.owner = owner_user.user_id
            WHERE
                company.name LIKE %s
            AND
                contact.site_id = %s
            AND
                company.site_id = %s
            ORDER BY
                %s %s",
            $wildCardString,
            $this->_siteID,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );

        return $this->_db->getAllAssoc($sql);
    }

    /**
     * Returns all contacts with titles matching $wildCardString.
     *
     * @param string wildcard match string
     * @return array contacts data
     */
    public function byTitle($wildCardString, $sortBy, $sortDirection)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                contact.contact_id AS contactID,
                contact.company_id AS companyID,
                contact.last_name AS lastName,
                contact.first_name AS firstName,
                contact.title AS title,
                contact.phone_work AS phoneWork,
                contact.phone_cell AS phoneCell,
                contact.phone_other AS phoneOther,
                contact.email1 AS email1,
                contact.email2 AS email2,
                contact.is_hot AS isHotContact,
                contact.left_company AS leftCompany,
                DATE_FORMAT(
                    contact.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    contact.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                company.name AS companyName,
                contact.is_hot AS isHotCompany
            FROM
                contact
            LEFT JOIN company
                ON contact.company_id = company.company_id
            LEFT JOIN user AS owner_user
                ON contact.owner = owner_user.user_id
            WHERE
                contact.title LIKE %s
            AND
                contact.site_id = %s
            AND
                company.site_id = %s
            ORDER BY
                %s %s",
            $wildCardString,
            $this->_siteID,
            $this->_siteID,
            $sortBy,
            $sortDirection
        );

        return $this->_db->getAllAssoc($sql);
    }
}


/**
 *	Quick Search Library
 *	@package    CATS
 *	@subpackage Library
 */
class QuickSearch
{
    private $_db;
    private $_siteID;
    protected $_userID = -1;


    public function __construct($siteID)
    {
        $this->_siteID = $siteID;
        $this->_db = DatabaseConnection::getInstance();
        //FIXME: Library code Session dependencies suck.
        $this->_userID = $_SESSION['CATS']->getUserID();
    }
    
    
    /**
     * Support function for Quick Search code. Searches all relevant fields for
     * $wildCardString.
     *
     * @param string wildcard match string
     * @return array candidates data
     */
    public function candidates($wildCardString)
    {
		$wildCardStringRaw = $wildCardString;
		
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';		
        $wildCardString = $this->_db->makeQueryString($wildCardString);
				
		$wildCardStringWholeCaseInsensitive = $this->_db->makeQueryString(strtolower('%'.str_replace('*', '%',$wildCardStringRaw).'%'));

        $sql = sprintf(
            "SELECT
                candidate.candidate_id AS candidateID,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.phone_home AS phoneHome,
                candidate.phone_cell AS phoneCell,
                candidate.key_skills AS keySkills,
                candidate.email1 AS email1,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                candidate
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
            (
				LOWER(candidate.city) LIKE %s 
				OR LOWER(candidate.state) LIKE %s 
                OR LOWER(candidate.skill_texts) LIKE %s 
				OR CONCAT(candidate.first_name, ' ', candidate.last_name) LIKE %s
                OR CONCAT(candidate.last_name, ' ', candidate.first_name) LIKE %s
                OR CONCAT(candidate.last_name, ', ', candidate.first_name) LIKE %s
                OR candidate.email1 LIKE %s
                OR REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(candidate.phone_home, '-', ''),
                        '.', ''),
                    ')', ''),
                '(', '') LIKE %s
                OR REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(candidate.phone_cell, '-', ''),
                        '.', ''),
                    ')', ''),
                '(', '') LIKE %s
            )
            AND
                candidate.site_id = %s
            AND
                candidate.is_admin_hidden = 0
            ORDER BY
                candidate.date_modified DESC,
                candidate.first_name ASC,
                candidate.last_name ASC",
            $wildCardStringWholeCaseInsensitive,
			$wildCardStringWholeCaseInsensitive,
			$wildCardStringWholeCaseInsensitive,
			$wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $this->_siteID
        );
		//die($sql);

        return $this->_db->getAllAssoc($sql);
    }
    
    /**
     * Support function for Quick Search code. Searches all relevant fields for
     * $wildCardString.
     *
     * @param string wildcard match string
     * @return array companies data
     */
    public function companies($wildCardString)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                company.company_id AS companyID,
                company.name AS name,
                company.city AS city,
                company.state AS state,
                company.phone1 AS phone1,
                company.url AS url,
                company.key_technologies AS keyTechnologies,
                company.is_hot AS isHot,
                DATE_FORMAT(
                    company.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    company.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName
            FROM
                company
            LEFT JOIN user AS owner_user
                ON company.owner = owner_user.user_id
            WHERE
            (
                company.name LIKE %s
                OR company.phone1 LIKE %s
                OR company.phone2 LIKE %s
                OR company.url LIKE %s
            )
            AND
                company.site_id = %s
            ORDER BY
                company.name ASC",
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $this->_siteID
        );

        return $this->_db->getAllAssoc($sql);
    }
    
    /**
     * Support function for Quick Search code. Searches all relevant fields for
     * $wildCardString.
     *
     * @param string wildcard match string
     * @return array contacts data
     */
    public function contacts($wildCardString)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                contact.contact_id AS contactID,
                contact.company_id AS companyID,
                contact.last_name AS lastName,
                contact.first_name AS firstName,
                contact.title AS title,
                contact.phone_work AS phoneWork,
                contact.phone_cell AS phoneCell,
                contact.phone_other AS phoneOther,
                contact.email1 AS email1,
                contact.email2 AS email2,
                contact.is_hot AS isHotContact,
                contact.left_company AS leftCompany,
                DATE_FORMAT(
                    contact.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    contact.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                company.name AS companyName,
                company.is_hot AS isHotCompany
            FROM
                contact
            LEFT JOIN company
                ON contact.company_id = company.company_id
            LEFT JOIN user AS owner_user
                ON contact.owner = owner_user.user_id
            WHERE
            (
                CONCAT(contact.first_name, ' ', contact.last_name) LIKE %s
                OR CONCAT(contact.last_name, ' ', contact.first_name) LIKE %s
                OR CONCAT(contact.last_name, ', ', contact.first_name) LIKE %s
                OR contact.phone_work LIKE %s
                OR company.name LIKE %s
                OR contact.email1 LIKE %s
                OR contact.email2 LIKE %s
                OR REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(contact.phone_work, '-', ''),
                        '.', ''),
                    ')', ''),
                '(', '') LIKE %s
                OR REPLACE(
                    REPLACE(
                        REPLACE(
                            REPLACE(contact.phone_cell, '-', ''),
                        '.', ''),
                    ')', ''),
                '(', '') LIKE %s
            )
            AND
                contact.site_id = %s
            AND
                company.site_id = %s
            ORDER BY
                name ASC",
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $wildCardString,
            $this->_siteID,
            $this->_siteID
        );

        return $this->_db->getAllAssoc($sql);
    }
    
    /**
     * Support function for Quick Search code. Searches all relevant fields for
     * $wildCardString.
     *
     * @param string wildcard match string
     * @return array job orders data
     */
    public function jobOrders($wildCardString)
    {
        $wildCardString = str_replace('*', '%', $wildCardString) . '%';
        $wildCardString = $this->_db->makeQueryString($wildCardString);

        $sql = sprintf(
            "SELECT
                company.company_id AS companyID,
                company.name AS companyName,
                joborder.joborder_id AS jobOrderID,
                joborder.title AS title,
                joborder.type AS type,
                joborder.is_hot AS isHot,
                joborder.duration AS duration,
                joborder.rate_max AS maxRate,
                joborder.salary AS salary,
                joborder.status AS status,
                joborder.city AS city,
                joborder.state AS state,
                recruiter_user.first_name AS recruiterFirstName,
                recruiter_user.last_name AS recruiterLastName,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                DATE_FORMAT(
                    joborder.start_date, '%%m-%%d-%%y'
                ) AS startDate,
                DATE_FORMAT(
                    joborder.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                DATE_FORMAT(
                    joborder.date_modified, '%%m-%%d-%%y'
                ) AS dateModified
            FROM
                joborder
            LEFT JOIN company
                ON joborder.company_id = company.company_id
            LEFT JOIN user AS recruiter_user
                ON joborder.recruiter = recruiter_user.user_id
            LEFT JOIN user AS owner_user
                ON joborder.owner = owner_user.user_id
            WHERE
            (
                company.name LIKE %s
                OR joborder.title LIKE %s
            )
            AND
                joborder.is_admin_hidden = 0
            AND
                joborder.site_id = %s
            AND
                company.site_id = %s
            ORDER BY
                name ASC",
            $wildCardString,
            $wildCardString,
            $this->_siteID,
            $this->_siteID
        );

        if (!eval(Hooks::get('JO_SEARCH_SQL'))) return;
        if (!eval(Hooks::get('JO_SEARCH_BY_EVERYTHING'))) return;

        return $this->_db->getAllAssoc($sql);
    }
}

/**
 *	Saved Searches Library
 *	@package    CATS
 *	@subpackage Library
 */
class SavedSearches
{
    private $_db;
    private $_siteID;
    protected $_userID = -1;


    public function __construct($siteID)
    {
        $this->_siteID = $siteID;
        $this->_db = DatabaseConnection::getInstance();
        //FIXME: Library code Session dependencies suck.
        $this->_userID = $_SESSION['CATS']->getUserID();
    }
    
    
    /**
     * Removes a saved search entry.
     *
     * @param integer search ID
     * @return void
     */
    public function remove($searchID)
    {
        $sql = sprintf(
            "DELETE FROM
                saved_search
            WHERE
                search_id = %s
            AND
                user_id = %s
            AND
                site_id = %s",
            $this->_db->makeQueryInteger($searchID),
            $this->_userID,
            $this->_siteID
        );
        $this->_db->query($sql);
    }

    /**
     * Promotes a recent search to a saved search.
     *
     * @param integer search ID
     * @return boolean True if successful; false otherwise.
     */
    public function save($searchID)
    {
        $sql = sprintf(
            "UPDATE
                saved_search
            SET
                is_custom = 1
            WHERE
                search_id = %s
            AND
                user_id = %s
            AND
                site_id = %s",
            $this->_db->makeQueryInteger($searchID),
            $this->_userID,
            $this->_siteID
        );

        return (boolean) $this->_db->query($sql);
    }

    //FIXME: Document me.
    public function removeRecent($dataItemType, $text)
    {
        $sql = sprintf(
            "DELETE FROM
                saved_search
            WHERE
                data_item_text = %s
            AND
                data_item_type = %s
            AND
                user_id = %s
            AND
                is_custom = 0
            AND
                site_id = %s",
            $this->_db->makeQueryString($text),
            $this->_db->makeQueryInteger($dataItemType),
            $this->_userID,
            $this->_siteID
        );
        $this->_db->query($sql);
    }

    //FIXME: Document me.
    public function add($dataItemType, $text, $url, $isCustom)
    {
        /* If this item is already in the saved search list, remove it. */
        $this->removeRecent($dataItemType, $text);

        $sql = sprintf(
            "INSERT INTO saved_search (
                site_id,
                user_id,
                data_item_type,
                data_item_text,
                url,
                is_custom,
                date_created
            )
            VALUES (
                %s,
                %s,
                %s,
                %s,
                %s,
                %s,
                NOW()
            )",
            $this->_siteID,
            $this->_userID,
            $this->_db->makeQueryInteger($dataItemType),
            $this->_db->makeQueryString($text),
            $this->_db->makeQueryString($url),
            ($isCustom ? 1 : 0)
        );
        $this->_db->query($sql);

        $this->prune();
    }

    //FIXME: Document me.
    public function get($dataItemType)
    {
        $sql = sprintf(
            "SELECT
                search_id AS searchID,
                data_item_text AS dataItemText,
                url AS URL,
                is_custom AS isCustom
            FROM
                saved_search
            WHERE
                site_id = %s
            AND
                user_id = %s
            AND
                data_item_type = %s
            ORDER BY
                search_id DESC",
            $this->_siteID,
            $this->_userID,
            $this->_db->makeQueryInteger($dataItemType)
        );

        return $this->_db->getAllAssoc($sql);
    }

    /**
     * Removes old saved search entries for a user.
     *
     * @return void
     */
    private function prune()
    {
        $sql = sprintf(
            "SELECT
                COUNT(*) AS count
            FROM
                saved_search
            WHERE
                site_id = %s
            AND
                user_id = %s
            AND
                is_custom = 0",
            $this->_siteID,
            $this->_userID
        );
        $rs = $this->_db->getAssoc($sql);

        $count = $rs['count'];

        // FIXME: Remove multiple entries at onceif we're more than one over?
        while ($count > RECENT_SEARCH_MAX_ITEMS)
        {
            /* Remove the least recent entry. */
            $sql = sprintf(
                "SELECT
                    search_id AS searchID
                FROM
                    saved_search
                WHERE
                    site_id = %s
                AND
                    user_id = %s
                AND
                    is_custom = 0
                ORDER BY
                    search_id
                ASC LIMIT 1",
                $this->_siteID,
                $this->_userID
            );
            $rs = $this->_db->getAssoc($sql);

            $sql = sprintf(
                "DELETE FROM
                    saved_search
                WHERE
                    search_id = %s",
                $rs['searchID']
            );
            $this->_db->query($sql);

            --$count;
        }
    }
}


/**
 *	Search by Resume Pager
 *	@package    CATS
 *	@subpackage Library
 */
class SearchByResumePager extends Pager
{
    private $_siteID;
    private $_db;
    private $_WHERE;


    public function __construct($rowsPerPage, $currentPage, $siteID,
        $wildCardString, $sortBy, $sortDirection, $single_resume_text)
    {
        $this->_db = DatabaseConnection::getInstance();
        $this->_siteID = $siteID;

        $this->_sortByFields = array(
            'firstName',
            'lastName',
            'city',
            'state',
            'dateModifiedSort',
            'dateCreatedSort',
            'ownerSort'
        );

        if (ENABLE_SPHINX && strlen($single_resume_text)>0)
        {
            /* 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_ANY);
            $sphinx->SetLimits(0, 30000);
			$sphinx->SetArrayResult(true);
            $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);
			//die($wildCardString);
			
			$wildCardString_orig = $wildCardString;
			$wildCardString = '"'.DatabaseSearch::humanToSphinxBoolean($single_resume_text).'"';
            
			//die($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);
                }
                
				//$wildCardString = '"php"';
                $results = $sphinx->Query($wildCardString, SPHINX_INDEX);
				//print_r($results);
				//die("-");
				
                $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);
			
			//print_r($results);
			//die("end");
			
			$wildCardString = $wildCardString_orig;
            if (empty($results['matches']))
            {
				
				if(strlen($wildCardString)>0)
					$this->_WHERE = "(".$wildCardString.")";
				else {
                	$this->_WHERE = '0';
					$wildCardString = "0";
				}
				
				//echo("<p>passA - ".$this->_WHERE."</p>");
            }
            else
            {
				
                $attachmentIDs = implode(',', array_keys($results['matches']));
                $this->_WHERE = 'attachment.attachment_id IN(' . $attachmentIDs . ')';
				if(strlen($wildCardString)>0)
					$this->_WHERE .= " AND (".$wildCardString.")";
				else
					$wildCardString = "0";
					
				//echo("<p>passB - ".$this->_WHERE."</p>");
            }
			
        }
        else
        {
			/*
			$wildCardString = str_replace("(", "", $wildCardString);
			$wildCardString = str_replace(")", "", $wildCardString);
			
            $this->_WHERE = DatabaseSearch::makeBooleanSQLWhere(
                DatabaseSearch::fulltextEncode($wildCardString),
                $this->_db,
                'attachment.text'
            );
			*/
			$this->_WHERE = "(".$wildCardString.")";
        }
		
		//die($this->_WHERE);
		
        /* How many companies do we have? */
        $sql = sprintf(
            "SELECT
                COUNT(*) AS count
            FROM
                attachment
            LEFT JOIN candidate
                ON attachment.data_item_id = candidate.candidate_id
                AND attachment.data_item_type = %s
                AND attachment.site_id = candidate.site_id
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
                resume = 1
            AND
                %s
            AND
                (ISNULL(candidate.is_admin_hidden) OR (candidate.is_admin_hidden = 0))
            AND
                (ISNULL(candidate.is_active) OR (candidate.is_active = 1))
            AND
                attachment.site_id = %s",
            DATA_ITEM_CANDIDATE,
            $this->_WHERE,
            $this->_siteID
        );
		
		//die($sql);
        $rs = $this->_db->getAssoc($sql);

        /* Pass "Search By Resume"-specific parameters to Pager constructor. */
        parent::__construct($rs['count'], $rowsPerPage, $currentPage);
    }

	

    //FIXME: Document me.
    public function getPage()
    {
        $sql = sprintf(
            "SELECT
                attachment.attachment_id AS attachmentID,
                attachment.data_item_id AS candidateID,
                attachment.title AS title,
                attachment.text AS text,
                candidate.first_name AS firstName,
                candidate.last_name AS lastName,
                candidate.city AS city,
                candidate.state AS state,
                DATE_FORMAT(
                    candidate.date_created, '%%m-%%d-%%y'
                ) AS dateCreated,
                candidate.date_created AS dateCreatedSort,
                DATE_FORMAT(
                    candidate.date_modified, '%%m-%%d-%%y'
                ) AS dateModified,
                candidate.date_modified AS dateModifiedSort,
                owner_user.first_name AS ownerFirstName,
                owner_user.last_name AS ownerLastName,
                CONCAT(owner_user.last_name, owner_user.first_name) AS ownerSort
            FROM
                attachment
            LEFT JOIN candidate
                ON attachment.data_item_id = candidate.candidate_id
                AND attachment.site_id = candidate.site_id
            LEFT JOIN user AS owner_user
                ON candidate.owner = owner_user.user_id
            WHERE
                resume = 1
            AND
                %s
            AND
                (attachment.data_item_type = %s OR attachment.data_item_type = %s)
            AND
                attachment.site_id = %s
            AND
                (ISNULL(candidate.is_admin_hidden) OR (candidate.is_admin_hidden = 0))
            AND
                (ISNULL(candidate.is_active) OR (candidate.is_active = 1))
            ORDER BY
                %s %s
            LIMIT %s, %s",

            $this->_WHERE,
            DATA_ITEM_CANDIDATE,
            DATA_ITEM_BULKRESUME,
            $this->_siteID,
            $this->_sortBy,
            $this->_sortDirection,
            $this->_thisPageStartRow,
            $this->_rowsPerPage
        );
		//die($sql);
		
        return $this->_db->getAllAssoc($sql);
    }

    /**
     * Print a fatal error and die.
     *
     * @param string error message
     * @return void
     */
    protected function fatal($error)
    {
        $template = new Template();
        $template->assign('errorMessage', $error);
        $template->display('./Error.tpl');
        die();
    }
}


/**
 *	Search Results Pager
 *	@package    CATS
 *	@subpackage Library
 */
class SearchPager extends Pager
{
    private $_siteID;
    private $_db;
    private $_rs;


    public function __construct($rowsPerPage, $currentPage, $siteID)
    {
        $this->_sortByFields = array(
            'firstName',
            'lastName',
            'city',
            'state',
            'dateModified',
            'dateCreated',
            'owner',
            'phone1',
            'companyName',
            'title',
            'owner_user',
            'owner_user.last_name',
            'type',
            'status',
            'startDate',
            'recruiterLastName',
            'dateCreatedSort',
            'dateModifiedSort',
            'ownerSort'
        );

        /* Pass "Search By Resume"-specific parameters to Pager constructor. */
        parent::__construct(count($this->_rs), $rowsPerPage, $currentPage);
    }
}

?>
