/*******************************************    
Title : DocTranPostSaveEJB
    Date  : 03/08/12
    Author: CPandey

 ********************************************************/
package ibase.webitm.ejb.wms;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.InvAllocTraceBean;
import ibase.webitm.ejb.dis.StockUpdate;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;

import java.io.*;
import java.net.InetAddress;
import java.rmi.RemoteException;
import java.sql.*;
import java.sql.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import org.w3c.dom.*;

import javax.ejb.*;
import javax.naming.InitialContext;
@javax.ejb.Stateless
public class DockTranPos extends ValidatorEJB implements DockTranPosLocal, DockTranPosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	//Changed By Pragyan 09-MAY-14 to Take Local connection 
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{

		System.out.println( "Calling DockTranPos.........................................." );
		String forcedFlag = "false" ;
		String errString = "";
		String sql = "";
		String pickOrder = "";
		String pickType = "";
		String palletNo = "";
		String itemCode = "",siteCode = "",locCode = "",lotNo = "",lotSl = "",locCodeSys = "",
				saleOrder = "",lineNoSord = "",ptcn="";
		double quantity = 0.0,noArt = 0.0;
		int lineNo = 0,lineNumber = 0;
		int updCnt = 0;
		Document dom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		java.util.Date currDate1 = new java.util.Date();
		java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
		StockUpdate stkUpdate = null;
		String suppCodeMfg = "",dimension = "",locCodeTo = "", chguser = "",chgdate = "",chgterm = "",
				employeeCode = "",conmfirmed = "",waveId = "",tranIdissue = "",docktranID = "";
		String updateSql = "";
		String chgTerm ="";
		boolean isError = false;
		boolean isRecordexist = false;
		String empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );
		DistCommon distCommon = new DistCommon();
		PreparedStatement pstmt = null,pstmtUpd = null;
		ResultSet rs = null;
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		java.util.Date confDate = new java.util.Date();
		DockTranConf dockconf = new DockTranConf();
	/*	Connection conn1 = null;
		ConnDriver connDriver = null;*/
		try
		{
			/*connDriver = new ConnDriver();
			conn1 = connDriver.getConnectDB("DriverValidator");*/
			String applDateFormat = genericUtility.getApplDateFormat();
			String dbDateFormat = genericUtility.getDBDateFormat();
			SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
			String currDateStr = sdf.format(currDate1);
			java.sql.Timestamp tranDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , applDateFormat, dbDateFormat ) + " 00:00:00.0") ;
			dom = genericUtility.parseString(domString);
			chguser = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginSite");
			InetAddress ownIP=InetAddress.getLocalHost();
			chgTerm = ownIP.getHostAddress();
			currDate = new Timestamp(System.currentTimeMillis());
			palletNo = genericUtility.getColumnValue("pallet_no", dom, "1", "1");
			System.out.println("Location code to ->>["+locCodeSys+"]");
			locCodeTo = genericUtility.getColumnValue("loc_code__to", dom, "2" );
			System.out.println("palert no from generic utility ["+palletNo+"]  locCodeTo [" + locCodeTo  + "]");
			//conn.setAutoCommit(false);
			sql = "SELECT HDR1.TRAN_ID TRAN_ID,HDR.PICK_TYPE,HDR1.PICK_ORDER FROM PICK_ORD_HDR HDR,PICK_ISS_HDR HDR1 WHERE HDR.PICK_ORDER = HDR1.PICK_ORDER AND HDR1.PALLET_NO = ? AND (CASE WHEN HDR1.TRF_STATUS IS NULL THEN 'N' ELSE HDR1.TRF_STATUS END) = ?"; //PICK_ISS_HDR
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, palletNo);
			pstmt.setString( 2, "N");
			rs = pstmt.executeQuery();

			if(rs.next())
			{
				tranId = rs.getString("tran_id")==null?"":rs.getString("tran_id");
				pickType = rs.getString("pick_type")==null?"":rs.getString("pick_type");
				pickOrder = rs.getString("pick_order")==null?"":rs.getString("pick_order");
				System.out.println("pick order and pick type----  ["+palletNo+"] and pickType is ----["+pickType+"]");
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			sql = "UPDATE PICK_ISS_DET SET LOC_CODE__TO = ? WHERE TRAN_ID = ?  ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, locCodeTo);
			pstmt.setString( 2, tranId);
			pstmt.executeUpdate();
			pstmt.close(); pstmt = null;

			docktranID = generateTranId( "w_dock_tran_upd",siteCode,conn );

			System.out.println("generatetranID:"+docktranID);

			/*	sql =     " SELECT R.LINE_NO LINE_NO, R.ITEM_CODE ITEM_CODE, R.SITE_CODE SITE_CODE, R.LOC_CODE LOC_CODE, "
					+ " R.LOT_NO LOT_NO, R.LOT_SL LOT_SL, ( R.QUANTITY - CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END) AS QUANTITY, R.NO_ART NO_ART, "
					+ " R.LOC_CODE__TO LOC_CODE__TO, O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, O.PICK_ORDER,H.PICK_TYPE  "
					+ " FROM PICK_ISS_DET R,  PICK_ORD_DET O,PICK_ORD_HDR H" 
					+ " WHERE R.TRAN_ID = ? "
					+ " AND R.PICK_ORDER = O.PICK_ORDER "   
					+ " AND H.PICK_ORDER = O.PICK_ORDER"  
					+ " AND O.LINE_NO = R.LINE_NO__ORD " 
					+" AND ( R.QUANTITY - CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END) > 0 "
					+" AND R.LOC_CODE <> R.LOC_CODE__TO ";*/

			sql =  " SELECT R.LINE_NO LINE_NO, R.ITEM_CODE ITEM_CODE, R.SITE_CODE SITE_CODE, R.LOC_CODE LOC_CODE, " + 
					" R.LOT_NO LOT_NO, R.LOT_SL LOT_SL, ( R.QUANTITY - CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END) AS QUANTITY, " + 
					" R.NO_ART NO_ART, R.LOC_CODE__TO LOC_CODE__TO, R.LOC_CODE__SYS LOC_CODE__SYS, O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, "+ 
					" O.PICK_ORDER, H.PICK_TYPE, S.CONFIRMED, S.CONF_DATE, S.PALLET_NO, S.TRAN_ID, "+
					" S.EMP_CODE__APRV, WT.PTCN, WT.WAVE_ID, S.CHG_USER, S.CHG_DATE, S.CHG_TERM  "+
					" FROM PICK_ISS_DET R, PICK_ORD_DET O, PICK_ORD_HDR H, PICK_ISS_HDR S, WAVE_TASK_DET WT, WAVE_TASK W "+  
					" WHERE R.TRAN_ID = ? "+
					" AND R.PICK_ORDER = O.PICK_ORDER "+
					" AND H.PICK_ORDER = O.PICK_ORDER "+
					" AND O.LINE_NO = R.LINE_NO__ORD "+
					" AND W.WAVE_ID = WT.WAVE_ID " +
					" AND S.PICK_ORDER = R.PICK_ORDER " +
					//changed by sankara on 18/09/14 added join for one pick multiple pallet.
					" AND S.TRAN_ID = R.TRAN_ID " +
					" AND S.PICK_ORDER = WT.REF_ID "+
					" AND ( R.QUANTITY - CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END) > 0 "+
					" AND R.LOC_CODE <> R.LOC_CODE__TO ";
			pstmt = conn.prepareStatement(sql);           
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();     
			System.out.println("isRecordexist::"+isRecordexist);
			while(rs.next())
			{
				System.out.println("isRecordexist0::"+isRecordexist);
				itemCode = rs.getString( "ITEM_CODE" );
				siteCode = rs.getString( "SITE_CODE" );
				locCode = rs.getString( "LOC_CODE" );
				lotNo = rs.getString( "LOT_NO" );
				lotSl = rs.getString( "LOT_SL" );
				quantity = rs.getDouble( "QUANTITY" );
				noArt = rs.getDouble( "NO_ART" );
				locCodeTo = rs.getString( "LOC_CODE__TO" );
				saleOrder = rs.getString( "SALE_ORDER" );
				lineNoSord = rs.getString( "LINE_NO__SORD" );
				pickOrder = rs.getString( "PICK_ORDER" );
				lineNo = rs.getInt( "LINE_NO" );
				pickType = rs.getString( "PICK_TYPE" );
				locCodeSys = rs.getString( "LOC_CODE__SYS" );
				employeeCode  = rs.getString( "EMP_CODE__APRV" );
				conmfirmed =  rs.getString( "CONFIRMED" );
				confDate = rs.getDate( "CONF_DATE" );
				ptcn =   rs.getString( "PTCN" );
				waveId  =  rs.getString( "WAVE_ID" );
				palletNo  =  rs.getString( "PALLET_NO" );
				tranIdissue  =  rs.getString( "TRAN_ID" );
				System.out.println("pick type from hdr->> ["+pickType+"]");
				lineNumber++;
				System.out.println("confDate:"+confDate);
				System.out.println("confDate1:"+(Date)confDate);

				 if(isRecordexist == false)
				 {
					 updateSql = " INSERT INTO DOCK_TRAN (TRAN_ID, SITE_CODE, PALLET_NO, TRAN_ID__ISS, EMP_CODE , " +
								" PTCN, WAVE_ID,PICK_TYPE,CHG_DATE, CHG_USER, CHG_TERM) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";

						pstmtUpd = conn.prepareStatement(updateSql);
						pstmtUpd.setString(1, docktranID);
						pstmtUpd.setString(2, siteCode);
						pstmtUpd.setString(3, palletNo);
						pstmtUpd.setString(4, tranIdissue);
						pstmtUpd.setString(5, employeeCode);
						pstmtUpd.setString(6, ptcn);
						pstmtUpd.setString(7, waveId);
						pstmtUpd.setString(8, pickType);
						pstmtUpd.setTimestamp(9,currDate);
						pstmtUpd.setString(10, chguser);
						pstmtUpd.setString(11, chgTerm);	
						updCnt = pstmtUpd.executeUpdate();
						if(updCnt  > 0)
						{
							System.out.println("record inserted in dock_tran["+updCnt+"]");
						}
						System.out.println("Changed By 02/05/13 Pragyan sordalloc inserted  updCnt [" + updCnt + "]");
						pstmtUpd.close();pstmtUpd = null;	
				 }
				 
				updateSql = " INSERT INTO DOCK_TRAN_DET ( TRAN_ID, LINE_NO,LINE_NO__ISS,PICK_ORDER,LINE_NO__PICK,ITEM_CODE,LOC_CODE,LOT_NO,LOT_SL,NO_ART,QUANTITY," +
						    " LOC_CODE__TO,LOC_CODE__SYS,SALE_ORDER,LINE_NO__SORD) VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,? ) ";

				pstmtUpd = conn.prepareStatement(updateSql);
				pstmtUpd.setString(1, docktranID);
				pstmtUpd.setInt(2, lineNumber);
				pstmtUpd.setInt(3, lineNo);
				pstmtUpd.setString(4, pickOrder);
				pstmtUpd.setInt(5, lineNo);
				//pstmtUpd.setString(6, pickType);
				pstmtUpd.setString(6, itemCode);
				pstmtUpd.setString(7, locCode);
				pstmtUpd.setString(8, lotNo);
				pstmtUpd.setString(9, lotSl);
				pstmtUpd.setDouble(10, noArt);
				pstmtUpd.setDouble(11, quantity);
				pstmtUpd.setString(12, locCodeTo);	
				pstmtUpd.setString(13, locCodeSys);	
				pstmtUpd.setString(14, saleOrder);	
				pstmtUpd.setString(15, lineNoSord);	
				updCnt = pstmtUpd.executeUpdate();
				if(updCnt  > 0)
				{
					System.out.println("record inserted in dock_tran_det["+updCnt+"]");
				}
				pstmtUpd.close();pstmtUpd = null;	
				isRecordexist = true;
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			System.out.println("isRecordexist1::"+isRecordexist);
			/*updateSql = " INSERT INTO DOCK_TRAN (TRAN_ID, SITE_CODE, PALLET_NO, TRAN_ID__ISS, EMP_CODE , " +
					" PTCN, WAVE_ID, CHG_DATE, CHG_USER, CHG_TERM) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) ";

			pstmtUpd = conn.prepareStatement(updateSql);
			pstmtUpd.setString(1, tranID);
			pstmtUpd.setString(2, siteCode);
			pstmtUpd.setString(3, palletNo);
			pstmtUpd.setString(4, tranIdissue);
			pstmtUpd.setString(5, employeeCode);
			pstmtUpd.setString(6, ptcn);
			pstmtUpd.setString(7, waveId);
			pstmtUpd.setTimestamp(8,currDate);
			pstmtUpd.setString(9, chguser);
			pstmtUpd.setString(10, chgTerm);	
			updCnt = pstmtUpd.executeUpdate();
			if(updCnt  > 0)
			{
				System.out.println("record inserted in dock_tran["+updCnt+"]");
			}
			System.out.println("Changed By 02/05/13 Pragyan sordalloc inserted  updCnt [" + updCnt + "]");
			pstmtUpd.close();pstmtUpd = null;	*/
			
			AppConnectParm appConnect = new AppConnectParm();                   
			Properties p = appConnect.getProperty();
			InitialContext ctx = new InitialContext(p);
			DockTranConfLocal docktranConfLocal = null;
			docktranConfLocal = (DockTranConfLocal)ctx.lookup("ibase/DockTranConf/local"); 
			errString = docktranConfLocal.confirm(domString,docktranID,editFlag,xtraParams,conn );
			System.out.println(" ************ errString ******** ["+errString+"]");		

			//errString = dockconf.confirm(domString,tranID,editFlag,xtraParams,conn );
			if(errString == null || errString.trim().length() == 0 )
			{			 
				updateSql = " UPDATE DOCK_TRAN SET CONFIRMED = ?, CONF_DATE = ? WHERE TRAN_ID = ? ";
				pstmtUpd = conn.prepareStatement(updateSql);
				pstmtUpd.setString(1, "Y");
				pstmtUpd.setTimestamp(2,currDate);
				pstmtUpd.setString(3, docktranID);
				updCnt = pstmtUpd.executeUpdate();
				if(updCnt  > 0)
				{
					System.out.println("updte record in dock_tran["+updCnt+"]");
				}
				pstmtUpd.close();pstmtUpd = null;			
			}
		}
		catch(Exception e)
		{
			System.out.println("Exception before rollback "+e.getMessage());
			try
			{
				isError = true;
				conn.rollback();
			}
			catch (SQLException e1) 
			{

				e1.printStackTrace();
			}

			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				/*if( !isError  )
				{
					System.out.println("commmit");
					conn.commit(); 
				}*/
				if ( isError )
				{
					System.out.println("rollback");
					conn.rollback();
				}
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if( pstmtUpd != null )
				{
					pstmtUpd.close();
					pstmtUpd = null;
				}
			}
			catch(Exception e)
			{
				System.err.println("Exception :PickIssPos :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;	
	}
	private 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 = "";
		String paySiteCode = "";
		String effectiveDate = "";
		java.sql.Timestamp currDate = null;
		java.sql.Date effDate = 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 checkNull(tranId);
	}
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		return input;
	}
}



