Query.class.php 4.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130
  1. <?php
  2. require_once(__DIR__.SLASH.'..'.SLASH.'WidgetElement.class.php');
  3. class Query extends WidgetElement{
  4. public $id,$connection,$sql;
  5. public $TABLE_NAME = 'statistic_query';
  6. public $javascript = 'function(){stats_element_init("query");}';
  7. public $icon = 'fas fa-database';
  8. public $typeLabel = 'Requête';
  9. function __construct(){
  10. parent::__construct();
  11. $this->fields['connection'] = 'int';
  12. $this->fields['sql'] = 'longstring';
  13. $this->fieldMapping = $this->field_mapping($this->fields);
  14. }
  15. function editor(){
  16. $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()">';
  17. require_once (__DIR__.SLASH.'..'.SLASH.'Connection.class.php');
  18. foreach(Connection::loadAll() as $connection):
  19. $html .= '<option value="'.$connection->id.'" '.($this->connection==$connection->id ?'selected="selected"':'' ).' >'.$connection->label.'</option>';
  20. endforeach;
  21. $html .= '</select>
  22. <small><a id="database-add" target="_blank" href="setting.php?section=statistic"> Ajouter une base</a></small>
  23. <div id="server-status" class="d-inline-block float-right"></div>
  24. <div class="clear"></div>
  25. <hr>
  26. <div class="row workspace">
  27. <div class="col-md-7 query-area">
  28. <label>Requete SQL</label> <small class="text-muted"> - utilisez [[attribut]] pour acceder à une donnée ou {{attribut}} pour acceder à un filtre</small>
  29. <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>
  30. <div class="clear"></div>
  31. <textarea id="query">'.$this->sql.'</textarea>
  32. </div>
  33. <div class="col-md-5 tools-area">
  34. <ul class="nav nav-tabs" noPrint role="tablist">
  35. <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>
  36. <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>
  37. </ul>
  38. <div class="tab-content">
  39. <div class="tab-pane fade show active in active show" id="tab-results" role="tabpanel" aria-labelledby="tab-results">
  40. <div id="editorOutput"></div>
  41. </div>
  42. <div class="tab-pane fade" id="tab-tables" role="tabpanel" aria-labelledby="tab-tables">
  43. <div id="stats-query-tables">
  44. <label class="text-muted">Recherche</label> <input type="text" class="form-control-sm" onkeyup="stats_element_query_table_filter(this)">
  45. <ul class="stats-widget-element">
  46. <li data-table="{{table}}" class="table-tree hidden" onclick="stats_query_add_table(this)"><span>{{table}}</span>
  47. <div class="right" title="Voir les colonnes" onclick="stats_search_query_columns(this,event)"><i class="fas fa-columns"></i></div>
  48. <div class="clear"></div>
  49. <ul class="hidden"></ul>
  50. </li>
  51. </ul>
  52. </div>
  53. </div>
  54. </div>
  55. </div>
  56. </div>';
  57. return $html;
  58. }
  59. function preview($data = array(),$filters = array()){
  60. $response = array('data'=>array());
  61. require_once (__DIR__.SLASH.'..'.SLASH.'Connection.class.php');
  62. if(isset($this->connection) && $this->connection!=false) {
  63. $connection = Connection::getById($this->connection);
  64. if($connection!=false && !empty($connection->handler) ){
  65. try{
  66. $pdo = $connection->getPdo();
  67. if(!isset($pdo)) throw new Exception("Connexion à la base impossible");
  68. $query = self::template($this->sql,$filters,true);
  69. $query = preg_replace('/\[\[([^\]]*)\]\]/iU','{{$1}}', $query);
  70. $query = self::template($query,$data,true);
  71. $query = html_entity_decode($query,ENT_QUOTES);
  72. $response['meta'] = array('Requete finale' => $query);
  73. $results = $pdo->prepare($query);
  74. $results->execute(array());
  75. foreach ($results->fetchAll(PDO::FETCH_ASSOC) as $element){
  76. foreach($element as $index => $row){
  77. if($connection->handler == 'Oracle'){
  78. $element[$index] =iconv("Windows-1254", "UTF-8", html_entity_decode($row, ENT_QUOTES, 'UTF-8'));
  79. }else{
  80. $element[$index] =html_entity_decode($row, ENT_QUOTES, 'UTF-8');
  81. }
  82. }
  83. $response['data'][] = $element;
  84. }
  85. } catch(Exception $e) {
  86. $error = utf8_encode($e->getMessage());
  87. if(isset($pdo)) $error .= json_encode($pdo->errorInfo());
  88. if(isset($query)) $error .= '<hr/>'.$query;
  89. $error = str_replace(
  90. 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]'),
  91. array(' ...','Mauvaise Syntaxe'),$error);
  92. throw new Exception("SQL ".PHP_EOL.$error);
  93. }
  94. }
  95. }
  96. return $response;
  97. }
  98. }
  99. ?>