function fn_get_seo_rules($params = array(), $items_per_page = 0, $lang_code = DESCR_SL) { $lang_code = fn_get_corrected_seo_lang_code($lang_code); $global_total = db_get_fields("SELECT dispatch FROM ?:seo_names WHERE object_id = '0' AND type = 's' GROUP BY dispatch"); $local_total = db_get_fields("SELECT dispatch FROM ?:seo_names WHERE object_id = '0' AND type = 's' AND lang_code = ?s", $lang_code); if ($diff = array_diff($global_total, $local_total)) { foreach ($diff as $disp) { fn_create_seo_name(0, 's', str_replace('.', '-', $disp), 0, $disp, DESCR_SL); } } // Init filter $params = fn_init_view('seo_rules', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // default page is 1 $condition = ''; if (isset($params['name']) && fn_string_no_empty($params['name'])) { $condition .= db_quote(" AND name LIKE ?l", "%" . trim($params['name']) . "%"); } if (isset($params['controller']) && fn_string_no_empty($params['controller'])) { $condition .= db_quote(" AND dispatch LIKE ?l", "%" . trim($params['controller']) . "%"); } $limit = ''; if (!empty($items_per_page)) { $total = db_get_field("SELECT COUNT(*) FROM ?:seo_names WHERE object_id = '0' AND type = 's' AND lang_code = ?s ?p", $lang_code, $condition); $limit = fn_paginate($params['page'], $total, $items_per_page); } $seo_data = db_get_array("SELECT name, dispatch FROM ?:seo_names WHERE object_id = '0' AND type = 's' AND lang_code = ?s ?p ORDER BY dispatch {$limit}", $lang_code, $condition); return array($seo_data, $params); }
function fn_get_logs($params, $items_per_page = null) { // Init filter $params = fn_init_view('logs', $params); if ($items_per_page === null) { $items_per_page = Registry::get('settings.Appearance.admin_elements_per_page'); } $sortings = array('timestamp' => '?:logs.timestamp', 'user' => array('?:users.lastname', '?:users.firstname')); $directions = array('asc' => 'asc', 'desc' => 'desc'); $fields = array('?:logs.*', '?:users.firstname', '?:users.lastname'); 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'] = 'timestamp'; $params['sort_order'] = 'desc'; } $params['page'] = empty($params['page']) ? 1 : $params['page']; if (is_array($sortings[$params['sort_by']])) { $sorting = join(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) . ' ' . $directions[$params['sort_order']]; } else { $sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']]; } $join = "LEFT JOIN ?:users USING(user_id)"; $condition = ''; if (!empty($params['period']) && $params['period'] != 'A') { list($time_from, $time_to) = fn_create_periods($params); $condition .= db_quote(" AND (?:logs.timestamp >= ?i AND ?:logs.timestamp <= ?i)", $time_from, $time_to); } if (isset($params['q_user']) && fn_string_no_empty($params['q_user'])) { $condition .= db_quote(" AND (?:users.lastname LIKE ?l OR ?:users.firstname LIKE ?l)", "%" . trim($params['q_user']) . "%", "%" . trim($params['q_user']) . "%"); } if (!empty($params['q_type'])) { $condition .= db_quote(" AND (?:logs.type LIKE ?l OR ?:logs.type LIKE ?l)", "%{$params['q_type']}%", "%{$params['q_type']}%"); } if (!empty($params['q_action'])) { $condition .= db_quote(" AND (?:logs.action LIKE ?l OR ?:logs.action LIKE ?l)", "%{$params['q_action']}%", "%{$params['q_action']}%"); } $limit = ''; $total = 0; if (!empty($items_per_page)) { $total = db_get_field("SELECT COUNT(DISTINCT(?:logs.log_id)) FROM ?:logs ?p WHERE 1 ?p", $join, $condition); $limit = fn_paginate($params['page'], $total, $items_per_page); } $data = db_get_array("SELECT " . join(', ', $fields) . " FROM ?:logs ?p WHERE 1 ?p ORDER BY {$sorting} {$limit}", $join, $condition); if (!$total) { $total = count($data); } foreach ($data as $k => $v) { $data[$k]['backtrace'] = !empty($v['backtrace']) ? unserialize($v['backtrace']) : array(); $data[$k]['content'] = !empty($v['content']) ? unserialize($v['content']) : array(); } return array($data, $params, $total); }
function fn_tags_get_users(&$params, &$fields, &$sortings, &$condition, &$join) { if (isset($params['tag']) && fn_string_no_empty($params['tag'])) { $join .= db_quote(" LEFT JOIN ?:tag_links ON ?:users.user_id = ?:tag_links.user_id INNER JOIN ?:tags ON ?:tag_links.tag_id = ?:tags.tag_id "); $condition .= db_quote(" AND ?:tags.tag = ?s", $params['tag']); } }
function fn_get_subscribers($params, $lang_code = CART_LANGUAGE) { // Init filter $params = fn_init_view('subscribers', $params); // Set default values to input params $default_params = array('page' => 1); $params = array_merge($default_params, $params); // Define fields that should be retrieved $fields = array('?:subscribers.subscriber_id', '?:subscribers.email', '?:subscribers.timestamp', '?:subscribers.subscriber_id', "GROUP_CONCAT(?:user_mailing_lists.list_id) as list_ids"); // Define sort fields $sortings = array('email' => '?:subscribers.email', 'timestamp' => '?:subscribers.timestamp'); $directions = array('asc' => 'asc', 'desc' => 'desc'); $condition = ''; $group_by = "?:subscribers.subscriber_id"; $join = db_quote(" LEFT JOIN ?:user_mailing_lists ON ?:user_mailing_lists.subscriber_id = ?:subscribers.subscriber_id"); if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:subscribers.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (!empty($params['list_id'])) { $condition .= db_quote(" AND ?:user_mailing_lists.list_id = ?i", $params['list_id']); } if (!empty($params['confirmed'])) { $condition .= db_quote(" AND ?:user_mailing_lists.confirmed = ?i", $params['confirmed'] == 'Y'); } if (!empty($params['format'])) { $condition .= db_quote(" AND ?:user_mailing_lists.format = ?i", $params['format']); } if (!empty($params['language'])) { $condition .= db_quote(" AND ?:user_mailing_lists.lang_code = ?s", $params['language']); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:subscribers.timestamp >= ?i AND ?:subscribers.timestamp <= ?i)", $params['time_from'], $params['time_to']); } 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'] = 'timestamp'; } $sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $total = db_get_field("SELECT COUNT(DISTINCT(?:subscribers.subscriber_id)) FROM ?:subscribers {$join} WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total, Registry::get('settings.Appearance.admin_elements_per_page')); $subscribers = db_get_array('SELECT ' . implode(', ', $fields) . " FROM ?:subscribers {$join} WHERE 1 {$condition} GROUP BY {$group_by} ORDER BY {$sorting} {$limit}"); return array($subscribers, $params); }
function fn_get_rma_returns($params) { // Init filter $params = fn_init_view('rma', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // Define fields that should be retrieved $fields = array('DISTINCT ?:rma_returns.return_id', '?:rma_returns.order_id', '?:rma_returns.timestamp', '?:rma_returns.status', '?:rma_returns.total_amount', '?:rma_property_descriptions.property AS action', '?:users.firstname', '?:users.lastname'); // Define sort fields $sortings = array('return_id' => "?:rma_returns.return_id", 'timestamp' => "?:rma_returns.timestamp", 'order_id' => "?:rma_returns.order_id", 'status' => "?:rma_returns.status", 'amount' => "?:rma_returns.total_amount", 'action' => "?:rma_returns.action", 'customer' => "?:users.lastname"); $directions = array('asc' => 'asc', 'desc' => 'desc'); 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'] = 'timestamp'; } $sort = $sortings[$params['sort_by']] . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $join = $condition = $group = ''; if (isset($params['cname']) && fn_string_no_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (isset($params['rma_amount_from']) && fn_is_numeric($params['rma_amount_from'])) { $condition .= db_quote("AND ?:rma_returns.total_amount >= ?d", $params['rma_amount_from']); } if (isset($params['rma_amount_to']) && fn_is_numeric($params['rma_amount_to'])) { $condition .= db_quote("AND ?:rma_returns.total_amount <= ?d", $params['rma_amount_to']); } if (!empty($params['action'])) { $condition .= db_quote(" AND ?:rma_returns.action = ?s", $params['action']); } if (!empty($params['return_id'])) { $condition .= db_quote(" AND ?:rma_returns.return_id = ?i", $params['return_id']); } if (!empty($params['request_status'])) { $condition .= db_quote(" AND ?:rma_returns.status IN (?a)", $params['request_status']); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:rma_returns.timestamp >= ?i AND ?:rma_returns.timestamp <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($params['order_id'])) { $condition .= db_quote(" AND ?:rma_returns.order_id = ?i", $params['order_id']); } if (!empty($params['user_id'])) { $condition .= db_quote(" AND ?:rma_returns.user_id = ?i", $params['user_id']); } if (!empty($params['order_status'])) { $condition .= db_quote(" AND ?:orders.status IN (?a)", $params['order_status']); } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"; $group .= db_quote(" GROUP BY ?:rma_returns.return_id HAVING COUNT(?:orders.order_id) >= ?i", count($arr)); } $items_per_page = Registry::get('settings.Appearance.' . (AREA == 'A') ? 'admin_elements_per_page' : 'elements_per_page'); $total = db_get_field("SELECT COUNT(DISTINCT ?:rma_returns.return_id) FROM ?:rma_returns LEFT JOIN ?:rma_return_products ON ?:rma_return_products.return_id = ?:rma_returns.return_id LEFT JOIN ?:rma_property_descriptions ON ?:rma_property_descriptions.property_id = ?:rma_returns.action LEFT JOIN ?:users ON ?:rma_returns.user_id = ?:users.user_id LEFT JOIN ?:orders ON ?:rma_returns.order_id = ?:orders.order_id {$join} WHERE 1 {$condition} {$group}"); $limit = fn_paginate($params['page'], $total, $items_per_page); // FIXME $return_requests = db_get_array("SELECT " . implode(', ', $fields) . " FROM ?:rma_returns LEFT JOIN ?:rma_return_products ON ?:rma_return_products.return_id = ?:rma_returns.return_id LEFT JOIN ?:rma_property_descriptions ON (?:rma_property_descriptions.property_id = ?:rma_returns.action AND ?:rma_property_descriptions.lang_code = ?s) LEFT JOIN ?:users ON ?:rma_returns.user_id = ?:users.user_id LEFT JOIN ?:orders ON ?:rma_returns.order_id = ?:orders.order_id {$join} WHERE 1 {$condition} {$group} ORDER BY {$sort} {$limit}", AREA == 'C' ? CART_LANGUAGE : DESCR_SL); return array($return_requests, $params); }
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_shipments_info($params, $items_per_page = SHIPMENTS_PER_PAGE) { // Init view params $params = fn_init_view('shipments', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // default page is 1 $fields_list = array('?:shipments.shipment_id', '?:shipments.timestamp AS shipment_timestamp', '?:shipments.comments', '?:shipment_items.order_id', '?:orders.timestamp AS order_timestamp', '?:orders.s_firstname', '?:orders.s_lastname'); $joins = array('LEFT JOIN ?:shipment_items ON (?:shipments.shipment_id = ?:shipment_items.shipment_id)', 'LEFT JOIN ?:orders ON (?:shipment_items.order_id = ?:orders.order_id)'); $group = array('?:shipments.shipment_id'); // Define sort fields $sortings = array('id' => "?:shipments.shipment_id", 'order_id' => "?:orders.order_id", 'shipment_date' => "?:shipments.timestamp", 'order_date' => "?:orders.timestamp", 'customer' => array("?:orders.s_lastname", "?:orders.s_firstname")); $directions = array('asc' => 'asc', 'desc' => 'desc'); 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'] = 'id'; } $sorting = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $condition = ''; if (isset($params['advanced_info']) && $params['advanced_info']) { $fields_list[] = '?:shipping_descriptions.shipping AS shipping'; $fields_list[] = '?:shipments.tracking_number'; $fields_list[] = '?:shipments.carrier'; $joins[] = ' LEFT JOIN ?:shippings ON (?:shipments.shipping_id = ?:shippings.shipping_id)'; $joins[] = ' LEFT JOIN ?:shipping_descriptions ON (?:shippings.shipping_id = ?:shipping_descriptions.shipping_id)'; $condition .= db_quote(' AND ?:shipping_descriptions.lang_code = ?s', DESCR_SL); } if (!empty($params['order_id'])) { $condition .= db_quote(' AND ?:shipment_items.order_id = ?i', $params['order_id']); } if (!empty($params['shipment_id'])) { $condition .= db_quote(' AND ?:shipments.shipment_id = ?i', $params['shipment_id']); } if (isset($params['cname']) && fn_string_no_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:orders.firstname LIKE ?l AND ?:orders.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:orders.firstname LIKE ?l OR ?:orders.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:shipment_items.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:shipment_items.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)), ',')); } $joins[] = "LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"; } if (!empty($params['shipment_period']) && $params['shipment_period'] != 'A') { $params['time_from'] = $params['shipment_time_from']; $params['time_to'] = $params['shipment_time_to']; $params['period'] = $params['shipment_period']; list($params['shipment_time_from'], $params['shipment_time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:shipments.timestamp >= ?i AND ?:shipments.timestamp <= ?i)", $params['shipment_time_from'], $params['shipment_time_to']); } if (!empty($params['order_period']) && $params['order_period'] != 'A') { $params['time_from'] = $params['order_time_from']; $params['time_to'] = $params['order_time_to']; $params['period'] = $params['order_period']; list($params['order_time_from'], $params['order_time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:orders.timestamp >= ?i AND ?:orders.timestamp <= ?i)", $params['order_time_from'], $params['order_time_to']); } fn_set_hook('get_shipments', $params, $fields_list, $joins, $condition, $group); $fields_list = implode(', ', $fields_list); $joins = implode(' ', $joins); $group = implode(', ', $group); if (!empty($group)) { $group = ' GROUP BY ' . $group; } $limit = ''; if (!empty($items_per_page)) { $total = db_get_field("SELECT COUNT(DISTINCT(?:shipments.shipment_id)) FROM ?:shipments {$joins} WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total, $items_per_page); } $shipments = db_get_array("SELECT {$fields_list} FROM ?:shipments {$joins} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}"); if (isset($params['advanced_info']) && $params['advanced_info'] && !empty($shipments)) { foreach ($shipments as $id => $shipment) { $items = db_get_array('SELECT item_id, amount FROM ?:shipment_items WHERE shipment_id = ?i', $shipment['shipment_id']); if (!empty($items)) { foreach ($items as $item) { $shipments[$id]['items'][$item['item_id']] = $item['amount']; } } } } return array($shipments, $params, $total); }
$fields_select[$k] = db_quote("?s as lang_code", $new_language['lang_code']); db_query("REPLACE INTO ?:{$table} (" . implode(', ', $fields_insert) . ") SELECT " . implode(', ', $fields_select) . " FROM ?:{$table} WHERE lang_code = 'EN'"); } } else { fn_set_notification('E', fn_get_lang_var('error'), str_replace('[code]', $new_language['lang_code'], fn_get_lang_var('error_lang_code_exists'))); } } } $q = empty($_REQUEST['q']) ? '' : $_REQUEST['q']; return array(CONTROLLER_STATUS_OK, "languages.manage?q={$q}"); } // // Get language variables values // if ($mode == 'manage') { if (isset($_REQUEST['q']) && fn_string_no_empty($_REQUEST['q'])) { $_query = db_quote(" lang_code = ?s AND (name LIKE ?l OR value LIKE ?l)", DESCR_SL, "%" . trim($_REQUEST['q']) . "%", "%" . trim($_REQUEST['q']) . "%"); } else { $_query = db_quote(" lang_code = ?s", DESCR_SL); } $page = empty($_REQUEST['page']) ? 1 : $_REQUEST['page']; $lang_data_count = db_get_field("SELECT COUNT(*) FROM ?:language_values WHERE {$_query}"); $limit = fn_paginate($page, $lang_data_count, Registry::get('settings.Appearance.admin_elements_per_page')); $lang_data = db_get_array("SELECT name, value FROM ?:language_values WHERE {$_query} ORDER BY name {$limit}"); Registry::set('navigation.tabs', array('translations' => array('title' => fn_get_lang_var('translations'), 'js' => true), 'languages' => array('title' => fn_get_lang_var('languages'), 'js' => true))); $view->assign('lang_data', $lang_data); $view->assign('langs', Registry::get('languages')); } elseif ($mode == 'delete_variable') { if (!empty($_REQUEST['name'])) { db_query("DELETE FROM ?:language_values WHERE name = ?s", $_REQUEST['name']); }
function fn_get_carts($params) { // Init filter $params = fn_init_view('carts', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // Define fields that should be retrieved $fields = array('?:user_session_products.user_id', '?:users.firstname', '?:users.lastname', '?:user_session_products.timestamp AS date'); // Define sort fields $sortings = array('customer' => "CONCAT(?:users.lastname, ?:users.firstname)", 'date' => "?:user_session_products.timestamp"); $directions = array('asc' => 'asc', 'desc' => 'desc'); 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'] = 'customer'; } $sorting = $sortings[$params['sort_by']] . ' ' . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $condition = $join = ''; $group = " GROUP BY ?:user_session_products.user_id"; if (isset($params['cname']) && fn_string_no_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (!empty($params['user_id'])) { $condition .= db_quote(" AND ?:user_session_products.user_id = ?i", $params['user_id']); } if (!empty($params['online_only'])) { $join .= ' LEFT JOIN ?:sessions ON ?:sessions.session_id = ?:user_session_products.session_id'; $condition .= db_quote(" AND ?:sessions.expiry > ?i", TIME + SESSION_ALIVE_TIME - 300); } if (!empty($params['with_info_only'])) { $condition .= db_quote(" AND ?:users.email != ''"); } if (!empty($params['users_type'])) { if ($params['users_type'] == 'R') { $condition .= db_quote(" AND !ISNULL(?:users.user_id)"); } elseif ($params['users_type'] == 'G') { $condition .= db_quote(" AND ISNULL(?:users.user_id)"); } } if (!empty($params['total_from']) || !empty($params['total_to'])) { $having = ''; if (fn_is_numeric($params['total_from'])) { $having .= db_quote(" AND SUM(price * amount) >= ?d", $params['total_from']); } if (fn_is_numeric($params['total_to'])) { $having .= db_quote(" AND SUM(price * amount) <= ?d", $params['total_to']); } if (!empty($having)) { $users4total = db_get_fields("SELECT user_id FROM ?:user_session_products GROUP BY user_id HAVING 1 {$having}"); if (!empty($users4total)) { $condition .= db_quote(" AND (?:user_session_products.user_id IN (?n))", $users4total); } else { $condition .= " AND (?:user_session_products.user_id = 'no')"; } } } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:user_session_products.timestamp >= ?i AND ?:user_session_products.timestamp <= ?i)", $params['time_from'], $params['time_to']); } $_condition = array(); if (!empty($params['product_type_c'])) { $_condition[] = "?:user_session_products.type = 'C'"; } if (!empty($params['product_type_w']) && $params['product_type_w'] == 'Y') { $_condition[] = "?:user_session_products.type = 'W'"; } if (!empty($_condition)) { $condition .= " AND (" . implode(" OR ", $_condition) . ")"; } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:user_session_products.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $group .= " HAVING COUNT(?:user_session_products.user_id) >= " . count($arr); } $join .= " LEFT JOIN ?:users ON ?:user_session_products.user_id = ?:users.user_id"; // checking types for retrieving from the database $type_restrictions = array('C'); fn_set_hook('get_carts', $type_restrictions); if (!empty($type_restrictions) && is_array($type_restrictions)) { $condition .= " AND ?:user_session_products.type IN ('" . implode("', '", $type_restrictions) . "')"; } $total = db_get_field("SELECT COUNT(DISTINCT ?:user_session_products.user_id) FROM ?:user_session_products {$join} WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total); $carts_list = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:user_session_products {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}", 'user_id'); $_SESSION['abandoned_carts'] = db_get_fields("SELECT ?:user_session_products.user_id FROM ?:user_session_products {$join} WHERE 1 {$condition} GROUP BY user_id"); return array($carts_list, $params); }
function fn_get_gift_certificates($params) { // Init filter $params = fn_init_view('gift_certs', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // Define fields that should be retrieved $fields = array('?:gift_certificates.gift_cert_id', '?:gift_certificates.gift_cert_code', '?:gift_certificates.timestamp', '?:gift_certificates.amount', '?:gift_certificates.status', '?:gift_certificates.recipient', '?:gift_certificates.sender', '?:gift_certificates.send_via', '?:gift_certificates.email'); // Define sort fields $sortings = array('timestamp' => "?:gift_certificates.timestamp", 'amount' => "?:gift_certificates.amount", 'recipient' => "?:gift_certificates.recipient", 'sender' => "?:gift_certificates.sender", 'status' => "?:gift_certificates.status", 'gift_cert_code' => "?:gift_certificates.gift_cert_code", 'send_via' => "?:gift_certificates.send_via"); $directions = array('asc' => 'asc', 'desc' => 'desc'); 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'] = 'timestamp'; } $sort = $sortings[$params['sort_by']] . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $condition = $join = ''; if (isset($params['sender']) && fn_string_no_empty($params['sender'])) { $condition .= db_quote(" AND ?:gift_certificates.sender LIKE ?l", "%" . trim($params['sender']) . "%"); } if (isset($params['recipient']) && fn_string_no_empty($params['recipient'])) { $condition .= db_quote(" AND ?:gift_certificates.recipient LIKE ?l", "%" . trim($params['recipient']) . "%"); } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:gift_certificates.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (!empty($params['amount_from'])) { $condition .= db_quote(" AND ?:gift_certificates.amount >= ?d", $params['amount_from']); } if (!empty($params['amount_to'])) { $condition .= db_quote(" AND ?:gift_certificates.amount <= ?d", $params['amount_to']); } if (!empty($params['gift_cert_ids'])) { $condition .= db_quote(" AND ?:gift_certificates.gift_cert_id IN (?n)", $params['gift_cert_ids']); } if (!empty($params['status'])) { $condition .= db_quote(" AND ?:gift_certificates.status IN (?a)", $params['status']); } if (isset($params['gift_cert_code']) && fn_string_no_empty($params['gift_cert_code'])) { $condition .= db_quote(" AND ?:gift_certificates.gift_cert_code LIKE ?l", "%" . trim($params['gift_cert_code']) . "%"); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:gift_certificates.timestamp >= ?i AND ?:gift_certificates.timestamp <= ?i)", $params['time_from'], $params['time_to']); } $items_per_page = Registry::get('addons.gift_certificates.cert_per_page'); $total = db_get_field("SELECT COUNT(*) FROM ?:gift_certificates WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total, $items_per_page); $gift_certificates = db_get_array("SELECT " . implode(',', $fields) . " FROM ?:gift_certificates WHERE 1 {$condition} ORDER BY {$sort} {$limit}"); foreach ($gift_certificates as $k => $v) { $debit_balance = db_get_row("SELECT debit, debit_products FROM ?:gift_certificates_log WHERE gift_cert_id = ?i ORDER BY timestamp DESC", $v['gift_cert_id']); $gift_certificates[$k]['debit'] = empty($debit_balance) ? $v['amount'] : $debit_balance['debit']; } return array($gift_certificates, $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); }
function fn_get_users($params, &$auth, $items_per_page = 0, $custom_view = '') { // Init filter $_view = !empty($custom_view) ? $custom_view : 'users'; $params = fn_init_view($_view, $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // Define fields that should be retrieved $fields = array("?:users.user_id", "?:users.user_login", "?:users.timestamp", "?:users.user_type", "?:users.status", "?:users.firstname", "?:users.lastname", "?:users.email", "?:users.company", "?:users.company_id", "?:companies.company as company_name"); // Define sort fields $sortings = array('id' => "?:users.user_id", 'username' => "?:users.user_login", 'email' => "?:users.email", 'name' => array("?:users.lastname", "?:users.firstname"), 'date' => "?:users.timestamp", 'type' => "?:users.user_type", 'status' => "?:users.status", 'company' => "company_name"); $directions = array('asc' => 'asc', 'desc' => 'desc'); $condition = $join = $group = ''; $group .= " GROUP BY ?:users.user_id"; if (isset($params['company']) && fn_string_no_empty($params['company'])) { $condition .= db_quote(" AND ?:users.company LIKE ?l", "%" . trim($params['company']) . "%"); } if (isset($params['name']) && fn_string_no_empty($params['name'])) { $arr = fn_explode(' ', $params['name']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND (?:users.firstname LIKE ?l AND ?:users.lastname LIKE ?l)", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:users.firstname LIKE ?l OR ?:users.lastname LIKE ?l)", "%" . trim($params['name']) . "%", "%" . trim($params['name']) . "%"); } } if (isset($params['user_login']) && fn_string_no_empty($params['user_login'])) { $condition .= db_quote(" AND ?:users.user_login LIKE ?l", "%" . trim($params['user_login']) . "%"); } if (!empty($params['tax_exempt'])) { $condition .= db_quote(" AND ?:users.tax_exempt = ?s", $params['tax_exempt']); } if (isset($params['usergroup_id']) && $params['usergroup_id'] != ALL_USERGROUPS) { if (!empty($params['usergroup_id'])) { $join .= db_quote(" LEFT JOIN ?:usergroup_links ON ?:usergroup_links.user_id = ?:users.user_id AND ?:usergroup_links.usergroup_id = ?i", $params['usergroup_id']); $condition .= " AND ?:usergroup_links.status = 'A'"; } else { $join .= " LEFT JOIN ?:usergroup_links ON ?:usergroup_links.user_id = ?:users.user_id AND ?:usergroup_links.status = 'A'"; $condition .= " AND ?:usergroup_links.user_id IS NULL"; } } if (!empty($params['status'])) { $condition .= db_quote(" AND ?:users.status = ?s", $params['status']); } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:users.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (isset($params['address']) && fn_string_no_empty($params['address'])) { $condition .= db_quote(" AND (?:user_profiles.b_address LIKE ?l OR ?:user_profiles.s_address LIKE ?l)", "%" . trim($params['address']) . "%", "%" . trim($params['address']) . "%"); } if (isset($params['zipcode']) && fn_string_no_empty($params['zipcode'])) { $condition .= db_quote(" AND (?:user_profiles.b_zipcode LIKE ?l OR ?:user_profiles.s_zipcode LIKE ?l)", "%" . trim($params['zipcode']) . "%", "%" . trim($params['zipcode']) . "%"); } if (!empty($params['country'])) { $condition .= db_quote(" AND (?:user_profiles.b_country LIKE ?l OR ?:user_profiles.s_country LIKE ?l)", "%{$params['country']}%", "%{$params['country']}%"); } if (isset($params['state']) && fn_string_no_empty($params['state'])) { $condition .= db_quote(" AND (?:user_profiles.b_state LIKE ?l OR ?:user_profiles.s_state LIKE ?l)", "%" . trim($params['state']) . "%", "%" . trim($params['state']) . "%"); } if (isset($params['city']) && fn_string_no_empty($params['city'])) { $condition .= db_quote(" AND (?:user_profiles.b_city LIKE ?l OR ?:user_profiles.s_city LIKE ?l)", "%" . trim($params['city']) . "%", "%" . trim($params['city']) . "%"); } if (!empty($params['user_type'])) { $condition .= db_quote(' AND ?:users.user_type = ?s', $params['user_type']); } if (!empty($params['user_id'])) { $condition .= db_quote(' AND ?:users.user_id IN (?n)', $params['user_id']); } if (!empty($params['exclude_user_types'])) { $condition .= db_quote(" AND ?:users.user_type NOT IN (?a)", $params['exclude_user_types']); } if (defined('COMPANY_ID')) { if (empty($params['user_type']) || !empty($params['user_type']) && $params['user_type'] == 'C' || !empty($params['exclude_user_types']) && !in_array('C', $params['exclude_user_types'])) { $_cond = db_quote("(?:users.user_type = 'A' && ?:users.company_id = ?i)", COMPANY_ID); $company_customers = db_get_fields("SELECT user_id FROM ?:orders WHERE company_id = ?i", COMPANY_ID); if ($company_customers) { $_cond = db_quote("((?:users.user_type = 'C' && ?:users.user_id IN (?n)) OR {$_cond})", $company_customers); } $condition .= " AND {$_cond} "; } else { $condition .= fn_get_company_condition('?:users.company_id'); } } if (!empty($params['p_ids']) || !empty($params['product_view_id'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; if (empty($params['product_view_id'])) { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", $arr); } else { $condition .= db_quote(" AND ?:order_details.product_id IN (?n)", db_get_fields(fn_get_products(array('view_id' => $params['product_view_id'], 'get_query' => true)))); } $join .= db_quote(" LEFT JOIN ?:orders ON ?:orders.user_id = ?:users.user_id LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:orders.order_id"); } if (defined('RESTRICTED_ADMIN')) { // FIXME: NOT GOOD $condition .= db_quote(" AND (?:users.user_type != 'A' || (?:users.user_type = 'A' AND ?:users.user_id = ?i))", $auth['user_id']); } $active_user_types = fn_get_user_types(); $condition .= db_quote(" AND ?:users.user_type IN(?a)", array_keys($active_user_types)); $join .= db_quote(" LEFT JOIN ?:user_profiles ON ?:user_profiles.user_id = ?:users.user_id"); $join .= db_quote(" LEFT JOIN ?:companies ON ?:companies.company_id = ?:users.company_id"); fn_set_hook('get_users', $params, $fields, $sortings, $condition, $join); 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 = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; // Paginate search results $limit = ''; if (!empty($items_per_page)) { $total = db_get_field("SELECT COUNT(DISTINCT(?:users.user_id)) FROM ?:users {$join} WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total, $items_per_page); } $users = db_get_array("SELECT " . implode(', ', $fields) . " FROM ?:users {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}"); return array($users, $params); }
function fn_get_events($params) { // Init filter $params = fn_init_view('events', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // Define fields that should be retrieved $fields = array('*'); $condition = $join = ''; if (!empty($params['type'])) { $condition .= db_quote(" AND type IN (?a)", $params['type']); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (start_date >= ?i AND end_date <= ?i)", $params['time_from'], $params['time_to']); } if (isset($params['owner']) && fn_string_no_empty($params['owner'])) { $condition .= db_quote(" AND (owner LIKE ?l OR ?:giftreg_events.email LIKE ?l)", "%" . trim($params['owner']) . "%", "%" . trim($params['owner']) . "%"); } if (isset($params['title']) && fn_string_no_empty($params['title'])) { $condition .= db_quote(" AND title LIKE ?l", "%" . trim($params['title']) . "%"); } if (!empty($params['type'])) { $condition .= db_quote(" AND type IN (?a)", $params['type']); } if (!empty($params['status'])) { $condition .= db_quote(" AND status IN (?a)", $params['status']); } if (isset($params['subscriber']) && fn_string_no_empty($params['subscriber'])) { $join .= " INNER JOIN ?:giftreg_event_subscribers ON ?:giftreg_event_subscribers.event_id = ?:giftreg_events.event_id"; $condition .= db_quote(" AND (?:giftreg_event_subscribers.name LIKE ?l OR ?:giftreg_event_subscribers.email LIKE ?l)", "%" . trim($params['subscriber']) . "%", "%" . trim($params['subscriber']) . "%"); } if (!empty($params['search_fields'])) { $_cond = array(); $total_hits = 0; foreach ($params['search_fields'] as $f_id => $f_val) { $_condition = array(); if (substr_count($f_value, '/') == 2) { // FIXME: it's date field $_condition[] = db_quote("?:giftreg_event_fields.value = ?s", fn_parse_date($f_val)); } else { $_condition[] = db_quote("?:giftreg_event_fields.value LIKE ?l", "%{$f_val}%"); } if (!empty($f_val)) { $total_hits++; $_cond[] = db_quote("(?:giftreg_event_fields.field_id = ?i AND ", $f_id) . implode(" AND ", $_condition) . ')'; } } if (!empty($_cond)) { $cache_field_search = db_get_fields("SELECT event_id, COUNT(event_id) as cnt FROM ?:giftreg_event_fields WHERE (" . implode(' OR ', $_cond) . ") GROUP BY event_id HAVING cnt = {$total_hits}"); $condition .= db_quote(" AND event_id IN (?n)", $cache_field_search); } } if (!empty($params['today_events'])) { $condition .= db_quote("AND (start_date <= ?i AND end_date > ?i)", TIME, TIME); } $total = db_get_field("SELECT COUNT(*) FROM ?:giftreg_events {$join} WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total, Registry::get('settings.Appearance.' . (AREA == 'A' ? 'admin_' : '') . 'elements_per_page')); // FIXME $events = db_get_array("SELECT " . implode(',', $fields) . " FROM ?:giftreg_events {$join} WHERE 1 {$condition} ORDER BY start_date ASC {$limit}"); return array($events, $params); }
function fn_get_recurring_subscriptions($params, $additional_data = array()) { // Init filter $params = fn_init_view('subscriptions', $params); $params['page'] = empty($params['page']) ? 1 : $params['page']; $fields = array('?:recurring_subscriptions.*'); // Define sort fields $sortings = array('subscription_id' => 'subscription_id', 'order_id' => 'order_id', 'status' => 'status', 'customer' => array('lastname', 'firstname'), 'email' => 'email', 'date' => 'timestamp', 'price' => 'price', 'last_paid' => 'last_timestamp', 'duration' => 'duration'); $directions = array('asc' => 'asc', 'desc' => 'desc'); 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 = (is_array($sortings[$params['sort_by']]) ? implode(' ' . $directions[$params['sort_order']] . ', ', $sortings[$params['sort_by']]) : $sortings[$params['sort_by']]) . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $condition = $join = $group = ''; if (isset($params['cname']) && fn_string_no_empty($params['cname'])) { $arr = fn_explode(' ', $params['cname']); foreach ($arr as $k => $v) { if (!fn_string_no_empty($v)) { unset($arr[$k]); } } if (sizeof($arr) == 2) { $condition .= db_quote(" AND ?:recurring_subscriptions.firstname LIKE ?l AND ?:recurring_subscriptions.lastname LIKE ?l", "%" . array_shift($arr) . "%", "%" . array_shift($arr) . "%"); } else { $condition .= db_quote(" AND (?:recurring_subscriptions.firstname LIKE ?l OR ?:recurring_subscriptions.lastname LIKE ?l)", "%" . trim($params['cname']) . "%", "%" . trim($params['cname']) . "%"); } } if (isset($params['email']) && fn_string_no_empty($params['email'])) { $condition .= db_quote(" AND ?:recurring_subscriptions.email LIKE ?l", "%" . trim($params['email']) . "%"); } if (isset($params['price_from']) && fn_is_numeric($params['price_from'])) { $condition .= db_quote(" AND ?:recurring_subscriptions.price >= ?d", fn_convert_price($params['price_from'])); } if (isset($params['price_to']) && fn_is_numeric($params['price_to'])) { $condition .= db_quote(" AND ?:recurring_subscriptions.price <= ?d", fn_convert_price($params['price_to'])); } if (!empty($params['status'])) { $condition .= db_quote(' AND ?:recurring_subscriptions.status = ?s', $params['status']); } if (!empty($params['order_id'])) { $condition .= db_quote(' AND ?:recurring_subscriptions.order_id IN (?n)', $params['order_id']); } if (!empty($params['plan_id'])) { $condition .= db_quote(' AND ?:recurring_subscriptions.plan_id IN (?n)', $params['plan_id']); } if (!empty($params['p_ids'])) { $arr = strpos($params['p_ids'], ',') !== false || !is_array($params['p_ids']) ? explode(',', $params['p_ids']) : $params['p_ids']; $condition .= db_quote(" AND ?:order_details.product_id IN (?a)", $arr); $join .= " LEFT JOIN ?:order_details ON ?:order_details.order_id = ?:recurring_subscriptions.order_id AND ?:order_details.extra LIKE '%recurring_plan_id%'"; $group .= " GROUP BY ?:recurring_subscriptions.subscription_id HAVING COUNT(?:recurring_subscriptions.subscription_id) >= " . count($arr); } if (!empty($params['period_type']) && !empty($params['period']) && $params['period'] != 'A') { if ($params['period_type'] == 'D') { $timestamp = 'timestamp'; } elseif ($params['period_type'] == 'L') { $timestamp = 'last_timestamp'; } else { $timestamp = 'end_timestamp'; } list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:recurring_subscriptions.{$timestamp} >= ?i AND ?:recurring_subscriptions.{$timestamp} <= ?i)", $params['time_from'], $params['time_to']); } if (!empty($additional_data['plan_options'])) { $join .= " LEFT JOIN ?:recurring_plans ON ?:recurring_plans.plan_id = ?:recurring_subscriptions.plan_id"; $fields[] = '?:recurring_plans.allow_unsubscribe'; $fields[] = '?:recurring_plans.allow_change_duration'; } if (!empty($additional_data['user_id'])) { $condition .= db_quote(" AND ?:recurring_subscriptions.user_id = ?i AND ?:recurring_subscriptions.status = 'A'", $additional_data['user_id']); } elseif (!empty($additional_data['order_ids'])) { $condition .= db_quote(" AND FIND_IN_SET(?:recurring_subscriptions.order_id, ?s) AND ?:recurring_subscriptions.status = 'A'", implode(',', $additional_data['order_ids'])); } $total = db_get_field("SELECT DISTINCT(COUNT(?:recurring_subscriptions.subscription_id)) FROM ?:recurring_subscriptions {$join} WHERE 1 {$condition} {$group}"); $items_per_page = Registry::get('settings.Appearance.admin_elements_per_page'); $limit = fn_paginate($params['page'], $total, $items_per_page); $subscriptions = db_get_hash_array("SELECT " . implode(', ', $fields) . " FROM ?:recurring_subscriptions {$join} WHERE 1 {$condition} {$group} ORDER BY {$sorting} {$limit}", 'subscription_id'); return array($subscriptions, $params); }
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); }
function fn_get_discussions($params) { // Init filter $params = fn_init_view('discussion', $params); // Set default values to input params $params['page'] = empty($params['page']) ? 1 : $params['page']; // Define fields that should be retrieved $fields = array('?:discussion_posts.*', '?:discussion_messages.message', '?:discussion_rating.rating_value', '?:discussion.*'); // Define sort fields $sortings = array('object' => "?:discussion.object_type", 'name' => "?:discussion_posts.name", 'ip_address' => "?:discussion_posts.ip_address", 'timestamp' => "?:discussion_posts.timestamp", 'status' => "?:discussion_posts.status", 'date' => "?:orders.timestamp", 'total' => "?:orders.total"); $directions = array('asc' => 'asc', 'desc' => 'desc'); 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'] = 'timestamp'; } $sort = $sortings[$params['sort_by']] . " " . $directions[$params['sort_order']]; // Reverse sorting (for usage in view) $params['sort_order'] = $params['sort_order'] == 'asc' ? 'desc' : 'asc'; $condition = $join = ''; if (isset($params['name']) && fn_string_no_empty($params['name'])) { $condition .= db_quote(" AND ?:discussion_posts.name LIKE ?l", "%" . trim($params['name']) . "%"); } if (isset($params['message']) && fn_string_no_empty($params['message'])) { $condition .= db_quote(" AND ?:discussion_messages.message LIKE ?l", "%" . trim($params['message']) . "%"); } if (!empty($params['type'])) { $condition .= db_quote(" AND ?:discussion.type = ?s", $params['type']); } if (!empty($params['status'])) { $condition .= db_quote(" AND ?:discussion_posts.status = ?s", $params['status']); } if (!empty($params['post_id'])) { $condition .= db_quote(" AND ?:discussion_posts.post_id = ?i", $params['post_id']); } if (isset($params['ip_address']) && fn_string_no_empty($params['ip_address'])) { $condition .= db_quote(" AND ?:discussion_posts.ip_address = ?s", trim($params['ip_address'])); } if (!empty($params['rating_value'])) { $condition .= db_quote(" AND ?:discussion_rating.rating_value = ?i", $params['rating_value']); } if (!empty($params['object_type'])) { $condition .= db_quote(" AND ?:discussion.object_type = ?s", $params['object_type']); } if (!empty($params['period']) && $params['period'] != 'A') { list($params['time_from'], $params['time_to']) = fn_create_periods($params); $condition .= db_quote(" AND (?:discussion_posts.timestamp >= ?i AND ?:discussion_posts.timestamp <= ?i)", $params['time_from'], $params['time_to']); } $join .= " INNER JOIN ?:discussion ON ?:discussion.thread_id = ?:discussion_posts.thread_id"; $join .= " INNER JOIN ?:discussion_messages ON ?:discussion_messages.post_id = ?:discussion_posts.post_id"; $join .= " INNER JOIN ?:discussion_rating ON ?:discussion_rating.post_id = ?:discussion_posts.post_id"; $total = db_get_field("SELECT COUNT(*) FROM ?:discussion_posts {$join} WHERE 1 {$condition}"); $limit = fn_paginate($params['page'], $total, Registry::get('settings.Appearance.admin_elements_per_page')); $posts = db_get_array("SELECT " . implode(',', $fields) . " FROM ?:discussion_posts {$join} WHERE 1 {$condition} ORDER BY {$sort} {$limit}"); return array($posts, $params); }