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
00084
00085 $dataSqlObj = $this->getNewDataSqlObj();
00086
00087 $dataSqlObj->addMainTable($dataObj->m_MainTable);
00088
00089 if ($dataObj->m_TableJoins)
00090 {
00091 foreach($dataObj->m_TableJoins as $tableJoin)
00092 {
00093 $tbl_col = $dataSqlObj->addJoinTable($tableJoin);
00094 }
00095 }
00096
00097 foreach($dataObj->m_BizRecord as $bizFld)
00098 {
00099 if ($bizFld->m_IgnoreInQuery)
00100 continue;
00101 if ($bizFld->m_Column && $bizFld->m_Type == "Blob")
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
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
00124 $sqlSearchRule = $this->_ruleToSql($dataObj, $dataObj->m_SearchRule);
00125 $dataSqlObj->addSqlWhere($sqlSearchRule);
00126
00127
00128 $sqlSortRule = $this->_ruleToSql($dataObj, $dataObj->m_SortRule);
00129 $dataSqlObj->addOrderBy($sqlSortRule);
00130
00131
00132 $sqlOtherSQLRule = $this->_ruleToSql($dataObj, $dataObj->m_OtherSQLRule);
00133 $dataSqlObj->addOtherSQL($sqlOtherSQLRule);
00134
00135
00136 $sqlAccessSQLRule = $this->_ruleToSql($dataObj, $dataObj->m_AccessRule);
00137 $dataSqlObj->addSqlWhere($sqlAccessSQLRule);
00138
00139
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
00163 return $querySQL;
00164 }
00165
00175 public function buildUpdateSQL($dataObj)
00176 {
00177
00178 $sqlFlds = $dataObj->m_BizRecord->getToSaveFields('UPDATE');
00179 $colval_pairs = null;
00180 foreach($sqlFlds as $fldobj)
00181 {
00182 $col = $fldobj->m_Column;
00183
00184
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 != "")
00190 continue;
00191
00192 if ($fldobj->isLobField())
00193 continue;
00194
00195
00196 if ($fldobj->m_OldValue == $fldobj->m_Value)
00197 continue;
00198
00199 $_val = $fldobj->getSqlValue();
00200 $colval_pairs[$col] = $_val;
00201 }
00202 if ($colval_pairs == null) return false;
00203
00204
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;
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);
00226 $sql .= " WHERE " . $whereStr;
00227
00228
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
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);
00285 $sql .= " WHERE " . $whereStr;
00286
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
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
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
00354 if ($fldobj->m_Name == "Id" && $dataObj->m_IdGeneration == "Identity")
00355 continue;
00356
00357 if ($fldobj->isLobField())
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
00367
00368 if ( $_val === '') continue;
00369 $sql_col .= "`" . $col . "`, ";
00370
00371 $sql_val .= QueryStringParam::formatQueryValue($_val). ", ";
00372 }
00373
00374
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
00408 foreach($dataObj->m_BizRecord as $bizFld)
00409 {
00410 if (!$bizFld->m_Column && !$bizFld->m_Alias && !$bizFld->m_SqlExpression)
00411 continue;
00412 $fld_pattern = "[".$bizFld->m_Name."]";
00413 if (strpos($rule, $fld_pattern) === false)
00414 continue;
00415 else
00416 {
00417 if ($bizFld->m_Column && (strpos($bizFld->m_Column,',') != 0))
00418 {
00419 preg_match('/\['.$bizFld->m_Name.'\].*=.*\'(.+)\'/', $rule, $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
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, '[');
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, '[');
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);
00551 break;
00552 }
00553 else return false;
00554 }
00555 else
00556 {
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 ?>