/**
* PURPOSE : To confirmed the Transaction for packing component.
* AUTHOR : BALU 
*/ 

package ibase.webitm.ejb.wms;

import ibase.utility.EMail;
import ibase.utility.CommonConstants;
import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.system.config.*;
import ibase.webitm.utility.ITMException;

import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.util.*;
import java.text.*;
import javax.ejb.*;
import javax.naming.InitialContext;
import javax.ejb.Stateless; 
import org.w3c.dom.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.StreamSource;
import javax.xml.parsers.*;

@Stateless 
public class PackingConf extends ActionHandlerEJB implements PackingConfLocal, PackingConfRemote 
{
    /**
	 * The public method is used for confirming the service complaint transaction
	 * Returns confirmation message on successfull confirm otherwise returns error message
	 * @param tranID is the transaction id to be confirmed
	 * @param xtraParams contais additional information such as loginEmpCode,loginCode,chgTerm etc
	 * @param forcedFlag (true or false)
	 */
	// added by chaitali for overloading of confirm method - 03/12/11 
	public String confirm( String tranID, String xtraParams, String forcedFlag ) throws RemoteException,ITMException
	{
		String retString = "";
		Connection conn = null;
		/*commented by chaitali on 03 dec 2011
		ConnDriver connDriver = new ConnDriver();*/		
		boolean isConn= false;
		try
		{
			/*commentd by chaitali on 03 dec 2011
			conn = connDriver.getConnectDB("DriverITM");
			conn.setAutoCommit(false);
			connDriver = null;*/
			retString = this.confirm( tranID,xtraParams, forcedFlag ,conn ,isConn);
			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"]");
			}
		}
		catch( Exception exception )
		{
			System.out.println("Exception in [InvHoldConfEJB] getCurrdateAppFormat " + exception.getMessage());
		}
		return retString;
	}
	public String confirm(String tranID, String xtraParams, String forcedFlag, Connection conn, boolean connStatus) throws RemoteException, ITMException
	{
		String confirmed = "";
		String sql = "";		
		String retString = "";
		String loginEmpCode = "", ptcn = "";
		
		boolean isError = false;
		int updCnt = 0;
		
		/*commented by chaitali 03/12/11
		 * Connection conn = null;
		 */
		PreparedStatement pstmtUpd = null;
		PreparedStatement pstmtSql = null;
		ResultSet rs = null;
		//added by chaitali on 03/12/11
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		
		try
		{
			//added by chaitali on 03/12/11
			if ( conn == null )
			{
				ConnDriver connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				connDriver = null;
				conn.setAutoCommit(false);
				connStatus = true;
			}

			GenericUtility genericUtility = GenericUtility.getInstance();
			loginEmpCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
			/*commented by chaitali 0n 03 dec 2011
			 * ConnDriver connDriver = new ConnDriver();
			 * conn = connDriver.getConnectDB("DriverITM");
		     * connDriver = null;
		     * conn.setAutoCommit(false);
			 * ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
			 */

			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );

			sql = "SELECT CONFIRMED FROM PACK_HDR WHERE TRAN_ID = ?";
			pstmtSql = conn.prepareStatement(sql);
			pstmtSql.setString(1, tranID);
			rs = pstmtSql.executeQuery();
			if( rs.next() )
			{
				confirmed = checkNull( rs.getString("CONFIRMED") );
			}	
			rs.close();
			rs = null;
			pstmtSql.close();
			pstmtSql = null;

			if( confirmed.trim().equalsIgnoreCase("Y") )
			{
				retString = itmDBAccess.getErrorString("","PACKCONF","");
			}
			else
			{
				sql = "SELECT ptcn FROM wave_task_det WHERE ref_ID = ?";
				pstmtSql = conn.prepareStatement(sql);
				pstmtSql.setString(1, tranID);
				rs = pstmtSql.executeQuery();
				if( rs.next() )
				{
					ptcn = checkNull( rs.getString("ptcn") );
				}	
				rs.close();
				rs = null;
				pstmtSql.close();
				pstmtSql = null;

				sql = "UPDATE PACK_HDR SET CONFIRMED = 'Y', CONF_DATE = ?, EMP_CODE__APRV = ? WHERE TRAN_ID = ?";
				pstmtUpd = conn.prepareStatement( sql );	 
				pstmtUpd.setTimestamp(1, currDate ); 
				pstmtUpd.setString(2, loginEmpCode); 
				pstmtUpd.setString(3, tranID); 
				updCnt = pstmtUpd.executeUpdate();
				pstmtUpd.close();
				pstmtUpd = null;
				if ( updCnt > 0 )
				{
					System.out.println("PACK_HDR Updated Successfully ::"+updCnt);
					/*commented by chaitali on 02 dec 2011
					 * retString = itmDBAccess.getErrorString("","CONFSUCC","");
					 */
				}

				///update the status of packing task id in wave_task_det table
				/*//Changed by sumit on 03/08/12 update wave_status of wave_task_det
				//sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ? ";
				sql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' , WAVE_STATUS = 'V' WHERE REF_ID = ? ";
				pstmtUpd = conn.prepareStatement(sql);
				pstmtUpd.setString(1, tranID);	
				
				updCnt = pstmtUpd.executeUpdate();
				
				if( updCnt > 0 )
				{					
					System.out.println( updCnt + " rows updated successfully" );
				}	
				pstmtUpd.close();pstmtUpd = null;
				
				// Checking whether all task are done for particular wave_id and sale order, if yes then update 'C' wave_status of 'S-DSO' ref_ser start. 
				updCnt = 0;
				updCnt = waveStatusUpdate(tranID, conn);
				if( updCnt > 0)
				{
					System.out.println( updCnt + " row of wave_status updated successfully" );
				}
				//Checking whether all task are done for particular wave_id and sale order, if yes then update 'C' wave_status of 'S-DSO' ref_ser end.
 				*/
				
				System.out.println("Header updated successfully");
				ConsolidatToDoc conToDoc = new ConsolidatToDoc();
				//Changed by Rohan on 10-06-12 to add xtraParams in process as argument for hazmap report to get chg user
				//retString = conToDoc.process(ptcn, conn);
				retString = conToDoc.process(ptcn, conn,xtraParams);
				if (retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					return retString;
				}
				System.out.println("ret string :"+retString);			
				
			}
		}
		
		catch(Exception e)
		{
			isError = true;
			try
			{
				conn.rollback();
			}
			catch (Exception e1)
			{
			}
			e.printStackTrace();
			System.out.println("Exception ::"+e.getMessage());
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				//added by chaitali on 03/12/11
				if( !isError && connStatus )
				{
					conn.commit(); 
					retString = itmDBAccess.getErrorString("","CONFSUCC","");
				}
				else if (isError && connStatus)
				{
					conn.rollback();
				}
				if(rs != null)
				{
					rs.close();
					rs = null;					
				}
				if(pstmtUpd != null)
				{
					pstmtUpd.close();
					pstmtUpd = null;					
				}
				if( conn != null && ! conn.isClosed() && connStatus)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				System.out.println(e.getMessage());
				throw new ITMException(e);
			}
		}
		System.out.println("Returning Result ::"+retString);
		return retString;
	}
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		return input;
	}
	
	//Changed by sumit on 10/08/12 update wave_status after all task completed for wave_id and sale order start.
	private int waveStatusUpdate(String tranid, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		String waveId = "";
		String saleOrder = "";
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		int updateCount = 0;
		int firstCount = 0, secCount = 0;
		
		
		try
		{
			sql = "SELECT WAVE_ID, SALE_ORDER FROM PACK_HDR WHERE TRAN_ID = ? ";			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranid);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				waveId = checkNull(rs.getString("WAVE_ID"));
				saleOrder = checkNull(rs.getString("SALE_ORDER"));	
				
				sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, waveId);
			    pstmt1.setString(2, saleOrder);
			    rs1 = pstmt1.executeQuery();
			    if(rs1.next())
			    {
			    	firstCount = rs1.getInt("CONT");
			    }
			    rs1.close();rs1 = null;
			    pstmt1.close();pstmt1 = null;
				
			    //Changed by sumit on 19/03/13 changing condition
			    //sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_STATUS = 'V' AND WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
			    sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE STATUS = 'Y' AND WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, waveId);
			    pstmt1.setString(2, saleOrder);
			    rs1 = pstmt1.executeQuery();
			    if(rs1.next())
			    {
			    	secCount = rs1.getInt("CONT");
			    }
			    rs1.close();rs1 = null;
			    pstmt1.close();pstmt1 = null;
				
				
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			
			if( secCount == firstCount)
			{
				sql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER = 'S-DSP' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, waveId);
				pstmt.setString(2, saleOrder);
				updateCount = pstmt.executeUpdate();					
				pstmt.close();pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		return updateCount;
	}
	//Changed by sumit on 10/08/12 update wave_status after all task completed for wave_id and sale order start.
}
