package ibase.webitm.ejb.wms;

import ibase.webitm.ejb.*;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.SimpleDateFormat;

import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;

@javax.ejb.Stateless
public class PhyItemVerifyPos extends ValidatorEJB implements PhyItemVerifyPosLocal, PhyItemVerifyPosRemote
{
	public String postSave()throws RemoteException,ITMException
	{
		return "";
	}
	public String postSave(String domString, String tranId,String editFlag, String xtraParams, Connection conn ) throws RemoteException,ITMException
	{
		String errString = "";
		String  sSQL="",itemCode = "", locCode = "";
		String siteCode = "";
		String lotNo = "";
		String lotSl = "";
		NodeList parentNodeList = null;
		Node parentNode = null;
		int parentNodeListLength = 0;
		int ctr = 0;
		Document dom = null;
		PreparedStatement pstmt = null, pstmt1 = null;		
        ResultSet rs = null,rs1=null;		
        java.util.Date utilDate = new java.util.Date();
		int lineNo = 0;
		String invStat = "",sql = "";
		Double quantityInput = 0.0;
		String refId="";
		int refLineNo=0;
		int rec=0;
        try
		{
			conn.setAutoCommit(false);
			GenericUtility genericUtility = GenericUtility.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(genericUtility.getDBDateFormat());
			Timestamp date1 = Timestamp.valueOf(sdf.format(utilDate).toString() + " 00:00:00.0");
			System.out.println("STRING{"+domString+"}");
			dom = genericUtility.parseString(domString); 
			siteCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginSiteCode" );
			parentNodeList = dom.getElementsByTagName("Detail2");
			parentNodeListLength = parentNodeList.getLength();
			
			for(ctr = 0; ctr < parentNodeListLength ; ctr++ )
			{
				lineNo=Integer.parseInt((genericUtility.getColumnValueFromNode("line_no",dom.getElementsByTagName("Detail2").item(ctr))).trim());
				quantityInput = Double.parseDouble(genericUtility.getColumnValue("phy_input", dom) == null ?"0":genericUtility.getColumnValue("phy_input", dom)); //change done by kunal on 10/09/12 
				System.out.println("lineNo----"+lineNo);
				
				parentNode = parentNodeList.item(ctr);
				itemCode = genericUtility.getColumnValueFromNode("item_code", parentNode);
				locCode = genericUtility.getColumnValueFromNode("loc_code", parentNode);
				lotNo = genericUtility.getColumnValueFromNode("lot_no", parentNode);
				lotSl = genericUtility.getColumnValueFromNode("lot_sl", parentNode);
				refId=genericUtility.getColumnValueFromNode("ref_id", parentNode);
				refLineNo=Integer.parseInt(genericUtility.getColumnValueFromNode("ref_line_no", parentNode));
				System.out.println("ITEM CODE["+itemCode+"] SITE CODE["+siteCode+"] LOC CODE["+locCode+"] LOT_NO["+lotNo+"] LOT SL["+lotSl+"]");
				
				sql ="select inv_stat from location  where loc_code = ? ";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, locCode);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
					invStat = rs.getString("inv_stat");
				}
				rs.close();
				rs = null;
				pstmt.close();
				System.out.println("invStat"+invStat+"   quantityInput="+quantityInput);

				
				sSQL = "UPDATE STOCK SET LAST_PHYC_DATE = ? WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
				pstmt1 = conn.prepareStatement(sSQL);
				pstmt1.setTimestamp(1, date1);
				pstmt1.setString(2, itemCode);
				pstmt1.setString(3, siteCode);
				pstmt1.setString(4, locCode);
				pstmt1.setString(5, lotNo);
				pstmt1.setString(6, lotSl);				
				pstmt1.executeUpdate();	
				pstmt1.close();
				pstmt1 = null; 
				
				sSQL = "UPDATE PHYSCANDET SET STATUS=?,VERIFIED = ? WHERE TRAN_ID = ? AND LINE_NO = ? ";
				pstmt1 = conn.prepareStatement(sSQL);
				pstmt1.setString(1, "Y");
				pstmt1.setString(2, "Y");
				pstmt1.setString(3, refId);
				pstmt1.setInt(4, refLineNo);
								
				pstmt1.executeUpdate();	
				pstmt1.close();
				pstmt1 = null; 
				PhyScanQtyUpdate qtyUpdate = new PhyScanQtyUpdate();
				qtyUpdate.quantityUpdate( tranId, lineNo,  locCode, itemCode, lotNo, lotSl,  quantityInput , invStat, conn);
				rec=0;
				sql="select count(*) from physcandet where tran_id=? and line_no=? and QUANTITY<>QTY_PHYSICAL";
				pstmt1=conn.prepareStatement(sql);
				pstmt1.setString(1,tranId);
				pstmt1.setInt(2,lineNo);
				rs1=pstmt1.executeQuery();
				if(rs1.next())
				{
					rec=rs1.getInt(1);
				}
				rs1.close();
				rs1=null;
				pstmt1.close();
				pstmt1=null;
				if(rec==0)
				{
					sql="update physcandet set verified='Y',reverify='N' where tran_id=? and line_no=? ";
					pstmt1=conn.prepareStatement(sql);
					pstmt1.setString(1,tranId);
					pstmt1.setInt(2,lineNo);
					pstmt1.executeUpdate();
					pstmt1.close();
					pstmt1=null;
				}
			}
		}
		catch(Exception e)
		{
			try
			{
				System.out.println("Exception "+e.getMessage());
				e.printStackTrace();			
				throw new ITMException(e);
			}
			catch (Exception e1)
			{
				e1.printStackTrace();
			}
		}
		finally
		{
			try
			{				
				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}
				
			}
			catch(Exception e)
			{
				e.printStackTrace();
				throw new ITMException(e);
			}
		}
		return errString;
	}	
	
}



