D-Net

ALTER TABLE shadow.concept OWNER TO sqoop;

ALTER TABLE shadow.datasource OWNER TO sqoop;

CREATE TABLE datasource_languages (

ALTER TABLE shadow.datasource_languages OWNER TO sqoop;

ALTER TABLE shadow.datasource_organizations OWNER TO sqoop;

ALTER TABLE shadow.result_datasources OWNER TO sqoop;

ALTER TABLE shadow.datasource_results OWNER TO sqoop;

ALTER TABLE shadow.defaults OWNER TO sqoop;

ALTER TABLE shadow.category OWNER TO sqoop;

ALTER TABLE shadow.claim OWNER TO sqoop;

 ALTER TABLE shadow.context OWNER TO sqoop;

ALTER TABLE shadow.organization OWNER TO sqoop;

ALTER TABLE shadow.organization_datasources OWNER TO sqoop;

ALTER TABLE shadow.project_organizations OWNER TO sqoop;

ALTER TABLE shadow.organization_projects OWNER TO sqoop;

ALTER TABLE shadow.project OWNER TO sqoop;

ALTER TABLE shadow.project_results OWNER TO sqoop;

ALTER TABLE shadow.result OWNER TO sqoop;

ALTER TABLE shadow.result_claims OWNER TO sqoop;

ALTER TABLE shadow.result_classifications OWNER TO sqoop;

ALTER TABLE shadow.result_concepts OWNER TO sqoop;

CREATE TABLE result_languages (

ALTER TABLE shadow.result_languages OWNER TO sqoop;

ALTER TABLE shadow.result_results OWNER TO sqoop;

ALTER TABLE shadow.result_topics OWNER TO sqoop;

SET search_path = public;;

 CREATE OR REPLACE FUNCTION shadow.extra_defaults_datasource()

  RETURNS void AS

--drop table if exists shadow.project_results_publication CASCADE ;

--CREATE table shadow.project_results_publication AS

--SELECT  "shadow".project_results.result AS result, 

--"shadow".project_results.id AS project_results,

--"shadow"."result"."date" as  resultdate,

--"shadow"."project"."enddate" as projectenddate, 

--to_date("shadow"."result"."date", 'YYYY-MM-DD') - to_date("shadow"."project"."enddate", 'YYYY-MM-DD') as daysfromend

--FROM  "shadow".project_results, "shadow".result,  "shadow".project 

 --where  "shadow".project_results.result="shadow".result.id and  "shadow".result.type='publication' and  "shadow".project.project_results= "shadow".project_results.id;

	--view

--create or replace view shadow.res_pubs as select shadow.result.id from shadow.result where result.type='publication';

--query with view for has pubs

--UPDATE "shadow"."project" SET haspubs='yes' 

--WHERE id in ( select id from shadow.project_results 

 --  where shadow.project_results.result in ( select id from shadow.res_pubs ) and 

  --"shadow"."project_results".id = "shadow"."project".id ) 

  --and shadow.project.id in (select id from shadow.project_results);

DROP type if exists  infra_report_rec  cascade;

CREATE TYPE  infra_report_rec as

(Publications varchar(50), Open_Access_Publications varchar(50),

 FP7_Publications varchar(50), FP7_Closed_Access_Publications varchar(50),

 FP7_Open_Access_Publications varchar(50),

 FP7_Restricted_Access_Publications varchar(50), FP7_Embargo_Publications varchar(50) );

CREATE OR REPLACE FUNCTION shadow.getInfraReports()

  RETURNS   infra_report_rec

AS

  $$

DECLARE

  result_record infra_report_rec;

BEGIN

  SELECT count(*)  INTO result_record.Publications  FROM shadow.result where type='publication';

  SELECT count(*)   INTO result_record.Open_Access_Publications FROM shadow.result WHERE bestlicense='Open Access' and type='publication';

SELECT count (distinct result_projects.id) INTO result_record.FP7_Publications FROM shadow.result, shadow.result_projects, shadow.project

WHERE result.result_projects = result_projects.id and type='publication' and result_projects.project = project.id and funding_lvl0 = 'FP7';

SELECT count (distinct shadow.result_projects.id)

	INTO result_record.FP7_Closed_Access_Publications

	FROM shadow.result,

	shadow.result_projects,

	shadow.project

	WHERE result.result_projects = result_projects.id

	AND result_projects.project = project.id

	AND funding_lvl0 = 'FP7'

	AND bestlicense='Closed Access'

	AND type='publication';

SELECT count(distinct result_projects.id) INTO result_record.FP7_Open_Access_Publications  FROM shadow.result,

 shadow.result_projects,  shadow.project

 WHERE result_projects = result_projects.id AND result_projects.project = project.id and type='publication' and funding_lvl0 = 'FP7' and bestlicense='Open Access';

SELECT count(distinct result_projects.id) INTO result_record.FP7_Restricted_Access_Publications

 FROM shadow.result, shadow.result_projects, shadow.project

  WHERE result.result_projects=result_projects.id

   AND result_projects.project = project.id and funding_lvl0 = 'FP7' and bestlicense='Restricted' and type='publication';

SELECT count(distinct result_projects.id)  INTO result_record.FP7_Embargo_Publications FROM shadow.result, shadow.result_projects, shadow.project

WHERE result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'FP7' and bestlicense='Embargo' and type='publication';

RETURN result_record;

END

$$ LANGUAGE plpgsql;

drop type if exists fp7_report_rec cascade;

CREATE TYPE fp7_report_rec as

(FP7_Publications varchar(50),

 FP7_Projects varchar(50),

 FP7_SC39_Projects varchar(50),

 FP7_SC39_Publications varchar(50),

 FP7_SC39_Open_Access varchar(50));

CREATE OR REPLACE FUNCTION shadow.getFp7Reports()

  RETURNS   fp7_report_rec

AS

  $$

DECLARE

  result_record fp7_report_rec;

BEGIN

--FP7 WITH PUBS

SELECT count(distinct project.id) into result_record.FP7_Publications FROM shadow.result, shadow.result_projects, shadow.project

WHERE result.result_projects = result_projects.id and type='publication'

 and result_projects.project = project.id and funding_lvl0='FP7' ;

SELECT count(id) into result_record.FP7_Projects FROM shadow.project WHERE funding_lvl0 = 'FP7';

  SELECT count(number) into result_record.FP7_SC39_Projects from shadow.project where funding_lvl0='FP7' and sc39='yes';

SELECT count(distinct project.id) into result_record.FP7_SC39_Publications FROM  shadow.result, shadow.result_projects, shadow.project

  WHERE result_projects.project=project.id and funding_lvl0 = 'FP7' and sc39='yes' and result.result_projects = result_projects.id and type='publication';

SELECT count(distinct result_projects.id) into result_record.FP7_SC39_Open_Access  FROM shadow.result, shadow.result_projects, shadow.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';

RETURN result_record;

END

$$ LANGUAGE plpgsql;

drop type wt_report_rec cascade;

CREATE TYPE wt_report_rec as

(WT_Publications varchar(50),

 WT_Projects varchar(50),

 WT_Open_Access varchar(50),

 WT_Restricted_Access varchar(50),

 WT_Embargo varchar(50));

CREATE OR REPLACE FUNCTION shadow.getWTReports()

  RETURNS   wt_report_rec

AS

  $$

DECLARE

  result_record wt_report_rec;

BEGIN

SELECT count(distinct project.id)  into result_record.WT_Publications  FROM shadow.result, shadow.project, shadow.result_projects

where result_projects.project = project.id and project.funding_lvl0='WT' and result.result_projects = result_projects.id and type='publication';

SELECT count(id)  into  result_record.WT_Projects FROM project WHERE funding_lvl0 = 'WT';

SELECT count(distinct result_projects.id)   into  result_record.WT_Open_Access  FROM shadow.result, shadow.result_projects, shadow.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';

SELECT count(distinct result_projects.id)  into  result_record.WT_Restricted_Access   FROM shadow.result, shadow.result_projects,

shadow.project WHERE result.result_projects = result_projects.id AND

result_projects.project = project.id and funding_lvl0 = 'WT' and

bestlicense='Restricted' and type='publication';

SELECT count(distinct result_projects.id) into  result_record.WT_Embargo    FROM shadow.result, shadow.result_projects, shadow.project

WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funding_lvl0 = 'WT'

and bestlicense='Embargo' and type='publication';

RETURN result_record;

END

$$ LANGUAGE plpgsql;

drop type if exists erc_report_rec cascade;

CREATE TYPE erc_report_rec as

(ERC_Publications varchar(50),

 ERC_Projects varchar(50),

 ERC_Open_Access  varchar(50),

 ERC_Restricted_Access  varchar(50),

 ERC_Embargo varchar(50));

CREATE OR REPLACE FUNCTION shadow.getERCReports()

  RETURNS   erc_report_rec

AS

  $$

DECLARE

  result_record erc_report_rec;

BEGIN

SELECT count(distinct project.id) into result_record.ERC_Publications FROM shadow.result, shadow.project, shadow.result_projects

where result_projects.project = project.id and project.funding_lvl2='ERC' and result.result_projects = result_projects.id and type='publication';

SELECT count(id)  into result_record.ERC_Projects FROM shadow.project WHERE funding_lvl2 = 'ERC';

SELECT count(distinct result_projects.id) into result_record.ERC_Open_Access FROM shadow.result,

shadow.result_projects, shadow.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';

SELECT count(distinct result_projects.id)  into result_record.ERC_Restricted_Access FROM shadow.result, shadow.result_projects, shadow.project WHERE

 result.result_projects = result_projects.id AND result_projects.project = project.id and funding_lvl2 = 'ERC' and bestlicense='Restricted' and type='publication';

SELECT count(distinct result_projects.id)  into result_record.ERC_Embargo FROM shadow.result, shadow.result_projects, shadow.project

	WHERE result.result_projects=result_projects.id AND result_projects.project = project.id and funding_lvl2 = 'ERC' and bestlicense='Embargo' and type='publication';

RETURN result_record;

END

$$ LANGUAGE plpgsql;