Index: modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/utils/Serializer.java
===================================================================
--- modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/utils/Serializer.java	(revision 54430)
+++ modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/utils/Serializer.java	(revision 54431)
@@ -22,6 +22,8 @@
 import org.apache.log4j.Logger;
 import org.jsoup.Jsoup;
 
+import java.text.DateFormat;
+import java.text.ParseException;
 import java.text.SimpleDateFormat;
 import java.util.ArrayList;
 import java.util.Date;
@@ -100,6 +102,7 @@
         switch (valueEntity.getType()) {
             case datasource:
                 getDatasourceLanguages(valueEntity, relations, DELIM, ENCLOSING);
+                getDatasourceWebsite(valueEntity, relations, DELIM, ENCLOSING);
             case result:
                 getResultTopics(valueEntity, relations, DELIM, ENCLOSING);
                 getResultLanguages(valueEntity, relations, DELIM, ENCLOSING);
@@ -109,6 +112,7 @@
                 getResultDois(valueEntity, relations, DELIM, ENCLOSING);
                 getResultCitations(valueEntity, relations, DELIM, ENCLOSING);
                 getResultDescriptions(valueEntity, relations, DELIM, ENCLOSING);
+                getResultExtra(valueEntity, relations, DELIM, ENCLOSING);
 
             case project:
                 getProjectKeywords(valueEntity, relations, DELIM, ENCLOSING);
@@ -119,7 +123,46 @@
 
     }
 
+    private static void getDatasourceWebsite(OafEntity valueEntity, Multimap<String, String> relations, String DELIM, String ENCLOSING) {
+        Datasource d = valueEntity.getDatasource();
+        Metadata metadata = d.getMetadata();
 
+        relations.put("datasourceWebsite", getStringField(metadata.getWebsiteurl().getValue(), DELIM, ENCLOSING));
+    }
+
+    private static void getResultExtra(OafEntity valueEntity, Multimap<String, String> relations, String DELIM, String ENCLOSING) {
+        Result result = valueEntity.getResult();
+        Result.Metadata metadata = result.getMetadata();
+
+        StringBuilder buff = new StringBuilder();
+        String titleString = "";
+
+        for (int i = 0; i < metadata.getTitleList().size(); i++) {
+            StructuredProperty title = metadata.getTitleList().get(i);
+
+            titleString = title.getValue().replaceAll("\\s+", " ");
+            titleString = titleString.replaceAll("\n", " ");
+            break;
+        }
+
+        //  pubtitle
+        buff.append(getStringField(titleString, DELIM, ENCLOSING));
+
+        String sources = "";
+        for (Instance instance : (result.getInstanceList())) {
+            List<String> urls = instance.getUrlList();
+            for (String url : urls) {
+                sources += cleanUrl(url, DELIM, ENCLOSING) + " ;";
+            }
+        }
+
+        //sources
+        sources = ENCLOSING + sources + ENCLOSING + DELIM;
+        buff.append(sources);
+
+        relations.put("resultExtra", buff.toString());
+    }
+
     private static void getOriginalId(OafEntity oafEntity, Multimap<String, String> relations, String DELIM, String ENCLOSING) {
 
         String relName = oafEntity.getType().toString().toLowerCase() + "Oid";
@@ -289,11 +332,6 @@
         Metadata metadata = oaf.getEntity().getDatasource().getMetadata();
         StringBuilder buff = new StringBuilder();
 
-        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);
-
         // name
         if (metadata.getOfficialname().getValue().equalsIgnoreCase("unknown")) {
             buff.append(getStringField("Unknown Repository", DELIM, ENCLOSING));
@@ -300,8 +338,8 @@
         } else {
             buff.append(getStringField(metadata.getOfficialname().getValue(), DELIM, ENCLOSING));
         }
+
         // type
-
         if (metadata.hasDatasourcetype()) {
             buff.append(getStringField(metadata.getDatasourcetype().getClassname().replaceFirst(".*::", ""), DELIM, ENCLOSING));
         }
@@ -309,12 +347,6 @@
         // compatibility,
         buff.append(getStringField(metadata.getOpenairecompatibility().getClassname(), DELIM, ENCLOSING));
 
-        // latitude
-        buff.append(getLatLongField(metadata.getLatitude().getValue(), DELIM, ENCLOSING));
-
-        // longtitude
-        buff.append(getLatLongField(metadata.getLongitude().getValue(), DELIM, ENCLOSING));
-
         // dateofvalidation,
         buff.append(getStringDateField(metadata.getDateofvalidation().getValue(), DELIM, ENCLOSING));
 
@@ -321,8 +353,8 @@
         // yearofvalidation,
         buff.append(getYearInt(metadata.getDateofvalidation().getValue(), DELIM, ENCLOSING));
 
-        //website
-        buff.append(getStringField(metadata.getWebsiteurl().getValue(), DELIM, ENCLOSING));
+        //harvested
+        buff.append(getStringField("false", DELIM, ENCLOSING));
 
         //piwik_id
         String piwik_id = "";
@@ -334,15 +366,6 @@
         }
         buff.append(getStringField(cleanNumber(piwik_id), 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));
-
         return buff.toString();
 
     }
@@ -352,10 +375,6 @@
         StringBuilder buff = new StringBuilder();
         Organization.Metadata metadata = oaf.getEntity().getOrganization().getMetadata();
 
-        // `organization_datasources`,
-        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);
-        // organization_projects
-        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);
         // `name`,
         buff.append(getStringField(metadata.getLegalname().getValue(), DELIM, ENCLOSING));
 
@@ -362,15 +381,6 @@
         // `country`,
         buff.append(getStringField(metadata.getCountry().getClassid(), DELIM, ENCLOSING));
 
-        //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));
-
         return buff.toString();
     }
 
@@ -379,65 +389,18 @@
 
         Result.Metadata metadata = oaf.getEntity().getResult().getMetadata();
 
-        // 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));
         //  publisher
-
         buff.append(getStringField(metadata.getPublisher().getValue(), DELIM, ENCLOSING));
 
         //  journal
-
         buff.append(getStringField(metadata.getJournal().getName(), DELIM, ENCLOSING));  //#null#!
 
         // year
         buff.append(getYearInt(metadata.getDateofacceptance().getValue(), DELIM, ENCLOSING));
 
-        // date CHANGED THIS TO DATE FORMAT
+        // date
         buff.append(getStringDateField(metadata.getDateofacceptance().getValue(), DELIM, ENCLOSING));
 
-        // access_mode,
-        buff.append(getStringField(getAccessMode(oaf.getEntity().getResult()), DELIM, ENCLOSING));
-
         // bestlicense
         buff.append(getStringField(getBestLicense(oaf.getEntity().getResult()), DELIM, ENCLOSING));
 
@@ -448,8 +411,9 @@
         buff.append(getStringDateField(metadata.getEmbargoenddate().getValue(), DELIM, ENCLOSING));
 
         // `authors`,
-        int authors = 0;
+        int authors = metadata.getAuthorCount();
 
+
         String delayed = "no";
 
         for (OafRel rel : oaf.getEntity().getCachedRelList()) {
@@ -477,27 +441,6 @@
         //authors
         buff.append(getNumericField(String.valueOf(authors), DELIM, ENCLOSING));
 
-        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));
         return buff.toString();
 
     }
@@ -568,26 +511,18 @@
         StringBuilder buff = new StringBuilder();
         Project.Metadata metadata = oaf.getEntity().getProject().getMetadata();
         
-        // project_organizations
-        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);
-        
-        // project_results
-        buff.append(cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING) + DELIM);
-
-
         // `acronym`,
         String acronym = metadata.getAcronym().getValue();
         if (acronym.equalsIgnoreCase("UNKNOWN")) {
             acronym = metadata.getTitle().getValue();
         }
-
         buff.append(getStringField(acronym, DELIM, ENCLOSING));
 
-        //title!
+        //title
         buff.append(getStringField(metadata.getTitle().getValue(), DELIM, ENCLOSING));
 
+        //funding_lvl
         List<StringField> fundList = metadata.getFundingtreeList();
-
         if (!fundList.isEmpty()) // `funding_lvl0`,
         {
             //TODO funder + 3 funding levels
@@ -597,12 +532,11 @@
 	        funding_lvl2 text,
 	        funding_lvl3 text,*/
             buff.append(FundingParser.getFundingInfo(fundList.get(0).getValue(), DELIM, ENCLOSING));
-
         } else {
             buff.append(FundingParser.getFundingInfo("", DELIM, ENCLOSING));
-
         }
 
+        //sc39
         String sc39 = metadata.getEcsc39().getValue().toString();
         if (sc39.equalsIgnoreCase("true") || sc39.equalsIgnoreCase("t") || sc39.contains("yes")) {
             sc39 = "yes";
@@ -609,18 +543,12 @@
         } else if (sc39.equalsIgnoreCase("false") || sc39.equalsIgnoreCase("f") || sc39.contains("no")) {
             sc39 = "no";
         }
-
         buff.append(getStringField(sc39, DELIM, ENCLOSING));
 
-
         //project_type
         buff.append(getStringField(metadata.getContracttype().getClassid(),DELIM, ENCLOSING));
 
-        // `url`,
-        buff.append(getStringField(metadata.getWebsiteurl().getValue(), DELIM, ENCLOSING));
-
         // start_year
-
         buff.append(getYearInt(metadata.getStartdate().getValue(), DELIM, ENCLOSING));
 
         // end_year
@@ -627,7 +555,6 @@
         buff.append(getYearInt(metadata.getEnddate().getValue(), DELIM, ENCLOSING));
 
         // duration enddate-startdate
-
         buff.append(getYearDifferenceInteger(metadata.getEnddate().getValue(), metadata.getStartdate().getValue(), DELIM, ENCLOSING));
 
         // haspubs
@@ -637,10 +564,10 @@
         buff.append(getNumericField("0", DELIM, ENCLOSING));
 
         // enddate
-        buff.append(getNumericField(metadata.getEnddate().getValue(), DELIM, ENCLOSING));
+        buff.append(getStringDateField(metadata.getEnddate().getValue(), DELIM, ENCLOSING));
 
         // startdate
-        buff.append(getNumericField(metadata.getStartdate().getValue(), DELIM, ENCLOSING));
+        buff.append(getStringDateField(metadata.getStartdate().getValue(), DELIM, ENCLOSING));
 
         // `daysforlastpub`,
         buff.append(getNumericField("", DELIM, ENCLOSING));
@@ -650,32 +577,10 @@
 
         //call identifier
         buff.append(getStringField(metadata.getCallidentifier().getValue(), DELIM, ENCLOSING));
+
         //code
         buff.append(getStringField(metadata.getCode().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 buff.toString();
 
     }
@@ -809,7 +714,13 @@
             data = data.replace(DELIM, " ");
             data = data.replace(ENCLOSING, " ");
             data = data.replaceAll("\\r\\n|\\r|\\n", "");
-            return ENCLOSING + data + ENCLOSING + DELIM;
+            try {
+                DateFormat format = new SimpleDateFormat("yyyy-MM-dd");
+                data = format.format(format.parse(data));
+                return ENCLOSING + data + ENCLOSING + DELIM;
+            } catch (ParseException e) {
+                return ENCLOSING + "0" + ENCLOSING + DELIM;
+            }
         }
     }
 
Index: modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/daos/StatsDAO.java
===================================================================
--- modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/daos/StatsDAO.java	(revision 54430)
+++ modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/daos/StatsDAO.java	(revision 54431)
@@ -130,6 +130,23 @@
         log.info("Schema renamed");
     }
 
+    public void addArrayColumns() throws Exception {
+        log.info("Adding new columns ...");
+        Connection con = statsDatasource.getConnection();
+
+        startTime = System.currentTimeMillis();
+        String q = "{call shadow.create_arrays()}";
+
+        CallableStatement st = con.prepareCall(q);
+        st.execute();
+
+        st.close();
+        con.close();
+
+        endtime = System.currentTimeMillis();
+        log.info("Time to add columns: " + ((endtime - startTime) / 60000) + " minutes ");
+    }
+
     public void cleanTables() throws Exception {
         log.info(" Cleaning Tables...");
         Connection con = statsDatasource.getConnection();
@@ -169,7 +186,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call create_charts()}";
+        String q = "{call shadow.create_charts()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -191,7 +208,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call create_chart_indexes()}";
+        String q = "{call shadow.create_chart_indexes()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -212,7 +229,7 @@
         log.info(" Building Database Indexes...");
         Connection con = statsDatasource.getConnection();
         startTime = System.currentTimeMillis();
-        String q = "{call create_indexes()}";
+        String q = "{call shadow.create_indexes()}";
         CallableStatement st = con.prepareCall(q);
         st.execute();
         st.close();
@@ -231,7 +248,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call create_views()}";
+        String q = "{call shadow.create_views()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -254,12 +271,12 @@
         startTime = System.currentTimeMillis();
 
         populateDefaults();
-        update_project_results();
-        //update_project_has_pubs();
-        //update_project_pubs_count();
-        //update_project_delated_pubs();
-        //update_project_daysforlastpub();
-        //update_project_delayed_pubs();
+        //update_project_results();
+        update_project_has_pubs();
+        update_project_pubs_count();
+        update_project_delated_pubs();
+        update_project_daysforlastpub();
+        update_project_delayed_pubs();
         cleanUp();
 
         endtime = System.currentTimeMillis();
@@ -274,7 +291,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call extra_defaults_datasource()}";
+        String q = "{call shadow.extra_defaults_datasource()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -296,7 +313,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call update_project_results()}";
+        String q = "{call shadow.update_project_results()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -319,7 +336,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call project_has_pubs()}";
+        String q = "{call shadow.project_has_pubs()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -341,7 +358,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call project_pubs_count()}";
+        String q = "{call shadow.project_pubs_count()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -363,7 +380,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call project_delayedpubs()}";
+        String q = "{call shadow.project_delayedpubs()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -387,7 +404,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call project_daysforlastpub()}";
+        String q = "{call shadow.project_daysforlastpub()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -411,7 +428,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call project_delayed()}";
+        String q = "{call shadow.project_delayed()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
@@ -434,7 +451,7 @@
         Connection con = statsDatasource.getConnection();
 
         startTime = System.currentTimeMillis();
-        String q = "{call cleanTemps()}";
+        String q = "{call shadow.cleanTemps()}";
 
         CallableStatement st = con.prepareCall(q);
         st.execute();
Index: modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/drivers/DBDriver.java
===================================================================
--- modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/drivers/DBDriver.java	(revision 54430)
+++ modules/dnet-openaire-stats/branches/new-schema/src/main/java/eu/dnetlib/data/mapreduce/hbase/statsExport/drivers/DBDriver.java	(revision 54431)
@@ -39,7 +39,8 @@
     public void finalizedDB() throws Exception {
 
         try {
-            statsDao.cleanTables();
+            //statsDao.cleanTables();
+            //statsDao.addArrayColumns();
             statsDao.buildIndexes();
             statsDao.buildViews();
             statsDao.executeExtraInserts();
Index: modules/dnet-openaire-stats/branches/new-schema/src/main/resources/eu/dnetlib/data/mapreduce/hbase/statsExport/stats_db_schema.sql
===================================================================
--- modules/dnet-openaire-stats/branches/new-schema/src/main/resources/eu/dnetlib/data/mapreduce/hbase/statsExport/stats_db_schema.sql	(revision 54430)
+++ modules/dnet-openaire-stats/branches/new-schema/src/main/resources/eu/dnetlib/data/mapreduce/hbase/statsExport/stats_db_schema.sql	(revision 54431)
@@ -43,22 +43,13 @@
 
 CREATE TABLE shadow.datasource (
     id text NOT NULL,
-    datasource_results text NOT NULL,
-    datasource_organizations text NOT NULL,
-    datasource_languages text NOT NULL,
-    datasource_topics text NOT NULL,
     name text ,
     type text,
     compatibility text,
-    latitude text,
-    longitude text,
     dateofvalidation text,
     yearofvalidation integer,
-    websiteurl text,
-    piwik_id integer,
     harvested TEXT DEFAULT 'false',
-    deletedbyinference TEXT,
-    number integer DEFAULT 1 NOT NULL
+    piwik_id integer
 );
 
 --
@@ -70,7 +61,15 @@
     language text
 );
 
+--
+-- Name: datasource_websites; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
+--
 
+CREATE TABLE shadow.datasource_websites (
+    id text NOT NULL,
+    website text
+);
+
 --
 -- Name: datasource_organizations; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace: 
 --
@@ -152,13 +151,8 @@
 
 CREATE TABLE shadow.organization (
     id text NOT NULL,
-    organization_datasources text NOT NULL,
-    organization_projects text NOT NULL,
     name text,
-    country text,
-    websiteurl  text,
-  deletedbyinference TEXT,
-  number integer DEFAULT 1 NOT NULL
+    country text
 );
 
 
@@ -203,8 +197,6 @@
 
 CREATE TABLE shadow.project (
     id text NOT NULL,
-    project_organizations text NOT NULL,
-    project_results text NOT NULL,
     acronym text ,
     title text ,
     funder text,
@@ -211,10 +203,9 @@
     funding_lvl0 text,
     funding_lvl1 text,
     funding_lvl2 text,
-     funding_lvl3 text,
+    funding_lvl3 text,
     sc39 text,
     type text,
-    url text,
     start_year integer,
     end_year integer,
     duration integer,
@@ -225,11 +216,7 @@
     daysforlastpub integer,
     delayedpubs integer,
     callidentifier  text,
-    code text,
-    ecarticle293 text,
-    sources text,
-  deletedbyinference TEXT,
-    number text
+    code text
 );
 
 --
@@ -292,33 +279,27 @@
 --
 
 CREATE TABLE shadow.result (
-    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,
+  id text NOT NULL,
   publisher text,
   journal text,
   year integer,
   date text ,
-  access_mode text,
   bestlicense text,
   type text ,
   embargo_end_date text,
   delayed text,
-  authors integer,
-  source text,
-  deletedbyinference TEXT,
-  number integer DEFAULT 1 NOT NULL
+  authors integer
 );
 
+--
+-- Name: result_extra; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace:
+--
 
+CREATE TABLE shadow.result_extra (
+  id text NOT NULL,
+  title text,
+  source text
+);
 
 --
 -- Name: result_claims; Type: TABLE; Schema: shadow; Owner: sqoop; Tablespace: 
@@ -438,7 +419,7 @@
 $BODY$
 BEGIN
 
-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'));
+INSERT INTO "shadow".datasource (id,name,type,compatibility) (SELECT 'other','Other','Repository','unknown' WHERE NOT EXISTS (SELECT 1 FROM "shadow".datasource WHERE name='Unknown Repository'));
 UPDATE "shadow".datasource SET name='Other' where name='Unknown Repository';
 UPDATE "shadow".result_datasources SET datasource=(SELECT id FROM "shadow".datasource WHERE name='Other') WHERE datasource NOT IN (SELECT id FROM "shadow".datasource);
 
@@ -451,9 +432,9 @@
 UPDATE "shadow".datasource SET yearofvalidation=null WHERE yearofvalidation='-1';
 
 
-UPDATE shadow.project SET funder='FCT' WHERE funder='Fundação para a Ciência e a Tecnologia, I.P.';
+-- 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);
+UPDATE shadow.datasource set harvested ='true' WHERE datasource.id IN (SELECT DISTINCT d.id FROM datasource d, result_datasources rd where d.id=rd.datasource);
 
 END
 $BODY$
@@ -482,9 +463,9 @@
 as daysfromend FROM  "shadow".result_projects,
 "shadow".result,
 "shadow".project
- where  "shadow".result_projects.id="shadow".result.result_projects
+ where  "shadow".result_projects.id="shadow".result.id
  and  "shadow".result.type='publication'
- and  "shadow".project.project_results= "shadow".result_projects.project;
+ and  "shadow".project.id= "shadow".result_projects.project;
 
 END
 $BODY$
@@ -503,16 +484,8 @@
 $BODY$
 BEGIN
 
+UPDATE shadow.project SET haspubs='yes' WHERE id IN (SELECT pr.id FROM shadow.project_results pr, shadow.result r WHERE pr.result=r.id AND r.type='publication');
 
- 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 );
-
-
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE
@@ -531,13 +504,8 @@
 $BODY$
 BEGIN
 
- --COUNT PUBS
+UPDATE shadow.project p SET numpubs=prr.np FROM (SELECT pr.id, count(distinct pr.result) AS np FROM shadow.project_results pr, shadow.result r WHERE pr.result=r.id AND r.type='publication' GROUP BY pr.id) AS prr WHERE prr.id=p.id;
 
-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  );
-
-
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE
@@ -555,15 +523,9 @@
   RETURNS void AS
 $BODY$
 BEGIN
---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 pp SET delayedpubs = prr.dp FROM (SELECT p.id, count(distinct r.id) as dp FROM project p, project_results pr, result r WHERE p.id=pr.id AND pr.result=r.id AND r.type='publication' AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 0 GROUP BY p.id) AS prr WHERE pp.id=prr.id;
 
-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" );
-
-
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE
@@ -581,15 +543,8 @@
 $BODY$
 BEGIN
 
+UPDATE shadow.project pp SET daysforlastpub = prr.dp FROM (SELECT p.id, max(to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD')) as dp FROM project p, project_results pr, result r WHERE p.id=pr.id AND pr.result=r.id AND r.type='publication' AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 0 GROUP BY p.id) AS prr WHERE pp.id=prr.id;
 
---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" );
-
-
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE
@@ -607,12 +562,8 @@
 $BODY$
 BEGIN
 
---delayed
+UPDATE shadow.result SET delayed = 'yes' WHERE id IN (SELECT distinct r.id FROM result r, project_results pr, project p WHERE r.id=pr.result AND pr.id=p.id AND to_date(r.date, 'YYYY-MM-DD')-to_date(p.enddate, 'YYYY-MM-DD') > 0);
 
-UPDATE "shadow"."result" SET delayed = 'yes' WHERE result.id IN
-(SELECT  result  from shadow.project_results_publication where daysfromend >0);
-
-
 END
 $BODY$
   LANGUAGE plpgsql VOLATILE
@@ -644,7 +595,33 @@
 
   ---functions
 
+
 --
+-- Name: create_arrays(); Type FUNCTION; Schema: shadow; Owner: sqoop
+--
+CREATE OR REPLACE FUNCTION shadow.create_arrays()
+  RETURNS void AS
+$BODY$
+BEGIN
+
+ALTER TABLE shadow.result ADD COLUMN funders text[], ADD COLUMN funding_lvl0 text[], ADD COLUMN projects text[], ADD COLUMN datasources text[];
+UPDATE shadow.result r SET funders = prr.funders FROM (SELECT pr.result AS rid, array_agg(distinct funder) AS funders FROM shadow.project p, shadow.project_results pr WHERE p.id=pr.id GROUP BY pr.result) AS prr WHERE r.id = prr.rid;
+UPDATE shadow.result r SET funding_lvl0 = prr.funding_lvl0 FROM (SELECT pr.result AS rid, array_agg(distinct funding_lvl0) AS funding_lvl0 FROM shadow.project p, shadow.project_results pr WHERE p.id=pr.id GROUP BY pr.result) AS prr WHERE r.id = prr.rid;
+UPDATE shadow.result r SET projects = prr.ids FROM (SELECT pr.result AS rid, array_agg(distinct pr.id) AS ids FROM shadow.project_results pr GROUP BY pr.result) AS prr WHERE r.id = prr.rid;
+-- UPDATE shadow.result r SET datasources = drr.ids FROM (SELECT rd.id AS rid, array_agg(distinct rd.datasource) AS ids FROM shadow.result_datasources rd GROUP BY rd.id) AS drr WHERE r.id = drr.rid;
+
+CREATE TABLE shadow.result_temp AS SELECT r.id, publisher, journal, year, date, bestlicense, type, embargo_end_date, delayed, authors, funders, funding_lvl0, projects, array_agg(distinct rd.datasource) as datasources FROM shadow.result r, shadow.result_datasources rd WHERE r.id=rd.id GROUP BY r.id, r.publisher, r.journal, r.year, r.date, r.bestlicense, r.type, r.embargo_end_date, r.delayed, r.authors, r.funders, r.funding_lvl0, r.projects;
+DROP TABLE shadow.result CASCADE;
+ALTER TABLE shadow.result_temp RENAME TO result;
+
+END
+$BODY$
+  LANGUAGE plpgsql VOLATILE
+  COST 100;
+ALTER FUNCTION shadow.create_arrays()
+  OWNER TO sqoop;
+
+--
 -- Name: create_indexes(); Type: FUNCTION; Schema: shadow; Owner: sqoop
 --
 
@@ -654,21 +631,13 @@
 
 BEGIN
 
-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 datasource_id ON "shadow".datasource USING btree (id);
 CREATE INDEX datasource_type ON   "shadow".datasource USING btree (type);
 CREATE INDEX datasource_name ON   "shadow".datasource USING btree (name);
 CREATE INDEX datasource_piwik_id ON "shadow".datasource USING btree (piwik_id);
-CREATE INDEX result_access_mode ON   "shadow".result USING btree (access_mode);
+
 CREATE INDEX result_authors ON   "shadow".result USING btree (authors);
 CREATE INDEX result_id ON   "shadow".result USING btree (id);
-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 result_year ON   "shadow".result USING btree (year);
 CREATE INDEX result_date ON   "shadow"."result" USING btree ("date");
 CREATE INDEX result_type ON   "shadow"."result" USING btree ("type");
@@ -677,7 +646,6 @@
 CREATE INDEX project_acronym ON   "shadow"."project" USING btree (acronym);
 CREATE INDEX project_enddate ON   "shadow"."project" USING btree (enddate);
 CREATE INDEX project_id ON   "shadow"."project" USING btree (id);
-CREATE INDEX project_project_results ON   "shadow"."project" USING btree (project_results);
 CREATE INDEX project_results_result ON   "shadow"."project_results" USING btree (result);
 CREATE INDEX project_results_project ON   "shadow"."project_results" USING btree (id);
 
@@ -688,9 +656,11 @@
 CREATE INDEX result_pids_type ON "shadow".result_pids USING btree (type COLLATE pg_catalog."default");
 CREATE INDEX result_pids_pid ON "shadow".result_pids USING btree(pid COLLATE pg_catalog."default");
 
+CREATE INDEX result_extra_id ON "shadow".result_extra USING btree(id COLLATE pg_catalog."default");
 
 CREATE INDEX datasource_oids_id ON "shadow".datasource_oids USING btree (id COLLATE pg_catalog."default");
 CREATE INDEX datasource_oids_orid ON "shadow".datasource_oids USING btree(orid COLLATE pg_catalog."default");
+CREATE INDEX datasource_websites_id ON "shadow".datasource_websites USING btree(id COLLATE pg_catalog."default");
 
 
 
@@ -737,16 +707,6 @@
   USING btree
   (country COLLATE pg_catalog."default");
 
-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 proj_funder
   ON shadow.project
   USING btree
@@ -762,7 +722,6 @@
   USING btree
   (funding_lvl0 COLLATE pg_catalog."default");
 
-
 CREATE INDEX proj_fndlvl1
   ON shadow.project
   USING btree
@@ -773,7 +732,6 @@
   USING btree
   (funding_lvl2 COLLATE pg_catalog."default");
 
-
 CREATE INDEX proj_org_org
   ON shadow.project_organizations
   USING btree
@@ -784,19 +742,11 @@
   USING btree
   (id COLLATE pg_catalog."default");
 
-
-
-
-
-
-
 CREATE INDEX res_res_id
   ON shadow.result_results
   USING btree
   (id COLLATE pg_catalog."default");
 
-
-
 CREATE INDEX res_res_res
   ON shadow.result_results
   USING btree
@@ -806,6 +756,7 @@
   ON shadow.result_languages
   USING btree
   (id COLLATE pg_catalog."default");
+
 CREATE INDEX res_conc_id
   ON shadow.result_concepts
   USING btree
@@ -816,11 +767,6 @@
   USING btree
   (concept COLLATE pg_catalog."default");
 
-CREATE INDEX res_class
-  ON shadow.result
-  USING btree
-  (result_classifications COLLATE pg_catalog."default");
-
 CREATE INDEX res_bestlicense
   ON shadow.result
   USING btree
@@ -830,6 +776,7 @@
   ON shadow.project
   USING btree
   (sc39 COLLATE pg_catalog."default");
+
 CREATE INDEX proj_fndlvl3
   ON shadow.project
   USING btree
@@ -860,7 +807,12 @@
   USING btree
   (organization COLLATE pg_catalog."default");
 
+  CREATE INDEX result_funders_idx ON shadow.result USING gin (funders);
+  CREATE INDEX result_funding_lvl0_idx ON shadow.result USING gin (funding_lvl0);
+  CREATE INDEX result_projects_idx ON shadow.result USING gin (projects);
+  CREATE INDEX result_datasources_idx ON shadow.result USING gin (datasources);
 
+
   END;$$;
 
 
@@ -876,14 +828,13 @@
 
 BEGIN
 
-CREATE OR REPLACE    VIEW  "shadow".datasource_results   as  SELECT datasource as id  , id  as  result FROM "shadow".result_datasources ;
+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_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 ;
+CREATE OR REPLACE VIEW shadow.organization_projects AS SELECT id AS project, organization as id FROM shadow.project_organizations;
 
 
-
 CREATE OR REPLACE VIEW shadow.result_projects AS SELECT  shadow.project_results.result AS id,
   shadow.project_results.id AS project, ( select to_date("shadow"."result"."date", 'YYYY-MM-DD')- to_date("shadow"."project"."enddate", 'YYYY-MM-DD')
    from shadow.result, shadow.project where shadow.result.id =  shadow.project_results.result
@@ -893,8 +844,8 @@
  CREATE OR REPLACE   VIEW "shadow".datasource_topics AS
  SELECT distinct "shadow".datasource.id, "shadow".result_topics.topic
    FROM "shadow".datasource, "shadow".datasource_results, "shadow".result, "shadow".result_topics
-  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;
+  WHERE "shadow".datasource.id = "shadow".datasource_results.id AND "shadow".datasource_results.result = "shadow".result.id AND
+  "shadow".result_topics.id = "shadow".result.id;
 
 
   END;$$;
@@ -925,7 +876,7 @@
 CREATE TABLE "shadow".chart_datasource_projects_data AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=rd.id AND pr.result=r.id and r.type='dataset' GROUP BY rd.datasource, p.title;
 
 -- project charts
-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;
+CREATE TABLE "shadow".chart_project_year AS SELECT p.id, r.year, count( distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".project p WHERE r.id=pr.result AND p.id=pr.id AND r.year>=p.start_year GROUP BY p.id, r.year;
 CREATE TABLE "shadow".chart_project_license AS SELECT pr.id, r.bestlicense, count(distinct r.id) FROM "shadow".result r, "shadow".project_results pr WHERE r.id=pr.result AND r.type='publication' GROUP BY pr.id, r.bestlicense;
 CREATE TABLE "shadow".chart_project_repos AS SELECT pr.id, d.name, count (distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".datasource d, "shadow".datasource_results dr WHERE r.id=dr.result AND d.id=dr.id AND r.id=pr.result AND r.type='publication' GROUP BY pr.id, d.name;
 
@@ -998,185 +949,5 @@
 
   END;$$;
 
---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;
-
 -- sqoopQL database dump complete
 --
Index: modules/dnet-openaire-stats/branches/new-schema/pom.xml
===================================================================
--- modules/dnet-openaire-stats/branches/new-schema/pom.xml	(revision 54430)
+++ modules/dnet-openaire-stats/branches/new-schema/pom.xml	(revision 54431)
@@ -11,7 +11,7 @@
 	<modelVersion>4.0.0</modelVersion>
 	<groupId>eu.dnetlib</groupId>
 	<artifactId>dnet-openaire-stats</artifactId>
-	<version>3.0.0-SNAPSHOT</version>
+	<version>4.0.0-SNAPSHOT</version>
 
 	<build>
 		<plugins>
