metricstats.pm

codestriker is a develop useful tool to review code on web user interface.

###############################################################################
# Copyright (c) 2003 Jason Remillard.  All rights reserved.
#
# This program is free software; you can redistribute it and modify it under
# the terms of the GPL.

# Model object for handling metrics reports. This object is not an object, it
# is a normal module.

package Codestriker::Model::MetricStats;

use strict;
use Encode qw(decode_utf8);

use Codestriker::DB::DBI;

my $total_participants_header = 'Total Participants';
my $topic_size_lines_header = 'Topic Size In Lines';

# Returns the list of users that have participated in a Codestriker topics.
#
#
# Returns a collection of the following hash references.
# {
#   name
#   date_last_authored
#   date_last_participated
#   total_topics
# }

sub get_basic_user_metrics {
    # Gather basic user metrics for the past 16 weeks.
    my $last_n_days = 16*7;

    my $date = Codestriker->get_timestamp(time-($last_n_days*24*60*60));

    # Obtain a database connection.
    my $dbh = Codestriker::DB::DBI->get_connection();

    # Get the list of authors.
    my $author_list = $dbh->selectall_arrayref(
                                               'SELECT LOWER(author),MAX(modified_ts),COUNT(id)
         FROM topic
         WHERE modified_ts >= ?
         GROUP BY author
         ORDER BY 2 desc', {}, $date);

    my @users_metrics;

    foreach my $row (@$author_list) {
        my ($name, $last_authored_date, $count) = @$row;

        my $metrics =
          {
           name=>$name,
           date_last_authored=>
           int((time() - Codestriker->convert_date_timestamp_time($last_authored_date))/(60*60*24)),
           date_last_participated=>'',
           total_codestriker_time =>
           calculate_topic_view_time_for_user($date,$name),
           total_topics=>$count,
          };

        push(@users_metrics, $metrics);
    }

    # Get the list of participants from all these topics. You need to
    # submit at least one comment to be counted.
    my $participant_list = $dbh->selectall_arrayref(
                                                    'SELECT LOWER(commentdata.author),
            MAX(topic.modified_ts),
            COUNT(DISTINCT topic.id)
         FROM commentdata, commentstate, topic
         WHERE topic.modified_ts >= ? AND
           topic.id = commentstate.topicid AND
           LOWER(topic.author) <> LOWER(commentdata.author) AND
           commentdata.commentstateid = commentstate.id
         GROUP BY LOWER(commentdata.author)
         ORDER BY 2 desc',{}, $date);

    foreach my $row (@$participant_list) {
        my ($name, $last_participated_date, $count) = @$row;

        my $found = 0;
        foreach my $user (@users_metrics) {
            if ($user->{name} eq $name) {
                $user->{date_last_participated} =
                  int((time() -
                       Codestriker->convert_date_timestamp_time($last_participated_date))/(60*60*24));
                $user->{total_topics} += $count;
                $found = 1;
            }
        }

        if ($found == 0) {
            my $metrics =
              {
               name=>$name,
               date_last_authored=>'',
               date_last_participated=>
               int((time() -
                    Codestriker->convert_date_timestamp_time($last_participated_date))/(60*60*24)),
               total_topics=>$count,
               total_codestriker_time =>
               calculate_topic_view_time_for_user($date,$name),
              };

            push(@users_metrics, $metrics);
        }
    }

    # Close the connection, and check for any database errors.
    Codestriker::DB::DBI->release_connection($dbh, 1);

    return @users_metrics;
}

# Given the username, and the oldest date, calculate the total amount of
# codestriker time that was recorded in hours.
sub calculate_topic_view_time_for_user {
    my ($date,$user) = @_;

    # get the total time for this user.
    my $dbh = Codestriker::DB::DBI->get_connection();

    my $select_topic = $dbh->prepare_cached('SELECT creation_ts ' .
                                            'FROM topicviewhistory ' .
                                            'WHERE creation_ts > ? AND ' .
                                            'LOWER(email) = LOWER(?) ' .
                                            'ORDER BY creation_ts');

    $select_topic->execute($date,$user);

    my $total_time =
      Codestriker::Model::Metrics->calculate_topic_view_time($select_topic);

    Codestriker::DB::DBI->release_connection($dbh, 1);

    $total_time = sprintf("%1.1f",$total_time / (60*60));

    return $total_time;
}


# Returns a list of all the topic ids in the database.
sub get_topic_ids {
    # Obtain a database connection.
    my $dbh = Codestriker::DB::DBI->get_connection();

    # Get the list of authors.
    my $topicid_list  = $dbh->selectall_arrayref(
                                                 'SELECT id FROM topic ORDER BY creation_ts');

    # Close the connection, and check for any database errors.
    Codestriker::DB::DBI->release_connection($dbh, 1);

    return @$topicid_list;

}

# Returns in a hash the column names for the raw metric download feature.
sub get_download_headers {
    # Obtain a database connection.
    my $dbh = Codestriker::DB::DBI->get_connection();

    my @base_headers = qw(
                             TopicID
                             Author
                             Title
                             State
                             Creation
                             Project
                        );

    # Comment metric counts.
    my @comment_metric_headers;
    my $comment_metrics = $dbh->selectall_arrayref(
                                                   'SELECT DISTINCT name
         FROM commentstatemetric
         ORDER BY name
        ');
    foreach my $metric (@$comment_metrics) {
        push @comment_metric_headers, $metric->[0];
    }

    # Do the built-in comment metrics.
    push @comment_metric_headers, 'Comment Threads';
    push @comment_metric_headers, 'Submitted Comments';

    # Topic metrics counts.
    my @topic_metric_headers;
    my $topic_metrics = $dbh->selectall_arrayref(
                                                 'SELECT DISTINCT metric_name
         FROM topicmetric
         ORDER by metric_name
        ');

    foreach my $metric (@$topic_metrics) {
        push @topic_metric_headers, $metric->[0];
    }

    # Do the built in topic metrics.
    for (my $state = 0; $state < scalar(@Codestriker::topic_states);
         ++$state) {
        push @topic_metric_headers,
          'Time In ' . $Codestriker::topic_states[$state];
    }

    push @topic_metric_headers, $topic_size_lines_header;

    my @topic_user_metric_headers;

    # User topic metrics counts.
    my $user_topic_metrics  = $dbh->selectall_arrayref(
                                                       'SELECT DISTINCT metric_name
         FROM topicusermetric
         ORDER by metric_name
        ');

    foreach my $metric (@$user_topic_metrics) {
        push @topic_user_metric_headers, $metric->[0];
    }

    # Do the built in user metrics.
    push @topic_user_metric_headers, 'Codestriker Time';
    push @topic_user_metric_headers, $total_participants_header;

    my $headers =
      {
       base => \@base_headers,
       comment => \@comment_metric_headers,
       topic => \@topic_metric_headers,
       user => \@topic_user_metric_headers
      };

    # Close the connection, and check for any database errors.
    Codestriker::DB::DBI->release_connection($dbh, 1);

    return $headers;
}

# Given a topic id, and a header hash, return an list with all of the
# topics metric data returned in the order given by the header
# information.
sub get_raw_metric_data {
    my ($topicid, $headers) = @_;

    # Obtain a database connection.
    my $dbh = Codestriker::DB::DBI->get_connection();

    my @basic_topic_info = $dbh->selectrow_array('
        SELECT topic.id,
           LOWER(topic.author),
           topic.title,
           topic.state,
           topic.creation_ts,
           project.name
        FROM topic, project
        WHERE topic.id = ? AND
          topic.projectid = project.id',{}, $topicid);

    # Decode topic title and project name.
    $basic_topic_info[2] = decode_utf8($basic_topic_info[2]);
    $basic_topic_info[5] = decode_utf8($basic_topic_info[5]);

    if ($basic_topic_info[3] < @Codestriker::topic_states) {
        $basic_topic_info[3] =
          @Codestriker::topic_states[$basic_topic_info[3]];
    }

    $basic_topic_info[4] =
      Codestriker->format_date_timestamp($basic_topic_info[4]);

    $basic_topic_info[1] =
      Codestriker->filter_email($basic_topic_info[1]);

    my @row;

    push @row, @basic_topic_info;

    # Process the comment metric values.
    my $count_query =
      $dbh->prepare_cached('SELECT COUNT(*) ' .
                           'FROM topic, commentstate, commentstatemetric ' .
                           'WHERE topic.id = ? AND ' .
                           'topic.id = commentstate.topicid AND ' .
                           'commentstate.id = commentstatemetric.id AND '.
                           'commentstatemetric.name = ? ');
    foreach my $metric (@{$headers->{comment}}) {
        # Get the count for this metric name.
        $count_query->execute($topicid, $metric);
        my ($count) = $count_query->fetchrow_array();
        $count_query->finish();
        push @row, $count;
    }

    # Now process the 'Comment Threads' metric.
    my $comment_threads = $dbh->selectall_arrayref(
                                                   'SELECT COUNT(id)
         FROM commentstate
         WHERE topicid = ?
        ',{}, $topicid);
    push @row, $comment_threads->[0]->[0];

    # Now process the 'Submitted Comments' metric.
    my $submitted_comments = $dbh->selectall_arrayref(
                                                      'SELECT COUNT(id)
         FROM commentstate, commentdata
         WHERE commentstate.topicid = ?
             AND commentstate.id = commentdata.commentstateid
        ',{}, $topicid);
    push @row, $submitted_comments->[0]->[0];

    my $topic = Codestriker::Model::Topic->new($basic_topic_info[0]);

    my $metrics = $topic->get_metrics();

    my @topic_metrics = $metrics->get_topic_metrics();

    for (my $index = 0; $index < scalar(@{$headers->{topic}}); ++$index) {
        my $count = "";

        foreach my $metric ( @topic_metrics ) {
            $count = $metric->{value}
              if ($metric->{name} eq $headers->{topic}->[$index]);
        }

        if ($headers->{topic}->[$index] eq $topic_size_lines_header ) {