<%@ 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(); %>