Pull a DND report from FreePBX for the current day, or specified day for a user extension. Send via email.

today-dnd.pl

#!/usr/bin/perl -w
# This will search the CDR logs for DND toggles for a user.
# Run it from cron if you want, its quiet. If you don't specify a date,
# it will pull today's date.
use strict;
use MIME::Lite;
use Time::Local;

my $ext = $ARGV[0] or die "Must provide extension number";

my $today = `date +%Y-%m-%d`; chomp $today;
# Get udate from ARGV, or use today's date
my $udate = $ARGV[1] || $today;

my $debug = $ARGV[2] || 0;

# Setup totaltime for the top of the message
my $totaltime = '';

# Connect to the DB
my $dbh = DBConnect();

print "Pulling times for $ext on $udate.\n" if $debug;

# Get the times DND was turned on with *78 and off with *79 from the Database for $udate
my $oq = "SELECT calldate FROM cdr WHERE dst = '*78' AND src = '$ext' AND calldate LIKE '$udate%'";
my $sth = $dbh->prepare($oq, { Slice => {} });
$sth->execute();
print $oq."\n" if $debug;

my $dndon = $sth->fetchall_arrayref();

$sth = $dbh->prepare("SELECT calldate FROM cdr WHERE dst = '*79' AND src = '$ext' AND calldate LIKE '$udate%'", { Slice => {} });
$sth->execute();
my $dndoff = $sth->fetchall_arrayref();


use Data::Dumper;
print Dumper($dndon) if $debug > 0;
print Dumper($dndoff) if $debug > 0;

my @merged;

# Merge the two arrays into one
for my $d (@$dndon) {
    push @merged, { datetime => $d->[0], status => 'on' };
}

for my $d (@$dndoff) {
    push @merged, { datetime => $d->[0], status => 'off' };
}

# Sort the merged array by date and time
@merged = sort { $a->{datetime} cmp $b->{datetime} } @merged;

# loop through the merged array, the user has a start time of $start time and an end time of $end time.
# first, lets find the first time they turned it off, and find the minutes difference between $start and that time.
# then, find the next time they turned it on, and find the minutes difference between that time and the next time they turned it off.
# we want to end up with a count at the end of the day the total time they were on DND.
my $start = $udate.' 08:00:00'; # The user comes in at 8am
my $end = $udate.' 17:00:00'; # The user leaves at 5pm

# total mins they were on DND for the day
my $total = 0;

# Storage for the last on time.
my $tmpon = '';

# HTML Top of the message
my $html = 'DND For Today for Extension '.$ext."\n\n";


# loop through the merged array
for my $d (@merged) {
    my $timediff = 0;
    # if the first array value (start of the day) of the day is a DND off, 
    # then lets add to total the difference between $start and $d->{datetime}
    if ($d->{status} eq 'off' && $d == $merged[0]) {
        $timediff = &timediff($start,$d->{datetime});
        $html .= "Start of the day $start\n";
        $html .= "First DND off at $d->{datetime}, adding ".pmin($timediff)." to total\n\n";
        $total += $timediff;
        next;
    }

    # now lets calculate the times between the next DND off and the next DND on
    if ($d->{status} eq 'on') {
        # store the on time in $tmpon
        $tmpon = $d->{datetime};
        $html .= "DND turned on at $tmpon\n";
    }
    elsif ($d->{status} eq 'off') {
        # add the difference between $tmpon and $d->{datetime} to $total
        $timediff = &timediff($tmpon,$d->{datetime});
        $html .= "DND off at $d->{datetime}, adding ".pmin($timediff)." to total\n\n";
        #print "Start: $tmpon, End: $d->{datetime}\n" if $debug > 0;
        $total += $timediff;
    }
}

$totaltime = "\nTotal DND time for $ext on $udate was ".pmin($total).".\n\n";
$html .= $totaltime;

$html .= "Details of each DND toggle:\n";

# Print the sorted results
foreach my $entry (@merged) {
    my $datetime = $entry->{datetime};
    my $status = $entry->{status};
    $html .= "$datetime - $status\n";
}

     my $msg = MIME::Lite->new(
            From     => '"PBX Admin" <admin@pbxserver.com>',
            To       => 'manager@employees.com',
            Subject  => "Todays DND for $ext",
            Type     => 'text/plain',
            Data     => $totaltime."\n\n".$html,
        );
        $msg->send('smtp','localhost', Debug=>0 );
    
    print $html if $debug > 0;

# ---------------------------------------------------------------------- #
# pmin()                                                                 #
# Print minutes in a human readable format.                              #
# ---------------------------------------------------------------------- #
sub pmin {
    my $min = shift;

    my $hours = int($min / 60);
    my $minutes = $min % 60;

    return("$hours hours, $minutes minutes");
}

# ---------------------------------------------------------------------- #
# timediff()                                                             #
# Calculate the difference between two times. Return the difference in   #
# minutes.                                                               #
# ---------------------------------------------------------------------- #
sub timediff {
    my ($start,$end) = @_;

    # Split the start and end times into their respective parts
    my ($syear,$smonth,$sday,$shour,$smin,$ssec) = split(/[- :]/,$start);
    my ($eyear,$emonth,$eday,$ehour,$emin,$esec) = split(/[- :]/,$end);

    # Convert the times to epoch time
    my $stime = timelocal($ssec,$smin,$shour,$sday,$smonth-1,$syear);
    my $etime = timelocal($esec,$emin,$ehour,$eday,$emonth-1,$eyear);

    # Calculate the difference in minutes
    my $diff = ($etime - $stime) / 60;

    return($diff);
}

# ---------------------------------------------------------------------- #
# DBConnect()                                                            #
# Connect to the MySQL database. Return $dbh (database handle)           #
# ---------------------------------------------------------------------- #
sub DBConnect {
    use DBI;

    my $DSN = "DBI:mysql:database=asteriskcdrdb;";
    my $dbh = DBI->connect($DSN, 'root', 'furry-sack-bear-has-your-honey-m8')
        or die("Database Error: $DBI::errstr;");

    return($dbh);
}