gt:  	    catch (SQLException statementCloseEx) {
context: package com.idega.block.reports.business;
  
  
  
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.ResultSetMetaData;
  import java.sql.SQLException;
  import java.sql.Statement;
  import java.util.Collection;
  import java.util.List;
  import java.util.StringTokenizer;
  import java.util.TreeMap;
  import java.util.Vector;
  
  import com.idega.block.reports.data.ReportItem;
  import com.idega.util.database.ConnectionBroker;
  
  
  
  /**
  
   * Title:
  
   * Description:
  
   * Copyright:    Copyright (c) 2001
  
   * Company:      idega multimedia
  
   * @author       <a href="mailto:aron@idega.is">aron@idega.is</a>
  
   * @version 1.0
  
   */
  
  
  
  public class ReportMaker {
  
  
  
    public ReportMaker(){
  
  
  
    }
  
    public List makeReport(String sql){
  
      List v = this.searchInDatabase(sql);
  
      return v;
  
    }
  
  
  
    public List makeReport(Vector conds){
  
      String sql = worker(conds);
  
      List v = this.searchInDatabase(sql);
  
      return v;
  
    }
  
  
  
    public List makeReport(List conds,ReportCondition RCx){
  
      conds.add(RCx);
  
      String sql = worker(conds);
  
      List v = this.searchInDatabase(sql);
  
      return v;
  
    }
  
  
  
    public String makeSQL(Vector conds){
  
      return worker(conds);
  
    }
  
  
  
    private String worker(List conds){
  
      ReportCondition cond;
  
      Vector vSelect = new Vector();
  
      Vector vTables = new Vector();
  
      Vector vJoin = new Vector();
  
      Vector vWhere = new Vector();
  
      Vector vOrder = new Vector();
  
  		Vector vGroupBy = new Vector();
  
  		Vector vHaving = new Vector();
  
      TreeMap orderMap = new TreeMap();
  
  
  
      ReportItem item;
  
      int length = conds.size();
  
      String field;
  
      for (int i = 0; i < length; i++) {
  
        cond = (ReportCondition) conds.get(i);
  
        field = cond.getField();
  
        item = cond.getItem();
  
  
  
        String c = cond.getCondition();
  
        boolean hascondition = c != null && c.length() > 0 ;
  
  
  
        if(cond.isSelect() || hascondition){
  
          if(cond.isSelect()){
  
            System.err.println("item "+item.getName()+" is select");
  
            if(field!= null){
  
              if(!vSelect.contains(field)){
  
                vSelect.add(field);
  
              }
  
            }
  
            if(!cond.isFunction() && !vGroupBy.contains(field)){
  
              vGroupBy.add(field);
  
            }
  
          }
  
  
  
          if(item.getMainTable()!=  null){
  
            if(!vTables.contains(item.getMainTable())){
  
              System.err.println("adding main table "+ item.getMainTable());
  
              vTables.add(item.getMainTable());
  
            }
  
          }
  
  
  
          String[] sa = item.getJoinTable();
  
          if(sa != null){
  
            for (int j = 0; j < sa.length; j++) {
  
              if(!vTables.contains(sa[j])){
  
                System.err.println("adding join table "+sa[j]);
  
                vTables.add(sa[j]);
  
              }
  
            }
  
          }
  
          String sJoin = item.getJoin();
  
          if(sJoin.length() > 1){
  
            StringTokenizer ST = new StringTokenizer(sJoin,",;");
  
            while(ST.hasMoreTokens()){
  
              String join = ST.nextToken();
  
              if(!vJoin.contains(join)) {
  							vJoin.add(join);
  						}
  
            }
  
          }
  
  
  
          if(hascondition ){
  
            if(!cond.isFunction() && !vWhere.contains(c)) {
  						vWhere.add(c);
  					}
  					else if(!vHaving.contains(c)) {
  						vHaving.add(c);
  					}
  
          }
  
  
  
          Integer order = cond.getOrder();
  
          if(order != null){
  
            orderMap.put(order,field);
  
          }
  
        }
  
      }
  
      if(orderMap.size() > 0){
  
        Collection C = orderMap.values();
  
        vOrder.addAll(C);
  
      }
  
      // check Group by
  
      if(vGroupBy.size() == length){
  
        vGroupBy.clear();
  
      }
  
  
  
      String sql = makeSQL(vSelect,vTables,vJoin,vWhere,vOrder,vGroupBy,vHaving);
  
      return sql;
  
    }
  
  
  
    private String makeSQL(List Select,List From,List Join,List Where,List Order,List GroupBy,List Having){
  
      ///////////////////////////
  
      //  Select part
  
      ///////////////////////////
  
      StringBuffer sql = new StringBuffer("SELECT  ");
  
      int len = Select.size();
  
      for(int i = 0; i < len ; i++){
  
        sql.append(Select.get(i));
  
        if(i < len-1) {
  				sql.append(",");
  			}
  
      }
  
      sql.append(" ");
  
  
  
      ///////////////////////
  
      //  Table part
  
      //////////////////////
  
      int flen = From.size();
  
      if(flen > 0){
  
        sql.append(" FROM ");
  
        for(int i = 0; i < flen ; i++){
  
          sql.append(From.get(i));
  
          if(i < flen-1) {
  					sql.append(",");
  				}
  
        }
  
        sql.append(" ");
  
      }
  
  
  
      /////////////////////
  
      //  Join Part
  
      ////////////////////
  
      boolean ifwhere = false;
  
      len = Join.size();
  
      //System.err.println("join length "+len );
  
      // use the join if  we have more than two tables
  
      //
  
      if(len > 0 && flen > 1){
  
        sql.append(" WHERE ");
  
        ifwhere = true;
  
        for(int i = 0; i < len ; i++){
  
          sql.append(Join.get(i));
  
          if(i < len-1) {
  					sql.append(" AND ");
  				}
  
        }
  
      }
  
      sql.append(" ");
  
      ////////////////////////
  
      //  Where part
  
      ///////////////////////
  
      len = Where.size();
  
      if(len > 0 ){
  
        if(ifwhere) {
  				sql.append(" AND ");
  			}
  			else {
  				sql.append(" WHERE ");
  			}
  
        for(int i = 0; i < len ; i++){
  
          sql.append(Where.get(i));
  
          if( i < len-1) {
  					sql.append(" AND ");
  				}
  
        }
  
      }
  
  		sql.append(" ");
  
      ////////////////////////
  
      //  Group by part
  
      ///////////////////////
  
      if(GroupBy != null){
  
        len = GroupBy.size();
  
        if(len > 0 ){
  
          sql.append( "GROUP BY ");
  
          for(int i = 0; i < len ; i++){
  
            sql.append(GroupBy.get(i));
  
            if(i < len-1) {
  						sql.append(",");
  					}
  
          }
  
        }
  
      }
  
  		sql.append(" ");
  
      ////////////////////////
  
      //  Having part
  
      ///////////////////////
  
      if(Having != null){
  
        len = Having.size();
  
        if(len > 0 ){
  
          sql.append( " HAVING ");
  
          for(int i = 0; i < len ; i++){
  
            sql.append(Having.get(i));
  
            if(i < len-1) {
  						sql.append(" AND ");
  					}
  
          }
  
        }
  
      }
  
      sql.append(" ");
  
      ////////////////////////
  
      //  Order by part
  
      ///////////////////////
  
      if(Order != null){
  
        len = Order.size();
  
        if(len > 0 ){
  
          sql.append( "ORDER BY ");
  
          for(int i = 0; i < len ; i++){
  
            sql.append(Order.get(i));
  
            if(i < len-1) {
  						sql.append(",");
  					}
  
          }
  
        }
  
      }
  
      System.err.println(sql.toString());
  
      return sql.toString();
  
    }
  
  
  
    private Vector searchInDatabase(String SQL){
  
      Vector v = new Vector();
  
      Connection Conn = null;
      Statement stmt = null;
      ResultSet RS = null;
      
  
      try{
  
        Conn = ConnectionBroker.getConnection();
  
        stmt = Conn.createStatement();
  
        RS = stmt.executeQuery(SQL);
  
        ResultSetMetaData MD = RS.getMetaData();
  
        int count = MD.getColumnCount();
  
        String temp;
  
        ReportContent RC;
  
        String[] s;
  
        while(RS.next()){
  
          s = new String[count];
  
          for(int i = 1; i <= count; i++){
  
            temp = RS.getString(i);
  
            s[i-1] = temp!=null?temp:"";
  
          }
  
          RC = new ReportContent(s);
  
          v.add(RC);
  
        }
      }
  
      catch(SQLException e){
  
        e.printStackTrace();
  
      }
  
      finally {
      	// do not hide an existing exception
      	try { 
      		if (RS != null) {
      			RS.close();
  	      	}
      	}
  	    catch (SQLException resultCloseEx) {
  	    	System.err.println("[ReportMaker] result set could not be closed");
  	     	resultCloseEx.printStackTrace(System.err);
  	    }
  	    // do not hide an existing exception
  	    try {
  	    	if (stmt != null)  {
  	    		stmt.close();
  	    	    com.idega.util.database.ConnectionBroker.freeConnection(Conn);
  	    	}
  	    }
  