/**
 * PURPOSE : Confirmation of Picking issue transaction
 * AUTHOR : Gulzar on 13/09/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.*;


@Stateless 
public class PickIssConf extends ActionHandlerEJB implements PickIssConfLocal, PickIssConfRemote
{
	/**
	 * The public function is used to confirm a transaction corresponding to the tran id it takes as an argument
	 * @param : tranId
	 * @param : xtraParams
	 */
	// added by cpandey for overloading of confirm method - 07/11/11 
	public String confirm(String tranID,String xtraParams, String forcedFlag) throws RemoteException,ITMException
	{

		String retString = "";
		boolean isConn= false;
		Connection conn = null;

		try
		{
			retString = confirm( tranID, xtraParams, forcedFlag, conn, isConn );

			if ( retString != null && retString.length() > 0 )
			{
				throw new Exception("Exception while calling confirm for tran  Id:["+tranID+"]");
			}
		}
		catch(Exception exception)
		{
			System.out.println("Exception in [InvHoldConfEJB] getCurrdateAppFormat " + exception.getMessage());
		}
		return retString;
	}
	public String confirm( String tranId, String xtraParams,String forcedFlag, Connection conn, boolean connStatus ) throws RemoteException,ITMException
	{		
		String replOrder = "";
		String confirmed = "";
		String retString = "";
		String itemCode = "";
		String siteCode = "";
		String locCode = "";
		String lotNo = "";
		String lotSl = "";		
		String locCodeTo = "";
		String pickOrder = "";
		String ptcn = "";
		String dExpLev = "";
		String sql = "";
		String updateSql = "";

		java.sql.Timestamp expDate = null;
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp restestDate = null;
		String packCode = "";
		String siteCodeMfg = "";
		String packInstr = "";
		String suppCodeMfg = "";
		String unitAlt = "";
		String batchNo = "";
		String unit = "";
		String grade = "";
		String remarks = "";
		String dimension = "";
		String ediOption = "";
		String dataStr = "";
		String acctCodeDr = "";
		String cctrCodeDr = "";
		String saleOrder = "";
		String lineNoSord = "";
		String pickType = "";
		String stagingLoc =  "";//STAGING_LOC
		

		double grossRate = 0d;
		double convQtyStduom = 0d;
		double batchSize = 0d;
		double stkGrossRate = 0d;
		double stkRate = 0d;

		int linoNoOrd = 0;
		int lineNo = 0;
		int updCnt = 0;
		int lineNoOrd = 0;

		double quantity = 0.0;
		double noArt = 0.0;
		double noArtIss = 0d;
		double noArtOrd = 0d;

		HashMap updateRowMap = null;
		HashMap strAllocate = null;
		StockUpdate stkUpdate = null;
		InvAllocTraceBean invAllocTrace = null;
		//Changed by sumit on 07/08/12 for getting pick_order end.
		PreparedStatement pstmt1, pstmt2 =null;
		ResultSet rs1 = null;
		//Changed by sumit on 07/08/12 for getting pick_order start.
		//Connection conn = null;		
		PreparedStatement pstmt = null;
		PreparedStatement pstmtIss = null;
		PreparedStatement pstmtStock = null;
		PreparedStatement pstmtUpd = null;
		ResultSet rsStock = null;
		ResultSet rs = null;
		ResultSet rsIss = null;
		boolean isError = false;

		PreparedStatement pstmtUpdPickOrdDet =null;
		int updCntPickOrdDet=0;
		
		GenericUtility genericUtility = GenericUtility.getInstance();

		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		DistCommon distCommon = new DistCommon();
		
		boolean isUpdatehdr= false;
		// 10/10/12 manoharan
		HashMap itmVolumeMap = new HashMap();
		
		double shipperSize = 0,	itemGrossWeight = 0, caseGrossWeight = 0, itemNetWeight = 0, caseNetWeight = 0;
		// end 10/10/12 manoharan
		// 18/09/12 manoharan
		String  expLev = "", itemCodeOrd = "", itemRef = "",  status = "", itemGrade = "", allocMode = "";
		String reasCode = "",unitStd = "";
		java.sql.Timestamp dateAlloc = null;
		double pickQty = 0, sordAllocQty = 0,qtyOrd = 0.0;
		//double convQtyStduom = 0;
		
		try
		{
			if ( conn == null )
			{
				ConnDriver connDriver = new ConnDriver();
				conn = connDriver.getConnectDB("DriverITM");
				connDriver = null;
				conn.setAutoCommit(false);
				connStatus = true;
			}

			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			String empCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginEmpCode" );

			String dbDateFormat = genericUtility.getDBDateFormat();
			String applDateFormat = genericUtility.getApplDateFormat();
			java.util.Date currDate1 = new java.util.Date();
			SimpleDateFormat sdf = new SimpleDateFormat(applDateFormat);
			String currDateStr = sdf.format(currDate1);

			java.sql.Timestamp tranDate = java.sql.Timestamp.valueOf(genericUtility.getValidDateString( currDateStr , applDateFormat, dbDateFormat ) + " 00:00:00.0") ;

			updateRowMap = new HashMap();
			stkUpdate = new StockUpdate();

			sql = "SELECT CONFIRMED, PICK_ORDER,PICK_TYPE FROM PICK_ISS_HDR WHERE TRAN_ID = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				confirmed = rs.getString( "CONFIRMED" );
				pickOrder = rs.getString( "PICK_ORDER" );
				pickType = rs.getString( "PICK_TYPE" );
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;

			if( confirmed.equalsIgnoreCase("Y") )
			{
				isError = true;
				retString = itmDBAccess.getErrorString("","PCKISSCONF","");
				return retString;
			}
			else
			{
				sql = "SELECT SUM(NO_ART) NO_ART FROM PICK_ISS_DET WHERE TRAN_ID = ? AND PICK_ORDER = ? AND LINE_NO__ORD = ? GROUP BY PICK_ORDER, LINE_NO__ORD ";
				pstmtIss = conn.prepareStatement(sql);

				sql = "SELECT LINE_NO, NO_ART FROM PICK_ORD_DET WHERE PICK_ORDER = ? ORDER BY PICK_ORDER, LINE_NO ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString( 1, pickOrder );
				rs = pstmt.executeQuery();
				while ( rs.next() )
				{
					linoNoOrd = rs.getInt("LINE_NO");
					noArtOrd = rs.getDouble("NO_ART");

					pstmtIss.setString( 1, tranId );
					pstmtIss.setString( 2, pickOrder );
					pstmtIss.setInt( 3, linoNoOrd );
					rsIss = pstmtIss.executeQuery();
					if ( rsIss.next() )
					{
						noArtIss = rs.getDouble("NO_ART");
					}
					pstmtIss.clearParameters();
					rsIss.close(); rsIss = null;

					if ( noArtOrd != noArtIss )
					{
						//isError = true;
						//retString = itmDBAccess.getErrorString("","ORDNOTSCAN","");
						//return retString;
						System.out.println("@@@@ NOT MATCHING ....LINE NUMBER ["+linoNoOrd+" ] AND NO_ART :[ "+noArtOrd +" ] ");
					}
				}
				System.out.println("Current Pick type -->>["+pickType+"]");
				if("M".equalsIgnoreCase(pickType))
				{
				
					stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC_MPK", conn)==null?"":distCommon.getDisparams( "999999", "STAGING_LOC_MPK", conn);
				}
				else
				{
					stagingLoc = distCommon.getDisparams( "999999", "STAGING_LOC", conn)==null?"":distCommon.getDisparams( "999999", "STAGING_LOC", conn);
				}
				
				System.out.println("Statgic location -->>["+stagingLoc+"]");
				//String 

				// 17/10/11 manoharan to update stock if loc_code__to <> loc_code
				stkUpdate = new StockUpdate();
				invAllocTrace = new InvAllocTraceBean();
				sql = "select acct_code__inv, cctr_code__inv, "
						+ "exp_date, pack_code, mfg_date, site_code__mfg, "
						+ "pack_instr, supp_code__mfg, retest_date, rate, "
						+ "gross_rate, conv__qty_stduom, unit__alt, batch_no, batch_size, unit, grade, remarks, dimension "
						+ "from stock "
						+ "where item_code = ? "
						+ "and site_code = ? "
						+ "and loc_code = ? "
						+ "and lot_no = ? "
						+ "and lot_sl = ? ";
				pstmtStock = conn.prepareStatement(sql);

				sql = "SELECT R.LINE_NO LINE_NO, R.ITEM_CODE ITEM_CODE, R.SITE_CODE SITE_CODE, R.LOC_CODE LOC_CODE, "
						+ " R.LOT_NO LOT_NO, R.LOT_SL LOT_SL, R.QUANTITY QUANTITY, R.NO_ART NO_ART, "
						+ " R.LOC_CODE__TO LOC_CODE__TO, O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, O.PICK_ORDER,H.PICK_TYPE,R.LINE_NO__ORD,O.EXP_LEV EXP_LEV "
						+ " FROM PICK_ISS_DET R,  PICK_ORD_DET O ,PICK_ORD_HDR H"//CHANGES DONE BY CHITRANJAN PICK_ORD_HDR H  TO GET PICK TYPE 
						+ " WHERE R.TRAN_ID = ? "
						+ " AND R.PICK_ORDER = O.PICK_ORDER "   
						+"AND H.PICK_ORDER = O.PICK_ORDER" //CHANGES DONE BY CHITRANJAN 
						+ " AND O.LINE_NO = R.LINE_NO__ORD " 
				+ " AND R.LOC_CODE <> R.LOC_CODE__TO ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();
				while(rs.next())
				{

					itemCode = rs.getString( "item_code" );
					siteCode = rs.getString( "site_code" );
					locCode = rs.getString( "loc_code" );
					lotNo = rs.getString( "lot_no" );
					lotSl = rs.getString( "lot_sl" );
					quantity = rs.getDouble( "quantity" );
					noArt = rs.getDouble( "no_art" );
					locCodeTo = rs.getString( "loc_code__to" );
					saleOrder = rs.getString( "SALE_ORDER" );
					lineNoSord = rs.getString( "LINE_NO__SORD" );
					lineNoSord = "   " + lineNoSord; 
					lineNoSord = lineNoSord.substring(lineNoSord.length()-3);
					replOrder = rs.getString( "PICK_ORDER" );
					pickType = rs.getString( "PICK_TYPE" );
					lineNoOrd = rs.getInt( "LINE_NO__ORD" ); 
					System.out.println("pick type from hdr->> ["+pickType+"]");
					dExpLev = checkNullAndTrim(rs.getString( "EXP_LEV" ));
					System.out.println("dExpLev->> ["+dExpLev+"]");
					// 10/10/12 manoharan weight to be set properly
					itmVolumeMap.clear();
					itmVolumeMap = getItemVoumeMap(itemCode,lotNo,conn);
					shipperSize = (Double) itmVolumeMap.get("SHIPPER_SIZE");
					itemGrossWeight = (Double) itmVolumeMap.get("ITEM_GROSS_WEIGHT");
					caseGrossWeight = (Double) itmVolumeMap.get("CASE_GROSS_WEIGHT");
					itemNetWeight = (Double) itmVolumeMap.get("ITEM_NET_WEIGHT");
					caseNetWeight = (Double) itmVolumeMap.get("CASE_NET_WEIGHT");
					// end 10/10/12 manoharan

					// 16/10/11 manoharan check whether allocation against sales order is there
					// if allocation against sales order found change allocation to point to loc_code__to
					// and deallocate the same for loc_code in stock
					// update sordalloc with loc_code__to
					// This utility methods to update old and new location should be in 
					// a separate probably in stockupdate so that we can call the same from various places 
					//
					// 
					lineNo = rs.getInt( "LINE_NO" );

					pstmtStock.setString(1,itemCode);
					pstmtStock.setString(2,siteCode);
					pstmtStock.setString(3,locCode);
					pstmtStock.setString(4,lotNo);
					pstmtStock.setString(5,lotSl);
					rsStock = pstmtStock.executeQuery();
					if ( rsStock.next() )
					{
						acctCodeDr = rsStock.getString("acct_code__inv");
						cctrCodeDr = rsStock.getString("cctr_code__inv");

						expDate = rsStock.getTimestamp("exp_date");
						packCode = rsStock.getString("pack_code");
						mfgDate = rsStock.getTimestamp("mfg_date");
						siteCodeMfg = rsStock.getString("site_code__mfg");
						packInstr = rsStock.getString("pack_instr");
						suppCodeMfg = rsStock.getString("supp_code__mfg");
						restestDate = rsStock.getTimestamp("retest_date");
						stkRate = rsStock.getDouble("rate");
						stkGrossRate = rsStock.getDouble("gross_rate");
						convQtyStduom = rsStock.getDouble("conv__qty_stduom");
						unitAlt = rsStock.getString("unit__alt");
						batchNo = rsStock.getString("batch_no");
						batchSize = rsStock.getDouble("batch_size");
						unit = rsStock.getString("unit");
						grade = rsStock.getString("grade");
						remarks = rsStock.getString("remarks");
						dimension = rsStock.getString("dimension");
					}
					rsStock.close(); rsStock = null;
					pstmtStock.clearParameters();
					//changes done by cpandey on 07/08/12 
					//if(!("A".equalsIgnoreCase(pickType)))
					//{   	 
						updateRowMap.put("item_code", itemCode);
						updateRowMap.put("site_code", siteCode);
						updateRowMap.put("loc_code", locCode);
						updateRowMap.put("lot_no", lotNo);
						updateRowMap.put("lot_sl", lotSl);
						updateRowMap.put("quantity", ""+quantity);
						updateRowMap.put("no_art", noArt);
						updateRowMap.put("tran_ser", "PICISS");
						updateRowMap.put("acct_code__cr",acctCodeDr);
						updateRowMap.put("cctr_code__cr",cctrCodeDr);
						updateRowMap.put("acct_code_inv",acctCodeDr);
						updateRowMap.put("cctr_code_inv",cctrCodeDr);
						updateRowMap.put("rate",Double.toString(stkRate));
						updateRowMap.put("gross_rate",Double.toString(stkGrossRate));
						updateRowMap.put("tran_id", tranId );
						// 17/10/11 manoharan
						updateRowMap.put("line_no", "" + lineNo );
						// end 17/10/11 manoharan
						updateRowMap.put("tran_date", tranDate );
						updateRowMap.put("tran_type","ID"); 

						updateRowMap.put("qty_stduom", ""+quantity);
						updateRowMap.put("unit",unit);
						updateRowMap.put("grade",grade);
						updateRowMap.put("remarks",remarks);
						updateRowMap.put("dimension",dimension);

						//updateRowMap.put("tran_type", "I");//Creating issue

						//changed by gulzar on 12/24/2011
						updateRowMap.put("exp_date", expDate);
						updateRowMap.put("pack_code", packCode);
						updateRowMap.put("mfg_date", mfgDate);
						updateRowMap.put("site_code__mfg", siteCodeMfg);
						updateRowMap.put("pack_instr", packInstr);
						updateRowMap.put("supp_code__mfg", suppCodeMfg);
						updateRowMap.put("retest_date", restestDate);
						updateRowMap.put("conv__qty_stduom", ""+convQtyStduom);
						updateRowMap.put("unit__alt", unitAlt);
						updateRowMap.put("batch_no", batchNo);
						updateRowMap.put("batch_size", ""+batchSize);
						//End changes by gulzar on 12/24/2011
					//}
					// 10/10/12 manoharan weight and no_art to be set properly
					if("A".equalsIgnoreCase(pickType))
					{
						noArt = 1;
					}
					else
					{
						noArt = Math.floor((quantity) / shipperSize) ;
					}
					updateRowMap.put("no_art", noArt);
					updateRowMap.put("gross_weight", caseGrossWeight * noArt);
					updateRowMap.put("net_weight", caseNetWeight * noArt);
					updateRowMap.put("tare_weight", (caseGrossWeight - caseNetWeight) * noArt);
					// end 10/10/12  manoharan
					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}
					if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
					{
						updateRowMap.put("loc_code", stagingLoc);
					}
					else 
					{
						updateRowMap.put("loc_code", locCodeTo);
					}
					updateRowMap.put("tran_type", "R");//Creating receipt

					retString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					if ( retString != null && retString.trim().length() > 0 )
					{
						isError = true;
						break;
					}

					updateRowMap.clear();
					// 16/10/11 manoharan check whether allocation/hold are there
					// if so update the same
					//added by cpandey on 
					/*if(!("A".equalsIgnoreCase(pickType)) && !("M".equalsIgnoreCase(pickType)) )
					{*/
						strAllocate = new HashMap();
						strAllocate.put("tran_date",tranDate);
						strAllocate.put("ref_ser","PICISS");
						strAllocate.put("ref_id",tranId);
						strAllocate.put("ref_line", "" + lineNo);
						strAllocate.put("site_code",siteCode);
						strAllocate.put("item_code",itemCode);
						//strAllocate.put("loc_code",locCodeTo);
						if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
						{
							strAllocate.put("loc_code", stagingLoc);
						}
						else 
						{
							strAllocate.put("loc_code", locCodeTo);
						}
												
						strAllocate.put("lot_no",lotNo);
						strAllocate.put("lot_sl",lotSl);
						strAllocate.put("alloc_qty",new Double(quantity));
						strAllocate.put("chg_user",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"loginCode"));
						strAllocate.put("chg_term",GenericUtility.getInstance().getValueFromXTRA_PARAMS(xtraParams,"termId"));
						strAllocate.put("chg_win","w_pick_iss");
						retString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
						System.out.println("retString ::: " + retString);
						if (retString != null && retString.trim().length() > 0 )
						{
							isError = true;
							break;
						}
					//}

						//Changed By 07/12/12 Pragyan To check the sordalloc allocated quantity > quantity  then insert new record deducte quantity from old location.start
						sql = " SELECT EXP_LEV, ITEM_CODE__ORD, "
							+ " ITEM_REF, UNIT, DATE_ALLOC, STATUS, ITEM_GRADE, EXP_DATE, ALLOC_MODE, SITE_CODE, "
							+ " CONV__QTY_STDUOM, UNIT__STD,  MFG_DATE, SITE_CODE__MFG, REAS_CODE,QUANTITY,QTY_ALLOC FROM SORDALLOC  "
							+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
							+ " AND ITEM_CODE = ? "
							+ " AND LOC_CODE = ? "
							+ " AND LOT_NO = ? "
							+ " AND LOT_SL = ? "
							+ " AND QTY_ALLOC > 0";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, saleOrder);
							pstmt1.setString(2, lineNoSord);				
							pstmt1.setString(3, itemCode);				
							pstmt1.setString(4, locCode);				
							pstmt1.setString(5, lotNo);				
							pstmt1.setString(6, lotSl);				
							rs1 = pstmt1.executeQuery();
							
							if( rs1.next())
							{
							
								System.out.println("Inside the Query the Exp level["+expLev+"]");
								expLev = rs1.getString( "EXP_LEV" );
								System.out.println("Inside the Query the Exp level["+expLev+"]");
								if (expLev == null || expLev.trim().length() == 0)
								{
									expLev = dExpLev;
								}
								if (expLev == null || expLev.trim().length() == 0)
								{
									expLev = " ";
								}
								itemCodeOrd = rs1.getString( "ITEM_CODE__ORD" );
								itemRef = rs1.getString( "ITEM_REF" );
								unit = rs1.getString( "UNIT" );
								status = rs1.getString( "STATUS" );
								itemGrade = rs1.getString( "ITEM_GRADE" );
								allocMode = rs1.getString( "ALLOC_MODE" );
								siteCode = rs1.getString( "SITE_CODE" );
								siteCodeMfg = rs1.getString( "SITE_CODE__MFG" );
								reasCode = rs1.getString( "REAS_CODE" );
								unitStd = rs1.getString( "UNIT__STD" );
								convQtyStduom = rs1.getDouble( "CONV__QTY_STDUOM" );
								dateAlloc = rs1.getTimestamp("DATE_ALLOC");
								expDate = rs1.getTimestamp("EXP_DATE");
								mfgDate = rs1.getTimestamp("MFG_DATE");
								qtyOrd = rs1.getDouble( "QUANTITY" );
								sordAllocQty = rs1.getDouble( "QTY_ALLOC" );
							}
							else
							{
								retString = itmDBAccess.getErrorString("","RECNOTSORA","");
								return retString;
							}
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;
				
					
					if(sordAllocQty > quantity)
					{
							updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? " //LOC_CODE = ? ,LOT_SL = ? "
								+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
								+ " AND ITEM_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? "
								+ " AND QTY_ALLOC > 0";
							pstmtUpd = conn.prepareStatement(updateSql);
							pstmtUpd.setDouble(1, quantity); 
							pstmtUpd.setString(2, saleOrder);
							pstmtUpd.setString(3, lineNoSord);				
							pstmtUpd.setString(4, itemCode);				
							pstmtUpd.setString(5, locCode);				
							pstmtUpd.setString(6, lotNo);				
							pstmtUpd.setString(7, lotSl);				
		
							updCnt = pstmtUpd.executeUpdate();
							System.out.println("manohar 18/09/12 sordalloc updated  updCnt [" + updCnt + "]");
						
							if (expLev == null || expLev.trim().length() == 0)
							{
								System.out.println("Check1l["+expLev+"]");
								expLev = dExpLev;
							}
							System.out.println("Finally the Exp level["+expLev+"]");
							
							updateSql = "insert into sordalloc( SALE_ORDER, LINE_NO, EXP_LEV, ITEM_CODE__ORD, "
								+ " ITEM_CODE, LOT_NO, LOT_SL, LOC_CODE, ITEM_REF, QUANTITY, UNIT, "
								+ " QTY_ALLOC, DATE_ALLOC, STATUS, ITEM_GRADE, EXP_DATE, ALLOC_MODE, SITE_CODE, "
								+ " CONV__QTY_STDUOM, UNIT__STD, QUANTITY__STDUOM, MFG_DATE, SITE_CODE__MFG, "
								+ " REAS_CODE ) VALUES (?, ?, ?, ?, "
								+ " ?, ?, ? , ?, ?, ?, ?, "
								+ " ?,?, ?, ?, ?, ?, ?, "
								+ " ?, ?, ? , ?, ?, ?) ";
		
							pstmtUpd = conn.prepareStatement(updateSql);
							pstmtUpd.setString(1, saleOrder);
							pstmtUpd.setString(2, lineNoSord);
							pstmtUpd.setString(3, expLev);
							pstmtUpd.setString(4, itemCodeOrd);
							pstmtUpd.setString(5, itemCode);
							pstmtUpd.setString(6, lotNo);
							pstmtUpd.setString(7, lotSl);
							//pstmtUpd.setString(8, locCodeTo);
							if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
							{
								pstmtUpd.setString(8, stagingLoc);
							}
							else
							{	
								pstmtUpd.setString(8, locCodeTo);
							}	
							pstmtUpd.setString(9, itemRef);
							pstmtUpd.setDouble(10, qtyOrd);
							pstmtUpd.setString(11, unit);
							pstmtUpd.setDouble(12, quantity);
							pstmtUpd.setTimestamp(13, dateAlloc);
							pstmtUpd.setString(14, status);
							pstmtUpd.setString(15, itemGrade);
							pstmtUpd.setTimestamp(16, expDate);
							pstmtUpd.setString(17, allocMode);
							pstmtUpd.setString(18, siteCode);
							pstmtUpd.setDouble(19, convQtyStduom ); 
							pstmtUpd.setString(20,  unitStd); 
							pstmtUpd.setDouble(21, quantity * convQtyStduom); 
							pstmtUpd.setTimestamp(22, mfgDate ); 
							pstmtUpd.setString(23, siteCodeMfg);
							pstmtUpd.setString(24, reasCode);				
							updCnt = pstmtUpd.executeUpdate();
							System.out.println("manohar 18/09/12 sordalloc inserted  updCnt [" + updCnt + "]");
					}
					else
					{
							updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? "
								+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
								+ " AND ITEM_CODE = ? "
								+ " AND LOC_CODE = ? "
								+ " AND LOT_NO = ? "
								+ " AND LOT_SL = ? "
								+ " AND QTY_ALLOC > 0";
								pstmtUpd = conn.prepareStatement(updateSql);
								if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
								{
									pstmtUpd.setString(1, stagingLoc);
								}
								else
								{	
									pstmtUpd.setString(1, locCodeTo);
								}	
								pstmtUpd.setString(2, saleOrder);
								pstmtUpd.setString(3, lineNoSord);				
								pstmtUpd.setString(4, itemCode);				
								pstmtUpd.setString(5, locCode);				
								pstmtUpd.setString(6, lotNo);				
								pstmtUpd.setString(7, lotSl);				
			
								updCnt = pstmtUpd.executeUpdate();
								System.out.println("manohar 18/09/12 sordalloc Update  updCnt [" + updCnt + "]");
					}
					
				/*	strAllocate = null;
					// end 16/10/11 manoharan allocation stock for new location
					// 16/10/11 manoharan update sordalloc
					updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? "
							+ " WHERE SALE_ORDER = ? AND LINE_NO = ? "
							+ " AND ITEM_CODE = ? "
							+ " AND LOC_CODE = ? "
							+ " AND LOT_NO = ? "
							+ " AND LOT_SL = ? "
							+ " AND QTY_ALLOC > 0";
					pstmtUpd = conn.prepareStatement(updateSql);
					if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
					{
						pstmtUpd.setString(1, stagingLoc);
					}
					else
					{	
						pstmtUpd.setString(1, locCodeTo);
					}	
					pstmtUpd.setString(2, saleOrder);
					pstmtUpd.setString(3, lineNoSord);				
					pstmtUpd.setString(4, itemCode);				
					pstmtUpd.setString(5, locCode);				
					pstmtUpd.setString(6, lotNo);				
					pstmtUpd.setString(7, lotSl);				

					updCnt = pstmtUpd.executeUpdate();
					// end 16/10/11 manoharan update sordalloc
*/					//Changed on 07/12/12 By Pragyan To check the sordalloc allocated quantity > quantity  then insert new record deducte quantity from old location.end
					String updatePickOrdSql = "UPDATE PICK_ORD_DET SET STATUS = ? "
							+ " WHERE PICK_ORDER = ? AND LINE_NO = ? ";
							
					pstmtUpdPickOrdDet = conn.prepareStatement(updatePickOrdSql);
					pstmtUpdPickOrdDet.setString(1, "C");
					pstmtUpdPickOrdDet.setString(2, replOrder);
					pstmtUpdPickOrdDet.setInt(3, lineNoOrd);
									

					updCntPickOrdDet = pstmtUpdPickOrdDet.executeUpdate();
					
					if(updCntPickOrdDet > 0)
					{
						isUpdatehdr = updatePickOrderHeader(replOrder,conn);
					}
					
					updatePickOrdSql = "UPDATE PICK_ISS_DET SET LOC_CODE = ? "
						+ " WHERE TRAN_ID = ? AND LINE_NO = ? ";
						
					pstmtUpdPickOrdDet = conn.prepareStatement(updatePickOrdSql);
					if(("A".equalsIgnoreCase(pickType)) || ("M".equalsIgnoreCase(pickType)))
					{
						pstmtUpdPickOrdDet.setString(1, stagingLoc);
					}
					else
					{	
						pstmtUpdPickOrdDet.setString(1, locCodeTo);
					}	
					pstmtUpdPickOrdDet.setString(2, tranId);
					pstmtUpdPickOrdDet.setInt(3, lineNo);
									
	
					updCntPickOrdDet = pstmtUpdPickOrdDet.executeUpdate();
					
					if(updCntPickOrdDet > 0)
					{
						isUpdatehdr = updatePickOrderHeader(replOrder,conn);
					}
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				if (retString != null && retString.trim().length() > 0 )
				{
					isError = true;
					return retString;
				}
				
				
					
					
					updateSql = "UPDATE PICK_ISS_HDR SET CONFIRMED = 'Y', CONF_DATE = ?, EMP_CODE__APRV = ?"
							+ " WHERE TRAN_ID = ? ";
					pstmt = conn.prepareStatement(updateSql);
					pstmt.setTimestamp(1, currDate);
					pstmt.setString(2, empCode);
					pstmt.setString(3, tranId);				
	
					updCnt = pstmt.executeUpdate();
	
					if( updCnt > 0 )
					{					
						System.out.println( updCnt + " rows updated successfully" );
					}	
					pstmt.close();pstmt = null;
					
					if(isUpdatehdr)
					{
							//case added by cpandey in case of active it should status should not be update in wave_task_det on 08/08/12 
							if(!("A".equalsIgnoreCase(pickType)))
							{
							//Changed by sumit on 10/08/12 update wave_status of wave_task_det, same time of updating status 'Y'
							//updateSql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y' WHERE REF_ID = ? ";
								updateSql = "UPDATE WAVE_TASK_DET SET STATUS = 'Y', WAVE_STATUS = 'V' WHERE REF_ID = ? ";
								pstmt = conn.prepareStatement(updateSql);
								pstmt.setString(1, pickOrder);
				
								updCnt = pstmt.executeUpdate();
				
								if( updCnt > 0 )
								{					
									System.out.println( updCnt + " rows updated successfully" );
								}	
								pstmt.close();pstmt = null;
							}
							//end of addition on 08/08/12
							//changes by kunal on 08/08/12
							sql = "select ptcn from wave_task_det where ref_id = ?";
							pstmt = conn.prepareStatement(sql);
							pstmt.setString(1,replOrder);
							rs = pstmt.executeQuery();
							if( rs.next() )
							{
								ptcn = rs.getString("ptcn") == null?"":rs.getString("ptcn");
							}
							if( rs != null)
							{
								rs.close();
								rs = null;
							}
							if(pstmt != null)
							{
								pstmt.close();
								pstmt = null;
							}				
							
							//Changed by sumit on 07/08/12 for checking whether pick iss is inserted or not if yes then remaining, wave_task of M-Pack ref_ser will updated 'c' start.  
							sql = 	" SELECT * FROM PICK_ORD_HDR WHERE PICK_ORDER = ? AND PICK_TYPE = 'M' ";
							pstmt1 = conn.prepareStatement(sql);
							pstmt1.setString(1, pickOrder);
							rs1 = pstmt1.executeQuery();
							if( rs1.next())
							{
								updateSql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE REF_SER = 'M-PACK'" +
										" AND WAVE_ID = ?  AND SALE_ORDER = ? ";
								pstmt2 = conn.prepareStatement(updateSql);
								pstmt2.setString(1, rs1.getString("WAVE_ID"));
								pstmt2.setString(2, rs1.getString("SALE_ORDER"));
								updCnt = pstmt2.executeUpdate();
								if( updCnt > 0 )
								{					
									System.out.println( updCnt + " M-PACK rows updated successfully" );
								}
								pstmt2.close();pstmt2 = null;
							}
							rs1.close(); rs1 = null;
							pstmt1.close(); pstmt1 = null;
							
							updCnt= 0;
							// Checking whether all task are done for particular wave_id and sale order
							updCnt = waveStatusUpdate(pickOrder, conn);
							if( updCnt > 0)
							{
								System.out.println( updCnt + " row of wave_status updated successfully" );
							}
							
							//Changed by sumit on 07/08/12 for checking whether pick iss is inserted or not if yes then remaining, wave_task of M-Pack ref_ser will updated 'c' end.
							/*Added by Ashish Sonawane on 11-SEP-12 for calling consolidate to doc after status of PICK_ORD_HDR gets close [START]*/
								
								System.out.println("Header updated successfully");
								ConsolidatToDoc conToDoc = new ConsolidatToDoc();
								//Changed by Rohan on 10-06-12 to add xtraParams in process as argument for hazmap report to get chg user
								//retString = conToDoc.process(ptcn, conn);
								retString = conToDoc.process(ptcn, conn,xtraParams);
								if (retString != null && retString.trim().length() > 0 )
								{
									isError = true;
									return retString;
								}
								System.out.println("ret string :"+retString);			
					}	
					/*Added by Ashish Sonawane on 11-SEP-12 for calling consolidate to doc after status of PICK_ORD_HDR gets close [START]*/
			}
		}//	end of changes kunal
		catch( Exception e )
		{
			isError = true;
			e.printStackTrace();
			throw new ITMException(e);

		}
		finally
		{
			try
			{
				stkUpdate = null;
				invAllocTrace = null;
				if( !isError && connStatus )
				{
					conn.commit(); 
					retString = itmDBAccess.getErrorString("","CONFSUCESS","");
				}
				else if ( isError )
				{
					conn.rollback();
				}
				if(rs != null )
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				if(pstmtIss != null )
				{
					pstmtIss.close();
					pstmtIss = null;
				}
				if( conn != null && connStatus )
				{
					conn.close();
					conn = null;
				}
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return retString;
	}
	
	//Changed by sumit on 10/08/12 update wave_status after all task completed for wave_id and sale order start.
	private int waveStatusUpdate(String replOrder, Connection conn) throws ITMException
	{
		String sql = "", sql1 = "";
		String waveId = "";
		String saleOrder = "";
		PreparedStatement pstmt = null, pstmt1 = null;
		ResultSet rs = null, rs1 = null;
		int updateCount = 0;
		int firstCount = 0, secCount = 0;
		
		
		try
		{
			sql = "SELECT WAVE_ID, SALE_ORDER FROM PICK_ORD_HDR WHERE PICK_ORDER = ? ";			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, replOrder);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				waveId = rs.getString("WAVE_ID");
				saleOrder = rs.getString("SALE_ORDER");	
				
				sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, waveId);
			    pstmt1.setString(2, saleOrder);
			    rs1 = pstmt1.executeQuery();
			    if(rs1.next())
			    {
			    	firstCount = rs1.getInt("CONT");
			    }
			    rs1.close();rs1 = null;
			    pstmt1.close();pstmt1 = null;
				
			    sql1 = "SELECT COUNT(*) AS CONT FROM WAVE_TASK_DET WHERE WAVE_STATUS = 'V' AND WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER <> 'S-DSP' ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, waveId);
			    pstmt1.setString(2, saleOrder);
			    rs1 = pstmt1.executeQuery();
			    if(rs1.next())
			    {
			    	secCount = rs1.getInt("CONT");
			    }
			    rs1.close();rs1 = null;
			    pstmt1.close();pstmt1 = null;
				
				
			}
			rs.close();rs = null;
			pstmt.close();pstmt = null;
			
			if( secCount == firstCount)
			{
				sql = "UPDATE WAVE_TASK_DET SET WAVE_STATUS = 'C' WHERE WAVE_ID = ? AND SALE_ORDER = ? AND REF_SER = 'S-DSP' ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, waveId);
				pstmt.setString(2, saleOrder);
				updateCount = pstmt.executeUpdate();				
				pstmt.close();pstmt = null;
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		}		
		return updateCount;
	}
	//Changed by sumit on 10/08/12 update wave_status after all task completed for wave_id and sale order END.
	
	/* Added by Ashish Sonawane on 08-sep-12 for checking all the pick_ord_det record gets close(status) and depend on this closing (status) header from order. [START] */ 
	private boolean updatePickOrderHeader(String pickOrder ,Connection conn) throws ITMException
	{
		PreparedStatement pstmtPickOrdHdr =null;
		PreparedStatement pstmtUpdPickOrdHdr=null;
		ResultSet rsPicKordHdr =null;
		String status = "";
		Boolean isUpdateHdr=true;
		int updCntPickOrdHdr= 0;
		
		try {
			String sqlPickOrd = "SELECT STATUS FROM PICK_ORD_DET WHERE PICK_ORDER = ? ";
			pstmtPickOrdHdr = conn.prepareStatement(sqlPickOrd);
			pstmtPickOrdHdr.setString(1, pickOrder);
			
			rsPicKordHdr = pstmtPickOrdHdr.executeQuery();
			
			while(rsPicKordHdr.next())
			{
				status = rsPicKordHdr.getString("STATUS") == null ? "": rsPicKordHdr.getString("STATUS");
				if( !("C".equalsIgnoreCase(status) ))
				{
					isUpdateHdr =false;
					break;
				}
			}
			if(isUpdateHdr)
			{
				String updatePickOrdSql = "UPDATE PICK_ORD_HDR SET STATUS = ? "
						+ " WHERE PICK_ORDER = ? ";
						
				pstmtUpdPickOrdHdr = conn.prepareStatement(updatePickOrdSql);
				pstmtUpdPickOrdHdr.setString(1, "C");
				pstmtUpdPickOrdHdr.setString(2, pickOrder);
								

				updCntPickOrdHdr = pstmtUpdPickOrdHdr.executeUpdate();
				
				if(updCntPickOrdHdr > 0)
				{
					System.out.println("@@@@ Update Successfully");
					return true;
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new ITMException(e);
		}
	
		return false;
	}
	/* Added by Ashish Sonawane on 08-sep-12 for checking all the pick_ord_det record gets close(status) and depend on this closing (status) header from order. [END] */
	private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double shipperSize = 0,itemVolume = 0,caseVolume = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemGrossWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,caseGrossWeight = 0,itemNetWeight = 0, caseNetWeight = 0;
		HashMap dataVolumeMap = new HashMap();
		
		try {
			
			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_GROSS_WEIGHT,I.NET_WEIGHT ITEM_NET_WEIGHT,"
				  +" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT CASE_GROSS_WEIGHT,L.NET_WEIGHT CASE_NET_WEIGHT FROM"
				  +" ITEM I,ITEM_LOT_PACKSIZE L"
				  +" WHERE I.ITEM_CODE = L.ITEM_CODE"
				  +" AND L.LOT_NO__FROM <= ? AND L.LOT_NO__TO >= ?"
				  +" AND  I.ITEM_CODE = ?";
				  
			
			pstmt = con.prepareStatement(sql);
			if(lotNo != null && lotNo.length() > 0)
			{
				pstmt.setString(1, lotNo);
				pstmt.setString(2, lotNo);
			}
			else
			{
				pstmt.setString(1, "00");
				pstmt.setString(2, "ZZ");
			}
			pstmt.setString(3, itemCode);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				itmLen = rs.getDouble("ITEM_LEN");
				itmWidth = rs.getDouble("ITEM_WID");
				itmHeight = rs.getDouble("ITEM_HEIGHT");
				itemGrossWeight = rs.getDouble("ITEM_GROSS_WEIGHT");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				shipperSize = rs.getDouble("SHIPSIZE");				
				caseGrossWeight = rs.getDouble("CASE_GROSS_WEIGHT");				
				itemNetWeight = rs.getDouble("ITEM_NET_WEIGHT");				
				caseNetWeight = rs.getDouble("CASE_NET_WEIGHT");				
			}
			
			//shipperSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			itemVolume = (itmLen * itmWidth * itmHeight);
			caseVolume = (lotHeight * lotWidth * lotLen);
			
			dataVolumeMap.put("SHIPPER_SIZE", shipperSize);
			dataVolumeMap.put("ITEM_VOLUME", itemVolume);
			dataVolumeMap.put("CASE_VOLUME", caseVolume);
			dataVolumeMap.put("ITEM_GROSS_WEIGHT", itemGrossWeight);
			dataVolumeMap.put("CASE_GROSS_WEIGHT", caseGrossWeight);
			dataVolumeMap.put("ITEM_NET_WEIGHT", itemNetWeight);
			dataVolumeMap.put("CASE_NET_WEIGHT", caseNetWeight);
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
			
			
			
		} catch (Exception e) {
			// TODO: handle exception

			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	private String checkNullAndTrim( String inputVal )
	{
		if ( inputVal == null )
		{
			inputVal = "";
		}
		else
		{
			inputVal = inputVal.trim();
		}
		return inputVal;
	}
}
