/*
authored by Pawan 10/7/2010 to confirm the transaction
*/

package ibase.webitm.ejb.wms;

import ibase.webitm.utility.*;
import ibase.system.config.*;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.*;
import ibase.webitm.ejb.fin.*;

import java.rmi.RemoteException;
import java.util.Date;
import java.util.*;
import java.text.*;
import java.sql.*;

import javax.ejb.*;
import ibase.webitm.ejb.MasterStatefulLocal;
import javax.naming.InitialContext;


@Stateless
public class DeallocArtConf extends ActionHandlerEJB implements DeallocArtConfLocal, DeallocArtConfRemote
{
	String loginEmpCode = "";
	String confDateStr = "";
	GenericUtility genericUtility = GenericUtility.getInstance();
	@Override
	public String confirm( String tranId, String xtraParams, String forcedFlag ) throws RemoteException,ITMException
	{	
		System.out.println("DeallocArtConf Called........");
		
		boolean isError = false;
		int updCnt = 0;
		
		String confirmed = "";
		String sql = "", sql1 = "";
		//String siteCode = "";
		ResultSet rs = null, rs2 = null ;
		PreparedStatement pstmt = null, pstmt1 = null, pstmt2 = null;	
		Connection conn = null;
		String errString = "";
		String saleOrder = "", saleOrderLine = "", itemCode = "", custCode = "", expLev = "";
		String lotSl = "", lotNo = "", locCode = "", waveId = "", pickOrder = "", locCodeTo ="";
		String replOrder = "", siteCode = "";
		String pickType = "";
		//String comfirmed = "";
		double DeallocQty = 0.0;
		int pickLineNo = 0;
		boolean isIssueDone = false;
		HashMap replIss = new HashMap();
		
		ITMDBAccessEJB itmdbAccess = new ITMDBAccessEJB();
		try
		{	
			System.out.println(" force flag ["+forcedFlag+"]");
			System.out.println(" xtraParams --> ["+xtraParams+"]");
			loginEmpCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"loginEmpCode");
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams,"site_code");
			System.out.println(" site code ["+siteCode+"]");
			ConnDriver connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");
			conn.setAutoCommit(false);
			
			SimpleDateFormat sdf = new SimpleDateFormat( genericUtility.getDBDateTimeFormat());
			Date confDate = new Date();
			confDateStr = sdf.format(confDate);
			System.out.println("confDate is :"+confDate+" confDate str is : "+confDateStr);
			sql = "SELECT CONFIRMED FROM DEALLOC_ART WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				confirmed = checkNull( rs.getString("CONFIRMED") );
			}	
			if( pstmt != null )
			{
				pstmt.close();
				pstmt = null;
			}
			if ( rs != null )
			{
				rs.close();
				rs = null;
			}
			if( confirmed != null && confirmed.equalsIgnoreCase("Y") )
			{
				errString = itmdbAccess.getErrorString("","VTALCONF",loginEmpCode);
				return errString;
			}
			
			sql = " SELECT D.CONFIRMED FROM DESPATCH D, WAVE_TASK_DET W , dealloc_art da " +
					" WHERE W.PTCN = DA.PTCN  AND D.WAVE_ID = W.WAVE_ID AND W.REF_ID = D.DESP_ID " +
					" and da.tran_id = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				System.out.println(" confirmed ["+rs.getString("CONFIRMED")+"]");
				if("Y".equalsIgnoreCase(rs.getString("CONFIRMED")))
				{
					errString = itmdbAccess.getErrorString("","INVPTCNDSP",loginEmpCode);
					return errString;
				}
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			//Checking is one of the carton is already confirmed 
			sql = " SELECT C.STATUS FROM CARTON_MASTER C, DEALLOC_ART_DET DRD WHERE DRD.TRAN_ID = ? AND DRD.CARTON_NO = C.CARTON_NO ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while(rs.next())
			{
				System.out.println(" STATUS ["+rs.getString("STATUS")+"]");
				if("R".equalsIgnoreCase(rs.getString("STATUS")))
				{
					errString = itmdbAccess.getErrorString("","CARTALLCOF",loginEmpCode);
					return errString;
				}
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			
			sql = " ";
			sql =   " SELECT DISTINCT D.CARTON_NO, PD.SALE_ORDER , PD.LINE_NO__SORD, PD.ITEM_CODE, PD.EXP_LEV, PD.LOC_CODE__TO, PD.LOT_SL, PD.LOC_CODE, " +
					" PD.LOT_NO, D.QUANTITY, S.CUST_CODE, PH.WAVE_ID, D.PICK_ORDER, PD.LINE_NO FROM PICK_ORD_DET PD, PICK_ORD_HDR PH , DEALLOC_ART_DET D, SORDER S " +
					" WHERE D.TRAN_ID = ? AND D.PICK_ORDER = PH.PICK_ORDER AND PH.PICK_ORDER = PD.PICK_ORDER "+
					" AND D.LINE_NO__PICK = PD.LINE_NO AND PH.SALE_ORDER = S.SALE_ORDER ";
			
			sql1 = "UPDATE CARTON_MASTER SET STATUS ='R' WHERE CARTON_NO  = ? ";
			pstmt1 = conn.prepareStatement(sql1);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				//sorderDeAllocate(saleOrder, lineNo, itemCode, DeallocQty, expLev, custCode, lotSl, lotNo, locCode, tranId, allocLineno)
				saleOrder = rs.getString("SALE_ORDER");
				saleOrderLine = rs.getString("LINE_NO__SORD");
				itemCode = rs.getString("ITEM_CODE");
				custCode = rs.getString("CUST_CODE");
				expLev = rs.getString("EXP_LEV");
				lotSl = rs.getString("LOT_SL");
				lotNo = rs.getString("LOT_NO");
				locCode = rs.getString("LOC_CODE");
				locCodeTo = rs.getString("LOC_CODE__TO");
				DeallocQty = rs.getDouble("QUANTITY");
				waveId = rs.getString("WAVE_ID");
				pickOrder = rs.getString("PICK_ORDER");
				pickLineNo = rs.getInt("LINE_NO");
				saleOrderLine = "   " + saleOrderLine; 
				saleOrderLine = saleOrderLine.substring(saleOrderLine.length()-3);
				System.out.println(" sord line no ["+saleOrderLine+"] locCode ["+locCode+"] locCodeTo["+locCodeTo+"] pickLineNo ["+pickLineNo+"]");
				
				sql = "";
				sql = " SELECT PIH.PICK_ORDER, PIH.PICK_TYPE, PIH.CONFIRMED, PID.LOC_CODE__TO  FROM PICK_ISS_HDR PIH, PICK_ISS_DET PID WHERE PIH.PICK_ORDER = ? " +
						" AND PIH.PICK_ORDER = PID.PICK_ORDER";
				pstmt2 = conn.prepareStatement(sql);
				pstmt2.setString(1, pickOrder);
				rs2 = pstmt2.executeQuery();
				confirmed = "";
				if( rs2.next())
				{									
					pickType = rs2.getString("PICK_TYPE");
					confirmed = rs2.getString("CONFIRMED");
					System.out.println(" pickType ["+pickType+"] confirmed["+confirmed+"]");
					if( "Y".equalsIgnoreCase(confirmed))
					{
						locCode = rs2.getString("LOC_CODE__TO");
						isIssueDone = true;
					}
					else
					{
						locCode = locCodeTo;
					}
				}
				rs2.close(); rs2 = null;
				pstmt2.close(); pstmt2 = null;				
				System.out.println(" locCode ["+locCode+"] locCodeTo ["+locCodeTo+"]");
				
				//Deallocating from pick order in case of issue not done
				if( !isIssueDone )
				{
					
					replIss = new HashMap();
					replIss = isReplIssueDone(pickOrder, pickLineNo, conn);
					if("YES".equalsIgnoreCase(replIss.get("isRepl").toString()))
					{
						replOrder = replIss.get("repl_order").toString();
						System.out.println(" 88 replOrder ["+replOrder+"]");
						
						HashMap replParent = new HashMap();
						replParent = isParentExist(replOrder, conn);						
						confirmed = replParent.get("confirmed").toString();//confirmed						
						System.out.println("  confirmed ["+confirmed+"] paren_exist ["+replParent.get("paren_exist").toString()+"] ");
						if("Y".equalsIgnoreCase(replParent.get("confirmed").toString()))
						{
							locCode = replParent.get("loc_code__to").toString();
							lotNo = replParent.get("lot_no").toString();
							lotSl = replParent.get("lot_sl").toString();
							System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");	
							//Updating pick_order
							updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							
						}
						else if ("N".equalsIgnoreCase(confirmed) && "NO".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
						{
							locCode = replParent.get("loc_code__to").toString();
							lotNo = replParent.get("lot_no").toString();
							lotSl = replParent.get("lot_sl").toString();
							System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
							
							updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);	
							updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							
						}
						else if ( "N".equalsIgnoreCase(confirmed) && "YES".equalsIgnoreCase(replParent.get("paren_exist").toString()))
						{
							System.out.println(" repl order going to search it's ["+replOrder+"] parent ");
							replOrder = "";
							replOrder = replParent.get("pick_order").toString();
							System.out.println(" new repl order going to serch it's parent ["+replOrder+"]");
							
							replParent.clear();
							replParent = isParentExist(replOrder, conn);
							
							confirmed = "";
							confirmed = replParent.get("confirmed").toString();
							System.out.println(" *-* confirmed ["+confirmed+"]");
							if("Y".equalsIgnoreCase(confirmed))
							{
								locCode = replParent.get("loc_code__to").toString();
								lotNo = replParent.get("lot_no").toString();
								lotSl = replParent.get("lot_sl").toString();
								System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
								
								updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
								
							}
							else if ("N".equalsIgnoreCase(confirmed) && "NO".equalsIgnoreCase(replParent.get("paren_exist").toString()))        //parentExist.put("paren_exist", "NO");
							{
								locCode = replParent.get("loc_code__to").toString();
								lotNo = replParent.get("lot_no").toString();
								lotSl = replParent.get("lot_sl").toString();
								System.out.println(" locCode ["+locCode+"] lotNo ["+lotNo+"] lotSl ["+lotSl+"]");
								
								updateReplOrder(replOrder, siteCode, itemCode, locCodeTo, lotNo, lotSl, DeallocQty, conn);
								updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
							}
							
							
						}
						
					}
					else
					{
						updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
					}
				}
				else
				{
					updatePickOrder(pickOrder, pickLineNo, DeallocQty, conn);
				}			
				
				errString = sorderDeAllocate(saleOrder, saleOrderLine, itemCode, DeallocQty, expLev, custCode, lotSl, lotNo, locCode, xtraParams, waveId, pickOrder);
				
				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("errString :"+errString);
					return errString;
				}
				
				//Updating  carton_no of carton master
				pstmt1.setString(1, rs.getString("CARTON_NO"));
				if( pstmt1.executeUpdate() > 0)
				{
					System.out.println(rs.getString("CARTON_NO")+" carton_no updated successfully ");
				}
				pstmt1.clearParameters();
				
				
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			if( pstmt1 != null )
			{
				pstmt1.close();
				pstmt1 = null;
			}
			
			/*sql = " ";
			sql =   " SELECT DISTINCT D.CARTON_NO FROM PICK_ORD_DET PD, PICK_ORD_HDR PH , DEALLOC_ART_DET D, SORDER S " +
					" WHERE D.TRAN_ID = ? AND D.PICK_ORDER = PH.PICK_ORDER AND PH.PICK_ORDER = PD.PICK_ORDER "+
					" AND D.LINE_NO__PICK = PD.LINE_NO AND PH.SALE_ORDER = S.SALE_ORDER ";
			
			sql1 = "UPDATE CARTON_MASTER SET STATUS ='R' WHERE CARTON_NO  = ? ";
			pstmt1 = conn.prepareStatement(sql1);
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			while (rs.next())
			{				
				pstmt1.setString(1, rs.getString("CARTON_NO"));
				if( pstmt1.executeUpdate() > 0)
				{
					System.out.println(rs.getString("CARTON_NO")+" carton_no updated successfully ");
				}
				pstmt1.clearParameters();
				
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;	*/		
			
			
			sql = "UPDATE DEALLOC_ART SET CONFIRMED = 'Y', CONF_DATE = ? WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement( sql );	 
			pstmt.setTimestamp(1, Timestamp.valueOf( confDateStr ) );				
			pstmt.setString(2, tranId); 
			updCnt = pstmt.executeUpdate();
			if ( updCnt > 0 )
			{
				System.out.println("DEALLOC_ART Updated Successfully ::"+updCnt);
			}
			pstmt.close();
			pstmt = null;		
			
			
			
			//End changes by Gulzar on 5/10/2012
		}
		catch(Exception e)
		{
			isError = true;
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				System.out.println("isError =["+isError+"]");
				System.out.println("Commiting and Closing Connection..........");
				if ( !(isError) )
				{					
					conn.commit();
					errString = itmdbAccess.getErrorString("","VTSUCC1",loginEmpCode);
				}
				else
				{					
					conn.rollback();
				}
				if (rs != null )
				{
					rs.close(); rs = null;
				}
				if( pstmt != null )
				{					
					pstmt.close();
					pstmt = null;
				}
				if( conn != null && !conn.isClosed() )
				{					
					conn.close();
					conn = null;
				}
			}
			catch( Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}		
		return errString;		
	}
	private String checkNull(String input)
	{
		if(input == null)
		{
			input = "";
		}
		return input;
	}
	
	private String sorderDeAllocate(String saleOrder, String lineNo, String itemCode, double DeallocQty, String expLev,String custCode,String lotSl,String lotNo,String locCode, String xtraParams, String waveId, String pickOrder ) throws ITMException
	{
		System.out.println("updateSOrder calling ..............");
		String updateSorditem = null,updateSordAllocDet=null;
		String updateSql= null,DelQtyAlloc= null,SqlQtyAlloc= null ;
		String errString = "";
		String error = "";
		String errCode = "";
		String siteCode = "", chgUser = "", chgTerm = "", sql = "", despId = "";
		double quantity = 0.0;
		Connection conn = null;
		PreparedStatement pstmt=null, pstmt1 = null;
		Statement st = null;
		ResultSet rsQtyAlloc=null, rs = null, rs1 = null;
		HashMap invallocTraceMap = new HashMap();
		ConnDriver connDriver = new ConnDriver();
		InvAllocTraceBean invallocTraceBean = new InvAllocTraceBean();
		try
		{
			siteCode = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "site_code");
			chgUser = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "loginCode");
			chgTerm = genericUtility.getValueFromXTRA_PARAMS(xtraParams, "termId");
			System.out.println(" site code :["+siteCode+"] chgUser:["+chgUser+"] chgTerm:["+chgTerm+"]");
			
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			st = conn.createStatement();
			invallocTraceMap.put("ref_ser","DEART");
			invallocTraceMap.put("site_code",siteCode);
			invallocTraceMap.put("item_code",itemCode);
			invallocTraceMap.put("loc_code",locCode);
			invallocTraceMap.put("lot_no",lotNo);
			invallocTraceMap.put("lot_sl",lotSl);

			invallocTraceMap.put("alloc_qty",new Double(-1 * DeallocQty));

			System.out.print("DeallocQty = " + (-1*DeallocQty));
			invallocTraceMap.put("chg_user",chgUser);
			invallocTraceMap.put("chg_term",chgTerm);
			//invallocTraceMap.put("chg_win","W_SORD_DEALLOC"); dealloc_art
			invallocTraceMap.put("chg_win","W_DEALLOC_ART");
			
			if (DeallocQty > 0 && saleOrder.trim().length()>0)
			{
				invallocTraceMap.put("ref_id",saleOrder);
				invallocTraceMap.put("ref_line",lineNo);
				errString = invallocTraceBean.updateInvallocTrace(invallocTraceMap,conn);

				if (errString != null && errString.trim().length() > 0)
				{
					System.out.println("errString :::"+ errString );
					return errString;
				}

				updateSorditem ="UPDATE SORDITEM  SET QTY_ALLOC = QTY_ALLOC - " + new Double(DeallocQty).toString()
							   +" WHERE SALE_ORDER = '" + saleOrder + "' "
							   +" AND LINE_NO = '" + lineNo + "' "
							   +" AND EXP_LEV = '" + expLev + "' ";

				System.out.println("updateSql------->"+updateSorditem);
				st.executeUpdate(updateSorditem);
				System.out.println("UPDATE  SUCCESS FOR SORDITEM....>>>>>>>>");

				updateSql = " UPDATE SORDALLOC SET QTY_ALLOC =  QTY_ALLOC - " + new Double(DeallocQty).toString()
							  + " WHERE SALE_ORDER = '" + saleOrder + " ' "
							  + " AND LINE_NO = '" + lineNo + "' "
							  + " AND EXP_LEV = '" + expLev + "' "
							  + " AND ITEM_CODE = '" + itemCode + "' "
							  + " AND LOT_NO = '" + lotNo + "' "
							  + " AND LOT_SL = '" + lotSl + "' "
							  + " AND LOC_CODE = '" + locCode + "' " ;
				System.out.println("updateSql:::>>>>"+ updateSql);
				st.executeUpdate(updateSql);
				System.out.println("UPDATE  SUCCESS FOR SORDALLOC....");
				SqlQtyAlloc = "SELECT QTY_ALLOC FROM SORDALLOC "
							+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
							+ " AND LINE_NO = '" + lineNo + "' "
							+ " AND EXP_LEV = '" + expLev + "' "
							+ " AND ITEM_CODE = '" + itemCode + "' "
							+ " AND LOT_NO = '" + lotNo + "' "
							+ " AND LOT_SL = '" + lotSl + "' "
							+ " AND LOC_CODE = '" + locCode + "' " ;
				rsQtyAlloc  = st.executeQuery(SqlQtyAlloc);
				if (rsQtyAlloc.next())
				{
					System.out.println("Updated Allocated Qty :::"+rsQtyAlloc.getDouble(1));
					if(rsQtyAlloc.getDouble(1)<=0)
					{
						DelQtyAlloc = "DELETE FROM SORDALLOC"
									+ " WHERE SALE_ORDER = '" + saleOrder + " ' "
									+ " AND LINE_NO = '" + lineNo + "' "
									+ " AND EXP_LEV = '" + expLev + "' "
									+ " AND ITEM_CODE = '" + itemCode + "' "
									+ " AND LOT_NO = '" + lotNo + "' "
									+ " AND LOT_SL = '" + lotSl + "' "
									+ " AND LOC_CODE = '" + locCode + "' " ;
						System.out.println("DelQtyAlloc:::"+DelQtyAlloc);
						st.executeUpdate(DelQtyAlloc);
						System.out.println("Delete completed ");
					}
				}
				rsQtyAlloc.close();
				
				/*sql = "SELECT DESP_ID FROM DESPATCHDET WHERE SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
						" D.DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER ='S-DSP' AND SALE_ORDER = ? ) " +
						" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, saleOrder);
				pstmt1.setString(2, lineNo);
				pstmt1.setString(3, expLev);
				pstmt1.setString(4, waveId);
				pstmt1.setString(5, saleOrder);
				pstmt1.setString(6, itemCode);
				pstmt1.setString(7, lotNo);
				pstmt1.setString(8, lotSl);
				pstmt1.setString(9, locCode);
				rs1 = pstmt1.executeQuery();
				if( rs1.next())
				{
					
				}*/
				
				sql = "";
				sql = " SELECT ( QUANTITY - ?) AS QUANTITY, DESP_ID FROM DESPATCHDET WHERE SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
						" AND DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = 'S-DSP' AND SALE_ORDER = ? )" +
						" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setDouble(1, DeallocQty);
				pstmt.setString(2, saleOrder);
				pstmt.setString(3, lineNo);
				pstmt.setString(4, expLev);
				pstmt.setString(5, waveId);
				pstmt.setString(6, saleOrder);
				pstmt.setString(7, itemCode);
				pstmt.setString(8, lotNo);
				pstmt.setString(9, lotSl);
				pstmt.setString(10, locCode);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					quantity = rs.getDouble("QUANTITY");
					despId = rs.getString("DESP_ID");				
				
					if( quantity <= 0 )
					{
						sql = "";
						sql = " DELETE FROM DESPATCHDET WHERE SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
								" AND DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = 'S-DSP' AND SALE_ORDER = ? )" +
								" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
						pstmt1 = conn.prepareStatement(sql);					
						pstmt1.setString(1, saleOrder);
						pstmt1.setString(2, lineNo);
						pstmt1.setString(3, expLev);
						pstmt1.setString(4, waveId);
						pstmt1.setString(5, saleOrder);
						pstmt1.setString(6, itemCode);
						pstmt1.setString(7, lotNo);
						pstmt1.setString(8, lotSl);
						pstmt1.setString(9, locCode);
						
						if ( pstmt1.executeUpdate() > 0 )
						{
							System.out.println(despId+" deleted successfully ");
						}
						pstmt1.close(); pstmt1 = null;				
						
					}
					else
					{
						sql = ""; //QUANTITY__STDUOM, QUANTITY_REAL
						sql = " UPDATE DESPATCHDET SET QUANTITY = (QUANTITY - ?), QUANTITY__STDUOM = ( QUANTITY__STDUOM - ?), " +
								" QUANTITY_REAL = (QUANTITY_REAL - ? )  WHERE  SORD_NO = ? AND LINE_NO__SORD = ? AND EXP_LEV = ? " +
								" AND DESP_ID = (SELECT REF_ID FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND REF_SER = 'S-DSP' AND SALE_ORDER = ? )" +
								" AND ITEM_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND LOC_CODE = ? ";
						pstmt1 = conn.prepareStatement(sql);
						pstmt1.setDouble(1, DeallocQty);
						pstmt1.setDouble(2, DeallocQty);
						pstmt1.setDouble(3, DeallocQty);
						pstmt1.setString(4, saleOrder);
						pstmt1.setString(5, lineNo);
						pstmt1.setString(6, expLev);
						pstmt1.setString(7, waveId);
						pstmt1.setString(8, saleOrder);
						pstmt1.setString(9, itemCode);
						pstmt1.setString(10, lotNo);
						pstmt1.setString(11, lotSl);
						pstmt1.setString(12, locCode);
						
						if ( pstmt1.executeUpdate() > 0 )
						{
							System.out.println(despId+" updated successfully ");
						}
						pstmt1.close(); pstmt1 = null;
					}
				}
				rs.close();rs = null;
				pstmt.close(); pstmt = null;				
				
			}
			st.close();
			invallocTraceMap.clear();
			conn.commit();
		}		
		catch(Exception e)
		{
			System.out.println("Exception :" + e);
			errString = e.getMessage();
			e.printStackTrace();
			try
			{
				conn.rollback();
			}
			catch(Exception e1)
			{
				e = e1;
			}
			
			return errString ;
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if( rs != null)
					{
						rs.close(); rs = null;
					}
					if( pstmt != null )
					{
						pstmt.close(); pstmt = null;
					}
					if(st != null)
					{
						st.close();
						st = null;
					}
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				errString = e.getMessage();
				e.printStackTrace();
			}			
		}	
		
		return errString;
	}
	
	//Checking where parent repl exist 
	private HashMap isReplIssueDone(String pickOrder, int pickOrderLine, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean isRepl = false;
		HashMap replOrder = new HashMap();
		String tempReplOrder = "";
		try
		{
			sql = "SELECT REPL_ORDER FROM PICK_ORD_DET WHERE PICK_ORDER = ? AND LINE_NO = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, pickOrder);
			pstmt.setInt(2, pickOrderLine);			
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				tempReplOrder = checkNull(rs.getString("REPL_ORDER"));				
			}			
			if( tempReplOrder.trim().length() > 0 )
			{
				replOrder.put("isRepl", "YES");
				replOrder.put("repl_order", rs.getString("REPL_ORDER"));
			}
			else
			{
				replOrder.put("isRepl", "NO");
				replOrder.put("repl_order", "");
			}
			rs.close();rs = null;
			pstmt.close(); pstmt = null;			
			System.out.println(" is replOrder exist ["+replOrder+"]");				
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}		
		return replOrder;
	}
	private HashMap isParentExist(String replOrder, Connection conn) throws ITMException
	{
		String sql = "";
		String pickOrder = "";
		String confirmed = "";
		PreparedStatement pstmt = null, pstmt1 = null, pstmt2 = null;
		ResultSet rs = null, rs1 = null, rs2 = null;
		HashMap parentExist =  new HashMap();
		try
		{
			sql = "SELECT PICK_ORDER FROM REPL_ORD_DET WHERE REPL_ORDER = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, replOrder);
			rs = pstmt.executeQuery();
			if( rs.next() )
			{
				pickOrder = checkNull(rs.getString("PICK_ORDER"));
			}
			
			if( pickOrder.trim().length() > 0)
			{				
				parentExist.put("paren_exist", "YES");
				parentExist.put("pick_order", pickOrder);
			}
			else
			{
				parentExist.put("paren_exist", "NO");
				parentExist.put("pick_order", " ");				
			}
			rs.close();rs = null;
			pstmt.close(); pstmt = null;
				
			/*if( pickOrder.trim().length() > 0 )
			{*/				
				/*parentExist.put("paren_exist", "YES");
				parentExist.put("pick_order", pickOrder);*/
				
				sql = "";
				sql = " SELECT RID.REPL_ORDER, RIH.CONFIRMED, RID.ITEM_CODE, RID.LOT_NO, RID.LOT_SL, RID.LOC_CODE__TO, RID.LOC_CODE " +
						" FROM REPL_ISS_DET RID, REPL_ISS_HDR RIH WHERE RIH.REPL_ORDER = ? " +
						" AND RIH.TRAN_ID = RID.TRAN_ID ";
				pstmt1 = conn.prepareStatement(sql);
				pstmt1.setString(1, replOrder);
				rs1 = pstmt1.executeQuery();
				if( rs1.next() )
				{
					confirmed = checkNull(rs1.getString("CONFIRMED"));
					System.out.println( replOrder+" is confirmed ["+confirmed+"]");
					if("Y".equalsIgnoreCase(confirmed))
					{
						parentExist.put("confirmed", confirmed);
						parentExist.put("item_code", rs1.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs1.getString("LOT_NO"));
						parentExist.put("lot_sl", rs1.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs1.getString("LOC_CODE__TO"));
						
					}
					else
					{
						parentExist.put("confirmed", "N");
						parentExist.put("item_code", rs1.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs1.getString("LOT_NO"));
						parentExist.put("lot_sl", rs1.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs1.getString("LOC_CODE"));
					}
				}
				else
				{
					//Getting record from repl_order 
					sql = "";
					sql = " SELECT ITEM_CODE, LOC_CODE, LOT_NO, LOT_SL FROM REPL_ORD_DET WHERE REPL_ORDER = ? ";
					pstmt2 = conn.prepareStatement(sql);
					pstmt2.setString(1, replOrder);
					rs2 = pstmt2.executeQuery();
					if( rs2.next() )
					{
						parentExist.put("item_code", rs2.getString("ITEM_CODE"));
						parentExist.put("lot_no", rs2.getString("LOT_NO"));
						parentExist.put("lot_sl", rs2.getString("LOT_SL"));
						parentExist.put("loc_code__to", rs2.getString("LOC_CODE"));
					}
					parentExist.put("confirmed", "N");
					rs2.close();rs2 = null;
					pstmt2.close();pstmt2 = null;					
					
				}
				rs1.close();rs1 = null;
				pstmt1.close(); pstmt1 = null;
				
			//}
			/*else
			{*/
				
			//}
							
			System.out.println(" parentExist --> "+parentExist);
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if(rs != null)
				{
					rs.close();rs = null;
				}
				if( rs1 != null)
				{
					rs1.close(); rs1 = null;
				}
				if( rs2 != null)
				{
					rs2.close(); rs2 = null;
				}
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
				if( pstmt1 != null )
				{
					pstmt1.close(); pstmt1 = null;
				}
				if( pstmt2 != null )
				{
					pstmt2.close(); pstmt2 = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);				
			}
		}
		
		return parentExist;
	}
	// Updating repl order
	private int updateReplOrder(String replOrder, String siteCode, String itemCode, String locCode, String lotNo, String lotSl, double DeallocQty, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int update = 0;
		try
		{
			//sql = "";
			sql = "UPDATE REPL_ORD_DET SET ACTUAL_QTY = ( ACTUAL_QTY + ?) WHERE REPL_ORDER = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, DeallocQty );
			pstmt.setString(2, replOrder);
			//pstmt2.setInt(3, pickLineNo);
			update = pstmt.executeUpdate();
			if( update > 0 )
			{
				System.out.println(replOrder+" repl_order updated successfully ");
			}			
			pstmt.close();pstmt = null;		
					
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		
		return update;
	}
	private int updatePickOrder(String pickOrder, int pickLineNo, double DeallocQty, Connection conn) throws ITMException
	{
		String sql = "";
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int update = 0;
		try
		{			
			sql = "UPDATE PICK_ORD_DET SET NO_ART = ( NO_ART - 1), QUANTITY = (QUANTITY - ?) WHERE PICK_ORDER = ? AND LINE_NO = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, DeallocQty );
			pstmt.setString(2, pickOrder);
			pstmt.setInt(3, pickLineNo);
			update = pstmt.executeUpdate();
			if( update > 0 )
			{
				System.out.println(pickOrder+" pick_order updated successfully ");
			}			
			pstmt.close();pstmt = null;
			
			sql = "UPDATE PICK_ISS_DET SET NO_ART = ( NO_ART - 1), QUANTITY = (QUANTITY - ?) WHERE PICK_ORDER = ? AND LINE_NO__ORD = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, DeallocQty );
			pstmt.setString(2, pickOrder);
			pstmt.setInt(3, pickLineNo);
			update = pstmt.executeUpdate();
			if( update > 0 )
			{
				System.out.println(pickOrder+" pick_order updated successfully ");
			}			
			pstmt.close();pstmt = null;	
					
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				if( pstmt != null)
				{
					pstmt.close(); pstmt = null;
				}
			}
			catch(Exception ex)
			{
				ex.printStackTrace();
				throw new ITMException(ex);
			}
		}
		
		return update;
	}
	
}
