resourceutil.java.svn-base

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

			src.closeConn(conn);
			throw new NsiEventException( "isLoginNameExist() caught SQLException: ", se );
		}
		catch( Exception ex )
		{
			log.error( "isLoginNameExist() caught Exception: " , ex );
			src.closeConn(conn);
			throw new NsiEventException( "isLoginNameExist() caught Exception: ", ex );
		}
		return existed;
	}
	public boolean isResourceNameExist(String firstname, String lastname) throws NsiEventException
	{
		String sSql = "select * from t_resource where firstname ='" + firstname + "' and lastname ='" + lastname + "'";
		boolean existed = false;
		IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
		Connection conn = null;
		try
		{
			conn = src.getConnection();
			List<Map<String,String>> result = src.executeRetrieve(conn, sSql);
			src.closeConn(conn);
			if(!result.isEmpty())
			{
				existed = true;
			}
		}
		catch( SQLException se )
		{
			log.error( "isLoginNameExist() caught SQLException: " , se );
			src.closeConn(conn);
			throw new NsiEventException( "isLoginNameExist() caught SQLException: ", se );
		}
		catch( Exception ex )
		{
			log.error( "isLoginNameExist() caught Exception: " , ex );
			src.closeConn(conn);
			throw new NsiEventException( "isLoginNameExist() caught Exception: ", ex );
		}
		return existed;
	}
	public String getFirstworkingdate(String resourceid)
	{
		String sSql =	"select to_char(tt.weekbegining, 'MON-DD-YYYY') as weekbegining, dd.mon as mon, " +
							"dd.tue as tue, dd.wed as wed, dd.thu as thu, dd.fri as fri, dd.sat as sat, dd.sun as sun " +
							"from t_ts_detail dd, t_timesheet tt " + 
							"where dd.timesheetid = tt.timesheetid and tt.resourceid = " + resourceid + " " +
							"and tt.weekbegining in " + 
							"(select min(w.weekbegining) as minweekbegining from " + 
							"(select t.weekbegining from t_ts_detail d, t_timesheet t " + 
							"where t.resourceid = " + resourceid + " " + 
							"and d.timesheetid = t.timesheetid and d.subtotal <> 0 ) w)";
		String firstworkingdate = AppConstants.EMPTY_STRING;
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.executeRetrieve(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getFirstworkingdate() caught SQLException: " , se );
				src.closeConn(conn);
			}
			catch( Exception ex )
			{
				log.error( "getFirstworkingdate() caught Exception: " , ex );
				src.closeConn(conn);
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				int size = result.size();
				String weekbegining = AppConstants.EMPTY_STRING;
				int weekday = 6;
				int weekdaytmp = 6;
				for( int i=0;i<size;i++)
				{
					Map<String,String> resultmap = result.get(i);
					weekbegining = ValHelper.getInstance().getValue(resultmap, "weekbegining");
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "sat")) && weekdaytmp >= 6) weekday = 6;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "fri")) && weekdaytmp >= 5) weekday = 5;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "thu")) && weekdaytmp >= 4) weekday = 4;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "wed")) && weekdaytmp >= 3) weekday = 3;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "tue")) && weekdaytmp >= 2) weekday = 2;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "mon")) && weekdaytmp >= 1) weekday = 1;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "sun"))) weekday = 0;
					weekdaytmp = weekday;
				}
				firstworkingdate = TsDateUtil.getInstance().getDateStringinWeek( weekbegining, weekday );
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getFirstworkingdate() caught NsiEventException: " , nsiex );
		}
		return firstworkingdate;
	}
	public String getLastworkingdate(String resourceid)
	{
		String sSql =	"select to_char(tt.weekbegining, 'MON-DD-YYYY') as weekbegining, " + 
							"dd.mon as mon , dd.tue as tue, dd.wed as wed, dd.thu as thu, dd.fri as fri, " + 
							"dd.sat as sat, dd.sun as sun " + 
							"from t_ts_detail dd, t_timesheet tt " + 
							"where dd.timesheetid = tt.timesheetid and tt.resourceid = " + resourceid + " " +
							"and tt.weekbegining in " + 
							"(select max(w.weekbegining) as maxweekbegining from " + 
							"(select t.weekbegining from t_ts_detail d, t_timesheet t " + 
							"where t.resourceid = " + resourceid + " " + 
							"and d.timesheetid = t.timesheetid and d.subtotal <> 0 ) w)";
		String lastworkingdate = AppConstants.EMPTY_STRING;
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.executeRetrieve(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getLastworkingdate() caught SQLException: " , se );
				src.closeConn(conn);
			}
			catch( Exception ex )
			{
				log.error( "getLastworkingdate() caught Exception: " , ex );
				src.closeConn(conn);
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				int size = result.size();
				String weekbegining = AppConstants.EMPTY_STRING;
				int weekday = 0;
				int weekdaytmp = 0;
				for( int i=0;i<size;i++)
				{
					Map<String,String> resultmap = result.get(i);
					weekbegining = ValHelper.getInstance().getValue(resultmap, "weekbegining");
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "sun"))) weekday = 0;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "mon")) && weekdaytmp < 1) weekday = 1;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "tue")) && weekdaytmp < 2) weekday = 2;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "wed")) && weekdaytmp < 3) weekday = 3;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "thu")) && weekdaytmp < 4) weekday = 4;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "fri")) && weekdaytmp < 5) weekday = 5;
					if(!"0".equals(ValHelper.getInstance().getValue(resultmap, "sat")) && weekdaytmp < 6) weekday = 6;
					weekdaytmp = weekday;
				}
				lastworkingdate = TsDateUtil.getInstance().getDateStringinWeek( weekbegining, weekday );
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getLastworkingdate() caught NsiEventException: " , nsiex );
		}
		return lastworkingdate;
	}
	public String getFirstassigndate(String resourceid)
	{
		String sSql =	"select to_char(fromdate, 'YYYYMMDD') as fromdate , to_char(fromdate, 'MON-DD-YYYY') as fromdatestr " +
							"from t_proj_assign where resourceid = " + resourceid;
		String firstassigndate = AppConstants.EMPTY_STRING;
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.executeRetrieve(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getFirstassigndate() caught SQLException: " , se );
				src.closeConn(conn);
			}
			catch( Exception ex )
			{
				log.error( "getFirstassigndate() caught Exception: " , ex );
				src.closeConn(conn);
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				int size = result.size();
				Map<String,String> resultmap = result.get(0);
				String firstassigndatetmp = ValHelper.getInstance().getValue(resultmap, "fromdate");
				firstassigndate = ValHelper.getInstance().getValue(resultmap, "fromdatestr");
				for( int i=1;i<size;i++)
				{
					resultmap = result.get(i);
					if(firstassigndatetmp.compareTo(ValHelper.getInstance().getValue(resultmap, "fromdate")) > 0)
					{
						firstassigndatetmp = ValHelper.getInstance().getValue(resultmap, "fromdate");
						firstassigndate = ValHelper.getInstance().getValue(resultmap, "fromdatestr");
					}
				}
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getFirstassigndate() caught NsiEventException: " , nsiex );
		}
		return firstassigndate;
	}
	
	public String getLastassigndate(String resourceid)
	{
		String sSql =	"select to_char(a.todate, 'YYYYMMDD') as todate , to_char(a.todate, 'MON-DD-YYYY') as todatestr " +
							"from t_proj_assign a, t_project p " +
							"where p.projectid =a.projectid " + 
							"and p.globalind = 0 " + 
							"and a.resourceid = " + resourceid;
		String lastassigndate = AppConstants.EMPTY_STRING;
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.executeRetrieve(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getLastassigndate() caught SQLException: " , se );
				src.closeConn(conn);
			}
			catch( Exception ex )
			{
				log.error( "getLastassigndate() caught Exception: " , ex );
				src.closeConn(conn);
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				int size = result.size();
				Map<String,String> resultmap = result.get(0);
				String lastassigndatetmp = ValHelper.getInstance().getValue(resultmap, "todate");
				lastassigndate = ValHelper.getInstance().getValue(resultmap, "todatestr");
				for( int i=1;i<size;i++)
				{
					resultmap = result.get(i);
					if(lastassigndatetmp.compareTo(ValHelper.getInstance().getValue(resultmap, "todate")) < 0)
					{
						lastassigndatetmp = ValHelper.getInstance().getValue(resultmap, "todate");
						lastassigndate = ValHelper.getInstance().getValue(resultmap, "todatestr");
					}
				}
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getLastassigndate() caught NsiEventException: " , nsiex );
		}
		return lastassigndate;
	}
	public List<ResourceProfileInfo> getResourceListByEmptype(String empstatus, String emptype, String orderby, String sequence)
	{	
		List<ResourceProfileInfo> list = new ArrayList<ResourceProfileInfo>();
		List<Map<String,String>> result = new ArrayList<Map<String,String>>();
		String activecondition = " ";
		String emptypecondition = " ";
		String orderbycondition = " ";
		String seq = " asc";
		if("A".equals(empstatus))
		{
			activecondition = "and r.bactive = 'T' ";
		}
		else if("I".equals(empstatus))
		{
			activecondition = "and r.bactive = 'F' ";
		}
		if(!"0".equals(emptype))
		{
			emptypecondition = "and r.empltypeid= " + emptype + " ";
		}
		if("D".equals(sequence))
		{
			seq = " desc";
		}
		if("0".equals(orderby))
		{
			orderbycondition = "order by r.firstname " + seq + ",  r.middlename " + seq + ", r.lastname";
		}
		else if("1".equals(orderby))
		{
			orderbycondition = "order by r.startdate";
		}
		else if("2".equals(orderby))
		{
			orderbycondition = "order by r.enddate";
		}
		else if("3".equals(orderby))
		{
			orderbycondition = "order by e.empltype";
		}
		else if("4".equals(orderby))
		{
			orderbycondition = "order by a.agency";
		}
		String sSql =	"select r.resourceid, r.firstname, r.lastname, r.middlename, r.startdate, r.enddate, " + 
							"r.bactive, r.employeenum, e.empltype, r.remarks, a.agency " + 
							"from t_resource r " + 
							"left join ct_agency a on r.agencyid = a.agencyid " + 
							"join ct_empl_type e on e.empltypeid = r.empltypeid " + 
							"where r.resourceid is not null " + 
							activecondition + emptypecondition + orderbycondition + seq;
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.executeRetrieve(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getResourceListByEmptype() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getResourceListByEmptype() 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);
					ResourceProfileInfo info = new ResourceProfileInfo();
					info.setResourceid(ValHelper.getInstance().getValue(resultmap, "resourceid"));
					info.setFirstname(ValHelper.getInstance().getValue(resultmap, "firstname"));
					info.setLastname(ValHelper.getInstance().getValue(resultmap, "lastname"));
					info.setMiddlename(ValHelper.getInstance().getValue(resultmap, "middlename"));
					info.setStartdate(ValHelper.getInstance().getValue(resultmap, "startdate"));
					info.setEnddate(ValHelper.getInstance().getValue(resultmap, "enddate"));
					info.setActive(ValHelper.getInstance().getValue(resultmap, "bactive"));
					info.setEmployeenum(ValHelper.getInstance().getValue(resultmap, "employeenum"));
					info.setEmpltype(ValHelper.getInstance().getValue(resultmap, "empltype"));
					info.setAgency(ValHelper.getInstance().getValue(resultmap, "empltype"));
					list.add(info);
				}
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getResourceListByEmptype() caught NsiEventException: " + nsiex );
		}
		return list;
	}
}