function testExpandNumberRange()
{
$this->assertEquals('9569', expand_number_range('9569'));
$this->assertEquals('9568,9569', expand_number_range('9568,9569'));
$this->assertEquals('1,2,3,4,5,7', expand_number_range('1-5,7'));
}
/**
* NOTE: PRIVATE FUNCTION.
Will return the FROM and WHERE clauses for a selection from the item table.
If $owner_id defined, will limit to only items owned by owner_id
If $s_item_type defined, will limit to only items of that type.
If $category defined, will limit to only items of that category.
If $letter defined will limit to item.title starting with that letter.
If $interest_level defined will limit to items with that interest level or higher.
@param $HTTP_VARS['...'] variables supported:
owner_id, s_item_type, s_item_type[], s_item_type_group, title, title_match, category,
rating, attribute_type, lookup_attribute_val, attribute_val, attr_match,
update_on, datetimemask, update_on_days, letter, start_item_id
s_status_type[], status_comment, not_s_status_type[], interest_level
*/
function from_and_where_clause($HTTP_VARS, $column_display_config_rs = NULL, $query_type = 'LISTING')
{
// For checking whether count (DISTINCT ...) is supported, and thus
// whether we have to do any special processing!
$from_r[] = 'item i';
$from_r[] = 'item_instance ii';
$where_r[] = 'ii.item_id = i.id';
// only parent items should ever be listed.
//
// Owner restriction
//
if (strlen($HTTP_VARS['owner_id']) > 0) {
$where_r[] = 'ii.owner_id = \'' . $HTTP_VARS['owner_id'] . '\'';
} else {
if (strlen($HTTP_VARS['not_owner_id']) > 0) {
//For not showing current user items.
$where_r[] = 'ii.owner_id <> \'' . $HTTP_VARS['not_owner_id'] . '\'';
}
}
//
// Item Type / Item Type group restriction
//
if (!is_array($HTTP_VARS['s_item_type']) && strlen($HTTP_VARS['s_item_type']) > 0) {
$where_r[] = 'i.s_item_type = \'' . $HTTP_VARS['s_item_type'] . '\'';
} else {
if (strlen($HTTP_VARS['s_item_type_group']) > 0) {
$from_r[] = 's_item_type_group_rltshp sitgr';
$where_r[] = 'sitgr.s_item_type = i.s_item_type';
$where_r[] = 'sitgr.s_item_type_group = \'' . $HTTP_VARS['s_item_type_group'] . '\'';
} else {
if (is_not_empty_array($HTTP_VARS['s_item_type'])) {
$where_r[] = 'i.s_item_type IN(' . format_sql_in_clause($HTTP_VARS['s_item_type']) . ')';
}
}
}
$from_r[] = 's_status_type sst';
$where_r[] = 'sst.s_status_type = ii.s_status_type';
//
// Status Type restriction
//
if (is_not_empty_array($HTTP_VARS['s_status_type'])) {
$where_r[] = 'sst.s_status_type IN(' . format_sql_in_clause($HTTP_VARS['s_status_type']) . ')';
} else {
if ($HTTP_VARS['s_status_type'] != 'ALL' && strlen($HTTP_VARS['s_status_type']) > 0) {
$where_r[] = 'sst.s_status_type = \'' . $HTTP_VARS['s_status_type'] . '\'';
}
}
// no need for such a restriction if current user is item admin
if (!is_user_granted_permission(PERM_ITEM_ADMIN)) {
$where_r[] = "( sst.hidden_ind = 'N' OR ii.owner_id = '" . get_opendb_session_var('user_id') . "') ";
}
//
// User and Status type restriction
//
if (strcmp($HTTP_VARS['owner_id'], get_opendb_session_var('user_id')) !== 0) {
// not current user
$from_r[] = 'user u';
$where_r[] = 'u.user_id = ii.owner_id';
$where_r[] = 'u.active_ind = \'Y\'';
}
//
// Status Comment restriction
//
if (strlen($HTTP_VARS['status_comment']) > 0) {
// Escape only the single quote!
$HTTP_VARS['status_comment'] = str_replace("'", "\\'", $HTTP_VARS['status_comment']);
if ($HTTP_VARS['status_comment_match'] != 'exact') {
$parser = new BooleanParser();
$statements = $parser->parseBooleanStatement($HTTP_VARS['status_comment']);
if (is_array($statements)) {
$where_r[] = build_boolean_clause($statements, 'ii.status_comment', $HTTP_VARS['status_comment_match'], 'AND', $HTTP_VARS['status_comment_case']);
}
} else {
if (is_null($HTTP_VARS['status_comment_case'])) {
$where_r[] = 'ii.status_comment = \'' . $HTTP_VARS['status_comment'] . '\'';
} else {
$where_r[] = 'BINARY ii.status_comment = \'' . $HTTP_VARS['status_comment'] . '\'';
}
}
}
//
// Title restriction
//
if (strlen($HTTP_VARS['title']) > 0) {
// Escape only the single quote!
$HTTP_VARS['title'] = str_replace("'", "\\'", $HTTP_VARS['title']);
if ($HTTP_VARS['title_match'] != 'exact') {
$parser = new BooleanParser();
$statements = $parser->parseBooleanStatement($HTTP_VARS['title']);
if (is_array($statements)) {
$where_r[] = build_boolean_clause($statements, 'i.title', $HTTP_VARS['title_match'], 'AND', $HTTP_VARS['title_case']);
}
} else {
if (is_null($HTTP_VARS['title_case'])) {
$where_r[] = 'i.title = \'' . $HTTP_VARS['title'] . '\'';
} else {
$where_r[] = 'BINARY i.title = \'' . $HTTP_VARS['title'] . '\'';
}
}
} else {
if (strlen($HTTP_VARS['letter']) > 0) {
// Numeric match.
if ($HTTP_VARS['letter'] == '#') {
$where_r[] = 'ASCII(LEFT(title,1)) BETWEEN ASCII(\'0\') AND ASCII(\'9\')';
} else {
$where_r[] = 'UPPER(LEFT(i.title,1)) = \'' . strtoupper($HTTP_VARS['letter']) . '\'';
}
}
}
//
// Last Updated support
//
if (strlen($HTTP_VARS['update_on']) > 0) {
if (strlen($HTTP_VARS['datetimemask']) > 0) {
$timestamp = get_timestamp_for_datetime($HTTP_VARS['update_on'], $HTTP_VARS['datetimemask']);
if ($timestamp !== FALSE) {
$where_r[] = 'ii.update_on >= FROM_UNIXTIME(' . $timestamp . ')';
} else {
// by default get items from 1 day ago, if update_on can not be parsed correctly.
$where_r[] = 'TO_DAYS(ii.update_on) >= (TO_DAYS(now())-1)';
}
} else {
$where_r[] = 'ii.update_on >= \'' . $HTTP_VARS['update_on'] . '\'';
}
} else {
if (is_numeric($HTTP_VARS['update_on_days'])) {
// GIve us all records updated in the last however many days.
$where_r[] = 'TO_DAYS(ii.update_on) >= (TO_DAYS(now())-' . $HTTP_VARS['update_on_days'] . ')';
}
}
//
// Item Attribute listing/restriction
//
if (is_array($column_display_config_rs)) {
for ($i = 0; $i < count($column_display_config_rs); $i++) {
if ($column_display_config_rs[$i]['column_type'] == 's_attribute_type') {
if ($column_display_config_rs[$i]['search_attribute_ind'] != 'y') {
// either LISTING or COUNT
if ($query_type != 'COUNT') {
$left_join = 'LEFT JOIN item_attribute ia' . $i . ' ON ' . 'ia' . $i . '.item_id = i.id AND (ia' . $i . '.instance_no = 0 OR ia' . $i . '.instance_no = ii.instance_no) AND ia' . $i . '.s_attribute_type = \'' . $column_display_config_rs[$i]['s_attribute_type'] . '\' AND ia' . $i . '.attribute_no = 1';
// So we can work out which search attribute types to display
if (is_numeric($column_display_config_rs[$i]['order_no'])) {
$left_join .= ' AND ia' . $i . '.order_no = ' . $column_display_config_rs[$i]['order_no'];
}
$left_join_from_r[] = $left_join;
}
} else {
// search attribute
$from_r[] = 'item_attribute ia' . $i;
// now do the where clause.
$where_r[] = 'ia' . $i . '.item_id = i.id AND (ia' . $i . '.instance_no = 0 OR ia' . $i . '.instance_no = ii.instance_no) AND ia' . $i . '.s_attribute_type = \'' . $column_display_config_rs[$i]['s_attribute_type'] . '\'';
// AND ia'.$i.'.attribute_no = 1';
if (strlen($column_display_config_rs[$i]['attribute_val']) > 0 && $column_display_config_rs[$i]['attribute_val'] != '%' && $column_display_config_rs[$i]['attr_match'] != 'exact') {
$parser = new BooleanParser();
$statements = $parser->parseBooleanStatement(strtoupper(str_replace("'", "\\'", $column_display_config_rs[$i]['attribute_val'])));
if (is_array($statements)) {
if ($column_display_config_rs[$i]['lookup_attribute_ind'] == 'Y') {
$where_r[] = build_boolean_clause($statements, 'ia' . $i . '.lookup_attribute_val', 'plain', 'AND', $HTTP_VARS['attr_case']);
} else {
$where_r[] = build_boolean_clause($statements, 'ia' . $i . '.attribute_val', $column_display_config_rs[$i]['attr_match'], 'AND', $HTTP_VARS['attr_case']);
}
}
} else {
if (strlen($column_display_config_rs[$i]['lookup_attribute_val']) > 0 && $column_display_config_rs[$i]['lookup_attribute_val'] != '%' && $column_display_config_rs[$i]['lookup_attribute_ind'] == 'Y') {
$value = str_replace("'", "\\'", $column_display_config_rs[$i]['lookup_attribute_val']);
$where_r[] = 'ia' . $i . '.lookup_attribute_val = \'' . str_replace('\\_', '_', $value) . '\'';
} else {
if (strlen($column_display_config_rs[$i]['attribute_val']) > 0 && $column_display_config_rs[$i]['attribute_val'] != '%') {
if (starts_with($column_display_config_rs[$i]['attribute_val'], '"') && ends_with($column_display_config_rs[$i]['attribute_val'], '"')) {
$column_display_config_rs[$i]['attribute_val'] = substr($column_display_config_rs[$i]['attribute_val'], 1, -1);
}
$value = strtoupper(str_replace("'", "\\'", $column_display_config_rs[$i]['attribute_val']));
$where_r[] = 'UPPER(ia' . $i . '.attribute_val) = \'' . str_replace('\\_', '_', $value) . '\'';
}
}
}
if (strlen($HTTP_VARS['attr_update_on']) > 0) {
if (strlen($HTTP_VARS['datetimemask']) > 0) {
$timestamp = get_timestamp_for_datetime($HTTP_VARS['attr_update_on'], $HTTP_VARS['datetimemask']);
if ($timestamp !== FALSE) {
$where_r[] = 'ia' . $i . '.update_on >= FROM_UNIXTIME(' . $timestamp . ')';
} else {
// by default get items from 1 day ago, if update_on can not be parsed correctly.
$where_r[] = 'TO_DAYS(ia' . $i . '.update_on) >= (TO_DAYS(now())-1)';
}
} else {
$where_r[] = 'ia' . $i . '.update_on >= \'' . $HTTP_VARS['attr_update_on'] . '\'';
}
} else {
if (is_numeric($HTTP_VARS['attr_update_on_days'])) {
// GIve us all records updated in the last however many days.
$where_r[] = 'TO_DAYS(ia' . $i . '.update_on) >= (TO_DAYS(now())-' . $HTTP_VARS['attr_update_on_days'] . ')';
}
}
}
} else {
if ($column_display_config_rs[$i]['column_type'] == 's_field_type') {
if ($column_display_config_rs[$i]['s_field_type'] == 'CATEGORY') {
$from_r[] = 's_item_attribute_type catsiat';
$from_r[] = 's_attribute_type catsat';
$where_r[] = 'catsiat.s_item_type = i.s_item_type AND catsat.s_attribute_type = catsiat.s_attribute_type AND catsat.s_field_type = \'CATEGORY\'';
$left_join_clause = 'LEFT JOIN item_attribute catia ON ' . 'catia.item_id = i.id AND (catia.instance_no = 0 OR catia.instance_no = ii.instance_no) AND catia.s_attribute_type = catsiat.s_attribute_type AND catia.order_no = catsiat.order_no';
if (strlen($HTTP_VARS['category']) > 0 || strcasecmp($HTTP_VARS['attr_match'], 'category') === 0 && strlen($HTTP_VARS['attribute_val']) > 0) {
// Support specifying $attribute_val for $category where $attr_match=="category"!
// If item_type && item_type_group are not set!
if (strlen($HTTP_VARS['attribute_type']) > 0 && !is_array($HTTP_VARS['s_item_type']) && strlen($HTTP_VARS['s_item_type']) == 0 && strlen($HTTP_VARS['s_item_type_group']) == 0) {
$where_r[] = 'catsat.s_attribute_type = \'' . $HTTP_VARS['attribute_type'] . '\'';
}
// Escape single quotes only.
$value = strtoupper(str_replace("'", "\\'", ifempty($HTTP_VARS['category'], $HTTP_VARS['attribute_val'])));
$where_r[] = 'UPPER(catia.lookup_attribute_val) = \'' . str_replace('\\_', '_', $value) . '\'';
} else {
$left_join_clause .= ' AND catia.attribute_no = 1';
}
$left_join_from_r[] = $left_join_clause;
} else {
if ($column_display_config_rs[$i]['s_field_type'] == 'INTEREST') {
// can only restrict interest level if its displayed as a column
if (strlen($HTTP_VARS['interest_level']) > 0) {
$where_r[] = "it.item_id = ii.item_id AND it.instance_no = ii.instance_no AND it.user_id = '" . get_opendb_session_var('user_id') . "'" . " AND it.level >= " . $HTTP_VARS['interest_level'];
$from_r[] = "user_item_interest it";
} else {
$left_join_from_r[] = "LEFT JOIN user_item_interest it ON it.item_id = i.id AND it.instance_no = ii.instance_no AND it.user_id = '" . get_opendb_session_var('user_id') . "'";
}
}
}
}
}
}
}
// If attribute_val specified without a attribute_type, then do a loose join to item_attribute table,
// only on attribute_val column.
if (strlen($HTTP_VARS['attribute_type']) == 0 && (strlen($HTTP_VARS['attribute_val']) > 0 || strlen($HTTP_VARS['attr_update_on']) > 0 || strlen($HTTP_VARS['attr_update_on_days']) > 0)) {
$from_r[] = 'item_attribute ia';
// now do the where clause.
$where_r[] = 'ia.item_id = i.id ';
//AND ia.attribute_no = 1';
if ($HTTP_VARS['attr_match'] != 'exact') {
$parser = new BooleanParser();
$statements = $parser->parseBooleanStatement(strtoupper(str_replace("'", "\\'", $HTTP_VARS['attribute_val'])));
if (is_array($statements)) {
if (is_lookup_attribute_type($HTTP_VARS['attribute_type'])) {
$where_r[] = build_boolean_clause($statements, 'ia.lookup_attribute_val', 'plain', 'AND', $HTTP_VARS['attr_case']);
} else {
$where_r[] = build_boolean_clause($statements, 'ia.attribute_val', $HTTP_VARS['attr_match'], 'AND', $HTTP_VARS['attr_case']);
}
}
} else {
// attr_match = 'exact'
if (is_lookup_attribute_type($HTTP_VARS['attribute_type'])) {
$value = str_replace("'", "\\'", $HTTP_VARS['attribute_val']);
$where_r[] = 'ia.lookup_attribute_val = \'' . str_replace('\\_', '_', $value) . '\'';
} else {
$value = str_replace("'", "\\'", $HTTP_VARS['attribute_val']);
if (is_null($HTTP_VARS['attr_case'])) {
$where_r[] = '( ia.attribute_val = \'' . str_replace('\\_', '_', $value) . '\' OR ' . 'ia.attribute_val LIKE \'% ' . $value . ' %\' OR ' . 'ia.attribute_val LIKE \'' . $value . ' %\' OR ' . 'ia.attribute_val LIKE \'% ' . $value . '\')';
} else {
$where_r[] = '( BINARY ia.attribute_val = \'' . str_replace('\\_', '_', $value) . '\' OR ' . 'ia.attribute_val LIKE BINARY \'% ' . $value . ' %\' OR ' . 'ia.attribute_val LIKE BINARY \'' . $value . ' %\' OR ' . 'ia.attribute_val LIKE BINARY \'% ' . $value . '\')';
}
}
}
if (strlen($HTTP_VARS['attr_update_on']) > 0) {
if (strlen($HTTP_VARS['datetimemask']) > 0) {
$timestamp = get_timestamp_for_datetime($HTTP_VARS['attr_update_on'], $HTTP_VARS['datetimemask']);
if ($timestamp !== FALSE) {
$where_r[] = 'ia.update_on >= FROM_UNIXTIME(' . $timestamp . ')';
} else {
// by default get items from 1 day ago, if update_on can not be parsed correctly.
$where_r[] = 'TO_DAYS(ia.update_on) >= (TO_DAYS(now())-1)';
}
} else {
$where_r[] = 'ia.update_on >= \'' . $HTTP_VARS['attr_update_on'] . '\'';
}
} else {
if (is_numeric($HTTP_VARS['attr_update_on_days'])) {
// GIve us all records updated in the last however many days.
$where_r[] = 'TO_DAYS(ia.update_on) >= (TO_DAYS(now())-' . $HTTP_VARS['attr_update_on_days'] . ')';
}
}
}
//
// Review restrictions
//
if (strlen($HTTP_VARS['rating']) > 0) {
$where_r[] = 'r.item_id = i.id AND r.rating >= ' . $HTTP_VARS['rating'];
$from_r[] = 'review r';
}
//
// Item ID range restriction (Used by Import script)
//
if (strlen($HTTP_VARS['item_id_range']) > 0) {
$where_r[] = 'i.id IN (' . expand_number_range($HTTP_VARS['item_id_range']) . ')';
}
//
// Now build the SQL query
//
if (is_array($from_r)) {
$from_clause = '';
for ($i = 0; $i < count($from_r); $i++) {
if (strlen($from_clause) > 0) {
$from_clause .= ', ';
}
$from_clause .= $from_r[$i];
}
$query .= 'FROM (' . $from_clause . ') ';
}
if (is_array($left_join_from_r)) {
$left_join_from_clause = '';
for ($i = 0; $i < count($left_join_from_r); $i++) {
if (strlen($left_join_from_clause) > 0) {
$left_join_from_clause .= ' ';
}
$left_join_from_clause .= $left_join_from_r[$i];
}
$query .= $left_join_from_clause . ' ';
}
if (is_array($where_r)) {
$where_clause = '';
for ($i = 0; $i < count($where_r); $i++) {
if (strlen($where_clause) > 0) {
$where_clause .= ' AND ';
}
$where_clause .= $where_r[$i];
}
$query .= 'WHERE ' . $where_clause;
}
return $query;
}
