User Tools

Site Tools


Differences

This shows you the differences between two versions of the page.

Link to this comparison view

ast2 [2020-05-19 10:38]
ziggi created
ast2 [2020-05-19 10:46] (current)
ziggi
Line 83: Line 83:
 </​code>​ </​code>​
  
 +<code perl pbx02-reporter> ​
 +#​!/​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
 +</​code>​