|
1
|
<?php
|
|
2
|
|
|
3
|
require_once($help_functions);
|
|
4
|
require_once($model_logger.'/Logger.php');
|
|
5
|
Logger::configure($model_logger.'/log4php.xml');
|
|
6
|
require_once("paths.php");
|
|
7
|
require "./predis/autoload.php";
|
|
8
|
|
|
9
|
class MYDB {
|
|
10
|
|
|
11
|
private $db;
|
|
12
|
public $stmts = array();
|
|
13
|
private $schema;
|
|
14
|
private $facts = array();
|
|
15
|
private $metadata = array();
|
|
16
|
private $dimensions = array();
|
|
17
|
private $log;
|
|
18
|
private $local_log = array();
|
|
19
|
private $schema_file;
|
|
20
|
|
|
21
|
private $cache = null;
|
|
22
|
|
|
23
|
//-------------------------------------------------------------------------------------\\
|
|
24
|
//-------------------------------------------------------------------------------------\\
|
|
25
|
//------------------------------CONSTRUCT, SET UP AND FUNCTION-------------------------\\
|
|
26
|
//-------------------------------------------------------------------------------------\\
|
|
27
|
//-------------------------------------------------------------------------------------\\
|
|
28
|
function __construct() {
|
|
29
|
|
|
30
|
global $redis_host;
|
|
31
|
global $redis_port;
|
|
32
|
global $redis_scheme;
|
|
33
|
|
|
34
|
// this creates a logger named "MYDB"
|
|
35
|
$this->log = Logger::getLogger(__CLASS__);
|
|
36
|
|
|
37
|
if(class_exists("Predis\Client")){
|
|
38
|
|
|
39
|
try {
|
|
40
|
|
|
41
|
Predis\Autoloader::register();
|
|
42
|
$this->cache = new Predis\Client(array(
|
|
43
|
"scheme" => $redis_scheme,
|
|
44
|
"host" => $redis_host,
|
|
45
|
"port" => $redis_port,
|
|
46
|
"persistent" => 1,
|
|
47
|
//todo changed this from 0 to -1 to disable timeouts
|
|
48
|
"read_write_timeout" => -1));
|
|
49
|
|
|
50
|
$this->cache->connect();
|
|
51
|
|
|
52
|
$this->log->info("redis host: ".$redis_host);
|
|
53
|
$this->log->info("redis port: ".$redis_port);
|
|
54
|
|
|
55
|
} catch(Exception $e) {
|
|
56
|
$this->log->error("Error connecting to Redis server: ".$e->getMessage());
|
|
57
|
$this->cache = null;
|
|
58
|
}
|
|
59
|
|
|
60
|
} else {
|
|
61
|
$this->log->info("Class Predis does not exist\n");
|
|
62
|
}
|
|
63
|
}
|
|
64
|
|
|
65
|
// initialises the arrays containing the warehouse schema, via an xml that describes them
|
|
66
|
//TODO: insert checks on whether the data in the xml are correct (get schema from db)
|
|
67
|
function loadSchema($file = null) {
|
|
68
|
if(file_exists($file)) {
|
|
69
|
$json = file_get_contents($file);
|
|
70
|
//$this->log->info('schema of database: '.json_encode((array) simplexml_load_string($json)));
|
|
71
|
$this->schema = json_decode(json_encode((array) simplexml_load_string($json)),1);
|
|
72
|
}
|
|
73
|
else {
|
|
74
|
echo "loadSchema: could not find the xml";
|
|
75
|
$this->log->error('loadSchema: could not find the xml');
|
|
76
|
return -1;
|
|
77
|
}
|
|
78
|
foreach ($this->schema['table'] as $table) {
|
|
79
|
switch ($table['type']) {
|
|
80
|
case 'fact': array_push($this->facts,$table);
|
|
81
|
break;
|
|
82
|
//case 'm': array_push($this->metadata,$table);
|
|
83
|
// break;
|
|
84
|
default: array_push($this->dimensions,$table);
|
|
85
|
break;
|
|
86
|
}
|
|
87
|
}
|
|
88
|
$this->log->info("loaded schema");
|
|
89
|
}
|
|
90
|
|
|
91
|
|
|
92
|
function doConnect($file) {
|
|
93
|
try {
|
|
94
|
switch($GLOBALS['type']){
|
|
95
|
case 'postgres':
|
|
96
|
$this->db = new PDO('pgsql:host='.$GLOBALS['host'].';port=5432;dbname='.$GLOBALS['db_name'].';user='.$GLOBALS['username'].';password='.$GLOBALS['password']);
|
|
97
|
break;
|
|
98
|
case 'sqlite':
|
|
99
|
$this->db = new PDO('sqlite:'.$GLOBALS['head'].$GLOBALS['db_name']);
|
|
100
|
break;
|
|
101
|
default:
|
|
102
|
echo "not known database type\n";
|
|
103
|
break;
|
|
104
|
}
|
|
105
|
} catch(Exception $e){
|
|
106
|
echo "oops..".$e->getMessage()."\n";
|
|
107
|
$this->log->fatal($e->getMessage());
|
|
108
|
return false;
|
|
109
|
}
|
|
110
|
$this->schema_file = $file;
|
|
111
|
$this->loadSchema($file);
|
|
112
|
}
|
|
113
|
|
|
114
|
function doDisconnect($save = false) {
|
|
115
|
if($save) {
|
|
116
|
$fp = fopen($this->schema_file,"w");
|
|
117
|
$data = "<schema>\n".toxml($this->schema,"schema",1)."\n</schema>\n";
|
|
118
|
fwrite($fp,$data);
|
|
119
|
fclose($fp);
|
|
120
|
}
|
|
121
|
$this->db = null;
|
|
122
|
}
|
|
123
|
|
|
124
|
function beginTrans() {
|
|
125
|
$this->db->beginTransaction();
|
|
126
|
}
|
|
127
|
|
|
128
|
function doCommit() {
|
|
129
|
$this->db->commit();
|
|
130
|
}
|
|
131
|
|
|
132
|
//TODO here predis cache is checked and the query is submitted to the DB
|
|
133
|
|
|
134
|
function doQuery($query, $fetchMode=PDO::FETCH_BOTH,$persistent){
|
|
135
|
|
|
136
|
if($this->cache != null){
|
|
137
|
//TODO md5($query) -> REDIS hash set keys
|
|
138
|
$myKey = md5($query);
|
|
139
|
$this->log->debug("Searching for key: ".$myKey);
|
|
140
|
|
|
141
|
if($this->cache->exists($myKey)) {
|
|
142
|
|
|
143
|
$results = $this->cache->hget($myKey, "results");
|
|
144
|
return json_decode($results, true);
|
|
145
|
|
|
146
|
} else {
|
|
147
|
|
|
148
|
$this->log->debug("not found in cache");
|
|
149
|
$results = $this->doQueryNoCache($query, $fetchMode);
|
|
150
|
$this->log->info("adding in cache. key: ".$myKey);
|
|
151
|
|
|
152
|
if($persistent==null||$persistent=='1')
|
|
153
|
{$persistent = "true";}
|
|
154
|
|
|
155
|
if(($this->cache->hmset($myKey, array("query" => $query, "results" => json_encode($results), "persistent" => $persistent, "fetchMode" => $fetchMode))) == false) {
|
|
156
|
$this->log->info("adding key ".$myKey." in cache failed."); //predis
|
|
157
|
}
|
|
158
|
else {
|
|
159
|
$this->cache->save();
|
|
160
|
}
|
|
161
|
return $results;
|
|
162
|
}
|
|
163
|
} else {
|
|
164
|
return $this->doQueryNoCache($query, $fetchMode);
|
|
165
|
}
|
|
166
|
}
|
|
167
|
|
|
168
|
function doQueryNoCache($query, $fetchMode=PDO::FETCH_BOTH) {
|
|
169
|
|
|
170
|
$stmt = $this->db->query($query);
|
|
171
|
if(!$stmt){
|
|
172
|
$arr = $this->db->errorInfo();
|
|
173
|
$this->log->error($arr[2]);
|
|
174
|
return null;
|
|
175
|
}
|
|
176
|
$this->log->debug("***************FETCH MODE : ".$fetchMode);
|
|
177
|
|
|
178
|
return $stmt->fetchAll($fetchMode);
|
|
179
|
}
|
|
180
|
|
|
181
|
function doPrepare($query){
|
|
182
|
$stmt = $this->db->prepare($query);
|
|
183
|
if(!$stmt){
|
|
184
|
$arr = $this->db->errorInfo();
|
|
185
|
$this->log->error($arr[0]." ".$arr[1]." ".$arr[2]);
|
|
186
|
return false;
|
|
187
|
}
|
|
188
|
return $stmt;
|
|
189
|
}
|
|
190
|
|
|
191
|
function doExecute($stmt){
|
|
192
|
if(!$stmt->execute()){
|
|
193
|
$arr = $stmt->errorInfo();
|
|
194
|
$this->log->error($arr[2]);
|
|
195
|
return false;
|
|
196
|
}
|
|
197
|
return $stmt;
|
|
198
|
}
|
|
199
|
|
|
200
|
//-------------------------------------------------------------------------------------\\
|
|
201
|
//-------------------------------------------------------------------------------------\\
|
|
202
|
//------------------------------CHECKS, GETTERS, SETTERS-------------------------------\\
|
|
203
|
//-------------------------------------------------------------------------------------\\
|
|
204
|
//-------------------------------------------------------------------------------------\\
|
|
205
|
|
|
206
|
public function printSchema() {
|
|
207
|
print_r($this->schema);
|
|
208
|
}
|
|
209
|
|
|
210
|
public function getSchema() {
|
|
211
|
if(isset($this->schema))
|
|
212
|
return $this->schema;
|
|
213
|
return null;
|
|
214
|
}
|
|
215
|
|
|
216
|
public function getFacts() {
|
|
217
|
$list = $this->doQuery("select tableName from defaults order by tableorder",3,"false");
|
|
218
|
if($list != null){
|
|
219
|
$sorted = array();
|
|
220
|
if(isset($this->facts)) {
|
|
221
|
for($i=0;$i<count($list);$i++) {
|
|
222
|
foreach($this->facts as $fact){
|
|
223
|
if($fact['name'] == $list[$i][0]){
|
|
224
|
$sorted[] = $fact;
|
|
225
|
break;
|
|
226
|
}
|
|
227
|
}
|
|
228
|
}
|
|
229
|
return $sorted;
|
|
230
|
}
|
|
231
|
}
|
|
232
|
else
|
|
233
|
return $this->facts;
|
|
234
|
}
|
|
235
|
|
|
236
|
public function getDimensions() {
|
|
237
|
if(isset($this->dimensions)) {
|
|
238
|
return $this->dimensions;
|
|
239
|
}
|
|
240
|
return null;
|
|
241
|
}
|
|
242
|
|
|
243
|
public function getFactsNames() {
|
|
244
|
$names = array();
|
|
245
|
if(isset($this->facts)) {
|
|
246
|
foreach($this->facts as $fact) {
|
|
247
|
array_push($names,$fact['name']);
|
|
248
|
}
|
|
249
|
return $names;
|
|
250
|
}
|
|
251
|
return null;
|
|
252
|
}
|
|
253
|
|
|
254
|
function getType($oldType) {
|
|
255
|
switch ($oldType) {
|
|
256
|
case 'int':
|
|
257
|
case 'double':
|
|
258
|
return 'number';
|
|
259
|
break;
|
|
260
|
case 'varchar':
|
|
261
|
case 'datetime':
|
|
262
|
return 'string';
|
|
263
|
break;
|
|
264
|
case 'bool':
|
|
265
|
return 'boolean';
|
|
266
|
break;
|
|
267
|
default:
|
|
268
|
return false;
|
|
269
|
}
|
|
270
|
}
|
|
271
|
|
|
272
|
/*returns an array with the triplet (name,type,view) for each of the keys of the dimension table
|
|
273
|
or false on failure
|
|
274
|
type is either number, string or boolean //? need to add more ?*/
|
|
275
|
function findKeys($table) {
|
|
276
|
$myarray = array();
|
|
277
|
if($table == false) {
|
|
278
|
$this->log->error('findKeys: no table indicated');
|
|
279
|
return false;
|
|
280
|
}
|
|
281
|
if(is_array($table['key'])) {
|
|
282
|
foreach($table['key'] as $key) {
|
|
283
|
$keyInfo = $this->findOneField($table,$key,'attrib');
|
|
284
|
array_push($myarray,$keyInfo);
|
|
285
|
}
|
|
286
|
}
|
|
287
|
else {
|
|
288
|
$keyInfo = $this->findOneField($table,$table['key'],'attrib');
|
|
289
|
array_push($myarray,$keyInfo);
|
|
290
|
}
|
|
291
|
return $myarray;
|
|
292
|
}
|
|
293
|
|
|
294
|
//returns an array with the tetraplet (name,type,view,data) for each of the meas of the fact table or false on failure
|
|
295
|
//type is either number, string or boolean //? need to add more ?
|
|
296
|
function findMeas($table) {
|
|
297
|
$myarray = array();
|
|
298
|
if($table == false) {
|
|
299
|
$this->log->error('findMeas: no table indicated');
|
|
300
|
return false;
|
|
301
|
}
|
|
302
|
if(isset($table['meas'][0])) {
|
|
303
|
foreach($table['meas'] as $meas) {
|
|
304
|
if(isset($meas['view'])) {
|
|
305
|
$view = $meas['view'];
|
|
306
|
}
|
|
307
|
else
|
|
308
|
$view = $meas['name'];
|
|
309
|
array_push($myarray,array($meas['name'],$view,$meas['type'],$meas['data']));
|
|
310
|
}
|
|
311
|
}
|
|
312
|
else {
|
|
313
|
if(isset($table['meas']['view'])) {
|
|
314
|
$view = $meas['view'];
|
|
315
|
}
|
|
316
|
else
|
|
317
|
$view = $meas['name'];
|
|
318
|
array_push($myarray,array($meas['name'],$view,$meas['type'],$meas['data']));
|
|
319
|
}
|
|
320
|
return $myarray;
|
|
321
|
}
|
|
322
|
|
|
323
|
function getDims() {
|
|
324
|
return $this->dimensions;
|
|
325
|
}
|
|
326
|
|
|
327
|
function findOneField($table,$name,$ftype) {
|
|
328
|
$field = search($table[$ftype],'name',$name);
|
|
329
|
if(count($field)!=1) {
|
|
330
|
$this->log->error('error in xml');
|
|
331
|
return false;
|
|
332
|
}
|
|
333
|
|
|
334
|
$type = $this->getType($field[0]['type']);
|
|
335
|
|
|
336
|
if(isset($field[0]['view'])) {
|
|
337
|
$view = $field[0]['view'];
|
|
338
|
}
|
|
339
|
else
|
|
340
|
$view = $field[0]['name'];
|
|
341
|
|
|
342
|
return array($field[0]['name'],$type,$view,$field[0]['data']);
|
|
343
|
|
|
344
|
}
|
|
345
|
|
|
346
|
function getField($table,$name,$ftype) {
|
|
347
|
$field = search($table[$ftype],'name',$name);
|
|
348
|
if(count($field)!=1) {
|
|
349
|
$this->log->error('error in xml');
|
|
350
|
return false;
|
|
351
|
}
|
|
352
|
|
|
353
|
return $field[0];
|
|
354
|
}
|
|
355
|
|
|
356
|
function findTable($name,$type) {
|
|
357
|
if($type == 'dimension') {
|
|
358
|
$all_matching_files = search($this->dimensions,'name',$name);
|
|
359
|
}
|
|
360
|
elseif($type == 'fact') {
|
|
361
|
$all_matching_files = search($this->facts,'name',$name);
|
|
362
|
}
|
|
363
|
else {
|
|
364
|
$this->log->error('findTable: could not recognise type '.$type);
|
|
365
|
return false;
|
|
366
|
}
|
|
367
|
if(count($all_matching_files)==0) {
|
|
368
|
$this->log->error('xml file error: table: '.$name.' not found');
|
|
369
|
return false;
|
|
370
|
}
|
|
371
|
$matching_files = search($all_matching_files,'type',$type);
|
|
372
|
if(count($matching_files)!=1) {
|
|
373
|
$this->log->error('multiple '.$type.'s with name: '.$name);
|
|
374
|
return false;
|
|
375
|
}
|
|
376
|
return $matching_files[0];
|
|
377
|
|
|
378
|
}
|
|
379
|
|
|
380
|
//checks if a fact table with exactly the given dimensions exist
|
|
381
|
function searchWarehousebyDims($dims) {
|
|
382
|
$flag = false;
|
|
383
|
|
|
384
|
foreach($this->facts as $warehouse) {
|
|
385
|
|
|
386
|
//if the number of dims is the same as the given and every dim in the list exists in the given then return the warehouse name
|
|
387
|
if(count($warehouse['dim']) != count($dims)){
|
|
388
|
continue;
|
|
389
|
}
|
|
390
|
foreach($dims as $dim) {
|
|
391
|
$res = search($warehouse['dim'], 'name', $dim);
|
|
392
|
if(count($res) != 0) {
|
|
393
|
$flag = true;
|
|
394
|
}
|
|
395
|
else {
|
|
396
|
$flag = false;
|
|
397
|
break;
|
|
398
|
}
|
|
399
|
}
|
|
400
|
if($flag == true)
|
|
401
|
return $warehouse['name'];
|
|
402
|
}
|
|
403
|
//not found
|
|
404
|
return false;
|
|
405
|
}
|
|
406
|
|
|
407
|
/*check if a name is already in use for a fact table
|
|
408
|
if yes, return false, otherwise, return true*/
|
|
409
|
function checkName($name) {
|
|
410
|
if(findTable($name,"fact") != false) {
|
|
411
|
return true;
|
|
412
|
}
|
|
413
|
return false;
|
|
414
|
}
|
|
415
|
|
|
416
|
/*check if a name is already in use for a dimension table
|
|
417
|
if yes, return true, otherwise, return false*/
|
|
418
|
function checkDim($name) {
|
|
419
|
if(findTable($name,"dimension") != false) {
|
|
420
|
return true;
|
|
421
|
}
|
|
422
|
return false;
|
|
423
|
}
|
|
424
|
|
|
425
|
function findTableType($name) {
|
|
426
|
if($this->findTable($name,"dimension") != false) {
|
|
427
|
return "dimension";
|
|
428
|
}
|
|
429
|
if($this->findTable($name,"fact") != false) {
|
|
430
|
return "fact";
|
|
431
|
}
|
|
432
|
return false;
|
|
433
|
}
|
|
434
|
|
|
435
|
/*check if a list of dims all exist in the schema
|
|
436
|
returns a list with all dims that don't exist or true
|
|
437
|
*/
|
|
438
|
function checkListofDims($dims) {
|
|
439
|
$non_existent = array();
|
|
440
|
$flag = true;
|
|
441
|
foreach($dims as $dim) {
|
|
442
|
if(checkDim($dim) == true){
|
|
443
|
array_push($non_existent,$dim);
|
|
444
|
$flag = false;
|
|
445
|
}
|
|
446
|
}
|
|
447
|
if($flag == true)
|
|
448
|
return true;
|
|
449
|
else
|
|
450
|
return $non_existent;
|
|
451
|
}
|
|
452
|
|
|
453
|
function checkMeas($fact, $meas_name) {
|
|
454
|
//find the fact table
|
|
455
|
//if there is a meas with the same name
|
|
456
|
foreach($this->facts as $table)
|
|
457
|
if($table['name'] == $fact)
|
|
458
|
foreach($table['meas'] as $meas) {
|
|
459
|
|
|
460
|
if($meas== $meas_name)
|
|
461
|
{ return true;
|
|
462
|
}
|
|
463
|
}
|
|
464
|
return false;
|
|
465
|
}
|
|
466
|
|
|
467
|
function getAllDims() {
|
|
468
|
$dimnames = array();
|
|
469
|
foreach($this->dimensions as $dim) {
|
|
470
|
array_push($dimnames,$dim['name']);
|
|
471
|
}
|
|
472
|
return $dimnames;
|
|
473
|
}
|
|
474
|
|
|
475
|
function getDimsFieldsList($tableName) {
|
|
476
|
$fields = array();
|
|
477
|
//find the table
|
|
478
|
$table = $this->findTable($tableName,'dimension');
|
|
479
|
if($table == false) {
|
|
480
|
return false;
|
|
481
|
}
|
|
482
|
foreach($table['attrib'] as $field) {
|
|
483
|
if($field['name'] == 'id')
|
|
484
|
continue;
|
|
485
|
if(isset($field['analysed'])) {
|
|
486
|
$fields = array_merge($fields,$this->getDimsFieldsList($field['analysed']));
|
|
487
|
}
|
|
488
|
else {
|
|
489
|
if(isset($field['view']))
|
|
490
|
$view = $field['view'];
|
|
491
|
else
|
|
492
|
$view = $field['name'];
|
|
493
|
array_push($fields,array($tableName.".".$field['name'],$tableName.".".$view,$field['type'],$field['data']));
|
|
494
|
}
|
|
495
|
}
|
|
496
|
return $fields;
|
|
497
|
}
|
|
498
|
|
|
499
|
function getDimsList($tablename) {
|
|
500
|
$fields = array();
|
|
501
|
//find the factable
|
|
502
|
$table = $this->findTable($tablename,'fact');
|
|
503
|
if($table == false) {
|
|
504
|
return false;
|
|
505
|
}
|
|
506
|
foreach($table['dim'] as $dim) {
|
|
507
|
$temp = $this->getDimsFieldsList($dim['name']);
|
|
508
|
$fields = array_merge($fields, $temp);
|
|
509
|
}
|
|
510
|
return $fields;
|
|
511
|
}
|
|
512
|
|
|
513
|
function getDimsKeys($dimName) {
|
|
514
|
$table = $this->findTable($dimName,'dimension');
|
|
515
|
if($table == false) {
|
|
516
|
return false;
|
|
517
|
}
|
|
518
|
return $table['key'];
|
|
519
|
}
|
|
520
|
|
|
521
|
//-------------------------------------------------------------------------------------\\
|
|
522
|
//-------------------------------------------------------------------------------------\\
|
|
523
|
//----------------------------------DATA READING---------------------------------------\\
|
|
524
|
//-------------------------------------------------------------------------------------\\
|
|
525
|
//-------------------------------------------------------------------------------------\\
|
|
526
|
|
|
527
|
function getMeasMetadata($table) {
|
|
528
|
$query = 'select name, source, sourceUrl, definition, unit from Metadata where inTable="'.$table.'"';
|
|
529
|
$this->log->info("getMeasMetadata -> generated query: ".$query);
|
|
530
|
$res = $this->doQuery($query);
|
|
531
|
if($res == null)
|
|
532
|
return "false";
|
|
533
|
|
|
534
|
return $res;
|
|
535
|
}
|
|
536
|
|
|
537
|
/*
|
|
538
|
gets the name of a fact table and the name of a measurement and returns the minimun, maximum and count distinct values for it
|
|
539
|
*/
|
|
540
|
function measRangeData($facttable, $measurement) {
|
|
541
|
$query = "SELECT min($measurement), max($measurement), count(distinct $measurement) FROM $facttable WHERE $measurement != ''";
|
|
542
|
$this->log->info("generated query: ".$query);
|
|
543
|
$res = $this->doQuery($query);
|
|
544
|
$res=$res[0];
|
|
545
|
return array($res[0],$res[1],$res[2]);
|
|
546
|
}
|
|
547
|
|
|
548
|
//return the minimun, maximum
|
|
549
|
function getRangeData($table, $field) {
|
|
550
|
$query = 'SELECT min("'.$field.'"), max("'.$field.'") FROM "'.$table.'"';
|
|
551
|
if($GLOBALS['type'] == "sqlite")
|
|
552
|
$query .= ' WHERE "'.$field.'" != ""';
|
|
553
|
$this->log->info("generated query: ".$query);
|
|
554
|
$res = $this->doQuery($query);
|
|
555
|
$res = $res[0];
|
|
556
|
return array("min"=>$res[0],"max"=>$res[1]);
|
|
557
|
}
|
|
558
|
|
|
559
|
//return the distinct values
|
|
560
|
function getDistinctValues($table, $field) {
|
|
561
|
$query = 'select distinct "'.$field.'" from "'.$table.'" order by "'.$field.'"';
|
|
562
|
$this->log->info("generated query: ".$query);
|
|
563
|
$res = $this->doQuery($query, PDO::FETCH_NUM);
|
|
564
|
return $res;
|
|
565
|
}
|
|
566
|
|
|
567
|
function getFilterData($table,$field){
|
|
568
|
$flag = false;
|
|
569
|
$myDimTable = $this->findTable($table,'dimension');
|
|
570
|
$myFactTable = $this->findTable($table,'fact');
|
|
571
|
//is it a dim?
|
|
572
|
if($myDimTable != false){
|
|
573
|
//does it have the field?
|
|
574
|
if(!isAssoc($myDimTable['attrib'])){
|
|
575
|
foreach($myDimTable['attrib'] as $attrib){
|
|
576
|
if($attrib['name'] == $field){
|
|
577
|
$myField = $attrib;
|
|
578
|
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
|
|
579
|
}
|
|
580
|
}
|
|
581
|
}
|
|
582
|
else{
|
|
583
|
if($myDimTable['attrib']['name'] == $field){
|
|
584
|
$myField = $myDimTable['attrib'];
|
|
585
|
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
|
|
586
|
}
|
|
587
|
}
|
|
588
|
}
|
|
589
|
if($myFactTable != false){
|
|
590
|
//look in the dims
|
|
591
|
if(!isAssoc($myFactTable['dim'])){
|
|
592
|
foreach($myFactTable['dim'] as $dim) {
|
|
593
|
if($dim['name'] == $field){
|
|
594
|
$myField = $dim;
|
|
595
|
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
|
|
596
|
}
|
|
597
|
}
|
|
598
|
}
|
|
599
|
else{
|
|
600
|
if($myFactTable['dim']['name'] == $field){
|
|
601
|
$myField = $myFactTable['dim'];
|
|
602
|
//$flag = true;
|
|
603
|
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
|
|
604
|
}
|
|
605
|
}
|
|
606
|
if(!isAssoc($myFactTable['meas'])){
|
|
607
|
foreach($myFactTable['meas'] as $meas) {
|
|
608
|
if($meas['name'] == $field){
|
|
609
|
$myField = $meas;
|
|
610
|
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
|
|
611
|
}
|
|
612
|
}
|
|
613
|
}
|
|
614
|
else{
|
|
615
|
if($myFactTable['meas']['name'] == $field){
|
|
616
|
$myField = $myFactTable['meas'];
|
|
617
|
return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
|
|
618
|
}
|
|
619
|
}
|
|
620
|
$this->log->error('cannot find field '.$field.' in table '.$table);
|
|
621
|
return "cannot find field ".$field;
|
|
622
|
}
|
|
623
|
|
|
624
|
$this->log->error("cannot find table ".$table);
|
|
625
|
return "cannot find table ".$table;
|
|
626
|
}
|
|
627
|
|
|
628
|
function getDefaultData($table) {
|
|
629
|
$this->log->info("DefaultData -> table:".$table);
|
|
630
|
if($table !=null && $table!='null') {
|
|
631
|
$query = 'select * from "defaults" where tablename='."'".$table."'";
|
|
632
|
}
|
|
633
|
else{
|
|
634
|
$query = 'select * from "defaults" where tableorder=(select min(tableorder) from "defaults")';
|
|
635
|
}
|
|
636
|
$this->log->info("DefaultData -> query created: ".$query);
|
|
637
|
|
|
638
|
$res = $this->doQuery($query,3,"false");
|
|
639
|
if($res == null)
|
|
640
|
return "empty";
|
|
641
|
|
|
642
|
$selectedData = array();
|
|
643
|
$selectedData['table'] = $res[0][0];
|
|
644
|
$selectedData['fields'] = array();
|
|
645
|
$selectedData['fields'][0] = array();
|
|
646
|
$selectedData['fields'][0]['fld'] = $res[0][2];
|
|
647
|
$selectedData['fields'][0]['agg'] = $res[0][3];
|
|
648
|
$selectedData['fields'][0]['id'] = 0;
|
|
649
|
$selectedData['fields'][0]['type'] = "";
|
|
650
|
$selectedData['fields'][0]['yaxis'] = 1;
|
|
651
|
$selectedData['xaxis']['name'] = $res[0][1];
|
|
652
|
$selectedData['xaxis']['agg'] = 'avg';
|
|
653
|
$selectedData['group'] = '';
|
|
654
|
$selectedData['color'] = '';
|
|
655
|
if($this->checkMeas($selectedData['table'], $selectedData['xaxis']['name'])){
|
|
656
|
$type='scatter';
|
|
657
|
$selectedData['type'] = 'scatter';
|
|
658
|
}
|
|
659
|
else{
|
|
660
|
$selectedData['type'] = 'chart';
|
|
661
|
$type = $res[0][5];
|
|
662
|
}
|
|
663
|
$selectedData['size'] = $GLOBALS['size'];
|
|
664
|
return array('selectedData'=>$selectedData,'type'=>$type);
|
|
665
|
//return array('selectedData'=>$selectedData, 'data'=>$this->getData($selectedData),'type'=>$type);
|
|
666
|
}
|
|
667
|
|
|
668
|
/*
|
|
669
|
domi tou selectedData:
|
|
670
|
['table'] = fact table name
|
|
671
|
['fields'] = [{'fld':field name,'agg':aggregator for the field,'id': de mas noiazei}, ...]
|
|
672
|
['series'] = [dim field name with the list of the dim tables names that lead to it (eg: Product-Supplier-sup_name), ...]
|
|
673
|
['group'] = dim field name with the list of the dim tables names that lead to it (eg: Product-Supplier-sup_name) <---will have to group by it and have a series for each value of it
|
|
674
|
['xaxis'] = {'name': to onoma tou dim h meas pou paei ston x axis, 'agg': o aggregator an einai meas}
|
|
675
|
['type'](optional)
|
|
676
|
stin apli periptwsi to series einai panta keno
|
|
677
|
sto telos exoume tosa series osa fields (to poly 3)
|
|
678
|
|
|
679
|
an sto xaxis exoume meas tote exoume scatter plot kai den bazoume to xaxis sto group by
|
|
680
|
an den exoume series tote den exoume group by kai agnooume ta aggs
|
|
681
|
*/
|
|
682
|
/*
|
|
683
|
domi tou query:
|
|
684
|
ok select: to xaxis me patera to akribws proigoumeno sto onoma AND ola ta fields me ta aggs tous AND ola ta series me patera to akribws proigoumeno sto onoma
|
|
685
|
ok from: to table AND ola osa emfanizontai sto series kai to xaxis
|
|
686
|
where: 1 zeygari gia kathe syndyasmo diplanwn pinakwn pou emfanizetai sto series kai twn arxikwn me to table
|
|
687
|
(eg: gia to Product-Supplier tha exoume ena zeygari to Product,Supplier kai ena to Facttable,Product)
|
|
688
|
ok group by: to xaxis kai ola ta series opws akribws kai sto select
|
|
689
|
ok order by: to xaxis
|
|
690
|
*/
|
|
691
|
function makeQueryGroup($sel,$persistent){//print_r($sel);
|
|
692
|
$this->log->info("makeQueryGroup");
|
|
693
|
/*
|
|
694
|
select agg(field), xaxisfield, groupfield
|
|
695
|
from facttable, xaxistables, grouptables
|
|
696
|
where facttable = xaxistables and xaxistables and facttable = groupstables and groupstables
|
|
697
|
group by groupfield, xaxisfield (ektos ki an einai meas)
|
|
698
|
order by groupfield, xaxisfield
|
|
699
|
*/
|
|
700
|
$isMeas = false;
|
|
701
|
$field = "";
|
|
702
|
$xaxisfield = "";
|
|
703
|
$groupfield = "";
|
|
704
|
$tables = array();
|
|
705
|
$tablesPairs = array();
|
|
706
|
$tables[] = $sel['table'];
|
|
707
|
/*yaxis*/ //<---- panta measurement
|
|
708
|
$field = '"'.$sel['fields'][0]['fld'].'"';
|
|
709
|
/*xaxis*/
|
|
710
|
$xaxislist = explode("-",$sel['xaxis']['name']);
|
|
711
|
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1])){
|
|
712
|
$isMeas = true;
|
|
713
|
}
|
|
714
|
if(!$isMeas){
|
|
715
|
//an den einai measurement tote einai dimension kai mporei na exei tables
|
|
716
|
if(count($xaxislist) == 1){ //einai dim alla den analyetai se pinaka
|
|
717
|
$xaxisfield = '"'.$sel['table'].'"."'.$xaxislist[count($xaxislist)-1].'"';
|
|
718
|
}
|
|
719
|
else{
|
|
720
|
$xaxisfield = '"'.$xaxislist[count($xaxislist)-2].'"."'.$xaxislist[count($xaxislist)-1].'"';
|
|
721
|
|
|
722
|
//briskw tous pinakes enan enan kai tous bazw sta pairs alla kai sto xaxistables
|
|
723
|
for($i=0;$i<count($xaxislist)-1;$i++){
|
|
724
|
if(!in_array($xaxislist[$i],$tables)){
|
|
725
|
$tables[] = $xaxislist[$i];
|
|
726
|
}
|
|
727
|
if($i == count($xaxislist)-2){
|
|
728
|
if(!in_array_r(array($sel['table'],$xaxislist[$i]), $tablesPairs)){
|
|
729
|
$tablesPairs[] = array($sel['table'],$xaxislist[$i]);
|
|
730
|
}
|
|
731
|
}
|
|
732
|
else{
|
|
733
|
if(!in_array_r(array($xaxislist[$i],$xaxislist[$i+1]),$tablesPairs))
|
|
734
|
$tablesPairs[] = array($xaxislist[$i],$xaxislist[$i+1]);
|
|
735
|
}
|
|
736
|
}
|
|
737
|
}
|
|
738
|
}
|
|
739
|
else {
|
|
740
|
//einai meas, den kanw tipota
|
|
741
|
$xaxisfield = '"'.$sel['table'].'"."'.$sel['xaxis']['name'].'"';
|
|
742
|
$xaxistables = null;
|
|
743
|
}
|
|
744
|
/*group*/
|
|
745
|
//briskw tous pinakes tou group field kai tous bazw enan enan sto table alla kai sta pairs
|
|
746
|
$grouplist = explode("-",$sel['group']);
|
|
747
|
if(count($grouplist) == 1){ //einai dim alla den analyetai se pinaka
|
|
748
|
$groupfield = '"'.$sel['table'].'"."'.$grouplist[count($grouplist)-1].'"';
|
|
749
|
}
|
|
750
|
else{
|
|
751
|
$groupfield = '"'.$grouplist[count($grouplist)-2].'"."'.$grouplist[count($grouplist)-1].'"';
|
|
752
|
//briskw tous pinakes enan enan kai tous bazw sta pairs alla kai sto xaxistables
|
|
753
|
for($i=0;$i<count($grouplist)-1;$i++){
|
|
754
|
if(!in_array($grouplist[$i],$tables)){
|
|
755
|
$tables[] = $grouplist[$i];
|
|
756
|
}
|
|
757
|
if($i == count($grouplist)-2){
|
|
758
|
if(!in_array_r(array($sel['table'],$grouplist[$i]), $tablesPairs)){
|
|
759
|
$tablesPairs[] = array($sel['table'],$grouplist[$i]);
|
|
760
|
}
|
|
761
|
}
|
|
762
|
else{
|
|
763
|
if(!in_array_r(array($grouplist[$i],$grouplist[$i+1]),$tablesPairs))
|
|
764
|
$tablesPairs[] = array($grouplist[$i],$grouplist[$i+1]);
|
|
765
|
}
|
|
766
|
}
|
|
767
|
}
|
|
768
|
//steinw to query
|
|
769
|
$query = "select ";
|
|
770
|
/*xaxis*/
|
|
771
|
if($isMeas && $sel['color'] != 'yes'){ // prepei na balw kai ton aggregator
|
|
772
|
switch($sel['xaxis']['agg']){
|
|
773
|
case 'none':
|
|
774
|
case '':
|
|
775
|
case 'avg':
|
|
776
|
$query .= " avg(".$xaxisfield.") ";
|
|
777
|
break;
|
|
778
|
default:
|
|
779
|
$query .= $sel['xaxis']['agg']."(".$xaxisfield.") ";
|
|
780
|
break;
|
|
781
|
}
|
|
782
|
}
|
|
783
|
else{
|
|
784
|
$query .= " ".$xaxisfield;
|
|
785
|
}
|
|
786
|
/*yaxis*/
|
|
787
|
if($sel['color'] != 'yes')
|
|
788
|
switch($sel['fields'][0]['agg']){
|
|
789
|
case 'none':
|
|
790
|
case '':
|
|
791
|
case 'avg':
|
|
792
|
$query .= ", avg(".$field.") ";
|
|
793
|
break;
|
|
794
|
default:
|
|
795
|
$query .= ", ".$sel['fields'][0]['agg']."(".$field.") ";
|
|
796
|
break;
|
|
797
|
}
|
|
798
|
else
|
|
799
|
$query .= ', '.$field;
|
|
800
|
|
|
801
|
/*group*/
|
|
802
|
$query .= ", ".$groupfield;
|
|
803
|
|
|
804
|
$query .= " from ".implode(", ",$tables);
|
|
805
|
$query .= " where ";
|
|
806
|
for($i=0;$i<count($sel['fields']);$i++) {
|
|
807
|
if($i!=0) $query .=" and ";
|
|
808
|
$query .= '"'.$sel['fields'][$i]['fld'] .'" !=""';
|
|
809
|
}
|
|
810
|
|
|
811
|
if(count($tablesPairs) > 0){
|
|
812
|
$query .= " and ";
|
|
813
|
for($i=0;$i<count($tablesPairs);$i++){
|
|
814
|
$query .= '"'.$tablesPairs[$i][0].'"."'.$tablesPairs[$i][1].'"="'.$tablesPairs[$i][1].'".id';
|
|
815
|
if($i!=count($tablesPairs)-1) $query .= "and ";
|
|
816
|
else $query .= " ";
|
|
817
|
}
|
|
818
|
}
|
|
819
|
else $query .= ' ';
|
|
820
|
if($sel['color'] != 'yes'){
|
|
821
|
$query .= "group by ".$groupfield;
|
|
822
|
if(!$isMeas){
|
|
823
|
$query .=", ".$xaxisfield;
|
|
824
|
}
|
|
825
|
}
|
|
826
|
$query .= " order by ".$groupfield.", ".$xaxisfield;
|
|
827
|
$this->log->info("generated query: ".$query);
|
|
828
|
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
|
|
829
|
if($isMeas) $returnType = 'scatter';
|
|
830
|
else $returnType = 'chart';
|
|
831
|
return array('type'=>$returnType,'data'=>$res,'accomDims'=>$dimsList['fields']);
|
|
832
|
}
|
|
833
|
|
|
834
|
function getData($sel,$persistent){
|
|
835
|
$this->log->info("getting data");
|
|
836
|
$xaxislist = explode("-",$sel['xaxis']['name']);
|
|
837
|
$groups = array();
|
|
838
|
$brsels = array(); $queries = array();
|
|
839
|
$breakflag = false;
|
|
840
|
|
|
841
|
|
|
842
|
|
|
843
|
//check if we need to break the query to many (if there are filters that apply to only one field)
|
|
844
|
if(count($sel['fields']) > 1){
|
|
845
|
for($i=0;$i<count($sel['filters']);$i++)
|
|
846
|
if(isset($sel['filters'][$i]['to']) && $sel['filters'][$i]['to'] != -1){
|
|
847
|
if(!in_array($sel['filters'][$i]['to'],$groups))
|
|
848
|
$groups[] = $sel['filters'][$i]['to'];
|
|
849
|
$breakflag = true;
|
|
850
|
}
|
|
851
|
for($i=0;$i<count($sel['having']);$i++)
|
|
852
|
if(isset($sel['having'][$i]['to']) && $sel['having'][$i]['to'] != -1){
|
|
853
|
if(!in_array($sel['having'][$i]['to'],$groups))
|
|
854
|
$groups[] = $sel['having'][$i]['to'];
|
|
855
|
$breakflag = true;
|
|
856
|
}
|
|
857
|
}
|
|
858
|
if($breakflag){
|
|
859
|
$this->log->info("NEED TO BREAK!");
|
|
860
|
//will break the query into as many as the different values in the filters and havings to attribute -> count($groups)
|
|
861
|
$brsels[] = $this->clearselections($sel,$groups,-1,0);
|
|
862
|
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
|
|
863
|
$queries[] = $this->scatterData($brsels[0],false);
|
|
864
|
else
|
|
865
|
$queries[] = $this->chartData($brsels[0],false,$persistent);
|
|
866
|
for($i=0;$i<count($groups);$i++){
|
|
867
|
$brsels[] = $this->clearselections($sel,$groups,$groups[$i],$i+1);
|
|
868
|
|
|
869
|
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
|
|
870
|
$queries[] = $this->scatterData($brsels[$i+1],false);
|
|
871
|
else
|
|
872
|
$queries[] = $this->chartData($brsels[$i+1],false,$persistent);
|
|
873
|
}
|
|
874
|
|
|
875
|
$this->log->info("selections: ".print_r($brsels,true));
|
|
876
|
$this->log->info("user selections updated: ".print_r($sel,true));
|
|
877
|
$this->log->info("queries: ".print_r($queries,true));
|
|
878
|
//get all the queries and combine them to one
|
|
879
|
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
|
|
880
|
return $this->makeSuperQuery($queries,$sel,"scatter",$persistent);
|
|
881
|
else
|
|
882
|
return $this->makeSuperQuery($queries,$sel,"chart",$persistent);
|
|
883
|
}
|
|
884
|
else{
|
|
885
|
$this->log->info("NO NEED TO BREAK!");
|
|
886
|
if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
|
|
887
|
return $this->scatterData($sel,$persistent);
|
|
888
|
else
|
|
889
|
return $this->chartData($sel,true,$persistent);
|
|
890
|
}
|
|
891
|
}
|
|
892
|
|
|
893
|
private function makeSuperQuery($queries,$sel,$type,$persistent){
|
|
894
|
$superquerySelects = "SELECT ";
|
|
895
|
$superquerySelecte = " ";
|
|
896
|
$superqueryFrom = " FROM ";
|
|
897
|
$superquerySort = "";
|
|
898
|
//ta pedia tou xaxis kai tou group tha einai ayta tou outer join
|
|
899
|
//sta select yparxei prwta to xaxis, meta i lista me ta fields onomatismena, kai meta an yparxei to group
|
|
900
|
//sto megalo select tha yparxei gia kathe query to xaxis, meta ola ta fields twn queries me ti seira pou einai sto sel
|
|
901
|
//kai meta gia kathe query to group field
|
|
902
|
|
|
903
|
//prepei na kseroume kathe pedio tou select se poio query anikei, kai to pedio tou order by
|
|
904
|
for($q=0;$q<count($queries);$q++){//xaxis
|
|
905
|
//if($q>0) $superquerySelects .= ", ";
|
|
906
|
$superquerySelects .= "q".$q.".xfield, ";
|
|
907
|
if($q>0) $superquerySort .= ", ";
|
|
908
|
$superquerySort .= "q".$q.".xfield ";
|
|
909
|
|
|
910
|
if($sel['group']!='' && $sel['group'] != 'no'){//dimfields
|
|
911
|
/*if($q>0)*/ $superquerySelecte .= ", ";
|
|
912
|
$superquerySelecte .= "q".$q.".dimfield ";
|
|
913
|
$superquerySort .= ", q".$q.".dimfield ";
|
|
914
|
}
|
|
915
|
|
|
916
|
$superqueryFrom .= "(".$queries[$q].") as q".$q;
|
|
917
|
|
|
918
|
if($q>0){
|
|
919
|
$superqueryFrom .= " ON q".($q-1).".xfield = q".$q.".xfield ";
|
|
920
|
if($sel['group']!='' && $sel['group'] != 'no')
|
|
921
|
$superqueryFrom .= " AND q".($q-1).".dimfield = q".$q.".dimfield ";
|
|
922
|
}
|
|
923
|
|
|
924
|
if($q<count($queries)-1) {
|
|
925
|
$superqueryFrom .= " FULL JOIN ";
|
|
926
|
}
|
|
927
|
|
|
928
|
}
|
|
929
|
for($f=0;$f<count($sel['fields']);$f++){//fields
|
|
930
|
$superquerySelects .= "q".$sel['fields'][$f]['q'].".field".$sel['fields'][$f]['i'];
|
|
931
|
if($f<(count($sel['fields'])-1)) $superquerySelects .=", ";
|
|
932
|
}
|
|
933
|
if(isset($sel['sort']) && $sel['sort']!='xaxis'){
|
|
934
|
$ex = explode(".", $sel['sortq']);
|
|
935
|
$temp = "q".$ex[0].".field".$ex[1];
|
|
936
|
|
|
937
|
if($sel['order'] && $sel['order'] == 'd')
|
|
938
|
$temp.= " DESC, ";
|
|
939
|
else
|
|
940
|
$temp .= ", ";
|
|
941
|
|
|
942
|
$superquerySort = $temp . $superquerySort;
|
|
943
|
}
|
|
944
|
|
|
945
|
$superquerySort = " ORDER BY ".$superquerySort;
|
|
946
|
|
|
947
|
$superQuery = $superquerySelects.$superquerySelecte.$superqueryFrom.$superquerySort." LIMIT ".$sel['size'];
|
|
948
|
$this->log->info("superquery: ".$superQuery);
|
|
949
|
$res = $this->doQuery($superQuery, PDO::FETCH_NUM,$persistent);
|
|
950
|
return $this->combineData($res,count($queries),count($sel['fields']),$type);
|
|
951
|
}
|
|
952
|
|
|
953
|
private function combineData($data,$queries,$fields,$type){
|
|
954
|
$newdata = array();
|
|
955
|
if(count($data[0]) > $queries+$fields)
|
|
956
|
$dstart = $queries + $fields;
|
|
957
|
else
|
|
958
|
$dstart = -1;
|
|
959
|
$fstart = $queries;
|
|
960
|
for($d=0;$d<count($data);$d++){
|
|
961
|
$newdata[$d] = array();
|
|
962
|
for($i=0;$i<$queries;$i++)
|
|
963
|
if($data[$d][$i] != null){
|
|
964
|
$newdata[$d][0] = $data[$d][$i];
|
|
965
|
break;
|
|
966
|
}
|
|
967
|
for($i=$fstart;$i<$fields+$queries;$i++)
|
|
968
|
$newdata[$d][$i-$fstart+1] = $data[$d][$i];
|
|
969
|
if($dstart > 0)
|
|
970
|
for($i=$dstart;$i<count($data[0]);$i++)
|
|
971
|
if($data[$d][$i] != null){
|
|
972
|
$newdata[$d][$fields+1] = $data[$d][$i];
|
|
973
|
break;
|
|
974
|
}
|
|
975
|
}
|
|
976
|
$this->log->info("superquerys result: ".print_r($newdata,true));
|
|
977
|
return array('type'=>$type,'data'=>$newdata);
|
|
978
|
}
|
|
979
|
|
|
980
|
private function clearselections(&$sel,$seperate,$field,$index){
|
|
981
|
$newsel = array();
|
|
982
|
$fields = array();
|
|
983
|
$newsel['table'] = $sel['table'];
|
|
984
|
$newsel['xaxis'] = $sel['xaxis'];
|
|
985
|
$newsel['group'] = $sel['group'];
|
|
986
|
$newsel['color'] = $sel['color'];
|
|
987
|
$newsel['size'] = $sel['size'];
|
|
988
|
$newsel['fields'] = array();
|
|
989
|
$newsel['filters'] = array();
|
|
990
|
$newsel['having'] = array();
|
|
991
|
if($field == -1 || $field == -2){ //things that apply to whole chart, will remove only the things that apply to one field and that field
|
|
992
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
993
|
if(!in_array($i+1,$seperate)){
|
|
994
|
$newsel['fields'][] = $sel['fields'][$i];
|
|
995
|
$sel['fields'][$i]['q'] = $index;
|
|
996
|
$sel['fields'][$i]['i'] = count($newsel['fields'])-1;
|
|
997
|
$fields[] = $sel['fields'][$i]['agg'] + "-" + $sel['fields'][$i]['fld'];
|
|
998
|
}
|
|
999
|
}
|
|
1000
|
for($i=0;$i<count($sel['filters']);$i++){
|
|
1001
|
if(!isset($sel['filters'][$i]['to']) || $sel['filters'][$i]['to'] == -1){
|
|
1002
|
$newsel['filters'][] = $sel['filters'][$i];
|
|
1003
|
}
|
|
1004
|
}
|
|
1005
|
for($i=0;$i<count($sel['having']);$i++){
|
|
1006
|
if(!isset($sel['having'][$i]['to']) || $sel['having'][$i]['to'] == -1){
|
|
1007
|
$newsel['having'][] = $sel['having'][$i];
|
|
1008
|
}
|
|
1009
|
}
|
|
1010
|
if(in_array($sel['sort'],$fields)){
|
|
1011
|
$sel['sortq'] = $index.".".(count($newsel['fields'])-1);
|
|
1012
|
//$newsel['sort'] = $sel['sort'];
|
|
1013
|
}
|
|
1014
|
//else
|
|
1015
|
//$newsel['sort'] = 'xaxis';
|
|
1016
|
}
|
|
1017
|
else{ //we keep only the field, and the filters and havings that apply to that and the whole chart
|
|
1018
|
$newsel['fields'][] = $sel['fields'][$field-1];
|
|
1019
|
$sel['fields'][$field-1]['q'] = $index;
|
|
1020
|
$sel['fields'][$field-1]['i'] = count($newsel['fields'])-1;
|
|
1021
|
for($i=0;$i<count($sel['filters']);$i++){
|
|
1022
|
if(isset($sel['filters'][$i]['to']) && ($sel['filters'][$i]['to'] == $field || $sel['filters'][$i]['to'] == -1)){
|
|
1023
|
$newsel['filters'][] = $sel['filters'][$i];
|
|
1024
|
}
|
|
1025
|
}
|
|
1026
|
for($i=0;$i<count($sel['having']);$i++){
|
|
1027
|
if(isset($sel['having'][$i]['to']) && $sel['having'][$i]['to'] == $field){
|
|
1028
|
$newsel['having'][] = $sel['having'][$i];
|
|
1029
|
}
|
|
1030
|
}
|
|
1031
|
if(in_array($sel['sort'],$fields)){
|
|
1032
|
$newsel['sort'] = $sel['sort'];
|
|
1033
|
}
|
|
1034
|
else
|
|
1035
|
$newsel['sort'] = 'xaxis';
|
|
1036
|
}
|
|
1037
|
return $newsel;
|
|
1038
|
}
|
|
1039
|
|
|
1040
|
function chartData($sel, $doquery=true,$persistent){
|
|
1041
|
if($sel['group']!='' && $sel['group'] != 'no'){ //group
|
|
1042
|
return $this->chartDataGroup($sel, $doquery);
|
|
1043
|
}
|
|
1044
|
else{
|
|
1045
|
|
|
1046
|
$this->log->info("sel: ".print_r($sel,true));
|
|
1047
|
$tree = new QueryTree($sel['table']);
|
|
1048
|
$tree->updateXAxis($sel['xaxis']['name']);
|
|
1049
|
$tree->updateYAxis($sel['fields']);
|
|
1050
|
$tree->updateFilters($sel['filters']);
|
|
1051
|
if (isset($sel['sort'])) {$tree->updateOrderBy($sel['sort']);}
|
|
1052
|
if(isset($sel['order'])){$tree->updateOrder($sel['order']);}
|
|
1053
|
if(isset($sel['size'])){$tree->updateLimit($sel['size']);}
|
|
1054
|
if(isset($sel['nulls'])){$tree->excludeNulls($sel['nulls']);}
|
|
1055
|
|
|
1056
|
$this->log->info("tree: ".print_r($tree->tree, true));
|
|
1057
|
|
|
1058
|
$query = $tree->getQuery();
|
|
1059
|
|
|
1060
|
if(!$doquery){
|
|
1061
|
$this->log->info('chartData generated query:'.$query);
|
|
1062
|
$this->log->info('will not execute it');
|
|
1063
|
return $query;
|
|
1064
|
}
|
|
1065
|
|
|
1066
|
$this->log->info('chartData generated tree: '.print_r($tree->getTree(), true));
|
|
1067
|
$this->log->info('chartData generated query: '.$query);
|
|
1068
|
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
|
|
1069
|
$this->log->info("result: ".print_r($res,true));
|
|
1070
|
return array('type'=>'chart','data'=>$res);
|
|
1071
|
}
|
|
1072
|
|
|
1073
|
}
|
|
1074
|
|
|
1075
|
function chartDataGroup($sel, $doquery=true,$persistent){
|
|
1076
|
//exoume dyo dims, ena tou xaxis kai ena tou group
|
|
1077
|
//kanoume tin idia diadikasia kai gia ta dyo
|
|
1078
|
$xfield = '';
|
|
1079
|
$dimfield = '';
|
|
1080
|
$tables = array();
|
|
1081
|
$tablePairs = array();
|
|
1082
|
$filters = array();
|
|
1083
|
//order by it
|
|
1084
|
//may need to join
|
|
1085
|
//gia to xaxis
|
|
1086
|
$dimlist = explode("-",$sel['xaxis']['name']);
|
|
1087
|
if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
|
|
1088
|
$xfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
|
|
1089
|
}
|
|
1090
|
else{
|
|
1091
|
$xfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
|
|
1092
|
//briskw tous pinakes enan enan kai tous bazw sta pairs
|
|
1093
|
for($i=0;$i<count($dimlist)-1;$i++){
|
|
1094
|
if(!in_array($dimlist[$i],$tables)){
|
|
1095
|
$tables[] = $dimlist[$i];
|
|
1096
|
}
|
|
1097
|
if($i == 0){
|
|
1098
|
if(!in_array_r(array($sel['table'],$dimlist[$i]), $tablePairs)){
|
|
1099
|
$tablePairs[] = array($sel['table'],$dimlist[$i]);
|
|
1100
|
}
|
|
1101
|
}
|
|
1102
|
else{
|
|
1103
|
if(!in_array_r(array($dimlist[$i-1],$dimlist[$i]),$tablePairs))
|
|
1104
|
$tablePairs[] = array($dimlist[$i-1],$dimlist[$i]);
|
|
1105
|
}
|
|
1106
|
}
|
|
1107
|
}
|
|
1108
|
//gia to group
|
|
1109
|
$dimlist = explode("-",$sel['group']);
|
|
1110
|
if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
|
|
1111
|
$dimfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
|
|
1112
|
}
|
|
1113
|
else{
|
|
1114
|
$dimfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
|
|
1115
|
//briskw tous pinakes enan enan kai tous bazw sta pairs
|
|
1116
|
for($i=0;$i<count($dimlist)-1;$i++){
|
|
1117
|
if(!in_array($dimlist[$i],$tables)){
|
|
1118
|
$tables[] = $dimlist[$i];
|
|
1119
|
}
|
|
1120
|
if($i == 0){
|
|
1121
|
if(!in_array_r(array($sel['table'],$dimlist[$i]), $tablePairs)){
|
|
1122
|
$tablePairs[] = array($sel['table'],$dimlist[$i]);
|
|
1123
|
}
|
|
1124
|
}
|
|
1125
|
else{
|
|
1126
|
if(!in_array_r(array($dimlist[$i-1],$dimlist[$i]),$tablePairs))
|
|
1127
|
$tablePairs[] = array($dimlist[$i-1],$dimlist[$i]);
|
|
1128
|
}
|
|
1129
|
}
|
|
1130
|
}
|
|
1131
|
//filters
|
|
1132
|
if(isset($sel['filters'])){
|
|
1133
|
for($fc=0;$fc<count($sel['filters']);$fc++){
|
|
1134
|
$filterfields = explode("-",$sel['filters'][$fc]['name']);
|
|
1135
|
if(count($filterfields)>1){ //join needed
|
|
1136
|
$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
|
|
1137
|
for($i=0;$i<count($filterfields)-1;$i++){
|
|
1138
|
if(!in_array($filterfields[$i],$tables)){
|
|
1139
|
$tables[] = $filterfields[$i];
|
|
1140
|
}
|
|
1141
|
if($i == 0){
|
|
1142
|
if(!in_array_r(array($sel['table'],$filterfields[$i]), $tablePairs)){
|
|
1143
|
$tablePairs[] = array($sel['table'],$filterfields[$i]);
|
|
1144
|
}
|
|
1145
|
}
|
|
1146
|
else{
|
|
1147
|
if(!in_array_r(array($filterfields[$i-1],$filterfields[$i]),$tablePairs)){
|
|
1148
|
$tablePairs[] = array($filterfields[$i-1],$filterfields[$i]);
|
|
1149
|
}
|
|
1150
|
}
|
|
1151
|
}
|
|
1152
|
}
|
|
1153
|
else
|
|
1154
|
$sel['filters'][$fc]['name'] = $sel['table'].".".$sel['filters'][$fc]['name'];
|
|
1155
|
}
|
|
1156
|
}
|
|
1157
|
//----------------------------------------------------------------//
|
|
1158
|
//having
|
|
1159
|
if(isset($sel['having'])){
|
|
1160
|
for($fc=0;$fc<count($sel['having']);$fc++){
|
|
1161
|
$havingfields = explode("-",$sel['having'][$fc]['name']);
|
|
1162
|
if(count($havingfields)>1){ //join needed
|
|
1163
|
$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
|
|
1164
|
for($i=0;$i<count($havingfields)-1;$i++){
|
|
1165
|
if(!in_array($havingfields[$i],$tables)){
|
|
1166
|
$tables[] = $havingfields[$i];
|
|
1167
|
}
|
|
1168
|
if($i == 0){
|
|
1169
|
if(!in_array_r(array($sel['table'],$havingfields[$i]), $tablePairs)){
|
|
1170
|
$tablePairs[] = array($sel['table'],$havingfields[$i]);
|
|
1171
|
}
|
|
1172
|
}
|
|
1173
|
else{
|
|
1174
|
if(!in_array_r(array($havingfields[$i-1],$havingfields[$i]),$tablePairs)){
|
|
1175
|
$tablePairs[] = array($havingfields[$i-1],$havingfields[$i]);
|
|
1176
|
}
|
|
1177
|
}
|
|
1178
|
}
|
|
1179
|
}
|
|
1180
|
}
|
|
1181
|
}
|
|
1182
|
//----------------------------------------------------------------//
|
|
1183
|
$query = 'SELECT '.$xfield.' as xfield';
|
|
1184
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
1185
|
$query .= ', '.$sel['fields'][$i]['agg'].'("'.$sel['table'].'"."'.$sel['fields'][$i]['fld'].'") as field'.$i;
|
|
1186
|
}
|
|
1187
|
$query .= ', '.$dimfield.' as dimfield';
|
|
1188
|
$query .= ' FROM "'.$sel['table'].'"';
|
|
1189
|
if(count($tables)>0) $query .= ', "'.implode("\", \"",$tables).'" ';
|
|
1190
|
/*$query .= ' WHERE "'.$sel['xaxis']['name'].'" !=""';
|
|
1191
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
1192
|
$query .=" and ";
|
|
1193
|
$query .= '"'.$sel['fields'][$i]['fld'] .'" !=""';
|
|
1194
|
}*/
|
|
1195
|
if(count($tablePairs) > 0 || (isset($sel['filters']) && count($sel['filters']) > 0) || $sel['nulls']){
|
|
1196
|
$query .= " WHERE ";
|
|
1197
|
for($i=0;$i<count($tablePairs);$i++){
|
|
1198
|
$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
|
|
1199
|
if($i!=count($tablePairs)-1) $query .= " and ";
|
|
1200
|
else $query .= " ";
|
|
1201
|
}
|
|
1202
|
//filters
|
|
1203
|
if(isset($sel['filters']) && count($sel['filters'])>0){
|
|
1204
|
if(count($tablePairs)>0)
|
|
1205
|
$query .= " and ";
|
|
1206
|
for($i=0;$i<count($sel['filters']);$i++){
|
|
1207
|
if(isset($sel['filters'][$i]['max'])){
|
|
1208
|
if(strstr($sel['filters'][$i]['name'],"year") !== false && $sel['filters'][$i]['max']=='today')
|
|
1209
|
$query .= "(".$sel['filters'][$i]['name']."<= extract(YEAR from now()) and ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")";
|
|
1210
|
else if(strstr($sel['filters'][$i]['name'],"year") !== false && $sel['filters'][$i]['min']=='today')
|
|
1211
|
$query .="(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." and ".$sel['filters'][$i]['name'].">= extract(YEAR from now()))" ;
|
|
1212
|
else
|
|
1213
|
$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." and ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
|
|
1214
|
}
|
|
1215
|
else if(isset($sel['filters'][$i]['values'])){
|
|
1216
|
$query .= "(";
|
|
1217
|
for($j=0;$j<count($sel['filters'][$i]['values']);$j++){
|
|
1218
|
//$query .= $sel['filters'][$i]['name'].'="'.$sel['filters'][$i]['values'][$j].'"';
|
|
1219
|
$query .= $sel['filters'][$i]['name'].'=\''.$sel['filters'][$i]['values'][$j].'\'';
|
|
1220
|
if($j!=count($sel['filters'][$i]['values'])-1) $query .= " or ";
|
|
1221
|
}
|
|
1222
|
$query .= ")";
|
|
1223
|
}
|
|
1224
|
else if(isset($sel['filters'][$i]['exvalues'])){
|
|
1225
|
$query .= "(";
|
|
1226
|
for($j=0;$j<count($sel['filters'][$i]['exvalues']);$j++){
|
|
1227
|
$query .= $sel['filters'][$i]['name'].'!=\''.$sel['filters'][$i]['exvalues'][$j].'\'';
|
|
1228
|
if($j!=count($sel['filters'][$i]['exvalues'])-1) $query .= " and ";
|
|
1229
|
}
|
|
1230
|
$query .= ")";
|
|
1231
|
}
|
|
1232
|
//$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
|
|
1233
|
if($i!=count($sel['filters'])-1) $query .= " and ";
|
|
1234
|
else $query .= " ";
|
|
1235
|
}
|
|
1236
|
}
|
|
1237
|
if($sel['nulls'] == true){//non null values from xaxis field and dimfield
|
|
1238
|
if((isset($sel['filters']) && count($sel['filters'])>0) || count($tablePairs) > 0){
|
|
1239
|
$query .= " and ";
|
|
1240
|
}
|
|
1241
|
$query .= $xfield . " IS NOT NULL AND ". $dimfield . " IS NOT NULL ";
|
|
1242
|
}
|
|
1243
|
}
|
|
1244
|
//--------------------------------------------------------//
|
|
1245
|
$query .= " GROUP BY ".$dimfield.', '.$xfield;
|
|
1246
|
if(count($sel['having'])>0){
|
|
1247
|
$query .= " HAVING ";
|
|
1248
|
for($i=0;$i<count($sel['having']);$i++){
|
|
1249
|
if($i>0)
|
|
1250
|
$query .= " and ";
|
|
1251
|
$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
|
|
1252
|
}
|
|
1253
|
|
|
1254
|
}
|
|
1255
|
|
|
1256
|
if(!$doquery){
|
|
1257
|
$this->log->info('chartDataGroup generated query:'.$query);
|
|
1258
|
$this->log->info('will not execute it');
|
|
1259
|
return $query;
|
|
1260
|
}
|
|
1261
|
|
|
1262
|
//need to find the field to order the result set by
|
|
1263
|
$query.= " ORDER BY ";
|
|
1264
|
if(isset($sel['sort']) && $sel['sort']!='xaxis'){
|
|
1265
|
$ex = explode("-", $sel['sort']);
|
|
1266
|
$query .= $ex[0]."(".$sel['table'].".".$ex[1].")";
|
|
1267
|
if($sel['order'] && $sel['order'] == 'd')
|
|
1268
|
$query.= " DESC";
|
|
1269
|
$query .= ", ".$xfield;
|
|
1270
|
}
|
|
1271
|
else{
|
|
1272
|
$query.= $xfield;
|
|
1273
|
if($sel['order'] && $sel['order'] == 'd')
|
|
1274
|
$query.= " DESC";
|
|
1275
|
}
|
|
1276
|
$query.=', '.$dimfield;
|
|
1277
|
$query.= " LIMIT ".$sel['size'];
|
|
1278
|
//echo $query;
|
|
1279
|
$this->log->info('chartDataGroup generated query:'.$query);
|
|
1280
|
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
|
|
1281
|
$this->log->info("result: ".print_r($res,true));
|
|
1282
|
return array('type'=>'chart','data'=>$res);
|
|
1283
|
|
|
1284
|
}
|
|
1285
|
|
|
1286
|
function scatterData($sel, $doquery=true,$persistent){
|
|
1287
|
//group, color, or nothing
|
|
1288
|
//many fields or one
|
|
1289
|
if($sel['group']!='' && $sel['group'] != 'no'){ //group
|
|
1290
|
$this->log->info('scatterDataGroup called');
|
|
1291
|
return $this->scatterDataGroup($sel, $doquery);
|
|
1292
|
}
|
|
1293
|
else if($sel['color']!='' && $sel['color'] != 'no'){ //color
|
|
1294
|
$this->log->info('scatterDataColor called');
|
|
1295
|
return $this->scatterDataColor($sel, $doquery);
|
|
1296
|
}
|
|
1297
|
else{ //nothing
|
|
1298
|
$this->log->info('scatterData called');
|
|
1299
|
//code here
|
|
1300
|
//no dim to bother us, just the measurements
|
|
1301
|
//TODO: get all other dims to show in tooltip
|
|
1302
|
$tables = array();
|
|
1303
|
$tablePairs = array();
|
|
1304
|
$filters = array();
|
|
1305
|
//filters
|
|
1306
|
if(isset($sel['filters'])){
|
|
1307
|
for($fc=0;$fc<count($sel['filters']);$fc++){
|
|
1308
|
$filterfields = explode("-",$sel['filters'][$fc]['name']);
|
|
1309
|
if(count($filterfields)==1){ //no join needed
|
|
1310
|
;//$filters[] = $sel['filters'][$fc];
|
|
1311
|
$sel['filters'][$fc]['name'] = $sel['table'].".".$sel['filters'][$fc]['name'];
|
|
1312
|
}
|
|
1313
|
else{
|
|
1314
|
$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
|
|
1315
|
for($i=0;$i<count($filterfields)-1;$i++){
|
|
1316
|
if(!in_array($filterfields[$i],$tables)){
|
|
1317
|
$tables[] = $filterfields[$i];
|
|
1318
|
}
|
|
1319
|
if($i == 0){
|
|
1320
|
if(!in_array_r(array($sel['table'],$filterfields[$i]), $tablePairs)){
|
|
1321
|
$tablePairs[] = array($sel['table'],$filterfields[$i]);
|
|
1322
|
}
|
|
1323
|
}
|
|
1324
|
else{
|
|
1325
|
if(!in_array_r(array($filterfields[$i-1],$filterfields[$i]),$tablePairs)){
|
|
1326
|
$tablePairs[] = array($filterfields[$i-1],$filterfields[$i]);
|
|
1327
|
}
|
|
1328
|
}
|
|
1329
|
}
|
|
1330
|
}
|
|
1331
|
}
|
|
1332
|
}
|
|
1333
|
//----------------------------------------------------------------//
|
|
1334
|
//having
|
|
1335
|
if(isset($sel['having'])){
|
|
1336
|
for($fc=0;$fc<count($sel['having']);$fc++){
|
|
1337
|
$havingfields = explode("-",$sel['having'][$fc]['name']);
|
|
1338
|
if(count($havingfields)>1){ //join needed
|
|
1339
|
$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
|
|
1340
|
for($i=0;$i<count($havingfields)-1;$i++){
|
|
1341
|
if(!in_array($havingfields[$i],$tables)){
|
|
1342
|
$tables[] = $havingfields[$i];
|
|
1343
|
}
|
|
1344
|
if($i == 0){
|
|
1345
|
if(!in_array_r(array($sel['table'],$havingfields[$i]), $tablePairs)){
|
|
1346
|
$tablePairs[] = array($sel['table'],$havingfields[$i]);
|
|
1347
|
}
|
|
1348
|
}
|
|
1349
|
else{
|
|
1350
|
if(!in_array_r(array($havingfields[$i-1],$havingfields[$i]),$tablePairs)){
|
|
1351
|
$tablePairs[] = array($havingfields[$i-1],$havingfields[$i]);
|
|
1352
|
}
|
|
1353
|
}
|
|
1354
|
}
|
|
1355
|
}
|
|
1356
|
}
|
|
1357
|
}
|
|
1358
|
//----------------------------------------------------------------//
|
|
1359
|
$query = 'SELECT "'.$sel['table'].'"."'.$sel['xaxis']['name'].' " as xfield';
|
|
1360
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
1361
|
$query .= ', "'.$sel['table'].'"."'.$sel['fields'][$i]['fld'].'" as field'.$i;
|
|
1362
|
}
|
|
1363
|
$query .= ' FROM "'.$sel['table'].'"';
|
|
1364
|
if(count($tables)>0) $query .= ', "'.implode("\", \"",$tables).'" ';
|
|
1365
|
|
|
1366
|
$query .= ' WHERE ';
|
|
1367
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
1368
|
if($i>0) $query .=" AND ";
|
|
1369
|
$query .= "field".$i.'" IS NOT NULL';
|
|
1370
|
}
|
|
1371
|
//joins + filters
|
|
1372
|
if(count($tablePairs) > 0 || (isset($sel['filters']) && count($sel['filters']) > 0) || $sel['nulls']){
|
|
1373
|
$query .= " AND ";
|
|
1374
|
for($i=0;$i<count($tablePairs);$i++){
|
|
1375
|
$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
|
|
1376
|
if($i!=count($tablePairs)-1) $query .= " and ";
|
|
1377
|
else $query .= " ";
|
|
1378
|
}
|
|
1379
|
if(isset($sel['filters']) && count($sel['filters'])>0){
|
|
1380
|
if(count($tablePairs)>0) $query .= " and ";
|
|
1381
|
for($i=0;$i<count($sel['filters']);$i++){
|
|
1382
|
if(isset($sel['filters'][$i]['max'])){
|
|
1383
|
$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." and ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
|
|
1384
|
}
|
|
1385
|
else{
|
|
1386
|
$query .= "(";
|
|
1387
|
for($j=0;$j<count($sel['filters'][$i]['values']);$j++){
|
|
1388
|
$query .= $sel['filters'][$i]['name'].'="'.$sel['filters'][$i]['values'][$j].'"';
|
|
1389
|
if($j!=count($sel['filters'][$i]['values'])-1) $query .= " or ";
|
|
1390
|
}
|
|
1391
|
$query .= ")";
|
|
1392
|
}
|
|
1393
|
//$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
|
|
1394
|
if($i!=count($sel['filters'])-1) $query .= " and ";
|
|
1395
|
else $query .= " ";
|
|
1396
|
}
|
|
1397
|
}
|
|
1398
|
if($sel['nulls'] == true){//non null values from xaxis field and dimfield
|
|
1399
|
if((isset($sel['filters']) && count($sel['filters'])>0) || count($tablePairs) > 0){
|
|
1400
|
$query .= " and ";
|
|
1401
|
}
|
|
1402
|
$query .= $xfield . " IS NOT NULL ";
|
|
1403
|
}
|
|
1404
|
}
|
|
1405
|
if(count($sel['having'])>0){
|
|
1406
|
$query .= " HAVING ";
|
|
1407
|
for($i=0;$i<count($sel['having']);$i++){
|
|
1408
|
if($i>0)
|
|
1409
|
$query .= " and ";
|
|
1410
|
$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
|
|
1411
|
}
|
|
1412
|
|
|
1413
|
}
|
|
1414
|
//--------------------------------------------------------//
|
|
1415
|
|
|
1416
|
if(!$doquery){
|
|
1417
|
$this->log->info('scatterData generated query:'.$query);
|
|
1418
|
$this->log->info('will not execute it');
|
|
1419
|
return $query;
|
|
1420
|
}
|
|
1421
|
$this->log->info('scatterData generated query:'.$query);
|
|
1422
|
//echo "scatterplot, simple case:"; echo $query;
|
|
1423
|
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
|
|
1424
|
|
|
1425
|
$this->log->info("result: ".print_r($res,true));
|
|
1426
|
return array('type'=>'scatter','data'=>$res);
|
|
1427
|
}
|
|
1428
|
}
|
|
1429
|
|
|
1430
|
function scatterDataColor($sel, $doquery=true,$persistent){
|
|
1431
|
//one dim, the one for the color
|
|
1432
|
//TODO: get all other dims to show in tooltip
|
|
1433
|
$dimfield = '';
|
|
1434
|
$tables = array();
|
|
1435
|
$tablePairs = array();
|
|
1436
|
//order by it
|
|
1437
|
//may need to join
|
|
1438
|
$dimlist = explode("-",$sel['color']);
|
|
1439
|
if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
|
|
1440
|
$dimfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
|
|
1441
|
}
|
|
1442
|
else{
|
|
1443
|
$dimfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
|
|
1444
|
//briskw tous pinakes enan enan kai tous bazw sta pairs
|
|
1445
|
for($i=0;$i<count($dimlist)-1;$i++){
|
|
1446
|
if(!in_array($dimlist[$i],$tables)){
|
|
1447
|
$tables[] = $dimlist[$i];
|
|
1448
|
}
|
|
1449
|
if($i == count($dimlist)-2){
|
|
1450
|
if(!in_array_r(array($sel['table'],$dimlist[$i]), $tablePairs)){
|
|
1451
|
$tablePairs[] = array($sel['table'],$dimlist[$i]);
|
|
1452
|
}
|
|
1453
|
}
|
|
1454
|
else{
|
|
1455
|
if(!in_array_r(array($dimlist[$i],$dimlist[$i+1]),$tablePairs))
|
|
1456
|
$tablePairs[] = array($dimlist[$i],$dimlist[$i+1]);
|
|
1457
|
}
|
|
1458
|
}
|
|
1459
|
}
|
|
1460
|
//filters
|
|
1461
|
if(isset($sel['filters'])){
|
|
1462
|
for($fc=0;$fc<count($sel['filters']);$fc++){
|
|
1463
|
$filterfields = explode("-",$sel['filters'][$fc]['name']);
|
|
1464
|
if(count($filterfields)==1){ //no join needed
|
|
1465
|
;//$filters[] = $sel['filters'][$fc];
|
|
1466
|
}
|
|
1467
|
else{
|
|
1468
|
$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
|
|
1469
|
for($i=0;$i<count($filterfields)-1;$i++){
|
|
1470
|
if(!in_array($filterfields[$i],$tables)){
|
|
1471
|
$tables[] = $filterfields[$i];
|
|
1472
|
}
|
|
1473
|
if($i == 0){
|
|
1474
|
if(!in_array_r(array($sel['table'],$filterfields[$i]), $tablePairs)){
|
|
1475
|
$tablePairs[] = array($sel['table'],$filterfields[$i]);
|
|
1476
|
}
|
|
1477
|
}
|
|
1478
|
else{
|
|
1479
|
if(!in_array_r(array($filterfields[$i-1],$filterfields[$i]),$tablePairs)){
|
|
1480
|
$tablePairs[] = array($filterfields[$i-1],$filterfields[$i]);
|
|
1481
|
}
|
|
1482
|
}
|
|
1483
|
}
|
|
1484
|
}
|
|
1485
|
}
|
|
1486
|
}
|
|
1487
|
//----------------------------------------------------------------//
|
|
1488
|
//having
|
|
1489
|
if(isset($sel['having'])){
|
|
1490
|
for($fc=0;$fc<count($sel['having']);$fc++){
|
|
1491
|
$havingfields = explode("-",$sel['having'][$fc]['name']);
|
|
1492
|
if(count($havingfields)>1){ //join needed
|
|
1493
|
$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
|
|
1494
|
for($i=0;$i<count($havingfields)-1;$i++){
|
|
1495
|
if(!in_array($havingfields[$i],$tables)){
|
|
1496
|
$tables[] = $havingfields[$i];
|
|
1497
|
}
|
|
1498
|
if($i == 0){
|
|
1499
|
if(!in_array_r(array($sel['table'],$havingfields[$i]), $tablePairs)){
|
|
1500
|
$tablePairs[] = array($sel['table'],$havingfields[$i]);
|
|
1501
|
}
|
|
1502
|
}
|
|
1503
|
else{
|
|
1504
|
if(!in_array_r(array($havingfields[$i-1],$havingfields[$i]),$tablePairs)){
|
|
1505
|
$tablePairs[] = array($havingfields[$i-1],$havingfields[$i]);
|
|
1506
|
}
|
|
1507
|
}
|
|
1508
|
}
|
|
1509
|
}
|
|
1510
|
}
|
|
1511
|
}
|
|
1512
|
//----------------------------------------------------------------//
|
|
1513
|
$query = 'SELECT "'.$sel['table'].'"."'.$sel['xaxis']['name'].'" as xfield';
|
|
1514
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
1515
|
$query .= ', "'.$sel['table'].'"."'.$sel['fields'][$i]['fld'].'" as field'.$i;
|
|
1516
|
}
|
|
1517
|
$query .= ', '.$dimfield.' as dimfield';
|
|
1518
|
$query .= ' FROM "'.$sel['table'].'"';
|
|
1519
|
if(count($tables)>0) $query .= ', "'.implode("\", \"",$tables).'" ';
|
|
1520
|
|
|
1521
|
$query .= " WHERE ";
|
|
1522
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
1523
|
if($i>0) $query .=" AND ";
|
|
1524
|
$query .= "field".$i.'" IS NOT NULL';
|
|
1525
|
}
|
|
1526
|
if(count($tablePairs) > 0){
|
|
1527
|
$query .= " AND ";
|
|
1528
|
for($i=0;$i<count($tablePairs);$i++){
|
|
1529
|
$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
|
|
1530
|
if($i!=count($tablePairs)-1) $query .= "AND ";
|
|
1531
|
else $query .= " ";
|
|
1532
|
}
|
|
1533
|
}
|
|
1534
|
//filters
|
|
1535
|
if(isset($sel['filters']) && count($sel['filters'])>0){
|
|
1536
|
if(count($tablePairs)>0) $query .= " AND ";
|
|
1537
|
for($i=0;$i<count($sel['filters']);$i++){
|
|
1538
|
if(isset($sel['filters'][$i]['max'])){
|
|
1539
|
$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." AND ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
|
|
1540
|
}
|
|
1541
|
else{
|
|
1542
|
$query .= "(";
|
|
1543
|
for($j=0;$j<count($sel['filters'][$i]['values']);$j++){
|
|
1544
|
$query .= $sel['filters'][$i]['name'].'="'.$sel['filters'][$i]['values'][$j].'"';
|
|
1545
|
if($j!=count($sel['filters'][$i]['values'])-1) $query .= " or ";
|
|
1546
|
}
|
|
1547
|
$query .= ")";
|
|
1548
|
}
|
|
1549
|
//$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
|
|
1550
|
if($i!=count($sel['filters'])-1) $query .= " AND ";
|
|
1551
|
else $query .= " ";
|
|
1552
|
}
|
|
1553
|
}
|
|
1554
|
if(count($sel['having'])>0){
|
|
1555
|
$query .= " HAVING ";
|
|
1556
|
for($i=0;$i<count($sel['having']);$i++){
|
|
1557
|
if($i>0)
|
|
1558
|
$query .= " and ";
|
|
1559
|
$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
|
|
1560
|
}
|
|
1561
|
|
|
1562
|
}
|
|
1563
|
|
|
1564
|
if(!$doquery){
|
|
1565
|
$this->log->info('chartDataColor generated query:'.$query);
|
|
1566
|
$this->log->info('will not execute it');
|
|
1567
|
return $query;
|
|
1568
|
}
|
|
1569
|
|
|
1570
|
//--------------------------------------------------------//
|
|
1571
|
$query .= " ORDER BY ".$dimfield;
|
|
1572
|
//echo "scatterplot, case color:"; echo $query;
|
|
1573
|
$this->log->info('scatterDataColor generated query:'.$query);
|
|
1574
|
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
|
|
1575
|
|
|
1576
|
$this->log->info("result: ".print_r($res,true));
|
|
1577
|
return array('type'=>'scatter','data'=>$res);
|
|
1578
|
}
|
|
1579
|
|
|
1580
|
function scatterDataGroup($sel, $doquery=true,$persistent){
|
|
1581
|
//one dim, the one for the group
|
|
1582
|
//aggrs for all fields
|
|
1583
|
//TODO: get all other dims to show in tooltip
|
|
1584
|
$dimfield = '';
|
|
1585
|
$tables = array();
|
|
1586
|
$tablePairs = array();
|
|
1587
|
//order by it
|
|
1588
|
//may need to join
|
|
1589
|
$dimlist = explode("-",$sel['group']);
|
|
1590
|
if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
|
|
1591
|
$dimfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
|
|
1592
|
}
|
|
1593
|
else{
|
|
1594
|
$dimfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
|
|
1595
|
//briskw tous pinakes enan enan kai tous bazw sta pairs
|
|
1596
|
for($i=0;$i<count($dimlist)-1;$i++){
|
|
1597
|
if(!in_array($dimlist[$i],$tables)){
|
|
1598
|
$tables[] = $dimlist[$i];
|
|
1599
|
}
|
|
1600
|
if($i == count($dimlist)-2){
|
|
1601
|
if(!in_array_r(array($sel['table'],$dimlist[$i]), $tablePairs)){
|
|
1602
|
$tablePairs[] = array($sel['table'],$dimlist[$i]);
|
|
1603
|
}
|
|
1604
|
}
|
|
1605
|
else{
|
|
1606
|
if(!in_array_r(array($dimlist[$i],$dimlist[$i+1]),$tablePairs))
|
|
1607
|
$tablePairs[] = array($dimlist[$i],$dimlist[$i+1]);
|
|
1608
|
}
|
|
1609
|
}
|
|
1610
|
}
|
|
1611
|
//filters
|
|
1612
|
if(isset($sel['filters'])){
|
|
1613
|
for($fc=0;$fc<count($sel['filters']);$fc++){
|
|
1614
|
$filterfields = explode("-",$sel['filters'][$fc]['name']);
|
|
1615
|
if(count($filterfields)>1){ //join needed
|
|
1616
|
$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
|
|
1617
|
for($i=0;$i<count($filterfields)-1;$i++){
|
|
1618
|
if(!in_array($filterfields[$i],$tables)){
|
|
1619
|
$tables[] = $filterfields[$i];
|
|
1620
|
}
|
|
1621
|
if($i == 0){
|
|
1622
|
if(!in_array_r(array($sel['table'],$filterfields[$i]), $tablePairs)){
|
|
1623
|
$tablePairs[] = array($sel['table'],$filterfields[$i]);
|
|
1624
|
}
|
|
1625
|
}
|
|
1626
|
else{
|
|
1627
|
if(!in_array_r(array($filterfields[$i-1],$filterfields[$i]),$tablePairs)){
|
|
1628
|
$tablePairs[] = array($filterfields[$i-1],$filterfields[$i]);
|
|
1629
|
}
|
|
1630
|
}
|
|
1631
|
}
|
|
1632
|
}
|
|
1633
|
}
|
|
1634
|
}
|
|
1635
|
//----------------------------------------------------------------//
|
|
1636
|
//having
|
|
1637
|
if(isset($sel['having'])){
|
|
1638
|
for($fc=0;$fc<count($sel['having']);$fc++){
|
|
1639
|
$havingfields = explode("-",$sel['having'][$fc]['name']);
|
|
1640
|
if(count($havingfields)>1){ //join needed
|
|
1641
|
$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
|
|
1642
|
for($i=0;$i<count($havingfields)-1;$i++){
|
|
1643
|
if(!in_array($havingfields[$i],$tables)){
|
|
1644
|
$tables[] = $havingfields[$i];
|
|
1645
|
}
|
|
1646
|
if($i == 0){
|
|
1647
|
if(!in_array_r(array($sel['table'],$havingfields[$i]), $tablePairs)){
|
|
1648
|
$tablePairs[] = array($sel['table'],$havingfields[$i]);
|
|
1649
|
}
|
|
1650
|
}
|
|
1651
|
else{
|
|
1652
|
if(!in_array_r(array($havingfields[$i-1],$havingfields[$i]),$tablePairs)){
|
|
1653
|
$tablePairs[] = array($havingfields[$i-1],$havingfields[$i]);
|
|
1654
|
}
|
|
1655
|
}
|
|
1656
|
}
|
|
1657
|
}
|
|
1658
|
}
|
|
1659
|
}
|
|
1660
|
//----------------------------------------------------------------//
|
|
1661
|
$query = 'SELECT '.$sel['xaxis']['agg'].'("'.$sel['table'].'"."'.$sel['xaxis']['name'].'") as xfield';
|
|
1662
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
1663
|
$query .= ', '.$sel['fields'][$i]['agg'].'("'.$sel['table'].'"."'.$sel['fields'][$i]['fld'].'")';
|
|
1664
|
}
|
|
1665
|
$query .= ' ,'.$dimfield.' as dimfield';
|
|
1666
|
$query .= ' FROM "'.$sel['table'].'"';
|
|
1667
|
if(count($tables)>0) $query .= ', "'.implode("\", \"",$tables).'" ';
|
|
1668
|
|
|
1669
|
$query .= ' WHERE ';
|
|
1670
|
for($i=0;$i<count($sel['fields']);$i++){
|
|
1671
|
if($i>0) $query .=" AND ";
|
|
1672
|
$query .= 'field'.$i." IS NOT NULL";
|
|
1673
|
}
|
|
1674
|
|
|
1675
|
if(count($tablePairs) > 0){
|
|
1676
|
$query .= " AND ";
|
|
1677
|
for($i=0;$i<count($tablePairs);$i++){
|
|
1678
|
$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
|
|
1679
|
if($i!=count($tablePairs)-1) $query .= "AND ";
|
|
1680
|
else $query .= " ";
|
|
1681
|
}
|
|
1682
|
}
|
|
1683
|
//filters
|
|
1684
|
if(isset($sel['filters']) && count($sel['filters'])>0){
|
|
1685
|
$query .= " AND ";
|
|
1686
|
for($i=0;$i<count($sel['filters']);$i++){
|
|
1687
|
if(isset($sel['filters'][$i]['max'])){
|
|
1688
|
$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." AND ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
|
|
1689
|
}
|
|
1690
|
else{
|
|
1691
|
$query .= "(";
|
|
1692
|
for($j=0;$j<count($sel['filters'][$i]['values']);$j++){
|
|
1693
|
$query .= $sel['filters'][$i]['name'].'="'.$sel['filters'][$i]['values'][$j].'"';
|
|
1694
|
if($j!=count($sel['filters'][$i]['values'])-1) $query .= " or ";
|
|
1695
|
}
|
|
1696
|
$query .= ")";
|
|
1697
|
}
|
|
1698
|
//$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
|
|
1699
|
if($i!=count($sel['filters'])-1) $query .= " AND ";
|
|
1700
|
else $query .= " ";
|
|
1701
|
}
|
|
1702
|
}
|
|
1703
|
//--------------------------------------------------------//
|
|
1704
|
$query .= " GROUP BY ".$dimfield;
|
|
1705
|
if(count($sel['having'])>0){
|
|
1706
|
$query .= " HAVING ";
|
|
1707
|
for($i=0;$i<count($sel['having']);$i++){
|
|
1708
|
if($i>0)
|
|
1709
|
$query .= " and ";
|
|
1710
|
$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
|
|
1711
|
}
|
|
1712
|
|
|
1713
|
}
|
|
1714
|
|
|
1715
|
if(!$doquery){
|
|
1716
|
$this->log->info('chartDataGroup generated query:'.$query);
|
|
1717
|
$this->log->info('will not execute it');
|
|
1718
|
return $query;
|
|
1719
|
}
|
|
1720
|
//echo "scatterplot, case group:"; echo $query;
|
|
1721
|
$this->log->info('scatterDataGroup generated query:'.$query);
|
|
1722
|
$res = $this->doQuery($query, PDO::FETCH_NUM);
|
|
1723
|
|
|
1724
|
$this->log->info("result: ".print_r($res,true));
|
|
1725
|
return array('type'=>'scatter','data'=>$res);
|
|
1726
|
}
|
|
1727
|
|
|
1728
|
function performQuery($query,$persistent){
|
|
1729
|
$this->log->info("perfoming query ".$query." for persistence : ".$persistent." \n");
|
|
1730
|
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
|
|
1731
|
|
|
1732
|
$this->log->info("result: ".print_r($res,true));
|
|
1733
|
return $res;
|
|
1734
|
}
|
|
1735
|
|
|
1736
|
function makeQuery($selectedData){
|
|
1737
|
$this->log->info("makeQuery");
|
|
1738
|
$isMeas = false;
|
|
1739
|
$hasGroups = true;
|
|
1740
|
$selectList = array();
|
|
1741
|
$groupbyList = array();
|
|
1742
|
$whereList = array();
|
|
1743
|
$groupby = "";
|
|
1744
|
$orderby = "";
|
|
1745
|
$lastSelect = array();
|
|
1746
|
|
|
1747
|
$tablesList = array();
|
|
1748
|
$tablesPairs = array();
|
|
1749
|
$xaxistables = array();
|
|
1750
|
$dimsList = array();
|
|
1751
|
$dimsList = $this->queryAllDims($selectedData['table']);
|
|
1752
|
//create the tables list and the tables pairs list
|
|
1753
|
$tablesList[] = $selectedData['table'];
|
|
1754
|
foreach($selectedData['series'] as $series) {
|
|
1755
|
$seriesexploded = explode("-",$series);
|
|
1756
|
if(count($seriesexploded) == 1) {
|
|
1757
|
$groupbyList[] = '"'.$selectedData['table'].'"."'.$seriesexploded[0].'"';
|
|
1758
|
$lastSelect[] = $seriesexploded[0];
|
|
1759
|
}
|
|
1760
|
else{
|
|
1761
|
$i=0;
|
|
1762
|
if(!in_array($seriesexploded[$i],$tablesList))
|
|
1763
|
$tablesList[] = $seriesexploded[$i];
|
|
1764
|
|
|
1765
|
if(!in_array_r(array($selectedData['table'],$seriesexploded[0]),$tablesPairs))
|
|
1766
|
$tablesPairs[] = array($selectedData['table'],$seriesexploded[0]);
|
|
1767
|
|
|
1768
|
for($j=1;$j<count($seriesexploded)-1;$j++) {
|
|
1769
|
if(!in_array($seriesexploded[$j],$tablesList))
|
|
1770
|
$tablesList[] = $seriesexploded[$j];
|
|
1771
|
if(!in_array_r(array($seriesexploded[$i],$seriesexploded[$j]),$tablesPairs))
|
|
1772
|
$tablesPairs[] = array($seriesexploded[$i],$seriesexploded[$j]);
|
|
1773
|
$i++;
|
|
1774
|
}
|
|
1775
|
$groupbyList[] = '"'.$seriesexploded[count($seriesexploded)-2].'"."'.$seriesexploded[count($seriesexploded)-1].'"';
|
|
1776
|
$lastSelect[] = $seriesexploded[count($seriesexploded)-1];
|
|
1777
|
}
|
|
1778
|
}
|
|
1779
|
$xaxistables = explode("-",$selectedData['xaxis']['name']);
|
|
1780
|
if($this->checkMeas($selectedData['table'], $xaxistables[count($xaxistables)-1])){
|
|
1781
|
$isMeas = true;
|
|
1782
|
$hasGroups = false;
|
|
1783
|
}
|
|
1784
|
if(count($xaxistables) == 1){
|
|
1785
|
if($isMeas && $selectedData['group'] && $selectedData['group'] != ''){
|
|
1786
|
if($selectedData['xaxis']['agg'] == 'none')
|
|
1787
|
$selectList[] = "avg('".$selectedData['table'].'"."'.$xaxistables[0].'")';
|
|
1788
|
else
|
|
1789
|
$selectList[] = $selectedData['xaxis']['agg']+'("'.$selectedData['table'].'"."'.$xaxistables[0].'")';
|
|
1790
|
}
|
|
1791
|
else{
|
|
1792
|
$selectList[] = '"'.$selectedData['table'].'"."'.$xaxistables[0].'"';
|
|
1793
|
}
|
|
1794
|
if(!$isMeas){
|
|
1795
|
$groupbyList[] = '"'.$selectedData['table'].'"."'.$xaxistables[0].'"';
|
|
1796
|
}
|
|
1797
|
}
|
|
1798
|
else {
|
|
1799
|
$selectList[] = '"'.$xaxistables[count($xaxistables)-2].'"."'.$xaxistables[count($xaxistables)-1].'"';
|
|
1800
|
if(!$isMeas){
|
|
1801
|
$groupbyList[] = '"'.$xaxistables[count($xaxistables)-2].'"."'.$xaxistables[count($xaxistables)-1].'"';
|
|
1802
|
}
|
|
1803
|
if(!in_array($xaxistables[count($xaxistables)-2],$tablesList))
|
|
1804
|
$tablesList[] = $xaxistables[count($xaxistables)-2];
|
|
1805
|
|
|
1806
|
if(!in_array_r(array($selectedData['table'],$xaxistables[0]),$tablesPairs))
|
|
1807
|
$tablesPairs[] = array($selectedData['table'],$xaxistables[0]);
|
|
1808
|
for($i=0;$i<count($xaxistables)-2;$i++) {
|
|
1809
|
if(!in_array($xaxistables[$i],$tablesList))
|
|
1810
|
$tablesList[] = $xaxistables[$i];
|
|
1811
|
|
|
1812
|
if(!in_array_r(array($xaxistables[$i],$xaxistables[$i+1]),$tablesPairs))
|
|
1813
|
$tablesPairs[] = array($xaxistables[$i],$xaxistables[$i+1]);
|
|
1814
|
}
|
|
1815
|
}
|
|
1816
|
$orderby = implode(", ",$groupbyList);
|
|
1817
|
$groupby = implode(", ",$groupbyList);
|
|
1818
|
|
|
1819
|
foreach($tablesPairs as $pair) {
|
|
1820
|
$whereList[] = '"'.$pair[0].'"."'.$pair[1].'" = "'.$pair[1].'".id';
|
|
1821
|
}
|
|
1822
|
|
|
1823
|
foreach($selectedData['fields'] as $field) {
|
|
1824
|
if($hasGroups){
|
|
1825
|
if($field['agg'] == 'none')
|
|
1826
|
$selectList[] ='avg("'.$selectedData['table'].'"."'.$field['fld'].'")';
|
|
1827
|
else
|
|
1828
|
$selectList[] = $field['agg'].'("'.$selectedData['table'].'"."'.$field['fld'].'")';
|
|
1829
|
}
|
|
1830
|
else
|
|
1831
|
$selectList[] = '"'.$selectedData['table'].'"."'.$field['fld'].'"';
|
|
1832
|
}
|
|
1833
|
$query = "select ".implode(", ",$selectList);
|
|
1834
|
if(count($lastSelect)!=0)
|
|
1835
|
$query .= ", ".implode(" || '-' || ",$lastSelect)." as seriesName ";
|
|
1836
|
$query .= " from '".implode("', '",$tablesList)."'";
|
|
1837
|
$query .= " where ";
|
|
1838
|
for($i=0;$i<count($selectedData['fields']);$i++) {
|
|
1839
|
if($i!=0) $query .=" and ";
|
|
1840
|
$query .= '"'.$selectedData['fields'][$i]['fld'] .'" !=""';
|
|
1841
|
}
|
|
1842
|
if(count($whereList) !=0)
|
|
1843
|
$query .= " and ".implode(" and ",$whereList);
|
|
1844
|
if($groupby!='' && $selectedData['color']!= 'yes')
|
|
1845
|
$query .= " group by ".$groupby;
|
|
1846
|
if($orderby!='')
|
|
1847
|
$query .= " order by ".$orderby;
|
|
1848
|
//echo "NOT GROUP"; echo $query;
|
|
1849
|
$this->log->info("generated query: ".$query);
|
|
1850
|
$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
|
|
1851
|
//print_r($res);
|
|
1852
|
//$this->log->info('result set:'.json_encode($res));
|
|
1853
|
if($isMeas) $returnType = 'scatter';
|
|
1854
|
else $returnType = 'chart';
|
|
1855
|
return array('type'=>$returnType,'data'=>$res,'accomDims'=>$dimsList['fields']);
|
|
1856
|
}
|
|
1857
|
|
|
1858
|
function queryAllDims($tablename) {
|
|
1859
|
//get all the dims of the fact table and make a list with all their keys
|
|
1860
|
$fields = array();
|
|
1861
|
$joins = array();
|
|
1862
|
//find the factable
|
|
1863
|
$table = $this->findTable($tablename,'fact');
|
|
1864
|
if($table == false) {
|
|
1865
|
return false;
|
|
1866
|
}
|
|
1867
|
foreach($table['dim'] as $dim) {
|
|
1868
|
if(isset($dim['dimtable']) && $dim['dimtable'] == 'no') {//will need no join
|
|
1869
|
$fields[] = $dim['name'];
|
|
1870
|
}
|
|
1871
|
else{
|
|
1872
|
//find the key(s) put them in the fields list
|
|
1873
|
$key = $this->getDimsKeys($dim['name']);
|
|
1874
|
if(is_array($key)){
|
|
1875
|
foreach($key as $k)
|
|
1876
|
$k = "'".$k."'";
|
|
1877
|
$fields[] = implode(" || ",$key);
|
|
1878
|
}
|
|
1879
|
else
|
|
1880
|
$fields[] = $key;
|
|
1881
|
//and put it in the join list
|
|
1882
|
$joins[] = $dim['name'];
|
|
1883
|
}
|
|
1884
|
}
|
|
1885
|
return array('fields'=>$fields,'joins'=>$joins);
|
|
1886
|
}
|
|
1887
|
}
|
|
1888
|
|
|
1889
|
class QueryTree {
|
|
1890
|
public $tree = Array();
|
|
1891
|
|
|
1892
|
public function __construct($table) {
|
|
1893
|
$this->tree['table'] = $table;
|
|
1894
|
$this->tree['chains'] = Array();
|
|
1895
|
$this->tree['order'] = 'ASC';
|
|
1896
|
}
|
|
1897
|
|
|
1898
|
public function updateFilters($filters) {
|
|
1899
|
for ($i = 0; $i < count($filters); $i++) {
|
|
1900
|
$filterFields = explode("-", $filters[$i]['name']);
|
|
1901
|
$rootField = $filterFields[0];
|
|
1902
|
|
|
1903
|
$field = $filterFields[count($filterFields) - 1];
|
|
1904
|
$filter = Array();
|
|
1905
|
|
|
1906
|
$filter["column"] = $field;
|
|
1907
|
if (isset($filters[$i]['values']))
|
|
1908
|
$filter['values'] = $filters[$i]['values'];
|
|
1909
|
if (isset($filters[$i]['max']))
|
|
1910
|
$filter['max'] = $filters[$i]['max'];
|
|
1911
|
if (isset($filters[$i]['min']))
|
|
1912
|
$filter['min'] = $filters[$i]['min'];
|
|
1913
|
if (isset($filters[$i]['exvalues']))
|
|
1914
|
$filter['exvalues'] = $filters[$i]['exvalues'];
|
|
1915
|
|
|
1916
|
if (count($filterFields) > 1) {
|
|
1917
|
$this->buildChain($filters[$i]['name']);
|
|
1918
|
$this->tree['chains'][$rootField]['tables'][count($filterFields) - 2]['filters'][] = $filter;
|
|
1919
|
} else {
|
|
1920
|
$this->tree['filters'][] = $filter;
|
|
1921
|
}
|
|
1922
|
}
|
|
1923
|
}
|
|
1924
|
|
|
1925
|
public function updateYAxis($yAxisFields) {
|
|
1926
|
foreach ($yAxisFields as $field)
|
|
1927
|
$this->tree['yAxis'][] = $field['agg'] . '("' . $this->tree['table'] . '"."' . $field['fld'] . '")';
|
|
1928
|
}
|
|
1929
|
|
|
1930
|
public function updateXAxis($xAxisField) {
|
|
1931
|
$fields = explode("-", $xAxisField);
|
|
1932
|
|
|
1933
|
if (count($fields) == 1) {
|
|
1934
|
$this->tree['xAxis'] = '"'. $this->tree['table'] .'"."' . $fields[0] . '"';
|
|
1935
|
} else {
|
|
1936
|
if (!isset($this->tree['chains'][$fields[0]])) {
|
|
1937
|
$this->buildChain($xAxisField);
|
|
1938
|
}
|
|
1939
|
|
|
1940
|
$this->tree['xAxis'] = '"' . $fields[0] . '"."' . $fields[count($fields) - 1] . '"';
|
|
1941
|
$this->tree['chains'][$fields[0]]['return'] = '"'.$fields[count($fields) - 2] . '"."' . $fields[count($fields) - 1] . '"';
|
|
1942
|
}
|
|
1943
|
}
|
|
1944
|
|
|
1945
|
public function excludeNulls($exclude = true) {
|
|
1946
|
$this->tree['excludeNulls'] = $exclude;
|
|
1947
|
}
|
|
1948
|
|
|
1949
|
public function updateOrderBy($orderBy) {
|
|
1950
|
if ($orderBy == 'xaxis') {
|
|
1951
|
$this->tree['orderBy'] = $this->tree['xAxis'];
|
|
1952
|
} else {
|
|
1953
|
$fields = explode('-', $orderBy);
|
|
1954
|
|
|
1955
|
$this->tree['orderBy'] = $fields[0] . '("' . $this->tree['table'] . '"."' . $fields[1] . '")';
|
|
1956
|
}
|
|
1957
|
}
|
|
1958
|
|
|
1959
|
public function updateOrder($order) {
|
|
1960
|
if ($order == 'd') {
|
|
1961
|
$this->tree['order'] = 'DESC';
|
|
1962
|
}
|
|
1963
|
}
|
|
1964
|
|
|
1965
|
public function updateLimit($limit) {
|
|
1966
|
$this->tree['limit'] = $limit;
|
|
1967
|
}
|
|
1968
|
|
|
1969
|
public function getQuery() {
|
|
1970
|
$query = 'SELECT ' . $this->tree['xAxis'] . ' as xfield';
|
|
1971
|
|
|
1972
|
if (isset($this->tree['yAxis'])) {
|
|
1973
|
for ($i = 0; $i < count($this->tree['yAxis']); $i++) {
|
|
1974
|
|
|
1975
|
$query .= ', ';
|
|
1976
|
|
|
1977
|
if ($i < count($this->tree['yAxis']) - 1) {
|
|
1978
|
$query .= ' , ';
|
|
1979
|
}
|
|
1980
|
|
|
1981
|
$query .= $this->tree['yAxis'][$i] . ' as field' . $i;
|
|
1982
|
}
|
|
1983
|
}
|
|
1984
|
|
|
1985
|
$query .= ' FROM ' . $this->tree['table'];
|
|
1986
|
|
|
1987
|
foreach ($this->tree['chains'] as $chainName => $chain) {
|
|
1988
|
$query .= ' JOIN (' . $this->buildSubQuery($chain) . ') as ' .$chainName . ' ON "' . $this->tree['table'] . '"."' . $chainName . '" = "' . $chainName . '"."id"' ;
|
|
1989
|
}
|
|
1990
|
|
|
1991
|
if (isset($this->tree['excludeNulls']) || isset($this->tree['filters'])) {
|
|
1992
|
$query .= ' WHERE ';
|
|
1993
|
}
|
|
1994
|
|
|
1995
|
if (isset($this->tree['excludeNulls'])) {
|
|
1996
|
$query .= $this->tree['xAxis'] . ' IS NOT NULL';
|
|
1997
|
}
|
|
1998
|
|
|
1999
|
if (isset($this->tree['filters'])) {
|
|
2000
|
if (substr_compare($query, ' WHERE ', -strlen(' WHERE '), strlen(' WHERE ')) !== 0)
|
|
2001
|
$query .= ' AND ';
|
|
2002
|
|
|
2003
|
for ($i = 0; $i < count($this->tree['filters']); $i++) {
|
|
2004
|
$query .= '(' . $this->buildSubQueryFilter($this->tree['table'], $this->tree['filters'][$i]) .')';
|
|
2005
|
|
|
2006
|
if ($i < count($this->tree['filters']) - 1)
|
|
2007
|
$query .= ' AND ';
|
|
2008
|
}
|
|
2009
|
}
|
|
2010
|
|
|
2011
|
$query .= ' GROUP BY ' . $this->tree['xAxis'];
|
|
2012
|
|
|
2013
|
if (isset($this->tree['orderBy'])) {
|
|
2014
|
$query .= ' ORDER BY ' . $this->tree['orderBy'] . ' ' . $this->tree['order'];
|
|
2015
|
}
|
|
2016
|
|
|
2017
|
if (isset($this->tree['limit'])) {
|
|
2018
|
$query .= ' LIMIT ' . $this->tree['limit'];
|
|
2019
|
}
|
|
2020
|
|
|
2021
|
return $query;
|
|
2022
|
}
|
|
2023
|
|
|
2024
|
public function getTree() {
|
|
2025
|
return $this->tree;
|
|
2026
|
}
|
|
2027
|
|
|
2028
|
/* Helper functions */
|
|
2029
|
|
|
2030
|
private function buildChain($fieldsString) {
|
|
2031
|
$fields = explode("-", $fieldsString);
|
|
2032
|
$rootField = $fields[0];
|
|
2033
|
|
|
2034
|
if (!isset($this->tree['chains'][$rootField])) {
|
|
2035
|
$this->tree['chains'][$rootField] = Array();
|
|
2036
|
$this->tree['chains'][$rootField]['name'] = $rootField;
|
|
2037
|
}
|
|
2038
|
|
|
2039
|
for ($fc = 0; $fc < count($fields) - 1; $fc++) {
|
|
2040
|
$field = $fields[$fc];
|
|
2041
|
|
|
2042
|
if (!isset($this->tree['chains'][$rootField]['tables'][$fc]) ) {
|
|
2043
|
$this->tree['chains'][$rootField]['tables'][] = Array("table" => $field);
|
|
2044
|
}
|
|
2045
|
|
|
2046
|
}
|
|
2047
|
}
|
|
2048
|
|
|
2049
|
private function buildSubQuery($chain) {
|
|
2050
|
$subQuery = 'select distinct "' . $chain['tables'][0]['table'] . '"."id"';
|
|
2051
|
|
|
2052
|
if (isset($chain['return']))
|
|
2053
|
$subQuery .= ', ' . $chain['return'];
|
|
2054
|
|
|
2055
|
$subQuery .= ' from "' . $chain['tables'][0]['table'] . '"';
|
|
2056
|
|
|
2057
|
for ($i = 1; $i < count($chain['tables']); $i++) {
|
|
2058
|
$subQuery .= ' join "' . $chain['tables'][$i]['table'] .'" on "' . $chain['tables'][$i]['table'] . '"."id"="'.$chain['tables'][$i-1]['table'].'"."'.$chain['tables'][$i]['table'].'"';
|
|
2059
|
|
|
2060
|
if (isset($chain['tables'][$i]['filters'])) {
|
|
2061
|
foreach ($chain['tables'][$i]['filters'] as $filter) {
|
|
2062
|
$subQuery .= ' and (' . $this->buildSubQueryFilter($chain['tables'][$i]['table'], $filter) . ') ';
|
|
2063
|
}
|
|
2064
|
}
|
|
2065
|
}
|
|
2066
|
|
|
2067
|
return $subQuery;
|
|
2068
|
}
|
|
2069
|
|
|
2070
|
private function buildSubQueryFilter($tableName, $filter) {
|
|
2071
|
$column = $filter['column'];
|
|
2072
|
$filterText = "";
|
|
2073
|
|
|
2074
|
if (isset($filter['values'])) {
|
|
2075
|
for ($fc = 0; $fc < count($filter['values']); $fc++) {
|
|
2076
|
$value = $filter['values'][$fc];
|
|
2077
|
|
|
2078
|
if ($fc > 0)
|
|
2079
|
$filterText .= ' or ';
|
|
2080
|
$filterText .= '"' . $tableName . '"."'.$column.'" = \'' . $value . '\'';
|
|
2081
|
}
|
|
2082
|
} else if ( isset($filter['exvalues'])) {
|
|
2083
|
for ($fc = 0; $fc < count($filter['exvalues']); $fc++) {
|
|
2084
|
$value = $filter['exvalues'][$fc];
|
|
2085
|
|
|
2086
|
if ($fc > 0)
|
|
2087
|
$filterText .= ' and ';
|
|
2088
|
$filterText .= '"' . $tableName . '"."'.$column.'" != \'' . $value . '\'';
|
|
2089
|
}
|
|
2090
|
} else if (isset($filter['max'])) {
|
|
2091
|
if (strstr($column, 'year') && $filter['max'] == 'today') {
|
|
2092
|
$filterText = $column . ' <= extract(YEAR from now()) and ' . $column . ' >= ' . $filter['min'];
|
|
2093
|
} else if (strstr($column, 'year') && $filter['min'] == 'today') {
|
|
2094
|
$filterText = $column . ' >= extract(YEAR from now()) and ' . $column . ' <= ' . $filter['max'];
|
|
2095
|
} else {
|
|
2096
|
$filterText = $column . ' >= ' . $filter['min'] . ' and ' . $column . ' <= ' . $filter['max'];
|
|
2097
|
}
|
|
2098
|
}
|
|
2099
|
|
|
2100
|
return $filterText;
|
|
2101
|
}
|
|
2102
|
}
|
|
2103
|
|
|
2104
|
?>
|