LifeCenter/src/main/java/com/lemon/lifecenter/controller/PatientStatistics.java

package com.lemon.lifecenter.controller;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.security.GeneralSecurityException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.crypt.Encryptor;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;

import com.lemon.lifecenter.common.LifeCenterConfigVO;
import com.lemon.lifecenter.common.LifeCenterController;
import com.lemon.lifecenter.common.LifeCenterFileDownload;
import com.lemon.lifecenter.common.LifeCenterFunction;
import com.lemon.lifecenter.common.LifeCenterPaging;
import com.lemon.lifecenter.common.LifeCenterSessionController;
import com.lemon.lifecenter.dto.LocationDTO;
import com.lemon.lifecenter.dto.PatientDTO;
import com.lemon.lifecenter.service.CenterService;
import com.lemon.lifecenter.service.PatientService;

@Controller
@RequestMapping( "/statistics/patient" )
public class PatientStatistics extends LifeCenterController {
    @Autowired
    private CenterService centerService;
    @Autowired
    private LifeCenterConfigVO config;
    @Autowired
    private PatientService patientService;
    private LifeCenterPaging paging;
    
    @RequestMapping( "/list" )
    public ModelAndView list( 
            @RequestParam(value="page", required=false, defaultValue="1") int page,
            @RequestParam(value="q", required=false, defaultValue="") String q,
            @RequestParam(value="locationCode", required=false, defaultValue="") String locationCode,
            @RequestParam(value="startDate", required=false, defaultValue="") String startDate,
            @RequestParam(value="endDate", required=false, defaultValue="") String endDate,
            @RequestParam(value="registDay", required=false, defaultValue="") String inputRegistDay,
            @RequestParam(value="v", required=false, defaultValue="") String v ) {
        
        //일자별 통계
        if( v.equals( "day" ) ) {
            PatientDTO dto = new PatientDTO();
            
            if( endDate.equals( "" ) ) {
                endDate = LifeCenterFunction.getNow( "yyyy-MM-dd" );
            }
            
            if( startDate.equals( "" ) ) {
                //기록상 최초 환자 입소일 이후부터 계산
                startDate = "2020-11-23"; 
            }
            
            dto.setLimit( ( Integer.valueOf( page ) - 1 ) * config.pageDataSize );
            dto.setLimitMax( config.pageDataSize );
            dto.setQ(q);
            dto.setLocationCode(locationCode);
            dto.setStartDate(startDate);
            dto.setEndDate(endDate);
            
            List<LocationDTO> locationList = centerService.selectLocation();
            
            int total = patientService.selectStatisticsPatientCount(dto);
            List<PatientDTO> statisticsList = patientService.selectStatisticsPatientList(dto);
            PatientDTO statisticsSum = patientService.selectStatisticsPatientSum(dto);
            
            String param = "q=" + q + "&locationCode=" + locationCode + "&startDate=" + startDate + "&endDate=" + endDate + "&v=day";
            paging = LifeCenterPaging.getInstance();
            paging.paging(config, total, page, param);
            
            ModelAndView mv = setMV("statistics/list2");
            
            
            mv.addObject( "q" , q );
            mv.addObject( "statisticsList" , statisticsList );
            mv.addObject( "statisticsSum" , statisticsSum );
            mv.addObject( "locationList", locationList );
            mv.addObject( "locationCode", locationCode );
            mv.addObject( "startDate", startDate);
            mv.addObject( "endDate", endDate);
            mv.addObject( "total", total);
            mv.addObject( "paging", paging );
            mv.addObject( "page", page );
            mv.addObject( "pageSize", dto.getLimitMax() );
            
            return mv;
        } else {
            String registDayTime = "";
            //센터별 통계
            PatientDTO dto = new PatientDTO();
            
            dto.setLimit( ( Integer.valueOf( page ) - 1 ) * config.pageDataSize );
            dto.setLimitMax( config.pageDataSize );
            dto.setQ(q);
            dto.setLocationCode(locationCode);
            
            if (inputRegistDay.isEmpty()) {
                inputRegistDay = LifeCenterFunction.getNow("yyyy-MM-dd");
            }
            
            if( inputRegistDay.equals( LifeCenterFunction.getNow("yyyy-MM-dd") ) ) {
                dto.setRegistDayTime( inputRegistDay + " " + LifeCenterFunction.getNow("hh:mm") );
            } else {
                dto.setRegistDayTime( inputRegistDay + " 00:00" );
            }
            
            dto.setRegistDay(inputRegistDay);
            registDayTime = dto.getRegistDayTime();
            
            List<LocationDTO> locationList = centerService.selectLocation();
            
            int total = patientService.selectStatisticsPatientByCenterCount(dto);
            List<PatientDTO> statisticsList = new ArrayList<PatientDTO>();
            
            if( total > 0 ) {
                statisticsList = patientService.selectStatisticsPatientByCenterList(dto);
            }
            
            
            String param = "locationCode="+locationCode+"&q="+q+"&registDay="+inputRegistDay;
            
            paging = LifeCenterPaging.getInstance();
            paging.paging(config, total, page, param);
            
            ModelAndView mv = setMV("statistics/list");
            
            mv.addObject( "q" , q );
            mv.addObject( "statisticsList" , statisticsList );
            mv.addObject( "locationList", locationList );
            mv.addObject( "locationCode", locationCode );
            mv.addObject( "registDay", inputRegistDay );
            mv.addObject( "registDayTime", registDayTime );
            mv.addObject( "total", total);
            mv.addObject( "paging", paging );
            mv.addObject( "page", page );
            mv.addObject( "pageSize", dto.getLimitMax() );
            
            return mv;
        }
    }
    
    
    
    @RequestMapping( "/excel" )
    public void excel( 
            HttpServletRequest request, HttpServletResponse response,
            @RequestParam(value="page", required=false, defaultValue="1") int page,
            @RequestParam(value="q", required=false, defaultValue="") String q,
            @RequestParam(value="locationCode", required=false, defaultValue="") String locationCode,
            @RequestParam(value="startDate", required=false, defaultValue="") String startDate,
            @RequestParam(value="endDate", required=false, defaultValue="") String endDate,
            @RequestParam(value="registDay", required=false, defaultValue="") String inputRegistDay,
            @RequestParam(value="v", required=false, defaultValue="") String v ) {
        
        // 일자별 통계
        if( v.equals( "day" ) ) {
            PatientDTO dto = new PatientDTO();
            
            if( endDate.equals( "" ) ) {
                endDate = LifeCenterFunction.getNow( "yyyy-MM-dd" );
            }
            
            if( startDate.equals( "" ) ) {
                //기록상 최초 환자 입소일 이후부터 계산
                startDate = "2020-11-23"; 
            }
            
            dto.setQ(q);
            dto.setLocationCode(locationCode);
            dto.setStartDate(startDate);
            dto.setEndDate(endDate);
            
            int total = patientService.selectStatisticsPatientCount(dto);
            
            dto.setLimit( 0 );
            dto.setLimitMax( total );
            
            List<PatientDTO> statisticsList = patientService.selectStatisticsPatientList(dto);
            PatientDTO statisticsSum = patientService.selectStatisticsPatientSum(dto);
            
            getStatisticsPatientExcel(request, response, statisticsList, statisticsSum);
        } else {
            
            //센터별 통계
            PatientDTO dto = new PatientDTO();
            
            dto.setQ(q);
            dto.setLocationCode(locationCode);
            
            if (inputRegistDay.isEmpty()) {
                inputRegistDay = LifeCenterFunction.getNow("yyyy-MM-dd");
            }
            
            if( inputRegistDay.equals( LifeCenterFunction.getNow("yyyy-MM-dd") ) ) {
                dto.setRegistDayTime( inputRegistDay + " " + LifeCenterFunction.getNow("hh:mm") );
            } else {
                dto.setRegistDayTime( inputRegistDay + " 00:00" );
            }
            
            dto.setRegistDay(inputRegistDay);
            
            List<LocationDTO> locationList = centerService.selectLocation();
            
            int total = patientService.selectStatisticsPatientByCenterCount(dto);
            List<PatientDTO> statisticsList = new ArrayList<PatientDTO>();
            
            dto.setLimit( 0 );
            dto.setLimitMax( total );
            
            if( total > 0 ) {
                statisticsList = patientService.selectStatisticsPatientByCenterList(dto);
            }
            
            getStatisticsPatientByCenterExcel(request, response, statisticsList, inputRegistDay );
        }
    }
    
    private void getStatisticsPatientByCenterExcel(HttpServletRequest request, HttpServletResponse response, List<PatientDTO> data, String registDay ) {
        String password = LifeCenterSessionController.getSession(request, "sesPhoneNumber");
        password = password.toLowerCase();
        if (!password.equals("null") && !password.equals( "" )) {
            password = password.replace("-", "");
            password = password.substring(3).trim();
        } else {
            password = "";
        }
        
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet1 = workbook.createSheet("firstSheet");
        DecimalFormat df = new DecimalFormat("#,###");
        
        //1.셀 스타일 및 폰트 설정
        CellStyle styleOfBoardFillFontBlackBold16 = workbook.createCellStyle();
        //정렬
        styleOfBoardFillFontBlackBold16.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬
        styleOfBoardFillFontBlackBold16.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
        //배경색
        styleOfBoardFillFontBlackBold16.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        styleOfBoardFillFontBlackBold16.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //테두리 선 (우,좌,위,아래)
        styleOfBoardFillFontBlackBold16.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleOfBoardFillFontBlackBold16.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleOfBoardFillFontBlackBold16.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleOfBoardFillFontBlackBold16.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //폰트 설정
        Font fontOfGothicBlackBold16 = workbook.createFont();
//        fontOfGothicBlackBold16.setFontName("나눔고딕"); //글씨체
        fontOfGothicBlackBold16.setFontHeight((short)(10*20)); //사이즈
        fontOfGothicBlackBold16.setBoldweight(Font.BOLDWEIGHT_BOLD); //볼드 (굵게)
        styleOfBoardFillFontBlackBold16.setFont(fontOfGothicBlackBold16);
        
        
        styleOfBoardFillFontBlackBold16.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
        
        int i = 1;
        
        // th영역
        Row row = sheet1.createRow(0);
        Cell cell1 = row.createCell(0);
        Cell cell2 = row.createCell(1);
        Cell cell3 = row.createCell(2);
        Cell cell4 = row.createCell(3);
        Cell cell5 = row.createCell(4);
        Cell cell6 = row.createCell(5);
        Cell cell7 = row.createCell(6);

        
        cell1.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell2.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell3.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell4.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell5.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell6.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell7.setCellStyle(styleOfBoardFillFontBlackBold16);
        
        
        sheet1.setColumnWidth( 0, 10000); //생활치료센터명
        sheet1.setColumnWidth( 1, 6000); //소관
        sheet1.setColumnWidth( 2, 6000); //환자인원
        sheet1.setColumnWidth( 3, 6000); //입소
        sheet1.setColumnWidth( 4, 9000); //퇴실소계
        sheet1.setColumnWidth( 5, 6000); //퇴소
        sheet1.setColumnWidth( 6, 6000); //지정병원이송
        
        
        cell1.setCellValue("생활치료센터명");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,0,0));
        
        cell2.setCellValue("소관");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,1,1));
        
        cell3.setCellValue("환자인원");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,2,2));
        
        cell4.setCellValue("입소");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,3,3));
        
        cell5.setCellValue("퇴실소계 (퇴소 + 지정병원이송)");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,4,4));
        
        cell6.setCellValue("퇴실");
        sheet1.addMergedRegion(new CellRangeAddress(0,0,5,6));

        
        row = sheet1.createRow(i);
        cell1 = row.createCell(0);
        cell2 = row.createCell(1);
        cell3 = row.createCell(2);
        cell4 = row.createCell(3);
        cell5 = row.createCell(4);
        cell6 = row.createCell(5);
        cell7 = row.createCell(6);
        
        cell1.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell2.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell3.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell4.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell5.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell6.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell7.setCellStyle(styleOfBoardFillFontBlackBold16);
        
        cell6.setCellValue( "퇴소" );
        cell7.setCellValue( "지정병원이송" );
        
        i++;
        // th영역 끝
        
        CellStyle styleOfBoardFillFontBlackBold17 = workbook.createCellStyle();
        CellStyle styleOfBoardFillFontBlackBold18 = workbook.createCellStyle();
        CellStyle styleOfBoardFillFontBlackBold19 = workbook.createCellStyle();
        Font fontOfGothicBlackBold17 = workbook.createFont();
        Font fontOfGothicBlackBold18 = workbook.createFont();
        Font fontOfGothicBlackBold19 = workbook.createFont();
        fontOfGothicBlackBold17.setFontName("맑은 고딕"); //글씨체
        fontOfGothicBlackBold18.setFontName("맑은 고딕"); //글씨체
        fontOfGothicBlackBold19.setFontName("맑은 고딕"); //글씨체
        fontOfGothicBlackBold17.setBoldweight(Font.BOLDWEIGHT_BOLD); //볼드 (굵게)
        styleOfBoardFillFontBlackBold17.setFont(fontOfGothicBlackBold17);
        styleOfBoardFillFontBlackBold17.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
        styleOfBoardFillFontBlackBold18.setFont(fontOfGothicBlackBold18);
        styleOfBoardFillFontBlackBold19.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
        
        for (PatientDTO dto : data) {
            // 센터별 통계 row
            row = sheet1.createRow(i);
            cell1 = row.createCell(0);
            cell2 = row.createCell(1);
            cell3 = row.createCell(2);
            cell4 = row.createCell(3);
            cell5 = row.createCell(4);
            cell6 = row.createCell(5);
            cell7 = row.createCell(6);
            
            styleOfBoardFillFontBlackBold17.setAlignment(CellStyle.ALIGN_RIGHT);
            cell1.setCellStyle(styleOfBoardFillFontBlackBold18);
            cell2.setCellStyle(styleOfBoardFillFontBlackBold19);
            cell3.setCellStyle(styleOfBoardFillFontBlackBold17);
            cell4.setCellStyle(styleOfBoardFillFontBlackBold17);
            cell5.setCellStyle(styleOfBoardFillFontBlackBold19);
            cell6.setCellStyle(styleOfBoardFillFontBlackBold19);
            cell7.setCellStyle(styleOfBoardFillFontBlackBold19);
            
            cell1.setCellValue( dto.getCenterName() );
            cell2.setCellValue( dto.getJurisdictionName() );
            cell3.setCellValue( dto.gethTotal() + dto.getdTotal() + dto.gettTotal() );
            cell4.setCellValue( dto.gethTotal() );
            cell5.setCellValue( dto.getdTotal() + dto.gettTotal() );
            cell6.setCellValue( dto.getdTotal() );
            cell7.setCellValue( dto.gettTotal() );
            
            i++;
        }
        
        row = sheet1.createRow(i);
        cell1 = row.createCell(2);
        cell2 = row.createCell(3);
        cell3 = row.createCell(4);
        cell4 = row.createCell(5);
        cell5 = row.createCell(6);
        cell6 = row.createCell(7);
        cell7 = row.createCell(7);
        
        try {
          // JBOSS에서 구동시 /home1/jboss/jboss-eap-7.3/domain/test/excel-temp 경로에 저장이됨
          String tempPath = "../excel-temp/testExcel.xlsx";
          String downName = LifeCenterFunction.getNow() + "_["+registDay+"] 환자입퇴소통계.xlsx";
          File xlsFile = new File(tempPath);
          
          ByteArrayOutputStream fileOut = new ByteArrayOutputStream();
          FileOutputStream fos = new FileOutputStream(tempPath);
          workbook.write(fileOut);
          
          InputStream filein = new ByteArrayInputStream(fileOut.toByteArray());
          OPCPackage opc = OPCPackage.open(filein);

          POIFSFileSystem fileSystem = new POIFSFileSystem();

          EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
          Encryptor encryptor = encryptionInfo.getEncryptor();
          encryptor.confirmPassword(password);

          opc.save(encryptor.getDataStream(fileSystem));
          opc.flush();
          
          fileSystem.writeFilesystem(fos);
          
          fileOut.close();
          opc.close();
          
          filein.close();
          fileSystem.close();
          
          LifeCenterFileDownload.download(request, response, tempPath, downName);
          
          xlsFile.delete();
      } catch (FileNotFoundException e) {
          e.printStackTrace();
      } catch (IOException e) {
          e.printStackTrace();
      } catch (InvalidFormatException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      } catch (GeneralSecurityException e) {
          // TODO Auto-generated catch block
          e.printStackTrace();
      }
    }
    
    private void getStatisticsPatientExcel(HttpServletRequest request, HttpServletResponse response, List<PatientDTO> data, PatientDTO sum ) {
        String password = LifeCenterSessionController.getSession(request, "sesPhoneNumber");
        password = password.toLowerCase();
        if (!password.equals("null") && !password.equals( "" )) {
            password = password.replace("-", "");
            password = password.substring(3).trim();
        } else {
            password = "";
        }
        
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet1 = workbook.createSheet("firstSheet");
        DecimalFormat df = new DecimalFormat("#,###");
        
        //1.셀 스타일 및 폰트 설정
        CellStyle styleOfBoardFillFontBlackBold16 = workbook.createCellStyle();
        //정렬
        styleOfBoardFillFontBlackBold16.setAlignment(CellStyle.ALIGN_CENTER); //가운데 정렬
        styleOfBoardFillFontBlackBold16.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
        //배경색
        styleOfBoardFillFontBlackBold16.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        styleOfBoardFillFontBlackBold16.setFillPattern(CellStyle.SOLID_FOREGROUND);
        //테두리 선 (우,좌,위,아래)
        styleOfBoardFillFontBlackBold16.setBorderRight(HSSFCellStyle.BORDER_THIN);
        styleOfBoardFillFontBlackBold16.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        styleOfBoardFillFontBlackBold16.setBorderTop(HSSFCellStyle.BORDER_THIN);
        styleOfBoardFillFontBlackBold16.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //폰트 설정
        Font fontOfGothicBlackBold16 = workbook.createFont();
//        fontOfGothicBlackBold16.setFontName("나눔고딕"); //글씨체
        fontOfGothicBlackBold16.setFontHeight((short)(10*20)); //사이즈
        fontOfGothicBlackBold16.setBoldweight(Font.BOLDWEIGHT_BOLD); //볼드 (굵게)
        styleOfBoardFillFontBlackBold16.setFont(fontOfGothicBlackBold16);
        
        
        styleOfBoardFillFontBlackBold16.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
        
        int i = 1;
        
        // th영역
        Row row = sheet1.createRow(0);
        Cell cell1 = row.createCell(0);
        Cell cell2 = row.createCell(1);
        Cell cell3 = row.createCell(2);
        Cell cell4 = row.createCell(3);
        Cell cell5 = row.createCell(4);
        Cell cell6 = row.createCell(5);

        
        cell1.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell2.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell3.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell4.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell5.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell6.setCellStyle(styleOfBoardFillFontBlackBold16);
        
        
        sheet1.setColumnWidth( 0, 5000); //날짜
        sheet1.setColumnWidth( 1, 6000); //환자인원
        sheet1.setColumnWidth( 2, 6000); //입소
        sheet1.setColumnWidth( 3, 8000); //퇴실소계
        sheet1.setColumnWidth( 4, 6000); //퇴소
        sheet1.setColumnWidth( 5, 6000); //지정병원 이송
        
        
        cell1.setCellValue("날짜");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,0,0));
        cell2.setCellValue("환자인원");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,1,1));
        cell3.setCellValue("입소");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,2,2));
        cell4.setCellValue("퇴실소계 (퇴소 + 지정병원이송)");
        sheet1.addMergedRegion(new CellRangeAddress(0,1,3,3));
        
        cell5.setCellValue("퇴실");
        sheet1.addMergedRegion(new CellRangeAddress(0,0,4,5));
        
        row = sheet1.createRow(i);
        cell1 = row.createCell(0);
        cell2 = row.createCell(1);
        cell3 = row.createCell(2);
        cell4 = row.createCell(3);
        cell5 = row.createCell(4);
        cell6 = row.createCell(5);
        
        cell1.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell2.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell3.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell4.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell5.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell6.setCellStyle(styleOfBoardFillFontBlackBold16);
        
        cell5.setCellValue( "퇴소" );
        cell6.setCellValue( "지정병원이송" );
        
        i++;
        // th영역 끝
        
        
        
        // 누적 row 
        CellStyle styleOfBoardFillFontBlackBold17 = workbook.createCellStyle();
        CellStyle styleOfBoardFillFontBlackBold18 = workbook.createCellStyle();
        CellStyle styleOfBoardFillFontBlackBold19 = workbook.createCellStyle();
        Font fontOfGothicBlackBold17 = workbook.createFont();
        Font fontOfGothicBlackBold18 = workbook.createFont();
        Font fontOfGothicBlackBold19 = workbook.createFont();
        fontOfGothicBlackBold17.setFontName("맑은 고딕"); //글씨체
        fontOfGothicBlackBold18.setFontName("맑은 고딕"); //글씨체
        fontOfGothicBlackBold19.setFontName("맑은 고딕"); //글씨체
        fontOfGothicBlackBold17.setBoldweight(Font.BOLDWEIGHT_BOLD); //볼드 (굵게)
        styleOfBoardFillFontBlackBold17.setFont(fontOfGothicBlackBold17);
        styleOfBoardFillFontBlackBold17.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
        styleOfBoardFillFontBlackBold18.setFont(fontOfGothicBlackBold18);
        styleOfBoardFillFontBlackBold19.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));
        
        row = sheet1.createRow(i);
        cell1 = row.createCell(0);
        cell2 = row.createCell(1);
        cell3 = row.createCell(2);
        cell4 = row.createCell(3);
        cell5 = row.createCell(4);
        cell6 = row.createCell(5);
        
        styleOfBoardFillFontBlackBold17.setAlignment(CellStyle.ALIGN_RIGHT);
        styleOfBoardFillFontBlackBold17.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        
        styleOfBoardFillFontBlackBold18.setAlignment(CellStyle.ALIGN_CENTER);
        cell1.setCellStyle(styleOfBoardFillFontBlackBold18);
        cell2.setCellStyle(styleOfBoardFillFontBlackBold19);
        cell3.setCellStyle(styleOfBoardFillFontBlackBold17);
        cell4.setCellStyle(styleOfBoardFillFontBlackBold17);
        cell5.setCellStyle(styleOfBoardFillFontBlackBold19);
        cell6.setCellStyle(styleOfBoardFillFontBlackBold19);
        
        cell1.setCellValue( "누적" );
        cell2.setCellValue( sum.gethTotal() + sum.getdTotal() + sum.gettTotal() );
        cell3.setCellValue( sum.gethTotal() );
        cell4.setCellValue( sum.getdTotal() + sum.gettTotal() );
        cell5.setCellValue( sum.getdTotal() );
        cell6.setCellValue( sum.gettTotal() );
        
        i++;
        // 누적 row 끝
        
        
        
        // 일별 통계 row
        for (PatientDTO dto : data) {
            row = sheet1.createRow(i);
            cell1 = row.createCell(0);
            cell2 = row.createCell(1);
            cell3 = row.createCell(2);
            cell4 = row.createCell(3);
            cell5 = row.createCell(4);
            cell6 = row.createCell(5);
            
            styleOfBoardFillFontBlackBold17.setAlignment(CellStyle.ALIGN_RIGHT);
            cell1.setCellStyle(styleOfBoardFillFontBlackBold18);
            cell2.setCellStyle(styleOfBoardFillFontBlackBold19);
            cell3.setCellStyle(styleOfBoardFillFontBlackBold17);
            cell4.setCellStyle(styleOfBoardFillFontBlackBold17);
            cell5.setCellStyle(styleOfBoardFillFontBlackBold19);
            cell6.setCellStyle(styleOfBoardFillFontBlackBold19);
            
            cell1.setCellValue( dto.getInDate() );
            cell2.setCellValue( dto.gethTotal() + dto.getdTotal() + dto.gettTotal() );
            cell3.setCellValue( dto.gethTotal() );
            cell4.setCellValue( dto.getdTotal() + dto.gettTotal() );
            cell5.setCellValue( dto.getdTotal() );
            cell6.setCellValue( dto.gettTotal() );
            
            i++;
        }
        
        row = sheet1.createRow(i);
        cell1 = row.createCell(2);
        cell2 = row.createCell(3);
        cell3 = row.createCell(4);
        cell4 = row.createCell(5);
        cell5 = row.createCell(6);
        cell6 = row.createCell(7);
        
        try {
          // JBOSS에서 구동시 /home1/jboss/jboss-eap-7.3/domain/test/excel-temp 경로에 저장이됨
          String tempPath = "../excel-temp/testExcel.xlsx";
          String downName = LifeCenterFunction.getNow() + " 환자입퇴소통계.xlsx";
          File xlsFile = new File(tempPath);
//          FileOutputStream fileOut = new FileOutputStream(tempPath);
//          workbook.write(fileOut);
//          fileOut.close();
          
          ByteArrayOutputStream fileOut = new ByteArrayOutputStream();
          FileOutputStream fos = new FileOutputStream(tempPath);
          workbook.write(fileOut);
          
          InputStream filein = new ByteArrayInputStream(fileOut.toByteArray());
          OPCPackage opc = OPCPackage.open(filein);

          POIFSFileSystem fileSystem = new POIFSFileSystem();

          EncryptionInfo encryptionInfo = new EncryptionInfo(EncryptionMode.agile);
          Encryptor encryptor = encryptionInfo.getEncryptor();
          encryptor.confirmPassword(password);

          opc.save(encryptor.getDataStream(fileSystem));
          opc.flush();
          
          fileSystem.writeFilesystem(fos);
          
          fileOut.close();
          opc.close();
          
          filein.close();
          fileSystem.close();
          
          LifeCenterFileDownload.download(request, response, tempPath, downName);

          xlsFile.delete();
      } catch (FileNotFoundException e) {
          e.printStackTrace();
      } catch (IOException e) {
          e.printStackTrace();
      } 
        catch (InvalidFormatException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        } catch (GeneralSecurityException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
}