#!/usr/bin/perl -w

use DBI;
use strict;
use Time::Local;

# Config options

# Local DB connection settings
my $dsn = 'DBI:mysql:asterisk:localhost';
my $db_user_name = 'asteriskuser';
my $db_password = 'asteriskpass';
my $local_dbh = DBI->connect($dsn, $db_user_name, $db_password);

# Remote DB connection settings
$dsn = 'DBI:mysql:asterisk:localhost';
$db_user_name = 'asteriskuser';
$db_password = 'asteriskpass';
my $remote_dbh = DBI->connect($dsn, $db_user_name, $db_password);

# Define variables
my $l_field='LINES_NUMBER'; # field name with lines number
my $c_field='MAX_CALLS'; # maximum calls to one number
my $sql='1'; #sql string
my $sth='';
my $wday=0; #day of the week
my $numRows=0; #umber of found rows
my @array; # tmp array
my @queue_interval;
my @queue_voice;
my $queue_ids='';
my @task_arr;
my $l_count=0; # amount of free lines
my $max_tries=0;
my $tmp='';
my $i=0;
my $settings_check=0;
my $res2_ids='';
my $res3_ids='';
my $update_ids='';
my $ref='';
my $filee;

# Get current settings
&get_settings;

# Daemonize proccess
DAEMON:
$settings_check++;
if ($settings_check==4) {
    # Refresh settings
    &get_settings;
    $settings_check=0;
}
# Do the work
&work;
sleep 2;
goto DAEMON;

sub get_settings
{
    $sql="SELECT `IDS`,`VALUE` FROM `t_autoinformator_settings`";
    $sth = $remote_dbh->prepare($sql);
    $sth->execute;
    while (@array = $sth->fetchrow_array()) {
        $l_count = int($array[1]) if $array[0] eq $l_field;
        $max_tries = int($array[1]) if $array[0] eq $c_field ;
    }
    $l_count=1 if $l_count==0;
    $max_tries=1 if $max_tries==0;
    $sth->finish;
}

sub work
{
    $sql="SELECT `id`, `phone`, `result`, UNIX_TIMESTAMP(last_time), `count`, `voice`, `interval` FROM t_autoinformator_temp";
    $sth = $local_dbh->prepare($sql);
    $sth->execute;
    $numRows = $sth->rows;
    if ($numRows<=0) {
        $sth->finish;
        print "Temp table is empty. Trying to add some tasks...\n";
        print &temp_fill;
        return 0;
    } else {
        # check calls state
        $i=0;
        while (@array = $sth->fetchrow_array()) {
            # We should got different status for tasks and update some tables
            if (int($array[2])==2) {
                # call successfully complited
                $res2_ids="$array[0],$res2_ids";
            }elsif(int($array[2])==3 && int($array[4])==0){
                # call aborted
                $res3_ids="$array[0],$res3_ids";
            }elsif(int($array[2])!=2 && int($array[2])!=3 && int($array[2])!=4) {
                # call isn't complited, so we should check for turn
                if(int($array[4])>0 && (int($array[3])+int($array[6])) <= time) {
                    # it's our turn
                    # store id in array, we will update our table later
                    $update_ids="$array[0],$update_ids";
                    # store all ids, phones and voices, we will create call files later
                    # let's use queue_voice array for this, for memmory economy ;)
                    $i++;
                    $queue_voice[$i]="$array[0];$array[1];$array[5]";
                }
            }
        }
        # Update t_autoinformator_debtor_phone and remove complited calls from temp
        # This 2 queries whould be joined into one
        $res2_ids=substr($res2_ids,0,-1);
        $res3_ids=substr($res3_ids,0,-1);
        @array=split(',',$res2_ids);
        @task_arr=split(',',$res3_ids);
        if ( @array > 0)    {
            $sql="UPDATE `t_autoinformator_debtor_phone` SET `STATUS`=2 WHERE `ID` IN ($res2_ids)";
            $remote_dbh->do("$sql");
        }
        if ( @array > 0)    {
            $sql="UPDATE `t_autoinformator_debtor_phone` SET `STATUS`=3 WHERE `ID` IN ($res3_ids)";
            $remote_dbh->do("$sql");
        }
        if ( @array>0 || @task_arr>0)   {
            if(@array>0 && @task_arr>0) {
                $tmp=substr("$res2_ids,$res3_ids",0,-1);
            }elsif(@array>0 && @task_arr==0){
                $tmp=$res2_ids;
            }elsif(@array==0 && @task_arr>0){
                $tmp=$res3_ids;
            }
            $sql="DELETE FROM `t_autoinformator_temp` WHERE `ID` IN ($tmp)";
            $local_dbh->do("$sql");
        }
        # Check for awaited tasks
        if ($i>0) {
            # Some tasks awaiting. Make call files for them.
            foreach $tmp (@queue_voice) {
                @array=split(';',$tmp);
                $filee="/var/spool/asterisk/outgoing/z_".$array[0].".call";
                #$tmp="/var/tmp/z_".$arr{'telef'}.".call";
                if(!(-e $filee)){
                    open (FILE, ">/var/tmp/z_".$array[0].".call");
                    print FILE "Channel: Local/",$array[1],"\@outgoing\n";
                    #print FILE "MaxRetries: 0\n";
                    print FILE "WaitTime: 30\n";
                    print FILE "Context: dialout\n";
                    print FILE "Extension: s\n";
                    print FILE "Priority: 1\n";
                    print FILE "Set: PassedInfo=",$array[0],"-",$array[1],"-",$array[2],"\n";
                    close (FILE);
                }
            }
            # Move files to asterisk spool directory
            system("mv /var/tmp/z_*.call /var/spool/asterisk/outgoing/");
            # Update task status in database
            $update_ids=substr($update_ids,0,-1);
            @array=split(',',$update_ids);
            $sql="UPDATE `t_autoinformator_temp` SET `result`=1, `count`=(`count`-1) WHERE `ID` IN ($update_ids)";
            $local_dbh->do("$sql");
        }
        # Fill temporary table
        &temp_fill;
    }
} # end sub work

sub temp_fill
{
    # Current day of the week for DB format
    ($wday) = (localtime(time))[6]-1;
    $wday=6 if $wday<0;

    $sql="
        SELECT `ID`, `INTERVAL` , `VOICE`
        FROM `t_autoinformator`
        WHERE `ACTIVE`=1
        AND
            (`DAYS` LIKE '$wday'
            OR `DAYS` LIKE '\%$wday\%'
            OR `DAYS` LIKE '\%$wday'
            OR `DAYS` LIKE '$wday\%')
        AND
            (`DATE_START` < CURDATE()
            OR
                (`DATE_START` = CURDATE()
                AND `TIME_START` <= CURTIME() ) )
        AND `DATE_END` >= CURDATE()
        AND
            ( (`INTERVAL1_BEGIN` <= CURTIME() AND `INTERVAL1_END` >= CURTIME())
            OR (`INTERVAL2_BEGIN` <= CURTIME() AND `INTERVAL2_END` >= CURTIME())
            OR (`INTERVAL3_BEGIN` <= CURTIME() AND `INTERVAL3_END` >= CURTIME())
            OR (`INTERVAL4_BEGIN` <= CURTIME() AND `INTERVAL4_END` >= CURTIME()) )
    ";
    #print $sql;
    $sth = $remote_dbh->prepare($sql);
    #find active queues
    $sth->execute;
    $numRows = $sth->rows;
    if ($numRows<=0) {
        $sth->finish;
        return "No active queues found.\n";
    } else {
        $queue_ids='';
        while (@array = $sth->fetchrow_array()) {

            $queue_interval[$array[0]]=int(3600/$array[1]); # interval set by 'calls per hour'
            $queue_voice[$array[0]]=$array[2]; # voice file
            $queue_ids="$array[0],$queue_ids";

        }
        $queue_ids=substr($queue_ids,0,-1);
        $sth->finish;

        $sql="SELECT $l_count-count(id) FROM `t_autoinformator_temp` WHERE `result`=1";
        $sth = $local_dbh->prepare($sql);
        $sth->execute;
        $l_count = int($sth->fetchrow_array());
        $sth->finish;
        if ($l_count<=0) {
            return "Active queues found but all lines usage exceed.\n";
        }else{
            $sql="
                SELECT `t_autoinformator_debtor_phone`.`ID`, `t_phone`.`NUMBER`, `t_autoinformator_debtor`.`AUTOINFORMATOR`
                FROM `t_autoinformator_debtor_phone`, `t_autoinformator_debtor`, `t_phone`
                WHERE `t_autoinformator_debtor_phone`.`AUTOINFORMATOR_DEBTOR`=`t_autoinformator_debtor`.`ID`
                AND `t_autoinformator_debtor`.`AUTOINFORMATOR` IN ($queue_ids)
                AND `t_autoinformator_debtor_phone`.`PHONE`=`t_phone`.`ID`
                AND `t_autoinformator_debtor_phone`.`STATUS`=0 LIMIT $l_count;
            ";
            print $sql;
            $sth = $remote_dbh->prepare($sql);
            $sth->execute;
            $numRows = $sth->rows;
            if ($numRows<=0) {
                $sth->finish;
                return "No tasks with status=0 for dial found.\n";
            } else {
                $tmp="INSERT INTO `t_autoinformator_temp` (`id`, `phone`, `result`, `count`, `voice`, `interval`) VALUES";
                $queue_ids='';
                $i=0;
                while (@array = $sth->fetchrow_array()) {
                    $task_arr[0]=$array[0]; # task id
                    $task_arr[1]=$array[1]; # phone number
                    $task_arr[2]=$queue_interval[$array[2]]; # interval
                    $task_arr[3]=$queue_voice[$array[2]]; # voice file
                    $tmp="$tmp ('$task_arr[0]','$task_arr[1]',0,'$max_tries','$task_arr[3]','$task_arr[2]'),";
                    $queue_ids="$task_arr[0],$queue_ids";
                    $i++;
                }
                $sth->finish;
                $tmp=substr($tmp,0,-1);
                $queue_ids=substr($queue_ids,0,-1);
                $local_dbh->do("$tmp");
                $sql="UPDATE `t_autoinformator_debtor_phone` SET `STATUS`=1 WHERE `ID` IN ($queue_ids)";
                $remote_dbh->do("$sql");
                return "Added $i task(s) to temp table\n";
            }
        }
    }
} #end sub temp_fill

$local_dbh->disconnect();
$remote_dbh->disconnect();
exit;