ngcp-reminder

#!/usr/bin/perl -w
use strict;
use warnings;

use Config::Tiny;
use File::Temp qw(tempfile);
use File::Basename;
use File::Copy;
use DBI;
use Readonly;

Readonly my $config_file => "/etc/ngcp-reminder/reminder.conf";
Readonly my $owner => 'asterisk';

my $config = Config::Tiny->read($config_file)
    or die "Program stopping, couldn't open the configuration file '$config_file'.\n";
my %cfg = ( %{$config->{_}} );

if (!defined $cfg{weekdays} || $cfg{weekdays} =~ /^\s*$/) {
    $cfg{weekdays} = '2,3,4,5,6,7';
}
my @wdays = split /\s*,\s*/, $cfg{weekdays};

my $dsn = "DBI:mysql:database=$cfg{database};host=$cfg{dbhost};port=0";

my $dbh = DBI->connect($dsn, @{cfg}{qw(dbuser dbpassword)})
    or die "Cannot connect to db: ".$DBI::errstr;
my $sth = $dbh->prepare(<<SQL);
SELECT s.username, d.domain, r.recur, r.id, vup.value as lang
FROM provisioning.voip_reminder r
LEFT JOIN provisioning.voip_subscribers s ON r.subscriber_id = s.id
LEFT JOIN provisioning.voip_domains d ON s.domain_id = d.id
LEFT JOIN billing.v_subscriber_timezone tz ON tz.uuid = s.uuid
LEFT JOIN provisioning.voip_usr_preferences vup ON r.subscriber_id=vup.subscriber_id
LEFT JOIN provisioning.voip_preferences vp ON vp.id=vup.attribute_id
WHERE r.active = 1
AND vp.attribute = "language"
AND r.time = time_format(CONVERT_TZ(now(), "localtime", tz.name), '%H:%i:00')
UNION
SELECT s.username, d.domain, r.recur, r.id, vdp.value as lang
FROM provisioning.voip_reminder r
LEFT JOIN provisioning.voip_subscribers s ON r.subscriber_id = s.id
LEFT JOIN provisioning.voip_domains d ON s.domain_id = d.id
LEFT JOIN billing.v_subscriber_timezone tz ON tz.uuid = s.uuid
LEFT JOIN provisioning.voip_dom_preferences vdp ON vdp.domain_id=d.id
LEFT JOIN provisioning.voip_preferences vp ON vp.id=vdp.attribute_id
WHERE r.active = 1
AND vp.attribute = "language"
AND r.time = time_format(CONVERT_TZ(now(), "localtime", tz.name), '%H:%i:00') LIMIT 1;
SQL
my $sth_d = $dbh->prepare("UPDATE voip_reminder SET active=0 WHERE id=?");

$sth->execute() or die "Cannot execute: ".$DBI::errstr;

while (my $ref = $sth->fetchrow_hashref()) {
    print "$ref->{'username'}\@$ref->{'domain'}, recur=$ref->{'recur'}, language=$ref->{'lang'}\n";

    if ($ref->{'recur'} eq "weekdays") {
        my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = gmtime(time);
        $wday++; # make sun=1, sat=7
        next unless grep { /^$wday$/ } @wdays;
    }

    my ($tmp, $tmp_filename) = tempfile("$ref->{'username'}.XXXXXX", DIR => $cfg{tmpdir}, UNLINK => 0);
    unless (defined $tmp) {
        die "Failed to create temporary call file: $!\n";
    }

    print "Using tmpfile '$tmp_filename'\n";

    print $tmp "Channel: SIP/$cfg{sip_peer}/$ref->{'username'}__AT__$ref->{'domain'}\n";
    print $tmp "MaxRetries: $cfg{retries}\n";
    print $tmp "RetryTime: $cfg{retry_time}\n";
    print $tmp "WaitTime: $cfg{wait_time}\n";
    print $tmp "Extension: s\n";
    print $tmp "Context: $cfg{context}\n";
    print $tmp "Priority: 1\n";
    print $tmp "Setvar: LANG=$ref->{'lang'}\n";
    close $tmp;

    my ($login,$pass,$uid,$gid) = getpwnam($owner)
                                    or die "user '$owner' not in passwd file";
    chown $uid, $gid, $tmp_filename;
    chmod 0600, $tmp_filename;

    my $out_filename = "$cfg{spool}/".basename($tmp_filename);
    move "$tmp_filename", $out_filename
        or die "Failed to move call '$tmp_filename' file to spool: $!\n";

    if($ref->{'recur'} eq "never") {
        $sth_d->execute($ref->{'id'})
            or die "Cannot execute: ".$DBI::errstr;
    }
}

$sth->finish;
$sth_d->finish;

$dbh->disconnect;

exit 0;