/**
 * 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 = "";
		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();
		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));
					System.out.println("---->tran_id =[" +tranID+ "] and pick order no =["+pickOrder+"]");
					//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");
					sql = "UPDATE CARTON_MASTER SET STATUS = ? WHERE CARTON_NO = ? ";
					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 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");
						}
						else if("D".equalsIgnoreCase(updateFlag))
						{
							pstmt.setString(1,"A");
						}						
						pstmt.setString(2,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+"]");
							sql1 = "UPDATE CARTON_MASTER SET STATUS = 'U' WHERE CARTON_NO = ? ";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1,outerCarton);
							pstmt1.executeUpdate();	
						}
						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 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;			
				
			}
			//Changed by sumit on 08/02/13 updating pack_hdr 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;
	}
}
