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

E:/E/GEAMP/www/openbiz/openbiz/bin/data/private/BizDataObj_SQLHelper.php

00001 <?php
00025 class BizDataObj_SQLHelper
00026 {
00031     private $_dataSqlObj = null;
00032 
00037     private static $_instance = null;
00038 
00044     public static function instance()
00045     {
00046         if (self::$_instance == null)
00047             self::$_instance = new BizDataObj_SQLHelper();
00048         return self::$_instance;
00049     }
00050 
00056     protected function getDataSqlObj()
00057     {
00058         if (!$this->_dataSqlObj)
00059             $this->_dataSqlObj = new BizDataSql();
00060         return $this->_dataSqlObj;
00061     }
00062 
00068     protected function getNewDataSqlObj()
00069     {
00070         $this->_dataSqlObj = null;
00071         $this->_dataSqlObj = new BizDataSql();
00072         return $this->_dataSqlObj;
00073     }
00074 
00081     public function buildQuerySQL($dataObj)
00082     {
00083         // TODO: if no searchrule or sortrule change ...
00084         // build the SQL statement based on the fields and search rule
00085         $dataSqlObj = $this->getNewDataSqlObj();
00086         // add table
00087         $dataSqlObj->addMainTable($dataObj->m_MainTable);
00088         // add join table
00089         if ($dataObj->m_TableJoins)
00090         {
00091             foreach($dataObj->m_TableJoins as $tableJoin)
00092             {
00093                 $tbl_col = $dataSqlObj->addJoinTable($tableJoin);
00094             }
00095         }
00096         // add columns
00097         foreach($dataObj->m_BizRecord as $bizFld)
00098         {
00099             if ($bizFld->m_IgnoreInQuery) // field to be ignore in query - save memory
00100                 continue;
00101             if ($bizFld->m_Column && $bizFld->m_Type == "Blob")   // ignore blob column
00102                 continue;
00103             if ($bizFld->m_Column && !$bizFld->m_SqlExpression && (strpos($bizFld->m_Column,',') == 0))
00104                 $dataSqlObj->addTableColumn($bizFld->m_Join, $bizFld->m_Column, $bizFld->m_Alias);
00105             if ($bizFld->m_SqlExpression)
00106             {
00107                 $dataSqlObj->addSqlExpression($this->_convertSqlExpression($dataObj, $bizFld->m_SqlExpression),$bizFld->m_Alias);
00108             }
00109         }
00110 
00111         $dataSqlObj->resetSQL();
00112 
00113         // append DataPerm in the WHERE clause
00114         if($dataObj->m_DataPermControl=='Y')
00115         {
00116                $svcObj = BizSystem::GetService(DATAPERM_SERVICE);
00117                $hasOwnerField = $this->_hasOwnerField($dataObj);
00118                $dataPermSQLRule = $svcObj->buildSqlRule($dataObj,'select',$hasOwnerField);
00119                $sqlSearchRule = $this->_ruleToSql($dataObj, $dataPermSQLRule);
00120                $dataSqlObj->addSqlWhere($sqlSearchRule);
00121         }
00122         
00123         // append SearchRule in the WHERE clause
00124         $sqlSearchRule = $this->_ruleToSql($dataObj, $dataObj->m_SearchRule);
00125         $dataSqlObj->addSqlWhere($sqlSearchRule);
00126 
00127         // append SearchRule in the ORDER BY clause
00128         $sqlSortRule = $this->_ruleToSql($dataObj, $dataObj->m_SortRule);
00129         $dataSqlObj->addOrderBy($sqlSortRule);
00130 
00131         // append SearchRule in the other SQL clause
00132         $sqlOtherSQLRule = $this->_ruleToSql($dataObj, $dataObj->m_OtherSQLRule);
00133         $dataSqlObj->addOtherSQL($sqlOtherSQLRule);
00134 
00135         // append SearchRule in the AccessRule clause
00136         $sqlAccessSQLRule = $this->_ruleToSql($dataObj, $dataObj->m_AccessRule);
00137         $dataSqlObj->addSqlWhere($sqlAccessSQLRule);
00138 
00139         // add association to SQL
00140         if ($dataObj->m_Association["AsscObjName"] != ""
00141                 && $dataObj->m_Association["FieldRefVal"] == "")
00142         {
00143             $asscObj = BizSystem::getObject($dataObj->m_Association["AsscObjName"]);
00144             $dataObj->m_Association["FieldRefVal"] = $asscObj->getFieldValue($dataObj->m_Association["FieldRef"]);
00145         }
00146         
00147        if ($dataObj->m_Association["AsscObjName"] != ""
00148                 && $dataObj->m_Association["FieldRefVal2"] == "")
00149         {
00150             $asscObj = BizSystem::getObject($dataObj->m_Association["AsscObjName"]);
00151             $dataObj->m_Association["FieldRefVal2"] = $asscObj->getFieldValue($dataObj->m_Association["FieldRef2"]);
00152         }
00153         
00154         if($dataObj->m_Association["Relationship"]=="Self-Self")
00155         {            
00156               $dataObj->m_Association["ParentRecordIdColumn"] = $dataObj->getField("Id")->m_Column;
00157         }
00158         $dataSqlObj->addAssociation($dataObj->m_Association);
00159 
00160         $querySQL = $dataSqlObj->getSqlStatement() . " ";
00161 
00162         //echo $dataobj->m_QuerySQL."###<br>";
00163         return $querySQL;
00164     }
00165 
00175     public function buildUpdateSQL($dataObj)
00176     {
00177         // generate column value pairs. ignore those whose inputValue=fieldValue
00178         $sqlFlds = $dataObj->m_BizRecord->getToSaveFields('UPDATE');
00179         $colval_pairs = null;
00180         foreach($sqlFlds as $fldobj)
00181         {
00182             $col = $fldobj->m_Column;
00183 
00184             // ignore empty vallue for Date or Datetime
00185             if (($fldobj->m_Value == "" && $fldobj->m_OldValue == "")
00186                     && ($fldobj->m_Type == "Date" || $fldobj->m_Type == "Datetime"))
00187                 continue;
00188 
00189             if ($fldobj->m_ValueOnUpdate != "") // ignore ValueOnUpdate field first
00190                 continue;
00191 
00192             if ($fldobj->isLobField())  // take care of blob/clob type later
00193                 continue;
00194 
00195             // ignore the column where old value is same as new value; set the column only if new value is diff than the old value
00196             if ($fldobj->m_OldValue == $fldobj->m_Value)
00197                 continue;
00198 
00199             $_val = $fldobj->getSqlValue();
00200             $colval_pairs[$col] = $_val; //($_val===null || $_val === '') ? "''" : $_val;
00201         }
00202         if ($colval_pairs == null) return false;
00203 
00204         // take care value on update fields only
00205         foreach($sqlFlds as $fldobj)
00206         {
00207             $col = $fldobj->m_Column;
00208             if ($fldobj->m_ValueOnUpdate != "")
00209             {
00210                 $_val = $fldobj->getValueOnUpdate();
00211                 $colval_pairs[$col] = $_val; //($_val===null || $_val === '') ? "''" : $_val;
00212             }
00213         }
00214 
00215         $sql = "";
00216         foreach ($colval_pairs as $col=>$val)
00217         {
00218             $queryString = QueryStringParam::formatQueryString("`$col`", "=", $val);
00219             if ($sql!="") $sql .= ", $queryString";
00220             else $sql .= $queryString;
00221         }
00222 
00223         $sql = "UPDATE `" . $dataObj->m_MainTable . "` SET " . $sql;
00224 
00225         $whereStr = $dataObj->m_BizRecord->getKeySearchRule(true, true);  // use old value and column name
00226         $sql .= " WHERE " . $whereStr;
00227        
00228         // append DataPerm in the WHERE clause
00229         if($dataObj->m_DataPermControl=='Y')
00230         {
00231                $svcObj = BizSystem::GetService(DATAPERM_SERVICE);
00232                $hasOwnerField = $this->_hasOwnerField($dataObj);
00233                $dataPermSQLRule = $svcObj->buildSqlRule($dataObj,'update',$hasOwnerField);
00234                $sqlSearchRule = $this->_convertSqlExpressionWithoutPrefix($dataObj, $dataPermSQLRule);
00235                if($whereStr!='')
00236                {
00237                      $sql .= ' AND '.$sqlSearchRule;
00238                }else
00239                {             
00240                      $sql .= $sqlSearchRule;
00241                }
00242         }
00243         return $sql;
00244     }
00245     
00246     public function buildUpdateSQLwithCondition($dataObj, $setValue, $condition = null)
00247     {   
00248             
00249         $setValueStr = $this->_convertSqlExpressionWithoutPrefix($dataObj, $setValue);                 
00250         $sql = "UPDATE `" . $dataObj->m_MainTable ."` SET ".$setValueStr;
00251        if($condition)
00252         {
00253               $whereStr = $this->_convertSqlExpressionWithoutPrefix($dataObj, $condition); 
00254               $sql .= " WHERE " . $whereStr;
00255         }
00256         
00257        // append DataPerm in the WHERE clause
00258         if($dataObj->m_DataPermControl=='Y')
00259         {
00260                $svcObj = BizSystem::GetService(DATAPERM_SERVICE);
00261                $hasOwnerField = $this->_hasOwnerField($dataObj);
00262                $dataPermSQLRule = $svcObj->buildSqlRule($dataObj,'update',$hasOwnerField);
00263                $sqlSearchRule = $this->_convertSqlExpressionWithoutPrefix($dataObj, $dataPermSQLRule);
00264                if($whereStr!='')
00265                {
00266                      $sql .= ' AND '.$sqlSearchRule;
00267                }else
00268                {             
00269                      $sql .= $sqlSearchRule;
00270                }
00271         }
00272         return $sql;
00273     }
00274 
00281     public function buildDeleteSQL($dataObj)
00282     {
00283         $sql = "DELETE FROM `" . $dataObj->m_MainTable ."`";
00284         $whereStr = $dataObj->m_BizRecord->getKeySearchRule(false, true);  // use cur value and column name
00285         $sql .= " WHERE " . $whereStr;
00286        // append DataPerm in the WHERE clause
00287         if($dataObj->m_DataPermControl=='Y')
00288         {
00289                $svcObj = BizSystem::GetService(DATAPERM_SERVICE);
00290                $hasOwnerField = $this->_hasOwnerField($dataObj);
00291                $dataPermSQLRule = $svcObj->buildSqlRule($dataObj,'delete',$hasOwnerField);
00292                $sqlSearchRule = $this->_convertSqlExpressionWithoutPrefix($dataObj, $dataPermSQLRule);
00293                if($whereStr!='')
00294                {
00295                      $sql .= ' AND '.$sqlSearchRule;
00296                }else
00297                {             
00298                      $sql .= $sqlSearchRule;
00299                }
00300         }
00301         return $sql;
00302     }
00303     
00304     public function buildDeleteSQLwithCondition($dataObj, $condition = null)
00305     {
00306        
00307         $sql = "DELETE FROM `" . $dataObj->m_MainTable . "`";  
00308         if($condition)
00309         {
00310               $whereStr = $this->_convertSqlExpressionWithoutPrefix($dataObj, $condition); 
00311               $sql .= " WHERE " . $whereStr;
00312         }
00313               // append DataPerm in the WHERE clause
00314         if($dataObj->m_DataPermControl=='Y')
00315         {
00316                $svcObj = BizSystem::GetService(DATAPERM_SERVICE);
00317                $hasOwnerField = $this->_hasOwnerField($dataObj);
00318                $dataPermSQLRule = $svcObj->buildSqlRule($dataObj,'delete',$hasOwnerField);
00319                $sqlSearchRule = $this->_convertSqlExpressionWithoutPrefix($dataObj, $dataPermSQLRule);
00320                if($whereStr!='')
00321                {
00322                      $sql .= ' AND '.$sqlSearchRule;
00323                }else
00324                {             
00325                      $sql .= $sqlSearchRule;
00326                }
00327         }
00328         return $sql;
00329     }
00330 
00339     public function buildInsertSQL($dataObj, $joinValues=null)
00340     {
00341         // generate column value pairs.
00342         $sqlFlds = $dataObj->m_BizRecord->getToSaveFields('CREATE');
00343 
00344         $dbInfo = BizSystem::configuration()->getDatabaseInfo($dataObj->m_Database);
00345         $dbType = $dbInfo["Driver"];
00346 
00347         $sql_col = "";
00348         $sql_val = "";
00349         foreach($sqlFlds as $fldobj)
00350         {
00351             $col = $fldobj->m_Column;
00352 
00353             // if Field Id has null value and Id is an identity type, remove the Id's column from the array
00354             if ($fldobj->m_Name == "Id" && $dataObj->m_IdGeneration == "Identity")
00355                 continue;
00356 
00357             if ($fldobj->isLobField())  // special value for blob/clob type
00358                 $_val = $fldobj->getInsertLobValue($dbType);
00359             else
00360             {
00361               $_val = $fldobj->getSqlValue();
00362                 if ($_val =='' && $fldobj->m_ValueOnCreate != "")
00363                     $_val = $fldobj->getValueOnCreate();                
00364             }
00365 
00366             //if (!$_val || $_val == '') continue;
00367             // modified by jixian for not ignore 0 value
00368             if ( $_val === '') continue;
00369             $sql_col .= "`" . $col . "`, ";
00370             //$sql_val .= $_val. ", ";
00371             $sql_val .= QueryStringParam::formatQueryValue($_val). ", ";
00372         }
00373 
00374         // if joinValues is given then add join values in to the main table InsertSQL.
00375         if(is_array($joinValues))
00376         {
00377             foreach($joinValues as $joinColumn=>$joinValue)
00378             {
00379                 if (!$joinValue || $joinValue == '') continue;
00380                 $sql_col .= "`".$joinColumn. "`, ";
00381                 $sql_val .= "'".$joinValue. "', ";
00382             }
00383         }
00384 
00385         $sql_col = substr($sql_col, 0, -2);
00386         $sql_val = substr($sql_val, 0, -2);
00387 
00388         $sql = "INSERT INTO  `" . $dataObj->m_MainTable . "` (" . $sql_col . ") VALUES (" . $sql_val.")";
00389         return $sql;
00390     }
00391 
00401     private function _ruleToSql($dataObj, $rule)
00402     {
00403         $dataSqlObj = $this->getDataSqlObj();
00404 
00405         $rule = Expression::evaluateExpression($rule,$dataObj);
00406 
00407         // replace all [field] with table.column
00408         foreach($dataObj->m_BizRecord as $bizFld)
00409         {
00410             if (!$bizFld->m_Column && !$bizFld->m_Alias && !$bizFld->m_SqlExpression)
00411                 continue;   // ignore if no column mapped
00412             $fld_pattern = "[".$bizFld->m_Name."]";
00413             if (strpos($rule, $fld_pattern) === false)
00414                 continue;   // ignore if no [field] found
00415             else
00416             {
00417                 if ($bizFld->m_Column && (strpos($bizFld->m_Column,',') != 0))
00418                 {  // handle composite key
00419                     preg_match('/\['.$bizFld->m_Name.'\].*=.*\'(.+)\'/', $rule, $matches); //print_r($matches);
00420                     $keyval = $matches[1];
00421                     $rule = $this->_compKeyRuleToSql($bizFld->m_Column,$keyval);
00422                 }
00423                 else
00424                 {
00425                     if ($bizFld->m_Alias){
00426                         $rule = str_replace($fld_pattern, $bizFld->m_Alias, $rule);
00427                     }
00428                     elseif($bizFld->m_SqlExpression){
00429                      $rule = str_replace($fld_pattern, $bizFld->m_SqlExpression, $rule);
00430                     }
00431                     else
00432                     {
00433                         $tableColumn = $dataSqlObj->getTableColumn($bizFld->m_Join, $bizFld->m_Column);
00434                         $rule = str_replace($fld_pattern, $tableColumn, $rule);
00435                     }
00436                 }
00437             }
00438         }
00439 
00440         return $rule;
00441     }
00442 
00443     //TODO: refactor:rename, what's mean of comp?
00450     private function _compKeyRuleToSql($compColumn, $compValue)
00451     {
00452         $dataSqlObj = $this->getDataSqlObj();
00453         $colArr = explode(",", $compColumn);
00454         $valArr = explode(CK_CONNECTOR, $compValue);
00455         $sql = "";
00456         for ($i=0; $i < count($colArr); $i++)
00457         {
00458             if ($i>0) $sql .= "and";
00459             $tableColumn = $dataSqlObj->getTableColumn("", $colArr[$i]);
00460             if ($valArr[$i] == '')
00461                 $sql .= " ($tableColumn = '" . $valArr[$i] . "' OR $tableColumn is null) ";
00462             else
00463                 $sql .= " $tableColumn = '" . $valArr[$i] . "' ";
00464         }
00465         return $sql;
00466     }
00467 
00476     private function _convertSqlExpression($dataObj, $sqlExpr)
00477     {
00478         $dataSqlObj = $this->getDataSqlObj();
00479         $sqlstr = $sqlExpr;
00480         $startpos = 0;
00481         while (true)
00482         {
00483             $fieldname = substr_lr($sqlstr,"[","]",$startpos);
00484             if ($fieldname == "") break;
00485             else
00486             {
00487                 $bizFld = $dataObj->m_BizRecord->get($fieldname);
00488                 $tableColumn = $dataSqlObj->getTableColumn($bizFld->m_Join, $bizFld->m_Column);
00489                 $sqlstr = str_replace("[$fieldname]", $tableColumn, $sqlstr);
00490                 $startpos = strpos($sqlstr, '['); // Move startpos to the first [ (if it exists) in order to be detect by next itteration
00491             }
00492         }
00493         return $sqlstr;
00494     }
00495 
00496     private function _convertSqlExpressionWithoutPrefix($dataObj, $sqlExpr)
00497     {
00498         $dataSqlObj = $this->getDataSqlObj();
00499         $sqlstr = $sqlExpr;
00500         $startpos = 0;
00501         while (true)
00502         {
00503             $fieldname = substr_lr($sqlstr,"[","]",$startpos);
00504             if ($fieldname == "") break;
00505             else
00506             {
00507                 $bizFld = $dataObj->m_BizRecord->get($fieldname);
00508                 $tableColumn = "`".$bizFld->m_Column."`";
00509                 $sqlstr = str_replace("[$fieldname]", $tableColumn, $sqlstr);
00510                 $startpos = strpos($sqlstr, '['); // Move startpos to the first [ (if it exists) in order to be detect by next itteration
00511             }
00512         }
00513         return $sqlstr;
00514     }    
00515     
00516     private function _hasOwnerField($dataObj){
00517        $fld = $dataObj->getField('owner_id');
00518        if($fld){
00519               return true;
00520        }else{
00521               return false;
00522        }
00523     }
00524 }
00525 
00526 
00535 function substr_lr(&$str, $left, $right, &$startpos, $findfirst=false)
00536 {
00537     $pos0 = strpos($str, $left, $startpos);
00538     if ($pos0 === false) return false;
00539     $tmp = trim(substr($str,$startpos,$pos0-$startpos));
00540     if ($findfirst && $tmp!="") return false;
00541 
00542     $posleft = $pos0+strlen($left);
00543     while(true)
00544     {
00545         $pos1 = strpos($str, $right, $posleft);
00546         if ($pos1 === false)
00547         {
00548             if (trim($right)=="")
00549             {
00550                 $pos1 = strlen($str); // if right is whitespace
00551                 break;
00552             }
00553             else return false;
00554         }
00555         else
00556         {   // avoid \$right is found
00557             if (substr($str,$pos1-1,1) == "\\")  $posleft = $pos1+1;
00558             else break;
00559         }
00560     }
00561 
00562     $startpos = $pos1 + strlen($right);
00563     $retStr = substr($str, $pos0 + strlen($left), $pos1-$pos0-strlen($left));
00564     return $retStr;
00565 }
00566 
00567 ?>

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