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,pstmt2=null;		
        ResultSet rs = null,rs1=null,rs2=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;
		String tranType="";
		String empCode = "",loginCode= "";
		int upd = 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();
			
			// added BY RITESH ON  01/mar/14 start
			loginCode = genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" );
			System.out.println("********* login code ["+loginCode+"]");
			
			sql = "SELECT EMP_CODE FROM USERS WHERE CODE = ?";
			pstmt = conn.prepareStatement( sql );
			pstmt.setString(1, loginCode);						
			rs = pstmt.executeQuery();	
			if( rs.next() )
			{
				empCode = rs.getString("EMP_CODE");
			}
			if(rs != null)
			{	
				rs.close();
				rs = null;
			}
			if(pstmt != null)
			{	
				pstmt.close();
				pstmt = null;
			}	
			// added BY RITESH on 01/mar/14 end
			sSQL="SELECT * FROM PHYSCANDET WHERE TRAN_ID=? ";
			pstmt2=conn.prepareStatement(sSQL);
			pstmt2.setString(1, tranId);
			rs2=pstmt2.executeQuery();
			while(rs2.next())
			//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 
					
					
				//parentNode = parentNodeList.item(ctr);
				//if( "A".equalsIgnoreCase( getCurrentUpdateFlag( parentNode ) ))
				//{
					/*Commented by Manoj dtd 14022013 to get Values from table 
					 System.out.println("Update Flag---"+getCurrentUpdateFlag( parentNode ));
					lineNo=Integer.parseInt(genericUtility.getColumnValueFromNode("line_no", parentNode));
					System.out.println("lineNo----"+lineNo);
					quantityInput= Double.parseDouble(genericUtility.getColumnValueFromNode("phy_input", parentNode));
					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));
					*/
					lineNo=rs2.getInt("line_no");
					System.out.println("lineNo----"+lineNo);
					quantityInput= rs2.getDouble("phy_input");
					itemCode = rs2.getString("item_code");
					locCode = rs2.getString("loc_code");
					lotNo = rs2.getString("lot_no");
					lotSl = rs2.getString("lot_sl");
					refId=rs2.getString("ref_id");
					refLineNo=rs2.getInt("ref_line_no");
					tranType=rs2.getString("tran_type");
					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);
	
					
					if(tranType!=null &&  !"E".equalsIgnoreCase(tranType))
					{
						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;
						
					}
					// added BY RITESH on 01/mar/14 start
					sql="select count(*) from physcandet where tran_id=? and item_code=? and QUANTITY<>QTY_PHYSICAL";
					pstmt1=conn.prepareStatement(sql);
					pstmt1.setString(1,refId);
					pstmt1.setString(2,itemCode);
					rs1=pstmt1.executeQuery();
					if(rs1.next())
					{
						rec=rs1.getInt(1);
					}
					rs1.close();
					rs1=null;
					pstmt1.close();
					pstmt1=null;
					if(rec > 0)
					{
						System.out.println("rec > 1 :  qty mismatch found");
						sql="update physcandet set EMP_CODE__VERIFY = ? where tran_id=? and item_code=? ";
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,empCode);
						pstmt1.setString(2,refId);
						pstmt1.setString(3,itemCode);
						pstmt1.executeUpdate();
						pstmt1.close();
						pstmt1=null;
						
					}
					else
					{
						System.out.println("EMP_CODE__USER UPDATED");
						sql="update physcandet set EMP_CODE__USER = '"+empCode +"' ,EMP_CODE__VERIFY = '"+empCode+"' where tran_id=? and item_code=? ";   	// change  on 01/mar/14 start
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setString(1,refId);
						pstmt1.setString(2,itemCode);
						pstmt1.executeUpdate();
						pstmt1.close();
						pstmt1=null;
						
					}
					
					sql="update physcandet set EMP_CODE__VERIFY = ? where tran_id=? and  reverify='N' ";
					pstmt1=conn.prepareStatement(sql);
					pstmt1.setString(1,empCode);
					pstmt1.setString(2,tranId);
					upd = pstmt1.executeUpdate();
					pstmt1.close();
					pstmt1=null;
					System.out.println(" upd success::"+upd);
//					sql="update physcandet set EMP_CODE__USER = ? where tran_id=? and  reverify='Y' ";
//					pstmt1=conn.prepareStatement(sql);
//					pstmt1.setString(1,empCode);
//					pstmt1.setString(2,tranId);
//					upd = pstmt1.executeUpdate();
//					pstmt1.close();
//					pstmt1=null;
//					System.out.println("EMP_CODE__USER updated success::"+upd);
					// added BY RITESH  01/mar/14 end

			}
			rs2.close();
			rs2=null;
			pstmt2.close();
			pstmt2=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;
	}	
	private String getCurrentUpdateFlag(Node currDetail)
	{
		NodeList currDetailList = null;
		String updateStatus = "",nodeName = "";
		int currDetailListLength = 0;

		currDetailList = currDetail.getChildNodes();
		currDetailListLength = currDetailList.getLength();
		for (int i=0;i< currDetailListLength;i++)
		{
			nodeName = currDetailList.item(i).getNodeName();
			if (nodeName.equalsIgnoreCase("Attribute"))
			{
				updateStatus = currDetailList.item(i).getAttributes().getNamedItem("updateFlag").getNodeValue();
				break;
			}
		}
		return updateStatus;		
	}
	
}



