protected function _get_Captions_with_Counts_Grouped_by_Captions($request_data, array $listingSids = array())
{
$items = array();
if (!empty($request_data[$this->field['field']]['tree'])) {
$this->parent = $items = explode(',', $request_data[$this->field['field']]['tree']);
$treeValues = $this->tree_values;
$this->tree_values = array();
}
if (SJB_Settings::getValue('enableBrowseByCounter')) {
$request_data['access_type'] = array('accessible' => SJB_UserManager::getCurrentUserSID());
$criteria = SJB_SearchFormBuilder::extractCriteriaFromRequestData($request_data);
$sqlTranslator = new SJB_SearchSqlTranslator('listings');
$whereStatement = $sqlTranslator->_getWhereStatement($criteria);
$sql = "`{$this->field['field']}` != ''";
if (count($items) > 1) {
$sql = " AND (";
foreach ($items as $key => $item) {
if ($key == 0) {
$sql .= " FIND_IN_SET('{$item}',`{$this->field['field']}`) ";
} else {
$sql .= " OR FIND_IN_SET('{$item}',`{$this->field['field']}`) ";
}
if (isset($treeValues[$item])) {
$this->tree_values[$item] = $treeValues[$item];
}
}
$sql .= ")";
}
if (!empty($listingSids)) {
$sql .= ' AND `listings`.`sid` IN (' . implode(',', $listingSids) . ')';
}
$sql = "SELECT `?w` AS caption, `sid` AS object_sid FROM `listings` {$whereStatement} AND {$sql}";
$result = SJB_DB::query($sql, $this->field['field']);
$result = self::_getCountsByItems($result);
} else {
if (count($items) > 1) {
foreach ($items as $item) {
$this->tree_values[$item] = $treeValues[$item];
}
}
$result = self::getTreeItems($this->tree_values, SJB_ListingFieldTreeManager::getTreeDepthBySID($this->field['sid']));
}
return $result;
}
protected function _get_Captions_with_Counts_Grouped_by_Captions($request_data, array $listingSids = array())
{
$columns = '?w as caption';
if (SJB_Settings::getValue('enableBrowseByCounter')) {
$columns .= ', count(*) as count';
}
$criteria = SJB_SearchFormBuilder::extractCriteriaFromRequestData($request_data);
$sqlTranslator = new SJB_SearchSqlTranslator('listings');
$whereStatement = $sqlTranslator->_getWhereStatement($criteria);
if (!empty($listingSids)) {
$whereStatement .= 'AND `listings`.`sid` IN (' . implode(',', $listingSids) . ')';
}
$res = SJB_DB::query("select {$columns} from listings {$whereStatement} and ?w != '' group by ?w", $this->field['field'], $this->field['field'], $this->field['field']);
$result = array();
foreach ($res as $value) {
if (SJB_Settings::getValue('enableBrowseByCounter')) {
$result[$value['caption']] = $value['count'];
} else {
$result[$value['caption']] = '';
}
}
return $result;
}
/**
* @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;
}