Revision 38202
Added by Eri Katsari over 9 years ago
| controller.php | ||
|---|---|---|
| 24 | 24 |
private $chart2; |
| 25 | 25 |
private $colors = array(); |
| 26 | 26 |
private $size = 30; |
| 27 |
|
|
| 28 | 27 |
|
| 28 |
|
|
| 29 | 29 |
private $myqueries = null; |
| 30 | 30 |
|
| 31 | 31 |
function __construct($myflag = true){
|
| ... | ... | |
| 80 | 80 |
|
| 81 | 81 |
$this->myqueries['erctime'] = array(); |
| 82 | 82 |
|
| 83 |
$this->myqueries['erctime']['q'] = "select extract(year from date(r.date)), count(r.id) from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and extract(year from date(r.date)) >= 2007 and date <> '' and funding_lvl2='ERC' and date(r.date) < now() group by extract(year from date(r.date)) order by extract(year from date(r.date))"; |
|
| 84 |
$this->myqueries['wttime'] = array(); |
|
| 85 |
$this->myqueries['wttime']['q'] = "select extract(year from date(r.date)), count(r.id) from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and extract(year from date(r.date)) >= 2007 and date <> '' and funding_lvl0='WT' and date(r.date) < now() group by extract(year from date(r.date)) order by extract(year from date(r.date))"; |
|
| 83 |
$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 < 2015 group by r.year order by r.year ; "; |
|
| 86 | 84 |
|
| 85 |
|
|
| 86 |
|
|
| 87 |
/* WT IS NOW A FUNDER */ |
|
| 88 |
$this->myqueries['wttime'] = array(); |
|
| 89 |
$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 funder='Wellcome Trust' and r.year < 2015 group by r.year order by r.year;"; |
|
| 90 |
/* |
|
| 91 |
$this->myqueries['wttime'] = array(); |
|
| 92 |
$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;"; |
|
| 93 |
|
|
| 94 |
*/ |
|
| 87 | 95 |
$this->myqueries['projpubs'] = array(); |
| 88 | 96 |
$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"; |
| 89 | 97 |
|
| ... | ... | |
| 138 | 146 |
|
| 139 | 147 |
|
| 140 | 148 |
/*TODO added WT pie query - copy to beta and prod*/ |
| 141 |
|
|
| 149 |
/* |
|
| 142 | 150 |
$this->myqueries['wtOA']= array(); |
| 143 | 151 |
$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;"; |
| 152 |
*/ |
|
| 144 | 153 |
|
| 145 |
|
|
| 146 | 154 |
$this->myqueries['ercOA']= array(); |
| 147 | 155 |
$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"; |
| 148 | 156 |
|
| 149 | 157 |
|
| 150 | 158 |
|
| 159 |
/* |
|
| 160 |
WT IS NOW A FUNDER !!! |
|
| 161 |
*/ |
|
| 162 |
|
|
| 163 |
$this->myqueries['wtOA']= array(); |
|
| 164 |
$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;"; |
|
| 165 |
|
|
| 166 |
|
|
| 151 | 167 |
/* FCT*/ |
| 168 |
/* |
|
| 152 | 169 |
|
| 153 |
|
|
| 154 | 170 |
$this->myqueries['fcttime'] = array(); |
| 155 | 171 |
|
| 156 |
//TODO : FCT IS NO LONGER ON FUNDING LVL0 BUT AS A FUNDER |
|
| 157 |
$this->myqueries['fcttime']['q'] = "select extract(year from date(r.date)), count( distinct r.id) from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and extract(year from date(r.date)) >= 2007 and date <> '' and funder='Fundação para a Ciência e a Tecnologia, I.P.' and date(r.date) < now() group by extract(year from date(r.date)) order by extract(year from date(r.date))"; |
|
| 172 |
$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"; |
|
| 158 | 173 |
|
| 159 | 174 |
|
| 160 | 175 |
$this->myqueries['fctOA']= array(); |
| 161 |
$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 = 'Fundação para a Ciência e a Tecnologia, I.P.' and type='publication' group by bestlicense";
|
|
| 176 |
$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";
|
|
| 162 | 177 |
|
| 163 | 178 |
|
| 164 | 179 |
$this->myqueries['fctfp7time']= array(); |
| 165 |
$this->myqueries['fctfp7time']['q']="select extract(year from date(r.date))::integer , 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='Fundação para a Ciência e a Tecnologia, I.P.' and p.funding_lvl0='FP7' group by extract(year from date(r.date)) order by extract(year from date(r.date))"; |
|
| 166 | 180 |
|
| 181 |
$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 ;"; |
|
| 182 |
|
|
| 183 |
|
|
| 184 |
*/ |
|
| 185 |
|
|
| 186 |
|
|
| 187 |
/* FCT FOR NEW FUNDERS*/ |
|
| 188 |
|
|
| 189 |
|
|
| 190 |
$this->myqueries['fcttime'] = array(); |
|
| 191 |
|
|
| 192 |
$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 funder = 'FCT' group by r.year order by r.year"; |
|
| 193 |
|
|
| 194 |
$this->myqueries['fctOA']= array(); |
|
| 195 |
$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"; |
|
| 196 |
|
|
| 197 |
|
|
| 198 |
$this->myqueries['fctfp7time']= array(); |
|
| 199 |
|
|
| 200 |
$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 < 2015 group by r.year order by r.year ;"; |
|
| 201 |
|
|
| 167 | 202 |
|
| 168 | 203 |
|
| 169 | 204 |
$this->colors[0] = '#4572A7'; |
| ... | ... | |
| 278 | 313 |
$this->chart['plotOptions']['area'] = array(); |
| 279 | 314 |
} |
| 280 | 315 |
|
| 281 |
function makeQuery($viztype) {
|
|
| 316 |
function makeQuery($viztype,$persistent) {
|
|
| 282 | 317 |
if(!isset($_GET['data'])){
|
| 283 | 318 |
$this->log->info("data param not set: ". print_r($_GET,true));
|
| 284 | 319 |
return 'empty'; |
| 285 | 320 |
} |
| 286 | 321 |
else {
|
| 287 | 322 |
$data = $_GET['data']; |
| 288 |
//$this->log->info("data param: ". $data. "end");
|
|
| 289 |
$this->data = json_decode($data,true);//print_r($this->data); |
|
| 323 |
|
|
| 324 |
|
|
| 325 |
$this->data = json_decode($data,true);//print_r($this->data); |
|
| 290 | 326 |
$this->log->debug("data param decoded: ". print_r($this->data,true));
|
| 291 | 327 |
$cachedData = $this->data; |
| 292 | 328 |
//unset($cachedData['yaxisheaders']); |
| ... | ... | |
| 296 | 332 |
//unset($cachedData['xaxistitle']); |
| 297 | 333 |
unset($cachedData['theme']); |
| 298 | 334 |
unset($cachedData['xStyle']); |
| 299 |
$this->size = $this->data['size']; |
|
| 300 |
|
|
| 301 |
|
|
| 302 |
return $this->computeChartObject($viztype); |
|
| 335 |
$this->size = $this->data['size']; |
|
| 336 |
return @$this->computeChartObject($viztype,$persistent); |
|
| 303 | 337 |
} |
| 304 | 338 |
} |
| 305 | 339 |
|
| 306 |
function computeChartObject($viztype){
|
|
| 340 |
function computeChartObject($viztype,$persistent){
|
|
| 307 | 341 |
//$this->log->info("DATA: ".print_r($this->data,true));
|
| 308 | 342 |
if(!isset($this->data['query'])){
|
| 309 | 343 |
$this->log->info("query not set");
|
| 310 |
$this->queryResult = $this->database->getData($this->data); |
|
| 344 |
//TODO here add persistent value?? |
|
| 345 |
$this->queryResult = $this->database->getData($this->data,$persistent); |
|
| 311 | 346 |
} |
| 312 | 347 |
else{
|
| 313 | 348 |
$this->log->info("query set");
|
| 314 |
$this->queryResult = array("type"=>"chart","data"=>$this->database->performQuery($this->myqueries[$this->data['query']]['q']));
|
|
| 349 |
$this->queryResult = array("type"=>"chart","data"=>$this->database->performQuery($this->myqueries[$this->data['query']]['q'],true));
|
|
| 350 |
|
|
| 315 | 351 |
} |
| 316 | 352 |
|
| 353 |
|
|
| 317 | 354 |
$this->log->info("data from DB: ".print_r($this->queryResult,true));
|
| 318 | 355 |
if($viztype=="chart"){
|
| 319 | 356 |
if($this->queryResult['type'] == 'scatter'){
|
| ... | ... | |
| 663 | 700 |
//print_r($this->queryResult['data']); |
| 664 | 701 |
$fields = count($this->data['fields']); |
| 665 | 702 |
$dimindex = 1+$fields; |
| 666 |
|
|
| 667 |
if(isset($this->data['xaxistitle']))
|
|
| 703 |
if(isset($this->data['xaxistitle'])) |
|
| 704 |
//if($this->data['xaxistitle']!='')
|
|
| 668 | 705 |
$this->chart['xAxis']['title']['text'] = $this->data['xaxistitle']; |
| 669 | 706 |
else{
|
| 670 | 707 |
$xaxis = explode("-",$this->data['xaxis']['name']);
|
| ... | ... | |
| 887 | 924 |
return; |
| 888 | 925 |
} |
| 889 | 926 |
|
| 890 |
$query = urldecode($_GET['query']); |
|
| 891 | 927 |
|
| 928 |
if(isset($_GET['persistent'])) |
|
| 929 |
{ $persistent=$_GET['persistent'];
|
|
| 930 |
} |
|
| 931 |
else |
|
| 932 |
{
|
|
| 933 |
$persistent="true"; |
|
| 934 |
} |
|
| 935 |
|
|
| 936 |
$query = urldecode($_GET['query']); |
|
| 937 |
|
|
| 892 | 938 |
$this->log->info("performing query: ".$query);
|
| 893 |
$resp = json_encode($this->database->performQuery($query)); |
|
| 894 |
//$this->log->info("response: ".$resp);
|
|
| 939 |
|
|
| 940 |
$resp = json_encode($this->database->performQuery($query, $persistent)); //$this->log->info("response: ".$resp);
|
|
| 895 | 941 |
echo $resp; |
| 896 | 942 |
} |
| 897 | 943 |
|
| ... | ... | |
| 960 | 1006 |
|
| 961 | 1007 |
$this->size = $this->data['size']; |
| 962 | 1008 |
|
| 1009 |
//TODO added persistent option |
|
| 1010 |
$this->persistent= $_GET['persistent']; |
|
| 1011 |
|
|
| 963 | 1012 |
echo $this->computeChartObject($viztype); |
| 964 | 1013 |
} |
| 965 | 1014 |
} |
Also available in: Unified diff
latest changes in beta