projectutil.java.svn-base

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

							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.wed <> 0 " +
							"union " + 
							"select min(t.weekbegining + '4 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.thu <> 0 " +
							"union " + 
							"select min(t.weekbegining + '5 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.fri <> 0 " +
							"union " + 
							"select min(t.weekbegining + '6 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.sat <> 0 " + ") " +
							"tt";
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.retrieveSingleRow(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getEarliestentrydate() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getEarliestentrydate() caught Exception: " + ex );
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				earliestdate = ValHelper.getInstance().getValue(result, "weekbegining");
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getEarliestentrydate() caught NsiEventException: " + nsiex );
		}
		return earliestdate;
	}
	public String getEarliestentrydateByassign( String projassignid, String resourceid )
	{
		String earliestdate = AppConstants.EMPTY_STRING;
		Map<String,String> result = new HashMap<String,String>();
		String sSql =	"select to_char( min(tt.weekbegining), 'MON-DD-YYYY') as weekbegining from ( " +
							"select min(t.weekbegining) as weekbegining from t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.sun <> 0 " +
							"union " + 
							"select min(t.weekbegining + '1 day') as weekbegining from t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.mon <> 0 " +
							"union " + 
							"select min(t.weekbegining + '2 day') as weekbegining from t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.tue <> 0 " +
							"union " + 
							"select min(t.weekbegining + '3 day') as weekbegining from t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " +
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.wed <> 0 " +
							"union " + 
							"select min(t.weekbegining + '4 day') as weekbegining from t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " +
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.thu <> 0 " +
							"union " + 
							"select min(t.weekbegining + '5 day') as weekbegining from t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.fri <> 0 " +
							"union " + 
							"select min(t.weekbegining + '6 day') as weekbegining from t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.sat <> 0 " + ") " +
		"tt";
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.retrieveSingleRow(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getEarliestentrydate() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getEarliestentrydate() caught Exception: " + ex );
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				earliestdate = ValHelper.getInstance().getValue(result, "weekbegining");
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getEarliestentrydate() caught NsiEventException: " + nsiex );
		}
		return earliestdate;
	}
	public String getLatestentrydate( String projectid )
	{
		String latestdate = AppConstants.EMPTY_STRING;
		Map<String,String> result = new HashMap<String,String>();
		String sSql =	"select to_char( max(tt.weekbegining), 'MON-DD-YYYY') as weekbegining from ( " +
							"select max(t.weekbegining) as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.sun <> 0 " +
							"union " + 
							"select max(t.weekbegining + '1 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.mon <> 0 " +
							"union " + 
							"select max(t.weekbegining + '2 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.tue <> 0 " +
							"union " + 
							"select max(t.weekbegining + '3 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.wed <> 0 " +
							"union " + 
							"select max(t.weekbegining + '4 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.thu <> 0 " +
							"union " + 
							"select max(t.weekbegining + '5 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.fri <> 0 " +
							"union " + 
							"select max(t.weekbegining + '6 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +
							"where t.timesheetid=d.timesheetid and d.projactid=a.projactid and a.projectid=p.projectid and p.projectid=" + projectid + " and d.sat <> 0 " + ") " + 
							"tt";
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.retrieveSingleRow(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getLatestentrydate() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getLatestentrydate() caught Exception: " + ex );
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				latestdate = ValHelper.getInstance().getValue(result, "weekbegining");
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getLatestentrydate() caught NsiEventException: " + nsiex );
		}
		return latestdate;
	}
	public String getLatestentrydateByassign( String projassignid, String resourceid )
	{
		String latestdate = AppConstants.EMPTY_STRING;
		Map<String,String> result = new HashMap<String,String>();
		String sSql =	"select to_char( max(tt.weekbegining), 'MON-DD-YYYY') as weekbegining from ( " +
							"select max(t.weekbegining) as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.sun <> 0 " +
							"union " + 
							"select max(t.weekbegining + '1 day') as weekbegining from   t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.mon <> 0 " +
							"union " + 
							"select max(t.weekbegining + '2 day') as weekbegining from   t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.tue <> 0 " +
							"union " + 
							"select max(t.weekbegining + '3 day') as weekbegining from   t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.wed <> 0 " +
							"union " + 
							"select max(t.weekbegining + '4 day') as weekbegining from   t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.thu <> 0 " +
							"union " +
							"select max(t.weekbegining + '5 day') as weekbegining from   t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.fri <> 0 " +
							"union " + 
							"select max(t.weekbegining + '6 day') as weekbegining from   t_timesheet t, t_ts_detail d,t_project p, t_proj_act a, t_proj_assign pa " +
							"where t.timesheetid = d.timesheetid and d.projactid = a.projactid and a.projectid = p.projectid and pa.projectid = p.projectid " + 
							"and pa.projassignid = " + projassignid + " and t.resourceid = " + resourceid + " and d.sat <> 0 " + ") " +
							"tt";
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.retrieveSingleRow(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getLatestentrydate() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getLatestentrydate() caught Exception: " + ex );
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				latestdate = ValHelper.getInstance().getValue(result, "weekbegining");
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getLatestentrydate() caught NsiEventException: " + nsiex );
		}
		return latestdate;
	}
	public String getResourceid(String projectid) {
		String resourceid = AppConstants.EMPTY_STRING;
		Map<String,String> result = new HashMap<String,String>();
		String sSql = "select resourceid from t_proj_assign where projectid= " + projectid + "and projroleid='2'";
		try {
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try {
				conn = src.getConnection();
				result = src.retrieveSingleRow(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getResourceid() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getResourceid() caught Exception: " + ex );
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty()) 
			{
				resourceid = ValHelper.getInstance().getValue(result, "resourceid");
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getResourceid() caught NsiEventException: " + nsiex );
		}
		return resourceid;
	}
}