View Issue Details

IDProjectCategoryView StatusLast Update
0003494mantisbtbugtrackerpublic2004-07-08 07:29
Reporterchipux Assigned ToNarcissus  
PrioritynormalSeverityfeatureReproducibilityN/A
Status closedResolutionfixed 
Fixed in Version0.19.0a1 
Summary0003494: Filter by Date Submited and Filter by Custom Fields
Description

Patch does:

  • Enables Date Selection HTML
  • Adds Filter Logic for Date Filters
  • Adds Filter Logic for Custom Fields
  • Adds Custom Fields to Cookies
  • Adds Custom Fields to Filter Form
  • Adds Config Var to Enable Custom Field Filters
  • Adds Config Var to Set Number of Custom Fields Per Row
  • Bumps Filter Cookie Version
  • Includes a few MySQL Table Optimizations
Additional Information
  • Needs Translations for "Start Date" / "End Date"

Patch Supersedes Bug 0003492 "Better Indexes on SQL"

http://force-elite.com/~chip/patches/mantis/super-searching.patch

TagsNo tags attached.
Attached Files
super-searching.patch (15,637 bytes)   
Index: config_defaults_inc.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/config_defaults_inc.php,v
retrieving revision 1.130
diff -u -r1.130 config_defaults_inc.php
--- config_defaults_inc.php	1 Sep 2003 14:06:57 -0000	1.130
+++ config_defaults_inc.php	7 Jan 2004 19:55:57 -0000
@@ -779,7 +787,7 @@
 	$g_cookie_path			= '/';
 	$g_cookie_domain		= '';
 	# cookie version for view_all_page
-	$g_cookie_version		= 'v4';
+	$g_cookie_version		= 'v5';
 
 	# --- cookie prefix ---------------
 	# set this to a unique identifier.  No spaces.
@@ -790,6 +798,13 @@
 	$g_project_cookie		= $g_cookie_prefix.'_PROJECT_COOKIE';
 	$g_view_all_cookie		= $g_cookie_prefix.'_VIEW_ALL_COOKIE';
 	$g_manage_cookie		= $g_cookie_prefix.'_MANAGE_COOKIE';
+
+	#######################################
+	# Mantis Filter Variables
+	#######################################
+	$g_filter_by_custom_fields = ON;
+	$g_filter_by_date = ON;
+	$g_filter_custom_fields_per_row = 10;
 
 	#######################################
 	# Mantis Database Table Variables
Index: view_all_inc.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/view_all_inc.php,v
retrieving revision 1.129
diff -u -r1.129 view_all_inc.php
--- view_all_inc.php	18 Oct 2003 16:34:59 -0000	1.129
+++ view_all_inc.php	7 Jan 2004 19:55:57 -0000
@@ -22,6 +22,8 @@
 
 	$t_checkboxes_exist = false;
 
+	$t_project_id = helper_get_current_project();
+
 	$t_icon_path = config_get( 'icon_path' );
 ?>
 <?php # -- ====================== FILTER FORM ========================= -- ?>
@@ -109,13 +111,78 @@
 		<input type="checkbox" name="hide_closed" <?php check_checked( $t_filter['hide_closed'], 'on' ); ?> />&nbsp;<?php echo lang_get( 'filter_closed' ); ?>
 	</td>
 </tr>
+</table>
+<?php # -- Custom Field Searching -- ?>
+<?php 
+if(ON == config_get( 'filter_by_custom_fields' )) {
+	$t_custom_fields = custom_field_get_ids($t_project_id);
+	if(sizeof($t_custom_fields) > 0) {
+		$t_per_row = config_get( 'filter_custom_fields_per_row' );
+		$t_num_rows = ceil(sizeof($t_custom_fields) / $t_per_row);
+		$t_base = 0;
+
+		for($i=0;$i< $t_num_rows; $i++) {
+			?>
+			<table class="width100" cellspacing="0">
+			<tr class="row-category2">
+			<?php
+			for($c=0;$c < $t_per_row;$c++) {
+				if(!isset($t_custom_fields[$t_base + $c]))
+					break;
+				$t_field_info = custom_field_cache_row($t_custom_fields[$t_base + $c], true);
+				echo '<td class="small-caption">' . $t_field_info['name'] . '</td>';
+			} // end for() per row
+			?>
+			</tr>
+			<tr>
+			<?php
+		
+			for($c=0;$c < $t_per_row;$c++) {
+				if(!isset($t_custom_fields[$t_base + $c]))
+					break;
+				echo '<td class="small-caption"><select name="custom_field_' . $t_custom_fields[$t_base + $c] .'">';
+				$t_distinct = custom_field_distinct_values($t_custom_fields[$t_base + $c]);
+				echo '<option value="any">any</option>';
+				echo '<option value=""></option>';
+				foreach($t_distinct as $t_item) {
+					if(strtolower($t_item) != "any" && trim($t_item) != "") {
+						echo '<option value="' .  htmlentities($t_item)  . '" '; 
+						check_selected($t_item, $t_filter['custom_fields'][$t_custom_fields[$t_base + $c]]);
+						echo '>' . $t_item  . '</option>' . "\n";
+					}
+				}
+				echo '</select></td>';
+			} // end for() per row
+			
+			?>
+			</tr>
+			</table>
+			<?php
+			$t_base += $t_per_row;
+		} // end for() num rows
+	?>
+<?php
+	}
+}
 
-
+?>
+<table class="width100" cellspacing="0">
 <?php # -- Search and Date Header Row -- ?>
 <tr class="row-category2">
 	<td class="small-caption" colspan="2"><?php echo lang_get( 'search' ) ?></td>
-	<td class="small-caption" colspan="2"><!--Start Date--></td>
-	<td class="small-caption" colspan="2"><!--End Date--></td>
+<?php
+if(ON == config_get( 'filter_by_date' )) {
+	?>
+	<td class="small-caption" colspan="2">Start Date</td>
+	<td class="small-caption" colspan="2">End Date</td>
+	<?php
+} else {
+	?>
+	<td class="small-caption" colspan="2">&nbsp;</td>
+	<td class="small-caption" colspan="2">&nbsp;</td>
+	<?php
+}
+	?>
 	<td class="small-caption" colspan="2">&nbsp;</td>
 </tr>
 
@@ -126,10 +193,11 @@
 	<td colspan="2">
 	    <input type="text" size="16" name="search" value="<?php echo $t_filter['search']; ?>" />
 	</td>
-
+<?php
+if(ON == config_get( 'filter_by_date' )) {
+?>
 	<?php # -- Start date -- ?>
 	<td class="left" colspan="2">
-	<!--
 		<select name="start_month">
 			<?php print_month_option_list( $t_filter['start_month'] ) ?>
 		</select>
@@ -139,12 +207,10 @@
 		<select name="start_year">
 			<?php print_year_option_list( $t_filter['start_year'] ) ?>
 		</select>
-	-->
 	</td>
 
 	<?php # -- End date -- ?>
 	<td class="left" colspan="2">
-	<!--
 		<select name="end_month">
 			<?php print_month_option_list( $t_filter['end_month'] ) ?>
 		</select>
@@ -154,9 +220,15 @@
 		<select name="end_year">
 			<?php print_year_option_list( $t_filter['end_year'] ) ?>
 		</select>
-	-->
 	</td>
-
+<?php
+} else {
+	?>
+	<td class="left" colspan="2">&nbsp;</td>
+	<td class="left" colspan="2">&nbsp;</td>
+	<?php
+}
+?>
 	<?php # -- SUBMIT button -- ?>
 	<td class="right" colspan="2">
 		<input type="submit" name="filter" value="<?php echo lang_get( 'filter_button' ) ?>" />
Index: view_all_set.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/view_all_set.php,v
retrieving revision 1.18
diff -u -r1.18 view_all_set.php
--- view_all_set.php	27 Feb 2003 18:31:15 -0000	1.18
+++ view_all_set.php	7 Jan 2004 19:55:57 -0000
@@ -28,10 +28,19 @@
 	$f_end_month			= gpc_get_string( 'end_month', date( 'm' ) );
 	$f_start_day			= gpc_get_string( 'start_day', 1 );
 	$f_end_day				= gpc_get_string( 'end_day', date( 'd' ) );
-	$f_start_year			= gpc_get_string( 'start_year', date( 'Y' ) );
-	$f_end_year				= gpc_get_string( 'end_year', date( 'Y' ) );
+	$f_start_year			= gpc_get_string( 'start_year', date( 'Y' ) - 1);
+	$f_end_year				= gpc_get_string( 'end_year', date( 'Y' ) + 1);
 	$f_search				= gpc_get_string( 'search', '' );
 
+	$t_custom_fields = custom_field_get_ids();
+	if(is_array($t_custom_fields) && sizeof($t_custom_fields) > 0) {
+		foreach($t_custom_fields as $t_cfid){
+			$f_custom_fields_data[$t_cfid] =  gpc_get_string( 'custom_field_' . $t_cfid, '' );
+		}
+	} else {
+		$f_custom_fields_data = array();
+	}
+
 	if ( $f_hide_closed ) {
 		$f_hide_closed = 'on';
 	}
@@ -77,11 +86,18 @@
 	16: $f_end_year
 	17: $f_search
 	18: $f_hide_resolved
+	19: $f_custom_field
 */
 	# Set new filter values.  These are stored in a cookie
 	$t_view_all_cookie = gpc_get_cookie( config_get( 'view_all_cookie' ), '' );
 	$t_setting_arr	= explode( '#', $t_view_all_cookie );
 
+	$f_custom_field = "";
+
+	foreach($f_custom_fields_data as $key => $value){
+		$f_custom_field .= "!*!$key%*%$value";
+	}
+	
 	if ( isset($t_setting_arr[5]) ) {
 		check_varset( $f_highlight_changed, $t_setting_arr[5] );
 	} else {
@@ -103,7 +119,7 @@
 									"#$f_start_month#$f_start_day".
 									"#$f_start_year#$f_end_month".
 									"#$f_end_day#$f_end_year".
-									"#$f_search#$f_hide_resolved";
+									"#$f_search#$f_hide_resolved#$f_custom_field";
 				break;
 		# Update filters
 		case '1':
@@ -116,7 +132,7 @@
 									"#$f_start_month#$f_start_day".
 									"#$f_start_year#$f_end_month".
 									"#$f_end_day#$f_end_year".
-									"#$f_search#$f_hide_resolved";
+									"#$f_search#$f_hide_resolved#$f_custom_field";
 				break;
 		# Set the sort order and direction
 		case '2':
@@ -129,7 +145,8 @@
 									"#$t_setting_arr[11]#$t_setting_arr[12]".
 									"#$t_setting_arr[13]#$t_setting_arr[14]".
 									"#$t_setting_arr[15]#$t_setting_arr[16]".
-									"#$t_setting_arr[17]#$t_setting_arr[18]";
+									"#$t_setting_arr[17]#$t_setting_arr[18]".
+									"#$t_setting_arr[19]#";
 				break;
 		# does nothing. catch all case
 		default:
@@ -142,7 +159,8 @@
 									"#$t_setting_arr[11]#$t_setting_arr[12]".
 									"#$t_setting_arr[13]#$t_setting_arr[14]".
 									"#$t_setting_arr[15]#$t_setting_arr[16]".
-									"#$t_setting_arr[17]#$t_setting_arr[18]";
+									"#$t_setting_arr[17]#$t_setting_arr[18]".
+									"#$t_setting_arr[19]#";
 	}
 
 	# set cookie values
Index: core/current_user_api.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/current_user_api.php,v
retrieving revision 1.11
diff -u -r1.11 current_user_api.php
--- core/current_user_api.php	25 Feb 2003 15:48:06 -0000	1.11
+++ core/current_user_api.php	7 Jan 2004 19:55:57 -0000
@@ -122,7 +122,19 @@
 		$t_filter['end_year']			= $t_setting_arr[16];
 		$t_filter['search']				= $t_setting_arr[17];
 		$t_filter['hide_resolved'] 		= $t_setting_arr[18];
+		$t_filter['custom_fields'] = array();
 
+		$t_custom_fields_data = explode( '!*!',$t_setting_arr[19]);
+		if(is_array($t_custom_fields_data)) {
+			foreach( $t_custom_fields_data as $t_data) {
+				$t_more_data = explode( '%*%' ,$t_data);
+				if(is_array($t_more_data))
+					$t_filter['custom_fields'][$t_more_data[0]] = html_entity_decode($t_more_data[1]); 
+			}
+		}
+		else {
+			$t_filter['custom_fields'] = '';
+		}
 		return $t_filter;
 	}
 ?>
Index: core/custom_field_api.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/custom_field_api.php,v
retrieving revision 1.20
diff -u -r1.20 custom_field_api.php
--- core/custom_field_api.php	9 Mar 2003 03:08:58 -0000	1.20
+++ core/custom_field_api.php	7 Jan 2004 19:55:58 -0000
@@ -84,6 +84,39 @@
 
 		return true;
 	}
+        # --------------------
+        # Get All Possible Values for a Field.
+        function custom_field_distinct_values( $p_field_id ) {
+                $c_field_id  = db_prepare_int( $p_field_id );
+
+                $t_custom_field_string_table = config_get( 'mantis_custom_field_string_table' );
+
+                $query = "SELECT value FROM
+                               $t_custom_field_string_table
+                               WHERE field_id = '$c_field_id'
+                               GROUP BY value";
+
+
+                $result = db_query( $query );
+
+                $t_row_count = db_num_rows( $result );
+
+                if ( 0 == $t_row_count ) {
+                        return false;
+                }
+
+                $t_distinct = array();
+
+                for ( $i=0 ; $i < $t_row_count ; $i++ ) {
+                        $row = db_fetch_array( $result );
+                        if(trim($row['value']) != "") {
+                                array_push( $t_distinct, $row['value'] );
+                        }
+                }
+
+                return $t_distinct;
+        }
+
 
 
 	#===================================
@@ -483,11 +516,22 @@
 
 	# --------------------
 	# Return an array all custom field ids
-	function custom_field_get_ids() {
+	function custom_field_get_ids($p_project_id = ALL_PROJECTS) {
 		$t_custom_field_table = config_get( 'mantis_custom_field_table' );
-		$query = "SELECT id FROM
+                if($p_project_id == ALL_PROJECTS){
+		        $query = "SELECT id FROM
 				  $t_custom_field_table
 				  ORDER BY name ASC";
+                } 
+                else {
+                        $query = "SELECT  $t_custom_field_table.id FROM
+                                         $t_custom_field_table, mantis_custom_field_project_table
+                                  WHERE
+                                         mantis_custom_field_project_table.project_id = '$p_project_id' AND
+                                         $t_custom_field_table.id = mantis_custom_field_project_table.field_id
+                                  ORDER BY name ASC";
+                }
+
 		$result = db_query( $query );
 
 		$t_row_count = db_num_rows( $result );
Index: core/filter_api.php
===================================================================
RCS file: /cvsroot/mantisbt/mantisbt/core/filter_api.php,v
retrieving revision 1.14
diff -u -r1.14 filter_api.php
--- core/filter_api.php	17 Aug 2003 23:08:48 -0000	1.14
+++ core/filter_api.php	7 Jan 2004 19:55:58 -0000
@@ -39,6 +39,7 @@
 		$t_bug_table			= config_get( 'mantis_bug_table' );
 		$t_bug_text_table		= config_get( 'mantis_bug_text_table' );
 		$t_bugnote_table		= config_get( 'mantis_bugnote_table' );
+		$t_custom_field_string_table	= config_get( 'mantis_custom_field_string_table' );
 		$t_bugnote_text_table	= config_get( 'mantis_bugnote_text_table' );
 		$t_project_table		= config_get( 'mantis_project_table' );
 		$t_limit_reporters		= config_get( 'limit_reporters' );
@@ -139,6 +140,53 @@
 			array_push( $t_where_clauses, "($t_bug_table.status='$c_show_status')" );
 		}
 
+		# date filter
+		if ( ON == config_get( 'filter_by_date' ) &&
+			    is_numeric( $t_filter['start_month'] ) && 
+			    is_numeric( $t_filter['start_day'] ) && 
+			    is_numeric( $t_filter['start_year'] ) && 
+			    is_numeric( $t_filter['end_month'] ) && 
+			    is_numeric( $t_filter['end_day'] ) && 
+			    is_numeric( $t_filter['end_year'] ) 
+			) {
+
+			$t_start_string = db_prepare_string( $t_filter['start_year']  . "-". $t_filter['start_month']  . "-" . $t_filter['start_day'] ." 00:00:00");
+			$t_end_string   = db_prepare_string( $t_filter['end_year']  . "-". $t_filter['end_month']  . "-" . $t_filter['end_day'] ." 23:59:59");
+
+			array_push( $t_where_clauses, "($t_bug_table.date_submitted BETWEEN '$t_start_string' AND '$t_end_string' )" );
+		}
+
+
+		if(ON == config_get( 'filter_by_custom_fields' )) {
+			# custom field filtering
+			$t_custom_fields = custom_field_get_ids();
+
+			$t_first_time = true;		
+			$t_my_where_clause = "(";
+			foreach($t_custom_fields as $t_cfid){
+				// ignore all custom filters that are not set, or that are set to "" or "any"
+					if ( isset($t_filter['custom_fields'][$t_cfid]) && 
+						'any' != strtolower($t_filter['custom_fields'][$t_cfid]) && 
+						"" != trim($t_filter['custom_fields'][$t_cfid])) {
+
+						if($t_first_time){
+							$t_first_time = false;
+						} else {
+							$t_my_where_clause .= " AND ";
+						}
+
+						$t_table_name = $t_custom_field_string_table."_".$t_cfid;
+						array_push( $t_join_clauses, "LEFT JOIN $t_custom_field_string_table as $t_table_name ON $t_table_name.bug_id = $t_bug_table.id" );
+						$t_my_where_clause .= 
+											"(  $t_table_name.field_id = $t_cfid AND " 
+											. "  $t_table_name.value = '" 
+											. db_prepare_string(trim($t_filter['custom_fields'][$t_cfid]))  . "')";
+					}
+			}
+			if($t_my_where_clause != "("){
+				array_push( $t_where_clauses, $t_my_where_clause . ')');
+			}
+		}
 		# Simple Text Search - Thnaks to Alan Knowles
 		if ( !is_blank( $t_filter['search'] ) ) {
 			$c_search = db_prepare_string( $t_filter['search'] );
@@ -168,7 +216,7 @@
 		}
 
 		# Get the total number of bugs that meet the criteria.
-		$query = "SELECT COUNT( $t_bug_table.id ) as count $t_from $t_join $t_where";
+		$query = "SELECT COUNT( DISTINCT ( $t_bug_table.id ) ) as count $t_from $t_join $t_where";
 		$result = db_query( $query );
 		$bug_count = db_result( $result );
 
@@ -224,7 +272,14 @@
 			$c_dir = 'ASC';
 		}
 
-		$query2 .= " ORDER BY '$c_sort' $c_dir";
+                $t_upd = ', last_updated DESC';
+
+                if( $c_sort != 'last_updated') {
+		        $query2 .= " ORDER BY '$c_sort' $c_dir $t_upd";
+                }
+                else {
+                        $query2 .= " ORDER BY '$c_sort' $c_dir";
+                }
 
 		# Figure out the offset into the db query
 		#
super-searching.patch (15,637 bytes)   
html_entity_decode.patch (1,126 bytes)   
--- html_api.php.old	2004-01-30 16:28:37.000000000 +0100
+++ html_api.php	2004-01-30 16:44:43.000000000 +0100
@@ -44,6 +44,12 @@
 	#  	 html_body_end
 	#  	 html_end
 	#
+        # The following funtion makes chipux's super-searching.patch work on
+        # PHP < 4.3.0. Shamelessly stolen from mike's comment at (see bottom):
+        #
+        # http://no.php.net/manual/en/function.html-entity-decode.php
+        #
+        #   html_entity_decode
 	###########################################################################
 
 	$t_core_dir = dirname( __FILE__ ).DIRECTORY_SEPARATOR;
@@ -732,4 +738,12 @@
 		html_button_bug_delete( $p_bug_id );
 		echo '</td></tr></table>';
 	}
+
+	# --------------------
+	# Replicates the functionality of html_entity_decode introduced in PHP 4.3
+        function html_entity_decode( $given_html, $quote_style = ENT_QUOTES ) {
+                $trans_table = array_flip(get_html_translation_table( HTML_SPECIALCHARS, $quote_style ));
+                $trans_table['&#39;'] = "'";
+                return ( strtr( $given_html, $trans_table ) );
+        }
 ?>
html_entity_decode.patch (1,126 bytes)   

Relationships

has duplicate 0003556 closedvboctor Search in custom fields 

Activities

tarjeik

tarjeik

2004-01-20 16:19

reporter   ~0004899

Due to the fact that your patch uses html_entity_decode(), php 4.3 or later is required for this patch to work.

tarjeik

tarjeik

2004-01-30 16:51

reporter   ~0004937

Added a patch to core/html_api.php which adds and implementation of html_entity_decode for those of you with PHP versions prior to 4.3 (tested on 4.2.2).

Of course, there's no need to apply this patch if you are running PHP 4.3 (I assume this will cause conflicts with the built in library - I'm not very PHP-savvy).

vboctor

vboctor

2004-02-07 07:35

manager   ~0004976

We implement methods that were added in later versions of PHP (compared to minimum required version) in core/php_api.php. Following is an example from there:

array_key_exists was not available on PHP 4.0.6

if ( ! function_exists( 'array_key_exists' ) ) {
function array_key_exists( $key, $search ) {
return key_exists( $key, $search );
}
}

This example will work with versions that have/don't have this function.

vboctor

vboctor

2004-02-07 07:38

manager   ~0004977

Is there a public Mantis installation where we can see these changes applied?

einstein2020

einstein2020

2004-02-13 06:58

reporter   ~0005038

Last edited: 2004-02-13 07:01

Yeap! That´s a wonderful work!
You can see here: http://www.einsoft.com.br/mantisbt/

User: mantisbt
Pass: mantisbt

After a couple modifications the patch is working with 0.18.1

edited on: 02-13-04 07:01

rhe

rhe

2004-02-25 05:02

reporter   ~0005102

Very nice (and exactly what we would need).

Will this be incorporated into the mantis source so we will get it
"out-of-the-box" in some future release?

vboctor

vboctor

2004-02-25 07:06

manager   ~0005106

We already have a patch that will add an enhanced filtering feature. This supports private/public saved queries, and other stuff. However, Lincoln is still working on it to support customer fields. It supports filtering based on submit date.

It is planned for this feature to go into 0.19.0 which is probably the release after 0.18.2. We are planning to release 0.18.2 first half of March.

chipux

chipux

2004-03-18 12:34

reporter   ~0005231

This Bug can be closed.

Code based off this patch has been commited to CVS.

-chip

vboctor

vboctor

2004-03-18 16:07

manager   ~0005233

This is now implemented in CVS thanks to Chipux and Lincoln. Chipux, I am not sure if the table optimisations were committed, if not, then please add a separate enhancement request for it.