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 javax.ejb.Stateless;
import org.json.simple.JSONObject;
/**
 * Session Bean implementation class SalesPersonSales
 */
@Stateless
public class HalolMFGRelease extends ValidatorEJB implements HalolMFGReleaseRemote, HalolMFGReleaseLocal
{
	
	String geoLocation="";
	public HalolMFGRelease()
	{}
	GenericUtility genericUtility = GenericUtility.getInstance();
	String firstWeek="";
	//1.This is the main method to show the parent graph 
	@SuppressWarnings("unchecked")		
	public JSONObject getHalolMFGReleaseBatches(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_forprd = "";
		String sql_forforcastweek="";
		String OverDueSql="";
		String sql_fortbdstack="";
		geoLocation=geoLoc;
		int overDue=0;
		
		try
		{
		   if(month!=null && month.trim().length()>0 &&   geoLoc!=null && geoLoc.trim().length()>0)
			{
		    	conn = connDriver.getConnectDB(dataSourceName);
				connDriver = null;
				SimpleDateFormat graphDateFormat =  new SimpleDateFormat("dd-MMM-yy");	
				/* added by swati for previous month calculation 13 feb 2015 */
				 String selectedYear  = month.substring(0, 4);	
				 String selectedMonth  = month.substring(4, 6);	
				 Calendar calendar = Calendar.getInstance();
				 calendar.set(Calendar.YEAR, Integer.parseInt(selectedYear));
				 calendar.add(Calendar.YEAR,-1);
				 String preMonth =""+ ( calendar.get(Calendar.YEAR)+""+selectedMonth);
				 System.out.println("month and prev month in getHalolMFGReleaseBatches ==="+month +"---"+preMonth);			
				 preMonth=month;
				 String curntYear=""+Calendar.getInstance().get(Calendar.YEAR)+""+((Calendar.MONTH)+1);
				 month=curntYear; 
				
      sql_forprd  = " SELECT C.BATCHES_DISPATCHED AS ACTUAL_BATCHES_RELEASED, D.PENDING_BATCHES AS PENDING_BATCHES, E.TBD_BATCHES AS TBD_BATCHES, C.CODE FROM (SELECT COUNT(BATCH_NO) AS BATCHES_DISPATCHED, CODE FROM MFG_RELEASE3,(SELECT CODE,FR_DATE,TO_DATE FROM PERIOD WHERE CODE BETWEEN '"+preMonth+"' AND '"+month+"') ACCTPRD WHERE release_date >= ACCTPRD.FR_DATE AND release_date <= ACCTPRD.TO_DATE AND site_code = '"+geoLoc+"' "
				   +" AND rel_status = 'Released' GROUP BY CODE) C, (SELECT COUNT(BATCH_NO) AS PENDING_BATCHES, CODE FROM MFG_RELEASE3,(SELECT CODE,FR_DATE,TO_DATE FROM PERIOD WHERE CODE BETWEEN '"+preMonth+"' AND '"+month+"') ACCTPRD WHERE release_date >= ACCTPRD.FR_DATE AND release_date <= ACCTPRD.TO_DATE AND site_code = '"+geoLoc+"' AND rel_status = 'Not Released' GROUP BY CODE) D , "
				   +" (SELECT COUNT(BATCH_NO) AS TBD_BATCHES, CODE FROM MFG_RELEASE3,(SELECT CODE,FR_DATE,TO_DATE FROM PERIOD WHERE CODE BETWEEN '"+preMonth+"' AND '"+month+"') ACCTPRD WHERE release_date >= ACCTPRD.FR_DATE AND release_date <= ACCTPRD.TO_DATE AND site_code = '"+geoLoc+"' AND BATCH_NO IS NOT NULL AND REL_STATUS = 'In METIS Not in LIMS'  GROUP BY CODE) E"
    		       +" WHERE C.CODE = D.CODE(+) AND C.CODE = E.CODE(+)  ORDER BY C.CODE " ;
		
			pstmt = conn.prepareStatement(sql_forprd);
			rs = pstmt.executeQuery();
			int count = 0;		
			while (rs.next())
			{
				rowData = new JSONObject();
				rowData.put("prd_code",                convertPrdToMonth(rs.getString("CODE")));
				rowData.put("tbd_batches",             rs.getString("TBD_BATCHES"));
				rowData.put("actual_batches_released", rs.getString("ACTUAL_BATCHES_RELEASED"));
				rowData.put("pending_batches",         rs.getString("PENDING_BATCHES"));							
				rawDataMthlySalesJson.put(count, rowData);
				count++;		
				
			}
			pstmt.close();
			pstmt=null;
			rs.close();
			rs=null;
			//end of prd code sql			
			//start of week implementation sql				
	 /* sql_forweek1 =" SELECT C.CODE, C.week, C.TOT_BATCH as TOT_PLANNED_BATCHES,D.BATCH_RELEASED AS ACTUAL_BATCHES_RELEASED, "
					 +" ABS((C.TOT_BATCH - D.BATCH_RELEASED)) AS PENDING_BATCHES,E.TBD AS TBD_BATCHES FROM  "
					+" (SELECT COUNT(context_1) AS TOT_BATCH, week,CODE FROM ( "
					+" select  DISTINCT context_1,plan_end_date, TO_CHAR(plan_end_date,'W') as week,CODE "
					+" 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' "
					+"  ) GROUP BY week, CODE order by week) C,(SELECT COUNT(context_1) AS BATCH_RELEASED, week,CODE "
					+" FROM(select  DISTINCT context_1,plan_end_date, TO_CHAR(plan_end_date,'W') as week,CODE "
					+" 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 REF_SER = 'QC-ORD' )  "
					+" GROUP BY week,CODE	order by week) D,(SELECT COUNT(context_1) AS TBD, week,CODE "
					+" FROM ( select  DISTINCT context_1,plan_end_date, TO_CHAR(plan_end_date,'W') as week,CODE "
					+" 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 REF_SER = 'QC-ORD' ) GROUP BY week,CODE order by week) E "
					+" WHERE C.week = D.week AND   C.week = E.week ORDER BY C.week ";*/
							
	  
		/*	sql_forcommonweeks =" SELECT C.CODE, C.week, C.TOT_BATCH as TOT_PLANNED_BATCHES,D.BATCH_RELEASED AS ACTUAL_BATCHES_RELEASED, "
								 +" E.PENDING_BATCHES AS PENDING_BATCHES FROM  "
								+" (SELECT COUNT(REF_ID) AS TOT_BATCH, week,CODE FROM ( "
								+" select  DISTINCT REF_ID,plan_end_date, TO_CHAR(plan_end_date,'WW') as week,CODE "
								+" 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, CODE order by week) C,(SELECT COUNT(REF_ID) AS BATCH_RELEASED, week,CODE "
								+" FROM(select  DISTINCT REF_ID,plan_end_date, TO_CHAR(plan_end_date,'WW') as week,CODE "
								+" 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 REF_SER = 'QC-ORD'  AND CONTEXT_VALUE = '"+geoLoc+"' )  "
								+" GROUP BY week,CODE	order by week) D,(SELECT COUNT(REF_ID) AS PENDING_BATCHES, week,CODE "
								+" FROM ( select  DISTINCT REF_ID,plan_end_date, TO_CHAR(plan_end_date,'WW') as week,CODE "
								+" 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 = 'N' and REF_SER = 'QC-ORD'  AND CONTEXT_VALUE = '"+geoLoc+"' ) GROUP BY week,CODE order by week) E "
								+" WHERE C.week = D.week AND   C.week = E.week ORDER BY C.week ";
								
			*/
			
			
			
		/*	sql_forcommonweeks = " SELECT C.BATCHES_DISPATCHED AS ACTUAL_BATCHES_RELEASED, D.PENDING_BATCHES AS PENDING_BATCHES,C.WEEK AS WEEK FROM (SELECT COUNT(WORKORDER) AS BATCHES_DISPATCHED, CODE,WEEK FROM MFG_RELEASE3,(SELECT CODE,FR_DATE,TO_DATE FROM PERIOD WHERE CODE BETWEEN '"+preMonth+"' AND '"+month+"') ACCTPRD WHERE ENTER_PLAN_DATE >= ACCTPRD.FR_DATE AND ENTER_PLAN_DATE <= ACCTPRD.TO_DATE AND site_code = '"+geoLoc+"' "
			   +" AND rel_status = 'Released' GROUP BY CODE, WEEK) C, (SELECT COUNT(WORKORDER) AS PENDING_BATCHES, CODE, WEEK FROM MFG_RELEASE3,(SELECT CODE,FR_DATE,TO_DATE FROM PERIOD WHERE CODE BETWEEN '"+preMonth+"' AND '"+month+"') ACCTPRD WHERE ENTER_PLAN_DATE >= ACCTPRD.FR_DATE AND ENTER_PLAN_DATE <= ACCTPRD.TO_DATE AND site_code = '"+geoLoc+"' AND rel_status = 'Not Released' GROUP BY CODE, WEEK) D"
		       +" WHERE C.CODE = D.CODE  ORDER BY C.CODE,C.WEEK " ;

	   	    	  
	  
			//TABLE(CAST(DATETYPETBL('201502')AS DATETYPESET )) DATETYPESET
			
			pstmt = conn.prepareStatement(sql_forcommonweeks);
			rs= pstmt.executeQuery();			
			while ( rs.next())
			{    
				rowData = new JSONObject();
				rowData.put("prd_code",               "WW"+checkNull(rs.getString("week")));
				rowData.put("tot_planned_batches",   " " );
				rowData.put("actual_batches_released", rs.getString("ACTUAL_BATCHES_RELEASED"));
				rowData.put("pending_batches",         rs.getString("PENDING_BATCHES"));							
				rawDataMthlySalesJson.put(count, rowData);
				count++;				
				System.out.println(" rowData-sql_forweek1====" + rowData.toString());
			}
			pstmt.close();
			pstmt=null;
			rs.close();
			rs=null;
			
			*/
			
	//Over Due start for graph	
		
	 OverDueSql  = " SELECT COUNT(BATCH_NO) AS OVERDUE_BATCHES  FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE < SYSDATE AND rel_status = 'Not Released' ";
	 pstmt = conn.prepareStatement(OverDueSql);
		rs= pstmt.executeQuery();			
		if ( rs.next())
		{    
			
			overDue=Integer.parseInt(rs.getString("OVERDUE_BATCHES"));			
			System.out.println("overDue is " + overDue);
		}
		pstmt.close();
		pstmt=null;
		rs.close();
		rs=null;		
	//End of Over Due of Graph			
	sql_forforcastweek  = " SELECT COUNT(BATCH_NO) AS PENDING_BATCHES , WEEK_NO_OF_THE_YEAR FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE >= SYSDATE AND rel_status = 'Not Released' GROUP BY WEEK_NO_OF_THE_YEAR"
			               +" UNION "
                           +" SELECT COUNT(BATCH_NO) AS PENDING_BATCHES, WEEK_NO_OF_THE_YEAR FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE >= SYSDATE AND rel_status = 'In LIMS Not in Metis' GROUP BY WEEK_NO_OF_THE_YEAR  ORDER BY WEEK_NO_OF_THE_YEAR ";
	
	
			pstmt = conn.prepareStatement(sql_forforcastweek);
			rs= pstmt.executeQuery();
			int   cnt=0;
			int   pendingBatches= 0;
			while ( rs.next())
			{    
				cnt++;
				rowData = new JSONObject();				
				if(cnt==1)
				{
					firstWeek   = "WW"+checkNull(rs.getString("WEEK_NO_OF_THE_YEAR"));
					rowData.put("prd_code",         firstWeek );			
					pendingBatches= Integer.parseInt(rs.getString("PENDING_BATCHES"))+ overDue;
					rowData.put("pending_batches", pendingBatches);
				}
				else
				{
					rowData.put("prd_code",        "WW"+checkNull(rs.getString("WEEK_NO_OF_THE_YEAR")));	
					rowData.put("pending_batches",  rs.getString("PENDING_BATCHES"));
				}
				
				rowData.put("actual_batches_released", " ");
				rowData.put("tbd_batches",            " ");				
				rawDataMthlySalesJson.put(count, rowData);
				count++;				
				System.out.println(" rowData-sql_forweek2====" + rowData.toString() + "cnt "+cnt);
			}
			
			pstmt.close();
			pstmt=null;
			rs.close();
			rs=null;	
			sql_fortbdstack  = " select COUNT(BATCH_NO) AS TBD_BATCHES from MFG_RELEASE3 where SITE_CODE = '"+geoLoc+"' and  BATCH_NO IS NOT NULL and REL_STATUS = 'In METIS Not in LIMS' ";
			pstmt = conn.prepareStatement(sql_fortbdstack);
			rs= pstmt.executeQuery();
			
			while ( rs.next())
					{    
												
						rowData = new JSONObject();
						rowData.put("prd_code",  "In METIS Not in LIMS");					
						rowData.put("actual_batches_released", " ");
						rowData.put("pending_batches",         " ");
						rowData.put("tbd_batches",  rs.getString("TBD_BATCHES"));
						rawDataMthlySalesJson.put(count, rowData);
						count++;				
					}
					pstmt.close();
					pstmt=null;
					rs.close();
					rs=null;				
		//end of week implementation sql
			
		}
			System.out.println("getHalolMFGReleaseBatches 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 :HalolMFGRelease:getHalolMFGReleaseBatches():" + d.getMessage());
				throw new ITMException(d);
			}
		}
		return rawDataMthlySalesJson;
	}
	
	
	
	
	
	
	//1.End  the main method to show the parent graph 
// 2. Start this method used for Month wise Pending release  by Birendra Pandey on dated 12/02/2015		
	@SuppressWarnings("unchecked")
	public JSONObject getMonthWisePendingRelease (String dataSourceName,String periodCode,String geoLoc) throws RemoteException, ITMException
	{
		JSONObject rawDataMonthWisePendingRelease = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		SimpleDateFormat gridDateFormat=null;
		SimpleDateFormat convrtToTimStmDate=null;
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();	
		geoLoc=geoLocation;
		String week="";
		try
		{
			//do the implement on date 19/02/2015
			gridDateFormat      = new SimpleDateFormat( genericUtility.getApplDateFormat());
			convrtToTimStmDate  = new SimpleDateFormat(genericUtility.getDBDateFormat());
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;	
			//prd_code  =month;
		    System.out.println("Comming  month inside getMonthWisePendingRelease ==="+periodCode+"And Length==="+periodCode.trim().length() +" and geoLoc is ="+geoLoc);	
		    if(periodCode!=null && periodCode.trim().length()==8 && geoLoc!=null && geoLoc.trim().length()>0)
				{
			       
					System.out.println("month=="+periodCode);
				
					periodCode =convertMonthToPrd(periodCode);
					System.out.println("month after conversion for year=="+periodCode);
					
		sql  =  "select MFG_RELEASE3.SITE_CODE AS SITE_CODE, MFG_RELEASE3.ITEM_CODE AS ITEM_CODE, MFG_RELEASE3.BATCH_NO AS BATCH_NO, MFG_RELEASE3.QUANTITY AS QUANTITY,  MFG_RELEASE3.RELEASE_DATE AS RELEASE_DATE_DRJ, MFG_RELEASE3.ENTER_PLAN_DATE AS ENTER_PLAN_DATE_DRJ , MFG_RELEASE3.QC_RELEASE_DATE AS QC_RELEASE_DATE_DRJ "
			   +" FROM MFG_RELEASE3,(SELECT CODE,FR_DATE,TO_DATE FROM PERIOD WHERE CODE = '"+periodCode+"') ACCTPRD "
			   +" WHERE release_date >= ACCTPRD.FR_DATE AND release_date <= ACCTPRD.TO_DATE AND SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' "; 
					
					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"));							
                            rowData.put("enter_plan_date", checkNull(rs.getString("ENTER_PLAN_DATE_DRJ")));	
                            rowData.put("qc_rel_date",   checkNull(rs.getString("QC_RELEASE_DATE_DRJ")));                            
                            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")));
							}	
                            rawDataMonthWisePendingRelease.put(count, rowData);
							count++;							
						}
				  }
		  
		    else if(periodCode!=null && periodCode.equals("In METIS Not in LIMS"))
			{
		        System.out.println("LIMS and TBD section =="+periodCode.trim());
		     		         
		       sql= "select  MFG_RELEASE3.SITE_CODE AS SITE_CODE, MFG_RELEASE3.ITEM_CODE AS ITEM_CODE, MFG_RELEASE3.BATCH_NO AS BATCH_NO, MFG_RELEASE3.QUANTITY AS QUANTITY,  MFG_RELEASE3.RELEASE_DATE AS RELEASE_DATE_DRJ, MFG_RELEASE3.ENTER_PLAN_DATE AS ENTER_PLAN_DATE_DRJ , MFG_RELEASE3.QC_RELEASE_DATE AS QC_RELEASE_DATE_DRJ "
			  +" from MFG_RELEASE3 WHERE SITE_CODE = '"+geoLoc+"' AND  BATCH_NO IS NOT NULL and REL_STATUS = 'In METIS Not in LIMS' "	; 
		    
		    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",        checkNull(rs.getString("QUANTITY")));							
                    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("release_date",    checkNull(rs.getString("RELEASE_DATE_DRJ")));
                    rawDataMonthWisePendingRelease.put(count, rowData);
					count++;							
				}
		  }
	    
		  else{
			    week=periodCode;
		    	periodCode  =periodCode.substring(2,4);   	
		    	System.out.println(" in Week BLick Months is week is ="+periodCode);
		    	if(firstWeek.equals(week))
		    	{
		    	sql =   " SELECT  MFG_RELEASE3.SITE_CODE AS SITE_CODE, MFG_RELEASE3.ITEM_CODE AS ITEM_CODE, MFG_RELEASE3.BATCH_NO AS BATCH_NO, MFG_RELEASE3.QUANTITY AS QUANTITY,  MFG_RELEASE3.RELEASE_DATE AS RELEASE_DATE_DRJ, MFG_RELEASE3.ENTER_PLAN_DATE AS ENTER_PLAN_DATE_DRJ , MFG_RELEASE3.QC_RELEASE_DATE AS QC_RELEASE_DATE_DRJ, MFG_RELEASE3.WEEK_NO_OF_THE_YEAR FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE < SYSDATE AND rel_status = 'Not Released'  "
		    			+" UNION ALL "
		    			+ " SELECT  MFG_RELEASE3.SITE_CODE AS SITE_CODE, MFG_RELEASE3.ITEM_CODE AS ITEM_CODE, MFG_RELEASE3.BATCH_NO AS BATCH_NO, MFG_RELEASE3.QUANTITY AS QUANTITY,  MFG_RELEASE3.RELEASE_DATE AS RELEASE_DATE_DRJ, MFG_RELEASE3.ENTER_PLAN_DATE AS ENTER_PLAN_DATE_DRJ , MFG_RELEASE3.QC_RELEASE_DATE AS QC_RELEASE_DATE_DRJ, MFG_RELEASE3.WEEK_NO_OF_THE_YEAR FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE >= SYSDATE AND rel_status = 'Not Released' AND MFG_RELEASE3.WEEK_NO_OF_THE_YEAR = '"+periodCode+"' "
			            +" ORDER BY WEEK_NO_OF_THE_YEAR ASC ";
		    	
		    	}else 
		    	{
		    		sql =   "SELECT  MFG_RELEASE3.SITE_CODE AS SITE_CODE, MFG_RELEASE3.ITEM_CODE AS ITEM_CODE, MFG_RELEASE3.BATCH_NO AS BATCH_NO, MFG_RELEASE3.QUANTITY AS QUANTITY,  MFG_RELEASE3.RELEASE_DATE AS RELEASE_DATE_DRJ, MFG_RELEASE3.ENTER_PLAN_DATE AS ENTER_PLAN_DATE_DRJ , MFG_RELEASE3.QC_RELEASE_DATE AS QC_RELEASE_DATE_DRJ, MFG_RELEASE3.WEEK_NO_OF_THE_YEAR FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE >= SYSDATE AND rel_status = 'Not Released' AND MFG_RELEASE3.WEEK_NO_OF_THE_YEAR = '"+periodCode+"' "
				            +"  ORDER BY WEEK_NO_OF_THE_YEAR ASC ";
		    	}	
		    	
		                     System.out.print(" fist week is ="+firstWeek + " and  month is ="+periodCode  + "and sql is ="+sql);
		    	
						   	    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",rlseDate);
								}
								
								String enterPlanDate   =checkNull(rs.getString("ENTER_PLAN_DATE_DRJ"));
								if(enterPlanDate.trim().length()>0)
								{
									rowData.put("enter_plan_date",enterPlanDate);
								}
								
								String qcRelDate   =checkNull(rs.getString("QC_RELEASE_DATE_DRJ"));
								if(qcRelDate.trim().length()>0)
								{
									rowData.put("qc_rel_date", qcRelDate);
								}
									                            
	                            //rowData.put("delay_days", " ");	                            
	                            rawDataMonthWisePendingRelease.put(count, rowData);
								count++;								
								
							}
		    			    	
		               }
		  
					System.out.println("final rawjson data inside HalolMFGRelease-getMonthWisePendingRelease====" + rawDataMonthWisePendingRelease.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 :HalolMFGRelease-getMonthWisePendingRelease:" + d.getMessage());
						throw new ITMException(d);
					}
			}
			return rawDataMonthWisePendingRelease;
	}
	// 2. endthe method  for Month wise Pending release  by Birendra Pandey on dated 12/02/2015	
	// 3. start the  method  for getMonthWiseActualRelease  by Birendra Pandey on dated 12/02/2015		
	@SuppressWarnings("unchecked")
	public JSONObject getMonthWiseActualRelease (String dataSourceName,String month,String geoLoc) throws RemoteException, ITMException
	{
		JSONObject rawDataMonthWiseActualRelease = new JSONObject();
		JSONObject rowData = null;
		String sql = "";
		String finYear=""; 
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();	
		geoLoc=geoLocation;
		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 getMonthWiseActualRelease ==="+month);	
					month=convertMonthToPrd(month);
					System.out.println("in getMonthWiseActualRelease   month after conversion==="+month);
					
		sql = "select  MFG_RELEASE3.SITE_CODE AS SITE_CODE, MFG_RELEASE3.ITEM_CODE AS ITEM_CODE, MFG_RELEASE3.BATCH_NO AS BATCH_NO, MFG_RELEASE3.QUANTITY AS QUANTITY, MFG_RELEASE3.RELEASE_DATE AS RELEASE_DATE_DRJ, MFG_RELEASE3.ENTER_PLAN_DATE AS ENTER_PLAN_DATE_DRJ , MFG_RELEASE3.QC_RELEASE_DATE AS QC_RELEASE_DATE_DRJ "
			  +" FROM MFG_RELEASE3,(SELECT CODE,FR_DATE,TO_DATE FROM PERIOD WHERE CODE = '"+month+"') ACCTPRD "
		      +" WHERE release_date >= ACCTPRD.FR_DATE AND release_date <= ACCTPRD.TO_DATE AND SITE_CODE = '"+geoLoc+"' AND rel_status = 'Released' ";
				
		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")));	                            
                            rowData.put("delay_days"," ");
                            rawDataMonthWiseActualRelease.put(count, rowData);
							count++;
							
							
						}
				}
					System.out.println("final rawjson data inside HalolMFGRelease-getMonthWiseActualRelease====" + rawDataMonthWiseActualRelease.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 :HalolMFGRelease-getMonthWiseActualRelease:" + d.getMessage());
						throw new ITMException(d);
					}
			}
			return rawDataMonthWiseActualRelease;
	}
 //3. End this method used for month wise actual release  by Birendra Pandey on dated 12/02/2015		
	
 // 4.Start this method used for getFutureReleasePlan by Birendra Pandey on dated 12/02/2015		
		/* (non-Javadoc)
		 * @see ibase.dashboard.mfg.ejb.HalolMFGReleaseRemote#getSummaryGrid(java.lang.String, java.lang.String, java.lang.String)
		 */
		@SuppressWarnings("unchecked")
 public JSONObject getSummaryGrid (String dataSourceName,String month,String geoLoc) throws RemoteException, ITMException
		{
			JSONObject rawDataSummaryGrid = new JSONObject();
			JSONObject rowData = null;
			String sql = "";			
			ResultSet rs = null;
			PreparedStatement pstmt = null;
			Connection conn = null;
			ConnDriver connDriver = new ConnDriver();	
			int sumOfWeek = 0;
			int tbdTotal = 0;
			int  grandTotal=0;
			
			try
			{
			    if(month!=null && month.trim().length()>0 && geoLoc!=null && geoLoc.trim().length()>0)
					{
				    	conn = connDriver.getConnectDB(dataSourceName);
						connDriver = null;
						System.out.println("final parameter goes to  getSummaryGrid =="+month);					
			         
						sql  = " select COUNT(BATCH_NO) AS TBD_BATCHES from MFG_RELEASE3 where SITE_CODE = '"+geoLoc+"' and  BATCH_NO IS NOT NULL and REL_STATUS = 'In METIS Not in LIMS' ";
						pstmt = conn.prepareStatement(sql);
						rs= pstmt.executeQuery();
						
						if ( rs.next())
						{    
							tbdTotal = rs.getInt("TBD_BATCHES");
							System.out.println("tbdTotal in loop summary-->"+tbdTotal);
						}
						pstmt.close();
						pstmt=null;
						rs.close();
						rs=null;
						
						
						sql   ="SELECT SUM(BATCHES)  AS WEEKCOUNT FROM ( SELECT COUNT(BATCH_NO) AS BATCHES  FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE < SYSDATE AND rel_status = 'Not Released' UNION ALL SELECT SUM(PENDING_BATCHES) AS BATCHES FROM (SELECT COUNT(BATCH_NO) AS PENDING_BATCHES , WEEK_NO_OF_THE_YEAR FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE >= SYSDATE AND rel_status = 'Not Released' GROUP BY WEEK_NO_OF_THE_YEAR) )";
					    pstmt = conn.prepareStatement(sql);
						rs = pstmt.executeQuery();
						int count=0;			
						while (rs.next())
							{
								/*rowData = new JSONObject();	
								sumOfWeek =  rs.getInt("WEEKCOUNT");
								System.out.println("sumOfWeek------->"+sumOfWeek);
								rowData.put("weekTotal", ""+sumOfWeek);
								rowData.put("tbdTotal", tbdTotal);
								System.out.println("tbdTotal------->"+tbdTotal);
								grandTotal = tbdTotal + sumOfWeek;
								System.out.println("grandTotal------->"+grandTotal);
	                            rowData.put("grandTotal", ""+grandTotal);	                            
	                            rawDataSummaryGrid.put(count, rowData);
								count++;*/	
								
								sumOfWeek =  rs.getInt("WEEKCOUNT");
								grandTotal = tbdTotal + sumOfWeek;
								
								rowData = new JSONObject();
				                rowData.put("label", "Not Released");
				                rowData.put("value", ""+sumOfWeek);
				                rowData.put("color", "#FF0000");
				                rawDataSummaryGrid.put(count, rowData);
				                count++;
				                rowData = new JSONObject();
				                rowData.put("label", "In METIS Not in LIMS");
				                rowData.put("value", ""+tbdTotal);
				                rowData.put("color", "#ffff00");
				                rawDataSummaryGrid.put(count, rowData);
				                count++;
				                rowData = new JSONObject();
				                rowData.put("label", "Grand Total");
				                rowData.put("value", ""+grandTotal);
				                rowData.put("color", "#FF8040");
				                rawDataSummaryGrid.put(count, rowData);
				                count++;
								
							}
					}
			    
			    
			    
			    
			    
					System.out.println("final rawjson data inside HalolMFGRelease-getSummaryGrid====" + rawDataSummaryGrid.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 :HalolMFGRelease-getSummaryGrid:" + d.getMessage());
							throw new ITMException(d);
						}
				}
				return rawDataSummaryGrid;
		}
	 //4. End this method used for getFutureReleasePlan by Birendra Pandey on dated 12/02/2015	
			
	
/* Add this method by Dhanendra on date 28Mar2015*/
	@SuppressWarnings("unchecked")
	
	
	public JSONObject getTBDBatch (String dataSourceName,String periodCode,String geoLoc) throws RemoteException, ITMException
	{
		JSONObject rawDataTBDBatch = new JSONObject();
		JSONObject rowData = null;
		String sql = "";			
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		Connection conn = null;
		ConnDriver connDriver = new ConnDriver();	
		geoLoc=geoLocation;
		
		try
		{
			System.out.println("periodCode-------->"+periodCode);
			conn = connDriver.getConnectDB(dataSourceName);
			connDriver = null;
			if(periodCode!=null && periodCode.equals("In METIS Not in LIMS"))
			{
				
			sql= "select  MFG_RELEASE3.SITE_CODE AS SITE_CODE, MFG_RELEASE3.ITEM_CODE AS ITEM_CODE, MFG_RELEASE3.BATCH_NO AS BATCH_NO, MFG_RELEASE3.QUANTITY AS QUANTITY,  MFG_RELEASE3.RELEASE_DATE AS RELEASE_DATE_DRJ, MFG_RELEASE3.ENTER_PLAN_DATE AS ENTER_PLAN_DATE_DRJ , MFG_RELEASE3.QC_RELEASE_DATE AS QC_RELEASE_DATE_DRJ "
					  +" from MFG_RELEASE3 WHERE SITE_CODE = '"+geoLoc+"' AND  BATCH_NO IS NOT NULL and REL_STATUS = 'In METIS Not in LIMS' "	;
				
				
				    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",        checkNull(rs.getString("QUANTITY")));							
		                    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("release_date",    checkNull(rs.getString("RELEASE_DATE_DRJ")));
		                    rawDataTBDBatch.put(count, rowData);
							count++;							
						}
				
			}else 
			{
				periodCode=convertMonthToPrd(periodCode);
				System.out.println("periodCode in tbd batches----->"+periodCode);
				sql = "SELECT  MFG_RELEASE3.SITE_CODE AS SITE_CODE, MFG_RELEASE3.ITEM_CODE AS ITEM_CODE, MFG_RELEASE3.BATCH_NO AS BATCH_NO, MFG_RELEASE3.QUANTITY AS QUANTITY, MFG_RELEASE3.RELEASE_DATE AS RELEASE_DATE_DRJ, MFG_RELEASE3.ENTER_PLAN_DATE AS ENTER_PLAN_DATE_DRJ , MFG_RELEASE3.QC_RELEASE_DATE AS QC_RELEASE_DATE_DRJ "
						+" FROM MFG_RELEASE3,(SELECT CODE,FR_DATE,TO_DATE FROM PERIOD WHERE CODE = '"+periodCode+"') ACCTPRD "
					    +" WHERE release_date >= ACCTPRD.FR_DATE AND release_date <= ACCTPRD.TO_DATE AND SITE_CODE = '"+geoLoc+"' AND BATCH_NO IS NOT NULL AND REL_STATUS = 'In METIS Not in LIMS' ";
				    
				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",        checkNull(rs.getString("QUANTITY")));							
	                    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("release_date",    checkNull(rs.getString("RELEASE_DATE_DRJ")));
	                    rawDataTBDBatch.put(count, rowData);
						count++;							
					}
				
			}
		} 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 :HalolMFGRelease-getTBDBatch:" + d.getMessage());
				throw new ITMException(d);
			}
		}
			return rawDataTBDBatch;
	}
/* End this method by Dhanendra on date 28Mar2015*/		
	
	
	// 5./* Add this method by Dhanendra on date 30Mar2015*/	
	 @SuppressWarnings("unchecked")
	 public JSONObject getUnreleasedSummaryGrid (String dataSourceName,String periodCode,String geoLoc) throws RemoteException, ITMException
			{
				JSONObject rawDataUnreleasedSummaryGrid = new JSONObject();
				JSONObject rowData = null;
				String sql = "";			
				ResultSet rs = null;
				PreparedStatement pstmt = null;
				Connection conn = null;
				ConnDriver connDriver = new ConnDriver();	
				int oosBatch = 0;
				int ootBatch = 0;
				int labEvent = 0;
				int others = 0;
				int totalProblmBatch=0;
				int count=0;
				
				try
				{
					System.out.println("periodCode in getUnreleasedSummaryGrid =="+periodCode);	
					if(periodCode!=null && periodCode.trim().length()>0 && geoLoc!=null && geoLoc.trim().length()>0)
						{
					    	conn = connDriver.getConnectDB(dataSourceName);
							connDriver = null;
											
				         
							sql  = "select COUNT(BATCH_NO) AS OOS_BATCHES "
									+" FROM MFG_RELEASE4 "
									+" WHERE SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' AND TRIM(MFG_RELEASE4.LAB_ESTIMATED_REASON_DESCR) = 'OOS' ";  
							pstmt = conn.prepareStatement(sql);
							rs= pstmt.executeQuery();
							
							if ( rs.next())
							{    
								oosBatch = rs.getInt("OOS_BATCHES");
								System.out.println("oosBatch summary-->"+oosBatch);
							}
							pstmt.close();
							pstmt=null;
							rs.close();
							rs=null;
							
							
							
							sql  = "select COUNT(BATCH_NO) AS OOT_BATCHES "
									+" FROM MFG_RELEASE4 "
									+" WHERE SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' AND TRIM(MFG_RELEASE4.LAB_ESTIMATED_REASON_DESCR) = 'OOT' ";   
							pstmt = conn.prepareStatement(sql);
							rs= pstmt.executeQuery();
							
							if ( rs.next())
							{    
								ootBatch = rs.getInt("OOT_BATCHES");
								System.out.println("ootBatch summary-->"+ootBatch);
							}
							pstmt.close();
							pstmt=null;
							rs.close();
							rs=null;
							
							
							sql  = "select COUNT(BATCH_NO) AS LAB_BATCHES "
									+" FROM MFG_RELEASE4 "
									+" WHERE SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' AND TRIM(MFG_RELEASE4.LAB_ESTIMATED_REASON_DESCR) = 'LAB EVENTS' ";    
							pstmt = conn.prepareStatement(sql);
							rs= pstmt.executeQuery();
							
							if ( rs.next())
							{    
								labEvent = rs.getInt("LAB_BATCHES");
								System.out.println("labEvent summary-->"+labEvent);
							}
							pstmt.close();
							pstmt=null;
							rs.close();
							rs=null;
							
							
							sql  = "select COUNT(BATCH_NO) AS OTHERS_BATCHES "
									+" FROM MFG_RELEASE4 "
									+" WHERE SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' AND TRIM(MFG_RELEASE4.LAB_ESTIMATED_REASON_DESCR) = 'OTHERS' ";     
							pstmt = conn.prepareStatement(sql);
							rs= pstmt.executeQuery();
							
							if ( rs.next())
							{    
								others = rs.getInt("OTHERS_BATCHES");
								System.out.println("others summary-->"+labEvent);
							}
							pstmt.close();
							pstmt=null;
							rs.close();
							rs=null;
							
							
							totalProblmBatch = oosBatch+ootBatch+labEvent+others;
							System.out.println("totalProblmBatch ---------->"+totalProblmBatch);
								
							
							/*rowData = new JSONObject();
			                rowData.put("label", "Summary Of Problematic Unreleased Batches");
			                rowData.put("value", "0");
			                rowData.put("color", "#FF8040");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                rowData = new JSONObject();
			                rowData.put("label", "QC Lab Problem Indication");
			                rowData.put("value", "0");
			                rowData.put("color", "#FF8040");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;*/
			                rowData = new JSONObject();
			                rowData.put("label", "OOS");
			                rowData.put("value", ""+oosBatch);
			                rowData.put("color", "#FF8040");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			                rowData = new JSONObject();
			                rowData.put("label", "OOT");
			                rowData.put("value", ""+ootBatch);
			                rowData.put("color", "#FF8040");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			                rowData = new JSONObject();
			                rowData.put("label", "LAB EVENTS");
			                rowData.put("value", ""+labEvent);
			                rowData.put("color", "#FF8040");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			                rowData = new JSONObject();
			                rowData.put("label", "OTHERS");
			                rowData.put("value", ""+others);
			                rowData.put("color", "#FF8040");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			                rowData = new JSONObject();
			                rowData.put("label", "Total Problematic Batches");
			                rowData.put("value", ""+totalProblmBatch);
			                rowData.put("color", "#FF8040");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			               
							
							/*sql   ="SELECT SUM(BATCHES)  AS WEEKCOUNT FROM ( SELECT COUNT(BATCH_NO) AS BATCHES  FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE < SYSDATE AND rel_status = 'Not Released' UNION ALL SELECT SUM(PENDING_BATCHES) AS BATCHES FROM (SELECT COUNT(BATCH_NO) AS PENDING_BATCHES , WEEK_NO_OF_THE_YEAR FROM MFG_RELEASE3 WHERE WEEK_NO_OF_THE_YEAR IS NOT NULL AND ENTER_PLAN_DATE >= SYSDATE AND rel_status = 'Not Released' GROUP BY WEEK_NO_OF_THE_YEAR) )";
						    pstmt = conn.prepareStatement(sql);
							rs = pstmt.executeQuery();
							int count=0;			
							while (rs.next())
								{
									sumOfWeek =  rs.getInt("WEEKCOUNT");
									grandTotal = tbdTotal + sumOfWeek;
									
									rowData = new JSONObject();
					                rowData.put("label", "Not Released");
					                rowData.put("value", ""+sumOfWeek);
					                rowData.put("color", "#FF0000");
					                rawDataUnreleasedSummaryGrid.put(count, rowData);
					                count++;
					                rowData = new JSONObject();
					                rowData.put("label", "In METIS Not in LIMS");
					                rowData.put("value", ""+tbdTotal);
					                rowData.put("color", "#ffff00");
					                rawDataUnreleasedSummaryGrid.put(count, rowData);
					                count++;
					                rowData = new JSONObject();
					                rowData.put("label", "Grand Total");
					                rowData.put("value", ""+grandTotal);
					                rowData.put("color", "#FF8040");
					                rawDataUnreleasedSummaryGrid.put(count, rowData);
					                count++;
									
								}*/
						}
				    
				    
				    
				    
				    
						System.out.println("final rawjson data inside HalolMFGRelease-getUnreleasedSummaryGrid====" + rawDataUnreleasedSummaryGrid.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 :HalolMFGRelease-getUnreleasedSummaryGrid:" + d.getMessage());
								throw new ITMException(d);
							}
					}
					return rawDataUnreleasedSummaryGrid;
			}
		 //4. /* End this method by Dhanendra on date 30Mar2015*/
		
	private String convertPrdToMonth(String code)
	{
		System.out.println("code===" +code);
		if(code.trim().length()>0)
		{
			String year = code.substring(0, 4);
			System.out.println("year===" +year);
			String month= code.substring(4, 6);
			System.out.println("month===" +month);
			if(month.equals("01"))
			{
				code="Jan-"+year;
			}
			if(month.equals("02"))
			{
				code="Feb-"+year;
			}
			if(month.equals("03"))
			{
				code="Mar-"+year;
			}
			if(month.equals("04"))
			{
				code="Apr-"+year;
			}
			if(month.equals("05"))
			{
				code="May-"+year;
			}
			if(month.equals("06"))
			{
				code="Jun-"+year;
			}
			if(month.equals("07"))
			{
				code="Jul-"+year;
			}
			if(month.equals("08"))
			{
				code="Aug-"+year;
			}
			if(month.equals("09"))
			{
				code="Sep-"+year;
			}
			if(month.equals("10"))
			{
				code="Oct-"+year;
			}
			if(month.equals("11"))
			{
				code="Nov-"+year;
			}
			if(month.equals("12"))
			{
				code="Dec-"+year;
			}
		}
		return code;
	}
	
	
	private  String convertMonthToPrd(String month)
	{
		String monthName = month.substring(0, 3);
		String periodd="";
		String year= month.substring(4, month.length());
		if(monthName.equalsIgnoreCase("Jan"))
		{
			periodd=year+"01";
		}else
		if(monthName.equalsIgnoreCase("Feb"))
		{
			periodd=year+"02";
		}else
		if(monthName.equalsIgnoreCase("Mar"))
		{
			periodd=year+"03";
		}else
		if(monthName.equalsIgnoreCase("Apr-"))
		{periodd=year+"04";
		}else
		if(monthName.equalsIgnoreCase("May"))
		{periodd=year+"05";
		}else
		if(monthName.equalsIgnoreCase("Jun"))
		{periodd=year+"06";
		}else
		if(monthName.equalsIgnoreCase("Jul"))
		{periodd=year+"07";
		}else
		if(monthName.equalsIgnoreCase("Aug"))
		{periodd=year+"08";
		}else
		if(monthName.equalsIgnoreCase("Sep"))
		{periodd=year+"09";
		}else
		if(monthName.equalsIgnoreCase("Oct"))
		{periodd=year+"10";
		}else
		if(monthName.equalsIgnoreCase("Nov"))
		{periodd=year+"11";
		}else
		if(monthName.equalsIgnoreCase("Dec"))
		{periodd=year+"12";
		}else
		{
		periodd=month;	
		}
		
		
		System.out.println("prd is ===" +periodd);
		
		return periodd;
	}
	
	private String checkNull(String str)
	{
		if(str == null)
		{
			return "";
		}
		else
		{
			return str ;
		}

	}
		

}
