<?
/*
CDE Simple is for developers just beginning with PHP database development, it takes the differences between the PHP database implementations and
makes it easy to work and switch between the databases. It also acts as the testing platform for the SQL translation tool.
*/
class CDESimple {
var $dbh;
var $error;
var $dbtype="sqlite";
var $dbpath="";
var $tmppath="";
var $lastsql="";
var $lasterror = Array();
var $debug=false;
var $affectedrows=0;
var $nooffields=0;
var $fieldinfo;
var $version="1.2 beta";
var $dbdateformat="m/d/Y";
var $inputdateformat="d/m/Y";
/* Function to make a FPDF report - returns filename */
function sql_report ( $sql="",
$groupby="",
$outputpath="output/",
$companyname="",
$title="",
$extraheader="somefunction",
$orientation="P",
$pagesize="A4",
$totalcolumns=Array(),
$compcolumns=Array(),
$createcsv=false,
$dontshow="",
$formats="",
$debug=false)
{
$pdf = new CDEFPDF($orientation, "mm", $pagesize);
$filename = $pdf->execute ($outputpath, $title, $companyname, $this, $sql, $orientation, $pagesize, $groupby, $totalcolumns, $compcolumns, $extraheader, $createcsv, $dontshow, $formats, $debug);
return $filename;
}
/* Error handling for the class */
function CDESimple_Error ($errno, $errstr, $errfile, $errline)
{
$backtrace = debug_backtrace();
$classfile = $errfile;
$classline = $errline;
foreach ($backtrace as $key => $value)
{
if (key_exists ( "file", $value))
{
if (basename($_SERVER["SCRIPT_FILENAME"]) == basename($value["file"]))
{
$errfile = $value["file"];
$errline = $value["line"];
}
}
}
$errorstyle = "font-family: Tahoma; color: red;";
$canadd = true;
switch ($errno)
{
case E_USER_ERROR:
//This is a fatal error and must stop the script!
$errorstyle = "font-family: Courier New; color: red;";
$errormsg = "<span style=\"$errorstyle\"><b>CDE Simple Error </b>$classfile [$classline] [$errno] $errstr in $errfile on line $errline <br />\n</span>";
if ($this->lastsql)
{
$errormsg .= "<pre><span style=\"$errorstyle\">".$this->get_error().":\n$this->lastsql </span></pre>";
}
echo $errormsg;
exit(1);
break;
case E_USER_WARNING:
//This error will allow application to continue but its not so good!
$errorstyle = "font-family: Courier New; color: blue;";
$errormsg = "<span style=\"$errorstyle\"><b>CDE Simple Warning </b>$classfile [$classline] [$errno] $errstr in $errfile on line $errline <br />\n</span>";
if ($this->lastsql)
{
$errormsg .= "<pre><span style=\"$errorstyle\">".$this->get_error().":\n$this->lastsql </span></pre>";
}
break;
case E_USER_NOTICE:
//This error is notifying the user of something that they should think about
$errorstyle = "font-family: Courier New; color: green;";
$errormsg = "<span style=\"$errorstyle\"><b>CDE Simple Notice </b>$classfile [$classline] [$errno] $errstr in $errfile on line $errline <br />\n</span>";
break;
default:
$canadd = false; //dont add these errors
//Not sure what the error is but read the screen - normally undeclared variables
$errorstyle = "font-family: Courier New; color: purple;";
$errormsg = "<span style=\"$errorstyle\"><b>CDE Simple Unknown </b>$classfile [$classline] [$errno] $errstr in $errfile on line $errline <br />\n</span>";
break;
}
//Assign the last error to error variable
$this->error = $errormsg;
if ($canadd) $this->lasterror [count($this->lasterror)] = $errormsg;
if ($canadd) echo $this->debug ? $errormsg : null;
}
/* Output the last error */
function last_error ()
{
return $this->lasterror [count($this->lasterror)-1];
}
/* Constructor for CDESimple */
function CDESimple ($dbpath="",
$username="",
$password="",
$dbtype="sqlite",
$debug=false,
$inputdateformat="d/m/Y")
{
$this->debug = $debug;
$this->inputdateformat = $inputdateformat;
//Define error handler
error_reporting(E_USER_ERROR | E_ALL | E_USER_WARNING | E_USER_NOTICE);
set_error_handler (array($this, 'CDESimple_Error'));
$this->connect ($dbpath, $username, $password, $dbtype);
}
/*****************************************************************************
BEGIN Connect
Connect to database and create handle in $dbh
*/
function connect ($dbpath="", $username="", $password="", $dbtype="sqlite")
{
if ($dbpath == "")
{
trigger_error ("No dbpath specified in ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
else
{
$this->dbpath = $dbpath;
$this->dbtype = $dbtype;
}
/*SQLite*/
if ($this->dbtype == "sqlite")
{
if (function_exists ("sqlite_popen"))
{
putenv("TMP=".$this->tmppath);
$this->dbh = @sqlite_popen ($this->dbpath);
}
else
{
trigger_error ("Please enable PHP module for ".$this->dbtype, E_USER_ERROR);
}
}
else
/*Firebird*/
if ($this->dbtype == "firebird")
{
if (function_exists ("ibase_pconnect"))
{
$this->dbh = @ibase_pconnect ($dbpath, $username, $password);
$this->dbdateformat = "m/d/Y";
}
else
{
trigger_error ("Please enable PHP module for ".$this->dbtype, E_USER_ERROR);
}
}
else
/*MySQL*/
if ($this->dbtype == "mysql")
{
if (function_exists ("mysql_connect"))
{
$dbpath = explode (":", $dbpath);
$this->dbh = @mysql_connect ($dbpath[0], $username, $password);
$this->dbdateformat = "Y-m-d";
@mysql_select_db ($dbpath[1]);
}
else
{
trigger_error ("Please enable PHP module for ".$this->dbtype, E_USER_ERROR);
}
}
else
/* Oracle */
if ($this->dbtype == "oracle")
{
if (function_exists ("oci_connect"))
{
$this->dbh = @oci_connect ($username, $password, $dbpath);
}
else
{
trigger_error ("Please enable PHP module for ".$this->dbtype, E_USER_ERROR);
}
}
else
/* Postgres */
if ($this->dbtype == "postgres")
{
$dbpath = explode (":", $dbpath);
$sconnect = "host={$dbpath[0]} dbname={$dbpath[1]} user=$username password=$password ";
$this->dbdateformat = "Y-m-d";
if (function_exists ("pg_connect"))
{
$this->dbh = @pg_connect ($sconnect);
}
else
{
trigger_error ("Please enable PHP module for ".$this->dbtype, E_USER_ERROR);
}
}
else
/* Microsoft SQL Server */
if ($this->dbtype == "mssql")
{
$dbpath = explode (":", $dbpath);
if (function_exists ("mssql_connect"))
{
//MSSQL needs changes in the php.ini file - we need to make the user aware of this.
$this->dbh = @mssql_pconnect ("HRV", $username, $password);
$this->dbdateformat = "m/d/Y";
@mssql_select_db ($dbpath[1]);
ini_set ("mssql.textlimit", "2147483647"); //We need to do this to make blobs work and it doesn't work!!!!!!
ini_set ("mssql.textsize", "2147483647"); //We need to do this to make blobs work
}
else
{
trigger_error ("Please enable PHP module for ".$this->dbtype, E_USER_ERROR);
}
}
else
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
/* Debugging for Connect */
if (!$this->dbh)
{
trigger_error ("Could not establish connection for $dbpath in ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
}
/*
END Connect
*****************************************************************************/
/*****************************************************************************
BEGIN Close
*/
function close ()
{
$result = false;
if (!$this->dbh)
{
trigger_error ("No database handle, use connect first in ".__METHOD__." for ".$this->dbtype, E_USER_WARNING);
}
else
/*SQLite*/
if ($this->dbtype == "sqlite")
{
$result = @sqlite_close($this->dbh);
$result = true;
}
else
/*Firebird*/
if ($this->dbtype == "firebird")
{
$result = @ibase_close($this->dbh);
$result = true;
}
else
/* Oracle */
if ($this->dbtype == "oracle")
{
$result = @oci_close($this->dbh);
$result = true;
}
else
/*MySQL*/
if ($this->dbtype == "mysql")
{
$result = @mysql_close($this->dbh);
$result = true;
}
else
/* Postgres */
if ($this->dbtype == "postgres")
{
$result = @pg_close ($this->dbh);
}
else
/* Microsoft SQL Server */
if ($this->dbtype == "mssql")
{
$result = @mssql_close ($this->dbh);
}
else
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
/* Debugging for Close */
if ($result)
{
$this->dbh = "";
}
else
{
trigger_error ("Cant close $this->dbpath in ".__METHOD__." for ".$this->dbtype, E_USER_NOTICE);
}
return $result;
}
/*
END Close
*****************************************************************************/
/*****************************************************************************
BEGIN set_database
*/
function set_database ()
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype);
}
/*
END set_database
*****************************************************************************/
/*****************************************************************************
BEGIN get_instance
Finds all types of a word and returns all the positions and word type
*/
function get_instance ($word, $sql)
{
$icount = 0;
foreach ($sql as $id => $value)
{
$value = trim ($value);
$ipos = stripos ($value, $word);
if ($ipos !== false)
{
$instance[$icount] = $id;
$icount++;
}
}
return $instance;
}
/*
END get_instance
*****************************************************************************/
/*****************************************************************************
BEGIN parsesql
*/
function parsesql ($sql="", $fromdbtype="generic", $todbtype="generic")
{
//ignore initially the fromdbtype & todbtype
//first section - change limits in mysql to firebird - needs to be enhanced for many sub selects
//flatten sql
if (stripos ($sql, "update") === false && stripos ($sql, "insert") === false && stripos ($sql, "delete") === false )
{
$sql = str_replace ("\n", "", $sql);
$sql = str_replace ("\r", "", $sql);
$sql = str_replace (" ,", ",", $sql);
$sql = str_replace (", ", ",", $sql);
$parsedsql = explode (" ", $sql);
if ($this->dbtype == "postgres" && stripos($sql, "blob") !== false) //postgres doesn't know about blobs it uses oids
{
foreach ($parsedsql as $id => $value)
{
$value = str_ireplace ("longblob", "oid", $value);
$value = str_ireplace ("shortblob", "oid", $value);
$parsedsql[$id] = str_ireplace ("blob", "oid", $value);
}
}
else
if ($this->dbtype == "mysql" && stripos($sql, "first ") !== false)
{
//select first 1 skip 10 must become
$firsts = $this->get_instance ("first", $parsedsql);
if (count($firsts) > 0)
{
$icount = 0;
//kill all the firsts
foreach ($firsts as $id => $index)
{
$limits[$icount] = $parsedsql[$index+1];
unset ($parsedsql[$index+1]);
unset ($parsedsql[$index]);
if (strtolower($parsedsql[$index+2]) == "skip")
{
$limits[$icount] = $parsedsql[$index+3].",".$limits[$icount];
unset ($parsedsql[$index+2]);
unset ($parsedsql[$index+3]);
}
$limits[$icount] = "limit ".$limits[$icount];
$limits[$icount] = str_replace ("0,", "", $limits[$icount]);
$icount++;
}
//Add the first limit to the end of the parsedsql;
array_push ($parsedsql, $limits[0]);
}
}
else
if ($this->dbtype == "firebird" && stripos($sql, "limit") !== false) //check for MySQL or ORacle limit
{
//find all the selects
$selects = $this->get_instance ("select", $parsedsql);
$limits = $this->get_instance ("limit", $parsedsql);
if (count($limits) > 0)
{
//remove all the limits & parse for skip & first
$icount = 0;
foreach ($limits as $id => $index)
{
$firstskip[$icount] = $parsedsql[$index+1];
if (stripos($firstskip[$icount], ")") !== false)
{
$parsedsql[$index+1] = ")";
$firstskip[$icount] = str_replace (")", "", $firstskip[$icount]);
}
else
{
unset ($parsedsql[$index+1]);
}
$firstskip[$icount] = explode(",", $firstskip[$icount]);
unset ($parsedsql[$index]);
$icount++;
}
//do the first & last select
if (count($firstskip[$icount-1]) == 1)
{
$parsedsql[$selects[0]] = "select first ".$firstskip[$icount-1][0];
}
else
{
$parsedsql[$selects[0]] = "select first ".$firstskip[$icount-1][1]. " skip ".$firstskip[$icount-1][0];
}
//and then the rest
if ($icount > 1)
{
for ($i = 1; $i < $icount; $i++)
{
if (count($firstskip[$i]) == 1)
{
$parsedsql[$selects[$i]] = "(select first ".$firstskip[$i][0];
}
else
{
$parsedsql[$selects[$i]] = "(select first ".$firstskip[$i][1]. " skip ".$firstskip[$i][0];
}
}
}
}
//print_r ($parsedsql);
}
else
if (($this->dbtype == "mssql") && (stripos($sql, "blob") !== false || stripos($sql, "date") !== false || stripos($sql, "now") !== false))
{
//check for blobs, dates and now
$parsedsql = $sql;
$sqlwords = array('/ blob/','/ date/', '/\'now\'/');
$repwords = array (' image null', ' datetime null', 'NOW()');
$parsedsql = preg_replace($sqlwords, $repwords, $parsedsql);
}
$newsql = "";
if (is_array($parsedsql))
{
foreach ($parsedsql as $id => $value)
{
if (trim($value) != "")
{
$newsql .= $value." ";
}
}
$parsedsql = $newsql;
}
}
else
{
$parsedsql = $sql;
}
$this->lastsql = $parsedsql; // save the last sql
return $parsedsql;
}
/*
END parsesql
*****************************************************************************/
/*****************************************************************************
BEGIN Set Params - make all ? replaced with passed params
*/
function set_params ($sql="", $inputvalues=Array())
{
$lastplace = 1; //Mustn't go back in the replace
$count = 0;
for ($i = 1; $i < sizeof($inputvalues); $i++)
{
$tryme = $inputvalues[$i];
$inputvalues[$i] = str_replace ("'", "''", $inputvalues[$i]); //some strings have single ' which make it break on replacing!
if ($this->dbtype == "mysql") $inputvalues[$i] = mysql_real_escape_string($inputvalues[$i]);
if ($this->dbtype == "sqlite") $inputvalues[$i] = sqlite_escape_string($inputvalues[$i]);
$inputvalues[$i] = "'".$inputvalues[$i]."'";
$lastpos = 1;
while ($lastpos <> 0)
{
$lastpos = strpos ($sql, "?", $lastplace);
if ($lastpos == "") break; //This checks that lastpos
if ($sql[$lastpos-1] != "<" || $sql[$lastpos+1] != ">")
{
$sql = substr_replace($sql, $inputvalues[$i], $lastpos, 1);
$lastplace = $lastpos+strlen($inputvalues[$i]);
}
$lastpos = 0;
}
$count++;
}
return $sql;
}
/*
END Set Params
*****************************************************************************/
/*****************************************************************************
BEGIN Exec
*/
function exec ($sql="")
{
$inputvalues = func_get_args();
$this->error = ""; // reset the last error;
$result = false;
$sql = $this->parsesql ($sql);
$sql = explode(";\n", $sql); //see if more than one statment - ; separated with line feed
if (!$this->dbh)
{
trigger_error ("No database handle, use connect first in ".__METHOD__." for ".$this->dbtype, E_USER_WARNING);
}
else
/*SQLite*/
if ($this->dbtype == "sqlite")
{
$result = "";
foreach ($sql as $id => $script)
{
$script = $this->set_params ($script, $inputvalues);
@sqlite_exec ($this->dbh, $script , $result);
if ($result == "")
{
$result .= "No Errors;"; //return result with errors separated by ;
}
}
}
else
/*Firebird*/
if ($this->dbtype == "firebird")
{
foreach ($sql as $id => $script)
{
$query = ibase_prepare ($this->dbh, $script);
$params = array ();
$params[0] = $query;
//what if we have passed some parameters - firebird can do this
for ($i = 1; $i < func_num_args(); $i++)
{
$params[$i] =func_get_arg($i);
}
if (sizeof ($params) != 0)
{
$result = @call_user_func_array ("ibase_execute", $params);
}
else
{
$result = @ibase_execute ($query);
}
}
}
else
/*Oracle*/
if ($this->dbtype == "oracle")
{
foreach ($sql as $id => $script)
{
$script = $this->set_params ($script, $inputvalues);
$query = @oci_parse ($this->dbh, $script);
$result = @oci_execute ($query);
}
}
else
/*MySQL*/
if ($this->dbtype == "mysql")
{
foreach ($sql as $id => $script)
{
$script = $this->set_params ($script, $inputvalues);
$result = @mysql_query ($script, $this->dbh);
if ($result != false)
{
$result = "No Errors";
}
else
{
$result = $this->get_error();
}
}
}
else
/*Postgres*/
if ($this->dbtype == "postgres")
{
foreach ($sql as $id => $script)
{
$script = $this->set_params ($script, $inputvalues);
$result = @pg_query ($script);
if ($result != false)
{
$result = "No Errors";
}
else
{
$result = $this->get_error();
}
}
}
else
/*Microsoft SQL Server*/
if ($this->dbtype == "mssql")
{
foreach ($sql as $id => $script)
{
$script = $this->set_params ($script, $inputvalues);
$result = @mssql_query ($script);
if ($result != false)
{
$result = "No Errors";
}
else
{
$result = $this->get_error();
}
}
}
else
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
/* Debugging for Exec */
if ($result)
{
return $result;
}
else
{
trigger_error ("Cant run ".__METHOD__." for ".$this->dbtype, E_USER_WARNING);
}
}
/*
END Exec
*****************************************************************************/
/*****************************************************************************
BEGIN Commit
*/
function commit()
{
if (!$this->dbh)
{
trigger_error ("No database handle, use connect first in ".__METHOD__." for ".$this->dbtype, E_USER_WARNING);
}
else
/*SQLite*/
if ($this->dbtype == "sqlite")
{
trigger_error ("Unsupported feature in ".__METHOD__." for ".$this->dbtype, E_USER_NOTICE);
$result = true;
}
else
/*Firebird*/
if ($this->dbtype == "firebird")
{
$result = @ibase_commit();
}
else
/*Oracle*/
if ($this->dbtype == "oracle")
{
$result = @oci_commit($this->dbh);
}
else
/*MySQL*/
if ($this->dbtype == "mysql")
{
trigger_error ("Unsupported feature in ".__METHOD__." for ".$this->dbtype, E_USER_NOTICE);
$result = true;
}
else
/*Postgres*/
if ($this->dbtype == "postgres")
{
//Please test this !!!
@pg_query($this->dbh, "commit");
//0trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
$result = true;
}
else
/*Microsoft SQL Server*/
if ($this->dbtype == "mssql")
{
trigger_error ("Unsupported feature in ".__METHOD__." for ".$this->dbtype, E_USER_NOTICE);
$result = true;
}
else
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
/* Debugging for Commit */
if ($result)
{
return $result;
}
else
{
trigger_error ("Cant run ".__METHOD__." for ".$this->dbtype, E_USER_WARNING);
}
}
/*
END Commit
*****************************************************************************/
/*****************************************************************************
BEGIN Get Error - last database error
*/
function get_error()
{
$result = false;
/*SQLite*/
if ($this->dbtype == "sqlite")
{
$result = @sqlite_error_string(sqlite_last_error ($this->dbh));
}
else
/*Firebird*/
if ($this->dbtype == "firebird")
{
$result = @ibase_errmsg();
}
else
/*Oracle*/
if ($this->dbtype == "oracle")
{
$result = @oci_error();
}
else
/*MySQL*/
if ($this->dbtype == "mysql")
{
$result = @mysql_error();
}
else
/*Postgres*/
if ($this->dbtype == "postgres")
{
$result = @pg_last_error($this->dbh);
}
else
/*Microsoft SQL Server*/
if ($this->dbtype == "mssql")
{
$result = @mssql_get_last_message();
}
else
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
return $result;
}
/*
END Get Error
*****************************************************************************/
/*****************************************************************************
BEGIN Get Row - Fetch a row in a number of formats
$rowtype = 0 - Object
1 - Array
2 - Array Indexed by Field Name
*/
function get_row ($sql="", $rowtype=0, $fetchblob=true)
{
$result = false;
//Clear the field data for new query
unset($this->fieldinfo);
//Dont matter if there is no sql - use the last one.
if ($sql == "") $sql = $this->lastsql;
$sql = $this->parsesql ($sql);
/*SQLite*/
if ($this->dbtype == "sqlite")
{
//build an array of results
$query = @sqlite_query ($this->dbh, $sql);
$icount = 0;
switch ($rowtype)
{
case 0: //Object
while ($row = @sqlite_fetch_object ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 1: //Index
while ($row = @sqlite_fetch_array ($query, SQLITE_NUM))
{
$result[$icount] = $row;
$icount++;
}
break;
case 2: //Associative Index
while ($row = @sqlite_fetch_array ($query, SQLITE_ASSOC))
{
$result[$icount] = $row;
$icount++;
}
break;
}
$this->nooffields = @sqlite_num_fields ($query);
for ($i = 0; $i < $this->nooffields; $i++)
{
$this->fieldinfo[$i]["name"] = @sqlite_field_name ($query, $i);
$this->fieldinfo[$i]["alias"] = ucwords(strtolower($this->fieldinfo[$i]["name"]));
}
$this->affectedrows = $icount;
}
else
/*Firebird*/
if ($this->dbtype == "firebird")
{
//build an array of results
$query = @ibase_query ($this->dbh, $sql);
if ($query)
{
$icount = 0;
switch ($rowtype)
{
case 0: //Object
while ($row = @ibase_fetch_object ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 1: //Index
while ($row = @ibase_fetch_row ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 2: //Associative Index
while ($row = @ibase_fetch_assoc ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
}
}
$this->nooffields = @ibase_num_fields ($query);
for ($i = 0; $i < $this->nooffields; $i++)
{
$this->fieldinfo[$i] = @ibase_field_info($query, $i);
//print_r ($this->fieldinfo[$i]);
if ($this->fieldinfo[$i]["name"] == ""){
$this->fieldinfo[$i][0] = $this->fieldinfo[$i]["alias"];
$this->fieldinfo[$i]["name"] = $this->fieldinfo[$i]["alias"];
}
}
$this->affectedrows = $icount;
}
else
/*Oracle*/
if ($this->dbtype == "oracle")
{
//build an array of results
$query = @oci_parse ($this->dbh, $sql);
@oci_execute ($query);
$icount = 0;
switch ($rowtype)
{
case 0: //Object
while ($row = @oci_fetch_object ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 1: //Index
while ($row = @oci_fetch_row ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 2: //Associative Index
while ($row = @oci_fetch_array ($query, OCI_ASSOC))
{
$result[$icount] = $row;
$icount++;
}
break;
}
}
else
/*My SQL*/
if ($this->dbtype == "mysql")
{
//build an array of results
$query = @mysql_query ($sql, $this->dbh);
$icount = 0;
switch ($rowtype)
{
case 0: //Object
while ($row = @mysql_fetch_object ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 1: //Index
while ($row = @mysql_fetch_row ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 2: //Associative Index
while ($row = @mysql_fetch_array ($query, MYSQL_ASSOC))
{
$result[$icount] = $row;
$icount++;
}
break;
}
$this->nooffields = @mysql_num_fields ($query);
for ($i = 0; $i < $this->nooffields; $i++)
{
$column_name = @mysql_field_name($query, $i);
$column_type = @mysql_field_type($query, $i);
$column_size = @mysql_field_len($query, $i);
$fieldinfo = @mysql_fetch_field ($query, $i);
$this->fieldinfo[$i]["name"] = strtoupper($column_name);
$this->fieldinfo[$i]["alias"] = strtoupper($column_name);
$this->fieldinfo[$i]["length"] = $column_size;
$this->fieldinfo[$i]["type"] = strtoupper($column_type);
$this->fieldinfo[$i][1] = strtoupper($column_name);;
$this->fieldinfo[$i][0] = strtoupper($column_name);;
$this->fieldinfo[$i][2] = $column_size;
$this->fieldinfo[$i][4] = strtoupper($column_type);
}
$this->affectedrows = $icount;
}
else
/*Postgres*/
if ($this->dbtype == "postgres")
{
//build an array of results
$query = @pg_query ($this->dbh, $sql);
$icount = 0;
switch ($rowtype)
{
case 0: //Object
while ($row = @pg_fetch_object ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 1: //Index
while ($row = @pg_fetch_row ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 2: //Associative Index
while ($row = @pg_fetch_assoc ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
}
$this->nooffields = @pg_num_fields ($query);
for ($i = 0; $i < $this->nooffields; $i++)
{
$column_name = @pg_field_name($query, $i);
$column_type = @pg_field_type($query, $i);
$column_size = @pg_field_size($query, $i);
$this->fieldinfo[$i]["name"] = strtoupper($column_name);
$this->fieldinfo[$i]["alias"] = strtoupper($column_name);
$this->fieldinfo[$i]["length"] = $column_size;
$this->fieldinfo[$i]["type"] = strtoupper($column_type);
$this->fieldinfo[$i][1] = strtoupper($column_name);;
$this->fieldinfo[$i][0] = strtoupper($column_name);;
$this->fieldinfo[$i][2] = $column_size;
$this->fieldinfo[$i][4] = strtoupper($column_type);
}
$this->affectedrows = $icount;
}
else
/*Microsoft SQL Server*/
if ($this->dbtype == "mssql")
{
//build an array of results
$query = @mssql_query ($sql);
$icount = 0;
switch ($rowtype)
{
case 0: //Object
while ($row = @mssql_fetch_object ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 1: //Index
while ($row = @mssql_fetch_row ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
case 2: //Associative Index
while ($row = @mssql_fetch_assoc ($query))
{
$result[$icount] = $row;
$icount++;
}
break;
}
$this->nooffields = @mssql_num_fields ($query);
for ($i = 0; $i < $this->nooffields; $i++)
{
$column_name = @mssql_field_name($query, $i);
$column_type = @mssql_field_type($query, $i);
$column_size = @mssql_field_length($query, $i);
$this->fieldinfo[$i]["name"] = strtoupper($column_name);
$this->fieldinfo[$i]["alias"] = strtoupper($column_name);
$this->fieldinfo[$i]["length"] = $column_size;
$this->fieldinfo[$i]["type"] = strtoupper($column_type);
$this->fieldinfo[$i][1] = strtoupper($column_name);;
$this->fieldinfo[$i][0] = strtoupper($column_name);;
$this->fieldinfo[$i][2] = $column_size;
$this->fieldinfo[$i][4] = strtoupper($column_type);
}
$this->affectedrows = $icount;
}
else
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
//create the field information based on the select statement
foreach ($this->fieldinfo as $id => $field)
{
if (strpos(strtoupper($field[4]), "NUMERIC") !== false || strpos(strtoupper($field[4]), "DECIMAL") !== false || strpos(strtoupper($field[4]), "INTEGER") !== false || strpos(strtoupper($field[4]), "INT") !== false || strpos(strtoupper($field[4]), "BIGINT") !== false || strpos(strtoupper($field[4]), "DOUBLE") !== false)
{
if (strpos(strtoupper($field[4]), "BIGINT") !== false) //make bigint into integer
{
$field[4] = "INTEGER";
$field["type"] = "INTEGER";
}
if (strpos(strtoupper($field[4]), "NUMERIC") !== false || strpos(strtoupper($field[4]), "DECIMAL") !== false || strpos(strtoupper($field[4]), "DOUBLE") !== false)
{
$field[4] = "CURRENCY";
$field["type"] = "CURRENCY";
}
$field[5] = "right";
$field["align"] = "right";
}
else
{
$field[5] = "left";
$field["align"] = "left";
}
if ($field[3] >= 100)
{
$field[6] = 180;
$field["htmllength"] = 180;
}
else
if ($field[3] < 100)
{
if ($field[4] == "CURRENCY")
{
$field[6] = 90;
$field["htmllength"] = 90 ;
}
else
{
$field[6] = 100;
$field["htmllength"] = 100;
}
}
else
{
$field[6] = $field[3];
$field["htmllength"] = $field[3];
}
$this->fieldinfo[$id] = $field;
}
/* Debugging for get_row */
if ($result)
{
//check the data
//Make the object uppercase for all the field names which is our standard convention
if ($rowtype == 0)
{
foreach ($result as $id => $value)
{
foreach ($value as $field => $fieldvalue)
{
$fieldinfo = $this->get_field_by_name ($field);
if ($fetchblob)
{
if ($fieldinfo["type"] == "BLOB" || $fieldinfo["type"] == "OID") $fieldvalue = $this->get_blob ($fieldvalue);
}
$field = strtoupper($field);
$newresult[$id]->$field = $fieldvalue;
}
}
$result = $newresult;
}
else
if ($rowtype == 1) //We can't leave this out because we need to read blobs - so the fieldnames are not made uppercase
{
foreach ($result as $id => $value)
{
foreach ($value as $field => $fieldvalue)
{
$fieldinfo = $this->fieldinfo[$field];
if ($fetchblob)
{
if ($fieldinfo["type"] == "BLOB" || $fieldinfo["type"] == "OID") $fieldvalue = $this->get_blob ($fieldvalue);
}
$newresult[$id][$field] = $fieldvalue;
}
}
$result = $newresult;
}
else
if ($rowtype == 2)
{
$newresult = Array();
foreach ($result as $id => $value)
{
foreach ($value as $field => $fieldvalue)
{
$fieldinfo = $this->get_field_by_name ($field);
if ($fetchblob)
{
if ($fieldinfo["type"] == "BLOB" || $fieldinfo["type"] == "OID") $fieldvalue = $this->get_blob ($fieldvalue);
}
$field = strtoupper($field);
$newresult[$id][$field] = $fieldvalue;
}
}
$result = $newresult;
}
}
else
{
trigger_error ("Cant get row for $this->dbpath in ".__METHOD__." for ".$this->dbtype, E_USER_NOTICE);
}
return $result;
}
/*
END Get Row
*****************************************************************************/
/*****************************************************************************
BEGIN Get Blob - Get the data from a blob like in Firebird
*/
function get_blob ($column)
{
$content = "";
if ($column && $this->dbtype == "firebird")
{
//Get the blob information
$blob_data = ibase_blob_info($this->dbh, $column);
//Get a handle to the blob
$blob_hndl = ibase_blob_open($this->dbh, $column);
//Get the blob contents
$content = ibase_blob_get($blob_hndl, $blob_data[0]);
}
else
if ($column && $this->dbtype == "postgres")
{
//This may kill performance finding the size of the blob - but how else ???
pg_query($this->dbh, "begin");
$handle = pg_lo_open($this->dbh, $column, "r");
//Find the end of the blob
pg_lo_seek($handle, 0, PGSQL_SEEK_END);
$size = pg_lo_tell($handle);
//Find the beginning of the blob
pg_lo_seek($handle, 0, PGSQL_SEEK_SET);
//Read the whole blob
$content = pg_lo_read($handle, $size);
pg_query($this->dbh, "commit");
}
else //All other databases ???
{
$content = $column;
}
return $content;
}
/*
END Get Blob
*****************************************************************************/
/*****************************************************************************
BEGIN Set Blob - Set the data to a blob like in Firebird, MySQL, Postgres
*/
function set_blob ($tablename,$column,$blobvalue,$filter="fieldname = 0")
{
$result = "";
if ($column && $this->dbtype == "sqlite")
{
$sqlupdate = "update $tablename set $column = '".sqlite_escape_string($blobvalue)."' where $filter";
$result = $this->exec ($sqlupdate);
}
else
if ($column && $this->dbtype == "firebird")
{
$sqlupdate = "update $tablename set $column = ? where $filter";
$result = $this->exec ($sqlupdate, $blobvalue);
}
else
if ($column && $this->dbtype == "mysql")
{
$sqlupdate = "update $tablename set $column = 0x".bin2hex($blobvalue)." where $filter";
$result = $this->exec ($sqlupdate);
}
else
if ($column && $this->dbtype == "postgres")
{
pg_query($this->dbh, "begin");
$oid = pg_lo_create($this->dbh);
$handle = pg_lo_open($this->dbh, $oid, "w");
pg_lo_write($handle, $blobvalue);
pg_lo_close($handle);
pg_query($this->dbh, "commit");
$sqlupdate = "update $tablename set $column = '$oid' where $filter";
$result = $this->exec ($sqlupdate);
}
else
if ($column && $this->dbtype == "mssql")
{
$sqlupdate = "update $tablename set $column = 0x".bin2hex($blobvalue)." where $filter";
$result = $this->exec ($sqlupdate);
}
return $result;
}
/*
END Set Blob
*****************************************************************************/
/*****************************************************************************
BEGIN Get Value - Fetch a row in a number of formats
*/
function get_value ($id=0, $sql="", $rowtype=0, $fetchblob=true)
{
$result = false;
//Dont matter if there is no sql - use the last one.
if ($sql == "") $sql = $this->lastsql;
$sql = $this->parsesql ($sql);
$result = $this->get_row ($sql, $rowtype, $fetchblob);
$result = $result[$id]; //return the first value
return $result;
}
/*
END Get Value
*****************************************************************************/
/*****************************************************************************
BEGIN get_database
Returns the layout of the whole database in an easy to use array
Need to add support for views & stored procedures later on
*/
function get_database ()
{
$result = false;
$database = NULL;
if (!$this->dbh)
{
trigger_error ("No database handle, use connect first in ".__METHOD__." for ".$this->dbtype, E_USER_WARNING);
}
else
/*SQLite*/
if ($this->dbtype == "sqlite")
{
$sqltables = "select name
from sqlite_master
where type='table'
order by name";
$tables = $this->get_row ($sqltables);
foreach ($tables as $id => $record)
{
$sqlinfo = "pragma table_info($record->NAME);";
$tableinfo = $this->get_row ($sqlinfo);
//Go through the tables and extract their column information
foreach ($tableinfo as $tid => $trecord)
{
$database[trim($record->NAME)][$tid]["column"] = trim($trecord->CID);
$database[trim($record->NAME)][$tid]["field"] = trim($trecord->NAME);
$database[trim($record->NAME)][$tid]["type"] = trim($trecord->TYPE);
$database[trim($record->NAME)][$tid]["default"] = trim($trecord->DFLT_VALUE);
$database[trim($record->NAME)][$tid]["notnull"] = trim($trecord->NOTNULL);
$database[trim($record->NAME)][$tid]["pk"] = trim($trecord->PK);
}
}
$result = $database;
}
else
/*Firebird*/
if ($this->dbtype == "firebird")
{
$sqltables = 'select distinct rdb$relation_name as tablename
from rdb$relation_fields
where rdb$system_flag=0
and rdb$view_context is null';
$tables = $this->get_row ($sqltables);
foreach ($tables as $id => $record)
{
$sqlinfo = 'SELECT r.RDB$FIELD_NAME AS field_name,
r.RDB$DESCRIPTION AS field_description,
r.RDB$DEFAULT_VALUE AS field_default_value,
r.RDB$NULL_FLAG AS field_not_null_constraint,
f.RDB$FIELD_LENGTH AS field_length,
f.RDB$FIELD_PRECISION AS field_precision,
f.RDB$FIELD_SCALE AS field_scale,
CASE f.RDB$FIELD_TYPE
WHEN 261 THEN \'BLOB\'
WHEN 14 THEN \'CHAR\'
WHEN 40 THEN \'CSTRING\'
WHEN 11 THEN \'D_FLOAT\'
WHEN 27 THEN \'DOUBLE\'
WHEN 10 THEN \'FLOAT\'
WHEN 16 THEN \'INT64\'
WHEN 8 THEN \'INTEGER\'
WHEN 9 THEN \'QUAD\'
WHEN 7 THEN \'SMALLINT\'
WHEN 12 THEN \'DATE\'
WHEN 13 THEN \'TIME\'
WHEN 35 THEN \'TIMESTAMP\'
WHEN 37 THEN \'VARCHAR\'
ELSE \'UNKNOWN\'
END AS field_type,
f.RDB$FIELD_SUB_TYPE AS field_subtype,
coll.RDB$COLLATION_NAME AS field_collation,
cset.RDB$CHARACTER_SET_NAME AS field_charset
FROM RDB$RELATION_FIELDS r
LEFT JOIN RDB$FIELDS f ON r.RDB$FIELD_SOURCE = f.RDB$FIELD_NAME
LEFT JOIN RDB$COLLATIONS coll ON r.RDB$COLLATION_ID = coll.RDB$COLLATION_ID
AND f.RDB$CHARACTER_SET_ID = coll.RDB$CHARACTER_SET_ID
LEFT JOIN RDB$CHARACTER_SETS cset ON f.RDB$CHARACTER_SET_ID = cset.RDB$CHARACTER_SET_ID
WHERE r.RDB$RELATION_NAME = \''.$record->TABLENAME.'\'
ORDER BY r.RDB$FIELD_POSITION';
$tableinfo = $this->get_row ($sqlinfo);
//Go through the tables and extract their column information
foreach ($tableinfo as $tid => $trecord)
{
$database[trim($record->TABLENAME)][$tid]["column"] = $tid;
$database[trim($record->TABLENAME)][$tid]["field"] = trim($trecord->FIELD_NAME);
$database[trim($record->TABLENAME)][$tid]["description"] = trim($trecord->FIELD_DESCRIPTION);
$database[trim($record->TABLENAME)][$tid]["type"] = trim($trecord->FIELD_TYPE);
$database[trim($record->TABLENAME)][$tid]["length"] = trim($trecord->FIELD_LENGTH);
$database[trim($record->TABLENAME)][$tid]["precision"] = trim($trecord->FIELD_PRECISION);
$database[trim($record->TABLENAME)][$tid]["default"] = trim($trecord->FIELD_DEFAULT_VALUE);
$database[trim($record->TABLENAME)][$tid]["notnull"] = trim($trecord->NOTNULL);
$database[trim($record->TABLENAME)][$tid]["pk"] = trim($trecord->PK);
}
}
$result = $database;
}
else
/* Oracle */
if ($this->dbtype == "oracle")
{
$result = true;
}
else
/*MySQL*/
if ($this->dbtype == "mysql")
{
$dbpath = explode (":", $this->dbpath);
$sqltables = "SELECT table_name, table_type, engine
FROM INFORMATION_SCHEMA.tables
WHERE upper(table_schema) = upper('{$dbpath[1]}')
ORDER BY table_type ASC, table_name DESC";
$tables = $this->get_row ($sqltables);
foreach ($tables as $id => $record)
{
$sqlinfo = 'show columns from '.$record->TABLE_NAME;
$tableinfo = $this->get_row ($sqlinfo);
//Go through the tables and extract their column information
foreach ($tableinfo as $tid => $trecord)
{
//split the length & type for field
if (strpos($trecord->TYPE, "(") !== false)
{
$type = substr ($trecord->TYPE, 0, strpos($trecord->TYPE, "("));
$length = substr ($trecord->TYPE, strpos($trecord->TYPE, "(")+1, strpos($trecord->TYPE, ")")-strpos($trecord->TYPE, "(")-1);
}
else
{
$type = $trecord->TYPE;
}
$database[trim($record->TABLE_NAME)][$tid]["column"] = $tid;
$database[trim($record->TABLE_NAME)][$tid]["field"] = trim($trecord->FIELD);
$database[trim($record->TABLE_NAME)][$tid]["description"] = trim($trecord->EXTRA);
$database[trim($record->TABLE_NAME)][$tid]["type"] = trim($type);
$database[trim($record->TABLE_NAME)][$tid]["length"] = trim($length);
$database[trim($record->TABLE_NAME)][$tid]["precision"] = "";
$database[trim($record->TABLE_NAME)][$tid]["default"] = trim($trecord->DEFAULT);
$database[trim($record->TABLE_NAME)][$tid]["notnull"] = trim($trecord->NULL);
$database[trim($record->TABLE_NAME)][$tid]["pk"] = trim($trecord->KEY);
}
}
$result = $database;
}
else
/*Postgres*/
if ($this->dbtype == "postgres")
{
$dbpath = explode (":", $this->dbpath);
$sqltables = "SELECT table_name
FROM INFORMATION_SCHEMA.tables
WHERE upper(table_catalog) = upper('{$dbpath[1]}')
AND upper(table_schema) = upper('public')
ORDER BY table_type ASC, table_name DESC";
$tables = $this->get_row ($sqltables);
foreach ($tables as $id => $record)
{
$sqlinfo = "select * from INFORMATION_SCHEMA.columns where upper(table_name) = upper('$record->TABLE_NAME')";
$tableinfo = $this->get_row ($sqlinfo);
//Go through the tables and extract their column information
foreach ($tableinfo as $tid => $trecord)
{
$database[trim($record->TABLE_NAME)][$tid]["column"] = $tid;
$database[trim($record->TABLE_NAME)][$tid]["field"] = trim(strtoupper($trecord->COLUMN_NAME));
$database[trim($record->TABLE_NAME)][$tid]["description"] = "";
$database[trim($record->TABLE_NAME)][$tid]["type"] = trim(strtoupper($trecord->UDT_NAME));
$database[trim($record->TABLE_NAME)][$tid]["length"] = trim(strtoupper($trecord->CHARACTER_MAXIMUM_LENGTH));
$database[trim($record->TABLE_NAME)][$tid]["precision"] = trim(strtoupper($trecord->NUMERIC_PRECISION));
$default = explode ("::", $trecord->COLUMN_DEFAULT);
$database[trim($record->TABLE_NAME)][$tid]["default"] = $default[0];
$database[trim($record->TABLE_NAME)][$tid]["notnull"] = trim(strtoupper($trecord->IS_NULLABLE));
$database[trim($record->TABLE_NAME)][$tid]["pk"] = "";
}
}
$result = $database;
}
else
/*Microsoft SQL Server*/
if ($this->dbtype == "mssql")
{
$tables = $this->get_row ("sp_tables @table_type = \"'table'\"");
foreach ($tables as $id => $record)
{
$columns = $this->get_row ("sp_columns $record->TABLE_NAME");
foreach ($columns as $tid => $trecord)
{
$database[trim($record->TABLE_NAME)][$tid]["column"] = $tid;
$database[trim($record->TABLE_NAME)][$tid]["field"] = trim(strtoupper($trecord->COLUMN_NAME));
$database[trim($record->TABLE_NAME)][$tid]["description"] = trim(strtoupper($trecord->REMARKS));
$database[trim($record->TABLE_NAME)][$tid]["type"] = trim(strtoupper($trecord->TYPE_NAME));
$database[trim($record->TABLE_NAME)][$tid]["length"] = trim(strtoupper($trecord->LENGTH));
$database[trim($record->TABLE_NAME)][$tid]["precision"] = trim(strtoupper($trecord->PRECISION));
$database[trim($record->TABLE_NAME)][$tid]["default"] = "";
$database[trim($record->TABLE_NAME)][$tid]["notnull"] = trim(strtoupper($trecord->IS_NULLABLE));
$database[trim($record->TABLE_NAME)][$tid]["pk"] = "";
}
}
$result = $database;
}
else
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
/* Debugging for Close */
if ($result)
{
//check the data
}
else
{
trigger_error ("Cant extract metadata from $this->dbpath in ".__METHOD__." for ".$this->dbtype, E_USER_NOTICE);
}
return $result;
}
/*
END Get Database
*****************************************************************************/
/*****************************************************************************
BEGIN Get Field Info - Fetch basic field info
result = Array (
["alias"] = Alias
["name"] = Name
["type"] = Generic field type
["width"] = Column width
)
*/
function get_field_info ($sql="")
{
$result = 0;
if ($sql == "")
{
$result = $this->fieldinfo;
}
else
{
$this->get_row ($sql);
$result = $this->fieldinfo;
}
return $result;
}
/*
END Get Field Info
*****************************************************************************/
/*****************************************************************************
BEGIN Get Affected Rows
Get the number of rows changed or retrieved by last SQL
*/
function get_affected_rows ($sql="")
{
$result = 0;
if ($sql == "")
{
$result = $this->affectedrows;
}
else
{
$this->get_row ($sql);
$result = $this->affectedrows;
}
return $result;
}
/*
END Get Affected Rows
*****************************************************************************/
/*****************************************************************************
BEGIN Get Field Info By Name
Get a fields info by name
*/
function get_field_by_name ($fieldname="")
{
foreach ($this->fieldinfo as $id => $value)
{
if (strtoupper($fieldname) == strtoupper($value["name"]))
{
return $value;
break;
}
}
return null;
}
/*
END Get Field By Name
*****************************************************************************/
/*****************************************************************************
BEGIN Get Next ID
Get the next id on a table by using the MAX function and adding 1
*/
function get_next_id ($tablename="", $fieldname="")
{
$result = "";
$sql = "select max($fieldname) as \"nextid\" from $tablename";
$sql = $this->parsesql($sql);
$row = $this->get_value(0, $sql);
$result = $row->NEXTID + 1;
return $result;
}
/*
END Get Next Id
*****************************************************************************/
/*****************************************************************************
BEGIN Date to DB
This function will format the date as needed by the database
*/
function date_to_db ($invalue)
{
if ($invalue != "") //works only for firebird currently
{
$num = explode ("/", $invalue);
if ($num[2] == "") $num[2] = date("Y");
return $num[1]."/".$num[0]."/".$num[2];
}
else
{
return "null";
}
}
/*
END Date to DB
*****************************************************************************/
/*****************************************************************************
BEGIN Get Insert SQL
This function attempts to eliminate errors by creating the insert statements using prefixed input fields
If you have a form with inputs prefixed with "txt" for example it will chop off the txt and make an insert statement
Field names need to be in uppercase for better processing
<form>
<input type="text" name="txtNAME" value="Andre">
<input type="text" name="txtDATE" value="01/10/2010">
</form>
*/
function get_insert_sql ($fieldprefix="edt", //Field prefix as discussed above
$tablename="", //Name of the tablename
$primarykey="", //Field name of the primary key
$genkey=true, //Generate a new number using inbuilt get_next_id
$requestvar="", //Request variable to populate with new id for post processing
$passwordfields="", //Fields that may be crypted automatically
$datefields="",
$exec=false) //Fields that may be seen as date fields and converted accordingly
{
//Get the length of field prefix
$prefixlen = strlen ($fieldprefix);
//Start the insert statement
if ($genkey)
{
$newid = $this->get_next_id($tablename, $primarykey);
$_REQUEST[$requestvar] = $newid;
$sqlinsert = "insert into $tablename ($primarykey";
}
else
{
$newid = $_REQUEST[$fieldprefix.strtoupper($primarykey)];
$_REQUEST[$requestvar] = $newid;
$sqlinsert = "insert into $tablename (";
}
//Search all the fields on the form
foreach ($_REQUEST as $name => $value)
{
if (substr($name, 0, $prefixlen) == $fieldprefix)
{
$sqlinsert .= ", ".strtoupper (substr ($name, $prefixlen, strlen($name)-$prefixlen));
}
}
//Check if must add the generated primary key value
if ($genkey)
{
$sqlinsert .= ") values ($newid";
}
else
{
$sqlinsert .= ") values (";
}
foreach ($_REQUEST as $name => $value)
{
if (substr($name, 0, $prefixlen) == $fieldprefix)
{
//if ($value == "on") $value = 1;
$value = stripcslashes ($value);
$value = str_replace ("'", "''", $value);
$tempfields = explode(",", $passwordfields);
foreach ($tempfields as $id => $fieldname) //Look for password fields
{
if ($name == $fieldprefix.strtoupper($fieldname))
{
$value = crypt($value);
}
}
$tempfields = explode(",", $datefields);
foreach ($tempfields as $id => $fieldname) //Look for date fields and convert them
{
if ($name == $fieldprefix.strtoupper($fieldname))
{
$value = $this->date_to_db($value);
}
}
$sqlinsert .= ", '".$value."'";
}
}
$sqlinsert .= ")";
//Clean up the sql
$sqlinsert = str_replace ("(,", "(", $sqlinsert);
$sqlinsert = str_replace ("'null'", "null", $sqlinsert);
if (!$exec) //Do we run the procedure execution
{
return $sqlinsert;
}
else
{
//Run the insert statement and upload files while we are at it.
$this->exec ($sqlinsert);
$error = $this->get_error();
if ($_FILES)
{
foreach ($_FILES as $name => $value)
{
if ($value["tmp_name"] != "")
{
if (substr($name, 0, $prefixlen) == $fieldprefix)
{
//upload the file correctly into a blob field
$this->set_blob ($tablename, strtoupper (substr ($name, $prefixlen, strlen($name)-$prefixlen)),file_get_contents($value["tmp_name"]),$filter="$primarykey = '".$_REQUEST[$name]."'");
}
}
}
}
return $error;
}
}
/*
END Get Insert SQL
*****************************************************************************/
/*****************************************************************************
BEGIN Get Update SQL
This function attempts to eliminate errors by creating the update statements using prefixed input fields
If you have a form with inputs prefixed with "txt" for example it will chop off the txt and make an update statement
Field names need to be in uppercase for better processing
<form>
<input type="text" name="txtNAME" value="Andre">
<input type="text" name="txtDATE" value="01/10/2010">
</form>
*/
function get_update_sql ($fieldprefix="edt", //Field prefix as discussed above
$tablename="", //Name of the tablename
$primarykey="", //Field name of the primary key
$index="", //Index
$requestvar="", //Request variable to populate with new id for post processing
$passwordfields="", //Fields that may be crypted automatically
$datefields="",
$exec=false) //Fields that may be seen as date fields and converted accordingly
{
//Get the length of field prefix
$prefixlen = strlen ($fieldprefix);
$sqlupdate = "update $tablename set 0=0 ";
foreach ($_REQUEST as $name => $value)
{
if (substr($name, 0, $prefixlen) == $fieldprefix)
{
if ($value == "on") $value = 1;
$tempfields = explode(",", $passwordfields);
$dontupdate = false;
foreach ($tempfields as $id => $fieldname) //Look for password fields
{
if ($name == $fieldprefix.strtoupper($fieldname))
{
if ($value != "") //only if there is a password do we encrypt it
{
$value = crypt($value);
}
else
{
$dontupdate = true; //we must not update an empty password
}
}
}
$tempfields = explode(",", $datefields);
foreach ($tempfields as $id => $fieldname) //Look for date fields and convert them
{
if ($name == $fieldprefix.strtoupper($fieldname))
{
$value = $this->date_to_db($value);
}
}
$value = stripcslashes ($value);
$value = str_replace ("'", "''", $value);
if (!$dontupdate)
{
$sqlupdate .= ", ".strtoupper (substr ($name, $prefixlen, strlen($name)-$prefixlen)). " = '".$value."'";
}
}
}
$sqlupdate .= " where $primarykey = '".$index."'";
$sqlupdate = str_replace ("0=0 ,", "", $sqlupdate);
$sqlupdate = str_replace ("'null'", "null", $sqlupdate);
if (!$exec) //Do we run the procedure execution
{
return $sqlupdate;
}
else
{
//Run the insert statement and upload files while we are at it.
$this->exec ($sqlupdate);
$error = $this->get_error();
if ($_FILES)
{
foreach ($_FILES as $name => $value)
{
if ($value["tmp_name"] != "")
{
if (substr($name, 0, $prefixlen) == $fieldprefix)
{
//upload the file correctly into a blob field
$this->set_blob ($tablename, strtoupper (substr ($name, $prefixlen, strlen($name)-$prefixlen)),file_get_contents($value["tmp_name"]),$filter="$primarykey = '".$index."'");
}
}
}
}
return $error;
}
}
/*
END Get Update SQL
*****************************************************************************/
/*****************************************************************************
BEGIN Template
Template function to add your own things
*/
function template ()
{
$result = false;
if (!$this->dbh)
{
trigger_error ("No database handle, use connect first in ".__METHOD__." for ".$this->dbtype, E_USER_WARNING);
}
else
/*SQLite*/
if ($this->dbtype == "sqlite")
{
$result = true;
}
else
/*Firebird*/
if ($this->dbtype == "firebird")
{
$result = true;
}
else
/* Oracle */
if ($this->dbtype == "oracle")
{
$result = true;
}
else
/*MySQL*/
if ($this->dbtype == "mysql")
{
$result = true;
}
else
/*Postgres*/
if ($this->dbtype == "postgres")
{
$result = true;
}
else
/*Microsoft SQL Server*/
if ($this->dbtype == "mssql")
{
$result = true;
}
else
{
trigger_error ("Please implement ".__METHOD__." for ".$this->dbtype, E_USER_ERROR);
}
/* Debugging for Close */
if ($result)
{
$this->dbh = "";
}
else
{
trigger_error ("Cant close $this->dbpath in ".__METHOD__." for ".$this->dbtype, E_USER_NOTICE);
}
return $result;
}
/*
END Close
*****************************************************************************/
}
/*CDE PDF CLASS*/
if (file_exists("fpdf/fpdf.php"))
{
require_once ("fpdf/fpdf.php");
class CDEFPDF extends FPDF
{
public $reporttitle;
public $extraheader;
public $company;
public $fields;
public $params;
public $groupby;
public $oldgroup;
public $columns;
public $orientation;
public $font;
public $heading;
public $line;
public $columntotals;
public $globaltotals;
public $customheader;
public $DB; //database connection
public $records; //Database fields
public $csvfile;
public $delim;
//Draw Line
function drawline ()
{
$this->Ln();
$this->Cell(0, 0, "", 1, 0, "C");
$this->Ln();
}
//Create header
function createheader ()
{
$columns = $this->columns;
$left = 0;
$y = $this->GetY()+1;
$x = 10;
$imaxy = 0;
for ($i = 0; $i < count($columns); $i++)
{
if ($columns[$i]["align"] == "left")
{
$align = "L";
}
else
{
$align = "R";
}
$left = $columns[$i]["ratiowidth"];
if (strtolower($columns[$i]["alias"]) != strtolower($this->groupby))
{
$this->SetY($y);
$this->SetX($x);
$this->SetFont($this->font,'B', $this->heading);
$this->MultiCell($left, 3, ucwords(strtolower($columns[$i]["alias"])), 0, "$align");
if ($this->GetY() > $imaxy)
{
$imaxy = $this->GetY();
}
$this->csvfile .= '"'.ucwords(strtolower($columns[$i]["alias"])).'",';
$x += $left;
}
}
$this->csvfile = substr($this->csvfile, 0, -1)."\n";
$this->SetY($imaxy-2);
$this->SetX (10);
$this->drawline();
}
//Page header
function Header()
{
//Arial bold 14
$this->SetFont($this->font,'B',12);
//Move to the right
//Title
$this->Cell(0,5, $this->company,0,0,'L');
$this->SetFont($this->font,'BI',14);
$this->SetTextColor(200,200,200);
$this->Cell(0,5, "Confidential",0,0,'R');
if (function_exists ($this->extraheader))
{
$this->Ln(6);
$this->SetTextColor(0,0,0);
$this->SetFont($this->font,'B',10);
$params = Array($this);
call_user_func_array ($this->extraheader, $params);
}
else
{
$this->Ln(6);
$this->SetFont($this->font,'B',10);
$this->SetTextColor(0,0,0);
$this->Cell(0,5, $this->reporttitle,0,0,'L');
}
$this->drawline();
$this->createheader();
}
//Page footer
function Footer()
{
//Position at 1.5 cm from bottom
$this->SetY(-15);
$this->drawline ();
$this->SetFont($this->font,"",7);
//Page number
$this->Cell(0,5,'Page '.$this->PageNo().'/{nb}',0,0,'L');
$this->SetX(0);
$this->Cell(0,5,"developed by Spiceware Software (C)opyright CDE",0,0,'C');
$this->Cell(0,5,date("d/m/Y h:i:s"),0,0,'R');
$this->csvfile .= "Page ".$this->PageNo()." developed by Spiceware Software (C)opyright CDE\n";
}
//create group header
function creategroupby ($name)
{
if ($this->columntotals) $this->createfooter();
$this->SetFont($this->font,'B', $this->heading+2);
$this->Cell(0, 5, $name, 0, 0, "L");
$this->Ln();
$this->Cell(0, 0, "", 1, 0, "C");
$this->Ln();
$this->csvfile .= "\n";
$this->csvfile .= $name."\n";
$this->csvfile .= "\n";
}
//Create footer
//Function to total up the values;
function createfooter ($total=false, $formats="")
{
$columns = $this->columns;
$left = 0;
$this->Ln();
$this->Cell(0, 0, "", 1, 0, "C");
$this->Ln();
if ($total)
{
$this->SetY($this->GetY()+0.5);
$this->Cell(0, 0, "", 1, 0, "C");
$this->Ln();
$this->csvfile .= "\n";
}
for ($i = 0; $i < count($columns); $i++)
{
if ($columns[$i]["align"] == "left")
{
$align = "L";
}
else
{
$align = "R";
}
if ($total)
{
$value = $this->grandtotals[strtolower($columns[$i]["alias"])];
if ($columns[$i]["comptype"] != "")
{
$compute = str_replace ("columntotals", "grandtotals", strtolower($columns[$i]["comptype"]));
//echo '$value = '.$compute.";";
eval ('$value = '.$compute.";");
}
}
else
{
$value = $this->columntotals[strtolower($columns[$i]["alias"])];
if ($columns[$i]["comptype"] != "")
{
$compute = strtolower($columns[$i]["comptype"]);
//echo '$value = '.$compute.";";
eval ('$value = '.$compute.";");
}
}
if (strtolower($this->columns[$i]["type"]) == "currency")
{
$value = number_format($value, 2);
}
else
if (strtolower($this->columns[$i]["type"]) == "integer")
{
$value = number_format($value);
}
$left = $columns[$i]["ratiowidth"];
if ($this->columns[$i]["totaltype"] == "")
{
$value = "";
}
else
{
if ($formats[strtolower($this->columns[$i]["alias"])])
{
$value = $formats[strtolower($columns[$i]["alias"])]["prefix"].$value.$formats[strtolower($columns[$i]["alias"])]["suffix"];
}
}
if (strtolower($columns[$i]["alias"]) != strtolower($this->groupby))
{
$this->SetFont($this->font,'B', $this->heading);
$this->Cell($left, 5, $value, 0, 0, "$align");
$this->csvfile .= '"'.$value.'",';
}
}
$this->csvfile = substr($this->csvfile, 0, -1);
foreach ($this->columntotals as $c => $tot)
{
$this->grandtotals[$c] += $tot;
}
unset ($this->columntotals);
$this->Ln();
$this->csvfile .= "\n";
}
//Get Alias for field
function getalias ($field)
{
foreach ($this->columns as $id => $value)
{
if ($value["name"] == $field)
{
return $value["alias"];
exit;
}
}
return $field;
}
//Create the rows
function createrows ($records, $dontshow, $formats)
{
if ($dontshow != "")
{
$dontshow = str_replace ('$record', '$row', $dontshow);
}
foreach ($records as $id => $row)
{
$columns = $this->columns;
if ($dontshow != "")
{
eval(' $notshow = ('.$dontshow.');');
}
else
{
$notshow = false;
}
if ($notshow == false) //if we can show these records
{
if ($this->groupby)
{
$groupby = strtoupper ($this->getalias($this->groupby));
if ($row[$groupby] != $this->oldgroup)
{
$this->oldgroup = $row[$groupby];
$this->creategroupby ($this->oldgroup);
}
}
$left = 0;
$x = 10;
$y = $this->GetY()+2;
if ($this->orientation == "P")
{
if ($y > 270) $this->AddPage();
}
else
{
if ($y > 180) $this->AddPage();
}
$y = $this->GetY()+2;
for ($i = 0; $i < count($columns); $i++)
{
if ($columns[$i]["align"] == "left")
{
$align = "L";
}
else
{
$align = "R";
}
$left = $columns[$i]["ratiowidth"];
$value = $row[strtoupper($columns[$i]["alias"])];
if ($columns[$i]["type"] == "BLOB")
{
//check if blob is an image perhaps ???
$value = "[BLOB]";
}
$totaltype = strtolower($this->columns[$i]["totaltype"]);
if ($totaltype != "")
{
if (!$this->columntotals[strtolower($columns[$i]["alias"])])
{
$this->columntotals[strtolower($columns[$i]["alias"])] = 0;
}
if ($totaltype == "sum")
{
$this->columntotals[strtolower($columns[$i]["alias"])] += $value;
}
else
if ($totaltype == "avg")
{
if ($this->columntotals[strtolower($columns[$i]["alias"])] != "" && $this->columntotals[strtolower($columns[$i]["alias"])] != 0)
{
$this->columntotals[strtolower($columns[$i]["alias"])] += $value;
$this->columntotals[strtolower($columns[$i]["alias"])] = $this->columntotals[strtolower($columns[$i]["alias"])] / 2;
}
else
{
$this->columntotals[strtolower($columns[$i]["alias"])] = $value;
}
}
else
if ($totaltype == "count")
{
$this->columntotals[strtolower($columns[$i]["alias"])]++;
}
}
//Format
if (strtolower($this->columns[$i]["type"]) == "currency")
{
$value = number_format($value, 2);
}
else
if (strtolower($this->columns[$i]["type"]) == "integer")
{
$value = number_format($value);
}
//echo $columns[$i]["field"]." != ".$this->groupby;
if ($formats[strtolower($columns[$i]["alias"])])
{
$value = $formats[strtolower($columns[$i]["alias"])]["prefix"].$value.$formats[strtolower($columns[$i]["alias"])]["suffix"];
}
//echo strtolower($columns[$i]["alias"])." - ".strtolower($this->groupby);
if (strtolower($columns[$i]["alias"]) != strtolower($this->groupby))
{
// echo "$x, $y - $value <br>";
$this->SetY($y);
$this->SetX($x);
$value = strip_tags ($value);
$this->SetFont($this->font,'', $this->line);
$this->MultiCell($left, 2, $value, 0, "$align");
$this->csvfile .= '"'.strip_tags($value).'",';
$x += $left;
}
}
$this->csvfile = substr($this->csvfile, 0, -1);
$this->csvfile .= "\n";
}
}
$this->createfooter (false, $formats);
}
//Create the report
function execute ($outputpath="output/", $reporttitle="New Report", $companyname="New Company", $DB, $sql, $orientation="P", $pagesize="A4", $groupby="", $totalcolumns="", $compcolumns="", $extraheader="", $createcsv=true, $dontshow="", $formats="", $debug=false)
{
$this->font = "Arial";
$this->heading = 8;
$this->line = 7;
$this->groupby = $groupby;
$this->records = $DB->get_row ($sql, 2);
$this->reporttitle = $reporttitle;
$this->company = $companyname;
$this->SetMargins (10, 3, 4);
$this->orientation = $orientation;
$this->extraheader = $extraheader;
$this->columns = $DB->fieldinfo;
$this->csvfile = "";
//Make lowercase computed columns
foreach ($compcolums as $id => $value)
{
$compcolumns[strtolower($id)] = strtolower($value);
}
if ($this->records)
{
//Create the format functionality fieldname,prefix,suffix|fieldname,prefix,suffix|.....
$newformat = array();
$formats = explode ("|", strtolower($formats));
foreach ($formats as $id => $value)
{
$value = explode(",", $value);
$newformat[trim($value[0])]["prefix"] = $value[1];
$newformat[trim($value[0])]["suffix"] = $value[2];
}
$formats = $newformat;
$debugout = "";
if ($debug)
{
$debugout .= print_r ($this->columns, 1);
}
if ($orientation == "P")
{
if ($pagesize == "A3")
{
$pagewidth = 295;
}
else //Assume A4 to be default
{
$pagewidth = 196;
}
}
else
{
if ($pagesize == "A3")
{
$pagewidth = 406;
}
else //Assume A4 to be default
{
$pagewidth = 284;
}
}
$total = 0;
for ($i = 0; $i < count($this->columns); $i++)
{
if (strtolower($this->columns[$i]["name"]) != strtolower($groupby))
{
if ($this->columns[$i]["htmllength"] == "") $this->columns[$i]["htmllength"] = 100;
$total = $total + $this->columns[$i]["htmllength"];
}
}
if ($debug)
{
$debugout .= "<pre> $total </pre>";
}
for ($i = 0; $i < count($this->columns); $i++)
{
if ($this->columns[$i]["htmllength"] == "") $this->columns[$i]["htmllength"] = 120;
$this->columns[$i]["ratiowidth"] = ($this->columns[$i]["htmllength"] / $total) * $pagewidth;
//see if there must be totals
foreach ($totalcolumns as $type => $value)
{
foreach ($value as $id => $fieldname)
{
if ($debug)
{
$debugout .= strtolower($fieldname)." === ".strtolower($this->columns[$i]["alias"])."<br>";
}
echo "<!-- ";
echo strtolower($fieldname)." === ".strtolower($this->columns[$i]["alias"])."<br>";
echo "-->";
if (strtolower($fieldname) == strtolower($this->columns[$i]["alias"]))
{
$this->columns[$i]["totaltype"] = $type;
}
}
}
//see if there must be calculations done
foreach ($compcolumns as $fieldname => $value)
{
if ($debug)
{
$debugout .= strtolower($fieldname)." === ".strtolower($this->columns[$i]["alias"])." - $value <br>";
}
if (strtolower($fieldname) == strtolower($this->columns[$i]["alias"]))
{
$this->columns[$i]["comptype"] = $value;
}
}
}
if ($debug)
{
$debugout .= print_r ($this->columns, 1);
}
//echo "<!--";
//print_r ($this->columns);
//echo "-->";
$this->AliasNbPages();
//$this->createheader();
$this->AddPage();
if ($debug)
{
$debugout .= print_r ($this->records, 1);
}
//print_r ($this->records);
$this->createrows ($this->records, $dontshow, $formats);
$this->createfooter(true, $formats);
$filename = rand(100000,999999);
$csvfilename = $filename.".csv";
$filename = $filename.".pdf";
$this->Output($outputpath.$filename, "F");
$this->Close();
file_put_contents ($outputpath.$csvfilename, $this->csvfile);
$result["filename"] = $outputpath.$filename;
$result["csvfile"] = $outputpath.$csvfilename;
if ($debug)
{
$result["debug"] = $debugout;
}
}
else
{
$result["filename"] = "";
$result["csvfile"] = "";
$result["debug"] = "SQL Error or No Data";
}
return $result;
}
}
}
?>