D-Net

        Result.Metadata metadata = oaf.getEntity().getResult().getMetadata();

        //  publisher

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

        //authors

        buff.append(getNumericField(String.valueOf(authors), DELIM, ENCLOSING));

        return buff.toString();

    }

            case entity:

                return cleanId(oaf.getEntity().getId(), DELIM, ENCLOSING);

            case relation:

                return cleanId(oaf.getRel().getSource(), DELIM, ENCLOSING);

        }

    @Override

    protected void map(final ImmutableBytesWritable keyIn, final Result result, final Context context) {

        try {

        } catch (Exception e) {

            context.getCounter(ERROR_COUNTERS.rottenRecords).increment(1);

        }

    }

    private boolean isValid(Oaf oaf) {

    }

        return columnMap != null && !columnMap.isEmpty();

    }

    @Override

    protected void cleanup(Context context) throws IOException, InterruptedException {

CREATE TABLE shadow.result (

  id text NOT NULL,

  publisher text,

  journal text,

  year integer,

  type text ,

  embargo_end_date text,

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

  END $BODY$;

ALTER FUNCTION shadow.create_arrays() OWNER TO sqoop;

    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 project_acronym ON   "shadow"."project" USING btree (acronym);

CREATE or replace FUNCTION "shadow".create_charts() RETURNS void LANGUAGE plpgsql AS $$

  BEGIN

    -- country nums

  END;$$;

--

    ALTER TABLE "shadow".project RENAME TO tproject;

    ALTER TABLE "shadow".organization RENAME TO torganization;

    CREATE VIEW "shadow".datasource AS SELECT tdatasource.id, tdatasource.name, tdatasource.type, tdatasource.compatibility, tdatasource.dateofvalidation, tdatasource.yearofvalidation, tdatasource.harvested, tdatasource.piwik_id, tdatasource.id AS datasource_results, tdatasource.id AS datasource_organizations, 1 AS number FROM tdatasource;

    CREATE VIEW "shadow".project AS SELECT tproject.id, tproject.acronym, tproject.title, tproject.funder, tproject.funding_lvl0, tproject.funding_lvl1, tproject.funding_lvl2, tproject.funding_lvl3, tproject.sc39, tproject.type, tproject.start_year, tproject.end_year, tproject.duration, tproject.haspubs, tproject.numpubs, tproject.enddate, tproject.startdate, tproject.daysforlastpub, tproject.delayedpubs, tproject.callidentifier, tproject.code, tproject.id AS project_results, tproject.id AS project_organizations, 1 AS number FROM tproject;

    CREATE VIEW "shadow".organization AS SELECT torganization.id, torganization.name, torganization.country, torganization.id AS organization_datasources, torganization.id AS organization_projects, 1 AS number FROM torganization;

<?xml version="1.0" encoding="UTF-8"?>

    <content url="file://$MODULE_DIR$">

    </content>

    <orderEntry type="inheritedJdk" />

    <orderEntry type="sourceFolder" forTests="false" />

            String sqlCreateTableSushiLog = "CREATE TABLE IF NOT EXISTS sushilog(source TEXT, repository TEXT, rid TEXT, date TEXT, metric_type TEXT, count INT, PRIMARY KEY(source, repository, rid, date, metric_type));";

            stmt.executeUpdate(sqlCreateTableSushiLog);

            String sqlCopyPublicSushiLog="INSERT INTO sushilog SELECT * FROM public.sushilog;";

            stmt.executeUpdate(sqlCopyPublicSushiLog);

            start.add(Calendar.MONTH, 1);

            String text = getJson(reportUrl, "", "");

                continue;

            }

        stmt.executeUpdate(sql);

        // sql = "SELECT 'OpenAIRE'::TEXT as source, d.id as repository_id, ro.id as result_id, month as date, max(views) AS count, max(openaire_referrer) AS openaire INTO views_stats FROM result_views_monthly p, datasource d, result_oids ro where p.source!='5' AND p.source=d.piwik_id and p.id=ro.orid group by repository_id, result_id, date ORDER BY repository_id, result_id, date;";

        stmt.executeUpdate(sql);

//        sql = "SELECT 'OpenAIRE'::TEXT as source, d.id as repository_id, ro.id as result_id, month as date, max(views) AS count INTO pageviews_stats FROM result_views_monthly p, datasource d, result_oids ro where p.source='5' AND p.source=d.piwik_id and p.id=ro.orid group by repository_id, result_id, date ORDER BY repository_id, result_id, date;";

        stmt.executeUpdate(sql);

        sql = "DROP VIEW IF EXISTS result_views_monthly;";

        //sql = "SELECT 'OpenAIRE'::TEXT as source, d.id as repository_id, ro.id as result_id, month as date, max(downloads) AS count INTO downloads_stats FROM result_downloads_monthly p, datasource d, result_oids ro where p.source!='5' AND p.source=d.piwik_id and p.id=ro.orid group by repository_id, result_id, date ORDER BY repository_id, result_id, date;";

//        sql = "SELECT 'OpenAIRE'::TEXT as source, d.id as repository_id, ro.id as result_id, month as date, max(downloads) AS count, max(openaire_referrer) AS openaire INTO downloads_stats FROM result_downloads_monthly p, datasource d, result_oids ro where p.source!='5' AND p.source=d.piwik_id and p.id=ro.orid group by repository_id, result_id, date ORDER BY repository_id, result_id, date;";

        stmt.executeUpdate(sql);

        sql = "DROP VIEW IF EXISTS result_downloads_monthly;";

        int diffMonth = diffYear * 12 + endCalendar.get(Calendar.MONTH) - startCalendar.get(Calendar.MONTH);

//        String sql = "SELECT to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY/MM') AS full_date INTO full_dates FROM generate_series(0, " + diffMonth + ", 1) AS offs;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX full_dates_full_date ON full_dates USING btree(full_date);";

        stmt.executeUpdate(sql);

//        sql = "SELECT coalesce(ds.source, vs.source) as source, coalesce(ds.repository_id, vs.repository_id) as repository_id, coalesce(ds.result_id, vs.result_id) as result_id, coalesce(ds.date, vs.date) as date, coalesce(ds.count, 0) as downloads, coalesce(vs.count, 0) as views, coalesce(ds.openaire, 0) as openaire_downloads, coalesce(vs.openaire, 0) as openaire_views INTO usage_stats FROM downloads_stats AS ds FULL OUTER JOIN views_stats AS vs ON ds.source=vs.source AND ds.repository_id=vs.repository_id AND ds.result_id=vs.result_id AND ds.date=vs.date;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX usage_stats_source ON usage_stats USING btree(source);";

        conn.setAutoCommit(false);

//        String sql = "SELECT entity_id AS id , COUNT(entity_id) AS number_of_views, timestamp::date AS date, source INTO repo_view_stats FROM piwiklog WHERE source!='5' AND action=\'action\' AND source_item_type=\'repItem\' GROUP BY entity_id, date, source ORDER BY entity_id, date ASC, COUNT(entity_id) DESC;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX repo_view_stats_id ON repo_view_stats USING btree (id)";

        stmt.executeUpdate(sql);

//        sql = "SELECT roid.id, sum(number_of_views), extract('year' from date) ||'/'|| LPAD(CAST(extract('month' from date) AS VARCHAR), 2, '0') AS month, source INTO repo_view_stats_monthly_clean FROM repo_view_stats rvs, result_oids roid where rvs.id=roid.orid group by roid.id, month, source;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX repo_view_stats_monthly_clean_id ON repo_view_stats_monthly_clean USING btree (id)";

        //sql="CREATE OR REPLACE view repo_view_stats_monthly AS select d.id, d.new_date AS month, case when rdm.sum is null then 0 else rdm.sum end, d.source from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY/MM') AS new_date, rdsm.source from generate_series(0, " + diffMonth +", 1) AS offs, repo_view_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum, source from repo_view_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id and d.source=rdm.source order by d.id, d.new_date";

//        sql = "select d.id, d.new_date AS month, case when rdm.sum is null then 0 else rdm.sum end, d.source INTO repo_view_stats_monthly from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY/MM') AS new_date, rdsm.source from generate_series(0, " + diffMonth + ", 1) AS offs, repo_view_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum, source from repo_view_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id and d.source=rdm.source order by d.id, d.new_date";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX repo_view_stats_monthly_id ON repo_view_stats_monthly USING btree (id)";

        conn.setAutoCommit(false);

//        String sql = "SELECT entity_id AS id, COUNT(entity_id) AS number_of_downloads, timestamp::date AS date, source INTO repo_download_stats FROM piwiklog WHERE source!='5' AND action=\'download\' AND source_item_type=\'repItem\' GROUP BY entity_id, date, source ORDER BY entity_id, date ASC, COUNT(entity_id) DESC;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX repo_download_stats_id ON repo_download_stats USING btree (id)";

        stmt.executeUpdate(sql);

//        sql = "SELECT roid.id, sum(number_of_downloads), extract('year' from date) ||'/'|| LPAD(CAST(extract('month' from date) AS VARCHAR), 2, '0') AS month, source INTO repo_download_stats_monthly_clean FROM repo_download_stats rvs, result_oids roid WHERE rvs.id=roid.orid GROUP BY roid.id, month, source;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX repo_download_stats_monthly_clean_id ON repo_download_stats_monthly_clean USING btree (id)";

        //sql="CREATE OR REPLACE view repo_download_stats_monthly AS select d.id, d.new_date AS month, case when rdm.sum is null then 0 else rdm.sum end, d.source from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY/MM') AS new_date, rdsm.source from generate_series(0, " + diffMonth +", 1) AS offs, repo_download_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum, source from repo_download_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id and d.source=rdm.source order by d.id, d.new_date";

        // sql = "select d.id, d.new_date AS month, case when rdm.sum is null then 0 else rdm.sum end, d.source INTO repo_download_stats_monthly from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY/MM') AS new_date, rdsm.source from generate_series(0, " + diffMonth + ", 1) AS offs, repo_download_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum, source from repo_download_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id and d.source=rdm.source order by d.id, d.new_date";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX repo_download_stats_monthly_id ON repo_download_stats_monthly USING btree (id)";

        conn.setAutoCommit(false);

        // String sql = "SELECT  orgid AS id, max(viewcount) AS number_of_views, date INTO datasource_stats FROM (select entity_id, ooid.id AS orgid, count(entity_id) viewcount, timestamp::date as date FROM piwiklog, datasource_oids ooid WHERE entity_id=ooid.orid AND source_item_type='datasource' GROUP BY entity_id, ooid.id, date) AS temp GROUP BY orgid, date ORDER BY orgid, date ASC, max(viewcount) DESC;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX datasource_stats_id ON datasource_stats USING btree (id)";

        stmt.executeUpdate(sql);

        // sql = "SELECT id, sum(number_of_views), extract('year' from date) ||'/'|| LPAD(CAST(extract('month' from date) AS VARCHAR), 2, '0') AS month INTO datasource_stats_monthly_clean FROM datasource_stats GROUP BY id, month;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX datasource_stats_monthly_clean_id ON datasource_stats_monthly_clean USING btree (id)";

        //sql="CREATE OR REPLACE view datasource_stats_monthly AS select d.id, d.new_date as month, case when rdm.sum is null then 0 else rdm.sum end from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, " + diffMonth +", 1) AS offs, datasource_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum from datasource_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id order by d.id, d.new_date";

        //sql = "select d.id, d.new_date AS month, case when rdm.sum is null then 0 else rdm.sum end INTO datasource_stats_monthly from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, " + diffMonth + ", 1) AS offs, datasource_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum from datasource_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id order by d.id, d.new_date";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX datasource_stats_monthly_id ON datasource_stats_monthly USING btree (id)";

        conn.setAutoCommit(false);

        // String sql = "SELECT orgid AS id, max(viewcount) AS number_of_views, date INTO result_stats FROM (SELECT entity_id, ooid.id AS orgid, count(entity_id) viewcount, timestamp::date as date FROM piwiklog, result_oids ooid WHERE entity_id=ooid.orid AND source_item_type='oaItem' GROUP BY entity_id, ooid.id, date) AS temp GROUP BY orgid, date ORDER BY orgid, date ASC, max(viewcount) DESC;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX result_stats_id ON result_stats USING btree (id)";

        conn.setAutoCommit(false);

        // String sql = "SELECT orgid AS id, max(viewcount) AS number_of_views, date INTO organization_stats FROM (SELECT entity_id, ooid.id AS orgid, count(entity_id) viewcount, timestamp::date AS date FROM piwiklog, organization_oids ooid WHERE entity_id=ooid.orid AND source_item_type='organization' GROUP BY entity_id, ooid.id, date) AS temp GROUP BY orgid, date ORDER BY orgid, date ASC, max(viewcount) DESC;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX organization_stats_id ON organization_stats USING btree (id)";

        stmt.executeUpdate(sql);

        // sql = "SELECT id, sum(number_of_views), extract('year' from date) ||'/'|| LPAD(CAST(extract('month' from date) AS VARCHAR), 2, '0') AS month INTO organization_stats_monthly_clean FROM organization_stats group by id, month;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX organization_stats_monthly_clean_id ON organization_stats_monthly_clean USING btree (id)";

        //sql="CREATE OR REPLACE view organization_stats_monthly AS select d.id, d.new_date as month, case when rdm.sum is null then 0 else rdm.sum end from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, " + diffMonth +", 1) AS offs, organization_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum from organization_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id order by d.id, d.new_date";

        // sql = "select d.id, d.new_date as month, case when rdm.sum is null then 0 else rdm.sum end INTO organization_stats_monthly from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY-MM-DD') AS new_date from generate_series(0, " + diffMonth + ", 1) AS offs, organization_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum from organization_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id order by d.id, d.new_date";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX organization_stats_monthly_id ON organization_stats_monthly USING btree (id)";

        conn.setAutoCommit(false);

        // String sql = "SELECT orgid AS id, max(viewcount) AS number_of_views, date INTO project_stats FROM (SELECT entity_id, ooid.id AS orgid, count(entity_id) viewcount, timestamp::date AS date FROM piwiklog, project_oids ooid WHERE entity_id=ooid.orid AND source_item_type='project' GROUP BY entity_id, ooid.id, date) AS temp GROUP BY orgid, date ORDER BY orgid, date ASC, max(viewcount) DESC;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX project_stats_id ON project_stats USING btree (id)";

        stmt.executeUpdate(sql);

        // sql = "SELECT id, sum(number_of_views), extract('year' from date) ||'/'|| LPAD(CAST(extract('month' from date) AS VARCHAR), 2, '0') AS month INTO project_stats_monthly_clean FROM project_stats group by id, month;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX project_stats_monthly_clean_id ON project_stats_monthly_clean USING btree (id)";

        // sql="CREATE OR REPLACE view project_stats_monthly AS select d.id, d.new_date as month, case when rdm.sum is null then 0 else rdm.sum end from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY/MM') AS new_date from generate_series(0, " + diffMonth +", 1) AS offs, project_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum from project_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id order by d.id, d.new_date";

        // sql = "select d.id, d.new_date as month, case when rdm.sum is null then 0 else rdm.sum end INTO project_stats_monthly from (select distinct rdsm.id, to_char(date_trunc('month', ('2016-01-01'::date + interval '1 month'*offs)), 'YYYY/MM') AS new_date from generate_series(0, " + diffMonth + ", 1) AS offs, project_stats_monthly_clean rdsm) d LEFT JOIN (select id, month, sum from project_stats_monthly_clean) rdm ON d.new_date=rdm.month and d.id=rdm.id order by d.id, d.new_date";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX project_stats_monthly_id ON project_stats_monthly USING btree (id)";