Exemplo n.º 1
0
 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;
 }
Exemplo n.º 2
0
 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;
 }
Exemplo n.º 3
0
 /**
  * @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;
 }