/*******************************************    
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
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	
	//Changed by sumit on 11/05/13 start.
	public String postSave( String domString, String tranId,String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		String errString = "";
		ConnDriver connDriver = null;
		Connection conn = null;
		Document dom = null;
		
		try
		{
			System.out.println("**** PickIssPos ***** postSave ");			
			System.out.println("**** PickIssPos ***** tranId ["+tranId+"]");
			System.out.println("**** PickIssPos ***** editFlag ["+editFlag+"]");
			System.out.println("**** PickIssPos ***** xtraParams ["+xtraParams+"]");
			dom = genericUtility.parseString(domString);
			NodeList detail3List = dom.getElementsByTagName("Detail3");
			if(detail3List != null && detail3List.getLength() > 0 && tranId.trim().length() == 0 )
			{	
				tranId = genericUtility.getColumnValueFromNode("tran_id", dom.getElementsByTagName("Detail2").item(0));
			}
			System.out.println(" tran_id ********* ["+tranId+"]");	
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");
			errString = postSave(domString,tranId,editFlag,xtraParams, conn );
			if(errString.trim().length() == 0)
			{
				conn.commit();
			}
			else
			{
				conn.rollback();
			}
			
						
		}
		catch(Exception e)
		{
			System.out.println("Exception :postSave :" + e.getMessage() + ":");
			try
			{
				if( conn != null )
				{
					conn.rollback();
				}
			}
			catch(Exception es )
			{
				es.printStackTrace();
				throw new ITMException(es);
			}
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( conn != null)
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
	//Changed by sumit on 11/05/13 end.
	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);
			//Changed by sumit on 11/05/13 checking tran_id already confirmed or not start.
			sql = "SELECT CASE WHEN CONFIRMED IS NULL THEN 'N' ELSE CONFIRMED END AS CONFIRMED  FROM PICK_ISS_HDR WHERE TRAN_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				System.out.println("["+tranId+"] tran_id is CONFIRMED ["+rs.getString("CONFIRMED")+"]");
				if("Y".equalsIgnoreCase(rs.getString("CONFIRMED")))
				{
					return "";
				}
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			//Changed by sumit on 11/05/13 checking tran_id already confirmed or not end.

			//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+"]");
						
						if(locCodeTo != null && locCodeTo.length() > 0)
						{

								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);
								}
								
								//Changed By pragyan 02-oct-13 to update pallet  no for respective transaction .start
								//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);
								}*/
								
								sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO =  ?  ";
								pstmt = conn.prepareStatement(sql);
								pstmt.setString(1, palletNo);
								updCnt = pstmt.executeUpdate();
								pstmt.close(); pstmt = null;
								if(updCnt > 0)
								{
									System.out.println("---row updated sucessfully in pallet no changed---->"+updCnt);
								}
								//Changed By pragyan 02-oct-13 to update pallet  no for respective transaction .end
								
								//Changed by Rohan on 25-02-13 for updating pallet no against master pack in carton master.start
								//Changed by Pragyan on 03-NOV-14
								//sql = " UPDATE CARTON_MASTER SET PALLET_NO = ? WHERE CARTON_NO IN(SELECT MASTER_CARTON FROM CARTON_MASTER WHERE PICK_ORDER = ? "
								sql = " UPDATE CARTON_MASTER SET PALLET_NO = ? WHERE CARTON_NO IN(SELECT MASTER_CARTON FROM CARTON_MASTER WHERE PICK_ORDER = ? AND CARTON_TYPE ='M' "
									  +" 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);
									}*/
									//Changed by Pragyan on 03-NOV-14
									if("M".equalsIgnoreCase(pickType)) // 22/09/12 manoharan
									{
										sql =   " UPDATE CARTON_MASTER SET PALLET_NO = ? WHERE PICK_ORDER = ? " +
												" AND LINE_NO__PICK IN (SELECT LINE_NO__ORD FROM PICK_ISS_DET WHERE TRAN_ID = ?) " +
												" AND STATUS <> 'R'";
									}
									else
									{
										
										
										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'";
									}
									//End Changed by Pragyan on 03-NOV-14
							
									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
										//Changed by Pragyan on 03-NOV-14
										/*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"))
						//{
						
						//Changed By pragyan 02-oct-13 to update pallet  no for respective transaction .start
						//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);
						}*/
						sql = "UPDATE PALLET_NO SET STATUS = 'U' WHERE PALLET_NO =  ?  ";
						pstmt = conn.prepareStatement(sql);
						pstmt.setString(1, palletNo);
						updCnt = pstmt.executeUpdate();
						pstmt.close(); pstmt = null;
						if(updCnt > 0)
						{
							System.out.println("---row updated sucessfully in pallet no changed---->"+updCnt);
						}
						//Changed By pragyan 02-oct-13 to update pallet  no for respective transaction .end
						
						
						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;
	}
}
