log = Logger::getLogger(__CLASS__); if(class_exists("Predis\Client")){ try { Predis\Autoloader::register(); // $this->cache = new Predis\Client(array( //predis // "scheme" => $redis_scheme, // "host" => $redis_host, // "port" => $redis_port, // "read_write_timeout" => 600)); // $this->cache->connect(); //predis // $this->cache->quit(); $this->log->info("redis host: ".$redis_host); $this->log->info("redis port: ".$redis_port); } catch(Exception $e) { $this->log->error("Error connecting to Redis server: ".$e->getMessage()); $this->cache = null; //predis } } else{ $this->log->info("cache does not exist"); //predis exit; } try { //for testing //$str = 'pgsql:host='.$host.';port=5432;dbname=stats;user=sqoop;password=sqoop'; //for deployment $str = 'pgsql:host='.$host.';port=5432;dbname=stats;user=dnet;password=dnetPwd'; $this->db = new PDO($str); } 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]); } private function storeQuery($key, $query) { global $redis_host; global $redis_port; global $redis_scheme; $this->cache = new Predis\Client(array("scheme" => $redis_scheme, "host" => $redis_host, "port" => $redis_port)); $this->cache->connect(); //predis $this->cache->hset('STATS_NUMBERS', $key, $this->doQuery($query)); $this->cache->quit(); } function computeStats() { /*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"; /*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='Repository'"; /*9*/ $qdtsrcoaj = "SELECT count(*) FROM datasource WHERE type='Journal'"; /*10*/ $qdtsrcpubaggr = "SELECT count(*) FROM datasource WHERE type='Aggregated Repository'"; /*11*/ $qfunders = "SELECT count(distinct funding_lvl0) FROM project WHERE funding_lvl0 !=''"; /*FP7*/ /*12: total number of fp7 pubs */ $qfp7pubstotal = "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 = '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_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 = 'FP7' and bestlicense='Open Access'"; /*16: fp7 restricted pubs*/ $qfp7respubs = "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 = 'FP7' and bestlicense='Restricted' and type='publication'"; /*17: fp7 pubs in embargo*/ $qfp7embpubs = "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 = 'FP7' and bestlicense='Embargo' and type='publication'"; /*18: total number of fp7 pubs with sc39*/ $qsc39fp7pubstotal = "SELECT count(distinct result_projects.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_projects.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(r.id) from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and funding_lvl2='ERC'"; /*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_projects.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_projects.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_projects.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'"; /*WT*/ /*28: total number of wt pubs*/ $qwtpubstotal = "select count(r.id) from result r natural join result_projects rp join project p on rp.project=p.id where type='publication' and date <> '' and funding_lvl0='WT'"; /*29: wt projects with pubs*/ $qwtprojpubs = "SELECT count(distinct project.id) FROM result, project, result_projects where result_projects.project = project.id and project.funding_lvl0='WT' and result.result_projects = result_projects.id and type='publication'"; /*30: total wt projects*/ $qwtprojtotal = "SELECT count(id) FROM project WHERE funding_lvl0 = 'WT'"; /*31: wt open access pubs*/ $qwtoapubs = "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 = 'WT' and bestlicense='Open Access' and type='publication'"; /*32: wt restricted pubs*/ $qwtrespubs = "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 = 'WT' and bestlicense='Restricted' and type='publication'"; /*33: wt embargo pubs*/ $qwtembpubs = "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 = 'WT' and bestlicense='Embargo' and type='publication'"; /*Natalia's extra stuff */ /*34: datasources with publication results */ $datasrc_withpubs = "SELECT count(distinct rd.datasource) from result_datasources rd join result r on r.id=rd.id where r.type='publication'"; /*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(id) from result where type='dataset';"; /*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); /*WT*/ /*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); /*35*/ $this->storeQuery('org_withpubs', $org_withpubs); /*36*/ $this->storeQuery('data_total', $data_total); //$this->log->info("statistics numbers: ".print_r($temp, TRUE)); //$this->cache->connect(); // $this->cache->hmset("STATS_NUMBERS", $temp); //$this->cache->save(); } } $csn = new ComputeStatsNumbers(); $csn->computeStats(); ?>