File: as_admintool_sqlqry.php

<?
/**
* @package as_admintool
* @desc as_admintool_sqlqry.php - "SQL query" plugin for as_admintool.php
* @author Alexander Selifonov <as-works@narod.ru>
* @copyright Alexander Selifonov 2007
* @link http://as-works.narod.ru/en/php/
* @version 1.003.024
* modified 20.03.2008 (dd.mm.yyyy)
================================================================================
*/

require_once('as_dbutils.php'); // DB access wrapper class

define('ASADM_SQLQUERY','sqlqry'); // ID type for this module
if(!defined('ASADM_MAXRECORDS')) define('ASADM_MAXRECORDS',800); // SQL query: maximal records returned
define('ASADM_QRYPARAM',4); // number of parameter fields shown for SQL query

define('ASADM_SQRYWHEIGHT',100); // SQL query textarea height (px)

# mandatory string - registering plugin
# example: $as_admt_plugins['unique_id'] = array('html-drawing_func','executing_func');
CAsAdminTool::RegisterPlugin('sqlqry','ASAdmt_sql_Form','ASAdmt_sql_exec');

// interface localization here !
if(empty($as_iface['predef-qry'])) $as_iface['predef-qry'] ='pre-defined queries...';
if(empty($as_iface['execqry'])) $as_iface['execqry'] ='Execute Query';
if(empty($as_iface['explainqry'])) $as_iface['explainqry'] ='Explain Query';
if(empty($as_iface['qryresult'])) $as_iface['qryresult'] ='Execution result';

if(empty($as_iface['msg_qrydone'])) $as_iface['msg_qrydone'] ='Query executed';
if(empty($as_iface['msg_qryerror'])) $as_iface['msg_qryerror'] ='Query error';

# $as_adm_qryaccess : SQL runing access:
# 0 - only pre-defined queries allowed (sqltext field is hidden), no 'explain query' button
# 1 - user can write queries, but only reading data, UPDATING queries will be denied
# 2 - full access, all queries executed
if(!isset($as_adm_qryaccess)) $as_adm_qryaccess = 0;

# ASAdmt_sql_Form - function for drawing "SQL query" screen page
# first par $pginfo is array: [0] - pageid, [1],[2]-max page size (width,height)
function ASAdmt_sql_Form($pginfo,$qrylist='',$par2=false,$par3=false) {
  global $as_iface, $as_cssclass, $as_adm_qryaccess;
  $pageid=isset($pginfo[0])? $pginfo[0]: 0;
  $lwidth = isset($pginfo[1])? $pginfo[1]: 800;
  $lheight = isset($pginfo[2])? $pginfo[2]: 600;
  $rest_h = $lheight - 115;
  $self = $_SERVER['PHP_SELF'];
  static $sql_js_drawn = false;
  $dbname = is_string($par2)? $par2 : '';
  if(!$sql_js_drawn) { #<3> draw only once !!!
    $sql_js_drawn = true;
     ?>
<script language='javascript'>
var as_admt_stdsqls = []; // array for predefined queries
var as_admt_subpars = [];
function SqlQry_ChangeStdQry(pageid,obj) {
  var fm = asGetObj("as_admt_sqlform_"+pageid);
  var iqry = obj.selectedIndex;
  if(iqry <=0) fm.sqltext.value = 'select * from';
  else fm.sqltext.value = as_admt_stdsqls[pageid][iqry][0].replace(/{CRLF}/g,"\r\n");
  for(ik=1; ik<=<?= ASADM_QRYPARAM ?>; ik++) {
    asGetObj("sqprm_"+pageid+"_"+ik).innerHTML= ((iqry<=0 || as_admt_stdsqls[pageid][iqry][ik]==undefined)? ('&amp;P'+ik) : as_admt_stdsqls[pageid][iqry][ik]);
  }
  if(typeof(as_admt_subpars[pageid][iqry])!='undefined') { fm.subpars.value=as_admt_subpars[pageid][iqry]; }
}
var ajax_sqlqrybusy = false;

function Admt_RunSqlQry(pageid,bexplain) {
  if (ajax_sqlqrybusy) return false;
  fm = window.asGetObj("as_admt_sqlform_"+pageid);
//  alert('KT-form element : '+fm.name);
  if(fm.sqltext.value=='') { alert('empty sqltext'); return false; }
  var xmlreq = NewXMLHttpRequest();
  if(!xmlreq) { return false; }
  ajax_sqlqrybusy = true;
  xmlreq.onreadystatechange= function() { //<3>
    if (xmlreq.readyState == 4) { //<3A>
//      alert(xmlreq.responseText); //debug
      var spl = xmlreq.responseText.split("{|}");
      delete xmlreq;
      ajax_sqlqrybusy = false;
      if(spl.length < 2) {
        asGetObj("sqlresult_"+pageid).innerHTML ='<?=$as_iface['msg_wrongreply']?> '+spl[0];
      }
      else {
        asGetObj("sqlresult_"+pageid).innerHTML = spl[1];
      } //<4>
    } //<3A>
  } //<3>

  xmlreq.open('POST','<?=$self?>',true);
  xmlreq.setRequestHeader("Content-Type", postcont);
  params = 'adm_action_type=sqlqry&pageid=' + pageid + '&'+ComputeParamString('as_admt_sqlform_'+pageid);
  if(bexplain==1) params += '&b_explain=1';
  xmlreq.send(params);
  asGetObj("sqlresult_"+pageid).innerHTML = "<?=$as_iface['msg_waiting']?>";
  return false;
}

</script>
<?
  } #<3>
  $stdsqls = array();
  if(is_array($qrylist)) $stdsqls = $qrylist;
  elseif(is_file($qrylist)) {
    $tlst = file($qrylist);
    foreach($tlst as $strk) {
      $strk = trim($strk);
      if($strk=='' || $strk[0]=='#') continue;
      $tval = explode('|',$strk);
      if(count($tval)<2) continue;
      // if(!empty($tval[1]))
      $stdsqls[] = $tval; //[0] = $tval[1];
    }
  }
  if(count($stdsqls)>0) {
   echo "<script language='javascript'>\n as_admt_stdsqls[$pageid] = [];\n as_admt_subpars[$pageid] = [];\n";
   $km=1;
   for($kk=0; $kk<count($stdsqls); $kk++) {
     $key = $stdsqls[$kk][0];
     $subpars = '';
     if(strlen($stdsqls[$kk][1])>1) {
       $allval = "\"{$stdsqls[$kk][1]}\"";
       for($nn=2;$nn<=ASADM_QRYPARAM+1;$nn++) {
         if(isset($stdsqls[$kk][$nn]) ) {
           if($stdsqls[$kk][$nn][0]==='#') $subpars .= ($subpars===''? '':'|').$stdsqls[$kk][$nn];
           else $allval .= ",\"{$stdsqls[$kk][$nn]}\"";
         }
       }
       echo " as_admt_stdsqls[$pageid][$km] = [$allval];\n as_admt_subpars[$pageid][$km] = \"$subpars\";\n";
       $km++;
     }
   }
   echo "</script>\n";
  }
?>
<table id='asadt_tbl_<?=$pageid?>'>
<tr><form name='as_admt_sqlform_<?=$pageid?>'><input type='hidden' name='subpars' value='' />
<!--td><?=$as_iface['parameters']?> :</td></tr-->
<tr>
 <?
  if(!empty($dbname)) echo "<input type=hidden name='_dbname_' value='$dbname'>";
  if(count($stdsqls)>0) {
    $rest_h -=40;
    echo "<tr><td colspan=4>{$as_iface['predef-qry']}<br><SELECT name='stdqry' style='width:200' onChange='SqlQry_ChangeStdQry($pageid,this)'>
   <OPTION value='0'>{$as_iface['predef-qry']}</OPTION>";
   for($kk=0; $kk<count($stdsqls); $kk++) /* as $kname=>$kval)*/ {
     $kname = $stdsqls[$kk][0];
     echo ( (strlen($stdsqls[$kk][1])>1)? "<OPTION value='$kname'>$kname</OPTION>" : "<OPTGROUP label='$kname'>");
   }
   echo "</SELECT></td></tr>";
  }
  for($kkp=1; $kkp<=ASADM_QRYPARAM; $kkp++) {
    if($kkp>4 && ($kkp % 5 ==1)) { $rest_h -=36; echo "</tr><tr>"; } // NN parameter per line
    echo "<td><span id='sqprm_{$pageid}_{$kkp}'> &amp;P{$kkp}</span><br><input type='TEXT' name='qparm{$kkp}' class='{$as_cssclass['textfield']}' style='width:120'></td>\n";
  }

  $attrib = ($as_adm_qryaccess>=1)? '':'READONLY';
  // with $as_adm_qryaccess=0 user won't even see SQL query text - just parameter fields
  if($as_adm_qryaccess>0) {
    $rest_h -=ASADM_SQRYWHEIGHT;
    $qryfield = "<tr><td><textarea name='sqltext' class='ibox' style='width:100%; Height=".ASADM_SQRYWHEIGHT."' {$attrib}>select * from</textarea></td></tr>";
  }
  else {
    $qryfield = "<input type='hidden' name='sqltext' value=''>";
  }
?>
</tr></table>
<div align=center>
<table width='98%'>
<?=$qryfield?>
<tr><td><button class='button' name='runsql' onClick='Admt_RunSqlQry(<?=$pageid?>);return false'><?=$as_iface['execqry']?></button>
<?
  if($as_adm_qryaccess>=1) { ?>
&nbsp; <button class='button' name='expsql' onClick='Admt_RunSqlQry(<?=$pageid?>,1);return false'><?=$as_iface['explainqry']?></button>
<? } ?>
</td></tr>
<tr><td><?=$as_iface['qryresult']?></td></tr></form>
<tr><td><div id='sqlresult_<?=$pageid?>' class='<?=$as_cssclass['resultarea']?>' style='overflow:auto; height:<?=$rest_h?>px; width:<?=$lwidth-20?>px;'>&nbsp;</div></td></tr>
</tr>
</table>
</div>
<?
}

// # ASAdmt_sql_exec - function for executing query and returning result through AJAX
function ASAdmt_sql_exec($parms) {
  global $as_iface,$as_cssclass, $as_adm_qryaccess, $as_dbengine;
  $pageid = isset($parms['pageid'])? $parms['pageid'] : '1';
  if($as_adm_qryaccess<2) {
    $qarr = explode(' ',trim($parms['sqltext']));
    $first = strtolower($qarr[0]);
    if(!in_array($first, array('select','show','desc','describe','explain'))) { return "$pageid{|}UPDATES NOT ALLOWED ! ($first - operator denied or unknown)"; }
  }
  $subpars = empty($parms['subpars'])? '': explode('|',$parms['subpars']); // additional parameters: "href columns" etc.
  $s_from = array();
  $s_to = array();
  $dbname = isset($parms['_dbname_']) ? $parms['_dbname_']: '';
  if(strlen($dbname)) { $seldb = $as_dbengine->select_db($dbname); }
  for($kk=1 ; $kk<=ASADM_QRYPARAM; $kk++) { if(isset($parms['qparm'.$kk])) { $s_from[] = '&P'.$kk; $s_to[]=$parms['qparm'.$kk]; } }
  $sqry = isset($parms['sqltext'])? $parms['sqltext'] : '';
  $sqry = str_replace($s_from, $s_to, $sqry);
  $sqry = trim(stripslashes($sqry));
  if(empty($sqry)) { return $ret; }
  $ret = "$pageid{|}"; # <table _width='900px' border=0 cellspacing=0 cellpadding=0><tr class='head' style='text-align:left'><td><b>$sqry</b></td></tr></table>\n";
  $qrylist = explode("/\r",$sqry);
  $explain = empty($parms['b_explain'])?false:true;
  foreach($qrylist as $no=>$oneqry) {
    $ret .= ASAdmt_RunOneSql($oneqry,$explain).'<br />';
  }
  return $ret;
}
/**
* @desc ASAdmt_RunOneSql performs one SQL query and returns <table> with record values or error text
**/
function ASAdmt_RunOneSql($querytext,$explain=false) {
  global $as_iface,$as_cssclass, $as_adm_qryaccess, $as_dbengine;
  $ret = '';
  $result = ($explain)? $as_dbengine->sql_explain($querytext) : $as_dbengine->sql_query($querytext);
  if($result) { //<2>
    if(is_resource($result)) { //<3> // show result recordset
        $ret .="<table border=0 cellspacing=1 >\n";
        $header = 0;
        $ii=0;
        while (($row = $as_dbengine->fetch_assoc($result)) && (ASADM_MAXRECORDS==0 || $ii<=ASADM_MAXRECORDS))
        { //<4>
           $values = array_values($row); // I'll need index-based values for HREF column composing
           if($header < 1)
           { //<5>
              $header = 1;
              $ret .="<tr>"; // class='{$as_cssclass['trowhead']}'
              foreach($row as $col_name=>$col_value) {
                if(is_string($col_name)) $ret .="<td class='{$as_cssclass['tdhead']}'>$col_name</td>";
              }
              if(is_array($subpars)) for($ipar=0;$ipar<count($subpars);$ipar++) $ret.="<td class='{$as_cssclass['tdhead']}'>&nbsp;</td>";
              $ret .='</tr>';
              reset($row); // back to first element !
           } //<5>
           $ii++;
           $classname = ($ii % 2) ? $as_cssclass['trowodd']:$as_cssclass['troweven'];
           $ret .= "\n<tr class='$classname'>";
           foreach($row as $col_name=>$col_value)
             if(is_string($col_name)) $ret .= "<td>$col_value</td>";

             if(is_array($subpars)) for($ipar=0;$ipar<count($subpars);$ipar++) {
                 $onepar = explode('^',$subpars[$ipar]);
                 $colvalue = '';
                 switch($onepar[0]) {
                   case '#HREF': $colvalue=@str_replace('{ID}',$values[$onepar[1]],$onepar[2]); break;
                   default: $colvalue=$onepar[0]; break;
                 }
                 $ret .="<td>$colvalue</td>";
             }

           $ret .='</tr>';
        } //<4>
        $as_dbengine->free_result($result);
        $ret .="</table></div>\n";
    }//<3>
    else
       $ret .= $as_iface['msg_qrydone']. ' (rows affected: '.$as_dbengine->affected_rows().')';
  }//<2>
  else $ret .= $as_iface['msg_qryerror'].' :'.$as_dbengine->sql_error();
  return $ret;
}
?>