package ibase.dashboard.mfg.ejb;

import ibase.dashboard.Report.Utility.DashboardUtility;
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 SalesPersonSales
 */
@Stateless
public class QCSampleCost extends ValidatorEJB implements QCSampleCostLocal, QCSampleCostRemote
{
	/**
	 * Default constructor.
	 */
	public QCSampleCost()
	{
	}
	
	@SuppressWarnings("unchecked")
    public JSONObject getQCSampleCostData(String dataSourceName, String geoLocation, String lastMonth) throws RemoteException, ITMException
	{
		JSONObject qcSampleCostDataJson = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();
		GenericUtility genericUtility = GenericUtility.getInstance();
		Date frmDate = null;
		Date toDate = null;
		DashboardUtility dashbordUtil = null;
		try
		{
			/*String[] geoLocArray = this.getSplitedStrng(geoLocation);
			if (geoLocArray != null)
			{
				for (int i = 0; i < geoLocArray.length; i++)
				{
					if (i == geoLocArray.length - 1)
					{
						modifyGeoLoc = modifyGeoLoc + "'" + geoLocArray[i] + "'";
					} else
					{
						modifyGeoLoc = modifyGeoLoc + "'" + geoLocArray[i] + "',";
					}
				}
			} else
			{
				modifyGeoLoc="";
			}*/
			
			/*monthInInt=Integer.parseInt(lastMonth);
			calendar = Calendar.getInstance();
			simpleDateFormat = new SimpleDateFormat("dd-MMM-yy");
			String toDate = simpleDateFormat.format(calendar.getTime());
			//calendar.add(Calendar.MONTH,-monthInInt);
			calendar.add(Calendar.MONTH,-6);
			String fromDate = simpleDateFormat.format(calendar.getTime());*/
			
			SimpleDateFormat convrtToTimStmDate = new SimpleDateFormat(genericUtility.getDBDateFormat());
			dashbordUtil = new DashboardUtility();
			geoLocation           = dashbordUtil.getCommaSeparated(geoLocation);
			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 geolocation is ="+geoLocation+" and lastmonth is ="+lastMonth);
		   if (frmDate != null && toDate != null)
			{
				conn = connDriver.getConnectDB(dataSourceName);
				connDriver = null;
				sql = " select to_char(tran_date,'MON-YY') as qc_date , to_date('01-' || to_char(tran_date,'MON-YY')),sum(i.quantity *  s.rate) as sample_cost" +
				" from invtrace i, stock s, site o where o.site_code = i.site_code and s.item_code = i.item_code and s.site_code = i.site_code" + 
				" and s.loc_code = i.loc_code and s.lot_no = i.lot_no and s.lot_sl = i.lot_sl and i.ref_ser in ('S-ISS','W-QCS') And I.Eff_Qty < 0" + " " +
				//" and o.geo_loc  in ("+ modifyGeoLoc +")" + 
				" and tran_date between ? and  ?" +
				" group by to_char(tran_date,'MON-YY') " + " Order By To_Date('01-' || To_Char(Tran_Date,'MON-YY'))";

				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("qc_date", checkNull(rs.getString("qc_date")));
					rowData.put("sample_cost", checkNull(rs.getString("sample_cost")));
					qcSampleCostDataJson.put(count, rowData);
					count++;
				}
			}
			System.out.println("json object for QCSampleCost data====" + qcSampleCostDataJson.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 :QCSampleCost: getQCSampleCostData(String dataSourceName, String geoLocation) throws RemoteException, ITMException:" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return qcSampleCostDataJson;
	}
	
	/*private String[] getSplitedStrng(String year)
	{
		String[] splitedArray = null;
		if (year != null && year.length() > 0)
		{
			splitedArray = year.split(",");
		}
		return splitedArray;
	}*/
	
	private String checkNull(String str)
	{
		if (str == null)
		{
			return "";
		} else
		{
			return str;
		}
	}
}
