package ibase.dashboard.mfg.ejb;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.ejb.sys.UtilMethods;
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 ibase.webitm.utility.GenericUtility;

import javax.ejb.Stateless;

import org.json.simple.JSONObject;

/**
 * Session Bean implementation class SalesPersonSales
 */
@Stateless
public class SampleRCpDtl extends ValidatorEJB implements SampleRCpDtlRemote, SampleRCpDtlLocal
{
	/**
	 * Default constructor.
	 */
	public SampleRCpDtl()
	{

	}

	/***
	 * End of method getProfitGraphData()
	 * 
	 * @author base
	 * @param metedata
	 *            as xml file by client side
	 * @return JSONObject
	 * @throws RemoteException
	 *             , ITMException @ *
	 ***/


	@SuppressWarnings("unchecked")
	public JSONObject getSampleRCPDtlData(String dataSourceName, String lastMonth) throws RemoteException, ITMException
	{

		JSONObject smplRcpDateWiseJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		SimpleDateFormat simpleDateFormat = null;
		Calendar calendar = null;
		UtilMethods utilMethods = new UtilMethods();
		int daysInInt=0;
		GenericUtility genericUtility = GenericUtility.getInstance();
		Date frmDate = null;
		Date toDate = null;
		try
		{
			SimpleDateFormat convrtToTimStmDate = new SimpleDateFormat(genericUtility.getDBDateFormat());
			SimpleDateFormat graphDateFormat =  new SimpleDateFormat("dd-MMM-yy");	
			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));
			}
			
			System.out.println("From date is =" + frmDate + "and  to date is =" + toDate+ "and  to lastMonth  is ="+lastMonth);
			if (frmDate != null && toDate != null)
			{
				conn = connDriver.getConnectDB(dataSourceName);
				connDriver = null;

				sql = "select facility_code, curr_date,sum(day_received) as day_received, sum(day_approved) day_approved, sum (day_pending) day_pending from ( select s.facility_code, q.QORDER_DATE as curr_date,count(1) as day_received," + "0 as day_pending, 0 as day_approved from qc_order q, site s Where S.Site_Code = Q.Site_Code " +
				" and q.QORDER_DATE between ? and ?"+
				" group by s.facility_code, q.QORDER_DATE union all select s.facility_code, " + "q.QORDER_DATE, 0 day_received, count(1) day_pending, 0 day_approved from qc_order q, site s where s.site_code = q.site_code and case when q.status is null then 'U' else q.status end = 'U' " +
				" and q.QORDER_DATE between ? and ?"+
			    " group by s.facility_code, q.QORDER_DATE union all select s.facility_code, q.status_DATE,0 day_received, 0 day_pending, count(1) day_approved from qc_order q, " + "site s where s.site_code = q.site_code and case when q.status is null then 'U' else q.status end = 'A' " +
				" and q.QORDER_DATE between  ? and ?"+
				" group by s.facility_code, q.status_DATE ) Group By Facility_Code, Curr_Date";

				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"));
				pstmt.setTimestamp(3,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
				pstmt.setTimestamp(4,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(toDate).toString() + " 00:00:00.0"));
				pstmt.setTimestamp(5,java.sql.Timestamp.valueOf(convrtToTimStmDate.format(frmDate).toString() + " 00:00:00.0"));
				pstmt.setTimestamp(6,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("curr_date", String.valueOf(graphDateFormat.format(rs.getDate("CURR_DATE"))).substring(0, 6));
					rowData.put("day_received", checkNull(rs.getString("DAY_RECEIVED")));
					rowData.put("day_approved", rs.getString("DAY_APPROVED"));
					rowData.put("day_pending", rs.getString("DAY_PENDING"));
					smplRcpDateWiseJson.put(count, rowData);
					count++;
				}
			}
			System.out.println("json object for sample rcp data====" + smplRcpDateWiseJson.toString());

		} 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 :SampleRCpDtl: getSampleRCPDtlData(String dataSourceName, String days) :" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return smplRcpDateWiseJson;
	}
	private String checkNull(String str)
	{
		if (str == null)
		{
			return "";
		} else
		{
			return str;
		}

	}

}
