package com.nsi.components.resource;

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.components.util.ResourceUtil;
import com.nsi.control.exceptions.NsiEventException;
import com.nsi.util.ConvertDBstr;
import com.nsi.util.ValHelper;

/**
 * @author Chris Ye, created on Oct 2, 2008
 *
 * ResourceSrc
 */
public class ResourceSrc extends NsiAbstractSrc
{
	private static Log log = LogFactory.getLog(ResourceSrc.class);
	private String resourceid;
	private ResourceInfo info;
	/**
	 * constructor of ResourceSrc 
	 * @throws NsiEventException
	 */
	public ResourceSrc() throws NsiEventException
	{
		super();
		resourceid = "";
		info = new ResourceInfo();
	}
	public String getResourceid()
	{
		return resourceid;
	}
	public void setResourceid(String resourceid)
	{
		this.resourceid = resourceid;
	}
	public ResourceInfo getInfo()
	{
		return info;
	}
	public void setInfo(ResourceInfo info)
	{
		this.info = info;
	}
	public void create() throws NsiEventException
	{
		insertResource();
	}
	public void load() throws NsiEventException
	{
		selectResource();
	}
	public void store() throws NsiEventException
	{
		updateResource();
	}
	private void insertResource() throws NsiEventException
	{
		String lName = ConvertDBstr.getInstance().convertDBstr(info.getLastname());
		String fName = ConvertDBstr.getInstance().convertDBstr(info.getFirstname());
		String mName = ConvertDBstr.getInstance().convertDBstr(info.getMiddlename());
		String adrs = ConvertDBstr.getInstance().convertDBstr(info.getAddress());
		String sCity = ConvertDBstr.getInstance().convertDBstr(info.getCity());
		String sProvince = ConvertDBstr.getInstance().convertDBstr(info.getProvince());
		String addUserProfile = info.getAdduserprofile();
		String sEndDate = "''";
		if( ValHelper.getInstance().isNotNullAndEmpty(info.getEnddate()))
		{
			sEndDate = "to_date('" + info.getEnddate() + "','MON-DD-YYYY')";
		}
		String sSql = 	"insert into t_resource ( resourceid, moduserid, employeenum, lastname, firstname, middlename, address, city, province, postalcode, homephone, officephone, pager, mobilephone, fax, email, empltypeid, bactive, remarks, startdate, enddate, agencyid ) " +
							"values ( "+ resourceid.trim() + ", " + modifyuser.trim() + ", " + info.getEmployeenum()+ ", " + 
							"'" + lName.trim() + "', "	+ "'"	+ fName.trim()	+ "', " + "'"	+ mName.trim() + "', " + 
							"'" + adrs.trim() + "', " + "'" + sCity.trim() + "', " + "'" + sProvince.trim() + "', " + 
							"'" + info.getPostalcode() + "', " + "'" + info.getHomephone() + "', " + 
							"'" + info.getOfficephone() + "', " + "'" + info.getPager() + "', " + 
							"'" + info.getMobilephone() + "', " + "'" + info.getFax() + "', " + 
							"'" + info.getEmail() + "', " + info.getEmpltypeid() + ", " + 
							"'" + info.getActive() + "', '" + info.getRemarks() + "', " + 
							"to_date('" + info.getStartdate() + "', 'MON-DD-YYYY'), " + sEndDate + ", " + info.getAgencyid() + ")";
		try
		{
			getConnection();
			Statement stmt = conn.createStatement();
			int resultCount = sdrc.executeUpdate(stmt, sSql);
			if(resultCount != 1)
			{
				throw new NsiEventException("ERROR insert resource into nsi_resource!!  resultCount = " + resultCount);

			}
			if("T".equals(addUserProfile))
			{
				insertUserProfile(info.getActive());
			}
			ResourceUtil.getInstance().mailling(info);
		}
		catch(SQLException se)
		{
			log.error("insertResource() -- caught SQLException : ", se);
			throw new NsiEventException("insertResource() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("insertResource() -- caught Exception : ", ex);
			throw new NsiEventException("insertResource() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
	}
	private void insertUserProfile(String bActive) throws NsiEventException
	{
		String rsid = info.getResourceid();
		String loginName = info.getLoginname();
		String modUserID = info.getModuserid();
		String password = "netstar";
		String userRoleID = "2";
		String sSql = 	"insert into t_user ( userid, loginname, password, bactive, resourceid, userroleid, moduserid ) " + 
							"values ( nextval('userid_seq'), '" + loginName + "', " + "'" + password + "', " + 
							"'" + bActive + "', " + rsid + ", " + userRoleID + ", " + modUserID + " )";
		try
		{
			getConnection();
			Statement stm = conn.createStatement();
			sdrc.executeUpdate(stm, sSql);
		}
		catch(SQLException se)
		{
			log.error("insertUserProfile() -- caught SQLException : ", se);
			throw new NsiEventException("insertUserProfile() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("insertUserProfile() -- caught Exception : ", ex);
			throw new NsiEventException("insertUserProfile() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}

	}
	private void updateResource() throws NsiEventException
	{
		String lName = ConvertDBstr.getInstance().convertDBstr(info.getLastname());
		String fName = ConvertDBstr.getInstance().convertDBstr(info.getFirstname());
		String mName = ConvertDBstr.getInstance().convertDBstr(info.getMiddlename());
		String adrs = ConvertDBstr.getInstance().convertDBstr(info.getAddress());
		String sCity = ConvertDBstr.getInstance().convertDBstr(info.getCity());
		String sProvince = ConvertDBstr.getInstance().convertDBstr(info.getProvince());
		String sRemarks = ConvertDBstr.getInstance().convertDBstr(info.getRemarks());
		String sEndDate = "''";
		if( ValHelper.getInstance().isNotNullAndEmpty(info.getEnddate()))
		{
			sEndDate = "to_date('" + info.getEnddate() + "','MON-DD-YYYY')";
		}
		String sSql = 	"update t_resource set " + 
							"moduserid=" + modifyuser.trim() + ", " + "lastname='" + lName.trim() + "', " + 
							"firstname='" + fName.trim() + "', " + "middlename='" + mName.trim() + "', " + 
							"address='" + adrs.trim() + "', " + "city='" + sCity.trim() + "', " + 
							"province='" + sProvince.trim() + "', " + "postalcode='" + info.getPostalcode() + "', " + 
							"homephone='" + info.getHomephone() + "', " + "officephone='" + info.getOfficephone() + "', " + 
							"pager='" + info.getPager() + "', " + "mobilephone='" + info.getMobilephone() + "', " + 
							"fax='" + info.getFax() + "', " + "email = '" + info.getEmail() + "', " + 
							"empltypeid= " + info.getEmpltypeid() + ", " + "employeenum=" + info.getEmployeenum() + ", " + 
							"bactive='" + info.getActive() + "', remarks='" + sRemarks + "', " + 
							"startdate=to_date('" + info.getStartdate() + "','MON-DD-YYYY'), enddate=" + sEndDate + ", " + 
							"agencyid=" + info.getAgencyid() + " where resourceid=" + resourceid + "";
		try
		{
			getConnection();
			Statement stm = conn.createStatement();
			int resultCount = sdrc.executeUpdate(stm, sSql);
			if(resultCount != 1) throw new NsiEventException("ERROR update resource from nsi_resource!!  resultCount = " + resultCount);
		}
		catch(SQLException se)
		{
			log.error("updateResource() -- caught SQLException : ", se);
			throw new NsiEventException("updateResource() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("updateResource() -- caught Exception : ", ex);
			throw new NsiEventException("updateResource() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
	}
	private void selectResource() throws NsiEventException
	{
		String sSql = 	"select resourceid,moduserid,employeenum,lastname,firstname,middlename,address,city," +
							"province,postalcode,homephone,officephone,pager,mobilephone,fax,email,remarks,empltypeid,bactive," + 
							"to_char(startdate, 'MON-DD-YYYY') as startdate, to_char(enddate, 'MON-DD-YYYY') as enddate, agencyid " +
							"from t_resource where resourceid =" + resourceid;
		try
		{
			getConnection();
			Map<String,String> result = sdrc.retrieveSingleRow(conn, sSql);
			if(!result.isEmpty())
			{
				info.reset();
				resourceid = ValHelper.getInstance().getValue(result, "resourceid");
				info.setResourceid(resourceid);
				modifyuser = ValHelper.getInstance().getValue(result, "moduserid");
				info.setEmployeenum(ValHelper.getInstance().getValue(result, "employeenum"));
				info.setLastname(ValHelper.getInstance().getValue(result, "lastname"));
				info.setFirstname(ValHelper.getInstance().getValue(result, "firstname"));
				info.setMiddlename(ValHelper.getInstance().getValue(result, "middlename"));
				info.setAddress(ValHelper.getInstance().getValue(result, "address"));
				info.setCity(ValHelper.getInstance().getValue(result, "city"));
				info.setProvince(ValHelper.getInstance().getValue(result, "province"));
				info.setPostalcode(ValHelper.getInstance().getValue(result, "postalcode"));
				info.setHomephone(ValHelper.getInstance().getValue(result, "homephone"));
				info.setOfficephone(ValHelper.getInstance().getValue(result, "officephone"));
				info.setPager(ValHelper.getInstance().getValue(result, "pager"));
				info.setMobilephone(ValHelper.getInstance().getValue(result, "mobilephone"));
				info.setFax(ValHelper.getInstance().getValue(result, "fax"));
				info.setEmail(ValHelper.getInstance().getValue(result, "email"));
				info.setRemarks(ValHelper.getInstance().getValue(result, "remarks"));
				info.setEmpltypeid(ValHelper.getInstance().getValue(result, "empltypeid"));
				info.setActive(ValHelper.getInstance().getValue(result, "bactive"));
				String startdt = ValHelper.getInstance().getValue(result, "startdate");
				if( ValHelper.getInstance().isNullOrEmpty(startdt))
				{
					startdt = "JAN-01-2007";
				}
				info.setStartdate(startdt);
				info.setEnddate(ValHelper.getInstance().getValue(result, "enddate"));
				String agencyid = ValHelper.getInstance().getValue(result, "agencyid");
				if( ValHelper.getInstance().isNullOrEmpty(agencyid))
				{
					agencyid = "0";
				}
				info.setAgencyid(agencyid);
			}
		}
		catch(SQLException se)
		{
			log.error("selectResource() -- caught SQLException : ", se);
			throw new NsiEventException("selectResource() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("selectResource() -- caught Exception : ", ex);
			throw new NsiEventException("selectResource() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
	}
	public boolean resouceExists() throws NsiEventException
	{
		boolean flag = false;
		String sSql = "select resourceid from t_resource where resourceid =" + resourceid;
		try
		{
			getConnection();
			List<Map<String,String>> result = sdrc.executeRetrieve(conn, sSql);
			if(!result.isEmpty()) flag = true;
		}
		catch(SQLException se)
		{
			log.error("resouceExists() -- caught SQLException : ", se);
			throw new NsiEventException("resouceExists() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("resouceExists() -- caught Exception : ", ex);
			throw new NsiEventException("resouceExists() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
		return flag;
	}
	public boolean isResouceExists( String inresourceid ) throws NsiEventException
	{
		boolean flag = false;
		String sSql = "select resourceid from t_resource where resourceid =" + inresourceid;
		try
		{
			getConnection();
			List<Map<String,String>> result = sdrc.executeRetrieve(conn, sSql);
			if(!result.isEmpty()) 
			{
				resourceid = inresourceid;
				flag = true;
			}
		}
		catch(SQLException se)
		{
			log.error("resouceExists() -- caught SQLException : ", se);
			throw new NsiEventException("resouceExists() -- caught SQLException : ", se);
		}
		catch(Exception ex)
		{
			log.error("resouceExists() -- caught Exception : ", ex);
			throw new NsiEventException("resouceExists() -- caught Exception : ", ex);
		}
		finally
		{
			closeConnection();
		}
		return flag;
	}
}