View Issue Details
ID | Project | Category | View Status | Date Submitted | Last Update |
---|---|---|---|---|---|
0003494 | mantisbt | bugtracker | public | 2004-01-07 21:12 | 2004-07-08 07:29 |
Reporter | chipux | Assigned To | Narcissus | ||
Priority | normal | Severity | feature | Reproducibility | N/A |
Status | closed | Resolution | fixed | ||
Fixed in Version | 0.19.0a1 | ||||
Summary | 0003494: Filter by Date Submited and Filter by Custom Fields | ||||
Description | Patch does:
| ||||
Additional Information |
Patch Supersedes Bug 0003492 "Better Indexes on SQL" http://force-elite.com/~chip/patches/mantis/super-searching.patch | ||||
Tags | No 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' ); ?> /> <?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"> </td> + <td class="small-caption" colspan="2"> </td> + <?php +} + ?> <td class="small-caption" colspan="2"> </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"> </td> + <td class="left" colspan="2"> </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 # 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['''] = "'"; + return ( strtr( $given_html, $trans_table ) ); + } ?> | ||||
Due to the fact that your patch uses html_entity_decode(), php 4.3 or later is required for this patch to work. |
|
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). |
|
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.6if ( ! function_exists( 'array_key_exists' ) ) { This example will work with versions that have/don't have this function. |
|
Is there a public Mantis installation where we can see these changes applied? |
|
Yeap! That´s a wonderful work! User: mantisbt After a couple modifications the patch is working with 0.18.1 edited on: 02-13-04 07:01 |
|
Very nice (and exactly what we would need). Will this be incorporated into the mantis source so we will get it |
|
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. |
|
This Bug can be closed. Code based off this patch has been commited to CVS. -chip |
|
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. |
|