INSERT INTO mantis_plugin_query_definitions_table (query_id, query_name, query_desc, query_type, query_script, query_tables, query_joins, query_fields, query_filter, query_order, query_group, query_sql) VALUES
(1, 'Mantis User Table', 'Download of Mantis users', 'Q', '', 'mantis_user_table', '', 'username, realname,email, enabled', '', 'username', '', 'select username,realname,email,enabled from mantis_user_table order by username'),
(3, 'Yesterdays activities', 'What happened yesterday', 'Q', '', 'mantis_bug_history_table, mantis_bug_table,mantis_user_table', 'mantis_bug_history_table.user_id= mantis_user_table.id and mantis_bug_history_table.bug_id=mantis_bug_table.id', 'bug_id,summary,username,type, field_name, old_value,new_value, DATE( FROM_UNIXTIME( `date_modified` ) )', '', '', '', 'select bug_id,summary,username,type, field_name, old_value,new_value, DATE( FROM_UNIXTIME( `date_modified` ) ) from mantis_bug_history_table, mantis_bug_table,mantis_user_table where mantis_bug_history_table.user_id= mantis_user_table.id and mantis_bug_history_table.bug_id=mantis_bug_table.id'),
(4, 'Open issues per Coordinator', 'Open issues per Coordinator so you can chase the people', 'Q', '', 'mantis_bug_table,mantis_user_table', 'mantis_bug_table.handler_id=mantis_user_table.id', 'mantis_bug_table.id,summary,handler_id,username,realname,DATE( FROM_UNIXTIME( `date_submitted` ) ) AS submitted, status', 'status&lt;80', 'username,date_submitted', '', 'select mantis_bug_table.id,summary,handler_id,username,realname,DATE( FROM_UNIXTIME( `date_submitted` ) ) AS submitted, status from mantis_bug_table,mantis_user_table where mantis_bug_table.handler_id=mantis_user_table.id and status&lt;80 order by username,date_submitted'),
(5, 'Send Feedback mail', 'Send email to the reporters if their issue  is awaiting their feedback', 'X', '# This page sends an E-mail to the reporter if an issue is awaiting feedback\r\nrequire_once( ''../../../core.php'' );\r\n$t_core_path = config_get( ''core_path'' );\r\nrequire_once( $t_core_path.''email_api.php'' );\r\n$t_bug_table	= db_get_table( ''mantis_bug_table'' );\r\n$t_project		= 0;\r\n$status			= FEEDBACK;\r\n$t_rem_body1	= &quot;Please review the following issues&quot;;\r\n$t_rem_body2	= &quot;Please do not reply to this message&quot;;\r\n$query = &quot;select id,reporter_id,project_id from $t_bug_table where status=$status order by reporter_id&quot;;\r\n$results = db_query_bound( $query );\r\nif ($results){\r\n	$start = true ;\r\n	$list= &quot;&quot;;\r\n	// first group and store reminder per issue\r\n	while ($row1 = db_fetch_array($results)) {\r\n		$id 		= $row1[''id''];\r\n		$handler	= $row1[''reporter_id''];\r\n		$project	= $row1[''project_id''];\r\n		if ($start){\r\n			$handler2 = $handler ;\r\n			$start = false ;\r\n		}\r\n		if ($handler == $handler2){\r\n			$list .='' \\n\\n''; \r\n			$list .= string_get_bug_view_url_with_fqdn( $id, $handler2 );\r\n		} else {\r\n			// now send the grouped email\r\n			$body  = $t_rem_body1. &quot; \\n\\n&quot;;\r\n			$body .= $list. &quot; \\n\\n&quot;;\r\n			$body .= $t_rem_body2;\r\n			$result = email_group_reminder( $handler2, $body);\r\n			$handler2 = $handler ;\r\n			$list= string_get_bug_view_url_with_fqdn( $id, $handler2 );\r\n		}\r\n	}\r\n	// handle last grouped email\r\n	if ($results){\r\n		$body  = $t_rem_body1. &quot; \\n\\n&quot; ;\r\n		$body .= $list. &quot; \\n\\n&quot;;\r\n		$body .= $t_rem_body2;\r\n		$result = email_group_reminder( $handler2, $body);\r\n	}\r\n} \r\nif( php_sapi_name() != &quot;cli&quot; ) {  \r\n	print_header_redirect( ''plugin.php?page=Query/manage_query'' );\r\n}\r\n\r\n# Send Grouped reminder\r\nfunction email_group_reminder( $p_user_id, $issues ) {\r\n	$t_username = user_get_field( $p_user_id, ''username'' );\r\n	$t_email = user_get_email( $p_user_id );\r\n	$t_message = $issues ;\r\n	$t_subject	= &quot;Issues requiring your attention&quot;;\r\n	if( !is_blank( $t_email ) ) {\r\n		email_store( $t_email, $t_subject, $t_message );\r\n		if( OFF == config_get( ''email_send_using_cronjob'' ) ) {\r\n				email_send_all();\r\n		}\r\n	}\r\n}', '', '', '', '', '', '', ''),
(6, 'Issues approaching duedate', 'Reaching Duedate within 2 days and status = Assigned', 'S', '$t_rem_days		= 2;\r\n$t_rem_status		= 50;\r\nrequire_once( ''../../../core.php'' );\r\n$t_core_path = config_get( ''core_path'' );\r\n$t_bug_table	= db_get_table( ''mantis_bug_table'' );\r\n$t_user_table	= db_get_table( ''mantis_user_table'' );\r\n$baseline=time(true)+ ($t_rem_days*24*60*60);\r\n$query=&quot;select $t_bug_table.id,summary,due_date,username,realname from $t_bug_table,$t_user_table where $t_bug_table.handler_id=$t_user_table.id and status=$t_rem_status and due_date&gt;1 and due_date&lt;=$baseline&quot; ;\r\n$results = db_query_bound( $query );\r\nif (!$results) {\r\n	return;\r\n} else{\r\n	$content = &quot;Issue-id&quot; ;\r\n	$content .= &quot;,&quot; ;\r\n	$content .= &quot;Summary&quot; ;\r\n	$content .= &quot;,&quot; ;\r\n	$content .= &quot;Due date&quot; ;\r\n	$content .= &quot;,&quot; ;\r\n	$content .= &quot;Assigned to&quot; ;\r\n	$content .= &quot;,&quot; ;\r\n	$content .= &quot;Name&quot; ;\r\n	$content .= &quot;\\r\\n&quot;;\r\n}\r\nwhile ($row1 = db_fetch_array($results)) {\r\n	$id 		= $row1[''id''];\r\n	$summary	= $row1[''summary''];\r\n	$duedate	= date( config_get( ''short_date_format'' ),$row1[''due_date''] );\r\n	$assigned	= $row1[''username''];\r\n	$name		= $row1[''realname''];\r\n	$content .= $id ;\r\n	$content .= &quot;,&quot; ;\r\n	$content .= $summary ;\r\n	$content .= &quot;,&quot; ;\r\n	$content .= $duedate ;\r\n	$content .= &quot;,&quot; ;\r\n	$content .= $assigned ;\r\n	$content .= &quot;,&quot; ;\r\n	$content .= $name ;\r\n	$content .= &quot;\\r\\n&quot;;\r\n}\r\nreturn $content;', '', '', '', '', '', '', '');
