#(!/usr/local/bin/perl

#
# generates useful statistics on processing trouble tickets
#

use warnings;
use strict;

use diagnostics;
use POSIX;
use DBI;
use Data::Dumper;

# DBI attributes
my %dbattrs     = (
        RaiseError      => 1,   # saves a lot of error code
        PrintError      => 1,
        AutoCommit      => 0,
        LongReadLen     => 0,
        LongTruncOk     => 0,
        Warn            => 1,
        Taint           => 0,
);

# globals
my (    # handles
        $dbh,
        $sth,
        $newref,
);
my (    # variables we use with or from SQL
        $selectyear,
        $selectweek,
        $maxyear,
        $minyear,
        $sql_newreqs,
);
my (    # miscellaneous globals
        $weektotal,
        $unassigned,
        $total,
);

# SQL dates of week
my @days = (
        1, 2, 3, 4, 5, 6, 7,
);

# SRS priorities
my @pris = (
        "A1", "A2", "A3",
        "B1", "B2", "B3",
        "C1", "C2", "C3",
);

# global DBI settings
my $dbi_dbd     = "Sybase";             # actually MS-SQL 6.5, TDS protocol 4.2
my $sql_server  = "dbhost.mycorp.com";  # beware, no protection
my $dbname      = "myname";             # not case sensitive
my $dblogin     = "mylogin";
my $dbpass      = "mydbpass";
my $dbconnstr   = "dbi:$dbi_dbd"        . ":"
                . "server=$sql_server"  . ";"
                . "database=$dbname"    . ";"
;

# table field name -> perl var name mappings
my $table       = "arse";
my $submit_date = "datesub";
my $opened_date = "dateopen";
my $closed_date = "dateclosed";
my $uniqid      = "arse_id";
my $priority    = "priority";

### subroutines

# Takes a SQL selection statement and returns a scalar of
# the first field from the first row returned by the select.
# Useful primarily for getting output of SQL computations
# without actually selecting records from the database
# itself.  Note that we use a private statement handle for
# these so they can be used within an outer query loop
# easily.
sub
do_sql_select
{
        my $sth;
        my $selection;

        $selection = "select " . $_[0];
        $sth = $dbh->prepare ($selection);
        $sth->execute() || exit (1);

        return $sth->fetchrow_array();
}

sub
usage_exit
{
        my $errstring   = $_[0];
        my $progname    = $0;

        print ("$progname: usage error: $errstring\n");
        print <<EOH;

spits out SRS statistics for given year and week.

syntax: $progname <year> <week>

year is a four-digit Gregorian year
week is a one or two -digit ordinal week of the year numbered from 1

EOH
        print ("exiting...\n");

        exit (1);
}

sub
validate_arguments
{
        $#ARGV == 1 ||
                usage_exit ("wrong number of arguments given");

        $selectyear = $ARGV[0];
        $selectweek = $ARGV[1];

        length ($selectyear) == 4 ||
                usage_exit ("year requires four digits");
        length ($selectweek) == 2 ||
                length ($selectweek) == 1 ||
                        usage_exit ("week requires one or two digits");

        ($selectyear =~ /[^\d]/) &&
                usage_exit ("non-digits given for year");
        ($selectweek =~ /[^\d]/) &&
                usage_exit ("non-digits given for week");
}

sub
print_header
{
        my $arg = pop (@_);
        print "\n\n$arg:\n\n";
        return;
}

#
# - we should only be called to make the "pri x day"
#   weektotals tables (ie, new and closed reports
#
sub
do_bypri_table
{
        my (
                $day,
                $pri,
        );

        my $header      = $_[0];        # what to print
        my $fwidth      = $_[1];        # column width
        my $start_date  = $_[2];        # field name that starts time range
        my $end_date    = $_[3];        # field name that ends time range
        my $target_date = $_[4];        # field name of selection targets

        my %daytotals   = ();
        my %pritotals   = ();

        $total          = 0;
        $weektotal      = 0;

        foreach $pri (@pris) {
                $pritotals{$pri} = 0;
        }

        print_header ($header);

        printf "%$fwidth" . "s|", "day ";
        foreach my $colhead (@pris) {
                printf "%$fwidth" . "s", $colhead;
        }
        printf "%-$fwidth" . "s", "   |  ";
        printf "%-$fwidth" . "s", "total\n";
        print "-" x 80;
        print "\n";

        # DBD::Sybase->MS-SQL 6.5 == TDS 4.2 == no
        # placeholders, amazingly slow
        foreach $day (@days) {
                $daytotals{$day} = 0;
                printf "%$fwidth" . "s|", "$day ";
                foreach $pri (@pris) {
                        my (
                                $sth,
                                $results,
                                $result,
                        );
                        my $pricount = 0;
                        $sth = $dbh->prepare (qq{
                                select  $uniqid,
                                        $start_date,
                                        $end_date,
                                        $priority
                                from    $table
                                where   datepart(yy, $target_date) = $selectyear
                                and     datepart(wk, $target_date) = $selectweek
                                and     datepart(dw, $target_date) = $day
                                and     $priority = "$pri"
                        });

                        $sth->execute() ||
                                die "SQL query execute method failed";

                        $results = $sth->fetchall_hashref ($uniqid);
                        foreach $result (keys (%$results)) {
                                my (
                                        $query,
                                        $start,
                                        $end,
                                );
                                # this test is only useful for new SRSes, when
                                # we are called for the closed ones it's just
                                # a harmless but superfluous test
                                if (!defined ($results->{$result}->
                                              {$start_date})) {
                                        # SRS is still in wait queue, skip
                                        $unassigned++;
                                        next;
                                }
                                $start = $results->{$result}->{$start_date};
                                $end = $results->{$result}->{$end_date};
                                $query = "datediff(ss, \"$start\", \"$end\")";
                                $total += do_sql_select ($query);

                                $pritotals{$pri}++;
                                $daytotals{$day}++;
                                $pricount++;
                        }
                        printf "%$fwidth" . "s",
                               $pricount == 0 ? "." :
                               $pricount;
                }
                printf "%-$fwidth" . "s", "   |  ";
                printf "%$fwidth" . "s\n", "$daytotals{$day}";
        }
        print "-" x 80;
        print "\n";
        printf "%$fwidth" . "s|", "week ";
        foreach $pri (@pris) {
                printf "%$fwidth" . "s",
                       $pritotals{$pri} == 0 ? "." :
                       $pritotals{$pri};
        }

        $weektotal = 0;
        printf "%-$fwidth" . "s", "   |  ";
        foreach my $day (@days) {
                $weektotal += $daytotals{$day};
        }
        printf "%$fwidth" . "s\n", "$weektotal";
        print "\n";
}
^L
### runtime

validate_arguments();

$dbh = DBI->connect ($dbconnstr, $dblogin, $dbpass, \%dbattrs);

$maxyear = do_sql_select ("datepart(yy, max($submit_date)) from $table");
$minyear = do_sql_select ("datepart(yy, min($submit_date)) from $table");
if ($selectyear > $maxyear ||
    $selectyear < $minyear) {
        usage_exit ("year given is out of range database contains");
}

# master header for whole report
print "=" x 80 . "\n";
print "= ticket statistics for week $selectweek, year $selectyear, run ";
print POSIX::strftime ("%Y%m%d", localtime()) . "\n";
print "=" x 80 . "\n";
print "\n4 reports follow\n";

$unassigned = 0;

### report 1

do_bypri_table ("1: NEW REQUESTS", 6, $submit_date, $opened_date, $submit_date);
if ($weektotal > 0) {
        my $averagewaiting = $total / $weektotal / 60 / 60;
        printf " %.1f hours on average before assignment\n", $averagewaiting;
}
print " $unassigned as yet unassigned tickets ignored\n";

### report 2

do_bypri_table ("2: CLOSED REQUESTS", 6, $opened_date, $closed_date,
                $closed_date);
if ($weektotal > 0) {
        my $averageopen = $total / $weektotal / 60 / 60 / 24;
        printf " %.1f days on average before close\n", $averageopen;
}

exit (0);

#do_byday_table (3: 

^L