Java tutorial
/* * To change this license header, choose License Headers in Project Properties. * To change this template file, choose Tools | Templates * and open the template in the editor. */ package com.claim.controller; import com.claim.connection.DBManage; import com.claim.dao.Vajira11535Dao; import com.claim.object.HospitalService; import com.claim.object.ObjRptVajiraHc11535Detail; import com.claim.object.ObjRptVajiraHc11535Summary; import com.claim.object.OppReport; import com.claim.support.Console; import com.claim.constants.ConstantMessage; import com.claim.constants.ConstantVariable; import com.claim.support.DateUtil; import com.claim.support.FileUtil; import com.claim.object.ProgrameStatus; import com.claim.support.StringOpUtil; import com.claim.xls.ExcellBaseUtil; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.sql.Connection; import java.sql.SQLException; import java.util.ArrayList; import java.util.List; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.util.CellRangeAddress; /** * * @author POOL_LAPTOP */ public class Vajira11535Controller extends ExcellBaseUtil { //############## POI ############## FileInputStream file = null; FileOutputStream out = null; HSSFCell cell = null; HSSFRow row = null; //############## POI ############## //############# JDBC ############### Connection connection = null; //############# JDBC ############### String EXCELL_HEADER1 = null; String EXCELL_HEADER2 = null; String EXCELL_HOSPITAL = null; static String HEADER_DETAIL = "??? Op? ? {YEAR}"; static String HEADER_SUMMARY = "??? ??? {YEAR}"; static String SUB_HEADER = "/ {DATE} ? {STMP}"; static String FILE_NAME_DETAIL = "DetailPay_11535_"; static String FILE_NAME_SUM = "SumPay_11535_"; public static void main(String[] args) { Vajira11535Controller vajira = new Vajira11535Controller(); OppReport report = new OppReport(); report.setYearMonth("201507"); report.setNo("1"); report.setPathFile("C:\\excell\\claim\\"); report.setServiceCode("11535"); report.setServiceName("TEST 11583 TEST"); report.setStmp("201507-1"); // List<HospitalService> serviceList = new ArrayList<>(); // serviceList.add(new HospitalService("21755", "TEST TEST")); // report.setListServiceCode(serviceList); vajira.hc11535ByHmain(report); vajira.hc11535Summary(report); } public ProgrameStatus hc11535Detail(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); int col_last = 20; int row_start = 8; // index row int row_formula_start = row_start + 1; List<ObjRptVajiraHc11535Detail> listData = new ArrayList<ObjRptVajiraHc11535Detail>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try { connection = new DBManage().open(); Vajira11535Dao vajiraHCDao = new Vajira11535Dao(); vajiraHCDao.setConnection(connection); //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "HC_11535_DETAIL.xls")); EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}", new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH)); //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report); EXCELL_HEADER2 = report.getTitle2(); EXCELL_HOSPITAL = "?: " + StringOpUtil.removeNull(report.getServiceName()) + " (" + StringOpUtil.removeNull(report.getServiceCode()) + ")"; // style Excell this.setFontFamily("TH SarabunPSK"); //this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.loadStyle(new HSSFWorkbook(file)); // Start sheet 1 HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.setColumnWidth((col_last + 1), WIDTH_TXID); // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 2 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int autoNumber = 1; listData = vajiraHCDao.getListVajiraDetail(report); for (int i = 0; i < listData.size(); i++) { ObjRptVajiraHc11535Detail objData = listData.get(i); row = sheet.createRow(curRow); row.setHeight((short) 400); cell = row.createCell(0); cell.setCellValue(autoNumber); cell.setCellStyle(csNum3); cell = row.createCell(1); cell.setCellValue(objData.getPid()); cell.setCellStyle(csStringLeft); cell = row.createCell(2); cell.setCellValue(objData.getHn()); cell.setCellStyle(csStringCenter); cell = row.createCell(3); cell.setCellValue(objData.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(4); cell.setCellValue(objData.getHmain() + " : " + objData.getHmain_name()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(objData.getHmainip() + " : " + objData.getHmainip_name()); cell.setCellStyle(csStringLeft); cell = row.createCell(6); cell.setCellValue(objData.getDateopd()); cell.setCellStyle(csStringLeft); cell = row.createCell(7); cell.setCellValue(objData.getPdxcode()); cell.setCellStyle(csStringCenter); cell = row.createCell(8); cell.setCellValue(objData.getChrg_hc()); cell.setCellStyle(csDouble2R); cell = row.createCell(9); cell.setCellValue(objData.getChrg_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(10); cell.setCellValue(objData.getChrg_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(11); cell.setCellValue(objData.getChrg_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(12); cell.setCellValue(objData.getChrg_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(13); cell.setCellValue(objData.getPaid_model()); cell.setCellStyle(csDouble2R); cell = row.createCell(14); cell.setCellValue(objData.getPaid_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(15); cell.setCellValue(objData.getPaid_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(16); cell.setCellValue(objData.getPaid_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(17); cell.setCellValue(objData.getPaid_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(18); cell.setCellValue(objData.getReimburse()); cell.setCellStyle(csDouble2R); cell = row.createCell(19); cell.setCellValue(objData.getPoint()); cell.setCellStyle(csDouble2R); cell = row.createCell(20); cell.setCellValue(objData.getTotalreimburse()); cell.setCellStyle(csDouble2R); cell = row.createCell(21); cell.setCellValue(objData.getInvoice_no()); cell.setCellStyle(csStringLeft); cell = row.createCell(22); cell.setCellValue(objData.getTxid()); cell.setCellStyle(csStringtxid); curRow++; autoNumber++; } System.out.println("row.getPhysicalNumberOfCells(); ::==" + row.getPhysicalNumberOfCells()); //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); // row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 7)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); row.createCell(7).setCellStyle(csNum4B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSumRound(8, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSumRound(9, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSumRound(10, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSumRound(11, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSumRound(12, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSumRound(13, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSumRound(14, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSumRound(15, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSumRound(16, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSumRound(17, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(18); cell.setCellFormula(builderFormulaSumRound(18, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(19); cell.setCellFormula(builderFormulaSumRound(19, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(20); cell.setCellFormula(builderFormulaSumRound(20, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last)); cell.setCellStyle(csStringB); System.out.println("sheet.getLastRowNum(); ::==" + sheet.getLastRowNum()); /* ############ ? ############### */ workbookBase.setSheetName(0, report.getServiceCode());// + " " + report.getServiceName()); new FileUtil().mkdirMutiDirectory(pathDirectory); out = new FileOutputStream(pathDirectory + "" + File.separator + FILE_NAME_DETAIL + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG("? " + StringOpUtil.removeNull(report.getServiceName()) + " : " + report.getYearMonth() + "-" + report.getNo() + " ?", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; } public ProgrameStatus hc11535Summary(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); int col_last = 17; int row_start = 8; // index row int row_formula_start = row_start + 1; List<ObjRptVajiraHc11535Summary> listData = new ArrayList<ObjRptVajiraHc11535Summary>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try { connection = new DBManage().open(); Vajira11535Dao vajiraREDao = new Vajira11535Dao(); vajiraREDao.setConnection(connection); //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "HC_11535_SUM.xls")); EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}", new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH)); //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report); EXCELL_HEADER2 = report.getTitle2(); EXCELL_HOSPITAL = "?: ? (11535)"; // style Excell this.setFontFamily("TH SarabunPSK"); //this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.loadStyle(new HSSFWorkbook(file)); // Start sheet 1 HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.setColumnWidth((col_last + 1), WIDTH_TXID); // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 2 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int autoNumber = 1; listData = vajiraREDao.getListVajiraSumGroupHmain(report); for (int i = 0; i < listData.size(); i++) { ObjRptVajiraHc11535Summary objData = listData.get(i); row = sheet.createRow(curRow); row.setHeight((short) 400); cell = row.createCell(0); cell.setCellValue(autoNumber); cell.setCellStyle(csNum3); cell = row.createCell(1); cell.setCellValue(objData.getHmain()); cell.setCellStyle(csStringLeft); cell = row.createCell(2); cell.setCellValue(objData.getHmain_name()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(objData.getCount_pid()); cell.setCellStyle(csNum3); cell = row.createCell(4); cell.setCellValue(objData.getCount_txid()); cell.setCellStyle(csNum3); cell = row.createCell(5); cell.setCellValue(objData.getSum_chrg_hc()); cell.setCellStyle(csDouble2R); cell = row.createCell(6); cell.setCellValue(objData.getSum_chrg_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(7); cell.setCellValue(objData.getSum_chrg_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(8); cell.setCellValue(objData.getSum_chrg_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(9); cell.setCellValue(objData.getSum_chrg_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(10); cell.setCellValue(objData.getSum_paid_hc()); cell.setCellStyle(csDouble2R); cell = row.createCell(11); cell.setCellValue(objData.getSum_paid_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(12); cell.setCellValue(objData.getSum_paid_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(13); cell.setCellValue(objData.getSum_paid_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(14); cell.setCellValue(objData.getSum_paid_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(15); cell.setCellValue(objData.getSum_point()); cell.setCellStyle(csDouble2R); cell = row.createCell(16); cell.setCellValue(objData.getSum_reimburse()); cell.setCellStyle(csDouble2R); cell = row.createCell(17); cell.setCellValue(objData.getSum_totalreimburse()); cell.setCellStyle(csDouble2R); curRow++; autoNumber++; } //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); // row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csString2Center); row.createCell(2).setCellStyle(csString2Center); cell = row.createCell(3); cell.setCellFormula(builderFormulaSumRound(3, row_formula_start, curRow, 0)); cell.setCellStyle(csNum4BCenter); cell = row.createCell(4); cell.setCellFormula(builderFormulaSumRound(4, row_formula_start, curRow, 0)); cell.setCellStyle(csNum4BCenter); cell = row.createCell(5); cell.setCellFormula(builderFormulaSumRound(5, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(6); cell.setCellFormula(builderFormulaSumRound(6, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSumRound(7, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSumRound(8, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSumRound(9, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSumRound(10, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSumRound(11, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSumRound(12, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSumRound(13, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSumRound(14, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSumRound(15, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSumRound(16, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSumRound(17, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last)); cell.setCellStyle(csStringB); /* ############ ? ############### */ workbookBase.setSheetName(0, report.getServiceCode());// + " " + report.getServiceName()); new FileUtil().mkdirMutiDirectory(pathDirectory); out = new FileOutputStream(pathDirectory + "" + File.separator + "vajira_summary_11535_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG( "??? Op? ? ???", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; } public ProgrameStatus hc11535ByHmain(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); List<HospitalService> listData = new ArrayList<>(); try { connection = new DBManage().open(); Vajira11535Dao vajira11535Dao = new Vajira11535Dao(); vajira11535Dao.setConnection(connection); // connection = new DBManage().open(); listData = vajira11535Dao.getHospitalServiceWithHmain(report); for (int i = 0; i < listData.size(); i++) { HospitalService objData = listData.get(i); report.setServiceCode(objData.getHosHmain()); report.setServiceName(objData.getHosHmainName()); programeStatus = hc11535Detail(report); } } catch (Exception e) { e.printStackTrace(); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; } public ProgrameStatus hc_genReportAllFunction(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); try { programeStatus = this.hc11535ByHmain(report); programeStatus = this.hc11535Summary(report); } catch (Exception e) { e.printStackTrace(); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; } }