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 javax.ejb.Stateless;
import org.json.simple.JSONObject;
/**
 * Session Bean implementation class BSRTransferReport
 */
@Stateless
public class HalolMFGCommitVsActual extends ValidatorEJB implements HalolMFGCommitVsActualRemote, HalolMFGCommitVsActualLocal
{

String geoLocation="";	
public HalolMFGCommitVsActual()
{}
GenericUtility genericUtility = GenericUtility.getInstance();
@SuppressWarnings("unchecked")
public JSONObject getHalolMFGCommitVsActual(String dataSourceName, String month,String geoLoc) throws RemoteException, ITMException
{
	JSONObject rawDataMthlySalesJson = new JSONObject();
	JSONObject rowData = null;
	ResultSet rs = null;
	PreparedStatement pstmt =null;
	Connection conn = null;
	ConnDriver connDriver = new ConnDriver();
	String sql = "",finYear="";
	geoLocation =geoLoc;
	try
	{
		conn = connDriver.getConnectDB(dataSourceName);
		connDriver = null;				
		if(month!=null && month.trim().length()>0 && geoLoc!=null && geoLoc.trim().length()>0)
		{
	    	System.out.println("Comming  month inside getWeekWiseActualRelease ==="+month);					
			String selectedMonth  = month.substring(4, 6);				
			if(selectedMonth.trim().equalsIgnoreCase("01") ||selectedMonth.trim().equalsIgnoreCase("02") ||selectedMonth.trim().equalsIgnoreCase("03"))
			{
		    	finYear = String.valueOf(Integer.parseInt(month.substring(0, 4))-1)+"-"+month.substring(2, 4);
			}
			else
			{
				finYear = month.substring(0, 4)+"-"+ (String.valueOf(Integer.parseInt(month.substring(0, 4))+1)).substring(2, 4);
			}
			System.out.println("final parameter goes on  Sql for getHalolMFGCommitVsActual =="+month+"finYear =="+finYear);
		}
		/*sql =" SELECT C.week,C.TOT_BATCH AS TOT_PLANNED_BATCHES, "
				    +"   NVL(D.BATCH_RELEASED,0) AS ACTUAL_BATCHES_RELEASED,ABS((C.TOT_BATCH - D.BATCH_RELEASED)) AS PENDING_BATCHES, "
				    +"  E.TBD AS TBD_BATCHES,ROUND((ABS((D.BATCH_RELEASED))/(C.TOT_BATCH)* 100),2) AS PERCENT_ACHEIVED "
				    +" FROM (SELECT COUNT(context_1) AS TOT_BATCH, week "
				    +" FROM (SELECT  DISTINCT context_1,plan_end_date, TO_CHAR(plan_end_date,'WW') AS week FROM tran_track_status "
					   //  --(SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '201405') ACCTPRD 
				    +" WHERE plan_end_date >= SYSDATE-40  AND   plan_end_date <= SYSDATE AND REF_SER = 'QC-ORD' )  "
					+" GROUP BY week	ORDER BY week) C,(SELECT COUNT(context_1) AS BATCH_RELEASED, week "
					+" FROM (SELECT  DISTINCT context_1,plan_end_date, TO_CHAR(plan_end_date,'WW') AS week FROM  tran_track_status "
					   // -- (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '201405') ACCTPRD  
					+" WHERE plan_end_date >= SYSDATE-40  AND   plan_end_date <= SYSDATE AND   SEQ_NO = 2 AND   status_flag = 'Y' "
					+" AND REF_SER = 'QC-ORD' ) GROUP BY week ORDER BY week) D,(SELECT COUNT(context_1) AS TBD, week "
					+" 	FROM (SELECT  DISTINCT context_1,plan_end_date, TO_CHAR(plan_end_date,'WW') AS week FROM  tran_track_status "
					//     --(SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '201405') ACCTPRD 
					+" WHERE plan_end_date >= SYSDATE-40  AND   plan_end_date <= SYSDATE AND   udf_2 = 'TBD' "
					+" AND REF_SER = 'QC-ORD' ) GROUP BY week ORDER BY week) E WHERE C.week = D.week(+) AND   C.week = E.week ORDER BY C.week  "; */
		
	sql =" SELECT C.week,C.TOT_BATCH AS TOT_PLANNED_BATCHES, "
	    +"   NVL(D.BATCH_RELEASED,0) AS ACTUAL_BATCHES_RELEASED,ABS((C.TOT_BATCH - D.BATCH_RELEASED)) AS PENDING_BATCHES, "
	    +"  E.TBD AS TBD_BATCHES,ROUND((ABS((D.BATCH_RELEASED))/(C.TOT_BATCH)* 100),2) AS PERCENT_ACHEIVED "
	    +" FROM (SELECT COUNT(REF_ID) AS TOT_BATCH, week "
	    +" FROM (SELECT  DISTINCT REF_ID,plan_end_date, TO_CHAR(plan_end_date,'WW') AS week FROM tran_track_status, "
		+"  (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD "
	    +" WHERE plan_end_date >= ACCTPRD.FR_DATE  AND   plan_end_date <= ACCTPRD.TO_DATE  AND REF_SER = 'QC-ORD' AND CONTEXT_VALUE = '"+geoLoc+"' )  "
		+" GROUP BY week	ORDER BY week) C,(SELECT COUNT(REF_ID) AS BATCH_RELEASED, week "
		+" FROM (SELECT  DISTINCT REF_ID,plan_end_date, TO_CHAR(plan_end_date,'WW') AS week FROM  tran_track_status, "
		+"  (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD "
		+" WHERE plan_end_date >= ACCTPRD.FR_DATE  AND   plan_end_date <= ACCTPRD.TO_DATE  AND   SEQ_NO = 2 AND   status_flag = 'Y' AND CONTEXT_VALUE = '"+geoLoc+"' "
		+" AND REF_SER = 'QC-ORD' ) GROUP BY week ORDER BY week) D,(SELECT COUNT(REF_ID) AS TBD, week "
		+" 	FROM (SELECT  DISTINCT REF_ID,plan_end_date, TO_CHAR(plan_end_date,'WW') AS week FROM  tran_track_status, "
		+"  (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD "
		+" WHERE plan_end_date >= ACCTPRD.FR_DATE  AND   plan_end_date <= ACCTPRD.TO_DATE  AND   udf_2 = 'TBD' AND CONTEXT_VALUE = '"+geoLoc+"' " 
		+" AND REF_SER = 'QC-ORD' ) GROUP BY week ORDER BY week) E WHERE C.week = D.week AND   C.week = E.week ORDER BY C.week  "; 
		
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		int count = 0;		
		while (rs.next())
		{
			rowData = new JSONObject();
			rowData.put("week",                            "WW"+checkNull(rs.getString("week")));
			rowData.put("tot_planned_batches",             checkNull(rs.getString("TOT_PLANNED_BATCHES")));
			rowData.put("actual_batches_released",         checkNull(rs.getString("ACTUAL_BATCHES_RELEASED")));
			rowData.put("pending_batches",                 checkNull(rs.getString("PENDING_BATCHES")));
			rowData.put("percent_acheived",                 checkNull(rs.getString("PERCENT_ACHEIVED")));
			rawDataMthlySalesJson.put(count, rowData);
			count++;				
			
		}
	
		System.out.println("getHalolMFGCommitVsActual rawdata====" + rawDataMthlySalesJson.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 :HalolMFCommitVsActual:getHalolMFCommitVsActual():" + d.getMessage());
			throw new ITMException(d);
		}
	}
	return rawDataMthlySalesJson;
}
//Start this method used for month wise actual release  by Birendra Pandey on dated 12/02/2015	


	@SuppressWarnings("unchecked")
	public JSONObject getWeekWiseActualRelease (String dataSourceName,String month,String week,String geoLoc) throws RemoteException, ITMException
	{
		JSONObject rawDatagetWeekWiseActualRelease = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		String finYear=""; 
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();	
		geoLoc=geoLocation;
		week =week.substring(2, 4);
		try
		{
		    if(month!=null && month.trim().length()>0 && geoLoc!=null && geoLoc.trim().length()>0 )
				{
			    	conn = connDriver.getConnectDB(dataSourceName);
					connDriver = null;
					SimpleDateFormat gridDateFormat = new SimpleDateFormat( genericUtility.getApplDateFormat());
					SimpleDateFormat convrtToTimStmDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
					System.out.println("Comming  month inside getWeekWiseActualRelease1 ==="+month);					
					String selectedMonth  = month.substring(4, 6);				
					if(selectedMonth.trim().equalsIgnoreCase("01") ||selectedMonth.trim().equalsIgnoreCase("02") ||selectedMonth.trim().equalsIgnoreCase("03"))
					{
				    	finYear = String.valueOf(Integer.parseInt(month.substring(0, 4))-1)+"-"+month.substring(2, 4);
					}
					else
					{
						finYear = month.substring(0, 4)+"-"+ (String.valueOf(Integer.parseInt(month.substring(0, 4))+1)).substring(2, 4);
					}
					System.out.println("final parameter goes on  Sql for getWeekWiseActualRelease2 =="+month+"finYear =="+finYear);
				
				sql=" SELECT REF_ID as WORKORDER, "
					     +"   CONTEXT_VALUE  AS SITE_CODE, "
					     +"  ITEM_CODE, "
					     +" CONTEXT_1 AS BATCH_NO, "
					     +" CONTEXT_5 AS QUANTITY, "
					     +" RELEASE_DATE_DRJ, "
					     +" ENTER_PLAN_DATE_DRJ, "
					     +" QC_RELEASE_DATE_DRJ, "
					     +" DELAY_DAYS, "
					     +" TO_CHAR(PLAN_END_DATE,'WW') as WEEK, "
					     +" SCL_ROW "
					     +" FROM "
					     +" ( "
					     +" SELECT  A.CONTEXT_1, "
					     +"     A.REF_ID, "
					     +"     A.LINE_NO, "
					     +"     A.CONTEXT_VALUE, "
					     +"     A.ITEM_CODE, "
					     +"     A.CONTEXT_5, "
					     +"     A.PLAN_END_DATE, "
					     +"     A.COMPLETION_DATE, "
					     +"    (CASE WHEN A.LINE_NO = 1  THEN A.PLAN_END_DATE ELSE NULL END) AS RELEASE_DATE_DRJ, "
					     +"    (CASE WHEN A.LINE_NO = 1  THEN A.PLAN_END_DATE ELSE NULL END) AS ENTER_PLAN_DATE_DRJ, "
					     +"    (CASE WHEN B.LINE_NO = 2  THEN B.COMPLETION_DATE ELSE NULL END) AS QC_RELEASE_DATE_DRJ, "
					     +"     A.DELAY_DAYS, "
					     +"    (case when a.line_no = 1 then '*' else '' end) as SCL_ROW " 
					     +" FROM  "
					     +" (select  STS.CONTEXT_1, "
					     +"     STS.REF_ID, "
					     +"     STS.LINE_NO, "
					     +"     STS.CONTEXT_VALUE, "
					     +"     (TRIM(ITEM.ITEM_CODE)|| ' - ' ||ITEM.DESCR) AS ITEM_CODE, "
					     +"     STS.CONTEXT_5, "
					     +"     STS.PLAN_END_DATE, "
					     +"     STS.COMPLETION_DATE, "
					     +"     STS.DELAY_DAYS "
					     +" from  tran_track_status STS, "
					     +"   (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD, "
					     +"   ITEM    "
					     +" where (STS.plan_end_date >= ACCTPRD.FR_DATE AND   STS.plan_end_date <= ACCTPRD.TO_DATE) " 
					     +" AND    STS.ITEM_CODE = ITEM.ITEM_CODE(+) "
					     +" AND    STS.REF_SER = 'QC-ORD' AND STS.CONTEXT_VALUE = '"+geoLoc+"' "
						//--and  trim(STS.context_1) = 'JKN3219' "
						+" ) A, "
						+" (select STS.CONTEXT_1, "
						+"      STS.REF_ID, "
						+"      STS.LINE_NO, "
						+"      STS.CONTEXT_VALUE, "
						+"      STS.ITEM_CODE, "
						+"      STS.CONTEXT_5, "
						+"      STS.PLAN_END_DATE, "
						+"      STS.COMPLETION_DATE "
						+" from tran_track_status STS, "
						+"   (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD, "
						+"   ITEM "
						+" where (STS.plan_end_date >= ACCTPRD.FR_DATE AND   STS.plan_end_date <= ACCTPRD.TO_DATE) " 
						+" AND   STS.ITEM_CODE = ITEM.ITEM_CODE(+) "
						+" and   STS.SEQ_NO = 2  "
						+" and   STS.status_flag = 'Y' " 
						+" AND   STS.REF_SER = 'QC-ORD' AND STS.CONTEXT_VALUE = '"+geoLoc+"'"
						+" ) B "
						+" WHERE A.REF_ID = B.REF_ID "
						+" ) "
						+" WHERE SCL_ROW = '*' "
						+" AND TO_CHAR(PLAN_END_DATE,'WW') ='"+week+"' "
						//+" AND CONTEXT_VALUE = 'SP105 '"
						+" ORDER BY WEEK ASC";				
				    pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					int count = 0;
								
					while (rs.next())
						{
							rowData = new JSONObject();								
							rowData.put("workorder",    checkNull(rs.getString("WORKORDER")));								
							rowData.put("site_code",    checkNull(rs.getString("SITE_CODE")));
							rowData.put("item_code",    checkNull(rs.getString("ITEM_CODE")));
							rowData.put("batch_no",     checkNull(rs.getString("BATCH_NO")));
							rowData.put("quantity",     rs.getString("QUANTITY"));							
							
							String rlseDate   =checkNull(rs.getString("RELEASE_DATE_DRJ"));
							if(rlseDate.trim().length()>0)
							{
								rowData.put("release_date", gridDateFormat.format(convrtToTimStmDate.parse(rlseDate)));
							}
							else
							{
								rowData.put("release_date",checkNull (rs.getString("RELEASE_DATE_DRJ")));
							}	
							String enterPlanDate   =checkNull(rs.getString("ENTER_PLAN_DATE_DRJ"));
							if(enterPlanDate.trim().length()>0)
							{
								rowData.put("enter_plan_date", gridDateFormat.format(convrtToTimStmDate.parse(enterPlanDate)));
							}
							else
							{
								rowData.put("enter_plan_date",checkNull (rs.getString("ENTER_PLAN_DATE_DRJ")));
							}
							String qcRelDate   =checkNull(rs.getString("QC_RELEASE_DATE_DRJ"));
							if(qcRelDate.trim().length()>0)
							{
								rowData.put("qc_rel_date", gridDateFormat.format(convrtToTimStmDate.parse(qcRelDate)));
							}
							else
							{
								rowData.put("qc_rel_date",checkNull (rs.getString("QC_RELEASE_DATE_DRJ")));
							}
                            rowData.put("delay_days", checkNull(rs.getString("Delay_Days")));	                         
                            rawDatagetWeekWiseActualRelease.put(count, rowData);
							count++;
						}
				}
					System.out.println("final rawjson data inside HalolMFGCommitVsActual-getWeekWiseActualRelease====" + rawDatagetWeekWiseActualRelease.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 :getHalolMFGCommitVsActual-getWeekWiseActualRelease:" + d.getMessage());
						throw new ITMException(d);
					}
			}
			return rawDatagetWeekWiseActualRelease;
	}
// End this method used for month wise actual release  by Birendra Pandey on dated 12/02/2015	
//Start this method used for Month wise Pending release  by Birendra Pandey on dated 12/02/2015	
	
	
	@SuppressWarnings("unchecked")
	public JSONObject getWeekWiseCommitedRelease (String dataSourceName,String month,String week,String geoLoc) throws RemoteException, ITMException
	{
		JSONObject rawDataWeekWiseCommitedRelease = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		String finYear=""; 
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();	
		geoLoc=geoLocation;
		week  =week.substring(2, 4);
		try
		{
		    if(month!=null && month.trim().length()>0 && geoLoc!=null && geoLoc.trim().length()>0)
				{
			    	conn = connDriver.getConnectDB(dataSourceName);
					connDriver = null;	
					SimpleDateFormat gridDateFormat = new SimpleDateFormat( genericUtility.getApplDateFormat());
					SimpleDateFormat convrtToTimStmDate= new SimpleDateFormat(genericUtility.getDBDateFormat());
				   System.out.println("Comming  month inside getWeekWiseCommitedRelease ==="+month);					
				   String selectedMonth  = month.substring(4, 6);				
					if(selectedMonth.trim().equalsIgnoreCase("01") ||selectedMonth.trim().equalsIgnoreCase("02") ||selectedMonth.trim().equalsIgnoreCase("03"))
					{
				    	finYear = String.valueOf(Integer.parseInt(month.substring(0, 4))-1)+"-"+month.substring(2, 4);
					}
					else
					{
						finYear = month.substring(0, 4)+"-"+ (String.valueOf(Integer.parseInt(month.substring(0, 4))+1)).substring(2, 4);
					}
					System.out.println("final parameter goes on  Sql for getWeekWiseCommitedRelease =="+month+"finYear =="+finYear);
				
				sql = " SELECT C.WORKORDER, "
					    +" C.SITE_CODE, "
					    +" C.ITEM_CODE, "
					    +" C.BATCH_NO, "
					    +" C.QUANTITY, "
					    +" C.RELEASE_DATE_DRJ, "
					    +" TO_CHAR(C.ENTER_PLAN_DATE_DRJ) AS ENTER_PLAN_DATE_DRJ, "
					    +" TO_CHAR(C.QC_RELEASE_DATE_DRJ) AS QC_RELEASE_DATE_DRJ , "
					    +" C.DELAY_DAYS, "
					    +" TO_CHAR(C.PLAN_END_DATE,'WW') as WEEK "
					    +" FROM "
					    +" ( "
					    +" SELECT REF_ID as WORKORDER, "
					    +" CONTEXT_VALUE  AS SITE_CODE, "
					    +" ITEM_CODE, "
					    +" CONTEXT_1 AS BATCH_NO, "
					    +" CONTEXT_5 AS QUANTITY, "
					    +" RELEASE_DATE_DRJ, "
					    +" ENTER_PLAN_DATE_DRJ, "
					    +" QC_RELEASE_DATE_DRJ, "
					    +" PLAN_END_DATE, "
					    +" DELAY_DAYS, "
					    +" SCL_ROW "
					    +" FROM "
					    +" ( "
					    +" SELECT  A.CONTEXT_1, "
					    +"  A.REF_ID, "
					    +"  A.LINE_NO, "
					    +"  A.CONTEXT_VALUE, "
					    +"  A.ITEM_CODE, "
					    +"  A.CONTEXT_5, "
					    +"  A.PLAN_END_DATE, "
					    +"  A.COMPLETION_DATE, "
					    +" (CASE WHEN A.LINE_NO = 1  THEN A.PLAN_END_DATE ELSE NULL END) AS RELEASE_DATE_DRJ, "
					    +" (CASE WHEN A.LINE_NO = 1  THEN A.PLAN_END_DATE ELSE NULL END) AS ENTER_PLAN_DATE_DRJ, "
					    +" (CASE WHEN B.LINE_NO = 1  THEN B.COMPLETION_DATE ELSE NULL END) AS QC_RELEASE_DATE_DRJ, "
					    +"  A.DELAY_DAYS, "
					    +" (case when a.line_no = 1 then '*' else '' end) as SCL_ROW " 
					    +" FROM  "
					    +" (select STS.CONTEXT_1, "
					    +"  STS.REF_ID, "
					    +"  STS.LINE_NO, "
					    +"  STS.CONTEXT_VALUE, "
					    +"  (TRIM(ITEM.ITEM_CODE)|| ' - ' ||ITEM.DESCR) AS ITEM_CODE, "
					    +"  STS.CONTEXT_5, "
					    +"  STS.PLAN_END_DATE, "
					    +"  STS.COMPLETION_DATE, "
					    +"  STS.DELAY_DAYS "
					    +" from  tran_track_status STS, "
					    +" (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD, "
					    +" ITEM    "
					    +" where (STS.plan_end_date >= ACCTPRD.FR_DATE AND   STS.plan_end_date <= ACCTPRD.TO_DATE) " 
					    +" AND    STS.ITEM_CODE = ITEM.ITEM_CODE(+) "
					    +" AND    STS.REF_SER = 'QC-ORD' AND STS.CONTEXT_VALUE = '"+geoLoc+"' " 
					//--and  trim(STS.context_1) = 'JKN3219'
					    +" ) A, "
					+" (select STS.CONTEXT_1, "
					+" STS.REF_ID, "
					+"      STS.LINE_NO, "
					+"      STS.CONTEXT_VALUE, "
					+"      STS.ITEM_CODE, "
					+"      STS.CONTEXT_5, "
					+"      STS.PLAN_END_DATE, "
					+"      STS.COMPLETION_DATE "
					+" from tran_track_status STS, "
					+"   (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD, "
					+"   ITEM "
					+" where (STS.plan_end_date >= ACCTPRD.FR_DATE AND   STS.plan_end_date <= ACCTPRD.TO_DATE) " 
					+" AND   STS.ITEM_CODE = ITEM.ITEM_CODE(+) "
					+" and   STS.SEQ_NO = 2  "
					+" and   STS.status_flag = 'Y' " 
					+" AND   STS.REF_SER = 'QC-ORD' AND STS.CONTEXT_VALUE = '"+geoLoc+"' "
					+" ) B "
					+" WHERE A.REF_ID = B.REF_ID "
					+" ) "
					+" WHERE SCL_ROW = '*' "
					+" )C "
					+" WHERE TO_CHAR(C.PLAN_END_DATE,'WW') = '"+week+"' "
					+" UNION "
					+" SELECT D.WORKORDER, "
					+"     D.SITE_CODE, "
					+"     D.ITEM_CODE, "
					+"     D.BATCH_NO, "
					+"     D.QUANTITY, "
					+"     D.RELEASE_DATE_DRJ, "
					+"     D.ENTER_PLAN_DATE_DRJ, "
					+"     D.QC_RELEASE_DATE_DRJ, "
					+"     D.DELAY_DAYS, "
					+"     TO_CHAR(D.PLAN_END_DATE,'WW') as WEEK "
					+" FROM "
					+" ( "
					+" SELECT REF_ID as WORKORDER, "
					+"     CONTEXT_VALUE  AS SITE_CODE, "
					+"     ITEM_CODE, "
					+"     CONTEXT_1 AS  BATCH_NO, "
					+"     CONTEXT_5 AS QUANTITY, "
					+"     RELEASE_DATE_DRJ, "
					+"     NVL(TO_CHAR(ENTER_PLAN_DATE_DRJ),'TBD') AS ENTER_PLAN_DATE_DRJ, "
					+"     NVL(TO_CHAR(QC_RELEASE_DATE_DRJ),'TBD') AS QC_RELEASE_DATE_DRJ, "
					+"     PLAN_END_DATE, "
					+"     DELAY_DAYS, "
					+"     SCL_ROW "
					+" FROM "
					+" ( "
					+" SELECT  A.CONTEXT_1, "
					+"      A.REF_ID, "
					+"      A.LINE_NO, "
					+"      A.CONTEXT_VALUE, "
					+"      A.ITEM_CODE, "
					+"      A.CONTEXT_5, "
					+"      A.PLAN_END_DATE, "
					+"      A.COMPLETION_DATE, "
					+"     (CASE WHEN A.LINE_NO = 1  THEN A.PLAN_END_DATE ELSE NULL END) AS RELEASE_DATE_DRJ, "
					+"     (CASE WHEN A.LINE_NO = 1  THEN A.COMPLETION_DATE  ELSE NULL END) AS ENTER_PLAN_DATE_DRJ, "
					+"     (CASE WHEN B.LINE_NO = 2  THEN B.COMPLETION_DATE  ELSE NULL END) AS QC_RELEASE_DATE_DRJ, "
					+"      A.DELAY_DAYS, "
					+"     (case when a.line_no = 2 then '*' else '' end) as SCL_ROW " 
					+" FROM  "
					+" (select STS.CONTEXT_1, "
					+"      STS.REF_ID, "
					+"      STS.LINE_NO, "
					+"      STS.CONTEXT_VALUE, "
					+"      (TRIM(ITEM.ITEM_CODE)|| ' - ' ||ITEM.DESCR) AS ITEM_CODE, "
					+"      STS.CONTEXT_5, "
					+"      STS.PLAN_END_DATE, "
					+"      STS.COMPLETION_DATE, "
					+"      STS.DELAY_DAYS "
					+" from  tran_track_status STS, "
					+"    (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD, "
					+"    ITEM    "
					+" where (STS.plan_end_date >= ACCTPRD.FR_DATE AND   STS.plan_end_date <= ACCTPRD.TO_DATE) " 
					+" AND    STS.ITEM_CODE = ITEM.ITEM_CODE(+) "
					+" AND    STS.REF_SER = 'QC-ORD' AND STS.CONTEXT_VALUE = '"+geoLoc+"' "
					//--and  trim(STS.context_1) = 'JKN3219'
					+" ) A, "
					+" (select STS.CONTEXT_1, "
					+"      STS.REF_ID, "
					+"      STS.LINE_NO, "
					+"      STS.CONTEXT_VALUE, "
					+"      STS.ITEM_CODE, "
					+"      STS.CONTEXT_5, "
					+"      STS.PLAN_END_DATE, "
					+"      STS.COMPLETION_DATE "
					+" from tran_track_status STS, "
					+"   (SELECT FR_DATE, TO_DATE,CODE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD, "
					+"   ITEM "
					+" where (STS.plan_end_date >= ACCTPRD.FR_DATE AND   STS.plan_end_date <= ACCTPRD.TO_DATE) " 
					+" AND   STS.ITEM_CODE = ITEM.ITEM_CODE(+) "
					+" and   STS.SEQ_NO = 2  "
					+" and   STS.status_flag = 'N' " 
					+" AND   STS.REF_SER = 'QC-ORD' AND STS.CONTEXT_VALUE = '"+geoLoc+"' "				
					+" ) B "
					+" WHERE A.REF_ID = B.REF_ID "
					+" ) "
					+" WHERE SCL_ROW = '*' "
					+" ) D  WHERE TO_CHAR(D.PLAN_END_DATE,'WW') = '"+week+"' " 
					+" ORDER BY WEEK ASC, BATCH_NO ";	
					
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					int count = 0;								
					while (rs.next())
						{
							rowData = new JSONObject();								
							rowData.put("workorder",    checkNull(rs.getString("WORKORDER")));								
							rowData.put("site_code",    checkNull(rs.getString("SITE_CODE")));
							rowData.put("item_code",    checkNull(rs.getString("ITEM_CODE")));
							rowData.put("batch_no",     checkNull(rs.getString("BATCH_NO")));
							rowData.put("quantity",     rs.getString("QUANTITY"));
							
							String rlseDate   =checkNull(rs.getString("RELEASE_DATE_DRJ"));
							if(rlseDate.trim().length()>0)
							{
								rowData.put("release_date", gridDateFormat.format(convrtToTimStmDate.parse(rlseDate)));
							}
							else
							{
								rowData.put("release_date",checkNull (rs.getString("RELEASE_DATE_DRJ")));
							}	
							
							rowData.put("enter_plan_date",checkNull (rs.getString("ENTER_PLAN_DATE_DRJ")));							
							
							rowData.put("qc_rel_date",checkNull (rs.getString("QC_RELEASE_DATE_DRJ")));
							
                            rowData.put("delay_days",    checkNull(rs.getString("Delay_Days")));	
                            rawDataWeekWiseCommitedRelease.put(count, rowData);
							count++;							
							
						}
				}
					System.out.println("final rawjson data inside getHalolMFGCommitVsActual-getWeekWiseCommitedRelease====" + rawDataWeekWiseCommitedRelease.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 :getHalolMFGCommitVsActual-getWeekWiseCommitedRelease:" + d.getMessage());
						throw new ITMException(d);
					}
			}
			return rawDataWeekWiseCommitedRelease;
	}
	
	
// End this method used for Month wise Pending release  by Birendra Pandey on dated 12/02/2015	


private String checkNull(String str)
{
	if(str == null)
	{
		return "";
	}
	else
	{
		return str ;
	}

}

}