kenken999/php
0
1<?php2ini_set("display_errors","1");3ini_set("display_startup_errors","1");4include("include/dbcommon.php");5header("Expires: Thu, 01 Jan 1970 00:00:01 GMT");6 7include("include/reportfunctions.php");8 9 10include('include/xtempl.php');11$xt = new Xtempl();12 13$gSettings = null;14 15$res_head = "";16$res_body = "";17$res_error = "";18if((!is_wr_custom() || !isWRAdmin() || !postvalue("sql")) && postvalue("sql")!="add")19{20 $type = (postvalue('type') == "webcharts") ? "webcharts" : "webreports";21 $b_includes="<script>";22 $b_includes.='23 $(document).ready(function(){24 $("#sql_button").hide();25 ';26 $b_includes.='27 $("#sql_name").hide();28 $("#check_button").hide();29 $("#sql_textarea").attr("readonly","readonly");30 });31 ';32 $b_includes.="</script>";33 $xt->assign("b_includes", $b_includes);34 if(is_wr_project()) 35 {36 if($type=="webreports")37 $arr_tables = getReportTablesList();38 else39 $arr_tables = getChartTablesList();40 foreach ($arr_tables as $tbl) 41 include("include/" . GetTableURL($tbl) . "_variables.php");42 $gSettings = new ProjectSettings($arr_tables[0], PAGE_REPORT);43 }44 else 45 {46 global $strTableName;47 $gSettings = new ProjectSettings($strTableName, PAGE_REPORT);48 }49 50 if(!is_wr_project())51 {52 $sql_query_display = $_SESSION[$type]['sql'].$_SESSION[$type]['where'].$_SESSION[$type]['group_by'].$_SESSION[$type]['order_by']; 53 if(strlen($_SESSION[$type]['sql_preview']))54 $sql_query = $_SESSION[$type]['sql_preview'];55 else56 $sql_query = $_SESSION[$type]['sql'];57 58 if(strlen($_SESSION[$type]['order_by_preview']))59 $order_by = $_SESSION[$type]['order_by_preview'];60 else61 $order_by = $_SESSION[$type]['order_by'];62 63 $sql_query .= $_SESSION[$type]['where'].$_SESSION[$type]['group_by'].$order_by;64 $sql_query_display=$sql_query;65 }66 else67 {68 $gQuery = $gSettings->getSQLQuery();69 $gstrSQL = $gQuery->gSQLWhere("");70 $sql_query = str_replace("select ","\n"."select ",$gstrSQL);71 $sql_query = str_replace(" from ","\n"."from ",$sql_query);72 $sql_query = str_replace(" inner ","\n"."inner ",$sql_query);73 $sql_query = str_replace(" right ","\n"."right ",$sql_query);74 $sql_query = str_replace(" full ","\n"."full ",$sql_query);75 $sql_query = str_replace(" where ","\n"."where ",$sql_query);76 $sql_query = str_replace(" order by ","\n"."order by ",$sql_query);77 $sql_query = str_replace(" group by ","\n"."group by ",$sql_query);78 $sql_query = str_replace("SELECT ","\n"."SELECT ",$sql_query);79 $sql_query = str_replace(" FROM ","\n"."FROM ",$sql_query);80 $sql_query = str_replace(" INNER ","\n"."INNER ",$sql_query);81 $sql_query = str_replace("RIGHT ","\n"."RIGHT ",$sql_query);82 $sql_query = str_replace(" FULL ","\n"."FULL ",$sql_query);83 $sql_query = str_replace(" WHERE ","\n"."WHERE ",$sql_query);84 $sql_query = str_replace(" ORDER BY ","\n"."ORDER BY ",$sql_query);85 $sql_query = str_replace(" GROUP BY ","\n"."GROUP BY ",$sql_query);86 $sql_query_display = $sql_query;87 }88 89 90 $xt->assign("sql_query", runner_htmlspecialchars($sql_query_display));91}92else93{94 $_SESSION["webobject"]["table_type"]="custom";95 $page = (postvalue('type') == "webcharts") ? GetTableLink("webchart0") : GetTableLink("webreport0");96 $sql_query_display=$_SESSION["customSQL"];97 $sql_query=$_SESSION["customSQL"];98 99 if(postvalue("sql")=="add")100 {101 102 // #9875 It's expected that webreport_sql table belongs to the webreport db connection103 $_connection = $cman->getForWebReports();104 105 $sname = "Query";106 $prefix = 0;107 while(true)108 {109 if( $prefix > 0 )110 $sname = "Query_".$prefix;111 112 $sql = "select count(*) from ".$_connection->addTableWrappers("webreport_sql")." where ".$_connection->addFieldWrappers("sqlname")."=".$_connection->prepareString($sname);113 $data = $_connection->query($sql)->fetchNumeric();114 if( $data[0] > 0 ) 115 $prefix++;116 else117 break;118 }119 120 $_SESSION["idSQL"]="";121 $_SESSION["nameSQL"] = $sname;122 $_SESSION["customSQL"] = "";123 $sql_query_display = "";124 $sql_query = "";125 }126 elseif(postvalue("sql")=="makesql")127 {128 $sql_query_display=postvalue("output");129 $sql_query=postvalue("output");130 }131 132 $b_includes="<script>";133 134 $b_includes .= '135 $(document).ready(function(){';136 $b_includes .= alertDialog();137 $b_includes .= '138 $("#cancel_sql").click(function(){139 window.parent.$.fancybox.close();140 });141 $("#save_sql").click(function(){142 $.ajax({143 type: "POST",144 url: "'.GetTableLink("save-admin").'",145 data: {146 name: "sqledit",147 sqlcontent: $("#sql_textarea").val(),148 namesql: $("#sql_name").val(),149 rnd: (new Date().getTime())150 },151 success: function(msg){152 if ( msg == "OK" ) {153 window.parent.$.fancybox.close();';154 if(!postvalue("page"))155 $b_includes .= 'if(!$("#go_permiss").attr("checked"))156 window.parent.location.href="'.GetTableLink("webreport_sql").'?name="+$("#sql_name").val();157 else158 window.parent.location.href="'.GetTableLink("webreport_admin").'?username='159 .rawurlencode( Security::getUserName() ).160 '&queryname="+$("#sql_name").val();';161 else162 $b_includes .= 'window.parent.location.href="'.$page.'?sqlname="+$("#sql_name").val();';163 $b_includes .= '} else {164 $("#alert").html("<p>"+msg+"</p>")165 .dialog("option", "buttons", {"OK": function() { $(this).dialog("close");}})166 .dialog("open");167 }168 }169 });170 });171 $("#view_sql").click(function(){172 $("#table-container").html("");173 $.ajax({174 type: "POST",175 url: "'.GetTableLink("web_query").'",176 data: {177 sql: "makesql",178 output: $("#sql_textarea").val(),179 rnd: (new Date().getTime())180 },181 success: function(msg)182 {183 $("#table-container").html(msg);184 }185 });186 });';187 if(postvalue("sql")=="result")188 $b_includes .= '$("#view_sql").click();';189 if(!postvalue("check"))190 $b_includes.='$("#check_button").hide();'; 191 $b_includes .= '});';192 193 $b_includes.='$("#sql_button").show();';194 $b_includes.="</script>";195 $xt->assign("b_includes", $b_includes);196 $xt->assign("sql_name", $_SESSION["nameSQL"]);197}198$xt->assign("wr_pagestylepath", $wr_pagestylepath);199$xt->assign("sql_query", runner_htmlspecialchars($sql_query_display));200$sql_query_all=$sql_query;201$_SESSION["object_sql"]=$sql_query;202 203$conn = $cman->getForWebReports();204if(is_wr_project())205 $conn = $cman->byTable($arr_tables[0]);206 207 208if(!IsStoredProcedure($sql_query))209{210 if( $conn->dbType == nDATABASE_MSSQLServer )211 {212 $pos=strrpos(strtoupper($sql_query_all),"ORDER BY");213 if($pos)214 $sql_query_all=substr($sql_query_all,0,$pos);215 }216 if($sql_query)217 {218 if( $conn->dbType == nDATABASE_MySQL )219 {220 if(!strpos(strtolower($sql_query)," limit "))221 $sql_query .= " LIMIT 50";222 }223 elseif( $conn->dbType == nDATABASE_MSSQLServer || $conn->dbType == nDATABASE_Access )224 {225 if(!strpos(strtolower($sql_query)," distinct "))226 {227 if(!strpos(strtolower($sql_query)," top "))228 $sql_query = str_replace("SELECT", "SELECT TOP 50", strtoupper($sql_query));229 }230 else231 {232 if(!strpos(strtolower($sql_query)," top "))233 $sql_query = str_replace("SELECT DISTINCT", "SELECT DISTINCT TOP 50", strtoupper($sql_query));234 }235 }236 elseif( $conn->dbType == nDATABASE_Oracle )237 {238 //239 }240 elseif( $conn->dbType == nDATABASE_PostgreSQL )241 {242 //243 }244 }245}246$num_rows=50;247$errstr="";248 249if($sql_query) {250 $qResult=$conn->query($sql_query);251 $data = $qResult->fetchAssoc();252}253 254if(!$data || !$sql_query)255{ 256 if($conn->dbType==nDATABASE_Oracle && !$sql_query)257 $errstr="Query was empty";258 if(postvalue("sql")!="makesql")259 {260 $xt->assign("sql_error_block", true);261 $xt->assign("sql_error", $errstr);262 }263 else264 $result=$errstr;265}266else267{268 $firstTime=true;269 $i=0;270 $fields_type=array();271 if(is_wr_custom())272 $fields_type=WRGetAllCustomFieldType();273 while($i<$num_rows && $data)274 {275 $i++;276 277 if ($firstTime) {278 foreach( array_keys($data) as $key ) 279 {280 if(is_wr_project())281 {282 if(IsBinaryType($gSettings->getFieldType($key)))283 continue;284 }285 elseif(is_wr_custom())286 {287 if(IsBinaryType($fields_type[$key]))288 continue;289 }290 if($gSettings)291 $gKey = $gSettings->label($key);292 else293 $gKey = $key;294 295 $res_head .= "<th>" . runner_htmlspecialchars($gKey) . "</th>"; 296 }297 }298 $res_body .= '<tr>';299 foreach( $data as $fld=>$val ) 300 {301 if(is_wr_project())302 {303 if(IsBinaryType($gSettings->getFieldType($fld)))304 continue;305 }306 elseif(is_wr_custom())307 {308 if(IsBinaryType($fields_type[$fld]))309 continue;310 }311 elseif(is_array($val))312 {313 $res_body .= '<td><span></span></td>';314 continue;315 }316 $s=$val;317 if(strlen($s)>100)318 $s=substr($s,0,100);319 $res_body .= '<td><span>' . runner_htmlspecialchars($s) . '</span></td>';320 }321 $res_body .= '</tr>';322 $data = $qResult->fetchAssoc();323 $firstTime=false;324 }325 if(!IsStoredProcedure($sql_query_all))326 {327 if($conn->dbType != nDATABASE_Oracle)328 $rs=$conn->query("select count(*) from (".$sql_query_all.") as t")->fetchNumeric();329 else330 $rs=$conn->query("select count(*) from (".$sql_query_all.")")->fetchNumeric();331 $total_rows=0;332 if($data=$rs)333 $total_rows=$data[0];334 }335 if($i>0)336 {337 if(!IsStoredProcedure($sql_query))338 {339 if(postvalue("sql")=="makesql")340 {341 $result="<div><b>".$total_rows."</b> records";342 if($total_rows>50)343 $result.=" (displaying first 50)";344 $result.="</div>";345 $result.="<table class=\"sql_result\" cellpadding=\"1\" cellspacing=\"1\" border=\"0\">";346 $result.="<tr><thead><tr>".$res_head."</tr></thead>";347 $result.="<tbody>".$res_body."</tbody>";348 $result.="</tr></table>";349 }350 else351 {352 $xt->assign("res_head", $res_head);353 $xt->assign("res_body", $res_body);354 $xt->assign("sql_result_block", true);355 $xt->assign("total_count", $total_rows);356 if($total_rows>50)357 $xt->assign("first_rec", true);358 }359 }360 else361 {362 if(postvalue("sql")=="makesql")363 {364 $result="<div>";365 if($i<50)366 $result.="<b>".$i."</b> records";367 else368 $result.="Displaying first 50 records";369 $result.="</div>";370 $result.="<table class=\"sql_result\" cellpadding=\"1\" cellspacing=\"1\" border=\"0\">";371 $result.="<tr><thead><tr>".$res_head."</tr></thead>";372 $result.="<tbody>".$res_body."</tbody>";373 $result.="</tr></table>";374 }375 else376 {377 $total_rows=$i;378 $xt->assign("res_head", $res_head);379 $xt->assign("res_body", $res_body);380 $xt->assign("sql_result_block", true);381 $xt->assign("total_count", $total_rows);382 }383 }384 } 385 else 386 {387 $xt->assign("sql_message_block", true);388 $result="<p>No records to display</p>";389 }390}391if(postvalue("sql")=="makesql")392{393 echo $result;394 exit();395}396else397 $templatefile = GetTemplateName("", "web_query");398$xt->display($templatefile);399?>400