Project

General

Profile

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){
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(isset($_GET['persistent']))
153
  					$persistent = $_GET['persistent'];
154
				else
155
	  				$persistent = true; //default value is true if not provided
156
  
157
  				if(($this->cache->hmset($myKey, array("query" => $query, "results" => json_encode($results), "persistent" => $persistent, "fetchMode" => $fetchMode))) == false) {
158
  					$this->log->info("adding key ".$myKey." in cache failed."); //predis
159
  				} else 
160
{
161
  					$this->cache->save();
162
  				}
163
  				return $results;
164
			}
165
		} else {
166
			return $this->doQueryNoCache($query, $fetchMode);
167
		}
168
	}
169

    
170
	function doQueryNoCache($query, $fetchMode=PDO::FETCH_BOTH) {
171
	
172
		$stmt = $this->db->query($query); 
173
		if(!$stmt){
174
			$arr = $this->db->errorInfo();
175
			$this->log->error($arr[2]);
176
			return null;
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");
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
					if($meas['name'] == $meas_name){
460
						return true;
461
					}
462
				}
463
			return false;
464
	}
465

    
466
	function getAllDims() {
467
		$dimnames = array();
468
		foreach($this->dimensions as $dim) {
469
			array_push($dimnames,$dim['name']);
470
		}
471
		return $dimnames;
472
	}
473

    
474
	function getDimsFieldsList($tableName) {
475
		$fields = array();
476
		//find the table
477
		$table = $this->findTable($tableName,'dimension');
478
		if($table == false) {
479
			return false;
480
		}
481
		foreach($table['attrib'] as $field) {
482
			if($field['name'] == 'id')
483
				continue;
484
			if(isset($field['analysed'])) {
485
				$fields = array_merge($fields,$this->getDimsFieldsList($field['analysed']));
486
			}
487
			else {
488
				if(isset($field['view']))
489
					$view = $field['view'];
490
				else 
491
					$view = $field['name'];
492
				array_push($fields,array($tableName.".".$field['name'],$tableName.".".$view,$field['type'],$field['data'])); 
493
			}
494
		}
495
		return $fields;
496
	}
497

    
498
	function getDimsList($tablename) {
499
		$fields = array();
500
		//find the factable
501
		$table = $this->findTable($tablename,'fact');
502
		if($table == false) {
503
			return false;
504
		}
505
		foreach($table['dim'] as $dim) {
506
			$temp = $this->getDimsFieldsList($dim['name']);
507
			$fields = array_merge($fields, $temp);
508
		}
509
		return $fields;
510
	}
511
	
512
	function getDimsKeys($dimName) {
513
		$table = $this->findTable($dimName,'dimension');
514
		if($table == false) {
515
			return false;
516
		}
517
		return $table['key'];
518
	}
519
  
520
//-------------------------------------------------------------------------------------\\
521
//-------------------------------------------------------------------------------------\\
522
//----------------------------------DATA READING---------------------------------------\\
523
//-------------------------------------------------------------------------------------\\
524
//-------------------------------------------------------------------------------------\\
525

    
526
function getMeasMetadata($table) {
527
	$query = 'select name, source, sourceUrl, definition, unit from Metadata where inTable="'.$table.'"';
528
	$this->log->info("getMeasMetadata -> generated query: ".$query);
529
	$res = $this->doQuery($query);
530
	if($res == null)
531
		return "false";
532
	
533
	return $res;
534
}
535

    
536
/*
537
gets the name of a fact table and the name of a measurement and returns the minimun, maximum and count distinct values for it
538
*/
539
function measRangeData($facttable, $measurement) {
540
	$query = "SELECT min($measurement), max($measurement), count(distinct $measurement) FROM $facttable WHERE $measurement != ''";
541
	$this->log->info("generated query: ".$query);
542
	$res = $this->doQuery($query);
543
	$res=$res[0];
544
	return array($res[0],$res[1],$res[2]);
545
}
546

    
547
//return the minimun, maximum
548
function getRangeData($table, $field) {
549
	$query = 'SELECT min("'.$field.'"), max("'.$field.'") FROM "'.$table.'"';
550
	if($GLOBALS['type'] == "sqlite")
551
		$query .= ' WHERE "'.$field.'" != ""';
552
	$this->log->info("generated query: ".$query);
553
	$res = $this->doQuery($query);
554
	$res = $res[0];
555
	return array("min"=>$res[0],"max"=>$res[1]);
556
}
557

    
558
//return the distinct values
559
function getDistinctValues($table, $field) {
560
	$query = 'select distinct "'.$field.'" from "'.$table.'" order by "'.$field.'"';
561
	$this->log->info("generated query: ".$query);
562
	$res = $this->doQuery($query, PDO::FETCH_NUM);
563
	return $res;
564
}
565

    
566
function getFilterData($table,$field){
567
	$flag = false;
568
	$myDimTable = $this->findTable($table,'dimension');
569
	$myFactTable = $this->findTable($table,'fact');
570
	//is it a dim?
571
	if($myDimTable != false){
572
		//does it have the field?
573
		if(!isAssoc($myDimTable['attrib'])){
574
			foreach($myDimTable['attrib'] as $attrib){
575
				if($attrib['name'] == $field){
576
					$myField = $attrib;
577
					return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
578
				}
579
			}
580
		}
581
		else{
582
			if($myDimTable['attrib']['name'] == $field){
583
				$myField = $myDimTable['attrib'];
584
				return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
585
			}
586
		}
587
	}
588
	if($myFactTable != false){
589
		//look in the dims
590
		if(!isAssoc($myFactTable['dim'])){
591
			foreach($myFactTable['dim'] as $dim) {
592
				if($dim['name'] == $field){
593
					$myField = $dim;
594
					return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
595
				}
596
			}
597
		}
598
		else{
599
			if($myFactTable['dim']['name'] == $field){
600
				$myField = $myFactTable['dim'];
601
				//$flag = true;
602
				return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
603
			}
604
		}
605
		if(!isAssoc($myFactTable['meas'])){
606
			foreach($myFactTable['meas'] as $meas) {
607
				if($meas['name'] == $field){
608
					$myField = $meas;
609
					return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
610
				}
611
			}
612
		}
613
		else{
614
			if($myFactTable['meas']['name'] == $field){
615
				$myField = $myFactTable['meas'];
616
				return array_merge(array('type'=>$myField['data'],'data'=>$this->getDistinctValues($table, $field)),$this->getRangeData($table, $field));
617
			}
618
		}
619
		$this->log->error('cannot find field '.$field.' in table '.$table);
620
		return "cannot find field ".$field;
621
	}
622

    
623
	$this->log->error("cannot find table ".$table);
624
	return "cannot find table ".$table;
625
}
626
	
627
	function getDefaultData($table) {
628
		$this->log->info("DefaultData -> table:".$table);
629
		if($table !=null && $table!='null') {
630
			$query = 'select * from "defaults" where tablename='."'".$table."'";
631
		}
632
		else{
633
			$query = 'select * from "defaults" where tableorder=(select min(tableorder) from "defaults")';
634
		}
635
		$this->log->info("DefaultData -> query created: ".$query);
636
		$res = $this->doQuery($query);
637
		if($res == null)
638
			return "empty";
639

    
640
		$selectedData = array();
641
		$selectedData['table'] = $res[0][0];
642
		$selectedData['fields'] = array();
643
		$selectedData['fields'][0] = array();
644
		$selectedData['fields'][0]['fld'] = $res[0][2];
645
		$selectedData['fields'][0]['agg'] = $res[0][3];
646
		$selectedData['fields'][0]['id'] = 0;
647
		$selectedData['fields'][0]['type'] = "";
648
		$selectedData['fields'][0]['yaxis'] = 1;
649
		$selectedData['xaxis']['name'] = $res[0][1];
650
		$selectedData['xaxis']['agg'] = 'avg';
651
		$selectedData['group'] = '';
652
		$selectedData['color'] = '';
653
		if($this->checkMeas($selectedData['table'], $selectedData['xaxis']['name'])){
654
			$type='scatter';
655
			$selectedData['type'] = 'scatter';
656
		}
657
		else{
658
			$selectedData['type'] = 'chart';
659
			$type = $res[0][5];
660
		}
661
		$selectedData['size'] = $GLOBALS['size'];
662
		return array('selectedData'=>$selectedData,'type'=>$type);
663
		//return array('selectedData'=>$selectedData, 'data'=>$this->getData($selectedData),'type'=>$type);
664
	}
665

    
666
/*
667
domi tou selectedData:
668
['table'] = fact table name
669
['fields'] = [{'fld':field name,'agg':aggregator for the field,'id': de mas noiazei}, ...]
670
['series'] = [dim field name with the list of the dim tables names that lead to it (eg: Product-Supplier-sup_name), ...]
671
['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 
672
['xaxis'] = {'name': to onoma tou dim h meas pou paei ston x axis, 'agg': o aggregator an einai meas}
673
['type'](optional)
674
stin apli periptwsi to series einai panta keno
675
sto telos exoume tosa series osa fields (to poly 3)
676

    
677
an sto xaxis exoume meas tote exoume scatter plot kai den bazoume to xaxis sto group by
678
an den exoume series tote den exoume group by kai agnooume ta aggs
679
*/
680
/*
681
domi tou query:
682
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
683
ok	from: to table AND ola osa emfanizontai sto series kai to xaxis
684
	where: 1 zeygari gia kathe syndyasmo diplanwn pinakwn pou emfanizetai sto series kai twn arxikwn me to table 
685
	(eg: gia to Product-Supplier tha exoume ena zeygari to Product,Supplier kai ena to Facttable,Product)
686
ok	group by: to xaxis kai ola ta series opws akribws kai sto select
687
ok	order by: to xaxis 
688
*/
689
	function makeQueryGroup($sel){//print_r($sel);
690
		$this->log->info("makeQueryGroup");
691
		/*
692
		select agg(field), xaxisfield, groupfield
693
		from facttable, xaxistables, grouptables
694
		where facttable = xaxistables and xaxistables and facttable = groupstables and groupstables
695
		group by groupfield, xaxisfield (ektos ki an einai meas)
696
		order by groupfield, xaxisfield
697
		*/
698
		$isMeas = false;
699
		$field = "";
700
		$xaxisfield = "";
701
		$groupfield = "";
702
		$tables = array();
703
		$tablesPairs = array();
704
		$tables[] = $sel['table'];
705
		/*yaxis*/ //<---- panta measurement
706
		$field = '"'.$sel['fields'][0]['fld'].'"';
707
		/*xaxis*/ 
708
		$xaxislist = explode("-",$sel['xaxis']['name']);
709
		if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1])){
710
			$isMeas = true;
711
		}
712
		if(!$isMeas){
713
			//an den einai measurement tote einai dimension kai mporei na exei tables
714
			if(count($xaxislist) == 1){ //einai dim alla den analyetai se pinaka
715
				$xaxisfield = '"'.$sel['table'].'"."'.$xaxislist[count($xaxislist)-1].'"';
716
			}
717
			else{
718
				$xaxisfield = '"'.$xaxislist[count($xaxislist)-2].'"."'.$xaxislist[count($xaxislist)-1].'"';
719

    
720
				//briskw tous pinakes enan enan kai tous bazw sta pairs alla kai sto xaxistables
721
				for($i=0;$i<count($xaxislist)-1;$i++){
722
					if(!in_array($xaxislist[$i],$tables)){
723
						$tables[] = $xaxislist[$i];
724
					}
725
					if($i == count($xaxislist)-2){
726
						if(!in_array_r(array($sel['table'],$xaxislist[$i]), $tablesPairs)){
727
							$tablesPairs[] = array($sel['table'],$xaxislist[$i]);
728
						}
729
					}
730
					else{
731
						if(!in_array_r(array($xaxislist[$i],$xaxislist[$i+1]),$tablesPairs))
732
							$tablesPairs[] = array($xaxislist[$i],$xaxislist[$i+1]);
733
					}
734
				}
735
			}
736
		}
737
		else {
738
			//einai meas, den kanw tipota
739
			$xaxisfield = '"'.$sel['table'].'"."'.$sel['xaxis']['name'].'"';
740
			$xaxistables = null;
741
		}
742
		/*group*/
743
		//briskw tous pinakes tou group field kai tous bazw enan enan sto table alla kai sta pairs
744
		$grouplist = explode("-",$sel['group']);
745
		if(count($grouplist) == 1){ //einai dim alla den analyetai se pinaka
746
			$groupfield = '"'.$sel['table'].'"."'.$grouplist[count($grouplist)-1].'"';
747
		}
748
		else{
749
			$groupfield = '"'.$grouplist[count($grouplist)-2].'"."'.$grouplist[count($grouplist)-1].'"';
750
			//briskw tous pinakes enan enan kai tous bazw sta pairs alla kai sto xaxistables
751
			for($i=0;$i<count($grouplist)-1;$i++){
752
				if(!in_array($grouplist[$i],$tables)){
753
					$tables[] = $grouplist[$i];
754
				}
755
				if($i == count($grouplist)-2){
756
					if(!in_array_r(array($sel['table'],$grouplist[$i]), $tablesPairs)){
757
						$tablesPairs[] = array($sel['table'],$grouplist[$i]);
758
					}
759
				}
760
				else{
761
					if(!in_array_r(array($grouplist[$i],$grouplist[$i+1]),$tablesPairs))
762
						$tablesPairs[] = array($grouplist[$i],$grouplist[$i+1]);
763
				}
764
			}
765
		}
766
		//steinw to query
767
		$query = "select ";
768
		/*xaxis*/
769
		if($isMeas && $sel['color'] != 'yes'){ // prepei na balw kai ton aggregator
770
			switch($sel['xaxis']['agg']){
771
				case 'none':
772
				case '':
773
				case 'avg':
774
					$query .= " avg(".$xaxisfield.") ";
775
					break;
776
				default:
777
					$query .= $sel['xaxis']['agg']."(".$xaxisfield.") ";
778
					break;
779
			}
780
		}
781
		else{
782
			$query .= " ".$xaxisfield;
783
		}
784
		/*yaxis*/
785
		if($sel['color'] != 'yes')
786
			switch($sel['fields'][0]['agg']){
787
				case 'none':
788
				case '':
789
				case 'avg':
790
					$query .= ", avg(".$field.") ";
791
					break;
792
				default:
793
					$query .= ", ".$sel['fields'][0]['agg']."(".$field.") ";
794
					break;
795
			}
796
		else
797
			$query .= ', '.$field;
798
				
799
		/*group*/
800
		$query .= ", ".$groupfield;
801
		
802
		$query .= " from ".implode(", ",$tables);
803
		$query .= " where ";
804
		for($i=0;$i<count($sel['fields']);$i++) {
805
			if($i!=0) $query .=" and ";
806
			$query .= '"'.$sel['fields'][$i]['fld'] .'" !=""';
807
		}
808

    
809
		if(count($tablesPairs) > 0){
810
			$query .= " and ";
811
			for($i=0;$i<count($tablesPairs);$i++){
812
				$query .= '"'.$tablesPairs[$i][0].'"."'.$tablesPairs[$i][1].'"="'.$tablesPairs[$i][1].'".id';
813
				if($i!=count($tablesPairs)-1) 	$query .= "and ";
814
				else $query .= " ";
815
			}
816
		}
817
		else $query .= ' ';
818
		if($sel['color'] != 'yes'){
819
			$query .= "group by ".$groupfield;
820
			if(!$isMeas){
821
				$query .=", ".$xaxisfield;
822
			}
823
		}
824
		$query .= " order by ".$groupfield.", ".$xaxisfield;
825
		$this->log->info("generated query: ".$query);
826
		$res = $this->doQuery($query, PDO::FETCH_NUM);
827
		if($isMeas) $returnType = 'scatter';
828
		else $returnType = 'chart';
829
		return array('type'=>$returnType,'data'=>$res,'accomDims'=>$dimsList['fields']);
830
	}
831

    
832
	function getData($sel){
833
		$this->log->info("getting data");
834
		$xaxislist = explode("-",$sel['xaxis']['name']);
835
		$groups = array();
836
		$brsels = array(); $queries = array();
837
		$breakflag = false;
838
		//check if we need to break the query to many (if there are filters that apply to only one field)
839
		if(count($sel['fields']) > 1){
840
			for($i=0;$i<count($sel['filters']);$i++)
841
				if(isset($sel['filters'][$i]['to']) && $sel['filters'][$i]['to'] != -1){
842
					if(!in_array($sel['filters'][$i]['to'],$groups))
843
						$groups[] = $sel['filters'][$i]['to'];
844
					$breakflag = true;
845
				}
846
			for($i=0;$i<count($sel['having']);$i++)
847
				if(isset($sel['having'][$i]['to']) && $sel['having'][$i]['to'] != -1){
848
					if(!in_array($sel['having'][$i]['to'],$groups))
849
						$groups[] = $sel['having'][$i]['to'];
850
					$breakflag = true;
851
				}
852
		}
853
		if($breakflag){
854
			$this->log->info("NEED TO BREAK!");
855
			//will break the query into as many as the different values in the filters and havings to attribute -> count($groups)
856
			$brsels[] = $this->clearselections($sel,$groups,-1,0);
857
			if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
858
				$queries[] = $this->scatterData($brsels[0],false);
859
			else
860
				$queries[] = $this->chartData($brsels[0],false);
861
			for($i=0;$i<count($groups);$i++){
862
				$brsels[] = $this->clearselections($sel,$groups,$groups[$i],$i+1);
863

    
864
				if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
865
					$queries[] = $this->scatterData($brsels[$i+1],false);
866
				else
867
					$queries[] = $this->chartData($brsels[$i+1],false);
868
			}
869

    
870
			$this->log->info("selections: ".print_r($brsels,true));
871
			$this->log->info("user selections updated: ".print_r($sel,true));
872
			$this->log->info("queries: ".print_r($queries,true));
873
			//get all the queries and combine them to one
874
			if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
875
				return $this->makeSuperQuery($queries,$sel,"scatter");
876
			else
877
				return $this->makeSuperQuery($queries,$sel,"chart");
878
		}
879
		else{
880
			$this->log->info("NO NEED TO BREAK!");
881
			if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
882
				return $this->scatterData($sel);
883
			else
884
				return $this->chartData($sel);
885
		}
886
	}
887

    
888
	private function makeSuperQuery($queries,$sel,$type){
889
		$superquerySelects = "SELECT ";
890
		$superquerySelecte = " ";
891
		$superqueryFrom = " FROM ";
892
		$superquerySort = "";
893
		//ta pedia tou xaxis kai tou group tha einai ayta tou outer join
894
		//sta select yparxei prwta to xaxis, meta i lista me ta fields onomatismena, kai meta an yparxei to group
895
		//sto megalo select tha yparxei gia kathe query to xaxis, meta ola ta fields twn queries me ti seira pou einai sto sel
896
		//kai meta gia kathe query to group field
897

    
898
		//prepei na kseroume kathe pedio tou select se poio query anikei, kai to pedio tou order by 
899
		for($q=0;$q<count($queries);$q++){//xaxis
900
			//if($q>0) $superquerySelects .= ", ";
901
			$superquerySelects .= "q".$q.".xfield, ";
902
			if($q>0) $superquerySort .= ", "; 
903
			$superquerySort .= "q".$q.".xfield "; 
904

    
905
			if($sel['group']!='' && $sel['group'] != 'no'){//dimfields
906
				/*if($q>0)*/ $superquerySelecte .= ", ";
907
				$superquerySelecte .= "q".$q.".dimfield ";
908
				$superquerySort .= ", q".$q.".dimfield ";
909
			}
910

    
911
			$superqueryFrom .= "(".$queries[$q].") as q".$q;
912

    
913
			if($q>0){
914
				$superqueryFrom .= " ON q".($q-1).".xfield = q".$q.".xfield ";
915
				if($sel['group']!='' && $sel['group'] != 'no')
916
					$superqueryFrom .= " AND q".($q-1).".dimfield = q".$q.".dimfield ";
917
			}
918

    
919
			if($q<count($queries)-1) {
920
				$superqueryFrom .= " FULL JOIN ";
921
			}
922

    
923
		}
924
		for($f=0;$f<count($sel['fields']);$f++){//fields
925
			$superquerySelects .= "q".$sel['fields'][$f]['q'].".field".$sel['fields'][$f]['i'];
926
			if($f<(count($sel['fields'])-1)) $superquerySelects .=", ";
927
		}
928
		if(isset($sel['sort']) && $sel['sort']!='xaxis'){
929
			$ex = explode(".", $sel['sortq']);
930
			$temp = "q".$ex[0].".field".$ex[1];
931

    
932
			if($sel['order'] && $sel['order'] == 'd')
933
				$temp.= " DESC, ";
934
			else
935
				$temp .= ", ";
936

    
937
			$superquerySort = $temp . $superquerySort;
938
		}
939

    
940
		$superquerySort = " ORDER BY ".$superquerySort;
941

    
942
		$superQuery = $superquerySelects.$superquerySelecte.$superqueryFrom.$superquerySort." LIMIT ".$sel['size'];
943
		$this->log->info("superquery: ".$superQuery);
944
		$res = $this->doQuery($superQuery, PDO::FETCH_NUM);
945
		return $this->combineData($res,count($queries),count($sel['fields']),$type);
946
	}
947

    
948
	private function combineData($data,$queries,$fields,$type){
949
		$newdata = array();
950
		if(count($data[0]) > $queries+$fields)
951
			$dstart = $queries + $fields;
952
		else
953
			$dstart = -1;
954
		$fstart = $queries;
955
		for($d=0;$d<count($data);$d++){
956
			$newdata[$d] = array();
957
			for($i=0;$i<$queries;$i++)
958
				if($data[$d][$i] != null){
959
					$newdata[$d][0] = $data[$d][$i];
960
					break;
961
				}
962
			for($i=$fstart;$i<$fields+$queries;$i++)
963
				$newdata[$d][$i-$fstart+1] = $data[$d][$i];
964
			if($dstart > 0)
965
				for($i=$dstart;$i<count($data[0]);$i++)
966
					if($data[$d][$i] != null){
967
						$newdata[$d][$fields+1] = $data[$d][$i];
968
						break;
969
					}
970
		}
971
		$this->log->info("superquerys result: ".print_r($newdata,true));
972
		return array('type'=>$type,'data'=>$newdata);
973
	}
974

    
975
	private function clearselections(&$sel,$seperate,$field,$index){
976
		$newsel = array();
977
		$fields = array();
978
		$newsel['table'] = $sel['table'];
979
		$newsel['xaxis'] = $sel['xaxis'];
980
		$newsel['group'] = $sel['group'];
981
		$newsel['color'] = $sel['color'];
982
		$newsel['size'] = $sel['size'];
983
		$newsel['fields'] = array();
984
		$newsel['filters'] = array();
985
		$newsel['having'] = array();
986
		if($field == -1 || $field == -2){ //things that apply to whole chart, will remove only the things that apply to one field and that field
987
			for($i=0;$i<count($sel['fields']);$i++){
988
				if(!in_array($i+1,$seperate)){
989
					$newsel['fields'][] = $sel['fields'][$i];
990
					$sel['fields'][$i]['q'] = $index;
991
					$sel['fields'][$i]['i'] = count($newsel['fields'])-1;
992
					$fields[] = $sel['fields'][$i]['agg'] + "-" + $sel['fields'][$i]['fld'];
993
				}
994
			}
995
			for($i=0;$i<count($sel['filters']);$i++){
996
				if(!isset($sel['filters'][$i]['to']) || $sel['filters'][$i]['to'] == -1){
997
					$newsel['filters'][] = $sel['filters'][$i];
998
				}
999
			}
1000
			for($i=0;$i<count($sel['having']);$i++){
1001
				if(!isset($sel['having'][$i]['to']) || $sel['having'][$i]['to'] == -1){
1002
					$newsel['having'][] = $sel['having'][$i];
1003
				}
1004
			}
1005
			if(in_array($sel['sort'],$fields)){
1006
				$sel['sortq'] = $index.".".(count($newsel['fields'])-1);
1007
				//$newsel['sort'] = $sel['sort'];
1008
			}
1009
			//else
1010
				//$newsel['sort'] = 'xaxis';
1011
		}
1012
		else{ //we keep only the field, and the filters and havings that apply to that and the whole chart
1013
			$newsel['fields'][] = $sel['fields'][$field-1];
1014
			$sel['fields'][$field-1]['q'] = $index;
1015
			$sel['fields'][$field-1]['i'] = count($newsel['fields'])-1;
1016
			for($i=0;$i<count($sel['filters']);$i++){
1017
				if(isset($sel['filters'][$i]['to']) && ($sel['filters'][$i]['to'] == $field || $sel['filters'][$i]['to'] == -1)){
1018
					$newsel['filters'][] = $sel['filters'][$i];
1019
				}
1020
			}
1021
			for($i=0;$i<count($sel['having']);$i++){
1022
				if(isset($sel['having'][$i]['to']) && $sel['having'][$i]['to'] == $field){
1023
					$newsel['having'][] = $sel['having'][$i];
1024
				}
1025
			}
1026
			if(in_array($sel['sort'],$fields)){
1027
				$newsel['sort'] = $sel['sort'];
1028
			}
1029
			else
1030
				$newsel['sort'] = 'xaxis';
1031
		}
1032
		return $newsel;
1033
	}
1034

    
1035
	function chartData($sel, $doquery=true){
1036
		if($sel['group']!='' && $sel['group'] != 'no'){	//group
1037
			return $this->chartDataGroup($sel, $doquery);
1038
		}
1039
		else{	
1040

    
1041
$this->log->info("sel: ".print_r($sel,true));
1042
			$tree = new QueryTree($sel['table']);
1043
			$tree->updateXAxis($sel['xaxis']['name']);
1044
			$tree->updateYAxis($sel['fields']);
1045
			$tree->updateFilters($sel['filters']);
1046
			if (isset($sel['sort'])) {
1047
				$tree->updateOrderBy($sel['sort']);
1048
				$tree->updateOrder($sel['order']);
1049
			}
1050
			$tree->updateLimit($sel['size']);
1051
			$tree->excludeNulls($sel['nulls']);
1052

    
1053
$this->log->info("tree: ".print_r($tree->tree, true));
1054

    
1055
			$query = $tree->getQuery();
1056

    
1057
			if(!$doquery){
1058
				$this->log->info('chartData generated query:'.$query);
1059
				$this->log->info('will not execute it');
1060
				return $query;
1061
			}
1062

    
1063
			$this->log->info('chartData generated tree: '.print_r($tree->getTree(), true));
1064
			$this->log->info('chartData generated query: '.$query);
1065
			$res = $this->doQuery($query, PDO::FETCH_NUM);
1066
			$this->log->info("result: ".print_r($res,true));
1067
			return array('type'=>'chart','data'=>$res);
1068
		}
1069
	
1070
	}
1071
	
1072
	function chartDataGroup($sel, $doquery=true){
1073
		//exoume dyo dims, ena tou xaxis kai ena tou group
1074
		//kanoume tin idia diadikasia kai gia ta dyo
1075
		$xfield = '';
1076
		$dimfield = '';
1077
		$tables = array();
1078
		$tablePairs = array();
1079
		$filters = array();
1080
		//order by it
1081
		//may need to join
1082
		//gia to xaxis
1083
		$dimlist = explode("-",$sel['xaxis']['name']);
1084
		if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
1085
			$xfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
1086
		}
1087
		else{
1088
			$xfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
1089
			//briskw tous pinakes enan enan kai tous bazw sta pairs
1090
			for($i=0;$i<count($dimlist)-1;$i++){
1091
				if(!in_array($dimlist[$i],$tables)){
1092
					$tables[] = $dimlist[$i];
1093
				}
1094
				if($i == 0){
1095
					if(!in_array_r(array($sel['table'],$dimlist[$i]), $tablePairs)){
1096
						$tablePairs[] = array($sel['table'],$dimlist[$i]);
1097
					}
1098
				}
1099
				else{
1100
					if(!in_array_r(array($dimlist[$i-1],$dimlist[$i]),$tablePairs))
1101
						$tablePairs[] = array($dimlist[$i-1],$dimlist[$i]);
1102
				}
1103
			}
1104
		}
1105
		//gia to group
1106
		$dimlist = explode("-",$sel['group']);
1107
		if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
1108
			$dimfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
1109
		}
1110
		else{
1111
			$dimfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
1112
			//briskw tous pinakes enan enan kai tous bazw sta pairs
1113
			for($i=0;$i<count($dimlist)-1;$i++){
1114
				if(!in_array($dimlist[$i],$tables)){
1115
					$tables[] = $dimlist[$i];
1116
				}
1117
				if($i == 0){
1118
					if(!in_array_r(array($sel['table'],$dimlist[$i]), $tablePairs)){
1119
						$tablePairs[] = array($sel['table'],$dimlist[$i]);
1120
					}
1121
				}
1122
				else{
1123
					if(!in_array_r(array($dimlist[$i-1],$dimlist[$i]),$tablePairs))
1124
						$tablePairs[] = array($dimlist[$i-1],$dimlist[$i]);
1125
				}
1126
			}
1127
		}
1128
		//filters
1129
		if(isset($sel['filters'])){
1130
			for($fc=0;$fc<count($sel['filters']);$fc++){
1131
				$filterfields = explode("-",$sel['filters'][$fc]['name']);
1132
				if(count($filterfields)>1){ //join needed
1133
					$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
1134
					for($i=0;$i<count($filterfields)-1;$i++){
1135
						if(!in_array($filterfields[$i],$tables)){
1136
							$tables[] = $filterfields[$i];
1137
						}
1138
						if($i == 0){
1139
							if(!in_array_r(array($sel['table'],$filterfields[$i]), $tablePairs)){
1140
								$tablePairs[] = array($sel['table'],$filterfields[$i]);
1141
							}
1142
						}
1143
						else{
1144
							if(!in_array_r(array($filterfields[$i-1],$filterfields[$i]),$tablePairs)){
1145
								$tablePairs[] = array($filterfields[$i-1],$filterfields[$i]);
1146
							}
1147
						}
1148
					}
1149
				}
1150
				else
1151
                                	$sel['filters'][$fc]['name'] = $sel['table'].".".$sel['filters'][$fc]['name'];
1152
			}
1153
		}
1154
		//----------------------------------------------------------------//
1155
		//having
1156
		if(isset($sel['having'])){
1157
			for($fc=0;$fc<count($sel['having']);$fc++){
1158
				$havingfields = explode("-",$sel['having'][$fc]['name']);
1159
				if(count($havingfields)>1){ //join needed
1160
					$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
1161
					for($i=0;$i<count($havingfields)-1;$i++){
1162
						if(!in_array($havingfields[$i],$tables)){
1163
							$tables[] = $havingfields[$i];
1164
						}
1165
						if($i == 0){
1166
							if(!in_array_r(array($sel['table'],$havingfields[$i]), $tablePairs)){
1167
								$tablePairs[] = array($sel['table'],$havingfields[$i]);
1168
							}
1169
						}
1170
						else{
1171
							if(!in_array_r(array($havingfields[$i-1],$havingfields[$i]),$tablePairs)){
1172
								$tablePairs[] = array($havingfields[$i-1],$havingfields[$i]);
1173
							}
1174
						}
1175
					}
1176
				}
1177
			}
1178
		}
1179
		//----------------------------------------------------------------//
1180
		$query = 'SELECT '.$xfield.' as xfield';
1181
		for($i=0;$i<count($sel['fields']);$i++){
1182
			$query .= ', '.$sel['fields'][$i]['agg'].'("'.$sel['table'].'"."'.$sel['fields'][$i]['fld'].'") as field'.$i;
1183
		}
1184
		$query .= ', '.$dimfield.' as dimfield';
1185
		$query .= ' FROM "'.$sel['table'].'"';
1186
		if(count($tables)>0) $query .= ', "'.implode("\", \"",$tables).'" ';
1187
		/*$query .= ' WHERE "'.$sel['xaxis']['name'].'" !=""';
1188
		for($i=0;$i<count($sel['fields']);$i++){
1189
			$query .=" and ";
1190
			$query .= '"'.$sel['fields'][$i]['fld'] .'" !=""';
1191
		}*/
1192
		if(count($tablePairs) > 0 || (isset($sel['filters']) && count($sel['filters']) > 0) || $sel['nulls']){
1193
			$query .= " WHERE ";
1194
			for($i=0;$i<count($tablePairs);$i++){
1195
				$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
1196
				if($i!=count($tablePairs)-1) 	$query .= " and ";
1197
				else $query .= " ";
1198
			}
1199
			//filters
1200
			if(isset($sel['filters']) && count($sel['filters'])>0){
1201
				if(count($tablePairs)>0)
1202
					$query .= " and ";
1203
				for($i=0;$i<count($sel['filters']);$i++){
1204
					if(isset($sel['filters'][$i]['max'])){
1205
						if(strstr($sel['filters'][$i]['name'],"year") !== false  && $sel['filters'][$i]['max']=='today')
1206
							$query .= "(".$sel['filters'][$i]['name']."<= extract(YEAR from now()) and ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")";
1207
						else if(strstr($sel['filters'][$i]['name'],"year") !== false  && $sel['filters'][$i]['min']=='today')
1208
							$query .="(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." and ".$sel['filters'][$i]['name'].">= extract(YEAR from now()))" ;
1209
						else
1210
							$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." and ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
1211
					}
1212
					else if(isset($sel['filters'][$i]['values'])){
1213
						$query .= "(";
1214
						for($j=0;$j<count($sel['filters'][$i]['values']);$j++){
1215
							//$query .= $sel['filters'][$i]['name'].'="'.$sel['filters'][$i]['values'][$j].'"';
1216
							$query .= $sel['filters'][$i]['name'].'=\''.$sel['filters'][$i]['values'][$j].'\'';
1217
							if($j!=count($sel['filters'][$i]['values'])-1) $query .= " or ";
1218
						}
1219
						$query .= ")";
1220
					}
1221
					else if(isset($sel['filters'][$i]['exvalues'])){
1222
						$query .= "(";
1223
						for($j=0;$j<count($sel['filters'][$i]['exvalues']);$j++){
1224
							$query .= $sel['filters'][$i]['name'].'!=\''.$sel['filters'][$i]['exvalues'][$j].'\'';
1225
							if($j!=count($sel['filters'][$i]['exvalues'])-1) $query .= " and ";
1226
						}
1227
						$query .= ")";
1228
					}
1229
					//$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
1230
					if($i!=count($sel['filters'])-1) 	$query .= " and ";
1231
					else $query .= " ";
1232
				}
1233
			}
1234
			if($sel['nulls'] == true){//non null values from xaxis field and dimfield
1235
				if((isset($sel['filters']) && count($sel['filters'])>0) || count($tablePairs) > 0){
1236
					$query .= " and ";
1237
				}
1238
				$query .= $xfield . " IS NOT NULL AND ". $dimfield . " IS NOT NULL ";
1239
			}
1240
		}
1241
		//--------------------------------------------------------//
1242
		$query .= " GROUP BY ".$dimfield.', '.$xfield;
1243
		if(count($sel['having'])>0){
1244
			$query .= " HAVING "; 
1245
			for($i=0;$i<count($sel['having']);$i++){
1246
				if($i>0)
1247
					$query .= " and ";
1248
				$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
1249
			}
1250
			
1251
		}
1252
		
1253
		if(!$doquery){
1254
			$this->log->info('chartDataGroup generated query:'.$query);
1255
			$this->log->info('will not execute it');
1256
			return $query;
1257
		}
1258
		
1259
		//need to find the field to order the result set by
1260
		$query.= " ORDER BY ";
1261
		if(isset($sel['sort']) && $sel['sort']!='xaxis'){
1262
			$ex = explode("-", $sel['sort']);
1263
			$query .= $ex[0]."(".$sel['table'].".".$ex[1].")";
1264
			if($sel['order'] && $sel['order'] == 'd')
1265
				$query.= " DESC";
1266
			$query .= ", ".$xfield;
1267
		}
1268
		else{
1269
			$query.= $xfield;
1270
			if($sel['order'] && $sel['order'] == 'd')
1271
				$query.= " DESC";
1272
		}
1273
		$query.=', '.$dimfield;
1274
		$query.= " LIMIT ".$sel['size'];
1275
		//echo $query;
1276
		$this->log->info('chartDataGroup generated query:'.$query);
1277
		$res = $this->doQuery($query, PDO::FETCH_NUM);
1278
		$this->log->info("result: ".print_r($res,true));
1279
		return array('type'=>'chart','data'=>$res);
1280
	
1281
	}
1282
	
1283
	function scatterData($sel, $doquery=true){
1284
		//group, color, or nothing
1285
		//many fields or one
1286
		if($sel['group']!='' && $sel['group'] != 'no'){			//group
1287
			$this->log->info('scatterDataGroup called');
1288
			return $this->scatterDataGroup($sel, $doquery);
1289
		}
1290
		else if($sel['color']!='' && $sel['color'] != 'no'){	//color
1291
			$this->log->info('scatterDataColor called');
1292
			return $this->scatterDataColor($sel, $doquery);
1293
		}
1294
		else{													//nothing
1295
			$this->log->info('scatterData called');
1296
			//code here
1297
			//no dim to bother us, just the measurements
1298
			//TODO: get all other dims to show in tooltip
1299
			$tables = array();
1300
			$tablePairs = array();
1301
			$filters = array();
1302
			//filters
1303
			if(isset($sel['filters'])){
1304
				for($fc=0;$fc<count($sel['filters']);$fc++){
1305
					$filterfields = explode("-",$sel['filters'][$fc]['name']);
1306
					if(count($filterfields)==1){ //no join needed
1307
						;//$filters[] = $sel['filters'][$fc];
1308
                                                $sel['filters'][$fc]['name'] = $sel['table'].".".$sel['filters'][$fc]['name'];
1309
					}
1310
					else{
1311
						$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
1312
						for($i=0;$i<count($filterfields)-1;$i++){
1313
							if(!in_array($filterfields[$i],$tables)){
1314
								$tables[] = $filterfields[$i];
1315
							}
1316
							if($i == 0){
1317
								if(!in_array_r(array($sel['table'],$filterfields[$i]), $tablePairs)){
1318
									$tablePairs[] = array($sel['table'],$filterfields[$i]);
1319
								}
1320
							}
1321
							else{
1322
								if(!in_array_r(array($filterfields[$i-1],$filterfields[$i]),$tablePairs)){
1323
									$tablePairs[] = array($filterfields[$i-1],$filterfields[$i]);
1324
								}
1325
							}
1326
						}
1327
					}
1328
				}
1329
			}
1330
			//----------------------------------------------------------------//
1331
			//having
1332
			if(isset($sel['having'])){
1333
				for($fc=0;$fc<count($sel['having']);$fc++){
1334
					$havingfields = explode("-",$sel['having'][$fc]['name']);
1335
					if(count($havingfields)>1){ //join needed
1336
						$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
1337
						for($i=0;$i<count($havingfields)-1;$i++){
1338
							if(!in_array($havingfields[$i],$tables)){
1339
								$tables[] = $havingfields[$i];
1340
							}
1341
							if($i == 0){
1342
								if(!in_array_r(array($sel['table'],$havingfields[$i]), $tablePairs)){
1343
									$tablePairs[] = array($sel['table'],$havingfields[$i]);
1344
								}
1345
							}
1346
							else{
1347
								if(!in_array_r(array($havingfields[$i-1],$havingfields[$i]),$tablePairs)){
1348
									$tablePairs[] = array($havingfields[$i-1],$havingfields[$i]);
1349
								}
1350
							}
1351
						}
1352
					}
1353
				}
1354
			}
1355
			//----------------------------------------------------------------//
1356
			$query = 'SELECT "'.$sel['table'].'"."'.$sel['xaxis']['name'].' " as xfield';
1357
			for($i=0;$i<count($sel['fields']);$i++){
1358
				$query .= ', "'.$sel['table'].'"."'.$sel['fields'][$i]['fld'].'" as field'.$i;
1359
			}
1360
			$query .= ' FROM "'.$sel['table'].'"';
1361
			if(count($tables)>0) $query .= ', "'.implode("\", \"",$tables).'" ';
1362
			
1363
			$query .= ' WHERE ';
1364
			for($i=0;$i<count($sel['fields']);$i++){
1365
				if($i>0) $query .=" AND ";
1366
				$query .= "field".$i.'" IS NOT NULL';
1367
			}
1368
			//joins + filters
1369
			if(count($tablePairs) > 0 || (isset($sel['filters']) && count($sel['filters']) > 0) || $sel['nulls']){
1370
				$query .= " AND ";
1371
				for($i=0;$i<count($tablePairs);$i++){
1372
					$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
1373
					if($i!=count($tablePairs)-1) 	$query .= " and ";
1374
					else $query .= " ";
1375
				}
1376
				if(isset($sel['filters']) && count($sel['filters'])>0){
1377
					if(count($tablePairs)>0) $query .= " and ";
1378
					for($i=0;$i<count($sel['filters']);$i++){
1379
						if(isset($sel['filters'][$i]['max'])){
1380
							$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." and ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
1381
						}
1382
						else{
1383
							$query .= "(";
1384
							for($j=0;$j<count($sel['filters'][$i]['values']);$j++){
1385
								$query .= $sel['filters'][$i]['name'].'="'.$sel['filters'][$i]['values'][$j].'"';
1386
								if($j!=count($sel['filters'][$i]['values'])-1) $query .= " or ";
1387
							}
1388
							$query .= ")";
1389
						}
1390
						//$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
1391
						if($i!=count($sel['filters'])-1) 	$query .= " and ";
1392
						else $query .= " ";
1393
					}
1394
				}
1395
				if($sel['nulls'] == true){//non null values from xaxis field and dimfield
1396
					if((isset($sel['filters']) && count($sel['filters'])>0) || count($tablePairs) > 0){
1397
						$query .= " and ";
1398
					}
1399
					$query .= $xfield . " IS NOT NULL ";
1400
				}
1401
			}
1402
			if(count($sel['having'])>0){
1403
				$query .= " HAVING "; 
1404
				for($i=0;$i<count($sel['having']);$i++){
1405
					if($i>0)
1406
						$query .= " and ";
1407
					$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
1408
				}
1409
			
1410
			}
1411
			//--------------------------------------------------------//
1412
			
1413
			if(!$doquery){
1414
				$this->log->info('scatterData generated query:'.$query);
1415
				$this->log->info('will not execute it');
1416
				return $query;
1417
			}
1418
			$this->log->info('scatterData generated query:'.$query);
1419
			//echo "scatterplot, simple case:"; echo $query;
1420
			$res = $this->doQuery($query, PDO::FETCH_NUM);
1421
			
1422
			$this->log->info("result: ".print_r($res,true));
1423
			return array('type'=>'scatter','data'=>$res);
1424
		}
1425
	}
1426
	
1427
	function scatterDataColor($sel, $doquery=true){
1428
		//one dim, the one for the color
1429
		//TODO: get all other dims to show in tooltip
1430
		$dimfield = '';
1431
		$tables = array();
1432
		$tablePairs = array();
1433
		//order by it
1434
		//may need to join
1435
		$dimlist = explode("-",$sel['color']);
1436
		if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
1437
			$dimfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
1438
		}
1439
		else{
1440
			$dimfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
1441
			//briskw tous pinakes enan enan kai tous bazw sta pairs
1442
			for($i=0;$i<count($dimlist)-1;$i++){
1443
				if(!in_array($dimlist[$i],$tables)){
1444
					$tables[] = $dimlist[$i];
1445
				}
1446
				if($i == count($dimlist)-2){
1447
					if(!in_array_r(array($sel['table'],$dimlist[$i]), $tablePairs)){
1448
						$tablePairs[] = array($sel['table'],$dimlist[$i]);
1449
					}
1450
				}
1451
				else{
1452
					if(!in_array_r(array($dimlist[$i],$dimlist[$i+1]),$tablePairs))
1453
						$tablePairs[] = array($dimlist[$i],$dimlist[$i+1]);
1454
				}
1455
			}
1456
		}
1457
		//filters
1458
		if(isset($sel['filters'])){
1459
			for($fc=0;$fc<count($sel['filters']);$fc++){
1460
				$filterfields = explode("-",$sel['filters'][$fc]['name']);
1461
				if(count($filterfields)==1){ //no join needed
1462
					;//$filters[] = $sel['filters'][$fc];
1463
				}
1464
				else{
1465
					$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
1466
					for($i=0;$i<count($filterfields)-1;$i++){
1467
						if(!in_array($filterfields[$i],$tables)){
1468
							$tables[] = $filterfields[$i];
1469
						}
1470
						if($i == 0){
1471
							if(!in_array_r(array($sel['table'],$filterfields[$i]), $tablePairs)){
1472
								$tablePairs[] = array($sel['table'],$filterfields[$i]);
1473
							}
1474
						}
1475
						else{
1476
							if(!in_array_r(array($filterfields[$i-1],$filterfields[$i]),$tablePairs)){
1477
								$tablePairs[] = array($filterfields[$i-1],$filterfields[$i]);
1478
							}
1479
						}
1480
					}
1481
				}
1482
			}
1483
		}
1484
		//----------------------------------------------------------------//
1485
		//having
1486
		if(isset($sel['having'])){
1487
			for($fc=0;$fc<count($sel['having']);$fc++){
1488
				$havingfields = explode("-",$sel['having'][$fc]['name']);
1489
				if(count($havingfields)>1){ //join needed
1490
					$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
1491
					for($i=0;$i<count($havingfields)-1;$i++){
1492
						if(!in_array($havingfields[$i],$tables)){
1493
							$tables[] = $havingfields[$i];
1494
						}
1495
						if($i == 0){
1496
							if(!in_array_r(array($sel['table'],$havingfields[$i]), $tablePairs)){
1497
								$tablePairs[] = array($sel['table'],$havingfields[$i]);
1498
							}
1499
						}
1500
						else{
1501
							if(!in_array_r(array($havingfields[$i-1],$havingfields[$i]),$tablePairs)){
1502
								$tablePairs[] = array($havingfields[$i-1],$havingfields[$i]);
1503
							}
1504
						}
1505
					}
1506
				}
1507
			}
1508
		}
1509
		//----------------------------------------------------------------//
1510
		$query = 'SELECT "'.$sel['table'].'"."'.$sel['xaxis']['name'].'" as xfield';
1511
		for($i=0;$i<count($sel['fields']);$i++){
1512
			$query .= ', "'.$sel['table'].'"."'.$sel['fields'][$i]['fld'].'" as field'.$i;
1513
		}
1514
		$query .= ', '.$dimfield.' as dimfield';
1515
		$query .= ' FROM "'.$sel['table'].'"';
1516
		if(count($tables)>0) $query .= ', "'.implode("\", \"",$tables).'" ';
1517
		
1518
		$query .= " WHERE ";
1519
		for($i=0;$i<count($sel['fields']);$i++){
1520
			if($i>0) $query .=" AND ";
1521
			$query .= "field".$i.'" IS NOT NULL';
1522
		}
1523
		if(count($tablePairs) > 0){
1524
			$query .= " AND ";
1525
			for($i=0;$i<count($tablePairs);$i++){
1526
				$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
1527
				if($i!=count($tablePairs)-1) 	$query .= "AND ";
1528
				else $query .= " ";
1529
			}
1530
		}
1531
		//filters
1532
		if(isset($sel['filters']) && count($sel['filters'])>0){
1533
			if(count($tablePairs)>0) $query .= " AND ";
1534
			for($i=0;$i<count($sel['filters']);$i++){
1535
				if(isset($sel['filters'][$i]['max'])){
1536
					$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." AND ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
1537
				}
1538
				else{
1539
					$query .= "(";
1540
					for($j=0;$j<count($sel['filters'][$i]['values']);$j++){
1541
						$query .= $sel['filters'][$i]['name'].'="'.$sel['filters'][$i]['values'][$j].'"';
1542
						if($j!=count($sel['filters'][$i]['values'])-1) $query .= " or ";
1543
					}
1544
					$query .= ")";
1545
				}
1546
				//$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
1547
				if($i!=count($sel['filters'])-1) 	$query .= " AND ";
1548
				else $query .= " ";
1549
			}
1550
		}
1551
		if(count($sel['having'])>0){
1552
			$query .= " HAVING "; 
1553
			for($i=0;$i<count($sel['having']);$i++){
1554
				if($i>0)
1555
					$query .= " and ";
1556
				$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
1557
			}
1558
			
1559
		}
1560
		
1561
		if(!$doquery){
1562
			$this->log->info('chartDataColor generated query:'.$query);
1563
			$this->log->info('will not execute it');
1564
			return $query;
1565
		}
1566
		
1567
		//--------------------------------------------------------//
1568
		$query .= " ORDER BY ".$dimfield;
1569
		//echo "scatterplot, case color:"; echo $query;
1570
		$this->log->info('scatterDataColor generated query:'.$query);
1571
		$res = $this->doQuery($query, PDO::FETCH_NUM);
1572
		
1573
		$this->log->info("result: ".print_r($res,true));
1574
		return array('type'=>'scatter','data'=>$res);
1575
	}
1576
	
1577
	function scatterDataGroup($sel, $doquery=true){
1578
		//one dim, the one for the group
1579
		//aggrs for all fields
1580
		//TODO: get all other dims to show in tooltip
1581
		$dimfield = '';
1582
		$tables = array();
1583
		$tablePairs = array();
1584
		//order by it
1585
		//may need to join
1586
		$dimlist = explode("-",$sel['group']);
1587
		if(count($dimlist) == 1){ //einai dim alla den analyetai se pinaka
1588
			$dimfield = '"'.$sel['table'].'"."'.$dimlist[count($dimlist)-1].'"';
1589
		}
1590
		else{
1591
			$dimfield = '"'.$dimlist[count($dimlist)-2].'"."'.$dimlist[count($dimlist)-1].'"';
1592
			//briskw tous pinakes enan enan kai tous bazw sta pairs
1593
			for($i=0;$i<count($dimlist)-1;$i++){
1594
				if(!in_array($dimlist[$i],$tables)){
1595
					$tables[] = $dimlist[$i];
1596
				}
1597
				if($i == count($dimlist)-2){
1598
					if(!in_array_r(array($sel['table'],$dimlist[$i]), $tablePairs)){
1599
						$tablePairs[] = array($sel['table'],$dimlist[$i]);
1600
					}
1601
				}
1602
				else{
1603
					if(!in_array_r(array($dimlist[$i],$dimlist[$i+1]),$tablePairs))
1604
						$tablePairs[] = array($dimlist[$i],$dimlist[$i+1]);
1605
				}
1606
			}
1607
		}
1608
		//filters
1609
		if(isset($sel['filters'])){
1610
			for($fc=0;$fc<count($sel['filters']);$fc++){
1611
				$filterfields = explode("-",$sel['filters'][$fc]['name']);
1612
				if(count($filterfields)>1){ //join needed
1613
					$sel['filters'][$fc]['name'] = '"'.$filterfields[count($filterfields)-2].'"."'.$filterfields[count($filterfields)-1].'"';
1614
					for($i=0;$i<count($filterfields)-1;$i++){
1615
						if(!in_array($filterfields[$i],$tables)){
1616
							$tables[] = $filterfields[$i];
1617
						}
1618
						if($i == 0){
1619
							if(!in_array_r(array($sel['table'],$filterfields[$i]), $tablePairs)){
1620
								$tablePairs[] = array($sel['table'],$filterfields[$i]);
1621
							}
1622
						}
1623
						else{
1624
							if(!in_array_r(array($filterfields[$i-1],$filterfields[$i]),$tablePairs)){
1625
								$tablePairs[] = array($filterfields[$i-1],$filterfields[$i]);
1626
							}
1627
						}
1628
					}
1629
				}
1630
			}
1631
		}
1632
		//----------------------------------------------------------------//
1633
		//having
1634
		if(isset($sel['having'])){
1635
			for($fc=0;$fc<count($sel['having']);$fc++){
1636
				$havingfields = explode("-",$sel['having'][$fc]['name']);
1637
				if(count($havingfields)>1){ //join needed
1638
					$sel['having'][$fc]['name'] = '"'.$havingfields[count($havingfields)-2].'"."'.$havingfields[count($havingfields)-1].'"';
1639
					for($i=0;$i<count($havingfields)-1;$i++){
1640
						if(!in_array($havingfields[$i],$tables)){
1641
							$tables[] = $havingfields[$i];
1642
						}
1643
						if($i == 0){
1644
							if(!in_array_r(array($sel['table'],$havingfields[$i]), $tablePairs)){
1645
								$tablePairs[] = array($sel['table'],$havingfields[$i]);
1646
							}
1647
						}
1648
						else{
1649
							if(!in_array_r(array($havingfields[$i-1],$havingfields[$i]),$tablePairs)){
1650
								$tablePairs[] = array($havingfields[$i-1],$havingfields[$i]);
1651
							}
1652
						}
1653
					}
1654
				}
1655
			}
1656
		}
1657
		//----------------------------------------------------------------//
1658
		$query = 'SELECT '.$sel['xaxis']['agg'].'("'.$sel['table'].'"."'.$sel['xaxis']['name'].'") as xfield';
1659
		for($i=0;$i<count($sel['fields']);$i++){
1660
			$query .= ', '.$sel['fields'][$i]['agg'].'("'.$sel['table'].'"."'.$sel['fields'][$i]['fld'].'")';
1661
		}
1662
		$query .= ' ,'.$dimfield.' as dimfield';
1663
		$query .= ' FROM "'.$sel['table'].'"';
1664
		if(count($tables)>0) $query .= ', "'.implode("\", \"",$tables).'" ';
1665
		
1666
		$query .= ' WHERE ';
1667
		for($i=0;$i<count($sel['fields']);$i++){
1668
			if($i>0) $query .=" AND ";
1669
			$query .= 'field'.$i." IS NOT NULL";
1670
		}
1671

    
1672
		if(count($tablePairs) > 0){
1673
			$query .= " AND ";
1674
			for($i=0;$i<count($tablePairs);$i++){
1675
				$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
1676
				if($i!=count($tablePairs)-1) 	$query .= "AND ";
1677
				else $query .= " ";
1678
			}
1679
		}
1680
		//filters
1681
		if(isset($sel['filters']) && count($sel['filters'])>0){
1682
			$query .= " AND ";
1683
			for($i=0;$i<count($sel['filters']);$i++){
1684
				if(isset($sel['filters'][$i]['max'])){
1685
					$query .= "(".$sel['filters'][$i]['name']."<=".$sel['filters'][$i]['max']." AND ".$sel['filters'][$i]['name'].">=".$sel['filters'][$i]['min'].")" ;
1686
				}
1687
				else{
1688
					$query .= "(";
1689
					for($j=0;$j<count($sel['filters'][$i]['values']);$j++){
1690
						$query .= $sel['filters'][$i]['name'].'="'.$sel['filters'][$i]['values'][$j].'"';
1691
						if($j!=count($sel['filters'][$i]['values'])-1) $query .= " or ";
1692
					}
1693
					$query .= ")";
1694
				}
1695
				//$query .= '"'.$tablePairs[$i][0].'"."'.$tablePairs[$i][1].'"="'.$tablePairs[$i][1].'".id';
1696
				if($i!=count($sel['filters'])-1) 	$query .= " AND ";
1697
				else $query .= " ";
1698
			}
1699
		}
1700
		//--------------------------------------------------------//
1701
		$query .= " GROUP BY ".$dimfield;
1702
		if(count($sel['having'])>0){
1703
			$query .= " HAVING "; 
1704
			for($i=0;$i<count($sel['having']);$i++){
1705
				if($i>0)
1706
					$query .= " and ";
1707
				$query .= $sel['having'][$i]['agg']."(".$sel['having'][$i]['name'].")".$sel['having'][$i]['fnc'].$sel['having'][$i]['value'];
1708
			}
1709
			
1710
		}
1711
		
1712
		if(!$doquery){
1713
			$this->log->info('chartDataGroup generated query:'.$query);
1714
			$this->log->info('will not execute it');
1715
			return $query;
1716
		}
1717
		//echo "scatterplot, case group:"; echo $query;
1718
		$this->log->info('scatterDataGroup generated query:'.$query);
1719
		$res = $this->doQuery($query, PDO::FETCH_NUM);
1720
		
1721
		$this->log->info("result: ".print_r($res,true));
1722
		return array('type'=>'scatter','data'=>$res);
1723
	}
1724
	
1725
	function performQuery($query){
1726
		$this->log->info("perfoming query");		
1727
		$res = $this->doQuery($query, PDO::FETCH_NUM);
1728
		
1729
		$this->log->info("result: ".print_r($res,true));
1730
		return $res;
1731
	}
1732
	
1733
	function makeQuery($selectedData){
1734
		$this->log->info("makeQuery");
1735
		$isMeas = false;
1736
		$hasGroups = true;
1737
		$selectList = array();
1738
		$groupbyList = array();
1739
		$whereList = array();
1740
		$groupby = "";
1741
		$orderby = ""; 
1742
		$lastSelect = array();
1743
		
1744
		$tablesList = array();
1745
		$tablesPairs = array();
1746
		$xaxistables = array();
1747
		$dimsList = array();
1748
		$dimsList = $this->queryAllDims($selectedData['table']);
1749
		//create the tables list and the tables pairs list
1750
		$tablesList[] = $selectedData['table'];
1751
		foreach($selectedData['series'] as $series) {
1752
			$seriesexploded = explode("-",$series);
1753
			if(count($seriesexploded) == 1) {
1754
				$groupbyList[] = '"'.$selectedData['table'].'"."'.$seriesexploded[0].'"';
1755
				$lastSelect[] = $seriesexploded[0];
1756
			}
1757
			else{
1758
				$i=0;
1759
				if(!in_array($seriesexploded[$i],$tablesList))
1760
					$tablesList[] = $seriesexploded[$i];
1761
				
1762
				if(!in_array_r(array($selectedData['table'],$seriesexploded[0]),$tablesPairs))
1763
					$tablesPairs[] = array($selectedData['table'],$seriesexploded[0]);
1764
					
1765
				for($j=1;$j<count($seriesexploded)-1;$j++) {
1766
					if(!in_array($seriesexploded[$j],$tablesList))
1767
						$tablesList[] = $seriesexploded[$j];
1768
					if(!in_array_r(array($seriesexploded[$i],$seriesexploded[$j]),$tablesPairs))
1769
						$tablesPairs[] = array($seriesexploded[$i],$seriesexploded[$j]);
1770
					$i++;
1771
				}
1772
				$groupbyList[] = '"'.$seriesexploded[count($seriesexploded)-2].'"."'.$seriesexploded[count($seriesexploded)-1].'"';
1773
				$lastSelect[] = $seriesexploded[count($seriesexploded)-1];
1774
			}
1775
		}
1776
		$xaxistables = explode("-",$selectedData['xaxis']['name']);
1777
		if($this->checkMeas($selectedData['table'], $xaxistables[count($xaxistables)-1])){
1778
			$isMeas = true;
1779
			$hasGroups = false;
1780
		}
1781
		if(count($xaxistables) == 1){
1782
			if($isMeas && $selectedData['group'] && $selectedData['group'] != ''){
1783
				if($selectedData['xaxis']['agg'] == 'none')
1784
					$selectList[] = "avg('".$selectedData['table'].'"."'.$xaxistables[0].'")';
1785
				else
1786
					$selectList[] = $selectedData['xaxis']['agg']+'("'.$selectedData['table'].'"."'.$xaxistables[0].'")';
1787
			}
1788
			else{
1789
				$selectList[] = '"'.$selectedData['table'].'"."'.$xaxistables[0].'"';
1790
			}
1791
			if(!$isMeas){
1792
				$groupbyList[] = '"'.$selectedData['table'].'"."'.$xaxistables[0].'"';
1793
			}
1794
		}
1795
		else {
1796
			$selectList[] = '"'.$xaxistables[count($xaxistables)-2].'"."'.$xaxistables[count($xaxistables)-1].'"';
1797
			if(!$isMeas){
1798
				$groupbyList[] = '"'.$xaxistables[count($xaxistables)-2].'"."'.$xaxistables[count($xaxistables)-1].'"';
1799
			}
1800
			if(!in_array($xaxistables[count($xaxistables)-2],$tablesList))
1801
				$tablesList[] = $xaxistables[count($xaxistables)-2];
1802
			
1803
			if(!in_array_r(array($selectedData['table'],$xaxistables[0]),$tablesPairs))
1804
					$tablesPairs[] = array($selectedData['table'],$xaxistables[0]);
1805
			for($i=0;$i<count($xaxistables)-2;$i++) {
1806
				if(!in_array($xaxistables[$i],$tablesList))
1807
					$tablesList[] = $xaxistables[$i];
1808
			
1809
				if(!in_array_r(array($xaxistables[$i],$xaxistables[$i+1]),$tablesPairs))
1810
					$tablesPairs[] = array($xaxistables[$i],$xaxistables[$i+1]);
1811
			}
1812
		}
1813
		$orderby = implode(", ",$groupbyList);
1814
		$groupby = implode(", ",$groupbyList);
1815

    
1816
		foreach($tablesPairs as $pair) {
1817
			$whereList[] = '"'.$pair[0].'"."'.$pair[1].'" = "'.$pair[1].'".id';
1818
		}
1819
		
1820
		foreach($selectedData['fields'] as $field) {
1821
			if($hasGroups){
1822
				if($field['agg'] == 'none')
1823
					$selectList[] ='avg("'.$selectedData['table'].'"."'.$field['fld'].'")';
1824
				else
1825
					$selectList[] = $field['agg'].'("'.$selectedData['table'].'"."'.$field['fld'].'")';
1826
			}
1827
			else
1828
				$selectList[] = '"'.$selectedData['table'].'"."'.$field['fld'].'"';
1829
		}
1830
		$query = "select ".implode(", ",$selectList);
1831
		if(count($lastSelect)!=0)
1832
			$query .= ", ".implode(" || '-' || ",$lastSelect)." as seriesName ";
1833
		$query .= " from '".implode("', '",$tablesList)."'";
1834
		$query .= " where ";
1835
		for($i=0;$i<count($selectedData['fields']);$i++) {
1836
			if($i!=0) $query .=" and ";
1837
			$query .= '"'.$selectedData['fields'][$i]['fld'] .'" !=""';
1838
		}
1839
		if(count($whereList) !=0)
1840
			$query .= " and ".implode(" and ",$whereList);
1841
		if($groupby!='' && $selectedData['color']!= 'yes')
1842
			$query .= " group by ".$groupby;
1843
		if($orderby!='')
1844
			$query .= " order by ".$orderby;
1845
		//echo "NOT GROUP"; echo $query;
1846
		$this->log->info("generated query: ".$query);
1847
		$res = $this->doQuery($query, PDO::FETCH_NUM);
1848
		//print_r($res);
1849
		//$this->log->info('result set:'.json_encode($res));
1850
		if($isMeas) $returnType = 'scatter';
1851
		else $returnType = 'chart';
1852
		return array('type'=>$returnType,'data'=>$res,'accomDims'=>$dimsList['fields']);
1853
	}
1854

    
1855
	function queryAllDims($tablename) {
1856
		//get all the dims of the fact table and make a list with all their keys
1857
		$fields = array();
1858
		$joins = array();
1859
		//find the factable
1860
		$table = $this->findTable($tablename,'fact');
1861
		if($table == false) {
1862
			return false;
1863
		}
1864
		foreach($table['dim'] as $dim) {
1865
			if(isset($dim['dimtable']) && $dim['dimtable'] == 'no') {//will need no join
1866
				$fields[] = $dim['name'];
1867
			}
1868
			else{
1869
				//find the key(s) put them in the fields list 
1870
				$key = $this->getDimsKeys($dim['name']);
1871
				if(is_array($key)){
1872
					foreach($key as $k)
1873
						$k = "'".$k."'";
1874
					$fields[] = implode(" || ",$key);
1875
				}
1876
				else
1877
					$fields[] = $key;
1878
				//and put it in the join list
1879
				$joins[] = $dim['name'];
1880
			}
1881
		}
1882
		return array('fields'=>$fields,'joins'=>$joins);
1883
	}
1884
}
1885

    
1886
class QueryTree {
1887
	public $tree = Array();
1888
	
1889
	public function __construct($table) {
1890
		$this->tree['table'] = $table;
1891
		$this->tree['chains'] = Array();
1892
		$this->tree['order'] = 'ASC';
1893
	}
1894
	
1895
	public function updateFilters($filters) {
1896
		for ($i = 0; $i < count($filters); $i++) {
1897
			$filterFields = explode("-", $filters[$i]['name']);
1898
			$rootField = $filterFields[0];
1899

    
1900
			$field = $filterFields[count($filterFields) - 1];
1901
			$filter = Array();
1902
			
1903
			$filter["column"] = $field;
1904
			if (isset($filters[$i]['values']))
1905
				$filter['values'] = $filters[$i]['values'];
1906
			if (isset($filters[$i]['max']))
1907
				$filter['max'] = $filters[$i]['max'];
1908
			if (isset($filters[$i]['min']))
1909
				$filter['min'] = $filters[$i]['min'];
1910
			if (isset($filters[$i]['exvalues']))
1911
				$filter['exvalues'] = $filters[$i]['exvalues'];
1912
			
1913
			if (count($filterFields) > 1) {
1914
				$this->buildChain($filters[$i]['name']);
1915
				$this->tree['chains'][$rootField]['tables'][count($filterFields) - 2]['filters'][] = $filter;
1916
			} else {
1917
				$this->tree['filters'][] = $filter;
1918
			}
1919
		}
1920
	}
1921
	
1922
	public function updateYAxis($yAxisFields) {
1923
		foreach ($yAxisFields as $field)
1924
			$this->tree['yAxis'][] = $field['agg'] . '("' . $this->tree['table'] . '"."' . $field['fld'] . '")';
1925
	}
1926
	
1927
	public function updateXAxis($xAxisField) {
1928
		$fields = explode("-", $xAxisField);
1929
		
1930
		if (count($fields) == 1) {
1931
			$this->tree['xAxis'] = '"'. $this->tree['table'] .'"."' . $fields[0] . '"';
1932
		} else {
1933
			if (!isset($this->tree['chains'][$fields[0]])) {
1934
				$this->buildChain($xAxisField);
1935
			}
1936
			
1937
			$this->tree['xAxis'] = '"' . $fields[0] . '"."' . $fields[count($fields) - 1] . '"';
1938
			$this->tree['chains'][$fields[0]]['return'] = '"'.$fields[count($fields) - 2] . '"."' . $fields[count($fields) - 1] . '"';
1939
		}
1940
	}
1941
	
1942
	public function excludeNulls($exclude = true) {
1943
		$this->tree['excludeNulls'] = $exclude;
1944
	}
1945
	
1946
	public function updateOrderBy($orderBy) {
1947
		if ($orderBy == 'xaxis') {
1948
			$this->tree['orderBy'] = $this->tree['xAxis'];
1949
		} else {
1950
			$fields = explode('-', $orderBy);
1951
			
1952
			$this->tree['orderBy'] = $fields[0] . '("' . $this->tree['table'] . '"."' . $fields[1] . '")';
1953
		}
1954
	}
1955
	
1956
	public function updateOrder($order) {
1957
		if ($order == 'd') {
1958
			$this->tree['order'] = 'DESC';
1959
		}
1960
	}
1961
	
1962
	public function updateLimit($limit) {
1963
		$this->tree['limit'] = $limit;
1964
	}
1965
	
1966
	public function getQuery() {
1967
		$query = 'SELECT ' . $this->tree['xAxis'] . ' as xfield';
1968
		
1969
		if (isset($this->tree['yAxis'])) {
1970
			for ($i = 0; $i < count($this->tree['yAxis']); $i++) {
1971
				
1972
				$query .= ', ';
1973
				
1974
				if ($i < count($this->tree['yAxis']) - 1) {
1975
					$query .= ' , ';
1976
				}
1977
				
1978
				$query .= $this->tree['yAxis'][$i] . ' as field' . $i;
1979
			}
1980
		}
1981
		
1982
		$query .= ' FROM ' . $this->tree['table'];
1983
		
1984
		foreach ($this->tree['chains'] as $chainName => $chain) {
1985
			$query .= ' JOIN (' . $this->buildSubQuery($chain) . ') as ' .$chainName . ' ON "' . $this->tree['table'] . '"."' . $chainName . '" = "' . $chainName . '"."id"' ;
1986
		}
1987
		
1988
		if (isset($this->tree['excludeNulls']) || isset($this->tree['filters'])) {
1989
			$query .= ' WHERE ';
1990
		}
1991
		
1992
		if (isset($this->tree['excludeNulls'])) {
1993
		 	$query .= $this->tree['xAxis'] . ' IS NOT NULL';
1994
		}
1995
		
1996
		if (isset($this->tree['filters'])) {
1997
			if (substr_compare($query, ' WHERE ', -strlen(' WHERE '), strlen(' WHERE ')) !== 0)
1998
				$query .= ' AND ';
1999
				
2000
			for ($i = 0; $i < count($this->tree['filters']); $i++) {
2001
				$query .= '(' . $this->buildSubQueryFilter($this->tree['table'], $this->tree['filters'][$i]) .')';
2002
				
2003
				if ($i < count($this->tree['filters']) - 1)
2004
					$query .= ' AND ';
2005
			}
2006
		}
2007
		
2008
		$query .= ' GROUP BY ' . $this->tree['xAxis'];
2009
		
2010
		if (isset($this->tree['orderBy'])) {
2011
			$query .= ' ORDER BY ' . $this->tree['orderBy'] . ' ' . $this->tree['order'];
2012
		}
2013
		
2014
		if (isset($this->tree['limit'])) {
2015
			$query .= ' LIMIT ' . $this->tree['limit'];
2016
		}
2017
		
2018
		return $query;
2019
	}
2020
	
2021
	public function getTree() {
2022
		return $this->tree;
2023
	}
2024
	
2025
	/*	Helper functions  */
2026
	
2027
	private function buildChain($fieldsString) {
2028
		$fields = explode("-", $fieldsString);
2029
		$rootField = $fields[0];
2030

    
2031
		if (!isset($this->tree['chains'][$rootField])) {
2032
			$this->tree['chains'][$rootField] = Array();
2033
			$this->tree['chains'][$rootField]['name'] = $rootField;
2034
		}
2035
					
2036
		for ($fc = 0; $fc < count($fields) - 1; $fc++) {
2037
			$field = $fields[$fc];
2038

    
2039
			if (!isset($this->tree['chains'][$rootField]['tables'][$fc]) ) {
2040
				$this->tree['chains'][$rootField]['tables'][] = Array("table" => $field);
2041
			}					
2042

    
2043
		}
2044
	}
2045
	
2046
	private function buildSubQuery($chain) {
2047
		$subQuery = 'select distinct "' . $chain['tables'][0]['table'] . '"."id"';
2048
		
2049
		if (isset($chain['return']))
2050
			$subQuery .= ', ' . $chain['return'];
2051

    
2052
		$subQuery .= ' from "' . $chain['tables'][0]['table'] . '"';
2053
		
2054
		for ($i = 1; $i < count($chain['tables']); $i++) {
2055
			$subQuery .= ' join "' . $chain['tables'][$i]['table'] .'" on "' . $chain['tables'][$i]['table'] . '"."id"="'.$chain['tables'][$i-1]['table'].'"."'.$chain['tables'][$i]['table'].'"';
2056
			
2057
			if (isset($chain['tables'][$i]['filters'])) {
2058
				foreach ($chain['tables'][$i]['filters'] as $filter) {
2059
					$subQuery .= ' and (' . $this->buildSubQueryFilter($chain['tables'][$i]['table'], $filter) . ') ';
2060
				}
2061
			}
2062
		}
2063
		
2064
		return $subQuery;
2065
	}
2066
	
2067
	private function buildSubQueryFilter($tableName, $filter) {
2068
		$column = $filter['column'];
2069
		$filterText = "";
2070
		
2071
		if (isset($filter['values'])) {
2072
			for ($fc = 0; $fc < count($filter['values']); $fc++) {
2073
				$value = $filter['values'][$fc];
2074
				
2075
				if ($fc > 0)
2076
					$filterText .= ' or ';
2077
				$filterText .= '"' . $tableName . '"."'.$column.'" = \'' . $value . '\'';
2078
			}
2079
		} else if (	isset($filter['exvalues'])) {
2080
			for ($fc = 0; $fc < count($filter['exvalues']); $fc++) {
2081
				$value = $filter['exvalues'][$fc];
2082

    
2083
				if ($fc > 0)
2084
					$filterText .= ' and ';
2085
				$filterText .= '"' . $tableName . '"."'.$column.'" != \'' . $value . '\'';
2086
			}
2087
		} else if (isset($filter['max'])) {
2088
			if (strstr($column, 'year') && $filter['max'] == 'today') {
2089
				$filterText = $column . ' <= extract(YEAR from now()) and ' . $column . ' >= ' . $filter['min'];
2090
			} else 	if (strstr($column, 'year') && $filter['min'] == 'today') {
2091
				$filterText = $column . ' >= extract(YEAR from now()) and ' . $column . ' <= ' . $filter['max'];
2092
			} else {
2093
				$filterText = $column . ' >= ' . $filter['min'] . ' and ' . $column . ' <= ' . $filter['max'];
2094
			}
2095
		}
2096
		
2097
		return $filterText;
2098
	}
2099
}
2100

    
2101
?>
(1-1/30)