/**
Title : post save component for purchase receipt PoRcpPos component
Date  : 25/11/11
Author: Gulzar Ahmad
*/

package ibase.webitm.ejb.wms;

import ibase.system.config.AppConnectParm;
import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.*;
import ibase.webitm.ejb.dis.DistCommon;
import ibase.webitm.ejb.dis.DistStkUpdLocal;
import ibase.webitm.ejb.dis.adv.PoRcpConf;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import ibase.webitm.ejb.ITMDBAccessEJB;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;

import org.w3c.dom.*;

import java.util.HashMap;

import javax.naming.InitialContext;
@javax.ejb.Stateless
public class PoRcpPos extends ValidatorEJB implements PoRcpPosLocal, PoRcpPosRemote
{
//	@Override
//	public String postSave()throws RemoteException,ITMException
//	{
//		return "";
//	}
//	@Override
	public String postSaveForm(String xmlString, String tranId, String objContext, String editFlag, String xtraParams) throws RemoteException,ITMException
	//public String postSave(String xmlString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		
		String forcedFlag = "false" ;
		String errString = "";
		String sql = "";
		String purcOrder = "";
		//String domId="",objContext="",postType="",itemSer="";
		String domId="",postType="",itemSer="";
		 NodeList parentNodeList=null;
         NodeList childList = null;
         Node parentNode=null;
         Node childNode = null;
         boolean alreadyScanned = false;
         String quantityStr="",lineNo="",unit="",unitRate="",unitStd="",quantityStduom="",lotNo="";
         String itemCode="",rateStr="",rateClg="";
         double quantity=0.0,rate=0.0,convQtyStduom=0.0,rateStduom=0.0,convRtuomStduom=0.0,nquantityStduom=0.0,partQty=0.0;
         DistCommon distCommon = null;
 		distCommon = new DistCommon();	
 		ArrayList convAr=null;
 		double amount=0.0,totAmt=0.0;
		int noArt =0;
		int totOrdDet = 0;
		int ordDetScaned = 0;
		String asnNo="";
		String sSQL = "";
			
		
		//Changed by Rohan on 26/02/13 for define variables.start
		double sumQty=0.0;
		double shipperSize = 0.0;
		//Changed by Rohan on 26/02/13 for define variables.end
		boolean toCommit = false, toConfirm = true;

		Document dom = null;
		String purcOrderDb = "",sql1 = "";

		PreparedStatement pstmt = null,pstmt1 = null,pstmtAsn = null,pstmtPorcp = null;
        ResultSet rs = null , rs1 = null,rsAsn = null,rsPorcp = null;
        
        
        
		PoRcpConf porcpConf = new PoRcpConf();
		ConnDriver connDriver = null;
		Connection conn=null;
		double grossWeight=0;
        double netWeight=0;
        double tareWeight=0;
        
        double rcpgrossWeight=0;
        double rcpnetWeight=0;
        double rcptareWeight=0;
        
        double asnQty = 0.0,porcpQty = 0.0;

		try
		{
			
			connDriver = new ConnDriver();
			conn = connDriver.getConnectDB("DriverValidator");
			if(conn==null)
			{
				System.out.println("Connection is null");
				return "";
			}
			
			conn.setAutoCommit(false);
			ITMDBAccessEJB itmDBAccessEJB =new ITMDBAccessEJB();
			System.out.println("tranId-----"+tranId);
			System.out.println("objContext-----"+objContext);
			System.out.println("editFlag-----"+editFlag);
			System.out.println("xtraParams-----"+xtraParams);
			System.out.println("@@@@@ xmlString"+xmlString);
			GenericUtility genericUtility = GenericUtility.getInstance();
			dom = genericUtility.parseString(xmlString);
			purcOrder = genericUtility.getColumnValueFromNode("purc_order", dom.getElementsByTagName("Detail2").item(0));
			System.out.println("Purchase order from header ["+purcOrder+"]");
			//adding validation BY SHAMIM
			String lineNoOrd="";
			double qtyDb=0.0;
			asnNo=genericUtility.getColumnValue("asn_no", dom);
			//Changed by Dhanraj Thakare 18-07-2014 for confirmed afetr manual
			//sql="select tran_id from porcp where asn_no=?";
			sql="select tran_id from porcp where asn_no=? and confirmed='N'";
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1,asnNo);
			rs=pstmt.executeQuery();
			if(rs.next())
			{
				tranId=rs.getString("tran_id");
			}
			rs.close();
			rs=null;
			pstmt.close();
			pstmt=null;
			Map<String, Double> qtyMap = new HashMap<String, Double>();
			sql = "select sum(quantity),line_no from porddet where purc_order= ? group by line_no";  //cp
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, purcOrder);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				qtyMap.put(rs.getString(2), rs.getDouble(1)) ;
			}
			rs.close();
			pstmt.close();
			//Iterating through Map
//			qtyMap.keySet().
			Iterator<String> it =  (qtyMap.keySet()).iterator();
			while (it.hasNext())
			{
				String key =it.next();
				//Changed by Rohan on 26/02/13 for bug fixing
				//double sumQty=0.0;
				sumQty=0.0;
				int length=dom.getElementsByTagName("Detail3").getLength();
				System.out.println("@@@@@ length:["+length+"]");
				String lineNoPOrd="";
				for(int i =0; i<length ; i++)
				{
					lineNoPOrd =genericUtility.getColumnValueFromNode("line_no__ord",dom.getElementsByTagName("Detail3").item(i));
					System.out.println("@@@@@ lineNoPOrd:["+lineNoPOrd+"]");
					if(key.equals(lineNoPOrd))
					{
						System.out.println("@@@@@quantity:"+(genericUtility.getColumnValueFromNode("quantity",dom.getElementsByTagName("Detail3").item(i))));
						/*Added By Ashish on 03/Aug/12 [START]*/
						String scannedQtyStr = genericUtility.getColumnValueFromNode("quantity",dom.getElementsByTagName("Detail3").item(i));
						double scannedQty = 0.0 ,scannedPartQty =0.0;
						int scannedNoArt=0;
						if(scannedQtyStr != null &&  scannedQtyStr.trim().length() >0 )
						{
							scannedQty = Double.parseDouble(scannedQtyStr);
						}
						String scannedNoArtStr = genericUtility.getColumnValueFromNode("no_art",dom.getElementsByTagName("Detail3").item(i));
						System.out.println("scannedNoArtStr----"+scannedNoArtStr);
						if ( scannedNoArtStr != null &&  scannedNoArtStr.trim().length() >0 )
						{
							
							scannedNoArt=Integer.parseInt(scannedNoArtStr);
						}
						String scannedPartQtyStr = genericUtility.getColumnValueFromNode("part_qty",dom.getElementsByTagName("Detail3").item(i));
						if ( scannedPartQtyStr != null &&  scannedPartQtyStr.trim().length() >0 )
						{
							scannedPartQty = Double.parseDouble(scannedPartQtyStr);
						}
						
						String scannedItemCode = genericUtility.getColumnValueFromNode("item_code",dom.getElementsByTagName("Detail3").item(i));
						String scannedLotNo = genericUtility.getColumnValueFromNode("lot_no",dom.getElementsByTagName("Detail3").item(i));
						System.out.println("@@@@ inside validation scannedQty :["+scannedQty+"] scannedNoArt :["+ scannedNoArt +"] scannedPartQty :["+scannedPartQty+"]  scannedItemCode:[ "+ scannedItemCode+"] scannedLotNo:["+ scannedLotNo +"]");
						double packSize =0.0;
						if(scannedQty == 0.0)
						{
							scannedQty = getQuantityOnPackSize( scannedItemCode , scannedLotNo ,scannedNoArt ,scannedPartQty ,conn);
						}
						
						//sumQty+=Double.parseDouble(genericUtility.getColumnValueFromNode("quantity",dom.getElementsByTagName("Detail3").item(i)));        // CHANGE BY CPATIL
						sumQty+=scannedQty;        
						/*Added By Ashish Sonawane on 03/Aug/12 [END]*/
					}
					
				}
				System.out.println("@@@@@@ sumQty: ["+sumQty+"]::::qtyMap.get(key):["+qtyMap.get(key)+"]");
				//if(sumQty !=qtyMap.get(key)) //commentd by Ashish on 03/Aug/12 for allowing quantity less than ASN quantity 
				
				//Changed by sumit on 27/12/12 commented as this is not required from Client start.
				/*if(sumQty > qtyMap.get(key))
				{
//					iTMDBAccessEJB.gt
					return errString = itmDBAccessEJB.getErrorString("","VTQMM",""); 
				}*/
				//Changed by sumit on 27/12/12 commented as this is not required from Client end.
				
			}
			/*added start*/
			
			System.out.println("@@@@@ quantity code called--- 1-07-12"+sumQty);
			
			 genericUtility = GenericUtility.getInstance();
             parentNodeList = dom.getElementsByTagName("Detail3");
             int childNodeListLength = parentNodeList.getLength();

			 for(int ctr = 0; ctr < childNodeListLength; ctr++)
             {
				 parentNode = parentNodeList.item(ctr);
                 //curTranIDIssue =genericUtility.getColumnValueFromNode("tran_id", parentNode);
                 //tranId= genericUtility.getColumnValueFromNode("tran_id", parentNode);
                 quantityStr = genericUtility.getColumnValueFromNode("quantity", parentNode);
                 lineNoOrd = genericUtility.getColumnValueFromNode("line_no__ord", parentNode);
                 lineNo =genericUtility.getColumnValueFromNode("line_no", parentNode);
                 unit =genericUtility.getColumnValueFromNode("unit", parentNode);
                 unitRate =genericUtility.getColumnValueFromNode("unit__rate", parentNode);
                 unitStd =genericUtility.getColumnValueFromNode("unit__std", parentNode);
                 quantityStduom =genericUtility.getColumnValueFromNode("quantity__stduom", parentNode);
                 rateStr=genericUtility.getColumnValueFromNode("rate", parentNode);
                 rateClg=genericUtility.getColumnValueFromNode("rate_clg", parentNode);
                 itemCode = genericUtility.getColumnValueFromNode("item_code", parentNode);
                 /*Added by Ashish Sonawane on 03/Aug/12 [START] */
                 lotNo =genericUtility.getColumnValueFromNode("lot_no", parentNode);
                 String partQtyStr=genericUtility.getColumnValueFromNode("part_qty", parentNode);
                 partQty =Double.parseDouble(partQtyStr == null ? "0" :partQtyStr);
                 String noArtStr =genericUtility.getColumnValueFromNode("no_art", parentNode);
                 noArt =Integer.parseInt(noArtStr == null ? "0" : noArtStr);
                 /*Added by Ashish Sonawane on 03/Aug/12 [END] */
                 
                 quantityStr = quantityStr == null ? "0" :quantityStr;
                 rateStr = rateStr == null ? "0" :rateStr;
                 
                 System.out.println("@@@@@ quantity:"+quantityStr);
                 System.out.println("@@@@@ rate:"+rateStr);
                 
                 quantity = Double.parseDouble(quantityStr);
                 /*Added by Ashish Sonawane on 03/Aug/12 [START] */
                 if(quantity == 0.0)
                 {
                	 quantity=getQuantityOnPackSize( itemCode , lotNo ,noArt ,partQty ,conn);
                 }
                 System.out.println("@@@@ quantity"+quantity);
                 /*Added by Ashish Sonawane on 03/Aug/12 [END] */
                 rate = Double.parseDouble(rateStr);
                 
                 if (unit.trim().equalsIgnoreCase(unitStd.trim()))
                 {
                	// nquantityStduom = Double.parseDouble(quantityStr); //commented by Ashish Sonawane on 03/Aug/12
                	 nquantityStduom = quantity; //Added by Ashish Sonawane on 03/Aug/12
                     System.out.println("@@@@@1 quantityStduom :"+nquantityStduom);
                     convQtyStduom=1.0;
                     convRtuomStduom=1.0;
                     rateStduom=rate;
                 }
                 else
                 {
                	 //quantityStduom = distCommon.getConvQuantityFact(unit, unitStd, itemCode, quantity, convFact, conn);
                	 convAr = distCommon.getConvQuantityFact(unit, unitStd, itemCode, quantity, rate, conn);
                	 convQtyStduom = Double.parseDouble( convAr.get(0).toString() );
                	 nquantityStduom = Double.parseDouble( convAr.get(1).toString() );
					 
					 convAr = distCommon.getConvQuantityFact(unitStd,unit,itemCode, quantity,rate, conn);
					 convRtuomStduom = Double.parseDouble( convAr.get(0).toString() );
					 rateStduom = Double.parseDouble( convAr.get(1).toString() );
				 }
                 amount+=rateStduom*nquantityStduom;
               //Added By Manoj dtd 31/08/2012 to update tareweight
                 //Changed by Rohan on 26-02-13 for get shipper size
                 //sql="select gross_weight, net_weight, length, width ,height from item_lot_packsize WHERE ITEM_CODE = ? AND LOT_NO__FROM <= ? AND LOT_NO__TO >= ? ";
                 sql="select gross_weight, net_weight, length, width ,height,shipper_size from item_lot_packsize WHERE ITEM_CODE = ? AND LOT_NO__FROM <= ? AND LOT_NO__TO >= ? ";
                 pstmt=conn.prepareStatement(sql);
                 pstmt.setString(1,itemCode);
                 if(lotNo != null && lotNo.trim().length() > 0)
	         		{
	         			System.out.println("lotNo is not null ");		
	         			pstmt.setString(2,lotNo);
	         			pstmt.setString(3,lotNo);
	         		}
	         		else
	         		{
	         			System.out.println("lotNo is null ");			
	         			pstmt.setString(2,"00");
	         			pstmt.setString(3,"ZZ");
	         		}
                 
                 	rs=pstmt.executeQuery();
	         		if(rs.next())
	         		{	         			
	         			grossWeight =rs.getDouble(1);
	         			netWeight=rs.getDouble(2);
	         			//Changed by Rohan on 26-02-13 for get shipper size
	         			shipperSize = rs.getDouble("shipper_size");
	         		}
	         		rs.close();
	         		rs=null;
	         		pstmt.close();
	         		pstmt=null;
	         		
	         		rcpgrossWeight=grossWeight*noArt;
	         		rcpnetWeight=netWeight*noArt;
	         		grossWeight=0;
	         		netWeight=0;
	         		//Changed by Rohan on 26-02-13 for 26-02-13 for considering gross weight,net weight of item master if quantity < shipper Size
	         		//Changed by Rohan on 27-02-13 for bug fixing
	         		//if(sumQty < shipperSize)
	         		if(quantity < shipperSize)
	         		{
	         			noArt = 1;
	         			
		         		sql="select gross_weight,net_weight from item where item_code=?";
		         		pstmt=conn.prepareStatement(sql);
		         		pstmt.setString(1,itemCode);
		         		rs=pstmt.executeQuery();
		         		if(rs.next())
		         		{
		         			grossWeight =rs.getDouble(1);
		         			netWeight=rs.getDouble(2);
		         		}
		         		rs.close();
		         		rs=null;
		         		pstmt.close();
		         		pstmt=null;
		         		
		         		//Changed by Rohan on 28-02-13 for bug fixing of calculation of weights.start
		         		rcpgrossWeight = grossWeight*quantity;
		         		rcpnetWeight = netWeight*quantity;
		         		//Changed by Rohan on 28-02-13 for bug fixing of calculation of weights.end
		         		
	         		}	
	         		//Changed by Rohan on 26/02/13 for bug fixing of calculation of weights .start
	         		/*
	         		rcpgrossWeight+=grossWeight*partQty;
	         		rcpnetWeight+=netWeight*partQty;
	         		*/
	         		//Changed by Rohan on 28-02-13 for bug fixing of calculation of weights.start
	         		/*
	         		
	         		rcpgrossWeight = grossWeight*quantity;
	         		rcpnetWeight = netWeight*quantity;
	         		*/
	         		//Changed by Rohan on 28-02-13 for bug fixing of calculation of weights.end
	         		//Changed by Rohan on 26/02/13 for bug fixing of calculation of weights.end
	         		rcptareWeight = rcpgrossWeight-rcpnetWeight;
	         		System.out.println("rcpgrossWeight:::::"+rcpgrossWeight+"rcpnetWeight::::"+rcpnetWeight);
	         		System.out.println("rcpgrossWeight"+rcpgrossWeight+"rcpnetWeight"+rcpnetWeight+"rcptareWeight"+rcptareWeight);
     			 
                 System.out.println("@@@@@ quantityStduom:["+nquantityStduom+"] ::::convQtyStduom:["+convQtyStduom+"] ::::rateStduom:["+rateStduom+"] :::::convRtuomStduom:["+convRtuomStduom+"]");
                 System.out.println("@@@@@ update querry executed next");
                 sql = "update porcpdet set quantity__stduom=?,rate__stduom=?,conv__qty_stduom=?,conv__rtuom_stduom=?, net_amt=? * ? ,quantity = ? ,no_art = ? , part_qty = ?,net_weight=?,gross_weight=?,tare_weight=?,realised_qty = ?  where tran_id= ? and line_no=?";  //cp//PORCPDET
                 pstmt = conn.prepareStatement(sql);
                 pstmt.setDouble(1,nquantityStduom);
                 pstmt.setDouble(2,rateStduom);
                 pstmt.setDouble(3,convQtyStduom);
                 pstmt.setDouble(4,convRtuomStduom);
                 pstmt.setDouble(5,rateStduom);
                 pstmt.setDouble(6,nquantityStduom);
                 /*Added By Ashish  Sonawane on 03/Aug/12 [START]*/
                 
                 pstmt.setDouble(7,quantity);
                 pstmt.setDouble(8,noArt);
                 pstmt.setDouble(9,partQty);
                
                 /*Added By Ashish  Sonawane on 03/Aug/12 [END]*/
                 //Added By Manoj dtd 31/08/2012 to update tareweight
                 pstmt.setDouble(10,rcpnetWeight);
                 pstmt.setDouble(11,rcpgrossWeight);
                 pstmt.setDouble(12,rcptareWeight);
                 pstmt.setDouble(13,quantity);//added by kunal on 05/09/12 update realised_qty 
                 pstmt.setString(14,tranId);
                 pstmt.setString(15,lineNo);
                 
                 
				int rowcnt= pstmt.executeUpdate();
				System.out.println("@@@@@ rowcnt:"+rowcnt);
				if(rowcnt==0)
				{
					
					throw new Exception("UpdateFailed");
				}
				 pstmt.close();
				 pstmt = null;		
             } // end for
		
			// added by cpatil on 19-07-12 start 
				
					sql = "select post_type from itemser where item_ser in (select  item_ser  from porder where purc_order in ( select purc_order from porcpdet  where tran_id=? ))";     // added by cpatil and line_no=? 
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1,tranId );
					rs = pstmt.executeQuery();
					if(rs.next())
					{										
					 postType = rs.getString("post_type") == null ? "" : rs.getString("post_type");
					}
					if (rs != null)
					{
					 rs.close();
					 rs = null;
					}
					if (pstmt != null)
					{
					 pstmt.close();
					 pstmt = null;
					}
					 	
					if( postType==null || postType.trim().length()== 0)
					{
						postType="A";
					}
				System.out.println("@@@@@---- post_type :["+postType+"]");
				// end by cpatil
			 
             sql = "update porcp set amount=?,tot_amt=?,post_type=? where tran_id= ?";  //cp//PORCPDET
             pstmt = conn.prepareStatement(sql);
             pstmt.setDouble(1,amount);
             pstmt.setDouble(2,amount);
             pstmt.setString(3,postType);
             pstmt.setString(4,tranId);
             
            int rowcnt= pstmt.executeUpdate();
			System.out.println("@@@@@ rowcnt:"+rowcnt);
			if(rowcnt==0)
			{
				throw new Exception("UpdateFailed");
			}
			 pstmt.close();
			 pstmt = null;		
			
			 /*finish*/
			
			//end of validation
			
 /* comment for test
			 //select no of art from pick_ord_det 
			sql = "SELECT COUNT(*) AS COUNT FROM PORDDET WHERE PURC_ORDER = ?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString( 1, purcOrder );
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				totOrdDet = rs.getInt("COUNT");
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;

			//select the no of order scanned 
			sql = "SELECT COUNT(*) AS COUNT FROM PORCPDET WHERE PURC_ORDER = ? ";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString( 1, purcOrder );
			rs = pstmt.executeQuery();
			if(rs.next())
			{
				ordDetScaned = rs.getInt("COUNT");
			}
			rs.close(); rs = null;
			pstmt.close(); pstmt = null;
			//if total purchase order detail is equals to total order scanned (receipt detail) then confirm method is called
			System.out.println("Total details of purchase order = ["+totOrdDet+"]");
			System.out.println("Total order scanned for receipt = ["+ordDetScaned+"]");
			if( totOrdDet == ordDetScaned )
			{
				System.out.println("Calling Confirm method..................");
				errString = porcpConf.confirm( tranId, xtraParams, forcedFlag, conn, toCommit );
			}
			
			
	*/		
			
			
			//GenericUtility genericUtility = GenericUtility.getInstance();
			dom = genericUtility.parseString(xmlString);
			purcOrder = genericUtility.getColumnValueFromNode("purc_order", dom.getElementsByTagName("Detail2").item(0));
			System.out.println("Purchase order from header ["+purcOrder+"]");
			//adding validation BY SHAMIM
			lineNoOrd="";
			qtyDb=0.0;
			//Changed By Pragyan 21-APR-14 comapre quantity from the ASN Detail.start
			/*
			Map<String, Double> qtyMap1 = new HashMap<String, Double>();
			sql = "select sum(quantity),line_no from porddet where purc_order= ? group by line_no";  
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, purcOrder);
			rs = pstmt.executeQuery();
			while (rs.next())
			{
				qtyMap1.put(rs.getString(2), rs.getDouble(1)) ;
			}
			rs.close();
			pstmt.close();
			Iterator<String> it1 =  (qtyMap.keySet()).iterator();
			while (it1.hasNext())
			{
				String key =it1.next();
				//Changed by Rohan on 26-02-13 for define variables
				//double sumQty=0.0;
				sumQty=0.0;
				
				int length=dom.getElementsByTagName("Detail3").getLength();
				System.out.println("@@@@@ length:["+length+"]");
				//quantity =genericUtility.getColumnValueFromNode("quantity",dom.getElementsByTagName("Detail2").item(i));
				String lineNoPOrd="";
				for(int i =0; i<length ; i++)
				{
					lineNoPOrd =genericUtility.getColumnValueFromNode("line_no__ord",dom.getElementsByTagName("Detail3").item(i));
					System.out.println("@@@@@ lineNoPOrd:["+lineNoPOrd+"]");
					if(key.equals(lineNoPOrd))
					{
						//sumQty+=Double.parseDouble(genericUtility.getColumnValueFromNode("quantity",dom.getElementsByTagName("Detail3").item(i)));        // CHANGE BY CPATIL
						Added By Ashish on 03/Aug/12 [START]
						String scannedQtyStr = genericUtility.getColumnValueFromNode("quantity",dom.getElementsByTagName("Detail3").item(i));
						double scannedQty = 0.0 ,scannedPartQty =0.0;
						int scannedNoArt=0;
						if(scannedQtyStr != null &&  scannedQtyStr.trim().length() >0 )
						{
							scannedQty = Double.parseDouble(scannedQtyStr);
						}
						String scannedNoArtStr = genericUtility.getColumnValueFromNode("no_art",dom.getElementsByTagName("Detail3").item(i));
						if ( scannedNoArtStr != null &&  scannedNoArtStr.trim().length() >0 )
						{
							scannedNoArt=Integer.parseInt(scannedNoArtStr);
						}
						String scannedPartQtyStr = genericUtility.getColumnValueFromNode("part_qty",dom.getElementsByTagName("Detail3").item(i));
						if ( scannedPartQtyStr != null &&  scannedPartQtyStr.trim().length() >0 )
						{
							scannedPartQty = Double.parseDouble(scannedPartQtyStr);
						}
						String scannedItemCode = genericUtility.getColumnValueFromNode("item_code",dom.getElementsByTagName("Detail3").item(i));
						String scannedLotNo = genericUtility.getColumnValueFromNode("lot_no",dom.getElementsByTagName("Detail3").item(i));
						System.out.println("@@@@ inside before confirm scannedQty :["+scannedQty+"] scannedNoArt :["+ scannedNoArt +"] scannedPartQty :["+scannedPartQty+"]  scannedItemCode:[ "+ scannedItemCode+"] scannedLotNo:["+ scannedLotNo +"]");
						double packSize =0.0;
						if(scannedQty == 0.0)
						{
							scannedQty = getQuantityOnPackSize( scannedItemCode , scannedLotNo ,scannedNoArt ,scannedPartQty ,conn);
						}
						sumQty+=scannedQty;
						Added By Ashish Sonawane on 03/Aug/12 [END]
					}
				}
				System.out.println("@@@@@@ sumQty: ["+sumQty+"]::::qtyMap.get(key):["+qtyMap.get(key)+"]");
				if (sumQty != qtyMap.get(key))
				{
					toConfirm = false;
					return errString = itmDBAccessEJB.getErrorString("","VTNTCNF",""); 
					
				}
			}
			*/
			
			toConfirm = true;
			//Changed By Pragyan 07-Aug-14 To get the total ASN quantity po wize 
			//Changed by Dhanraj querey change for get unconfirm item code 18/07/14 start.
			/*sSQL = "SELECT ITEM_CODE,LOT_NO,QUANTITY FROM ASN_DET WHERE TRAN_ID IN (SELECT" 
					+ " TRAN_ID FROM ASN_HDR WHERE ASN_NO = ? )";*/
			/*sSQL = " SELECT ASN_DET.ITEM_CODE,ASN_DET.LOT_NO,SUM(ASN_DET.QUANTITY-PORDDET.DLV_QTY) as QUANTITY " 
					+ " FROM ASN_DET,PORDDET,ASN_HDR,PORDER WHERE "
					+ " ASN_HDR.ASN_NO = ? "
					+ " AND ASN_HDR.ASN_NO = PORDER.ASN_NO "
					+ " AND PORDER.PURC_ORDER = PORDDET.PURC_ORDER "
					+ " AND "
					+ " ASN_DET.TRAN_ID = ASN_HDR.TRAN_ID "
					+ " AND ASN_DET.LINE_NO__ORD = PORDDET.LINE_NO "
					+ " AND (ASN_DET.QUANTITY-PORDDET.DLV_QTY) > 0 "
					+ " AND "
					+ " ASN_DET.ITEM_CODE = PORDDET.ITEM_CODE "
					+ " GROUP BY "
					+ " ASN_DET.ITEM_CODE,ASN_DET.LOT_NO ";*/
			
			sSQL =  " SELECT ITEM_CODE,LOT_NO,SUM(QUANTITY) AS QUANTITY  FROM ASN_DET WHERE TRAN_ID IN " 
					+ "(SELECT TRAN_ID FROM ASN_HDR WHERE PURC_ORDER =  ? AND CONFIRMED ='Y')" 
					+ " GROUP BY ITEM_CODE,LOT_NO ";
			//Changed by Dhanraj querey change for get unconfirm item code 18/07/14 end.
			pstmtAsn = conn.prepareStatement(sSQL);
			//Changed By Pragyan  07-Aug-14 To get the total PORCPDET QUANTITY 
			//pstmtAsn.setString(1, asnNo);
			pstmtAsn.setString(1, purcOrder);
			rsAsn = pstmtAsn.executeQuery();
			
			//Changed By Pragyan  07-Aug-14 To get the total PORCPDET QUANTITY 
			//sSQL = "SELECT SUM(QUANTITY) FROM PORCPDET WHERE TRAN_ID = ? AND ITEM_CODE = ? AND LOT_NO = ? ";
			sSQL = "SELECT SUM(QUANTITY) FROM PORCPDET WHERE TRAN_ID IN (SELECT TRAN_ID FROM PORCP " 
					+ " WHERE PURC_ORDER =  ? ) AND ITEM_CODE = ? AND LOT_NO = ? GROUP BY ITEM_CODE, LOT_NO";
				
			pstmtPorcp = conn.prepareStatement(sSQL);
			
			
			while(rsAsn.next())
			{
				
				asnQty = rsAsn.getDouble("QUANTITY");
				//Changed By Pragyan  07-Aug-14 To get the total PORCPDET QUANTITY 
				//pstmtPorcp.setString(1, tranId);
				pstmtPorcp.setString(1, purcOrder);
				pstmtPorcp.setString(2,rsAsn.getString("ITEM_CODE"));
				pstmtPorcp.setString(3,rsAsn.getString("LOT_NO"));
				
				
				rsPorcp = pstmtPorcp.executeQuery();
				porcpQty = 0.0;
				
				if(rsPorcp.next())
				{
					porcpQty = rsPorcp.getDouble(1);
				}
				
				if(rsPorcp != null)
				{
					rsPorcp.close();
					rsPorcp =null;
					
				}
				
				if(porcpQty != asnQty)
				{
					toConfirm = false;
					break;
					
				}
			}
			
			if(rsAsn != null)
			{
				rsAsn.close();
				rsAsn =null;
				
			}
			
			
			if(pstmtAsn != null)
			{
				pstmtAsn.close();
				pstmtAsn =null;
				
			}
			
			if(pstmtPorcp != null)
			{
				pstmtPorcp.close();
				pstmtPorcp =null;
				
			}
			
			//Changed By Pragyan 21-APR-14 comapre quantity from the ASN Detail.end
			
	        			
			if(toConfirm)
			{
				System.out.println("@@@@@ Calling Confirm method..................");
				errString = porcpConf.confirm( tranId, xtraParams, forcedFlag, conn, toCommit );
				System.out.println("@@@@@ Confirm method executed successfully ..."+errString );
				if(errString.trim().length() == 0)
				{
					int updCnt = 0;
					sql1 = "SELECT PURC_ORDER  FROM PORCP where TRAN_ID = ? "; 
					pstmt1 = conn.prepareStatement(sql1);
					pstmt1.setString(1, tranId);
					rs1 = pstmt1.executeQuery();
					if ( rs1.next() )
					{
						purcOrderDb = rs1.getString("PURC_ORDER");
					}		

					pstmt1.close();
					pstmt1 = null;					

					rs1.close();
					rs1 = null;

					sql1 = " UPDATE PORDER SET STATUS = 'C'  WHERE  PURC_ORDER = ? ";//added by Kunal on 06/09/12
					pstmt1 = conn.prepareStatement(sql1);
					pstmt1.setString(1, purcOrderDb);
					updCnt = pstmt1.executeUpdate();
					pstmt1.close();
					pstmt1 = null;
					System.out.println("updCnt ="+updCnt);
					
					
					sql1 = "UPDATE PORCP SET STATUS = 'C' WHERE TRAN_ID = ? ";//CHNAGE done by Kunal on 06/09/12
					pstmt1 = conn.prepareStatement(sql1);
					pstmt1.setString(1, tranId);
					updCnt = pstmt1.executeUpdate();
					pstmt1.close();
					pstmt1 = null;
				}
			}
			//Changed By Pragyan 09-MAY-14 To return previous message for quantity mismatch.start
			else
			{
				return errString = itmDBAccessEJB.getErrorString("","VTNTCNF","");
			}
			//Changed By Pragyan 09-MAY-14 To return previous message for quantity mismatch.end
		}
		catch(Exception e)
		{
			try {
				//Added by manoj dtd 21/08/2012
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			System.out.println("Exception "+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{
				//Added by manoj dtd 21/08/2012
				conn.commit();
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				
				if(rsAsn != null)
				{
					rsAsn.close();
					rsAsn =null;
					
				}
				
				
				if(pstmtAsn != null)
				{
					pstmtAsn.close();
					pstmtAsn =null;
					
				}
				
				if(pstmtPorcp != null)
				{
					pstmtPorcp.close();
					pstmtPorcp =null;
					
				}
				//Added by manoj dtd 21/08/2012
				conn.close();
				conn=null;
			}
			catch(Exception e)
			{
				System.err.println("Exception :PoRcpPos :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}
	//method to find value  from table conn ,no_pallet, asn_det, asn_no
	private String findValue(Connection conn, String columnName ,String tableName, String columnName2, String value) throws  ITMException, RemoteException
	{

		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(columnName);
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;	        
		}
		catch(Exception e)
		{
			System.out.println("Exception in findValue ");
			e.printStackTrace();
		    throw new ITMException(e); 
		}
		System.out.println("returning String from findValue ");
		return findValue;
	}
    
	private double getQuantityOnPackSize (String scannedItemCode , String scannedLotNo ,int scannedNoArt ,double scannedPartQty ,Connection conn) throws  ITMException, RemoteException
	{
		PreparedStatement pstmtPackSize=null;
        ResultSet rsPackSize = null;
        double scannedQty=0.0;
        double packSize=0.0;
        
		try {
			String sqlPackSize ="SELECT SHIPPER_SIZE FROM ITEM_LOT_PACKSIZE WHERE ITEM_CODE =?  AND LOT_NO__FROM <= ? AND LOT_NO__TO >= ?";
			pstmtPackSize =conn.prepareStatement(sqlPackSize);
			pstmtPackSize.setString(1, scannedItemCode);
			pstmtPackSize.setString(2, scannedLotNo);
			pstmtPackSize.setString(3, scannedLotNo);
			rsPackSize = pstmtPackSize.executeQuery();
			
			if(rsPackSize.next())
			{
				packSize=Double.parseDouble((rsPackSize.getString("SHIPPER_SIZE")==null?"0":rsPackSize.getString("SHIPPER_SIZE")));
			}
			
			scannedQty= (packSize * scannedNoArt) + scannedPartQty;
			pstmtPackSize.close();
			pstmtPackSize=null;
			rsPackSize.close();
			rsPackSize=null;
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new ITMException(e); 
		}
		
		return scannedQty;
	}
	
	

}

