#!/usr/bin/perl
#############################################################################
#
# CGI скрипт для поиска данных абонента в биллинге на основе даты и ip адреса
#
# вызывается из скрипта search с параметрами сессии:
# IP-Адрес, День, Месяц, Год, Время 
#
# например:
# cgi-bin/usersearch?ip=4.8.5.1&day=01&month=03&year=2015&t=00:09:46
#
# Автор: Сергей Калинин email: banzaj28@yandex.ru
# распространяется под лицензией GPL
# (c) 2015
#########################################################################


use CGI ':standard';
use DBI;

# Вывод заголовка
print header(-type=>'text/html', -charset=>'utf-8');
print start_html("Данные по абоненту");
#print "<table width=100%><tr><td width=10%><img src=/logo.gif></td><td width=90% align=center><h2>Поиск сессий абонента</h2></td></tr></table>\n";
print "<h2>Данные по абоненту</h2>";
print "<hr>\n";

# выполняем процедуры описанные ниже
print_prompt();
do_search();
print_tail();
print end_html;


###################################################################
# Описание процедур
###################################################################

# Выводим параметры поиска на экран
sub print_prompt {
    my $date = param(day);
    my $month = param(month);
    my $year = param(year);
    my $date = join("\.", $date, $month, $year);
    print "<p>Параметры поиска</p>";
    print "IP адрес - ".param(ip);
    print "<br>Дата - ".$date;
    print "<br>Время - ".param(t)."<hr><br>";
}


# поиск данных в БД биллинга
sub do_search {

    my $ip = param(ip);
    my $day = param(day);
    my $month = param(month);
    my $year = param(year);
    my $time = param(t);
    # определяем имя таблицы с сессиями
    my $table = "inet_session_log_1_".$year."".$month;
    # форматируем дату для запроса
    my $date = $year."-".$month."-".$day." ".$time;
    # конектимся к GLPI базе.
    my $dsn = 'DBI:mysql:bgbilling:192.168.1.250';
    my $db_user_name = 'netflow';
    my $db_password = 'Cbcntvfnbpfwbz112318';
    my ($id, $password);
    my $dbh = DBI->connect($dsn, $db_user_name, $db_password);


#    my $table = "inet_session_log_1_".$year."".$month;

    # формируем SQL запрс

    my $sth = $dbh->prepare(qq{select c.title, serv.login,  INET_NTOA(CONV(HEX( session.ipAddress ), 16, 10)) as ip, session.sessionStart, session.sessionStop  from inet_serv_1 serv
    left join $table session on  serv.id = session.servid
    left join contract c on c.id = serv.contractid
    where serv.contractid <> '2' and INET_NTOA(CONV(HEX( session.ipAddress ), 16, 10)) = '$ip' and session.sessionStart <= '$date' AND session.sessionStop >= '$date'

    });
#    my $sth = $dbh->prepare(qq{select c.title, serv.login,  INET_NTOA(CONV(HEX( session.ipAddress ), 16, 10)) as ip, session.sessionStart, session.sessionStop  from inet_serv_1 serv
#    left join $table session on  serv.login = session.username
#    left join contract c on c.id = serv.contractid
#    where serv.contractid <> '2' and   INET_NTOA(CONV(HEX( session.ipAddress ), 16, 10)) = '$ip' and session.sessionStart <= '$date' AND session.sessionStop >= '$date'
#    });
    # Выполняем запрос к БД
    $sth->execute();
    # если в предыдущем запросе ничего не найдено ищем в таблице активных сессий    
    if ($sth->rows == 0)
    {
	$sth = $dbh->prepare(qq{select c.title, i_c_1.username, INET_NTOA(CONV(HEX( i_c_1.ipAddress ), 16, 10)) as ip , session.sessionstart, session.sessionstop
        from inet_connection_1 i_c_1
        join inet_session_1 session on session.connectionid = i_c_1.id
        join inet_serv_1 i_s_1 on i_s_1.id = i_c_1.servid
        join contract c on c.id = i_s_1.contractid
        where i_c_1.parentid = 0 and INET_NTOA(CONV(HEX( i_c_1.ipAddress ), 16, 10)) = '$ip' and session.sessionStart <= '$date'});

        $sth->execute();
    }

    print "<p>Найдено записей - ".$sth->rows."</p>";
    # получаем данные запроса форматируем и выводим на экран
    while (($title, $login, $ip, $start, $stop) =  $sth->fetchrow_array())
    {
	print "Договор - <b><i>$title</i></b><br>Имя пользователя - <b><i><a href=/cgi-bin/loginsearch?login=".$login."\&date=".$day."\.".$month."\.".$year."&contr=".$title.">$login</a></i></b><br>IP - <b><i>$ip</i></b><br>Cессиия - <b><i>$start - $stop</i></b><br>";
    }
    $sth->finish();
    # Отцепляемся от БД
    $dbh->disconnect();

}

sub print_tail {
   print "<hr><div align=center> &copy 2015, OOO \"Терион\"</div>";
}