LifeCenter/src/main/java/com/lemon/lifecenter/controller/CenterController.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.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.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
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.CenterInfoDTO;
import com.lemon.lifecenter.dto.ClinicConfigurationDTO;
import com.lemon.lifecenter.dto.FileDownloadDTO;
import com.lemon.lifecenter.dto.LocationDTO;
import com.lemon.lifecenter.dto.LoginDTO;
import com.lemon.lifecenter.dto.StaffDTO;
import com.lemon.lifecenter.service.CenterService;
import com.lemon.lifecenter.service.ClinicService;
import com.lemon.lifecenter.service.FileDownloadService;
import com.lemon.lifecenter.service.LoginService;
import com.lemon.lifecenter.service.StaffService;

// 생활치료센터관리 contorller
@Controller
@RequestMapping("/center")
public class CenterController extends LifeCenterController {
    private final Logger logger = LoggerFactory.getLogger(this.getClass());
    
    @Autowired
    private CenterService centerService;
    @Autowired
    private StaffService memberService;
    @Autowired
    private LoginService loginService;
    @Autowired
    private FileDownloadService fileDownloadService;
    @Autowired
    private ClinicService clinicService;
    @Autowired
    private LifeCenterConfigVO config;
    private LifeCenterPaging paging;
    
    @RequestMapping("/new")
    public ModelAndView centerNew() {
        List<LocationDTO> jurisdiction = centerService.selectLocation();
        List<LocationDTO> list = centerService.selectLocation();
        List<LocationDTO> cooperativeList = centerService.selectCooperativeHospialList();
        
        ModelAndView mv = setMV("center/new");
        mv.addObject("jurisdiction", jurisdiction);
        mv.addObject("cooperativeList", cooperativeList);
        list.remove(list.size()-1); 
        mv.addObject("locationList", list);
        return mv;
    }
    
    @RequestMapping("/new/regist")
    @Transactional(propagation=Propagation.REQUIRED)
    public String centerNewRegist(
            @ModelAttribute("dto") final CenterInfoDTO dto,
            @RequestParam(value="detailAddr", required=true) String detailAddr,
            @RequestParam(value="staffId", required=true) String staffId,
            @RequestParam(value="staffPw", required=true) String staffPw,
            @RequestParam(value="staffName", required=true) String staffName,
            @RequestParam(value="staffPhoneNumber", required=true) String staffPhoneNumber) throws Exception {

        String centerAddress = dto.getCenterAddress() + "|" + detailAddr;
        dto.setCenterAddress(centerAddress);
        StaffDTO mDto = new StaffDTO();
        
        centerService.insertCenter(dto);
        int centerCode = dto.getCenterCode();
        ClinicConfigurationDTO clinicConfig = new ClinicConfigurationDTO();
        clinicConfig.setCenterCode(centerCode);
        clinicService.insertConfiguration(clinicConfig);
        
        mDto.setCenterCode(centerCode);
        mDto.setId(staffId);
//        mDto.setPassword(LifeCenterFunction.aesEncrypt(config.aesKey, config.IV, staffPw));
        mDto.setPassword(LifeCenterFunction.sha256Encrypt(staffPw));
        mDto.setName(staffName);
        mDto.setPhoneNumber(staffPhoneNumber);
        mDto.setGroupIdx( 2 ); // 1: 시스템관리자 2:관리자 3:일반사용자, 센터신규등록시 아이디생성은 관리자 권한으로 생성, 센터하나에 관리자 1명으로 개발진행
        
        memberService.inserMember(mDto);
        
        return "redirect:/center/info?centerCode=" + dto.getCenterCode();
    }
    
    @RequestMapping("/info")
    public ModelAndView centerInfo(
            @RequestParam(value="centerCode", required=true) int centerCode) {
        CenterInfoDTO dto = new CenterInfoDTO();
        dto.setCenterCode(centerCode);
        dto = centerService.selectCenterInfo(dto);
        dto.setStaffPhoneNumber( LifeCenterFunction.phone( dto.getStaffPhoneNumber() ) );
        dto.setCenterNumber( LifeCenterFunction.phone( dto.getCenterNumber() ) );
        
        ModelAndView mv = setMV("center/info");
        
        String centerAddress = dto.getCenterAddress().split( "|" )[0];
        String detailAddress = dto.getCenterAddress().split( "|" )[1];
        
        mv.addObject("centerInfo", dto);
        mv.addObject( "centerAddress", centerAddress );
        mv.addObject( "detailAddress", detailAddress );
        mv.addObject("centerCode", centerCode);
        return mv;
    }

    @RequestMapping("/edit/update")
    @Transactional(propagation=Propagation.REQUIRED, rollbackFor = { RuntimeException.class, Error.class })
    public String centerEditUpdate(
            @ModelAttribute("dto") final CenterInfoDTO dto,
            @RequestParam(value="detailAddr", required=true) String detailAddr,
            @RequestParam(value="staffId", required=true) String staffId,
            @RequestParam(value="staffName", required=true) String staffName,
            @RequestParam(value="staffPhoneNumber", required=true) String staffPhoneNumber,
            HttpServletRequest request, HttpServletResponse response ) throws Exception {
        String sesGroupIdx  = LifeCenterSessionController.getSession( request, "sesGroupIdx" );
        String referer      = request.getHeader( "Referer" );
        
        if( !sesGroupIdx.equals( "1" ) ) {
            LifeCenterFunction.scriptMessage( response, "alertBox({ txt : '권한이 없습니다.', callBack : function(){ location.href='" + referer + "'} });" );
            return "/common/blank";
        } else {
            StaffDTO memberDTO = new StaffDTO();
            LoginDTO loginDTO  = new LoginDTO();
            loginDTO.setId( staffId );
            
            int memberTotal = loginService.selectMemberIdCount( loginDTO );
            
            if( memberTotal == 1 ) {
                String address = dto.getCenterAddress() + "|" + detailAddr;
                dto.setCenterAddress( address );
                int result = centerService.updateCenterInfo( dto );
                
                if (result > 0) {
                    memberDTO.setId( staffId );
                    memberDTO.setName( staffName );
                    memberDTO.setPhoneNumber(staffPhoneNumber);
                    
                    memberService.updateMember( memberDTO );
                }
            } else {
                LifeCenterFunction.scriptMessage( response, "alertBox({ txt : '비밀번호가 일치하지않습니다.', callBack : function(){ location.href='../edit?centerCode="+dto.getCenterCode()+"'} });" );
                return "/common/blank";
            }
        }
        
        return "redirect:/center/info?centerCode=" + dto.getCenterCode();
    }
    
    @RequestMapping("/edit")
    public ModelAndView centerEdit(
            @RequestParam(value="centerCode", required=true) int centerCode) {
        
        List<LocationDTO> jurisdiction = centerService.selectLocation();
        List<LocationDTO> list = centerService.selectLocation();
        List<LocationDTO> cooperativeList = centerService.selectCooperativeHospialList();
        
        CenterInfoDTO dto = new CenterInfoDTO();
        dto.setCenterCode(centerCode);
        dto = centerService.selectCenterInfo(dto);
        
        String address = dto.getCenterAddress();
        String mainAddr = "";
        String detailAddr = "";
        if (address.contains("|") == true) {
            String[] strAr = dto.getCenterAddress().split("[|]");
            mainAddr = strAr[0];
            detailAddr = strAr[1];
        } else {
            mainAddr = address;
        }
        
        ModelAndView mv = setMV("center/edit");
        list.remove(list.size()-1);
        mv.addObject("locationList", list);
        mv.addObject("jurisdiction", jurisdiction);
        mv.addObject("cooperativeList", cooperativeList);
        mv.addObject("centerInfo", dto);
        mv.addObject("mainAddr", mainAddr);
        mv.addObject("detailAddr", detailAddr);
        
        return mv;
    }

    @RequestMapping("/list")
    public ModelAndView centerList(
            @ModelAttribute("dto") final CenterInfoDTO dto,
            @RequestParam(value="page", required=false, defaultValue="1") int page,
            @RequestParam(value="centerName", required=false, defaultValue="") String centerName,
            @RequestParam(value="locationCode", required=false, defaultValue="") String locationCode,
            @RequestParam(value="startDate", required=false, defaultValue="") String startDate,
            @RequestParam(value="endDate", required=false, defaultValue="") String endDate) {
        
        dto.setLimit( ( Integer.valueOf( page ) - 1 ) * config.pageDataSize );
        dto.setLimitMax( config.pageDataSize );
        
        List<LocationDTO> list = centerService.selectLocation();
        
        int total = centerService.selectCenterCount(dto);
        List<CenterInfoDTO> result = new ArrayList<CenterInfoDTO>();
        if (total > 0) {
            result = centerService.selectCenterList(dto);
            
            for( CenterInfoDTO temp : result ) {
                result.get( result.indexOf( temp ) ).setStaffPhoneNumber( LifeCenterFunction.phone( temp.getStaffPhoneNumber() ) );
            }
        }
        
        String param = "centerName=" + dto.getCenterName() + "&locationCode=" + dto.getLocationCode() + "&startDate=" + dto.getStartDate() + "&endDate=" + dto.getEndDate() + "&useYn=" + dto.getUseYn();
        paging = LifeCenterPaging.getInstance();
        paging.paging(config, total, page, param);
        
        ModelAndView mv = setMV("center/list");
        
//        startDate = startDate.equals( "" ) ? LifeCenterFunction.getNow( "yyyy-MM-dd" ) : startDate;
//        endDate   = endDate.equals( "" )   ? LifeCenterFunction.getNow( "yyyy-MM-dd" ) : endDate;
        
        mv.addObject("locationList", list);
        mv.addObject("locationCode", locationCode);
        mv.addObject("startDate", startDate);
        mv.addObject("endDate", endDate);
        mv.addObject("useYn", dto.getUseYn());
        mv.addObject("centerName", centerName);
        mv.addObject("total", total);
        mv.addObject("item", result);
        mv.addObject("paging", paging);
        mv.addObject("page", page);
        mv.addObject("pageSize", dto.getLimitMax());
        
        return mv;
    }
    
    @RequestMapping( value="/passwordReset", method = RequestMethod.POST )
    @ResponseBody
    public boolean passwordReset(
            @RequestParam( value="staffId", required = true ) String id,
            @RequestParam( value="type", required = true ) String type) throws Exception {

        boolean result = false;
        String pw = "";
        StaffDTO dto = new StaffDTO();
        dto.setId(id);
        if (type.equals("staff")) {
//            pw = LifeCenterFunction.aesEncrypt(config.aesKey, config.IV, config.staffResetPw);
            pw = LifeCenterFunction.sha256Encrypt(config.staffResetPw);
        } else {
//            pw = LifeCenterFunction.aesEncrypt(config.aesKey, config.IV, config.centerResetPw);
            pw = LifeCenterFunction.sha256Encrypt(config.centerResetPw);
        }
        dto.setPassword(pw);
        
        int rts = memberService.updateMemberPwReset(dto);
        
        if (rts == 1) {
            result = true;
        }
        
        return result;
    }
    
    @RequestMapping( value="/excel", method=RequestMethod.POST )
    public void getCenterList(
            @RequestParam(value="downMemo", required=false, defaultValue="") String downMemo,
            HttpServletRequest request,HttpServletResponse response ) {
        String userId = LifeCenterSessionController.getSession(request, "sesId");

        //excel 다운로드 로그 남기기
        FileDownloadDTO fileDTO = new FileDownloadDTO();
        fileDTO.setId( userId );
        fileDTO.setIp( LifeCenterFunction.getRemoteAddr( request ) );
        fileDTO.setUrl( request.getRequestURI().toString() );
        fileDTO.setMemo( downMemo );
        fileDTO.setEtc( "" );
        fileDownloadService.insertExcelDownloadLog( fileDTO );
        
        CenterInfoDTO dto = new CenterInfoDTO();
        int total = centerService.selectCenterCount(dto);
        List<CenterInfoDTO> result = new ArrayList<CenterInfoDTO>();
        if (total > 0) {
            dto.setLimit(0);
            dto.setLimitMax(total);
            result = centerService.selectCenterList(dto);
            
            for( CenterInfoDTO temp : result ) {
                result.get( result.indexOf( temp ) ).setStaffPhoneNumber( LifeCenterFunction.phone( temp.getStaffPhoneNumber() ) );
            }
        }
        
        getCenterListExcel(request, response, result);
    }
    
    private void getCenterListExcel(HttpServletRequest request, HttpServletResponse response, List<CenterInfoDTO> data ) {
        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);
        
        int i = 1;
        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);
        Cell cell8 = row.createCell(7);
        Cell cell9 = row.createCell(8);
        Cell cell10 = row.createCell(9);
        Cell cell11 = row.createCell(10);
        Cell cell12 = row.createCell(11);
        Cell cell13 = row.createCell(12);
        Cell cell14 = row.createCell(13);
        
        cell1.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell2.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell3.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell4.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell5.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell6.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell7.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell8.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell9.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell10.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell11.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell12.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell13.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell14.setCellStyle(styleOfBoardFillFontBlackBold16);
        
        sheet1.setColumnWidth( 0, 10000); //생활치료센터명
        sheet1.setColumnWidth( 1, 5000); //지역
        sheet1.setColumnWidth( 2, 5000); //소관
        sheet1.setColumnWidth( 3, 8000); //협력병원
        sheet1.setColumnWidth( 4, 6000); //담당자 이름
        sheet1.setColumnWidth( 5, 4000); //담당자 아이디
        sheet1.setColumnWidth( 6, 4000); //담당자 전화번호
        sheet1.setColumnWidth( 7, 4000); //전화번호
        sheet1.setColumnWidth( 8, 2500); //의료진수
        sheet1.setColumnWidth( 9, 2500); //환자 총 수용인원
        sheet1.setColumnWidth( 10, 2500); //입소자수
        sheet1.setColumnWidth( 11, 2500); //퇴소자수
        sheet1.setColumnWidth( 12, 2500); //지정병원이송
        sheet1.setColumnWidth( 13, 2500); //기타
        
        cell1.setCellValue("생활치료센터명");
        cell2.setCellValue("지역");
        cell3.setCellValue("소관");
        cell4.setCellValue("협력병원");
        cell5.setCellValue("담당자 이름");
        cell6.setCellValue("담당자 아이디");
        cell7.setCellValue("담당자 전화번호");
        cell8.setCellValue("전화번호");
        cell9.setCellValue("의료진수");
        cell10.setCellValue("환자 총 수용인원");
        cell11.setCellValue("입소자수");
        cell12.setCellValue("퇴소자수");
        cell13.setCellValue("지정병원이송");
        cell14.setCellValue("기타");
        
        for (CenterInfoDTO 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);
            cell7 = row.createCell(6);
            cell8 = row.createCell(7);
            cell9 = row.createCell(8);
            cell10 = row.createCell(9);
            cell11 = row.createCell(10);
            cell12 = row.createCell(11);
            cell13 = row.createCell(12);
            cell14 = row.createCell(13);
            
            String centerName = dto.getCenterName();
            String locationName = dto.getLocationName();
            String jurisdictionName = dto.getJurisdictionName();
            String cooperativeName = dto.getCooperativeName();
            String staffName = dto.getStaffName();
            String staffId = dto.getStaffId();
            String staffPhoneNumber = dto.getStaffPhoneNumber();
            String centerNumber = dto.getCenterNumber();
            int medicalTotal = dto.getTotalStaff();
            int patientTotal = dto.getTotalCapacity();
            int patientTotalH = dto.getTotalPatientH();
            int patientTotalD = dto.getTotalPatientD();
            int patientTotalT = dto.getTotalPatientT();
            int patientTotalE = dto.getTotalPatientE();
            
            cell1.setCellValue(centerName);
            cell2.setCellValue(locationName);
            cell3.setCellValue(jurisdictionName);
            cell4.setCellValue(cooperativeName);
            cell5.setCellValue(staffName);
            cell6.setCellValue(staffId);
            cell7.setCellValue(staffPhoneNumber);
            cell8.setCellValue(centerNumber);
            cell9.setCellValue(medicalTotal);
            cell10.setCellValue(patientTotal);
            cell11.setCellValue(patientTotalH);
            cell12.setCellValue(patientTotalD);
            cell13.setCellValue(patientTotalT);
            cell14.setCellValue(patientTotalE);
            
            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(8);
        cell8 = row.createCell(9);
        cell9 = row.createCell(10);
        cell10 = row.createCell(11);
        cell11 = row.createCell(12);
        cell12 = row.createCell(13);
        cell13 = row.createCell(14);
        cell14 = row.createCell(15);
        
        try {
//          File file = new File(".");
//          String rootPath = file.getAbsolutePath();
//          System.out.println("현재 프로젝트의 경로 : "+rootPath );
          
          // 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();
    }
    }
}