package CybOrg::DB;

#
#    CybOrg - The Cybercafe Organizer
#    http://cyborg.sourceforge.net
#
#    Copyright (C) 2003 - The CybOrg Project
#
#    This program is free software; you can redistribute it and/or modify
#    it under the terms of the GNU General Public License version 2 as
#    published by the Free Software Foundation.
#
#    This program is distributed in the hope that it will be useful,
#    but WITHOUT ANY WARRANTY; without even the implied warranty of
#    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#    GNU General Public License for more details.
#
#    You should have received a copy of the GNU General Public License along
#    with this program; if not, write to the Free Software Foundation, Inc.,
#    59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
#

use strict;
use warnings 'all';

use DBI;
use CybOrg::Config qw(%config %options %database &def_options);

our $dbh;
our %options;

sub _exception($$)
{
	use CybOrg::Exception;

	my ($error_code, $error_params) = @_;

	my $exception = CybOrg::Exception->new();
	$exception->error_code($error_code);
        $exception->error_params($error_params);

	if ($config{'debug'}) {
		print STDERR "DB Error:$error_code\nParams:$error_params\n\n";
	}

	die $exception;
}

sub _connect
{

	my $dbh;

	eval {
		$dbh = DBI->connect("dbi:$database{'driver'}:dbname=$database{'dbname'};".
					"host=$database{'host'};port=$database{'port'}",
					$database{'user'}, $database{'password'},
					{AutoCommit => 0, RaiseError => 1});
	};

	if ($@) {
		_exception('db_connect', {'host' => "$database{'host'}",
					  'port' => "$database{'port'}",
					  'user' => "$database{'user'}"});
	} else {
		return $dbh;
	}

}

sub _commit
{
	eval {
		$dbh -> commit;
	};
	if ($@) {
		_exception('db_commit', {'host' => "$database{'host'}",
					 'port' => "$database{'port'}",
					 'user' => "$database{'user'}"});
	}
}

sub _rollback
{
	eval {
		$dbh -> rollback;
	};
	if ($@) {
		_exception('db_rollback', {'host' => "$database{'host'}",
					   'port' => "$database{'port'}",
					   'user' => "$database{'user'}"});
	}
}

sub _select_row($)
{
	my $query = shift;
	my @result;

	eval {
		my $sth = $dbh -> prepare($query);
		$sth->execute;
		@result = $sth->fetchrow_array
	};

	if ($@){
		_rollback;
		_exception('db_select',{'query' => "$query"});
	} else {
		return \@result;
	}
}

sub _select_hash($)
{
	my $query = shift;
	my %aux;

	eval {
		my $sth = $dbh -> prepare($query);
		$sth->execute;
		my @row;
		while (@row = $sth->fetchrow_array){
			my $id = shift @row;
			$aux{$id} = [@row];
		}
	};
	if ($@) {
		_rollback;
		_exception('db_select',{'query' => "$query"});
	} else {
		return \%aux;
	}
}

sub _modify($)
{
	my $query = shift;

	my $result;
	eval {
		$result = $dbh ->do($query);
	};

	# Modifying queries MUST allways afect a non-zero number of rows
	if (($@) or ($result eq '0E0')) {
		_rollback;
		_exception('db_modify',{'query' => "$query"});
	}
}

sub _update_station($$)
{
	my ($station, $status) = @_;
	_modify("update stations set status='$status' where id='$station'");
}


BEGIN {
        use Exporter   ();
        our ($VERSION, @ISA, @EXPORT);
        $VERSION     = 0.1;
        @ISA         = qw(Exporter);

        @EXPORT      = qw(&db_get_groups &db_get_some_group &db_get_stations &db_get_station
			  &db_update_station &db_rent_station &db_get_tariffs &db_get_clients
			  &db_get_address &db_validate_user &db_update_session
			  &db_validate_session &db_get_group &db_delete_rent &delete_paused_rent
			  &db_get_stations_groups &db_delete_stations &db_create_station
			  &db_update_station_data &db_get_client &db_delete_clients
			  &db_update_client &db_create_client &db_get_admins &db_get_admin
			  &db_delete_admins &db_update_admin &db_create_admin &db_admin_exists
			  &db_get_groups_and_count &db_delete_groups &db_create_group &db_update_group
			  &db_get_tariff &db_create_tariff &db_update_tariff &db_delete_tariffs
			  &db_tariff_exists &db_update_rent_station &db_update_rent_tariff
			  &db_get_status &db_pause_rent &db_activate_rent &db_get_idle_rents
			  &db_free_rent_station &db_update_discount &db_get_rent
			  &db_finalize_rent &db_get_station_from_ip &db_get_options
			  &db_update_options);
	$dbh = _connect;

}

sub db_get_options($)
{
	my $username = shift;
	my $aux = _select_row("select options.language, options.locale, options.list_view, options.charset from admins, options where options.id_admin = admins.id and admins.username = '$username'");
	$options{'language'} = $$aux[0];
	$options{'locale'}   = $$aux[1];
	$options{'list_view'} = $$aux[2];
	$options{'charset'} = $$aux[3];
}

sub db_update_options($$$$$)
{
	my ($username, $language, $locale, $list_view, $charset) = @_;
	my $aux = _select_row("select id from admins where username='$username'");
	_modify("update options set language = '$language', locale = '$locale', list_view = '$list_view', charset='$charset' where id_admin = '$$aux[0]'");
	_commit;
}

sub db_get_status($)
{
	my $station = shift;
	my $aux = _select_row("select status from stations where id='$station'");
	return $$aux[0];
}

sub db_get_groups
{
	return  _select_hash("select id, description from groups");
}

sub db_get_some_group
{
	my $aux =  _select_row("select id from groups");
	if (defined $aux) {
		return $$aux[0]
	}
}

sub db_get_groups_and_count
{
	return _select_hash("select groups.id, groups.description, count(stations.id) from groups left join stations on groups.id = stations.id_group group by groups.id, groups.description");
}

sub db_validate_user($$)
{
	my ($username, $password) = @_;
	if ($username and $password){
		my $aux = _select_row("select password from admins where username='$username'");
		return (defined $$aux[0])? ($$aux[0] eq $password): 0
	} else {
		return 0
	}
}

sub db_validate_session($$)
{
	my ($username, $session) = @_;
	if ($username and $session){
		my $aux = _select_row("select session from admins where username='$username'");
		return (defined $$aux[0])? ($$aux[0] eq $session): 0
	} else {
		return 0
	}
}


sub db_update_session($$)
{
	my ($username, $session) = @_;
	_modify("update admins set session='$session' where username='$username'");
	_commit;
}

sub db_get_stations($)
{
	my $group = shift;
	return _select_hash("select stations.id, stations.description, stations.status, rents.id, rents.id_tariff, rents.start_time, rents.discount_time, rents.id_client from (stations left outer join rents_stations on stations.id = rents_stations.id_station ) left outer join rents on rents_stations.id_rent = rents.id where stations.id_group='$group'");
}

sub db_get_station_from_ip($)
{
	my $ip_address = shift;
	my @ip = split('\.', $ip_address);

	my $station =  _select_row("select stations.description, stations.status, rents.id_tariff, rents.start_time, rents.discount_time, rents.id_client, stations.id_group from (stations left outer join rents_stations on stations.id = rents_stations.id_station ) left outer join rents on rents_stations.id_rent = rents.id where stations.ip1 = '$ip[0]' and stations.ip2 = '$ip[1]' and stations.ip3 = '$ip[2]' and stations.ip4 = '$ip[3]'");
	my $group   =  _select_row("select description from groups where id = '$$station[6]'");

	my @result;
	push (@result, $$station[0], $$group[0], $$station[1]);

	if ($$station[1] eq '1') {
		my $client = _select_row("select last_name, name, balance from clients where id = '$$station[5]'");
		push (@result, $$client[0], $$client[1], $$client[2]);
		my $tariff = _select_row("select description, quarter, half, hour from tariffs where id = '$$station[2]'");
		push (@result, $$tariff[0], $$tariff[1], $$tariff[2], $$tariff[3], $$station[3], $$station[4]);
	}

	return \@result;
}

sub db_get_station($)
{
	my $station = shift;
	return _select_row("select status, description, id_group, ip1, ip2, ip3, ip4 from stations where id='$station'");
}

sub db_delete_stations($)
{
	my $stations = shift;
	foreach(@$stations) {
		_modify("delete from stations where id='$_'");
		_commit;
	}
}

sub db_get_stations_groups
{
	return _select_hash("select stations.id, stations.description, stations.ip1, stations.ip2, stations.ip3, stations.ip4, groups.description from stations inner join groups on stations.id_group = groups.id order by stations.description");
}

sub db_get_idle_rents
{
	return _select_hash("select id, id_tariff, start_time, idle_time, discount_time, id_client from rents where idle='1'");
}

sub db_update_station_data($$$$$$$)
{
	my ($desc, $ip1, $ip2, $ip3, $ip4, $group, $key) = @_;
	_modify("update stations set description='$desc', ip1='$ip1', ip2='$ip2', ip3='$ip3', ip4='$ip4', id_group='$group'  where id='$key'");
	_commit;
}

sub db_create_station($$$$$$)
{
	my ($desc, $ip1, $ip2, $ip3, $ip4, $group) = @_;
	_modify("insert into stations (description, id_group, ip1, ip2, ip3, ip4, status) values ('$desc','$group','$ip1','$ip2','$ip3','$ip4','2')");
	_commit;
}

sub db_get_group($)
{
	my $group = shift;
	my $aux = _select_row("select description from groups where id='$group'");
	return $$aux[0];
}

sub db_update_station($$)
{
	my ($station, $status) = @_;
	_update_station($station, $status);
	_commit;
}

sub db_rent_station($$$)
{
	my ($station, $tariff, $client) = @_;
	my $time = time;
	db_update_station($station, '1');
	_modify("insert into rents (id_client, id_tariff, start_time, idle_time, idle, discount_time) values ('$client','$tariff','$time','0','0','0')");
	my $aux = _select_row("select id from rents where id_client='$client' and start_time='$time'");
	_modify("insert into rents_stations values ($$aux[0], $station)");
	_commit;
}

sub db_delete_rent($$)
{
	my ($station, $rent) = @_;
	_update_station($station, 2);
	_modify("delete from rents_stations where id_rent='$rent' and id_station='$station'");
	_modify("delete from rents where id='$rent'");
	_commit;
}

sub db_delete_paused_rent($)
{
	my $rent = shift;
	_modify("delete from rents where id='$rent'");
	_commit;
}

sub db_update_rent_station($$)
{
	my ($station, $station_new) = @_;
	_update_station($station, 2);
	_update_station($station_new, 1);
	_modify("update rents_stations set id_station='$station_new' where id_station='$station'");
	_commit;
}

sub db_free_rent_station($$)
{
	my ($rent, $station) = @_;
	_update_station($station, 2);
	_modify("delete from rents_stations where id_rent='$rent' and id_station='$station'");

	# Don't commit changes here!
}

sub db_pause_rent($)
{
	my $rent = shift;
	my $time = time;
	_modify("update rents set idle='1', idle_time='$time' where id='$rent'");
	_commit;
}


sub db_update_discount($)
{
	my $rent = shift;
	my $time = time;
	my $aux = _select_row("select idle_time, discount_time from rents  where id='$rent'");
	my $new_discount = $$aux[1] + ($time - $$aux[0]);
	_modify("update rents set idle='0', idle_time='0', discount_time='$new_discount' where id='$rent'");

	# Don't commit changes here!
}


sub db_activate_rent($$)
{
	my ($rent, $station) = @_;
	_update_station($station, 1);
	_modify("insert into rents_stations (id_rent, id_station) values ('$rent', '$station')");
	db_update_discount($rent);
	_commit;
}

sub db_get_rent($)
{
	my $rent = shift;
	return _select_row("select rents.id, clients.last_name, clients.name, clients.balance, tariffs.description, tariffs.quarter, tariffs.half, tariffs.hour, rents.start_time, rents.discount_time from rents, clients, tariffs where (rents.id='$rent') and (rents.id_client=clients.id) and (rents.id_tariff=tariffs.id)");
}

sub db_update_rent_tariff($$)
{
	my ($rent, $tariff_new) = @_;
	_modify("update rents set id_tariff='$tariff_new' where id='$rent'");
	_commit;
}

sub db_finalize_rent($$$$)
{
	use POSIX;
	my ($rent, $effective_cost, $to_account, $username) = @_;

	my $user_id = _select_row("select id from admins where username='$username'");

	if (defined $$user_id[0]) {
		my $aux = _select_row("select rents.id_client, rents.id_tariff, tariffs.quarter, tariffs.half, tariffs.hour, rents.start_time, rents.idle_time, rents.discount_time from rents, tariffs where (rents.id='$rent') and (rents.id_tariff = tariffs.id)");

		my $id_client = $$aux[0];
		my $id_tariff = $$aux[1];
		my $start_time = $$aux[5];
		my $end_time = $$aux[6];
		my $discount_time = $$aux[7];
		my $total_time = floor((($end_time - $start_time) - $discount_time) / 60);

		my @tariff = ($$aux[2], $$aux[3], $$aux[4]);

		my $suggested_price;

		if ($total_time <= 15) {
			$suggested_price = $tariff[0];
		} elsif ($total_time <= 30) {
			$suggested_price = $tariff[1];
		} elsif ($total_time <= 60) {
			$suggested_price = $tariff[2];
		} else {
			$suggested_price = (floor($total_time / 60) * $tariff[2]) + (floor( (($total_time % 60) + 14) / 15) * ($tariff[2] / 4));
		}

		if (!defined $to_account) {
			$to_account = ''
		} elsif ($to_account eq 'yes') {
			my $balance = _select_row("select balance from clients where id='$id_client'");
			$$balance[0] -= $effective_cost;
			_modify("update clients set balance='$$balance[0]' where id='$id_client'");

		}
		_modify("delete from rents where id='$rent'");
		_modify("insert into liquidations (id_client, id_admin, id_tariff, start_time, end_time, discount_time, suggested_price, price) values ('$id_client', '$$user_id[0]', '$id_tariff', '$start_time', '$end_time', '$discount_time', '$suggested_price', '$effective_cost')");
		_commit;
	}
}

sub db_get_tariffs
{
	return _select_hash("select id, description, quarter, half, hour from tariffs");
}

sub db_get_clients
{
	return _select_hash("select id, last_name, name, balance from clients");
}

sub db_get_address($)
{
	my $station = shift;
	my $aux = _select_row("select ip1, ip2, ip3, ip4 from stations where id='$station'");
	return "$$aux[0].$$aux[1].$$aux[2].$$aux[3]";
}

sub db_get_client($)
{
	my $client = shift;
	return _select_row("select last_name, name, balance from clients where id='$client'");
}

sub db_delete_clients($)
{
	my $clients = shift;
	foreach (@$clients) {
		_modify("delete from clients where id='$_'");
		_commit;
	}
}

sub db_update_client($$$$)
{
	my ($last_name, $name, $balance, $key) = @_;
	_modify("update clients set last_name='$last_name', name='$name', balance='$balance' where id='$key'");
	_commit;
}

sub db_create_client($$$)
{
	my ($last_name, $name, $balance) = @_;
	_modify("insert into clients (last_name, name, balance) values ('$last_name','$name','$balance')");
	_commit;
}

sub db_get_admins
{
	return _select_hash("select id, username from admins");
}

sub db_get_admin($)
{
	my $admin = shift;
	return _select_row("select username from admins where id='$admin'");
}

sub db_delete_admins($)
{
	my $admins = shift;
	foreach(@$admins) {
		_modify("delete from admins where id='$_'");
		_modify("delete from options where id_admin='$_'");
		_commit;
	}
}

sub db_update_admin($$$)
{
	my ($username, $password, $key) = @_;
	_modify("update admins set username='$username', password='$password' where id='$key'");
	_commit;
}

sub db_create_admin($$)
{
	my ($username, $password) = @_;
	_modify("insert into admins (username, password, session) values ('$username','$password', null)");
	my $aux = _select_row("select id from admins where username = '$username'");
	my %def_options = def_options;
	my $language = $def_options{'language'};
	my $locale = $def_options{'locale'};
	my $list_view = $def_options{'list_view'};
	my $charset = $def_options{'charset'};

	_modify("insert into options (id_admin, language, locale, list_view, charset) values ('$$aux[0]', '$language', '$locale', '$list_view', '$charset')");
	_commit;
}

sub db_admin_exists($)
{
	my ($username) = @_;
	my $aux = _select_row("select id from admins where username='$username'");
	return (defined $$aux[0])? (1, $$aux[0]): (0,'');
}

sub db_delete_groups($)
{
	my $groups = shift;
	foreach (@$groups) {
		_modify("delete from groups where id='$_'");
		_commit;
	}
}

sub db_update_group($$)
{
	my ($description, $key) = @_;
	_modify("update groups set description='$description' where id='$key'");
	_commit;
}

sub db_create_group($)
{
	my ($description) = @_;
	_modify("insert into groups (description) values ('$description')");
	_commit;
}

sub db_get_tariff($)
{
	my $tariff = shift;
	return _select_row("select description, quarter, half, hour from tariffs where id='$tariff'");
}

sub db_delete_tariffs($)
{
	my $tariff = shift;
	foreach(@$tariff) {
		_modify("delete from tariffs where id='$_'");
		_commit;
	}
}

sub db_update_tariff($$$$$)
{
	my ($desc, $quarter, $half, $hour, $key) = @_;
	_modify("update tariffs set description='$desc', quarter='$quarter', half='$half', hour='$hour' where id='$key'");
	_commit;
}

sub db_create_tariff($$$$)
{
	my ($desc, $quarter, $half, $hour) = @_;
	_modify("insert into tariffs (description, quarter, half, hour) values ('$desc','$quarter','$half','$hour')");
	_commit;
}

sub db_tariff_exists($)
{
	my ($desc) = @_;
	my $aux = _select_row("select id from tariffs where description='$desc'");
	return (defined $$aux[0])?(1, $$aux[0]):(0,'');
}

END {
	$dbh -> disconnect;
}

1;