-- phpMyAdmin SQL Dump
-- version 2.7.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jan 25, 2007 at 09:04 AM
-- Server version: 5.0.22
-- PHP Version: 4.4.2
--
-- Database: `bugtracker`
--
-- --------------------------------------------------------
--
-- Table structure for table `custom_report`
--
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');
INSERT INTO `custom_report` VALUES (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=''''');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (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%>%''');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (6, 'Detail report by status and client (form)', 1, 0, NULL, '
\r\n
\r\n
new
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
feedback
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
acknowledged
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
confirmed
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
assigned
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
resolved
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
closed
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
Customer
\r\n
\r\n
\r\n
\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');
INSERT INTO `custom_report` VALUES (7, 'Bugs by Date', 2, 0, 'Lists the number of bugs broken down by date (last 30 days)', '
\r\n
\r\n
Status
\r\n
\r\n\r\n
\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');
INSERT INTO `custom_report` VALUES (8, 'List Bugs', 2, 0, NULL, '
\r\n
\r\n
Date (yyyy-mm-dd)
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
Status
\r\n
\r\n\r\n
\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');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (11, 'Readme Generator', 1, 0, NULL, '
\r\n
\r\n
From (yyyy-mm-dd)
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
\r\n
To (yyyy-mm-dd)
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
\r\n
Read Me Keyword
\r\n
\r\n\r\n
\r\n
\r\n
\r\n\r\n
Status
\r\n
\r\n\r\n
\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%>%''');
INSERT INTO `custom_report` VALUES (12, 'Detail report by status and client ordered', 1, 0, 'ordered by username, status', '
\r\n
\r\n
new
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
feedback
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
acknowledged
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
confirmed
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
assigned
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
resolved
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
closed
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
Customer
\r\n
\r\n
\r\n
\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');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (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%>');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (18, 'Bugs Not Related to 1222', 2, 1, 'Lists all bugs not related to 1222', '
\r\n
\r\n
Date (yyyy-mm-dd)
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
Status
\r\n
\r\n\r\n
\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');
INSERT INTO `custom_report` VALUES (19, 'List Bugs by Fixed In Version', 2, 1, NULL, '
\r\n
\r\n
Version
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
Status
\r\n
\r\n\r\n
\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');
INSERT INTO `custom_report` VALUES (20, 'List Open Issues', 2, 1, 'Display open issues by version and/or assigned to name', '
\r\n
\r\n
Version
\r\n
\r\n\r\n
\r\n
\r\n
\r\n
Assigned To
\r\n
\r\n\r\n
\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'')');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (22, 'Tester Close Numbers', 2, 1, 'Shows the number of issues closed per day by username', '
\r\n
\r\n
username
\r\n
\r\n \r\nUse % sign as a wild card. \r\nA % sign by itself will return all users.\r\n
\r\n
\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');
INSERT INTO `custom_report` VALUES (23, 'Release Notes', 2, 1, 'Displays release note messages', '
\r\n
\r\n
Version
\r\n
\r\n\r\n (ex. ''2.5'' or ''2.6'')
\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\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');
INSERT INTO `custom_report` VALUES (24, 'Known Issues', 2, 1, 'Displays release note known issues', '
\r\n
\r\n
Version
\r\n
\r\n\r\n (ex. ''2.5'' or ''2.6'')
\r\n
\r\n
\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');
INSERT INTO `custom_report` VALUES (26, 'Management Summary Report w/Links', 2, 0, NULL, '
\r\n
\r\n
Version
\r\n
\r\n\r\n
\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'')');
INSERT INTO `custom_report` VALUES (27, 'Developer Resolve Numbers', 2, 1, 'Shows the number of issues resolved per week by username', '
\r\n
\r\n
username
\r\n
\r\n \r\nUse % sign as a wild card. \r\nA % sign by itself will return all users.\r\n
\r\n
\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');
INSERT INTO `custom_report` VALUES (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');
INSERT INTO `custom_report` VALUES (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'')');
INSERT INTO `custom_report` VALUES (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
Updated Since (yyyy-mm-dd)
\r\n
\r\n\r\n
\r\n
\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');
INSERT INTO `custom_report` VALUES (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
Updated Since (yyyy-mm-dd)
\r\n
\r\n\r\n
\r\n
\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''');