package com.cluster.RPClusterCapacity;

public class RPClusterCapacity {
   // Fields
   
   private Long clusterId;
   private Integer pollerMaxRP;
   private Integer pollerUsageRP;
   private Integer licenseMaxRP;
   private Integer licenseUsageRP;
            
   // Constructors

   /** full constructor */
   public RPClusterCapacity(Long clusterId, Integer pollerMaxRP, Integer pollerUsageRP,
         Integer licenseMaxRP, Integer licenseUsageRP) {
      this.clusterId = clusterId;
      this.pollerMaxRP = pollerMaxRP;
      this.pollerUsageRP = pollerUsageRP;
      this.licenseMaxRP = licenseMaxRP;
      this.licenseUsageRP = licenseUsageRP;
      }

   //Property accessors
   public Long getClusterId() {
      return clusterId;
   }

   public void setClusterId(Long clusterId) {
      this.clusterId = clusterId;
   }

   public Integer getPollerMaxRP() {
      return pollerMaxRP;
   }

   public void setPollerMaxRP(Integer pollerMaxRP) {
      this.pollerMaxRP = pollerMaxRP;
   }

   public Integer getPollerUsageRP() {
      return pollerUsageRP;
   }

   public void setPollerUsageRP(Integer pollerUsageRP) {
      this.pollerUsageRP = pollerUsageRP;
   }

   public Integer getLicenseMaxRP() {
      return licenseMaxRP;
   }

   public void setLicenseMaxRP(Integer licenseMaxRP) {
      this.licenseMaxRP = licenseMaxRP;
   }

   public Integer getLicenseUsageRP() {
      return licenseUsageRP;
   }

   public void setLicenseUsageRP(Integer licenseUsageRP) {
      this.licenseUsageRP = licenseUsageRP;
   }
   
}

public class System implements java.io.Serializable {

   // Fields

   private Long systemId;
   private String hostname;
   private String systemType;
   private Long clusterId;
   private Integer pollerMax;
   private Integer pollerUsage;
   private Integer licenseMax;
   private Integer licenseUsage;
   private Long maxPollTime;

   // Constructors

   /** default constructor */
   public System() {
   }

   /** minimal constructor */
   public System(Long systemId) {
      this.systemId = systemId;
   }

   /** full constructor */
   public System(Long systemId, String hostname, String systemType,
         Long clusterId, Integer pollerMax, Integer pollerUsage,
         Integer licenseMax, Integer licenseUsage, Long maxPollTime) {
      this.systemId = systemId;
      this.hostname = hostname;
      this.systemType = systemType;
      this.clusterId = clusterId;
      this.pollerMax = pollerMax;
      this.pollerUsage = pollerUsage;
      this.licenseMax = licenseMax;
      this.licenseUsage = licenseUsage;
      this.maxPollTime = maxPollTime;
   }

   // Property accessors

   public Long getSystemId() {
      return this.systemId;
   }

   public void setSystemId(Long systemId) {
      this.systemId = systemId;
   }

   public String getHostname() {
      return this.hostname;
   }

   public void setHostname(String hostname) {
      this.hostname = hostname;
   }

   public String getSystemType() {
      return this.systemType;
   }

   public void setSystemType(String systemType) {
      this.systemType = systemType;
   }

   public Long getClusterId() {
      return this.clusterId;
   }

   public void setClusterId(Long clusterId) {
      this.clusterId = clusterId;
   }

   public Integer getPollerMax() {
      return this.pollerMax;
   }

   public void setPollerMax(Integer pollerMax) {
      this.pollerMax = pollerMax;
   }

   public Integer getPollerUsage() {
      return this.pollerUsage;
   }

   public void setPollerUsage(Integer pollerUsage) {
      this.pollerUsage = pollerUsage;
   }

   public Integer getLicenseMax() {
      return this.licenseMax;
   }

   public void setLicenseMax(Integer licenseMax) {
      this.licenseMax = licenseMax;
   }

   public Integer getLicenseUsage() {
      return this.licenseUsage;
   }

   public void setLicenseUsage(Integer licenseUsage) {
      this.licenseUsage = licenseUsage;
   }

   public Long getMaxPollTime() {
      return this.maxPollTime;
   }

   public void setMaxPollTime(Long maxPollTime) {
      this.maxPollTime = maxPollTime;
   }

}

select   new com.cluster.RPClusterCapacity(s.clusterId, sum(s.pollerMax), sum(s.pollerUsage), sum(s.licenseMax), sum(s.licenseUsage))
from     System as s
where    s.systemType = 'RP' and s.licenseUsage < (s.licenseMax*0.9) and s.pollerUsage < (s.pollerMax*0.9)
and s.maxPollTime < 270
group by s.clusterId
order by s.clusterId