kiss/plugin/statistic/element/Query.class.php

<?php
require_once(__DIR__.SLASH.'..'.SLASH.'WidgetElement.class.php');
class Query extends WidgetElement{
	public $id,$connection,$sql;
	public $TABLE_NAME = 'statistic_query';
	public $javascript = 'function(){stats_element_init("query");}';
	public $icon = 'fas fa-database';
	public $typeLabel = 'Requête';

	function __construct(){
		parent::__construct();
		$this->fields['connection'] = 'int';
		$this->fields['sql'] = 'longstring';
		$this->fieldMapping = $this->field_mapping($this->fields);
	}


	function editor(){
		$html = '<label><i class="fas fa-database"></i> Base de données : </label><select id="connection" class="form-control-sm" onchange="stats_save_widget_connection()">';
		require_once (__DIR__.SLASH.'..'.SLASH.'Connection.class.php');

		foreach(Connection::loadAll() as $connection):
			$html .= '<option value="'.$connection->id.'" '.($this->connection==$connection->id ?'selected="selected"':'' ).' >'.$connection->label.'</option>';
		endforeach;

		$html .= '</select>
				<small><a id="database-add" target="_blank" href="setting.php?section=statistic"> Ajouter une base</a></small>
				<div id="server-status" class="d-inline-block float-right"></div>
				<div class="clear"></div>
				<hr>
				<div class="row workspace">
					<div class="col-md-7 query-area">
						<label>Requete SQL</label> <small class="text-muted"> - utilisez [[attribut]] pour acceder à une donnée ou {{attribut}} pour acceder à un filtre</small>
						<div class="prev-custom-query float-right btn btn-small mb-2" onclick="stats_element_preview(this);"><i class="fas fa-play-circle"></i> Exécuter</div>
						<div class="clear"></div>
						<textarea id="query">'.$this->sql.'</textarea>
					</div>
					<div class="col-md-5 tools-area">
						<ul class="nav nav-tabs" noPrint role="tablist">
							<li class="nav-item"><a data-toggle="tab" class="nav-link active" href="#tab-results" aria-controls="tab-results" aria-selected="false">Résultats</a></li>
							<li class="nav-item stats-table-tab"><a data-toggle="tab" class="nav-link" href="#tab-tables" aria-controls="tab-tables" aria-selected="true">Tables</a></li>
						</ul>

						<div class="tab-content">
							<div class="tab-pane fade show active in active show" id="tab-results" role="tabpanel" aria-labelledby="tab-results">
								<div id="editorOutput"></div>
							</div>
							<div class="tab-pane fade" id="tab-tables" role="tabpanel" aria-labelledby="tab-tables">
								<div id="stats-query-tables">
									<label class="text-muted">Recherche</label> <input type="text" class="form-control-sm" onkeyup="stats_element_query_table_filter(this)">
									<ul class="stats-widget-element">
										<li data-table="{{table}}" class="table-tree hidden" onclick="stats_query_add_table(this)"><span>{{table}}</span>
											<div class="right" title="Voir les colonnes" onclick="stats_search_query_columns(this,event)"><i class="fas fa-columns"></i></div>
											<div class="clear"></div>
											<ul class="hidden"></ul>
										</li>
									</ul>
								</div>
							</div>
						</div>
					</div>
				</div>';
		return $html;
	}



	function preview($data = array(),$filters = array()){
		$response = array('data'=>array());

		require_once (__DIR__.SLASH.'..'.SLASH.'Connection.class.php');

		if(isset($this->connection) && $this->connection!=false) {



			$connection = Connection::getById($this->connection);

			if($connection!=false && !empty($connection->handler) ){

				try{
					$pdo = $connection->getPdo();

					if(!isset($pdo)) throw new Exception("Connexion à la base impossible");


					$query = self::template($this->sql,$filters,true);


					$query = preg_replace('/\[\[([^\]]*)\]\]/iU','{{$1}}', $query);
					$query = self::template($query,$data,true);

					$query = html_entity_decode($query,ENT_QUOTES);


					$response['meta'] = array('Requete finale' => $query);
					$results = $pdo->prepare($query);

           			$results->execute(array());

					foreach ($results->fetchAll(PDO::FETCH_ASSOC) as $element){
						foreach($element as $index => $row){
							if($connection->handler == 'Oracle'){
								$element[$index] =iconv("Windows-1254", "UTF-8", html_entity_decode($row, ENT_QUOTES, 'UTF-8'));
							}else{
								$element[$index] =html_entity_decode($row, ENT_QUOTES, 'UTF-8');
							}

						}
						$response['data'][] = $element;
					}

				} catch(Exception $e) {
					$error = utf8_encode($e->getMessage());
					if(isset($pdo)) $error .= json_encode($pdo->errorInfo());
					if(isset($query)) $error .= '<hr/>'.$query;

					$error = str_replace(
						array('Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ','SQLSTATE[42000]'),
						array(' ...','Mauvaise Syntaxe'),$error);

					throw new Exception("SQL ".PHP_EOL.$error);
				}
			}
		}
		return $response;
	}
}

?>