package ibase.dashboard.mfg.ejb;


import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

import javax.ejb.Stateless;

import org.json.simple.JSONObject;

/**
 * Session Bean implementation class RecallDetails
 */
@Stateless

public class RecallDetails extends ValidatorEJB  implements RecallDetailsRemote, RecallDetailsLocal {

    /**
     * Default constructor. 
     */
    public RecallDetails() {
    	
    }
    GenericUtility genericUtility = GenericUtility.getInstance();
    @SuppressWarnings("unchecked")
	public JSONObject getMarketWiseRecallDetails(String dataSourceName,String lastMonth) throws RemoteException, ITMException
	{
    	
    	JSONObject rawDataMarketWiseRecallJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		Date frmDate = null;
		Date toDate = null;
		try
		{
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;				
			
			SimpleDateFormat convrtToTimStmDate = new SimpleDateFormat(genericUtility.getDBDateFormat());
			if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				Calendar cal = Calendar.getInstance();		
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				int month = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, month, cal.get(Calendar.DATE));
			}
           if (frmDate != null && toDate != null )
			{
				sql =" select i.market_reg as MARKET_REG ,count(1) as  MARKET_RECALL "
						+" from item_lot_info l, item i "
						+" where i.item_code = l.item_code "
						//+" and case when l.STOP_BUSINESS is null then 'N' else l.STOP_BUSINESS  end= 'Y' "
						//+" and l.stop_business_date between ? and ? "
						+" group by i.market_reg ";
				pstmt = conn.prepareStatement(sql);
				//pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
				//pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
	
				rs = pstmt.executeQuery();
				int count = 0;			
				while (rs.next())
				{
					rowData = new JSONObject();
					rowData.put("market_reg", checkNull(rs.getString("MARKET_REG")));
					rowData.put("market_recall", rs.getString("MARKET_RECALL"));				
					rawDataMarketWiseRecallJson.put(count, rowData);
					count++;
				}
           }
			System.out.println("getMarketWiseRecallDetails data :"+rawDataMarketWiseRecallJson);
			
		}
		catch (Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		} finally
		{
			try
			{
				if (conn != null)
				{
					if (rs != null)
					rs.close();
					rs = null;
					if (pstmt != null)
					pstmt.close();
					pstmt = null;
					conn.close();
					conn = null;
				}
				conn = null;
			} catch (Exception d)
			{
				d.printStackTrace();
				System.out.println("Exception in :RecallDetails :getPlantWiseRecallDetails:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataMarketWiseRecallJson;
	}
    
    @SuppressWarnings("unchecked")
   	public JSONObject getProductWiseRecallDetails(String dataSourceName,String lastMonth) throws RemoteException, ITMException
   	{
       	
       	JSONObject rawDataProductWiseRecallJson = new JSONObject();
   		JSONObject rowData = null;
   		String sql = "";
   		ResultSet rs = null;
   		PreparedStatement pstmt = null;
   		Connection conn = null;
   		ConnDriver connDriver = new ConnDriver();
   		Date frmDate = null;
		Date toDate = null;
   		try
   		{
   			conn = connDriver.getConnectDB(dataSourceName);
   			connDriver = null;	
   			SimpleDateFormat convrtToTimStmDate = new SimpleDateFormat(genericUtility.getDBDateFormat());
			if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				Calendar cal = Calendar.getInstance();		
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				int month = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, month, cal.get(Calendar.DATE));
			}
            if (frmDate != null && toDate != null )
			{
		
   			sql =" select t.unit__form,count(1) as product_recall "
   					+" from item_lot_info l, item i, item_type t "
   					+" where i.item_code = l.item_code "
   					//+" and t.item_type = i.item_type "
   				//	+" and case when l.STOP_BUSINESS is null then 'N' else l.STOP_BUSINESS  end = 'Y' "
   				//	+" and l.stop_business_date between ? and ? "
   					+" group by t.unit__form ";
   			pstmt = conn.prepareStatement(sql);
   		//pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
			//pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));

   			rs = pstmt.executeQuery();
   			int count = 0;			
   			while (rs.next())
   			{
   				rowData = new JSONObject();
   				rowData.put("unit__form", checkNull(rs.getString("unit__form")));
   				rowData.put("product_recall", rs.getString("product_recall"));				
   				rawDataProductWiseRecallJson.put(count, rowData);
   				count++;
   			}
		}
   			System.out.println("getProductWiseRecallDetails data :"+rawDataProductWiseRecallJson);
   		}
   		catch (Exception e)
   		{
   			e.printStackTrace();
   			throw new ITMException(e);
   		} finally
   		{
   			try
   			{
   				if (conn != null)
   				{
   					if (rs != null)
   					rs.close();
   					rs = null;
   					if (pstmt != null)
   					pstmt.close();
   					pstmt = null;
   					conn.close();
   					conn = null;
   				}
   				conn = null;
   			} catch (Exception d)
   			{
   				d.printStackTrace();
   				System.out.println("Exception in :RecallDetails :getProductWiseRecallDetails:" + d.getMessage());
   				throw new ITMException(d);
   			}
   		}
   		return rawDataProductWiseRecallJson;
   	}
    
    @SuppressWarnings("unchecked")
   	public JSONObject getPlantWiseRecallDetails(String dataSourceName,String lastMonth) throws RemoteException, ITMException
   	{
       	JSONObject rawDataPlantWiseRecallJson = new JSONObject();
   		JSONObject rowData = null;
   		String sql = "";
   		ResultSet rs = null;
   		PreparedStatement pstmt = null;
   		Connection conn = null;
   		
   		ConnDriver connDriver = new ConnDriver();
   		Date frmDate = null;
		Date toDate = null;
   		try
   		{
   			conn = connDriver.getConnectDB(dataSourceName);
   			connDriver = null;				
   			SimpleDateFormat convrtToTimStmDate = new SimpleDateFormat(genericUtility.getDBDateFormat());
			if(lastMonth!=null && lastMonth.trim().length()>0 )
			{
				Calendar cal = Calendar.getInstance();		
				toDate = new Date(cal.get(Calendar.YEAR) - 1900, cal.get(Calendar.MONTH), cal.get(Calendar.DATE));
				cal.get(Calendar.MONTH + 1);
				cal.add(Calendar.MONTH,-(Integer.parseInt(lastMonth)));	
				int month = cal.get(Calendar.MONTH);
				frmDate = new Date(cal.get(Calendar.YEAR) - 1900, month, cal.get(Calendar.DATE));
			}
           if (frmDate != null  && toDate != null)
			{
			
	   			sql =" select s.geo_loc as geo_loc,g.descr as geodescr,count(1) as product_recall "
	   					+" from item_lot_info l, site s,gencodes g "
	   					+" where g.fld_value = s.geo_loc and g.fld_name = 'GEO_LOC' and g.mod_name = 'X' "
	   					+" and s.geo_loc in(110,120,113,114,404,307,612,628) "
	   				    /*+" w.item_code = l.item_code "
	   					+" and w.lot_no = l.lot_no "
	   					+" and s.site_code = w.site_code "
	   					+" and case when l.STOP_BUSINESS is null then 'N' else l.STOP_BUSINESS  end= 'Y' "
	   					+" and l.stop_business_date between ? and ? " //dwh_production w,
*/	   					+" group by s.geo_loc,g.descr ";
	   			pstmt = conn.prepareStatement(sql);
	   		    //pstmt.setTimestamp(1,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
				//pstmt.setTimestamp(2,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
	
	   			rs = pstmt.executeQuery();
	   			int count = 0;			
	   			while (rs.next())
	   			{
	   				rowData = new JSONObject();
	   				rowData.put("geo_loc", checkNull(rs.getString("geodescr")));
	   				rowData.put("product_recall", rs.getString("product_recall"));
	   				
	   				rawDataPlantWiseRecallJson.put(count, rowData);
	   				count++;
	   			}
          }
   		 System.out.println("getPlantWiseRecallDetails data :"+rawDataPlantWiseRecallJson);
   		}
   		catch (Exception e)
   		{
   			e.printStackTrace();
   			throw new ITMException(e);
   		} finally
   		{
   			try
   			{
   				if (conn != null)
   				{
   					if (rs != null)
   					rs.close();
   					rs = null;
   					if (pstmt != null)
   					pstmt.close();
   					pstmt = null;
   					conn.close();
   					conn = null;
   				}
   				conn = null;
   			} catch (Exception d)
   			{
   				d.printStackTrace();
   				System.out.println("Exception in :RecallDetails :getPlantWiseRecallDetails:" + d.getMessage());
   				throw new ITMException(d);
   			}
   		}
   		return rawDataPlantWiseRecallJson;
   	}
    
    private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}
    
}