#!/usr/bin/perl
##########################################################################
#                                                                        #
# Perl Script for Statistique SIP Registeration in Portaone 'location'   #
# Switzernet(c)2011                                                      #
#                                                                        #
##########################################################################
# my modules
use strict;
use warnings;
use DBI;
use Socket;
use POSIX;
use DateTime;
##########################################################################
# Globals
my ($dbh1,$DB_NAME1,$DB_HOST1,$DB_USER1,$DB_PASS1);
my ($dbh2,$DB_NAME2,$DB_HOST2,$DB_USER2,$DB_PASS2);
my %select_node;
my %select_serv;
##########################################################################
#                                 main()                                 #
##########################################################################
$DB_NAME1    = 'porta-sip';
$DB_HOST1    = 'db2.switzernet.com';
$DB_USER1    = 'login';
$DB_PASS1    = 'password';
$dbh1 = DBI->connect("DBI:mysql:$DB_NAME1;host=$DB_HOST1", $DB_USER1, $DB_PASS1, {PrintError=>0});

$DB_NAME2    = '110204-reg-stat';
$DB_HOST2    = 'localhost';
$DB_USER2    = 'login';
$DB_PASS2    = 'password';
$dbh2 = DBI->connect("DBI:mysql:$DB_NAME2;host=$DB_HOST2", $DB_USER2, $DB_PASS2, {PrintError=>0});


my @time = (localtime);
my $date = DateTime->new(year => $time[5]+1900,
                         month => $time[4]+1,
                         day => $time[3],
                         hour => $time[2],
                         minute => $time[1],
                         second => $time[0],
                         time_zone => "Europe/Prague");

my $DATE_L=$date->strftime("%Y%m%d%H%M%S");
$date->subtract( hours => 23 );
$date->set_time_zone("America/Los_Angeles");
my $DATE=$date->strftime("%Y%m%d%H%M%S");

if (defined $dbh1) {
        my $request1="select ip from `porta-billing`.Nodes where name NOT LIKE 'rm_%' and i_node_type=11;";
        my $sth1 = $dbh1->prepare($request1);
        $sth1->execute();
        while (my $result = $sth1->fetchrow_hashref){
                my $host=gethostbyaddr(inet_aton($result->{ip}), AF_INET);
                if ($host){
                          $select_node{$result->{ip}} = $host;
                          $select_serv{$result->{ip}} = 0;
                          }
                }
        $sth1->finish;
}

if (defined $dbh1) {
        # my $request1="select count(distinct(username)) as number,domain from `porta-sip`.location group by domain;";
        # my $request1="select count(distinct(username)) as number,domain from `porta-sip`.location where expires > DATE_ADD(NOW(), INTERVAL -3 HOUR) group by domain;";

        my $request1="select count(l1.username) as number,
                      l1.domain as domain from location l1,
                      (select username,MAX(last_modified) as last_modified
                      from location
                      where last_modified > $DATE group by username )
                      as l2 where l1.username=l2.username
                      and
                      l1.last_modified=l2.last_modified
                       group by domain;";

        my $sth1 = $dbh1->prepare($request1);
        $sth1->execute();
        while (my $result = $sth1->fetchrow_hashref) {
                $select_serv{$result->{domain}} = $result->{number};
                }
$sth1->finish;
}

my $request="insert into customer_by_host (host,ip,cus,stat_time) VALUES ";
foreach my $i (keys(%select_serv)){
$request = $request . "('$select_node{$i}','$i',$select_serv{$i},'$DATE_L') ,";
}
$request = substr($request,0,-1) . ";";
my $sth2 = $dbh2->prepare($request);
$sth2->execute();
$sth2->finish;
##########################################################################
#                                  END                                   #
##########################################################################

