<%@page import="com.csc.library.utility.DHMConvert"%>
<%@page import="java.util.ArrayList"%>
<%@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.session.InitialEnvironment" %>
<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<jsp:useBean id="EMV" class="com.csc.library.system.Task" scope="page" />
<jsp:setProperty name="EMV" property="appname" value="EMPVIEW" />
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>Insert title here</title>
</head>
<body>
Not found data
<%
EMV.setChannel(request, response);
HelpEntry screen = (HelpEntry)EMV.process("HelpEntry", "MCOMPANYHELP");
screen.setMaxLine(-1);
CheckNull chk = new CheckNull();
setHelpEntry(screen);
screen.process();
ByteArrayOutputStream baos = new ByteArrayOutputStream();
Document document = new Document(PageSize.A4.rotate(), 20, 22, 74, 36);
PdfWriter.getInstance(document, baos);
document.open();
document.addTitle("Document Title");
BaseFont bf = BaseFont.createFont(application.getRealPath("FONTS/Cordia.ttf"), BaseFont.IDENTITY_H, BaseFont.EMBEDDED);
BaseFont bfHead = BaseFont.createFont(application.getRealPath("FONTS/Cordia.ttf"), BaseFont.IDENTITY_H, BaseFont.EMBEDDED, true);
Font font = new Font(bf, 12);
Font boldFont = new Font(bfHead, 12, Font.BOLD);
Font fontHead = new Font(bfHead, 14);
Font boldHead = new Font(bfHead, 16, Font.BOLD);
//fontHead.setStyle(1); // ตัวหนา
// set vlign middle
Phrase content = new Phrase("Blah blah blah", font);
Float fontSize = content.getFont().getSize();
Float capHeight = content.getFont().getBaseFont().getFontDescriptor(BaseFont.CAPHEIGHT, fontSize);
Float padding = 5f;
boolean statusData = false;
String bu1 = chk.chkNullString(request.getParameter("bu1"));
String bu2 = chk.chkNullString(request.getParameter("bu2"));
String bu3 = chk.chkNullString(request.getParameter("bu3"));
String bu4 = chk.chkNullString(request.getParameter("bu4"));
String bu5 = chk.chkNullString(request.getParameter("bu5"));
String startMonth = chk.chkNullString(request.getParameter("startMonth"));
String startYear = chk.chkNullString(request.getParameter("startYear"));
String endMonth = chk.chkNullString(request.getParameter("endMonth"));
String endYear = chk.chkNullString(request.getParameter("endYear"));
String filter = chk.chkNullString(request.getParameter("__filter"),"1=1");
UProfile uprofile = screen.getUProfile();
DecimalFormat df = new DecimalFormat("###,##0.00");
if(!bu1.equals("")){
filter += " and me.bu1='"+bu1+"'";
}
if(!bu2.equals("")){
filter += " and me.bu2='"+bu2+"'";
}
if(!bu3.equals("")){
filter += " and me.bu3='"+bu3+"'";
}
if(!bu4.equals("")){
filter += " and me.bu4='"+bu4+"'";
}
if(!bu5.equals("")){
filter += " and me.bu5='"+bu5+"'";
}
if(screen.getInquiry().recCount() > 0) {
try{
PdfPTable detail = new PdfPTable(9);
detail.setWidthPercentage(100);
detail.setWidths(sizeColumn);
PdfPCell cellDetail;
String sql="select me.bu4 as bu4id,b4.TDESC as bu4 from MEMPLOYEE me left join MBU4 b4 on me.BU4=b4.BU4ID where "+filter+" group by me.bu4,b4.TDESC";
//out.print(sql.toString());
DataSet ds = new DataSet(sql,screen.getUProfile().get("dbname"),"Memployee");
ds.initConnection(screen.getUProfile());
String tmpCase = "";
String tmpBu = "";
int no = 1;
String descEmp = "จำนวนพนักงานตามแผนกำลัง(คน)\n"
+"จำนวนพนักงานลาออก(คน)\n"
+"Tumover rate(%)";
while (ds.next()) {
statusData = true;
String stDate = "01-"+startMonth+"-"+startYear;
String enDate = "01-"+endMonth+"-"+endYear;
CscCalendar st = new CscCalendar(stDate);
CscCalendar en = new CscCalendar(enDate);
cellDetail = new PdfPCell(new Phrase(ds.getString("bu4"), font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_LEFT);
detail.addCell(cellDetail);
cellDetail = new PdfPCell(new Phrase(descEmp, font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_LEFT);
detail.addCell(cellDetail);
int x = 0;
String EMP ="";
String OUT ="";
String SUM ="";
String sumOUT = "";
double totalEMP = 0.0;
double totalOUT = 0.0;
double count = 0;
while(st.beforeEquals(en)){
EMP = getEmp(String.valueOf(st.getYYYYMMDD()),chk.chkNullString(ds.getString("bu4id")),uprofile);
OUT = getOut(String.valueOf(st.getYYYYMMDD()),chk.chkNullString(ds.getString("bu4id")),uprofile);
if(!EMP.equals("0")){
SUM = df.format(Double.parseDouble(OUT)/Double.parseDouble(EMP));
}else{
SUM = "0.00";
}
String desc = EMP+"\n"+OUT+"\n"+SUM+"%";
cellDetail = new PdfPCell(new Phrase(desc, font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
detail.addCell(cellDetail);
totalEMP+=Double.parseDouble(EMP);
totalOUT+=Double.parseDouble(OUT);
st.incMonth(1);
x++;
count++;
}
if(x!=6){
while(x<6){
cellDetail = new PdfPCell(new Phrase("", font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_LEFT);
detail.addCell(cellDetail);
x++;
}
}
String empTotal = df.format(totalEMP/count);
String outTotal = df.format(totalOUT/count);
if(totalEMP!=0.0){
sumOUT = df.format((Double.parseDouble(String.valueOf(totalOUT))/Double.parseDouble(String.valueOf(totalEMP))));
}else{
sumOUT = "0.00";
}
String descTotal = empTotal+"\n"+outTotal+"\n"+sumOUT+"%";
cellDetail = new PdfPCell(new Phrase(descTotal, font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
detail.addCell(cellDetail);
no++;
}
/*-----------------------------------------------TOTAL------------------------------------------------------*/
String stDate2 = "01-"+startMonth+"-"+startYear;
String enDate2 = "01-"+endMonth+"-"+endYear;
CscCalendar st2 = new CscCalendar(stDate2);
CscCalendar en2 = new CscCalendar(enDate2);
cellDetail = new PdfPCell(new Phrase("Total", font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_LEFT);
detail.addCell(cellDetail);
cellDetail = new PdfPCell(new Phrase(descEmp, font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_LEFT);
detail.addCell(cellDetail);
int x2 = 0;
String EMP2 ="";
String OUT2 ="";
String SUM2 ="";
String sumOUT2 = "";
double totalEMP2 = 0.0;
double totalOUT2 = 0.0;
double count2 = 0;
while(st2.beforeEquals(en2)){
EMP2 = getEmp(String.valueOf(st2.getYYYYMMDD()),"all",uprofile);
OUT2 = getOut(String.valueOf(st2.getYYYYMMDD()),"all",uprofile);
if(!EMP2.equals("0")){
SUM2 = df.format(Double.parseDouble(OUT2)/Double.parseDouble(EMP2));
}else{
SUM2 = "0.00";
}
String desc2 = EMP2+"\n"+OUT2+"\n"+SUM2+"%";
cellDetail = new PdfPCell(new Phrase(desc2, font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
detail.addCell(cellDetail);
totalEMP2+=Double.parseDouble(EMP2);
totalOUT2+=Double.parseDouble(OUT2);
st2.incMonth(1);
x2++;
count2++;
}
if(x2!=6){
while(x2<6){
cellDetail = new PdfPCell(new Phrase("", font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_LEFT);
detail.addCell(cellDetail);
x2++;
}
}
String empTotal2 = df.format(totalEMP2/count2);
String outTotal2 = df.format(totalOUT2/count2);
if(totalEMP2!=0.0){
sumOUT2 = df.format((Double.parseDouble(String.valueOf(totalOUT2))/Double.parseDouble(String.valueOf(totalEMP2))));
}else{
sumOUT2 = "0.00";
}
String descTotal2 = empTotal2+"\n"+outTotal2+"\n"+sumOUT2+"%";
cellDetail = new PdfPCell(new Phrase(descTotal2, font));
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
detail.addCell(cellDetail);
document.add(detail);
ds.closeConnection();
} catch(Exception e) {
e.printStackTrace();
}
}
if(!statusData) {
Paragraph p = new Paragraph("\n\n\nไม่พบข้อมูล",fontHead);
p.setAlignment(Element.ALIGN_CENTER);
document.add(p);
}
document.close();
OutputStream os = response.getOutputStream();
// Create a reader
PdfReader reader = new PdfReader(baos.toByteArray());
// Create a stamper
PdfStamper stamper = new PdfStamper(reader,baos);
// Loop over the pages and add a header to each page
int n = reader.getNumberOfPages();
for (int i = 1; i <= n; i++) {
getHeaderTable(i, n, font, boldFont,boldHead,startMonth,startYear,endMonth,endYear).writeSelectedRows(0, -1, 20, 550, stamper.getOverContent(i)); //ไม่รู้ ซ้าย ขวา ล่าง
}
// Close the stamper
stamper.close();
reader.close();
response.setHeader("Expires", "0");
response.setHeader("Cache-Control","must-revalidate, post-check=0 , pre-check=0");
response.setHeader("Pragma", "public");
// setting the content type
response.setContentType("application/pdf");
// the contentlength
response.setContentLength(baos.size());
// write ByteArrayOutputStream to the ServletOutputStream
baos.writeTo(os);
os.flush();
os.close();
%>
<%!
String startdate = "";
String enddate = "";
String buDesc = "";
private void setStartdate(String startdate) {
this.startdate = startdate;
}
private void setEnddate(String enddate) {
this.enddate = enddate;
}
private void setBuDesc(String buDesc) {
this.buDesc = buDesc;
}
float[] sizeColumn = new float[] {3.0f,5.0f,1.0f,1.0f,1.0f,1.0f,1.0f,1.0f,1.0f};
private PdfPTable getHeaderTable(int x, int y,Font font,Font fontHead,Font boldHead,String startMonth,String startYear,String endMonth,String endYear) {
try{
Phrase content = new Phrase("Blah blah blah", font);
Float fontSize = content.getFont().getSize();
Float capHeight = content.getFont().getBaseFont().getFontDescriptor(BaseFont.CAPHEIGHT, fontSize);
Float padding = 3f;
InitialEnvironment en = new InitialEnvironment("GLOBAL");
String pathImage = en.getValue("EXCEL-DIR");
//BaseColor color = new BaseColor(red, green, blue);
PdfPTable detail = new PdfPTable(9);
PdfPCell cellDetail;
detail.setTotalWidth(800);
detail.setWidths(sizeColumn);
detail.getDefaultCell().setHorizontalAlignment(Element.ALIGN_LEFT);
String header = "";
if(x==1){
header = " รายงานสรุปการลาออกแยกตามแผนก (พนักงานรายวัน) ปี "+startYear;
}else{
header = " ";
}
cellDetail = new PdfPCell(new Phrase(String.format(header, x, y),fontHead));
cellDetail.setHorizontalAlignment(Element.ALIGN_LEFT);
cellDetail.setBorder(Rectangle.NO_BORDER);
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
cellDetail.setColspan(9);
detail.addCell(cellDetail);
// ------------------ head detail ----------------------
cellDetail = new PdfPCell(new Phrase(String.format("แผนก", x, y),fontHead));
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
detail.addCell(cellDetail);
cellDetail = new PdfPCell(new Phrase(String.format("จำนวนพนักงาน(คน)", x, y),fontHead));
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
detail.addCell(cellDetail);
String stDate = "01-"+startMonth+"-"+startYear;
String enDate = "01-"+endMonth+"-"+endYear;
CscCalendar st = new CscCalendar(stDate);
CscCalendar ed = new CscCalendar(enDate);
int count = 0;
while(st.beforeEquals(ed)){
cellDetail = new PdfPCell(new Phrase(String.format(st.getEngShortMonth()+"-"+st.getEngYY(), x, y),fontHead));
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
detail.addCell(cellDetail);
st.incMonth(1);
count++;
}
if(count!=6){
while(count<6){
cellDetail = new PdfPCell(new Phrase(String.format("", x, y),fontHead));
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
detail.addCell(cellDetail);
count++;
}
}
cellDetail = new PdfPCell(new Phrase(String.format("Total", x, y),fontHead));
cellDetail.setHorizontalAlignment(Element.ALIGN_CENTER);
cellDetail.setPadding(padding);
cellDetail.setPaddingTop(capHeight - fontSize + padding);
detail.addCell(cellDetail);
// --------------- new line -----------------
return detail;
}catch(Exception e){
e.printStackTrace();
}
return null;
}
%>
<%!
HelpEntry screen;
private void setHelpEntry(HelpEntry screen) {
this.screen = screen;
}
private String getEmp(String date,String bu4,UProfile up) {
String filter = "";
if(!bu4.equals("all")){
filter =" and BU4 = '"+bu4+"' ";
}
String emp = "";
CscCalendar csc = new CscCalendar(date);
String month = csc.getYear()+"-"+csc.getMM();
String sql = "select COUNT(1) as emp from MEMPLOYEE where RESIGNDATE > '"+month+"-31' "+filter+" ";
try{
DataSet ds = new DataSet(sql,up.get("dbname"),"Memployee");
ds.initConnection(up);
while (ds.next()) {
emp = ds.getString("emp");
}
ds.closeConnection();
}catch(Exception e) {
e.printStackTrace();
}
return emp;
}
private String getOut(String date,String bu4,UProfile up) {
String filter = "";
if(!bu4.equals("all")){
filter =" and BU4 = '"+bu4+"' ";
}
String emp = "";
CscCalendar csc = new CscCalendar(date);
String month = csc.getYear()+"-"+csc.getMM();
String sql = "select COUNT(1) as emp from MEMPLOYEE where RESIGNDATE > '"+month+"-01' and RESIGNDATE <='"+month+"-31' "+filter+" ";
try{
DataSet ds = new DataSet(sql,up.get("dbname"),"Memployee");
ds.initConnection(up);
while (ds.next()) {
emp = ds.getString("emp");
}
ds.closeConnection();
}catch(Exception e) {
e.printStackTrace();
}
return emp;
}
%>
</body>
</html>