PSRN001_EXP_printwriter.jsp

<%@ page import="java.io.PrintWriter,java.lang.*" %>
<%@ page contentType="application/excel" language="java" %>
<%@ page import="com.csc.library.entry.HelpEntry,com.csc.library.system.Task"%>
<%@ page import="com.csc.library.database.*,com.csc.library.session.*"  %>
<%@ page import="com.csc.library.utilities.CheckNull"%>
<%@ page import="com.csc.library.utilities.*"%>
<jsp:useBean id="PSR002" class="com.csc.library.system.Task" scope="page"/>
<%
	//response.setHeader("Cache-Control","must-revalidate");	
    response.reset();
    response.setHeader("Content-type","application/xls");	
	response.setCharacterEncoding("utf-8");
	//response.setContentType("application/vnd.ms-excel; UTF-8");
	response.setHeader("Content-Disposition", "inline; filename=" + "export.xls");
	CheckNull chk = new CheckNull();
	CscCalendar cc = new CscCalendar();
	PSR002.setChannel(request, response);
	HelpEntry screen = (HelpEntry) PSR002.process("HelpEntry","MEMPLOYEEREPORTHELP");
	screen.setMaxLine(20);
    screen.getInquiry().setParam("__active_on", "off");
	String statusFirst = chk.chkNullString(request.getParameter("statusFirst"), "false");
	String branch = chk.chkNullString(request.getParameter("branch"));
	String branchDesc = chk.chkNullString(request.getParameter("MBRANCH@TDESC"));
	String startdate = chk.chkNullString(request.getParameter("startdate"), cc.getDDMMYYYY());
	String enddate = chk.chkNullString(request.getParameter("enddate"), cc.getDDMMYYYY());
	String view = chk.chkNullString(request.getParameter("__view"));
	String filter = chk.chkNullString(request.getParameter("__filter"),"1=1");
	String allbranch = chk.chkNullString(request.getParameter("allbranch"));
	UProfile uprofile = screen.getUProfile();
	CscCalendar st = new CscCalendar(startdate);
	CscCalendar en = new CscCalendar(enddate);
	screen.process();

	if(!allbranch.equals("")){
		filter += allbranch;
	}

	if(!branch.equals("")){
		filter += " and branch = '"+branch+"'";
	}

	if(!startdate.equals("")){
		filter += " and startdate between '"+st.getYYYYMMDD()+"' and '"+en.getYYYYMMDD()+"'";
	}
%>

<%!
public static int daysBetween(CscCalendar d1, CscCalendar d2) {
  return (int) ((d2.getTime() - d1.getTime()) / (1000 * 60 * 60 * 24))+1;
 }
%>
<%!
  private boolean chkZero(String val) {
  	return val.equals("0000000")?true:false;
  }
  
  private boolean chkBlank(String val) {
  	return val.equals("")?true:false;
  }
  
  private String getDescBu(String table, String bu, String buid,UProfile up) {
  	String val = "";
  	try {
  		DbInquiry mbu = new InitialInquiry(up).getDbInquiry(table);
  		mbu.setColumn("tdesc");
  		mbu.clearOldCondition();
  		mbu.setFilter(bu + "='"+ buid +"'");
  		mbu.refresh();
		if (mbu.next()) {
			val = mbu.getString("tdesc");
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
  	return val;
  }

private String getDescPosition(String table, String po,UProfile up) {
  	String val = "";
  	try {
  		DbInquiry mbu = new InitialInquiry(up).getDbInquiry(table);
  		mbu.setColumn("tdesc");
  		mbu.clearOldCondition();
  		mbu.setFilter("positionid='"+ po +"'");
  		mbu.refresh();
		if (mbu.next()) {
			val = mbu.getString("tdesc");
		}
	} catch (Exception e) {
		e.printStackTrace();
	}
  	return val;
}
%>
<%		
		 PrintWriter op = response.getWriter();
		  StringBuffer bf=new StringBuffer();
		bf.append("<table  border=\"1\">");
		bf.append("				<tr>");
		bf.append("			<th rowspan=\"2\">ลำดับ</th>");
		bf.append("			<th rowspan=\"2\">รหัสพนักงาน</th>");
		bf.append("			<th rowspan=\"2\">ชื่อ-นามสกุล</th>");
		bf.append("			<th rowspan=\"2\">แผนก</th>");
		bf.append("			<th rowspan=\"2\">ฝ่าย</th>");
		bf.append("			<th rowspan=\"2\">วันที่เริ่มต้น</th>");
		bf.append("			<th colspan=\"4\">วันที่ครบกำหนดทดลองงาน</th>");
		bf.append("		</tr>");
		bf.append("		<tr>");
		bf.append("			<th>ครบ 60 วัน</th>");
		bf.append("			<th>ครบ 85 วัน</th>");
		bf.append("			<th>ครบ 90 วัน</th>");
		bf.append("			<th>ครบ 119 วัน</th>");
		bf.append("		</tr>");
		int no = 1;
		String sql = "select me.EMPLOYEEID,pre.TDESC+me.FNAME+' '+me.LNAME as fullname,b5.TDESC as bu5,b4.TDESC as bu4,b3.TDESC as bu3,STARTDATE from MEMPLOYEE me left join MPREFIX pre on me.EMP_PREFIX = pre.PREFIXID left join MBU3 b3 on me.BU3 = b3.BU3ID left join MBU4 b4 on me.BU4 = b4.BU4ID left join MBU5 b5 on me.BU5 = b5.BU5ID where "+filter+"";
		//out.print(sql);
		try{
			DataSet ds = new DataSet(sql,screen.getUProfile().get("dbname"),"Memployee");
			ds.initConnection(screen.getUProfile());
			while (ds.next()) {
				CscCalendar stDate = new CscCalendar(chk.chkNullString(ds.getString("startdate")));
				CscCalendar test1 = new CscCalendar(chk.chkNullString(ds.getString("startdate")));
				CscCalendar test2 = new CscCalendar(chk.chkNullString(ds.getString("startdate")));
				CscCalendar test3 = new CscCalendar(chk.chkNullString(ds.getString("startdate")));
				CscCalendar test4 = new CscCalendar(chk.chkNullString(ds.getString("startdate")));
				String testWork1 = test1.incDate(60).getDDMMYYYY() ;
				String testWork2 = test2.incDate(85).getDDMMYYYY() ;
				String testWork3 = test3.incDate(90).getDDMMYYYY() ;
				String testWork4 = test4.incDate(119).getDDMMYYYY() ;
				bf.append("	<tr>");
				bf.append("	<td align=\"center\">").append(no).append("</td>");
				bf.append("		<td align=\"center\">").append(chk.chkNullString(ds.getString("employeeid"))).append("</td>");
				bf.append("		<td align=\"left\">").append(chk.chkNullString(ds.getString("fullname"))).append("</td>");
//				bf.append("		<!-- <td align="left"><div style="width: 200px;">=chk.chkNullString(ds.getString("bu5"))></div></td> -->");
				bf.append("		<td align=\"left\">").append(chk.chkNullString(ds.getString("bu4"))).append("</td>");
				bf.append("		<td align=\"left\">").append(chk.chkNullString(ds.getString("bu3"))).append("</td>");
				bf.append("		<td align=\"center\">").append(stDate.getDDMMYYYY()).append("</td>");
				bf.append("		<td align=\"center\">").append(testWork1).append("</td>");
				bf.append("		<td align=\"center\">").append(testWork2).append("</td>");
				bf.append("		<td align=\"center\">").append(testWork3).append("</td>");
				bf.append("		<td align=\"center\">").append(testWork4).append("</td>");
				bf.append("	</tr>");
				no++;
			}
		} catch(Exception e) {
			e.printStackTrace();
		}

		bf.append("	</table>");
		op.write(bf.toString());
		op.close();
		%>