protected function _prepareTable(&$table)
{
jimport('joomla.filter.output');
$date = JFactory::getDate();
$user = JFactory::getUser();
$params = JComponentHelper::getParams('com_djcatalog2');
$db = JFactory::getDbo();
if ($params->get('fed_intro_description_editor', null) == 'none' && !empty($table->intro_desc)) {
$table->intro_desc = nl2br($table->intro_desc, true);
}
if ($params->get('fed_description_editor', null) == 'none' && !empty($table->description)) {
$table->description = nl2br($table->description, true);
}
$table->name = htmlspecialchars_decode($table->name, ENT_QUOTES);
$table->alias = JApplication::stringURLSafe($table->alias);
// TODO - just temporary
$table->group_id = 0;
if (empty($table->alias)) {
$table->alias = JApplication::stringURLSafe($table->name);
}
if (empty($table->id)) {
if (empty($table->ordering)) {
$db = JFactory::getDbo();
$db->setQuery('SELECT MAX(ordering) FROM #__djc2_items WHERE cat_id = ' . $table->cat_id);
$max = $db->loadResult();
$table->ordering = $max + 1;
}
}
if (!isset($table->group_id)) {
$table->group_id = 0;
}
if (!isset($table->latitude) || !isset($table->longtitude) || $table->latitude == 0 || $table->longtitude == 0) {
require_once JPath::clean(JPATH_ROOT . '/administrator/components/com_djcatalog2/lib/geocode.php');
$address = array();
if (!empty($table->address)) {
$address[] = $table->address;
}
if (!empty($table->city)) {
$address[] = $table->city;
}
if (!empty($table->postcode)) {
$address[] = $table->postcode;
}
if (!empty($table->country)) {
$db->setQuery('select country_name from #__djc2_countries where id=' . (int) $table->country);
$country = $db->loadResult();
if ($country) {
$address[] = $country;
}
}
$address_str = implode(',', $address);
if ($address_str) {
if ($coords = DJCatalog2Geocode::getLocation($address_str)) {
$table->latitude = !empty($coords['lat']) ? $coords['lat'] : null;
$table->longitude = !empty($coords['lng']) ? $coords['lng'] : null;
}
}
}
}
public function geocode($pks)
{
if (empty($pks)) {
return false;
}
$ids = implode(',', $pks);
$db = JFactory::getDbo();
$app = JFactory::getApplication();
require_once JPath::clean(JPATH_ROOT . '/administrator/components/com_djcatalog2/lib/geocode.php');
$db->setQuery('select * from #__djc2_items where id IN (' . $ids . ')');
$items = $db->loadObjectList();
foreach ($items as $item) {
$address = array();
if (!empty($item->address)) {
$address[] = $item->address;
}
if (!empty($item->city)) {
$address[] = $item->city;
}
if (!empty($item->postcode)) {
$address[] = $item->postcode;
}
if (!empty($item->country)) {
$db->setQuery('select country_name from #__djc2_countries where id=' . (int) $item->country);
$country = $db->loadResult();
if ($country) {
$address[] = $country;
}
}
$address_str = implode(',', $address);
if ($address_str) {
if ($coords = DJCatalog2Geocode::getLocation($address_str)) {
// bypassing Google Maps limits
usleep(150000);
$latitude = !empty($coords['lat']) ? $coords['lat'] : null;
$longitude = !empty($coords['lng']) ? $coords['lng'] : null;
$db->setQuery('UPDATE #__djc2_items SET latitude = ' . $latitude . ', longitude = ' . $longitude . ' WHERE id = ' . (int) $item->id);
if (!$db->query()) {
$this->setError($db->getErrorMsg());
return false;
}
$app->enqueueMessage(JText::sprintf('COM_DJCATALOG2_GEOLOCATION_OK', $item->id), 'message');
} else {
$app->enqueueMessage(JText::sprintf('COM_DJCATALOG2_ERROR_GEOLOCATION_NOT_FOUND', $item->id), 'notice');
}
} else {
$app->enqueueMessage(JText::sprintf('COM_DJCATALOG2_ERROR_GEOLOCATION_EMPTY_ADDRESS', $item->id), 'notice');
}
}
return true;
}
function _buildContentWhere($ignoreFilters = array(), &$query)
{
$view = JFactory::getApplication()->input->get('view');
$db = JFactory::getDbo();
$user = JFactory::getUser();
$userGroups = implode(',', $user->getAuthorisedViewLevels());
$params = $this->getState('params');
if (empty($params)) {
$params = Djcatalog2Helper::getParams();
}
$filter_featured = $this->getState('filter.featured');
$filter_restricted = $params->get('items_show_restricted', 0);
$filter_catid = $this->getState('filter.category');
$filter_catalogue = $this->getState('filter.catalogue');
$filter_producerid = $this->getState('filter.producer');
$filter_pks = $this->getState('filter.item_ids');
$filter_parent = $this->getState('filter.parent', 0);
$filter_price_from = $this->getState('filter.price_from');
$filter_price_to = $this->getState('filter.price_to');
$filter_index = $this->getState('filter.index', false);
$filter_state = $this->getState('filter.state', '1');
$filter_owner = $this->getState('filter.owner');
$filter_map_address = $this->getState('filter.map.address', false);
$filter_map_radius = $this->getState('filter.map.radius', false);
$filter_map = $filter_map_address != false ? true : $this->getState('filter.map');
$where = array();
///// new
$category_subquery = 'SELECT ic.item_id ' . 'FROM #__djc2_items_categories AS ic ' . 'INNER JOIN #__djc2_categories AS c ON c.id=ic.category_id ' . 'WHERE c.published = 1';
if (!$user->authorise('core.admin')) {
$category_subquery .= ' AND c.access IN (' . $userGroups . ') ';
}
$join_subcategories = true;
if (is_array($filter_catid) && !empty($filter_catid)) {
JArrayHelper::toInteger($filter_catid);
$category_subquery .= ' AND category_id IN (' . implode(',', $filter_catid) . ')';
} else {
if ((int) $filter_catid >= 0) {
if ($filter_catalogue && is_scalar($filter_catid)) {
$category_subquery .= ' AND ic.category_id =' . (int) $filter_catid;
} else {
if ((int) $filter_catid > 0) {
$categories = Djc2Categories::getInstance(array('state' => '1', 'access' => $userGroups));
if ($parent = $categories->get((int) $filter_catid)) {
$childrenList = array($parent->id);
$parent->makeChildrenList($childrenList);
if ($childrenList) {
$cids = implode(',', $childrenList);
$category_subquery .= ' AND ic.category_id IN (' . $cids . ')';
} else {
if ($filter_catid != 0) {
JError::raiseError(404, JText::_("COM_DJCATALOG2_PAGE_NOT_FOUND"));
}
}
}
} else {
$join_subcategories = false;
}
}
}
}
if ($join_subcategories) {
$query->join('inner', '(' . $category_subquery . ') as category_filter ON i.id = category_filter.item_id');
} else {
$query->where('c.published = 1');
}
/// ------
if (!in_array('producer', $ignoreFilters) && $filter_producerid > 0) {
$where[] = 'i.producer_id = ' . (int) $filter_producerid;
}
if (!in_array('price', $ignoreFilters)) {
if ($filter_price_from > 0) {
$where[] = '((i.price >= ' . floatval(str_replace(',', '.', $filter_price_from)) . ' AND i.special_price = 0) OR (i.special_price > 0 AND i.special_price >= ' . floatval(str_replace(',', '.', $filter_price_from)) . '))';
}
if ($filter_price_to > 0) {
$where[] = '((i.price <= ' . floatval(str_replace(',', '.', $filter_price_to)) . ' AND i.special_price = 0) OR (i.special_price > 0 AND i.special_price <= ' . floatval(str_replace(',', '.', $filter_price_to)) . '))';
}
}
if (!in_array('featured', $ignoreFilters) && $filter_featured > 0) {
$where[] = 'i.featured = 1';
}
$nullDate = $db->quote($db->getNullDate());
$date = JFactory::getDate();
$nowDate = $db->quote($date->toSql());
if ($this->getState('filter.publish_date', true)) {
$query->where('(i.publish_up = ' . $nullDate . ' OR i.publish_up <= ' . $nowDate . ')');
$query->where('(i.publish_down = ' . $nullDate . ' OR i.publish_down >= ' . $nowDate . ')');
}
if (!in_array('atoz', $ignoreFilters) && $filter_index !== false) {
//$where[] = ' LOWER(i.name) LIKE '.$db->quote( $db->escape( $filter_index, true ).'%', false );
if ($filter_index === 'num') {
$where[] = '( i.name REGEXP ' . $db->quote('^[0-9]', false) . ')';
} else {
$where[] = '( LOWER(i.name) LIKE ' . $db->quote($db->escape($filter_index, true) . '%', false) . ' COLLATE utf8_bin ' . ' OR UPPER(i.name) LIKE ' . $db->quote($db->escape($filter_index, true) . '%', false) . ' COLLATE utf8_bin )';
}
}
if (!in_array('item_ids', $ignoreFilters) && !empty($filter_pks) && is_array($filter_pks)) {
JArrayHelper::toInteger($filter_pks);
$query->join('inner', '(select id from #__djc2_items where id in (' . implode(',', $filter_pks) . ')) AS item_pks on item_pks.id = i.id');
}
if (!in_array('owner', $ignoreFilters) && $filter_owner > 0) {
$where[] = 'i.created_by = ' . $filter_owner;
}
if ($filter_state == '1') {
$where[] = 'i.published = 1';
} else {
if ($filter_state == '-1') {
$where[] = 'i.published = 0';
} else {
if ($filter_state == '2') {
$where[] = 'i.published = 2';
} else {
if ($filter_state == '2') {
$where[] = '(i.published = 1 OR i.published = 2)';
}
}
}
}
if (!$user->authorise('core.admin') && !$filter_restricted) {
$where[] = 'i.access IN (' . $userGroups . ')';
}
if ($filter_parent !== false && (string) $filter_parent != '*') {
$where[] = 'i.parent_id=' . (int) $filter_parent;
}
if ($filter_map) {
$where[] = '(i.latitude IS NOT NULL AND i.longitude IS NOT NULL)';
}
if ($filter_map_address) {
require_once JPath::clean(JPATH_ROOT . '/administrator/components/com_djcatalog2/lib/geocode.php');
$filter_map_radius = $this->getState('filter.map.radius', 25);
$latitude = $longitude = false;
if ($coords = DJCatalog2Geocode::getLocation($filter_map_address)) {
$latitude = !empty($coords['lat']) ? $coords['lat'] : false;
$longitude = !empty($coords['lng']) ? $coords['lng'] : false;
}
if ($latitude != false && $longitude != false) {
$radius_coeff = 0;
$filter_map_unit = $this->getState('filter.map.unit', 'km');
if ($filter_map_unit == 'km') {
$radius_coeff = 6371;
} else {
$radius_coeff = 3958;
}
$query->having($filter_map_radius . ' >= ' . '(' . $radius_coeff . ' * ACOS(COS( RADIANS(i.latitude) ) * COS(RADIANS(' . $latitude . ')) * COS(RADIANS(' . $longitude . ')-radians(i.longitude)) + SIN(RADIANS(i.latitude)) * SIN(RADIANS(' . $latitude . '))))');
}
}
return $where;
}
