LifeCenter/src/main/java/com/lemon/lifecenter/controller/ClinicController.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.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Set;

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

import com.lemon.lifecenter.common.LifeCenterController;
import com.lemon.lifecenter.common.LifeCenterFileDownload;
import com.lemon.lifecenter.common.LifeCenterFunction;
import com.lemon.lifecenter.common.LifeCenterSessionController;
import com.lemon.lifecenter.dto.ClinicConfigurationDTO;
import com.lemon.lifecenter.dto.FileDownloadDTO;
import com.lemon.lifecenter.dto.MentalDTO;
import com.lemon.lifecenter.dto.PatientDTO;
import com.lemon.lifecenter.dto.PatientMemoDTO;
import com.lemon.lifecenter.dto.PatientPHRHistoryDTO;
import com.lemon.lifecenter.dto.PatientPHRLatestDTO;
import com.lemon.lifecenter.dto.PatientSymptomSimDTO;
import com.lemon.lifecenter.service.ClinicService;
import com.lemon.lifecenter.service.FileDownloadService;
import com.lemon.lifecenter.service.MentalService;
import com.lemon.lifecenter.service.PHRService;
import com.lemon.lifecenter.service.PatientService;

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.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
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;

@Controller
@RequestMapping("/clinic")
public class ClinicController extends LifeCenterController {

	@Autowired
	private ClinicService clinicService;

	@Autowired
	private PHRService phrService;

	@Autowired
	private PatientService patientService;
	
	@Autowired
    private FileDownloadService fileDownloadService;
  
  @Autowired
  private MentalService mentalService;
	
	private final Logger logger = LoggerFactory.getLogger(this.getClass());

	private enum FilterType {
	    newPatient(0x01), 
	    alarm(0x02), 
	    temperatureBlank(0x04), 
	    bloodPressureBlank(0x08), 
	    pulseRateBlank(0x10), 
	    oxygenSaturationBlank(0x20), 
	    bloodSugarBlank(0x40), 
	    symptomBlank(0x80),
	    mentalBlank(0x100);
	
	    private final int value;
	
	    FilterType(int value) {
	    	this.value = value;
	    }
	
	    public int getValue() {
	    	return value;
	    }
	}

	private String[] parseFilter(String filter) {
    ArrayList<String> arrayList = new ArrayList<String>();
    try {
      int filterValue = Integer.parseInt(filter);
      for (FilterType f: FilterType.values()) {
        if ((filterValue & f.getValue()) == f.getValue()) {
          arrayList.add(f.name());
          System.out.println( f.name() );
        }
      }
    }
    catch( Exception e) {
    	// 에러시 필터가 없는 것으로 간주
    }
    
    return arrayList.toArray(new String[arrayList.size()]);
  }

	@RequestMapping("/state")
	public ModelAndView clinicState(HttpServletRequest request,
			@RequestParam(value = "page", required = true, defaultValue = "1") int page,
			@RequestParam(value = "size", required = true, defaultValue = "30") int pageSize,
			@RequestParam(value = "searchText", required = false, defaultValue = "") String searchText,
			@RequestParam(value = "filter", required = false, defaultValue = "") String filter) {


		String centerCode = LifeCenterSessionController.getSession( request, "sesCenterCode" );

		PatientPHRLatestDTO dto = new PatientPHRLatestDTO();
		dto.setLimit((Integer.valueOf(page) - 1) * pageSize);
		dto.setLimitMax(pageSize);
		dto.setCenterCode(centerCode);
		dto.setSearchText(searchText);
		if (!filter.equals("")) {
	        String[] filterList = parseFilter(filter);
          System.out.println( "#######################");
          System.out.println( filterList );
          System.out.println("#######################");
          
	        if (filterList.length > 0) {
	        	dto.setFilterList(filterList);
	        }
		}
		
		if (Calendar.getInstance().get(Calendar.HOUR_OF_DAY) < 12) {
			dto.setTimeCriterion("AM");
		} else {
			dto.setTimeCriterion("PM");
		}

		int total = clinicService.selectPHRLatestCount(dto);

		// 서버사이드 렌더링 안함 
//		List<PatientPHRLatestDTO> result = new ArrayList<PatientPHRLatestDTO>();
//
//		if (total > 0) {
//			result = clinicService.selectPHRLatestList(dto);
//		}

    // 알람 임계치 정보
    ClinicConfigurationDTO configDto = new ClinicConfigurationDTO();
		configDto.setCenterCode(Integer.parseInt(centerCode));
		configDto = clinicService.selectConfiguration(configDto);

		ModelAndView mv = setMV("clinic/state");

		mv.addObject("page", page);
		mv.addObject("searchText", searchText);
		mv.addObject("filter", filter);
		mv.addObject("total", total);
		mv.addObject("config", configDto);
//		mv.addObject("items", result);

		return mv;
	}

	@RequestMapping("/info")
	public ModelAndView patientInfo(HttpServletRequest request,
			@RequestParam(value = "patientIdx", required = true, defaultValue = "") int patientIdx,
			@RequestParam(value = "refererSearch", required = false, defaultValue = "") String refererSearch,
			@RequestParam(value = "refererPage", required = false, defaultValue = "") String refererPage,
      @RequestParam(value = "refererFilter", required = false, defaultValue = "") String refererFilter) {

    String centerCode = LifeCenterSessionController.getSession( request, "sesCenterCode" );
		
    // 환자정보
		PatientDTO patientDto = new PatientDTO();
		patientDto.setPatientIdx(patientIdx);
		patientDto = patientService.selectPatientOne(patientDto);

		String jumin = patientDto.getJumin();
		jumin = LifeCenterFunction.changeJuminToBirthday(jumin);
		patientDto.setJumin(jumin);
		
		patientDto.setPatientPhone(LifeCenterFunction.phone(patientDto.getPatientPhone()));
		patientDto.setGuardianPhone(LifeCenterFunction.phone(patientDto.getGuardianPhone()));
		
		String bloodPress = patientDto.getBloodPressureLevel();
		
    if (!bloodPress.equals("") && !bloodPress.equals("|")) {
        String[] bloodPressureLevel = bloodPress.split("[|]");
        if (bloodPressureLevel[0] != null) {
            patientDto.setBloodPressureLevelCon(bloodPressureLevel[0]);
        }
        if (bloodPressureLevel[1] != null) {
            patientDto.setBloodPressureLevelRel(bloodPressureLevel[1]);
        }
    }
    
    String strDisease = "";
    String strSymptom = LifeCenterFunction.getSymptom(patientDto);
    if (patientDto.getBasalDiseaseYn().equals("Y")) {
        strDisease = LifeCenterFunction.getDisease(patientDto);
    }

    // 히스토리 데이터
    PatientPHRHistoryDTO dto = new PatientPHRHistoryDTO();
    dto.setPatientIdx(patientIdx);
		// 체온
		dto.setPhrType("temperature");
		List<PatientPHRHistoryDTO> temperatureResult = phrService.selectPHRHistoryList(dto);
    // 혈압
    dto.setPhrType("bloodPressure");
    List<PatientPHRHistoryDTO> bloodPressureResult = phrService.selectPHRHistoryList(dto);
    // 맥박
    dto.setPhrType("pulseRate");
    List<PatientPHRHistoryDTO> pulseRateResult = phrService.selectPHRHistoryList(dto);
    // 산소포화도
    dto.setPhrType("oxygenSaturation");
    List<PatientPHRHistoryDTO> oxygenSaturationResult = phrService.selectPHRHistoryList(dto);
    // 혈당
    dto.setPhrType("bloodSugar");
    List<PatientPHRHistoryDTO> bloodSugarResult = phrService.selectPHRHistoryList(dto);
    // 증상
    PatientSymptomSimDTO dto2 = new PatientSymptomSimDTO();
		dto2.setPatientIdx(patientIdx);
    List<PatientSymptomSimDTO> symptomResult = phrService.selectSymptomList(dto2);
    // 메모
    PatientMemoDTO dto3 = new PatientMemoDTO();
		dto3.setPatientIdx(patientIdx);
		List<PatientMemoDTO> memoResult = clinicService.selectMemoList(dto3);

    // response
    List<PatientPHRHistoryDTO> bloodPressureUnionResult = getBloodPressureUnionResult(patientIdx, bloodPressureResult, pulseRateResult);


    // 알람 임계치 정보
    ClinicConfigurationDTO configDto = new ClinicConfigurationDTO();
		configDto.setCenterCode(Integer.parseInt(centerCode));
		configDto = clinicService.selectConfiguration(configDto);


    // 정신건강 자가진단 추가 21.06.17
    MentalDTO mDTO = new MentalDTO();
    mDTO.setPatientIdx(patientIdx);
    List<MentalDTO> mentalList = new ArrayList<MentalDTO>();
    int mentalTotal = mentalService.selectMentalHealthTotalCount(mDTO);
    if( mentalTotal > 0 ) {
      mentalList = mentalService.selectMentalHealthTotalList(mDTO);
    }
    
		ModelAndView mv = setMV("clinic/info");

    // 정신건강 항목 데이터
    List<MentalDTO> mentalCheckList = mentalService.selectMentalHealthCheckList(mDTO);
    mv.addObject("mentalCheckList", mentalCheckList);

		mv.addObject("patientIdx", patientIdx);
    mv.addObject("info", patientDto);
		mv.addObject("symptom", strSymptom);
		mv.addObject("disease", strDisease);
		mv.addObject("temperatureResult", temperatureResult);
    mv.addObject("bloodPressureResult", bloodPressureResult);
    mv.addObject("pulseRateResult", pulseRateResult);
    mv.addObject("bloodPressureUnionResult", bloodPressureUnionResult);
    mv.addObject("oxygenSaturationResult", oxygenSaturationResult);
    mv.addObject("bloodSugarResult", bloodSugarResult);
    mv.addObject("symptomResult", symptomResult);
    mv.addObject("memoResult", memoResult);
    mv.addObject("refererSearch", refererSearch);
		mv.addObject("refererPage", refererPage);
    mv.addObject("refererFilter", refererFilter);
    mv.addObject("config", configDto);
    mv.addObject("mentalTotal" , mentalTotal);
    mv.addObject("mentalList" , mentalList);

		return mv;
	}

  @RequestMapping("/config")
	public ModelAndView clinicState(HttpServletRequest request) {

		String centerCode = LifeCenterSessionController.getSession( request, "sesCenterCode" );

		ClinicConfigurationDTO dto = new ClinicConfigurationDTO();
		dto.setCenterCode(Integer.parseInt(centerCode));
		
		dto = clinicService.selectConfiguration(dto);

		ModelAndView mv = setMV("clinic/config");

		mv.addObject("config", dto);

		return mv;
	}

	@RequestMapping("/api/state")
	public @ResponseBody String getStateAPI(HttpServletRequest request,
			@RequestParam(value = "page", required = true, defaultValue = "1") int page,
			@RequestParam(value = "size", required = true, defaultValue = "30") int pageSize,
			@RequestParam(value = "searchText", required = false, defaultValue = "") String searchText,
			@RequestParam(value = "filter", required = false, defaultValue = "") String filter,
			@RequestParam(value = "sortType", required = false, defaultValue = "") String sortType) {

		String centerCode = LifeCenterSessionController.getSession( request, "sesCenterCode" );

		PatientPHRLatestDTO dto = new PatientPHRLatestDTO();
		dto.setLimit((Integer.valueOf(page) - 1) * pageSize);
		dto.setLimitMax(pageSize);
		dto.setCenterCode(centerCode);
		dto.setSearchText(searchText);
		if (!filter.equals("")) {
	        String[] filterList = parseFilter(filter);
	        if (filterList.length > 0) {
	        	dto.setFilterList(filterList);
	        }
		}
		if (Calendar.getInstance().get(Calendar.HOUR_OF_DAY) < 12) {
			dto.setTimeCriterion("AM");
		} else {
			dto.setTimeCriterion("PM");
		}
		
		dto.setSortType(sortType);

		int total = clinicService.selectPHRLatestCount(dto);
		List<PatientPHRLatestDTO> result = new ArrayList<PatientPHRLatestDTO>();

		if (total > 0) {
			result = clinicService.selectPHRLatestList(dto);
		}
		
		JSONObject json = new JSONObject();

		json.put("count", total);
		json.put("items", result);
		
		return json.toString();
	}

	@RequestMapping("/api/phrDatas")
	public @ResponseBody List<PatientPHRHistoryDTO> getPhrDatasAPI(
			@RequestParam(value = "patientIdx", required = true, defaultValue = "") int patientIdx,
			@RequestParam(value = "phrType", required = true, defaultValue = "temperature") String phrType) {

		PatientPHRHistoryDTO dto = new PatientPHRHistoryDTO();
		dto.setPatientIdx(patientIdx);
    dto.setPhrType(phrType);

    List<PatientPHRHistoryDTO> result = phrService.selectPHRHistoryList(dto);
    
    boolean needPulseRate = phrType.equals("bloodPressure");
    if (needPulseRate) {
      PatientPHRHistoryDTO dto2 = new PatientPHRHistoryDTO();
		  dto2.setPatientIdx(patientIdx);
      dto2.setPhrType("pulseRate");

      List<PatientPHRHistoryDTO> pulseRateResult = phrService.selectPHRHistoryList(dto2);
      List<PatientPHRHistoryDTO> bloodPressureUnionResult = getBloodPressureUnionResult(patientIdx, result, pulseRateResult);
      return bloodPressureUnionResult;
    }
    else {
      return result;
    }

	}

	@RequestMapping(value = "/api/phrData", method = RequestMethod.POST)
	public @ResponseBody String insertPhrDataAPI(@ModelAttribute("dto") final PatientPHRHistoryDTO dto) {

		try {
			phrService.insertPHR(dto);
			
			if (dto.getPhrValueExtra() != null) {
		        PatientPHRHistoryDTO pulseRateDto = new PatientPHRHistoryDTO();
		        pulseRateDto.setPatientIdx(dto.getPatientIdx());
		        pulseRateDto.setPhrType("pulseRate");
		        pulseRateDto.setPhrValue(dto.getPhrValueExtra());
		        pulseRateDto.setRecordedByName(dto.getRecordedByName());
		        if (dto.getRecordedById() != null) {
		        	pulseRateDto.setRecordedById(dto.getRecordedById());
		        }
		        if (dto.getRecordedDate() != null) {
		        	pulseRateDto.setRecordedDate(dto.getRecordedDate());
		        }
		        phrService.insertPHR(pulseRateDto);
			}

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
	}

	@RequestMapping("/api/symptomDatas")
	public @ResponseBody List<PatientSymptomSimDTO> getSymptomDatasAPI(
			@RequestParam(value = "patientIdx", required = true, defaultValue = "") int patientIdx) {

		PatientSymptomSimDTO dto = new PatientSymptomSimDTO();
		dto.setPatientIdx(patientIdx);

		return phrService.selectSymptomList(dto);

	}

	@RequestMapping(value = "/api/symptomData", method = RequestMethod.POST)
	public @ResponseBody String insertSymptomDataAPI(@ModelAttribute("dto") final PatientSymptomSimDTO dto) {

		try {
			phrService.insertSymptom(dto);

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
	}

	@RequestMapping("/api/memoDatas")
	public @ResponseBody List<PatientMemoDTO> getMemoDatasAPI(
			@RequestParam(value = "patientIdx", required = true, defaultValue = "") int patientIdx) {

		PatientMemoDTO dto = new PatientMemoDTO();
		dto.setPatientIdx(patientIdx);

		return clinicService.selectMemoList(dto);
	}

	@RequestMapping(value = "/api/memoData", method = RequestMethod.POST)
	public @ResponseBody String insertMemoDataAPI(@ModelAttribute("dto") final PatientMemoDTO dto) {

		try {
			clinicService.insertMemo(dto);

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
	}

  @RequestMapping(value = "/api/memoData", method = RequestMethod.DELETE)
	public @ResponseBody String deleteMemoDataAPI(@ModelAttribute("dto") final PatientMemoDTO dto) {

		try {
			clinicService.deleteMemo(dto);

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
	}

  @RequestMapping(value = "/api/memoData", method = RequestMethod.PATCH)
	public @ResponseBody String updateMemoDataAPI(@ModelAttribute("dto") final PatientMemoDTO dto) {

		try {
			clinicService.updateMemo(dto);

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
  }
  
  private List<PatientPHRHistoryDTO> getBloodPressureUnionResult(int patientIdx, List<PatientPHRHistoryDTO> bloodPressureResult, List<PatientPHRHistoryDTO>pulseRateResult) {
    // 혈압 맥박 정리
    int bloodPressureIndex = 0;
    int pulseRateIndex = 0;
    List<PatientPHRHistoryDTO> bloodPressureUnionResult = new ArrayList<PatientPHRHistoryDTO>();
    while (bloodPressureIndex < bloodPressureResult.size() || pulseRateIndex < pulseRateResult.size()) {
      PatientPHRHistoryDTO union = new PatientPHRHistoryDTO();
      union.setPatientIdx(patientIdx);
      union.setPhrType("bloodPressure");
      
      if (bloodPressureIndex == bloodPressureResult.size()) {
        PatientPHRHistoryDTO pr = pulseRateResult.get(pulseRateIndex);
        union.setCreateDate(pr.getCreateDate());
        union.setRecordedDate(pr.getRecordedDate());
        union.setPhrValueExtra(pr.getPhrValue());
        union.setIsExtraWarning(pr.getIsWarning());
        union.setRecordedByName(pr.getRecordedByName());
        union.setRecordedById(pr.getRecordedById());

        bloodPressureUnionResult.add(union);
        pulseRateIndex += 1;
        continue;
      }
      else if (pulseRateIndex == pulseRateResult.size()) {
        PatientPHRHistoryDTO bp = bloodPressureResult.get(bloodPressureIndex);
        union.setCreateDate(bp.getCreateDate());
        union.setRecordedDate(bp.getRecordedDate());
        union.setPhrValue(bp.getPhrValue());
        union.setPhrValue2(bp.getPhrValue2());
        union.setIsWarning(bp.getIsWarning());
        union.setRecordedByName(bp.getRecordedByName());
        union.setRecordedById(bp.getRecordedById());

        bloodPressureUnionResult.add(union);
        bloodPressureIndex += 1;
        continue;
      }

      PatientPHRHistoryDTO bp = bloodPressureResult.get(bloodPressureIndex);
      PatientPHRHistoryDTO pr = pulseRateResult.get(pulseRateIndex);

      SimpleDateFormat originalFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
      SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

      String bpRecordedDateFormatted = bp.getRecordedDate();
      String prRecordedDateFormatted = pr.getRecordedDate();
      try {
        Date t = originalFormat.parse(bpRecordedDateFormatted);
        bpRecordedDateFormatted = targetFormat.format(t);
        Date t2 = originalFormat.parse(prRecordedDateFormatted);
        prRecordedDateFormatted = targetFormat.format(t2);
      }
      catch (Exception e) {
      }

      int dateDiffer = bpRecordedDateFormatted.compareTo(prRecordedDateFormatted);
      if (dateDiffer == 0) {
        union.setCreateDate(bp.getCreateDate());
        union.setRecordedDate(bp.getRecordedDate());
        union.setPhrValue(bp.getPhrValue());
        union.setPhrValue2(bp.getPhrValue2());
        union.setIsWarning(bp.getIsWarning());
        union.setRecordedByName(bp.getRecordedByName());
        union.setRecordedById(bp.getRecordedById());

        union.setPhrValueExtra(pr.getPhrValue());
        union.setIsExtraWarning(pr.getIsWarning());

        bloodPressureIndex += 1;
        pulseRateIndex += 1;
      }
      else if (dateDiffer < 0) {
        union.setCreateDate(bp.getCreateDate());
        union.setRecordedDate(bp.getRecordedDate());
          union.setPhrValue(bp.getPhrValue());
          union.setPhrValue2(bp.getPhrValue2());
          union.setIsWarning(bp.getIsWarning());
          union.setRecordedByName(bp.getRecordedByName());
          union.setRecordedById(bp.getRecordedById());

          bloodPressureIndex += 1;
      }
      else if (dateDiffer > 0) {
        union.setCreateDate(pr.getCreateDate());
        union.setRecordedDate(pr.getRecordedDate());
          union.setPhrValueExtra(pr.getPhrValue());
          union.setIsExtraWarning(pr.getIsWarning());
          union.setRecordedByName(pr.getRecordedByName());
          union.setRecordedById(pr.getRecordedById());

          pulseRateIndex += 1;
      }

      bloodPressureUnionResult.add(union);
    }
    return bloodPressureUnionResult;
  }
  
	@RequestMapping("/excel")
	public void getExcelFile(
			@RequestParam(value="phrType", required=true, defaultValue="temperature") String phrType,
      @RequestParam(value="patientIdx", required=true, defaultValue="") int patientIdx,
      @RequestParam(value="downMemo", required=true, defaultValue="") String downMemo,
			HttpServletRequest request, HttpServletResponse response ) {

		PatientDTO patientDto = new PatientDTO();
		patientDto.setPatientIdx(patientIdx);
	    patientDto = patientService.selectPatientOne(patientDto);		
	    
	    //excel 다운로드 로그 남기기
	    String userId = LifeCenterSessionController.getSession(request, "sesId");
	
	    FileDownloadDTO fileDTO = new FileDownloadDTO();
	    fileDTO.setId( userId );
	    fileDTO.setIp( LifeCenterFunction.getRemoteAddr( request ) );
	    fileDTO.setUrl( request.getRequestURI().toString() );
	    fileDTO.setMemo( downMemo );
	    fileDTO.setEtc( "진료관리, " + patientDto.getPatientName() + ", " + patientDto.getJumin() + ", " + Integer.toString(patientDto.getCenterCode()) );
	    fileDTO.setPatientIdx( patientIdx );
	    fileDownloadService.insertExcelDownloadLog( fileDTO );
		
		this.createExcel(request, response, patientDto);
	}

	private void createExcel(HttpServletRequest request, HttpServletResponse response, PatientDTO patientDto) {
        Workbook workbook = new XSSFWorkbook();
        
        // 셀 스타일 및 폰트 설정
        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);
        
        this.createPHRSheet(workbook, styleOfBoardFillFontBlackBold16, patientDto, "체온", "temperature");
        this.createPHRSheet(workbook, styleOfBoardFillFontBlackBold16, patientDto, "혈압", "bloodPressure");
        this.createPHRSheet(workbook, styleOfBoardFillFontBlackBold16, patientDto, "맥박", "pulseRate");
        this.createPHRSheet(workbook, styleOfBoardFillFontBlackBold16, patientDto, "산소포화도", "oxygenSaturation");        
        this.createMentalSheet(workbook, styleOfBoardFillFontBlackBold16, patientDto);
        this.createPHRSheet(workbook, styleOfBoardFillFontBlackBold16, patientDto, "혈당", "bloodSugar");
        
        this.createSymptomSheet(workbook, styleOfBoardFillFontBlackBold16, patientDto);
        
        this.createMemoSheet(workbook, styleOfBoardFillFontBlackBold16, patientDto);
        
        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 directoryName = "../excel-temp/";
        	File directory = new File(directoryName);
        	if (! directory.exists()) {
        		directory.mkdir();
        	}
        	
			 String password = LifeCenterSessionController.getSession(request, "sesPhoneNumber");
			 password = password.toLowerCase();
			 if (!password.equals("null")) {
				 password = password.replace("-", "");
			     password = password.substring(3).trim();
			 } else {
			     password = "";
			 }
        	
			String timestamp = LifeCenterFunction.getNow("yyyy-MM-dd_HH-mm-ss");
			// 다운로드 파일 명: 호실_환자명(생년월일)_건강정보기록_다운로드일시.xlsx
			String downName = patientDto.getRoomNumber() + "_" + patientDto.getPatientName() + "(" + patientDto.getJumin() + ")_건강정보기록_" + timestamp + ".xlsx";
			String tempPath = directoryName + downName;          
      
            File xlsFile = new File(tempPath);
//            FileOutputStream fileOut = new FileOutputStream(xlsFile);
//            workbook.write(fileOut);
//
//            LifeCenterFileDownload.download(request, response, tempPath, downName);
//
//            xlsFile.delete();
            
            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) {
			e.printStackTrace();
		} catch (GeneralSecurityException e) {
			e.printStackTrace();
		}
	}
	
    private void createPHRSheet(Workbook workbook, CellStyle styleOfBoardFillFontBlackBold16, PatientDTO patientDto, String phrTypeKorean, String phrType) {
    	PatientPHRHistoryDTO patientPHRHistoryDto = new PatientPHRHistoryDTO();
    	patientPHRHistoryDto.setPhrType(phrType);
    	patientPHRHistoryDto.setPatientIdx(patientDto.getPatientIdx());		
		
		int total = phrService.selectPHRHistoryCount(patientPHRHistoryDto);
		List<PatientPHRHistoryDTO> data = new ArrayList<PatientPHRHistoryDTO>();

		if (total > 0) {
			data = phrService.selectPHRHistoryList(patientPHRHistoryDto);
		}
		
        Sheet sheet1 = workbook.createSheet(phrTypeKorean);
        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, 5000); // 생년월일
        sheet1.setColumnWidth( 2, 5000); // 동, 호실
        sheet1.setColumnWidth( 3, 5000); // 측정일시
        sheet1.setColumnWidth( 4, 3000); // 값
        sheet1.setColumnWidth( 5, 4000); // 기록자
        
        cell1.setCellValue("환자명");
        cell2.setCellValue("생년월일");
        cell3.setCellValue("동,호실");
        cell4.setCellValue("측정일시");
        cell5.setCellValue(phrTypeKorean); 
        cell6.setCellValue("기록자");
        
        // 동,호실
		String roomNumber = "";
		if (!patientDto.getWardNumber().equals("")) {
			roomNumber = patientDto.getWardNumber() + "동";
		}
		roomNumber += patientDto.getRoomNumber() + "호";
        
		int i = 1;
        for (PatientPHRHistoryDTO 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);

			// 일시
			String recordedDate = null;
			SimpleDateFormat originalFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
			SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			try {
				Date t = originalFormat.parse(dto.getRecordedDate());
				recordedDate =  targetFormat.format(t);
			} catch (Exception e) {
				recordedDate = dto.getRecordedDate();
			}

			// 값
			String phrValue = "";
			String phrValue2 = "";
			if (phrType.equals("temperature")) {
				phrValue = String.format("%.1f", dto.getPhrValue());
				phrValue2 = String.format("%.1f", dto.getPhrValue2());
			} else {
				phrValue = String.format("%.0f", dto.getPhrValue());
				phrValue2 = String.format("%.0f", dto.getPhrValue2());
			}

			// 기록자
			String recordedByName = dto.getRecordedByName();

			cell1.setCellValue(patientDto.getPatientName());
			cell2.setCellValue(patientDto.getJumin());
			cell3.setCellValue(roomNumber);
			
			cell4.setCellValue(recordedDate);
			if (phrType.equals("bloodPressure")) {
				cell5.setCellValue(phrValue + "/" + phrValue2);
			} else {
				cell5.setCellValue(phrValue);
			}
			
            cell6.setCellValue(recordedByName);

            i++;
        }
    }
	
	private void createSymptomSheet(Workbook workbook, CellStyle styleOfBoardFillFontBlackBold16, PatientDTO patientDto) {
        
		PatientSymptomSimDTO patientSymptomSimDto = new PatientSymptomSimDTO();
		patientSymptomSimDto.setPatientIdx(patientDto.getPatientIdx());		
		
		int total = phrService.selectSymptomCount(patientSymptomSimDto);
		List<PatientSymptomSimDTO> data = new ArrayList<PatientSymptomSimDTO>();

		if (total > 0) {
			data = phrService.selectSymptomList(patientSymptomSimDto);
		}
				
        Sheet sheet1 = workbook.createSheet("임상증상");               
        
        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);
        Cell cell15 = row.createCell(14);
        Cell cell16 = row.createCell(15);
        Cell cell17 = row.createCell(16);
        Cell cell18 = row.createCell(17);
        Cell cell19 = row.createCell(18);
        Cell cell20 = row.createCell(19);
        Cell cell21 = row.createCell(20);

        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);
        cell15.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell16.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell17.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell18.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell19.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell20.setCellStyle(styleOfBoardFillFontBlackBold16);
        cell21.setCellStyle(styleOfBoardFillFontBlackBold16);

        sheet1.setColumnWidth(0, 5000); // 환자명
        sheet1.setColumnWidth(1, 5000); // 생년월일
        sheet1.setColumnWidth(2, 5000); // 동호실
        sheet1.setColumnWidth(3, 5000); // 측정일시
        sheet1.setColumnWidth(4, 3000); // 기침
        sheet1.setColumnWidth(5, 3000); // 호흡곤란
        sheet1.setColumnWidth(6, 3000); // 오한	
        sheet1.setColumnWidth(7, 3000); // 근육통	
        sheet1.setColumnWidth(8, 3000); // 두통
        sheet1.setColumnWidth(9, 3000); // 인후통
        sheet1.setColumnWidth(10, 3000); // 후각/미각 손실									
        sheet1.setColumnWidth(11, 3000); // 피로
        sheet1.setColumnWidth(12, 3000); // 식욕감소
        sheet1.setColumnWidth(13, 3000); // 가래
        sheet1.setColumnWidth(14, 3000); // 오심	
        sheet1.setColumnWidth(15, 3000); // 구토	
        sheet1.setColumnWidth(16, 3000); // 설사
        sheet1.setColumnWidth(17, 3000); // 어지러움
        sheet1.setColumnWidth(18, 3000); // 콧물/코막힘
        sheet1.setColumnWidth(19, 3000); // 기타증상
        sheet1.setColumnWidth(20, 5000); // 기록자
        
        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("가래");
        cell15.setCellValue("오심");
        cell16.setCellValue("구토");
        cell17.setCellValue("설사");
        cell18.setCellValue("어지러움");        
        cell19.setCellValue("콧물/코막힘");  
        cell20.setCellValue("기타증상");
        cell21.setCellValue("기록자");
        
        // 동,호실
		String roomNumber = "";
		if (!patientDto.getWardNumber().equals("")) {
			roomNumber = patientDto.getWardNumber() + "동";
		}
		roomNumber += patientDto.getRoomNumber() + "호";
     			
		int i = 1;
        for (int index = data.size() - 1; index >= 0; index--) {
        	PatientSymptomSimDTO dto = data.get(index);
            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);
            cell15 = row.createCell(14);
			cell16 = row.createCell(15);
			cell17 = row.createCell(16);
			cell18 = row.createCell(17);
			cell19 = row.createCell(18);
			cell20 = row.createCell(19);
			cell21 = row.createCell(20);
			
			String recordedDate = null;
			SimpleDateFormat originalFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
			SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			try {
				Date t = originalFormat.parse(dto.getRecordedDate());
				recordedDate =  targetFormat.format(t);
			} catch (Exception e) {
				recordedDate = dto.getRecordedDate();
			}			
			
			cell1.setCellValue(patientDto.getPatientName());
			cell2.setCellValue(patientDto.getJumin());
			cell3.setCellValue(roomNumber);			
			cell4.setCellValue(recordedDate);			
			cell5.setCellValue(dto.getCoughCheck().equals("Y") ? "V" : "-");
			cell6.setCellValue(dto.getDyspneaCheck().equals("Y") ? "V" : "-");
			cell7.setCellValue(dto.getColdFitCheck().equals("Y") ? "V" : "-");
			cell8.setCellValue(dto.getMusclePainCheck().equals("Y") ? "V" : "-");
			cell9.setCellValue(dto.getHeadacheCheck().equals("Y") ? "V" : "-");
			cell10.setCellValue(dto.getSoreThroatCheck().equals("Y") ? "V" : "-");
			cell11.setCellValue(dto.getSmellPalateCheck().equals("Y") ? "V" : "-");
			cell12.setCellValue(dto.getFatigueCheck().equals("Y") ? "V" : "-");
			cell13.setCellValue(dto.getAppetiteLossCheck().equals("Y") ? "V" : "-");
			cell14.setCellValue(dto.getSputumCheck().equals("Y") ? "V" : "-");
			cell15.setCellValue(dto.getOcinCheck().equals("Y") ? "V" : "-");
			cell16.setCellValue(dto.getVomitingCheck().equals("Y") ? "V" : "-");
			cell17.setCellValue(dto.getDiarrheaCheck().equals("Y") ? "V" : "-");
			cell18.setCellValue(dto.getDizzinessCheck().equals("Y") ? "V" : "-");
			cell19.setCellValue(dto.getNoseCheck().equals("Y") ? "V" : "-");
			cell20.setCellValue(dto.getEtcCheck().equals("Y") ? dto.getEtcContent() : "-");			
			cell21.setCellValue(dto.getRecordedByName());

            i++;
        }
    }
	
	private void createMemoSheet(Workbook workbook, CellStyle styleOfBoardFillFontBlackBold16, PatientDTO patientDto) {
        
		PatientMemoDTO patientMemoDto = new PatientMemoDTO();
		patientMemoDto.setPatientIdx(patientDto.getPatientIdx());		
		
		List<PatientMemoDTO> data = new ArrayList<PatientMemoDTO>();
		data = clinicService.selectMemoList(patientMemoDto);	
				
        Sheet sheet1 = workbook.createSheet("의료진 메모");               
        
        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, 5000); // 생년월일
        sheet1.setColumnWidth(2, 5000); // 동호실
        sheet1.setColumnWidth(3, 5000); // 처리일시
        sheet1.setColumnWidth(4, 20000); // 내용 
        sheet1.setColumnWidth(5, 3000); // 기록자
              
        cell1.setCellValue("환자명");
        cell2.setCellValue("생년월일");
        cell3.setCellValue("동,호실");
        cell4.setCellValue("처리일시");
        cell5.setCellValue("내용");
        cell6.setCellValue("기록자");
        
        // 동,호실
		String roomNumber = "";
		if (!patientDto.getWardNumber().equals("")) {
			roomNumber = patientDto.getWardNumber() + "동";
		}
		roomNumber += patientDto.getRoomNumber() + "호";
        
		int i = 1;
        for (int index = data.size() - 1; index >= 0; index--) {
        	PatientMemoDTO dto = data.get(index);
            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);
			
			String recordedDate = null;
			SimpleDateFormat originalFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
			SimpleDateFormat targetFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
			try {
				Date t = originalFormat.parse(dto.getRecordedDate());
				recordedDate =  targetFormat.format(t);
			} catch (Exception e) {
				recordedDate = dto.getRecordedDate();
			}			
			
			cell1.setCellValue(patientDto.getPatientName());
			cell2.setCellValue(patientDto.getJumin());
			cell3.setCellValue(roomNumber);
			cell4.setCellValue(recordedDate);
			cell5.setCellValue(dto.getContents());		
			cell6.setCellValue(dto.getRecordedByName());			
			
			CellStyle cs = workbook.createCellStyle();
		    cs.setWrapText(true);
		    cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); //높이 가운데 정렬
		    cell5.setCellStyle(cs);

            i++;
        }
    }
	

    private void createMentalSheet(Workbook workbook, CellStyle styleOfBoardFillFontBlackBold16, PatientDTO patientDto) {
      // 정신건강 자가진단 추가 21.06.17
      MentalDTO mDTO = new MentalDTO();
      mDTO.setPatientIdx(patientDto.getPatientIdx());
      List<MentalDTO> data = new ArrayList<MentalDTO>();
      int mentalTotal = mentalService.selectMentalHealthTotalCount(mDTO);
      if (mentalTotal > 0) {
        data = mentalService.selectMentalHealthTotalList(mDTO);
      }

      Sheet sheet1 = workbook.createSheet("정신건강");

      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);

      cell1.setCellStyle(styleOfBoardFillFontBlackBold16);
      cell2.setCellStyle(styleOfBoardFillFontBlackBold16);
      cell3.setCellStyle(styleOfBoardFillFontBlackBold16);
      cell4.setCellStyle(styleOfBoardFillFontBlackBold16);
      cell5.setCellStyle(styleOfBoardFillFontBlackBold16);
      cell6.setCellStyle(styleOfBoardFillFontBlackBold16);
      cell7.setCellStyle(styleOfBoardFillFontBlackBold16);
      cell8.setCellStyle(styleOfBoardFillFontBlackBold16);

      sheet1.setColumnWidth(0, 5000); // 측정일시
      sheet1.setColumnWidth(1, 7000); // 외상후 스트레스 증상(총점)
      sheet1.setColumnWidth(2, 4000); // 우울(총점)
      sheet1.setColumnWidth(3, 5000); // 우울(9번 문항)
      sheet1.setColumnWidth(4, 3000); // VAS
      sheet1.setColumnWidth(5, 4000); // 상담연계동의
      sheet1.setColumnWidth(6, 4000); // 기록자
      sheet1.setColumnWidth(7, 5000); // 기록일

      cell1.setCellValue("측정일시");
      cell2.setCellValue("외상후 스트레스 증상(총점)");
      cell3.setCellValue("우울(총점)");
      cell4.setCellValue("우울(9번 문항)");
      cell5.setCellValue("VAS");
      cell6.setCellValue("상담연계동의");
      cell7.setCellValue("기록자");
      cell8.setCellValue("기록일");


      int i = 1;
      for (MentalDTO 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);

        cell1.setCellValue( dto.getRecordedDate() );
        cell2.setCellValue( dto.getStressTotal() );
        cell3.setCellValue( dto.getDepressedTotal() );
        cell4.setCellValue( dto.getDepressedNine() );
        cell5.setCellValue( dto.getVasTotal() );
        cell6.setCellValue( dto.getAgreeYn().equals("Y") ? "동의" : "비동의" );
        cell7.setCellValue( dto.getCreatedBy() );
        cell8.setCellValue( dto.getCreateDate() );

        CellStyle cs = workbook.createCellStyle();
        cs.setWrapText(true);
        cs.setVerticalAlignment(CellStyle.VERTICAL_CENTER); // 높이 가운데 정렬
        cell5.setCellStyle(cs);

        i++;
      }
    }


	@RequestMapping("/api/configuration")
	public @ResponseBody ClinicConfigurationDTO getConfigurationAPI(
			@RequestParam(value = "centerCode", required = true, defaultValue = "") int centerCode) {

		ClinicConfigurationDTO dto = new ClinicConfigurationDTO();
		dto.setCenterCode(centerCode);
		
		return clinicService.selectConfiguration(dto);
	}

	@RequestMapping(value = "/api/configuration", method = RequestMethod.POST)
	public @ResponseBody String insertConfigurationAPI(@ModelAttribute("dto") final ClinicConfigurationDTO dto) {

		try {
			clinicService.insertConfiguration(dto);

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
	}

	@RequestMapping(value = "/api/configuration", method = RequestMethod.DELETE)
	public @ResponseBody String deleteConfigurationAPI(@ModelAttribute("dto") final ClinicConfigurationDTO dto) {

		try {
			clinicService.deleteConfiguration(dto);

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
	}

	@RequestMapping(value = "/api/configuration/display", method = RequestMethod.PATCH)
	public @ResponseBody String updateDisplayConfigurationAPI(@ModelAttribute("dto") final ClinicConfigurationDTO dto) {

		try {
			clinicService.updateDisplayConfiguration(dto);

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
    }
	
	@RequestMapping(value = "/api/configuration/threshold", method = RequestMethod.PATCH)
	public @ResponseBody String updateThresholdConfigurationAPI(@ModelAttribute("dto") final ClinicConfigurationDTO dto) {

		try {
			clinicService.updateThresholdConfiguration(dto);

			JSONObject json = new JSONObject();

			json.put("code", "00");
			json.put("message", "");

			return json.toString();
		} catch (Exception e) {
			JSONObject json = new JSONObject();

			json.put("code", "01");
			json.put("message", e.getLocalizedMessage());

			return json.toString();
		}
  }


  @RequestMapping(value = "/api/mentalDetail", method = RequestMethod.POST)
  public @ResponseBody String selectMentalRecord(
    @RequestParam(value = "totalIdx", required = true ) int totalIdx,
    HttpServletRequest request ) {
      try {
        MentalDTO dto = new MentalDTO();
        dto.setTotalIdx(totalIdx);
  
        JSONObject json = new JSONObject();
  
        json.put("code", "00");
        json.put("data", mentalService.selectMentalHealthRecord(dto) );
  
        return json.toString();
        
      } catch (Exception e) {
        JSONObject json = new JSONObject();

        json.put("code", "01");
        json.put("message", e.getLocalizedMessage());

        return json.toString();
      }

  }

  @RequestMapping(value = "/api/mentalInsert", method = RequestMethod.POST)
  public @ResponseBody String insertMentalData(@ModelAttribute("dto") MentalDTO dto, HttpServletRequest request ) {
      try{
        Set<String> keySet = request.getParameterMap().keySet();
        String createdBy = LifeCenterSessionController.getSession(request, "sesName");
        String recordId = LifeCenterSessionController.getSession(request, "sesId");
        int patientIdx = dto.getPatientIdx();
  
        List<MentalDTO> mentalList = mentalService.selectMentalHealthCheckList(dto);
        HashMap<String, String> ml = new HashMap<String, String>();
  
        for (MentalDTO d : mentalList) {
          ml.put(d.getItemCode(), d.getEtcCode());
        }
  
        int stressTotal = 0;
        int depressedTotal = 0;
        int depressedNine = 0;
        int vasTotal = 0;
        String agreeYn = "";
        String recordedDate = "";
  
        // total 통계부터 insert 계산
        for (String key : keySet) {
          if (key.equals("recordedDate")) {
            System.out.println( "request.getParameter(key) : " + request.getParameter(key) );
            recordedDate = request.getParameter(key).equals("") ? LifeCenterFunction.getNow("yyyy-MM-dd HH:mm").toString(): request
                .getParameter(key);
            continue;
          }
          if (key.equals("patientIdx")) continue;
  
          dto = new MentalDTO();
          String itemCode = key.trim();
          
          int itemScore = Integer.valueOf(request.getParameter(key));
  
          if (itemCode.equals("agree")) {
            agreeYn = itemScore == 1 ? "Y" : "N";
            continue;
          }
          
  
          if (ml.get(key).equals("stress")) {
            stressTotal += itemScore;
          } else if (ml.get(key).equals("depressed")) {
            // 우울-> 9번항목 itemCode는 A0016이다
            if (key.equals("A0016")) {
              depressedNine = itemScore;
            }
  
            // 우울 마지막항목 itemCode는 A0017이다 마지막항목은 총점제외
            if (!itemCode.equals("A0017")) {
              depressedTotal += itemScore;
            }
          } else if (ml.get(key).equals("vas")) {
            vasTotal += itemScore;
          }
  
        }
        
        dto.setPatientIdx(patientIdx);
        dto.setCreatedBy(createdBy);
        dto.setStressTotal(stressTotal);
        dto.setDepressedTotal(depressedTotal);
        dto.setDepressedNine(depressedNine);
        dto.setVasTotal(vasTotal);
        dto.setAgreeYn(agreeYn);
        dto.setRecordId(recordId);
        mentalService.insertMentalHealthTotal(dto);
        int totalIdx = dto.getIdx();
  
        // detail 상세점수
        for (String key : keySet) {
          if (key.equals("patientIdx"))
            continue;
          if (key.equals("agree"))
            continue;
          if (key.equals("recordedDate"))
            continue;
  
          dto = new MentalDTO();
          int itemScore = Integer.valueOf(request.getParameter(key));
          String itemCode = key.trim();
  
          dto.setTotalIdx(totalIdx);
          dto.setPatientIdx(patientIdx);
          dto.setCreatedBy(createdBy);
          dto.setItemCode(itemCode);
          dto.setItemScore(itemScore);
  
          mentalService.insertMentalHealthRecord(dto);
        }
  
        JSONObject json = new JSONObject();
  
        json.put("createDate", LifeCenterFunction.getNow("yyyy-MM-dd HH:mm").toString() );
        json.put("stressTotal", stressTotal);
        json.put("depressedTotal", depressedTotal);
        json.put("depressedNine", depressedNine);
        json.put("vasTotal", vasTotal);
        json.put("agreeYn", agreeYn);
        json.put("createdBy", createdBy);
        json.put("recordedDate", recordedDate);
        json.put("totalIdx", totalIdx);
        
  
        json.put("code", "00");
        json.put("message", "");

        return json.toString();

      } catch (Exception e) {
        JSONObject json = new JSONObject();

        json.put("code", "01");
        json.put("message", e.getLocalizedMessage());

        return json.toString();
      } 
  }
}