4 // copied from http://www.php.net/json_encode
\r
5 if (!function_exists('json_encode'))
\r
7 function json_encode($a=false)
\r
9 if (is_null($a)) return 'null';
\r
10 if ($a === false) return 'false';
\r
11 if ($a === true) return 'true';
\r
16 // Always use "." for floats.
\r
17 return floatval(str_replace(",", ".", strval($a)));
\r
22 static $jsonReplaces = array(array("\\", "/", "\n", "\t", "\r", "\b", "\f", '"'), array('\\\\', '\\/', '\\n', '\\t', '\\r', '\\b', '\\f', '\"'));
\r
23 return '"' . str_replace($jsonReplaces[0], $jsonReplaces[1], $a) . '"';
\r
29 for ($i = 0, reset($a); $i < count($a); $i++, next($a))
\r
40 foreach ($a as $v) $result[] = json_encode($v);
\r
41 return '[' . join(',', $result) . ']';
\r
45 foreach ($a as $k => $v) $result[] = json_encode($k).':'.json_encode($v);
\r
46 return '{' . join(',', $result) . '}';
\r
51 class ricoXmlResponse {
\r
53 // public properties
\r
56 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)
\r
57 var $convertCharSet; // set to true if database is ISO-8859-1 encoded, false if UTF-8
\r
58 var $AllRowsMax; // max # of rows to send if numrows=-1
\r
59 var $fmt; // xml, json, html, xl
\r
61 // private properties
\r
75 function ricoXmlResponse() {
\r
76 if (isset($GLOBALS['oDB']) && is_object($GLOBALS['oDB'])) {
\r
77 $this->objDB=$GLOBALS['oDB']; // use oDB global as database connection, if it exists
\r
79 $this->orderByRef=false;
\r
80 $this->sendDebugMsgs=false;
\r
81 $this->readAllRows=true; // has no effect on SQL Server 2005, Oracle, and MySQL because they use Query2xmlRaw_Limit()
\r
82 $this->convertCharSet=false;
\r
83 $this->SendHdg=false;
\r
84 $this->AllRowsMax=1999;
\r
85 $this->Headings=array();
\r
86 $this->HiddenCols=array();
\r
89 function ProcessQuery($id, $sqlselect, $filters=array(), $errmsg='') {
\r
90 $this->fmt=isset($_GET["_fmt"]) ? $_GET["_fmt"] : "xml";
\r
91 $offset=isset($_GET["offset"]) ? $_GET["offset"] : "0";
\r
92 $size=isset($_GET["page_size"]) ? $_GET["page_size"] : "";
\r
93 $total=isset($_GET["get_total"]) ? strtolower($_GET["get_total"]) : "false";
\r
94 $distinct=isset($_GET["distinct"]) ? $_GET["distinct"] : "";
\r
95 $edit=isset($_GET["edit"]) ? $_GET["edit"] : "";
\r
96 if (isset($_GET["hidden"]) && $_GET["hidden"]!="") $this->HiddenCols=explode(",", $_GET["hidden"]);
\r
99 if ($this->fmt != "xl") {
\r
100 header("Cache-Control: no-cache");
\r
101 header("Pragma: no-cache");
\r
102 header("Expires: ".gmdate("D, d M Y H:i:s",time()+(-1*60))." GMT");
\r
105 switch ($this->fmt) {
\r
107 header("Content-type: text/html");
\r
108 echo "<html><head></head><body>\n";
\r
109 $closetags="</body></html>";
\r
110 $this->RowsStart="\n<table border='1'>";
\r
111 $this->RowsEnd="\n</table>";
\r
113 $this->sendDebugMsgs=false;
\r
114 $this->SendHdg=true;
\r
117 $this->convertCharSet=false;
\r
118 header("Content-type: application/vnd.ms-excel");
\r
119 echo "<html><head></head><body>\n";
\r
120 $closetags="</body></html>";
\r
121 $this->RowsStart="\n<table>";
\r
122 $this->RowsEnd="\n</table>";
\r
124 $this->sendDebugMsgs=false;
\r
125 $this->SendHdg=true;
\r
128 header("Content-type: application/json");
\r
129 echo "{\n\"id\":\"" . $id . "\"";
\r
130 $this->RowsStart=",\n\"update_ui\":true,\n\"offset\":" . $offset . ",\n\"rows\":[";
\r
131 $this->RowsEnd="\n]";
\r
133 $this->sendDebugMsgs=false;
\r
138 header("Content-type: text/xml");
\r
139 echo "<?xml version='1.0' encoding='UTF-8'?".">\n";
\r
140 echo "\n<ajax-response><response type='object' id='" . $id . "'>";
\r
141 $closetags="</response></ajax-response>";
\r
142 $this->RowsStart="\n<rows update_ui='true' offset='" . $offset . "'>";
\r
143 $this->RowsEnd="\n</rows>";
\r
146 if (!empty($errmsg)) {
\r
147 $this->ErrorResponse($errmsg);
\r
148 } elseif (empty($id)) {
\r
149 $this->ErrorResponse("No ID provided!");
\r
150 } elseif ($distinct=="" && !is_numeric($offset)) {
\r
151 $this->ErrorResponse("Invalid offset!");
\r
152 } elseif ($distinct=="" && !is_numeric($size)) {
\r
153 $this->ErrorResponse("Invalid size!");
\r
154 } elseif ($distinct!="" && !is_numeric($distinct)) {
\r
155 $this->ErrorResponse("Invalid distinct parameter!");
\r
157 if ($this->SendHdg && is_array($sqlselect)) {
\r
158 // populate $Headings from $sqlselect[9] taking into account hidden columns
\r
159 for ($i=0,$j=0,$SkipIdx=0; $i<count($sqlselect[9]); $i++) {
\r
161 if ($SkipIdx < count($this->HiddenCols)) {
\r
162 $skip=($this->HiddenCols[$SkipIdx] == $i);
\r
163 if ($skip) $SkipIdx++;
\r
166 $this->Headings[$j++]=$sqlselect[9][$i];
\r
170 $this->objDB->DisplayErrors=false;
\r
171 $this->objDB->ErrMsgFmt="MULTILINE";
\r
172 if ($distinct!="" && is_numeric($distinct)) {
\r
173 $this->Query2xmlDistinct($sqlselect, intval($distinct), -1, $filters);
\r
174 } elseif ($edit!="" && is_numeric($edit) && is_array($sqlselect)) {
\r
175 $this->Query2xml($sqlselect[8][intval($edit)], intval($offset), intval($size), $total!="false", $filters);
\r
177 $this->Query2xml($sqlselect, intval($offset), intval($size), $total!="false", $filters);
\r
179 if (!empty($this->objDB->LastErrorMsg)) {
\r
180 $this->ErrorResponse($this->objDB->LastErrorMsg);
\r
183 echo "\n".$closetags;
\r
186 function ErrorResponse($msg) {
\r
187 $this->AppendResponse("error",$msg);
\r
190 function AppendResponse($tag, $content) {
\r
191 switch ($this->fmt) {
\r
193 echo "\n<p>".$tag."<br>".htmlspecialchars($content)."</p>";
\r
196 echo "\n<p>".$tag."<br>".htmlspecialchars($content)."</p>";
\r
199 echo ",\n\"".$tag."\":".json_encode($content);
\r
202 echo "\n<".$tag.">".htmlspecialchars($content)."</".$tag.">";
\r
207 // All Oracle and SQL Server 2005 queries *must* have an ORDER BY clause
\r
208 // "as" clauses are now ok
\r
209 // If numrows < 0, then retrieve all rows
\r
210 function Query2xml($sqlselect, $offset, $numrows, $gettotal, $filters=array()) {
\r
211 if ($numrows >= 0) {
\r
212 $Dialect=$this->objDB->Dialect;
\r
214 $numrows=$this->AllRowsMax;
\r
215 $Dialect=""; // don't use limit query
\r
217 switch ($this->objDB->Dialect) {
\r
218 case "MySQL": $this->orderByRef=true; break;
\r
220 $this->arParams=array('H'=>array(), 'W'=>array());
\r
221 $this->oParse= new sqlParse();
\r
222 if (is_array($sqlselect)) {
\r
223 $this->oParse->LoadArray($sqlselect);
\r
225 $this->oParse->ParseSelect($sqlselect);
\r
227 $this->ApplyQStringParms($filters);
\r
228 $this->allParams=array_merge($this->arParams['W'],$this->arParams['H']);
\r
229 echo $this->RowsStart;
\r
230 switch ($Dialect) {
\r
233 $this->objDB->SingleRecordQuery("select @@VERSION", $version);
\r
234 if (is_string($sqlselect) && strtoupper(substr($sqlselect,0,7))!="SELECT ") {
\r
235 $this->allParams=array();
\r
236 $totcnt=$this->Query2xmlRaw($sqlselect, $offset, $numrows);
\r
238 else if (preg_match("/SQL Server 200(5|8)/i",$version[0])) {
\r
239 $this->sqltext=$this->UnparseWithRowNumber($offset, $numrows + 1, true);
\r
240 $totcnt=$this->Query2xmlRaw_Limit($this->sqltext, $offset, $numrows, 1);
\r
243 $this->sqltext=$this->oParse->UnparseSelectSkip($this->HiddenCols);
\r
244 $totcnt=$this->Query2xmlRaw($this->sqltext, $offset, $numrows);
\r
249 $this->sqltext=$this->UnparseWithRowNumber($offset, $numrows + 1, false);
\r
250 $totcnt=$this->Query2xmlRaw_Limit($this->sqltext, $offset, $numrows, 1);
\r
254 $this->sqltext=$this->oParse->UnparseSelectSkip($this->HiddenCols)." LIMIT ".$offset.",".($numrows + 1);
\r
255 $totcnt=$this->Query2xmlRaw_Limit($this->sqltext, $offset, $numrows, 0);
\r
259 $this->sqltext=$this->oParse->UnparseSelectSkip($this->HiddenCols);
\r
260 $totcnt=$this->Query2xmlRaw($this->sqltext, $offset, $numrows);
\r
263 echo $this->RowsEnd;
\r
264 if ($this->sendDebugMsgs) {
\r
265 $this->AppendResponse("debug",$this->objDB->db->lastQuery);
\r
267 if (!$this->eof && $gettotal) {
\r
268 $totcnt=$this->getTotalRowCount();
\r
270 if ($this->fmt=="xml" || $this->fmt=="json") {
\r
271 if ($this->eof) $this->AppendResponse("rowcount",$totcnt);
\r
273 $this->oParse=NULL;
\r
278 function Query2xmlDistinct($sqlselect, $colnum, $numrows, $filters=array()) {
\r
279 if ($numrows < 0) $numrows=$this->AllRowsMax;
\r
280 $this->arParams=array('H'=>array(), 'W'=>array());
\r
281 $this->oParse= new sqlParse();
\r
282 if (is_array($sqlselect)) {
\r
283 $this->oParse->LoadArray($sqlselect);
\r
285 $this->oParse->ParseSelect($sqlselect);
\r
287 $this->ApplyQStringParms($filters);
\r
288 $this->allParams=array_merge($this->arParams['W'],$this->arParams['H']);
\r
289 echo $this->RowsStart;
\r
290 $this->sqltext=$this->oParse->UnparseDistinctColumn($colnum);
\r
291 $totcnt=$this->Query2xmlRaw($this->sqltext, 0, $numrows);
\r
292 echo $this->RowsEnd;
\r
293 if ($this->sendDebugMsgs) {
\r
294 $this->AppendResponse("debug",$this->objDB->db->lastQuery);
\r
296 $this->oParse=NULL;
\r
300 // Tested ok with SQL Server 2005, MySQL, and Oracle
\r
301 function getTotalRowCount() {
\r
302 $countSql="SELECT ".$this->oParse->UnparseColumnList()." FROM ".$this->oParse->FromClause;
\r
303 if (!empty($this->oParse->WhereClause)) {
\r
304 $countSql.=" WHERE ".$this->oParse->WhereClause;
\r
306 if (is_array($this->oParse->arGroupBy)) {
\r
307 if (count($this->oParse->arGroupBy) > 0) {
\r
308 $countSql.=" GROUP BY ".implode(",",$this->oParse->arGroupBy);
\r
311 if (!empty($this->oParse->HavingClause)) {
\r
312 $countSql.=" HAVING ".$this->oParse->HavingClause;
\r
314 $countSql="SELECT COUNT(*) FROM (".$countSql.")";
\r
315 if ($this->objDB->Dialect != "Oracle") {
\r
316 $countSql.=" AS rico_Main";
\r
318 if (count($this->allParams)>0) {
\r
319 $rsMain=$this->objDB->RunParamQuery($countSql,$this->allParams);
\r
321 $rsMain=$this->objDB->RunQuery($countSql);
\r
324 echo "\n<debug>getTotalRowCount: rsMain is null</debug>";
\r
327 if (!$this->objDB->db->FetchArray($rsMain,$a)) return;
\r
328 $this->objDB->rsClose($rsMain);
\r
334 function UnparseWithRowNumber($offset, $numrows, $includeAS) {
\r
335 if (is_array($this->oParse->arOrderBy)) {
\r
336 if (count($this->oParse->arOrderBy) > 0) {
\r
337 $strOrderBy=implode(",",$this->oParse->arOrderBy);
\r
340 if (empty($strOrderBy) && !preg_match("/\bjoin\b/",$this->oParse->FromClause)) {
\r
341 // order by clause should be included in main sql select statement
\r
342 // However, if it isn't, then use primary key as sort - assuming FromClause is a simple table name
\r
343 $strOrderBy=$this->objDB->PrimaryKey($this->oParse->FromClause);
\r
345 $unparseText="SELECT ROW_NUMBER() OVER (ORDER BY ".$strOrderBy.") AS rico_rownum,";
\r
346 $unparseText.=$this->oParse->UnparseColumnListSkip($this->HiddenCols)." FROM ".$this->oParse->FromClause;
\r
347 if (!empty($this->oParse->WhereClause)) {
\r
348 $unparseText.=" WHERE ".$this->oParse->WhereClause;
\r
350 if (is_array($this->oParse->arGroupBy)) {
\r
351 if (count($this->oParse->arGroupBy) > 0) {
\r
352 $unparseText.=" GROUP BY ".implode(",",$this->oParse->arGroupBy);
\r
355 if (!empty($this->oParse->HavingClause)) {
\r
356 $unparseText.=" HAVING ".$this->oParse->HavingClause;
\r
358 $unparseText="SELECT * FROM (".$unparseText.")";
\r
360 $unparseText.=" AS rico_Main";
\r
362 $unparseText.=" WHERE rico_rownum > ".$offset." AND rico_rownum <= ".($offset + $numrows);
\r
363 return $unparseText;
\r
366 function Query2xmlRaw($rawsqltext, $offset, $numrows) {
\r
367 if (count($this->allParams)>0) {
\r
368 $rsMain=$this->objDB->RunParamQuery($rawsqltext,$this->allParams);
\r
370 $rsMain=$this->objDB->RunQuery($rawsqltext);
\r
372 if (!$rsMain) return;
\r
374 $colcnt = $this->objDB->db->NumFields($rsMain);
\r
375 $totcnt = $this->objDB->db->NumRows($rsMain);
\r
376 //echo "<debug>Query2xmlRaw: NumRows=$totcnt</debug>";
\r
377 if ($offset < $totcnt || $totcnt==-1) {
\r
378 $this->objDB->db->Seek($rsMain,$offset);
\r
379 switch ($this->fmt) {
\r
380 case "json": $rowcnt=$this->WriteRowsJSON($rsMain, $numrows, 0); break;
\r
381 default: $rowcnt=$this->WriteRowsXHTML($rsMain, $numrows, 0); break;
\r
384 $totcnt=$offset+$rowcnt;
\r
385 while($this->objDB->db->FetchRow($rsMain,$row))
\r
391 $this->objDB->rsClose($rsMain);
\r
396 function Query2xmlRaw_Limit($rawsqltext, $offset, $numrows, $firstcol) {
\r
397 if (count($this->allParams)>0) {
\r
398 $rsMain=$this->objDB->RunParamQuery($rawsqltext,$this->allParams);
\r
400 $rsMain=$this->objDB->RunQuery($rawsqltext);
\r
402 //if ($this->objDB->db->HasError()) echo "<error>" . $this->objDB->db->ErrorMsg() . "</error>";
\r
405 if (!$rsMain) return;
\r
406 switch ($this->fmt) {
\r
407 case "json": $totcnt+=$this->WriteRowsJSON($rsMain, $numrows, $firstcol); break;
\r
408 default: $totcnt+=$this->WriteRowsXHTML($rsMain, $numrows, $firstcol); break;
\r
410 $this->objDB->rsClose($rsMain);
\r
414 function WriteRowsXHTML($rsMain, $numrows, $firstcol) {
\r
415 $colcnt = $this->objDB->db->NumFields($rsMain);
\r
417 if ($this->SendHdg) {
\r
419 for ($i=$firstcol; $i < $colcnt; $i++) {
\r
420 $n=empty($this->Headings[$i-$firstcol]) ? $this->objDB->db->FieldName($rsMain,$i) : $this->Headings[$i-$firstcol];
\r
421 print $this->XmlStringCell($n);
\r
425 while(($this->objDB->db->FetchRow($rsMain,$row)) && $rowcnt < $numrows) {
\r
428 for ($i=$firstcol; $i < $colcnt; $i++)
\r
429 print $this->XmlStringCell($row[$i]);
\r
432 $this->eof=($rowcnt < $numrows);
\r
436 function WriteRowsJSON($rsMain, $numrows, $firstcol) {
\r
437 $colcnt = $this->objDB->db->NumFields($rsMain);
\r
439 if ($this->SendHdg) {
\r
441 for ($i=$firstcol; $i < $colcnt; $i++) {
\r
442 //$n=empty($this->Headings($i-$firstcol)) ? $this->objDB->db->FieldName($rsMain,$i) : $this->Headings($i-$firstcol);
\r
443 print json_encode($n);
\r
447 while(($this->objDB->db->FetchRow($rsMain,$row)) && $rowcnt < $numrows) {
\r
448 if ($rowcnt>0 || $this->SendHdg) echo ",";
\r
451 for ($i=$firstcol; $i < $colcnt; $i++) {
\r
452 if ($i>$firstcol) echo ",";
\r
454 if ($this->convertCharSet) $value=utf8_encode($value);
\r
455 print json_encode($value);
\r
459 $this->eof=($rowcnt < $numrows);
\r
463 function SetDbConn(&$dbcls) {
\r
464 $this->objDB=&$dbcls;
\r
467 function PushParam($newvalue) {
\r
468 $parm=$this->convertCharSet ? utf8_decode($newvalue) : $newvalue;
\r
469 if (get_magic_quotes_gpc()) $parm=stripslashes($parm);
\r
470 array_push($this->arParams[$this->condType], $parm);
\r
471 if ($this->sendDebugMsgs) {
\r
472 echo "\n<debug>".$this->condType." param=".htmlspecialchars($parm)."</debug>";
\r
476 function setCondType($selectItem) {
\r
477 $this->condType=(preg_match("/\bmin\(|\bmax\(|\bsum\(|\bcount\(/i",$selectItem) && !preg_match("/\bselect\b/i",$selectItem)) ? 'H' : 'W';
\r
480 function addCondition($newfilter) {
\r
481 switch ($this->condType) {
\r
482 case 'H': $this->oParse->AddHavingCondition($newfilter); break;
\r
483 case 'W': $this->oParse->AddWhereCondition($newfilter); break;
\r
487 function ApplyQStringParms($filters) {
\r
488 foreach($_GET as $qs => $value) {
\r
489 $prefix=substr($qs,0,1);
\r
492 // user-invoked condition
\r
496 if (!is_numeric($i)) break;
\r
498 if ($i<0 || $i>=count($filters)) break;
\r
499 $newfilter=$filters[$i];
\r
500 $this->condType=strtoupper($prefix);
\r
502 $j=strpos($newfilter," in (?)");
\r
503 if ($j !== false) {
\r
504 $a=explode(",", $value);
\r
505 for ($i=0; $i < count($a); $i++) {
\r
506 $this->PushParam($a[$i]);
\r
509 $newfilter=substr($newfilter,0,$j+4) . implode(",",$a) . substr($newfilter,$j+5);
\r
510 } elseif (strpos($newfilter,"?") !== false) {
\r
511 $this->PushParam($value);
\r
514 $this->addCondition($newfilter);
\r
520 if (!is_numeric($i)) break;
\r
522 if ($i<0 || $i>=count($this->oParse->arSelList)) break;
\r
523 $value=strtoupper(substr($value,0,4));
\r
524 if (!in_array($value,array('ASC','DESC'))) $value="ASC";
\r
525 if ($this->orderByRef)
\r
526 $this->oParse->AddSort(($i + 1)." ".$value);
\r
528 $this->oParse->AddSort($this->oParse->arSelList[$i]." ".$value);
\r
531 // user-supplied filter
\r
534 foreach($value as $i => $filter) {
\r
535 if ($i<0 || $i>=count($this->oParse->arSelList)) break;
\r
536 $newfilter=$this->oParse->arSelList[$i];
\r
537 $this->setCondType($newfilter);
\r
538 switch ($filter['op']) {
\r
540 $newfilter='('.$this->AddCoalesce($newfilter).' IN '.$this->GetMultiParmFilter($filter).')';
\r
544 $this->PushParam($filter[0]);
\r
548 $this->PushParam($filter[0]);
\r
550 case "NULL": $newfilter.=" is null"; break;
\r
551 case "NOTNULL": $newfilter.=" is not null"; break;
\r
553 $newfilter.=" LIKE ?";
\r
554 $this->PushParam(str_replace("*",$this->objDB->Wildcard,$filter[0]));
\r
557 $newfilter='('.$this->AddCoalesce($newfilter).' NOT IN '.$this->GetMultiParmFilter($filter).')';
\r
560 $this->addCondition($newfilter);
\r
567 function AddCoalesce($newfilter) {
\r
568 if ($this->objDB->Dialect=="Access") {
\r
569 return "iif(IsNull(" . $newfilter . "),''," . $newfilter . ")";
\r
571 return "coalesce(" . $newfilter . ",'')";
\r
576 function GetMultiParmFilter($filter) {
\r
577 $flen=$filter['len'];
\r
578 if (!is_numeric($flen)) return "";
\r
579 $flen=intval($flen);
\r
581 for ($j=0; $j<$flen; $j++) {
\r
582 if ($j > 0) $newfilter.=",";
\r
584 $this->PushParam($filter[$j]);
\r
590 function XmlStringCell($value) {
\r
591 if (!isset($value)) {
\r
595 if ($this->convertCharSet) {
\r
596 $value=utf8_encode($value);
\r
597 $result=htmlspecialchars($value, ENT_COMPAT, 'UTF-8');
\r
599 $result=htmlspecialchars($value);
\r
602 if ($this->fmt=="html" && $result=="") $result=" ";
\r
603 return "<td>".$result."</td>";
\r
606 // for the root node, parentID should "" (empty string)
\r
607 // containerORleaf: L/zero (leaf), C/non-zero (container)
\r
608 // selectable: 0->not selectable, 1->selectable
\r
609 function WriteTreeRow($parentID, $ID, $description, $containerORleaf, $selectable) {
\r
611 echo $this->XmlStringCell($parentID);
\r
612 echo $this->XmlStringCell($ID);
\r
613 echo $this->XmlStringCell($description);
\r
614 echo $this->XmlStringCell($containerORleaf);
\r
615 echo $this->XmlStringCell($selectable);
\r