package WebGUI::Macro::SQLExt;

use strict;
use WebGUI::Macro;
use WebGUI::Session;
use WebGUI::DatabaseLink;
use WebGUI::SQL;
use Data::Dumper;
use DBI;

$WebGUI::Macro::SQLExt::VERSION	= '00.00.02';

#-------------------------------------------------------------------
# Syntax:
# ^SQLExt($statement,$format,[$databaseLinkName|$database],[$username],[$password],[$host]);

sub process {
	my ($output, @data, $rownum, $temp);
	my ($statement, $format,$dblh,$db_user,$db_pass,$db_host) = WebGUI::Macro::getParams(shift);
	$format = '^0;' if ($format eq "");
	if (	
				$statement =~ /^\s*select/i  	||
				$statement =~ /^\s*show/i 		||
				$statement =~ /^\s*describe/i
			) 
	{
		my $dbh	= &_dbh($dblh,$db_user,$db_pass,$db_host);
		# handler error if $dbh is not a DBI type
		return $dbh if (ref($dbh) ne 'DBI::db');
		my $sth = WebGUI::SQL->unconditionalRead($statement,$dbh);
		unless ($sth->errorCode < 1) { 
			return '<p><b>SQLExt Macro Failed:</b> '.$sth->errorMessage.'<p>';
		} else {
			while (@data = $sth->array) {
                		$temp = $format; 
	                        $temp =~ s/\^(\d+)\;/$data[$1]/g; 
        	                $rownum++;
                	        $temp =~ s/\^rownum\;/$rownum/g;
				$output .= $temp;
	                }
			$sth->finish;
			return $output;
		}
	} elsif (
				$statement =~ /^\s*insert/i  	||
				$statement =~ /^\s*update/i  	||
				$statement =~ /^\s*delete/i  	
			) 
	{
		my $dbh	= &_dbh($dblh,$db_user,$db_pass,$db_host);
		# handler error if $dbh is not a DBI type
		return $dbh if (ref($dbh) ne 'DBI::db');
		my $sth = WebGUI::SQL->unconditionalRead($statement,$dbh);
		unless ($sth->errorCode < 1) { 
			return '<p><b>SQLExt Macro Failed:</b> '.$sth->errorMessage.'<p>';
		} 
		return '';
			
	} else 
	{
		return "Cannot execute this type of query.";
	}
}




sub _dbh {
	my $dbh;
	# if not defined assume localhost
	$_[3] ||= 'localhost';
	# if not defined database at this point, assume current sessione db handle
	if (!defined $_[0]) {
		$dbh	= $session{dbh};
	} elsif (!defined $_[1] && !defined $_[2]) {
		# if not defined username and password assume first params as 
		# database link name
        my $dbLink = WebGUI::DatabaseLink->new($_[0]);
        $dbh = $dbLink->dbh;
    } else {
		my $dsn 	= qq|DBI:mysql:database=$_[0];host=$_[3]|;
        #$dbh = DBI->connect($dsn,$_[1],$_[2];
		#return Data::Dumper::Dumper($dbh . "$dsn $_[1] $_[2]");
        eval {$dbh = DBI->connect($dsn,$_[1],$_[2],{ RaiseError => 1})};
		if ($@) {
			$dbh 	= "Unable to connect to database: " . DBI->errstr;
		}
    }
	return $dbh;
}

1;