import java.sql.*;
import java.util.*;
import java.io.*;
import jet.universe.engine.*;
import jet.datasource.*;

public class HierarchicalDataset implements JRHierarchicalDataset
{
	//used to connect database
	Connection connection;
	//used to excecute query
	Statement statement1;	
	Statement statement2;	
	Statement statement3;
	//current resultset
	ResultSet resultset = null;	
	//resultset for "EMPLOYEE"
	ResultSet emprs = null;	
	//resultset for "ORDERS"
	ResultSet orderrs = null;	
	//resultset for "ORDERSDETAIL"
	ResultSet detailrs = null;	
	//string of column names in the order specified by UFakeHDSTable 
	String employeeSQL;
	String orderSQL;
	String orderdetailSQL;
	//index of the column which is used to join other table
	int employeeKeyIndex = 0;
	int orderKeyIndex = 0;	
	//indicate whether the last leaf read had the value of SQL NULL 
	boolean isNull = false;
	int lastLeafIdx = -1;
	//its value is user's parameter, indicating maximal employee id 
	int maxID;
	//indicate whether corresponding ResultSet(orderrs and detailrs) has more rows
	boolean isOrderEnd = false;
	boolean isDetailEnd = false;

	public HierarchicalDataset(String param) throws JRUserDataSourceException
	{				
		//connect database
		try{
        		Class.forName("org.hsqldb.jdbcDriver");                
			connection = DriverManager.getConnection("jdbc:hsqldb:C:\\JReport\\Demo\\db\\DemoDB", "sa", "");            			
			statement1 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);			
			statement2 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);			
			statement3 = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);			
			maxID = Integer.parseInt(param);			
        	}catch (ClassNotFoundException ex){
        		System.err.println("Cannot find the database driver classes.");
        		System.err.println(ex);
        	}catch (SQLException ex) {
			System.err.println("Cannot connect to this database.");
			System.err.println(ex);
        	}catch (NumberFormatException ex) {
			throw new JRUserDataSourceException("Parameter is needed. ");
        	}								
	}
	
	//set used tables and columns
	public void setUsedFields(UFakeHDSTable table)
	{				
		//set string of used column for root table		
		employeeSQL = getDBSQL(table);
		Vector childeren = table.getTabs();
		if(childeren.size() > 0)
		{
			UFakeHDSTable subtable = (UFakeHDSTable)childeren.elementAt(0);		
			//set string of used columns for "ORDERS" table
			orderSQL = getDBSQL(subtable);					
			childeren = subtable.getTabs();
			//set string of used columns for "ORDERSDETAIL" table
			if(childeren.size() > 0)
				orderdetailSQL = getDBSQL((UFakeHDSTable)childeren.elementAt(0));			
		}

		try{       
			//get root table's resultset
			if(!employeeSQL.trim().equals(""))
			{
				emprs = statement1.executeQuery("select " + employeeSQL + " from EMPLOYEE where EMPLOYEEID <=" + maxID);								
			}else if(!orderSQL.trim().equals("")){
				orderrs = statement2.executeQuery("select " + orderSQL + " from ORDERS where EMPLOYEEID <=" + maxID);								
			}else if(!orderdetailSQL.trim().equals("")){								
				detailrs = statement3.executeQuery("select " + orderdetailSQL + " from (ORDERS_DETAIL INNER JOIN Orders ON ORDERS_DETAIL.ORDERID = ORDERS.ORDERID) where ORDERS.EMPLOYEEID <=" + maxID);												
			}
        }catch (SQLException ex) {
            System.err.println("Cannot connect to this database.");
            System.err.println(ex);
        }				
	}

	//return used columns' string of the table
	private String getDBSQL(UFakeHDSTable table)
	{	
		Vector leafs = table.getCols();		
		//no column is used
		if(leafs.size() <= 0)
			return "";
		StringBuffer buf = new StringBuffer();
		
		for(int i = 0; i < leafs.size(); i++)
		{
			UFakeHDSColumn hdsColumn = (UFakeHDSColumn)leafs.elementAt(i);
			String columnName = hdsColumn.getColumnName();			
			buf.append(columnName);
			
			if(i < (leafs.size() - 1 ))
				buf.append(",");
			//set index of column which is used to join other table
			if(table.getTableName().equals("Employee"))
			{
				if(columnName.equals("EMPLOYEEID"))
					employeeKeyIndex = i + 1;
			}else if(table.getTableName().equals("Orders"))
			{
				if(columnName.equals("ORDERID"))
					orderKeyIndex = i + 1;
			}
		}			
		return buf.toString();
	}

	/**
	 * Retrieves the number, types and properties of this object's leaves
	 */
	public JRHierarchicalDatasetMetaData getMetaData()
	{		
		return new HierarchicalDatasetMetaData();	
	}

	/**
	 * Moves the cursor down one row from its current position in the current node.  
	 * return true if the new current row is valid; false if there are no more rows in the current node.
 	 */
	public boolean next(String branchName)
	{										
		String sql;		
		try{
			if(branchName.equals("Employee"))
			{									
				if(emprs != null)
				{	
					if(emprs.next())
					{
						//set current resultset
						resultset = emprs;																
						int empoyeeid = emprs.getInt(employeeKeyIndex);															
						emprs.absolute(emprs.getRow()) ;
						if((orderSQL != null)&&!orderSQL.trim().equals(""))
						{
							//set resultset for branch table
							sql = "select " + orderSQL +  " from ORDERS where EMPLOYEEID =" + empoyeeid + ";";
							orderrs = statement2.executeQuery(sql);						
						}					
					}else
						return false;
				}else if((orderrs != null)&&(!isOrderEnd)||(detailrs != null)&&(!isDetailEnd))
					return true;				
				else 
					return false;
			}else if(branchName.equals("Employee.Orders")){								
				if(orderrs != null)
				{										
					if(orderrs.next())
					{
						//set current resultset					
						resultset = orderrs	;				
						int orderid = orderrs.getInt(orderKeyIndex);												
						orderrs.absolute(orderrs.getRow()) ;					
						if((orderdetailSQL != null)&&!orderdetailSQL.trim().equals(""))
						{
							//set resultset for branch table						
							sql = "select " + orderdetailSQL + " from ORDERS_DETAIL where ORDERID =" + orderid + ";";											
							detailrs = statement3.executeQuery(sql);												
						}										
					}else{
						isOrderEnd = true;
						return false;
					}
				}else if((detailrs != null)&&(!isDetailEnd))
					return true;				
				else 
					return false;
			}else{				
				if((detailrs != null)&&detailrs.next())
				{
					//set current resultset
					resultset = detailrs;										
				}else {
					isDetailEnd = true;
					return false;
				}
			}						
		}catch(SQLException e){
			e.printStackTrace();
		}				
		return true;

	}

	/**
	 * Returns the value of the designated leaf in the current branch node row  as an int in the Java programming language. 
	 */

	public int getInt(int leafIndex)
	{					
		try{			
			lastLeafIdx = leafIndex;			
			return resultset.getInt(leafIndex);			
		}catch(SQLException e){
			e.printStackTrace();
			return 0;
		}
	}

	/**
	 * Gets the value of the designated leaf in the current branch node row as a java.math.BigDecimal with full precision.
	 */
	public java.math.BigDecimal getBigDecimal(int leafIndex)
	{		
		try{			
			lastLeafIdx = leafIndex;
			return resultset.getBigDecimal(leafIndex);
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Returns the value of the designated leaf in the current branch node row  as a java.sql.Timestamp object 
	 * in the Java programming language. 
	 */
	public java.sql.Timestamp getTimestamp(int leafIndex)
	{ 					
		try{		
			lastLeafIdx = leafIndex;			
			return resultset.getTimestamp(leafIndex);
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Returns the value of the designated leaf in the current branch node row  as a String in the Java programming language. 
	 */
	public String getString(int leafIndex)
	{		
		try{			
			lastLeafIdx = leafIndex;
			return resultset.getString(leafIndex);
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public boolean getBoolean(int leafIndex)
	{				
		return false;
	}

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public byte getByte(int leafIndex)
	{		
		return 0;
	}

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public short getShort(int leafIndex)
	{
		try{			
			lastLeafIdx = leafIndex;
			return resultset.getShort(leafIndex);
		}catch(SQLException e){
			e.printStackTrace();
			return 0;
		}
	}	

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public long getLong(int leafIndex)
	{
		return 0;
	}

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public float getFloat(int leafIndex)
	{
		return 0;
	}

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public double getDouble(int leafIndex)
	{
		return 0;
	}	

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public java.sql.Date getDate(int leafIndex)
	{		
		return null;
	}
	
	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public java.sql.Time getTime(int leafIndex)
	{		
		return null;
	}		

	/**
 	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public java.sql.Array getArray(int leafIndex)
	{
		return null;
	}

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public byte[] getBytes(int number)
	{
		return null;
	}

	/**
	 * Because the selected table does not have the data in this type, leave the method empty.
	 */
	public InputStream getBinaryStream(int number)
	{		
		return null;
	}

	/**
	 * Reports whether the last leaf read had a value of SQL NULL
	 */
	public boolean wasNull()
	{			
		try{			
//			boolean ret =  (resultset.getObject(lastLeafIdx) == null);			
			boolean ret =  resultset.wasNull();
			return ret;
		}catch(SQLException e){
			e.printStackTrace();
			return true;
		}
	}	

	/**
	 * Releases this object's resource.
	 */
	public void close()
	{
		try{
			connection.close() ;
		}catch(SQLException e){
			e.printStackTrace();
		}
	}

}
