path: root/extensions/BMO/lib/Reports/ReleaseTracking.pm

# 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::BMO::Reports::ReleaseTracking;

use 5.10.1;
use strict;
use warnings;

use Bugzilla::Constants;
use Bugzilla::Error;
use Bugzilla::Extension::BMO::Util;
use Bugzilla::Field;
use Bugzilla::FlagType;
use Bugzilla::Util qw(trick_taint validate_date);
use JSON qw(-convert_blessed_universally);
use List::MoreUtils qw(uniq);

use constant DATE_RANGES => [
    {
        value => '20160126-20160307',
        label => '2016-01-26 and 2016-03-07'
    },
    {
        value => '20151215-20160125',
        label => '2015-12-15 and 2016-01-25'
    },
    {
        value => '20151103-20151214',
        label => '2015-11-03 and 2015-12-14'
    },
    {
        value => '20150922-20151102',
        label => '2015-09-22 and 2015-11-02'
    },
    {
        value => '20150811-20150921',
        label => '2015-08-11 and 2015-09-21'
    },
    {
        value => '20150630-20150810',
        label => '2015-06-30 and 2015-08-10'
    },
    {
        value => '20150512-20150629',
        label => '2015-05-12 and 2015-06-29'
    },
    {
        value => '20150331-20150511',
        label => '2015-03-31 and 2015-05-11'
    },
    {
        value => '20150224-20150330',
        label => '2015-02-24 and 2015-03-30'
    },
    {
        value => '20150113-20150223',
        label => '2015-01-13 and 2015-02-23'
    },
    {
        value => '20141111-20141222',
        label => '2014-11-11 and 2014-12-22'
    },
    {
        value => '20140930-20141110',
        label => '2014-09-30 and 2014-11-10'
    },
    {
        value => '20140819-20140929',
        label => '2014-08-19 and 2014-09-29'
    },
    {
        value => '20140708-20140818',
        label => '2014-07-08 and 2014-08-18'
    },
    {
        value => '20140527-20140707',
        label => '2014-05-27 and 2014-07-07'
    },
    {
        value => '20140415-20140526',
        label => '2014-04-15 and 2014-05-26'
    },
    {
        value => '20140304-20140414',
        label => '2014-03-04 and 2014-04-14'
    },
    {
        value => '20140121-20140303',
        label => '2014-01-21 and 2014-03-03'
    },
    {
        value => '20131210-20140120',
        label => '2013-12-10 and 2014-01-20'
    },
    {
        value => '20131029-20131209',
        label => '2013-10-29 and 2013-12-09'
    },
    {
        value => '20130917-20131028',
        label => '2013-09-17 and 2013-10-28'
    },
    {
        value => '20130806-20130916',
        label => '2013-08-06 and 2013-09-16'
    },
    {
        value => '20130625-20130805',
        label => '2013-06-25 and 2013-08-05'
    },
    {
        value => '20130514-20130624',
        label => '2013-05-14 and 2013-06-24'
    },
    {
        value => '20130402-20130513',
        label => '2013-04-02 and 2013-05-13'
    },
    {
        value => '20130219-20130401',
        label => '2013-02-19 and 2013-04-01'
    },
    {
        value => '20130108-20130218',
        label => '2013-01-08 and 2013-02-18'
    },
    {
        value => '20121120-20130107',
        label => '2012-11-20 and 2013-01-07'
    },
    {
        value => '20121009-20121119',
        label => '2012-10-09 and 2012-11-19'
    },
    {
        value => '20120828-20121008',
        label => '2012-08-28 and 2012-10-08'
    },
    {
        value => '20120717-20120827',
        label => '2012-07-17 and 2012-08-27'
    },
    {
        value => '20120605-20120716',
        label => '2012-06-05 and 2012-07-16'
    },
    {
        value => '20120424-20120604',
        label => '2012-04-24 and 2012-06-04'
    },
    {
        value => '20120313-20120423',
        label => '2012-03-13 and 2012-04-23'
    },
    {
        value => '20120131-20120312',
        label => '2012-01-31 and 2012-03-12'
    },
    {
        value => '20111220-20120130',
        label => '2011-12-20 and 2012-01-30'
    },
    {
        value => '20111108-20111219',
        label => '2011-11-08 and 2011-12-19'
    },
    {
        value => '20110927-20111107',
        label => '2011-09-27 and 2011-11-07'
    },
    {
        value => '20110816-20110926',
        label => '2011-08-16 and 2011-09-26'
    },
    {
        value => '*',
        label => 'Anytime'
    }
];

sub report {
    my ($vars) = @_;
    my $dbh = Bugzilla->dbh;
    my $input = Bugzilla->input_params;
    my $user = Bugzilla->user;

    my @flag_names = qw(
        approval-mozilla-release
        approval-mozilla-beta
        approval-mozilla-aurora
        approval-mozilla-central
        approval-comm-release
        approval-comm-beta
        approval-comm-aurora
        approval-calendar-release
        approval-calendar-beta
        approval-calendar-aurora
        approval-mozilla-esr10
    );

    my @flags_json;
    my @fields_json;
    my @products_json;

    #
    # tracking flags
    #

    my $all_products = $user->get_selectable_products;
    my @usable_products;

    # build list of flags and their matching products

    my @invalid_flag_names;
    foreach my $flag_name (@flag_names) {
        # grab all matching flag_types
        my @flag_types = @{Bugzilla::FlagType::match({ name => $flag_name, is_active => 1 })};

        # remove invalid flags
        if (!@flag_types) {
            push @invalid_flag_names, $flag_name;
            next;
        }

        # we need a list of products, based on inclusions/exclusions
        my @products;
        my %flag_types;
        foreach my $flag_type (@flag_types) {
            $flag_types{$flag_type->name} = $flag_type->id;
            my $has_all = 0;
            my @exclusion_ids;
            my @inclusion_ids;
            foreach my $flag_type (@flag_types) {
                if (scalar keys %{$flag_type->inclusions}) {
                    my $inclusions = $flag_type->inclusions;
                    foreach my $key (keys %$inclusions) {
                        push @inclusion_ids, ($inclusions->{$key} =~ /^(\d+)/);
                    }
                } elsif (scalar keys %{$flag_type->exclusions}) {
                    my $exclusions = $flag_type->exclusions;
                    foreach my $key (keys %$exclusions) {
                        push @exclusion_ids, ($exclusions->{$key} =~ /^(\d+)/);
                    }
                } else {
                    $has_all = 1;
                    last;
                }
            }

            if ($has_all) {
                push @products, @$all_products;
            } elsif (scalar @exclusion_ids) {
                push @products, @$all_products;
                foreach my $exclude_id (uniq @exclusion_ids) {
                    @products = grep { $_->id != $exclude_id } @products;
                }
            } else {
                foreach my $include_id (uniq @inclusion_ids) {
                    push @products, grep { $_->id == $include_id } @$all_products;
                }
            }
        }
        @products = uniq @products;
        push @usable_products, @products;
        my @product_ids = map { $_->id } sort { lc($a->name) cmp lc($b->name) } @products;

        push @flags_json, {
            name => $flag_name,
            id => $flag_types{$flag_name} || 0,
            products => \@product_ids,
            fields => [],
        };
    }
    foreach my $flag_name (@invalid_flag_names) {
        @flag_names = grep { $_ ne $flag_name } @flag_names;
    }
    @usable_products = uniq @usable_products;

    # build a list of tracking flags for each product
    # also build the list of all fields

    my @unlink_products;
    foreach my $product (@usable_products) {
        my @fields =
            sort { $a->sortkey <=> $b->sortkey }
            grep { is_active_status_field($_) }
            Bugzilla->active_custom_fields({ product => $product });
        my @field_ids = map { $_->id } @fields;
        if (!scalar @fields) {
            push @unlink_products, $product;
            next;
        }

        # product
        push @products_json, {
            name => $product->name,
            id => $product->id,
            fields => \@field_ids,
        };

        # add fields to flags
        foreach my $rh (@flags_json) {
            if (grep { $_ eq $product->id } @{$rh->{products}}) {
                push @{$rh->{fields}}, @field_ids;
            }
        }

        # add fields to fields_json
        foreach my $field (@fields) {
            my $existing = 0;
            foreach my $rh (@fields_json) {
                if ($rh->{id} == $field->id) {
                    $existing = 1;
                    last;
                }
            }
            if (!$existing) {
                push @fields_json, {
                    name => $field->name,
                    desc => $field->description,
                    id => $field->id,
                };
            }
        }
    }
    foreach my $rh (@flags_json) {
        my @fields = uniq @{$rh->{fields}};
        $rh->{fields} = \@fields;
    }

    # remove products which aren't linked with status fields

    foreach my $rh (@flags_json) {
        my @product_ids;
        foreach my $id (@{$rh->{products}}) {
            unless (grep { $_->id == $id } @unlink_products) {
                push @product_ids, $id;
            }
            $rh->{products} = \@product_ids;
        }
    }

    #
    # run report
    #

    if ($input->{q} && !$input->{edit}) {
        my $q = _parse_query($input->{q});

        my @where;
        my @params;
        my $query = "
            SELECT DISTINCT b.bug_id
              FROM bugs b
                   INNER JOIN flags f ON f.bug_id = b.bug_id\n";

        if ($q->{start_date}) {
            $query .= "INNER JOIN bugs_activity a ON a.bug_id = b.bug_id\n";
        }

        $query .= "WHERE ";

        if ($q->{start_date}) {
            push @where, "(a.fieldid = ?)";
            push @params, $q->{field_id};

            push @where, "(CONVERT_TZ(a.bug_when, 'UTC', 'America/Los_Angeles') >= ?)";
            push @params, $q->{start_date} . ' 00:00:00';
            push @where, "(CONVERT_TZ(a.bug_when, 'UTC', 'America/Los_Angeles') <= ?)";
            push @params, $q->{end_date} . ' 23:59:59';

            push @where, "(a.added LIKE ?)";
            push @params, '%' . $q->{flag_name} . $q->{flag_status} . '%';
        }

        my ($type_id) = $dbh->selectrow_array(
            "SELECT id FROM flagtypes WHERE name = ?",
            undef,
            $q->{flag_name}
        );
        push @where, "(f.type_id = ?)";
        push @params, $type_id;

        push @where, "(f.status = ?)";
        push @params, $q->{flag_status};

        if ($q->{product_id}) {
            push @where, "(b.product_id = ?)";
            push @params, $q->{product_id};
        }

        if (scalar @{$q->{fields}}) {
            my @fields;
            foreach my $field (@{$q->{fields}}) {
                my $field_sql = "(";
                if ($field->{type} == FIELD_TYPE_EXTENSION) {
                    $field_sql .= "
                        COALESCE(
                            (SELECT tracking_flags_bugs.value
                               FROM tracking_flags_bugs
                                    LEFT JOIN tracking_flags
                                         ON tracking_flags.id = tracking_flags_bugs.tracking_flag_id
                              WHERE tracking_flags_bugs.bug_id = b.bug_id
                                    AND tracking_flags.name = " . $dbh->quote($field->{name}) . ")
                        , '') ";
                }
                else {
                    $field_sql .= "b." . $field->{name};
                }
                $field_sql .= " " . ($field->{value} eq '+' ? '' : 'NOT ') . "IN ('fixed','verified'))";
                push(@fields, $field_sql);
            }
            my $join = uc $q->{join};
            push @where, '(' . join(" $join ", @fields) . ')';
        }

        $query .= join("\nAND ", @where);

        my $bugs = $dbh->selectcol_arrayref($query, undef, @params);
        push @$bugs, 0 unless @$bugs;

        my $urlbase = Bugzilla->localconfig->{urlbase};
        my $cgi = Bugzilla->cgi;
        print $cgi->redirect(
            -url => "${urlbase}buglist.cgi?bug_id=" . join(',', @$bugs)
        );
        exit;
    }

    #
    # set template vars
    #

    my $json = JSON->new()->shrink(1);
    $vars->{flags_json} = $json->encode(\@flags_json);
    $vars->{products_json} = $json->encode(\@products_json);
    $vars->{fields_json} = $json->encode(\@fields_json);
    $vars->{flag_names} = \@flag_names;
    $vars->{ranges} = DATE_RANGES;
    $vars->{default_query} = $input->{q};
    $vars->{is_custom} = $input->{is_custom};
    foreach my $field (qw(product flags range)) {
        $vars->{$field} = $input->{$field};
    }
}

sub _parse_query {
    my $q = shift;
    my @query = split(/:/, $q);
    my $query;

    # field_id for flag changes
    $query->{field_id} = get_field_id('flagtypes.name');

    # flag_name
    my $flag_name = shift @query;
    @{Bugzilla::FlagType::match({ name => $flag_name, is_active => 1 })}
        or ThrowUserError('report_invalid_parameter', { name => 'flag_name' });
    trick_taint($flag_name);
    $query->{flag_name} = $flag_name;

    # flag_status
    my $flag_status = shift @query;
    $flag_status =~ /^([\?\-\+])$/
        or ThrowUserError('report_invalid_parameter', { name => 'flag_status' });
    $query->{flag_status} = $1;

    # date_range -> from_ymd to_ymd
    my $date_range = shift @query;
    if ($date_range ne '*') {
        $date_range =~ /^(\d\d\d\d)(\d\d)(\d\d)-(\d\d\d\d)(\d\d)(\d\d)$/
            or ThrowUserError('report_invalid_parameter', { name => 'date_range' });
        $query->{start_date} = "$1-$2-$3";
        $query->{end_date} = "$4-$5-$6";
        validate_date($query->{start_date})
          || ThrowUserError('illegal_date', { date   => $query->{start_date},
                                              format => 'YYYY-MM-DD' });
        validate_date($query->{end_date})
          || ThrowUserError('illegal_date', { date   => $query->{end_date},
                                              format => 'YYYY-MM-DD' });
    }

    # product_id
    my $product_id = shift @query;
    $product_id =~ /^(\d+)$/
        or ThrowUserError('report_invalid_parameter', { name => 'product_id' });
    $query->{product_id} = $1;

    # join
    my $join = shift @query;
    $join =~ /^(and|or)$/
        or ThrowUserError('report_invalid_parameter', { name => 'join' });
    $query->{join} = $1;

    # fields
    my @fields;
    foreach my $field (@query) {
        $field =~ /^(\d+)([\-\+])$/
            or ThrowUserError('report_invalid_parameter', { name => 'fields' });
        my ($id, $value) = ($1, $2);
        my $field_obj = Bugzilla::Field->new($id)
            or ThrowUserError('report_invalid_parameter', { name => 'field_id' });
        push @fields, { id => $id, value => $value,
                        name => $field_obj->name, type => $field_obj->type };
    }
    $query->{fields} = \@fields;

    return $query;
}

1;