<?php
/*
Has a database object, a codifier object and a cache object ....
gets requests for data (from view), codifys them, checks if there are in the cache, if not gets them from db, if yes, gets them from cache
places restrictions on them and sends them to model
also sets up the view

*/
require_once("paths.php");
//setting up the logger
require_once('./js/log4php/Logger.php');
Logger::configure('./js/log4php/log4php.xml');
$logger = Logger::getLogger("controller");
require_once('MYDB.php');
//require_once('error_handler.php');
//require_once('help_functions.php');

class Controller {

	private $database;
	private $log;
	private $data = null;
	private $chart;
	private $chart2;
	private $colors = array();
	private $size = 30;
 
 
	private $myqueries = null;

	function __construct($myflag = true){

		// this creates a logger named "Controller"
		$this->log = Logger::getLogger(__CLASS__);
		$this->database = new MYDB();
		
		if($myflag) {
			$this->database->loadSchema($GLOBALS['schema_file']);
		} else {
			$this->database->doConnect($GLOBALS['schema_file']);
		}

		$this->myqueries = array();
		$this->myqueries['mperf1'] = array();
		$this->myqueries['mperf1']['q'] = "select funding_lvl1, max(daysfromend) from result_projects, project where result_projects.project=project.id and funding_lvl0='FP7' group by funding_lvl1 order by funding_lvl1";	
		$this->myqueries['aperf1'] = array();
		$this->myqueries['aperf1']['q'] = "select funding_lvl1, avg(daysfromend) from result_projects, project where result_projects.project=project.id and funding_lvl0='FP7' and daysfromend>=0 group by funding_lvl1 order by funding_lvl1";	

		$this->myqueries['mperf2'] = array();
		$this->myqueries['mperf2']['q'] = "select funding_lvl2, max(daysfromend) from result_projects, project where result_projects.project=project.id and funding_lvl0='FP7' group by funding_lvl2 order by funding_lvl2";	
		$this->myqueries['aperf2'] = array();
		$this->myqueries['aperf2']['q'] = "select funding_lvl2, avg(daysfromend) from result_projects, project where result_projects.project=project.id and funding_lvl0='FP7'  and daysfromend>=0 group by funding_lvl2 order by funding_lvl2";	

		$this->myqueries['maperf1'] = array();
		$this->myqueries['maperf1']['q'] = "select funding_lvl1, max(daysfromend), avg(daysfromend) from result_projects, project where result_projects.project=project.id and funding_lvl0='FP7'  and daysfromend>=0 group by funding_lvl1 order by funding_lvl1";	

		$this->myqueries['maperf2'] = array();
		$this->myqueries['maperf2']['q'] = "select funding_lvl2, max(daysfromend), avg(daysfromend) from result_projects, project where result_projects.project=project.id and funding_lvl0='FP7'  and daysfromend>=0 group by funding_lvl2 order by funding_lvl2";	

                $this->myqueries['pubsperf1'] = array();
                $this->myqueries['pubsperf1']['q'] = "select r.year, count(distinct p.id) as field0, funding_lvl1 from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id where r.year >= 2007 and funding_lvl0='FP7' and r.year < 2017 group by funding_lvl1, r.year order by r.year, funding_lvl1";
		$this->myqueries['pubsperf2'] = array();

//		$this->myqueries['pubsperf2']['q'] = "select r.year, count(distinct r.id) as field0, funding_lvl2 from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id where extract(year from date(r.date)) >= 2007 and date <> '' and funding_lvl0='FP7' and funding_lvl2 <> '' and date(r.date) < 2014 group by funding_lvl2, extract(year from date(r.date)) order by  extract(year from date(r.date)), funding_lvl2 ";

$this->myqueries['pubsperf2']['q'] ="  select r.year, count(distinct r.id) as field0, funding_lvl2 from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id where r.year  >= 2007  and funding_lvl0='FP7' and funding_lvl2 <> '' and r.year <  2017 group by funding_lvl2, r.year order by  r.year, funding_lvl2 ";
	

$this->myqueries['pubsperf1_double'] = array();

$this->myqueries['pubsperf1_double']['q'] = "select funding_lvl1, count(sq.id), sum((case when access_mode='Open Access' then 1 else 0 end)) as open from (select distinct r.id, access_mode, funding_lvl1 from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id where  funding_lvl0='FP7' and type='publication') as sq group by sq.funding_lvl1 order by funding_lvl1";

		
$this->myqueries['pubsperf2_double'] = array();
		$this->myqueries['pubsperf2_double']['q'] = "select funding_lvl2, count(sq.id), sum((case when access_mode='Open Access' then 1 else 0 end)) as open from (select distinct r.id, access_mode, funding_lvl2 from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id where  funding_lvl0='FP7' and funding_lvl2 <> ''  and type='publication') as sq group by sq.funding_lvl2 order by funding_lvl2";

		$this->myqueries['pubsperf1_total'] = array();
		$this->myqueries['pubsperf1_total']['q'] = "select funding_lvl1, count(sq.id) from (select distinct r.id, access_mode, funding_lvl1 from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id where  funding_lvl0='FP7' and type='publication') as sq group by sq.funding_lvl1 order by funding_lvl1";

		$this->myqueries['pubsperf2_total'] = array();
		$this->myqueries['pubsperf2_total']['q'] = "select funding_lvl2, count(sq.id) from (select distinct r.id, access_mode, funding_lvl2 from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id where  funding_lvl0='FP7' and funding_lvl2 <> '' and type='publication') as sq group by sq.funding_lvl2 order by funding_lvl2";

		$this->myquerie['allproj'] = array();
		$this->myqueries['allproj']['q'] = "select acronym, startdate, enddate, funding_lvl1, funding_lvl2, numpubs, delayedpubs, case when daysforlastpub>0 then daysforlastpub else 0 end as days from project where funding_lvl0='FP7' and numpubs>0 and enddate!='' and cast(enddate as date) < CURRENT_DATE  order by days desc, funding_lvl1, funding_lvl2";

		$this->myqueries['erctime'] = array();

 $this->myqueries['erctime']['q'] = "select r.year, count(r.id)  from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and r.year >= 2007 and r.year > 0  and funding_lvl2='ERC' and r.year < 2017 group by r.year order by r.year ; ";	


/* WT IS NOW A FUNDER */
	$this->myqueries['wttime'] = array();

$this->myqueries['wttime']['q'] = " select r.year, count(r.id)  from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and r.year >= 2007  and funder='Wellcome Trust' and r.year <= 2017 group by r.year order by r.year;";
/* 
 $this->myqueries['wttime'] = array();
        $this->myqueries['wttime']['q'] = " select r.year, count(r.id)  from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and r.year >= 2007 and r.year >0  and funding_lvl0='WT' and r.year < 2015 group by r.year order by r.year;";

*/
		$this->myqueries['projpubs'] = array();
		$this->myqueries['projpubs']['q'] = "SELECT project.funding_lvl1 as xfield, count(distinct project.id) as field0 FROM project, project_results, result  WHERE project.project_results=project_results.id and project_results.result=result.id  and (project.funding_lvl0='FP7') and (project.haspubs='yes') and (result.type='publication')  GROUP BY project.funding_lvl1 ORDER BY project.funding_lvl1 LIMIT 30";

		$this->myqueries['projpubsf2'] = array();
		$this->myqueries['projpubsf2']['q'] = "SELECT project.funding_lvl2 as xfield, count(distinct project.id) as field0 FROM project, project_results, result  WHERE project.project_results=project_results.id and project_results.result=result.id  and (project.funding_lvl0='FP7') and (project.funding_lvl2 <> '' ) and (project.haspubs='yes') and (result.type='publication')  GROUP BY project.funding_lvl2 ORDER BY project.funding_lvl2 LIMIT 30";

		$this->myqueries['fp7pubsdtsrc']= array();
		$this->myqueries['fp7pubsdtsrc']['q'] = "select datasource.type, count(distinct result_projects.id) from result_projects, project, result_datasources, datasource, result where result.id=result_projects.id and result.type='publication' and result_projects.project=project.id and result_datasources.datasource=datasource.id and result_projects.id=result_datasources.id and funding_lvl0='FP7' and datasource.type!='Publication Catalogue' group by datasource.type";


// ERI - TODO egi timeline has been updated- change it everywhere 
$this->myqueries['egiTimeline']= array();
$this->myqueries['egiTimeline']['q'] = "select r.year as xfield, count(r.id) as field0 from result r,result_concepts rc, concept con, context ctx, category cat where rc.id=r.id  and  con.id=rc.concept and con.category= cat.id and lower(cat.context)= lower(ctx.name)  and cat.context ='egi' and r.year>2007 group by r.year order by r.year asc;";

$this->myqueries['egiProjects']= array();
$this->myqueries['egiProjects']['q'] = "select c.name as xfield, count(distinct rc.id) as field0 from result_concepts rc join concept c on c.id=rc.concept join category cat on cat.id=c.category where cat.id='egi::projects' group by c.name order by c.name;";

$this->myqueries['egiVO']= array();
		$this->myqueries['egiVO']['q'] = "select c.name as xfield, count(distinct rc.id) as field0 from result_concepts rc join concept c on c.id=rc.concept join category cat on cat.id=c.category where cat.id='egi::virtual' group by c.name order by c.name;";
		
$this->myqueries['egiOA']= array();
$this->myqueries['egiOA']['q'] = "select bestlicense as xfield, count(distinct r.id) as field0 from result r join result_concepts rc on r.id=rc.id join concept c on c.id=rc.concept group 
by r.bestlicense";


//FET queries

$this->myqueries['fetTimeline']= array();
 
$this->myqueries['fetTimeline']['q'] = "select r.year as xfield, count(distinct r.id) as field0 from result r,result_concepts rc, concept con, category cat where rc.id=r.id  and  con.id =rc.concept and con.category= cat.id and cat.context like 'fet%' and r.year >0 group by r.year order by r.year asc;"; 
$this->myqueries['fetFlagships']= array();

                $this->myqueries['fetFlagships']['q'] = "select  c.name as  xfield , count ( distinct r.id) as   field0  from result r,result_concepts rc , project p,concept c ,result_projects rp where rc.id =r.id and r.id=rp.id and rp.project=p.id and c.category ='fet-fp7::flagship' and rc.concept=c.id group by p.id , c.name  order by count ( distinct r.id) desc limit 3;";


$this->myqueries['fetProactive']= array();

$this->myqueries['fetProactive']['q'] = "select  c.name as  xfield , count ( distinct r.id) as   field0  from result r,result_concepts rc , project p,concept c ,result_projects rp where rc.id =r.id and r.id=rp.id and rp.project=p.id and c.category ='fet-fp7::proactive' and rc.concept=c.id  group by p.id , c.name  order by count ( distinct r.id) desc limit 5;";


$this->myqueries['fetOA']= array();
$this->myqueries['fetOA']['q'] = "select bestlicense as xfield, count(distinct r.id) as field0 from result r join result_concepts rc on r.id=rc.id join concept c on c.id=rc.concept join category cat on cat.id=c.category  where cat.id like 'fet%' group by bestlicense ; ";

$this->myqueries['fetOpen']= array();

$this->myqueries['fetOpen']['q'] = "select  c.name as  xfield , count ( distinct r.id) as   field0  from result r,result_concepts rc , project p,concept c ,result_projects rp where rc.id =r.id and r.id=rp.id and rp.project=p.id and c.category ='fet-fp7::open' and rc.concept=c.id  group by p.id , c.name  order by count ( distinct r.id) desc limit 5;";


/*TODO added WT pie query - copy to beta and prod*/
/*
$this->myqueries['wtOA']= array();
$this->myqueries['wtOA']['q'] =" SELECT bestlicense as xfield,count(distinct result_projects.id)  as field0   FROM result,result_projects, project WHERE result.result_projects= result_projects.id  AND  result_projects.project = project.id and funding_lvl0 = 'WT' and type='publication'  group by bestlicense;";
*/

$this->myqueries['ercOA']= array();
$this->myqueries['ercOA']['q'] ="SELECT bestlicense as xfield,count(distinct result_projects.id)  as field0   FROM result,result_projects, project WHERE result.result_projects= result_projects.id  AND  result_projects.project = project.id and funding_lvl2 = 'ERC' and type='publication'  group by bestlicense";


/*
WT IS NOW A FUNDER !!!
*/

$this->myqueries['wtOA']= array();
$this->myqueries['wtOA']['q'] =" SELECT bestlicense as xfield,count(distinct result_projects.id)  as field0   FROM result,result_projects, project WHERE result.result_projects= result_projects.id  AND  result_projects.project = project.id and funder = 'Wellcome Trust' and type='publication'  group by bestlicense;";


/* FCT*/
/*

 $this->myqueries['fcttime'] = array();

 $this->myqueries['fcttime']['q'] = "select r.year, count( distinct r.id) from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and  r.year >= 2007 and r.year < 2015  and funding_lvl0='FCT'  group by r.year order by r.year";


$this->myqueries['fctOA']= array();
$this->myqueries['fctOA']['q'] ="SELECT bestlicense as xfield,count(distinct result_projects.id)  as field0   FROM result,result_projects, project WHERE result.result_projects= result_projects.id  AND  result_projects.project = project.id and funding_lvl0 = 'FCT' and type='publication'  group by bestlicense";


$this->myqueries['fctfp7time']= array();

$this->myqueries['fctfp7time']['q']="select r.year, count(distinct r.id) from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id  join result_projects rp2 on rp2.id=r.id  join  project p2 on rp2.project=p2.id  where p2.funding_lvl0='FCT' and p.funding_lvl0='FP7'  and r.year >= 2007 and r.year < 2015 group by r.year  order by r.year ;";


*/


/* FCT FOR NEW FUNDERS*/


 $this->myqueries['fcttime'] = array();

 $this->myqueries['fcttime']['q'] = "select r.year, count( distinct r.id) from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and  r.year >= 2007 and r.year < 2017  and  funder = 'FCT'  group by r.year order by r.year";
$this->myqueries['fctOA']= array();
$this->myqueries['fctOA']['q'] ="SELECT bestlicense as xfield,count(distinct result_projects.id)  as field0   FROM result,result_projects, project WHERE result.result_projects= result_projects.id  AND  result_projects.project = project.id and  funder='FCT'  and type='publication'  group by bestlicense";


$this->myqueries['fctfp7time']= array();

$this->myqueries['fctfp7time']['q']="select r.year, count(distinct r.id) from result r join result_projects rp on r.id=rp.id join project p on rp.project=p.id  join result_projects rp2 on rp2.id=r.id  join  project p2 on rp2.project=p2.id  where p2.funder='FCT' and p.funding_lvl0='FP7'  and r.year >= 2007 and r.year < 2017 group by r.year  order by r.year ;";

$this->myqueries['dtscrtype']= array();

$this->myqueries['dtscrtype']['q']="select d.type,count ( distinct r.id) from datasource d, result r, result_datasources rd, project p, project_results pr where p.funding_lvl0='FP7' and r.type='publication' and p.id=pr.id and r.id=pr.result and d.id= rd.datasource and r.id=rd.id  and d.type!='Publication Catalogue' group by d.type order by count ( distinct r.id);";

// FOR DATASOURCE AD PROJCT VIEW PAGES


$this->myqueries['dtsrcPubs']['q'] =array();
$this->myqueries['dtsrcPubs']['q'] = "select result_classifications.type as xfield, count( distinct result.id) as field0 FROM result  ,result_datasources, datasource,result_classifications where datasource.id='DTSRC_NAME' and result_datasources.datasource=datasource.id and result_datasources.id=result.id and result.id=result_classifications.id GROUP BY result_classifications.type ;" ;

$this->myqueries['dtsrcYear']['q'] = array();

$this->myqueries['dtsrcYear']['q'] = "select result.year as xfield, count( distinct  result.id) as field0 FROM result,result_datasources, datasource  where datasource.id='DTSRC_NAME' and result_datasources.datasource=datasource.id and result_datasources .id=result.id and  result.year >= 1995 and result.year < 2017  GROUP BY  result.year  order by result.year asc;";

$this->myqueries['projScient']['q'] = array();
$this->myqueries['projScient']['q']="select r.year as xfield, count( distinct r.id) as field0 from result r  , result_projects rp, project p  where r.id=rp.id and p.id =rp.project and r.year>=p.start_year and r.year<=p.end_year  and r.type='publication' and  p.id=  'PROJ_TITLE' group by r.year;";


$this->myqueries['dtsrcPubsFund']['q'] = array();
$this->myqueries['dtsrcPubsFund']['q']=" select p.funder as xfield, count( distinct  result.id) as field0 FROM result,result_datasources, datasource, project p, project_results pr  where datasource.id='DTSRC_NAME' and result_datasources.datasource=datasource.id and result_datasources .id=result.id and p.id=pr.id and pr.result=result.id  GROUP BY  p.funder  order by count( distinct  result.id) desc ;";
$this->myqueries['dtsrcProjPubs']['q'] = array();

$this->myqueries['dtsrcProjPubs']['q'] =" select p.title as xfield, count( distinct  result.id) as field0 FROM result,result_datasources, datasource, project p, project_results pr  where datasource.id='DTSRC_NAME' and result_datasources.datasource=datasource.id and result_datasources .id=result.id and p.id=pr.id and pr.result=result.id GROUP BY  p.title  order by count( distinct  result.id) desc limit 10; ";
$this->myqueries['dtsrcProjData']['q'] = array();

$this->myqueries['dtsrcProjData']['q'] =" select p.title as xfield, count( distinct  result.id) as field0 FROM result,result_datasources, datasource, project p, project_results pr  where datasource.id='DTSRC_NAME' and result_datasources.datasource=datasource.id and result_datasources .id=result.id and p.id=pr.id and pr.result=result.id  and result.type='dataset' GROUP BY  p.title  order by count( distinct  result.id) desc limit 10;";


$this->myqueries['dtsrcTimeline']= array();

$this->myqueries['dtsrcTimeline']['q'] = "select  ds.timestamp_month as xfield , ds.numberofviews  as field0 from usagestats.datasourcesstats ds where ds.datasourceid='DTSRC_NAME'  order by to_date(ds.timestamp_month,'MM/YYYY')  limit 10 ; ";

$this->myqueries['dtsrcFund']= array();

$this->myqueries['dtsrcFund']['q'] = "select  p.funder as xfield, sum(ps.numberofviews)  as field0 from usagestats.projectsstats ps, datasource_results dr, result_projects rp , project p where dr.id='DTSRC_NAME' and dr.result=rp.id and rp.project=p.id  and ps.projectid=p.id group by p.funder order by  sum (ps.numberofviews) desc limit 10;";

$this->myqueries['dtsrcAreas']=array();
$this->myqueries['dtsrcAreas']['q'] = "select  p.funding_lvl2 as xfield, sum(ps.numberofviews)  as field0 from usagestats.projectsstats ps, datasource_results dr, result_projects rp , project p where dr.id='DTSRC_NAME'  and dr.result=rp.id and rp.project=p.id  and p.funding_lvl2!=' ' and ps.projectid=p.id group by p.funding_lvl2 order by  sum (ps.numberofviews) desc limit 10;";


$this->myqueries['dtsrcProjT']=array();
$this->myqueries['dtsrcProjT']['q'] = "select  r. as xfield, sum(ps.numberofviews)  as field0 from usagestats.projectsstats ps, datasource_results dr, result_projects rp , project p where dr.id='DTSRC_NAME'  and dr.result=rp.id and rp.project=p.id  and p.funding_lvl2!=' ' and ps.projectid=p.id group by p.id order by sum (ps.numberofviews) desc limit 10; ";


$this->myqueries['projTimeline']= array();
$this->myqueries['projTimeline']['q'] = "select  ps.timestamp_month as xfield , ps.numberofviews  as field0 from usagestats.projectsstats ps where ps.projectid='PROJ_TITLE'  order by to_date(ps.timestamp_month,'MM/YYYY') limit 10 ;";


$this->myqueries['projScient']['q'] = array();
$this->myqueries['projScient']['q']="select r.year as xfield, count( distinct r.id) as field0 from result r  , result_projects rp, project p  where r.id=rp.id and p.id =rp.project and r.year>=p.start_year and r.year<=p.end_year  and  p.id=  'PROJ_TITLE' group by r.year;";

$this->myqueries['projOA']['q'] = array();
$this->myqueries['projOA']['q'] = "select r.bestlicense  as xfield, count( distinct r.id) as field0 from result r  , result_projects rp, project p  where r.id=rp.id and p.id =rp.project and r.type='publication' and  p.id=  'PROJ_TITLE' group by r.bestlicense ;" ;

$this->myqueries['projPubsRepos']['q'] = array();
$this->myqueries['projPubsRepos']['q']="select d.name, count (distinct r.id) from result r, project_results pr, datasource d, datasource_results dr  , project p where r.id=dr.result and d.id=dr.id and p.id=pr.id and r.id=pr.result  and r.type='publication' and p.id='PROJ_TITLE' group by d.name order by count (distinct r.id) desc;";


$this->myqueries['orgTimeline']= array();
$this->myqueries['orgTimeline']['q'] = "select  os.timestamp_month as xfield , os.numberofviews  as field0 from usagestats.organizationsstats os where os.organizationid='ORG_TITLE'  order by to_date(os.timestamp_month,'MM/YYYY') limit 10 ;";

$this->myqueries['orgProj']= array();
$this->myqueries['orgProj']['q'] = "select  p.title  as xfield , sum(ps.numberofviews)  as field0 from usagestats.projectsstats ps , project_organizations por project p where por.organization='ORG_TITLE'  and p.id= ps.projectid  group by p.title, p.id order by sum(ps.numberofviews) limit 10 ;";


$this->myqueries['resTimeline']= array();
$this->myqueries['resTimeline']['q'] = "select  rs.timestamp_month as xfield , rs.numberofviews  as field0 from usagestats.resultsstats rs where rs.resultid='RES_TITLE'  order by to_date(rs.timestamp_month,'MM/YYYY') limit 10 ;";


//h2020 stuff


$this->myqueries['h2020Timeline']= array();
$this->myqueries['h2020Timeline']['q'] = "select r.year as xfield, count(r.id) as field0 from result r, project_results pr, project p  where pr.id=p.id  and  pr.result=r.id and p.funding_lvl0='H2020' and r.year>0 group by r.year order by r.year asc;";


$this->myqueries['h2020dtscr']= array();
$this->myqueries['h2020dtscr']['q']="select d.type,count ( distinct r.id) from datasource d, result r, result_datasources rd, project p, project_results pr where p.funding_lvl0='H2020' and r.type='publication' and p.id=pr.id and r.id=pr.result and d.id= rd.datasource and r.id=rd.id  and d.type!='Publication Catalogue' group by d.type order by count ( distinct r.id);";


$this->colors[0] = '#4572A7';
		$this->colors[1] = '#AA4643';
		$this->colors[2] = '#89A54E';

		$this->types[0] = 'column';
		$this->types[1] = 'spline';
		$this->types[2] = 'spline';

		/*set up*/
		$this->chart = array();
		$this->chart['chart'] = array();
		//$this->chart['exporting'] = array();
		$this->chart['title'] = array();
		$this->chart['xAxis'] = array();
		$this->chart['xAxis']['labels'] = array();
		$this->chart['xAxis']['title'] = array();
		$this->chart['xAxis']['categories'] = array();
		$this->chart['yAxis'] = array();
		$this->chart['tooltip'] = array();
		$this->chart['legend'] = array();
		$this->chart['series'] = array();
		/*more*/
//checkign for exporting options
		//$this->chart['exporting']['enabled'] = false;
		$this->chart['chart']['renderTo'] = 'chart';
		$this->chart['chart']['reflow'] = false;
		$this->chart['chart']['showAxes'] = true;
		$this->chart['chart']['zoomType'] = 'xy';
		//$this->chart['chart']['margin'] = 'auto';
		//$this->chart['chart']['marginRight'] = 70;
		//$this->chart['chart']['marginBottom'] = 70;
		//$this->chart['chart']['height'] = 450;
		//$this->chart['chart']['spacingBottom'] = 100;
		$this->chart['credits']['enabled'] = false;
		//$this->chart['credits']['text'] = "from OpenAIRE via HighCharts".date("d / m / Y");
		//$this->chart['credits']['href'] = "#";
		//$this->chart['credits']['position'] = array();
		//$this->chart['credits']['position']['y'] = -2;


		/*$this->chart['xAxis']['labels']['overflow'] = null;//'justify';
		$this->chart['xAxis']['labels']['style'] = array();
		$this->chart['xAxis']['labels']['style']['font-size'] = '10px';

		$this->chart['xAxis']['showEmpty'] = true;
		$this->chart['xAxis']['labels']['enabled'] = true;
		$this->chart['xAxis']['labels']['style'] = array();
		$this->chart['xAxis']['labels']['style']['font-size'] = '8px';
		//$this->chart['xAxis']['labels']['style']['margin-left'] = '10px';
		//$this->chart['xAxis']['labels']['style']['margin-right'] = '10px';*/
		$this->chart['xAxis']['startOnTick'] = true;
		$this->chart['xAxis']['endOnTick'] = true;
		$this->chart['xAxis']['showFirstLabel'] = true;
		$this->chart['xAxis']['showLastLabel'] = true;

		$this->chart['tooltip']['percentageDecimals'] = 1;
		$this->chart['tooltip']['valueDecimals'] = 1;
		$this->chart['legend']['layout'] = 'vertical';
		$this->chart['legend']['align'] = 'right';
		$this->chart['legend']['verticalAlign'] = 'top';
		$this->chart['legend']['floating'] = true;
		$this->chart['legend']['borderWidth'] = 0;
		$this->chart['legend']['x'] = -10;
		$this->chart['legend']['y'] = 50;
		$this->chart['legend']['padding'] = 3;
		$this->chart['legend']['itemMarginBottom'] = 5;

		//for scatter plots
		$this->chart['plotOptions'] = array();
		$this->chart['plotOptions']['series'] = array();
	        $this->chart['plotOptions']['series']['showCheckbox'] = true;
	        $this->chart['plotOptions']['series']['selected'] = true;
		$this->chart['plotOptions']['scatter'] = array();
		$this->chart['plotOptions']['scatter']['marker'] = array();
		$this->chart['plotOptions']['scatter']['marker']['radius'] = 5;
		$this->chart['plotOptions']['scatter']['marker']['states'] = array();
		$this->chart['plotOptions']['scatter']['marker']['states']['hover'] = array();
		$this->chart['plotOptions']['scatter']['marker']['states']['hover']['enabled'] = true;
		$this->chart['plotOptions']['scatter']['marker']['states']['hover']['lineColor'] = 'rgb(100,100,100)';
		$this->chart['plotOptions']['scatter']['states'] = array();
		$this->chart['plotOptions']['scatter']['states']['hover'] = array();
		$this->chart['plotOptions']['scatter']['states']['hover']['marker'] = array();
		$this->chart['plotOptions']['scatter']['states']['hover']['marker']['enabled'] = false;

		$this->chart['plotOptions']['area'] = array();
                $this->chart['plotOptions']['area']['stacking'] = null;

                $this->chart['plotOptions']['areaspline'] = array();
                $this->chart['plotOptions']['areaspline']['stacking'] = null;

		//for pie charts
		$this->chart['plotOptions']['pie'] = array();
		$this->chart['plotOptions']['pie']['allowPointSelect'] = true;
		//$this->chart['plotOptions']['pie']['size'] = '50%';
		$this->chart['plotOptions']['pie']['cursor'] = 'pointer';
		$this->chart['plotOptions']['pie']['showInLegend'] = true;
		$this->chart['plotOptions']['pie']['dataLabels'] = array();
		$this->chart['plotOptions']['pie']['dataLabels']['enabled'] = true;
		$this->chart['plotOptions']['pie']['dataLabels']['color'] = '#000000';
		$this->chart['plotOptions']['pie']['dataLabels']['connectorColor'] = '#000000';
		$this->chart['plotOptions']['pie']['dataLabels']['crop'] = false;
		$this->chart['plotOptions']['pie']['dataLabels']['distance'] = 10;
		//$this->chart['plotOptions']['pie']['dataLabels']['formatter'] = '';
		//for column
		$this->chart['plotOptions']['column'] = array();
		$this->chart['plotOptions']['column']['allowPointSelect'] = true;
		$this->chart['plotOptions']['column']['cursor'] = 'pointer';
		$this->chart['plotOptions']['column']['showInLegend'] = true;
		$this->chart['plotOptions']['column']['grouping'] = true;

		$this->chart['plotOptions']['area'] = array();
	}

	function makeQuery($viztype,$persistent) {
		if(!isset($_GET['data'])){
			$this->log->info("data param not set: ". print_r($_GET,true));
			return 'empty';
		}
		else {
			$data = $_GET['data'];
                      
	
                        $this->data = json_decode($data,true);//print_r($this->data);
			$this->log->debug("data param decoded: ". print_r($this->data,true));
			$cachedData = $this->data;
			//unset($cachedData['yaxisheaders']);
			//unset($cachedData['fieldsheaders']);
			unset($cachedData['title']);
			unset($cachedData['subtitle']);
			//unset($cachedData['xaxistitle']);
			unset($cachedData['theme']);
			unset($cachedData['xStyle']);
			$this->size = $this->data['size'];		
			return @$this->computeChartObject($viztype,$persistent);
		}
	}

	function computeChartObject($viztype,$persistent){
		//$this->log->info("DATA: ".print_r($this->data,true));
		if(!isset($this->data['query'])){
			$this->log->info("query not set");
//TODO here add persistent value??
			$this->queryResult = $this->database->getData($this->data,$persistent);
		}
		else{
			 

//TODO added here option for custom queries in project & gatasouces views

$this->log->info("Query set ".$this->data['query']);

if(isset($this->data['dtsrcName']))
{$this->log->info("Datasource query".$this->data['dtsrcName']);
$dtsrc= $this->myqueries[$this->data['query']]['q'];
$dtsrc=str_replace("DTSRC_NAME",$this->data['dtsrcName'],$dtsrc);
$this->log->info("Query for datasource ".$dtsrc);
$this->queryResult = array("type"=>"chart","data"=>$this->database->performQuery($dtsrc,"false"));
}
else if(isset($this->data['projTitle']))
{$this->log->info("Project query".$this->data['projTitle']);
$proj= $this->myqueries[$this->data['query']]['q'];
$proj=str_replace("PROJ_TITLE",$this->data['projTitle'],$proj);
$this->log->info("Query for proj ".$proj);
$this->queryResult = array("type"=>"chart","data"=>$this->database->performQuery($proj,"false"));

}
else if(isset($this->data['orgTitle']))
{$this->log->info("Organization query".$this->data['orgTitle']);
$org= $this->myqueries[$this->data['query']]['q'];
$org=str_replace("ORG_TITLE",$this->data['orgTitle'],$org);
$this->log->info("Query for org ".$org);
$this->queryResult = array("type"=>"chart","data"=>$this->database->performQuery($org,"false"));

}
else if(isset($this->data['resTitle']))
{$this->log->info("Result query".$this->data['resTitle']);
$res= $this->myqueries[$this->data['query']]['q'];
$res=str_replace("RES_TITLE",$this->data['resTitle'],$res);
$this->log->info("Query for res ".$res);
$this->queryResult = array("type"=>"chart","data"=>$this->database->performQuery($res,"false"));

}
else
{$this->queryResult = array("type"=>"chart","data"=>$this->database->performQuery($this->myqueries[$this->data['query']]['q'],true));
}

}

		$this->log->info("data from DB: ".print_r($this->queryResult,true));
		if($viztype=="chart"){
			if($this->queryResult['type'] == 'scatter'){
				$this->createScatterData();
			}
			else{
				$this->createChartData();
			}

			if(isset($this->data['in']) && count($this->data['in'])){
				$this->chart2 = $this->chart;
				for($w=0;$w<count($this->chart['series']);$w++){
					//$whichfield = $this->data['in'][$w]['f'];
					//$this->chart2 = $this->chart;
					$data = $this->chart2['series'][$w]['data'];
					for($i=1;$i<count($this->chart2['xAxis']['categories']);$i++){
						$data[$i] += $data[$i-1];
					}
					$this->chart2['series'][$w]['data'] = $data;
				}
				//return json_encode(array($this->chart,$this->chart2),JSON_NUMERIC_CHECK);
				return json_encode(array($this->chart,$this->chart2));
			}

			//return json_encode($this->chart,JSON_NUMERIC_CHECK);
			return json_encode($this->chart);
		}
		if($viztype=='table'){
			//return json_encode($this->queryResult,JSON_NUMERIC_CHECK);
			return json_encode($this->queryResult);
		}
	}

	function COM_getMeasMetadata(){
		if(!isset($_GET['table'])) {
			echo 'empty';
		}
		else{
			echo json_encode($this->database->getMeasMetadata($_GET['table']));
		}
	}

	function COM_defaultChart() {
		//get the default values for this table
		$temp = $this->database->getDefaultData($_GET['table']);
		if($temp == 'empty')
			echo json_encode("empty");
		else{
			$this->size = $temp['size'];
			//$this->queryResult = $temp['data'];
			$this->data = $temp['selectedData'];
			//echo json_encode($this->data,JSON_NUMERIC_CHECK);
			echo json_encode($this->data);
		}
		//$this->data['group'] = '';
		//$this->data['color'] = '';
		//print_r($this->queryResult);return;
		/*if($this->data['type'] == 'chart')
			$this->createChartData();
		else
			$this->createScatterData();
		$toreturn = array();
		$toreturn['chart'] = $this->chart;
		$toreturn['selectedData'] = $this->data;
		echo json_encode($toreturn,JSON_NUMERIC_CHECK);*/
	}

	function defaultChart() {
		//get the default values for this table
		$temp = $this->database->getDefaultData($_GET['table']);
		$this->queryResult = $temp['data'];
		$this->data = $temp['selectedData'];
		$this->data['group'] = '';
		$this->data['color'] = 'no';
		//print_r($this->queryResult);return;
		if($temp['type'] == 'chart')
			$this->createChartData($temp['type']);
		else
			$this->createScatterData('');
		//$this->chart['chart']['type'] = $temp['type'];
		//print_r($this->chart);return;
		//print_r($this->data);
		$toreturn = array();
		$toreturn['chart'] = $this->chart;
		$toreturn['selectedData'] = $this->data;
		//return json_encode($toreturn,JSON_NUMERIC_CHECK);
		return json_encode($toreturn);
	}

	function defaultChartSelections() {
		//get the default values for this table
		$temp = $this->database->getDefaultData($_GET['table']);
		$this->queryResult = $temp['data'];
		$this->data = $temp['selectedData'];

		//echo json_encode($this->data,JSON_NUMERIC_CHECK);
		echo json_encode($this->data);
	}

	function COM_getFilterData(){
		if(!isset($_GET['table']) || !isset($_GET['field'])) {
			echo 'empty';
		}
		else {
			echo $this->computeFilterObject($_GET['table'],$_GET['field'],true);
		}
	}

	function getFilterData($table, $field){
		return $this->computeFilterObject($table,$field,false);
	}

	function computeFilterObject($table, $field, $encode){
		$flds = explode("-",$field);
		if(count($flds)>1){
			$ctable = $flds[count($flds)-2];
			$field = $flds[count($flds)-1];
		}
		else {
			$ctable = $table;
			$field = $field;
		}
		//call mydb function
		if($encode)
			//return json_encode($this->database->getFilterData($ctable,$field),JSON_NUMERIC_CHECK);
			return json_encode($this->database->getFilterData($ctable,$field));
		else
			return $this->database->getFilterData($ctable,$field);
	}

	function COM_get_schema(){
		$facts = $this->database->getFacts();
		$dimensions = $this->database->getDimensions();
		$response = array();
		$menu = array();
		if($facts === null) {
			$this->log->error("there are no fact tables");
			echo "empty";
			return;
		}//print_r($facts);
		foreach($facts as $facttable){
			$newfact = array();
			$newfact['name'] = $facttable['name'];
			if(!isset($facttable['meas'][0]))
				$newfact['meas'][] = $facttable['meas'];
			else
				$newfact['meas'] = $facttable['meas'];
			$newfact['dim'] = array();
			//gia kathe dim theloume: name, type, data, kai attrib opou to attrib mporei na einai ki ayto dim
			//otan pigainoume se bathos stin ierarxia twn diastasewn kratame touw progonous kai an ftasoume se diastasi pou yparxei stous progonous tin agnooume
			foreach($facttable['dim'] as $curdim) {
				$newdim = array();
				$newdim['name'] = $curdim['name'];
				$newdim['type'] = $curdim['type'];
				//$newdim['data'] = $curdim['data'];//may not need it
				if(isset($curdim['view']))
					$newdim['view'] = $curdim['view'];
				if(isset($curdim['dimtable']))
					$newdim['dimtable'] = 'no';
				else{
					$newdim['dimtable'] = 'yes';
					$newdim['attrib'] = array();

					//need to find table $curdim['refer']['table'] from all the dimentions
					$tabledim = search($dimensions, 'name', $curdim['name']);
					//print_r($tabledim);
					for($i=0;$i<count($tabledim);$i++){
						if(isset($tabledim[$i]['type']) && $tabledim[$i]['type']=='dimension')
							break;
					}
					$newdim['attrib'] = $this->makeAttrList($tabledim[$i]['attrib'],$dimensions,array($curdim['name']),$facttable['name']);
				}
				//I am interested in all the attributes of this dimension
				array_push($newfact['dim'],$newdim);
			}
			array_push($menu,$newfact);
		}
		$response['name'] = $GLOBALS['db_name'];
		$response['schema'] = $menu;
		echo json_encode($response);
	}

	function makeAttrList($attrList, $dimensions, $ancestors,$facttable){
		$newList = array();
		if(is_array($attrList)) {
			foreach($attrList as $attr) {
				if($attr['name'] != 'id' && $attr['name']!=$facttable) {
					$newAttr = array();
					$newAttr['name'] = $attr['name'];
					if(isset($newAttr['view']))
						$newAttr['view'] = $attr['view'];
					$newAttr['type'] = $attr['type'];
					if(isset($attr['analysed']) && !in_array($attr['name'],$ancestors)){

						$finddim = search($dimensions, 'name', $attr['name']);
						$nextdim = search($finddim,'type','dimension');//print_r($nextdim);
						$newnewList = $this->makeAttrList($nextdim[0]['attrib'],$dimensions, array_merge($ancestors,array($attr['name'])),$facttable);
						$ancestors[] = $attr['name'];
						$newAttr['analysed'] = $attr['name'];
						$newAttr['attrib']= $newnewList;
					}
					else{
						$newAttr['attrib']= array();
					}
					array_push($newList,$newAttr);
				}
			}
		}
		return $newList;
	}


/*
type = color | group | ''
*/
	function createScatterData(){
		unset($this->chart['xAxis']['categories']);
		$this->chart['chart']['type'] = 'bubble';
		$xaxisindex = 0;
		$yaxisindex = 1;
		$left = 0;
		$right = 0;
		$fields = count($this->data['fields']);
		$dimindex = 1+$fields;
		if($this->data['group'] == '')
			$this->chart['xAxis']['title']['text'] = $this->data['xaxis']['name'];
		else
			$this->chart['xAxis']['title']['text'] = $this->data['xaxis']['agg'].'('.$this->data['xaxis']['name'].')';
		//arxikopoiisi
		//posoi yaxis??
		$this->chart['yAxis'] = array();
		$yaxis = $this->data['fields'][$fields-1]['yaxis'];
		$tempy = -1;
		for($f=0;$f<count($this->data['fields']);$f++){
			if($this->data['fields'][$f]['yaxis']!=$tempy+1){//new y
				$tempy++;
				$this->chart['yAxis'][$tempy] = array();
				$this->chart['yAxis'][$tempy]['labels'] = array();
				$this->chart['yAxis'][$tempy]['labels']['enabled'] = true;
				$this->chart['yAxis'][$tempy]['labels']['overflow'] = 'justify';
				//$this->chart['yAxis'][$tempy]['offset'] = 70;
				$this->chart['yAxis'][$tempy]['title'] = array();
				if($this->data['yaxisheaders'][$tempy] !=''){
					$this->chart['yAxis'][$tempy]['title']['text'] = $this->data['yaxisheaders'][$tempy];
				}
				else{
					if($this->data['fieldsheaders'][$f] !=''){
						$this->chart['yAxis'][$tempy]['title']['text'] = $this->data['fieldsheaders'][$f];
					}
					else{
						if($this->data['group'] == '')
							$this->chart['yAxis'][$tempy]['title']['text'] = $this->data['fields'][$f]['fld'];
						else
							$this->chart['yAxis'][$tempy]['title']['text'] = $this->data['fields'][$f]['agg'].'('.$this->data['fields'][$f]['fld'].')';
					}
				}
				if($right>=$left){
					$left++;
				}
				else{
					$right++;
					$this->chart['yAxis'][$tempy]['opposite'] = true;
				}
			}
			else if($this->data['yaxisheaders'][$tempy] == ""){//more in the name
				if($this->data['fieldsheaders'][$f] !=''){
					$this->chart['yAxis'][$tempy]['title']['text'] .= " / ".$this->data['fieldsheaders'][$f];
				}
				else{
					if($this->data['group'] == '')
						$this->chart['yAxis'][$tempy]['title']['text'] .= " / ".$this->data['fields'][$f]['fld'];
					else
						$this->chart['yAxis'][$tempy]['title']['text'] .= " / ".$this->data['fields'][$f]['agg'].'('.$this->data['fields'][$f]['fld'].')';
				}
			}
		}
		$this->chart['series'] = array();
		$this->chart['legend']['enabled'] = false;
		//an einai group exw ena series
		if(($this->data['group']  && $this->data['group'] != '') || $this->data['color'] == ''){//ena series kai diaforetiko onoma gia kathe data
			for($f=0;$f<$fields;$f++){
				$this->chart['series'][$f] = array();
				$this->chart['series'][$f]['data'] = array();
				if($this->data['fields'][$f]['yaxis']!=1)
					$this->chart['series'][$f]['yAxis'] = $this->data['fields'][$f]['yaxis']-1;
			}
			//for($line=0;$line<10;$line++){
			for($line=0;$line<count($this->queryResult['data']);$line++){
				for($f=0;$f<$fields;$f++){
					//insert a new data object to the only series
					$data = array();
					$data['x'] = $this->queryResult['data'][$line][$xaxisindex];
					$data['y'] = $this->queryResult['data'][$line][$yaxisindex+$f];
					if($this->data['group'] != ''){
						$data['name'] = $this->queryResult['data'][$line][$dimindex];
						if($this->data['fieldsheaders'][$f]=="")
							$this->chart['series'][$f]['name'] = $this->data['fields'][$f]['agg'].'('.$this->data['fields'][$f]['fld'].')';
						else
							$this->chart['series'][$f]['name'] = $this->data['fieldsheaders'][$f];
					}
					else{
						if($this->data['fieldsheaders'][$f]=="")
							$this->chart['series'][$f]['name'] = $this->data['fields'][$f]['fld'];
						else
							$this->chart['series'][$f]['name'] = $this->data['fieldsheaders'][$f];
					}
					$this->chart['series'][$f]['data'][] = $data;
				}
			}//print_r($this->chart['series']);
		}
		//an einai color exw ena series gia kathe timi tou dimindex
		else{
			$this->chart['legend']['enabled'] = true;
			$curseries = '';
			$seriescount = 0;
			//for($line=0;$line<10;$line++){
			for($line=0;$line<count($this->queryResult['data']);$line++){
				if($this->queryResult['data'][$line][$dimindex] != $curseries){
					$curseries = $this->queryResult['data'][$line][$dimindex];
					for($f=0;$f<$fields;$f++){
						$this->chart['series'][$seriescount+$f] = array();
						if($this->data['fieldsheaders'][$f]!='')
							$this->chart['series'][$seriescount+$f]['name'] = $this->data['fieldsheaders'][$f]." for ".$this->queryResult['data'][$line][$dimindex];
						else
							$this->chart['series'][$seriescount+$f]['name'] = $this->data['fields'][$f]['fld']." for ".$this->queryResult['data'][$line][$dimindex];
						$this->chart['series'][$seriescount+$f]['data'] = array();
					}
					$seriescount += $fields;
				}
				for($f=0;$f<$fields;$f++){
					$this->chart['series'][$seriescount-$fields+$f]['data'][] = array($this->queryResult['data'][$line][$xaxisindex],$this->queryResult['data'][$line][$yaxisindex+$f]);
				}
			}
		}

	}

	function createChartData(){
		$this->chart['xAxis']['categories'] = array();
		unset($this->chart['chart']['type']);
		$this->chart['series'] = array();
		$this->chart['yAxis'] = array();
		$xaxisindex = 0;
		$yaxisindex = 1;
		$left = 0;
		$right = 0;
		//print_r($this->queryResult['data']);
		$fields = count($this->data['fields']);
		$dimindex = 1+$fields;
if(isset($this->data['xaxistitle']))
		//if($this->data['xaxistitle']!='')
			$this->chart['xAxis']['title']['text'] = $this->data['xaxistitle'];
		else{
			$xaxis = explode("-",$this->data['xaxis']['name']);
			$this->chart['xAxis']['title']['text'] = $xaxis[count($xaxis)-1];
		}
		$this->chart['legend']['enabled'] = true;
		//posoi yaxis??
		$yaxis = $this->data['fields'][$fields-1]['yaxis'];
		$tempy = -1;
		for($f=0;$f<count($this->data['fields']);$f++){
			if($this->data['fields'][$f]['yaxis']!=$tempy+1){//new y
				$tempy++;
				$this->chart['yAxis'][$tempy] = array();
				$this->chart['yAxis'][$tempy]['labels'] = array();
				$this->chart['yAxis'][$tempy]['labels']['enabled'] = true;
				$this->chart['yAxis'][$tempy]['labels']['overflow'] = 'justify';
				$this->chart['yAxis'][$tempy]['min'] = 0;
				//$this->chart['yAxis'][$tempy]['offset'] = 70;
				$this->chart['yAxis'][$tempy]['title'] = array();
				if($this->data['yaxisheaders'][$tempy]!=''){
					$this->chart['yAxis'][$tempy]['title']['text'] = $this->data['yaxisheaders'][$tempy];
					if($this->data['fieldsheaders'][$f] == '' && (($f<count($this->data['fields'])-1 && $this->data['fields'][$f]['yaxis']!=$this->data['fields'][$f+1]['yaxis']) || $f==count($this->data['fields'])-1)){//the fields name is not set and it is the only field in the axis
						$this->data['fieldsheaders'][$f] = $this->data['yaxisheaders'][$tempy];
					}
				}
				else{
					if($this->data['fieldsheaders'][$f] !=''){
						$this->chart['yAxis'][$tempy]['title']['text']  = $this->data['fieldsheaders'][$f];
					}
					else {
						$this->chart['yAxis'][$tempy]['title']['text'] = $this->data['fields'][$f]['agg'].'('.$this->data['fields'][$f]['fld'].')';
					}
				}
				if($right>=$left){
					$left++;
				}
				else{
					$right++;
					$this->chart['yAxis'][$tempy]['opposite'] = true;
				}
			}
			else if($this->data['yaxisheaders'][$tempy]==''){//more in the name
				if($this->data['fieldsheaders'][$f] !='')
					$this->chart['yAxis'][$tempy]['title']['text']  .= " / ".$this->data['fieldsheaders'][$f];
				else
					$this->chart['yAxis'][$tempy]['title']['text'] .= " / ".$this->data['fields'][$f]['agg'].'('.$this->data['fields'][$f]['fld'].')';
			}
		}

		if($this->data['group'] != ''){//periptwsi analyze
			$this->chartDataGroup();
		}
		else{//polloi yaxis xwris group
			$this->chartDataMultiY();
		}

		if(count($this->chart['xAxis']['categories']) > 30){
			$this->chart['xAxis']['labels']['formatter'] = 'trim';
			//$this->chart['xAxis']['labels']['rotation'] = 90;
		}//print_r($this->chart);
		else{
			$trimflag = false;
			for($i=0;$i<count($this->chart['xAxis']['categories']);$i++){
				if(is_string($this->chart['xAxis']['categories'][$i]) && strlen($this->chart['xAxis']['categories'][$i])>10){
					$trimflag = true;
					break;
				}
			}
			if($trimflag){
				$this->chart['xAxis']['labels']['formatter'] = 'cond-trim';
				//$this->chart['xAxis']['labels']['rotation'] = 90;
			}
		}
	}

	function chartDataGroup(){
		//ena series gia kathe field gia kathe timi tou groupdim
		//read data
		$res = $this->queryResult['data'];
		$seriesindex = array();
		$names = array();
		$name = '';
		$seriescount = 0;
		$curseries = '';
		$xaxisindex = 0;
		$yaxisindex = 1;
		$fields = count($this->data['fields']);
		$dimindex = 1+$fields;

		($this->size >= count($this->queryResult['data'])) ? $num=count($this->queryResult['data']) : $num=$this->size; 
		//$num = $this->size;
		$flag = true;
		for($line=0;$line<$num;$line++){
			//list with x axis categories
			if(!in_array($res[$line][$xaxisindex],$this->chart['xAxis']['categories'])){
				if(count($this->chart['xAxis']['categories'])<$num){
					$this->chart['xAxis']['categories'][] = $res[$line][$xaxisindex];
					$flag = true;
				}
				else{
					$flag = false;
				}
			}
			else{
				$flag = true;
			}
			if($flag){
				//insert new data in currentseries or make new one
				if($this->queryResult['data'][$line][$dimindex] != $curseries){
					$curseries = $this->queryResult['data'][$line][$dimindex];
					for($f=0;$f<$fields;$f++){
						if($this->data['fieldsheaders'][$f]!="")
							$names[$f] = $this->data['fieldsheaders'][$f] ." for ".$curseries;
						else
							$names[$f] = $curseries;
						if(!isset($seriesindex[$names[$f]])){
							$seriesindex[$names[$f]] = array();
							$seriesindex[$names[$f]]['name'] = $names[$f];
							if($this->data['fields'][$f]['type'] !='area')
								$seriesindex[$names[$f]]['type'] = $this->data['fields'][$f]['type'];
							else
								$this->chart->type='area';
							if($this->data['fields'][$f]['yaxis']>1){
								$seriesindex[$names[$f]]['yAxis'] = $this->data['fields'][$f]['yaxis']-1;
							}
							$seriesindex[$names[$f]]['data'] = array();
							if($curseries == "UNKNOWN")
								$seriesindex[$names[$f]]['visible'] = false;

							if(isset($this->data['fields'][$f]['c']) && $this->data['fields'][$f]['c'] == true)
								$seriesindex[$names[$f]]['c'] = true;
							else
								$seriesindex[$names[$f]]['c'] = false;
						}
					}
					$seriescount += $fields;
				}
				$index = array_search($this->queryResult['data'][$line][$xaxisindex],$this->chart['xAxis']['categories']);
				for($f=0;$f<$fields;$f++){
					if($this->data['fieldsheaders'][$f]!="")
						$name = $this->data['fieldsheaders'][$f] . " for ".$curseries;
					else
						$name = $this->data['fields'][$f]['agg'].'('.$this->data['fields'][$f]['fld'].") for ".$curseries;
					$lastindex = count($seriesindex[$name]['data']);
					for($t=$lastindex;$t<$index;$t++){
						$seriesindex[$name]['data'][$t] = 0;
					}
					$seriesindex[$name]['data'][$index] = $this->queryResult['data'][$line][$yaxisindex+$f];
				}
			}
		}
		foreach($seriesindex as $s){
			$this->chart['series'][] = $s;
		}
	}

	function chartDataMultiY(){
		$xaxisindex = 0;
		$yaxisindex = 1;
		//1 series gia kathe field
		for($i=0; $i<count($this->data['fields']);$i++){
			$this->chart['series'][] = array();
			if($this->data['fieldsheaders'][$i]!='')
				$this->chart['series'][$i]['name'] = $this->data['fieldsheaders'][$i];
			else
				$this->chart['series'][$i]['name'] = $this->data['fields'][$i]['agg'].'('.$this->data['fields'][$i]['fld'].')';
			if($this->data['fields'][$i]['type'] != 'area')
				$this->chart['series'][$i]['type'] = $this->data['fields'][$i]['type'];
			else
				$this->chart->type='area';
			//$this->chart['series'][$i]['yAxis'] = $this->data['fields'][$i]['yAxis'];

			if($this->data['fields'][$i]['yaxis']>1){
				$this->chart['series'][$i]['yAxis'] = $this->data['fields'][$i]['yaxis']-1;
			}
			$this->chart['series'][$i]['data'] = array();
		}
		//read data
		$res = $this->queryResult['data'];

		($this->size >= count($this->queryResult['data'])) ? $num=count($this->queryResult['data']) : $num=$this->size; 
		//$num = $this->size;
		$flag = true;
		for($line=0;$line<$num;$line++){
			//list with x axis categories
			if(!in_array($res[$line][$xaxisindex],$this->chart['xAxis']['categories'])){
				if(count($this->chart['xAxis']['categories'])<$num){
					$this->chart['xAxis']['categories'][] = $res[$line][$xaxisindex];
					$flag = true;
				}
				else{
					$flag = false;
				}
			}
			else{
				$flag = true;
			}
			//insert new data in each series
			if($flag){
				for($s=0;$s<count($this->data['fields']);$s++) {
					if($this->data['fields'][$s]['type'] == 'pie'){
						$this->chart['series'][$s]['data'][$line]['name'] = $res[$line][$xaxisindex];
						$this->chart['series'][$s]['data'][$line]['y'] = $res[$line][$s+$yaxisindex];
					}
					else {
						$this->chart['series'][$s]['data'][] = $res[$line][$s+$yaxisindex];
					}
				}
			}
		}

		if(count($this->data['fields']) == 1)
			$this->chart['legend']['enabled'] = false;
	}

	function COM_performQuery() {
	
		if(!isset($_GET['query']) || $_GET['query'] == ''){
			$this->log->error("no query string");
			return;
		}


if(isset($_GET['persistent']))
{ $persistent=$_GET['persistent'];
            }
else
{
 $persistent="true";
}

                $query = urldecode($_GET['query']);

		$this->log->info("performing query: ".$query);

		$resp = json_encode($this->database->performQuery($query, $persistent));		//$this->log->info("response: ".$resp);
		echo $resp;
	}

	function sortSeries() {
		//exoume mia series gia kathe field
		//prepei na broume ayti ti series kai me basi ayti na kanoume sort kai oles tis alles kai ta categories tou xaxis
		$snum = 0;
		for($snum=0;$snum<$this->data['fields'];$snum++){
			if($this->data['fields'][$snum]['agg']."(".$this->data['fields'][$snum]['fld'].")" == $this->data['sort'])
				break;
		}
		//kanw sort ayto to series diatirontas ta kleidia opws einai kai meta kanw sort tis ypoloipes me basi ti seira kleidiwn aytinis
		if($this->data['order'] && $this->data['order'] == 'd'){
			arsort($this->chart['series'][$snum]['data']);
		}
		else{
			asort($this->chart['series'][$snum]['data']);
		}
		//ypoloipes
		//categories
		$tempordered = array() ;
		foreach (array_keys($this->chart['xAxis']['categories']) as $key) {
			$tempordered[$key] = $this->chart['xAxis']['categories'][$key] ;
		}
		$this->chart['xAxis']['categories'] = $tempordered;
		
		for($i=0;$i<$this->data['fields'];$i++){
			if($i!=$snum){
				unset($tempordered);
				$tempordered = array();
				$myarray = $this->chart['series'][$i]['data'];
				foreach (array_keys($myarray) as $key) {
					$tempordered[$key] = $myarray[$key] ;
				}
				$this->chart['series'][$i]['data'] = $tempordered;
			}
		}
	}


//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

	function COM_makeQuery() {
		if(!isset($_GET['data'])){
			$this->log->info("data param not set: ". print_r($_GET,true));
			echo 'empty';
		}
		else {
			$viztype = $_GET['type'];
			$data = $_GET['data'];
			$this->data = json_decode($data,true);

			$cachedData = $this->data;
			unset($cachedData['yaxisheaders']);
			unset($cachedData['fieldsheaders']);
			unset($cachedData['title']);
			unset($cachedData['subtitle']);
			unset($cachedData['xaxistitle']);
			unset($cachedData['theme']);
			unset($cachedData['xStyle']);

			$this->size = $this->data['size'];

//TODO added persistent  option
if(!isset($_GET['persistent'])){
$persistent='true';}
else {
$persistent=$_GET['persistent'];}

  
echo $this->computeChartObject($viztype,$persistent);

		}
	}

	function COM_read() {
		if(!isset($_GET['facttable']) || !isset($_GET['dimensions']) || !isset($_GET['measures'])) {
			echo "empty";
		}

		$dims = explode(';',$_GET['dimensions']);
		$meas = explode(';',$_GET['measures']);
		echo $this->readtest($dims, $meas, $_GET['facttable'],$_GET['aggregation']);
	
	}

	function COM_check_fact() {
		if(!isset($_GET['name'])) {
			echo "empty";
		}
		else{
			$res = $this->database->findTableType($_GET['name']);
			if($res == false) echo 'false';
			else echo $res;
		}
	}
	
	function COM_list_of_dim_names() {
		$res = $this->database->getAllDims();
		if($res != false)
			echo json_encode($res);
		else
			echo "false";
	}

	function COM_search_warehouse_by_dims() {
		if(!isset($_GET['dims'])) {
			echo 'empty';
		}
		else {
			$res = $this->database->searchWarehousebyDims(explode(';',$_GET['dims']));
			if($res == false) echo "false";
			else echo $res;	
		}
	}

	function COM_create_new_fact_table() {
		if(!isset($_GET['table']) || !isset($_GET['dims']) || !isset($_GET['meas'])) {
			echo 'empty';
		} 
		else {
			//set up the list of dims
			$dims = explode(',',$_GET['dims']);
			//set up the list of meas
			$new_meas = rtrim($_GET['meas'],';');
			$meas = explode(';',$new_meas);
			//call the db function
			$res = $this->database->createNewFact($_GET['table'], $dims, $meas);
			if($res == false)
				echo 'false';
			else
				echo 'true';
		}
	}

	function COM_list_of_fact_tables() {
		$res = json_encode($this->database->getFactsNames());
		if($res == null)
			echo 'null';
		else
			echo $res;
	}

	function COM_list_of_meas() {
		if(!isset($_GET['table'])) {
			echo 'empty';
		} 
		else {
			$res = $this->database->findMeas($this->database->findTable($_GET['table'],'fact'));
			if($res == false)
				echo 'false';
			else
				echo json_encode($res);
		}
	}

	function COM_list_of_dims() {
		$res = $this->database->getDims();
		if($res == false)
			echo 'false';
		else
			echo json_encode($res);
	}

	function COM_del_meas() {
		if(!isset($_GET['facttable']) || !isset($_GET['name'])) {
			echo "empty";
		}
		else {
			$res = $this->database->delMeas($_GET['facttable'], $_GET['name']);
			if($res == true)
				echo "true";
			else 
				echo "false";
		}
	}

	function COM_add_meas() {
		if(!isset($_GET['facttable']) || !isset($_GET['name']) || !isset($_GET['formal_name']) || !isset($_GET['type'])) {
			echo "empty";
		}
		else {
			$res = $this->database->addMeas($_GET['facttable'], $_GET['name'], $_GET['formal_name'], $_GET['type']);
			if($res == true)
				echo "true";
			else 
				echo "false";
		}
	}

	function COM_meas_range_data() {
		if(!isset($_GET['facttable']) || !isset($_GET['measurement'])) {
			echo "empty";
		}
		else {
			$res = $this->database->measRangeData($_GET['facttable'],$_GET['measurement']);
			echo json_encode($res);
		}
	}

	function COM_get_dims_fields_list(){
		if(!isset($_GET['table'])) {
			echo "empty";
		}
		else {
			$res = $this->database->getDimsList($_GET['table']);

			if($res == false)
				echo "false";
			else if($res == null)
				echo "null";
			else
				echo json_encode($res);
		}
	}


	function fetchData($table, $dim1, $dim2, $meas, $constraints = null) {
		return $this->database->getJsonData($table, $dim1, $dim2, $meas, $constraints);
	}

	function readData($table, $dim1, $dim2, $meas, $constraints = null) {
		return $this->database->readData($table, $dim1, $dim2, $meas, $constraints);
	}

	function timeload($start,$end) {
		$this->database->loadTime($start,$end);
	}

	function readtest($dimensions,$measures,$table,$aggr) {
		$data = $this->database->readtest($dimensions,$measures,$table,$aggr);
		//echo json_encode($data,JSON_NUMERIC_CHECK);
		echo json_encode($data);
	}
	
	
	/////////////////////////////////////////////////////////////////////////////////////

}

/*

	function createChartDataGroup($givenType) {
		//exoume anagkastika ena mono yaxis
		//ena series gia kathe diaforetiki timi tou tritou pediou
		if($givenType == 'chart'){
			$this->chart['xAxis']['categories'] = array();
			
		}
		else{
			unset($this->chart['xAxis']['categories']);
		}
		$this->chart['series'] = array();
		
		$currentSeries = "";
		$seriesCnt = -1;
		$numOfColumns = count($this->queryResult[0]);
		if(count($this->data['series']) == 0)
			$sNameIndex = -1;
		else
			$sNameIndex = count($this->data['fields']) +1;
			//$sNameIndex = $numOfColumns-1;
		$xaxisIndex = 0;
		$fStartindex = 1;
		$groupindex = 2;
		$this->chart['xAxis']['title']['text'] = $this->data['xaxis']['name'];
		//arxikopoiisi
		$this->chart['yAxis'] = array();
		$this->chart['series'] = array();
		$this->chart['legend']['enabled'] = false;
		//edw tha ektelestei mono mia fora <-------NA TO ALLAKSW
		for($i=0; $i<count($this->data['fields']);$i++){
			//gia kathe field, exoume enan yaxis kai ena series
			$this->chart['yAxis'][] = array();
			$this->chart['yAxis'][$i]['labels'] = array();
			$this->chart['yAxis'][$i]['offset'] = 70;
			//$this->chart['yAxis'][$i]['labels']['formatter'] = 'simple';
			$this->chart['yAxis'][$i]['title'] = array();
			$this->chart['yAxis'][$i]['title']['text'] = $this->data['fields'][$i]['fld'];
			if($i>0){
				$this->chart['yAxis'][$i]['opposite'] = true;
				$this->chart['yAxis'][$i]['gridLineWidth'] = 0;
			}

			//$this->chart['series'][] = array();
			//$this->chart['series'][$i]['name'] = $this->data['fields'][$i]['fld'];
			
			//if($i>0) {
			//	$this->chart['series'][$i]['yAxis'] = $i;
			//}
			//$this->chart['series'][$i]['data'] = array();
			//$seriesData[$i] = array();
		}
		
		$res = $this->queryResult['data'];
		//gia kathe diaforetiki timi tou pediou 2, kainourgio category an exoume chart
		//gia kathe diaforetiki timi tou pediou 3, kainourgio series me onoma tin timi tou pediou 3
		//oso eisai sto idio series, apla prosthese ta data
		//an allakseis ftiakse kainourgio
		for($line=0;$line<count($this->queryResult['data']);$line++){
			//list with x axis categories
			if($givenType == 'chart'){
				if(!in_array($res[$line][$xaxisIndex],$this->chart['xAxis']['categories'])){
					$this->chart['xAxis']['categories'][] = $res[$line][$xaxisIndex];
				}
			}
			//new series? 
			if($res[$line][$groupindex] != $currentSeries){
				$currentSeries = $res[$line][$groupindex];
				$this->chart['series'][] = array();
				$seriesCnt++;
				$this->chart['series'][$seriesCnt]['name'] = $currentSeries;
				$this->chart['series'][$seriesCnt]['data'] = array();
			}
			$this->chart['series'][$seriesCnt]['data'][] = $res[$line][$fStartindex];
		}

		if(count($this->chart['xAxis']['categories']) > 15){
			$this->chart['xAxis']['labels']['formatter'] = 'trim';
			$this->chart['xAxis']['labels']['rotation'] = 45;
		}

	}
*/
?>