package ibase.dashboard.mfg.ejb;

import ibase.system.config.ConnDriver;
import ibase.utility.EMail;
import ibase.webitm.ejb.ValidatorEJB;
import ibase.webitm.utility.ITMException;
import java.awt.Color;
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 java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.ejb.Stateless;
import com.lowagie.text.Document;
import com.lowagie.text.Element;
import com.lowagie.text.Font;
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 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();
		Calendar cal = null;
		SimpleDateFormat sdf = null;

		try
		{
			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			cal = Calendar.getInstance();
			sdf = new SimpleDateFormat("dd-MMM-yy");
			cal.set(Calendar.DATE, cal.getActualMaximum(Calendar.DATE));
			cal = Calendar.getInstance();
			// cal.add(cal.MONTH,-1);
			cal.set(Calendar.DATE, cal.getActualMinimum(Calendar.DATE));
			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' encoding='ISO-8859-1'?><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;
			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' encoding='ISO-8859-1'?><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>");
			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 = "";

		double dailyBudgetQty = 0;
		String geol_Descr = "";
		String unit_Descr = "";
		int year = 0;
		String month = "";
		String[] tempfrmDate = null;
		String[] temptoDate = null;
		ArrayList<String> countryList = new ArrayList<String>();
		ArrayList<String> countryDescrList = new ArrayList<String>();
		int firstDayOfMonth = 0;
		int lastDayOfMonth = 0;
		listDate = new ArrayList<String>();
		xmlData = new StringBuffer("<?xml version='1.0' encoding='ISO-8859-1'?><Root>");
		xmlData.append("<Detail>");
		String country = "";
		String varValue = "";
		int count = 0;
		String geoLocationArr[] = null;
		String varValueArr[] = null;
		StringBuffer prsntGeoLocForCntry = null;

		int cntFrBudget = 0;
		String curCumQty = "";
		String missingDate = "";
		String pyrCumqty = "0.0";
		int dayCount = 0;
		String descr="";
		try
		{
			countryList.add("DWH_GEOLOC_IND");
			countryList.add("DWH_GEOLOC_US");
			countryList.add("DWH_GEOLOC_ROW");
			
			countryDescrList.add("INDIA");
			countryDescrList.add("UNITED STATES");
			countryDescrList.add("ROW");

			conn = connDriver.getConnectDB("DriverITM");
			connDriver = null;
			// change on date 11/11/2013
			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]);
			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);
			// geoLoc = "'105','110','117','145','120','129','130'";
			 geoLocationArr = geoLoc.replace("'", "").split(",");

			//////****Added new code by sachin on 06-02-15***\\\\\\\\\\

			for (int countryCnt = 0; countryCnt < countryList.size(); countryCnt++)
			{

				count = 0;
				country = countryList.get(countryCnt);
				descr="";
				sql = "select trim(var_value) as var_value, descr from disparm where var_name = ?";
				pstmt = conn.prepareStatement(sql);
				pstmt.setString(1, country);
				// End of setter statement
				rs = pstmt.executeQuery();
				if (rs.next())
				{
					varValue = checkNull(rs.getString("var_value"));
					//descr=checkNull(rs.getString("descr"));
				}
				pstmt.close();
				pstmt = null;
				rs.close();
				rs = null;
                descr=countryDescrList.get(countryCnt);
				//varValue = "110,105,200";
				varValueArr = varValue.split(",");
				prsntGeoLocForCntry = new StringBuffer();
				for (int i = 0; i < geoLocationArr.length; i++)
				{
					for (int j = 0; j < varValueArr.length; j++)
					{
						if (geoLocationArr[i].equalsIgnoreCase(varValueArr[j]))
						{
							if (prsntGeoLocForCntry.length() != 0)
							{
								prsntGeoLocForCntry.append(",'" + varValueArr[j] + "'");
							} else
							{
								prsntGeoLocForCntry.append("'" + varValueArr[j] + "'");
							}
						}
					}
				}
				geoLoc = prsntGeoLocForCntry.toString();
				if(geoLoc!=null && geoLoc.trim().length() > 0)
				{
				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','GRANULES') 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();
				cntFrBudget = 0;
				curCumQty = "";
				missingDate = "";
				pyrCumqty = "0.0";
				dayCount = 0;

				xmlData.append("<country_code id=\"" + country + "\"   descr=\"" + descr + "\">");
				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++;
				}
				pstmt.close();
				pstmt = null;
				rs.close();
				rs = null;

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

						for (int p = dayCount; p < lastDayOfMonth; p++)
						{
							cntFrBudget++;
							// 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("</country_code>");
			}
			}
			xmlData.append("</Detail>");
			xmlData.append("</Root>");

			

		} 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);
			}
		}

		System.out.println("xmlData.toString() is" + xmlData.toString());
		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 String generatePDF(HashMap<String, List<String>> hashMap, String exportPDFPath, String exportImagePath, String preyearMonth, String rptTitle, String userID, HashMap<String, List<String>> chartNameMap,HashMap<String, String> cntryIdNDescrMap) throws RemoteException, ITMException
	{
		//	System.out.print("Country list in generate pdf="+countryList  );
		System.out.print("hashMap is=" + hashMap);
		System.out.print("chartNameMap   is  =" + chartNameMap);
		@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;

		String pdfFileName = null;
		String toMailId = "";
		String ccMailId = "";
		String bccAddress = "";
		String subject = "";
		String userWiseBodytext = "";
		String resultFlage = "false";
		int keyListSize = 0;
		int mod = 0;
		int devide = 0;
		int prntTblNo = 0;
		int parentCnt = 0;
		int childTblCnt = 0;
		PdfPTable parentTable = null;
		int imageCounter = 0;
		String country = "";
		SimpleDateFormat simpleDateFormat = null;
		Calendar calendar = null;
		String currDate = "";
		Paragraph preface = null;
		Paragraph countryTitle = null;
		List<String> countyWiseDataList = new ArrayList<String>();
		System.out.print("Calling for PDF  created step1");
		try
		{
			simpleDateFormat = new SimpleDateFormat("dd-MMM-yy");
			calendar = Calendar.getInstance();
			currDate = simpleDateFormat.format(calendar.getTime());
			document = new Document();

			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);

			//ArrayList<List<String>> valueList = new ArrayList<List<String>>();

			for (Map.Entry<String, List<String>> entry : chartNameMap.entrySet())
			{
				country = entry.getKey();
				String countryDescr=cntryIdNDescrMap.get(country);
				countryTitle = new Paragraph();
				countryTitle.setAlignment(Element.ALIGN_CENTER);
			//	countryTitle.add(new Paragraph(countryDescr, FontFactory.getFont(FontFactory.TIMES_ROMAN, 12)));
				countryTitle.add(new Paragraph(countryDescr, FontFactory.getFont(FontFactory.TIMES_ROMAN, 12)));
				countryTitle.add(new Paragraph(" "));
				//PdfPTable	countryLblTbl = new PdfPTable(1);
				//countryLblTbl.addCell(countryTitle);
				
		      	document.add(countryTitle);
				System.out.println(" countryDescr is  = " + countryDescr);
				countyWiseDataList = entry.getValue();
				
				//keyList.add(entry.getKey());
				//countyWiseDataList.add(hashMap.get(entry.getKey()));
				
				keyListSize = countyWiseDataList.size();
				System.out.println(" keyListSize is  = " + keyListSize);
				mod = keyListSize % 3;
				devide = keyListSize / 3;
				prntTblNo = mod + devide;
				parentCnt = 0;
				childTblCnt = 0;
				imageCounter = 0;

				System.out.println("prntTblNo is =  " + prntTblNo);
				for (int size = 1; size <= prntTblNo; size++)
				{
					parentCnt++;
					parentTable = new PdfPTable(3);
					parentTable.setWidthPercentage(100);
					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 = countyWiseDataList.get(imageCounter);
							System.out.println("imageName is  ==" + imageName);
							gridDataList = hashMap.get(imageName);
							//Add by Birendra Pandey   
							imageCounter++;

							//imgFilePath = exportImagePath + imageName + userID + ".png";
							imgFilePath = exportImagePath + imageName + ".png";

							System.out.println("Image name with image path ==" + imageName + "  imgFilePath==" + imgFilePath);

							image = Image.getInstance(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("Work Plan", font));
							cell.setHorizontalAlignment(Element.ALIGN_CENTER);
							cell.setBackgroundColor(Color.GREEN);
							table.addCell(cell);

							cell = new PdfPCell(new Paragraph("Actual/Work Plan", 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)/Work Plan", 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);
							//2
							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.newPage();
			}
			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 =" + userID + "AND According to Email ID=======" + 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() && 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("*********** Pdf and sending mail done succesfully (Final)**************");
		} 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();

			}
		}
		return resultFlage;
	}

	//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******* 
}