/*  PURPOSE  : Scan loc_code and lon_no OR LOT_SL then Display Detail information of stock.
 *  AUTHOR   : Created By Dhanraj Thakare On 05/09/2014 W14FSUN004 
 *  
 */

package ibase.webitm.ejb.wms;

import ibase.webitm.utility.ITMException;

//import java.util.HashMap;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.HashMap;
//import java.sql.SQLException;
//import java.sql.Statement;
import ibase.system.config.ConnDriver;

import javax.ejb.Stateless;

@Stateless
public class InventoryDispInfoDB implements InventoryDispInfoDBRemote,InventoryDispInfoDBLocal
{
	
   public String getStocklDataLoc( String location,String lotNo,String lpn,String site) throws ITMException
   {
	    System.out.println("Call InventoryDispInfo getStocklDataLoc ");
	    System.out.println("Call InventoryDispInfo getStocklDataLoc Location [ "+location+" ] ####### lotNo [ "+lotNo+" ] Site Code [ "+site+" ]");
	    Connection conn = null;
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
		ResultSet rs = null;  
		//HashMap<String,String> pndListObj = new HashMap<String,String>();
		StringBuffer valueXmlString = new StringBuffer();
		
		valueXmlString = new StringBuffer( "<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>" );
	    valueXmlString.append( "A" ).append( "</editFlag>\r\n</Header>\r\n" ); //SET A.
        
		try{
			
				conn = connDriver.getConnectDB("DriverITM");
				int domID=0;
				String query = "";
				
				String itemCode="";
				String LotNo="";
				String LotSl="";
				String quantity="";
				String availQuantity="";
				String allocQuantity="";
				String holdQty="";
				String locationCode="";
				String itemDescr="";
				String condWhere="";
				String unit="";
				double noArt=0.0;
				double packWT=0.0;
				
				if("".equals(location) || location == null )
				{
					
				}else{
					condWhere=" AND STOCK.LOC_CODE ='"+location+"'";
				}
				
				if("".equals(lotNo) || lotNo == null )
				{
					
				}else{
					condWhere= condWhere + " AND STOCK.LOT_NO ='"+lotNo+"'";
				}
				
				if("".equals(lpn) || lpn == null )
				{
					
				}else{
					condWhere =condWhere +" AND STOCK.LOT_SL='"+lpn+"'";
				}
				
				System.out.println("condWhere$$$$$$$$$$$$$$$$$ "+condWhere);
				query = "SELECT STOCK.LOC_CODE,STOCK.LOT_NO,nvl(QUANTITY,0) as QUANTITY,nvl(ALLOC_QTY,0) as ALLOC_QTY ,nvl(HOLD_QTY,0) as HOLD_QTY,"
					   +" (nvl(QUANTITY,0)-nvl(ALLOC_QTY,0))as AVAIL_QTY,nvl(NO_ART,0) as NO_ART,STOCK.ITEM_CODE,STOCK.LOT_SL,ITEM.DESCR,ITEM.UNIT "
	            	   +" FROM STOCK,ITEM WHERE STOCK.ITEM_CODE=ITEM.ITEM_CODE AND QUANTITY>0 AND STOCK.SITE_CODE= ? "+condWhere+"  ORDER BY LOC_CODE,ITEM_CODE,LOT_NO,LOT_SL ";//PENDING......FROM JOINS 
				
			/*pstmt = conn.prepareStatement(query);
			rs = pstmt.executeQuery();*/
			
			pstmt = conn.prepareStatement( query );
			pstmt.setString( 1, site );	
		/*	pstmt.setString( 2, lotNo );	*/
			rs = pstmt.executeQuery();
			
			while(rs.next())
			{
				//pndListObj.put("1", "1"); //map for key ,value
				packWT=0.0;
				
				itemCode=rs.getString("ITEM_CODE").trim();
				LotNo=rs.getString("LOT_NO").trim();
				holdQty=rs.getString("HOLD_QTY");
				quantity=rs.getString("QUANTITY");
				availQuantity=rs.getString("AVAIL_QTY");
				allocQuantity=rs.getString("ALLOC_QTY");
				locationCode=rs.getString("LOC_CODE");
				LotSl= rs.getString("LOT_SL");
				noArt=Double.parseDouble(rs.getString("NO_ART"));
				unit = rs.getString("UNIT");
				itemDescr=rs.getString("DESCR");
				System.out.println(" 55555555555555555555 "+quantity);
				HashMap itemVolMap = getItemVoumeMap(itemCode, LotNo, conn);
				packWT = (Double)itemVolMap.get("PACK_WEIGHT");	
				packWT=packWT * noArt;
				domID++;
	             System.out.println("Location code from db===>"+rs.getString("LOC_CODE"));
	             
				valueXmlString.append( "<Detail1 domID='"+ domID +"' selected=\"N\">\r\n" );
					
				long holdQtyL=Long.parseLong(holdQty);
				if(holdQtyL>0){
					valueXmlString.append("<hold_flg><![CDATA[").append("1").append("]]></hold_flg>\r\n");// for hold item
				}else{
					valueXmlString.append("<hold_flg><![CDATA[").append("0").append("]]></hold_flg>\r\n");//normal item
				}
				
				valueXmlString.append("<item><![CDATA[").append(itemCode).append("]]></item>\r\n");
				valueXmlString.append("<loc_code><![CDATA[").append(locationCode).append( "]]></loc_code>\r\n" );
				valueXmlString.append("<lot_no><![CDATA[").append(LotNo).append("]]></lot_no>\r\n");
				
				valueXmlString.append("<lot_sl><![CDATA[").append(LotSl).append("]]></lot_sl>\r\n");  
				
				valueXmlString.append("<quantity><![CDATA[").append(quantity).append("]]></quantity>\r\n"); 
				valueXmlString.append("<alloc_qty><![CDATA[").append(allocQuantity).append("]]></alloc_qty>\r\n");
				valueXmlString.append("<avail_qty><![CDATA[").append(availQuantity).append("]]></avail_qty>\r\n");
				valueXmlString.append("<gross_wt><![CDATA[").append(packWT).append("]]></gross_wt>\r\n");
				
				/*valueXmlString.append("<loc_code><![CDATA[").append(locationCode).append( "]]></loc_code>\r\n" ); 
				valueXmlString.append("<lot_no><![CDATA[").append(LotNo).append("]]></lot_no>\r\n");*/
				
				valueXmlString.append("<hold_qty><![CDATA[").append(holdQty).append("]]></hold_qty>\r\n");
				valueXmlString.append("<no_art><![CDATA[").append(noArt).append("]]></no_art>\r\n");
				valueXmlString.append("<item_descr><![CDATA[").append(itemDescr).append("]]></item_descr>\r\n");
				valueXmlString.append("<unit><![CDATA[").append(unit).append("]]></unit>\r\n");
				
				valueXmlString.append("</Detail1>\r\n");
			}
			
		//System.out.println(" Pending activity wise count....."+valueXmlString.toString());
		}
		catch (Exception E)
		{
			 E.printStackTrace();
		}
		finally
		{
			try
			{
				if(conn != null)
				{
					if(rs != null) 
					{
						rs.close();
						rs = null;
					}
					if(pstmt != null) 
					{
						pstmt.close();
						pstmt = null;
	
					}
					conn.close();
				}
				conn = null;	
			}
			catch(Exception d)
			{
				d.printStackTrace();
			}		
		}
	
	valueXmlString.append( "</Root>\r\n" );	
    System.out.println( "\n****ValueXmlString :" + valueXmlString.toString() + ":********" );
    return valueXmlString.toString();
   // return pndListObj;
  
  }
	
 /* public String getStocklDataLpn(String lpnSl) throws ITMException
  {

		System.out.println("Call InventoryDispInfo getStockDataLpn ");
		System.out.println("Call InventoryDispInfo getStockDataLpn LPN No [ "+lpnSl+" ]");
	    Connection conn = null;
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
		ResultSet rs = null;  
	    int domID=0;
		HashMap<String,String> pndListObj = new 	HashMap<String,String>();
		
        StringBuffer valueXmlString = new StringBuffer();
		
		valueXmlString = new StringBuffer( "<?xml version=\"1.0\"?>\r\n<Root>\r\n<Header>\r\n<editFlag>" );
        valueXmlString.append( "A" ).append( "</editFlag>\r\n</Header>\r\n" ); //SET A.
		
		try{
		
			conn = connDriver.getConnectDB("DriverITM");
			
			String query = "";
			
			query = "SELECT LOC_CODE,LOT_NO,nvl(QUANTITY,0) as QUANTITY,nvl(ALLOC_QTY,0) as ALLOC_QTY ,nvl(HOLD_QTY,0) as HOLD_QTY, "
				   +" (nvl(QUANTITY,0)-(nvl(ALLOC_QTY,0)+ nvl(HOLD_QTY,0)))as AVAIL_QTY,nvl(NO_ART,0) as NO_ART,LOT_SL,ITEM_CODE "
	               +" FROM STOCK WHERE LOT_SL= ? ";//PENDING......FROM JOINS 
			
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		
		pstmt = conn.prepareStatement( query );
		pstmt.setString( 1,lpnSl);	
		rs = pstmt.executeQuery();
		
		String itemCode="";
		String LotNo="";
		String LotSl="";
		String quantity="";
		String availQuantity="";
		String allocQuantity="";
		String holdQty="";
		String locationCode="";
		double packWT=0.0;
		
		while(rs.next())
		{
				itemCode=rs.getString("ITEM_CODE").trim();
				LotNo=rs.getString("LOT_NO").trim();
				holdQty=rs.getString("HOLD_QTY");
				quantity=rs.getString("QUANTITY");
				availQuantity=rs.getString("AVAIL_QTY");
				allocQuantity=rs.getString("ALLOC_QTY");
				locationCode=rs.getString("LOC_CODE");
				LotSl=rs.getString("LOT_SL");
				System.out.println(" 66666666666666666666 "+quantity);
			
	        HashMap itemVolMap = getItemVoumeMap(itemCode, LotNo, conn);
			packWT = (Double)itemVolMap.get("PACK_WEIGHT");
			
			domID++;

			valueXmlString.append( "<Detail1 domID='"+ domID +"' selected=\"N\">\r\n" );
				
				//valueXmlString.append("<line_no/>\r\n");
				//In feed form line_no currently is not generating automatically so line_no is set as "1"
				//valueXmlString.append("<line_no><![CDATA[").append("1").append("]]></line_no>\r\n");
			
			valueXmlString.append("<loc_code><![CDATA[").append(locationCode).append( "]]></loc_code>\r\n" );
			valueXmlString.append("<lot_no><![CDATA[").append(LotNo).append("]]></lot_no>\r\n");
			
			valueXmlString.append("<item><![CDATA[").append(itemCode).append("]]></item>\r\n");
			
			valueXmlString.append("<quantity><![CDATA[").append(quantity).append("]]></quantity>\r\n");
			valueXmlString.append("<alloc_qty><![CDATA[").append(allocQuantity).append("]]></alloc_qty>\r\n");
			valueXmlString.append("<avail_qty><![CDATA[").append(availQuantity).append("]]></avail_qty>\r\n");
			valueXmlString.append("<gross_wt><![CDATA[").append(packWT).append("]]></gross_wt>\r\n");
			
			valueXmlString.append("<hold_qty><![CDATA[").append(holdQty ).append("]]></hold_qty>\r\n");//for flag.
			valueXmlString.append("<lot_sl><![CDATA[").append(LotSl).append("]]></lot_sl>\r\n");
			valueXmlString.append("<no_art><![CDATA[").append(rs.getString("NO_ART")).append("]]></no_art>\r\n");
			
			valueXmlString.append("</Detail1>\r\n");
		}
		
	   System.out.println(" Stock Detail....."+valueXmlString.toString());
	}
	catch (Exception E)
	{
		 E.printStackTrace();
	}
	finally
	{
		try
		{
			if(conn != null)
			{
				if(rs != null) 
				{
					rs.close();
					rs = null;
				}
				if(pstmt != null) 
				{
					pstmt.close();
					pstmt = null;

				}
				conn.close();
			}
			conn = null;	
		}
		catch(Exception d)
		{
			d.printStackTrace();
		}		
	}
	valueXmlString.append( "</Root>\r\n" );	
	System.out.println( "\n****ValueXmlString :" + valueXmlString.toString() + ":********" );
    return valueXmlString.toString();
 }*/
  
  private HashMap getItemVoumeMap(String itemCode,String lotNo,Connection con)throws Exception
	{
		double packSize = 0,itemSize = 0,lotSize = 0;
		PreparedStatement pstmt = null;
		String sql="";
		ResultSet rs = null;
		double itmLen = 0,itmWidth = 0,itmHeight = 0,itemWeight = 0,lotLen = 0 ,lotHeight = 0,lotWidth = 0,lotWeight = 0;
		HashMap dataVolumeMap = new HashMap();
		
		try {
			
			sql = "SELECT I.LENGTH ITEM_LEN,I.WIDTH ITEM_WID,I.HEIGHT ITEM_HEIGHT,I.GROSS_WEIGHT ITEM_WEIGHT,"
				  +" L.LENGTH LITEM_LEN,L.WIDTH LITEM_WID,L.HEIGHT LITEM_HEIGHT,L.SHIPPER_SIZE SHIPSIZE,L.GROSS_WEIGHT LOT_WEIGHT FROM"
				  +" ITEM I,ITEM_LOT_PACKSIZE L"
				  +" WHERE I.ITEM_CODE = L.ITEM_CODE"
				  +" AND L.LOT_NO__FROM <= ? AND L.LOT_NO__TO >= ?"
				  +" AND  I.ITEM_CODE = ?";
				  
			
			pstmt = con.prepareStatement(sql);
			if(lotNo != null && lotNo.length() > 0)
			{
				pstmt.setString(1, lotNo);
				pstmt.setString(2, lotNo);
			}
			else
			{
				pstmt.setString(1, "00");
				pstmt.setString(2, "ZZ");
			}
			pstmt.setString(3, itemCode);
			rs = pstmt.executeQuery();
			
			if(rs.next())
			{
				itmLen = rs.getDouble("ITEM_LEN");
				itmWidth = rs.getDouble("ITEM_WID");
				itmHeight = rs.getDouble("ITEM_HEIGHT");
				itemWeight = rs.getDouble("ITEM_WEIGHT");
				lotLen = rs.getDouble("LITEM_LEN");
				lotWidth = rs.getDouble("LITEM_WID");
				lotHeight = rs.getDouble("LITEM_HEIGHT");				
				packSize = rs.getDouble("SHIPSIZE");				
				lotWeight = rs.getDouble("LOT_WEIGHT");				
			}
			
			//packSize = (lotHeight * lotWidth * lotLen)/(itmLen * itmWidth * itmHeight);
			/*itemSize = Math.floor(itmLen * itmWidth * itmHeight);
			lotSize = Math.floor((lotHeight * lotWidth * lotLen));*/
			itemSize = itmLen * itmWidth * itmHeight;
			lotSize = lotHeight * lotWidth * lotLen;
			
			dataVolumeMap.put("PACK_SIZE", packSize);
			dataVolumeMap.put("ITEM_SIZE", itemSize);
			dataVolumeMap.put("LOT_SIZE", lotSize);
			dataVolumeMap.put("ITEM_WEIGHT", itemWeight);
			dataVolumeMap.put("PACK_WEIGHT", lotWeight);
			
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
			
			
			
		} catch (Exception e) {
			// TODO: handle exception

			throw e;
		}
		finally
		{
			if(pstmt != null)
			{
				pstmt.close();
				pstmt = null;
		
			}
			if(rs != null)
			{
				rs.close();
				rs = null;
		
			}
		}
		
		return dataVolumeMap;
	}
	
	

}
