D-Net

        sql = "CREATE TABLE IF NOT EXISTS views_stats_tmp AS 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 FROM result_views_monthly_tmp p, public.datasource d, public.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);

        stmt.executeUpdate(sql);

        stmt.close();

        ConnectDB.getConnection().commit();

        ConnectDB.getConnection().close();

        Statement stmt = ConnectDB.getConnection().createStatement();

        ConnectDB.getConnection().setAutoCommit(false);

        String sql = "CREATE OR REPLACE VIEW result_downloads_monthly_tmp as select entity_id AS id, COUNT(entity_id) as downloads, SUM(CASE WHEN referrer_name LIKE '%openaire%' THEN 1 ELSE 0 END) AS openaire_referrer, extract('year' from timestamp::date) ||'/'|| LPAD(CAST(extract('month' from timestamp::date) AS VARCHAR), 2, '0') AS month, source FROM piwiklogtmp where action='download' and (source_item_type='oaItem' or source_item_type='repItem') group by id, month, source order by source, id, month;";

        stmt.executeUpdate(sql);

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

        sql = "CREATE TABLE IF NOT EXISTS downloads_stats_tmp AS 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 FROM result_downloads_monthly_tmp p, public.datasource d, public.result_oids ro where 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_tmp;";

        stmt.executeUpdate(sql);

        sql = "CREATE INDEX IF NOT EXISTS downloads_stats_date ON downloads_stats USING btree(date);";

        stmt.executeUpdate(sql);

        sql = "CREATE TABLE IF NOT EXISTS usage_stats AS 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 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 IF NOT EXISTS usage_stats_date ON usage_stats USING btree(date);";

        stmt.executeUpdate(sql);

        stmt.close();

        ConnectDB.getConnection().commit();

        ConnectDB.getConnection().close();

        Statement stmt = con.createStatement();

        con.setAutoCommit(false);

        stmt.executeUpdate(sql);

        stmt.close();

//        con.commit();

//        con.commit();

        stmt = con.createStatement();

        stmt.executeUpdate(sql);

        stmt.close();

//        con.commit();

        stmt = con.createStatement();

        stmt.executeUpdate(sql);

        stmt.close();

//        con.commit();

    private void updateProdTables() throws SQLException {

        Statement stmt = ConnectDB.getConnection().createStatement();

        ConnectDB.getConnection().setAutoCommit(false);

        sql = "insert into views_stats select * from views_stats_tmp;";

        stmt.executeUpdate(sql);

        sql = "insert into pageviews_stats select * from pageviews_stats_tmp;";

        stmt.executeUpdate(sql);

        sql = "DROP TABLE IF EXISTS views_stats_tmp;";

        stmt.executeUpdate(sql);

        sql = "DROP TABLE IF EXISTS downloads_stats_tmp;";

        stmt.executeUpdate(sql);

        stmt.executeUpdate(sql);

        stmt.close();

        ConnectDB.getConnection().commit();

        ConnectDB.getConnection().close();