Project

General

Profile

« Previous | Next » 

Revision 58680

Added by Antonis Lempesis over 4 years ago

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