//
/*******************************************    
    Title : Physical item scan post save
    Date  : 24/06/12
    Author: sumit sarkar

 ********************************************************/
package ibase.webitm.ejb.wms;

import ibase.system.config.*;
import ibase.webitm.ejb.*;
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 ibase.webitm.ejb.ITMDBAccessEJB;
import ibase.webitm.utility.TransIDGenerator;
import ibase.utility.CommonConstants;
import java.io.*;
import java.rmi.RemoteException;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import org.w3c.dom.*;

import javax.ejb.*;
import javax.naming.InitialContext;

@javax.ejb.Stateless
public class PhyItemScanPos extends ValidatorEJB implements PhyItemScanPosLocal, PhyItemScanPosRemote
{
	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 sql = "", sSQL="",itemCode = "", locCode = "", sql1="";
		boolean isError = false;
		GenericUtility genericUtility = GenericUtility.getInstance();
		ITMDBAccessEJB itmDBAccess = new ITMDBAccessEJB();		
		PreparedStatement pstmt = null, pstmt1 = null;		
		ResultSet rs = null, rs1 = null;
		DistCommon discommon = new DistCommon();
		java.util.Date utilDate = new java.util.Date();
		java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime());
		int count = 0, countNew = 0;
		String siteCode="";
		String lotNo="";
		String refId="";
		java.sql.Timestamp tranDate=null;
		String lotSl="";
		Document dom = null;
		int length=0;
		int lineNo=0;
		java.util.Date currDate = null;
		String dbDateFormat="";
		SimpleDateFormat sdf=null;
		String currDateStr="",currAppdate= "";
		Timestamp currDateNew=null;
		Double quantityInput = 0.0;
		String invStat = "", winName = "";
		NodeList parentNodeList = null;
		Node parentNode = null;
		String unit="";
		int rec=0;
		String tranType="";

		try
		{
			conn.setAutoCommit(false);	
			dom=parseString(domString);
			currDate = new java.util.Date();
			dbDateFormat = genericUtility.getDBDateFormat();
			sdf = new SimpleDateFormat(dbDateFormat);
			currDateStr = sdf.format(currDate);
			//Changed by sumit sarkar to get login code  start.						
			//loginCode = checkNull(genericUtility.getValueFromXTRA_PARAMS( xtraParams, "loginCode" ));
			currDateNew = new java.sql.Timestamp(System.currentTimeMillis()) ;
			currAppdate = new SimpleDateFormat(genericUtility.getApplDateFormat()).format(currDateNew).toString();	
			currAppdate = genericUtility.getValidDateString(currAppdate,genericUtility.getApplDateFormat(),genericUtility.getDBDateFormat());
			currDateNew = Timestamp.valueOf(currAppdate + " 00:00:00.00");

			String resrvLoc  = discommon.getDisparams("999999","RESERV_LOCATION",conn);
			String casePickLoc  = discommon.getDisparams("999999","CASE_PICK_INVSTAT",conn);
			//String activePickLoc  = discommon.getDisparams("999999","ACTIVE_PICK_INVSTAT",conn);
			String deepStoreLoc  = discommon.getDisparams("999999","DEEP_STORE_INVSTAT",conn);

			parentNodeList = dom.getElementsByTagName("Detail2");
			parentNode = parentNodeList.item(0);
			winName = getObjName(parentNode);
			System.out.println("winName = "+winName);
			System.out.println("editFlag----"+editFlag);
			System.out.println("domString----"+domString);
			if("w_phy_scan_verify".equalsIgnoreCase(winName)) //added by Kunal on 07/09/12
			{
				length=dom.getElementsByTagName("Detail3").getLength();
			}
			else
			{
				length=dom.getElementsByTagName("Detail2").getLength();
			}
			for(int i =0; i<length ; i++)
			{
				if("w_phy_scan_verify".equalsIgnoreCase(winName))//added by Kunal on 07/09/12
				{
					lineNo=Integer.parseInt((genericUtility.getColumnValueFromNode("line_no",dom.getElementsByTagName("Detail3").item(i))).trim());
					tranId=genericUtility.getColumnValueFromNode("tran_id",dom.getElementsByTagName("Detail3").item(i));
				}
				else
				{
					lineNo=Integer.parseInt((genericUtility.getColumnValueFromNode("line_no",dom.getElementsByTagName("Detail2").item(i))).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
				System.out.println("invStat"+invStat);
				

				System.out.println("lineNo----"+lineNo);

				sSQL="SELECT * FROM PHYSCANDET WHERE TRAN_ID=? and line_no=? ";
				pstmt=conn.prepareStatement(sSQL);
				pstmt.setString(1,tranId);
				pstmt.setInt(2,lineNo);
				rs=pstmt.executeQuery();
				if(rs.next())
				{
					itemCode=rs.getString("ITEM_CODE");
					siteCode=rs.getString("SITE_CODE");
					lotNo=rs.getString("LOT_NO");
					locCode=rs.getString("LOC_CODE");
					lotSl=rs.getString("LOT_SL");
					quantityInput = rs.getDouble("PHY_INPUT");
					unit=rs.getString("UNIT");
					tranType=rs.getString("TRAN_TYPE");
					if(unit == null || unit.trim().length() == 0)
						{
							sql1 ="select unit from item  where item_code = ? ";
							pstmt1 = conn.prepareStatement(sql1);
							pstmt1.setString(1, itemCode);
							rs1 = pstmt1.executeQuery();
							if( rs1.next())
							{
								unit = rs1.getString("unit");
							}
							rs1.close();
							rs1 = null;
							pstmt1.close();
							pstmt1 = null;
						}
					
					sql="update PHYSCANDET SET STATUS='Y',UNIT=? WHERE  " +
							" tran_id=? and line_no=?";
					pstmt1=conn.prepareStatement(sql);
					pstmt1.setString(1,unit);
					pstmt1.setString(2,tranId);
					pstmt1.setInt(3,lineNo);
					pstmt1.executeUpdate();
					pstmt1.close();
					pstmt1=null;

					if(tranType != null && !"E".equalsIgnoreCase(tranType))
					{
						sql="update STOCK SET LAST_PHYC_DATE=? WHERE item_code=? and" +
								" site_code=? and lot_no=? and loc_code=? and lot_sl=?  " ;
	
						pstmt1=conn.prepareStatement(sql);
						pstmt1.setTimestamp(1,currDateNew);
						pstmt1.setString(2,itemCode);
						pstmt1.setString(3,siteCode);
						pstmt1.setString(4,lotNo);
						pstmt1.setString(5,locCode);
						pstmt1.setString(6,lotSl);
	
						pstmt1.executeUpdate();
						pstmt1.close();
						pstmt1=null;
					}
					
					if(invStat == null || invStat.trim().length() == 0)
					{
						sql1 ="select inv_stat from location  where loc_code = ? ";
						pstmt1 = conn.prepareStatement(sql1);
						pstmt1.setString(1, locCode);
						rs1 = pstmt1.executeQuery();
						if( rs1.next())
						{
							invStat = rs1.getString("inv_stat");
						}
						rs1.close();
						rs1 = null;
						pstmt1.close();
						pstmt1 = null;
					}
					System.out.println("invStat"+invStat);

					//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";
					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;
					}


				}
				rs.close();
				rs=null;
				pstmt.close();
				pstmt=null;
			}

			/*
			sSQL = "SELECT I.ITEM_CODE,S.LOC_CODE FROM ITEM_PROCESS I,STOCK S,ITEM M,LOCATION L"
				   +" WHERE I.TRAN_DATE = ? AND I.ITEM_CODE = S.ITEM_CODE AND " 
				   +" I.ITEM_CODE = M.ITEM_CODE  AND S.QUANTITY > 0 "
				   +" AND S.LOC_CODE = L.LOC_CODE  AND L.INV_STAT IN(?,?,?) " ;				  
				   //+" AND (I.ITEM_CODE, S.LOC_CODE ) IN (SELECT ITEM_CODE, LOC_CODE FROM PHYSCANDET)";

			System.out.println("** sSQL **["+sSQL+"]");
			pstmt = conn.prepareStatement(sSQL);
			pstmt.setDate(1, sqlDate);
			pstmt.setString(2, resrvLoc);
			pstmt.setString(3, casePickLoc);
			//pstmt.setString(4, activePickLoc);
			pstmt.setString(4, deepStoreLoc);			
			rs = pstmt.executeQuery();
			while( rs.next())
			{

				itemCode = rs.getString("ITEM_CODE");
				locCode = rs.getString("LOC_CODE");

				sql1 = "SELECT COUNT(*) AS CNT FROM ITEM_PROCESS I,STOCK S,ITEM M,LOCATION L"
					   +" WHERE I.TRAN_DATE = ? AND I.ITEM_CODE = S.ITEM_CODE AND " 
					   +" I.ITEM_CODE = M.ITEM_CODE  AND S.QUANTITY > 0 "
					   +" AND S.LOC_CODE = L.LOC_CODE  AND L.INV_STAT IN(?,?,?) "
					   +" AND S.ITEM_CODE = ? ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setDate(1, sqlDate);
				pstmt1.setString(2, resrvLoc);
				pstmt1.setString(3, casePickLoc);
				//pstmt1.setString(4, activePickLoc);
				pstmt1.setString(4, deepStoreLoc);	
				pstmt1.setString(5, itemCode);
				rs1 = pstmt1.executeQuery();
				if( rs1.next())
				{
					count = rs1.getInt("CNT");
				}
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				if( pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}

				sql1 = "SELECT COUNT(*) AS CNT FROM PHYSCANDET "
					   +" WHERE ITEM_CODE = ? ";
				pstmt1 = conn.prepareStatement(sql1);
				pstmt1.setString(1, itemCode);
				rs1 = pstmt1.executeQuery();
				if( rs1.next())
				{
					countNew = rs1.getInt("CNT");
				}
				System.out.println(" count ["+count+"] and countNew ["+countNew+"]");
				if( rs1 != null )
				{
					rs1.close();
					rs1 = null;
				}
				if( pstmt1 != null)
				{
					pstmt1.close();
					pstmt1 = null;
				}

				if( count == countNew)
				{				
					sql = "UPDATE ITEM_PROCESS SET STATUS = 'Y' WHERE ITEM_CODE = ? "
					//Changed by Rohan on 7/3/12
					+" AND TRAN_DATE = ? "; 
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, rs.getString("ITEM_CODE"));
					pstmt1.setDate(2, sqlDate);
					pstmt1.executeUpdate();
					System.out.println("upadte done !!!!!!!!!!!!!!");
					pstmt1.clearParameters();
					if( pstmt1 != null)
					{
						pstmt1.close();
						pstmt1 = null;
					}
				}
			}
	        if(rs != null)
	        {
	        	rs.close();
	        	rs = null;
	        }
	        if( pstmt != null)
	        {
	        	pstmt.close();
	        	pstmt = 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 getObjName(Node node) throws ITMException
	{
		String objName = "";
		NodeList nodeList = null;
		Node detaulNode = null;
		Node detailNode = null;
		if(node != null)
		{
		nodeList = node.getChildNodes();
		NamedNodeMap attrMap = node.getAttributes();
		objName = attrMap.getNamedItem( "objName" ).getNodeValue();
		}
		/*
		for(int ctr = 0; ctr < nodeList.getLength(); ctr++ )

		{

			detailNode = nodeList.item(ctr);

			if(detailNode.getNodeName().equalsIgnoreCase("attribute") )

			{

				objName = detailNode.getAttributes().getNamedItem("objName").getNodeValue();

			}

		}
		 */
		return "w_" + objName;

	}

}
class PhyScanQtyUpdate 
{

	public int quantityUpdate(String tranId,int lineNo,String  locCode,String itemCode,String lotNo,String lotSl,double  quantityInput ,String invStat,Connection conn) throws ITMException
	{
		System.out.println("quantityUpdate called...");
		int upctr = 0;
		PreparedStatement pstmt = null ;
		ResultSet rs = null ;
		String phyHanBasis="",unit="",toUnit="",mrfPartNo="";
		double shipperSize = 0, volumn = 0, convFact = 1, meffQty = 0,quantity=0;
		DistCommon discommon = new DistCommon();
		String sql = "";	

		try
		{
			java.util.Date dt=new Date();
			GenericUtility genericUtility=GenericUtility.getInstance();
			SimpleDateFormat sdf1= new SimpleDateFormat(genericUtility.getDBDateFormat());
			java.sql.Timestamp dts=java.sql.Timestamp.valueOf(sdf1.format(dt).toString() + " 00:00:00.0");


			sql = " SELECT L.PHY_HAN_BASIS, L.PHY_HAN_UOM , D.UNIT FROM PHYSCAN H,PHYSCANDET D,ITEM M,LOCATION L "
					+ " WHERE H.TRAN_ID=D.TRAN_ID AND H.TRAN_ID = ? AND D.ITEM_CODE = M.ITEM_CODE  AND D.LOC_CODE = L.LOC_CODE  AND L.INV_STAT = ? "
					+ " AND D.LOT_NO = ? AND D.ITEM_CODE = ? AND D.LOT_SL = ?  ";
					

			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, tranId);								
			pstmt.setString(2, invStat);	
			pstmt.setString(3, lotNo);
			pstmt.setString(4, itemCode);
			pstmt.setString(5, lotSl);

			rs = pstmt.executeQuery();
			if( rs.next())
			{
				phyHanBasis = rs.getString("phy_han_basis");
			}
			rs.close();
			rs = null;
			pstmt.close();
			pstmt = null;

			sql =" SELECT ILP.SHIPPER_SIZE, I.UNIT, I.MFR_PART_NO, I.VOLUME FROM ITEM I,  ITEM_LOT_PACKSIZE ILP " +
					" WHERE ILP.ITEM_CODE = ?  AND I.ITEM_CODE = ILP.ITEM_CODE "+
					" AND ? BETWEEN LOT_NO__FROM AND LOT_NO__TO ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, itemCode);
			pstmt.setString(2, lotNo);
			rs = pstmt.executeQuery();
			if( rs.next())
			{
				shipperSize = rs.getInt("SHIPPER_SIZE");
				mrfPartNo = rs.getString("MFR_PART_NO");
				volumn = rs.getDouble("VOLUME");
			}
			rs.close();
			rs = null;
			pstmt.close();



			if("L".equalsIgnoreCase(phyHanBasis))
			{
				quantity = shipperSize * quantityInput;  
			}

			else if("I".equalsIgnoreCase(phyHanBasis))
			{
				quantity= quantityInput;
			}

			else if ("U".equalsIgnoreCase(phyHanBasis))
			{
				System.out.println(" as per 'U' ");
				if(!unit.equalsIgnoreCase(toUnit))
				{
					if(volumn != 0)
					{
						convFact = volumn;
					}
					ArrayList convQtyList = discommon.getConvQuantityFact(unit, toUnit, itemCode, quantityInput, convFact,conn);
					System.out.println("convQtyList = "+convQtyList);
					meffQty = Double.parseDouble(convQtyList.get(1).toString());
					System.out.println("meffQty ["+meffQty+"]");

					if(meffQty == -999999999)
					{
						meffQty = 0;	
					}

					if (volumn == 0)
					{
						volumn= Double.parseDouble(convQtyList.get(0).toString());
					}

				}

				else
				{
					meffQty = quantityInput ;//change  bykunal on 12/09/12 
					if (volumn == 0) 
					{
						volumn=1;
					}
				}

				

				quantity = meffQty;
				System.out.println("meffQty ["+meffQty+"] and quantity ["+quantity+"]");

			}

			else if ("P".equalsIgnoreCase(phyHanBasis))
			{											
				quantity =  quanitityPerPallet(itemCode,lotNo, quantityInput ,conn );
			}

			sql = " update  physcandet set qty_physical = ?, phy_han_basis = ? where tran_id = ? and line_no = ? ";
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, quantity);
			pstmt.setString(2, phyHanBasis);
			pstmt.setString(3, tranId);
			pstmt.setInt(4, lineNo);
			upctr = pstmt.executeUpdate();	
			System.out.println("NO OF ROW UPDATED = "+upctr);
			pstmt.close();
			pstmt = null;

		}
		catch(Exception e)
		{
			try 
			{
				conn.rollback();
			}
			catch (SQLException e1) 
			{
				e1.printStackTrace();
			}
			System.out.println("Exception "+e.getMessage());
			e.printStackTrace();
			throw new ITMException(e);
		}
		finally
		{
			try
			{

				if( rs != null )
				{
					rs.close();
					rs = null;
				}
				if( pstmt != null )
				{
					pstmt.close();
					pstmt = null;
				}

			}
			catch(Exception e)
			{
				System.err.println("Exception :PoRcpPos :\n"+e.getMessage());
				e.printStackTrace();
				throw new ITMException(e);
			}
		}

		return upctr;
	}

	private double quanitityPerPallet(String itemCode,String lotNo, double pallet ,Connection conn) throws ITMException

	{



		String sql="";

		PreparedStatement pstmt = null;

		ResultSet rs =null;

		double shipperSize =0.0;

		double grossWeight =0.0;

		double netWeight =0.0;

		double length=0.0;

		double width=0.0;

		double height=0.0;


		double quantity = 0.0;

		DistCommon distComm = new DistCommon();



		sql= "SELECT SHIPPER_SIZE, GROSS_WEIGHT, NET_WEIGHT, LENGTH, WIDTH ,HEIGHT FROM ITEM_LOT_PACKSIZE WHERE ITEM_CODE = ? " +

			" AND ? BETWEEN LOT_NO__FROM AND LOT_NO__TO "; 

		try {

			pstmt =conn.prepareStatement(sql);

			pstmt.setString(1,itemCode);

			pstmt.setString(2,lotNo);

			rs=pstmt.executeQuery();

			if(rs.next())

			{

				shipperSize=rs.getDouble(1);

				grossWeight =rs.getDouble(2);

				netWeight=rs.getDouble(3);

				length=rs.getDouble(4);

				width=rs.getDouble(5);

				height=rs.getDouble(6);

			}

			rs.close();

			pstmt.close();

			rs=null;

			pstmt =null;

			double perPallateVolume = Double.parseDouble(distComm.getDisparams("999999","PER_PALLET__VOLUME",conn));

			System.out.println("shipperSize ["+shipperSize+"] grossWeight ["+grossWeight+"] netWeight ["+netWeight+"] length ["+length+"] width ["+width+"] height ["+height+"]");

			//nosOfPallet=(quantity*length*width*height/shipperSize)/(Double.parseDouble(distComm.getDisparams("999999","PER_PALLET__VOLUME",conn)));

			double newValue = Math.floor((pallet * perPallateVolume)/(length*width*height));

			System.out.println(" before Math.floor -->["+newValue+"]");

			quantity = newValue * shipperSize;			

			System.out.println("<!@#>QUANTITY  " +quantity);

			/*//nosOfPalletPerVolume=(new Double((Math.round(nosOfPallet)))).intValue();

		System.out.println("<!@#>nosOfPalletPerVolume" +nosOfPalletPerVolume);*/						

			//System.out.println("<!@#>findNosOfPallet" +findNosOfPallet);



		} catch (Exception e) {

			e.printStackTrace();
			throw new  ITMException(e);

		}		

		return quantity; 

	}




}


