D-Net

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // latitude

        buff.append(getLatLongField(metadata.getLatitude().getValue(), DELIM, ENCLOSING));

        // longtitude

        buff.append(getLatLongField(metadata.getLongitude().getValue(), DELIM, ENCLOSING));

        //website

        buff.append(getStringField(metadata.getWebsiteurl().getValue(), DELIM, ENCLOSING));

        //harvested

        buff.append(getStringField("false", DELIM, ENCLOSING));

        // deletedByInference

        buff.append(getStringField(String.valueOf(oaf.getDataInfo().getDeletedbyinference()), DELIM, ENCLOSING));

        // number??

        buff.append(getStringField("1", DELIM, ENCLOSING));

        // `organization_datasources`,

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // organization_projects

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        //website

        buff.append(getStringField(metadata.getWebsiteurl().getValue(), DELIM, ENCLOSING));

        // deletedByInference

        buff.append(getStringField(String.valueOf(oaf.getDataInfo().getDeletedbyinference()), DELIM, ENCLOSING));

        // number

        buff.append(getStringField("1", DELIM, ENCLOSING));

        // result_topics/

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // result_languages

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // `result_projects`,

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // `result_datasources`,

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // `result_classifications`,

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        /// `result_infrastructures`,

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // `result_claims`,

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // `result_results`,

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // pubtitle

        String titleString = new String();

        for (int i = 0; i < metadata.getTitleList().size(); i++) {

            StructuredProperty title = metadata.getTitleList().get(i);

            if (i == 0) {

                titleString = title.getValue().replaceAll("\\s+", " ");

                titleString = titleString.replaceAll("\n", " ");

            }

            break;

        }

        //  pubtitle

        buff.append(getStringField(titleString, DELIM, ENCLOSING));

        //   format

        String formatString = new String();

        for (StringField format : metadata.getFormatList()) {

            formatString += format.getValue() + ";";

        }

        buff.append(getStringField(formatString, DELIM, ENCLOSING));

        // date CHANGED THIS TO DATE FORMAT

        // access_mode,

        buff.append(getStringField(getAccessMode(oaf.getEntity().getResult()), DELIM, ENCLOSING));

        int authors = 0;

        String sources = new String();

        for (Instance instance : (oaf.getEntity().getResult().getInstanceList())) {

            List<String> urls = instance.getUrlList();

            for (String url : urls) {

                sources += cleanUrl(url, DELIM, ENCLOSING) + " ;";

            }

        }

        //sources

        sources = ENCLOSING + sources + ENCLOSING + DELIM;

        buff.append(sources);

        // deletedByInference

        buff.append(getStringField(String.valueOf(oaf.getDataInfo().getDeletedbyinference()), DELIM, ENCLOSING));

        // number??

        buff.append(getStringField("1", DELIM, ENCLOSING));

        // project_organizations

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        // project_results

        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);

        //title!

        // `url`,

        buff.append(getStringField(metadata.getWebsiteurl().getValue(), DELIM, ENCLOSING));

        buff.append(getNumericField(metadata.getEnddate().getValue(), DELIM, ENCLOSING));

        buff.append(getNumericField(metadata.getStartdate().getValue(), DELIM, ENCLOSING));

        //esc39

        buff.append(getStringField(metadata.getEcsc39().getValue(), DELIM, ENCLOSING));

        //getUrl

        String sources = new String();

        for (Instance instance : (oaf.getEntity().getResult().getInstanceList())) {

            List<String> urls = instance.getUrlList();

            for (String u : urls) {

                sources += u + ";";

            }

        }

        sources = cleanUrl(sources, DELIM, ENCLOSING);

        sources = ENCLOSING + sources + ENCLOSING + DELIM;

        buff.append(sources);

        // deletedByInference

        buff.append(getStringField(String.valueOf(oaf.getDataInfo().getDeletedbyinference()), DELIM, ENCLOSING));

        // `number`

        buff.append(getStringField("1", DELIM, ENCLOSING));

            return ENCLOSING + data + ENCLOSING + DELIM;

        String q = "{call create_charts()}";

        String q = "{call create_chart_indexes()}";

        String q = "{call create_indexes()}";

        String q = "{call create_views()}";

        update_project_results();

        //update_project_has_pubs();

        //update_project_pubs_count();

        //update_project_delated_pubs();

        //update_project_daysforlastpub();

        //update_project_delayed_pubs();

        String q = "{call extra_defaults_datasource()}";

        String q = "{call update_project_results()}";

        String q = "{call project_has_pubs()}";

        String q = "{call project_pubs_count()}";

        String q = "{call project_delayedpubs()}";

        String q = "{call project_daysforlastpub()}";

        String q = "{call project_delayed()}";

        String q = "{call cleanTemps()}";

            statsDao.cleanTables();

    datasource_results text NOT NULL,

    datasource_organizations text NOT NULL,

    datasource_languages text NOT NULL,

    datasource_topics text NOT NULL,

    latitude text,

    longitude text,

    websiteurl text,

    piwik_id integer,

    deletedbyinference TEXT,

    number integer DEFAULT 1 NOT NULL

    organization_datasources text NOT NULL,

    organization_projects text NOT NULL,

    country text,

    websiteurl  text,

  deletedbyinference TEXT,

  number integer DEFAULT 1 NOT NULL

    project_organizations text NOT NULL,

    project_results text NOT NULL,

     funding_lvl3 text,

    url text,

    code text,

    ecarticle293 text,

    sources text,

  deletedbyinference TEXT,

    number text

    id text NOT NULL,

    result_topics text NOT NULL,

    result_languages text NOT NULL,

    result_projects text NOT NULL,

    result_datasources text NOT NULL,

    result_classifications text NOT NULL,

    result_infrastructures text NOT NULL,

    result_claims text NOT NULL,

    result_results text NOT NULL,

  title text,

  format text,

  access_mode text,

  authors integer,

  source text,

  deletedbyinference TEXT,

  number integer DEFAULT 1 NOT NULL

INSERT INTO "shadow".datasource (id,datasource_results,datasource_organizations,datasource_languages,datasource_topics,name,type,compatibility,number) (SELECT 'other','other','other','other','other','Other','Repository','unknown',1 WHERE NOT EXISTS (SELECT 1 FROM "shadow".datasource WHERE name='Unknown Repository'));

UPDATE shadow.project SET funder='FCT' WHERE funder='Fundação para a Ciência e a Tecnologia, I.P.';

  update shadow.datasource set harvested ='true' where datasource.id in ( select distinct d.id from datasource d, datasource_results dr where d.id=dr.id);

 where  "shadow".result_projects.id="shadow".result.result_projects

 and  "shadow".project.project_results= "shadow".result_projects.project;

 CREATE OR REPLACE VIEW shadow.project_pub_count AS

 SELECT count(*) AS count, project_results_publication.project_results

 FROM shadow.project_results_publication

 GROUP BY project_results_publication.project_results;

--HAS PUBS

UPDATE "shadow"."project" SET haspubs='yes' WHERE project_results in (select project_results from shadow.project_results_publication );

 --COUNT PUBS

UPDATE "shadow"."project" SET numpubs=( SELECT  count from  shadow.project_pub_count

WHERE shadow.project_pub_count.project_results = shadow.project.project_results )

where "shadow"."project".project_results in (  SELECT "shadow".project_results_publication.project_results   FROM  shadow.project_results_publication  );

--delayedpubs PUBS

create or replace view shadow.delayedpubs as SELECT count(*) , project_results from "shadow"."project_results_publication"

 WHERE  "shadow"."project_results_publication".daysfromend > 0 group by project_results;

UPDATE "shadow"."project" SET delayedpubs = (SELECT "shadow"."delayedpubs".count from "shadow"."delayedpubs"

 WHERE  "shadow"."delayedpubs".project_results=project.project_results ) where "shadow".project.project_results in ( select project_results from  "shadow"."delayedpubs" );

--daysforlastpub

UPDATE "shadow"."project" SET daysforlastpub = (

SELECT max(daysfromend)

FROM "shadow"."project_results_publication" WHERE shadow.project.project_results = shadow.project_results_publication.project_results AND "shadow"."project_results_publication".daysfromend > 0  )

where    "shadow".project.project_results in ( select project_results from  "shadow"."delayedpubs" );

--delayed

UPDATE "shadow"."result" SET delayed = 'yes' WHERE result.id IN

(SELECT  result  from shadow.project_results_publication where daysfromend >0);

CREATE INDEX datasource_datasource_languages ON  "shadow".datasource USING btree (datasource_languages);

CREATE INDEX datasource_datasource_organizations ON   "shadow".datasource USING btree (datasource_organizations);

CREATE INDEX datasource_datasource_results ON   "shadow".datasource USING btree (datasource_results);

CREATE INDEX datasource_datasource_topics ON   "shadow".datasource USING btree (datasource_topics);

CREATE INDEX result_access_mode ON   "shadow".result USING btree (access_mode);

CREATE INDEX result_result_datasources ON   "shadow".result USING btree (result_datasources);

CREATE INDEX result_result_languages ON   "shadow".result USING btree (result_languages);

CREATE INDEX result_result_projects ON   "shadow".result USING btree (result_projects);

CREATE INDEX result_result_topics ON   "shadow".result USING btree (result_topics);

CREATE INDEX project_project_results ON   "shadow"."project" USING btree (project_results);

CREATE INDEX org_dtsrc

  ON shadow.organization

  USING btree

  (organization_datasources COLLATE pg_catalog."default");

CREATE INDEX org_proj

  ON shadow.organization

  USING btree

  (organization_projects COLLATE pg_catalog."default");

CREATE INDEX res_class

  ON shadow.result

  USING btree

  (result_classifications COLLATE pg_catalog."default");

CREATE OR REPLACE    VIEW  "shadow".datasource_results   as  SELECT datasource as id  , id  as  result FROM "shadow".result_datasources ;

CREATE OR REPLACE   VIEW "shadow".organization_datasources as SELECT   organization  as id , id as datasource  FROM "shadow".datasource_organizations ;

CREATE OR REPLACE   VIEW "shadow".organization_projects as SELECT  id  as project, organization as id FROM "shadow".project_organizations ;

  WHERE "shadow".datasource.datasource_results = "shadow".datasource_results.id AND "shadow".datasource_results.result = "shadow".result.id AND

  "shadow".result_topics.id = "shadow".result.result_topics;

CREATE TABLE "shadow".chart_project_year AS SELECT p.id, r.year, count( distinct r.id) FROM "shadow".result r, "shadow".result_projects rp, "shadow".project p WHERE r.id=rp.id AND p.id=rp.project AND r.year>=p.start_year GROUP BY p.id, r.year;

--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;

	<version>3.0.0-SNAPSHOT</version>