• Main Page
  • Related Pages
  • Namespaces
  • Data Structures
  • Files
  • File List

E:/E/GEAMP/www/openbiz/openbiz/bin/data/BizDataSql.php

00001 <?PHP
00024 class BizDataSql
00025 {
00026     protected $_tableColumns = null;
00027     protected $_tableJoins = null;
00028     protected $_joinAliasList = array();
00029     protected $_tableAliasList = array();
00030     protected $_sqlWhere = null;
00031     protected $_orderBy = null;
00032     protected $_otherSQL = null;
00033     protected $_aliasIndex = 0;
00034     protected $_mainTable;
00035 
00036     public function __construct()
00037     {
00038     }
00039 
00046     public function addMainTable($mainTable)
00047     {
00048         $this->_mainTable = "$mainTable";
00049         $this->_tableJoins = " `$mainTable` T0 ";
00050     }
00051 
00065     public function addJoinTable($tableJoin)
00066     {
00067         $table = $tableJoin->m_Table;
00068         $joinType = $tableJoin->m_JoinType;
00069         $column = $tableJoin->m_Column;
00070         $joinRef = $tableJoin->m_JoinRef;
00071         $columnRef = $tableJoin->m_ColumnRef;
00072 
00073         $alias = "T".(count($this->_joinAliasList)+1);   // start with T1, T2
00074         $this->_joinAliasList[$tableJoin->m_Name] = $alias;
00075         $this->_tableAliasList[$table] = $alias;
00076         $aliasRef = $this->getJoinAlias($joinRef);
00077         $this->_tableJoins .= " $joinType `$table` $alias ON $alias.$column = $aliasRef.$columnRef ";
00078     }
00079 
00087     public function addTableColumn($join, $column, $alias=null)
00088     {
00089         $tcol = $this->getTableColumn($join, $column);
00090         if ($alias) $tcol .= " AS ".$alias;
00091         if (!$this->_tableColumns)
00092             $this->_tableColumns = $tcol;
00093         else
00094             $this->_tableColumns .= ", ".$tcol;
00095     }
00096 
00105     public function addSqlExpression($sqlExpr, $alias=null)
00106     {
00107         if ($alias)
00108             $sqlExpr .= ' AS '.$alias;
00109         if (!$this->_tableColumns)
00110             $this->_tableColumns = $sqlExpr;
00111         else
00112             $this->_tableColumns .= ", ".$sqlExpr;
00113     }
00114 
00121     public function getJoinAlias($join)
00122     {
00123         if (!$join) // main table, no join
00124             return "T0";
00125         else
00126             return $this->_joinAliasList[$join];
00127     }
00128 
00136     public function getTableColumn($join, $col)
00137     {
00138         // check the function format on $col
00139         $alias = $this->getJoinAlias($join);
00140         return "$alias.`$col`";
00141     }
00142 
00148     public function resetSQL()
00149     {
00150         $this->_sqlWhere = null;
00151         $this->_orderBy = null;
00152         $this->_otherSQL = null;
00153     }
00154 
00161     public function addSqlWhere($sqlWhere)
00162     {
00163         if ($sqlWhere == null)
00164             return;
00165         if ($this->_sqlWhere == null)
00166         {
00167             $this->_sqlWhere = $sqlWhere;
00168         }
00169         elseif (strpos($this->_sqlWhere, $sqlWhere) === false)
00170         {
00171             $this->_sqlWhere .= " AND " . $sqlWhere;
00172         }
00173     }
00174 
00181     public function addOrderBy($orderBy)
00182     {
00183         if ($orderBy == null)
00184             return;
00185         if ($this->_orderBy == null)
00186         {
00187             $this->_orderBy = $orderBy;
00188         }
00189         elseif (strpos($this->_orderBy, $orderBy) === false)
00190         {
00191             $this->_orderBy .= " AND " . $orderBy;
00192         }
00193     }
00194 
00201     public function addOtherSQL($otherSQL)
00202     {
00203         if ($otherSQL == null)
00204             return;
00205         if ($this->_otherSQL == null)
00206         {
00207             $this->_otherSQL = $otherSQL;
00208         }
00209         elseif (strpos($this->_otherSQL, $otherSQL) === false)
00210         {
00211             $this->_otherSQL .= " AND " . $otherSQL;
00212         }
00213     }
00214 
00221     public function addAssociation($assoc)
00222     {
00223         $where = "";
00224         if ($assoc["Relationship"] == "1-M" || $assoc["Relationship"] == "M-1" || $assoc["Relationship"] == "1-1")
00225         {
00226             // assc table should same as maintable
00227             if ($assoc["Table"] != $this->_mainTable) return;
00228             // add table to join table
00229             $mytable_col = $this->getTableColumn(null, $assoc["Column"]);
00230             // construct table.column = 'field value'
00231             $where = $mytable_col." = '".$assoc["FieldRefVal"]."'";
00232             
00233             $mytable_col2 = $this->getTableColumn(null, $assoc["Column2"]);
00234             if($assoc["Column2"]){
00235               $where .= " AND ".$mytable_col2." = '".$assoc["FieldRefVal2"]."'";
00236             }
00237             $mytable_cond_col = $this->getTableColumn(null, $assoc["CondColumn"]);
00238             if($assoc["CondColumn"]){
00239               $where .= " AND $mytable_cond_col ='".$assoc["CondValue"]."' ";
00240             }
00241               if($assoc["Condition"]){
00242               $where .= " AND ".$assoc["Condition"]." ";
00243             }
00244         }
00245         elseif ($assoc["Relationship"] == "M-M" || $assoc["Relationship"] == "Self-Self")
00246         {
00247             // ... INNER JOIN xtable TX ON TX.column2 = T0.column
00248             // WHERE ... Tx.column1 = 'PrtTableColumnValue'
00249             $mytable_col = $this->getTableColumn(null, $assoc["Column"]);   // this table's alias.column
00250             $xtable = $assoc["XTable"];    // xtable's name
00251             $column1 = $assoc["XColumn1"]; // xtable column1
00252             $column2 = $assoc["XColumn2"]; // xtable column2
00253             $xalias = "TX";
00254             if (isset($this->_tableAliasList[$xtable]))
00255                 $xalias = $this->_tableAliasList[$xtable];
00256 
00257             // add a table join for the xtable if such join of the table is not there before (note: may report error if has same join).
00258             //if (strpos($this->m_TableJoins, "JOIN $xtable") === false)
00259             if (!isset($this->_tableAliasList[$xtable]))
00260             {
00261                 $this->_tableJoins .= " INNER JOIN `$xtable` $xalias ON $xalias.$column2 = $mytable_col ";
00262                 $this->_tableAliasList[$xtable] = $xalias;
00263             }
00264             
00265             // add a new where condition
00266             if($assoc["Relationship"] == "Self-Self")
00267             {   
00268               $columnId = $this->getTableColumn("", $assoc["ParentRecordIdColumn"]);              
00269               $where = "($xalias.$column1 = '".$assoc["FieldRefVal"]."' OR $xalias.$column2 = '".$assoc["FieldRefVal"]."' ) AND $columnId != '".$assoc["FieldRefVal"]."'";
00270               $this->_tableJoins .=  " OR $xalias.$column1 = $mytable_col ";
00271             }else{
00272               $where = "$xalias.$column1 = '".$assoc["FieldRefVal"]."'";
00273             }
00274         }
00275 
00276         if (strlen($where) > 1)
00277             $this->addSqlWhere($where);
00278     }
00279 
00285     public function getSqlStatement()
00286     {
00287         $ret = "SELECT " . $this->_tableColumns;
00288         $ret .= " FROM " . $this->_tableJoins;
00289 
00290         if ($this->_sqlWhere != null)
00291         {
00292             $ret .= " WHERE " . $this->_sqlWhere;
00293         }
00294         /*
00295         if ($this->m_OrderBy != null)
00296         {
00297             $ret .= " ORDER BY " . $this->m_OrderBy;
00298        }
00299         */
00300         if ($this->_otherSQL != null)
00301         {
00302             $ret .= " " . $this->_otherSQL;
00303         }
00304         if ($this->_orderBy != null)
00305         {
00306             $ret .= " ORDER BY " . $this->_orderBy;
00307         }
00308         return $ret;
00309     }
00310 }
00311 ?>

Generated on Thu Apr 19 2012 17:09:13 for openbiz by  doxygen 1.7.2