log = Logger::getLogger(__CLASS__); Logger::configure('./js/log4php/log4php.xml'); if(class_exists("Predis\Client")){ try { //todo changed timeout from 0 to -1 $this->cache = new Predis\Client(array("scheme" => $redis_scheme,"host" => $redis_host,"port" => $redis_port,"read_write_timeout" => -1)); $this->log->info("redis host: ".$redis_host." and redis port: ".$redis_port); } catch(Exception $e) { $this->log->error("Error connecting to Redis server: ".$e->getMessage()); $this->cache = null; } } else{ $this->log->info("cache does not exist"); //predis exit; } //NOW GET DB try { $str = 'pgsql:host='.$host.";port=5432;dbname=".$db_name.";user=".$username.";password=".$password; $this->db = new PDO($str); //TODO set search path here } catch(Exception $e){ $this->log->error('Could not connect to database: ' . $e->getMessage()); exit; } } private function doQuery($query){ $stmt = $this->db->query($query); if(!$stmt){ $arr = $this->db->errorInfo(); $this->log->error("Error executing query: ".$query." ".$arr[2]); return "-"; } $t = $stmt->fetch(); return number_format($t[0]); } //TODO here store Queries in cache private function storeQuery($key, $query) { $res=$this->doQuery($query); //$this->cache = new Predis\Client(array("scheme" => $redis_scheme, "host" => $redis_host, "port" => $redis_port)); $this->cache->connect(); //predis $this->cache->hset('SHADOW_STATS_NUMBERS',$key,$res); $this->log->info("Stored query : ".$key." ".$this->cache->hget('SHADOW_STATS_NUMBERS',$key)); $this->cache->quit(); } function computeStats() { global $redis_host; /*OVERALL*/ /*1*/ $qpubs = "SELECT count(*) FROM result where type='publication'"; /*2*/ $qoapubs = "SELECT count(*) FROM result WHERE bestlicense='Open Access' and type='publication'"; /*3*/ $qnoapubs = "SELECT count(*) FROM result WHERE bestlicense='Closed Access' and type='publication'"; /*4*/ $qfpubs = "SELECT count(distinct result_projects.id) FROM result, result_projects where result.result_projects = result_projects.id and type='publication'"; /*5*/ $qproj = "SELECT count(*) FROM project"; /*DATASOURCES*/ /*6*/ $qdatasrc = "SELECT count(*) FROM datasource"; /*7*/ $qdtsrcpubs = "select count(*) from datasource where compatibility != 'not available' and compatibility != 'under validation'"; /*8*/ $qdtsrcpubreps = "SELECT count(*) FROM datasource WHERE type='Publication Repository'"; /*9*/ $qdtsrcoaj = "SELECT count(*) FROM datasource WHERE type='Journal Platform'"; /*10*/ $qdtsrcpubaggr = "SELECT count(*) FROM datasource WHERE type like 'Aggreg%'"; /*10*/ $qdtsrcdatarepo = "SELECT count(*) FROM datasource WHERE type = 'Data Repository'"; /*FUNDER*/ /*11*/ $qfunders = "SELECT count(distinct funding_lvl0) FROM project WHERE funding_lvl0 !=''"; /*FP7*/ /*12: total number of fp7 pubs */ $qfp7pubstotal = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id and type='publication' and result_projects.project = project.id and funding_lvl0 = 'FP7'"; /*13: number of fp7 projects with publications*/ $qfp7projpubs = "SELECT count(distinct project.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id and type='publication' and result_projects.project = project.id and funding_lvl0='FP7'"; /*14: total number of fp7 projects*/ $qfp7projtotal = "SELECT count(id) FROM project WHERE funding_lvl0 = 'FP7'"; /*15: fp7 open access pubs*/ $qfp7oapubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result_projects = result_projects.id AND result_projects.project = project.id and type='publication' and funding_lvl0 = 'FP7' and bestlicense='Open Access'"; /*16: fp7 restricted pubs*/ $qfp7respubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'FP7' and bestlicense='Restricted' and type='publication'"; /*17: fp7 pubs in embargo*/ $qfp7embpubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'FP7' and bestlicense='Embargo' and type='publication'"; /*18: total number of fp7 pubs with sc39*/ $qsc39fp7pubstotal = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result_projects.project = project.id and funding_lvl0 = 'FP7' and sc39='yes' and result.result_projects = result_projects.id and type='publication'"; /*19: fp7 projs with sc39 and pubs*/ $qsc39fp7projpubs = "SELECT count(distinct project.id) FROM result, result_projects, project WHERE result_projects.project=project.id and funding_lvl0 = 'FP7' and sc39='yes' and result.result_projects = result_projects.id and type='publication'"; /*20: total number of fp7 proj with sc39*/ $qsc39fp7projtotal = "SELECT count(number) from project where funding_lvl0='FP7' and sc39='yes'"; /*21: open access fp7 pubs with sc39*/ $qsc39fp7oapubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'FP7' and bestlicense='Open Access' and sc39='yes' and type='publication'"; /*ERC*/ /*22: total number of erc pubs*/ $qercpubstotal = "SELECT count( distinct result.id) FROM result, project, result_projects where result_projects.project = project.id and project.funding_lvl2='ERC' and result.result_projects = result_projects.id and result.type='publication'"; /*23: erc projects with pubs*/ $qercprojpubs = "SELECT count(distinct project.id) FROM result, project, result_projects where result_projects.project = project.id and project.funding_lvl2='ERC' and result.result_projects = result_projects.id and type='publication'"; /*24: total erc projects*/ $qercprojtotal = "SELECT count(id) FROM project WHERE funding_lvl2 = 'ERC'"; /*25: erc open access pubs*/ $qercoapubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects= result_projects.id AND result_projects.project = project.id and funding_lvl2 = 'ERC' and bestlicense='Open Access' and type='publication'"; /*26: erc restricted pubs*/ $qercrespubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl2 = 'ERC' and bestlicense='Restricted' and type='publication'"; /*27: erc embargo pubs*/ $qercembpubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funding_lvl2 = 'ERC' and bestlicense='Embargo' and type='publication'"; /*Wellcome Trust*/ /*28: total number of wt pubs*/ $qwtpubstotal = "SELECT count(distinct result.id) 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'"; /*29: wt projects with pubs*/ $qwtprojpubs = "SELECT count(distinct project.id) FROM result, project, result_projects where result_projects.project = project.id and project.funder='Wellcome Trust' and result.result_projects = result_projects.id and type='publication'"; /*30: total wt projects*/ $qwtprojtotal = "SELECT count(id) FROM project WHERE funder ='Wellcome Trust'"; /*31: wt open access pubs*/ $qwtoapubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects= result_projects.id AND result_projects.project = project.id and funder ='Wellcome Trust' and bestlicense='Open Access' and type='publication'"; /*32: wt restricted pubs*/ $qwtrespubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funder='Wellcome Trust' and bestlicense='Restricted' and type='publication'"; /*33: wt embargo pubs*/ $qwtembpubs = "SELECT count(distinct result.id) FROM result, result_projects, project WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funder ='Wellcome Trust' and bestlicense='Embargo' and type='publication'"; /*FET */ $fetpubs ="select 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.context like 'fet%'"; $fetoapubs ="select count(distinct rc.id) as field0 from result_concepts rc join result r on r.id=rc.id join concept c on c.id=rc.concept join category cat on cat.id=c.category where cat.context like 'fet%' and r. bestlicense='Open Access'"; /* $fet_noa_pubs ="select count(distinct rc.id) as field0 from result_concepts rc join result r on r.id=rc.id join concept c on c.id=rc.concept join category cat on cat.id=c.category where cat.context ='fet' and r. bestlicense='Closed Access'";*/ $fetembpubs ="select count(distinct rc.id) as field0 from result_concepts rc join result r on r.id=rc.id join concept c on c.id=rc.concept join category cat on cat.id=c.category where cat.context like 'fet%' and r. bestlicense='Embargo'"; $fetrespubs ="select count(distinct rc.id) as field0 from result_concepts rc join result r on r.id=rc.id join concept c on c.id=rc.concept join category cat on cat.id=c.category where cat.context like 'fet%' and r. bestlicense='Restricted'"; /*Natalia's extra stuff */ /*35: organisations with publication results */ $org_withpubs = "select count(*) from (select dor.organization as organization from datasource_organizations dor join result_datasources rd on rd.datasource=dor.id join result r on r.id=rd.id where r.type='publication' union select por.organization as organization from project_organizations por join result_projects rp on rp.project=por.id join result r on r.id=rp.id where r.type='publication') as foo"; /*36: number of datasets */ $data_total = "select count(distinct id) from result where type='dataset'"; # $datasrc_withpubs = "SELECT count(distinct rd.datasource) from result_datasources rd join result r on r.id=rd.id where r.type='publication'"; $datasrc_withpubs = "SELECT count(distinct rd.datasource) from result_datasources rd "; /*DATASETS */ $datasetpubs= "select count(distinct r.id) from result r join result_results rr on rr.id=r.id join result rp on rp.id=rr.result where r.type='dataset' and rp.type='publication'" ; //# of datasets linked to projects $datasetproj="select count(distinct r.id) from result r join result_projects rp on rp.id=r.id join project p on p.id=rp.project where r.type='dataset' "; //# of publications linked to datasets //$pubsdatasets ="select count(distinct r.id) from result r join result_results rr on rr.id=r.id join result rp on rp.id=rr.result where r.type='publication' and rp.type='dataset'"; $pubsdatasets ="select count(distinct rp.id) from result rp where rp.type='dataset'"; /*EGI PAGE NUMS*/ $egivo="select count(distinct c.name) 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'"; $egiproj="select count(distinct c.name) 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' "; /*total of egi projects */ $egiprojtotal="select count (distinct c.name) as field0 from concept c join category cat on cat.id=c.category where cat.id='egi::projects'"; $egipubs= " select count( distinct rc.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 ='egi'"; $egioa=" select count( distinct rc.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 ='egi' and r.bestlicense='Open Access'"; //$eginoa="select 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 where bestlicense='Closed Access'"; $egiemb=" select count( distinct rc.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 ='egi' and bestlicense='Embargo'"; $egires=" select count( distinct rc.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 ='egi' and bestlicense='Restricted'"; /*FCT*/ /*22: total number of FCT pubs*/ $qfctpubstotal = "SELECT count( distinct result.id) FROM result, project, result_projects where result_projects.project = project.id and project.funder='FCT' and result.result_projects = result_projects.id and result.type='publication'"; /*23: FCT projects with pubs*/ $qfctprojpubs = "SELECT count(distinct project.id) FROM result, project, result_projects where result_projects.project = project.id and project.funder='FCT' and result.result_projects = result_projects.id and type='publication'"; /*24: total FCT projects*/ $qfctprojtotal = "SELECT count(id) FROM project WHERE funder ='FCT'"; /*25: FCT open access pubs*/ $qfctoapubs = "SELECT count(distinct result_projects.id) FROM result, result_projects, project WHERE result.result_projects= result_projects.id AND result_projects.project = project.id and funder ='FCT' and bestlicense='Open Access' and type='publication'"; /*26: FCT restricted pubs*/ $qfctrespubs = "SELECT count(distinct result_projects.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funder='FCT' and bestlicense='Restricted' and type='publication'"; /*27: FCT embargo pubs*/ $qfctembpubs = "SELECT count(distinct result_projects.id) FROM result, result_projects, project WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funder ='FCT' and bestlicense='Embargo' and type='publication'"; $qres=' select count (distinct id) from result'; $qdtsrcdatarepo = "SELECT count(*) FROM datasource WHERE type = 'Data Repository'"; /*total of egi projects */ $egiprojtotal="select count (distinct c.name) as field0 from concept c join category cat on cat.id=c.category where cat.id='egi::projects'"; /*H2020*/ /*12: total number of H2020 pubs */ $qh2020pubstotal = "SELECT count(distinct result_projects.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id and type='publication' and result_projects.project = project.id and funding_lvl0 = 'H2020'"; /*13: number of fp7 projects with publications*/ $qh2020projpubs = "SELECT count(distinct project.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id and type='publication' and result_projects.project = project.id and funding_lvl0='H2020'"; /*14: total number of fp7 projects*/ $qh2020projtotal = "SELECT count(id) FROM project WHERE funding_lvl0 = 'H2020'"; /*15: fp7 open access pubs*/ $qh2020oapubs = "SELECT count(distinct result_projects.id) FROM result, result_projects, project WHERE result_projects = result_projects.id AND result_projects.project = project.id and type='publication' and funding_lvl0 = 'H2020' and bestlicense='Open Access'"; /*16: fp7 restricted pubs*/ $qh2020respubs = "SELECT count(distinct result_projects.id) FROM result, result_projects, project WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'H2020' and bestlicense='Restricted' and type='publication'"; /*17: fp7 pubs in embargo*/ $qh2020embpubs = "SELECT count(distinct result_projects.id) FROM result, result_projects, project WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'H2020' and bestlicense='Embargo' and type='publication'"; /*1*/ $this->storeQuery('pubs', $qpubs); /*2*/ $this->storeQuery('oapubs', $qoapubs); /*3*/ $this->storeQuery('noapubs', $qnoapubs); /*4*/ $this->storeQuery('fpubs', $qfpubs); /*5*/ $this->storeQuery('proj', $qproj); /*6*/ $this->storeQuery('datasrc', $qdatasrc); /*7*/ $this->storeQuery('dtsrcpubs', $qdtsrcpubs); /*8*/ $this->storeQuery('dtsrcpubreps', $qdtsrcpubreps); /*9*/ $this->storeQuery('dtsrcoaj', $qdtsrcoaj); /*10*/ $this->storeQuery('dtsrcpubaggr', $qdtsrcpubaggr); /*11*/ $this->storeQuery('funders', $qfunders); /*FP7*/ /*12*/ $this->storeQuery('fp7pubstotal', $qfp7pubstotal); /*13*/ $this->storeQuery('fp7projpubs', $qfp7projpubs); /*14*/ $this->storeQuery('fp7projtotal', $qfp7projtotal); /*15*/ $this->storeQuery('fp7oapubs', $qfp7oapubs); /*16*/ $this->storeQuery('fp7respubs', $qfp7respubs); /*17*/ $this->storeQuery('fp7embpubs', $qfp7embpubs); /*18*/ $this->storeQuery('sc39fp7pubstotal', $qsc39fp7pubstotal); /*19*/ $this->storeQuery('sc39fp7projpubs', $qsc39fp7projpubs); /*20*/ $this->storeQuery('sc39fp7projtotal', $qsc39fp7projtotal); /*21*/ $this->storeQuery('sc39fp7oapubs', $qsc39fp7oapubs); /*ERC*/ /*22*/ $this->storeQuery('ercpubstotal', $qercpubstotal); /*23*/ $this->storeQuery('ercprojpubs', $qercprojpubs); /*24*/ $this->storeQuery('ercprojtotal', $qercprojtotal); /*25*/ $this->storeQuery('ercoapubs', $qercoapubs); /*26*/ $this->storeQuery('ercrespubs', $qercrespubs); /*27*/ $this->storeQuery('ercembpubs', $qercembpubs); /*Wellcome Trust*/ /*28*/ $this->storeQuery('wtpubstotal', $qwtpubstotal); /*29*/ $this->storeQuery('wtprojpubs', $qwtprojpubs); /*30*/ $this->storeQuery('wtprojtotal', $qwtprojtotal); /*31*/ $this->storeQuery('wtoapubs', $qwtoapubs); /*32*/ $this->storeQuery('wtrespubs', $qwtrespubs); /*33*/ $this->storeQuery('wtembpubs', $qwtembpubs); /*34*/ $this->storeQuery('datasrc_withpubs', $datasrc_withpubs) ; //todo commented those for duffy /*35*/$this->storeQuery('org_withpubs', $org_withpubs); /*36*/ $this->storeQuery('data_total', $data_total); //FET NUMS //FET NUMS $this->storeQuery('fetpubs', $fetpubs); $this->storeQuery('fetoapubs', $fetoapubs); $this->storeQuery('fetrespubs', $fetrespubs); $this->storeQuery('fetembpubs', $fetembpubs); //DATASET NUMS $this->storeQuery('datasetpubs', $datasetpubs); $this->storeQuery('datasetproj', $datasetproj); $this->storeQuery('pubsdatasets', $pubsdatasets); /*EGI nums*/ $this->storeQuery('egipubs', $egipubs); $this->storeQuery('egivo', $egivo); $this->storeQuery('egiproj', $egiproj); $this->storeQuery('egioa', $egioa); $this->storeQuery('egiemb', $egiemb); $this->storeQuery('egires', $egires); //FCT NUMS /*22*/ $this->storeQuery('fctpubstotal', $qfctpubstotal); /*23*/ $this->storeQuery('fctprojpubs', $qfctprojpubs); /*24*/ $this->storeQuery('fctprojtotal', $qfctprojtotal); /*25*/ $this->storeQuery('fctoapubs', $qfctoapubs); /*26*/ $this->storeQuery('fctrespubs', $qfctrespubs); /*27*/ $this->storeQuery('fctembpubs', $qfctembpubs); $this->storeQuery('dtsrcdatarepo', $qdtsrcdatarepo); $this->storeQuery('res', $qres); $this->storeQuery('egiprojtotal',$egiprojtotal); /* H2020*/ /*12*/ $this->storeQuery('h2020pubstotal', $qh2020pubstotal); /*13*/ $this->storeQuery('h2020projpubs', $qh2020projpubs); /*14*/ $this->storeQuery('h2020projtotal', $qh2020projtotal); /*15*/ $this->storeQuery('h2020oapubs', $qh2020oapubs); /*16*/ $this->storeQuery('h2020respubs', $qh2020respubs); /*17*/ $this->storeQuery('h2020embpubs', $qh2020embpubs); } } ?>