tsheaderutil.java.svn-base

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

package com.nsi.control.web.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
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.control.exceptions.NsiEventException;
import com.nsi.persistence.DataSrcUtil;
import com.nsi.persistence.IsqlDataSource;
import com.nsi.util.ValHelper;
import com.nsi.util.tsdate.TsDateUtil;

public final class TsHeaderUtil
{
	private static Log log = LogFactory.getLog(TsHeaderUtil.class);
	/**
	 * private constructor of TsHeaderUtil, provent for instantiation
	 */
	private TsHeaderUtil()
	{
	}
	private static class TsHeaderUtilHolder
	{
		static final TsHeaderUtil tsHeaderUtil = new TsHeaderUtil();
	}
	/**
	 * @return an instance of TsHeaderUtil
	 */
	public static TsHeaderUtil getInstance()
	{
		return TsHeaderUtilHolder.tsHeaderUtil;
	}
	public boolean checkResourceTsHead(String weekbegin, String resourceid ) throws NsiEventException
	{
		boolean flag = true;
		String sSql = 	"select timesheetid from t_timesheet " + 
							"where weekbegining = to_date('" + weekbegin.trim() + "', 'MON-DD-YYYY' ) " + 
							"and resourceid =" + resourceid.trim();
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			List<Map<String, String>> result = src.executeRetrieve(conn, sSql);
			if(result.size()!=0){
				flag=false;
			}
		} catch (SQLException se) {
			log.error("checkResourceTsHead() caught SQLException: " + se);
		}
		catch(Exception ex)
		{
			log.error("checkResourceTsHead() caught Exception: " + ex);
		}
		finally
		{
			src.closeConn(conn);
		}
		return flag;
	}
	public boolean checkTsHead(String weekbegin ) throws NsiEventException
	{
		boolean flag = true;
		String sSql = "select timesheetid from t_timesheet where weekbegining = to_date('" + weekbegin.trim() + "', 'MON-DD-YYYY' )";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			List<Map<String,String>> result = src.executeRetrieve(conn, sSql);
			if(!result.isEmpty()) flag = false;
		}
		catch(SQLException se)
		{
			log.error("checkTsHead() caught SQLException: " + se);
		}
		catch(Exception ex)
		{
			log.error("checkTsHead() caught Exception: " + ex);
		}
		finally
		{
			src.closeConn(conn);
		}
		return flag;
	}
	public List<String> getActiveResourceList() throws NsiEventException
	{
		List<String> resourcelist =  new ArrayList<String>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql = "select resourceid from t_user where bactive='T' order by resourceid";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch(SQLException se)
		{
			log.error("getResourcemap() caught SQLException: " + se);
		}
		catch(Exception ex)
		{
			log.error("getResourcemap() 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);
				resourcelist.add( ValHelper.getInstance().getValue(resultmap, "resourceid"));
			}
		}
		return resourcelist;
	}
	public Map<String,String> getResourcemap() throws NsiEventException
	{
		Map<String,String> resourcemap =  new HashMap<String,String>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String sSql = "select resourceid, bactive from t_resource order by resourceid";
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			result = src.executeRetrieve(conn, sSql);
		}
		catch(SQLException se)
		{
			log.error("getResourcemap() caught SQLException: " + se);
		}
		catch(Exception ex)
		{
			log.error("getResourcemap() 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 active = "F";
				if( ValHelper.getInstance().isNotNullAndEmpty(ValHelper.getInstance().getValue(resultmap, "bactive")))
				{
					active = ValHelper.getInstance().getValue(resultmap, "bactive");
				}
				resourcemap.put( ValHelper.getInstance().getValue(resultmap, "resourceid"), active);
			}
		}
		return resourcemap;
	}

	public String getTimeSheetId(String weekbegin,String resourceid) throws NsiEventException {
		String timesheetid = "";
		Map<String,String> result = null;
		String sSql = "select timesheetid from t_timesheet where weekbegining='" + weekbegin + "' 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("getTimeSheetId() caught SQLException: " + se);
		}
		catch(Exception ex)
		{
			log.error("getTimeSheetId caught Exception: " + ex);
		}
		finally
		{
			src.closeConn(conn);
		}
		if(!result.isEmpty())
		{
			timesheetid = ValHelper.getInstance().getValue(result, "timesheetid");
		}
		return timesheetid;
	}

	public void insertResourceNewTsHead(String weekbegin, String resourceid, String modifyuser) throws NsiEventException
	{
		if(checkResourceTsHead(weekbegin,resourceid ))
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				Statement stmt = conn.createStatement();
				insertTS(resourceid.trim(), "T", weekbegin.trim(), modifyuser.trim(), src, stmt);
				conn.commit();
			}
			catch(Exception ex)
			{
				log.error("insertNewTsHead() -- caught Exception : ", ex);
				throw new NsiEventException("insertNewTsHead() -- caught Exception : ", ex);
			}
			finally
			{
				src.closeConn(conn);
			}
		}
	}
	public void insertNewTsHeadFourWeeks() throws NsiEventException
	{
		for (int i = 0; i < 4; i++)
		{
			String weekbegin = TsDateUtil.getInstance().getWeekBeginString(i);
			insertNewTsHead( weekbegin, "999" );
		}
	}
	public void insertNewTsHead(String weekbegin, String modifyuser) throws NsiEventException
	{
		if(checkTsHead(weekbegin.trim()))
		{
			List<String> resourcelist = getActiveResourceList();
			if(!resourcelist.isEmpty())
			{
				int size = resourcelist.size();
				IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
				Connection conn = null;
				try
				{
					conn = src.getConnection();
					
					for( int i=0;i<size;i++)
					{
						String resourceid = resourcelist.get(i);
						if( checkResourceTsHead( weekbegin, resourceid ))
						{
							Statement stmt = conn.createStatement();
							insertTS(resourceid.trim(), "T", weekbegin.trim(), modifyuser.trim(), src, stmt);
						}
					}
					conn.commit();
				}
				catch(Exception ex)
				{
					log.error("insertNewTsHead() -- caught Exception : ", ex);
					throw new NsiEventException("insertNewTsHead() -- caught Exception : ", ex);
				}
				finally
				{
					src.closeConn(conn);
				}
			}
			log.debug("completed insert batch");
		}
	}
	public void insertTS(String resourceid, String active, String weekbegin, String modifyuser, IsqlDataSource src, Statement stmt) throws NsiEventException
	{
		String sSql =	"insert into t_timesheet ( timesheetid, moduserid, resourceid, bactive, bfinalized, weekbegining ) " + 
							"values ( nextval('timesheetid_seq'), " + modifyuser.trim() + ", " + resourceid.trim() + ", '" + active + "', " + 
							"'F', " + "to_date( '" + weekbegin.trim() + "', 'MON-DD-YYYY' ) )";
		try
		{
			int resultCount = src.executeUpdate(stmt, sSql);
			if(resultCount != 1) throw new NsiEventException("ERROR insert ts header into t_timesheet!!  resultCount = " + resultCount);
		}
		catch(SQLException se)
		{
			log.error("insertTS() -- caught SQLException : ", se);
			throw new NsiEventException("insertTS() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("insertProject() -- caught Exception : ", ex);
			throw new NsiEventException("insertProject() -- caught Exception : ", ex);
		}
	}
}