-- phpMyAdmin SQL Dump -- version 2.7.0-pl2 -- http://www.phpmyadmin.net -- -- Host: localhost -- Generation Time: Jun 23, 2006 at 09:57 AM -- Server version: 5.0.22 -- PHP Version: 4.4.2 -- -- Database: `bugtracker` -- -- -------------------------------------------------------- -- -- Table structure for table `custom_report` -- -- Creation: Jun 15, 2006 at 03:18 PM -- CREATE TABLE IF NOT EXISTS `custom_report` ( `id` int(11) NOT NULL auto_increment, `name` varchar(128) NOT NULL default '', `engine` int(11) NOT NULL default '1', `visible` int(11) NOT NULL default '1', `description` varchar(255) default NULL, `form` text, `sql` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Table for storing custom reports' AUTO_INCREMENT=33 ; -- -- Dumping data for table `custom_report` -- INSERT INTO `custom_report` VALUES (1, 'Hours by username and client', 1, 0, NULL, NULL, 'select u.username, c.value as `customer`, count(*) as `count`, sum(h.value) as `hours`\r\nfrom\r\n`mantis_bug_table` b\r\n inner join `mantis_custom_field_string_table` c\r\non b.id = c.bug_id\r\n\r\n inner join `mantis_custom_field_string_table` h\r\non b.id = h.bug_id\r\n\r\n inner join `mantis_user_table` u\r\non u.id = b.handler_id\r\n\r\nwhere c.field_id=1\r\nand h.field_id=2\r\n\r\ngroup by u.username, c.value'), (2, 'Bugs without hours', 1, 0, NULL, NULL, 'select\r\n b.id,\r\n t.description,\r\n u.username,\r\n h.value as ''hours''\r\nfrom\r\n`mantis_bug_table` b\r\n\r\n inner join `mantis_user_table` u\r\non u.id = b.handler_id\r\n\r\n inner join `mantis_bug_text_table` t\r\non b.bug_text_id = t.id\r\n\r\n left join `mantis_custom_field_string_table` h\r\non b.id = h.bug_id\r\n\r\nwhere h.field_id = 2\r\nand h.value='''''), (3, 'Hours by username and client (no enhancements)', 1, 0, NULL, NULL, 'select u.username, c.value as `customer`, count(*) as `count`, sum(h.value) as `hours`\r\nfrom\r\n`mantis_bug_table` b\r\n inner join `mantis_custom_field_string_table` c\r\non b.id = c.bug_id\r\n\r\n inner join `mantis_custom_field_string_table` h\r\non b.id = h.bug_id\r\n\r\n inner join `mantis_user_table` u\r\non u.id = b.handler_id\r\n\r\nwhere c.field_id=1\r\nand h.field_id=2\r\n\r\ngroup by u.username, c.value'), (4, 'mantis_user_table', 1, 0, 'This is a test report that uses a form.', 'Username ', 'select * from mantis_user_table\r\nwhere username like ''%<%username%>%'''), (5, 'Detail report all clients', 1, 0, NULL, NULL, 'select b.id as `C_id`, \r\nu.username as `C_username`,\r\nslut.value as `severity`, bt.description, \r\nc.value as `customer` , \r\nbnt.id as `note id`, \r\nbnt.note\r\n\r\nfrom `mantis_bug_table` b\r\n\r\n inner join\r\nmantis_user_table u\r\n\r\non b.handler_id = u.id\r\n\r\n inner join `mantis_custom_field_string_table` c\r\n\r\non b.id = c.bug_id\r\n\r\n inner join `mantis_bug_text_table` bt\r\n\r\non bt.id = b.bug_text_id\r\n\r\n inner join `mantis_bugnote_table` bn\r\n\r\non bn.bug_id = b.id\r\n\r\n inner join `mantis_bugnote_text_table` bnt\r\n\r\non bn.id = bnt.id\r\n\r\n inner join `severity_lut` slut\r\n\r\non b.severity = slut.id where c.field_id=1\r\n\r\norder by c.value, slut.value, b.id, bnt.id\r\n'), (6, 'Detail report by status and client (form)', 1, 0, NULL, '\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
new\r\n\r\n
feedback\r\n\r\n
acknowledged\r\n\r\n
confirmed\r\n\r\n
assigned\r\n\r\n
resolved\r\n\r\n
closed\r\n\r\n
Customer\r\n
\r\n', 'select b.id as `C_id`, \r\nu.username as `C_username`,\r\nslut.value as `severity`, bt.description, \r\ns.value as `C_status`,\r\nc.value as `customer` , \r\nbnt.id as `note id`, \r\nbnt.note\r\n\r\nfrom `mantis_bug_table` b\r\n\r\n inner join status_lut s\r\non b.status = s.id\r\n\r\n left join\r\nmantis_user_table u\r\n\r\non b.handler_id = u.id\r\n\r\n inner join `mantis_custom_field_string_table` c\r\n\r\non b.id = c.bug_id\r\n\r\n inner join `mantis_bug_text_table` bt\r\n\r\non bt.id = b.bug_text_id\r\n\r\n left join `mantis_bugnote_table` bn\r\n\r\non bn.bug_id = b.id\r\n\r\n left join `mantis_bugnote_text_table` bnt\r\n\r\non bn.id = bnt.id\r\n\r\n inner join `severity_lut` slut\r\n\r\non b.severity = slut.id \r\n\r\nwhere c.field_id=1\r\nand b.status in (0<%NEW%><%FEEDBACK%><%ACKNOWLEDGED%><%CONFIRMED%><%ASSIGNED%><%RESOLVED%><%CLOSED%>)\r\nand lcase(c.value) like lcase(''%<%show_customer%>%'')\r\n\r\norder by c.value, slut.value, b.id, bnt.id'), (7, 'Bugs by Date', 2, 0, 'Lists the number of bugs broken down by date (last 30 days)', '\r\n\r\n\r\n\r\n\r\n
Status\r\n\r\n
\r\n', 'SELECT concat('''', date_format(last_updated, ''%M %D, %Y''), '''') as `Resolved Date`,\r\ncount(*) as `Count`\r\nFROM `mantis_bug_table` \r\nwhere status=<%form_status%>\r\ngroup by date_format(last_updated, ''%M %D, %Y'')\r\norder by last_updated desc\r\nLIMIT 0 , 30'), (8, 'List Bugs', 2, 0, NULL, '\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
Date (yyyy-mm-dd)\r\n\r\n
Status\r\n\r\n
\r\n', 'SELECT concat('''', b.id, '''') AS C_id, \r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_username, \r\nb.last_updated, \r\nc.value as C_customer,\r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\nINNER JOIN mantis_custom_field_string_table c on b.id = c.bug_id\r\n\r\nWHERE dayofyear( b.last_updated ) = dayofyear( ''<%date%>'' ) \r\nand b.status = <%form_status%>\r\nand c.field_id = 1\r\n\r\nLIMIT 0 , 30'), (9, 'Resolved Bugs with Resolution Open', 2, 1, 'Shows all bugs that were not resolved properly', NULL, 'SELECT concat('''', b.id, '''') AS C_id, \r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_username, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\n\r\nWHERE b.status = 80\r\nand b.resolution = 10\r\n\r\norder by u.username'), (10, 'Resolved Bugs by Customer', 1, 0, NULL, '', 'SELECT concat('''', b.id, '''') AS C_id, \r\nu.username AS `C_username` , \r\nslut.value AS `severity` , \r\nbt.description, \r\ns.value AS `C_status` , \r\nc.value AS `customer` \r\nFROM `mantis_bug_table` b\r\nINNER JOIN status_lut s ON b.status = s.id\r\nLEFT JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN `mantis_custom_field_string_table` c ON b.id = c.bug_id\r\nINNER JOIN `mantis_bug_text_table` bt ON bt.id = b.id\r\nINNER JOIN `severity_lut` slut ON b.severity = slut.id\r\nWHERE c.field_id = 1 AND b.status\r\nIN ( 80 ) \r\nORDER BY c.value, slut.value, b.id'), (11, 'Readme Generator', 1, 0, NULL, '\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
From (yyyy-mm-dd)\r\n\r\n
To (yyyy-mm-dd)\r\n\r\n
Read Me Keyword\r\n\r\n
Status\r\n\r\n
\r\n', 'select b.id, \r\nb.last_updated,\r\nr.value as resolution,\r\nb.summary,\r\nu.username as `user`,\r\nbnt.note\r\n\r\nfrom mantis_bug_table b \r\n inner join status_lut s\r\non b.status = s.id\r\n inner join resolution_lut r\r\non b.resolution = r.id\r\n left join `mantis_bugnote_table` bn\r\non bn.bug_id = b.id\r\n inner join mantis_user_table u\r\non bn.reporter_id = u.id\r\n left join `mantis_bugnote_text_table` bnt\r\non bn.id = bnt.id\r\n\r\n\r\nwhere b.last_updated between ''<%fromdate%>'' and ''<%todate%>''\r\nand b.status = <%form_status%>\r\nand bnt.note like ''%<%KEYWORD%>%'''), (12, 'Detail report by status and client ordered', 1, 0, 'ordered by username, status', '\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
new\r\n\r\n
feedback\r\n\r\n
acknowledged\r\n\r\n
confirmed\r\n\r\n
assigned\r\n\r\n
resolved\r\n\r\n
closed\r\n\r\n
Customer\r\n
\r\n', 'select b.id as `C_id`, \r\nconcat('''', b.id, '''') AS C_link, \r\nu.username as `C_username`,\r\nslut.value as `severity`, bt.description, \r\ns.value as `C_status`,\r\nc.value as `customer` \r\n\r\nfrom `mantis_bug_table` b\r\n\r\n inner join status_lut s\r\non b.status = s.id\r\n\r\n left join\r\nmantis_user_table u\r\n\r\non b.handler_id = u.id\r\n\r\n inner join `mantis_custom_field_string_table` c\r\n\r\non b.id = c.bug_id\r\n\r\n inner join `mantis_bug_text_table` bt\r\n\r\non bt.id = b.bug_text_id\r\n\r\n inner join `severity_lut` slut\r\n\r\non b.severity = slut.id \r\n\r\nwhere c.field_id=1\r\nand b.status in (0<%NEW%><%FEEDBACK%><%ACKNOWLEDGED%><%CONFIRMED%><%ASSIGNED%><%RESOLVED%><%CLOSED%>)\r\nand lcase(c.value) like lcase(''%<%show_customer%>%'')\r\n\r\norder by u.username, c.value, slut.value'), (13, 'Unassigned Tickets', 1, 0, 'Order by Customer', '', 'SELECT b.id AS `C_id` , \r\n concat( '''', b.id, '''' ) AS C_link, \r\n u.username AS `C_reporter` , \r\n slut.value AS `severity` , \r\n bt.description, \r\n s.value AS `C_status` , \r\n c.value AS `customer` \r\nFROM `mantis_bug_table` b\r\nINNER JOIN status_lut s ON b.status = s.id\r\nLEFT JOIN mantis_user_table u ON b.reporter_id = u.id\r\nINNER JOIN `mantis_custom_field_string_table` c ON b.id = c.bug_id\r\nINNER JOIN `mantis_bug_text_table` bt ON bt.id = b.bug_text_id\r\nINNER JOIN `severity_lut` slut ON b.severity = slut.id\r\nWHERE \r\nc.field_id = 1 AND b.status NOT \r\nIN ( 80, 90 ) and b.handler_id = 0\r\nORDER BY c.value, slut.value'), (14, 'Search Tickets By Handler', 1, 0, 'Order by Customer', 'Username ', 'SELECT b.id AS `C_id` , \r\n concat( '''', b.id, '''' ) AS C_link, \r\n u.username AS `C_reporter` , \r\n u2.username AS `C_handler`, \r\n slut.value AS `severity` , \r\n bt.description, \r\n s.value AS `C_status` , \r\n c.value AS `customer` \r\nFROM `mantis_bug_table` b\r\nINNER JOIN status_lut s ON b.status = s.id\r\nLEFT JOIN mantis_user_table u ON b.reporter_id = u.id\r\nLEFT JOIN mantis_user_table u2 ON b.handler_id = u2.id\r\nLEFT JOIN `mantis_custom_field_string_table` c ON b.id = c.bug_id\r\nINNER JOIN `mantis_bug_text_table` bt ON bt.id = b.bug_text_id\r\nINNER JOIN `severity_lut` slut ON b.severity = slut.id\r\nWHERE lcase(u2.username) = lcase(''<%username%>'') and \r\n(c.field_id = 1 or c.field_id is null) AND b.status not in (80,90)\r\nORDER BY c.value, slut.value'), (15, 'Search Closed/Resolved Tickets By Handler', 1, 0, 'Order by Customer', 'Username ', 'SELECT b.id AS `C_id` , \r\n concat( '''', b.id, '''' ) AS C_link, \r\n u.username AS `C_reporter` , \r\n u2.username AS `C_handler`, \r\n slut.value AS `severity` , \r\n bt.description, \r\n s.value AS `C_status` , \r\n c.value AS `customer` \r\nFROM `mantis_bug_table` b\r\nINNER JOIN status_lut s ON b.status = s.id\r\nLEFT JOIN mantis_user_table u ON b.reporter_id = u.id\r\nLEFT JOIN mantis_user_table u2 ON b.handler_id = u2.id\r\nLEFT JOIN `mantis_custom_field_string_table` c ON b.id = c.bug_id\r\nINNER JOIN `mantis_bug_text_table` bt ON bt.id = b.bug_text_id\r\nINNER JOIN `severity_lut` slut ON b.severity = slut.id\r\nWHERE lcase(u2.username) = lcase(''<%username%>'') and \r\n(c.field_id = 1 or c.field_id is null) AND b.status in (80,90)\r\nORDER BY c.value, slut.value'), (16, 'Search Closed/Resolved Tickets By Date', 1, 0, 'Order by Customer (2.0 Applications Only)', 'FromDate (yyyy-mm-dd)
\r\nOrder By \r\n', 'SELECT b.id AS `C_id` , \r\n concat( '''', b.id, '''' ) AS C_link, \r\n u.username AS `C_reporter` , \r\n u2.username AS `C_handler`, \r\n slut.value AS `severity` , \r\n b.last_updated AS `C_date`, \r\n bt.description, \r\n s.value AS `C_status` , \r\n c.value AS `customer` \r\nFROM `mantis_bug_table` b\r\nINNER JOIN status_lut s ON b.status = s.id\r\nLEFT JOIN mantis_user_table u ON b.reporter_id = u.id\r\nLEFT JOIN mantis_user_table u2 ON b.handler_id = u2.id\r\nLEFT JOIN `mantis_custom_field_string_table` c ON b.id = c.bug_id\r\nINNER JOIN `mantis_bug_text_table` bt ON bt.id = b.bug_text_id\r\nINNER JOIN `severity_lut` slut ON b.severity = slut.id\r\nWHERE b.last_updated > ''<%fromdate%>'' and \r\n(c.field_id = 1 or c.field_id is null) AND b.status in (80,90)\r\nand b.project_id = 2\r\nORDER BY <%orderby%>'), (17, 'Enhancment Requests', 1, 0, NULL, NULL, 'select concat('''', b.id, '''') AS `C_id`, \r\nu.username as `C_username`,\r\nb.category, \r\nplut.value as `projection`,\r\nbt.description,\r\nc.value as `customer`,\r\nh.value as `hours`\r\n\r\nfrom `mantis_bug_table` b\r\n\r\ninner join mantis_user_table u on b.handler_id = u.id\r\ninner join mantis_bug_text_table bt on b.id = bt.id\r\ninner join `severity_lut` slut on b.severity = slut.id \r\ninner join `projection_lut` plut on b.projection = plut.id\r\ninner join `mantis_custom_field_string_table` c on b.id = c.bug_id\r\ninner join `mantis_custom_field_string_table` h on b.id = h.bug_id\r\n\r\nwhere slut.value = ''enhancement''\r\nand c.field_id=1\r\nand h.field_id=2\r\norder by b.category, slut.value'), (18, 'Bugs Not Related to 1222', 2, 1, 'Lists all bugs not related to 1222', '\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
Date (yyyy-mm-dd)\r\n\r\n
Status\r\n\r\n
\r\n', 'SELECT distinct concat('''', b.id, '''') AS C_id, \r\nb.id,\r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_username, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\nLEFT OUTER JOIN\r\nmantis_bug_relationship_table br on\r\nb.id = br.source_bug_id\r\n\r\n\r\nWHERE slut.value not in (''resolved'',''closed'')\r\nand br.id is null\r\nORDER BY b.id\r\n\r\n'), (19, 'List Bugs by Fixed In Version', 2, 1, NULL, '\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
Version\r\n\r\n
Status\r\n\r\n
\r\n', 'SELECT concat('''', b.id, '''') AS C_id, \r\nb.id,\r\nb.fixed_in_version as C_fiv,\r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_username, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\n\r\n\r\nWHERE b.fixed_in_version like ''<%version%>''\r\nand b.status in (0<%Status%>)\r\norder by b.last_updated desc'), (20, 'List Open Issues', 2, 1, 'Display open issues by version and/or assigned to name', '\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n\r\n
Version\r\n\r\n
Assigned To\r\n\r\n
\r\n', 'SELECT concat('''', b.id, '''') AS C_id, \r\nb.version as C_version,\r\nb.fixed_in_version as C_fiv,\r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_username, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\n\r\nWHERE b.version like ''<%version%>%''\r\nand u.username like ''<%user%>%''\r\n\r\nand slut.value not in (''resolved'',''closed'')'), (21, 'Closed Bugs with Resolution Open', 2, 1, 'Shows all bugs that were not resolved properly', NULL, 'SELECT concat('''', b.id, '''') AS C_id, \r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_username, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\n\r\nWHERE b.status = 90\r\nand b.resolution = 10\r\n\r\norder by u.username'), (22, 'Tester Close Numbers', 2, 1, 'Shows the number of issues closed per day by username', '\r\n\r\n\r\n\r\n\r\n
username\r\n
\r\nUse % sign as a wild card.
\r\nA % sign by itself will return all users.\r\n
', 'SELECT u.username, DATE_FORMAT(h.date_modified, ''%y%m%d'') as ndate, count(*) as ''closed''\r\nFROM `mantis_bug_history_table` as h\r\ninner join mantis_user_table as u\r\non h.user_id = u.id\r\nWHERE h.field_name = ''status'' \r\nAND h.old_value = ''80'' \r\nAND h.new_value = ''90''\r\nAND u.username like ''<%username%>''\r\ngroup by u.username, ndate\r\norder by ndate desc'), (23, 'Release Notes', 2, 1, 'Displays release note messages', '\r\n\r\n\r\n\r\n\r\n
Version\r\n\r\n (ex. ''2.5'' or ''2.6'')
\r\n', 'SELECT concat('''', b.id, '''') AS C_id, \r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nb.fixed_in_version,\r\nb.summary,\r\nc2.value as ''Readme Note''\r\n\r\n\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\nINNER JOIN mantis_custom_field_string_table c on b.id = c.bug_id\r\nINNER JOIN mantis_custom_field_string_table c2 on b.id = c2.bug_id\r\nWHERE b.project_id in (2,12)\r\nand c.field_id = 9\r\nand c.value = ''Yes''\r\nand c2.field_id=8\r\nand b.resolution not in (10,30)\r\nand b.fixed_in_version like ''<%version%>%''\r\nORDER BY CAST(SUBSTRING(b.fixed_in_version, 5) AS UNSIGNED) desc, b.id'), (24, 'Known Issues', 2, 1, 'Displays release note known issues', '\r\n\r\n\r\n\r\n\r\n
Version\r\n\r\n (ex. ''2.5'' or ''2.6'')
\r\n', 'SELECT concat('''', b.id, '''') AS C_id, \r\ns.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nb.version,\r\nu.username,\r\nb.summary,\r\nrn.value as ''Readme Note''\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut s ON b.status = s.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN mantis_custom_field_string_table rn on b.id = rn.bug_id\r\nINNER JOIN mantis_custom_field_string_table ir on b.id = ir.bug_id\r\nwhere rn.field_id = 8 \r\nand ir.field_id = 9\r\nand ir.value = ''Yes''\r\nand b.status not in (80,90)\r\nand b.version like ''<%version%>%''\r\nand b.project_id in (2,12)\r\nORDER BY CAST(SUBSTRING(b.version, 5) AS UNSIGNED) desc, b.id\r\n'), (26, 'Management Summary Report w/Links', 2, 0, NULL, '\r\n\r\n\r\n\r\n\r\n
Version\r\n\r\n
\r\n', 'SELECT concat('''', b.id, '''') AS C_id, \r\nb.version as C_version,\r\nb.fixed_in_version as C_fiv,\r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_username, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\n\r\nWHERE b.version like ''<%version%>''\r\n\r\nand slut.value not in (''resolved'',''closed'')'), (27, 'Developer Resolve Numbers', 2, 1, 'Shows the number of issues resolved per week by username', '\r\n\r\n\r\n\r\n\r\n
username\r\n
\r\nUse % sign as a wild card.
\r\nA % sign by itself will return all users.\r\n
', 'SELECT u.username, DATE_FORMAT(h.date_modified, ''Year: %y Week: %u'') as ndate, count(*) as ''resolved''\r\nFROM `mantis_bug_history_table` as h\r\ninner join mantis_user_table as u\r\non h.user_id = u.id\r\nWHERE h.field_name = ''status'' \r\nAND h.new_value = ''80''\r\nAND u.username like ''<%username%>''\r\ngroup by u.username, ndate\r\norder by ndate desc, resolved desc'), (28, 'Resolution Rates per Week', 2, 1, 'Shows the number of issues resolved per week', '', 'SELECT DATE_FORMAT(h.date_modified, ''Year: %y Week: %u'') as ndate, count(*) as ''resolved''\r\nFROM `mantis_bug_history_table` as h\r\nWHERE h.field_name = ''status'' \r\nAND h.new_value = ''80''\r\ngroup by ndate\r\norder by ndate desc, resolved desc'), (29, 'Open issues assigned to disabled users', 2, 1, 'List all issues that are currently assigned to a person who doesn''t work here anymore.', '', 'SELECT concat('''', b.id, '''') AS C_id, \r\nb.version as C_version,\r\nb.fixed_in_version as C_fiv,\r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_username, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\n\r\nWHERE u.enabled = 0\r\nand slut.value not in (''resolved'',''closed'')'), (31, 'Recently resolved and closed issues without fixed_in_version', 2, 1, 'List all recently resolved and closed issues that do not have fixed_in_version filled in.', '\r\n\r\n\r\n\r\n\r\n\r\n
Updated Since (yyyy-mm-dd)\r\n\r\n
\r\n', 'SELECT concat('''', b.id, '''') AS C_id, \r\nb.version as C_version,\r\nb.fixed_in_version as C_fiv,\r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_Handler, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\n\r\nwhere project_id in (2,12)\r\nand status in (80,90)\r\nand last_updated > ''<%fromdate%>''\r\nand CHAR_LENGTH(fixed_in_version) = 0'), (32, 'Recently resolved and closed issues with no readme flag', 2, 1, 'List all recently resolved and closed issues that do not have a readme note', '\r\n\r\n\r\n\r\n\r\n\r\n
Updated Since (yyyy-mm-dd)\r\n\r\n
\r\n', 'SELECT concat('''', b.id, '''') AS C_id, \r\nb.version as C_version,\r\nb.fixed_in_version as C_fiv,\r\nslut.value AS C_status, \r\nr.value AS C_resolution, \r\nb.category, \r\nu.username AS C_Handler, \r\nb.last_updated, \r\nb.summary\r\n\r\nFROM mantis_bug_table b\r\nINNER JOIN status_lut slut ON b.status = slut.id\r\nINNER JOIN mantis_user_table u ON b.handler_id = u.id\r\nINNER JOIN resolution_lut r ON b.resolution = r.id\r\ninner join mantis_custom_field_string_table cfs on b.id=cfs.bug_id and cfs.field_id=9\r\nwhere project_id in (2,12)\r\nand status in (80,90)\r\nand last_updated > ''<%fromdate%>''\r\nand CHAR_LENGTH(fixed_in_version) = 0\r\nand cfs.value=''No'''); -- -------------------------------------------------------- -- -- Table structure for table `priority_lut` -- -- Creation: Jun 15, 2006 at 03:46 PM -- CREATE TABLE IF NOT EXISTS `priority_lut` ( `id` int(11) NOT NULL default '0', `value` varchar(25) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `priority_lut` -- INSERT INTO `priority_lut` VALUES (10, 'none'), (20, 'low'), (30, 'normal'), (40, 'high'), (50, 'urgent'), (60, 'immediate'); -- -------------------------------------------------------- -- -- Table structure for table `projection_lut` -- -- Creation: Jun 15, 2006 at 03:47 PM -- Last update: Jun 15, 2006 at 04:16 PM -- CREATE TABLE IF NOT EXISTS `projection_lut` ( `id` int(11) NOT NULL default '0', `value` varchar(25) NOT NULL default '' ) ENGINE=MyISAM DEFAULT CHARSET=utf8; -- -- Dumping data for table `projection_lut` -- INSERT INTO `projection_lut` VALUES (10, 'none'), (30, 'tweak'), (50, 'minor fix'), (70, 'major rework'), (90, 'redesign'); -- -------------------------------------------------------- -- -- Table structure for table `resolution_lut` -- -- Creation: Jun 15, 2006 at 03:46 PM -- CREATE TABLE IF NOT EXISTS `resolution_lut` ( `id` int(11) NOT NULL default '0', `value` varchar(25) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `resolution_lut` -- INSERT INTO `resolution_lut` VALUES (10, 'open'), (20, 'fixed'), (30, 'reopened'), (40, 'unable to duplicate'), (50, 'not fixable'), (60, 'duplicate'), (70, 'not a bug'), (80, 'suspended'), (90, 'wont fix'); -- -------------------------------------------------------- -- -- Table structure for table `severity_lut` -- -- Creation: Jun 15, 2006 at 03:46 PM -- CREATE TABLE IF NOT EXISTS `severity_lut` ( `id` int(11) NOT NULL default '0', `value` varchar(25) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `severity_lut` -- INSERT INTO `severity_lut` VALUES (1, 'training'), (2, 'enhancement'), (3, 'bug'), (4, 'data'), (5, 'installation'), (7, 'vague'), (10, 'feature'), (20, 'trivial'), (30, 'text'), (40, 'tweak'), (50, 'minor'), (60, 'major'), (70, 'crash'), (80, 'block'); -- -------------------------------------------------------- -- -- Table structure for table `status_lut` -- -- Creation: Jun 15, 2006 at 03:46 PM -- CREATE TABLE IF NOT EXISTS `status_lut` ( `id` int(11) NOT NULL default '0', `value` varchar(25) NOT NULL default '', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- -- Dumping data for table `status_lut` -- INSERT INTO `status_lut` VALUES (10, 'new'), (20, 'feedback'), (30, 'acknowledged'), (40, 'confirmed'), (50, 'assigned'), (80, 'resolved'), (90, 'closed');