package com.madeu.view.web;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.servlet.view.AbstractView;

import com.madeu.util.ConvertStringUtil;

import jakarta.servlet.ServletOutputStream;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class WebStockCloseDetailView extends AbstractView{
	
	private static final String CONTENT_TYPE_XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
	 
	@Override
	protected void renderMergedOutputModel(Map<String, Object> model, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		
		// 출력 데이터
		Map<String, Object> map = (Map<String, Object>)model.get("map");
		String filename = (String)map.get("name");
		Map<String, Object> maps = (Map<String, Object>) map.get("selectListStockCloseDetail");
		List<Map<String, Object>> selectList = (List<Map<String, Object>>)maps.get("rows");
		
		int rowCount = 0;   // row
		int colCount = 0;   // col

		//1차로 workbook을 생성
		XSSFWorkbook workbook = new XSSFWorkbook();
		
		setContentType(CONTENT_TYPE_XLSX);
		
		//2차는 sheet생성
		Sheet sheet = workbook.createSheet(maps.get("year") + "년" + maps.get("month") + "월");
		XSSFCellStyle headerStyle = workbook.createCellStyle();
		headerStyle.setAlignment(HorizontalAlignment.CENTER);
		headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		
		Row header0 = sheet.createRow(rowCount++);
		createHeaderCell(0, header0, headerStyle, maps.get("year") + "년" + maps.get("month") + "월 재고 현황");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 16));
		
		Row header1 = sheet.createRow(rowCount++);			

		createHeaderCell(0, header1, headerStyle, "번호");
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 0, 0));
		createHeaderCell(1, header1, headerStyle, "거래처명");
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));
		createHeaderCell(2, header1, headerStyle, "재고구분");
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 2, 2));
		createHeaderCell(3, header1, headerStyle, "제품명");
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 3, 3));
		createHeaderCell(4, header1, headerStyle, "이월");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 4, 6));
		createHeaderCell(7, header1, headerStyle, "입고");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 7, 9));
		createHeaderCell(10, header1, headerStyle, "실사용");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 10, 12));
		createHeaderCell(13, header1, headerStyle, "과부족");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 13, 15));
		createHeaderCell(16, header1, headerStyle, "실재 재고");
		sheet.addMergedRegion(new CellRangeAddress(1, 1, 16, 18));
		createHeaderCell(19, header1, headerStyle, "사유");
		sheet.addMergedRegion(new CellRangeAddress(1, 2, 19, 19));
		
		Row header2 = sheet.createRow(rowCount++);
		
		createHeaderCell(4, header2, headerStyle, "단가");
		createHeaderCell(5, header2, headerStyle, "재고수량");
		createHeaderCell(6, header2, headerStyle, "금액");
		createHeaderCell(7, header2, headerStyle, "단가");
		createHeaderCell(8, header2, headerStyle, "재고수량");
		createHeaderCell(9, header2, headerStyle, "금액");
		createHeaderCell(10, header2, headerStyle, "단가");
		createHeaderCell(11, header2, headerStyle, "재고수량");
		createHeaderCell(12, header2, headerStyle, "금액");
		createHeaderCell(13, header2, headerStyle, "단가");
		createHeaderCell(14, header2, headerStyle, "재고수량");
		createHeaderCell(15, header2, headerStyle, "금액");
		createHeaderCell(16, header2, headerStyle, "단가");
		createHeaderCell(17, header2, headerStyle, "재고수량");
		createHeaderCell(18, header2, headerStyle, "금액");
		Float r_totalamount = (float) 0.0;
		Float i_totalamount = (float) 0.0;
		Float u_totalamount = (float) 0.0;
		Float m_totalamount = (float) 0.0;
		Float totalamount = (float) 0.0;
		// 엑셀에 데이터 입력
		for(Map<String, Object> e:selectList){
			Row row = sheet.createRow(rowCount++);
			row.setHeight((short)350);
			row.createCell(0).setCellValue(ConvertStringUtil.ifNullToString(rowCount-3));
			row.createCell(1).setCellValue(ConvertStringUtil.ifNullToString(e.get("companyName")));
			row.createCell(2).setCellValue(ConvertStringUtil.ifNullToString(e.get("treatmentName")));			
			row.createCell(3).setCellValue(ConvertStringUtil.ifNullToString(e.get("productName")));			
			row.createCell(4).setCellValue(ConvertStringUtil.ifNullToString(e.get("r_price")));
			row.createCell(5).setCellValue(ConvertStringUtil.ifNullToString(e.get("r_quantity")));
			row.createCell(6).setCellValue(ConvertStringUtil.ifNullToString(e.get("r_totalAmount")));
			r_totalamount += Float.parseFloat(e.get("r_totalAmount").toString());
			row.createCell(7).setCellValue(ConvertStringUtil.ifNullToString(e.get("i_price")));
			row.createCell(8).setCellValue(ConvertStringUtil.ifNullToString(e.get("i_quantity")));
			row.createCell(9).setCellValue(ConvertStringUtil.ifNullToString(e.get("i_totalAmount")));
			i_totalamount += Float.parseFloat(e.get("i_totalAmount").toString());
			row.createCell(10).setCellValue(ConvertStringUtil.ifNullToString(e.get("u_price")));
			row.createCell(11).setCellValue(ConvertStringUtil.ifNullToString(e.get("u_quantity")));
			row.createCell(12).setCellValue(ConvertStringUtil.ifNullToString(e.get("u_totalAmount")));
			u_totalamount += Float.parseFloat(e.get("u_totalAmount").toString());
			row.createCell(13).setCellValue(ConvertStringUtil.ifNullToString(e.get("m_price")));
			row.createCell(14).setCellValue(ConvertStringUtil.ifNullToString(e.get("m_quantity")));
			row.createCell(15).setCellValue(ConvertStringUtil.ifNullToString(e.get("m_totalAmount")));
			m_totalamount += Float.parseFloat(e.get("m_totalAmount").toString());
			row.createCell(16).setCellValue(ConvertStringUtil.ifNullToString(e.get("price")));
			row.createCell(17).setCellValue(ConvertStringUtil.ifNullToString(e.get("quantity")));
			row.createCell(18).setCellValue(ConvertStringUtil.ifNullToString(e.get("totalAmount")));
			totalamount += Float.parseFloat(e.get("totalAmount").toString());
			row.createCell(19).setCellValue(ConvertStringUtil.ifNullToString(e.get("ETC")));
		}
		
		Row lastrow = sheet.createRow(rowCount++);
		lastrow.createCell(0).setCellValue("합계");
		sheet.addMergedRegion(new CellRangeAddress(rowCount-1, rowCount-1, 0, 3));
		lastrow.createCell(6).setCellValue(r_totalamount);
		lastrow.createCell(9).setCellValue(i_totalamount);
		lastrow.createCell(12).setCellValue(u_totalamount);
		lastrow.createCell(15).setCellValue(m_totalamount);
		lastrow.createCell(18).setCellValue(totalamount);
		
		//response.setContentType("Application/Msexcel");
		response.setContentType(getContentType());
		Date toDay = new Date();        
	    SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    String nowDateTime = dateFormat.format(toDay);
	    log.debug("1. 현재날짜                                : "+ nowDateTime);
		response.setHeader("Content-Disposition", "ATTachment; Filename=".concat(new String(filename.getBytes("euc-kr"),"8859_1")).concat("_").concat(nowDateTime).concat(".xlsx"));
		
		ServletOutputStream out = response.getOutputStream();
        out.flush();
        workbook.write(out);
        out.flush();
        
   }
	
	private void createHeaderCell(int col, Row row, CellStyle cellStyle, String value){
		Cell cell = row.createCell(col);
		cell.setCellStyle(cellStyle);
		cell.setCellValue(value);
	}
}