package Atmail::SQL;
##
# Copyright (c) 2009-2010 ATMAIL. All rights reserved
# See http://atmail.com/license.php for license agreement
##

# Added into PHP port
# Written by Ben Duncan for Calcode.com
# Use the Database Library to connect to our mySQL server

require 5.001;

use strict;
use vars qw($VERSION @ISA @EXPORT);
use DBI;

my $dbh;

$VERSION = '1.1';

sub new {
    my ($this) = @_;

    my $class = ref($this) || $this;
    my $self  = {};

    $self->{debug} = 0;

    bless( $self, $class );

	open(F, "/usr/local/atmail/webmail/config/dbconfig.ini") || print "Cannot open /usr/local/atmail/webmail/libs/Atmail/Config.php: $!\n";
	while(<F>)	{
	# Get rid of starting and ending whitespace
	s/^\s*(.*?)\s*$/$1/;
	my $line = $_;
	chomp($line);
	
	$self->{sql_host} = $1 if($line =~ /database.params.host.*?= "(.*)"$/);
	$self->{sql_user} = $1 if($line =~ /database.params.username.*?= "(.*)"$/);
	
	if($line =~ /database.params.password.*?= "(.*)"$/) {
		my $pass = $1;
		$self->{sql_pass} = $pass if($pass);
	}
	
	$self->{sql_table} = $1 if($line =~ /database.params.dbname.*?= "(.*)"$/);
		
	}
	
	close(F);
		
	# Create the database handle for the module
    $self->dbconnect();
	
    return $self;
}

sub DESTROY {
        my($self) = @_;

        if($dbh && $ENV{SCRIPT_NAME})        {
        $dbh->disconnect();
        }

}

sub dberror {
    my ($error) = @_;
    my ($hint);

        if ( $error =~ /Can't connect to/ ) {
            $hint =
"<li>Verify the SQL database is running</li><li>Check the SQL server is 
    listening to the specified socket, IP address</li>";

        }

        print <<_EOF;
Content-Type: text/html\n\n

<h1>Database Connection Error</h1>
<font color='red'>$error</font>
<ul>
$hint
<li>Check the global configuration 
file (/usr/local/atmail/webmail/config/dbconfig.ini) for the correct database details. Edit the configuraiton file with a text-editor and define the DB settings</li>
<li>Run the /usr/local/atmail/server-install.php script to check database details</li>
<li>Verify the database server is running correctly</li>
<li>Verify the MySQL /etc/my.cnf file has the correct settings for the number of database connections </li>

</ul>
_EOF

        exit(75);

}

sub init_connect {

}

sub sqlquery {
    my ( $self, $query ) = @_;

	$self->dbconnect();

    my $sth = $dbh->prepare($query) || print($dbh->errstr);
    $sth->execute || print($query . ":" . $dbh->errstr);

    return $sth->rows();
}

sub getid {
    my ($self) = @_;

	$self->dbconnect();

    return $dbh->selectrow_array('SELECT LAST_INSERT_ID()');

}

sub getvalue {
    my ( $self, $query ) = @_;

	$self->dbconnect();

	return $dbh->selectrow_array($query);
}

sub prepare {
    my ( $self, $query ) = @_;

	$self->dbconnect();

    my $sth = $dbh->prepare($query);

    return $sth;
}

sub execute {
    my ($self) = shift;
    my ($sth)  = shift;

    my @args = @_;

	#$self->dbconnect();
    $sth->execute(@args) || print("Error inserting record: $DBI::errstr");
    return 1;
}

sub idquery {
    my ( $self, $query ) = @_;
    my @data;

	$self->dbconnect();

	return if(!$query);

    my $sth = $dbh->prepare($query) || print($dbh->errstr);
    $sth->execute || print("$query - " . $dbh->errstr);

	my $i;

	for($i = 0; $i < $sth->rows(); $i++)	{
		my $row = $sth->fetchrow_array();
		push(@data, $row);
	}

	# Finish the statement
	$sth->finish();

	# Return the users data from the SQL query
	return @data;
}

sub hashquery {
    my ( $self, $query, $field ) = @_;
    my @id;

	$self->dbconnect();

    my $sth = $dbh->prepare($query) || print($dbh->errstr);
    $sth->execute || print("$query - " . $dbh->errstr);

    return $sth->fetchall_hashref($field);
}

sub quote {
    my ( $self, $s ) = @_;

	$self->dbconnect();

    return $dbh->quote($s);
}

sub disconnect {
    my ($self) = @_;

	if($dbh)	{
    $dbh->disconnect();
    $dbh = undef;
}
	
	return;
}

sub func {
    my ( $self, $type ) = @_;

	$self->dbconnect();

    my @d = $dbh->func($type);

    return @d;

}

sub tables	{
	my($self) = @_;

	$self->dbconnect();

	return $dbh->tables();

}

# Create a hash element
sub hashelement	{
	my($self, $query, @args) = @_;
	my(%hash);

    # Execute the query
    my $sth = $self->prepare($query) || print($dbh->errstr);
    $self->execute($sth, @args) || print($dbh->errstr);

	while(my @row = $sth->fetchrow_array()) {
				$hash{$row[0]} = $row[1];
	}

	# Finish the statement
	$sth->finish();

	# Return the users data from the SQL query
	return %hash;
}

# Create a query with placeholders ( more advanced )
sub sqlarray	{
	my($self, $query, @args) = @_;
	my(@data);

    my $sth = $self->prepare($query) || print($dbh->errstr);
    $self->execute($sth, @args) || print($dbh->errstr);

	my $i;

	for($i = 0; $i < $sth->rows(); $i++)	{
		my $row = $sth->fetchrow_array();
		push(@data, $row);
	}

	# Finish the statement
	$sth->finish();

	# Return the users data from the SQL query
	return @data;
}

# Run an SQL query, but do not bomb out if an SQL error occurs
sub sqlraw	{
	my($self, $query, @args) = @_;

	# Connect to the DB first
	$self->dbconnect();

    my $sth = $self->prepare($query) || print STDERR $dbh->errstr;
    $sth->execute(@args) || print STDERR $dbh->errstr;

}

sub sqldo	{
	my($self, $query, @args) = @_;

    my $sth = $self->prepare($query) || print ($dbh->errstr);
    $self->execute($sth, @args) || print ($dbh->errstr);

}

sub sqlgetfield	{
	my($self, $query, @args) = @_;

    # Insert the message into the EmailDatabase table (header info only)
    my $sth = $self->prepare($query) || print ($dbh->errstr);
    $self->execute($sth, @args) || print ($dbh->errstr);

	my @row = $sth->fetchrow_array();
	
	return $row[0];

	# Return if the query validates any fields
	#return $sth->rows();

}

sub sqlhash {
    my ( $self, $query, @args ) = @_;
    my %db;

    return if ( !$query );

	$self->dbconnect();

    my $sth = $dbh->prepare($query) || print($dbh->errstr);
    $sth->execute(@args) || print($dbh->errstr . " Query: " . $query);

    return if ( !$sth->rows );

    while ( my @fields = $sth->fetchrow ) {
        my $cols = $sth->{NUM_OF_FIELDS};

        for ( my $dbi = 0 ; $dbi < $cols ; $dbi++ ) {
                $db{$sth->{NAME}->[$dbi]} = $fields[$dbi];
        }

    }

    $sth->finish();
    return %db;
}

sub dbconnect {
my($self) = @_;

# Check the database handle exists already, and check that the connection is alive, otherwise reset
if($dbh && $dbh->ping()	)	{

		unless ($dbh) {
			$dbh->disconnect;
			undef $dbh;
			return $self->dbconnect();
		}

} else	{

	# We are a new connection to the database, init
    $dbh =
      DBI->connect(
      "DBI:mysql:database=" . $self->{sql_table} . ";hostname=" . $self->{sql_host},
      $self->{sql_user}, $self->{sql_pass} ) || dberror($DBI::errstr);
	
}

}

sub dbtables	{
my($self) = @_;

return $dbh->tables();

}

1;