/**
 * PURPOSE : Post Save Logic implementation for Mater Pack component.
 * AUTHOR : Chaitali Parab 28/11/11
 */
package ibase.webitm.ejb.wms;

import ibase.webitm.utility.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.*;
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 ibase.webitm.ejb.wms.ReplIssConf;

@Stateless 
public class MasterPackPos extends ValidatorEJB implements MasterPackPosRemote, MasterPackPosLocal
{
	public String postSave( String domString, String tranId, String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		System.out.println("---->MasterPackPos EJB called");
		String errString = "";
		String sql = "";
		String sql1 = "";
		String tranID = "";

		PreparedStatement pstmt = null,pstmt2 = null;
		ResultSet rs = null;
		PreparedStatement pstmt1 = null;
		ResultSet rs1 = null,rs2 = null;
		//changed on 03/12/11
		boolean toCommit = false;
		String forcedFlag = "false";
		String confirmed = "",sql2 = "";;
		String outerCarton = "";
		String ptcn = "";
		String pickOrder = "";
		//Change by Rohan on 25-02-12 for define variables
		String outerCartonNo = "";
		Set cartonSet = new HashSet();//Changed by sumit on 05/05/13
		int cartonPack = 0;
		int cartonPick = 0;
		int cnt = 0;
		int count = 0;
		System.out.println("TRANID = "+tranId);
		String updateFlag = "";
		Document dom = null;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccessLocal = new ITMDBAccessEJB();
		PackingConf packingConf = new PackingConf();
		//changed by sankara on 04/02/14 for update status in carton_master
		String status = "";
		try
		{
			dom = genericUtility.parseString(domString);
			System.out.println("document string ["+domString+"]");
			NodeList detail3List = dom.getElementsByTagName("Detail3");
			if(detail3List != null && detail3List.getLength() > 0)
			{
				System.out.println("Detail3 length = "+detail3List.getLength());
				sql2 = "SELECT CONFIRMED FROM PACK_HDR WHERE TRAN_ID = ?";
				pstmt2 = conn.prepareStatement(sql2);
				pstmt2.setString(1, tranId);
				rs2 = pstmt2.executeQuery();
				if( rs2.next() )
				{
					confirmed = checkNull( rs2.getString("CONFIRMED") );
				}	
				rs2.close();
				rs2 = null;
				pstmt2.close();
				pstmt2 = null;

				if("Y".equalsIgnoreCase(confirmed.trim())) //added by Kunal on 21/08/12 
				{
					errString = itmDBAccessLocal.getErrorString("","PACKCONF","");
				}
				else
				{

					tranID = GenericUtility.getInstance().getColumnValueFromNode("tran_id", dom.getElementsByTagName("Detail2").item(0));
					pickOrder = GenericUtility.getInstance().getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));
					//Changed by Rohan on 25-02-13 for update master carton
					outerCartonNo = GenericUtility.getInstance().getColumnValueFromNode("outer_carton_no", dom.getElementsByTagName("Detail1").item(0));
					System.out.println("---->tran_id =[" +tranID+ "] and pick order no =["+pickOrder+"]"+"outerCartonNo"+outerCartonNo);
					//Commented By Mahesh Patidar on 16/06/12
					//			sql = "select count(distinct(carton_no)) as carton_pack from pack_det where tran_id = ?";
					//			pstmt =  conn.prepareStatement(sql);
					//			pstmt.setString(1, tranID);
					//			rs = pstmt.executeQuery();
					//			if(rs.next())
					//			{
					//				cartonPack = rs.getInt("carton_pack"); 
					//			}
					//			System.out.println("----->cartonPack =[" +cartonPack+ "]");
					//			pstmt.close();
					//			pstmt = null;
					//			rs.close();
					//			rs = null;

					/*--commented by gulzar on 12/14/2011 as it is not required
			sql ="select pick_ord_hdr.pick_order as pick_order" +
					" from pack_hdr pack_hdr, pick_ord_hdr pick_ord_hdr" +
					" where pack_hdr.SALE_ORDER = pick_ord_hdr.SALE_ORDER" +
					" and pack_hdr.WAVE_ID = pick_ord_hdr.WAVE_ID" +
					" and pack_hdr.tran_id = ? AND pick_ord_hdr.pick_type = 'M'";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,tranID);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				pickOrder = rs.getString("pick_order");
			}
			pstmt.close(); pstmt = null;
			rs.close(); rs = null;
					 */
					//End changes by gulzar on 12/14/2011

					//			sql = "select count(distinct(carton_no)) as carton_pick from pick_iss_det where pick_order = ?";
					//			pstmt =  conn.prepareStatement(sql);
					//			pstmt.setString(1, pickOrder);
					//			rs = pstmt.executeQuery();
					//			if(rs.next())
					//			{
					//				cartonPick = rs.getInt("carton_pick"); 
					//			}
					//			System.out.println("----->cartonPick =[" +cartonPick+ "]");
					//			pstmt.close();pstmt = null;
					//			rs.close();	rs = null;

					//			if( cartonPick != cartonPack )
					//			{
					//				errString = itmDBAccessLocal.getErrorString("","VTCARTNO3","");
					//				return errString;
					//			}
					//End comment By Mahesh Patidar
					//changed on 16/06/12 by Mahesh Patidar

					sql = "select ptcn from wave_task_det where ref_id = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, tranId);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						ptcn = rs.getString(1);
					}
					rs.close();
					rs = null;
					pstmt.close(); pstmt = null;

					NodeList node2= dom.getElementsByTagName("Detail3");
					System.out.println("****************%%%%%%%%%%%%%%%%******************************");
					//Changed by sumit on 13/03/13 changing sql adding status condition
					//Changed by Rohan on 25-02-13 for update master carton
					//sql = "UPDATE CARTON_MASTER SET STATUS = ? WHERE CARTON_NO = ? ";
					//sql = "UPDATE CARTON_MASTER SET STATUS = ?,MASTER_CARTON = ? WHERE CARTON_NO = ? ";
					sql = "UPDATE CARTON_MASTER SET STATUS = ?,MASTER_CARTON = ? WHERE CARTON_NO = ? AND STATUS <> 'R' ";
					pstmt = conn.prepareStatement(sql);
					for(cnt = 0; cnt < node2.getLength(); cnt++)
					{	
						updateFlag = "";
						//Changed by sumit on 08/12/12 getting updateFlag value start
						/*outerCarton = genericUtility.getColumnValueFromNode("carton_no", node2.item(cnt));
						System.out.println("-------->Carton" +outerCarton);
						pstmt.setString(1,outerCarton);
						pstmt.addBatch();
						pstmt.clearParameters();*/
						Node childNode = node2.item( cnt );
						
						NodeList childNodeList = childNode.getChildNodes();//.getAttributes().getNamedItem("updateFlag").getNodeValue();		
						//Changed by sumit on 08/12/12 getting updateFlag value end
						int childNodLen = childNodeList.getLength();
						for(int ctr1 = 0 ; ctr1 < childNodLen ;ctr1++ )
						{
							Node attribNode = childNodeList.item(ctr1);
							
							if( attribNode.getNodeType() != Node.ELEMENT_NODE )
							{
								continue;
							}
							String childNodeName = attribNode.getNodeName();
							System.out.println(" columnName [" + childNodeName + "]");
							if ( "attribute".equalsIgnoreCase( childNodeName ) )
							{						
								updateFlag = attribNode.getAttributes().getNamedItem("updateFlag").getNodeValue();
								break;
							}
						}						
						
						outerCarton = genericUtility.getColumnValueFromNode("carton_no", node2.item(cnt));
						
						System.out.println("-------->Carton ["+outerCarton+"] updateFlag ["+updateFlag+"]");
						
						//Changed by sumit on 05/06/13						
						if( "A".equalsIgnoreCase(updateFlag) || "D".equalsIgnoreCase(updateFlag))
						{
							cartonSet.add(outerCarton);
						}
						
						//Changed by sumit on 08/12/12 changing input parameter as per sql start
						//pstmt.setString(1,outerCarton);
						
						if("E".equalsIgnoreCase(updateFlag) || "A".equalsIgnoreCase(updateFlag) )
						{
							pstmt.setString(1,"U");
							//Changed by Rohan on 25-02-13 for update master carton
							pstmt.setString(2,outerCartonNo);
						}
						else if("D".equalsIgnoreCase(updateFlag))
						{
							pstmt.setString(1,"A");
							//Changed by Rohan on 25-02-13 for update master carton
							pstmt.setString(2,"");
						}
						//Changed by Rohan on 25-02-13 for update master carton
						//pstmt.setString(2,outerCarton);
						pstmt.setString(3,outerCarton);
						
						pstmt.addBatch();
						pstmt.clearParameters();
						//Changed by sumit on 08/12/12 changing input parameter as per sql end
					}
					pstmt.executeBatch();
					pstmt.clearBatch();

					sql = "select count(*) from carton_master where  carton_type= 'C' and status='A' and " +
							" ptcn = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, ptcn);
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						count = rs.getInt(1);
					}
					rs.close();
					rs = null;
					pstmt.close(); pstmt = null;

					if(count == 0)
					{
						sql = "SELECT distinct (a.carton_no) " +
								" FROM carton_master a ,WAVE_TASK_DET b " +
								" WHERE b.PTCN = ? AND b.REF_SER = 'M-PACK' " +
								" and a.ptcn = b.ptcn and a.carton_type = 'M' group by a.carton_no";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, ptcn);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							outerCarton = rs.getString(1);
							System.out.println("Outer Carton["+outerCarton+"]");
							//Changed by sumit on 13/03/13 changing sql adding status condition
							//sql = "UPDATE CARTON_MASTER SET STATUS = ?,MASTER_CARTON = ? WHERE CARTON_NO = ? ";
							//changed by sankara on 04/02/14 update status in carton_master start.
							/*sql1 = "UPDATE CARTON_MASTER SET STATUS = 'U' WHERE CARTON_NO = ? AND STATUS <> 'R'";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1,outerCarton);
							pstmt1.executeUpdate();	*/
							sql1 = " SELECT COUNT(*) AS COUNT FROM CARTON_MASTER WHERE MASTER_CARTON = ? AND CARTON_TYPE = 'C' AND STATUS IN ('A','U') ";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1, outerCarton);
							rs1 = pstmt1.executeQuery();
							if(rs1.next())
							{
								count = rs1.getInt("COUNT"); 
								System.out.println("count::::::"+count);
							}
							rs1.close();rs1 = null;
							pstmt1.close(); pstmt1 = null;

							if(count == 0)
							{
								status = "R";
							}
							if(count > 0)
							{
								status = "U";
							}
							sql1 = " UPDATE CARTON_MASTER SET STATUS = ? WHERE CARTON_NO = ? AND STATUS <> 'R' ";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1,status);
							pstmt1.setString(2,outerCarton);
							pstmt1.executeUpdate();	
							//changed by sankara on 04/02/14 update status in carton_master end.
						}
						rs.close();
						rs = null;
						pstmt.close(); pstmt = null;

						sql = "SELECT REF_ID FROM WAVE_TASK_DET WHERE PTCN = ? AND REF_SER = 'M-PACK'";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, ptcn);
						rs = pstmt.executeQuery();
						while(rs.next())
						{
							tranId = rs.getString(1);
							System.out.println("Carton No for Confirm["+tranId+"]");
							errString = packingConf.confirm( tranId , xtraParams, forcedFlag, conn, toCommit );	
						}
						rs.close();
						rs = null;
						pstmt.close(); pstmt = null;
					}
					//Ended By Mahesh Patidar
					
					//Changed By Pragyan 10-oct-13 to set the changes status back after proper packing
					//updateChangeStatus(cartonSet, conn);
					updatePrintChangeStatus(ptcn , conn);
				}						
			}
			//Changed By Pragyan 23-MAY-14 Master Pack remove the master carton no iff record not present pack_det detail.start
			//Changed by sumit on 08/02/13 updating pack_hdr start.
			/*else
			{
				int update = 0;
				sql = "UPDATE PACK_HDR SET OUTER_CARTON_NO = NULL WHERE TRAN_ID = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);	
				update = pstmt.executeUpdate();
				if( update > 0)
				{
					System.out.println(update+" column of PACK_HDR is update successfuly ");
				}
				pstmt.close(); pstmt = null;			
				
			}*/
			
			if(tranId != null)
			{		
				
				int update = 0;
				sql = "UPDATE PACK_HDR SET OUTER_CARTON_NO = NULL WHERE TRAN_ID = ?  AND (SELECT COUNT(*) FROM PACK_DET WHERE TRAN_ID= ?) = 0 ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				pstmt.setString(2, tranId);
				update = pstmt.executeUpdate();
				if( update > 0)
				{
					System.out.println(update+" column of PACK_HDR is update successfuly ");
				}
				pstmt.close(); pstmt = null;	
			}
			//Changed by sumit on 08/02/13 updating pack_hdr end.
			//Changed By Pragyan 23-MAY-14 Master Pack remove the master carton no iff record not present pack_det detail.end

		}
		catch( Exception e )
		{
			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 )
			{
				System.err.println("Exception :MasterPackPos :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
	private String checkNull( String input )
	{
		if( input == null )
		{
			input = "";
		}
		return input;
	}
	
	private int updateChangeStatus(Set cartonSet,Connection conn) throws ITMException
	{
		String sql = "";
		String cartonNo = "";
		String orgMasterCarton = "";
		String newMasterCarton = "";
		
		int update = 0;
		
		PreparedStatement pstmt = null,pstmt1 = null;
		ResultSet rs = null;
		
		try
		{  
			sql = "UPDATE CARTON_MASTER SET CHANGE_STATUS = ? WHERE MASTER_CARTON = ?";
			pstmt1 = conn.prepareStatement(sql);
			
			Iterator it = cartonSet.iterator();
			while( it.hasNext())
			{
				cartonNo = it.next().toString();
				System.out.println(" cartonNo ["+cartonNo+"]");
				
				sql = "SELECT MASTER_CARTON FROM WAVE_STATUS_ORG_DET WHERE CARTON_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, cartonNo);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					orgMasterCarton = rs.getString("MASTER_CARTON");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				
				sql = "SELECT MASTER_CARTON FROM CARTON_MASTER WHERE CARTON_NO = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, cartonNo);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					newMasterCarton = rs.getString("MASTER_CARTON");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;
				
				if( !orgMasterCarton.equalsIgnoreCase(newMasterCarton) )
				{
					update = 0;					
					pstmt1.setString(1, "Y");
					pstmt1.setString(2, orgMasterCarton);
					update = pstmt1.executeUpdate();
					pstmt1.clearParameters();
					if( update > 0 )
					{
						System.out.println("update successfully in WAVE_STATUS_ORG_DET ");
					}	
					
					update = 0;
					pstmt1.setString(1, "Y");
					pstmt1.setString(2, newMasterCarton);
					update = pstmt1.executeUpdate();
					pstmt1.clearParameters();
					if( update > 0 )
					{
						System.out.println("update successfully in WAVE_STATUS_ORG_DET ");
					}
					
					sql = "UPDATE WAVE_STATUS_ORG SET PRINT_STATUS = ? WHERE PTCN IN (SELECT PTCN FROM CARTON_MASTER WHERE CARTON_NO = ?)";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, "N");
					pstmt.setString(2, cartonNo);
					update = pstmt.executeUpdate();
					if( update > 0 )
					{
						System.out.println("update successfully in WAVE_STATUS_ORG ");
					}
					pstmt.close(); pstmt = null;
					
				}
			}
			if(pstmt1 != null )
			{
				pstmt1.close(); pstmt1 = null;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null )
				{
					rs.close(); rs = null;
				}
				if(pstmt1 != null )
				{
					pstmt1.close(); pstmt1 = null;
				}
				if(pstmt != null )
				{
					pstmt.close(); pstmt = null;
				}
				
			}
			catch(Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return 0;		
	}
	
	
	private int updatePrintChangeStatus(String PTCN,Connection conn) throws ITMException
	{
		String sql = "",sSQL = "";
		String cartonNo = "";
		String orgMasterCarton = "";
		String newMasterCarton = "";
		
		int update = 0;
		
		PreparedStatement pstmt = null,pstmt1 = null,pstmtWaveChk = null;
		ResultSet rs = null,rsWavChk = null;
		
		boolean isPackChanged = false;
		
		try
		{  
			sSQL = "SELECT DISTINCT W.MASTER_CARTON FROM CARTON_MASTER C ,"
				+" WAVE_STATUS_ORG_DET W WHERE C.PTCN= ? "
				+" AND C.PTCN = W.PTCN AND C.CARTON_NO = W.CARTON_NO "
				+" AND C.MASTER_CARTON <> W.MASTER_CARTON AND C.MASTER_CARTON= ?";
				
			pstmtWaveChk = conn.prepareStatement(sSQL);
				
			sql = "UPDATE CARTON_MASTER SET CHANGE_STATUS = ? WHERE PTCN = ?";
			pstmt1 = conn.prepareStatement(sql);
			pstmt1.setString(1, "N");
			pstmt1.setString(2, PTCN);
			int updCountDefault = pstmt1.executeUpdate();
			
			sql = "UPDATE CARTON_MASTER SET CHANGE_STATUS = ? WHERE MASTER_CARTON = ?";
			pstmt1 = conn.prepareStatement(sql);
			System.out.println("Default Update Status as N ["+updCountDefault+"]");
			
			
			sql = "SELECT DISTINCT MASTER_CARTON FROM WAVE_STATUS_ORG_DET WHERE PTCN = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, PTCN);
			rs = pstmt.executeQuery();
			
			while( rs.next())
			{
					orgMasterCarton = rs.getString("MASTER_CARTON");
					pstmtWaveChk.setString(1, PTCN);
					pstmtWaveChk.setString(2, orgMasterCarton);
					
					rsWavChk  = pstmtWaveChk.executeQuery();
					
					while(rsWavChk.next())
					{
						isPackChanged = true;
						newMasterCarton = rsWavChk.getString(1);
						pstmt1.setString(1, "Y");
						pstmt1.setString(2, newMasterCarton);
						update = pstmt1.executeUpdate();
						if( update > 0 )
						{
							System.out.println("update change status Count "+update+"for  ["+newMasterCarton+"]");
						}
						pstmt1.setString(2, orgMasterCarton);
						update = pstmt1.executeUpdate();
						if( update > 0 )
						{
							System.out.println("update change status Count "+update+"for  ["+orgMasterCarton+"]");
						}
						pstmt1.clearParameters();
					}
					
					if(rsWavChk != null)
					{
						rsWavChk.close();
						rsWavChk = null;
					}
					pstmtWaveChk.clearParameters();
			}
			
			if(isPackChanged)
			{
				sql = "UPDATE WAVE_STATUS_ORG SET PRINT_STATUS = ? WHERE PTCN = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "N");
				pstmt.setString(2, PTCN);
				update = pstmt.executeUpdate();
				if( update > 0 )
				{
					System.out.println("update successfully in WAVE_STATUS_ORG as N ");
				}
			
			}
			else
			{
				sql = "UPDATE WAVE_STATUS_ORG SET PRINT_STATUS = ? WHERE PTCN = ? " +
					" AND TRAN_CODE IN (SELECT TRAN_CODE FROM DESPATCH WHERE PTCN = ?)";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, "Y");
				pstmt.setString(2, PTCN);
				pstmt.setString(3, PTCN);
				update = pstmt.executeUpdate();
				
				if( update > 0 )
				{
					System.out.println("update successfully in WAVE_STATUS_ORG as Y");
				}
			}
			
		
			
			if(rs != null)
			{
				rs.close(); rs = null;
			}
			if(pstmt !=null)
			{
				pstmt.close(); pstmt = null;
			}
			if(pstmt1 != null )
			{
				pstmt1.close(); pstmt1 = null;
			}
			if(pstmtWaveChk != null )
			{
				pstmtWaveChk.close(); pstmtWaveChk = null;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null )
				{
					rs.close(); rs = null;
				}
				if(pstmt1 != null )
				{
					pstmt1.close(); pstmt1 = null;
				}
				if(pstmt != null )
				{
					pstmt.close(); pstmt = null;
				}
				
			}
			catch(Exception es)
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
		}
		return 0;		
	}
}
