/*
 * MPhyItemScanPos.java
 * Developer - Mahesh Patidar
 * Date - 08/08/12 
 */

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 MPhyItemScanPos extends ValidatorEJB implements MPhyItemScanPosLocal, MPhyItemScanPosRemote
{
	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;		
        java.util.Date utilDate = new java.util.Date();
		int lineNo = 0;
		String invStat = "",sql = "";
		Double quantityInput = 0.0;
		String unit="";
		String analysisClass= "",cycleCriteria="";// ADDED ON 28/MAR/14 BY RITESH 
		int rec=0;
		String loginCode= "",empCode=""; // ADDED ON 25/MAR/14 BY RITESH 
		int upd= 0; // ADDED ON 25/MAR/14 BY RITESH 
        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("Detail3");
			parentNodeListLength = parentNodeList.getLength();
			// added on 25/MAR/14 BY RITESH 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 ON 25/MAR/14 BY RITESH END
			for(ctr = 0; ctr < parentNodeListLength ; ctr++ )
			{
				lineNo=Integer.parseInt((genericUtility.getColumnValueFromNode("line_no",dom.getElementsByTagName("Detail3").item(ctr))).trim());
				//quantityInput = Double.parseDouble(genericUtility.getColumnValueFromNode("phy_input", dom.getElementsByTagName("Detail2").item(i))); //added by Kunal
				//invStat = genericUtility.getColumnValue("inv_stat", dom); //added by Kunal
				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("invStat"+invStat);
				
				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);
				System.out.println("ITEM CODE["+itemCode+"] SITE CODE["+siteCode+"] LOC CODE["+locCode+"] LOT_NO["+lotNo+"] LOT SL["+lotSl+"]");
				
		//		sql ="select unit from item  where item_code = ? ";
				sql ="select  unit,analysis_class,cycle_criteria  from item  where item_code = ? "; // // change ON 28/MAR/14 BY RITESH 
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, itemCode);
				rs = pstmt.executeQuery();
				if( rs.next())
				{
				
					unit = rs.getString("unit");
					analysisClass = rs.getString("analysis_class");
					cycleCriteria = rs.getString("cycle_criteria");
				}
				rs.close();
				rs = null;
				pstmt.close();
				pstmt=null;
				
				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 = ?,UNIT=? WHERE TRAN_ID = ? AND ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? ";
				pstmt1 = conn.prepareStatement(sSQL);
				pstmt1.setString(1, "Y");
				pstmt1.setString(2, unit);
				pstmt1.setString(3, tranId);
				pstmt1.setString(4, itemCode);
				pstmt1.setString(5, siteCode);
				pstmt1.setString(6, locCode);
				pstmt1.setString(7, lotNo);
				pstmt1.setString(8, lotSl);				
				pstmt1.executeUpdate();	
				pstmt1.close();
				pstmt1 = null; 
							// added by ritesh on 28/mar/14 start
				sSQL = "UPDATE PHYSCANDET SET analysis_class = ?,cycle_criteria=? WHERE TRAN_ID = ? AND ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ?";
				pstmt1 = conn.prepareStatement(sSQL);
				pstmt1.setString(1, analysisClass);
				pstmt1.setString(2, cycleCriteria);
				pstmt1.setString(3, tranId);
				pstmt1.setString(4, itemCode);
				pstmt1.setString(5, siteCode);
				pstmt1.setString(6, locCode);
				pstmt1.setString(7, lotNo);
				pstmt1.setString(8, lotSl);				
				pstmt1.executeUpdate();	
				pstmt1.close();
				pstmt1 = null; 
										// added by ritesh on 28/mar/14 end
				/*
				sSQL = "UPDATE CYCLECNT_SCH SET STATUS = ?  WHERE ITEM_CODE = ? AND SITE_CODE = ? AND LOC_CODE = ? AND LOT_NO = ? AND LOT_SL = ? AND TRAN_DATE = ? ";
				pstmt1 = conn.prepareStatement(sSQL);
				pstmt1.setString(1, "Y");
				pstmt1.setString(2, itemCode);
				pstmt1.setString(3, siteCode);
				pstmt1.setString(4, locCode);
				pstmt1.setString(5, lotNo);
				pstmt1.setString(6, lotSl);			
				pstmt1.setTimestamp(7, date1);	
				pstmt1.executeUpdate();	
				pstmt1.close();
				pstmt1 = null;
				*/
				
				//added by Kunal ON 07/09/12
				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";
				pstmt=conn.prepareStatement(sql);
				pstmt.setString(1,tranId);
				pstmt.setInt(2,lineNo);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					rec=rs.getInt(1);
				}
				rs.close();
				rs=null;
				pstmt.close();
				pstmt=null;
				if(rec==0)
				{
					sql="update physcandet set verified='Y',reverify='N' where tran_id=? and line_no=? ";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,tranId); // modified on 25/MAR/14
					pstmt.setInt(2,lineNo);   // modified on 25/MAR/14
					pstmt.executeUpdate();
					pstmt.close();
					pstmt=null;
					// ADDED BY RITESH 25/MAR/14 START
					sql="update physcandet set emp_code__verify = ?,reverify='N' where tran_id=? and line_no=? ";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,empCode); 
					pstmt.setString(2,tranId); 
					pstmt.setInt(3,lineNo); 
					upd = pstmt.executeUpdate();
					pstmt.close();
					pstmt=null;
					System.out.println(" UPDATE physcandet ON MANUAL CYCLE COUNT :"+upd);
					
				}else
				{
					sql="update physcandet set reverify='Y' where tran_id=? and line_no=? ";
					pstmt=conn.prepareStatement(sql);
					pstmt.setString(1,tranId); 
					pstmt.setInt(2,lineNo); 
					upd = pstmt.executeUpdate();
					pstmt.close();
					pstmt=null;
					System.out.println(" UPDATE physcandet ONs MANUAL CYCLE COUNT on qty mismatch:"+upd);
				}
				// ADDED BY RITESH 25/MAR/14 START
				
				
			}
		}
		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;
	}	
	
}


