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,$persistent){
135
	
136
		if($this->cache != null){
137
		      //TODO md5($query) -> REDIS hash set keys
138
			$myKey = md5($query);
139
			$this->log->debug("Searching for key: ".$myKey);
140
				
141
			if($this->cache->exists($myKey)) { 
142
			
143
				$results = $this->cache->hget($myKey, "results");
144
				return json_decode($results, true);
145
				
146
			} else {
147
			
148
				$this->log->debug("not found in cache"); 
149
				$results = $this->doQueryNoCache($query, $fetchMode);
150
				$this->log->info("adding in cache. key: ".$myKey); 
151
				
152
				if($persistent==null||$persistent=='1')
153
  			          {$persistent = "true";}
154
  				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
		$list = $this->doQuery("select tableName from defaults order by tableorder","false");
215
		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
			foreach($table['meas'] as $meas) {
456
                                      
457
		if($meas== $meas_name)
458
				{		return true;
459
					}
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
		$res = $this->doQuery($query,"false");
635
		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
	function makeQueryGroup($sel,$persistent){//print_r($sel);
688
		$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
		$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
825
		if($isMeas) $returnType = 'scatter';
826
		else $returnType = 'chart';
827
		return array('type'=>$returnType,'data'=>$res,'accomDims'=>$dimsList['fields']);
828
	}
829

    
830
	function getData($sel,$persistent){
831
		$this->log->info("getting data");
832
		$xaxislist = explode("-",$sel['xaxis']['name']);
833
		$groups = array();
834
		$brsels = array(); $queries = array();
835
		$breakflag = false;
836

    
837
               
838
		
839
                     //check if we need to break the query to many (if there are filters that apply to only one field)
840
		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
				$queries[] = $this->chartData($brsels[0],false,$persistent);
862
			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
					$queries[] = $this->chartData($brsels[$i+1],false,$persistent);
869
			}
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
				return $this->makeSuperQuery($queries,$sel,"scatter",$persistent);
877
			else
878
				return $this->makeSuperQuery($queries,$sel,"chart",$persistent);
879
		}
880
		else{
881
			$this->log->info("NO NEED TO BREAK!");
882
			if($this->checkMeas($sel['table'], $xaxislist[count($xaxislist)-1]))
883
				return $this->scatterData($sel,$persistent);
884
			else
885
				return $this->chartData($sel,true,$persistent);
886
		}
887
	}
888

    
889
	private function makeSuperQuery($queries,$sel,$type,$persistent){
890
		$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
		$res = $this->doQuery($superQuery, PDO::FETCH_NUM,$persistent);
946
		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
	function chartData($sel, $doquery=true,$persistent){
1037
		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
			if (isset($sel['sort'])) {$tree->updateOrderBy($sel['sort']);}
1048
if(isset($sel['order'])){$tree->updateOrder($sel['order']);}
1049
if(isset($sel['size'])){$tree->updateLimit($sel['size']);}
1050
if(isset($sel['nulls'])){$tree->excludeNulls($sel['nulls']);}
1051

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

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

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

    
1062
			$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
			return array('type'=>'chart','data'=>$res);
1067
		}
1068
	
1069
	}
1070
	
1071
	function chartDataGroup($sel, $doquery=true,$persistent){
1072
		//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
		$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
1277
		$this->log->info("result: ".print_r($res,true));
1278
		return array('type'=>'chart','data'=>$res);
1279
	
1280
	}
1281
	
1282
	function scatterData($sel, $doquery=true,$persistent){
1283
		//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
			$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
1420
			
1421
			$this->log->info("result: ".print_r($res,true));
1422
			return array('type'=>'scatter','data'=>$res);
1423
		}
1424
	}
1425
	
1426
	function scatterDataColor($sel, $doquery=true,$persistent){
1427
		//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
		$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
1571
		
1572
		$this->log->info("result: ".print_r($res,true));
1573
		return array('type'=>'scatter','data'=>$res);
1574
	}
1575
	
1576
	function scatterDataGroup($sel, $doquery=true,$persistent){
1577
		//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
	function performQuery($query,$persistent){
1725
		$this->log->info("perfoming query");		
1726
		$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
1727
		
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
		$res = $this->doQuery($query, PDO::FETCH_NUM,$persistent);
1847
		//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
?>
(1-1/21)