package com.nsi.components.projassign;

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.ValHelper;

/**
 * @author Chris Ye, created on Oct 3, 2008
 *
 * ProAssignSrc
 */
public class ProAssignSrc extends NsiAbstractSrc
{
	private static Log log = LogFactory.getLog(ProAssignSrc.class);
	private ProAssignInfo info;
	private String projectid;
	/**
	 * constructor of ProAssignSrc
	 * @throws NsiEventException
	 */
	public ProAssignSrc() throws NsiEventException
	{
		super();
		projectid = "";
		info = new ProAssignInfo();
	}
	public ProAssignInfo getInfo()
	{
		return info;
	}
	public void setInfo(ProAssignInfo info)
	{
		this.info = info;
	}
	public String getProjectid()
	{
		return projectid;
	}
	public void setProjectid(String projectid)
	{
		this.projectid = projectid;
	}
	public void load() throws NsiEventException
	{
		selectProAssign();
	}
	public void store() throws NsiEventException
	{
		updateProAssign();
	}
	public boolean isProjectExists( String inprojectid) throws NsiEventException
	{
		boolean flag = false;
		String sSql = "select projectid from t_project where projectid =" + inprojectid;
		try
		{
			getConnection();
			List<Map<String,String>> result = sdrc.executeRetrieve(conn, sSql);
			if(!result.isEmpty())
			{
				projectid = inprojectid;
				flag = true;
			}
		}
		catch(SQLException se)
		{
			log.error("isExists() -- caught SQLException : ", se);
			throw new NsiEventException("isExists() -- 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 isExists() 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("isExists() -- caught SQLException : ", se);
			throw new NsiEventException("isExists() -- 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 selectProAssign() throws NsiEventException
	{
		String sSql = 	"select p.projassignid,p.projectid,p.resourceid,p.moduserid,p.projroleid, " + 
							"p.chargetypeid,p.bbillable,p.percentageload,p.budgetdays,p.budgetamount, " + 
							"to_char( p.fromdate, 'MON-DD-YYYY' ) as fromdate, to_char( p.todate, 'MON-DD-YYYY' ) as todate, " + 
							"to_char( r.startdate, 'MON-DD-YYYY' ) as hiringdate,to_char( r.enddate, 'MON-DD-YYYY' ) as termdate " + 
							"from t_proj_assign p, t_resource r  where p.projectid=" + projectid + " " + 
							"and p.resourceid = r.resourceid order by projroleid";
		try
		{
			getConnection();
			List<Map<String,String>> result = sdrc.executeRetrieve(conn, sSql);
			info.reset();
			info.setProjectid(projectid);
			if(!result.isEmpty())
			{
				int size = result.size();
				for( int i=0; i<size; i++)
				{
					Map<String,String> dtlresult = result.get(i);
					ProAssignDetail detail = new ProAssignDetail();
					detail.setProjassignid(ValHelper.getInstance().getValue(dtlresult, "projassignid"));
					modifyuser = ValHelper.getInstance().getValue(dtlresult, "moduserid");
					detail.setProjectid(projectid);
					detail.setResourceid(ValHelper.getInstance().getValue(dtlresult, "resourceid"));
					detail.setProjroleid(ValHelper.getInstance().getValue(dtlresult, "projroleid"));
					detail.setChargetypeid(ValHelper.getInstance().getValue(dtlresult, "chargetypeid"));
					String sbillable = "T";
					String svalue = ValHelper.getInstance().getValue(dtlresult, "bbillable");
					if ( ValHelper.getInstance().isNotNullAndEmpty(svalue))
					{
						sbillable = svalue;
					}
					detail.setBillable(sbillable);
					detail.setBudgetamount(ValHelper.getInstance().getValue(dtlresult, "budgetamount"));
					detail.setBudgetdays(ValHelper.getInstance().getValue(dtlresult, "budgetdays"));
					detail.setPercentageload(ValHelper.getInstance().getValue(dtlresult, "percentageload"));
					detail.setFromdate(ValHelper.getInstance().getValue(dtlresult, "fromdate"));
					detail.setEnddate(ValHelper.getInstance().getValue(dtlresult, "todate"));
					detail.setHiringdate(ValHelper.getInstance().getValue(dtlresult, "hiringdate"));
					detail.setTermdate(ValHelper.getInstance().getValue(dtlresult, "termdate"));
					info.getDetaillist().add(detail);
				}
			}
		}
		catch(SQLException se)
		{
			log.error("selectProAssign() -- caught SQLException : ", se);
			throw new NsiEventException("selectProAssign() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("selectProAssign() -- caught Exception : ", ex);
			throw new NsiEventException("selectProAssign() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
	}
	private void updateProAssign() throws NsiEventException
	{
		List<ProAssignDetail> detailList=info.getDetaillist();
		if(!detailList.isEmpty()) 
		{
			try
			{
				int size = detailList.size();
				for (int i = 0; i < size; i++)
				{
					ProAssignDetail detail = detailList.get(i);
					String sSql = "";
					String projID = detail.getProjectid().trim();
					if(projectid.trim().equalsIgnoreCase(projID))
					{
						sSql = prepareSql(detail,projID);
					}
					if( ValHelper.getInstance().isNotNullAndEmpty(sSql))
					{
						try
						{
							getConnection();
							Statement stm = conn.createStatement();
							int resultCount = sdrc.executeUpdate(stm, sSql);
							if(resultCount != 1) throw new NsiEventException("ERROR update project from T_PROJ_ASSIGN!  resultCount = " + resultCount);
						}
						catch(SQLException se)
						{
							log.error("updateProAssign() -- caught SQLException : ", se);
							throw new NsiEventException("updateProAssign() -- caught SQLException : ", se);
						}
						catch(Exception ex)
						{
							log.error("updateProAssign() -- caught Exception : ", ex);
							throw new NsiEventException("updateProAssign() -- caught Exception : ", ex);
						}
						finally
						{
							closeConnection();
						}
					}
				}
			}
			catch(Exception ex)
			{
				log.error("updateProAssign() -- caught Exception : ", ex);
				throw new NsiEventException("updateProAssign() -- caught Exception : ", ex);
			}
		}
	}
	private String prepareSql(ProAssignDetail detail, String projID)
	{
		String sql="";
		String proassid = detail.getProjassignid().trim();
		String resourceid = detail.getResourceid().trim();
		String projroleid = detail.getProjroleid().trim();
		if( ValHelper.getInstance().isNotNullAndEmpty(proassid))
		{
			sql = "update t_proj_assign set projectid=" + projID + ", " + "resourceid=" + resourceid + ", " + 
					"projroleid=" + projroleid + ", " + "chargetypeid=" + detail.getChargetypeid().trim() + ", " + 
					"moduserid=" + modifyuser.trim() + ", " + "bbillable='" + detail.getBillable().trim() + "', " + 
					"percentageload=" + detail.getPercentageload().trim() + ", " + "budgetdays=" + detail.getBudgetdays().trim() + ", " + 
					"budgetamount=" + detail.getBudgetamount().trim() + ", " + 
					"fromdate=to_date( '" + detail.getFromdate().trim() + "', 'MON-DD-YYYY' ), " + 
					"todate=to_date( '" + detail.getEnddate().trim() + "', 'MON-DD-YYYY' ) " + 
					"where projassignid=" + proassid + "";
		}
		else
		{
			if(( ValHelper.getInstance().isNotNullAndEmpty(resourceid)) &&( ValHelper.getInstance().isNotNullAndEmpty(projroleid)))
			{
				sql = "insert into t_proj_assign (projassignid,moduserid,projectid,resourceid,projroleid,chargetypeid,fromdate,todate,bbillable,percentageload,budgetdays,budgetamount ) " + 
						"values ( nextval('projassignid_seq')," + modifyuser.trim() + "," + projID + "," + resourceid + "," + projroleid+ "," + 
						detail.getChargetypeid().trim() + "," + "to_date( '" + detail.getFromdate().trim() + "', 'MON-DD-YYYY' )," + 
						"to_date( '" + detail.getEnddate().trim() + "', 'MON-DD-YYYY' ), " + "'" + detail.getBillable().trim()+ "', " + 
						detail.getPercentageload().trim()+ "," + detail.getBudgetdays().trim() + "," + detail.getBudgetamount().trim() + " )";
			}
		}
		return sql;
	}
}