/********************************************************
	Title     : WOTrackUpdatePos.java (M14BSUN003)
	Date      : 13-june-2014
	Developer : Kunal Mandhre
	Purpose   : Update the activities for Workorder
********************************************************/

package ibase.webitm.ejb.sys;

import ibase.utility.CommonConstants;
import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.utility.TransIDGenerator;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.SimpleDateFormat;

import org.w3c.dom.Document;
@javax.ejb.Stateless
public class WOTrackUpdatePos extends ValidatorEJB implements WOTrackUpdatePosLocal, WOTrackUpdatePosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		String errString = "";
		String sql = "";
		String userId = "", termId = "" ,siteCode = "";
		String refId = "",actCode = "" ,remarks = "",status = "",workorder = "",descr = "";
		int refLineNo = 0 , count = 0;
		java.util.Date tranDate = null,eventDate = null ,complDate = null;
		boolean isError = false;
		Document dom = null;
		PreparedStatement pstmt = null , pstmtInst = null;
        ResultSet rs = null;
        SimpleDateFormat sdf = null;

		try
		{
			GenericUtility genericUtility = GenericUtility.getInstance();
			sdf = new SimpleDateFormat(genericUtility.getApplDateFormat());
			userId = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");  
			termId =  genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginSiteCode");
			conn.setAutoCommit(false);
			System.out.println("Dom String =="+domString);
			dom = genericUtility.parseString(domString);
			
			refId = genericUtility.getColumnValueFromNode("ref_id", dom.getElementsByTagName("Detail1").item(0));
			refLineNo = Integer.parseInt( genericUtility.getColumnValueFromNode("ref_line_no", dom.getElementsByTagName("Detail1").item(0)).trim());
			tranDate = sdf.parse(genericUtility.getColumnValueFromNode("tran_date", dom.getElementsByTagName("Detail1").item(0)));
			workorder = genericUtility.getColumnValueFromNode("work_order", dom.getElementsByTagName("Detail1").item(0));
			status = genericUtility.getColumnValueFromNode("status_flag", dom.getElementsByTagName("Detail1").item(0));
			actCode = genericUtility.getColumnValueFromNode("event_no", dom.getElementsByTagName("Detail1").item(0));
			descr = genericUtility.getColumnValueFromNode("descr", dom.getElementsByTagName("Detail1").item(0));
			remarks = genericUtility.getColumnValueFromNode("remarks", dom.getElementsByTagName("Detail1").item(0));
			if(genericUtility.getColumnValueFromNode("event_date", dom.getElementsByTagName("Detail1").item(0)) != null)
			{
				eventDate = sdf.parse(genericUtility.getColumnValueFromNode("event_date", dom.getElementsByTagName("Detail1").item(0)));
			}
			else
			{
				eventDate = null;
			}
			if(genericUtility.getColumnValueFromNode("completion_date", dom.getElementsByTagName("Detail1").item(0)) != null)
			{
				complDate = sdf.parse(genericUtility.getColumnValueFromNode("completion_date", dom.getElementsByTagName("Detail1").item(0)));
			}
			else
			{
				complDate = null;
			}
			
			sql = " select count(*) from wo_track_update where ref_id = ?  and ref_line_no = ?   ";
			pstmt =  conn.prepareStatement(sql);
			pstmt.setString(1,refId);
			pstmt.setInt(2,refLineNo);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				count = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			
			if(count > 0)
			{
				sql = "update  wo_track_update  set  work_order = ?, status_flag = ?,event_no = ?,descr = ?,"
			        +"event_date = ?,completion_date = ?,remarks = ?,chg_date = ?,chg_term = ?,chg_user = ?  where ref_id = ?  and ref_line_no = ? " ;
				pstmtInst = conn.prepareStatement(sql);
				pstmtInst.setString(1,workorder);
				pstmtInst.setString(2,status);
				pstmtInst.setString(3,actCode);
				pstmtInst.setString(4,descr);
				if(eventDate != null)
				{
					pstmtInst.setDate(5, new java.sql.Date(eventDate.getTime()));
				}
				else
				{
					pstmtInst.setDate(5, null);
				}
				if(complDate != null)
				{
					pstmtInst.setDate(6, new java.sql.Date(complDate.getTime()));
				}
				else
				{
					pstmtInst.setDate(6, null);
				}
				pstmtInst.setString(7,remarks);
				pstmtInst.setDate(8, new java.sql.Date(new java.util.Date().getTime()));
				pstmtInst.setString(9,termId);
				pstmtInst.setString(10,userId);
				pstmtInst.setString(11,refId); 
				pstmtInst.setInt(12, refLineNo );
				System.out.println("update "+pstmtInst.executeUpdate());
				
				
			}
			else
			{
				tranId = generateTranId("w_wo_track_update",siteCode, conn);
				System.out.println("tranId="+tranId);
				sql = "insert into wo_track_update (tran_id,tran_date,ref_id,ref_line_no,work_order, status_flag,event_no,descr,"
				         +"event_date,completion_date,remarks,chg_date,chg_term,chg_user) values (?,?,?,?,?,?,?,?,?,?,    ?,?,?,?) " ;
					pstmtInst = conn.prepareStatement(sql);
					pstmtInst.setString(1,tranId);
					pstmtInst.setDate(2, new java.sql.Date(tranDate.getTime()));
					pstmtInst.setString(3,refId); 
					pstmtInst.setInt(4, refLineNo );
					pstmtInst.setString(5,workorder);
					pstmtInst.setString(6,status);
					pstmtInst.setString(7,actCode);
					pstmtInst.setString(8,descr);
					if(eventDate != null)
					{
						pstmtInst.setDate(9, new java.sql.Date(eventDate.getTime()));
					}
					else
					{
						pstmtInst.setDate(9, null);
					}
					if(complDate != null)
					{
						pstmtInst.setDate(10, new java.sql.Date(complDate.getTime()));
					}
					else
					{
						pstmtInst.setDate(10, null);
					}
					pstmtInst.setString(11,remarks);
					pstmtInst.setDate(12, new java.sql.Date(new java.util.Date().getTime()));
					pstmtInst.setString(13,userId);
					pstmtInst.setString(14,termId);
					System.out.println("insert "+pstmtInst.executeUpdate());
			}
			
		

			
		}
		catch(Exception e)
		{
			try
			{
				isError = true;
				if(conn != null)
				{
				conn.rollback();
				}
				System.out.println("Exception.. "+e.getMessage());
				e.printStackTrace();			
				throw new ITMException(e);
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
				throw new ITMException(e1);
			}
		}
		finally
		{
			try
			{		
				if(isError)
				{
					conn.rollback();
					System.out.println("Transaction rollback... ");
				}
				
				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
	public String generateTranId(String windowName ,String siteCode ,Connection conn)throws ITMException
	{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String selSql = "";
		String tranId = "";
		String tranSer = "";
		String keyString = "";
		String keyCol = "";
		String xmlValues = "";	

		java.sql.Timestamp currDate = null;
		GenericUtility genericUtility = GenericUtility.getInstance();

		try
		{
			SimpleDateFormat sdfAppl = new SimpleDateFormat(genericUtility.getApplDateFormat());
			currDate = new java.sql.Timestamp(System.currentTimeMillis());
			String currDateStr = sdfAppl.format(currDate);
			selSql = "SELECT KEY_STRING, TRAN_ID_COL, REF_SER FROM TRANSETUP WHERE TRAN_WINDOW = ? ";
			//System.out.println("selSql :"+selSql);
			pstmt = conn.prepareStatement(selSql);
			pstmt.setString( 1, windowName );
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				keyString = rs.getString("KEY_STRING");
				keyCol = rs.getString("TRAN_ID_COL");
				tranSer = rs.getString("REF_SER");
			}

			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("keyString :"+keyString);
			System.out.println("keyCol :"+keyCol);
			System.out.println("tranSer :"+tranSer);

			xmlValues ="<?xml version=\"1.0\" encoding=\"utf-8\"?><Root>";
			xmlValues = xmlValues + "<Header></Header>";
			xmlValues = xmlValues + "<Detail1>";
			xmlValues = xmlValues +        "<tran_id></tran_id>";
			xmlValues = xmlValues +        "<site_code>" + siteCode + "</site_code>";   
			xmlValues = xmlValues +        "<tran_date>" + currDateStr + "</tran_date>";
			xmlValues = xmlValues + "</Detail1></Root>";
			System.out.println("xmlValues  :["+xmlValues+"]");
			TransIDGenerator tg = new TransIDGenerator(xmlValues, "BASE", CommonConstants.DB_NAME);
			tranId = tg.generateTranSeqID(tranSer, keyCol, keyString, conn);
			System.out.println("tranId :"+tranId);
		}
		catch (SQLException ex)
		{
			System.out.println("Exception ::" +selSql+ ex.getMessage() + ":");
			ex.printStackTrace();
			throw new ITMException(ex);
		}
		catch (Exception e)
		{
			System.out.println("Exception ::" + e.getMessage() + ":");
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if (rs != null)
				{
					rs.close();
					rs = null;
				}
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			}

			catch(Exception e)
			{
			}
		}
		return tranId;
	}
}


