function _getListingSidCollectionFromRequest()
{
$listing = new SJB_Listing(array(), $this->listing_type_sid);
$id_alias_info = $listing->addIDProperty();
$listing->addActivationDateProperty();
$listing->addFeaturedProperty();
$listing->addFeaturedLastShowedProperty();
$username_alias_info = $listing->addUsernameProperty();
$listing_type_id_info = $listing->addListingTypeIDProperty();
$listing->addCompanyNameProperty();
// select only accessible listings by user sid
// see SearchCriterion.php, AccessibleCriterion class
if ($this->listing_type_id == 'Resume') {
$this->requested_data['access_type'] = array('accessible' => SJB_UserManager::getCurrentUserSID());
}
$criteria = $this->criteria_saver->getCriteria();
if (isset($this->requested_data['PostedWithin']['multi_like'][0]) || isset($criteria['PostedWithin']['multi_like'][0])) {
$within_period = '';
if (isset($this->requested_data['PostedWithin']['multi_like'][0])) {
$within_period = $this->requested_data['PostedWithin']['multi_like'][0];
unset($this->requested_data['PostedWithin']['multi_like']);
}
if (isset($criteria['PostedWithin']['multi_like'][0])) {
$within_period = $criteria['PostedWithin']['multi_like'][0];
unset($criteria['PostedWithin']);
}
$i18n = SJB_I18N::getInstance();
$this->requested_data['activation_date']['not_less'] = $i18n->getDate(date('Y-m-d', strtotime("- {$within_period} days")));
}
if (isset($this->requested_data['CompanyName']['multi_like_and'][0]) || isset($criteria['CompanyName']['multi_like_and'][0])) {
if (isset($this->requested_data['CompanyName']['multi_like_and'][0])) {
$companyName = $this->requested_data['CompanyName']['multi_like_and'][0];
unset($this->requested_data['CompanyName']);
}
if (isset($criteria['CompanyName']['multi_like_and'][0])) {
$companyName = $criteria['CompanyName']['multi_like_and'][0];
unset($criteria['CompanyName']);
}
$userName = SJB_UserManager::getUserNameByCompanyName($companyName);
if ($userName) {
$this->requested_data['username']['equal'] = $userName;
}
}
$criteria = SJB_SearchFormBuilder::extractCriteriaFromRequestData(array_merge($criteria, $this->requested_data), $listing);
$aliases = new SJB_PropertyAliases();
$aliases->addAlias($id_alias_info);
$aliases->addAlias($username_alias_info);
$aliases->addAlias($listing_type_id_info);
$sortingFields = array();
if ($this->usePriority) {
$sortingFields['priority'] = 'DESC';
}
$innerJoin = array();
$orderInfo = $this->criteria_saver->getOrderInfo();
if (is_array($orderInfo['sorting_field'])) {
$requestedSortingField = array();
foreach ($orderInfo['sorting_field'] as $orderInfoProperty) {
$fieldName = strstr($orderInfoProperty, '_', true);
$id = $this->getLocationProperty($fieldName, $listing, $orderInfoProperty);
if (!empty($id)) {
switch ($orderInfoProperty) {
case 'Location_State':
$innerJoin['states'] = array('stateName' => '`states`.`state_code`', 'join' => 'LEFT JOIN', 'join_field' => 'sid', 'join_field2' => $orderInfoProperty, 'main_table' => 'listings');
break;
case 'Location_Country':
$innerJoin['countries'] = array('countryName' => '`countries`.`country_name`', 'join' => 'LEFT JOIN', 'join_field' => 'sid', 'join_field2' => $orderInfoProperty, 'main_table' => 'listings');
break;
default:
break;
}
$sortingFields[$orderInfoProperty] = $orderInfo['sorting_order'];
$requestedSortingField[] = $orderInfoProperty;
}
$this->requested_data['sorting_field'] = $requestedSortingField;
$this->requested_data['sorting_order'] = $orderInfo['sorting_order'];
}
} else {
$property = $listing->getProperty($orderInfo['sorting_field']);
if (!empty($property) && $property->isSystem()) {
$sortingFields[$orderInfo['sorting_field']] = $orderInfo['sorting_order'];
if ($property->getID() == 'CompanyName') {
$innerJoin['users'] = array('join' => 'INNER JOIN', 'join_field' => 'sid', 'join_field2' => 'user_sid', 'main_table' => 'listings');
}
$this->requested_data['sorting_field'] = $orderInfo['sorting_field'];
$this->requested_data['sorting_order'] = $orderInfo['sorting_order'];
} else {
$sortingFields['activation_date'] = 'DESC';
$this->requested_data['sorting_field'] = 'activation_date';
$this->requested_data['sorting_order'] = 'DESC';
}
}
$searcher = new SJB_ListingSearcher();
if ($this->listingsLimit) {
$searcher->setLimit($this->listingsLimit);
}
$this->listing_search_structure['sorting_field'] = $this->requested_data['sorting_field'];
$this->listing_search_structure['sorting_order'] = $this->requested_data['sorting_order'];
return $searcher->getObjectsSIDsByCriteria($criteria, $aliases, $sortingFields, $innerJoin, $this->relevance);
}
/**
* @param $fieldName
* @param $fieldID
* @param SJB_SearchResultsTP $stp
* @param $userField
* @return mixed
*/
public static function countListingsByFieldName($fieldName, $fieldID, $stp, $userField)
{
$refineSearchLimit = SJB_Settings::getSettingByName('refine_search_items_limit');
$limit = $refineSearchLimit ? ' LIMIT 0, ' . $refineSearchLimit : '';
$listing = new SJB_Listing(array(), $stp->listing_type_sid);
$id_alias_info = $listing->addIDProperty();
$listing->addActivationDateProperty();
$listing->addFeaturedProperty();
$username_alias_info = $listing->addUsernameProperty();
$listing_type_id_info = $listing->addListingTypeIDProperty();
$listing->addCompanyNameProperty();
$requestedCriteria = $stp->criteria_saver->getCriteria();
if (isset($requestedCriteria['PostedWithin']) && $requestedCriteria['PostedWithin']['multi_like'][0] != '') {
$within_period = $requestedCriteria['PostedWithin']['multi_like'][0];
$i18n = SJB_I18N::getInstance();
$requestedCriteria['activation_date']['not_less'] = $i18n->getDate(date('Y-m-d', strtotime("- {$within_period} days")));
unset($requestedCriteria['PostedWithin']);
}
if (isset($requestedCriteria['CompanyName']['multi_like_and'][0])) {
$userName = SJB_UserManager::getUserNameByCompanyName($requestedCriteria['CompanyName']['multi_like_and'][0]);
unset($requestedCriteria['CompanyName']);
if ($userName) {
$requestedCriteria['username']['equal'] = $userName;
}
}
$criteria = SJB_SearchFormBuilder::extractCriteriaFromRequestData($requestedCriteria, $listing);
$aliases = new SJB_PropertyAliases();
$aliases->addAlias($id_alias_info);
$aliases->addAlias($username_alias_info);
$aliases->addAlias($listing_type_id_info);
$aliases->changeAliasValuesInCriteria($criteria);
$sqlTranslator = new SJB_SearchSqlTranslator('listings');
$whereStatement = $sqlTranslator->_getWhereStatement($criteria);
$objectSids = implode(',', $stp->found_listings_sids);
if ($userField == 1) {
$field = SJB_UserProfileFieldManager::getFieldInfoBySID($fieldID);
} else {
$field = SJB_ListingFieldDBManager::getListingFieldInfoBySID($fieldID);
}
$result = array();
$cache = SJB_Cache::getInstance();
if (!empty($field['parent_sid'])) {
$parentInfo = SJB_ListingFieldManager::getFieldInfoBySID($field['parent_sid']);
$fieldName = $parentInfo['id'] . "_" . $fieldName;
$field['id'] = $fieldName;
$field['parentID'] = $parentInfo['id'];
}
switch ($field['type']) {
case 'list':
case 'multilist':
if ($userField == 1) {
$query = "SELECT up.`{$fieldName}` as caption, count(`listings`.`sid`) as count\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t FROM `listings`\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t INNER JOIN `users` `up` ON `listings`.`user_sid` = `up`.`sid`\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t {$whereStatement}\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t AND up.`{$fieldName}` != ''\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t GROUP BY `up`.`{$fieldName}` ORDER BY count DESC";
if (!($result = $cache->load(md5($query)))) {
$result = SJB_DB::query($query);
$cache->save($result, md5($query), array(SJB_Cache::TAG_LISTINGS, SJB_Cache::TAG_USERS));
}
} else {
$query = "SELECT `{$fieldName}` as caption, count(`{$fieldName}`) as count FROM `listings` {$whereStatement} AND `{$fieldName}` != '' GROUP BY `{$fieldName}` ORDER BY count DESC";
if (!($result = $cache->load(md5($query)))) {
$result = SJB_DB::query($query);
$cache->save($result, md5($query), array(SJB_Cache::TAG_LISTINGS));
}
}
self::breakMultiCategory($result);
$newResult = array();
$listItem = new SJB_ListingFieldListItemManager();
foreach ($result as $key => $val) {
if (!empty($field['parent_sid'])) {
$caption = '';
if ($field['id'] == $field['parentID'] . '_State') {
$listValues = SJB_StatesManager::getStatesNamesByCountry(false, true, $field['display_as']);
} else {
$listValues = $field['list_values'];
}
foreach ($listValues as $listValue) {
if ($listValue['id'] == $val['caption']) {
$caption = $listValue['caption'];
break;
}
}
} else {
$itemInfo = $listItem->getListItemBySID($val['caption']);
$caption = $itemInfo ? $itemInfo->getValue() : null;
}
if ($caption != null) {
$newResult[$key]['count'] = $val['count'];
$newResult[$key]['value'] = $caption;
$newResult[$key]['sid'] = $val['caption'];
}
}
arsort($newResult);
$result = $newResult;
if (count($result) > $refineSearchLimit) {
$result = array_slice($result, 0, $refineSearchLimit);
}
break;
case 'tree':
$query = "SELECT `lt`.`sid` as `sid`, `lt`.`caption` as `value`, count(`listings`.`sid`) as `count`\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM `listings`\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tLEFT JOIN `listing_field_tree` `lt` ON `lt`.`field_sid` = {$field['sid']} AND find_in_set(`lt`.`sid`, `listings`.`{$fieldName}`)\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t{$whereStatement} GROUP BY `lt`.`sid` having `lt`.`sid` IS NOT NULL ORDER BY `count` DESC {$limit}";
if (!($propertyValue = $cache->load(md5($query)))) {
$propertyValue = SJB_DB::query($query);
$cache->save($propertyValue, md5($query), array(SJB_Cache::TAG_LISTINGS, SJB_Cache::TAG_FIELDS));
}
foreach ($propertyValue as $value) {
$result[$value['sid']] = $value;
}
break;
default:
if ($userField == 1) {
$companyColumn = "up.`{$fieldName}`";
$query = "SELECT {$companyColumn} as `value`, count(listings.`sid`) as `count`\n\t\t\t\t\t\t\t\t\t\t\t FROM `listings`\n\t\t\t\t\t\t\t\t\t\t\t INNER JOIN `users` `up` ON `listings`.`user_sid` = `up`.`sid`\n\t\t\t\t\t\t\t\t\t\t\t {$whereStatement}\n\t\t\t\t\t\t\t\t\t\t\t GROUP BY {$companyColumn} ORDER BY `count` DESC {$limit}";
if (!($result = $cache->load(md5($query)))) {
$result = SJB_DB::query($query);
$cache->save($result, md5($query), array(SJB_Cache::TAG_LISTINGS, SJB_Cache::TAG_USERS));
}
} else {
if ($field['type'] == 'complex') {
$query = "SELECT `value`, count(`value`) as count FROM `listings_properties` WHERE `id`='{$fieldName}' AND `value` != '' AND `object_sid` in ({$objectSids}) GROUP BY `value` ORDER BY count DESC {$limit}";
} else {
$query = "SELECT `{$fieldName}` as value, count(`{$fieldName}`) as count FROM `listings` {$whereStatement} AND `{$fieldName}` != '' GROUP BY `{$fieldName}` ORDER BY count DESC {$limit}";
}
if (!($result = $cache->load(md5($query)))) {
$result = SJB_DB::query($query);
$cache->save($result, md5($query), array(SJB_Cache::TAG_LISTINGS));
}
}
break;
}
$returnArr['caption'] = $field['caption'];
$returnArr['values'] = $result;
return $returnArr;
}
