3 class ricoXmlResponse {
8 var $readAllRows; // always return the total number of rows? (if true, the user will always see the total number of rows, but there is a small performance hit)
9 var $convertCharSet; // set to true if database is ISO-8859-1 encoded, false if UTF-8
10 var $AllRowsMax; // max # of rows to send if numrows=-1
21 function ricoXmlResponse() {
22 if (is_object($GLOBALS['oDB'])) {
23 $this->objDB=$GLOBALS['oDB']; // use oDB global as database connection, if it exists
25 $this->orderByRef=false;
26 $this->sendDebugMsgs=false;
27 $this->readAllRows=true; // has no effect on SQL Server 2005, Oracle, and MySQL because they use Query2xmlRaw_Limit()
28 $this->convertCharSet=false;
29 $this->AllRowsMax=1999;
32 // All Oracle and SQL Server 2005 queries *must* have an ORDER BY clause
33 // "as" clauses are now ok
34 // If numrows < 0, then retrieve all rows
36 function Query2xml($sqlselect, $offset, $numrows, $gettotal, $filters=array()) {
38 $Dialect=$this->objDB->Dialect;
40 $numrows=$this->AllRowsMax;
41 $Dialect=""; // don't use limit query
43 if ($this->sendDebugMsgs) {
44 echo "\n<debug>Query2xml: SQL Dialect=".htmlspecialchars($Dialect)."</debug>";
45 echo "\n<debug>Query2xml: numrows=".$numrows."</debug>";
46 echo "\n<debug>Query2xml: resource type=".get_resource_type($this->objDB->dbMain)."</debug>";
48 switch ($this->objDB->Dialect) {
49 case "MySQL": $this->orderByRef=true; break;
51 $this->arParams=array('H'=>array(), 'W'=>array());
52 $this->oParse= new sqlParse();
53 $this->oParse->ParseSelect($sqlselect);
54 $this->ApplyQStringParms($filters);
55 $this->allParams=array_merge($this->arParams['W'],$this->arParams['H']);
56 echo "\n<rows update_ui='true' offset='".$offset."'>";
60 $this->objDB->SingleRecordQuery("select @@VERSION", $version);
61 if (strtoupper(substr($sqlselect,0,7))!="SELECT ") {
62 $this->allParams=array();
63 $totcnt=$this->Query2xmlRaw($sqlselect, $offset, $numrows);
65 else if (preg_match("/SQL Server 200(5|8)/i",$version[0])) {
66 $this->sqltext=$this->UnparseWithRowNumber($offset, $numrows + 1, true);
67 $totcnt=$this->Query2xmlRaw_Limit($this->sqltext, $offset, $numrows, 1);
70 $this->sqltext=$this->oParse->UnparseSelect();
71 $totcnt=$this->Query2xmlRaw($this->sqltext, $offset, $numrows);
76 $this->sqltext=$this->UnparseWithRowNumber($offset, $numrows + 1, false);
77 $totcnt=$this->Query2xmlRaw_Limit($this->sqltext, $offset, $numrows, 1);
81 $this->sqltext=$this->oParse->UnparseSelect()." LIMIT ".$offset.",".($numrows + 1);
82 $totcnt=$this->Query2xmlRaw_Limit($this->sqltext, $offset, $numrows, 0);
86 $this->sqltext=$this->oParse->UnparseSelect()." LIMIT ".($numrows + 1) . " OFFSET ".$offset;
87 $totcnt=$this->Query2xmlRaw_Limit($this->sqltext, $offset, $numrows, 0);
91 $this->sqltext=$this->oParse->UnparseSelect();
92 $totcnt=$this->Query2xmlRaw($this->sqltext, $offset, $numrows);
96 if ($this->sendDebugMsgs) {
97 echo "\n<debug>origQuery=".htmlspecialchars($sqlselect)."</debug>";
98 echo "\n<debug>execQuery=".htmlspecialchars($this->objDB->db->lastQuery)."</debug>";
100 if (!$this->eof && $gettotal) {
101 $totcnt=$this->getTotalRowCount();
102 if ($this->sendDebugMsgs) {
103 echo "\n<debug>cntQuery=".htmlspecialchars($this->objDB->db->lastQuery)."</debug>";
107 echo "\n<rowcount>".$totcnt."</rowcount>";
114 function Query2xmlDistinct($sqlselect, $colnum, $numrows, $filters=array()) {
115 if ($numrows < 0) $numrows=$this->AllRowsMax;
116 $this->arParams=array('H'=>array(), 'W'=>array());
117 $this->oParse= new sqlParse();
118 $this->oParse->ParseSelect($sqlselect);
119 $this->ApplyQStringParms($filters);
120 $this->allParams=array_merge($this->arParams['W'],$this->arParams['H']);
121 echo "\n<rows update_ui='true' offset='0' distinct='" . $colnum . "'>";
122 $this->sqltext=$this->oParse->UnparseDistinctColumn($colnum);
123 $totcnt=$this->Query2xmlRaw($this->sqltext, 0, $numrows);
125 if ($this->sendDebugMsgs) {
126 echo "\n<debug>origQuery=".htmlspecialchars($sqlselect)."</debug>";
127 echo "\n<debug>execQuery=".htmlspecialchars($this->objDB->db->lastQuery)."</debug>";
133 // Tested ok with SQL Server 2005, MySQL, and Oracle
134 function getTotalRowCount() {
135 $countSql="SELECT ".$this->oParse->UnparseColumnList()." FROM ".$this->oParse->FromClause;
136 if (!empty($this->oParse->WhereClause)) {
137 $countSql.=" WHERE ".$this->oParse->WhereClause;
139 if (is_array($this->oParse->arGroupBy)) {
140 if (count($this->oParse->arGroupBy) > 0) {
141 $countSql.=" GROUP BY ".implode(",",$this->oParse->arGroupBy);
144 if (!empty($this->oParse->HavingClause)) {
145 $countSql.=" HAVING ".$this->oParse->HavingClause;
147 $countSql="SELECT COUNT(*) FROM (".$countSql.")";
148 if ($this->objDB->Dialect != "Oracle") {
149 $countSql.=" AS rico_Main";
151 if (count($this->allParams)>0) {
152 $rsMain=$this->objDB->RunParamQuery($countSql,$this->allParams);
154 $rsMain=$this->objDB->RunQuery($countSql);
157 echo "\n<debug>getTotalRowCount: rsMain is null</debug>";
160 if (!$this->objDB->db->FetchArray($rsMain,$a)) return;
161 $this->objDB->rsClose($rsMain);
167 function UnparseWithRowNumber($offset, $numrows, $includeAS) {
168 if (is_array($this->oParse->arOrderBy)) {
169 if (count($this->oParse->arOrderBy) > 0) {
170 $strOrderBy=implode(",",$this->oParse->arOrderBy);
173 if (empty($strOrderBy) && !preg_match("/\bjoin\b/",$this->oParse->FromClause)) {
174 // order by clause should be included in main sql select statement
175 // However, if it isn't, then use primary key as sort - assuming FromClause is a simple table name
176 $strOrderBy=$this->objDB->PrimaryKey($this->oParse->FromClause);
178 $unparseText="SELECT ROW_NUMBER() OVER (ORDER BY ".$strOrderBy.") AS rico_rownum,";
179 $unparseText.=$this->oParse->UnparseColumnList()." FROM ".$this->oParse->FromClause;
180 if (!empty($this->oParse->WhereClause)) {
181 $unparseText.=" WHERE ".$this->oParse->WhereClause;
183 if (is_array($this->oParse->arGroupBy)) {
184 if (count($this->oParse->arGroupBy) > 0) {
185 $unparseText.=" GROUP BY ".implode(",",$this->oParse->arGroupBy);
188 if (!empty($this->oParse->HavingClause)) {
189 $unparseText.=" HAVING ".$this->oParse->HavingClause;
191 $unparseText="SELECT * FROM (".$unparseText.")";
193 $unparseText.=" AS rico_Main";
195 $unparseText.=" WHERE rico_rownum > ".$offset." AND rico_rownum <= ".($offset + $numrows);
199 function Query2xmlRaw($rawsqltext, $offset, $numrows) {
200 if (count($this->allParams)>0) {
201 $rsMain=$this->objDB->RunParamQuery($rawsqltext,$this->allParams);
203 $rsMain=$this->objDB->RunQuery($rawsqltext);
206 echo "\n<debug>Query2xmlRaw: rsMain is null - ".htmlspecialchars($this->objDB->ErrorMsg())."</debug>";
210 $colcnt = $this->objDB->db->NumFields($rsMain);
211 $totcnt = $this->objDB->db->NumRows($rsMain);
212 //echo "<debug>Query2xmlRaw: NumRows=$totcnt</debug>";
213 if ($offset < $totcnt || $totcnt==-1)
216 $this->objDB->db->Seek($rsMain,$offset);
217 while(($this->objDB->db->FetchRow($rsMain,$row)) && $rowcnt < $numrows)
221 for ($i=0; $i < $colcnt; $i++)
222 print $this->XmlStringCell($row[$i]);
226 $totcnt=$offset+$rowcnt;
227 while($this->objDB->db->FetchRow($rsMain,$row))
235 $this->objDB->rsClose($rsMain);
240 function Query2xmlRaw_Limit($rawsqltext, $offset, $numrows, $firstcol) {
241 if (count($this->allParams)>0) {
242 $rsMain=$this->objDB->RunParamQuery($rawsqltext,$this->allParams);
244 $rsMain=$this->objDB->RunQuery($rawsqltext);
246 if ($this->objDB->db->HasError()) echo "<error>" . $this->objDB->db->ErrorMsg() . "</error>";
250 echo "<debug>Query2xmlRaw_Limit: rsMain is null</debug>";
253 $colcnt = $this->objDB->db->NumFields($rsMain);
255 while(($this->objDB->db->FetchRow($rsMain,$row)) && $rowcnt < $numrows)
259 for ($i=$firstcol; $i < $colcnt; $i++)
260 print $this->XmlStringCell($row[$i]);
264 $this->eof=($rowcnt < $numrows);
265 $this->objDB->rsClose($rsMain);
269 function SetDbConn(&$dbcls) {
270 $this->objDB=&$dbcls;
273 function PushParam($newvalue) {
274 $parm=$this->convertCharSet ? utf8_decode($newvalue) : $newvalue;
275 if (get_magic_quotes_gpc()) $parm=stripslashes($parm);
276 array_push($this->arParams[$this->condType], $parm);
277 if ($this->sendDebugMsgs) {
278 echo "\n<debug>".$this->condType." param=".htmlspecialchars($parm)."</debug>";
282 function setCondType($selectItem) {
283 $this->condType=(preg_match("/\bmin\(|\bmax\(|\bsum\(|\bcount\(/i",$selectItem) && !preg_match("/\bselect\b/i",$selectItem)) ? 'H' : 'W';
286 function addCondition($newfilter) {
287 switch ($this->condType) {
288 case 'H': $this->oParse->AddHavingCondition($newfilter); break;
289 case 'W': $this->oParse->AddWhereCondition($newfilter); break;
293 function ApplyQStringParms($filters) {
294 foreach($_GET as $qs => $value) {
295 $prefix=substr($qs,0,1);
298 // user-invoked condition
302 if (!is_numeric($i)) break;
304 if ($i<0 || $i>=count($filters)) break;
305 $newfilter=$filters[$i];
306 $this->condType=strtoupper($prefix);
308 $j=strpos($newfilter," in (?)");
310 $a=explode(",", $value);
311 for ($i=0; $i < count($a); $i++) {
312 $this->PushParam($a[$i]);
315 $newfilter=substr($newfilter,0,$j+4) . implode(",",$a) . substr($newfilter,$j+5);
316 } elseif (strpos($newfilter,"?") !== false) {
317 $this->PushParam($value);
320 $this->addCondition($newfilter);
326 if (!is_numeric($i)) break;
328 if ($i<0 || $i>=count($this->oParse->arSelList)) break;
329 $value=strtoupper(substr($value,0,4));
330 if (!in_array($value,array('ASC','DESC'))) $value="ASC";
331 if ($this->orderByRef)
332 $this->oParse->AddSort(($i + 1)." ".$value);
334 $this->oParse->AddSort($this->oParse->arSelList[$i]." ".$value);
337 // user-supplied filter
340 foreach($value as $i => $filter) {
341 if ($i<0 || $i>=count($this->oParse->arSelList)) break;
342 $newfilter=$this->oParse->arSelList[$i];
343 $this->setCondType($newfilter);
344 switch ($filter['op']) {
346 if ($filter[0]=="") {
347 if ($this->objDB->Dialect=="Access") {
348 $newfilter="iif(IsNull(" . $newfilter . "),''," . $newfilter . ")";
350 $newfilter="coalesce(" . $newfilter . ",'')";
354 $this->PushParam($filter[0]);
358 $this->PushParam($filter[0]);
362 $this->PushParam($filter[0]);
364 case "NULL": $newfilter.=" is null"; break;
365 case "NOTNULL": $newfilter.=" is not null"; break;
367 $newfilter.=" LIKE ?";
368 $this->PushParam(str_replace("*",$this->objDB->Wildcard,$filter[0]));
371 $flen=$filter['len'];
372 if (!is_numeric($flen)) break;
374 $newfilter.=" NOT IN (";
375 for ($j=0; $j<$flen; $j++) {
376 if ($j > 0) $newfilter.=",";
378 $this->PushParam($filter[$j]);
383 $this->addCondition($newfilter);
390 function XmlStringCell($value) {
391 if (!isset($value)) {
395 if ($this->convertCharSet) $value=utf8_encode($value);
396 $result=htmlspecialchars($value, ENT_COMPAT, 'UTF-8');
398 return "<td>".$result."</td>";
401 // for the root node, parentID should "" (empty string)
402 // containerORleaf: L/zero (leaf), C/non-zero (container)
403 // selectable: 0->not selectable, 1->selectable
404 function WriteTreeRow($parentID, $ID, $description, $containerORleaf, $selectable) {
406 echo $this->XmlStringCell($parentID);
407 echo $this->XmlStringCell($ID);
408 echo $this->XmlStringCell($description);
409 echo $this->XmlStringCell($containerORleaf);
410 echo $this->XmlStringCell($selectable);