<%@page import="java.util.ArrayList"%>
<%@page import="com.csc.library.database.StaticProperties"%>
<%@page import="java.util.Iterator"%>
<%@page import="java.util.Vector"%>
<%@page import="java.util.LinkedHashMap"%>
<%@page import="com.csc.library.report.ReportUDF"%>
<%@page import="javax.servlet.jsp.jstl.sql.Result"%>
<%@page import="java.io.FileOutputStream"%>
<%@page import="com.csc.library.utilities.MyLog"%>
<%@page import="java.io.OutputStream"%>
<%@page import="java.io.ByteArrayOutputStream"%>
<%@page import="java.net.URLDecoder"%>
<%@page import="java.text.DecimalFormat"%>
<%@page import="java.net.URLEncoder"%>
<%@page import="java.util.HashMap"%>
<%@page import="com.csc.library.database.DataSet"%>
<%@page import="com.csc.library.session.InitialInquiry"%>
<%@page import="com.csc.library.utilities.UProfile"%>
<%@page import="com.csc.library.utilities.CscCalendar"%>
<%@page import="com.csc.library.session.DbInquiry"%>
<%@page import="com.csc.library.utilities.CheckNull"%>
<%@page import="com.csc.library.entry.HelpEntry"%>
<%@page import="com.itextpdf.text.*"%>
<%@page import="com.itextpdf.text.pdf.*"%>
<%@page import="java.io.ByteArrayOutputStream" %>
<%@page import="com.csc.library.entry.HelpEntry,com.csc.library.system.*,com.csc.library.utilities.*"%>
<%@page import="com.csc.library.entry.*,com.csc.library.database.*,com.csc.library.session.*" %>
<%@page import="java.util.Iterator,com.csc.library.timeattendance.DayShift,com.csc.library.timeattendance.Shift" %>
<%@page import="com.csc.library.timeattendance.WorkingShift,com.csc.library.report.ReportUDF" %>
<%@page import="java.text.DecimalFormat,java.util.*" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<jsp:useBean id="SALEFORCEO14" class="com.csc.library.system.Task" scope="page"/>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Insert title here</title>
</head>
<body>
<%
SALEFORCEO14.setChannel(request,response);
UIManager screen = (UIManager) SALEFORCEO14.process("SingleEntry","SALEFORCEO14");
screen.getRecord().set("dayshift","");
CscCalendar cs=new CscCalendar();
ReportUDF rp=new ReportUDF();
//setHelpEntry(screen);
DecimalFormat df = new DecimalFormat("#,###,###");
DecimalFormat two = new DecimalFormat("00");
String employeeid = request.getParameter("employeeid");
String date_start = request.getParameter("date_start");
String date_end = request.getParameter("date_end");
String Buname = request.getParameter("__mbu");
String lang = request.getParameter("__language");
String chkgas = request.getParameter("gas");
String gas ="";
String setStart = cs.setCscCalendar(date_start).getYYYYMMDD();
String setEnd = cs.setCscCalendar(date_end).getYYYYMMDD();
screen.referLangOn();
screen.process();
try {
response.setContentType("application/vnd.ms-excel");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "max-age=0");
response.setHeader("Content-Disposition","attachment; filename=EXCEL001.xls");
StringBuilder data = new StringBuilder();
String test = "test";
data.append("<table>");
if(lang.equals("tha")){
data.append("<tr>");
data.append("<td><colspan>6</colspan><format>border-center-middle</format><b>true</b><font-size>12</font-size>รายงานตารางเหตุผลเข้าพบลูกค้า</td>");
data.append("</tr>");
data.append("<tr>");
data.append("<td><colspan>6</colspan><format>border-center-middle</format><font-size>12</font-size>ตั้งแต่วันที่ " +getthai(date_start)+" - " +getthai(date_end)+ "</td>");
}
else{
data.append("<tr>");
data.append("<td><colspan>6</colspan><format>border-center-middle</format><b>true</b><font-size>12</font-size>Plan visit to customer</td>");
data.append("</tr>");
data.append("<tr>");
data.append("<td><colspan>6</colspan><format>border-center-middle</format><font-size>12</font-size> " +geteng(date_start)+" - " +geteng(date_end)+ "</td>");
}
data.append("</tr>");
data.append("<tr>");
if(lang.equals("tha")){
data.append("<td><format>border-center-middle</format><width>30</width>วันที</td>");
data.append("<td><format>border-center-middle</format><width>20</width>เวลา</td>");
data.append("<td><format>border-center-middle</format><width>30</width>สถานที่ติดต่อ</td>");
data.append("<td><format>border-center-middle</format><width>30</width>เหตุผลการเข้าพบ</td>");
data.append("<td><format>border-center-middle</format><width>20</width>จังหวัด</td>");
data.append("<td><format>border-center-middle</format><width>10</width>เงินช่วยเหลือค่าพาหนะ</td>");
}
else{
data.append("<td><format>border-center-middle</format><width>30</width>DATE</td>");
data.append("<td><format>border-center-middle</format><width>20</width>TIME</td>");
data.append("<td><format>border-center-middle</format><width>30</width>Location</td>");
data.append("<td><format>border-center-middle</format><width>30</width>Subject to visit</td>");
data.append("<td><format>border-center-middle</format><width>20</width>Province</td>");
data.append("<td><format>border-center-middle</format><width>10</width>Gasfee</td>");
}
data.append("</tr>");
String tmpEmp = "";
int i=0;
String filter = "";
if(chkgas.equals("0")){
if(!employeeid.equals("")){
filter = "t.employeeid = '"+employeeid+"' and t.dateid between '"+setStart+"' and '"+setEnd+"'";
}
else{
filter = "t.dateid between '"+setStart+"' and '"+setEnd+"'";
}
}
else{
if(!employeeid.equals("")){
filter = "t.employeeid = '"+employeeid+"' and t.dateid between '"+setStart+"' and '"+setEnd+"' and t.gas not in('0.00')";
}
else{
filter = "t.dateid between '"+setStart+"' and '"+setEnd+"' and t.gas not in('0.00')";
}
}
String sql = "select BU2,EDESC from MEMPLOYEE as m inner join tmcustomer as t on m.EMPLOYEEID = t.EMPLOYEEID inner join MBU2 bu2 on m.bu2 = bu2.bu2id where "+filter+" group by BU2,bu2.edesc";
DataSet ds = new DataSet(sql,screen.getUProfile().get("dbname"),"MEMPLOYEE");
ds.initConnection(screen.getUProfile());
while(ds.next()){
String sql3 = "select bu3id,edesc from mbu3 where parent ='"+ds.getString("bu2")+"'";
DataSet ds2 = new DataSet(sql3,screen.getUProfile().get("dbname"),"MBU3");
ds2.initConnection(screen.getUProfile());
while(ds2.next()){
data.append("<tr>");
data.append("<td><colspan>6</colspan><format>border-left-middle</format><font-size>10</font-size>"+ds.getString("bu2")+" : "+ds.getString("edesc")+"</td>");
data.append("</tr>");
data.append("<tr>");
data.append("<td><colspan>6</colspan><format>border-left-middle</format><font-size>10</font-size>"+ds2.getString("bu3id")+" : "+ds2.getString("edesc")+"</td>");
data.append("</tr>");
String sqlemp="";
if(employeeid.equals("")){
sqlemp = "select m.EMPLOYEEID,fname,lname,emp_position from MEMPLOYEE m inner join TMCUSTOMER t on m.EMPLOYEEID = t.EMPLOYEEID where M.BU3 = '"+ds2.getString("bu3id")+"' group by m.EMPLOYEEID,fname,lname,emp_position";
}
else{
sqlemp = "select m.EMPLOYEEID,fname,lname,emp_position from MEMPLOYEE m inner join TMCUSTOMER t on m.EMPLOYEEID = t.EMPLOYEEID where M.BU3 = '"+ds2.getString("bu3id")+"' and m.employeeid = '"+employeeid+"' group by m.EMPLOYEEID,fname,lname,emp_position";
}
DataSet ds3 = new DataSet(sqlemp,screen.getUProfile().get("dbname"),"MEMPLOYEE");
ds3.initConnection(screen.getUProfile());
while(ds3.next()){
String position ="";
String pos = "select * from MPOSITION where POSITIONID = '"+ds3.getString("emp_position")+"'";
DataSet dsp = new DataSet(pos,screen.getUProfile().get("dbname"),"MPOSITION");
dsp.initConnection(screen.getUProfile());
if(dsp.next()){
position = dsp.getString("tdesc");
}
data.append("<tr>");
data.append("<td><colspan>6</colspan><format>border-left-middle</format><font-size>10</font-size>"+ds3.getString("employeeid")+" : "+ds3.getString("fname")+" "+ds3.getString("lname")+" "+position+"</td>");
data.append("</tr>");
String sqltm = "select * from tmcustomer where employeeid = '"+ds3.getString("employeeid")+"' and dateid between '"+setStart+"' and '"+setEnd+"' and workplace in ('1')";
DataSet ds4 = new DataSet(sqltm,screen.getUProfile().get("dbname"),"TMCUSTOMER");
ds4.initConnection(screen.getUProfile());
while(ds4.next()){
data.append("<tr>");
data.append("<td>" +ds4.getString("dateid")+"</td>");
data.append("<td>" +ds4.getString("c_tm_bg")+" - "+ds4.getString("c_tm_en")+"</td>");
data.append("<td>" +ds4.getString("con_place")+"</td>");
data.append("<td>" +ds4.getString("p_visti")+"</td>");
data.append("<td>" +ds4.getString("con_province")+"</td>");
data.append("<td>" +ds4.getString("gas")+"</td>");
data.append("</tr>");
}
}
}
i++;
}
if(i==0){
data.append("<tr>");
data.append("<td><colspan>9</colspan><format>border-center-middle</format><font-size>12</font-size>ไม่พบข้อมูล</td>");
data.append("</tr>");
}
data.append("</table>");
ExcelFreedom excel = new ExcelFreedom(data.toString(), response.getOutputStream());
//excel.setAutoSize(true);
excel.write();
} catch (Exception e) {
e.printStackTrace();
}
%>
<%!
private String getthai(String date){
CscCalendar csc = new CscCalendar();
return csc.setCscCalendar(date).getThaiFullDDMMYYYY();
}
private String geteng(String date){
CscCalendar csc = new CscCalendar();
return csc.setCscCalendar(date).getEngFull();
}
%>
</body>
</html>