package com.nsi.components.project;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

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

import com.nsi.components.src.NsiAbstractSrc;
import com.nsi.control.exceptions.NsiEventException;
import com.nsi.util.ConvertDBstr;
import com.nsi.util.ValHelper;

public class ProjectSrc extends NsiAbstractSrc
{
	private static Log log = LogFactory.getLog(ProjectSrc.class);
	private String projectid;
	private ProjectInfo info;
	/**
	 * constructor of ProjectSrc 
	 * @throws NsiEventException
	 */
	public ProjectSrc() throws NsiEventException
	{
		super();
		projectid = "";
		info = new ProjectInfo();
	}
	public String getProjectid()
	{
		return projectid;
	}
	public void setProjectid(String projectid)
	{
		this.projectid = projectid;
	}
	public ProjectInfo getInfo()
	{
		return info;
	}
	public void setInfo(ProjectInfo info)
	{
		this.info = info;
	}
	public void create() throws NsiEventException
	{
		insertProject();
	}
	public void load() throws NsiEventException
	{
		selectProject();
	}
	public void store() throws NsiEventException
	{
		updateProject();
	}
	public boolean projExists() throws NsiEventException
	{
		boolean flag = false;
		String sSql = "select projectid from t_project where projectid =" + projectid;
		try
		{
			getConnection();
			List<Map<String,String>> result = sdrc.executeRetrieve(conn, sSql);
			if(!result.isEmpty()) flag = true;
		}
		catch(SQLException se)
		{
			log.error("projExists() -- caught SQLException : ", se);
			throw new NsiEventException("projExists() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("projExists() -- caught Exception : ", ex);
			throw new NsiEventException("projExists() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
		return flag;
	}
	public boolean isProjExists( String inprojid) throws NsiEventException
	{
		boolean flag = false;
		String sSql = "select projectid from t_project where projectid =" + inprojid;
		try
		{
			getConnection();
			List<Map<String,String>> result = sdrc.executeRetrieve(conn, sSql);
			if(!result.isEmpty())
			{
				projectid = inprojid;
				flag = true;
			}
		}
		catch(SQLException se)
		{
			log.error("projExists() -- caught SQLException : ", se);
			throw new NsiEventException("projExists() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("projExists() -- caught Exception : ", ex);
			throw new NsiEventException("projExists() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
		return flag;
	}
	private void selectProject() throws NsiEventException
	{
		String sSql = 	"select projectid, projectcode, projectname, moduserid, 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
		{
			getConnection();
			Map<String,String> result = sdrc.retrieveSingleRow(conn, sSql);
			if(!result.isEmpty())
			{
				info.reset();
				projectid = ValHelper.getInstance().getValue(result, "projectid");
				info.setProjectid(projectid);
				modifyuser = ValHelper.getInstance().getValue(result, "moduserid");
				String projCode = ValHelper.getInstance().getValue(result, "projectcode");
				setProjectCode(projCode);
				info.setProjectname(ValHelper.getInstance().getValue(result, "projectname"));
				info.setShortname(ValHelper.getInstance().getValue(result, "shortname"));
				info.setProjstatusid(ValHelper.getInstance().getValue(result, "projstatusid"));
				info.setStartdate(ValHelper.getInstance().getValue(result, "startdate"));
				info.setEnddate(ValHelper.getInstance().getValue(result, "enddate"));
				info.setGlobalind(ValHelper.getInstance().getValue(result, "globalind"));
				info.setClientid(ValHelper.getInstance().getValue(result, "clientid"));
			}
		}
		catch(SQLException se)
		{
			log.error("selectProject() -- caught SQLException : ", se);
			throw new NsiEventException("selectProject() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("selectProject() -- caught Exception : ", ex);
			throw new NsiEventException("selectProject() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
	}
	private void setProjectCode( String projectcodestr)
	{
		String projectcodeprefix="";
		String projectcode="";
		if(projectcodestr.indexOf("FFP") != -1)
		{
			projectcodeprefix = "FFP";
			projectcode = projectcodestr.substring(3);
		}
		else if(projectcodestr.indexOf("TM") != -1)
		{
			projectcodeprefix = "TM";
			projectcode = projectcodestr.substring(2);
		}
		else if(projectcodestr.indexOf("INT") != -1)
		{
			projectcodeprefix = "INT";
			projectcode = projectcodestr.substring(3);
		}
		info.setProjectcodeprefix(projectcodeprefix);
		info.setProjectcode(projectcode);
	}
	private void insertProject() throws NsiEventException
	{
		String sCode = ConvertDBstr.getInstance().convertDBstr(info.getProjectcode());
		String sCodePrefix = ConvertDBstr.getInstance().convertDBstr(info.getProjectcodeprefix());
		//String sClientID = ConvertDBstr.getInstance().convertDBstr(info.getClientid());
		String sLongName = ConvertDBstr.getInstance().convertDBstr(info.getProjectname());
		String sName = ConvertDBstr.getInstance().convertDBstr(info.getShortname());
		String sSql = 	"insert into t_project ( projectid, moduserid, projectcode, projectname, shortname, startdate, enddate, projstatusid, globalind, clientid ) " +
							"values ( " + projectid.trim() + ", " + modifyuser.trim() + ", " + "'" + sCodePrefix.trim() + sCode.trim() + "', " + "'" + sLongName.trim() + "', " + 
							"'" + sName.trim() + "', " + "to_date( '" + info.getStartdate().trim() + "', 'MON-DD-YYYY' ), " + 
							"to_date( '" + info.getEnddate().trim() + "', 'MON-DD-YYYY' ), " + info.getProjstatusid().trim() + ", " + 
							info.getGlobalind().trim() + ", " + info.getClientid() + ");";
		
		String insertIntoT_proj_assignSQL = "insert into t_proj_assign (projassignid,moduserid,projectid,resourceid,projroleid,chargetypeid,fromdate,todate,bbillable,percentageload,budgetdays,budgetamount ) " + 
		"values ( nextval('projassignid_seq')," + modifyuser.trim() + "," + projectid.trim() + "," + info.getResourceid().trim() + "," + 2+ "," + 
		3 + "," + "to_date( '" + info.getStartdate().trim() + "', 'MON-DD-YYYY' )," + 
		"to_date( '" + info.getEnddate().trim() + "', 'MON-DD-YYYY' ), " + "'" + 'F'+ "', " + 
		0+ "," + 0 + "," + 0 + " );";
		
		String sql = sSql + insertIntoT_proj_assignSQL;
		try
		{
			getConnection();
			Statement stmt = conn.createStatement();
			int resultCount = sdrc.executeUpdate(stmt, sql);
			if(resultCount != 1) throw new NsiEventException("ERROR insert Project into nsi_project!!  resultCount = " + resultCount);
		}
		catch(SQLException se)
		{
			log.error("insertProject() -- caught SQLException : ", se);
			throw new NsiEventException("insertProject() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("insertProject() -- caught Exception : ", ex);
			throw new NsiEventException("insertProject() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
	}
	private void updateProject() throws NsiEventException
	{
		String sCode = ConvertDBstr.getInstance().convertDBstr(info.getProjectcode());
		String sCodePrefix = ConvertDBstr.getInstance().convertDBstr(info.getProjectcodeprefix());
		//String sClientID = ConvertDBstr.getInstance().convertDBstr(pinfo.getClientid());
		String sLongName = ConvertDBstr.getInstance().convertDBstr(info.getProjectname());
		String sName = ConvertDBstr.getInstance().convertDBstr(info.getShortname());
		String sSql =	"update t_project set moduserid=" + modifyuser.trim() + ", " + 
							"projectcode='" + sCodePrefix.trim() + sCode.trim() + "', " + "projectname='" + sLongName.trim() + "', " + 
							"shortname='" + sName.trim() + "', " + "startdate=to_date( '" + info.getStartdate().trim() + "', 'MON-DD-YYYY' ), " + 
							"enddate=to_date( '" + info.getEnddate().trim() + "', 'MON-DD-YYYY' ), " + 
							"projstatusid=" + info.getProjstatusid().trim() + ", globalind=" + info.getGlobalind().trim() + ", " + 
							"clientid=" + info.getClientid().trim() + " " +
							"where projectid="+ projectid + ";";
		
		String sql = " update t_proj_assign set resourceid= " + info.getResourceid().trim() + " where projectid= " + projectid + " and projroleid= " + 2 + " and resourceid= " + info.getOldResourceid().trim() + ";";
		String totalSQL = sSql + sql;
		
		String sSqlSelect =	"select to_char( enddate, 'MON-DD-YYYY' ) as enddate " + 
									"from t_project where projectid =" + projectid + "";
		try
		{
			getConnection();
			Map<String,String> result = sdrc.retrieveSingleRow(conn, sSqlSelect);
			String originalEndDate = "";
			if(!result.isEmpty())
			{
				originalEndDate = ValHelper.getInstance().getValue(result, "enddate");
			}
			String sSqlAssignEndDate = "update t_proj_assign set moduserid=" + modifyuser.trim() + ", todate=to_date( '" + info.getEnddate().trim() + "', 'MON-DD-YYYY' ) " + 
												"where projectid ="+ projectid + " " + 
												"and resourceid in (select resourceid from t_resource where enddate is null or enddate >= to_date( '" + info.getEnddate().trim() + "', 'MON-DD-YYYY' )) " + 
												"and (( todate > to_date( '" + info.getEnddate().trim() + "', 'MON-DD-YYYY' ) " + 
												"and to_date( '" + info.getEnddate().trim() + "', 'MON-DD-YYYY' ) < to_date( '" + originalEndDate + "', 'MON-DD-YYYY' )) " + 
												"or (todate=to_date( '" + originalEndDate + "', 'MON-DD-YYYY' ) " + 
												"and to_date( '" + info.getEnddate().trim() + "', 'MON-DD-YYYY' ) > to_date( '" + originalEndDate + "', 'MON-DD-YYYY' )))";
			Statement stmuptprj = conn.createStatement();
			int resultCount = sdrc.executeUpdate(stmuptprj, totalSQL);
			if(resultCount == 1)
			{
				Statement stmuptprjassign = conn.createStatement();
				sdrc.executeUpdate(stmuptprjassign, sSqlAssignEndDate);
			}
			else
			{
				throw new NsiEventException("ERROR update project from t_project!!  resultCount = " + resultCount);

			}
		}
		catch(SQLException se)
		{
			log.error("updateProject() -- caught SQLException : ", se);
			throw new NsiEventException("updateProject() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("updateProject() -- caught Exception : ", ex);
			throw new NsiEventException("updateProject() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
	}
}