rswebinfoutil.java.svn-base

一个timesheet程序,用来统计开发人员的度量衡web在线程序.用于软件行业

package com.nsi.control.web.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.nsi.components.project.ProjectInfo;
import com.nsi.constants.AppConstants;
import com.nsi.control.exceptions.NsiEventException;
import com.nsi.persistence.DataSrcUtil;
import com.nsi.persistence.IsqlDataSource;
import com.nsi.util.ValHelper;

/**
 * @author Chris Ye, created on Oct 7, 2008
 *
 * RsWebInfoUtil
 */
public final class RsWebInfoUtil
{
	private static Log log = LogFactory.getLog(RsWebInfoUtil.class);
	/**
	 * private constructor of RsWebInfoUtil, provent for instantiation
	 */
	private RsWebInfoUtil()
	{
	}
	private static class RsWebInfoUtilHolder
	{
		static final RsWebInfoUtil rsWebInfoUtil = new RsWebInfoUtil();
	}
	/**
	 * @return an instance of RsWebInfoUtil
	 */
	public static RsWebInfoUtil getInstance()
	{
		return RsWebInfoUtilHolder.rsWebInfoUtil;
	}
	public Map<String,String> selectProAssign(String projectid, String resourceid) throws NsiEventException
	{
		Map<String, String> result = new HashMap<String, String>();
		String sSql =	"select projassignid, projectid, resourceid, to_char( fromdate, 'MON-DD-YYYY' ) as fromdate, to_char( todate, 'MON-DD-YYYY' ) as todate " + 
							"from t_proj_assign " + 
							"where projectid=" + projectid + " " +
							"and resourceid=" + resourceid;
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.retrieveSingleRow(conn, sSql);
		}
		catch(SQLException se)
		{
			log.error("getHRRecord() caught SQLException: " + se);
		}
		catch(Exception ex)
		{
			log.error("getHRRecord() caught Exception: " + ex);
		}
		finally
		{
			src.closeConn(conn);
		}
		return result;
	}
	public List<Map<String,String>> selectAgency() throws NsiEventException
	{
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql = "select agencyid, agency from ct_agency order by agency asc";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch( SQLException se )
		{
			log.error( "getEmployeeTypeList() caught SQLException: " + se );
		}
		catch( Exception ex )
		{
			log.error( "getEmployeeTypeList() caught Exception: " + ex );
		}
		finally
		{
			src.closeConn(conn);
		}
		return result;
	}
	public Map<String,List<String>> setRemarkmap(String resourceid, String sdate) throws NsiEventException
	{
		Map<String,List<String>> remarkmap = new HashMap<String,List<String>>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql = 	"select a.projectid, d.projactid, c.remarks, c.activity " + 
							"from ct_activity c, t_proj_act d, t_proj_assign a, t_project b " + 
							"where c.activityid = d.activityid " + 
							"and d.projectid = b.projectid " + 
							"and b.projectid = a.projectid " + 
							"and d.bactive = 't' " + 
							"and a.resourceid =" + resourceid + " " + 
							"and a.fromdate <= to_date( '"+ sdate.trim() + "', 'MON-DD-YYYY' ) " + 
							"and a.todate > to_date( '" + sdate.trim() + "', 'MON-DD-YYYY' ) " + 
							"union " + 
							"select b.projectid, d.projactid, c.remarks, c.activity " + 
							"from ct_activity c, t_proj_act d, t_project b " + 
							"where c.activityid = d.activityid " + 
							"and d.projectid = b.projectid " + 
							"and d.bactive = 't' " + 
							"and b.globalind = 1 " + 
							"order by projectid, activity, projactid";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch( SQLException se )
		{
			log.error( "getEmployeeTypeList() caught SQLException: " + se );
		}
		catch( Exception ex )
		{
			log.error( "getEmployeeTypeList() caught Exception: " + ex );
		}
		finally
		{
			src.closeConn(conn);
		}
		if(!result.isEmpty())
		{
			String tmp_proejctid = AppConstants.EMPTY_STRING;
			int size = result.size();
			for (int i = 0; i < size; i++)
			{
				Map<String,String> resultmap = result.get(i);
				String projectid = ValHelper.getInstance().getValue(resultmap, "projectid");
				if(!tmp_proejctid.trim().equalsIgnoreCase(projectid.trim()))
				{
					List<String> list = new ArrayList<String>();
					list.add(ValHelper.getInstance().getValue(resultmap, "projactid"));
					remarkmap.put(projectid, list);
					tmp_proejctid = projectid;
				}
				else
				{
					List<String> list = remarkmap.get(projectid);
					list.add(ValHelper.getInstance().getValue(resultmap, "projactid"));
				}
			}
		}
		return remarkmap;
	}
	public Map<String, ProjectInfo> setProjectmap(String resourceid) throws NsiEventException
	{
		Map<String, ProjectInfo> projectmap = new HashMap<String, ProjectInfo>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql = 	"select a.projectid, b.projectname, b.projectcode, b.shortname " + 
							"from t_proj_assign a, t_project b " + 
							"where resourceid =" + resourceid.trim() + " " + 
							"and a.projectid = b.projectid " + 
							"union " + 
							"select projectid, projectname, projectcode, shortname " + 
							"from t_project " + 
							"where globalind = 1";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch( SQLException se )
		{
			log.error( "getEmployeeTypeList() caught SQLException: " + se );
		}
		catch( Exception ex )
		{
			log.error( "getEmployeeTypeList() caught Exception: " + ex );
		}
		finally
		{
			src.closeConn(conn);
		}
		if(!result.isEmpty())
		{
			int size = result.size();
			for (int i = 0; i < size; i++)
			{
				Map<String,String> resultmap = result.get(i);
				String projectid = ValHelper.getInstance().getValue(resultmap, "projectid");
				ProjectInfo info = new ProjectInfo();
				info.setProjectid(projectid);
				info.setProjectname(ValHelper.getInstance().getValue(resultmap, "projectname"));
				info.setProjectcode(ValHelper.getInstance().getValue(resultmap, "projectcode"));
				info.setShortname(ValHelper.getInstance().getValue(resultmap, "shortname"));
				projectmap.put(projectid, info);
			}
		}
		return projectmap;
	}
	public Map<String,Map<String,String>> setActivitymap(String resourceid) throws NsiEventException
	{
		Map<String, Map<String,String>> activitymap = new HashMap<String, Map<String,String>>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql = 	"select a.projectid, d.projactid, c.activity, ac.catgroup as cat " + 
							"from ct_activity c, t_proj_act d, t_proj_assign a, t_project b, ct_act_category ac " + 
							"where c.activityid = d.activityid " + 
							"and d.projectid = b.projectid " + 
							"and b.projectid = a.projectid " + 
							"and d.bactive = 'T' " + 
							"and a.resourceid =" + resourceid.trim() + " " + 
							"and c.actcatid=ac.actcatid " + 
							"union " + 
							"select b.projectid, d.projactid, c.activity, ac.catgroup as cat " + 
							"from ct_activity c, t_proj_act d, t_project b, ct_act_category ac " + 
							"where c.activityid = d.activityid " + 
							"and d.projectid = b.projectid " + 
							"and d.bactive = 'T' " + 
							"and b.globalind = 1 " + 
							"and c.actcatid=ac.actcatid " + 
							"order by projectid, cat";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch( SQLException se )
		{
			log.error( "getEmployeeTypeList() caught SQLException: " + se );
		}
		catch( Exception ex )
		{
			log.error( "getEmployeeTypeList() caught Exception: " + ex );
		}
		finally
		{
			src.closeConn(conn);
		}
		if(!result.isEmpty())
		{
			String tmp_proejctid = AppConstants.EMPTY_STRING;
			int size = result.size();
			for (int i = 0; i < size; i++)
			{
				Map<String,String> resultmap = result.get(i);
				String projectid = ValHelper.getInstance().getValue(resultmap, "projectid");
				if(!tmp_proejctid.trim().equalsIgnoreCase(projectid.trim()))
				{
					Map<String,String> actmap = new HashMap<String,String>();
					actmap.put( ValHelper.getInstance().getValue(resultmap, "projactid"), ValHelper.getInstance().getValue(resultmap, "activity"));
					activitymap.put(projectid, actmap);
					tmp_proejctid = projectid;
				}
				else
				{
					Map<String,String> actmap = activitymap.get(projectid); 
					actmap.put( ValHelper.getInstance().getValue(resultmap, "projactid"), ValHelper.getInstance().getValue(resultmap, "activity"));
				}
			}
		}
		return activitymap;
	}
	public List<String[]> setAvailableprojectlist(String resourceid, String sdate) throws NsiEventException
	{
		List<String[]> availableprojectlist = new ArrayList<String[]>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql =	"select distinct a.projectid as projectid, a.projectcode as projectcode, a.shortname as shortname " + 
							"from t_project a, t_proj_assign b " + 
							"where a.enddate >= to_date('" + sdate.trim() + "', 'MON-DD-YYYY') " + 
							"and (( a.projstatusid = 1) " + 
							"or (a.moddate > to_date('" + sdate.trim() + "', 'MON-DD-YYYY'))) " + 
							"and (( a.projectid = b.projectid " + 
							"and b.resourceid = " + resourceid.trim() + " " + 
							"and b.fromdate <= (to_date('" + sdate.trim() + "', 'MON-DD-YYYY') + 6) " + 
							"and b.todate >= to_date('" + sdate.trim() + "', 'MON-DD-YYYY')) " + 
							"or ( a.globalind = 1 )) " + 
							"order by projectcode";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch( SQLException se )
		{
			log.error( "getEmployeeTypeList() caught SQLException: " + se );
		}
		catch( Exception ex )
		{
			log.error( "getEmployeeTypeList() caught Exception: " + ex );
		}
		finally
		{
			src.closeConn(conn);
		}
		if(!result.isEmpty())
		{
			int size = result.size();
			for (int i = 0; i < size; i++)
			{
				Map<String,String> resultmap = result.get(i);
				String projectid = ValHelper.getInstance().getValue(resultmap, "projectid");
				String projectcode = ValHelper.getInstance().getValue(resultmap, "projectcode");
				String shortname = ValHelper.getInstance().getValue(resultmap, "shortname");
				availableprojectlist.add(new String[] { projectid, projectcode, shortname });
			}
		}
		return availableprojectlist;
	}
	public List<String[]> setActcategorylist() throws NsiEventException
	{
		List<String[]> actcategorylist = new ArrayList<String[]>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql =	"select catgroup, category from ct_act_category order by catgroup";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch( SQLException se )
		{
			log.error( "getEmployeeTypeList() caught SQLException: " + se );
		}
		catch( Exception ex )
		{
			log.error( "getEmployeeTypeList() caught Exception: " + ex );
		}
		finally
		{
			src.closeConn(conn);
		}
		if(!result.isEmpty())
		{
			int size = result.size();
			for (int i = 0; i < size; i++)
			{
				Map<String,String> resultmap = result.get(i);
				String catgroup = ValHelper.getInstance().getValue(resultmap, "catgroup");
				String category = ValHelper.getInstance().getValue(resultmap, "category");
				actcategorylist.add(new String[] { catgroup, category });
			}
		}
		return actcategorylist;
	}
	public Map<String, List<String[]>> setProjectactmap(String resourceid) throws NsiEventException
	{
		Map<String, List<String[]>> projectactmap = new HashMap<String, List<String[]>>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql =	"select a.projectid as projectid, d.projactid as projactid, c.activity as activity, ac.catgroup as catgroup " +
							"from ct_activity c, t_proj_act d, t_proj_assign a, t_project b, ct_act_category ac " + 
							"where c.activityid = d.activityid " + 
							"and d.projectid = b.projectid " +
							"and b.projectid = a.projectid " + 
							"and d.bactive = 'T' " + 
							"and a.resourceid =" + resourceid.trim() + " " + 
							"and c.actcatid=ac.actcatid " + 
							"union " +
							"select b.projectid, d.projactid, c.activity, ac.catgroup as cat " + 
							"from ct_activity c, t_proj_act d, t_project b, ct_act_category ac " + 
							"where c.activityid = d.activityid " +
							"and d.projectid = b.projectid " + 
							"and d.bactive = 'T' " + 
							"and b.globalind = 1 " + 
							"and c.actcatid=ac.actcatid " + 
							"order by projectid, catgroup, activity";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch( SQLException se )
		{
			log.error( "setProjectactmap() caught SQLException: " + se );
		}
		catch( Exception ex )
		{
			log.error( "setProjectactmap() caught Exception: " + ex );
		}
		finally
		{
			src.closeConn(conn);
		}
		if(!result.isEmpty())
		{
			String tmp_proejctid = AppConstants.EMPTY_STRING;
			int size = result.size();
			for (int i = 0; i < size; i++)
			{
				Map<String,String> resultmap = result.get(i);
				String projectid = ValHelper.getInstance().getValue(resultmap, "projectid");
				String projactid = ValHelper.getInstance().getValue(resultmap, "projactid");
				String activity = ValHelper.getInstance().getValue(resultmap, "activity");
				String catgroup = ValHelper.getInstance().getValue(resultmap, "catgroup");
				if(!tmp_proejctid.trim().equalsIgnoreCase(projectid.trim()))
				{
					List<String[]> list = new ArrayList<String[]>();
					list.add(new String[] { projactid, activity, catgroup });
					projectactmap.put(projectid, list);
					tmp_proejctid = projectid;
				}
				else
				{
					List<String[]> list = projectactmap.get(projectid);
					list.add(new String[] { projactid, activity, catgroup });
				}
			}
		}
		return projectactmap;
	}
}