Project

General

Profile

« Previous | Next » 

Revision 40137

Added by Antonis Lempesis over 8 years ago

minor fixes is sql queries

View differences:

modules/uoa-goldoa-service/trunk/src/main/java/eu/dnetlib/goldoa/service/stats/StatsManagerImpl.java
33 33 
	public static final String NUMBERS_PROJECTS = "select count(distinct project) from request r where r.status&" + Request.RequestStatus.APPROVED.getCode() + "= " + Request.RequestStatus.APPROVED.getCode() + " and (r.status&" + Request.RequestStatus.ACCOUNTING_DENIED.getCode() + "=0)";
34 34 
	public static final String NUMBERS_TOTAL_AMOUNT = "select sum(apc_paid) from request r where r.status&" + Request.RequestStatus.APPROVED.getCode() + "= " + Request.RequestStatus.APPROVED.getCode() + " and (r.status&" + Request.RequestStatus.ACCOUNTING_DENIED.getCode() + "=0)";
35 35 

  		




  36
  
  
    
	public static final String SERIES_COUNTRY = "select c.name as category, count(r.id) from request r join organisation o on r.organization=o.id join country c on c.id=o.country %WHERE% group by c.name";


  		




  
  36
  
    
	public static final String SERIES_COUNTRY = "select c.name as category, count(r.id) as count from request r join organisation o on r.organization=o.id join country c on c.id=o.country %WHERE% group by c.name";


  		




  37
  37
  
    
	public static final String SERIES_ORGANIZATION = "select case when o.shortname is not null then o.shortname else o.name end as category, count(r.id) from request r join organisation o on r.organization=o.id %WHERE% group by o.shortname, o.name";


  		




  38
  38
  
    
	public static final String SERIES_PUBLISHER = "select p.name as category, count(r.id) from (select r.id, r.status, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id %WHERE% group by p.name";


  		




  39
  39
  
    

  		




  40
  
  
    
	public static final String BROWSE_SCIENTIFIC_AREA = "select p.id as id, p.scientificarea as category, count(r.id) as count from request r join project p on r.project=p.id group by p.scientificarea, p.id";


  		




  41
  
  
    
	public static final String BROWSE_COUNTRY = "select c.id as id, c.name as category, count(r.id) as count from request r join organisation o on r.organization=o.id join country c on c.id=o.country group by c.name, c.id";


  		




  42
  
  
    
	public static final String BROWSE_ORGANIZATION = "select o.id as id, o.shortname as category, count(r.id) as count from request r join organisation o on o.id=r.organization group by o.shortname, o.id";


  		




  43
  
  
    
	public static final String BROWSE_PUBLISHER = "select p.id as id, p.name as category, count(r.id) as count from (select r.id, r.status, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id group by p.name, p.id";


  		




  
  40
  
    
	public static final String BROWSE_SCIENTIFIC_AREA = "select distinct p.scientificarea as id, p.scientificarea as category, count(r.id) as count from request r join project p on r.project=p.id group by p.scientificarea order by category";


  		




  
  41
  
    
	public static final String BROWSE_COUNTRY = "select c.id as id, c.name as category, count(r.id) as count from request r join organisation o on r.organization=o.id join country c on c.id=o.country group by c.name, c.id order by category";


  		




  
  42
  
    
	public static final String BROWSE_ORGANIZATION = "select o.id as id, case when o.shortname is not null then o.shortname else o.name end  as category, count(r.id) as count from request r join organisation o on o.id=r.organization group by o.shortname, o.name, o.id order by category";


  		




  
  43
  
    
	public static final String BROWSE_PUBLISHER = "select p.id as id, p.name as category, count(r.id) as count from (select r.id, r.status, j.publisher, 'j' from request r join journal j on r.journal=j.id where j.publisher is not null union select r.id, r.status, p.id, 'p' from request r join publisher p on r.publisher=p.id) as r join publisher p on r.publisher=p.id group by p.name, p.id order by category";


  		




  44
  44
  
    

  		




  45
  45
  
    
	@Autowired


  		




  46
  46
  
    
	private DataSource dataSource;


  		












Also available in:  Unified diff