package ibase.dashboard.mfg.ejb;

import ibase.system.config.ConnDriver;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.ITMException;
import ibase.utility.EMail;
import java.awt.Color;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.MalformedURLException;
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 java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.processing.FilerException;
import javax.ejb.Stateless;
import com.lowagie.text.*;
import com.lowagie.text.pdf.*;

/**
 * Session Bean implementation class ProductionVsBudgetReport
 */
@Stateless
public class ProductionVsBudgetReport extends ValidatorEJB implements ProductionVsBudgetReportRemote, ProductionVsBudgetReportLocal
{
	/**
	 * Default constructor.
	 */
	List<String> listDate = null;
	StringBuffer xmlDataForGeo = null;
	StringBuffer xmlDataForUnit = null;

	public ProductionVsBudgetReport() throws RemoteException, ITMException
	{
		// TODO Auto-generated constructor stub

		String sql = "";
		ResultSet rs = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ConnDriver connDriver = new ConnDriver();
		String firstDateofMonth = "";
		String lstDateofMonth = "";
		Calendar cal = null;
		SimpleDateFormat sdf = null;

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			/*
			 * sql="select  to_char ( trunc(sysdate,'Month') + rownum - 1,'DD') as tran_date from item where rownum <= last_day (sysdate) - trunc(sysdate,'Month') + 1 order by 1 "
			 * ;
			 * 
			 * pstmt = conn.prepareStatement(sql); rs = pstmt.executeQuery();
			 * 
			 * while(rs.next()) { listDate.add(rs.getString(1)); }
			 * pstmt.close(); rs.close(); pstmt = null; rs = null;
			 * 
			 * /** Code for get first n last date of month date 17/10/13
			 */

			cal = Calendar.getInstance();
			sdf = new SimpleDateFormat("dd-MMM-yy");
			cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE));
			lstDateofMonth = sdf.format(cal.getTime());
			cal = Calendar.getInstance();
			// cal.add(cal.MONTH,-1);
			cal.set(Calendar.DATE, cal.getActualMinimum(Calendar.DATE));
			firstDateofMonth = sdf.format(cal.getTime());

			// This will generated geoLocation droup down changes on dated
			// 28/11/2013
			// sql(V.1)
			// ="select  fld_value, descr from gencodes  where FLD_NAME ='GEO_LOC' and mod_name='X' ";
			// sql(v.2)
			// ="select fld_value, descr from gencodes where fld_name = 'GEO_LOC' and mod_name = 'X' and fld_value in (select distinct geo_loc from site where site_code in (select distinct site_code from dwh_production_day_events where tran_date between ? and ?  and ref_ser ='W-RCP'))";
			
			sql = "select g.fld_value, g.descr from gencodes g where fld_name = 'GEO_LOC' and mod_name = 'X' and instr((select trim(var_value) from disparm where var_name = 'DWH_SITE_GEO_LOC'),trim(g.fld_value)) >0";

			pstmt = conn.prepareStatement(sql);			
			rs = pstmt.executeQuery();
			xmlDataForGeo = new StringBuffer("<?xml version=\"1.0\"?><Root>");

			while (rs.next())
			{

				xmlDataForGeo.append("<Detail>");
				xmlDataForGeo.append("<fld_value>").append("<![CDATA[" + checkNull(rs.getString("fld_value")) + "]]>").append("</fld_value>");
				xmlDataForGeo.append("<descr>").append("<![CDATA[" + checkNull(rs.getString("descr")) + "]]>").append("</descr>");
				xmlDataForGeo.append("</Detail>");

			}
			xmlDataForGeo.append("</Root>");
			System.out.println("Geo Location Drop Down List==" + xmlDataForGeo);
			pstmt.close();
			rs.close();
			pstmt = null;
			rs = null;

			// End of geoLoaction with unit_form
			// coding for gererate unit form drop down

			// sql
			// ="select distinct(unit__form)  from item_type  where  unit__form is not null";

			sql = "select distinct (i.unit__form),u.descr from item_type i ,uom u  where i.unit__form=u.unit and i.unit__form is not null";

			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			xmlDataForUnit = new StringBuffer("<?xml version=\"1.0\"?><Root>");
			while (rs.next())
			{
				xmlDataForUnit.append("<Detail>");
				xmlDataForUnit.append("<unit_form>").append("<![CDATA[" + checkNull(rs.getString("unit__form")) + "]]>").append("</unit_form>");
				xmlDataForUnit.append("<descr>").append("<![CDATA[" + checkNull(rs.getString("descr")) + "]]>").append("</descr>");
				xmlDataForUnit.append("</Detail>");
			}
			xmlDataForUnit.append("</Root>");
			System.out.println("unitForm  Drop Down List==" + xmlDataForUnit);
			pstmt.close();
			rs.close();
			pstmt = null;
			rs = null;

			// End of unit for drop down

		} catch (Exception e)
		{
			e.printStackTrace();
			throw new ITMException(e);
		} finally
		{
			try
			{
				if (conn != null)
				{
					conn.close();
					conn = null;
				}
				if (rs != null)
				{
					rs.close();
					rs = null;
				}

				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}

			} catch (Exception d)
			{
				d.printStackTrace();
				throw new ITMException(d);
			}
		}
	}

	// This method will be return xml data for geoLoaction List
	public String getGeoLoCodeXmlData()
	{
		return xmlDataForGeo.toString();
	}

	public String getUnitFormXmlData()
	{
		return xmlDataForUnit.toString();
	}

	// Start  main method which are performed to all chart

	public String getProductionBudget(String frmDate, String toDate, String geoLoc, String unitForm) throws RemoteException, ITMException
	{
		String sql = "";
		ResultSet rs = null, rs1 = null;
		Connection conn = null;
		PreparedStatement pstmt = null, pstmt1;
		ConnDriver connDriver = new ConnDriver();
		StringBuffer xmlData = null;
		String geoLocation = "";
		String unit = "";
		String previousUnit = "";
		String geoLocationPrevious = "";
		int count = 0;
		double dailyBudgetQty = 0;
		String geol_Descr = "";
		String unit_Descr = "";
		int year = 0;
		String month = "";
		String[] tempfrmDate = null;
		String[] temptoDate = null;

		int firstDayOfMonth = 0;
		int lastDayOfMonth = 0;
		listDate = new ArrayList<String>();

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			// change on date 11/11/2013
			// case1
			System.out.println("from date from jsp =" + frmDate + "To Date from jsp==" + toDate);
			tempfrmDate = frmDate.split("-");
			firstDayOfMonth = Integer.parseInt(tempfrmDate[0]);
			month = tempfrmDate[1];
			year = Integer.parseInt(tempfrmDate[2]);
			// case2
			temptoDate = toDate.split("-");
			lastDayOfMonth = Integer.parseInt(temptoDate[0]);
			System.out.println("First of Month==" + firstDayOfMonth + "& Last of Month===" + lastDayOfMonth + "Month from jsp ==" + month + "Year from jsp ==" + year);
			for (int i = firstDayOfMonth; i <= lastDayOfMonth; i++)
			{

				listDate.add(String.valueOf(i));

			}

			// end of changes
			System.out.println("select Final initGeoLocation inside in EJB ===" + geoLoc + "selected initUnitForm inside in java ==" + unitForm + "length of List ====" + listDate.size() + "AND Value of ===" + listDate);
	
			sql = "select geo_loc, unit__form,to_char (tran_date,'DD') as tran_date, day_quantity, pyr_cumqty, " + "cum_budqty , sum(day_quantity) over (partition by geo_loc, unit__form order by tran_date) " + "as cur_cumqty from ( select s.GEO_LOC,p.tran_date, t.unit__form, " + "sum(fn_qty_form(p.item_code,p.unit,t.unit__form,i.phy_attrib_6, p.quantity)) " + "as day_quantity, fn_get_pyr_cumqty( to_date(add_months(p.tran_date,-12)),s.geo_loc,t.unit__form ) " + "as pyr_cumqty, fn_get_budgetqty(p.tran_date,s.GEO_LOC,t.unit__form ) " + "as cum_budqty from dwh_production_day_events p, item i, item_type t, " + "site s where p.site_code = s.site_code and  p.item_code = i.item_code " + "and i.item_type = t.item_type and p.tran_date >= ? " + "and p.tran_date <= ? " + "and p.ref_type = (case when  t.item_type in('AMPOULES','BOTTLES','DROPS','INHALER','POWDER','POUCH','SUSPENSION','TUBE','VIALS') then 'F'  when  t.item_type in('CAPSULES','TABLETS',' SOFTGELCAP') then 'F' else '' end  ) " + "and p.ref_ser = 'W-RCP' and s.geo_loc in(" + geoLoc + ") " + "and t.unit__form in(" + unitForm + ") group by s.GEO_LOC, p.tran_date, t.unit__form ) " + "order by geo_loc, unit__form,tran_date";
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, frmDate);
			pstmt.setString(2, toDate);
			// End of setter statement

			rs = pstmt.executeQuery();
			xmlData = new StringBuffer("<?xml version=\"1.0\"?><Root>");
			xmlData.append("<Detail>");
			int cntFrBudget = 0;
			String curCumQty = "";
			String missingDate = "";
			String pyrCumqty = "0.0";
			int dayCount = 0;

			while (rs.next())
			{

				count++;
				geoLocation = checkNull(rs.getString("geo_loc"));
				unit = checkNull(rs.getString("unit__form"));

				if (count > 1 && ((!previousUnit.equals(unit)) || (!geoLocationPrevious.equals(geoLocation))))
				{

					if (dayCount <= lastDayOfMonth)
					{
						int cntr = dayCount;
						for (int j = 0; j < lastDayOfMonth - cntr; j++)
						{
							cntFrBudget++;
							xmlData.append("<tran_date  date=\"" + (listDate.get(dayCount)) + "\">");
							xmlData.append("<day_quantity quantity=\"0\">");

							// Add new Block for preyear Cumulative
							// sql="select to_date(add_months(?,-12)) from dual ";
							missingDate = listDate.get(dayCount) + "-" + month + "-" + year;
							sql = "select fn_get_pyr_cumqty( to_date(add_months('" + missingDate + "',-12))," + geoLocationPrevious + ",'" + previousUnit + "') as pyr_cumqty from dual";
							pstmt1 = conn.prepareStatement(sql);
							rs1 = pstmt1.executeQuery();
							if (rs1.next())
							{
								if (rs1.getDouble("pyr_cumqty") != 0)
								{
									pyrCumqty = checkNull(rs1.getString("pyr_cumqty"));
									System.out.println("preyrcumulative ===" + pyrCumqty);
									System.out.println("preyrcumulative for missing date:" + missingDate + "===geoloc:" + geoLocationPrevious + ":previousUnit:" + previousUnit);
								}
							}
							pstmt1.close();
							rs1.close();
							pstmt1 = null;
							rs1 = null;
							// End of preyearCumulative

							xmlData.append("<pyr_cumqty>").append("<![CDATA[" + pyrCumqty + "]]>").append("</pyr_cumqty>");
							xmlData.append("<cum_budqty>").append("<![CDATA[" + dailyBudgetQty * (cntFrBudget) + "]]>").append("</cum_budqty>");
							xmlData.append("<cur_cumqty>").append("<![CDATA[" + curCumQty + "]]>").append("</cur_cumqty>");
							xmlData.append("</day_quantity>");
							xmlData.append("</tran_date>");
							dayCount++;

						}
					}

					xmlData.append("</unit_form>");
					xmlData.append("</geo_loc>");
					// dayCount=0;
				}

				if ((count == 1) || (!previousUnit.equals(unit)) || (!geoLocationPrevious.equals(geoLocation)))
				{

					// Add here for decr pupose
					sql = "select  descr from gencodes  where FLD_NAME='GEO_LOC' and fld_value= ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, geoLocation);
					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{
						geol_Descr = checkNull(rs1.getString("descr"));
					}
					pstmt1 = null;
					rs1 = null;
					// **************************************************
					sql = "select descr from uom where unit= ?";
					pstmt1 = conn.prepareStatement(sql);
					pstmt1.setString(1, unit);

					rs1 = pstmt1.executeQuery();
					if (rs1.next())
					{

						unit_Descr = checkNull(rs1.getString("descr"));

					}
					pstmt1.close();
					pstmt1 = null;
					rs1.close();
					rs1 = null;

					// End here decr pupose for Unit_form and geo_location

					cntFrBudget = 0;
					xmlData.append("<geo_loc location=\"" + geoLocation + "\" glocdescr =\"" + geol_Descr + "\"  >");
					xmlData.append("<unit_form unit=\"" + checkNull(rs.getString("unit__form")) + "\" unitdescr =\"" + unit_Descr + "\">");
					dayCount = 0;
					curCumQty = "0.0";
					pyrCumqty = "0.0";
					geoLocationPrevious = geoLocation = checkNull(rs.getString("geo_loc"));
					previousUnit = checkNull(rs.getString("unit__form"));
					// Add descr
					geol_Descr = "";
					unit_Descr = "";

				}

				dailyBudgetQty = rs.getDouble("cum_budqty");
				cntFrBudget++;

				if (!listDate.get(dayCount).equals(rs.getString("tran_date")))
				{
					int cnt = dayCount;
					for (int i = 0; i < Integer.parseInt(rs.getString("tran_date")) - (cnt + 1); i++)
					{
						// Add new Block for preyear Cumulative
						// sql="select to_date(add_months(?,-12)) from dual ";
						missingDate = listDate.get(dayCount) + "-" + month + "-" + year;
						sql = "select fn_get_pyr_cumqty( to_date(add_months('" + missingDate + "',-12))," + geoLocation + ",'" + unit + "') as pyr_cumqty from dual";
						pstmt1 = conn.prepareStatement(sql);
						rs1 = pstmt1.executeQuery();
						if (rs1.next())
						{
							// Modify by birendra Pandey
							if (rs1.getDouble("pyr_cumqty") != 0)
							{
								pyrCumqty = checkNull(rs1.getString("pyr_cumqty"));
								System.out.println("preyrcumulative ===" + pyrCumqty);
							}

						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;
						// End of preyearCumulative

						xmlData.append("<tran_date  date=\"" + (listDate.get(dayCount)) + "\">");
						xmlData.append("<day_quantity quantity=\"0\">");
						xmlData.append("<pyr_cumqty>").append("<![CDATA[" + pyrCumqty + "]]>").append("</pyr_cumqty>");
						xmlData.append("<cum_budqty>").append("<![CDATA[" + dailyBudgetQty * (cntFrBudget) + "]]>").append("</cum_budqty>");
						xmlData.append("<cur_cumqty>").append("<![CDATA[" + curCumQty + "]]>").append("</cur_cumqty>");
						xmlData.append("</day_quantity>");
						xmlData.append("</tran_date>");
						dayCount++;
						cntFrBudget++;

					}

				}

				xmlData.append("<tran_date  date=\"" + checkNull(rs.getString("tran_date")) + "\">");
				xmlData.append("<day_quantity quantity=\"" + checkNull(rs.getString("day_quantity")) + "\">");
				xmlData.append("<pyr_cumqty>").append("<![CDATA[" + checkNull(rs.getString("pyr_cumqty")) + "]]>").append("</pyr_cumqty>");
				xmlData.append("<cum_budqty>").append("<![CDATA[" + dailyBudgetQty * (cntFrBudget) + "]]>").append("</cum_budqty>");
				xmlData.append("<cur_cumqty>").append("<![CDATA[" + checkNull(rs.getString("cur_cumqty")) + "]]>").append("</cur_cumqty>");
				xmlData.append("</day_quantity>");
				xmlData.append("</tran_date>");
				curCumQty = checkEmpty(checkNull(rs.getString("cur_cumqty")));
				pyrCumqty = checkEmpty(checkNull(rs.getString("pyr_cumqty")));
				dayCount++;

			}

			if (count > 0)
			{
				if (dayCount < lastDayOfMonth)
				{

					for (int p = dayCount; p < lastDayOfMonth; p++)
					{
						cntFrBudget++;
						// Add new Block for preyear Cumulative
						// sql="select to_date(add_months(?,-12)) from dual ";
						missingDate = listDate.get(dayCount) + "-" + month + "-" + year;
						sql = "select fn_get_pyr_cumqty( to_date(add_months('" + missingDate + "',-12))," + geoLocation + ",'" + unit + "') as pyr_cumqty from dual";
						pstmt1 = conn.prepareStatement(sql);
						rs1 = pstmt1.executeQuery();
						if (rs1.next())
						{
							if (rs1.getDouble("pyr_cumqty") != 0)
							{
								pyrCumqty = checkNull(rs1.getString("pyr_cumqty"));
								System.out.println("pyrCumqty As New ===" + pyrCumqty);

							}

						}
						pstmt1.close();
						rs1.close();
						pstmt1 = null;
						rs1 = null;
						// End of preyearCumulative

						xmlData.append("<tran_date  date=\"" + (listDate.get(dayCount)) + "\">");
						xmlData.append("<day_quantity quantity=\"0\">");
						xmlData.append("<pyr_cumqty>").append("<![CDATA[" + pyrCumqty + "]]>").append("</pyr_cumqty>");
						xmlData.append("<cum_budqty>").append("<![CDATA[" + dailyBudgetQty * (cntFrBudget) + "]]>").append("</cum_budqty>");
						xmlData.append("<cur_cumqty>").append("<![CDATA[" + curCumQty + "]]>").append("</cur_cumqty>");
						xmlData.append("</day_quantity>");
						xmlData.append("</tran_date>");
						dayCount++;

					}
				}
				xmlData.append("</unit_form>");
				xmlData.append("</geo_loc>");
			}

			xmlData.append("</Detail>");
			xmlData.append("</Root>");

			pstmt.close();
			pstmt = null;
			rs.close();
			rs = null;

		} catch (Exception e)
		{

			e.printStackTrace();
			System.out.println("ibase.dashboard.mfg.ejb :ProductionVsBudgetReport" + e);
			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("ibase.dashboard.mfg.ejb :ProductionVsBudgetReport" + d);
				throw new ITMException(d);
			}
		}
		return xmlData.toString();
	}

	// End  main method which are performed to all chart	
	
	private String checkNull(String input)
	{

		if (input == null)
		{
			input = "";
		} else
		{
			input = input.trim();
		}
		return input;
	}

	private String checkEmpty(String input)
	{

		if (input == "")
		{
			input = "0";
		} else
		{
			input = input.trim();
		}
		return input;
	}
	
// start method  for roule validation
	public boolean getRoleValid(String userId, String roleCode) throws RemoteException, ITMException
	{
		boolean flag = false;
		ConnDriver connDriver = new ConnDriver();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try
		{

			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			String sql = " SELECT * FROM WF_ROLE_USERS WHERE userid='" + userId + "' AND role_code='" + roleCode + "' ";
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next())
			{
				flag = true;
			}
			pstmt.close();
			pstmt = null;
			conn.close();
			conn = null;
			rs.close();
			rs = null;
		} catch (Exception e)
		{
			flag = false;

		} finally
		{
			try
			{
				if (pstmt != null)
				{
					pstmt.close();
					pstmt = null;
				}
			} catch (Exception e)
			{
				flag = false;

			}
		}
		return flag;
	}

//end Role validation
	
//start method for PDF generate 	
	public void generatePDF(HashMap<String, List<String>> hashMap, String exportPDFPath, String exportImagePath, String preyearMonth, String rptTitle,String userID) throws RemoteException, ITMException
	{

		@SuppressWarnings("unused")
		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;
		List<String> gridDataList = null;
		Image image = null;
		File attachFile    = null;
	    String imgFilePath =null;	
	    File  existImgFile =null;
	    int seconds        =0;
	    String pdfFileName =null;
	   
	    
		System.out.print("Call for PDF created");
		try
		{
			document      = new Document();
			seconds       = (int) (System.currentTimeMillis() / 1000) % 60 ;
			pdfFileName   = exportPDFPath+"ProductionVsBudget-"+userID+"-"+seconds+".pdf";			
			file          = new FileOutputStream(new File(pdfFileName));			
			writer        = PdfWriter.getInstance(document, file);
			document.open();
			
			Paragraph 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);
			
			ArrayList<String> keyList=new ArrayList<String>();
			ArrayList<List<String>> valueList=new ArrayList<List<String>>();
			
			for (Map.Entry<String, List<String>> entry : hashMap.entrySet())
			{
				keyList.add(entry.getKey());
				valueList.add(entry.getValue());
			}	
			int keyListSize=keyList.size();
			int mod= keyListSize%3;
			int devide=keyListSize/3;
			int prntTblNo=mod+devide;			
			int parentCnt=0;
			int childTblCnt=0;
			PdfPTable parentTable=null;
			int imageCounter=0;		
			
			for(int size=1;size<=prntTblNo;size++)
			{
				parentCnt++;
				parentTable = new PdfPTable(3);
				parentTable.setWidthPercentage(100);
				/*parentTable = new PdfPTable(3);*/
				PdfPTable childtable = null;
				PdfPTable table = null;
				PdfPCell cell = null;
				childTblCnt=0;
				
				for(int i= 0; i < 3; i++)
				{
					if(imageCounter < keyListSize)
					{
					childTblCnt++;
					childtable = new PdfPTable(1);
					childtable.setWidthPercentage(90);

			        imageName=keyList.get(imageCounter);	
			        gridDataList=valueList.get(imageCounter);
                        
			        imageCounter++; 
			        
			        imgFilePath = exportImagePath+imageName+userID+".png";			        
					image = Image.getInstance(imgFilePath);
					System.out.println("Image Present before  status=="+imgFilePath); 
					childtable.addCell(image);
					//code for images deleted
					existImgFile= new File(imgFilePath);
					if(existImgFile.exists()){
						existImgFile.delete();
						System.out.println("Image Deleted after status *******"+imgFilePath ); 
					}
						
					
					
	            	
					image.scaleAbsolute(320f, 250f);// image width,height
					image.setAlignment(5);
					 
					table = new PdfPTable(7);
					table.setWidthPercentage(50); // Code 2
					table.setHorizontalAlignment(Element.ALIGN_CENTER);
					Font font= FontFactory.getFont(FontFactory.TIMES_ROMAN, 5);
				 
				    cell = new PdfPCell(new Paragraph("Month",font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.GREEN);
					table.addCell(cell);
				 
				 
					cell = new PdfPCell(new Paragraph("Actual",font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.GREEN);				
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph("LBE",font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.GREEN);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph("Actual/LBE",font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.GREEN);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph("Pace(EOM)",font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.GREEN);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph("Pace(EOM)/LBE",font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.GREEN);
					table.addCell(cell);					
					cell = new PdfPCell(new Paragraph(preyearMonth,font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.GREEN);
					table.addCell(cell);
				
					cell = new PdfPCell(new Paragraph(gridDataList.get(0),font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.YELLOW);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph(gridDataList.get(1),font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.YELLOW);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph(gridDataList.get(2),font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.YELLOW);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph(gridDataList.get(3),font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.YELLOW);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph(gridDataList.get(4),font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.YELLOW);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph(gridDataList.get(5),font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.YELLOW);
					table.addCell(cell);
					
					cell = new PdfPCell(new Paragraph(gridDataList.get(6),font));
					cell.setHorizontalAlignment(Element.ALIGN_CENTER);
					cell.setBackgroundColor(Color.YELLOW);
					table.addCell(cell);				
					
					
					childtable.addCell(table);
					
					cell = new PdfPCell(childtable);
					cell.setBorder(Rectangle.NO_BORDER);
					parentTable.addCell(cell);
					}
				 }
				if(childTblCnt==1)
				{
					cell = new PdfPCell();
					cell.setBorder(Rectangle.NO_BORDER);
					parentTable.addCell(cell);
					parentTable.addCell(cell);
				}
				else if(childTblCnt==2)
				{
					cell = new PdfPCell();
					cell.setBorder(Rectangle.NO_BORDER);
					parentTable.addCell(cell);
				}
				
				document.add(parentTable);
			}
			document.close();
			file.close();
			System.out.println("PDF Created succesfully");
			System.out.println("System goes to connected database table:users");
			

				conn = connDriver.getConnectDB("DriverITM");
				connDriver         =null;
				String toMailId    =""; 
				String ccMailId    ="";
				String bccAddress  =""; 
				String subject     ="Regarding:ProductionVsBudgetReport as PDF ";
				String userWiseBodytext = "Plase find attachment of Production Vs BudgetReport PDF";				
				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("EMAIL ID  from table :users=="+toMailId);
				}
				pstmt.close();
				pstmt = null;
				conn.close();
				conn = null;
				rs.close();
				rs = null;
				
				//toMailId    ="sachin.shinde57@gmail.com"; 
				System.out.println("Export Pdf path is ="+pdfFileName);
				attachFile = new File(pdfFileName);
			    if(attachFile.exists())
			            {
							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);
						}
							
				
			System.out.println("All Activity have been completed(PDF,Mail Sending) :Thanks you");
		
			
		} catch (Exception e)
		{
			System.out.println("Exception :ProductionVsBudgetReport :generatePDF(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();

			}
		}
	}
	//end method for PDF generate 
	
	//start method for sending mail***************
	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()");
		  email.sendMail(commInfo.toString(), "ITM", null);
		  System.out.println(" Email send succesfully ***********");
		} catch (Exception e)
		{
			e.printStackTrace();
			System.out.println("Exception :ProductionVsBudgetReport :sendMail(String ,String)" + e.getMessage());
		}
		email = null;
	}
	//End method for sending mail******* 
	
}