#!/usr/bin/perl

use strict;
use DBI;
use Getopt::Std;

my %options = ();
getopts("c:hst",\%options);

if ($options{t}) {
    print <<_STR_;
URL,Database name,Library name,first login,Remote login count,first remote login,last remote login,No.Items,Last import,No.loans,Last loan
_STR_
    exit 0;
}

my $configFile = $options{c};
if ($options{h} || !$configFile || ! -f $configFile) {
    print <<_STR_;
Usage: $0 -c CONFIG_FILE [-hst]
    -c  specify a configuration file
    -h  print help
    -s  print single line. Fields are separated by commas.
    -t  print header and exit
_STR_
    exit 1;
}

my $config = loadConfig($configFile);
my $dbh = makeConnection($config);
END {
    if ($dbh) {
        $dbh->disconnect();
    }
}

$| = 1;
# Codes start...

my $URL = getURL($configFile);
my $dbName = $config->{'db_name'};
my $libName = getLibName($dbh);
my ($first_login, $remote_login_count, $first_remote_login, $last_remote_login) = getLoginInfo($dbh);
my $numberOfAvailableItems = getNumberOfAvailableItems($dbh);
my $lastDateImport = getLastItemImportDate($dbh);
my ($loan_count, $last_loan) = getLoanInfo($dbh);

if ($options{t} || $options{s}) {
    if ($options{s}) {
        print <<_STR_;
<tr><td><a href="$URL">$dbName</a></td><td>$libName</td><td>$first_login</td><td>$remote_login_count</td><td>$first_remote_login</td><td>$last_remote_login</td><td>$numberOfAvailableItems</td><td>$lastDateImport</td><td>$loan_count</td><td>$last_loan</td></tr>
_STR_
    }
}
else {
    print <<_STR_;
URL:                        http://$URL
Database name:              $dbName
Library name:               $libName
The first login:            $first_login
Remote login count:         $remote_login_count
The first remote login:     $first_remote_login
The last remote login:      $last_remote_login
Number of available items:  $numberOfAvailableItems
The last import date:       $lastDateImport
Number of loans:            $loan_count
The last loan:              $last_loan
_STR_
}

# Codes end.

exit 0;
################################################################################


sub makeConnection {
    my ($config) = @_;
    if (!$config) {
        return;
    }
    my ($db_driver, $db_name, $db_host, $db_port, $db_user, $db_password);

    $db_driver   = $config->{'db_driver'} || 'mysql';
    $db_name     = $config->{'db_name'};
    $db_host     = $config->{'db_host'};
    $db_port     = $config->{'db_port'}   || '3306';
    $db_user     = $config->{'db_user'};
    $db_password = $config->{'db_password'};

    my $dsn = "dbi:$db_driver:$db_name:$db_host:$db_port";

    my $dbh = DBI->connect($dsn, $db_user, $db_password) || die "ERROR: $db_name\n";

    return $dbh;
}
############################################################


sub loadConfig {
    my ($configFile) = @_;
#    print "Enter the config filename of Opals: ";
#    $configFile = <STDIN>;
    my $config = {};

    open CONF, $configFile || die "Cannot open file $configFile";
    while (<CONF>) {
        chomp;
        s/#.*//;                # remove comments
        next if /^\s*$/;        # ignore blank lines

        if (/^\s*(\w+)\s*=\s*(.*?)\s*$/) {
            $config->{$1} = $2;
        }
    }
    close CONF;

    return $config;
}
############################################################


sub getURL {
    my ($configFile) = @_;
    my $HTTP_CONF = `grep $configFile\$ /etc/httpd/conf.d/opals/*|cut -d: -f1`;
    chomp $HTTP_CONF;
    my $URL =`basename $HTTP_CONF .conf`;
    chomp $URL;

    return $URL;
}
############################################################


sub getLibName {
    my ($dbh) = @_;

    my $sql = <<_SQL_;
select  val
from    opl_preference
where   var = 'libname'
_SQL_

    my ($libName) = $dbh->selectrow_array($sql);

    return $libName;
}
############################################################


sub getLoginInfo {
    my ($dbh) = @_;

    my $sql = <<_SQL_;
select  DATE_FORMAT(lasttime,'%Y-%m')
from    opl_session
order by lasttime asc limit 1
_SQL_

    my ($first_login) = $dbh->selectrow_array($sql);

    my $from_where = <<_SQL_;
from    opl_session
where   ipaddress not regexp '^199.202.10[01].'
_SQL_

    $sql = <<_SQL_;
select  count(*)
$from_where
_SQL_

    my ($remote_login_count) = $dbh->selectrow_array($sql);

    $sql = <<_SQL_;
select  DATE_FORMAT(lasttime,'%Y-%m')
$from_where
order by lasttime asc limit 1
_SQL_

    my ($first_remote_login) =  $dbh->selectrow_array($sql);

    $sql = <<_SQL_;
select  DATE_FORMAT(lasttime,'%Y-%m')
$from_where
order by lasttime desc limit 1
_SQL_

    my ($last_remote_login) =  $dbh->selectrow_array($sql);

    return ($first_login, $remote_login_count, $first_remote_login, $last_remote_login);
}
############################################################


sub getNumberOfAvailableItems {
    my ($dbh) = @_;

    my $sql = <<_SQL_;
select  count(*)
from    opl_item
where   available = 1
_SQL_

    my ($count) = $dbh->selectrow_array($sql);

    return $count;
}
############################################################


sub getLastItemImportDate {
    my ($dbh) = @_;

    my $sql = <<_SQL_;
select DATE_FORMAT(dateImport,'%Y-%m')
from    opl_item
order by dateImport desc limit 1
_SQL_

    my ($lastDateImport) = $dbh->selectrow_array($sql);

    return $lastDateImport;
}
############################################################


sub getLoanInfo {
    my ($dbh) = @_;

    my $sql = <<_SQL_;
select  count(*)
from    opl_loan
_SQL_

    my ($loan_count) = $dbh->selectrow_array($sql);

    $sql = <<_SQL_;
select  DATE_FORMAT(dateLoan, '%Y-%m')
from    opl_loan
order by dateLoan desc limit 1
_SQL_

    my ($last_loan) =  $dbh->selectrow_array($sql);

    return ($loan_count, $last_loan);
}