summaryrefslogtreecommitdiffstats
path: root/extensions/ProductDashboard/lib/Queries.pm
blob: fe5d049776b3e550179b593e13bb5ac61ea5221e (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
# 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::Search;
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 $extra;

    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;