package com.nsi.components.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.components.util.info.ProjDropInfo;
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;

public final class ProjectUtil
{
	private static Log log = LogFactory.getLog(ProjectUtil.class);
	/**
	 * private constructor of ProjectUtil, prevent instantiation
	 */
	private ProjectUtil()
	{
	}
	private static class ProjectUtilHolder
	{
		static final ProjectUtil projectUtil = new ProjectUtil();
	}
	/**
	 * @return an instance of ProjectUtil
	 */
	public static ProjectUtil getInstance()
	{
		return ProjectUtilHolder.projectUtil;
	}
	public ProjectInfo getSingleProjectInfo( String projectid )
	{
		ProjectInfo info = new ProjectInfo();
		Map<String,String> result = new HashMap<String,String>();
		if(projectid.equals(""))
		{
			projectid = "0";
		}
		String sSql = 	"select projectid, projectcode, projectname, shortname, projstatusid, " + 
							"to_char( startdate, 'MON-DD-YYYY' ) as startdate, to_char( enddate, 'MON-DD-YYYY' ) as enddate, " + 
							"globalind, clientid " + 
							"from t_project " + 
							"where projectid =" + projectid + "";
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.retrieveSingleRow(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getSingleProjectInfo() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getSingleProjectInfo() caught Exception: " + ex );
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				info.setProjectid(ValHelper.getInstance().getValue(result, "projectid"));
				info.setProjectcode(ValHelper.getInstance().getValue(result, "projectcode"));
				info.setProjectname(ValHelper.getInstance().getValue(result, "projectname"));
				info.setShortname(ValHelper.getInstance().getValue(result, "shortname"));
				info.setClientid(ValHelper.getInstance().getValue(result, "clientid"));
				info.setGlobalind(ValHelper.getInstance().getValue(result, "globalind"));
				info.setStartdate(ValHelper.getInstance().getValue(result, "startdate"));
				info.setEnddate(ValHelper.getInstance().getValue(result, "enddate"));
				info.setProjstatusid(ValHelper.getInstance().getValue(result, "projstatusid"));
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getSingleProjectInfo() caught NsiEventException: " + nsiex );
		}
		return info;
	}
	public ProjDropInfo getSinglerecourd( String projectid )
	{
		ProjDropInfo info = new ProjDropInfo();
		Map<String,String> result = new HashMap<String,String>();
		String sSql = 	"select projectid, projectcode, shortname, projectname " + 
							"from t_project " + 
							"where projectid =" + projectid + "";
		try
		{
			IsqlDataSource src = DataSrcUtil.getInstance().getDataSource();
			Connection conn = null;
			try
			{
				conn = src.getConnection();
				result = src.retrieveSingleRow(conn, sSql);
			}
			catch( SQLException se )
			{
				log.error( "getSinglerecourd() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getSinglerecourd() caught Exception: " + ex );
			}
			finally
			{
				src.closeConn(conn);
			}
			if(!result.isEmpty())
			{
				info.setProjectid(ValHelper.getInstance().getValue(result, "projectid"));
				info.setCode(ValHelper.getInstance().getValue(result, "projectcode"));
				info.setName(ValHelper.getInstance().getValue(result, "projectname"));
				info.setShortname(ValHelper.getInstance().getValue(result, "shortname"));
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getSinglerecourd() caught NsiEventException: " + nsiex );
		}
		return info;
	}
	public List<ProjDropInfo> getProjectlist()
	{	
		String sSql = 	"select projectid, projectcode, shortname, projectname from t_project order by projectcode";
		return getProjectlistresult( sSql );
	}
	public List<ProjDropInfo> getActiveProjectlist()
	{	
		String sSql = 	"select projectid, projectcode, shortname, projectname " + 
							"from t_project where enddate >= current_date - 60 order by projectcode";
		return getProjectlistresult( sSql );
	}
	public List<ProjDropInfo> getProjectlistviatype( String searchtype, String projtype )
	{	
		String projtypesql = AppConstants.EMPTY_STRING;
		String projstatussql = AppConstants.EMPTY_STRING;
		if(!"ALL".equals(projtype))
		{
			projtypesql = " and projectcode like '" + projtype + "%' ";
		}
		if("A".equals(searchtype))
		{
			projstatussql = " and projstatusid = 1 ";
		}
		if("I".equals(searchtype))
		{
			projstatussql = " and projstatusid <> 1 ";
		}

		String sSql = 	"select projectid, projectcode, shortname, projectname " + 
							"from t_project " + 
							"where projectid is not null " + 
							projstatussql + projtypesql + " order by projectcode";
		return getProjectlistresult( sSql );
	}
	public List<ProjDropInfo> getProjectlistPerProjectLeader( List<String> projectids )
	{	
		List<ProjDropInfo> list = new ArrayList<ProjDropInfo>();
		if( !projectids.isEmpty())
		{
			String ids = AppConstants.EMPTY_STRING;
			int idssize= projectids.size();
			for (int icnt = 0; icnt < idssize; icnt++)
			{
				if(icnt == 0) ids = ids + projectids.get(icnt);
				else ids = ids + ", " + projectids.get(icnt);
			}
			String sSql = 	"select projectid, projectcode, shortname, projectname " + 
								"from t_project " + 
								"where projectid in( " + ids + " ) " + "order by projectcode";
			list = getProjectlistresult( sSql );
		}
		return list;
	}
	public List<ProjDropInfo> getActiveProjectlistPerProjectLeader( List<String> projectids )
	{	
		List<ProjDropInfo> list = new ArrayList<ProjDropInfo>();
		if( !projectids.isEmpty())
		{
			String ids = AppConstants.EMPTY_STRING;
			int idssize= projectids.size();
			for (int icnt = 0; icnt < idssize; icnt++)
			{
				if(icnt == 0) ids = ids + projectids.get(icnt);
				else ids = ids + ", " + projectids.get(icnt);
			}
			String sSql = 	"select projectid, projectcode, shortname, projectname " + 
								"from t_project where projectid in( " + ids + " ) " + " " + 
								"and enddate >= current_date - 60 order by projectcode";
			list = getProjectlistresult( sSql );
		}
		return list;
	}
	public List<ProjDropInfo> getProjectlistresult(String sSql )
	{
		List<ProjDropInfo> list = new ArrayList<ProjDropInfo>();
		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( "getProjectlistresult() caught SQLException: " + se );
			}
			catch( Exception ex )
			{
				log.error( "getProjectlistresult() 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);
					ProjDropInfo info = new ProjDropInfo();
					info.setProjectid(ValHelper.getInstance().getValue(resultmap, "projectid"));
					info.setCode(ValHelper.getInstance().getValue(resultmap, "projectcode"));
					info.setName(ValHelper.getInstance().getValue(resultmap, "projectname"));
					info.setShortname(ValHelper.getInstance().getValue(resultmap, "shortname"));
					list.add(info);
				}
			}
		}
		catch( NsiEventException nsiex )
		{
			log.error( "getProjectlistresult() caught NsiEventException: " + nsiex );
		}
		return list;
	}
	public String getEarliestentrydate( String projectid )
	{
		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 " +
							"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 min(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 min(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 min(t.weekbegining + '3 day') as weekbegining from t_timesheet t,t_ts_detail d,t_project p,t_proj_act a " +