Viewing file:  dal.php (19.49 KB) -rw-r--r--
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
$dal_info = array();
/**
* User API function
* @param String sql
*/
function UsersTableName()
{
global $cman;
$connection = $cman->getForLogin();
return $connection->addTableWrappers("");
}
/**
* User API function
* It uses the default db connection
* @param String dalSQL
*/
function CustomQuery($dalSQL)
{
$connection = getDefaultConnection();
$result = $connection->query( $dalSQL );
if($result)
return $result;
}
/**
* User API function
* It uses the default db connection
* @param String sql
*/
function DBLookup($sql)
{
$connection = getDefaultConnection();
$data = $connection->query( $sql )->fetchAssoc();
if( $data )
return reset($data);
return null;
}
/**
* Data Access Layer.
*/
class tDAL
{
var $tblitce_data_at_itce_co_in__aboutus;
var $tblitce_data_at_itce_co_in__admission;
var $tblitce_data_at_itce_co_in__answersheet;
var $tblitce_data_at_itce_co_in__batch;
var $tblitce_data_at_itce_co_in__books;
var $tblitce_data_at_itce_co_in__branch;
var $tblitce_data_at_itce_co_in__certificate;
var $tblitce_data_at_itce_co_in__complain;
var $tblitce_data_at_itce_co_in__course_details;
var $tblitce_data_at_itce_co_in__courses;
var $tblitce_data_at_itce_co_in__fees;
var $tblitce_data_at_itce_co_in__gallery;
var $tblitce_data_at_itce_co_in__marks;
var $tblitce_data_at_itce_co_in__monthly_fees;
var $tblitce_data_at_itce_co_in__news;
var $tblitce_data_at_itce_co_in__PERMISSION_EXAM;
var $tblitce_data_at_itce_co_in__question_set_paper;
var $tblitce_data_at_itce_co_in__slider_photo;
var $tblitce_data_at_itce_co_in__studentlogin;
var $tblitce_data_at_itce_co_in__teacher;
var $tblitce_data_at_itce_co_in__teacher_registration;
var $tblitce_data_at_itce_co_in__test_result;
var $tblitce_data_at_itce_co_in__videolist;
var $tblitce_data_at_itce_co_in__website_theme;
var $lstTables;
var $Table = array();
function FillTablesList()
{
if($this->lstTables)
return;
$this->lstTables[] = array("name" => "aboutus", "varname" => "itce_data_at_itce_co_in__aboutus", "altvarname" => "aboutus", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "admission", "varname" => "itce_data_at_itce_co_in__admission", "altvarname" => "admission", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "answersheet", "varname" => "itce_data_at_itce_co_in__answersheet", "altvarname" => "answersheet", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "batch", "varname" => "itce_data_at_itce_co_in__batch", "altvarname" => "batch", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "books", "varname" => "itce_data_at_itce_co_in__books", "altvarname" => "books", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "branch", "varname" => "itce_data_at_itce_co_in__branch", "altvarname" => "branch", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "certificate", "varname" => "itce_data_at_itce_co_in__certificate", "altvarname" => "certificate", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "complain", "varname" => "itce_data_at_itce_co_in__complain", "altvarname" => "complain", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "course_details", "varname" => "itce_data_at_itce_co_in__course_details", "altvarname" => "course_details", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "courses", "varname" => "itce_data_at_itce_co_in__courses", "altvarname" => "courses", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "fees", "varname" => "itce_data_at_itce_co_in__fees", "altvarname" => "fees", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "gallery", "varname" => "itce_data_at_itce_co_in__gallery", "altvarname" => "gallery", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "marks", "varname" => "itce_data_at_itce_co_in__marks", "altvarname" => "marks", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "monthly_fees", "varname" => "itce_data_at_itce_co_in__monthly_fees", "altvarname" => "monthly_fees", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "news", "varname" => "itce_data_at_itce_co_in__news", "altvarname" => "news", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "PERMISSION_EXAM", "varname" => "itce_data_at_itce_co_in__PERMISSION_EXAM", "altvarname" => "PERMISSION_EXAM", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "question_set_paper", "varname" => "itce_data_at_itce_co_in__question_set_paper", "altvarname" => "question_set_paper", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "slider_photo", "varname" => "itce_data_at_itce_co_in__slider_photo", "altvarname" => "slider_photo", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "studentlogin", "varname" => "itce_data_at_itce_co_in__studentlogin", "altvarname" => "studentlogin", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "teacher", "varname" => "itce_data_at_itce_co_in__teacher", "altvarname" => "teacher", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "teacher_registration", "varname" => "itce_data_at_itce_co_in__teacher_registration", "altvarname" => "teacher_registration", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "test_result", "varname" => "itce_data_at_itce_co_in__test_result", "altvarname" => "test_result", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "videolist", "varname" => "itce_data_at_itce_co_in__videolist", "altvarname" => "videolist", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
$this->lstTables[] = array("name" => "website_theme", "varname" => "itce_data_at_itce_co_in__website_theme", "altvarname" => "website_theme", "connId" => "itce_data_at_itce_co_in", "schema" => "", "connName" => "sql_itce_co_in at itce.co.in");
}
/**
* Returns table object by table name.
* @intellisense
*/
function & Table($strTable, $schema = "", $connName = "")
{
$this->FillTablesList();
foreach($this->lstTables as $tbl)
{
if(strtoupper($strTable)==strtoupper($tbl["name"]) &&
( $schema == "" || strtoupper($schema) == strtoupper($tbl["schema"]) ) &&
($connName == "" || strtoupper($connName) == strtoupper($tbl["connName"]) ) )
{
$this->CreateClass($tbl);
return $this->{"tbl".$tbl["varname"]};
}
}
// check table names without dbo. and other prefixes
foreach($this->lstTables as $tbl)
{
if(strtoupper(cutprefix($strTable))==strtoupper(cutprefix($tbl["name"])))
{
$this->CreateClass($tbl);
return $this->{"tbl".$tbl["varname"]};
}
}
$dummy=null;
return $dummy;
}
function CreateClass(&$tbl)
{
if($this->{"tbl".$tbl["varname"]})
return;
// load table info
global $dal_info;
include(getabspath("include/dal/".($tbl["varname"]).".php"));
// create class and object
$classname="class_".$tbl["varname"];
$str = "class ".$classname." extends tDALTable {";
foreach($dal_info[$tbl["varname"]] as $fld)
{
$str.=' var $'.$fld["varname"].'; ';
}
$tableName = $tbl["name"];
if( $tbl["schema"] )
$tableName = $tbl["schema"] . "." . $tbl["name"];
$str.=' function __construct()
{
$this->m_TableName = \''.escapesq( $tableName ).'\';
$this->infoKey = \'' . $tbl["varname"] . '\';
$this->setConnection(\''. $tbl["connId"] .'\');
}
};';
eval($str);
$this->{"tbl".$tbl["varname"]} = new $classname;
$this->{$tbl["altvarname"]} = $this->{"tbl".$tbl["varname"]};
$this->Table[$tbl["name"]]=&$this->{"tbl".$tbl["varname"]};
}
/**
* Returns list of table names.
* @intellisense
*/
function GetTablesList()
{
$this->FillTablesList();
$res=array();
foreach($this->lstTables as $tbl)
$res[]=$tbl["name"];
return $res;
}
/**
* Get list of table fields by table name.
* @intellisense
*/
function GetFieldsList($table)
{
$tbl = $this->Table($table);
return $tbl->GetFieldsList();
}
/**
* Get field type by table name and field name.
* @intellisense
*/
function GetFieldType($table,$field)
{
$tbl = $this->Table($table);
return $tbl->GetFieldType($field);
}
/**
* Get table key fields by table name.
* @intellisense
*/
function GetDBTableKeys($table)
{
$tbl = $this->Table($table);
return $tbl->GetDBTableKeys();
}
}
$dal = new tDAL;
/**
* Data Access Layer table class.
*/
class tDALTable
{
var $m_TableName;
var $infoKey;
var $Param = array();
var $Value = array();
/**
* @type Connection
*/
var $_connection;
/**
* Set the connection property
* @param String connId
*/
function setConnection( $connId )
{
global $cman;
$this->_connection = $cman->byId( $connId );
}
/**
* Get table key fields.
* @intellisense
*/
function GetDBTableKeys()
{
global $dal_info;
if( !array_key_exists($this->infoKey, $dal_info) || !is_array($dal_info[ $this->infoKey ]) )
return array();
$ret = array();
foreach($dal_info[ $this->infoKey ] as $fname=>$f)
{
if( @$f["key"] )
$ret[] = $fname;
}
return $ret;
}
/**
* Get list of table fields.
* @intellisense
*/
function GetFieldsList()
{
global $dal_info;
return array_keys( $dal_info[ $this->infoKey ] );
}
/**
* Get field type.
* @intellisense
*/
function GetFieldType($field)
{
global $dal_info;
if( !array_key_exists( $field, $dal_info[ $this->infoKey ]) )
return 200;
return $dal_info[ $this->infoKey ][ $field ]["type"];
}
/**
*
*/
function PrepareValue($value, $type)
{
if( $this->_connection->dbType == nDATABASE_Oracle || $this->_connection->dbType == nDATABASE_DB2 || $this->_connection->dbType == nDATABASE_Informix )
{
if( IsBinaryType($type) )
{
if( $this->_connection->dbType == nDATABASE_Oracle )
return "EMPTY_BLOB()";
return "?";
}
if( $this->_connection->dbType == nDATABASE_Informix && IsTextType($type) )
return "?";
}
if( IsDateFieldType($type) )
{
if( !$value )
return "null";
else
$this->_connection->addDateQuotes( $value );
}
if( NeedQuotes($type) )
return $this->_connection->prepareString( $value );
return 0 + $value;
}
/**
* Get table name.
* @intellisense
*/
function TableName()
{
return $this->_connection->addTableWrappers( $this->m_TableName );
}
/**
* @param Array blobs
* @param String dalSQL
* @param Array tableinfo
*/
protected function Execute_Query($blobs, $dalSQL, $tableinfo)
{
$blobTypes = array();
if( $this->_connection->dbType == nDATABASE_Informix )
{
foreach( $blobs as $fname => $fvalue )
{
$blobTypes[ $fname ] = $tableinfo[ $fname ]["type"];
}
}
$this->_connection->execWithBlobProcessing( $dalSQL, $blobs, $blobTypes );
}
/**
* Add new record to the table.
* @intellisense
*/
function Add()
{
global $dal_info;
$insertFields = "";
$insertValues = "";
$tableinfo = &$dal_info[ $this->infoKey ];
$blobs = array();
// prepare parameters
foreach($tableinfo as $fieldname => $fld)
{
if( isset($this->{$fld['varname']}) )
{
$this->Value[ $fieldname ] = $this->{$fld['varname']};
}
foreach($this->Value as $field => $value)
{
if( strtoupper($field) != strtoupper($fieldname) )
continue;
$insertFields.= $this->_connection->addFieldWrappers( $fieldname ).",";
$insertValues.= $this->PrepareValue($value,$fld["type"]) . ",";
if( $this->_connection->dbType == nDATABASE_Oracle || $this->_connection->dbType == nDATABASE_DB2 || $this->_connection->dbType == nDATABASE_Informix )
{
if( IsBinaryType( $fld["type"] ) )
$blobs[ $fieldname ] = $value;
if( $this->_connection->dbType == nDATABASE_Informix && IsTextType( $fld["type"] ) )
$blobs[ $fieldname ] = $value;
}
break;
}
}
// prepare and exec SQL
if( $insertFields != "" && $insertValues != "" )
{
$insertFields = substr($insertFields, 0, -1);
$insertValues = substr($insertValues, 0, -1);
$dalSQL = "insert into ".$this->_connection->addTableWrappers( $this->m_TableName )." (".$insertFields.") values (".$insertValues.")";
$this->Execute_Query($blobs, $dalSQL, $tableinfo);
}
// cleanup
$this->Reset();
}
/**
* Query all records from the table.
* @intellisense
*/
function QueryAll()
{
$dalSQL = "select * from ".$this->_connection->addTableWrappers( $this->m_TableName );
return $this->_connection->query( $dalSQL );
}
/**
* Do a custom query on the table.
* @intellisense
*/
function Query($swhere = "", $orderby = "")
{
if ($swhere)
$swhere = " where ".$swhere;
if ($orderby)
$orderby = " order by ".$orderby;
$dalSQL = "select * from ".$this->_connection->addTableWrappers( $this->m_TableName ).$swhere.$orderby;
return $this->_connection->query( $dalSQL );
}
/**
* Delete a record from the table.
* @intellisense
*/
function Delete()
{
global $dal_info;
$deleteFields = "";
$tableinfo = &$dal_info[ $this->infoKey ];
// prepare parameters
foreach($tableinfo as $fieldname => $fld)
{
if( isset($this->{$fld['varname']}) )
{
$this->Param[ $fieldname ] = $this->{$fld['varname']};
}
foreach($this->Param as $field => $value)
{
if( strtoupper($field) != strtoupper($fieldname) )
continue;
$deleteFields.= $this->_connection->addFieldWrappers( $fieldname )."=". $this->PrepareValue($value, $fld["type"]) . " and ";
break;
}
}
// do delete
if ($deleteFields)
{
$deleteFields = substr($deleteFields, 0, -5);
$dalSQL = "delete from ".$this->_connection->addTableWrappers( $this->m_TableName )." where ".$deleteFields;
$this->_connection->exec( $dalSQL );
}
// cleanup
$this->Reset();
}
/**
* Reset table object.
* @intellisense
*/
function Reset()
{
global $dal_info;
$this->Value = array();
$this->Param = array();
$tableinfo = &$dal_info[ $this->infoKey ];
// prepare parameters
foreach($tableinfo as $fieldname => $fld)
{
unset($this->{$fld["varname"]});
}
}
/**
* Update record in the table.
* @intellisense
*/
function Update()
{
global $dal_info;
$tableinfo = &$dal_info[ $this->infoKey ];
$updateParam = "";
$updateValue = "";
$blobs = array();
foreach($tableinfo as $fieldname => $fld)
{
$command = 'if(isset($this->'.$fld['varname'].')) { ';
if( $fld["key"] )
$command.= '$this->Param[\''.escapesq($fieldname).'\'] = $this->'.$fld['varname'].';';
else
$command.= '$this->Value[\''.escapesq($fieldname).'\'] = $this->'.$fld['varname'].';';
$command.= ' }';
eval($command);
if( !$fld["key"] && !array_key_exists( strtoupper($fieldname), array_change_key_case($this->Param, CASE_UPPER) ) )
{
foreach($this->Value as $field => $value)
{
if( strtoupper($field) != strtoupper($fieldname) )
continue;
$updateValue.= $this->_connection->addFieldWrappers( $fieldname )."=".$this->PrepareValue($value, $fld["type"]) . ", ";
if( $this->_connection->dbType == nDATABASE_Oracle || $this->_connection->dbType == nDATABASE_DB2 || $this->_connection->dbType == nDATABASE_Informix )
{
if( IsBinaryType( $fld["type"] ) )
$blobs[ $fieldname ] = $value;
if( $this->_connection->dbType == nDATABASE_Informix && IsTextType( $fld["type"] ) )
$blobs[ $fieldname ] = $value;
}
break;
}
}
else
{
foreach($this->Param as $field=>$value)
{
if( strtoupper($field) != strtoupper($fieldname) )
continue;
$updateParam.= $this->_connection->addFieldWrappers( $fieldname )."=".$this->PrepareValue($value, $fld["type"]) . " and ";
break;
}
}
}
// construct SQL and do update
if ($updateParam)
$updateParam = substr($updateParam, 0, -5);
if ($updateValue)
$updateValue = substr($updateValue, 0, -2);
if ($updateValue && $updateParam)
{
$dalSQL = "update ".$this->_connection->addTableWrappers( $this->m_TableName )." set ".$updateValue." where ".$updateParam;
$this->Execute_Query($blobs, $dalSQL, $tableinfo);
}
// cleanup
$this->Reset();
}
/**
* Select one or more records matching the condition
*/
function FetchByID()
{
global $dal_info;
$tableinfo = &$dal_info[ $this->infoKey ];
$dal_where = "";
foreach($tableinfo as $fieldname => $fld)
{
$command = 'if(isset($this->'.$fld['varname'].')) { ';
$command.= '$this->Value[\''.escapesq($fieldname).'\'] = $this->'.$fld['varname'].';';
$command.= ' }';
eval($command);
foreach($this->Param as $field => $value)
{
if( strtoupper($field) != strtoupper($fieldname) )
continue;
$dal_where.= $this->_connection->addFieldWrappers( $fieldname )."=".$this->PrepareValue($value, $fld["type"]) . " and ";
break;
}
}
// cleanup
$this->Reset();
// construct and run SQL
if ($dal_where)
$dal_where = " where ".substr($dal_where, 0, -5);
$dalSQL = "select * from ".$this->_connection->addTableWrappers( $this->m_TableName ).$dal_where;
return $this->_connection->query( $dalSQL );
}
}
function cutprefix($table)
{
$pos = strpos($table,".");
if( $pos === false )
return $table;
return substr($table, $pos + 1);
}
function escapesq($str)
{
return str_replace(array("\\","'"),array("\\\\","\\'"),$str);
}
?>
|