wi12.unix7.org

<?php
 
$con = mysql_connect("192.168.xxx.xxx","asterisk","xxxxxxx") or die("Could not connect: " . mysql_error());
printf("Connected successfully\n");
 
mysql_select_db('asterisk') or die('Could not select database');
printf("Select db successfully\n");
 
$query = "select calldate, src, linkedid, userfield from cdr where dst='605090'";
 
$result = mysql_query($query) or die('Query failed:'. mysql_error());
 
$count_rows = mysql_num_rows($result);
if ($count_rows > 0) {
  while ($row = mysql_fetch_assoc($result)) {
    $userfield = $row['userfield'];
    $linkedid = $row['linkedid'];
    $src = $row['src'];
    $calldate = $row['calldate'];
# если запись помечена необработанной
    if ($userfield != "resolv") {
 
# выборка терминированых на sip звонков с данным linkedid

      $s2query = "select calldate, src, dst, lastapp, billsec ,disposition, linkedid  from cdr where ";
      $s2query = $s2query . "   linkedid = '" . $linkedid . "' and " ;
      $s2query = $s2query . "   lastapp = 'Dial' and ";
      $s2query = $s2query . "   dstchannel regexp 'SIP/.*' order by calldate";
# запрос 
      $s2result = mysql_query($s2query) or die('Query failed:'. mysql_error());
      $s2count_rows = mysql_num_rows($s2result);
# изначально считаем, что звонок не терминирован и и не отвечен
      $call_sip_terminated = 0;
      $call_sip_answered = 0;
      $call_answered_by = '';
# если есть записи с терминированными на sip каналами
      if ($s2count_rows > 0) {
# то считать что данный linkedid терминирован на sip
      $call_sip_terminated = 1;
# то для каждой записи с данным linkedid
        while ($s2row = mysql_fetch_assoc($s2result)) {
          $s2disposition = $s2row['disposition'];
          $s2src = $s2row['src'];
          $s2dst = $s2row['dst'];
          $s2calldate = $s2row['calldate'];
          $s2linkedid = $s2row['linkedid'];
          printf("src=%s dst=%s linkedid=%s disposition=%s\n", $s2src, $s2dst, $s2linkedid, $s2disposition);
# если есть хоть один sip канал, где ответили
          if ($s2disposition == "ANSWERED") {
# то считать звонок отвеченным оператором
            $call_sip_answered = 1;
            $call_answered_by = $s2dst;
          }
        }
      }
# итого, собираем данные
# если звонок был таки отвечен
      if ($call_sip_answered > 0) {
        printf("звонок %s\tтерминирован на sip телефоны и отвечен абонентом %s, %s\n\n", 
                    $src, $call_answered_by, $s2calldate);
      } else {
# если не отвечен, но терминирован на sip
        if ($call_sip_terminated > 0 && $call_sip_answered == 0) {
          printf("звонок %s терминирован на sip телефоны, но пропущен\n\n", $src);
        } else {
# если не терминирован на sip телефон
          printf("звонок %s не терминирован на sip телефоны\n\n", $src);
        }
      }
      mysql_free_result($s2result);
    }
 
  }
}
mysql_free_result($result);
 
mysql_close($con);
?>

#!/usr/bin/env perl
 
#-----------
#--- DBI ---
#-----------
 
package aDBI;
 
use strict;
use warnings;
use DBI;
use DBD::Pg;
 
sub new {
    my ($class, %args) = @_;
    my $self = {
        host => $args{host} || '127.0.0.1',
        login => $args{login} || 'postgres',
        password => $args{password} || 'password',
        database => $args{database} || 'postgres',
        engine => $args{engine} || 'Pg',
        error => ''
    };
    bless $self, $class;
    return $self;
}
 
sub login {
    my ($self, $login) = @_;
    return $self->{login} unless $login;
    $self->{login} = $login;
    $self;
}
 
sub password {
    my ($self, $password) = @_;
    return $self->{password} unless $password;
    $self->{password} = $password;
    $self;
}
 
sub host {
    my ($self, $host) = @_;
    return $self->{host} unless $host;
    $self->{host} = $host;
    $self;
}
 
sub database {
    my ($self, $database) = @_;
    return $self->{database} unless $database;
    $self->{database} = $database;
    $self;
}
 
sub error {
    my ($self, $error) = @_;
    return $self->{error} unless $error;
    $self->{error} = $error;
    $self;
}
 
sub engine {
    my ($self, $engine) = @_;
    return $self->{engine} unless $engine;
    $self->{engine} = $engine;
    $self;
}
 
sub exec {
    my ($self, $query) = @_;
    return undef unless $query;
 
    my $dsn = 'dbi:'.$self->engine.
                ':dbname='.$self->database.
                ';host='.$self->host;
    my $dbi;
#    eval {
        $dbi = DBI->connect($dsn, $self->login, $self->password, {
            RaiseError => 1,
            PrintError => 0,
            AutoCommit => 1
        });
#    };
    $self->error($@);
    return undef if $@;
 
    my $sth;
#    eval {
        $sth = $dbi->prepare($query);
#    };
    $self->error($@);
    return undef if $@;
 
    my $rows = $sth->execute;
    my @list;
 
    while (my $row = $sth->fetchrow_hashref) {
        push @list, $row;
    }
    $sth->finish;
    $dbi->disconnect;
    \@list;
}
 
sub exec1 {
    my ($self, $query) = @_;
    return undef unless $query;
 
    my $dsn = 'dbi:'.$self->engine.
                ':dbname='.$self->database.
                ';host='.$self->host;
    my $dbi;
#    eval {
        $dbi = DBI->connect($dsn, $self->login, $self->password, {
            RaiseError => 1,
            PrintError => 0,
            AutoCommit => 1
        });
#    };
    $self->error($@);
    return undef if $@;
 
    my $sth;
#    eval {
        $sth = $dbi->prepare($query);
#    };
    $self->error($@);
    return undef if $@;
 
    my $rows = $sth->execute;
    my $row = $sth->fetchrow_hashref;
 
    $sth->finish;
    $dbi->disconnect;
    $row;
}
 
sub do {
    my ($self, $query) = @_;
    return undef unless $query;
    my $dsn = 'dbi:'.$self->engine.
                ':dbname='.$self->database.
                ';host='.$self->host;
    my $dbi;
#    eval {
        $dbi = DBI->connect($dsn, $self->login, $self->password, {
            RaiseError => 1,
            PrintError => 0,
            AutoCommit => 1
        });
#    };
    $self->error($@);
    return undef if $@;
    my $rows;
#    eval {
        $rows = $dbi->do($query);
#    };
    $self->error($@);
    return undef if $@;
 
    $dbi->disconnect;
    $rows*1;
}
 
1;
 
package main;
 
 
use strict;
#use Mojo::Util qw(dumper);
use POSIX qw(strftime);
 
my $year = strftime "%Y", localtime;
my $mon = strftime "%m", localtime;
 
$mon--;
 
my $date1 = sprintf("%4d-%02d-01 00:00:01", $year, $mon);
 
$mon++;
my $date2;
if ($mon + 1 > 12) {
    $date2 = sprintf("%4d-%02d-01 00:00:01", $year + 1, 1);
} else {
    $date2 = sprintf("%4d-%02d-01 00:00:01", $year, $mon + 1);
}
 
#print $date1, "\n";
#print $date2, "\n";
 
#$date1 = "2018-12-01 00:00:01";
#$date2 = "2019-01-01 00:00:01";
 
my $db = aDBI->new(
            host => "xxxxxxxxxxxxxxx",
            login => "xxxxxxxxxx",
            password => "xxxxxxxxxx",
            database => "xxxxxxxxxxxx");
 
my $query = "select distinct calldate, src, dst, linkedid from cdr
                where calldate > '$date1'
                and calldate < '$date2'
                order by calldate;";
 
foreach my $rec (@{$db->exec($query)}) {
    #print dumper $rec;
    my $userfield = $rec->{userfield};
    my $linkedid = $rec->{linkedid};
    my $src = $rec->{src};
    my $dst = $rec->{dst};
    my $calldate = $rec->{calldate};
    my $billsec = $rec->{billsec};
    my $dcontext = $rec->{dcontext};
 
    next if $dst ne "s"; 
 
    my $query = "select calldate, src, dst, lastapp, billsec, disposition, linkedid, dcontext
                        from cdr
                        where linkedid = '$linkedid'
                            and lastapp = 'Dial'
                            and dstchannel ~ 'SIP/'
                        order by calldate 
                        limit 1;";
 
    my $res = $db->exec($query);
    next unless $res;
 
    my $answered = 0;
 
    foreach my $res2 (@{$res}) {
        if ($res2->{disposition} =~ /ANSWERED/) {
            $answered = 1;
            $billsec = $res2->{billsec};
            $calldate = $res2->{calldate};
        }
    }
 
    if ($answered == 1) { 
        print "$calldate,$src,$dst,$billsec\r\n";
    }
 
};
#EOF