/*******************************************    
Title : PickPostSaveEJB
    Date  : 04/11/11
    Author: Chitranjan Pandey

 ********************************************************/
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.rmi.RemoteException;
import java.sql.*;
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 PickIssPos extends ValidatorEJB implements PickIssPosLocal, PickIssPosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		String forcedFlag = "false" ;
		String errString = "";
		String sql = "";
		String pickOrder = "";
		String pickType = "",refSer = "";
		String palletNo = "", locCodeTo = "";
		System.out.println("TRAN ID FROM POST SAVE = "+tranId);

		int totOrdDet = 0;
		int ordDetScaned = 0;
		int updCnt = 0;


		boolean toCommit = false;

		Document dom = null;

		PreparedStatement pstmt = null;
		PreparedStatement pstmtTran = null;
		PreparedStatement pstmtUpdCarton = null;
		ResultSet rs = null;
		ResultSet rsTran = null;
		System.out.println("Dom String = "+domString);
		PickIssConf pickConf = new PickIssConf();
		ArrayList tranIdArrList = new ArrayList();



		try
		{
			conn.setAutoCommit(false);

			GenericUtility genericUtility = GenericUtility.getInstance();
			dom = genericUtility.parseString(domString);

			NodeList detail4List = dom.getElementsByTagName("Detail4");
			NodeList detail3List = dom.getElementsByTagName("Detail3");
			if(detail3List != null && detail3List.getLength() > 0)
			{
				System.out.println("Detail3 length = "+detail3List.getLength());

				pickOrder = genericUtility.getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));

				System.out.println("pick order = "+pickOrder);
				//sql = "select pick_type from pick_ord_hdr where pick_order = ?";
				sql = "SELECT P.PICK_TYPE,W.REF_SER  FROM PICK_ORD_HDR P , WAVE_TASK_DET W WHERE  W.REF_ID = P.PICK_ORDER AND P.PICK_ORDER= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, pickOrder );
				rs = pstmt.executeQuery();
				
				if(rs.next())
				{
					pickType = rs.getString("pick_type");
					refSer  = rs.getString("ref_ser");
				}
				rs.close(); rs = null;
				pstmt.close(); pstmt = null;

				System.out.println("pick type = "+pickType);

				if(!("A".equalsIgnoreCase(pickType)))
				{
					System.out.println("called for case pick ");
					if(detail4List != null && detail4List.getLength() > 0)
					{
						System.out.println("Detail4 length = "+detail4List.getLength());

						palletNo = genericUtility.getColumnValue("pallet_no", dom, "1", "1");
						locCodeTo = genericUtility.getColumnValueFromNode("loc_code__to", dom.getElementsByTagName("Detail4").item(0)); // 22/09/12 manoharan
						pickOrder = genericUtility.getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));
						System.out.println("pick order from header ["+pickOrder+"]");

						sql = "UPDATE pick_iss_det SET loc_code__to =  ? WHERE  tran_id = ? ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,locCodeTo);
						pstmt.setString(2,tranId);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully of master pick---->"+updCnt);
						}
						
						//added by kunal mandhre on 09/08/12 as per S Manoharan Sir instruction 
						sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN(SELECT PALLET_NO FROM PICK_ISS_HDR WHERE PICK_ORDER =  ? ) ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, pickOrder);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully in pallet no ---->"+updCnt);
						}
						
						//Changed by Rohan on 25-02-13 for updating pallet no against master pack in carton master.start
						sql = " UPDATE CARTON_MASTER SET PALLET_NO = ? WHERE CARTON_NO IN(SELECT MASTER_CARTON FROM CARTON_MASTER WHERE PICK_ORDER = ? "
							  +" GROUP BY MASTER_CARTON) ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, palletNo);
						pstmt.setString(2, pickOrder);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully in carton master--->"+updCnt);
						}
						//Changed by Rohan on 25-02-13 for updating pallet no against master pack in carton master.end
						
						//Changed By Pragyan 05/03/12  to update pallet no on carton master. 
						//Update the carton master 
						//if(!"M".equalsIgnoreCase(pickType)) // 22/09/12 manoharan
						/*if(!"M".equalsIgnoreCase(pickType) || "P-PICK".equalsIgnoreCase(refSer)) // 22/09/12 manoharan
						{
							//Changed by sumit on 11/09/13 adding condition of status.
							//sql = "UPDATE CARTON_MASTER SET STATUS = 'U', pallet_no = ? WHERE PICK_ORDER = ? and line_no__pick in (select line_no__ord from pick_iss_det where tran_id = ?) ";
							sql =   " UPDATE CARTON_MASTER SET STATUS = 'U', PALLET_NO = ? WHERE PICK_ORDER = ? " +
									" AND LINE_NO__PICK IN (SELECT LINE_NO__ORD FROM PICK_ISS_DET WHERE TRAN_ID = ?) " +
									" AND STATUS <> 'R'";
							
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,palletNo);
							pstmt.setString(2, pickOrder);
							pstmt.setString(3,tranId);
							updCnt = pstmt.executeUpdate();
							pstmt.close(); pstmt = null;
							if(updCnt > 0)
							{
								System.out.println("---row updated sucessfully of master pick---->"+updCnt);
							}
							
							//added by kunal mandhre on 09/08/12 as per S Manoharan Sir instruction 
							sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN(SELECT PALLET_NO FROM PICK_ISS_HDR WHERE PICK_ORDER =  ? ) ";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1, pickOrder);
							updCnt = pstmt.executeUpdate();
							pstmt.close(); pstmt = null;
							if(updCnt > 0)
							{
								System.out.println("---row updated sucessfully in pallet no ---->"+updCnt);
							}
							//end by kunal
						}*/
						//select no of art from pick_ord_det 
						//Changed By Pragyan WM2LSUN011 23/03/13 This code is no longer required for confirmation checking.start
						//select no of art from pick_ord_det 
						/*//Changed by sumit on 18/03/13 adding condition 
						//sql = "SELECT SUM(NO_ART) FROM PICK_ORD_DET WHERE PICK_ORDER = ? ";
						sql = "SELECT SUM(NO_ART) FROM PICK_ORD_DET WHERE PICK_ORDER = ? " +
							  "AND (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) > 0 ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString( 1, pickOrder );
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							totOrdDet = rs.getInt(1);
						}
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;

						//select the no of order scanned 
						sql = "SELECT SUM(NO_ART) AS COUNT FROM PICK_ISS_DET WHERE PICK_ORDER = ? ";
						pstmt = conn.prepareStatement( sql );
						pstmt.setString( 1, pickOrder );
						rs = pstmt.executeQuery();
						if(rs.next())
						{
							ordDetScaned = rs.getInt("COUNT");
						}
						rs.close(); rs = null;
						pstmt.close(); pstmt = null;

						//if total pick order detail is equals to total order scanned then confirm method is called
						System.out.println("Total details of pick order == ["+totOrdDet+"]");
						System.out.println("Total order scanned == ["+ordDetScaned+"]");
						*/
						
						
						/*if( totOrdDet == ordDetScaned )
						{ */
							//changes done by cpandey to call confirm method in case of pick type is (m or c) 31//07/12
							System.out.println("pick type from pick_ord_hdr table--->>["+pickType+"]");
							//commented by chitranjan to call confirm method for all pick type on 07/08/12
							//if (!("A".equalsIgnoreCase(pickType)))
							//if(pickType.equalsIgnoreCase("C")|| pickType.equalsIgnoreCase("M"))
							//{
							/*System.out.println("Calling Confirm method..................");
							errString = pickConf.confirm( tranId, xtraParams, forcedFlag ,conn, toCommit );
							
							if (errString.indexOf("CONFSUCESS") > -1)
							{
								errString = "";
							}
							if ( errString != null && errString.length() > 0 )
							{
								throw new Exception(errString);
							}*/
							sql =   " UPDATE CARTON_MASTER SET STATUS = 'U', PALLET_NO = ? WHERE PICK_ORDER = ? " +
							" AND LINE_NO__PICK IN (SELECT LINE_NO__ORD FROM PICK_ISS_DET WHERE TRAN_ID = ?) " +
							" AND STATUS <> 'R'";
					
							pstmtUpdCarton = conn.prepareStatement(sql);
					
							sql = "SELECT DISTINCT H.TRAN_ID,H.PICK_ORDER,H.PICK_TYPE FROM PICK_ISS_HDR H, PICK_ISS_DET D WHERE H.TRAN_ID = D.TRAN_ID AND  H.PALLET_NO = ?";
							pstmtTran = conn.prepareStatement( sql );
							pstmtTran.setString( 1, palletNo );
							rsTran = pstmtTran.executeQuery();
							
							while(rsTran.next())
							{
								//changed by Rohan on 28-03-13 for bug fixing.start
								/*
								tranId = rs.getString("TRAN_ID");
								pickOrder = rs.getString("PICK_ORDER");
								pickType =  rs.getString("PICK_TYPE");
								*/
								tranId = rsTran.getString("TRAN_ID");
								pickOrder = rsTran.getString("PICK_ORDER");
								pickType =  rsTran.getString("PICK_TYPE");
								//changed by Rohan on 28-03-13 for bug fixing.end
								
								if(!"M".equalsIgnoreCase(pickType) || "P-PICK".equalsIgnoreCase(refSer)) // 22/09/12 manoharan
								{
									
									
									pstmtUpdCarton.setString(1,palletNo);
									pstmtUpdCarton.setString(2, pickOrder);
									pstmtUpdCarton.setString(3,tranId);
									updCnt = pstmtUpdCarton.executeUpdate();
									pstmtUpdCarton.clearParameters();
									if(updCnt > 0)
									{
										System.out.println("---row updated sucessfully of master pick---->"+updCnt);
									}
								}
								
								errString = pickConf.confirm( tranId, xtraParams, forcedFlag ,conn, toCommit );
								
								if (errString.indexOf("CONFSUCESS") > -1)
								{
									errString = "";
								}
								if ( errString != null && errString.length() > 0 )
								{
									throw new Exception(errString);
								}
								
							}
							
							if(rsTran != null)
							{
								rsTran.close(); 
								rsTran = null;
							}
							if(pstmtTran != null)
							{
								pstmtTran.close(); 
								pstmtTran = null;
							}
							
							if(pstmtUpdCarton != null)
							{
								pstmtUpdCarton.close(); 
								pstmtUpdCarton = null;
							}
							//Changed By Pragyan WM2LSUN011 23/03/13 This code is no longer required for confirmation checking.end
							//}
						/*} */
					}
				}
				else
				{
					System.out.println("called for active pick ");
					palletNo = genericUtility.getColumnValue("pallet_no", dom, "1", "1");
					pickOrder = genericUtility.getColumnValueFromNode("pick_order", dom.getElementsByTagName("Detail2").item(0));
					System.out.println("pick order from header ["+pickOrder+"]");
					/*

					sql = "select pick_type from pick_ord_hdr where pick_order = ?";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString( 1, pickOrder );
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						pickType = rs.getString("pick_type");
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;
					 */

					/*if("M".equalsIgnoreCase(pickType))
			{
				sql = "update pick_iss_det set carton_no = ?  where tran_id = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, palletNo);
				pstmt.setString(2, tranId);
				int updCnt1 = pstmt.executeUpdate();
				pstmt.close(); pstmt = null;
				if(updCnt1 > 0)
				{
					System.out.println("---row updated sucessfully of master pick_iss_det table ---->"+updCnt1);
				}
			}*/

					//Update the carton master 
						sql = "UPDATE CARTON_MASTER SET STATUS = 'U', pallet_no = ? WHERE PICK_ORDER = ? and line_no__pick in (select line_no__ord from pick_iss_det where tran_id = ?) ";
						
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1,palletNo);
						pstmt.setString(2, pickOrder);
						pstmt.setString(3,tranId);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully of master pick---->"+updCnt);
						}

					/*//added by kunal mandhre on 09/08/12 as per S Manoharan Sir instruction 
					sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN(SELECT PALLET_NO FROM PICK_ISS_HDR WHERE PICK_ORDER =  ? ) ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, pickOrder);
					updCnt = pstmt.executeUpdate();
					pstmt.close(); pstmt = null;
					if(updCnt > 0)
					{
						System.out.println("---row updated sucessfully in pallet no ---->"+updCnt);
					}*/
					//end by kunal

					//select no of art from pick_ord_det 
					//Changed by sumit on 18/03/13 adding condition 
					//sql = "SELECT SUM(NO_ART) FROM PICK_ORD_DET WHERE PICK_ORDER = ? ";
					sql = "SELECT SUM(NO_ART) FROM PICK_ORD_DET WHERE PICK_ORDER = ? " +
						  "AND (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) > 0 ";
					pstmt = conn.prepareStatement(sql);
					pstmt.setString( 1, pickOrder );
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						totOrdDet = rs.getInt(1);
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;

					//select the no of order scanned 
					//Changed by sumit on 18/03/13 adding condition 
					//sql = "SELECT SUM(NO_ART) AS COUNT FROM PICK_ISS_DET WHERE PICK_ORDER = ? ";
					sql = "SELECT SUM(NO_ART) AS COUNT FROM PICK_ISS_DET WHERE PICK_ORDER = ? " +
							" AND (QUANTITY - CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END ) > 0 ";
					pstmt = conn.prepareStatement( sql );
					pstmt.setString( 1, pickOrder );
					rs = pstmt.executeQuery();
					if(rs.next())
					{
						ordDetScaned = rs.getInt("COUNT");
					}
					rs.close(); rs = null;
					pstmt.close(); pstmt = null;

					//if total pick order detail is equals to total order scanned then confirm method is called
					System.out.println("Total details of pick order == ["+totOrdDet+"]");
					System.out.println("Total order scanned == ["+ordDetScaned+"]");
					if( totOrdDet == ordDetScaned )
					{
						//changes done by cpandey to call confirm method in case of pick type is (m or c) 31//07/12
						System.out.println("pick type from pick_ord_hdr table--->>["+pickType+"]");
						//commented by chitranjan to call confirm method for all pick type on 07/08/12
						//if (!("A".equalsIgnoreCase(pickType)))
						//if(pickType.equalsIgnoreCase("C")|| pickType.equalsIgnoreCase("M"))
						//{
						
						//added by kunal mandhre on 09/08/12 as per S Manoharan Sir instruction 
						sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO IN(SELECT PALLET_NO FROM PICK_ISS_HDR WHERE PICK_ORDER =  ? ) ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, pickOrder);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully in pallet no ---->"+updCnt);
						}
						
						
						System.out.println("Calling Confirm method..................");
						errString = pickConf.confirm( tranId, xtraParams, forcedFlag ,conn, toCommit );
						if (errString.indexOf("CONFSUCESS") > -1)
						{
							errString = "";
						}
						if ( errString != null && errString.length() > 0 )
						{
							throw new Exception(errString);
						}
						//}
					}

				}
			}
		}
		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)
			{
				System.err.println("Exception :PickIssPos :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
}
