companydao.java

j2ee源码

/*
 * Created on 2005年11月18日
 *
 * To change the template for this generated file go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
package com.leeman.wkexs.master.company.dao;

import java.sql.ResultSet;
import java.util.ArrayList;

import com.leeman.common.data.Dao;
import com.leeman.common.data.ResultSetReader;
import com.leeman.common.util.Formatter;

import com.leeman.wkexs.master.company.entity.CompanyVO;
import com.leeman.wkexs.master.company.entity.CompanySO;
import com.leeman.wkexs.master.privilege.entity.*;
import com.leeman.wkexs.master.rolemgt.entity.*;

/**
 * @author kenneth
 *
 * To change the template for this generated type comment go to
 * Window>Preferences>Java>Code Generation>Code and Comments
 */
public class CompanyDao extends Dao{
	
	//For Create Combo Box Only
	public String getListQuery()
	{
		StringBuffer sql = new StringBuffer();
		sql.append("select");
		sql.append(" *");
		sql.append(" from company where active = 'Y'");
		sql.append(" order by company_id");
		return sql.toString();
	}
	//For Create Combo Box Only
	public ArrayList getList() throws Exception
	{
		initDbConn();
		ResultSet rs = dbConn.executeQuery(getListQuery());
		ArrayList list = new ArrayList();

		while (rs.next())
		{
			CompanyVO companyVO = new CompanyVO();
			companyVO.setCompany_id(rs.getString("company_id"));
			companyVO.setCompany_name_cht(rs.getString("company_name_cht"));
			companyVO.setCompany_name_chs(rs.getString("company_name_chs"));
			companyVO.setCompany_name_eng(rs.getString("company_name_eng"));
			companyVO.setCompany_desc_cht(rs.getString("company_desc_cht"));
			companyVO.setCompany_desc_chs(rs.getString("company_desc_chs"));
			companyVO.setCompany_desc_eng(rs.getString("company_desc_eng"));
			companyVO.setCompany_chop_name_cht(rs.getString("company_chop_name_cht"));
			companyVO.setCompany_chop_name_chs(rs.getString("company_chop_name_chs"));
			companyVO.setCompany_chop_name_eng(rs.getString("company_chop_name_eng"));
			companyVO.setCompany_address_cht(rs.getString("company_address_cht"));
			companyVO.setCompany_address_chs(rs.getString("company_address_chs"));
			companyVO.setCompany_address_eng(rs.getString("company_address_eng"));
			companyVO.setBase_currency(rs.getString("base_currency"));
			companyVO.setTelephone(rs.getString("telephone"));
			companyVO.setFax(rs.getString("fax"));
			companyVO.setEmail(rs.getString("email"));
			companyVO.setActive(rs.getString("active"));
			companyVO.setPostal_cd(rs.getString("postal_cd"));
			companyVO.setCreate_by(rs.getString("create_by"));
			companyVO.setCreate_dttm(rs.getDate("create_dttm"));
			companyVO.setLast_modify_by(rs.getString("last_modify_by"));
			companyVO.setLast_modify_dttm(rs.getDate("last_modify_dttm"));
			companyVO.setCompany_prefix_cd(rs.getString("company_prefix_cd"));
			companyVO.setLast_mth_end(rs.getDate("last_mth_end"));
			companyVO.setBank_id(rs.getString("bank_id"));
			list.add(companyVO);
		}
		rs.close();
		rs = null;
		return list;
	}
	//For Search Page
	public int getListCount(CompanySO companySO) throws Exception
	{
		int total = 0;
		initDbConn();
		ResultSet rs = dbConn.executeQuery(getListQuery(companySO, true));
		if(rs.next()){				
			total = rs.getInt("total");
		}
		rs.close();
		dbConn.closeQuery();
		return total;		
	}
	
	public String getListQuery(CompanySO companySO, boolean isCountSQL)
	{
		StringBuffer sql = new StringBuffer();
		String sqlJoin = " where";
		
		sql.append("select");
		if (isCountSQL){
			sql.append(" count(*) as total");
		}
		else{
			sql.append(" *");
		}
		sql.append(" from company");
		if (!Formatter.replaceNullString(companySO.getCompany_id()).equals("")){
			sql.append(sqlJoin);
			sql.append(" company_id like " + Formatter.toSqlString(companySO.getCompany_id().toUpperCase() + "%"));
			sqlJoin = " and";
		}
		//Company Name
		if (!Formatter.replaceNullString(companySO.getCompany_name()).equals("")){
			sql.append(sqlJoin);
			sql.append(" (");
			sql.append(" upper(company_name_eng) like " + Formatter.toSqlString(companySO.getCompany_name().toUpperCase() + "%"));
			sql.append(" or upper(company_name_cht) like " + Formatter.toSqlString(companySO.getCompany_name().toUpperCase() + "%"));
			sql.append(" or upper(company_name_chs) like " + Formatter.toSqlString(companySO.getCompany_name().toUpperCase() + "%"));
			sql.append(")");
			sqlJoin = " and";
		}
		//Company Address
		if (!Formatter.replaceNullString(companySO.getCompany_address()).equals("")){
			sql.append(sqlJoin);
			sql.append(" (");
			sql.append(" upper(company_address_eng) like " + Formatter.toSqlString(companySO.getCompany_address().toUpperCase() + "%"));
			sql.append(" or upper(company_address_cht) like " + Formatter.toSqlString(companySO.getCompany_address().toUpperCase() + "%"));
			sql.append(" or upper(company_address_chs) like " + Formatter.toSqlString(companySO.getCompany_address().toUpperCase() + "%"));
			sql.append(")");
			sqlJoin = " and";
		}
		//Base Currency
		if (!Formatter.replaceNullString(companySO.getBase_currency()).equals("")){
			sql.append(sqlJoin);
			sql.append(" base_currency like " + Formatter.toSqlString(companySO.getBase_currency().toUpperCase() + "%"));
			sqlJoin = " and";
		}
		//Telephone
		if (!Formatter.replaceNullString(companySO.getTelephone()).equals("")){
			sql.append(sqlJoin);
			sql.append(" telephone like " + Formatter.toSqlString(companySO.getTelephone() + "%"));
			sqlJoin = " and";
		}
		//Fax
		if (!Formatter.replaceNullString(companySO.getFax()).equals("")){
			sql.append(sqlJoin);
			sql.append(" fax like " + Formatter.toSqlString(companySO.getFax() + "%"));
			sqlJoin = " and";
		}
		//Email
		if (!Formatter.replaceNullString(companySO.getEmail()).equals("")){
			sql.append(sqlJoin);
			sql.append(" upper(email) like " + Formatter.toSqlString(companySO.getEmail().toUpperCase() + "%"));
			sqlJoin = " and";
		}
		//Active
		if (!Formatter.replaceNullString(companySO.getActive()).equals("")){
			sql.append(sqlJoin);
			sql.append(" upper(active) = " + Formatter.toSqlString(companySO.getActive().toUpperCase()));
			sqlJoin = " and";
		}
		//Postal Code
		if (!Formatter.replaceNullString(companySO.getPostal_cd()).equals("")){
			sql.append(sqlJoin);
			sql.append(" upper(postal_cd) like " + Formatter.toSqlString(companySO.getPostal_cd().toUpperCase() + "%"));
			sqlJoin = " and";
	  	}
		sql.append(" order by company_id");
		return sql.toString();
	}
	
	public ArrayList getList(CompanySO companySO, int startIndex, int maxRecords) throws Exception
	{
		initDbConn();
		ResultSet rs = dbConn.executeQuery(getListQuery(companySO, false));
		ArrayList list = new ArrayList();
			
		ResultSetReader reader = new ResultSetReader(rs, startIndex, maxRecords); 
		while (reader.next())
		{
			CompanyVO vo = new CompanyVO();
			vo.setCompany_id(rs.getString("company_id"));
			vo.setCompany_name_cht(rs.getString("Company_name_cht"));
			vo.setCompany_name_chs(rs.getString("Company_name_chs"));
			vo.setCompany_name_eng(rs.getString("Company_name_eng"));
			vo.setCompany_desc_cht(rs.getString("Company_desc_cht"));
			vo.setCompany_desc_chs(rs.getString("Company_desc_chs"));
			vo.setCompany_desc_eng(rs.getString("Company_desc_eng"));
			vo.setCompany_chop_name_cht(rs.getString("Company_chop_name_cht"));
			vo.setCompany_chop_name_chs(rs.getString("Company_chop_name_chs"));
			vo.setCompany_chop_name_eng(rs.getString("Company_chop_name_eng"));
			vo.setCompany_address_cht(rs.getString("Company_address_cht"));
			vo.setCompany_address_chs(rs.getString("Company_address_chs"));
			vo.setCompany_address_eng(rs.getString("Company_address_eng"));
			vo.setBase_currency(rs.getString("Base_currency"));
			vo.setPostal_cd(rs.getString("Postal_cd"));
			vo.setTelephone(rs.getString("Telephone"));
			vo.setFax(rs.getString("Fax"));
			vo.setEmail(rs.getString("Email"));
			vo.setActive(rs.getString("Active"));
			vo.setCreate_by(rs.getString("Create_by"));
			vo.setCreate_dttm(rs.getDate("Create_dttm"));
			vo.setLast_modify_by(rs.getString("Last_modify_by"));
			vo.setLast_modify_dttm(rs.getDate("Last_modify_dttm"));
			vo.setCompany_prefix_cd(rs.getString("company_prefix_cd"));
			vo.setLast_mth_end(rs.getDate("last_mth_end"));
			vo.setBank_id(rs.getString("bank_id"));
			vo.setCustomer_prefix_cd(rs.getString("customer_prefix_cd"));
			
			list.add(vo);
		}
		rs.close();
		rs = null;
		reader = null;		
		return list;
	}
	
	//For getting unique set of privilege when new company is added
	public String getPrivListQuery(boolean r)
	{
		StringBuffer sql = new StringBuffer();
		if (r)
		{
			sql.append("select distinct module, lang_id, program_id, program_name, priv_id, priv_desc");
			sql.append(" from privilege order by lang_id, program_id, priv_id");
		}
		else
		{
			sql.append("select distinct program_id, priv_id");
			sql.append(" from privilege order by program_id, priv_id");
		}
		return sql.toString();
	}
	
	//For getting unique set of privilege when new company is added
	public ArrayList getPrivList(CompanyVO companyVO, boolean r) throws Exception
	{
		initDbConn();
		ResultSet rs = dbConn.executeQuery(getPrivListQuery(r));
		ArrayList list = new ArrayList();

		if (r)
		{
			while (rs.next())
			{
				PrivilegeVO vo = new PrivilegeVO();
				vo.setCompany_id(companyVO.getCompany_id());
				vo.setModule(rs.getString("module"));
				vo.setLang_id(rs.getString("lang_id"));
				vo.setProgram_id(rs.getString("program_id"));
				vo.setProgram_name(rs.getString("program_name"));
				vo.setPriv_id(new Long(rs.getLong("priv_id")));
				vo.setPriv_desc(rs.getString("priv_desc"));
				list.add(vo);
			}
		}
		else
		{
			while (rs.next())
			{
				PrivilegeVO vo = new PrivilegeVO();
				//vo.setCompany_id(companyVO.getCompany_id());
				//vo.setModule(rs.getString("module"));
				//vo.setLang_id(rs.getString("lang_id"));
				vo.setProgram_id(rs.getString("program_id"));
				//vo.setProgram_name(rs.getString("program_name"));
				vo.setPriv_id(new Long(rs.getLong("priv_id")));
				//vo.setPriv_desc(rs.getString("priv_desc"));
				list.add(vo);
			}
		}
		rs.close();
		rs = null;
		return list;
	}
	
	//For adding privilege with new company id
	public void insertPrivListQuery(PrivilegeVO vo) throws Exception
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into privilege(company_id, module, lang_id, program_id, program_name, priv_id, priv_desc)");
		sql.append(" values(" + Formatter.toSqlString(vo.getCompany_id()));
		sql.append(" , " + Formatter.toSqlString(vo.getModule()));
		sql.append(" , " + Formatter.toSqlString(vo.getLang_id()));
		sql.append(" , " + Formatter.toSqlString(vo.getProgram_id()));
		sql.append(" , " + Formatter.toSqlString(vo.getProgram_name()));
		sql.append(" , ");
		sql.append(vo.getPriv_id());
		sql.append(" , " + Formatter.toSqlString(vo.getPriv_desc()) + ")");
		
		initDbConn();
		ResultSet rs = dbConn.executeQuery(sql.toString());
		rs.close();
		dbConn.closeQuery();
		//rs = null;
	}
	
	//For adding role_dtl
	public void insertRoleDtlListQuery(PrivilegeVO vo) throws Exception
	{
		StringBuffer sql = new StringBuffer();
		sql.append(" insert into role_dtl(company_id, role_id, program_id, priv_id, active)");
		sql.append(" values(" + Formatter.toSqlString(vo.getCompany_id()));
		sql.append(" , 'ADMIN'");
		sql.append(" , " + Formatter.toSqlString(vo.getProgram_id()));
		sql.append(" , " + Formatter.toSqlString(vo.getPriv_id()));
		sql.append(" , 'Y')");
			
		initDbConn();
		ResultSet rs = dbConn.executeQuery(sql.toString());
		rs.close();
		dbConn.closeQuery();
		//rs = null;
	}
	
	//For removing privilege
	public void deletePrivListQuery(CompanyVO vo) throws Exception
	{
	 	StringBuffer sql = new StringBuffer();
	 	sql.append(" delete from privilege");
	 	sql.append(" where company_id = " + Formatter.toSqlString(vo.getCompany_id()));
	 			
		initDbConn();
		ResultSet rs = dbConn.executeQuery(sql.toString());
		rs.close();
		dbConn.closeQuery();
		//rs = null;
	}
}