package data;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;

import org.apache.scaffold.model.ModelException;
import org.apache.scaffold.model.ModelResourceException;

import com.sumware.sql.DataAccess;
import com.sumware.sql.RowList;
import com.sumware.sql.SQLParam;

/**
 * All data access of Bean value objects are done through this
 * DataAccessObject.  
 * <p>
 * @author Nathan Anderson
 * @version $Revision: 1.0 $ $Date: 2001/12/06 $
 */

public class Model
{
	
	public static final String DATASOURCE = null;
	
	/**
	 * Select all of the value objects from the database
	 * <p>
     * @param resource The name of the datasource to update to
     * prepared statement.
     * @returns ArrayList ArrayList of Bean objects 
     * @exception ModelException if SQL error occurs
	 */
	public static Collection select() 
			throws ModelException {
		try {
			String sql = "SELECT bank_id, entity_id FROM bank"; 
			return getCollection(DataAccess.executeQuery(DATASOURCE,sql,null));
		} catch (SQLException e) { throw new ModelResourceException(e); }
	}
	
	/**
	 * Select all of the value objects from the database that match
	 * the non null values of the value object.
	 * <p>
     * @param resource The name of the datasource to update to
     * @param bean Value object to evaluate against the 
     * prepared statement.
     * @returns ArrayList ArrayList of Bean objects 
     * @exception ModelException if SQL error occurs
	 */
	public static Collection selectWhere(Bean bean) 
			throws ModelException {		
		try {
			String sql = "SELECT bank_id, entity_id FROM bank WHERE"; 
			if (bean.getEntityID()!=null) sql += " entity_id=? AND";
			if (bean.getBankID()!=null) sql += " bank_id=? AND";
			if (sql.endsWith("AND")) {
				sql=sql.substring(0, sql.length()-3);
				return getCollection(DataAccess.executeQuery(
					DATASOURCE,sql,getParams(bean)));
			} else return select();
		} catch (SQLException e) { throw new ModelResourceException(e); }
	}	

	/**
	 * Insert the value object into the database
     * @param resource The name of the datasource to update to
     * @param bean Bean value object to evaluate against the 
     * prepared statement.
     * @returns int The number of rows affected
     * @exception ModelException if SQL error occurs
	 */
	public static int insert(Bean bean) 
			throws ModelException {
		try {
			bean.setBankID(null);
			String sql = "INSERT INTO bank (entity_id) VALUES (?)"; 
			return DataAccess.executeUpdate(
				DATASOURCE,sql,getAllParams(bean, false));
		} catch (SQLException e) { throw new ModelResourceException(e); }
	}
	
	/**
	 * Update the value object into the database
     * @param resource The name of the datasource to update to
     * @param bean Bean value object to evaluate against the 
     * prepared statement.
     * @returns int The number of rows affected
     * @exception ModelException if SQL error occurs
	 */
	public static int update(Bean bean) 
			throws ModelException {
		try {
			String sql = "UPDATE bank SET entity_id=? WHERE bank_id=?"; 
			return DataAccess.executeUpdate(
				DATASOURCE,sql,getAllParams(bean));
		} catch (SQLException e) { throw new ModelResourceException(e); }
	}
	
	/** 
	 * Store a value object into the database.
	 * <p>
	 * If the recored does not exists (value object key is null),
	 * then insert, else update.
     * @param resource The name of the datasource to update to
     * @param bean Bean value object to pass to insert or update.
     * @returns int The number of rows affected
     * @exception ModelException if SQL error occurs
	 */
	public static int store(Bean bean) 
			throws ModelException {
		if (bean.getBankID()==null) 
			return insert(bean);
		else 
			return update(bean);
	}
	
	/** 
	 * Store a collection of value objects into the database.
	 * <p>
	 * If a recored does not exists (value object key is null),
	 * then insert it, else update it.
     * @param resource The name of the datasource to update to
     * @param bankList ArrayList of value objects to pass to 
     * insert or update.
     * @returns int The number of rows affected
     * @exception ModelException if SQL error occurs
	 */
	public static int store(Collection beanList) 
			throws ModelException {
		int result = 0;
		Bean bean = null;
		for (Iterator i=beanList.iterator(); i.hasNext(); ) {
			bean = (Bean) i.next();
			if (bean.getBankID()==null) 
				result += insert(bean);
			else 
				result += update(bean);
		}
		return result;
	}

	/**
	 * Delete the value object from the database
     * @param resource The name of the datasource to update to
     * @param bean Bean value object to evaluate against the 
     * prepared statement.
     * @returns int The number of rows affected
     * @exception ModelException if SQL error occurs
	 */
	public static int delete(Bean bean) 
			throws ModelException {
		try {
			ArrayList params = new ArrayList();
			String sql = "DELETE FROM bank where bank_id=?"; 
			params.add(new SQLParam(bean.getBankID(), bean.BANKID_TYPE));
			return DataAccess.executeUpdate(DATASOURCE,sql,params);
		} catch (SQLException e) { throw new ModelResourceException(e); }
	}

	/**
	 * Count the number of rows in the database
	 * @param resource The name of the datasource to update to
     * @param bean Bean value object to evaluate against the 
     * prepared statement.
     * @returns int The number of rows affected
     * @exception ModelException if SQL error occurs
	 */
	public static int count() throws ModelException {
		try {
			String sql = "SELECT count(*) FROM bank"; 
			return DataAccess.execute(DATASOURCE,sql,null);
		} catch (SQLException e) { throw new ModelResourceException(e); }
	}

	/**
	 * Count the number of rows in the database that match 
     * @param resource The name of the datasource to update to
     * @param bean Bean value object to evaluate against the 
     * prepared statement.
     * @returns int The number of rows affected
     * @exception ModelException if SQL error occurs
	 */
	public static int countWhere(Bean bean) 
			throws ModelException {
		try {
			String sql = "SELECT count(*) FROM bank WHERE"; 
			if (bean.getEntityID()!=null) sql += " entity_id=? AND";
			if (bean.getBankID()!=null) sql += " bank_id=? AND";
			if (sql.endsWith("AND")) {
				sql=sql.substring(0, sql.length()-3);
				return DataAccess.execute(DATASOURCE,sql,getParams(bean));
			} else return count();
		} catch (SQLException e) { throw new ModelResourceException(e); }
	}
		
		
		
	/** 
	 * Take a RowList and convert it into a collection of Bean objects
	 * @param rowList RowList to morph into a Collection
	 * @returns Collection ArrayList [to maintain order] of Bean objects
	 */	
	protected static Collection getCollection (RowList rowList) {
		ArrayList rows = new ArrayList();
		ArrayList elements = null;
		Bean bean = null;
		for (Iterator i=rowList.iterator(); i.hasNext(); ) {
			elements = (ArrayList) i.next();
			bean = new Bean();
			bean.setBankID((Integer) elements.get(0));
			bean.setEntityID((Integer) elements.get(1));
			rows.add(bean);
		}
		return (Collection) rows;
	}
	
	/**
	 * Convert the non-null values of a Bean value object into an
	 * ArrayList of SQLParams
     * @param bean Bean value object 
     * @returns ArrayList ArrayList of SQLParam
	 */
	protected static Collection getParams (Bean bean) {
		ArrayList params = new ArrayList();
		if (bean.getEntityID()!=null) 
			params.add(new SQLParam(bean.getEntityID(), bean.ENTITYID_TYPE));
		if (bean.getBankID()!=null) 
			params.add(new SQLParam(bean.getBankID(), bean.BANKID_TYPE));
		return (Collection) params;
	}
	
	/**
	 * Convert all values of a Bean value object [including nulls]
	 * into an ArrayList of SQLParams
     * @param bean Bean value object 
     * @param key True if the primary key should be included
     * @returns ArrayList ArrayList of SQLParam
	 */
	protected static Collection getAllParams (Bean bean, boolean key) {
		ArrayList params = new ArrayList();
		params.add(new SQLParam(bean.getEntityID(), bean.ENTITYID_TYPE));
		if (key) params.add(new SQLParam(bean.getBankID(), bean.BANKID_TYPE));
		return (Collection) params;
	}	
	
	protected static Collection getAllParams (Bean bean) {
		return getAllParams(bean, true);
	}	
		
} // ---- End BankDAO ----