
package ibase.webitm.ejb.wms;
import ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.ejb.ValidatorEJB;
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 java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
@javax.ejb.Stateless
public class MultiplePtcnWizPos extends ValidatorEJB implements MultiplePtcnWizPosLocal, MultiplePtcnWizPosRemote
{
	GenericUtility genericUtility = GenericUtility.getInstance();
	boolean isError = false;
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	
	
	public String postSave( String domString, String tranId,String editFlag, String xtraParams) throws RemoteException,ITMException
	{
		return "";
	}
	
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws ITMException
	{
		isError = false;
		System.out.println("**** MultiplePtcnWizPos ***** postSave ");			
		System.out.println("**** MultiplePtcnWizPos ***** tranId ["+tranId+"]");
		System.out.println("**** MultiplePtcnWizPos ***** editFlag ["+editFlag+"]");
		System.out.println("**** MultiplePtcnWizPos ***** xtraParams ["+xtraParams+"]");
		System.out.println("**** MultiplePtcnWizPos ***** domString ["+domString+"]");
		Document dom = null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String errorString = "",sql = "",ptcn= "",saleOrder= "",childNodeName = "",mark = "";	
		int count = 0,nodeLength = 0,cnt = 0,childNodeListLength= 0;	
		Node parentNode = null;
		Node childNode = null;
		NodeList detail3List = null,detail2List = null;
		NodeList childNodeList = null;
		MultiplePtcnWizConf MultiplePtcnWizConfobj = null;
		NodeList parentNodeList = null;
		ArrayList<String> sorderList = new ArrayList <String>();
		HashMap<String,Integer> sorderItemCountMap = new HashMap<String,Integer> ();
		StringBuffer sqlBuf = new StringBuffer();
		int pstmtCnt = 0;
		try
		{		
			conn.setAutoCommit(false);
			dom = genericUtility.parseString(domString);
			if(dom != null)
			{							
				parentNodeList = dom.getElementsByTagName("Detail2");
				System.out.println("parentNode >>>{"+parentNode+"}");
				int nodeCnt = parentNodeList.getLength();
				System.out.println("nodeCnt >>>{"+nodeCnt+"}");
				if(nodeCnt > 0)
				{				
					childNodeListLength = parentNodeList.getLength();					
					System.out.println("childNodeListLength----->>"+childNodeListLength);
					for (int ctr = 0; ctr < childNodeListLength; ctr++)
					{
						childNode = parentNodeList.item(ctr);			
						childNodeName = childNode.getNodeName();
						System.out.println("Child name --->> "+childNodeName);
						if (childNode.getNodeType() == Node.ELEMENT_NODE)
						{
							Element eElement = (Element) childNode;					
							ptcn =  eElement.getElementsByTagName("ptcn").item(0).getTextContent();
							saleOrder =  eElement.getElementsByTagName("sale_order").item(0).getTextContent();
							saleOrder = saleOrder == null ? "" : saleOrder.trim();
							System.out.println("ptcn --->> "+ptcn);	
							System.out.println("saleOrder --->> "+saleOrder);	
							sorderList.add(ptcn);

						}
					}
				}
			}
			System.out.println("saleOrder List-------->>"+sorderList);
			if(sorderList.size() > 0)
			{			
				/*SELECT * FROM PICK_ORD_DET WHERE PICK_ORDER IN 
				( SELECT REF_ID FROM WAVE_TASK_DET WHERE PTCN = 'PTCN000076' AND STATUS = 'N' 
				and wave_status = 'C' AND ref_ser in ('A-PICK','C-PICK','P-PICK','M-PICK')) 
				AND (QUANTITY  - (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END)) > 0*/
				sqlBuf.append( "SELECT COUNT(*) FROM PICK_ORD_DET WHERE PICK_ORDER IN (");
						
				sqlBuf.append(" SELECT REF_ID FROM WAVE_TASK_DET WHERE PTCN in ( ");
				
				for(int i = 0; i < sorderList.size(); i++)
				{
					mark = mark + "?,";
				}
				mark = mark.substring(0,mark.length() - 1);
				sqlBuf.append(mark);		
									
				sqlBuf.append(") AND STATUS = 'N' AND WAVE_STATUS = 'C' AND REF_SER IN ('A-PICK','C-PICK','P-PICK','M-PICK')) ");						
				sqlBuf.append("AND (QUANTITY  - (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END)) > 0");
				System.out.println("sqlBuf : "+sqlBuf.toString());				
				pstmt = conn.prepareStatement(sqlBuf.toString());
				for (String list : sorderList)
				{						
					pstmt.setString(++pstmtCnt,list);	
				}			

				rs = pstmt.executeQuery();
				if(rs.next())
				{
					count = rs.getInt(1);
				}					
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;

			}
			System.out.println("PICK_ORD_DET count--------->>"+count);	
			
			
			sql = "SELECT COUNT(*) FROM MULTI_PICK_ISS_DET WHERE TRAN_ID = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				cnt = rs.getInt(1);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;
			System.out.println("multi_pick_iss_det count---->>["+cnt+"]");
			//nodeLength = detail3List.getLength();				
			if( count != 0 && cnt == count)
			{
				MultiplePtcnWizConfobj = new MultiplePtcnWizConf();					
				errorString = MultiplePtcnWizConfobj.confirm( tranId, xtraParams,"", conn, false ) ;
				//errorString = confirmedMultiplePickIssue(tranId, xtraParams, conn);				

			}
			System.out.println("errorString postSave------->>["+errorString+"]");
			if(errorString != null && errorString.length() > 0)
			{
				throw new Exception(errorString);
			}
		}
		catch(Exception e)
		{			
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			System.out.println("in finally........."+isError);
			
			try
			{
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}				
				
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		return "";
	}
	
	/*private boolean isPtcnConfirmed(ArrayList<String> ptcnList,Connection conn) throws Exception
	{
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		String sql = "";
		try
		{
			if(ptcnList !=null && ptcnList.size() > 0)
			{
				
			}
			
		}
		catch(Exception e)
		{
			e.printStackTrace();
			throw new Exception(e);
		}
	}*/
	private String confirmedMultiplePickIssue(String tranId,String xtraParams,Connection conn) throws Exception
	{
		System.out.println("@@@@@@@@ Call confirmedMultiplePickIssue Method  @@@@@@@@");
		ResultSet rs = null,rsStock = null,rs1 = null,rsSord = null;
		PreparedStatement pstmt = null,pstmtStock = null,pstmt1 = null,pstmtUpd = null,pstmtSord = null;
		PreparedStatement pstmtUpdPickOrdDet =null;
		String sql = "",errorString = "",empCode = "",confirmed = "";
		String itemCode = "",siteCode = "",locCode = "",lotNo = "",lotSl = "",locCodeTo = "",saleOrder = "",
		lineNoSord = "",replOrder = "",pickType = "",dExpLev = "",remarks = "",dimension = "";
		String acctCodeDr = "",cctrCodeDr = "",packCode = "",siteCodeMfg = "",stagingLoc = "",
		packInstr = "",suppCodeMfg = "",unitAlt = "",batchNo = "",unit = "",grade = "",		
		expLev = "",itemCodeOrd = "",itemRef = "",status = "",itemGrade = "",
		allocMode = "",reasCode = "",unitStd = "",sSQL = "",updateSql = "";
		int lineNoOrd= 0,lineNo= 0,updCnt = 0,rowsCount = 0;  
		double quantity = 0,noArt =0,stkGrossRate = 0,convQtyStduom = 0,batchSize = 0,holdQuantity = 0,stkRate = 0;
		double shipperSize = 0,	itemGrossWeight = 0, caseGrossWeight = 0, itemNetWeight = 0, caseNetWeight = 0,
				qtyOrd = 0,sordAllocQty = 0;
		isError = false;
		boolean isUpdatehdr= false;
		java.sql.Timestamp expDate = null;
		java.sql.Timestamp mfgDate = null;
		java.sql.Timestamp restestDate = null;
		java.sql.Timestamp dateAlloc = null;
		HashMap updateRowMap = new HashMap();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();
		DistCommon distCommon = new DistCommon();
		StockUpdate stkUpdate = null;
		InvAllocTraceBean invAllocTrace = null;
		HashMap itmVolumeMap = new HashMap();
		HashMap strAllocate = null;
		try
		{
			java.sql.Timestamp currDate = new java.sql.Timestamp( System.currentTimeMillis() );
			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") ;
			
			confirmed = checkNull(getColumnDescr(conn, "confirmed", "multi_pick_iss", "tran_id", tranId));
			System.out.println("confirmed----->>["+confirmed+"]");
			confirmed = confirmed.length() == 0 ? "N" :confirmed;
			if("Y".equalsIgnoreCase(confirmed))
			{
				errorString = itmDBAccess.getErrorString("","PCKISSCONF","");
				return errorString;
			}
			else
			{
				stagingLoc = checkNull(distCommon.getDisparams( "999999", "STAGING_LOC", conn)==null?"":distCommon.getDisparams( "999999", "STAGING_LOC", conn));

				System.out.println("stagingLoc------>>["+stagingLoc+"]");
				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 ,hold_qty from stock "
						+ "where item_code = ? "
						+ "and site_code = ? "
						+ "and loc_code = ? "
						+ "and lot_no = ? "
						+ "and lot_sl = ? ";
				
				pstmtStock = conn.prepareStatement(sql);

				sql = "SELECT R.TRAN_ID,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 - (CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END )) AS QUANTITY,"
						+ " R.NO_ART NO_ART,O.SALE_ORDER SALE_ORDER, O.LINE_NO__SORD LINE_NO__SORD, O.PICK_ORDER,H.PICK_TYPE,"
						+ "O.EXP_LEV EXP_LEV "
						+ " FROM MULTI_PICK_ISS_DET R,  PICK_ORD_DET O ,PICK_ORD_HDR H  WHERE R.TRAN_ID = ? "
						+ " AND R.PICK_ORDER = O.PICK_ORDER AND H.PICK_ORDER = O.PICK_ORDER "
						+ " AND O.LINE_NO = R.LINE_NO__SORD "
						+ " AND (R.QUANTITY - (CASE WHEN R.DEALLOC_QTY IS NULL THEN 0 ELSE R.DEALLOC_QTY END )) > 0 ";
				//+ " AND R.LOC_CODE <> R.LOC_CODE__TO";

				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, tranId);
				rs = pstmt.executeQuery();

				while(rs.next())
				{
					System.out.println("WHILE CONDITION.........."+(++rowsCount));
					itemCode = rs.getString( "item_code" );
					siteCode = rs.getString( "site_code" );
					locCode = rs.getString( "loc_code" );
					lotNo = checkNull(rs.getString( "lot_no" ));
					lotSl = checkNull(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__SORD" ); 
					dExpLev = checkNull(rs.getString( "EXP_LEV" ));
					System.out.println("dExpLev->> ["+dExpLev+"]");
					System.out.println("locCode->> ["+locCode+"]");

					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");

					if(lotNo == null || lotNo.trim().length() == 0)
					{
						lotNo ="               ";
					}
					if(lotSl == null || lotSl.trim().length()==0)
					{
						lotSl = "     ";
					}

					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");					
						holdQuantity = rsStock.getDouble("hold_qty");				
					}
					rsStock.close(); rsStock = null;
					pstmtStock.clearParameters();


					if(holdQuantity > 0)
					{					

						errorString = getError(siteCode, lotNo, lotSl, itemCode, locCode, "INVITMQTST", conn);
						return errorString;

					}

					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("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);			
					
					System.out.println("quantity---->>["+quantity+" shipperSize--->>["+shipperSize+"]");
					if(quantity < shipperSize || shipperSize == 0)
					{
						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);
					System.out.println("errorString BEFORE updateStock1----->["+errorString+"]");
					errorString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					System.out.println("errorString after updateStock1----->["+errorString+"]");

					if ( errorString != null && errorString.trim().length() > 0 )
					{
						isError = true;
						break;
					}

					updateRowMap.put("loc_code", stagingLoc);
					updateRowMap.put("tran_type", "R");//Creating receipt
					System.out.println("errorString BEFORE updateStock2..........");
					errorString = stkUpdate.updateStock( updateRowMap, xtraParams, conn );
					System.out.println("errorString after updateStock2----->["+errorString+"]");
					if ( errorString != null && errorString.trim().length() > 0 )
					{
						isError = true;
						break;
					}

					updateRowMap.clear();

					strAllocate = new HashMap();
					strAllocate.put("tran_date",tranDate);
					strAllocate.put("ref_ser","M-PTCN");
					strAllocate.put("ref_id",tranId);
					strAllocate.put("ref_line", "" + lineNo);
					strAllocate.put("site_code",siteCode);
					strAllocate.put("item_code",itemCode);

					strAllocate.put("loc_code", stagingLoc);

					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_multi_pick_iss");
					errorString = invAllocTrace.updateInvallocTrace(strAllocate, conn);
					System.out.println("errorString after updateInvallocTrace----->["+errorString+"]");
					
					// To check the sordalloc allocated quantity > quantity  then insert new record deducte quantity from old location.
					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
							{
								isError = true;
								errorString = itmDBAccess.getErrorString("","RECNOTSORA","");
								return errorString;
							}
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;
							
							sSQL = "SELECT COUNT(*) AS COUNT FROM SORDALLOC WHERE " 
									+ " SALE_ORDER = ? AND LINE_NO = ? "
									+ " AND ITEM_CODE = ? "
									+ " AND LOC_CODE = ? "
									+ " AND LOT_NO = ? "
									+ " AND LOT_SL = ? ";
							int count = 0;
							System.out.println("sordAllocQty---->>["+sordAllocQty+" quantity--->["+quantity+"]");						
							
							if(sordAllocQty > quantity)
							{
								System.out.println("sordAllocQty in greater.................");	
								
							      updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? ,QUANTITY__STDUOM = QUANTITY__STDUOM - ?, WAVE_FLAG = 'Y' " 
									+ " 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); 
								//Changed by sumit on 10/09/13 setting 
								pstmtUpd.setDouble(2, quantity * convQtyStduom);
								pstmtUpd.setString(3, saleOrder);
								pstmtUpd.setString(4, lineNoSord);				
								pstmtUpd.setString(5, itemCode);				
								pstmtUpd.setString(6, locCode);				
								pstmtUpd.setString(7, lotNo);				
								pstmtUpd.setString(8, lotSl);				
			
								updCnt = pstmtUpd.executeUpdate();
								
								if(pstmtUpd !=null)
								{
									pstmtUpd.close(); 
									pstmtUpd=null;
								}
								
								System.out.println(" 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+"]");								
								
								pstmtSord = conn.prepareStatement(sSQL);
								pstmtSord.setString(1, saleOrder);
								pstmtSord.setString(2, lineNoSord);				
								pstmtSord.setString(3, itemCode);			
								pstmtSord.setString(4, stagingLoc);
												
								pstmtSord.setString(5, lotNo);				
								pstmtSord.setString(6, lotSl);				
			
								rsSord = pstmtSord.executeQuery();
								
								if(rsSord.next())
								{
									count = rsSord.getInt(1);
								}
								
								System.out.println("count SORDALLOC@@------>>["+count+"]");							
								
								if(count > 0)
								{

									System.out.println(" sordalloc If alrady exist Location Update Inside grater  updCnt1st [" + updCnt + "]");

									//updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?"
									updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?, WAVE_FLAG = 'Y' "
											+ " 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.setDouble(2, quantity);
									pstmtUpd.setString(3, saleOrder);
									pstmtUpd.setString(4, lineNoSord);				
									pstmtUpd.setString(5, itemCode);	
									pstmtUpd.setString(6, stagingLoc);
									pstmtUpd.setString(7, lotNo);				
									pstmtUpd.setString(8, lotSl);				

									updCnt = pstmtUpd.executeUpdate();

									if(pstmtUpd !=null)
									{
										pstmtUpd.close(); 
										pstmtUpd=null;
									}

									System.out.println(" sordalloc If alrady exist Location Update  Inside grater  updCnt2nd [" + updCnt + "]");
								}								
								else
								{
									System.out.println("--------insert record in SORDALLOC table--------------");									

									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, WAVE_FLAG ) "
											+ " 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, stagingLoc);										
									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);									
									pstmtUpd.setString(25, "Y");
									updCnt = pstmtUpd.executeUpdate();
									System.out.println(" INSERT in sordalloc  updCnt [" + updCnt + "]");
									
									if(pstmtUpd != null)
									{
										pstmtUpd.close(); 
										pstmtUpd=null;
									}
									
								}
						
							}
							else
							{	
								System.out.println("sordAllocQty in lesser.................");	
								pstmtSord = conn.prepareStatement(sSQL);
								pstmtSord.setString(1, saleOrder);
								pstmtSord.setString(2, lineNoSord);				
								pstmtSord.setString(3, itemCode);		
								pstmtSord.setString(4, stagingLoc);
												
								pstmtSord.setString(5, lotNo);				
								pstmtSord.setString(6, lotSl);				
			
								rsSord = pstmtSord.executeQuery();
								
								if(rsSord.next())
								{
									count = rsSord.getInt(1);
								}
								System.out.println("count SORDALLOC--->>"+count);								
								
								if(count > 0)
								{									
									//updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ? " //LOC_CODE = ? ,LOT_SL = ? "
									  updateSql = "UPDATE SORDALLOC SET   QTY_ALLOC = QTY_ALLOC - ?, WAVE_FLAG = 'Y' "
										+ " 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(" sordalloc If alrady exist Location Update  updCnt1st [" + updCnt + "]");
										
										if(pstmtUpd !=null)
										{
											pstmtUpd.close(); 
											pstmtUpd=null;
										}										
										//updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?"
										updateSql = "UPDATE SORDALLOC SET QTY_ALLOC = QTY_ALLOC + ? ,QUANTITY__STDUOM = QUANTITY__STDUOM + ?, WAVE_FLAG = 'Y' "
										+ " 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.setDouble(2, quantity);
										pstmtUpd.setString(3, saleOrder);
										pstmtUpd.setString(4, lineNoSord);				
										pstmtUpd.setString(5, itemCode);	
										pstmtUpd.setString(6, stagingLoc);														
										pstmtUpd.setString(7, lotNo);				
										pstmtUpd.setString(8, lotSl);				
					
										updCnt = pstmtUpd.executeUpdate();
										System.out.println(" sordalloc If alrady exist Location Update  updCnt2nd [" + updCnt + "]");
										
										if(pstmtUpd != null)
										{
											pstmtUpd.close();
											pstmtUpd=null;
										}
										
								}
								else
								{									
									//updateSql = "UPDATE SORDALLOC SET LOC_CODE = ? "
									updateSql = "UPDATE SORDALLOC SET LOC_CODE = ?, WAVE_FLAG = 'Y' "
											+ " 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.setString(1, stagingLoc);
									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();

									if(pstmtUpd !=null)
									{
										pstmtUpd.close(); 
										pstmtUpd=null;
									}

								}						
								
								System.out.println(" sordalloc Update  updCnt [" + updCnt + "]");
						
							}
							if(rsSord != null)
							{
								rsSord.close(); 
								rsSord=null;
							}
							if(pstmtSord != null)
							{
								pstmtSord.close();
								pstmtSord=null;
							}
							
							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);
											

							int updCntPickOrdDet = pstmtUpdPickOrdDet.executeUpdate();
							
							if(updCntPickOrdDet > 0)
							{
								isUpdatehdr = updatePickOrderHeader(replOrder,conn);
							}
							
							if(pstmtUpdPickOrdDet != null)
							{
								pstmtUpdPickOrdDet.close(); 
								pstmtUpdPickOrdDet=null;
							}
							
							updatePickOrdSql = "UPDATE MULTI_PICK_ISS_DET SET LOC_CODE = ? "
									+ " WHERE TRAN_ID = ? AND LINE_NO = ? ";
							
							pstmtUpdPickOrdDet = conn.prepareStatement(updatePickOrdSql);								
							pstmtUpdPickOrdDet.setString(1, stagingLoc);
							pstmtUpdPickOrdDet.setString(2, tranId);
							pstmtUpdPickOrdDet.setInt(3, lineNo);										
			
							updCntPickOrdDet = pstmtUpdPickOrdDet.executeUpdate();
							
							if(updCntPickOrdDet > 0)
							{
								isUpdatehdr = updatePickOrderHeader(replOrder,conn);
							}
							
							if(pstmtUpdPickOrdDet != null)
							{
								pstmtUpdPickOrdDet.close(); 
								pstmtUpdPickOrdDet=null;
							}
					
				} //end while
				
				if(rs != null)
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null)
				{
					pstmt.close();
					pstmt = null;//While close.
				}
				if(pstmtStock != null)
				{
					pstmtStock.close();
					pstmtStock = null;
				}
				
				if (errorString != null && errorString.trim().length() > 0 )
				{
					isError = true;
					return errorString;
				}
				
				updateSql = "UPDATE MULTI_PICK_ISS SET CONFIRMED = 'Y', CONF_DATE = ?, EMP_CODE__USER = ?"
						+ " 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 + " MULTI_PICK_ISS rows updated successfully" );
				}	
				pstmt.close();
				pstmt = null;
				
				System.out.println("isUpdatehdr-------->>["+isUpdatehdr+"]");			
				
				if(isUpdatehdr)
				{
					
				}

			}
			
			
		}
		catch(Exception e)
		{
			isError = true;
			e.printStackTrace();
			throw new Exception (e);			
		}
		
		return errorString;
	}
	private String getColumnDescr(Connection conn, String columnName ,String tableName, String columnName2, String value) throws Exception 
	{

			PreparedStatement pstmt = null ;
			ResultSet rs = null ; 
			String sql = "";
			String findValue = "";
			try
			{			
				sql = "SELECT " + columnName + " from " + tableName + " where " + columnName2 +"= ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1,value);
				rs = pstmt.executeQuery();
				if(rs.next())
				{					
					findValue = rs.getString(1);
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;	        
			}
			catch(Exception e)
			{
				System.out.println("Exception in getColumnDescr ");
				isError = true;
				e.printStackTrace();
				throw new Exception(e);
				
			}
			System.out.println("returning String from getColumnDescr " + findValue);
			return findValue;
		 
	}
	private  String getError(String siteCode,String lotNo,String lotSl,String itemCode,String locCode,String Code,Connection conn)  throws ITMException, Exception
    {
        String mainStr ="";
        
        try
        {
        	String errString = "";
        	errString =  new ITMDBAccessEJB().getErrorString("",Code,"","",conn);
			String begPart = errString.substring(0,errString.indexOf("<message>")+9);
			String endDesc = errString.substring(errString.indexOf("</description>"));
			mainStr= begPart+"Inventory is on hold"+"</message><description>";
			mainStr= mainStr+"Hold Quantity present in Site [" + siteCode + "] Lot No ["+lotNo+"] and Lot Sl ["+lotSl+"] Item Code["+itemCode+"] Location ="+locCode+" "+endDesc;
			System.out.println("mainStr:::::::::::::::::: "+mainStr);
			begPart = null;
        }
        catch(Exception e)
        {
            e.printStackTrace();
            throw new ITMException(e);
        }
        return mainStr;
    }
	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;
		double pickQty = 0;
		
		try {
			
			String sqlPickOrd = "SELECT STATUS,(QUANTITY - (CASE WHEN DEALLOC_QTY IS NULL THEN 0 ELSE DEALLOC_QTY END )) AS QUANTITY 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").trim();
				pickQty = rsPicKordHdr.getDouble("QUANTITY");
				
				System.out.println("@@@@ Inside Check Status["+status+"]pickQty["+pickQty+"]");
				if( !("C".equalsIgnoreCase(status) ) && pickQty > 0)
				{
					isUpdateHdr =false;
					break;
				}
			}
			
			if(pstmtPickOrdHdr != null)
			{
				pstmtPickOrdHdr.close();
				pstmtPickOrdHdr=null;
			}
			
			if(isUpdateHdr)
			{
				
				System.out.println("@@@@ Updating hader status as C ");
				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(pstmtUpdPickOrdHdr !=null)
				{
					pstmtUpdPickOrdHdr.close();
					pstmtUpdPickOrdHdr=null;
				}
				
				
				if(updCntPickOrdHdr > 0)
				{
					System.out.println("@@@@ Update Successfully");
					return true;
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			isError = true;
			throw new ITMException(e);
		}
	
		return false;
	}
	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
			isError = true;
			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	private String checkNull(String str)
	{
		if(str == null)
		{
			str = "";
		}
		return str.trim();
	}
}
