/*
 * $Header: /cvsroot/mvnforum/mvnforum/src/com/mvnforum/db/jdbc/CompanyDAOImplJDBC.java,v 1.27 2005/01/18 11:52:19 minhnn Exp $
 * $Author: minhnn $
 * $Revision: 1.27 $
 * $Date: 2005/01/18 11:52:19 $
 *
 * ====================================================================
 *
 * Copyright (C) 2002-2005 by MyVietnam.net
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or any later version.
 *
 * All copyright notices regarding mvnForum MUST remain intact
 * in the scripts and in the outputted HTML.
 * The "powered by" text/logo with a link back to
 * http://www.mvnForum.com and http://www.MyVietnam.net in the
 * footer of the pages MUST remain visible when the pages
 * are viewed on the internet or intranet.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 *
 * Support can be obtained from support forums at:
 * http://www.mvnForum.com/mvnforum/index
 *
 * Correspondence and Marketing Questions can be sent to:
 - info@MyVietnam.net
 -
 - @author: Tran Van Giang   trangiang1605@users.sourceforge.net
 */
package com.mvnforum.db.jdbc;

import java.sql.*;
import java.util.*;

import com.mvnforum.db.*;
import net.myvietnam.mvncore.db.DBUtils;
import net.myvietnam.mvncore.exception.*;
import net.myvietnam.mvncore.util.DateUtil;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class CompanyDAOImplJDBC implements CompanyDAO {

    private static Log log = LogFactory.getLog(CompanyDAOImplJDBC.class);

    // this variable will support caching if cache for this class is needed
    private static boolean m_dirty = true;

    public CompanyDAOImplJDBC() {
    }

    protected static boolean isDirty() {
        return m_dirty;
    }

    protected static void setDirty(boolean dirty) {
        m_dirty = dirty;
    }

    public void findByPrimaryKey(int companyID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanyID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, companyID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the primary key (" + companyID + ") in table 'Company'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByPrimaryKey.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void findByAlternateKey_GroupID(int groupID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT GroupID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE GroupID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, groupID);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the alternate key [GroupID] (" + groupID + ") in table 'Company'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByAlternateKey_GroupID.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void findByAlternateKey_CompanyName(String companyName)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyName");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanyName = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, companyName);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the alternate key [CompanyName] (" + companyName + ") in table 'Company'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByAlternateKey_CompanyName.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void findByAlternateKey_CompanyEmail(String companyEmail)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyEmail");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanyEmail = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, companyEmail);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the alternate key [CompanyEmail] (" + companyEmail + ") in table 'Company'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByAlternateKey_CompanyEmail.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void findByAlternateKey_CompanySpaceName(String companySpaceName)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanySpaceName");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanySpaceName = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, companySpaceName);
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the alternate key [CompanySpaceName] (" + companySpaceName + ") in table 'Company'.");
            }
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.findByAlternateKey_CompanySpaceName.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: GroupID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP,
     *                   CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite,
     *                   CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber,
     *                   CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate
     * Excluded columns: CompanyID
     */
    public void create(int groupID, String companyName, String companyAddress,
                        String companyCity, String companyCAP, String companyProvince,
                        String companyRegion, String companyPhone, String companyFax,
                        String companyWebsite, String companyEmail, String companySpaceName,
                        String companySpaceHeader, String companySpaceFooter, String companyVATNumber,
                        String companyLogo, String companyCss, Timestamp companyCreationDate,
                        Timestamp companyModifiedDate)
        throws CreateException, DatabaseException, DuplicateKeyException, ForeignKeyNotFoundException {

        // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
        // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
        try {
            //Check if alternate key already exists
            findByAlternateKey_GroupID(groupID);
            //If so, then we have to throw an exception
            throw new DuplicateKeyException("Alternate key already exists. Cannot create new Company with the same [GroupID] (" + groupID + ").");
        } catch(ObjectNotFoundException e) {
            //Otherwise we can go ahead
        }

        // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
        // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
        try {
            //Check if alternate key already exists
            findByAlternateKey_CompanyName(companyName);
            //If so, then we have to throw an exception
            throw new DuplicateKeyException("Alternate key already exists. Cannot create new Company with the same [CompanyName] (" + companyName + ").");
        } catch(ObjectNotFoundException e) {
            //Otherwise we can go ahead
        }

        // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
        // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
        try {
            //Check if alternate key already exists
            findByAlternateKey_CompanyEmail(companyEmail);
            //If so, then we have to throw an exception
            throw new DuplicateKeyException("Alternate key already exists. Cannot create new Company with the same [CompanyEmail] (" + companyEmail + ").");
        } catch(ObjectNotFoundException e) {
            //Otherwise we can go ahead
        }

        // @todo: Comment this try-catch block if the needed columns dont have attribute 'include'
        // If this is the case, then it is highly recommended that you regenerate this method with the attribute 'include' turned on
        try {
            //Check if alternate key already exists
            findByAlternateKey_CompanySpaceName(companySpaceName);
            //If so, then we have to throw an exception
            throw new DuplicateKeyException("Alternate key already exists. Cannot create new Company with the same [CompanySpaceName] (" + companySpaceName + ").");
        } catch(ObjectNotFoundException e) {
            //Otherwise we can go ahead
        }

        try {
            // @todo: modify the parameter list as needed
            // You may have to regenerate this method if the needed columns dont have attribute 'include'
            DAOFactory.getGroupsDAO().findByPrimaryKey(groupID);
        } catch(ObjectNotFoundException e) {
            throw new ForeignKeyNotFoundException("Foreign key refers to table 'Groups' does not exist. Cannot create new Company.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("INSERT INTO " + TABLE_NAME + " (GroupID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate)");
        sql.append(" VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            statement.setInt(1, groupID);
            statement.setString(2, companyName);
            statement.setString(3, companyAddress);
            statement.setString(4, companyCity);
            statement.setString(5, companyCAP);
            statement.setString(6, companyProvince);
            statement.setString(7, companyRegion);
            statement.setString(8, companyPhone);
            statement.setString(9, companyFax);
            statement.setString(10, companyWebsite);
            statement.setString(11, companyEmail);
            statement.setString(12, companySpaceName);
            statement.setString(13, companySpaceHeader);
            statement.setString(14, companySpaceFooter);
            statement.setString(15, companyVATNumber);
            statement.setString(16, companyLogo);
            statement.setString(17, companyCss);
            statement.setTimestamp(18, companyCreationDate);
            statement.setTimestamp(19, companyModifiedDate);

            if (statement.executeUpdate() != 1) {
                throw new CreateException("Error adding a row into table 'Company'.");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.create.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void delete(int companyID)
        throws DatabaseException, ObjectNotFoundException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("DELETE FROM " + TABLE_NAME);
        sql.append(" WHERE CompanyID = ?");

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, companyID);
            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot delete a row in table Company where primary key = (" + companyID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.delete.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CompanyName, CompanyAddress, CompanyCity, CompanyCAP, CompanyProvince,
     *                   CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail,
     *                   CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyModifiedDate
     * Excluded columns: CompanyID, GroupID, CompanyLogo, CompanyCss, CompanyCreationDate
     */
    public void updateCompanyInfo(int companyID, // primary key
                        String companyName, String companyAddress, String companyCity,
                        String companyCAP, String companyProvince, String companyRegion,
                        String companyPhone, String companyFax, String companyWebsite,
                        String companyEmail, String companySpaceName, String companySpaceHeader,
                        String companySpaceFooter, String companyVATNumber, Timestamp companyModifiedDate)
        throws ObjectNotFoundException, DatabaseException, DuplicateKeyException {

        CompanyBean bean = getCompany(companyID);

        if ( !companyName.equals(bean.getCompanyName()) ) {
            // Company tries to change its alternate key <CompanyName>, so we must check if it already exist
            try {
                findByAlternateKey_CompanyName(companyName);
                throw new DuplicateKeyException("Alternate key [CompanyName] (" + companyName + ")already exists. Cannot update Company.");
            } catch(ObjectNotFoundException e) {
                //Otherwise we can go ahead
            }
        }

        if ( !companyEmail.equals(bean.getCompanyEmail()) ) {
            // Company tries to change its alternate key <CompanyEmail>, so we must check if it already exist
            try {
                findByAlternateKey_CompanyEmail(companyEmail);
                throw new DuplicateKeyException("Alternate key [CompanyEmail] (" + companyEmail + ")already exists. Cannot update Company.");
            } catch(ObjectNotFoundException e) {
                //Otherwise we can go ahead
            }
        }

        if ( !companySpaceName.equals(bean.getCompanySpaceName()) ) {
            // Company tries to change its alternate key <CompanySpaceName>, so we must check if it already exist
            try {
                findByAlternateKey_CompanySpaceName(companySpaceName);
                throw new DuplicateKeyException("Alternate key [CompanySpaceName] (" + companySpaceName + ")already exists. Cannot update Company.");
            } catch(ObjectNotFoundException e) {
                //Otherwise we can go ahead
            }
        }

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET CompanyName = ?, CompanyAddress = ?, CompanyCity = ?, CompanyCAP = ?, CompanyProvince = ?, CompanyRegion = ?, CompanyPhone = ?, CompanyFax = ?, CompanyWebsite = ?, CompanyEmail = ?, CompanySpaceName = ?, CompanySpaceHeader = ?, CompanySpaceFooter = ?, CompanyVATNumber = ?, CompanyModifiedDate = ?");
        sql.append(" WHERE CompanyID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setString(1, companyName);
            statement.setString(2, companyAddress);
            statement.setString(3, companyCity);
            statement.setString(4, companyCAP);
            statement.setString(5, companyProvince);
            statement.setString(6, companyRegion);
            statement.setString(7, companyPhone);
            statement.setString(8, companyFax);
            statement.setString(9, companyWebsite);
            statement.setString(10, companyEmail);
            statement.setString(11, companySpaceName);
            statement.setString(12, companySpaceHeader);
            statement.setString(13, companySpaceFooter);
            statement.setString(14, companyVATNumber);
            statement.setTimestamp(15, companyModifiedDate);

            // primary key column(s)
            statement.setInt(16, companyID);

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update table Company where primary key = (" + companyID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.updateCompanyInfo.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void updateCompanyLogo(int companyID, // primary key
                        String companyLogo)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET CompanyLogo = ?");
        sql.append(" WHERE CompanyID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setString(1, companyLogo);

            // primary key column(s)
            statement.setInt(2, companyID);

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update table Company where primary key = (" + companyID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.updateCompanyLogo.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public void updateCompanyCss(int companyID, // primary key
                                 String companyCss)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("UPDATE " + TABLE_NAME + " SET CompanyCss = ?");
        sql.append(" WHERE CompanyID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());

            // // column(s) to update
            statement.setString(1, companyCss);

            // primary key column(s)
            statement.setInt(2, companyID);

            if (statement.executeUpdate() != 1) {
                throw new ObjectNotFoundException("Cannot update table Company where primary key = (" + companyID + ").");
            }
            m_dirty = true;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.updateCompanyCss.");
        } finally {
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getCompanyIDFromGroupID(int groupID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE GroupID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, groupID);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Company where alternate key [GroupID] = (" + groupID + ").");
            }

            return resultSet.getInt("CompanyID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanyIDFromGroupID(ak).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getCompanyIDFromCompanyEmail(String companyEmail)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanyEmail = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, companyEmail);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Company where alternate key [CompanyEmail] = (" + companyEmail + ").");
            }

            return resultSet.getInt("CompanyID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanyIDFromCompanyEmail(ak).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getCompanyIDFromCompanySpaceName(String companySpaceName)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanySpaceName = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, companySpaceName);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Company where alternate key [CompanySpaceName] = (" + companySpaceName + ").");
            }

            return resultSet.getInt("CompanyID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanyIDFromCompanySpaceName(ak).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getCompanyIDFromCompanyCreationDate(java.util.Date companyCreationDate)
        throws ObjectNotFoundException, DatabaseException {

        int totalCompanies = 0;
        int ret = 0;

        try {
            totalCompanies = getNumberOfCompanies();
        } catch (Exception err) {}

        Collection collectionCompany = getCompanies_withSortSupport_limit(0, totalCompanies, "CompanyCreationDate", "ASC");
        Iterator iteratorCompany = collectionCompany.iterator();
        while (iteratorCompany.hasNext()) {
            CompanyBean companyBean = (CompanyBean) iteratorCompany.next();
            java.util.Date companyCreationDateDB = new java.util.Date(companyBean.getCompanyCreationDate().getTime());
            String dateFormat = DateUtil.getDateDDMMYYYY(companyCreationDate);
            String dateFormatDB = DateUtil.getDateDDMMYYYY(companyCreationDateDB);
            if (dateFormat.equals(dateFormatDB)) {
                ret = companyBean.getCompanyID();
                break;
            }
        }
        if (ret == 0) {
            throw new ObjectNotFoundException("Cannot get company ID from company date.");
        }
        return ret;
    }

    /*
     * Included columns: CompanyID, GroupID, CompanyName, CompanyAddress, CompanyCity,
     *                   CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax,
     *                   CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter,
     *                   CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate
     * Excluded columns:
     */
    public CompanyBean getCompany(int companyID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID, GroupID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanyID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, companyID);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Company where primary key = (" + companyID + ").");
            }

            CompanyBean bean = new CompanyBean();
            // @todo: uncomment the following line(s) as needed
            //bean.setCompanyID(companyID);
            bean.setCompanyID(resultSet.getInt("CompanyID"));
            bean.setGroupID(resultSet.getInt("GroupID"));
            bean.setCompanyName(resultSet.getString("CompanyName"));
            bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
            bean.setCompanyCity(resultSet.getString("CompanyCity"));
            bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
            bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
            bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
            bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
            bean.setCompanyFax(resultSet.getString("CompanyFax"));
            bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
            bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
            bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
            bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
            bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
            bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
            bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
            bean.setCompanyCss(resultSet.getString("CompanyCss"));
            bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
            bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
            return bean;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompany(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public Collection getCompanies_withSortSupport_limit(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {
        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
            return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order);
        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
            return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order);
        }
        return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order);
    }

    /*
     * Included columns: CompanyID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP,
     *                   CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite,
     *                   CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate
     * Excluded columns:
     */
    /**
     * This method support sorting and for PUBLIC view
     */
    /* @todo fix bug that cannot prepare sort and order */
    private Collection getBeans_withSortSupport_limit_mysql(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {
        if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed.");
        if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed.");
        /*
         * Sort by
         * CompanyID or CompanyName or CompanyCreationDate
         */
        if ((!sort.equals("CompanyID")) &&
           (!sort.equals("CompanyName")) &&
           (!sort.equals("CompanyAddress")) &&
           (!sort.equals("CompanyCreationDate"))) {
           throw new IllegalArgumentException("Cannot sort, reason: don't understand the criteria '" + sort + "'.");
        }

        if ((!order.equals("ASC")) &&
           (!order.equals("DESC")) ) {
           throw new IllegalArgumentException("Cannot sort, reason: don't understand the order '" + order + "'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT *");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC
        if (rowsToReturn != 1) {
            sql.append(" LIMIT ?, ?");
        }

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            if (rowsToReturn != 1) {
                statement.setInt(1, offset);
                statement.setInt(2, rowsToReturn);
            }
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                CompanyBean bean = new CompanyBean();
                bean.setCompanyID(resultSet.getInt("CompanyID"));
                bean.setGroupID(resultSet.getInt("GroupID"));
                bean.setCompanyName(resultSet.getString("CompanyName"));
                bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
                bean.setCompanyCity(resultSet.getString("CompanyCity"));
                bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
                bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
                bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
                bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
                bean.setCompanyFax(resultSet.getString("CompanyFax"));
                bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
                bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
                bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
                bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
                bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
                bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
                bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
                bean.setCompanyCss(resultSet.getString("CompanyCss"));
                bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
                bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getBeans_withSortSupport_limit_mysql.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CompanyID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP,
     *                   CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite,
     *                   CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate
     * Excluded columns:
     */
    /**
     * This method support sorting and for PUBLIC view
     */
    /* @todo fix bug that cannot prepare sort and order */
    private Collection getBeans_withSortSupport_limit_noscroll(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {
        if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed.");
        if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed.");
        /*
         * Sort by
         * CompanyID or CompanyName or CompanyCreationDate
         */
        if ((!sort.equals("CompanyID")) &&
           (!sort.equals("CompanyName")) &&
           (!sort.equals("CompanyAddress")) &&
           (!sort.equals("CompanyCreationDate"))) {
           throw new IllegalArgumentException("Cannot sort, reason: don't understand the criteria '" + sort + "'.");
        }

        if ((!order.equals("ASC")) &&
           (!order.equals("DESC")) ) {
            throw new IllegalArgumentException("Cannot sort, reason: don't understand the order '" + order + "'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT *");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            if (rowsToReturn != 1) {
                statement.setMaxRows(offset + rowsToReturn);
            }
            resultSet = statement.executeQuery();
            int rowIndex = -1;
            while (resultSet.next()) {
                rowIndex++;
                if(rowsToReturn !=1) {
                    if (rowIndex < offset) continue;
                }
                CompanyBean bean = new CompanyBean();
                bean.setCompanyID(resultSet.getInt("CompanyID"));
                bean.setGroupID(resultSet.getInt("GroupID"));
                bean.setCompanyName(resultSet.getString("CompanyName"));
                bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
                bean.setCompanyCity(resultSet.getString("CompanyCity"));
                bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
                bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
                bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
                bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
                bean.setCompanyFax(resultSet.getString("CompanyFax"));
                bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
                bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
                bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
                bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
                bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
                bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
                bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
                bean.setCompanyCss(resultSet.getString("CompanyCss"));
                bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
                bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
                retValue.add(bean);
                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

   /*
    * Included columns: CompanyID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP,
    *                   CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite,
    *                   CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate
    * Excluded columns:
    */
   /**
    * This method support sorting and for PUBLIC view
    */
   /* @todo fix bug that cannot prepare sort and order */

    private Collection getBeans_withSortSupport_limit_general(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {
        if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed.");
        if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed.");
        /*
         * Sort by
         * CompanyID or CompanyName or CompanyCreationDate
         */
        if ((!sort.equals("CompanyID")) &&
            (!sort.equals("CompanyName")) &&
            (!sort.equals("CompanyAddress")) &&
            (!sort.equals("CompanyCreationDate"))) {
            throw new IllegalArgumentException("Cannot sort, reason: don't understand the criteria '" + sort + "'.");
        }

        if ((!order.equals("ASC")) &&
            (!order.equals("DESC")) ) {
            throw new IllegalArgumentException("Cannot sort, reason: don't understand the order '" + order + "'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT *");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (rowsToReturn != 1) {
                statement.setMaxRows(offset + rowsToReturn);
            }
            try {
                statement.setFetchSize(rowsToReturn);
            } catch (SQLException sqle) {
                //do nothing, postgreSQL doesnt support this method
            }
            resultSet = statement.executeQuery();
            boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
            while (loop) {
                CompanyBean bean = new CompanyBean();
                bean.setCompanyID(resultSet.getInt("CompanyID"));
                bean.setGroupID(resultSet.getInt("GroupID"));
                bean.setCompanyName(resultSet.getString("CompanyName"));
                bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
                bean.setCompanyCity(resultSet.getString("CompanyCity"));
                bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
                bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
                bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
                bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
                bean.setCompanyFax(resultSet.getString("CompanyFax"));
                bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
                bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
                bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
                bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
                bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
                bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
                bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
                bean.setCompanyCss(resultSet.getString("CompanyCss"));
                bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
                bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
                retValue.add(bean);
                if(rowsToReturn !=1) {
                    if (retValue.size() == rowsToReturn) break; // Fix the Sybase bug
                }
                loop = resultSet.next();
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getBeans_withSortSupport_limit_general.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getNumberOfCompanies()
        throws AssertionException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT Count(*)");
        sql.append(" FROM " + TABLE_NAME);
        //sql.append(" WHERE "); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in CompanyDAOImplJDBC.getNumberOfCompanies.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getNumberOfCompanies.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CompanyID, GroupID, CompanyName, CompanyAddress, CompanyCity,
     *                   CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax,
     *                   CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter,
     *                   CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate
     * Excluded columns:
     */
    public Collection getCompanies()
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID, GroupID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate");
        sql.append(" FROM " + TABLE_NAME);
        //sql.append(" WHERE "); // @todo: uncomment as needed
        //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                CompanyBean bean = new CompanyBean();
                bean.setCompanyID(resultSet.getInt("CompanyID"));
                bean.setGroupID(resultSet.getInt("GroupID"));
                bean.setCompanyName(resultSet.getString("CompanyName"));
                bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
                bean.setCompanyCity(resultSet.getString("CompanyCity"));
                bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
                bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
                bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
                bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
                bean.setCompanyFax(resultSet.getString("CompanyFax"));
                bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
                bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
                bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
                bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
                bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
                bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
                bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
                bean.setCompanyCss(resultSet.getString("CompanyCss"));
                bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
                bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanies.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

} // end of class CompanyDAOImplJDBC