Project

General

Profile

« Previous | Next » 

Revision 58680

Modified queries to make them non postgres specific

View differences:

modules/dnet-openaire-stats-api/src/main/java/eu/dnetlib/statsapi/repositories/CommunityRepository.java
58 58
                return new Result("OK", "200", new ObjectMapper().readValue(redisResponse, Community.class));
59 59
            } else {
60 60
                connection = statsDB.getConnection();
61
                st = connection.prepareStatement("SELECT r.type, COUNT(DISTINCT r.id) AS total, COUNT(DISTINCT CASE WHEN r.bestlicense='Open Access' THEN r.id END) AS open_access, COUNT(DISTINCT CASE WHEN r.bestlicense='Embargo' THEN r.id END) AS embargo, COUNT(DISTINCT CASE WHEN r.bestlicense='Restricted' THEN r.id END) AS restricted, COUNT(DISTINCT CASE WHEN r.bestlicense='Closed Access' THEN r.id END) AS closed_access FROM public.result_concepts rc, public.result r WHERE rc.id=r.id AND (rc.concept=? OR rc.concept LIKE ? || '::%') GROUP BY r.type;");
61
                st = connection.prepareStatement("SELECT r.type, COUNT(DISTINCT r.id) AS total, sum(CASE WHEN r.bestlicence='Open Access' THEN 1 else 0 END) AS open_access, sum(CASE WHEN r.bestlicence='Embargo' THEN 1 else 0 END) AS embargo, sum(CASE WHEN r.bestlicence='Restricted' THEN 1 else 0 END) AS restricted, sum(CASE WHEN r.bestlicence='Closed Access' THEN 1 else 0 END) AS closed_access FROM result_concepts rc, result r WHERE rc.id=r.id AND (rc.concept=? OR rc.concept LIKE concat(? , '::%')) GROUP BY r.type;");
62 62
                st.setString(1, community_id);
63 63
                st.setString(2, community_id);
64 64

  
......
87 87
                rs.close();
88 88
                st.close();
89 89

  
90
                st = connection.prepareStatement("SELECT r.type, COUNT(DISTINCT pr.id) FROM public.result r, public.result_concepts rc, public.project_results pr WHERE r.id=rc.id AND r.id=pr.result AND (rc.concept=? OR rc.concept LIKE ? || '::%') GROUP BY r.type;");
90
                st = connection.prepareStatement("SELECT r.type, COUNT(DISTINCT pr.id) FROM result r, result_concepts rc, project_results pr WHERE r.id=rc.id AND r.id=pr.result AND (rc.concept=? OR rc.concept LIKE concat(?, '::%')) GROUP BY r.type;");
91 91
                st.setString(1, community_id);
92 92
                st.setString(2, community_id);
93 93

  
......
113 113
                rs.close();
114 114
                st.close();
115 115

  
116
                st = connection.prepareStatement("SELECT COUNT(DISTINCT c.name) FROM public.result_concepts rc, public.concept c, public.category cat WHERE c.id=rc.concept AND cat.id=c.category AND cat.id=? || '::virtual';");
116
                st = connection.prepareStatement("SELECT COUNT(DISTINCT c.name) FROM result_concepts rc, concept c, category cat WHERE c.id=rc.concept AND cat.id=c.category AND cat.id=concat(?, '::virtual');");
117 117
                st.setString(1, community_id);
118 118

  
119 119
                int virtual_organizations = 0;
......
129 129
                return new Result("OK", "200", community);
130 130
            }
131 131
        } catch (Exception e) {
132
            log.error(e.getMessage());
132
            log.error(e);
133 133
        } finally {
134 134
            DbUtils.closeQuietly(rs);
135 135
            DbUtils.closeQuietly(st);

Also available in: Unified diff