Project

General

Profile

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