
<%@page import = "org.jpedal.*,org.jpedal.fonts.*"%>
<%@page import = "org.jpedal.exception.PdfException"%>
<%@page import="java.awt.Image, javax.imageio.ImageIO,java.awt.image.*,java.util.*"%>
<%@page import="java.io.*,java.sql.*,ibase.system.config.ConnDriver,ibase.utility.CommonConstants"%>
<%!

String siteCode= "",empCode="",namePrefix="",empFname="",empLname="",desig="",itemSer="",emailId="",reportTo="",passportNo="";
String birthPlace="",placeOfIssue="",mealPref="",empCodeTmHd="",stateCode="",zoneCode="",hub="";		
String location="",confirmed="",empMname="";
String tranID="",nsmName="";
String fileType="",str="",siteDescr="";
String add1="",add2="",add3="",pinPass="",countName="",nationality="",grpCode="";
String city="",stateDescr="",tel1="",mobNo="",passportFullName="",status="";
java.sql.Date travelDateFrom=null,travelDateTo=null,tranDate=null,birthDate=null,dateOfIssue=null,validUpto=null,confDate=null;

%>
<%
	System.out.println("Calling empTravelInfo.jsp");
	String sql="";
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	Connection conn=null;
	Statement stSuper = null;
	ResultSet rsSuper =null;
	ConnDriver ConnDriver = new ConnDriver();
	StringBuffer xmlBuff=new StringBuffer();
	FileWriter fw = null;
	tranID =  request.getParameter("tran_id") == null ?"0001": request.getParameter("tran_id");
	System.out.println("tranID--receiped from jsp 7/APR/14--["+tranID+"]");
	File file = new File(CommonConstants.JBOSSHOME + "/server/default/deploy/ibase.ear/ibase.war/webitm/tempDoc/");
	int cnt = 0;
	String refSer1 = "";
	Blob image = null;String fileName="";
	byte[] imgData = null;Object obj = null;
	boolean flag = false;
	try
		{
				FileOutputStream fos = null;
				
				boolean deleteDir = delete(file);
				if(deleteDir)
				System.out.println("file  deleted successfully");
				if(!file.exists())
				{
					System.out.println(" directory cretated");
					file.mkdirs(); 
				}
			
			sql= " SELECT TRAN_DATE,SITE_CODE,EMP_CODE,NAME_PREFIX,EMP_FNAME,EMP_MNAME,EMP_LNAME,DESIGNATION,ITEM_SER,EMAIL_ID,REPORT_TO,PASSPORT_NO, " +
				  " BIRTH_DATE,BIRTH_PLACE,DATE_OF_ISSUE,PLACE_OF_ISSUE,VALID_UPTO,MEAL_PREF,EMP_CODE__TEAM_HEAD,STATE_CODE__WORK,ZONE_CODE,HUB, "+
				  " LOCATION,CONFIRMED,CONF_DATE,ADDR1__PASS,ADDR2__PASS,ADDR3__PASS,city__pass,state_descr__pass,cur_tel1,mobile_no,emp_name__pass,pin__pass,count_name,nationality,status  FROM EMP_TRAVEL_INFO WHERE TRAN_ID = ? ";
				conn = ConnDriver.getConnectDB("DriverITM");
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1,tranID);
				System.out.println( "@@@@@ sqlgetdata :: [[" + sql+"]]" );
				rs = pstmt.executeQuery(  );
				if(rs.next())
				{
					tranDate= rs.getDate("TRAN_DATE");
					siteCode= checkNull(rs.getString("SITE_CODE"));
					empCode= checkNull(rs.getString("EMP_CODE"));
					namePrefix= checkNull(rs.getString("NAME_PREFIX"));
					empFname= checkNull(rs.getString("EMP_FNAME"));
					empMname= checkNull(rs.getString("EMP_MNAME"));
					empLname= checkNull(rs.getString("EMP_LNAME"));
					desig= checkNull(rs.getString("DESIGNATION"));
					itemSer= checkNull(rs.getString("ITEM_SER"));
					emailId= checkNull(rs.getString("EMAIL_ID"));
					reportTo= checkNull(rs.getString("REPORT_TO"));
					passportNo= checkNull(rs.getString("PASSPORT_NO"));
					birthDate= rs.getDate("BIRTH_DATE");
					birthPlace= checkNull(rs.getString("BIRTH_PLACE"));
					dateOfIssue= rs.getDate("DATE_OF_ISSUE");
					placeOfIssue= checkNull(rs.getString("PLACE_OF_ISSUE"));
					validUpto= rs.getDate("VALID_UPTO");
					mealPref= checkNull(rs.getString("MEAL_PREF"));
					empCodeTmHd= checkNull(rs.getString("EMP_CODE__TEAM_HEAD"));
					stateCode= checkNull(rs.getString("STATE_CODE__WORK"));
					zoneCode= checkNull(rs.getString("ZONE_CODE"));
					hub= checkNull(rs.getString("HUB"));
					location= checkNull(rs.getString("LOCATION"));
					confirmed= checkNull(rs.getString("CONFIRMED"));
					confDate= rs.getDate("CONF_DATE");
					add1= checkNull(rs.getString("ADDR1__PASS"));
					add2= checkNull(rs.getString("ADDR2__PASS"));
					add3= checkNull(rs.getString("ADDR3__PASS"));
					city= checkNull(rs.getString("city__pass"));
					stateDescr= checkNull(rs.getString("state_descr__pass"));
					tel1= checkNull(rs.getString("cur_tel1"));
					mobNo= checkNull(rs.getString("mobile_no"));
					passportFullName= checkNull(rs.getString("emp_name__pass"));
					pinPass = checkNull(rs.getString("pin__pass"));
					countName = checkNull(rs.getString("count_name"));
					nationality = checkNull(rs.getString("nationality"));
					status = checkNull(rs.getString("status"));
					if("V".equalsIgnoreCase(mealPref))
						mealPref = "Veg";
					else
						mealPref="Nonveg";
				}

				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				sql = " select group_code,travel_date__from,travel_date__to from nsm_email where division = ? ";
				conn = ConnDriver.getConnectDB("DriverITM");
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1,itemSer);
				System.out.println( "@@@@@ sqlgetdata :: [[" + sql+"]]" );
				rs = pstmt.executeQuery(  );
				if(rs.next())
				{
					grpCode = rs.getString("group_code");
					travelDateFrom = rs.getDate("travel_date__from");
					travelDateTo = rs.getDate("travel_date__to");
				}
				
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				sql = " select descr from site where site_code = ? ";
				conn = ConnDriver.getConnectDB("DriverITM");
				pstmt = conn.prepareStatement( sql );
				pstmt.setString(1,siteCode);
				System.out.println( "@@@@@ sqlgetdata :: [[" + sql+"]]" );
				rs = pstmt.executeQuery(  );
				if(rs.next())
				{
					siteDescr = rs.getString("descr");
				}
				
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				
				sql = " SELECT ref_ser FROM TRANSETUP WHERE TRAN_WINDOW = 'w_emp_travel_info' ";
				pstmt = conn.prepareStatement( sql );
				System.out.println( "@@@@@ sqlgetdata :: [[" + sql+"]]" );
				rs = pstmt.executeQuery(  );
				if(rs.next())
				{
					refSer1 = rs.getString("ref_ser");
				}
				
				rs.close();
				rs = null;
				pstmt.close();
				pstmt = null;
				System.out.println( "@@@@@ refSer :: [[" + refSer1+"]]" );
				stSuper = conn.createStatement();
				rsSuper = stSuper.executeQuery(" select doc_object,doc_type,doc_name  from doc_contents where doc_id in (select doc_id from doc_transaction_link where ref_id  =  '"+tranID+"' and ref_ser = '"+refSer1+"')");
				
				while (rsSuper.next()) 
				{
					cnt ++;
					flag=true;
					image = rsSuper.getBlob(1);
					fileType = rsSuper.getString(2);
					fileName = rsSuper.getString(3);
					imgData = image.getBytes(1, (int) image.length());
// 					fileName = fileName.substring(0,fileName.indexOf("."));System.out.println("File Name[received]]"+ fileName +"]");
					System.out.println(" length of byte data :"+(int) image.length());
			//		response.setContentType("image/"+fileType);
					fos = new FileOutputStream(CommonConstants.JBOSSHOME+"/server/default/deploy/ibase.ear/ibase.war/webitm/tempDoc/"+fileName);
					fos.write(imgData);
				}
				if(fos !=null)
				fos.close();
				System.out.println("No.of attached document ::["+cnt+"]");
				if( conn != null )
					conn.close();
				conn = null;
			}catch( Exception ex )
			{
				ex.printStackTrace();
				System.out.println( "@@@@@ Exception occured:: " );
				
			}finally{
				try{
					if( rs != null )
						rs.close();
					rs = null;
					if( rsSuper != null )
						rsSuper.close();
					rsSuper = null;
					if( stSuper != null )
						stSuper.close();
					stSuper = null;
					if( pstmt != null )
						pstmt.close();
					pstmt = null;
					if( conn != null )
						conn.close();
					conn = null; 
				}catch( Exception ex ){}
				
			}
	
%>

<%! private String checkNull(String inp)
{
	if(inp == null)
		inp = " ";
	return inp.trim();
}
%>
<center>
<div style="height: 100%;overflow:auto;">
<fieldset STYLE="width:800PX;border-color:#99FF33">
<head><title>Employee travel information:</title></br></head>
<body>
<div STYLE= 'font-family:arial;color:black;background-color:#48D1CC;POSITION:relative;TOP:1;'><h5>Employee information:</h5></div>
<!-- <table> -->
<!-- <tr> -->
<table  style='font-family:arial;color:black;font-size:15px;'>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Tran Id :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=tranID%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right"  style = "width:300px">Devision : </td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=itemSer%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right"  style = "width:300px">Tran Date : </td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=tranDate==null?" ":tranDate%></td></tr>
	
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Site Code : </td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=siteCode%>
	</tr>
	<tr><td align="right" style = "width:300px"></td><td align="left" STYLE= 'background-color:#D3D3D3;POSITION:relative;TOP:1;left:20;width:1000'><%=siteDescr%>
	</tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Emp Code : </td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=empCode%>
	<tr><td align="right" style = "width:300px"></td><td align="left" STYLE= 'background-color:#D3D3D3;POSITION:relative;TOP:1;left:20;width:1000'><%=empFname+"  "+empMname%>
	</tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Report To :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=reportTo%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Nsm Code :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=empCodeTmHd%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Designation :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=desig%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Email Id :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=emailId%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Address :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=add1%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px"></td><td align="left"><%=add2%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px"></td><td align="left"><%=add3%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">City :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=city%></td></tr>
	<tr><td align="right" style = "width:300px"></td><td align="left" STYLE= 'background-color:#D3D3D3;POSITION:relative;TOP:1;left:20;width:1000'><%=stateDescr%>
	</tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Postal Code :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=pinPass%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Telephone :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=tel1%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Mobile :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=mobNo%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Confirmed :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=confirmed%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Conf Date :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=confDate==null?" ":confDate%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Status :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=status==null?" ":confDate%></td></tr>
</table>
<!-- </tr> -->
<!-- <tr> -->
<!-- <table> -->
<!-- <tr style = "height:20px"></tr> -->
<!-- <tr  STYLE= 'font-family:arial;color:black;POSITION:relative;TOP:1;left:20;width:1000'><td><h5>Passport Information:</h5></td></tr> -->
<!-- </table> -->
<!-- <!-- </tr> -->
<!-- <tr> -->
<div STYLE= 'font-family:arial;color:black;background-color:#48D1CC;POSITION:relative;TOP:1;'><h5>Travel information:</h5></div>
<table  style='font-family:arial;color:black;font-size:15px;'>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Group Code :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=grpCode%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Date of travel from :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=travelDateFrom==null?" ":travelDateFrom%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Date of travel to :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=travelDateTo==null?" ":travelDateTo%></td></tr>
</table>


<div STYLE= 'font-family:arial;color:black;background-color:#48D1CC;POSITION:relative;TOP:1;'><h5>Passport information:</h5></div>
<div align = 'left' STYLE= 'font-family:arial;color:#FF0000;font-size:15px;font-style: bold;'>Please enter below detail he is as per show in passport</div>
<table  style='font-family:arial;color:black;font-size:15px;'>

<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Title :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=namePrefix%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Surname :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=empLname%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Given name :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=passportFullName%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Birth Date :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=birthDate==null?" ":birthDate%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Birth Place :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=birthPlace%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Passport No :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=passportNo%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Meal Pref :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=mealPref%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Date Of Issue :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=dateOfIssue==null?" ":dateOfIssue%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Valid Upto :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=validUpto==null?" ":validUpto%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Place of Issue :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=placeOfIssue%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Country :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=countName%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Nationality :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=nationality%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">State Code Work :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=stateCode%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Zone Code :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=zoneCode%></td></tr>
<tr STYLE= 'background-color:#D3D3D3;'><td align="right" style = "width:300px">Hub :</td><td align="left"><%=hub%></td></tr>
<tr STYLE= 'background-color:#DCDCDC;'><td align="right" style = "width:300px">Location :</td><td align="left" STYLE= 'POSITION:relative;TOP:1;left:20;width:1000'><%=location%></td></tr>
</table>
<!-- </tr> -->

<!-- <tr> -->
<h5 style='font-family:arial;color:black;background-color:#48D1CC;'>List of attachment:</h5>

<%

	File folder  = new File(CommonConstants.JBOSSHOME + "/server/default/deploy/ibase.ear/ibase.war/webitm/tempDoc/");
	folder.listFiles();
	String fileExtn = "",filename="",filePrefix="";
	
	
	if(flag)
	{
		out.println("<table align='center' STYLE= 'POSITION:relative;TOP:1;'>");
		out.println("<tr>");
		for (final File fileEntry : folder.listFiles()) 
		{
			filename=  fileEntry.getName();
			fileExtn = filename.substring(filename.indexOf("."),filename.length());
			System.out.println("fie exten.["+fileExtn+"] file name :["+filename+"]");
			out.println("<figure><td  align='left'>");
			if(".jpg".equalsIgnoreCase(fileExtn.trim()) || ".png".equalsIgnoreCase(fileExtn.trim()) || ".jpeg".equalsIgnoreCase(fileExtn.trim()) || ".bmp".equalsIgnoreCase(fileExtn.trim()) || ".gif".equalsIgnoreCase(fileExtn.trim()))
			{
				out.println("<img src = '../tempDoc/"+filename+"' width='80px' height='100px'/></br>");
				out.println("<FONT SIZE='2'><a style='text-decoration: none;color:#696969;' width='100px' height='100px'  href= '../tempDoc/"+filename+"' target = '_blank'> "+filename+" </a></font>");
			}
			else
			{
				filePrefix = pdftoimage(filename);
				out.println("<img src = '../tempDoc/"+filePrefix+".bmp ' width='100px' height='100px'/></br>");
				out.println("<FONT SIZE='2'><a style='text-decoration: none;color:#696969;' width='100px' height='100px'  href= '../tempDoc/"+filename+"' target = '_blank'> "+filename+" </a></font>");
// 				out.println("<iframe src='../tempDoc/"+filename+"' width='500' height='500'/>");
			}
			out.println("</td>");
			out.println("<td height='20' width='20'>");
			out.println("</td></figure>");
		}
		out.println("</tr>");
		out.println("</table>");
		
	}else
	{
		out.println("<div STYLE= 'POSITION:relative;TOP:1;left:20;width:500'>No attachment found.</div>");
	}

%>
<!-- </tr></table> -->
<%!
public static boolean delete(File file)
{

	boolean flag= false;
// Check if file is directory/folder
	System.out.println("delete(File file) called");
	if(file.exists())
	{
		System.out.println(" directory found ");
	// Get all files in the folder
	File[] files=file.listFiles();
	if(files != null){
	for(int i=0;i<files.length;i++)
	{
		System.out.println(" files  found ");
	// Delete each file in the folder
	delete(files[i]);
	
	}}
	// Delete the folder
	flag = file.delete();
	}
	return flag;
// 	else
// 	{
	
// 	// Delete the file if it is not a folder
// 	return file.delete();
// 	}
}
%>
<%!
private String pdftoimage(String filename) throws IOException
{
	String filePrefix = filename.substring(0,filename.indexOf("."));

	System.out.println("pdftoimage called ");
	try{
		System.out.println("File prefix name ["+filePrefix+"]");
		PdfDecoder decode_pdf = new PdfDecoder(true);

		FontMappings.setFontReplacements();

		decode_pdf.openPdfFile(CommonConstants.JBOSSHOME+"/server/default/deploy/ibase.ear/ibase.war/webitm/tempDoc/"+filename); 

		decode_pdf.setExtractionMode(0, 1f); //do not save images
		BufferedImage img=decode_pdf.getPageAsImage(1);

		decode_pdf.closePdfFile();
		ImageIO.write(img, "bmp", new File(CommonConstants.JBOSSHOME+"/server/default/deploy/ibase.ear/ibase.war/webitm/tempDoc/"+filePrefix+".bmp"));
		return filePrefix;
		}
		catch(PdfException ex)
		{	
			System.out.println(" exception occuer");
			return filePrefix="";
		}
}
%>

