#!/usr/bin/perl
##########################################################################
#                                                                        #
# Perl Script for Statistic SIP Registration in DBA 'location2'          #
# Switzernet(c)2012                                                      #
#                                                                        #
##########################################################################
# my modules
use strict;
use warnings;
use DBI;
use Socket;  
use POSIX;
use DateTime;
##########################################################################
# Globals
my ($dbh2,$DB_NAME2,$DB_HOST2,$DB_USER2,$DB_PASS2);
my ($dbh3,$DB_NAME3,$DB_HOST3,$DB_USER3,$DB_PASS3);
my %select_serv;
##########################################################################
#                                 main()                                 #
##########################################################################
$DB_NAME3    = 'astrad';
$DB_HOST3    = 'db3.switzernet.com';
$DB_USER3    = 'XXXXXXX';
$DB_PASS3    = 'XXXXXXX';

$DB_NAME2    = '110204-reg-stat';
$DB_HOST2    = 'localhost';
$DB_USER2    = 'XXXXXXX';
$DB_PASS2    = 'XXXXXXX';

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");

$dbh3 = DBI->connect("DBI:mysql:$DB_NAME3;host=$DB_HOST3", $DB_USER3, $DB_PASS3, {PrintError=>0});
if (defined $dbh3) {
        my $request1="select count(username) as number,domain from (select * from (select domain, username from location2 order by domain) a group by username) b group by domain;";
        my $sth1 = $dbh3->prepare($request1);
        $sth1->execute();
        while (my $result = $sth1->fetchrow_hashref) {
                $select_serv{$result->{domain}} = $result->{number};
        }
        $sth1->finish;
        $dbh3->disconnect;
}

my $request="insert into customer_by_host2 (host,ip,cus,stat_time) VALUES ";
foreach my $i (keys(%select_serv)){
print ".";
        $request = $request . "('".gethostbyaddr(inet_aton($i),AF_INET)."','$i',$select_serv{$i},'$DATE_L') ,";
}
print $request;
$request = substr($request,0,-1) . ";";
print $request;

$dbh2 = DBI->connect("DBI:mysql:$DB_NAME2;host=$DB_HOST2", $DB_USER2, $DB_PASS2, {PrintError=>0});
my $sth2 = $dbh2->prepare($request);
$sth2->execute();
$sth2->finish;
$dbh2->disconnect;
##########################################################################
#                                  END                                   #
##########################################################################
