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);
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)
00124 return "T0";
00125 else
00126 return $this->_joinAliasList[$join];
00127 }
00128
00136 public function getTableColumn($join, $col)
00137 {
00138
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
00227 if ($assoc["Table"] != $this->_mainTable) return;
00228
00229 $mytable_col = $this->getTableColumn(null, $assoc["Column"]);
00230
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
00248
00249 $mytable_col = $this->getTableColumn(null, $assoc["Column"]);
00250 $xtable = $assoc["XTable"];
00251 $column1 = $assoc["XColumn1"];
00252 $column2 = $assoc["XColumn2"];
00253 $xalias = "TX";
00254 if (isset($this->_tableAliasList[$xtable]))
00255 $xalias = $this->_tableAliasList[$xtable];
00256
00257
00258
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
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
00296
00297
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 ?>