summaryrefslogtreecommitdiffstats
path: root/extensions/ProductDashboard/lib
diff options
context:
space:
mode:
Diffstat (limited to 'extensions/ProductDashboard/lib')
-rw-r--r--extensions/ProductDashboard/lib/Queries.pm475
-rw-r--r--extensions/ProductDashboard/lib/Util.pm95
2 files changed, 570 insertions, 0 deletions
diff --git a/extensions/ProductDashboard/lib/Queries.pm b/extensions/ProductDashboard/lib/Queries.pm
new file mode 100644
index 000000000..b58298ea8
--- /dev/null
+++ b/extensions/ProductDashboard/lib/Queries.pm
@@ -0,0 +1,475 @@
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+package Bugzilla::Extension::ProductDashboard::Queries;
+
+use strict;
+
+use base qw(Exporter);
+@Bugzilla::Extension::ProductDashboard::Queries::EXPORT = qw(
+ total_bugs
+ total_open_bugs
+ total_closed_bugs
+ by_version
+ by_value_summary
+ by_milestone
+ by_priority
+ by_severity
+ by_component
+ by_assignee
+ by_status
+ by_duplicate
+ by_popularity
+ recently_opened
+ recently_closed
+ total_bug_milestone
+ bug_milestone_by_status
+);
+
+use Bugzilla::CGI;
+use Bugzilla::User;
+use Bugzilla::Util;
+use Bugzilla::Component;
+use Bugzilla::Version;
+use Bugzilla::Milestone;
+
+use Bugzilla::Extension::ProductDashboard::Util qw(open_states closed_states
+ quoted_open_states quoted_closed_states);
+
+sub total_bugs {
+ my $product = shift;
+ my $dbh = Bugzilla->dbh;
+
+ return $dbh->selectrow_array("SELECT COUNT(bug_id)
+ FROM bugs
+ WHERE product_id = ?", undef, $product->id);
+}
+
+sub total_open_bugs {
+ my $product = shift;
+ my $bug_status = shift;
+ my $dbh = Bugzilla->dbh;
+
+ return $dbh->selectrow_array("SELECT COUNT(bug_id)
+ FROM bugs
+ WHERE bug_status IN (" . join(',', quoted_open_states()) . ")
+ AND product_id = ?", undef, $product->id);
+}
+
+sub total_closed_bugs {
+ my $product = shift;
+ my $dbh = Bugzilla->dbh;
+
+ return $dbh->selectrow_array("SELECT COUNT(bug_id)
+ FROM bugs
+ WHERE bug_status IN (" . join(',', quoted_closed_states()) . ")
+ AND product_id = ?", undef, $product->id);
+}
+
+sub bug_link_all {
+ my $product = shift;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name);
+}
+
+sub bug_link_open {
+ my $product = shift;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name) . "&bug_status=__open__";
+}
+
+sub bug_link_closed {
+ my $product = shift;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name) . "&bug_status=__closed__";
+}
+
+sub by_version {
+ my ($product, $bug_status) = @_;
+ my $dbh = Bugzilla->dbh;
+ my $extra = '';
+
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ return $dbh->selectall_arrayref("SELECT version, COUNT(bug_id),
+ ROUND(((COUNT(bugs.bug_id) / ( SELECT COUNT(*) FROM bugs WHERE bugs.product_id = ? $extra)) * 100))
+ FROM bugs
+ WHERE product_id = ?
+ $extra
+ GROUP BY version
+ ORDER BY COUNT(bug_id) DESC",
+ undef, $product->id, $product->id);
+}
+
+sub by_milestone {
+ my ($product, $bug_status) = @_;
+ my $dbh = Bugzilla->dbh;
+ my $extra = '';
+
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ return $dbh->selectall_arrayref("SELECT target_milestone, COUNT(bug_id),
+ ROUND(((COUNT(bugs.bug_id) / ( SELECT COUNT(*) FROM bugs WHERE bugs.product_id = ? $extra)) * 100))
+ FROM bugs
+ WHERE product_id = ?
+ $extra
+ GROUP BY target_milestone
+ ORDER BY COUNT(bug_id) DESC",
+ undef, $product->id, $product->id);
+}
+
+sub by_priority {
+ my ($product, $bug_status) = @_;
+ my $dbh = Bugzilla->dbh;
+ my $extra = '';
+
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ return $dbh->selectall_arrayref("SELECT priority, COUNT(bug_id),
+ ROUND(((COUNT(bugs.bug_id) / ( SELECT COUNT(*) FROM bugs WHERE bugs.product_id = ? $extra)) * 100))
+ FROM bugs
+ WHERE product_id = ?
+ $extra
+ GROUP BY priority
+ ORDER BY COUNT(bug_id) DESC",
+ undef, $product->id, $product->id);
+}
+
+sub by_severity {
+ my ($product, $bug_status) = @_;
+ my $dbh = Bugzilla->dbh;
+ my $extra = '';
+
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ return $dbh->selectall_arrayref("SELECT bug_severity, COUNT(bug_id),
+ ROUND(((COUNT(bugs.bug_id) / ( SELECT COUNT(*) FROM bugs WHERE bugs.product_id = ? $extra)) * 100))
+ FROM bugs
+ WHERE product_id = ?
+ $extra
+ GROUP BY bug_severity
+ ORDER BY COUNT(bug_id) DESC",
+ undef, $product->id, $product->id);
+}
+
+sub by_component {
+ my ($product, $bug_status) = @_;
+ my $dbh = Bugzilla->dbh;
+ my $extra = '';
+
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ return $dbh->selectall_arrayref("SELECT components.name, COUNT(bugs.bug_id),
+ ROUND(((COUNT(bugs.bug_id) / ( SELECT COUNT(*) FROM bugs WHERE bugs.product_id = ? $extra)) * 100))
+ FROM bugs INNER JOIN components ON bugs.component_id = components.id
+ WHERE bugs.product_id = ?
+ $extra
+ GROUP BY components.name
+ ORDER BY COUNT(bugs.bug_id) DESC",
+ undef, $product->id, $product->id);
+}
+
+sub by_value_summary {
+ my ($product, $type, $value, $bug_status) = @_;
+ my $dbh = Bugzilla->dbh;
+
+ my $query = "SELECT bugs.bug_id AS id,
+ bugs.bug_status AS status,
+ bugs.version AS version,
+ components.name AS component,
+ bugs.bug_severity AS severity,
+ bugs.short_desc AS summary
+ FROM bugs, components
+ WHERE bugs.product_id = ?
+ AND bugs.component_id = components.id ";
+
+ if ($type eq 'component') {
+ Bugzilla::Component->check({ product => $product, name => $value });
+ $query .= "AND components.name = ? " if $type eq 'component';
+ }
+ elsif ($type eq 'version') {
+ Bugzilla::Version->check({ product => $product, name => $value });
+ $query .= "AND bugs.version = ? " if $type eq 'version';
+ }
+ elsif ($type eq 'target_milestone') {
+ Bugzilla::Milestone->check({ product => $product, name => $value });
+ $query .= "AND bugs.target_milestone = ? " if $type eq 'target_milestone';
+ }
+
+ $query .= "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ") " if $bug_status eq 'open';
+ $query .= "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ") " if $bug_status eq 'closed';
+
+ trick_taint($value);
+
+ my $past_due_bugs = $dbh->selectall_arrayref($query .
+ "AND (bugs.deadline IS NOT NULL AND bugs.deadline != '')
+ AND bugs.deadline < now() ORDER BY bugs.deadline LIMIT 10",
+ {'Slice' => {}}, $product->id, $value);
+
+ my $updated_recently_bugs = $dbh->selectall_arrayref($query .
+ "AND bugs.delta_ts != bugs.creation_ts " .
+ "ORDER BY bugs.delta_ts DESC LIMIT 10",
+ {'Slice' => {}}, $product->id, $value);
+
+ my $timestamp = $dbh->selectrow_array("SELECT " . $dbh->sql_date_format("LOCALTIMESTAMP(0)", "%Y-%m-%d"));
+
+ return {
+ timestamp => $timestamp,
+ past_due => _filter_bugs($past_due_bugs),
+ updated_recently => _filter_bugs($updated_recently_bugs),
+ };
+}
+
+sub by_assignee {
+ my ($product, $bug_status, $limit) = @_;
+ my $dbh = Bugzilla->dbh;
+ my $extra = '';
+
+ $limit = ($limit && detaint_natural($limit)) ? $dbh->sql_limit($limit) : "";
+
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ my @result = map { [ Bugzilla::User->new($_->[0]), $_->[1], $_->[2] ] }
+ @{$dbh->selectall_arrayref("SELECT bugs.assigned_to AS userid, COUNT(bugs.bug_id),
+ ROUND(((COUNT(bugs.bug_id) / ( SELECT COUNT(*) FROM bugs WHERE bugs.product_id = ? $extra)) * 100))
+ FROM bugs, profiles
+ WHERE bugs.product_id = ?
+ AND bugs.assigned_to = profiles.userid
+ $extra
+ GROUP BY profiles.login_name
+ ORDER BY COUNT(bugs.bug_id) DESC $limit",
+ undef, $product->id, $product->id)};
+
+ return \@result;
+}
+
+sub by_status {
+ my ($product, $bug_status) = @_;
+ my $dbh = Bugzilla->dbh;
+ my $extra = '';
+
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ return $dbh->selectall_arrayref("SELECT bugs.bug_status, COUNT(bugs.bug_id),
+ ROUND(((COUNT(bugs.bug_id) / ( SELECT COUNT(*) FROM bugs WHERE bugs.product_id = ? $extra)) * 100))
+ FROM bugs
+ WHERE bugs.product_id = ?
+ $extra
+ GROUP BY bugs.bug_status
+ ORDER BY COUNT(bugs.bug_id) DESC",
+ undef, $product->id, $product->id);
+}
+
+sub total_bug_milestone {
+ my ($product, $milestone) = @_;
+ my $dbh = Bugzilla->dbh;
+
+ return $dbh->selectrow_array("SELECT COUNT(bug_id)
+ FROM bugs
+ WHERE target_milestone = ?
+ AND product_id = ?",
+ undef, $milestone->name, $product->id);
+}
+
+sub bug_milestone_by_status {
+ my ($product, $milestone, $bug_status) = @_;
+ my $dbh = Bugzilla->dbh;
+ my $extra = '';
+
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ return $dbh->selectrow_array("SELECT COUNT(bug_id)
+ FROM bugs
+ WHERE target_milestone = ?
+ AND product_id = ? $extra",
+ undef,
+ $milestone->name,
+ $product->id);
+
+}
+
+sub by_duplicate {
+ my ($product, $bug_status, $limit) = @_;
+ my $dbh = Bugzilla->dbh;
+ $limit = ($limit && detaint_natural($limit)) ? $dbh->sql_limit($limit) : "";
+
+ my $extra = '';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ my $unfiltered_bugs = $dbh->selectall_arrayref("SELECT bugs.bug_id AS id,
+ bugs.bug_status AS status,
+ bugs.version AS version,
+ components.name AS component,
+ bugs.bug_severity AS severity,
+ bugs.short_desc AS summary,
+ COUNT(duplicates.dupe) AS dupe_count
+ FROM bugs, duplicates, components
+ WHERE bugs.product_id = ?
+ AND bugs.component_id = components.id
+ AND bugs.bug_id = duplicates.dupe_of
+ $extra
+ GROUP BY bugs.bug_id, bugs.bug_status, components.name,
+ bugs.bug_severity, bugs.short_desc
+ HAVING COUNT(duplicates.dupe) > 1
+ ORDER BY COUNT(duplicates.dupe) DESC $limit",
+ {'Slice' => {}}, $product->id);
+
+ return _filter_bugs($unfiltered_bugs);
+}
+
+sub by_popularity {
+ my ($product, $bug_status, $limit) = @_;
+ my $dbh = Bugzilla->dbh;
+ $limit = ($limit && detaint_natural($limit)) ? $dbh->sql_limit($limit) : "";
+
+ my $extra = '';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")" if $bug_status eq 'open';
+ $extra = "AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")" if $bug_status eq 'closed';
+
+ my $unfiltered_bugs = $dbh->selectall_arrayref("SELECT bugs.bug_id AS id,
+ bugs.bug_status AS status,
+ bugs.version AS version,
+ components.name AS component,
+ bugs.bug_severity AS severity,
+ bugs.short_desc AS summary,
+ bugs.votes AS votes
+ FROM bugs, components
+ WHERE bugs.product_id = ?
+ AND bugs.component_id = components.id
+ AND bugs.votes > 1
+ $extra
+ ORDER BY bugs.votes DESC $limit",
+ {'Slice' => {}}, $product->id);
+
+ return _filter_bugs($unfiltered_bugs);
+}
+
+sub recently_opened {
+ my ($params) = @_;
+ my $dbh = Bugzilla->dbh;
+
+ my $product = $params->{'product'};
+ my $days = $params->{'days'};
+ my $limit = $params->{'limit'};
+ my $date_from = $params->{'date_from'};
+ my $date_to = $params->{'date_to'};
+
+ $days ||= 7;
+ $limit = ($limit && detaint_natural($limit)) ? $dbh->sql_limit($limit) : "";
+
+ my @values = ($product->id);
+
+ my $date_part;
+ if ($date_from && $date_to) {
+ validate_date($date_from)
+ || ThrowUserError('illegal_date', { date => $date_from,
+ format => 'YYYY-MM-DD' });
+ validate_date($date_to)
+ || ThrowUserError('illegal_date', { date => $date_to,
+ format => 'YYYY-MM-DD' });
+ $date_part = "AND bugs.creation_ts >= ? AND bugs.creation_ts <= ?";
+ push(@values, trick_taint($date_from), trick_taint($date_to));
+ }
+ else {
+ $date_part = "AND bugs.creation_ts >= CURRENT_DATE() - INTERVAL ? DAY";
+ push(@values, $days);
+ }
+
+ my $unfiltered_bugs = $dbh->selectall_arrayref("SELECT bugs.bug_id AS id,
+ bugs.bug_status AS status,
+ bugs.version AS version,
+ components.name AS component,
+ bugs.bug_severity AS severity,
+ bugs.short_desc AS summary
+ FROM bugs, components
+ WHERE bugs.product_id = ?
+ AND bugs.component_id = components.id
+ AND bugs.bug_status IN (" . join(',', quoted_open_states()) . ")
+ $date_part
+ ORDER BY bugs.bug_id DESC $limit",
+ {'Slice' => {}}, @values);
+
+ return _filter_bugs($unfiltered_bugs);
+}
+
+sub recently_closed {
+ my ($params) = @_;
+ my $dbh = Bugzilla->dbh;
+
+ my $product = $params->{'product'};
+ my $days = $params->{'days'};
+ my $limit = $params->{'limit'};
+ my $date_from = $params->{'date_from'};
+ my $date_to = $params->{'date_to'};
+
+ $days ||= 7;
+ $limit = ($limit && detaint_natural($limit)) ? $dbh->sql_limit($limit) : "";
+
+ my @values = ($product->id);
+
+ my $date_part;
+ if ($date_from && $date_to) {
+ validate_date($date_from)
+ || ThrowUserError('illegal_date', { date => $date_from,
+ format => 'YYYY-MM-DD' });
+ validate_date($date_to)
+ || ThrowUserError('illegal_date', { date => $date_to,
+ format => 'YYYY-MM-DD' });
+ $date_part = "AND bugs_activity.bug_when >= ? AND bugs_activity.bug_when <= ?";
+ push(@values, trick_taint($date_from), trick_taint($date_to));
+ }
+ else {
+ $date_part = "AND bugs_activity.bug_when >= CURRENT_DATE() - INTERVAL ? DAY";
+ push(@values, $days);
+ }
+
+ my $unfiltered_bugs = $dbh->selectall_arrayref("SELECT DISTINCT bugs.bug_id AS id,
+ bugs.bug_status AS status,
+ bugs.version AS version,
+ components.name AS component,
+ bugs.bug_severity AS severity,
+ bugs.short_desc AS summary
+ FROM bugs, components, bugs_activity
+ WHERE bugs.product_id = ?
+ AND bugs.component_id = components.id
+ AND bugs.bug_status IN (" . join(',', quoted_closed_states()) . ")
+ AND bugs.bug_id = bugs_activity.bug_id
+ AND bugs_activity.added IN (" . join(',', quoted_closed_states()) . ")
+ $date_part
+ ORDER BY bugs.bug_id DESC $limit",
+ {'Slice' => {}}, @values);
+
+ return _filter_bugs($unfiltered_bugs);
+}
+
+sub _filter_bugs {
+ my ($unfiltered_bugs) = @_;
+ my $dbh = Bugzilla->dbh;
+
+ return [] if !$unfiltered_bugs;
+
+ my @unfiltered_bug_ids = map { $_->{'id'} } @$unfiltered_bugs;
+ my %filtered_bug_ids = map { $_ => 1 } @{ Bugzilla->user->visible_bugs(\@unfiltered_bug_ids) };
+
+ my @filtered_bugs;
+ foreach my $bug (@$unfiltered_bugs) {
+ next if !$filtered_bug_ids{$bug->{'id'}};
+ push(@filtered_bugs, $bug);
+ }
+
+ return \@filtered_bugs;
+}
+
+1;
diff --git a/extensions/ProductDashboard/lib/Util.pm b/extensions/ProductDashboard/lib/Util.pm
new file mode 100644
index 000000000..5d9c161ef
--- /dev/null
+++ b/extensions/ProductDashboard/lib/Util.pm
@@ -0,0 +1,95 @@
+# This Source Code Form is subject to the terms of the Mozilla Public
+# License, v. 2.0. If a copy of the MPL was not distributed with this
+# file, You can obtain one at http://mozilla.org/MPL/2.0/.
+#
+# This Source Code Form is "Incompatible With Secondary Licenses", as
+# defined by the Mozilla Public License, v. 2.0.
+package Bugzilla::Extension::ProductDashboard::Util;
+
+use strict;
+
+use base qw(Exporter);
+@Bugzilla::Extension::ProductDashboard::Util::EXPORT = qw(
+ bug_link_all
+ bug_link_open
+ bug_link_closed
+ open_states
+ closed_states
+ quoted_open_states
+ quoted_closed_states
+ bug_milestone_link_total
+ bug_milestone_link_open
+ bug_milestone_link_closed
+);
+
+use Bugzilla::Status;
+use Bugzilla::Util;
+
+our $_open_states;
+sub open_states {
+ $_open_states ||= Bugzilla::Status->match({ is_open => 1, isactive => 1 });
+ return wantarray ? @$_open_states : $_open_states;
+}
+
+our $_quoted_open_states;
+sub quoted_open_states {
+ my $dbh = Bugzilla->dbh;
+ $_quoted_open_states ||= [ map { $dbh->quote($_->name) } open_states() ];
+ return wantarray ? @$_quoted_open_states : $_quoted_open_states;
+}
+
+our $_closed_states;
+sub closed_states {
+ $_closed_states ||= Bugzilla::Status->match({ is_open => 0, isactive => 1 });
+ return wantarray ? @$_closed_states : $_closed_states;
+}
+
+our $_quoted_closed_states;
+sub quoted_closed_states {
+ my $dbh = Bugzilla->dbh;
+ $_quoted_closed_states ||= [ map { $dbh->quote($_->name) } closed_states() ];
+ return wantarray ? @$_quoted_closed_states : $_quoted_closed_states;
+}
+
+sub bug_link_all {
+ my $product = shift;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name);
+}
+
+sub bug_link_open {
+ my $product = shift;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name) .
+ "&bug_status=__open__";
+}
+
+sub bug_link_closed {
+ my $product = shift;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name) .
+ "&bug_status=__closed__";
+}
+
+sub bug_milestone_link_total {
+ my ($product, $milestone) = @_;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name) .
+ "&target_milestone=" . url_quote($milestone->name);
+}
+
+sub bug_milestone_link_open {
+ my ($product, $milestone) = @_;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name) .
+ "&target_milestone=" . url_quote($milestone->name) . "&bug_status=__open__";
+}
+
+sub bug_milestone_link_closed {
+ my ($product, $milestone) = @_;
+
+ return correct_urlbase() . 'buglist.cgi?product=' . url_quote($product->name) .
+ "&target_milestone=" . url_quote($milestone->name) . "&bug_status=__closed__";
+}
+
+1;
generated by cgit v1.2.3-24-g4f1b (git 2.32.0) at 2024-11-18 03:24:25 +0100