Merge lp:~hjarrell555/ubuntu-qa-website/bug_1366579 into lp:ubuntu-qa-website

[Hunter]

Changed the defects report to add sorting to status and importance columns.

[Nicholas Skaggs (nskaggs)]

Hunter, I see status and importance are sortable; do you think we could make any field sortable? Should be a trivial enough tweak yes?

I'm having trouble testing this on my local install, so I'll take your word for it that it's tests out a-ok.

[Nicholas Skaggs (nskaggs)]

review: Needs Information

[Nicholas Skaggs (nskaggs)]

Also Hunter, it would be useful if you could writeup / script up using that production db dump on a new instance of the qatracker. Perhaps expand the script to do it? I wiped my old install, and now I'm having trouble recreating what I had you do, hah!

[Hunter (hjarrell555)]

I only restored the qatracker_launchpad_bug table but I could expand it to the other tables too if you wanted. While I'm at school the command I used was: pg_restore --data-only --table=qatracker_launchpad_bug <datadump> > bugs.pg

Then psql -f bug.pg

I then just created test milestones and series and products like the isodev setup guide and added bug numbers pulled from SELECT bugnumber FROM qatracker_launchpad_bug;

[Hunter (hjarrell555)]

I only restored the qatracker_launchpad_bug table but I could expand it to the other tables too if you wanted. While I'm at school the command I used was: pg_restore --data-only --table=qatracker_launchpad_bug <datadump> > bugs.pg

Then psql -f bug.pg

I then just created test milestones and series and products like the isodev setup guide and added bug numbers pulled from SELECT bugnumber FROM qatracker_launchpad_bug;

[Nicholas Skaggs (nskaggs)]

Ahh, you only restored the specific table. That sounds like a good idea. The full restore doesn't work out so well.

[Hunter (hjarrell555)]

Yeah when I tried a restore of just the full dumo I broke my install.

[Hunter (hjarrell555)]

Yeah any table can be sorted the change only has to be done on the header. It just needs a field value with the database field that column represents and a default sort order. Ex:

$header = array(
array('data' => 'Column Name', 'field' => 'databasefield', 'sort' => 'ASC')
);

Also about the database restore I forgot that last night I was going to use the pg_restore to restore just the data from all the qatracker tables and I left it about 20min and it still wasn't finished, so I canceled it.

[Nicholas Skaggs (nskaggs)]

Hunter, yes, I would just add the sorting for all fields to the header. With that, this has my approval.

[lp:~hjarrell555/ubuntu-qa-website/bug_1366579]

416. By Hunter on 2016-01-09

Added sortability to all columns except bugnumber for defects report

[Hunter (hjarrell555)]

The only issue is that I couldn't get the bug number to sort. I was getting an error with the tablesort module for the Bug # column.

[Nicholas Skaggs (nskaggs)]

LGTM, I will confirm in testing tomorrow.

[Nicholas Skaggs (nskaggs)]

review: Approve

=== modified file 'modules/qatracker/report/qatracker.report.defects.php'
--- modules/qatracker/report/qatracker.report.defects.php	2012-11-06 21:45:44 +0000
+++ modules/qatracker/report/qatracker.report.defects.php	2016-01-09 00:57:52 +0000
@@ -45,53 +45,66 @@
     return $content;
 }
 
-function qatracker_getbugs_for_release($siteid) {
+function qatracker_getbugs_for_release($siteid, $header) {
     /* 
      * Get all the bugs for all the milestones of a release 
      * for a given siteid 
      */
-
-    $result = db_query("
-SELECT  DISTINCT m.title mtitle
-       ,l.bugnumber
-       ,l.product
-       ,l.title btitle
-       ,l.status
-       ,l.importance 
-       ,l.assignee
-       ,l.commentscount
-       ,l.duplicatescount
-       ,l.subscriberscount
-       ,m.id
-       ,(CASE 
+    $query = db_select('qatracker_build', 'b')
+                ->extend('TableSort')
+                ->extend('PagerDefault');
+    $query->distinct();
+    $query->addField('m', 'title', 'mtitle');
+    $query->fields('l', array(
+        'bugnumber',
+        'product',
+        'title'));
+    $query->addField('l', 'title', 'btitle');
+    $query->fields('l', array(
+        'status',
+        'importance',
+        'assignee',
+        'commentscount',
+        'duplicatescount',
+        'subscriberscount'
+        ));
+    $query->addField('m', 'id');
+    $query->addExpression("CASE 
             WHEN l.importance = 'Critical' THEN 0
             WHEN l.importance = 'High' THEN 1
             WHEN l.importance = 'Medium' THEN 2
             WHEN l.importance = 'Low' THEN 3
             WHEN l.importance = 'Undecided' THEN 10
-        ELSE 5 END
-        ) importance_order
-FROM qatracker_build b
-JOIN qatracker_product p ON p.id = b.productid
-JOIN qatracker_build_milestone bm ON bm.buildid = b.id
-JOIN qatracker_milestone m ON bm.milestoneid = m.id
-JOIN qatracker_testsuite_product tp ON tp.productid = b.productid AND tp.milestone_seriesid = m.seriesid
-JOIN qatracker_testsuite_testcase tt ON tt.testsuiteid = tp.testsuiteid
-JOIN qatracker_testcase t ON tt.testcaseid = t.id
-JOIN qatracker_result r ON (r.testcaseid = t.id AND r.buildid = b.id)
-JOIN qatracker_bug g ON g.resultid = r.id
-JOIN qatracker_launchpad_bug l ON g.bugnumber = l.originalbug
-WHERE m.siteid = :siteid
-AND SUBSTRING(m.title, '^[^ ]+') IN (
-    SELECT DISTINCT SUBSTRING(m2.title, '^[^ ]+') 
-    FROM qatracker_milestone m2
-    WHERE m2.status IN (0,1)
-    AND m2.siteid = m.siteid
-)
-AND tt.status IN (0, 2)
-ORDER by m.id DESC, importance_order, l.status", array(":siteid" => intval($siteid)));
-
-    return $result->fetchAll();
+        ELSE 5 END", 'importance_order');
+    $query->join('qatracker_product', 'p', 'p.id = b.productid');
+    $query->join('qatracker_build_milestone', 'bm', 'bm.buildid = b.id');
+    $query->join('qatracker_milestone', 'm', 'bm.milestoneid = m.id');
+    $query->join('qatracker_testsuite_product', 'tp', 'tp.productid = b.productid AND tp.milestone_seriesid = m.seriesid');
+    $query->join('qatracker_testsuite_testcase', 'tt', 'tt.testsuiteid = tp.testsuiteid');
+    $query->join('qatracker_testcase', 't', 'tt.testcaseid = t.id');
+    $query->join('qatracker_result', 'r', '(r.testcaseid = t.id AND r.buildid = b.id)');
+    $query->join('qatracker_bug', 'g', 'g.resultid = r.id');
+    $query->join('qatracker_launchpad_bug', 'l', 'g.bugnumber = l.originalbug');
+    $query->condition('m.siteid', intval($siteid));
+
+    $subquery = db_select('qatracker_milestone', 'm2');
+    $subquery->distinct();
+    $subquery->addExpression("SUBSTRING(m2.title, '^[^ ]+')");
+    $or = db_or();
+    $or->condition('m2.status', 0, '=');
+    $or->condition('m2.status', 1, '=');
+    $subquery->condition($or);
+    $subquery->condition('m2.siteid', 'm.siteid');
+
+    $query->addExpression("SUBSTRING(m.title, '^[^ ]+') IN (" . $subquery->__toString() . ')');
+    $or = db_or();
+    $or->condition('tt.status', 0, '=');
+    $or->condition('tt.status', 2, '=');
+    $query->condition($or);
+    $query->condition('tt.status', array(0, 2), 'IN');
+    $query->orderBy('m.id', 'DESC');
+    $query->orderByHeader($header);
+    return $query->execute()->fetchAll();
 }
 
 
@@ -122,17 +135,17 @@
 
     $header = array(
         array('data' => 'Bug #'),
-        array('data' => 'Title'),
-        array('data' => 'Affects'),
-        array('data' => 'Status'),
-        array('data' => 'Importance'),
-        array('data' => 'Assignee'),
-        array('data' => 'Com.'),
-        array('data' => 'Sub.'),
-        array('data' => 'Dup.'),
+        array('data' => 'Title', 'field' => 'btitle', 'sort' => 'DESC'),
+        array('data' => 'Affects', 'field' => 'l.product', 'sort' => 'DESC'),
+        array('data' => 'Status', 'field' => 'l.status', 'sort' => 'DESC'),
+        array('data' => 'Importance', 'field' => 'l.importance', 'sort' => 'DESC'),
+        array('data' => 'Assignee', 'field' => 'l.assignee', 'sort' => 'DESC'),
+        array('data' => 'Com.', 'field' => 'l.commentscount', 'sort' => 'DESC'),
+        array('data' => 'Sub.', 'field' => 'l.subscriberscount', 'sort' => 'DESC'),
+        array('data' => 'Dup.', 'field' => 'l.duplicatescount', 'sort' => 'DESC'),
     );
 
-    $bugs = qatracker_getbugs_for_release($siteid);
+    $bugs = qatracker_getbugs_for_release($siteid, $header);
 
     $curr_milestone = "";
     $rows = array();