function fn_get_banners($params = array(), $lang_code = CART_LANGUAGE) { $default_params = array('items_per_page' => 0); $params = array_merge($default_params, $params); $sortings = array('position' => '?:banners.position', 'timestamp' => '?:banners.timestamp', 'name' => '?:banner_descriptions.banner'); $condition = $limit = ''; if (!empty($params['limit'])) { $limit = db_quote(' LIMIT 0, ?i', $params['limit']); } $sorting = db_sort($params, $sortings, 'name', 'asc'); $condition = AREA == 'A' ? '' : " AND ?:banners.status = 'A' "; $condition .= fn_get_localizations_condition('?:banners.localization'); $condition .= AREA == 'A' ? '' : " AND (?:banners.type != 'G' OR ?:banner_images.banner_image_id IS NOT NULL) "; if (!empty($params['item_ids'])) { $condition .= db_quote(' AND ?:banners.banner_id IN (?n)', explode(',', $params['item_ids'])); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:banners.timestamp >= ?i AND ?:banners.timestamp <= ?i)", $params['time_from'], $params['time_to']); } fn_set_hook('get_banners', $params, $condition, $sorting, $limit, $lang_code); $fields = array('?:banners.banner_id', '?:banners.type', '?:banners.target', '?:banners.status', '?:banners.position', '?:banner_descriptions.banner', '?:banner_descriptions.description', '?:banner_descriptions.url', '?:banner_images.banner_image_id'); if (fn_allowed_for('ULTIMATE')) { $fields[] = '?:banners.company_id'; } $banners = db_get_array("SELECT ?p FROM ?:banners " . "LEFT JOIN ?:banner_descriptions ON ?:banner_descriptions.banner_id = ?:banners.banner_id AND ?:banner_descriptions.lang_code = ?s" . "LEFT JOIN ?:banner_images ON ?:banner_images.banner_id = ?:banners.banner_id AND ?:banner_images.lang_code = ?s" . "WHERE 1 ?p ?p ?p", implode(", ", $fields), $lang_code, $lang_code, $condition, $sorting, $limit); foreach ($banners as $k => $v) { $banners[$k]['main_pair'] = fn_get_image_pairs($v['banner_image_id'], 'promo', 'M', true, false, $lang_code); } fn_set_hook('get_banners_post', $banners, $params); return array($banners, $params); }
function fn_get_postcode_location($postcode_location_id, $lang_code = CART_LANGUAGE) { $fields = array('?:postcode_locations.*', '?:postcode_location_descriptions.*', '?:country_descriptions.country as country_title'); $join = db_quote(" LEFT JOIN ?:postcode_location_descriptions ON ?:postcode_locations.postcode_location_id = ?:postcode_location_descriptions.postcode_location_id AND ?:postcode_location_descriptions.lang_code = ?s", $lang_code); $join .= db_quote(" LEFT JOIN ?:country_descriptions ON ?:postcode_locations.country = ?:country_descriptions.code AND ?:country_descriptions.lang_code = ?s", $lang_code); $condition = db_quote(" ?:postcode_locations.postcode_location_id = ?i ", $postcode_location_id); $condition .= AREA == 'C' && defined('CART_LOCALIZATION') ? fn_get_localizations_condition('?:postcode_locations.localization') : ''; $postcode_location = db_get_row('SELECT ?p FROM ?:postcode_locations ?p WHERE ?p', implode(', ', $fields), $join, $condition); return $postcode_location; }
function fn_get_store_location($store_location_id, $lang_code = CART_LANGUAGE) { $fields = array('?:store_locations.*', '?:store_location_descriptions.*', '?:country_descriptions.country as country_title'); $join = db_quote(" LEFT JOIN ?:store_location_descriptions ON ?:store_locations.store_location_id = ?:store_location_descriptions.store_location_id AND ?:store_location_descriptions.lang_code = ?s", $lang_code); $join .= db_quote(" LEFT JOIN ?:country_descriptions ON ?:store_locations.country = ?:country_descriptions.code AND ?:country_descriptions.lang_code = ?s", $lang_code); $condition = db_quote(" ?:store_locations.store_location_id = ?i ", $store_location_id); $condition .= AREA == 'C' && defined('CART_LOCALIZATION') ? fn_get_localizations_condition('?:store_locations.localization') : ''; $store_location = db_get_row('SELECT ?p FROM ?:store_locations ?p WHERE ?p', implode(', ', $fields), $join, $condition); $store_location["image_pairs"] = fn_get_image_pairs($store_location_id, 'store_locations', 'M', true, true, $lang_code); $store_location["additional_image_pairs"] = fn_get_image_pairs($store_location_id, 'store_locations', 'A', true, true, $lang_code); return $store_location; }
function fn_get_store_locations($params, $items_per_page = 0, $lang_code = CART_LANGUAGE) { $default_params = array('page' => 1, 'q' => '', 'match' => 'any'); $params = array_merge($default_params, $params); $fields = array('?:store_locations.*', '?:store_location_descriptions.*', '?:country_descriptions.country as country_title'); $join = db_quote(" LEFT JOIN ?:store_location_descriptions ON ?:store_locations.store_location_id = ?:store_location_descriptions.store_location_id AND ?:store_location_descriptions.lang_code = ?s", $lang_code); $join .= db_quote(" LEFT JOIN ?:country_descriptions ON ?:store_locations.country = ?:country_descriptions.code AND ?:country_descriptions.lang_code = ?s", $lang_code); $condition = 1; if (AREA == 'C') { $condition .= " AND status = 'A'"; } // Search string condition for SQL query if (!empty($params['q'])) { if ($params['match'] == 'any') { $pieces = explode(' ', $params['q']); $search_type = ' OR '; } elseif ($params['match'] == 'all') { $pieces = explode(' ', $params['q']); $search_type = ' AND '; } else { $pieces = array($params['q']); $search_type = ''; } $_condition = array(); foreach ($pieces as $piece) { $tmp = db_quote("?:store_location_descriptions.name LIKE ?l", "%{$piece}%"); // check search words $tmp .= db_quote(" OR ?:store_location_descriptions.description LIKE ?l", "%{$piece}%"); $tmp .= db_quote(" OR ?:store_location_descriptions.city LIKE ?l", "%{$piece}%"); $tmp .= db_quote(" OR ?:country_descriptions.country LIKE ?l", "%{$piece}%"); $_condition[] = '(' . $tmp . ')'; } $_cond = implode($search_type, $_condition); if (!empty($_condition)) { $condition .= ' AND (' . $_cond . ') '; } unset($_condition); } $condition .= AREA == 'C' && defined('CART_LOCALIZATION') ? fn_get_localizations_condition('?:store_locations.localization') : ''; $sorting = "?:store_locations.position, ?:store_location_descriptions.name"; $limit = ''; $total = 0; if (!empty($items_per_page)) { $total = db_get_field("SELECT COUNT(?:store_locations.store_location_id) FROM ?:store_locations ?p WHERE ?p", $join, $condition); $limit = fn_paginate($params['page'], $total, $items_per_page); } $data = db_get_array('SELECT ?p FROM ?:store_locations ?p WHERE ?p GROUP BY ?:store_locations.store_location_id ORDER BY ?p ?p', implode(', ', $fields), $join, $condition, $sorting, $limit); if (!$total) { $total = count($data); } return array($data, $params, $total); }
function fn_get_banners($params, $lang_code = CART_LANGUAGE) { $default_params = array('items_per_page' => 0, 'sort_by' => 'name'); $params = array_merge($default_params, $params); $sortings = array('timestamp' => '?:banners.timestamp', 'name' => '?:banner_descriptions.banner'); $directions = array('asc' => 'asc', 'desc' => 'desc'); $condition = $limit = ''; if (!empty($params['limit'])) { $limit = db_quote(' LIMIT 0, ?i', $params['limit']); } if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { $params['sort_order'] = 'asc'; } if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = 'name'; } $sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']]; $condition = AREA == 'A' ? '' : " AND ?:banners.status = 'A' "; $condition .= fn_get_localizations_condition('?:banners.localization'); if (!empty($params['item_ids'])) { $condition .= db_quote(' AND ?:banners.banner_id IN (?n)', explode(',', $params['item_ids'])); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:banners.timestamp >= ?i AND ?:banners.timestamp <= ?i)", $params['time_from'], $params['time_to']); } $banners = db_get_array("SELECT ?:banners.banner_id, ?:banners.type, ?:banners.target, ?:banners.status, ?:banners.url, ?:banner_descriptions.banner, ?:banner_descriptions.description FROM ?:banners LEFT JOIN ?:banner_descriptions ON ?:banner_descriptions.banner_id = ?:banners.banner_id AND ?:banner_descriptions.lang_code = ?s WHERE 1 ?p ORDER BY ?p ?p", $lang_code, $condition, $sorting, $limit); foreach ($banners as $k => $v) { $banners[$k]['main_pair'] = fn_get_image_pairs($v['banner_id'], 'banner', 'M', true, false, $lang_code); } if (!empty($params['item_ids'])) { $banners = fn_sort_by_ids($banners, explode(',', $params['item_ids']), 'banner_id'); } fn_set_hook('get_banners', $banners); return array($banners, $params); }
/** * Returns list of pages * <i>$params</i> - Array of various parameters used for element selection: * <ul> * <li>page_id - If defined and not zero, get data for the page with this id; otherwise get data for all pages</li> * <li>item_ids - A comma-delimited page identifiers list; if defined, get data for the pages with these ids; otherwise get data for all pages</li> * <li>except_id - Identifier of the page to be excluded from the result</li> * <li>parent_id - If defined and not zero, get data for the pages with this parent page id</li> * <li>active_page_id - Identifier of the page being currently shown</li> * <li>current_page_id - The same as <i>active_page_id</i></li> * * <li>add_root - If defined, additionally returns root element data</li> * <li>subpages - If defined, additionally returns subpages</li> * <li>get_tree - If defined, pages will be returned as tree; otherwise as list. Possible value: <i>plain</i></li> * <li>visible - For pages tree: show visible branch only</li> * * <li>page - Number of the current page for pagination</li> * * <li>pdescr - If defined, additionally returns descriptions. Possible value: <i>Y</i></li> * * <li>vendor_pages - If defined, try to return pages for the company defined by <i>company_id</i></li> * <li>company_id - If <i>vendor_pages</i> is defined: if defined, get data for the company with this id</li> * * <li>neighbours - If defined, try to return neighbor pages for the page with the id <i>neighbours_page_id</i></li> * <li>neighbours_page_id - If <i>neighbours</i> is defined: if defined, get neighbor pages for the page with this id</li> * * <li>limit - If defined, used to limit your MySQL query results by this value</li> * <li>sort_by - Table field to sort by, default is position</li> * <li>sort_order - Sorting direction, ascending or descending; Possible values: <i>asc</i> or <i>desc</i>, default is <i>asc</i></li> * * <li>status - If defined, returns pages only with this status. Can be comma delimited statuses list</li> * * <li>period - If defined, get pages by time period. Time period is generated by ::fn_create_periods</li> * <li>time_from - Returns pages created earlier than this time</li> * <li>time_to - Returns pages created later than this time</li> * * <li>parent_page_id - Deprecated, <i>parent_id</i> used instead</li> * <li>from_page_id - Deprecated, <i>parent_id</i> used instead</li> * </ul> * @param array $params Array of params * @param int $items_per_page Limit items per page * @param string $lang_code 2-letter language code * @return array List of pages, params */ function fn_get_pages($params = array(), $items_per_page = 0, $lang_code = CART_LANGUAGE) { /** * Changes params for selecting pages * * @param array $params Pages search params * @param int $items_per_page Items per page * @param string $lang_code Two-letter language code (e.g. 'en', 'ru', etc.) */ fn_set_hook('get_pages_pre', $params, $items_per_page, $lang_code); $view_type = 'pages'; if (!empty($params['page_type']) && fn_is_exclusive_page_type($params['page_type'])) { $view_type .= '_' . $params['page_type']; } // Init filter $params = LastView::instance()->update($view_type, $params); $default_params = array('page_id' => 0, 'page' => 1, 'visible' => false, 'get_tree' => '', 'pdescr' => '', 'subpages' => '', 'match' => '', 'page_type' => '', 'items_per_page' => $items_per_page); if (is_array($params)) { $params = array_merge($default_params, $params); } else { $params = $default_params; } if (empty($params['pname']) && empty($params['pdescr']) && empty($params['subpages'])) { $params['pname'] = 'Y'; } $fields = array('?:pages.*'); if (!empty($params['simple'])) { $fields[] = '?:page_descriptions.page'; } else { $fields[] = '?:page_descriptions.*'; } // Define sort fields $sortings = array('position' => array('?:pages.position', '?:page_descriptions.page'), 'name' => '?:page_descriptions.page', 'timestamp' => '?:pages.timestamp', 'type' => '?:pages.page_type', 'multi_level' => array('?:pages.parent_id', '?:pages.position', '?:page_descriptions.page')); $auth =& $_SESSION['auth']; $condition = '1'; $join = $limit = $group_by = ''; if (isset($params['q']) && fn_string_not_empty($params['q'])) { $params['q'] = trim($params['q']); if ($params['match'] == 'any') { $pieces = fn_explode(' ', $params['q']); $search_type = ' OR '; } elseif ($params['match'] == 'all') { $pieces = fn_explode(' ', $params['q']); $search_type = ' AND '; } else { $pieces = array($params['q']); $search_type = ''; } $_condition = array(); foreach ($pieces as $piece) { if (strlen($piece) == 0) { continue; } $tmp = array(); if (!empty($params['pname']) && $params['pname'] == 'Y') { $tmp[] = db_quote("?:page_descriptions.page LIKE ?l", "%{$piece}%"); // check search words } if ($params['pdescr'] == 'Y') { $tmp[] = db_quote("?:page_descriptions.description LIKE ?l", "%{$piece}%"); } if (!empty($tmp)) { $_condition[] = '(' . implode(' OR ', $tmp) . ')'; } } if (!empty($_condition)) { $condition .= ' AND (' . implode($search_type, $_condition) . ')'; } } $condition .= fn_get_company_condition('?:pages.company_id'); if (isset($params['parent_id']) && $params['parent_id'] !== '') { $p_ids = array(); if ($params['subpages'] == 'Y') { $p_ids = db_get_fields("SELECT a.page_id FROM ?:pages as a LEFT JOIN ?:pages as b ON b.page_id = ?i WHERE a.id_path LIKE CONCAT(b.id_path, '/%')", $params['parent_id']); } $p_ids[] = $params['parent_id']; $condition .= db_quote(" AND ?:pages.parent_id IN (?n)", $p_ids); } if (isset($params['parent_page_id'])) { // set parent id, that was set in block properties $params['from_page_id'] = $params['parent_page_id']; } if (!empty($params['from_page_id'])) { $from_id_path = db_get_field("SELECT id_path FROM ?:pages WHERE page_id = ?i", $params['from_page_id']); $condition .= db_quote(" AND ?:pages.id_path LIKE ?l", "{$from_id_path}/%"); } if (!empty($params['status'])) { $condition .= db_quote(" AND ?:pages.status IN (?a)", $params['status']); } if (!empty($params['vendor_pages']) && empty($params['company_id'])) { return array(array(), $params); } elseif (!empty($params['company_id'])) { $condition .= db_quote(" AND ?:pages.company_id = ?i", $params['company_id']); } if (empty($params['full_search'])) { $condition .= db_quote(" AND ?:pages.page_type IN (?a)", array_keys(fn_get_page_type_filter($params['page_type']))); } if (!empty($params['visible'])) { // for pages tree: show visible branch only $page_ids = array(); if (!empty($params['current_page_id'])) { $cur_id_path = db_get_field("SELECT id_path FROM ?:pages WHERE page_id = ?i", $params['current_page_id']); if (!empty($cur_id_path)) { $page_ids = explode('/', $cur_id_path); } } if (!empty($from_id_path)) { $_page_ids = explode('/', $from_id_path); $page_ids = array_merge($page_ids, $_page_ids); $page_ids = array_unique($page_ids); } $page_ids[] = $params['page_id']; $condition .= db_quote(" AND ?:pages.parent_id IN (?n)", $page_ids); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:pages.timestamp >= ?i AND ?:pages.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['item_ids'])) { // get only defined pages $condition .= db_quote(" AND ?:pages.page_id IN (?n)", explode(',', $params['item_ids'])); } if (!empty($params['except_id']) && (empty($params['item_ids']) || !empty($params['item_ids']) && !in_array($params['except_id'], explode(',', $params['item_ids'])))) { $condition .= db_quote(' AND ?:pages.page_id != ?i AND ?:pages.parent_id != ?i', $params['except_id'], $params['except_id']); } if (AREA != 'A') { $condition .= " AND (" . fn_find_array_in_set($auth['usergroup_ids'], '?:pages.usergroup_ids', true) . ")"; $condition .= fn_get_localizations_condition('?:pages.localization', true); $condition .= db_quote(" AND (use_avail_period = ?s OR (use_avail_period = ?s AND avail_from_timestamp <= ?i AND avail_till_timestamp >= ?i))", 'N', 'Y', TIME, TIME); } $join = db_quote('LEFT JOIN ?:page_descriptions ON ?:pages.page_id = ?:page_descriptions.page_id AND ?:page_descriptions.lang_code = ?s', $lang_code); if (!empty($params['limit'])) { $limit = db_quote(" LIMIT 0, ?i", $params['limit']); } if (!empty($params['neighbours'])) { $parent_ids = array(); if (!empty($params['neighbours_page_id'])) { $id_path = db_get_field("SELECT id_path FROM ?:pages WHERE page_id = ?i", $params['neighbours_page_id']); $parent_ids = explode('/', $id_path); if (count($parent_ids) == 1) { array_unshift($parent_ids, 0); } $params['root_id'] = $parent_ids[0]; } else { $parent_ids[] = 0; } $condition .= db_quote(" AND ?:pages.parent_id IN (?n)", array_unique($parent_ids)); } fn_set_hook('get_pages', $params, $join, $condition, $fields, $group_by, $sortings, $lang_code); if (!empty($params['get_tree'])) { $params['sort_by'] = 'multi_level'; } $sorting = db_sort($params, $sortings, 'position', 'asc'); if (!empty($group_by)) { $group_by = ' GROUP BY ' . $group_by; } // Get search conditions if (!empty($params['get_conditions'])) { return array($fields, $join, $condition); } if (!empty($params['items_per_page'])) { $params['total_items'] = db_get_field("SELECT COUNT(DISTINCT(?:pages.page_id)) FROM ?:pages ?p WHERE ?p ?p ?p", $join, $condition, $group_by, $sorting); $limit = db_paginate($params['page'], $params['items_per_page'], $params['total_items']); } $pages = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:pages ?p WHERE ?p ?p ?p ?p", 'page_id', $join, $condition, $group_by, $sorting, $limit); if (!empty($params['active_page_id']) && !empty($pages[$params['active_page_id']])) { $pages[$params['active_page_id']]['active'] = true; Registry::set('runtime.active_page_ids', explode('/', $pages[$params['active_page_id']]['id_path'])); } if (!empty($pages)) { foreach ($pages as $k => $v) { $pages[$k]['level'] = substr_count($v['id_path'], '/'); } if (!empty($params['get_tree'])) { $delete_keys = array(); foreach ($pages as $k => $v) { if (!empty($v['parent_id']) && !empty($pages[$v['parent_id']])) { $pages[$v['parent_id']]['subpages'][$v['page_id']] =& $pages[$k]; $delete_keys[] = $k; } if (!empty($v['parent_id']) && (!isset($params['root_id']) && empty($pages[$v['parent_id']]) || isset($params['root_id']) && $v['parent_id'] != $params['root_id']) && (empty($params['from_page_id']) || $params['from_page_id'] != $v['parent_id'])) { // delete pages that don't have parent. FIXME: should be done on database layer $delete_keys[] = $k; } } foreach ($delete_keys as $k) { unset($pages[$k]); } } elseif (!empty($params['item_ids'])) { $pages = fn_sort_by_ids($pages, explode(',', $params['item_ids']), 'page_id'); } if ($params['get_tree'] == 'plain') { $pages = fn_multi_level_to_plain($pages, 'subpages'); } if (!empty($params['get_children_count'])) { $where_condition = !empty($params['except_id']) ? db_quote(' AND page_id != ?i', $params['except_id']) : ''; if ($params['get_tree'] == 'plain') { $_page_ids = array(); foreach ($pages as $_p) { $_page_ids[] = $_p['page_id']; } } else { $_page_ids = array_keys($pages); } $children = db_get_hash_single_array("SELECT parent_id, COUNT(page_id) as children FROM ?:pages WHERE parent_id IN (?n) ?p GROUP BY parent_id", array('parent_id', 'children'), $_page_ids, $where_condition); if (!empty($children)) { if ($params['get_tree'] == 'plain') { foreach ($pages as $_id => $_p) { if (!empty($children[$_p['page_id']])) { $pages[$_id]['has_children'] = true; } } } else { foreach ($children as $k => $v) { $pages[$k]['has_children'] = !empty($v); } } } } } if (!empty($params['add_root'])) { array_unshift($pages, array('page_id' => '', 'page' => $params['add_root'])); } fn_dropdown_appearance_cut_second_third_levels($pages, 'subpages', $params); fn_set_hook('post_get_pages', $pages, $params, $lang_code); LastView::instance()->processResults($view_type, $pages, $params); return array($pages, $params); }
function fn_get_static_data($params, $lang_code = DESCR_SL) { $default_params = array(); $params = array_merge($default_params, $params); $schema = fn_get_schema('static_data', 'schema'); $section_data = $schema[$params['section']]; $fields = array('sd.param_id', 'sd.param', '?:static_data_descriptions.descr'); $condition = ''; $sorting = "sd.position"; if (!empty($params['multi_level'])) { $sorting = "sd.parent_id, sd.position, ?:static_data_descriptions.descr"; } if (!empty($params['status'])) { $condition .= db_quote(" AND sd.status = ?s", $params['status']); } // Params from request if (!empty($section_data['owner_object'])) { $param = $section_data['owner_object']; $value = $param['default_value']; if (!empty($params['request'][$param['key']])) { $value = $params['request'][$param['key']]; } elseif (!empty($_REQUEST[$param['key']])) { $value = $_REQUEST[$param['key']]; } $condition .= db_quote(" AND sd.?p = ?s", $param['param'], $value); } if (!empty($params['use_localization'])) { $condition .= fn_get_localizations_condition('sd.localization'); } if (!empty($params['get_params'])) { $fields[] = "sd.param_2"; $fields[] = "sd.param_3"; $fields[] = "sd.param_4"; $fields[] = "sd.param_5"; $fields[] = "sd.status"; $fields[] = "sd.position"; $fields[] = "sd.parent_id"; $fields[] = "sd.id_path"; $fields[] = "sd.class"; } fn_set_hook('get_static_data', $params, $fields, $condition, $sorting, $lang_code); $s_data = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:static_data AS sd LEFT JOIN ?:static_data_descriptions ON sd.param_id = ?:static_data_descriptions.param_id AND ?:static_data_descriptions.lang_code = ?s WHERE sd.section = ?s ?p ORDER BY sd.position", 'param_id', $lang_code, $params['section'], $condition); if (!empty($params['icon_name'])) { $_icons = fn_get_image_pairs(array_keys($s_data), $params['icon_name'], 'M', true, true, $lang_code); foreach ($s_data as $k => $v) { $s_data[$k]['icon'] = !empty($_icons[$k]) ? array_pop($_icons[$k]) : array(); } } if (!empty($params['generate_levels'])) { foreach ($s_data as $k => $v) { if (!empty($v['id_path'])) { $s_data[$k]['level'] = substr_count($v['id_path'], '/'); } } } if (!empty($params['multi_level']) && !empty($params['get_params'])) { $s_data = fn_make_tree($s_data, 0, 'param_id', 'subitems'); } if (!empty($params['plain'])) { $s_data = fn_multi_level_to_plain($s_data, 'subitems'); } return $s_data; }
function fn_get_static_data($params, $lang_code = DESCR_SL) { $default_params = array('section' => 'C'); $params = array_merge($default_params, $params); $fields = array('?:static_data.param_id', '?:static_data.param', '?:static_data_descriptions.descr'); $condition = ''; $sorting = "?:static_data.position"; if (!empty($params['multi_level'])) { $sorting = "?:static_data.parent_id, ?:static_data.position, ?:static_data_descriptions.descr"; } if (!empty($params['status'])) { $condition .= db_quote(" AND ?:static_data.status = ?s", $params['status']); } if (!empty($params['use_localization'])) { $condition .= fn_get_localizations_condition('?:static_data.localization'); } if (!empty($params['get_params'])) { $fields[] = "?:static_data.param_2"; $fields[] = "?:static_data.param_3"; $fields[] = "?:static_data.param_4"; $fields[] = "?:static_data.param_5"; $fields[] = "?:static_data.status"; $fields[] = "?:static_data.position"; $fields[] = "?:static_data.parent_id"; $fields[] = "?:static_data.id_path"; } $s_data = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:static_data LEFT JOIN ?:static_data_descriptions ON ?:static_data.param_id = ?:static_data_descriptions.param_id AND ?:static_data_descriptions.lang_code = ?s WHERE ?:static_data.section = ?s ?p ORDER BY ?:static_data.position", 'param_id', $lang_code, $params['section'], $condition); if (!empty($params['icon_name'])) { foreach ($s_data as $k => $v) { $s_data[$k]['icon'] = fn_get_image_pairs($v['param_id'], $params['icon_name'], 'M', true, true, $lang_code); } } if (!empty($params['generate_levels'])) { foreach ($s_data as $k => $v) { if (!empty($v['id_path'])) { $s_data[$k]['level'] = substr_count($v['id_path'], '/'); } } } if (!empty($params['multi_level'])) { $delete_keys = array(); foreach ($s_data as $k => $v) { if (!empty($v['parent_id'])) { if (!empty($s_data[$v['parent_id']])) { $s_data[$v['parent_id']]['subitems'][$v['param_id']] = $v; $s_data[$k] =& $s_data[$v['parent_id']]['subitems'][$v['param_id']]; } $delete_keys[] = $k; } } foreach ($delete_keys as $k) { unset($s_data[$k]); } } if (!empty($params['plain'])) { $s_data = fn_multi_level_to_plain($s_data, 'subitems'); } return $s_data; }
function fn_get_news($params, $items_per_page = 0, $lang_code = CART_LANGUAGE) { $default_params = array('page' => 1, 'items_per_page' => $items_per_page); $params = array_merge($default_params, $params); $fields = array('?:news.*', 'descr.news', 'descr.description'); // Define sort fields $sortings = array('position' => '?:news.position', 'name' => 'descr.news', 'date' => '?:news.date'); $limit = $condition = ''; $join = db_quote(" LEFT JOIN ?:news_descriptions AS descr ON descr.news_id = ?:news.news_id AND descr.lang_code = ?s", $lang_code); $condition .= AREA == 'A' ? '1 ' : " ?:news.status = 'A'"; $condition .= fn_get_localizations_condition('?:news.localization'); // Get additional information about companies if (fn_allowed_for('ULTIMATE')) { $fields[] = ' ?:companies.company as company'; $sortings['company'] = 'company'; $join .= db_quote(" LEFT JOIN ?:companies ON ?:companies.company_id = ?:news.company_id"); } if (isset($params['q']) && fn_string_not_empty($params['q'])) { $params['q'] = trim($params['q']); if ($params['match'] == 'any') { $pieces = fn_explode(' ', $params['q']); $search_type = ' OR '; } elseif ($params['match'] == 'all') { $pieces = fn_explode(' ', $params['q']); $search_type = ' AND '; } else { $pieces = array($params['q']); $search_type = ''; } $_condition = array(); foreach ($pieces as $piece) { if (strlen($piece) == 0) { continue; } $tmp = array(); $tmp[] = db_quote("descr.news LIKE ?l", "%{$piece}%"); $tmp[] = db_quote("descr.description LIKE ?l", "%{$piece}%"); $_condition[] = '(' . join(' OR ', $tmp) . ')'; } $_cond = implode($search_type, $_condition); if (!empty($_condition)) { $condition .= ' AND (' . $_cond . ') '; } } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:news.date >= ?i AND ?:news.date <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['item_ids'])) { $condition .= db_quote(' AND ?:news.news_id IN (?n)', explode(',', $params['item_ids'])); } $limit = ''; if (!empty($params['limit'])) { $limit = db_quote(" LIMIT 0, ?i", $params['limit']); } elseif (!empty($params['items_per_page'])) { $params['total_items'] = db_get_field("SELECT COUNT(?:news.news_id) FROM ?:news ?p WHERE ?p", $join, $condition); $limit = db_paginate($params['page'], $params['items_per_page']); } fn_set_hook('get_news', $params, $fields, $join, $condition, $sorting, $limit, $lang_code); $sorting = db_sort($params, $sortings, 'date', 'desc'); // Used for Extended search if (!empty($params['get_conditions'])) { return array($fields, $join, $condition); } $fields = join(', ', $fields); $news = db_get_array("SELECT ?p FROM ?:news ?p WHERE ?p ?p ?p", $fields, $join, $condition, $sorting, $limit); /** * Get additional data for selected news * * @param array $news news list * @param string $lang_code language code */ fn_set_hook('get_news_post', $news, $lang_code); return array($news, $params); }
function fn_get_products($params, $items_per_page = 0, $lang_code = CART_LANGUAGE) { /** * Changes params for selecting products * * @param array $params Product search params * @param int $items_per_page Items per page * @param string $lang_code Two-letter language code (e.g. 'en', 'ru', etc.) */ fn_set_hook('get_products_pre', $params, $items_per_page, $lang_code); // Init filter $params = LastView::instance()->update('products', $params); // Set default values to input params $default_params = array('area' => AREA, 'extend' => array('product_name', 'prices', 'categories'), 'custom_extend' => array(), 'pname' => '', 'pshort' => '', 'pfull' => '', 'pkeywords' => '', 'feature' => array(), 'type' => 'simple', 'page' => 1, 'action' => '', 'variants' => array(), 'ranges' => array(), 'custom_range' => array(), 'field_range' => array(), 'features_hash' => '', 'limit' => 0, 'bid' => 0, 'match' => '', 'tracking' => array(), 'get_frontend_urls' => false, 'items_per_page' => $items_per_page); if (empty($params['custom_extend'])) { $params['extend'] = !empty($params['extend']) ? array_merge($default_params['extend'], $params['extend']) : $default_params['extend']; } else { $params['extend'] = $params['custom_extend']; } $params = array_merge($default_params, $params); if ((empty($params['pname']) || $params['pname'] != 'Y') && (empty($params['pshort']) || $params['pshort'] != 'Y') && (empty($params['pfull']) || $params['pfull'] != 'Y') && (empty($params['pkeywords']) || $params['pkeywords'] != 'Y') && (empty($params['feature']) || $params['feature'] != 'Y') && !empty($params['q'])) { $params['pname'] = 'Y'; } $auth =& $_SESSION['auth']; // Define fields that should be retrieved if (empty($params['only_short_fields'])) { $fields = array('products.*'); } else { $fields = array('product_id' => 'products.product_id', 'product_code' => 'products.product_code', 'product_type' => 'products.product_type', 'status' => 'products.status', 'company_id' => 'products.company_id', 'list_price' => 'products.list_price', 'amount' => 'products.amount', 'weight' => 'products.weight', 'tracking' => 'products.tracking', 'is_edp' => 'products.is_edp', 'return_period' => 'products.return_period'); } //AS started_price $fields['subtitle'] = 'descr1.subtitle'; $fields['top_title'] = 'descr1.top_title'; $fields['started_price'] = 'prices.price AS started_price'; // Define sort fields $sortings = array('code' => 'products.product_code', 'status' => 'products.status', 'product' => 'product', 'position' => 'products_categories.position', 'price' => 'price', 'list_price' => 'products.list_price', 'weight' => 'products.weight', 'amount' => 'products.amount', 'timestamp' => 'products.timestamp', 'updated_timestamp' => 'products.updated_timestamp', 'popularity' => 'popularity.total', 'company' => 'company_name', 'null' => 'NULL'); if (!empty($params['get_subscribers'])) { $sortings['num_subscr'] = 'num_subscr'; $fields['num_subscr'] = 'COUNT(DISTINCT product_subscriptions.subscription_id) as num_subscr'; } if (!empty($params['order_ids'])) { $sortings['p_qty'] = 'purchased_qty'; $sortings['p_subtotal'] = 'purchased_subtotal'; $fields['purchased_qty'] = "SUM(?:order_details.amount) as purchased_qty"; $fields['purchased_subtotal'] = "SUM(?:order_details.price * ?:order_details.amount) as purchased_subtotal"; } if (isset($params['compact']) && $params['compact'] == 'Y') { $union_condition = ' OR '; } else { $union_condition = ' AND '; } $join = $condition = $u_condition = $inventory_condition = ''; $having = array(); $extraCondition = array(); // Search string condition for SQL query if (isset($params['q']) && fn_string_not_empty($params['q'])) { $params['q'] = trim($params['q']); if ($params['match'] == 'any') { $pieces = fn_explode(' ', $params['q']); $search_type = ' OR '; } elseif ($params['match'] == 'all') { $pieces = fn_explode(' ', $params['q']); $search_type = ' AND '; } else { $pieces = array($params['q']); $search_type = ''; } $_condition = array(); foreach ($pieces as $piece) { if (strlen($piece) == 0) { continue; } $tmp = db_quote("(descr1.search_words LIKE ?l)", '%' . $piece . '%'); // check search words if ($params['pname'] == 'Y') { $tmp .= db_quote(" OR descr1.product LIKE ?l", '%' . $piece . '%'); } if ($params['pshort'] == 'Y') { $tmp .= db_quote(" OR descr1.short_description LIKE ?l", '%' . $piece . '%'); $tmp .= db_quote(" OR descr1.short_description LIKE ?l", '%' . htmlentities($piece, ENT_QUOTES, 'UTF-8') . '%'); } if ($params['pfull'] == 'Y') { $tmp .= db_quote(" OR descr1.full_description LIKE ?l", '%' . $piece . '%'); $tmp .= db_quote(" OR descr1.full_description LIKE ?l", '%' . htmlentities($piece, ENT_QUOTES, 'UTF-8') . '%'); } if ($params['pkeywords'] == 'Y') { $tmp .= db_quote(" OR (descr1.meta_keywords LIKE ?l OR descr1.meta_description LIKE ?l)", '%' . $piece . '%', '%' . $piece . '%'); } if (!empty($params['feature']) && $params['action'] != 'feature_search') { $tmp .= db_quote(" OR ?:product_features_values.value LIKE ?l", '%' . $piece . '%'); } fn_set_hook('additional_fields_in_search', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $tmp, $piece, $having); $_condition[] = '(' . $tmp . ')'; } $_cond = implode($search_type, $_condition); if (!empty($_condition)) { $condition .= ' AND (' . $_cond . ') '; } if (!empty($params['feature']) && $params['action'] != 'feature_search') { $join .= " LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id"; $condition .= db_quote(" AND (?:product_features_values.feature_id IN (?n) OR ?:product_features_values.feature_id IS NULL)", array_values($params['feature'])); } //if perform search we also get additional fields if ($params['pname'] == 'Y') { $params['extend'][] = 'product_name'; } if ($params['pshort'] == 'Y' || $params['pfull'] == 'Y' || $params['pkeywords'] == 'Y') { $params['extend'][] = 'description'; } unset($_condition); } // // [Advanced and feature filters] // if (!empty($params['apply_limit']) && $params['apply_limit'] && !empty($params['pid'])) { $pids = array(); foreach ($params['pid'] as $pid) { if ($pid != $params['exclude_pid']) { if (count($pids) == $params['limit']) { break; } else { $pids[] = $pid; } } } $params['pid'] = $pids; } if (!empty($params['features_hash']) || !fn_is_empty($params['variants']) || !empty($params['feature_code'])) { $join .= db_quote(" LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id AND ?:product_features_values.lang_code = ?s", $lang_code); } if (!empty($params['variants'])) { $params['features_hash'] .= implode('.', $params['variants']); } // Feature code if (!empty($params['feature_code'])) { $join .= db_quote(" LEFT JOIN ?:product_features ON ?:product_features_values.feature_id = ?:product_features.feature_id"); $condition .= db_quote(" AND ?:product_features.feature_code = ?s", $params['feature_code']); } $advanced_variant_ids = $simple_variant_ids = $ranges_ids = $fields_ids = $fields_ids_revert = $slider_vals = array(); if (!empty($params['features_hash'])) { list($av_ids, $ranges_ids, $fields_ids, $slider_vals, $fields_ids_revert) = fn_parse_features_hash($params['features_hash']); $advanced_variant_ids = db_get_hash_multi_array("SELECT feature_id, variant_id FROM ?:product_feature_variants WHERE variant_id IN (?n)", array('feature_id', 'variant_id'), $av_ids); } if (!empty($params['multiple_variants'])) { $simple_variant_ids = $params['multiple_variants']; } if (!empty($advanced_variant_ids)) { $join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS advanced_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_advanced ON pfv_advanced.product_id = products.product_id", $lang_code); $where_and_conditions = array(); foreach ($advanced_variant_ids as $k => $variant_ids) { $where_or_conditions = array(); foreach ($variant_ids as $variant_id => $v) { $where_or_conditions[] = db_quote(" FIND_IN_SET('?i', advanced_variants)", $variant_id); } $where_and_conditions[] = '(' . implode(' OR ', $where_or_conditions) . ')'; } $condition .= ' AND ' . implode(' AND ', $where_and_conditions); } if (!empty($simple_variant_ids)) { $join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS simple_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_simple ON pfv_simple.product_id = products.product_id", $lang_code); $where_conditions = array(); foreach ($simple_variant_ids as $k => $variant_id) { $where_conditions[] = db_quote(" FIND_IN_SET('?i', simple_variants)", $variant_id); } $condition .= ' AND ' . implode(' AND ', $where_conditions); } // // Ranges from text inputs // // Feature ranges if (!empty($params['custom_range'])) { foreach ($params['custom_range'] as $k => $v) { $k = intval($k); if (isset($v['from']) && fn_string_not_empty($v['from']) || isset($v['to']) && fn_string_not_empty($v['to'])) { if (!empty($v['type'])) { if ($v['type'] == 'D') { $v['from'] = fn_parse_date($v['from']); $v['to'] = fn_parse_date($v['to']); } } $join .= db_quote(" LEFT JOIN ?:product_features_values as custom_range_{$k} ON custom_range_{$k}.product_id = products.product_id AND custom_range_{$k}.lang_code = ?s", $lang_code); if (fn_string_not_empty($v['from']) && fn_string_not_empty($v['to'])) { $condition .= db_quote(" AND (custom_range_{$k}.value_int >= ?i AND custom_range_{$k}.value_int <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i) ", $v['from'], $v['to'], $k); } else { $condition .= " AND custom_range_{$k}.value_int" . (fn_string_not_empty($v['from']) ? db_quote(' >= ?i', $v['from']) : db_quote(" <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i ", $v['to'], $k)); } } } } // Product field ranges $filter_fields = fn_get_product_filter_fields(); if (!empty($params['field_range'])) { foreach ($params['field_range'] as $field_type => $v) { $structure = $filter_fields[$field_type]; if (!empty($structure) && (!empty($v['from']) || !empty($v['to']))) { if ($field_type == 'P') { // price $v['cur'] = !empty($v['cur']) ? $v['cur'] : CART_SECONDARY_CURRENCY; if (empty($v['orig_cur'])) { // saving the first user-entered values // will be always search by it $v['orig_from'] = $v['from']; $v['orig_to'] = $v['to']; $v['orig_cur'] = $v['cur']; $params['field_range'][$field_type] = $v; } if ($v['orig_cur'] != CART_PRIMARY_CURRENCY) { // calc price in primary currency $cur_prim_coef = Registry::get('currencies.' . $v['orig_cur'] . '.coefficient'); $decimals = Registry::get('currencies.' . CART_PRIMARY_CURRENCY . '.decimals'); $search_from = round($v['orig_from'] * floatval($cur_prim_coef), $decimals); $search_to = round($v['orig_to'] * floatval($cur_prim_coef), $decimals); } else { $search_from = $v['orig_from']; $search_to = $v['orig_to']; } // if user switch the currency, calc new values for displaying in filter if ($v['cur'] != CART_SECONDARY_CURRENCY) { if (CART_SECONDARY_CURRENCY == $v['orig_cur']) { $v['from'] = $v['orig_from']; $v['to'] = $v['orig_to']; } else { $prev_coef = Registry::get('currencies.' . $v['cur'] . '.coefficient'); $cur_coef = Registry::get('currencies.' . CART_SECONDARY_CURRENCY . '.coefficient'); $v['from'] = floor(floatval($v['from']) * floatval($prev_coef) / floatval($cur_coef)); $v['to'] = ceil(floatval($v['to']) * floatval($prev_coef) / floatval($cur_coef)); } $v['cur'] = CART_SECONDARY_CURRENCY; $params['field_range'][$field_type] = $v; } } $params["{$structure['db_field']}_from"] = trim(isset($search_from) ? $search_from : $v['from']); $params["{$structure['db_field']}_to"] = trim(isset($search_to) ? $search_to : $v['to']); } } } // Ranges from database if (!empty($ranges_ids)) { $filter_conditions = db_get_hash_multi_array("SELECT `from`, `to`, feature_id, filter_id, range_id FROM ?:product_filter_ranges WHERE range_id IN (?n)", array('filter_id', 'range_id'), $ranges_ids); $where_conditions = array(); foreach ($filter_conditions as $fid => $range_conditions) { foreach ($range_conditions as $k => $range_condition) { $k = $fid . "_" . $k; $join .= db_quote(" LEFT JOIN ?:product_features_values as var_val_{$k} ON var_val_{$k}.product_id = products.product_id AND var_val_{$k}.lang_code = ?s", $lang_code); $where_conditions[] = db_quote("(var_val_{$k}.value_int >= ?i AND var_val_{$k}.value_int <= ?i AND var_val_{$k}.value = '' AND var_val_{$k}.feature_id = ?i)", $range_condition['from'], $range_condition['to'], $range_condition['feature_id']); } $condition .= db_quote(" AND (?p)", implode(" OR ", $where_conditions)); $where_conditions = array(); } } // Field ranges //$fields_ids = empty($params['fields_ids']) ? $fields_ids : $params['fields_ids']; if (!empty($params['fields_ids'])) { foreach ($fields_ids as $rid => $field_type) { if (!empty($filter_fields[$field_type])) { $structure = $filter_fields[$field_type]; if ($structure['condition_type'] == 'D' && empty($structure['slider'])) { $range_condition = db_get_row("SELECT `from`, `to`, range_id FROM ?:product_filter_ranges WHERE range_id = ?i", $rid); if (!empty($range_condition)) { $params["{$structure['db_field']}_from"] = $range_condition['from']; $params["{$structure['db_field']}_to"] = $range_condition['to']; } } elseif ($structure['condition_type'] == 'F') { $params['filter_params'][$structure['db_field']][] = $rid; } elseif ($structure['condition_type'] == 'C') { $params['filter_params'][$structure['db_field']][] = $rid == 1 ? 'Y' : 'N'; } } } } elseif (!empty($fields_ids_revert)) { foreach ($fields_ids_revert as $field_type => $rids) { if (!empty($filter_fields[$field_type])) { $structure = $filter_fields[$field_type]; if ($structure['condition_type'] == 'D' && empty($structure['slider'])) { foreach ($rids as $rid) { $range_condition = db_get_row("SELECT `from`, `to`, range_id FROM ?:product_filter_ranges WHERE range_id = ?i", $rid); if (!empty($range_condition)) { $params["{$structure['db_field']}_from"] = $range_condition['from']; $params["{$structure['db_field']}_to"] = $range_condition['to']; } } } elseif ($structure['condition_type'] == 'F') { $params['filter_params'][$structure['db_field']] = $rids; } elseif ($structure['condition_type'] == 'C') { if (count($rids) > 1) { foreach ($rids as $rid) { if ($fields_ids[$rid] == $field_type) { unset($fields_ids[$rid]); } $params['features_hash'] = fn_delete_range_from_url($params['features_hash'], array('range_id' => $rid), $field_type); } } else { $params['filter_params'][$structure['db_field']][] = $rids[0] == 1 ? 'Y' : 'N'; } } } } } // Slider ranges $slider_vals = empty($params['slider_vals']) ? $slider_vals : $params['slider_vals']; if (!empty($slider_vals)) { foreach ($slider_vals as $field_type => $vals) { if (!empty($filter_fields[$field_type])) { if ($field_type == 'P') { $currency = !empty($vals[2]) ? $vals[2] : CART_PRIMARY_CURRENCY; if ($currency != CART_PRIMARY_CURRENCY) { $coef = Registry::get('currencies.' . $currency . '.coefficient'); $decimals = Registry::get('currencies.' . CART_PRIMARY_CURRENCY . '.decimals'); $vals[0] = round(floatval($vals[0]) * floatval($coef), $decimals); $vals[1] = round(floatval($vals[1]) * floatval($coef), $decimals); } } $structure = $filter_fields[$field_type]; $params["{$structure['db_field']}_from"] = $vals[0]; $params["{$structure['db_field']}_to"] = $vals[1]; } } } // Checkbox features if (!empty($params['ch_filters']) && !fn_is_empty($params['ch_filters'])) { foreach ($params['ch_filters'] as $k => $v) { // Product field filter if (is_string($k) == true && !empty($v) && ($structure = $filter_fields[$k])) { $condition .= db_quote(" AND {$structure['table']}.{$structure['db_field']} IN (?a)", $v == 'A' ? array('Y', 'N') : $v); // Feature filter } elseif (!empty($v)) { $fid = intval($k); $join .= db_quote(" LEFT JOIN ?:product_features_values as ch_features_{$fid} ON ch_features_{$fid}.product_id = products.product_id AND ch_features_{$fid}.lang_code = ?s", $lang_code); $condition .= db_quote(" AND ch_features_{$fid}.feature_id = ?i AND ch_features_{$fid}.value IN (?a)", $fid, $v == 'A' ? array('Y', 'N') : $v); } } } // Text features if (!empty($params['tx_features'])) { foreach ($params['tx_features'] as $k => $v) { if (fn_string_not_empty($v)) { $fid = intval($k); $join .= " LEFT JOIN ?:product_features_values as tx_features_{$fid} ON tx_features_{$fid}.product_id = products.product_id"; $condition .= db_quote(" AND tx_features_{$fid}.value LIKE ?l AND tx_features_{$fid}.lang_code = ?s", "%" . trim($v) . "%", $lang_code); } } } $total = 0; fn_set_hook('get_products_before_select', $params, $join, $condition, $u_condition, $inventory_condition, $sortings, $total, $items_per_page, $lang_code, $having); // // [/Advanced filters] // $feature_search_condition = ''; if (!empty($params['feature'])) { // Extended search by product fields $_cond = array(); $total_hits = 0; foreach ($params['feature'] as $f_id) { if (!empty($f_val)) { $total_hits++; $_cond[] = db_quote("(?:product_features_values.feature_id = ?i)", $f_id); } } $params['extend'][] = 'categories'; if (!empty($_cond)) { $cache_feature_search = db_get_fields("SELECT product_id, COUNT(product_id) as cnt FROM ?:product_features_values WHERE (" . implode(' OR ', $_cond) . ") GROUP BY product_id HAVING cnt = {$total_hits}"); $feature_search_condition .= db_quote(" AND products_categories.product_id IN (?n)", $cache_feature_search); } } // Category search condition for SQL query if (!empty($params['cid'])) { $cids = is_array($params['cid']) ? $params['cid'] : explode(',', $params['cid']); if (!empty($params['subcats']) && $params['subcats'] == 'Y') { $_ids = db_get_fields("SELECT a.category_id FROM ?:categories as a LEFT JOIN ?:categories as b ON b.category_id IN (?n) WHERE a.id_path LIKE CONCAT(b.id_path, '/%')", $cids); $cids = fn_array_merge($cids, $_ids, false); } $params['extend'][] = 'categories'; $condition .= db_quote(" AND ?:categories.category_id IN (?n)", $cids); } // If we need to get the products by IDs and no IDs passed, don't search anything if (!empty($params['force_get_by_ids']) && empty($params['pid']) && empty($params['product_id'])) { return array(array(), $params, 0); } // Product ID search condition for SQL query if (!empty($params['pid'])) { $u_condition .= db_quote($union_condition . ' products.product_id IN (?n)', $params['pid']); } // Exclude products from search results if (!empty($params['exclude_pid'])) { $condition .= db_quote(' AND products.product_id NOT IN (?n)', $params['exclude_pid']); } // Search by feature comparison flag if (!empty($params['feature_comparison'])) { $condition .= db_quote(' AND products.feature_comparison = ?s', $params['feature_comparison']); } // Search products by localization $condition .= fn_get_localizations_condition('products.localization', true); $company_condition = ''; if (fn_allowed_for('MULTIVENDOR')) { if ($params['area'] == 'C') { $company_condition .= " AND companies.status = 'A' "; $params['extend'][] = 'companies'; } else { $company_condition .= fn_get_company_condition('products.company_id'); } } else { $cat_company_condition = ''; if (Registry::get('runtime.company_id')) { $params['extend'][] = 'categories'; $cat_company_condition .= fn_get_company_condition('?:categories.company_id'); } elseif (!empty($params['company_ids'])) { $params['extend'][] = 'categories'; $cat_company_condition .= db_quote(' AND ?:categories.company_id IN (?a)', explode(',', $params['company_ids'])); } $company_condition .= $cat_company_condition; } $condition .= $company_condition; if (!fn_allowed_for('ULTIMATE') && Registry::get('runtime.company_id') && isset($params['company_id'])) { $params['company_id'] = Registry::get('runtime.company_id'); } if (isset($params['company_id']) && $params['company_id'] != '') { $condition .= db_quote(' AND products.company_id = ?i ', $params['company_id']); } if (!empty($params['filter_params'])) { foreach ($params['filter_params'] as $field => $f_vals) { $condition .= db_quote(' AND products.' . $field . ' IN (?a) ', $f_vals); } } if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) { $having['price_from'] = db_quote(' price >= ?d', fn_convert_price(trim($params['price_from']))); $extraCondition["price_from"] = db_quote(' price >= ?d', fn_convert_price(trim($params['price_from']))); //$condition .= db_quote(' AND prices.price >= ?d', fn_convert_price(trim($params['price_from']))); $params['extend'][] = 'prices2'; } if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) { $having['price_to'] = db_quote(' price <= ?d', fn_convert_price(trim($params['price_to']))); $extraCondition["price_to"] = db_quote(' price <= ?d', fn_convert_price(trim($params['price_to']))); //$condition .= db_quote(' AND prices.price <= ?d', fn_convert_price(trim($params['price_to']))); $params['extend'][] = 'prices2'; } if (isset($params['weight_from']) && fn_is_numeric($params['weight_from'])) { $condition .= db_quote(' AND products.weight >= ?d', fn_convert_weight(trim($params['weight_from']))); } if (isset($params['weight_to']) && fn_is_numeric($params['weight_to'])) { $condition .= db_quote(' AND products.weight <= ?d', fn_convert_weight(trim($params['weight_to']))); } // search specific inventory status if (!empty($params['tracking'])) { $condition .= db_quote(' AND products.tracking IN(?a)', $params['tracking']); } if (isset($params['amount_from']) && fn_is_numeric($params['amount_from'])) { $condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount >= ?i, products.amount >= ?i)", $params['amount_from'], $params['amount_from']); $inventory_condition .= db_quote(' AND inventory.amount >= ?i', $params['amount_from']); } if (isset($params['amount_to']) && fn_is_numeric($params['amount_to'])) { $condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount <= ?i, products.amount <= ?i)", $params['amount_to'], $params['amount_to']); $inventory_condition .= db_quote(' AND inventory.amount <= ?i', $params['amount_to']); } if (Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && $params['area'] == 'C') { // FIXME? Registry in model $condition .= " AND IF(products.tracking = 'O', inventory.amount > 0, products.amount > 0)"; } if (!empty($params['status'])) { $condition .= db_quote(' AND products.status IN (?a)', $params['status']); } if (!empty($params['shipping_freight_from'])) { $condition .= db_quote(' AND products.shipping_freight >= ?d', $params['shipping_freight_from']); } if (!empty($params['shipping_freight_to'])) { $condition .= db_quote(' AND products.shipping_freight <= ?d', $params['shipping_freight_to']); } if (!empty($params['free_shipping'])) { $condition .= db_quote(' AND products.free_shipping = ?s', $params['free_shipping']); } if (!empty($params['downloadable'])) { $condition .= db_quote(' AND products.is_edp = ?s', $params['downloadable']); } if (isset($params['pcode']) && fn_string_not_empty($params['pcode'])) { $pcode = trim($params['pcode']); $fields['combination'] = 'inventory.combination'; $u_condition .= db_quote(" {$union_condition} (inventory.product_code LIKE ?l OR products.product_code LIKE ?l)", "%{$pcode}%", "%{$pcode}%"); $inventory_condition .= db_quote(" AND inventory.product_code LIKE ?l", "%{$pcode}%"); } if (isset($params['amount_to']) && fn_is_numeric($params['amount_to']) || isset($params['amount_from']) && fn_is_numeric($params['amount_from']) || !empty($params['pcode']) || Registry::get('settings.General.inventory_tracking') == 'Y' && Registry::get('settings.General.show_out_of_stock_products') == 'N' && $params['area'] == 'C') { $join .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = products.product_id {$inventory_condition}"; } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (products.timestamp >= ?i AND products.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['item_ids'])) { $condition .= db_quote(" AND products.product_id IN (?n)", explode(',', $params['item_ids'])); } if (isset($params['popularity_from']) && fn_is_numeric($params['popularity_from'])) { $params['extend'][] = 'popularity'; $condition .= db_quote(' AND popularity.total >= ?i', $params['popularity_from']); } if (isset($params['popularity_to']) && fn_is_numeric($params['popularity_to'])) { $params['extend'][] = 'popularity'; $condition .= db_quote(' AND popularity.total <= ?i', $params['popularity_to']); } if (!empty($params['order_ids'])) { $arr = strpos($params['order_ids'], ',') !== false || !is_array($params['order_ids']) ? explode(',', $params['order_ids']) : $params['order_ids']; $condition .= db_quote(" AND ?:order_details.order_id IN (?n)", $arr); $join .= " LEFT JOIN ?:order_details ON ?:order_details.product_id = products.product_id"; } $limit = ''; $group_by = 'products.product_id'; // Show enabled products $_p_statuses = array('A'); if ($params['p_status']) { $_p_statuses = array('A', 'H'); } $condition .= $params['area'] == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], 'products.usergroup_ids', true) . ')' . db_quote(' AND products.status IN (?a)', $_p_statuses) : ''; // -- JOINS -- if (in_array('product_name', $params['extend'])) { $fields['product'] = 'descr1.product as product'; $join .= db_quote(" LEFT JOIN ?:product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ?s ", $lang_code); } // get prices $price_condition = ''; if (in_array('prices', $params['extend'])) { // //$fields['price'] = 'MIN(IF(prices.percentage_discount = 0, prices.price, prices.price - (prices.price * prices.percentage_discount)/100)) as price'; $join .= " LEFT JOIN ?:product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1"; $price_condition = db_quote(' AND prices.usergroup_id IN (?n)', $params['area'] == 'A' ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids'])); $condition .= $price_condition; //$price_usergroup_cond_2 = db_quote(' AND usergroup_id IN (?n)', (($params['area'] == 'A') ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids']))); $extraConditionString = ""; if (count($extraCondition) > 0) { $extraConditionString = "WHERE " . implode(" AND ", $extraCondition); } $fields['price'] = 'case when p2.min_price > 0 then p2.min_price else p3.price2 end'; $join .= " LEFT JOIN (SELECT product_id, MIN(price) as min_price FROM ?:product_options_inventory_prices " . $extraConditionString . " GROUP BY product_id) AS p2 ON p2.product_id = products.product_id\n LEFT JOIN (SELECT product_id, MIN(IF(percentage_discount = 0, price, price - (price * percentage_discount)/100)) as price2 FROM ?:product_prices WHERE lower_limit = 1 GROUP by product_id) AS p3 ON p3.product_id=products.product_id "; } /* // get prices for search by price if (in_array('prices2', $params['extend'])) { $price_usergroup_cond_2 = db_quote(' AND prices_2.usergroup_id IN (?n)', (($params['area'] == 'A') ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids']))); $join .= " LEFT JOIN ?:product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price " . $price_usergroup_cond_2; $condition .= ' AND prices_2.price IS NULL'; $price_condition .= ' AND prices_2.price IS NULL'; } */ // if (in_array('prices2', $params['extend'])) { // $price_usergroup_cond_2 = db_quote(' AND usergroup_id IN (?n)', (($params['area'] == 'A') ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids']))); // $fields['price'] = 'case // when p2.min_price > 0 then p2.min_price // else p3.price2 // end'; // $join .= " LEFT JOIN (SELECT product_id, MIN(price) as min_price FROM ?:product_options_inventory_prices GROUP BY product_id) AS p2 ON p2.product_id = products.product_id // LEFT JOIN (SELECT product_id, MIN(IF(percentage_discount = 0, price, price - (price * percentage_discount)/100)) as price2 FROM ?:product_prices WHERE lower_limit = 1 ".$price_usergroup_cond_2." GROUP by product_id) AS p3 ON p3.product_id=products.product_id "; // } // get short & full description if (in_array('search_words', $params['extend'])) { $fields['search_words'] = 'descr1.search_words'; } // get short & full description if (in_array('description', $params['extend'])) { $fields['short_description'] = 'descr1.short_description'; if (in_array('full_description', $params['extend'])) { $fields['full_description'] = "descr1.full_description"; } else { $fields['full_description'] = "IF(descr1.short_description = '', descr1.full_description, '') as full_description"; } } // get companies $companies_join = db_quote(" LEFT JOIN ?:companies AS companies ON companies.company_id = products.company_id "); if (in_array('companies', $params['extend'])) { $fields['company_name'] = 'companies.company as company_name'; $join .= $companies_join; } // for compatibility if (in_array('category_ids', $params['extend'])) { $params['extend'][] = 'categories'; } // get categories $_c_statuses = array('A', 'H'); // Show enabled categories $skip_checking_usergroup_permissions = fn_is_preview_action($auth, $params); if ($skip_checking_usergroup_permissions) { $category_avail_cond = ''; } else { $category_avail_cond = $params['area'] == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], '?:categories.usergroup_ids', true) . ')' : ''; } $category_avail_cond .= $params['area'] == 'C' ? db_quote(" AND ?:categories.status IN (?a) ", $_c_statuses) : ''; $categories_join = " INNER JOIN ?:products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN ?:categories ON ?:categories.category_id = products_categories.category_id {$category_avail_cond} {$feature_search_condition}"; if (!empty($params['order_ids'])) { // Avoid duplicating by sub-categories $condition .= db_quote(' AND products_categories.link_type = ?s', 'M'); } if (in_array('categories', $params['extend'])) { $fields['category_ids'] = "GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids"; $fields['categories_position'] = 'products_categories.position'; $join .= $categories_join; $condition .= fn_get_localizations_condition('?:categories.localization', true); } // get popularity $popularity_join = db_quote(" LEFT JOIN ?:product_popularity as popularity ON popularity.product_id = products.product_id"); if (in_array('popularity', $params['extend'])) { $fields['popularity'] = 'popularity.total as popularity'; $join .= $popularity_join; } if (!empty($params['get_subscribers'])) { $join .= " LEFT JOIN ?:product_subscriptions as product_subscriptions ON product_subscriptions.product_id = products.product_id"; } // -- \JOINs -- if (!empty($u_condition)) { $condition .= " {$union_condition} ((" . ($union_condition == ' OR ' ? '0 ' : '1 ') . $u_condition . ')' . $company_condition . $price_condition . ')'; } /** * Changes additional params for selecting products * * @param array $params Product search params * @param array $fields List of fields for retrieving * @param array $sortings Sorting fields * @param string $condition String containing SQL-query condition possibly prepended with a logical operator (AND or OR) * @param string $join String with the complete JOIN information (JOIN type, tables and fields) for an SQL-query * @param string $sorting String containing the SQL-query ORDER BY clause * @param string $group_by String containing the SQL-query GROUP BY field * @param string $lang_code Two-letter language code (e.g. 'en', 'ru', etc.) * @param array $having HAVING condition */ fn_set_hook('get_products', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $lang_code, $having); // -- SORTINGS -- if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params = array_merge($params, fn_get_default_products_sorting()); if (empty($sortings[$params['sort_by']])) { $_products_sortings = fn_get_products_sorting(); $params['sort_by'] = key($_products_sortings); } } $default_sorting = fn_get_products_sorting(); if ($params['sort_by'] == 'popularity' && !in_array('popularity', $params['extend'])) { $join .= $popularity_join; } if ($params['sort_by'] == 'company' && !in_array('companies', $params['extend'])) { $join .= $companies_join; } if (empty($params['sort_order'])) { if (!empty($default_sorting[$params['sort_by']]['default_order'])) { $params['sort_order'] = $default_sorting[$params['sort_by']]['default_order']; } else { $params['sort_order'] = 'asc'; } } $sorting = db_sort($params, $sortings); if (fn_allowed_for('ULTIMATE')) { if (in_array('sharing', $params['extend'])) { $fields['is_shared_product'] = "IF(COUNT(IF(?:categories.company_id = products.company_id, NULL, ?:categories.company_id)), 'Y', 'N') as is_shared_product"; if (strpos($join, $categories_join) === false) { $join .= $categories_join; } } } // -- \SORTINGS -- // Used for View cascading if (!empty($params['get_query'])) { return "SELECT products.product_id FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY products.product_id"; } // Used for Extended search if (!empty($params['get_conditions'])) { return array($fields, $join, $condition); } if (!empty($params['limit'])) { $limit = db_quote(" LIMIT 0, ?i", $params['limit']); } elseif (!empty($params['items_per_page'])) { $limit = db_paginate($params['page'], $params['items_per_page']); } $calc_found_rows = ''; if (empty($total)) { $calc_found_rows = 'SQL_CALC_FOUND_ROWS'; } if (!empty($having)) { $having = ' HAVING ' . implode(' AND ', $having); } else { $having = ''; } $products = db_get_array("SELECT {$calc_found_rows} " . implode(', ', $fields) . " FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY {$group_by} {$having} {$sorting} {$limit}"); if (!empty($params['items_per_page'])) { $params['total_items'] = !empty($total) ? $total : db_get_found_rows(); } else { $params['total_items'] = count($products); } // Post processing if (in_array('categories', $params['extend'])) { foreach ($products as $k => $v) { list($products[$k]['category_ids'], $products[$k]['main_category']) = fn_convert_categories($v['category_ids']); } } if (!empty($params['get_frontend_urls'])) { foreach ($products as &$product) { $product['url'] = fn_url('products.view?product_id=' . $product['product_id'], 'C'); } } if (!empty($params['item_ids'])) { $products = fn_sort_by_ids($products, explode(',', $params['item_ids'])); } if (!empty($params['pid']) && !empty($params['apply_limit']) && $params['apply_limit']) { $products = fn_sort_by_ids($products, $params['pid']); } foreach ($products as $keyProduct => $productItem) { $checkInventoryPrices = array(); $inventory_product = array(); $checkInventoryPrices = db_get_row("SELECT b.price FROM ?:products as a JOIN ?:product_options_inventory_prices as b ON a.product_id=b.product_id WHERE a.product_id = ?i ", $productItem['product_id']); if ($checkInventoryPrices && count($checkInventoryPrices) > 0) { $conditionExt = ""; if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) { $conditionExt .= db_quote(' AND b.price >= ?d', fn_convert_price(trim($params['price_from']))); } if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) { $conditionExt .= db_quote(' AND b.price <= ?d', fn_convert_price(trim($params['price_to']))); } $inventory_product = db_get_row("SELECT MIN(b.price ) AS min_price, MAX(b.price ) AS max_price FROM ?:products as a JOIN ?:product_options_inventory_prices as b ON a.product_id=b.product_id WHERE a.product_id = ?i " . $conditionExt . " GROUP BY a.product_id", $productItem['product_id']); if (!$inventory_product || count($inventory_product) < 1) { unset($products[$keyProduct]); } elseif ($inventory_product && count($inventory_product) > 0) { $products[$keyProduct]['price_range'] = $inventory_product; } } //fn_promotion_apply('catalog', $productItem, $auth); } /** * Changes selected products * * @param array $products Array of products * @param array $params Product search params * @param string $lang_code Language code */ fn_set_hook('get_products_post', $products, $params, $lang_code); LastView::instance()->processResults('products', $products, $params); return array($products, $params); }
function fn_calculate_shipping_rates(&$cart, &$cart_products, $auth, $calculate_selected = false) { $shipping_rates = array(); $condition = ''; if ($calculate_selected == true) { $shipping_ids = !empty($cart['shipping']) ? array_keys($cart['shipping']) : array(); if (!empty($shipping_ids)) { $condition = db_quote(" AND a.shipping_id IN (?n)", $shipping_ids); } else { return array(); } } $condition .= fn_get_localizations_condition('a.localization'); $location = fn_get_customer_location($auth, $cart); $destination_id = fn_get_available_destination($location); $package_infos = fn_prepare_package_info($cart, $cart_products); foreach ($package_infos as $o_id => $package_info) { $c = fn_get_company_condition('a.company_id', false, $o_id, false, true); $companies = Registry::get('s_companies'); if (!empty($companies) && !empty($companies[$o_id]['shippings'])) { if (trim($c)) { $c = "{$c} OR "; } $c .= db_quote('a.shipping_id IN (?n)', explode(',', $companies[$o_id]['shippings'])); $c = "({$c})"; } if (trim($c)) { $c = " AND {$c}"; } //TODO select companies shippings fn_set_hook('calculate_shipping_rates', $c, $o_id); if (AREA == 'C') { $condition .= " AND (" . fn_find_array_in_set($auth['usergroup_ids'], 'a.usergroup_ids', true) . ")"; } $shipping_methods = db_get_hash_array("SELECT a.shipping_id, a.rate_calculation, a.service_id, a.self_service, b.shipping as name, b.delivery_time FROM ?:shippings as a LEFT JOIN ?:shipping_descriptions as b ON a.shipping_id = b.shipping_id AND b.lang_code = ?s WHERE (a.min_weight <= ?d AND (a.max_weight >= ?d OR a.max_weight = 0.00)) AND a.status = 'A' ?p ?p ORDER BY a.position", 'shipping_id', CART_LANGUAGE, $package_info['W'], $package_info['W'], $condition, $c); if (empty($shipping_methods)) { continue; } $found_rates = array(); foreach ($shipping_methods as $method) { // Manual rate calculation if ($method['rate_calculation'] == 'M') { if ($destination_id !== false) { $rate_data = db_get_row("SELECT rate_id, rate_value FROM ?:shipping_rates WHERE shipping_id = ?i AND destination_id = ?i", $method['shipping_id'], $destination_id); if (!empty($rate_data)) { $found_rates[$method['shipping_id']] = fn_calculate_shipping_rate($package_info, $rate_data['rate_value']); } } // Realtime rate calculation } else { $charge = db_get_field("SELECT rate_value FROM ?:shipping_rates WHERE shipping_id = ?i AND destination_id = 0", $method['shipping_id']); $rate_data = fn_calculate_realtime_shipping_rate($method['service_id'], $location, $package_info, $auth); if ($rate_data !== false) { $found_rates[$method['shipping_id']] = $rate_data['cost']; $found_rates[$method['shipping_id']] += fn_calculate_shipping_rate($package_info, $charge); } } } $shipping_freight = 0; foreach ($cart_products as $v) { if (($v['is_edp'] != 'Y' || $v['is_edp'] == 'Y' && $v['edp_shipping'] == 'Y') && $v['free_shipping'] != 'Y') { $shipping_freight += $v['shipping_freight'] * $v['amount']; } } foreach ($shipping_methods as $method) { $shipping_rates[$method['shipping_id']]['name'] = $method['name']; $shipping_rates[$method['shipping_id']]['delivery_time'] = $method['delivery_time']; $shipping_rates[$method['shipping_id']]['rates'] = array(); $shipping_rates[$method['shipping_id']]['self_service'] = $method['self_service']; } foreach ($found_rates as $shipping_id => $rate_value) { /*if (!isset($shipping_rates[$shipping_id])) { $shipping_rates[$shipping_id]['name'] = $shipping_methods[$shipping_id]['name']; $shipping_rates[$shipping_id]['delivery_time'] = $shipping_methods[$shipping_id]['delivery_time']; }*/ $shipping_rates[$shipping_id]['rates'][$o_id] = $rate_value + $shipping_freight; } } return $shipping_rates; }
function fn_get_news($params, $lang_code = CART_LANGUAGE) { $fields = array('?:news.*', 'descr.news', 'descr.description'); // Define sort fields $sortings = array('position' => '?:news.position', 'name' => '?:news_descriptions.news', 'date' => '?:news.date'); $directions = array('asc' => 'asc', 'desc' => 'desc'); $limit = $condition = $sorting = ''; $join = db_quote(" LEFT JOIN ?:news_descriptions as descr ON descr.news_id = ?:news.news_id AND descr.lang_code = ?s", $lang_code); $condition .= AREA == 'A' ? '1 ' : " ?:news.status = 'A'"; $condition .= fn_get_localizations_condition('?:news.localization'); if (!empty($params['b_id'])) { $join .= " LEFT JOIN ?:block_links ON ?:block_links.object_id = ?:news.news_id AND ?:block_links.location = 'news'"; $condition .= db_quote(' AND ?:block_links.block_id = ?i', $params['b_id']); } if (isset($params['q']) && fn_string_no_empty($params['q'])) { $params['q'] = trim($params['q']); if ($params['match'] == 'any') { $pieces = fn_explode(' ', $params['q']); $search_type = ' OR '; } elseif ($params['match'] == 'all') { $pieces = fn_explode(' ', $params['q']); $search_type = ' AND '; } else { $pieces = array($params['q']); $search_type = ''; } $_condition = array(); foreach ($pieces as $piece) { if (strlen($piece) == 0) { continue; } $tmp = array(); $tmp[] = db_quote("descr.news LIKE ?l", "%{$piece}%"); $tmp[] = db_quote("descr.description LIKE ?l", "%{$piece}%"); $_condition[] = '(' . join(' OR ', $tmp) . ')'; } $_cond = implode($search_type, $_condition); if (!empty($_condition)) { $condition .= ' AND (' . $_cond . ') '; } } if (!empty($params['limit'])) { $limit = db_quote(" LIMIT 0, ?i", $params['limit']); } if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { $params['sort_order'] = 'desc'; } if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = 'date'; } $sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']]; if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:news.date >= ?i AND ?:news.date <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['item_ids'])) { $condition .= db_quote(' AND ?:news.news_id IN (?n)', explode(',', $params['item_ids'])); } // Used for Extended search if (!empty($params['get_conditions'])) { return array($fields, $join, $condition); } if (!empty($params['paginate'])) { $params['page'] = empty($params['page']) ? 1 : $params['page']; $total = db_get_field("SELECT COUNT(?:news.news_id) FROM ?:news ?p WHERE ?p", $join, $condition); $limit = fn_paginate($params['page'], $total, AREA == 'A' ? Registry::get('settings.Appearance.admin_elements_per_page') : Registry::get('settings.Appearance.elements_per_page')); } fn_set_hook('get_news', $params, $fields, $join, $condition, $sorting, $limit, $lang_code); $fields = join(', ', $fields); $news = db_get_array("SELECT ?p FROM ?:news ?p WHERE ?p ORDER BY ?p ?p", $fields, $join, $condition, $sorting, $limit); fn_set_hook('get_news_post', $news); return array($news, $params); }
function fn_get_pages($params = array(), $items_per_page = 0, $lang_code = CART_LANGUAGE) { // Init filter $params = fn_init_view('pages', $params); $default_params = array('page_id' => 0, 'page' => 1, 'visible' => false, 'get_tree' => '', 'items_per_page' => 0, 'pdescr' => '', 'subpages' => ''); $params = array_merge($default_params, $params); if (empty($params['pname']) && empty($params['pdescr']) && empty($params['subpages'])) { $params['pname'] = 'Y'; } $fields = array('?:pages.*', '?:page_descriptions.*'); // Define sort fields $sortings = array('position' => array('?:pages.position', '?:page_descriptions.page'), 'name' => '?:page_descriptions.page', 'timestamp' => '?:pages.timestamp', 'type' => '?:pages.page_type', 'multi_level' => array('?:pages.parent_id', '?:pages.position', '?:page_descriptions.page')); $directions = array('asc' => 'asc', 'desc' => 'desc'); $auth =& $_SESSION['auth']; $condition = '1'; $join = $limit = $group_by = ''; if (isset($params['q']) && fn_string_no_empty($params['q'])) { $params['q'] = trim($params['q']); if ($params['match'] == 'any') { $pieces = fn_explode(' ', $params['q']); $search_type = ' OR '; } elseif ($params['match'] == 'all') { $pieces = fn_explode(' ', $params['q']); $search_type = ' AND '; } else { $pieces = array($params['q']); $search_type = ''; } $_condition = array(); foreach ($pieces as $piece) { if (strlen($piece) == 0) { continue; } $tmp = array(); if (!empty($params['pname']) && $params['pname'] == 'Y') { $tmp[] = db_quote("(?:page_descriptions.page LIKE ?l)", "%{$piece}%"); // check search words } if ($params['pdescr'] == 'Y') { $tmp[] = db_quote("?:page_descriptions.description LIKE ?l", "%{$piece}%"); } if (!empty($tmp)) { $_condition[] = '(' . implode(' OR ', $tmp) . ')'; } } if (!empty($_condition)) { $condition .= ' AND ' . implode($search_type, $_condition); } } $condition .= fn_get_company_condition('?:pages.company_id'); if (!empty($params['page_type'])) { $condition .= db_quote(" AND ?:pages.page_type = ?s", $params['page_type']); } if (isset($params['parent_id']) && $params['parent_id'] !== '') { $p_ids = array(); if ($params['subpages'] == 'Y') { $p_ids = db_get_fields("SELECT a.page_id FROM ?:pages as a LEFT JOIN ?:pages as b ON b.page_id = ?i WHERE a.id_path LIKE CONCAT(b.id_path, '/%')", $params['parent_id']); } $p_ids[] = $params['parent_id']; $condition .= db_quote(" AND ?:pages.parent_id IN (?n)", $p_ids); } if (!empty($params['from_page_id'])) { $from_id_path = db_get_field("SELECT id_path FROM ?:pages WHERE page_id = ?i", $params['from_page_id']); $condition .= db_quote(" AND ?:pages.id_path LIKE ?l", "{$from_id_path}/%"); } if (!empty($params['status'])) { $condition .= db_quote(" AND ?:pages.status IN (?a)", $params['status']); } if (!empty($params['vendor_pages']) && empty($params['company_id'])) { return array(array(), $params); } elseif (!empty($params['company_id'])) { $condition .= db_quote(" AND ?:pages.company_id = ?i", $params['company_id']); } if (!empty($params['visible'])) { // for pages tree: show visible branch only if (!empty($params['current_page_id'])) { $cur_id_path = db_get_field("SELECT id_path FROM ?:pages WHERE page_id = ?i", $params['current_page_id']); if (!empty($cur_id_path)) { $page_ids = explode('/', $cur_id_path); } } $page_ids[] = $params['page_id']; $condition .= db_quote(" AND ?:pages.parent_id IN (?n)", $page_ids); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:pages.timestamp >= ?i AND ?:pages.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['item_ids'])) { // get only defined pages $condition .= db_quote(" AND ?:pages.page_id IN (?n)", explode(',', $params['item_ids'])); } if (!empty($params['except_id']) && (empty($params['item_ids']) || !empty($params['item_ids']) && !in_array($params['except_id'], explode(',', $params['item_ids'])))) { $condition .= db_quote(' AND ?:pages.page_id != ?i AND ?:pages.parent_id != ?i', $params['except_id'], $params['except_id']); } if (AREA != 'A') { $condition .= " AND (" . fn_find_array_in_set($auth['usergroup_ids'], '?:pages.usergroup_ids', true) . ")"; $condition .= fn_get_localizations_condition('?:pages.localization', true); $condition .= db_quote(" AND (use_avail_period = ?s OR (use_avail_period = ?s AND avail_from_timestamp >= ?i AND avail_till_timestamp <= ?i))", 'N', 'Y', TIME, TIME); } $join = db_quote('LEFT JOIN ?:page_descriptions ON ?:pages.page_id = ?:page_descriptions.page_id AND ?:page_descriptions.lang_code = ?s', $lang_code); if (!empty($params['b_id'])) { $join .= " LEFT JOIN ?:block_links ON ?:block_links.object_id = ?:pages.page_id AND ?:block_links.location = 'pages'"; $condition .= db_quote(' AND ?:block_links.block_id = ?i', $params['b_id']); } if (!empty($params['limit'])) { $limit = db_quote(" LIMIT 0, ?i", $params['limit']); } fn_set_hook('get_pages', $params, $join, $condition, $fields, $group_by, $sortings); if (!empty($params['get_tree'])) { $params['sort_by'] = 'multi_level'; } if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { $params['sort_order'] = 'asc'; } if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = 'position'; } $sorting = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']]; if (!empty($group_by)) { $group_by = ' GROUP BY ' . $group_by; } // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; // Get search conditions if (!empty($params['get_conditions'])) { return array($fields, $join, $condition); } $total = 0; if (!empty($items_per_page) && !empty($params['paginate'])) { $total = db_get_field("SELECT COUNT(DISTINCT(?:pages.page_id)) FROM ?:pages ?p WHERE ?p ?p ORDER BY ?p", $join, $condition, $group_by, $sorting); $limit = fn_paginate($params['page'], $total, $items_per_page); } $pages = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:pages ?p WHERE ?p ?p ORDER BY ?p ?p", 'page_id', $join, $condition, $group_by, $sorting, $limit); if (!empty($pages)) { foreach ($pages as $k => $v) { $pages[$k]['level'] = substr_count($v['id_path'], '/'); } if (!empty($params['get_tree'])) { $delete_keys = array(); foreach ($pages as $k => $v) { if (!empty($v['parent_id']) && !empty($pages[$v['parent_id']])) { $pages[$v['parent_id']]['subpages'][$v['page_id']] =& $pages[$k]; $delete_keys[] = $k; } if (!empty($v['parent_id']) && (!isset($params['root_id']) && empty($pages[$v['parent_id']]) || isset($params['root_id']) && $v['parent_id'] != $params['root_id']) && (empty($params['from_page_id']) || $params['from_page_id'] != $v['parent_id'])) { // delete pages that don't have parent. FIXME: should be done on database layer $delete_keys[] = $k; } } foreach ($delete_keys as $k) { unset($pages[$k]); } } elseif (!empty($params['item_ids'])) { $pages = fn_sort_by_ids($pages, explode(',', $params['item_ids']), 'page_id'); } if ($params['get_tree'] == 'plain') { $pages = fn_multi_level_to_plain($pages, 'subpages'); } if (!empty($params['get_children_count'])) { $where_condition = !empty($params['except_id']) ? db_quote(' AND page_id != ?i', $params['except_id']) : ''; if ($params['get_tree'] == 'plain') { $_page_ids = array(); foreach ($pages as $_p) { $_page_ids[] = $_p['page_id']; } } else { $_page_ids = array_keys($pages); } $children = db_get_hash_single_array("SELECT parent_id, COUNT(page_id) as children FROM ?:pages WHERE parent_id IN (?n) ?p GROUP BY parent_id", array('parent_id', 'children'), $_page_ids, $where_condition); if (!empty($children)) { if ($params['get_tree'] == 'plain') { foreach ($pages as $_id => $_p) { if (!empty($children[$_p['page_id']])) { $pages[$_id]['has_children'] = true; } } } else { foreach ($children as $k => $v) { $pages[$k]['has_children'] = !empty($v); } } } } } if (!empty($params['add_root'])) { array_unshift($pages, array('page_id' => 0, 'page' => $params['add_root'])); } fn_set_hook('post_get_pages', $pages, $params, $lang_code); return array($pages, $params); }
/** * Lazily loads additional data related to products after they have been fetched from DB. * Used to ease main product loading SQL-query. * * @param array $products List of products * @param array $params Parameters passed to fn_get_products() * @param string $lang_code Language code passed to fn_get_products() * * @return array List of products with additional data merged into. */ function fn_load_products_extra_data($products, $params, $lang_code) { $products = fn_array_elements_to_keys($products, 'product_id'); $product_ids = array_keys($products); $extra_fields = array(); // Fields from "products" table $extra_fields['?:products'] = array('primary_key' => 'product_id', 'fields' => empty($params['only_short_fields']) ? array('*') : array('product_id', 'product_code', 'product_type', 'status', 'company_id', 'list_price', 'amount', 'weight', 'tracking', 'is_edp')); // Load prices lazily when they are needed and no sorting or filtering by price is applied if (in_array('prices', $params['extend']) && $params['sort_by'] != 'price' && !in_array('prices2', $params['extend'])) { $extra_fields['?:product_prices'] = array('primary_key' => 'product_id', 'fields' => array('price' => 'MIN(IF(' . '?:product_prices.percentage_discount = 0,' . '?:product_prices.price,' . '?:product_prices.price - (?:product_prices.price * ?:product_prices.percentage_discount)/100' . '))'), 'condition' => db_quote(' AND ?:product_prices.lower_limit = 1 AND ?:product_prices.usergroup_id IN (?n)', $params['area'] == 'A' ? USERGROUP_ALL : array_unique(array_merge(array(USERGROUP_ALL), $_SESSION['auth']['usergroup_ids']))), 'group_by' => ' GROUP BY ?:product_prices.product_id'); } // Descriptions $extra_fields['?:product_descriptions']['primary_key'] = 'product_id'; $extra_fields['?:product_descriptions']['condition'] = db_quote(" AND ?:product_descriptions.lang_code = ?s", $lang_code); if (in_array('search_words', $params['extend'])) { $extra_fields['?:product_descriptions']['fields'][] = 'search_words'; } if (in_array('description', $params['extend'])) { $extra_fields['?:product_descriptions']['fields'][] = 'short_description'; if (in_array('full_description', $params['extend'])) { $extra_fields['?:product_descriptions']['fields'][] = 'full_description'; } else { $extra_fields['?:product_descriptions']['fields']['full_description'] = "IF(?:product_descriptions.short_description = '', ?:product_descriptions.full_description, '')"; } } // Categories if (in_array('categories', $params['extend'])) { $categories_join = ' INNER JOIN ?:categories ON ?:categories.category_id = ?:products_categories.category_id'; if ($params['area'] == 'C') { if (!fn_is_preview_action($_SESSION['auth'], $params)) { $categories_join .= ' AND (' . fn_find_array_in_set($_SESSION['auth']['usergroup_ids'], '?:categories.usergroup_ids', true) . ')'; } $categories_join .= db_quote(' AND ?:categories.status IN (?a) ', array('A', 'H')); } $extra_fields['?:products_categories'] = array('primary_key' => 'product_id', 'fields' => array('category_ids' => 'GROUP_CONCAT(' . 'IF(?:products_categories.link_type = "M",' . ' CONCAT(?:products_categories.category_id, "M"),' . ' ?:products_categories.category_id)' . ')', 'position' => '?:products_categories.position'), 'condition' => fn_get_localizations_condition('?:categories.localization', true), 'join' => $categories_join, 'group_by' => ' GROUP BY ?:products_categories.product_id'); if (!empty($params['cid'])) { $extra_fields['?:products_categories']['group_by'] .= db_quote(' ,?:products_categories.category_id = ?i', $params['cid']); } } /** * Allows you to extend configuration of extra fields that should be lazily loaded for products. * * @see fn_load_extra_data_by_item_ids() * @param array $extra_fields * @param array $products List of products * @param array $product_ids List of product identifiers * @param array $params Parameters passed to fn_get_products() * @param string $lang_code Language code passed to fn_get_products() */ fn_set_hook('load_products_extra_data', $extra_fields, $products, $product_ids, $params, $lang_code); // Execute extra data loading SQL-queries and merge results into $products array fn_merge_extra_data_to_entity_list(fn_load_extra_data_by_entity_ids($extra_fields, $product_ids), $products); // Categories post-processing if (in_array('categories', $params['extend'])) { foreach ($products as $k => $v) { list($products[$k]['category_ids'], $products[$k]['main_category']) = fn_convert_categories($v['category_ids']); } } /** * Allows you lazily load extra data for products after they were fetched from DB or post-process lazy-loaded * additional data related to products. * * @param array $products List of products * @param array $product_ids List of product identifiers * @param array $params Parameters passed to fn_get_products() * @param string $lang_code Language code passed to fn_get_products() */ fn_set_hook('load_products_extra_data_post', $products, $product_ids, $params, $lang_code); return $products; }
function fn_get_available_destination($location) { $country = !empty($location['country']) ? $location['country'] : ''; $state = !empty($location['state']) ? $location['state'] : ''; $zipcode = !empty($location['zipcode']) ? $location['zipcode'] : ''; $city = !empty($location['city']) ? $location['city'] : ''; $address = !empty($location['address']) ? $location['address'] : ''; if (!empty($country)) { $state_id = fn_get_state_id($state, $country); $condition = ''; if (AREA == 'C') { $condition .= fn_get_localizations_condition('localization'); if (!empty($condition)) { $condition = db_quote('OR (1 ?p)', $condition); } } $__dests = db_get_array("SELECT a.* FROM ?:destination_elements as a LEFT JOIN ?:destinations as b ON b.destination_id = a.destination_id WHERE b.status = 'A' ?p", $condition); $destinations = array(); foreach ($__dests as $k => $v) { $destinations[$v['destination_id']][$v['element_type']][] = $v['element']; } $concur_destinations = array(); foreach ($destinations as $dest_id => $elm_types) { // Significance level. The more significance level means the most amount of coincidences $significance = 0; $dest_countries = !empty($elm_types['C']) ? $elm_types['C'] : array(); foreach ($elm_types as $elm_type => $elms) { // Check country if ($elm_type == 'C') { $suitable = fn_check_element($elms, $country); if ($suitable == false) { break; } $significance += 1 * (1 / count($elms)); } // Check state if ($elm_type == 'S') { // if country is in destanation_countries and it haven't got states, // we suppose that destanation cover all country if (!in_array($country, $dest_countries) || fn_get_country_states($country)) { $suitable = fn_check_element($elms, $state_id); if ($suitable == false) { break; } } else { $suitable = true; } $significance += 2 * (1 / count($elms)); } // Check city if ($elm_type == 'T') { $suitable = fn_check_element($elms, $city, true); if ($suitable == false) { break; } $significance += 3 * (1 / count($elms)); } // Check zipcode if ($elm_type == 'Z') { $suitable = fn_check_element($elms, $zipcode, true); if ($suitable == false) { break; } $significance += 4 * (1 / count($elms)); } // Check address if ($elm_type == 'A') { $suitable = fn_check_element($elms, $address, true); if ($suitable == false) { break; } $significance += 5 * (1 / count($elms)); } } $significance = number_format($significance, 4, '.', ''); if ($suitable == true) { $concur_destinations[$significance][] = $dest_id; } } if (!empty($concur_destinations)) { ksort($concur_destinations, SORT_NUMERIC); $concur_destinations = array_pop($concur_destinations); return reset($concur_destinations); } else { return false; } } return false; }
function fn_get_shippings($simple, $lang_code = CART_LANGUAGE) { $conditions = '1'; if (AREA == 'C') { $conditions .= " AND (" . fn_find_array_in_set($_SESSION['auth']['usergroup_ids'], 'a.usergroup_ids', true) . ")"; $conditions .= " AND a.status = 'A'"; $conditions .= fn_get_localizations_condition('a.localization'); } if ($simple == true) { return db_get_hash_single_array("SELECT a.shipping_id, b.shipping FROM ?:shippings as a LEFT JOIN ?:shipping_descriptions as b ON a.shipping_id = b.shipping_id AND b.lang_code = ?s WHERE ?p ORDER BY a.position", array('shipping_id', 'shipping'), $lang_code, $conditions); } else { return db_get_array("SELECT a.shipping_id, a.min_weight, a.max_weight, a.position, a.status, b.shipping, b.delivery_time, a.usergroup_ids FROM ?:shippings as a LEFT JOIN ?:shipping_descriptions as b ON a.shipping_id = b.shipping_id AND b.lang_code = ?s WHERE ?p ORDER BY a.position", $lang_code, $conditions); } }
****************************************************************************/ use Tygh\Registry; if (!defined('BOOTSTRAP')) { die('Access denied'); } $_REQUEST['category_id'] = empty($_REQUEST['category_id']) ? 0 : $_REQUEST['category_id']; if ($mode == 'catalog') { fn_add_breadcrumb(__('catalog')); $root_categories = fn_get_subcategories(0); foreach ($root_categories as $k => $v) { $root_categories[$k]['main_pair'] = fn_get_image_pairs($v['category_id'], 'category', 'M'); } Tygh::$app['view']->assign('root_categories', $root_categories); } elseif ($mode == 'view') { $_statuses = array('A', 'H'); $_condition = fn_get_localizations_condition('localization', true); $preview = fn_is_preview_action($auth, $_REQUEST); if (!$preview) { $_condition .= ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], 'usergroup_ids', true) . ')'; $_condition .= db_quote(' AND status IN (?a)', $_statuses); } if (fn_allowed_for('ULTIMATE')) { $_condition .= fn_get_company_condition('?:categories.company_id'); } $category_exists = db_get_field("SELECT category_id FROM ?:categories WHERE category_id = ?i ?p", $_REQUEST['category_id'], $_condition); if (!empty($category_exists)) { // Save current url to session for 'Continue shopping' button $_SESSION['continue_url'] = "categories.view?category_id={$_REQUEST['category_id']}"; // Save current category id to session $_SESSION['current_category_id'] = $_SESSION['breadcrumb_category_id'] = $_REQUEST['category_id']; // Get subcategories list for current category
function fn_get_products($params, $items_per_page = 0, $lang_code = CART_LANGUAGE) { if (isset($params['doLinks']) && $params['doLinks'] == 1) { $neighbours = fn_get_neighbours($_REQUEST['product_id']); $params['pid'] = $neighbours; $params['limit'] = 4; } if (isset($params['doSales']) && $params['doSales'] == 1) { $params['pid'] = fn_get_sales_products(); } if (isset($params['doSameLine']) && $params['doSameLine'] == 1) { $sameLineProducts = fn_get_same_line_products($_REQUEST['product_id']); if ($sameLineProducts) { $params['pid'] = $sameLineProducts; } else { return false; } } // Init filter $relevanceField = ''; $relevanceOrder = ''; $params = fn_init_view('products', $params); // Set default values to input params $default_params = array('pname' => '', 'pshort' => '', 'pfull' => '', 'pkeywords' => '', 'feature' => array(), 'type' => 'simple', 'page' => 1, 'action' => '', 'variants' => array(), 'ranges' => array(), 'custom_range' => array(), 'field_range' => array(), 'features_hash' => '', 'limit' => 0, 'bid' => 0, 'match' => '', 'search_tracking_flags' => array()); $params = array_merge($default_params, $params); if ((empty($params['pname']) || $params['pname'] != 'Y') && (empty($params['pshort']) || $params['pshort'] != 'Y') && (empty($params['pfull']) || $params['pfull'] != 'Y') && (empty($params['pkeywords']) || $params['pkeywords'] != 'Y') && (empty($params['feature']) || $params['feature'] != 'Y') && !empty($params['q'])) { $params['pname'] = 'Y'; } $auth =& $_SESSION['auth']; // Define fields that should be retrieved $fields = array('products.product_id', 'products.company_id', 'companies.company as company_name', 'descr1.product as product', 'products.tracking', 'products.feature_comparison', 'products.zero_price_action', 'products.product_type', 'products.tax_ids', 'products.weight', "GROUP_CONCAT(IF(products_categories.link_type = 'M', CONCAT(products_categories.category_id, 'M'), products_categories.category_id)) as category_ids", 'min_qty', 'max_qty', 'products.qty_step', 'products.list_qty_count', 'products.is_edp', 'avail_since', 'buy_in_advance', 'products.options_type', 'products.exceptions_type', 'companies.company as company_name', 'products.timestamp'); // Define sort fields $sortings = array('code' => 'products.product_code', 'status' => 'products.status', 'product' => 'descr1.product', 'position' => 'products_categories.position', 'price' => 'prices.price', 'list_price' => 'products.list_price', 'weight' => 'products.weight', 'amount' => 'products.amount', 'timestamp' => 'products.timestamp', 'popularity' => 'popularity.total', 'company' => "company_name"); $directions = array('asc' => 'asc', 'desc' => 'desc'); $join = $condition = $inventory_condition = ''; $condition .= fn_get_company_condition('products.company_id'); if (isset($params['company_id']) && $params['company_id'] != '') { $params['company_id'] = intval($params['company_id']); $condition .= db_quote(' AND products.company_id = ?i ', $params['company_id']); } if (AREA == 'C') { if (fn_check_suppliers_functionality()) { // if MVE or suppliers enabled $condition .= " AND (companies.status = 'A' OR products.company_id = 0) "; } else { // if suppliers disabled $condition .= fn_get_company_condition('products.company_id', true, '0', false, true); } } $join .= db_quote(" LEFT JOIN ?:companies companies ON companies.company_id = products.company_id "); $join .= db_quote(" LEFT JOIN ?:product_descriptions as descr1 ON descr1.product_id = products.product_id AND descr1.lang_code = ?s ", $lang_code); // Search string condition for SQL query if (isset($params['q']) && fn_string_no_empty($params['q'])) { $params['q'] = trim($params['q']); if ($params['match'] == 'any') { $pieces = fn_explode(' ', $params['q']); $search_type = ' OR '; } elseif ($params['match'] == 'all') { $pieces = fn_explode(' ', $params['q']); $search_type = ' AND '; } else { $pieces = array($params['q']); $search_type = ''; } $_condition = array(); foreach ($pieces as $piece) { if (strlen($piece) == 0) { continue; } $pieceTrimed = str_replace(array(' ', '-'), '', $piece); $tmp = db_quote("(descr1.search_words LIKE ?l)", "%{$piece}%"); // check search words if ($params['pname'] == 'Y') { $tmp .= db_quote(" OR MATCH (product) AGAINST ('" . mysql_real_escape_string($piece) . "')"); //$tmp .= db_quote(" OR MATCH (product) AGAINST ('$piece')"); $tmp .= db_quote(" OR REPLACE( REPLACE(upper(descr1.product), ' ', '' ) , '-', '' ) LIKE ?l", "%{$pieceTrimed}%"); $relevanceField = " ,MATCH(product) AGAINST ('" . mysql_real_escape_string($piece) . "' IN BOOLEAN MODE) AS relevance"; $relevanceOrder = "relevance DESC,"; } if ($params['pshort'] == 'Y') { //$tmp .= db_quote(" OR descr1.short_description LIKE ?l", "%$piece%"); } if ($params['pfull'] == 'Y') { //$tmp .= db_quote(" OR descr1.full_description LIKE ?l", "%$piece%"); } if ($params['pkeywords'] == 'Y') { // $tmp .= db_quote(" OR (descr1.meta_keywords LIKE ?l OR descr1.meta_description LIKE ?l)", "%$piece%", "%$piece%"); } if (!empty($params['feature']) && $params['action'] != 'feature_search') { // $tmp .= db_quote(" OR ?:product_features_values.value LIKE ?l", "%$piece%"); } $tmp .= db_quote(" OR products.product_code LIKE ?l", "%{$piece}%"); fn_set_hook('additional_fields_in_search', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $tmp); $_condition[] = '(' . $tmp . ')'; } $_cond = implode($search_type, $_condition); if (!empty($_condition)) { $condition .= ' AND (' . $_cond . ') '; } if (!empty($params['feature']) && $params['action'] != 'feature_search') { $join .= " LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id"; $condition .= db_quote(" AND (?:product_features_values.feature_id IN (?n) OR ?:product_features_values.feature_id IS NULL)", array_values($params['feature'])); } unset($_condition); } // // [Advanced and feature filters] // if (!empty($params['apply_limit']) && $params['apply_limit']) { $pids = array(); foreach ($params['pid'] as $pid) { if ($pid != $params['exclude_pid']) { if (count($pids) == $params['limit']) { break; } else { $pids[] = $pid; } } } $params['pid'] = $pids; } if (!empty($params['features_hash']) || !fn_is_empty($params['variants'])) { $join .= db_quote(" LEFT JOIN ?:product_features_values ON ?:product_features_values.product_id = products.product_id AND ?:product_features_values.lang_code = ?s", CART_LANGUAGE); } if (!empty($params['variants'])) { $params['features_hash'] .= implode('.', $params['variants']); } $advanced_variant_ids = $simple_variant_ids = $ranges_ids = $fields_ids = array(); if (!empty($params['features_hash'])) { if (!empty($params['advanced_filter'])) { list($av_ids, $ranges_ids, $fields_ids) = fn_parse_features_hash($params['features_hash']); $advanced_variant_ids = db_get_hash_multi_array("SELECT feature_id, variant_id FROM ?:product_feature_variants WHERE variant_id IN (?n)", array('feature_id', 'variant_id'), $av_ids); } else { list($simple_variant_ids, $ranges_ids, $fields_ids) = fn_parse_features_hash($params['features_hash']); } } if (!empty($params['multiple_variants']) && !empty($params['advanced_filter'])) { $simple_variant_ids = $params['multiple_variants']; } if (!empty($advanced_variant_ids)) { $join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS advanced_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_advanced ON pfv_advanced.product_id = products.product_id", CART_LANGUAGE); $where_and_conditions = array(); foreach ($advanced_variant_ids as $k => $variant_ids) { $where_or_conditions = array(); foreach ($variant_ids as $variant_id => $v) { $where_or_conditions[] = db_quote(" FIND_IN_SET('?i', advanced_variants)", $variant_id); } $where_and_conditions[] = "(" . implode(' OR ', $where_or_conditions) . ")"; } $condition .= ' AND ' . implode(' AND ', $where_and_conditions); } if (!empty($simple_variant_ids)) { $join .= db_quote(" LEFT JOIN (SELECT product_id, GROUP_CONCAT(?:product_features_values.variant_id) AS simple_variants FROM ?:product_features_values WHERE lang_code = ?s GROUP BY product_id) AS pfv_simple ON pfv_simple.product_id = products.product_id", CART_LANGUAGE); $where_conditions = array(); foreach ($simple_variant_ids as $k => $variant_id) { $where_conditions[] = db_quote(" FIND_IN_SET('?i', simple_variants)", $variant_id); } $condition .= ' AND ' . implode(' AND ', $where_conditions); } // // Ranges from text inputs // // Feature ranges if (!empty($params['custom_range'])) { foreach ($params['custom_range'] as $k => $v) { $k = intval($k); if (fn_string_no_empty($v['from']) || fn_string_no_empty($v['to'])) { if (!empty($v['type'])) { if ($v['type'] == 'D') { $v['from'] = fn_parse_date($v['from']); $v['to'] = fn_parse_date($v['to']); } } $join .= db_quote(" LEFT JOIN ?:product_features_values as custom_range_{$k} ON custom_range_{$k}.product_id = products.product_id AND custom_range_{$k}.lang_code = ?s", CART_LANGUAGE); if (fn_string_no_empty($v['from']) && fn_string_no_empty($v['to'])) { $condition .= db_quote(" AND (custom_range_{$k}.value_int >= ?i AND custom_range_{$k}.value_int <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i) ", $v['from'], $v['to'], $k); } else { $condition .= " AND custom_range_{$k}.value_int" . (fn_string_no_empty($v['from']) ? db_quote(' >= ?i', $v['from']) : db_quote(" <= ?i AND custom_range_{$k}.value = '' AND custom_range_{$k}.feature_id = ?i ", $v['to'], $k)); } } } } // Product field ranges $filter_fields = fn_get_product_filter_fields(); if (!empty($params['field_range'])) { foreach ($params['field_range'] as $field_type => $v) { $structure = $filter_fields[$field_type]; if (!empty($structure) && (!empty($v['from']) || !empty($v['to']))) { $params["{$structure['db_field']}_from"] = trim($v['from']); $params["{$structure['db_field']}_to"] = trim($v['to']); } } } // Ranges from database if (!empty($ranges_ids)) { $range_conditions = db_get_array("SELECT `from`, `to`, feature_id FROM ?:product_filter_ranges WHERE range_id IN (?n)", $ranges_ids); foreach ($range_conditions as $k => $range_condition) { $join .= db_quote(" LEFT JOIN ?:product_features_values as var_val_{$k} ON var_val_{$k}.product_id = products.product_id AND var_val_{$k}.lang_code = ?s", CART_LANGUAGE); $condition .= db_quote(" AND (var_val_{$k}.value_int >= ?i AND var_val_{$k}.value_int <= ?i AND var_val_{$k}.value = '' AND var_val_{$k}.feature_id = ?i) ", $range_condition['from'], $range_condition['to'], $range_condition['feature_id']); } } // Field ranges $fields_ids = empty($params['fields_ids']) ? $fields_ids : $params['fields']; if (!empty($fields_ids)) { foreach ($fields_ids as $rid => $field_type) { $structure = $filter_fields[$field_type]; if ($structure['condition_type'] == 'D') { $range_condition = db_get_row("SELECT `from`, `to`, range_id FROM ?:product_filter_ranges WHERE range_id = ?i", $rid); if (!empty($range_condition)) { $params["{$structure['db_field']}_from"] = $range_condition['from']; $params["{$structure['db_field']}_to"] = $range_condition['to']; } } elseif ($structure['condition_type'] == 'F') { $params[$structure['db_field']] = $rid; } elseif ($structure['condition_type'] == 'C') { $params[$structure['db_field']] = $rid == 1 ? 'Y' : 'N'; } } } // Checkbox features if (!empty($params['ch_filters']) && !fn_is_empty($params['ch_filters'])) { foreach ($params['ch_filters'] as $k => $v) { // Product field filter if (is_string($k) == true && !empty($v) && ($structure = $filter_fields[$k])) { $condition .= db_quote(" AND {$structure['table']}.{$structure['db_field']} IN (?a)", $v == 'A' ? array('Y', 'N') : $v); // Feature filter } elseif (!empty($v)) { $fid = intval($k); $join .= db_quote(" LEFT JOIN ?:product_features_values as ch_features_{$fid} ON ch_features_{$fid}.product_id = products.product_id AND ch_features_{$fid}.lang_code = ?s", CART_LANGUAGE); $condition .= db_quote(" AND ch_features_{$fid}.feature_id = ?i AND ch_features_{$fid}.value IN (?a)", $fid, $v == 'A' ? array('Y', 'N') : $v); } } } // Text features if (!empty($params['tx_features'])) { foreach ($params['tx_features'] as $k => $v) { if (fn_string_no_empty($v)) { $fid = intval($k); $join .= " LEFT JOIN ?:product_features_values as tx_features_{$fid} ON tx_features_{$fid}.product_id = products.product_id"; $condition .= db_quote(" AND tx_features_{$fid}.value LIKE ?l AND tx_features_{$fid}.lang_code = ?s", "%" . trim($v) . "%", CART_LANGUAGE); } } } // // [/Advanced filters] // $feature_search_condition = ''; if (!empty($params['feature'])) { // Extended search by product fields $_cond = array(); $total_hits = 0; foreach ($params['feature'] as $f_id) { if (!empty($f_val)) { $total_hits++; $_cond[] = db_quote("(?:product_features_values.feature_id = ?i)", $f_id); } } if (!empty($_cond)) { $cache_feature_search = db_get_fields("SELECT product_id, COUNT(product_id) as cnt FROM ?:product_features_values WHERE (" . implode(' OR ', $_cond) . ") GROUP BY product_id HAVING cnt = {$total_hits}"); $feature_search_condition .= db_quote(" AND products_categories.product_id IN (?n)", $cache_feature_search); } } // Category search condition for SQL query if (!empty($params['cid'])) { $cids = is_array($params['cid']) ? $params['cid'] : array($params['cid']); if (!empty($params['subcats']) && $params['subcats'] == 'Y') { $_ids = db_get_fields("SELECT a.category_id FROM ?:categories as a LEFT JOIN ?:categories as b ON b.category_id IN (?n) WHERE a.id_path LIKE CONCAT(b.id_path, '/%')", $cids); $cids = fn_array_merge($cids, $_ids, false); } $condition .= db_quote(" AND ?:categories.category_id IN (?n)", $cids); } // If we need to get the products by IDs and no IDs passed, don't search anything if (!empty($params['force_get_by_ids']) && empty($params['pid']) && empty($params['product_id'])) { return array(array(), array(), 0); } // Product ID search condition for SQL query if (!empty($params['pid'])) { if (isset($params['doLinks']) && $params['doLinks'] == 1) { $condition .= db_quote(' AND products.product_id IN (' . $params['pid'] . ')', $params['pid']); } else { $condition .= db_quote(' AND products.product_id IN (?n)', $params['pid']); } } // Exclude products from search results if (!empty($params['exclude_pid'])) { $condition .= db_quote(' AND products.product_id NOT IN (?n)', $params['exclude_pid']); } // Search by feature comparison flag if (!empty($params['feature_comparison'])) { $condition .= db_quote(' AND products.feature_comparison = ?s', $params['feature_comparison']); } // Search products by localization $condition .= fn_get_localizations_condition('products.localization', true); $condition .= fn_get_localizations_condition('?:categories.localization', true); if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) { $condition .= db_quote(' AND prices.price >= ?d', fn_convert_price(trim($params['price_from']))); } if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) { $condition .= db_quote(' AND prices.price <= ?d', fn_convert_price(trim($params['price_to']))); } if (isset($params['weight_from']) && fn_is_numeric($params['weight_from'])) { $condition .= db_quote(' AND products.weight >= ?d', fn_convert_weight(trim($params['weight_from']))); } if (isset($params['weight_to']) && fn_is_numeric($params['weight_to'])) { $condition .= db_quote(' AND products.weight <= ?d', fn_convert_weight(trim($params['weight_to']))); } // search specific inventory status if (!empty($params['search_tracking_flags'])) { $condition .= db_quote(' AND products.tracking IN(?a)', $params['search_tracking_flags']); } if (isset($params['amount_from']) && fn_is_numeric($params['amount_from'])) { $condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount >= ?i, products.amount >= ?i)", $params['amount_from'], $params['amount_from']); $inventory_condition .= db_quote(' AND inventory.amount >= ?i', $params['amount_from']); } if (isset($params['amount_to']) && fn_is_numeric($params['amount_to'])) { $condition .= db_quote(" AND IF(products.tracking = 'O', inventory.amount <= ?i, products.amount <= ?i)", $params['amount_to'], $params['amount_to']); $inventory_condition .= db_quote(' AND inventory.amount <= ?i', $params['amount_to']); } if (Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') { // FIXME? Registry in model $condition .= " AND IF(products.tracking = 'O', inventory.amount > 0, products.amount > 0)"; } if (!empty($params['status'])) { $condition .= db_quote(' AND products.status IN (?a)', $params['status']); } if (!empty($params['shipping_freight_from'])) { $condition .= db_quote(' AND products.shipping_freight >= ?d', $params['shipping_freight_from']); } if (!empty($params['shipping_freight_to'])) { $condition .= db_quote(' AND products.shipping_freight <= ?d', $params['shipping_freight_to']); } if (!empty($params['free_shipping'])) { $condition .= db_quote(' AND products.free_shipping = ?s', $params['free_shipping']); } if (!empty($params['downloadable'])) { $condition .= db_quote(' AND products.is_edp = ?s', $params['downloadable']); } if (!empty($params['b_id'])) { $join .= " LEFT JOIN ?:block_links ON ?:block_links.object_id = products.product_id AND ?:block_links.location = 'products'"; $condition .= db_quote(' AND ?:block_links.block_id = ?i', $params['b_id']); } if (isset($params['pcode']) && fn_string_no_empty($params['pcode'])) { $pcode = trim($params['pcode']); $fields[] = 'inventory.combination'; $condition .= db_quote(" AND (inventory.product_code LIKE ?l OR products.product_code LIKE ?l)", "%{$pcode}%", "%{$pcode}%"); $inventory_condition .= db_quote(" AND inventory.product_code LIKE ?l", "%{$pcode}%"); } if (isset($params['amount_to']) && fn_is_numeric($params['amount_to']) || isset($params['amount_from']) && fn_is_numeric($params['amount_from']) || !empty($params['pcode']) || Registry::get('settings.General.show_out_of_stock_products') == 'N' && AREA == 'C') { $join .= " LEFT JOIN ?:product_options_inventory as inventory ON inventory.product_id = products.product_id {$inventory_condition}"; } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (products.timestamp >= ?i AND products.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['item_ids'])) { $condition .= db_quote(" AND products.product_id IN (?n)", explode(',', $params['item_ids'])); } if (isset($params['popularity_from']) && fn_is_numeric($params['popularity_from'])) { $condition .= db_quote(' AND popularity.total >= ?i', $params['popularity_from']); } if (isset($params['popularity_to']) && fn_is_numeric($params['popularity_to'])) { $condition .= db_quote(' AND popularity.total <= ?i', $params['popularity_to']); } // Extended search mode condition for SQL query if ($params['type'] == 'extended') { array_push($fields, 'products.product_code', 'products.amount', 'MIN(prices.price) as price', 'products.status', 'products.list_price', 'products.temp_price', 'descr1.short_description', "IF(descr1.short_description = '', descr1.full_description, '') as full_description"); if (!empty($params['cid'])) { $fields[] = 'products_categories.position'; } } $price_usergroup_cond = db_quote(' AND prices.usergroup_id IN (?n)', AREA == 'A' ? USERGROUP_ALL : array_merge(array(USERGROUP_ALL), $auth['usergroup_ids'])); $condition .= $price_usergroup_cond; $price_usergroup_cond_2 = str_replace('prices', 'prices_2', $price_usergroup_cond); $join .= " LEFT JOIN ?:product_prices as prices ON prices.product_id = products.product_id AND prices.lower_limit = 1 LEFT JOIN ?:product_prices as prices_2 ON prices.product_id = prices_2.product_id AND prices_2.lower_limit = 1 AND prices_2.price < prices.price " . $price_usergroup_cond_2; $condition .= ' AND prices_2.price IS NULL'; // Show enabled products/categories $_p_statuses = array('A'); $_c_statuses = array('A', 'H'); $avail_cond = AREA == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], '?:categories.usergroup_ids', true) . ')' : ''; $avail_cond .= AREA == 'C' ? ' AND (' . fn_find_array_in_set($auth['usergroup_ids'], 'products.usergroup_ids', true) . ')' : ''; $avail_cond .= AREA == 'C' ? db_quote(" AND ?:categories.status IN (?a) AND products.status IN (?a)", $_c_statuses, $_p_statuses) : ''; $join .= " INNER JOIN ?:products_categories as products_categories ON products_categories.product_id = products.product_id INNER JOIN ?:categories ON ?:categories.category_id = products_categories.category_id {$avail_cond} {$feature_search_condition}"; $limit = ''; $group_by = 'products.product_id'; fn_set_hook('get_products', $params, $fields, $sortings, $condition, $join, $sorting, $group_by, $lang_code); if (empty($params['sort_by']) || empty($sortings[$params['sort_by']])) { $params['sort_by'] = Registry::get('settings.Appearance.default_products_sorting'); if (empty($sortings[$params['sort_by']])) { $_products_sortings = fn_get_products_sorting(false); $params['sort_by'] = key($_products_sortings); } } if ($params['type'] != 'extended' && $params['sort_by'] == 'price') { $params['sort_by'] = 'product'; } $default_sorting = fn_get_products_sorting(false); if (empty($params['sort_order']) || empty($directions[$params['sort_order']])) { if (!empty($default_sorting[$params['sort_by']]['default_order'])) { $params['sort_order'] = $default_sorting[$params['sort_by']]['default_order']; } else { $params['sort_order'] = 'asc'; } } if (AREA == 'A' || $params['sort_by'] == 'popularity') { $fields[] = 'popularity.total as popularity'; $join .= db_quote(" LEFT JOIN ?:product_popularity as popularity ON popularity.product_id = products.product_id"); } $sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; // Used for View cascading if (!empty($params['get_query'])) { return "SELECT products.product_id FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY products.product_id"; } // Used for Extended search if (!empty($params['get_conditions'])) { return array($fields, $join, $condition); } if (!empty($params['limit'])) { $limit = db_quote(" LIMIT 0, ?i", $params['limit']); } $total = 0; if (!empty($items_per_page)) { if (!empty($params['limit']) && $total > $params['limit']) { $total = $params['limit']; } $limit = fn_paginate($params['page'], 0, $items_per_page, true); } if (isset($_GET['no_pagination'])) { ini_set('memory_limit', '128M'); $limit = db_quote(""); } if (isset($params['doLinks']) && $params['doLinks'] == 1) { $sorting = "products.amount DESC, descr1.product ASC"; } $sqlquery = 'SELECT SQL_CALC_FOUND_ROWS ' . implode(', ', $fields) . ",products.amount {$relevanceField} FROM ?:products as products {$join} WHERE 1 {$condition} GROUP BY {$group_by} ORDER BY {$relevanceOrder} `products`.`amount` DESC, {$sorting} {$limit}"; $products = db_get_array($sqlquery); if (!empty($items_per_page)) { $total = db_get_found_rows(); fn_paginate($params['page'], $total, $items_per_page); } else { $total = count($products); } // Post processing foreach ($products as $k => $v) { $products[$k]['category_ids'] = fn_convert_categories($v['category_ids']); } if (!empty($params['item_ids'])) { $products = fn_sort_by_ids($products, explode(',', $params['item_ids'])); } if (!empty($params['pid']) && !empty($params['apply_limit']) && $params['apply_limit']) { $products = fn_sort_by_ids($products, $params['pid']); } fn_set_hook('get_products_post', $products); return array($products, $params, $total); }