usersdao.java

j2ee源码

/*
 * Created on 2005/11/2
 */
package com.leeman.wkexs.master.users.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.users.entity.*;
/**
 * @author Dennis
 */
public class UsersDao extends Dao{

	public int getListCount(UsersSO usersSO) throws Exception
	{
		int total = 0;
		initDbConn();
		ResultSet rs = dbConn.executeQuery(getListQuery(usersSO, true));
		if(rs.next()){				
			total = rs.getInt("total");
		}
		rs.close();
		dbConn.closeQuery();
		return total;		
	}
	
	public String getListQuery(UsersSO usersSO, boolean isCountSQL)
	{
		StringBuffer sql = new StringBuffer();
		
		sql.append("select");
		if (isCountSQL){
			sql.append(" count(*) as total");
		}
		else{
			sql.append(" *");
		}
		sql.append(" from users");
		sql.append(" where company_id = " + Formatter.toSqlString(usersSO.getCompany_id()));
		if (!Formatter.replaceNullString(usersSO.getUser_id()).equals("")){
			sql.append(" and user_id like " + Formatter.toSqlString(usersSO.getUser_id() + "%"));
		}
		if (!Formatter.replaceNullString(usersSO.getName()).equals("")){
			sql.append(" and upper(name) like " + Formatter.toSqlString(usersSO.getName().toUpperCase() + "%"));
		}
		if (!Formatter.replaceNullString(usersSO.getRole_id()).equals("")){
			sql.append(" and role_id like " + Formatter.toSqlString(usersSO.getRole_id() + "%"));
		}
		if (!Formatter.replaceNullString(usersSO.getPosition()).equals("")){
			sql.append(" and upper(position) like " + Formatter.toSqlString(usersSO.getPosition().toUpperCase() + "%"));
		}
		if (!Formatter.replaceNullString(usersSO.getDept_id()).equals("")){
			sql.append(" and dept_id = " + Formatter.toSqlString(usersSO.getDept_id()));
		}	
		if (!Formatter.replaceNullString(usersSO.getActive()).equals("")){
			sql.append(" and active = " + Formatter.toSqlString(usersSO.getActive()));
		}
		
		sql.append(" order by user_id");
		return sql.toString();
	}
	
	public ArrayList getList(UsersSO usersSO, int startIndex, int maxRecords) throws Exception
	{
		initDbConn();
		ResultSet rs = dbConn.executeQuery(getListQuery(usersSO, false));
		ArrayList list = new ArrayList();
			
		ResultSetReader reader = new ResultSetReader(rs, startIndex, maxRecords); 
		while (reader.next())
		{
			UsersVO vo = new UsersVO();
			vo.setCompany_id(rs.getString("company_id"));
			vo.setUser_id(rs.getString("user_id"));
			vo.setName(rs.getString("name"));
			vo.setPassword(rs.getString("password"));
			vo.setActive(rs.getString("active"));
			vo.setRole_id(rs.getString("role_id"));
			vo.setDept_id(rs.getString("dept_id"));
			vo.setPosition(rs.getString("position"));
			vo.setTelephone(rs.getString("telephone"));
			vo.setEmail(rs.getString("email"));
			vo.setFax(rs.getString("fax"));
			vo.setCreate_dttm(rs.getDate("create_dttm"));
			vo.setCreate_by(rs.getString("create_by"));
			vo.setLast_modify_dttm(rs.getDate("last_modify_dttm"));
			vo.setLast_modify_by(rs.getString("last_modify_by"));
						
			list.add(vo);
		}
		rs.close();
		rs = null;
		reader = null;		
		return list;
	}
	
//	check if salesman id exists in SO, purchaser id exists in PO
	 public long getUserIdCount(UsersVO hdrVO,String table) throws Exception
	 {
		 initDbConn();
		 StringBuffer sql = new StringBuffer();
		 sql.append("select count(*) as total from " + table);
		 sql.append(" where company_id = " + Formatter.toSqlString(hdrVO.getCompany_id()));
		 if (!Formatter.replaceNullString(hdrVO.getUser_id()).equals(""))
		 {
		 	 sql.append(" and salesperson_id = " + Formatter.toSqlString(hdrVO.getUser_id()));			
		 }
		 else
		 {
			 long num = 0;
			 return num;
		 }
		
		 ResultSet rs = dbConn.executeQuery(sql.toString());
		 long total = 0;
		 if (rs.next()) {				
			 total = rs.getLong("total");
		 }
		 rs.close();
		 rs = null;
		 dbConn.closeQuery();
		 return total;
	 }

}