package ibase.dashboard.mfg.ejb;
import ibase.system.config.ConnDriver;
import ibase.utility.CommonConstants;
import ibase.utility.EMail;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.GenericUtility;
import ibase.webitm.utility.ITMException;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import javax.ejb.Stateless;
import org.json.simple.JSONObject;
import com.lowagie.text.Document;
import com.lowagie.text.Element;
import com.lowagie.text.FontFactory;
import com.lowagie.text.Image;
import com.lowagie.text.Paragraph;
import com.lowagie.text.Rectangle;
import com.lowagie.text.pdf.PdfPCell;
import com.lowagie.text.pdf.PdfPTable;
import com.lowagie.text.pdf.PdfWriter;
/**
 * 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 DISTINCT CODE, SUM(ACTUAL_BATCHES_RELEASED) AS ACTUAL_BATCHES_RELEASED,  SUM(PENDING_BATCHES) AS PENDING_BATCHES, SUM(TBD_BATCHES) AS TBD_BATCHES FROM ( "
			      +" SELECT COUNT(BATCH_NO) AS ACTUAL_BATCHES_RELEASED, 0 AS PENDING_BATCHES, 0 AS TBD_BATCHES, ACCTPRD.CODE AS 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 0, 0, ACCTPRD.CODE "
			      +" UNION "
				   +" SELECT 0 AS ACTUAL_BATCHES_RELEASED, COUNT(BATCH_NO) AS PENDING_BATCHES, 0 AS TBD_BATCHES, ACCTPRD.CODE AS 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 0, 0, ACCTPRD.CODE "
			       +" UNION "
		           +" SELECT 0 AS ACTUAL_BATCHES_RELEASED, 0 AS PENDING_BATCHES, COUNT(BATCH_NO) AS TBD_BATCHES, ACCTPRD.CODE AS 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 = 'In METIS Not in LIMS' GROUP BY 0, 0, ACCTPRD.CODE ) GROUP BY CODE ORDER BY CODE ";
		 			 
				 
    /*  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",             checkZero(rs.getString("TBD_BATCHES")));
				rowData.put("actual_batches_released", checkZero(rs.getString("ACTUAL_BATCHES_RELEASED")));
				rowData.put("pending_batches",         checkZero(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 site_code = '"+geoLoc+"' AND 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 site_code = '"+geoLoc+"' AND  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 site_code = '"+geoLoc+"' AND  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)
				{
			       
									
					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 MFG_RELEASE3.release_date >= ACCTPRD.FR_DATE AND MFG_RELEASE3.release_date <= ACCTPRD.TO_DATE AND MFG_RELEASE3.SITE_CODE = '"+geoLoc+"' AND MFG_RELEASE3.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 SITE_CODE = '"+geoLoc+"' and 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 SITE_CODE = '"+geoLoc+"' and 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");	
				              //  rowData.put("color", "");
				                rawDataSummaryGrid.put(count, rowData);
				                count++;
				                rowData = new JSONObject();
				                rowData.put("label", "In METIS Not in LIMS");
				                rowData.put("value", ""+tbdTotal);
				                rowData.put("color", "ffff00");
				               // rowData.put("color", "");
				                rawDataSummaryGrid.put(count, rowData);
				                count++;
				                rowData = new JSONObject();
				                rowData.put("label", "Grand Total");
				                rowData.put("value", ""+grandTotal);
				                rowData.put("color", "FF8040");
				                //rowData.put("color", " ");
				                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 MFG_RELEASE3.release_date >= ACCTPRD.FR_DATE AND MFG_RELEASE3.release_date <= ACCTPRD.TO_DATE AND MFG_RELEASE3.SITE_CODE = '"+geoLoc+"' AND MFG_RELEASE3.BATCH_NO IS NOT NULL AND MFG_RELEASE3.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_RELEASE3 "
									+" WHERE SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' AND TRIM(MFG_RELEASE3.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_RELEASE3 "
									+" WHERE SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' AND TRIM(MFG_RELEASE3.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_RELEASE3 "
									+" WHERE SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' AND TRIM(MFG_RELEASE3.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_RELEASE3 "
									+" WHERE SITE_CODE = '"+geoLoc+"' AND rel_status = 'Not Released' AND TRIM(MFG_RELEASE3.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", "OOS");
			                rowData.put("value", ""+oosBatch);
			               // rowData.put("color", "#FF8040");
			                rowData.put("color", " ");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			                rowData = new JSONObject();
			                rowData.put("label", "OOT");
			                rowData.put("value", ""+ootBatch);
			                //rowData.put("color", "#FF8040");
			                rowData.put("color", " ");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			                rowData = new JSONObject();
			                rowData.put("label", "LAB EVENTS");
			                rowData.put("value", ""+labEvent);
			               // rowData.put("color", "#FF8040");
			                rowData.put("color", " ");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			                rowData = new JSONObject();
			                rowData.put("label", "OTHERS");
			                rowData.put("value", ""+others);
			                //rowData.put("color", "#FF8040");
			                rowData.put("color", " ");
			                rawDataUnreleasedSummaryGrid.put(count, rowData);
			                count++;
			                
			                rowData = new JSONObject();
			                rowData.put("label", "Total Problematic Batches");
			                rowData.put("value", ""+totalProblmBatch);
			                //rowData.put("color", "#FF8040");
			                rowData.put("color", " ");
			                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*/
	 
	 /*Start- this method add for generatePDF on 6Apr2015*/
	 public String getPDFByScheduler(String rptTitle, String remark, String userID, String imgName) throws RemoteException, ITMException 
	 {
		 System.out.println("In HalolMFGRelease EJB.getPDFByScheduler Method ");
			PdfWriter writer = null;	
			OutputStream file = null;
			Document document = null;		
			ConnDriver connDriver = new ConnDriver();
			Connection conn = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;		
			ArrayList<String> mailInfo=null;
			String imageName = null;
			Image image = null;
			File attachFile   = null;
		    String imgFilePath =null;	
		    File  existImgFile =null;
		    String pdfFileName =null;
		    String toMailId    =""; 
			String ccMailId    ="";
			String bccAddress  =""; 
			String subject     ="";
			String userWiseBodytext="";
			String resultFlage ="false";
			String exportPDFPath ="";
			String exportImagePath = "";
			String imgNameArray[] = null;  
			SimpleDateFormat simpleDateFormat = null;
			Calendar calendar = null;
			String currDate = "";
			int prntTblNo      =2;
			PdfPTable parentTable=null;
			Paragraph preface = null;
			System.out.print("Calling for PDF created step1");
			try
			{
				
				 //START CODE FOR WINDOWS
				//exportPDFPath    ="C://jboss-5.1.0.GA//server//default//deploy//ibase.ear//ibase.war//dashboard//mfg//Scheduler//ExportExample//ExportPDF//";
				//exportImagePath  ="C://jboss-5.1.0.GA//server//default//deploy//ibase.ear//ibase.war//dashboard//mfg//Scheduler//ExportExample//ExportImages//";
				 //END CODE FOR WINDOWS
				
				 //START CODE FOR LINUX
				// exportPDFPath      = CommonConstants.APPLICATION_CONTEXT + "dashboard" + File.separator + "mfg"+ File.separator + "Scheduler"+File.separator +"ExportExample"+ File.separator + "ExportPDF"+File.separator;
				exportPDFPath      = CommonConstants.JBOSSHOME + File.separator +"server"+File.separator +"default"+File.separator +"deploy"+File.separator +"ibase.ear"+File.separator + "ibase.war" +File.separator +  "dashboard" + File.separator + "mfg"+ File.separator + "Scheduler"+File.separator +"ExportExample"+ File.separator + "ExportPDF"+File.separator;
		        exportImagePath    = CommonConstants.JBOSSHOME + File.separator +"server"+File.separator +"default"+File.separator +"deploy"+File.separator +"ibase.ear"+File.separator + "ibase.war" +File.separator +  "dashboard" + File.separator + "mfg"+ File.separator + "Scheduler"+File.separator +"ExportExample"+ File.separator + "ExportImages"+File.separator;
		       //END CODE FOR LINUX
				System.out.println("exportPDFPath----->"+exportPDFPath);				
				simpleDateFormat = new SimpleDateFormat("dd-MMM-yy");
				calendar      = Calendar.getInstance();
				currDate      = simpleDateFormat.format(calendar.getTime());			
				document      = new Document();				
				rptTitle      = rptTitle+"-"+currDate;				
				pdfFileName   = exportPDFPath+rptTitle+"-"+userID+".pdf";				
				System.out.println("modify PDF file name "+	pdfFileName+"current date formate==="+currDate);
				file          = new FileOutputStream(new File(pdfFileName));			
				writer        = PdfWriter.getInstance(document, file);
				document.open();			
				preface = new Paragraph();
				preface.setAlignment(Element.ALIGN_CENTER);
			    preface.add(new Paragraph(rptTitle, FontFactory.getFont(FontFactory.TIMES_ROMAN, 15)));
			    preface.add(new Paragraph(" "));   
			    document.add(preface);
			   
				imgNameArray = imgName.split(",");
				for(int size=0;size<prntTblNo;size++)
				{
				
					System.out.println("imageName =="+imageName);
					if(size==0)
					{
						imageName= imgNameArray[size];
						parentTable = new PdfPTable(1);
						
						imgFilePath = exportImagePath+imageName+".png";
				        
						image = Image.getInstance(imgFilePath);
						parentTable.addCell(image);
						
						existImgFile= new File(imgFilePath);
						if(existImgFile.exists())
						{
							existImgFile.delete();
							System.out.println("Image Deleted after status **First*****"+imgFilePath ); 
						}
						parentTable.setWidthPercentage(100);
					}else
					{
						
						parentTable = new PdfPTable(2);
						
						for(int childTblCnt = 1; childTblCnt<imgNameArray.length;childTblCnt++)
						{
							imageName= imgNameArray[childTblCnt];
							imgFilePath = exportImagePath+imageName+".png";
					        image = Image.getInstance(imgFilePath);
							System.out.println("Image name with image path =="+imageName+"--------imgFilePath=="+imgFilePath); 
							
							PdfPTable table=new PdfPTable(1);
							PdfPTable gridTbl=new PdfPTable(2);
							PdfPTable imgTbl=new PdfPTable(1);							
							imgTbl.addCell(image);
	                        existImgFile= new File(imgFilePath);               
							PdfPCell cell = null;
							if(childTblCnt==1)
							{
							     cell = new PdfPCell (new Paragraph ("Summary Of Problematic Unreleased Batches",FontFactory.getFont(FontFactory.TIMES_ROMAN, 10)));
							}else{
								 cell = new PdfPCell (new Paragraph ("Final Summary Of Total Batches",FontFactory.getFont(FontFactory.TIMES_ROMAN, 10)));	
							}
							cell.setColspan (2);
							cell.setHorizontalAlignment (Element.ALIGN_CENTER);
							cell.setPadding (10.0f);
							gridTbl.addCell (cell);
							if(childTblCnt==1)
							{
							     cell = new PdfPCell (new Paragraph ("QC Lab Problem Indication",FontFactory.getFont(FontFactory.TIMES_ROMAN, 8)));
							}else{
								cell = new PdfPCell (new Paragraph ("Category Type",FontFactory.getFont(FontFactory.TIMES_ROMAN, 8)));
							}
							cell.setHorizontalAlignment (Element.ALIGN_CENTER);
							cell.setPadding (10.0f);
							gridTbl.addCell (cell);
							
							if(childTblCnt==1)
							{
								cell = new PdfPCell (new Paragraph ("Number Of Batches",FontFactory.getFont(FontFactory.TIMES_ROMAN, 8)));
							}else{
								cell = new PdfPCell (new Paragraph ("Number Of Batches",FontFactory.getFont(FontFactory.TIMES_ROMAN, 8)));
							}
							cell.setHorizontalAlignment (Element.ALIGN_CENTER);
							cell.setPadding (10.0f);
							gridTbl.addCell (cell);
							
							cell = new PdfPCell(gridTbl);
							cell.setBorder(Rectangle.NO_BORDER);
							table.addCell(cell);
							
							cell = new PdfPCell(imgTbl);
							cell.setBorder(Rectangle.NO_BORDER);
							table.addCell(cell);
							
							cell = new PdfPCell(table);
							cell.setBorder(Rectangle.NO_BORDER);
							parentTable.addCell(cell);
							
							if(existImgFile.exists())
							{
								existImgFile.delete();
								System.out.println("Image Deleted after status **Second*****"+imgFilePath ); 
							}
						
							
						}
						parentTable.setWidthPercentage(90);
					}
					
					
					document.add(parentTable);
					preface = new Paragraph();
					preface.add(new Paragraph(" "));
					document.add(preface);
					if(size==0)
					{
						preface = new Paragraph();
						preface.add(new Paragraph("Remark: "+remark, FontFactory.getFont(FontFactory.TIMES_ROMAN, 8)));
						preface.add(new Paragraph(" "));
						document.add(preface);
					}
			 }
				
				document.close();
				file.close();
				
				System.out.println("PDF Created succesfully");
				conn = connDriver.getConnectDB("DriverITM");
					connDriver  =null;
					toMailId    =""; 
					ccMailId    ="";
					bccAddress  =""; 
					subject     =rptTitle;
					userWiseBodytext ="Dear Sir, Please find attached herewith is the Daily Output:"+rptTitle+" Report in PDF format.";
					String sql = "select name,user_type,email_id FROM users WHERE code='"+userID+"'";
					pstmt = conn.prepareStatement(sql);
					rs = pstmt.executeQuery();
					if (rs.next())
					{
						toMailId   = checkNull(rs.getString("email_id"));
						
						System.out.println("No of Users for HalolMFGRelease.getPDFByScheduler  ="+userID +"AND According to the Email ID======="+toMailId);
					}
					
					pstmt.close();
					pstmt = null;
					conn.close();
					conn = null;
					rs.close();
					rs = null;				
					System.out.println("Export Pdf path for HalolMFGRelease.getPDFByScheduler ="+pdfFileName);
					attachFile = new File(pdfFileName);				
				    if(attachFile.exists() && attachFile.length()>0)
				            {
								mailInfo =new ArrayList<String>();
								mailInfo.add(toMailId);
								mailInfo.add(ccMailId);
								mailInfo.add(bccAddress);
								mailInfo.add(subject);
								mailInfo.add(userWiseBodytext);
								mailInfo.add(attachFile.getPath());
								this.sendingMail(mailInfo);
								resultFlage="true";
							}
				System.out.println("*sending  mail done succesfully for HalolMFGRelease.getPDFByScheduler*");
			} catch (Exception e)
			{
				System.out.println("Exception :HalolMFGRelease :getPDFByScheduler(HashMap<String, List<String>>,String,String,String,String"+e.getMessage());
				e.printStackTrace();
			}
			finally
			{
				try
				{
					if (pstmt != null)
					{
						pstmt.close();
						pstmt = null;
					}
				} catch (Exception e)
				{
					e.printStackTrace();

				}
			}
			return 	resultFlage;
		 	 
	 }
	 
	 public void sendingMail(ArrayList<String> mailInfo)
		{
			StringBuffer commInfo = new StringBuffer();
			commInfo.append("<ROOT>");
			commInfo.append("<MAILINFO>");
			commInfo.append("<EMAIL_TYPE>").append("page").append("</EMAIL_TYPE>");
			commInfo.append("<TO_ADD>").append("<![CDATA[" + mailInfo.get(0) + "]]>").append("</TO_ADD>");
			commInfo.append("<CC_ADD>").append("<![CDATA[" + mailInfo.get(1) + "]]>").append("</CC_ADD>");
			commInfo.append("<BCC_ADD>").append("<![CDATA[" + mailInfo.get(2) + "]]>").append("</BCC_ADD>");
			commInfo.append("<SUBJECT>").append("<![CDATA[" + mailInfo.get(3) + "]]>").append("</SUBJECT>");
			commInfo.append("<BODY_TEXT>").append("<![CDATA[" +  mailInfo.get(4) + "]]>").append("</BODY_TEXT>");
			commInfo.append("<XML_DATA_FILE_PATH>").append("<![CDATA[" +  mailInfo.get(5) + "]]>").append("</XML_DATA_FILE_PATH>");
			commInfo.append("</MAILINFO>");
			commInfo.append("</ROOT>");
			EMail email = new EMail();
			try
			{
			 System.out.println(" calling sendMail  method() for forHalolMFGRelease.getPDFByScheduler");
			  email.sendMail(commInfo.toString(), "ITM", null);
			  System.out.println(" ********Email send succesfully for HalolMFGRelease.getPDFByScheduler ***********");
			} catch (Exception e)
			{
				e.printStackTrace();
				System.out.println("Exception :HalolMFGRelease :sendMail(String ,String)" + e.getMessage());
			}
			email = null;
		}
	 /*End- this method add for generatePDF on 6Apr2015*/	
	private String convertPrdToMonth(String code)
	{
		
		if(code.trim().length()>0)
		{
			String year = code.substring(0, 4);			
			String month= code.substring(4, 6);			
			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 ;
		}

	}


	private String checkZero(String str)
	{
		if(str.equals("0"))
		{
			return "";
		}
		else
		{
			return str ;
		}

	}


}
