#!/usr/bin/perl
use strict;
use warnings;
use MIME::Lite;
my $options = { newidlehours => 4 * 24, openidlehours => 5 * 24, stalledidlehours => 20 * 24, debug => 0, emailfromaddr => 'Request Tracker <rt@example.com>', emailtoaddr => ['James Bromberger <james.bromberger@example.com>']};

##########################################################################
sub debug {
  my ($level, $message) = @_;
  return unless $level <= $options->{debug};
  printf "%s: %s\n", (caller(1))[3]||"main", $message;
}

use strict;
use lib '/usr/share/request-tracker3.8/lib/', '/usr/local/share/request-tracker3.8/plugins/RT-Authen-ExternalAuth/lib/', '/usr/local/share/request-tracker3.8/plugins/RT-Extension-ActivityReports/lib/', '/usr/local/share/request-tracker3.8/plugins/RTx-Calendar/lib/';
use RT::Interface::CLI qw(CleanEnv GetCurrentUser GetMessageContent loc);
use Getopt::Long;
GetOptions($options, "debug=i", "newidlehours=i", "openidlehours=i", "stalledidlehours=i", "queue=s@", "emailfromaddr=s", "emailtoaddr=s@", "help");
help() if defined $options->{help};
die "No queue specified" unless defined $options->{queue};
die "No destination for the email" unless defined $options->{emailtoaddr};
# Clean our the environment
CleanEnv();
# Load the RT configuration
RT::LoadConfig();
# Initialise RT
RT::Init();
# Load rest of the RT stuff (seems to go after initialization)
use RT::Date;
use RT::Queue;
use RT::Tickets;
#use RT::Action::SendEmail;



sub help {
print <<FOO;
$0 [ --queue <name> [ --queue <name2> ... ]]
     [ --newidlehours <i> ] [ --openidlehours <i> ] [ --stalledidlehours <i> ]
     [ --emailfromaddr <address> ] [ --emailtoaddr <address> ]
     [ --help ]
FOO
  exit;
}


sub find_idle_tickets_for_queue {
  my $queue = shift;
  my $state = shift;
  my $idle_results;

  debug(3, "Running idle search for queue $queue");
  my $date_ticket_rt = new RT::Date($RT::SystemUser);
  my $date_now_rt= new RT::Date($RT::SystemUser);
  $date_now_rt->SetToNow();

  foreach my $state qw(new open stalled) {
    debug(5, "  Running idle search for queue $queue for state $state");
    my $tickets = RT::Tickets->new($RT::SystemUser);
    $tickets->FromSQL(
      'Type = "ticket" AND '.
      'Status = "'. $state.'" AND '.
      'Queue = "'. $queue . '" AND '.
      'Updated < "' . $options->{$state . "idlehours"} . ' hours ago"'
      );
    $tickets->OrderBy(FIELD => 'Due', ORDER => 'ASC');
    $idle_results->{$state}{count} = 0;
    while (my $ticket = $tickets->Next) {
      $idle_results->{$state}{count}++;
      my $Requestor = $ticket->Requestors->UserMembersObj(Recursively => 1)->Next;
      debug(5, sprintf "Name: %s. Telephone: %s. Mobile: %s", $Requestor->RealName, $Requestor->MobilePhone, $Requestor->WorkPhone || "");
      $date_ticket_rt->Set(Format => "ISO",Value => $ticket->Created);
      my $created_relative_days = ($date_now_rt->Unix - $date_ticket_rt->Unix) / (3600 * 24);
      debug(2, sprintf "Ticket was created %d days ago", $created_relative_days);
      $idle_results->{$state}{summary}.= sprintf "<TR><TD><SPAN CLASS=\"%s\">$state</SPAN></TD><TD ALIGN=\"CENTER\"><A HREF=\"http://rt.example.com/Ticket/Display.html?id=%d\">%d</A></TD><TD>%s</TD><TD ALIGN=\"center\"><SMALL>%.1f</SMALL></TD><TD><SMALL>%s %s</SMALL></TD></TR>\n",
       $ticket->FirstCustomFieldValue("Severity") || "",
       $ticket->Id,
       $ticket->Id,
       length($ticket->Subject) > 30 ? substr($ticket->Subject, 0, 27) . "...": $ticket->Subject,
       $created_relative_days,
       defined($Requestor->RealName) ? (length($Requestor->RealName) > 25 ?  substr($ticket->RequestorRealName, 0, 22) . "..." : $Requestor->RealName) : (length($Requestor->RealName) > 25 ? substr($ticket->RequestorAddresses, 0, 22) . "..." : $ticket->RequestorAddresses),
       $Requestor->WorkPhone || $Requestor->MobilePhone  || "";
    }
    $idle_results->{total}{count} += $idle_results->{$state}{count};
  }

  debug(4, sprintf " ... Found %s idle tickets in %s", $idle_results->{total}{count}, $queue);
  return $idle_results;
}

sub count_total_current_and_unassigned_in_queue {
  my $queue = shift;

  debug(3, "Running current and unowned report for queue $queue");
  my $current_ticket_count = 0;
  my $unowned_ticket_count = 0;
  my $tickets_current = RT::Tickets->new($RT::SystemUser);
  my $tickets_unowned = RT::Tickets->new($RT::SystemUser);

  $tickets_current->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "resolved" AND Status != "rejected") AND '.
    'Queue = "'. $queue  . '"'
    );
  $tickets_unowned->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "resolved" AND Status != "rejected") AND '.
    'Queue = "'. $queue  . '" AND (Owner = "Nobody" OR Owner = "NULL")'
    );
  debug(4, sprintf " ... Found %d currently in queue, and %d unowned", $tickets_current->Count, $tickets_unowned->Count);
  my $tickets_current_severity_count;
  while (my $ticket = $tickets_current->Next) {
    $tickets_current_severity_count->{$ticket->FirstCustomFieldValue("Severity")||"Not set"}++;
  }
  return ($tickets_current->Count, $tickets_unowned->Count, $tickets_current_severity_count);
}

sub find_sla_for_currently_open_tickets_in_queue {
  my $queue = shift;
  debug(3, "Running open ticket SLA report for queue $queue");
  my $tickets_in_sla = RT::Tickets->new($RT::SystemUser);
  my $tickets_out_sla = RT::Tickets->new($RT::SystemUser);
  $tickets_in_sla->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "resolved" AND Status != "rejected") AND '.
    'Queue = "'. $queue  . '" AND Due <= "now"'
    );
  $tickets_out_sla->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "resolved" AND Status != "rejected") AND '.
    'Queue = "'. $queue  . '" AND Due > "now"'
    );
  my $ticket_sum = $tickets_in_sla->Count + $tickets_out_sla->Count;
  return unless $ticket_sum > 0;
  my $ticket_sla_percent = 100 * ($tickets_out_sla->Count / $ticket_sum);
  debug(4, sprintf " ... Found %.2f%% outside SLA in queue %s" , $ticket_sla_percent, $queue);
  return ($tickets_in_sla->Count, $tickets_out_sla->Count, $ticket_sla_percent);
}

sub find_sla_for_all_tickets_this_month_in_queue {
  my $queue = shift;
  my $tickets = RT::Tickets->new($RT::SystemUser);
  my $start_of_month = sprintf "%4d-%02d-01",
    (localtime())[5] + 1900,
    (localtime())[4] + 1;
  my $now = sprintf "%4d-%02d-%02d %02d:%02d:%02d",
    (localtime())[5] + 1900,
    (localtime())[4] + 1,
    (localtime())[3],
    (localtime())[2],
    (localtime())[1],
    (localtime())[0];


  $tickets->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "rejected") AND '.
    '(Resolved >= "'. $start_of_month . '" OR Status != "resolved") AND '.
    'Queue = "'. $queue  . '"'
    );

  my $stats;
  $stats->{withinSLA}=0;
  $stats->{outsideSLA}=0;
  while (my $ticket  = $tickets->Next) {
    debug(8, sprintf "ID: %s, Status: %s, Due: %s", $ticket->Id, $ticket->Status, $ticket->Due);

    if ($ticket->Status eq "resolved" && $ticket->Due gt $ticket->Resolved) {
      debug(6, sprintf "Ticket %d resolved in time", $ticket->Id);
      $stats->{withinSLA}++;
    } elsif ($ticket->Status ne "resolved" && $ticket->Status ne "stalled" && $ticket->Due gt $now) {
      debug(6, sprintf "Ticket %d not yet resolved but OK", $ticket->Id);
    } else {
      debug(6, sprintf "Ticket %d failed SLA", $ticket->Id);
      $stats->{outsideSLA}++;
    }
  }
  my $ticket_count = $stats->{outsideSLA} + $stats->{withinSLA};
  return unless $ticket_count > 0;
  my $ticket_sla_met_percent = $stats->{withinSLA} / $ticket_count * 100;
  debug(3, sprintf "So far this month (starting %s), Queue '%s' SLA was %.2f%%", $start_of_month, $queue, $ticket_sla_met_percent);
  return ($stats->{withinSLA}, $stats->{outsideSLA}, $ticket_sla_met_percent);
}

sub find_sla_for_all_tickets_last_month_in_queue {
  my $queue = shift;
  my $tickets = RT::Tickets->new($RT::SystemUser);

  my $current_year = (localtime())[5] + 1900;
  my $current_month = (localtime())[4] + 1;

  my $start_of_last_month = sprintf "%4d-%02d-01",
    $current_month == 1 ? $current_year - 1 : $current_year,
    $current_month == 1 ? $ 12 : $current_month-1;

  my $start_of_this_month = sprintf "%4d-%02d-01",
    $current_year, $current_month;


  $tickets->FromSQL(
    'Type = "ticket" AND '.
    '(Status != "deleted" AND Status != "rejected") AND '.
    '((Resolved >= "'. $start_of_last_month . '" AND Resolved < "'. $start_of_this_month .'") OR (Created >= "'. $start_of_this_month .'" AND Resolved > "'.$start_of_this_month.'") OR Resolved = "NULL") AND '.
    'Queue = "'. $queue  . '"'
    );

  my $stats;
  $stats->{withinSLA}=0;
  $stats->{outsideSLA}=0;
  while (my $ticket  = $tickets->Next) {
    debug(8, sprintf "ID: %s, Status: %s, Due: %s", $ticket->Id, $ticket->Status, $ticket->Due);

    if ($ticket->Status eq "resolved" && $ticket->Due gt $ticket->Resolved) {
      debug(6, sprintf "Ticket %d resolved in time", $ticket->Id);
      $stats->{withinSLA}++;
    } elsif ($ticket->Status ne "resolved" && $ticket->Status ne "stalled" && $ticket->Due gt $start_of_this_month) {
      debug(6, sprintf "Ticket %d not yet resolved but OK", $ticket->Id);
    } else {
      debug(6, sprintf "Ticket %d failed SLA", $ticket->Id);
      $stats->{outsideSLA}++;
    }
  }
  my $ticket_count = $stats->{outsideSLA} + $stats->{withinSLA};
  return unless $ticket_count > 0;
  my $ticket_sla_met_percent = $stats->{withinSLA} / $ticket_count * 100;
  debug(3, sprintf "Last month (starting %s), Queue '%s' SLA was %.2f%%", $start_of_last_month, $queue, $ticket_sla_met_percent);
  return ($stats->{withinSLA}, $stats->{outsideSLA}, $ticket_sla_met_percent);
}


my $message_subject;

my $message_headline = "<P>Blackberry: <em>SPACE</em>=pg down, <em>ALT-SPACE</em>=pg up, <em>b</em>=bottom, <em>t</em>=top</P>\n";
my $message_body;
my $message_footer = sprintf "<p><small>Tickets are assumed idle if <em>NEW</em> is left for %.1f days , <em>OPEN</em> for %.1f days, and <em>STALLED</em> for %.1f days.</small></p>",
   $options->{newidlehours}/24, $options->{openidlehours}/24, $options->{stalledidlehours}/24;
my $total_tickets_idle_all_queues = 0;

foreach my $queue (@{$options->{queue}}) {
  my @subject_parts;

  my ($current_sla_in, $current_sla_out, $current_sla_pct) = find_sla_for_currently_open_tickets_in_queue($queue);
  my ($this_months_sla_in, $this_months_sla_out, $this_months_sla_pct) = find_sla_for_all_tickets_this_month_in_queue($queue);
  my ($last_month_sla_in, $last_months_sla_out, $last_months_sla_pct) = find_sla_for_all_tickets_last_month_in_queue($queue);
  my $idle_results = find_idle_tickets_for_queue($queue);
  my ($ticket_count_current_in_queue, $tickets_count_unowned, $tickets_current_severity_count) = count_total_current_and_unassigned_in_queue($queue);

  # Add the SLA review to the body of the message

  # Add the Idle ticket review to the body of the message and the subject of the message.
  if (defined($idle_results->{total}{count}) && $idle_results->{total}{count} > 0) {
    $message_headline.= sprintf "<p>$queue queue has %d idle of %d current tickets, with %d unassigned. ", $idle_results->{total}{count}, $ticket_count_current_in_queue, $tickets_count_unowned;
    $message_headline.= "Severities: " if defined($tickets_current_severity_count);

    foreach my $severity (sort { $a cmp $b} keys %{$tickets_current_severity_count}) {
      $message_headline.= sprintf "%d %s. ",
        $tickets_current_severity_count->{$severity},
        $severity;
    }

    if (defined($this_months_sla_pct) or defined($current_sla_pct)) {
      $message_headline.= "SLAs: ";
      $message_headline.= sprintf "Open now %.2f%%. ", $current_sla_pct if defined $current_sla_pct;
      $message_headline.= sprintf "This month %.2f%%. ", $this_months_sla_pct if defined $this_months_sla_pct;
      $message_headline.= sprintf "Last month %.2f%%. ", $last_months_sla_pct if defined $last_months_sla_pct;
    }

    $message_headline.= "</p>\n";

    $message_body.= "<TABLE><CAPTION>$queue Idle Tickets</CAPTION>\n";
    $message_body.= "<TR><TH>State</TH><TH>ID</TH><TH>Subject</TH><TH>Age <small>(Days)</small></TH><TH>Req.</TH></TR>";
    $message_body.= $idle_results->{new}{summary}  if $idle_results->{new}{count} > 0;
    $message_body.= $idle_results->{open}{summary}  if $idle_results->{open}{count} > 0;
    $message_body.= $idle_results->{stalled}{summary}  if $idle_results->{stalled}{count} > 0;
    $message_body.= "</TABLE>\n";

    $total_tickets_idle_all_queues+= $idle_results->{total}{count};
    push @subject_parts, sprintf  "%d/%d idle", $idle_results->{total}{count}, $ticket_count_current_in_queue;
  }

  push @subject_parts, sprintf "cur SLA %.2f%%",  $this_months_sla_pct if defined( $this_months_sla_pct);

  $message_subject.= ". " if defined($message_subject) && @subject_parts;
  $message_subject.= "$queue: ". join(', ', @subject_parts) if (@subject_parts);
}

my $style = <<FOO;
<style type="text/css">
BODY { margin: 2px; background-color: #E0E0FF; font-family: helvetica, arial, sans-serif; }
TABLE {
  border-width: 2px; border-spacing: 1px; border-style: outset; border-color: black; border-collapse: collapse; background-color: rgb(250, 240, 230);
  padding-bottom: 0; padding-top: 0; margin-top: 0;
}
TH { border-width: 1px; padding: 1px ; border-style: dashed ; border-color: black ; background-color: #CCCCCC; }
TD { border-width: 1px; padding: 1px; border-style: dashed; border-color: black; }
P { padding-top: 0; margin-top: 0; border-top: 0; }
H1 { font-size: medium;  padding-bottom: 0; margin-bottom: 0; border-bottom: 0;}
H2 { font-size: small;   padding-bottom: 0; margin-bottom: 0; border-bottom: 0;}
H3 { font-size: x-small; padding-bottom: 0; margin-bottom: 0; border-bottom: 0;}
.Critical { background-color: red; }
.Normal { background-color: orange; }
.Minor { background-color: blue; }
</style>
FOO
my $message_html = "<HTML><HEAD>$style</HEAD><BODY>" . $message_headline . "\n" . $message_body . "\n" . $message_footer . "</BODY></HTML>\n";
if (defined($message_headline)) {
  debug(1, $message_html);
  foreach my $rcpt (@{$options->{emailtoaddr}}) {
    my $msg = MIME::Lite->new(
      To => $rcpt,
      From => $options->{emailfromaddr},
      Subject => "RT Summary: $message_subject",
      Type => "multipart/related",
      );
    $msg->attach (
      Type =>'text/html',
      Data => $message_html);
    eval { $msg->send("smtp", "127.0.0.1", Debug => 0) };
    warn $@ if $@;
  }
}