package com.webclient.beans.udpr;

import java.io.IOException;
import java.io.PrintWriter;
import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

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

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import psdi.mbo.MboRemote;
import psdi.mbo.MboSetRemote;
import psdi.server.MXServer;
import psdi.util.MXApplicationException;
import psdi.util.MXException;
import psdi.util.logging.FixedLoggers;
import psdi.webclient.system.controller.ControlInstance;
import psdi.webclient.system.controller.MPFormData;
import psdi.webclient.system.controller.WebClientEvent;
import psdi.webclient.system.session.WebClientSession;

/**
 * 导入模板工具类
 * @author lip
 * @date 2023-09-12 21:14
 *
 */
public class ImpExcelTool {

	private static final String clientSession = null;
	private static ImpExcelTool singleton = null;

	public ImpExcelTool() {
		// TODO Auto-generated constructor stub
	}

	public static ImpExcelTool getInstance() throws RemoteException {
		if (singleton == null) {
			singleton = new ImpExcelTool();
		}
		return singleton;
	}

	/**
	 * 检查文件类型是否是Excel类型并返回数据
	 * 
	 * @param webClientSession
	 * @return
	 * @throws MXException
	 */
	public MPFormData checkExcelType(WebClientSession webClientSession) throws MXException {
		// 判断读取文件的合法性
		WebClientEvent wce = webClientSession.getCurrentEvent();
		HttpServletRequest request = webClientSession.getRequest();
		String fName = "";
		MPFormData mpData = null;

		if (MPFormData.isRequestMultipart(request)) {
			try {
				ControlInstance uploadfileControl = wce.getSourceControlInstance();
				String maxfilesize = uploadfileControl.getProperty("maxfilesize");
				mpData = new MPFormData(request, Integer.parseInt(maxfilesize));
			} catch (MXException mxe) {
				FixedLoggers.APPLOGGER.error(mxe.getMessage(), mxe);
				throw mxe;
			}
			fName = mpData.getFullFileName();
			if (null==fName || "".equalsIgnoreCase(fName)) {
				throw new MXApplicationException("commonwarn", "commonwarn",
						new String[] { "没有选择Excel文件！" });
			}
			String filename = mpData.getFileName();
			String mimetype = mpData.getFileContentType();
			String filetype = filename.substring(filename.lastIndexOf(".") + 1, filename.length());
			if (filetype.equalsIgnoreCase("xls") || filetype.equalsIgnoreCase("xlsx")
					|| filetype.equalsIgnoreCase("xlsm")) {
				if (!mimetype.equals("application/vnd.ms-excel")
						&& !mimetype.equals(
								"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
						&& !mimetype.equals("application/vnd.ms-excel.sheet.macroenabled.12")
						&& !mimetype.equalsIgnoreCase("application/octet-stream")) {
					throw new MXApplicationException("commonwarn", "commonwarn",
							new String[] { "请选择Excel的文件类型！" });
				}
			} else {
				throw new MXApplicationException("commonwarn", "commonwarn",
						new String[] { "请选择Excel的文件类型！" });
			}
		}
		return mpData;
	}


	/**
	 * 格式化日期
	 * 
	 * @param str
	 * @return
	 * @throws ParseException
	 */
	private Date strToDate(String str) throws ParseException {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = null;
		if (!str.isEmpty()) {
			date = sdf.parse(str);
		}
		return date;
	}


	/**
	 * 获取excel单元格内容
	 * 
	 * @param row
	 * @param i
	 * @return
	 * @throws MXApplicationException
	 */
	private String getCellString(Row row, int i) throws MXApplicationException {
		Cell cell = row.getCell(i);
		String cellStr = "";
		if (cell == null) {
			return cellStr;
		}
		if (cell.getCellType() == Cell.CELL_TYPE_STRING) {
			cellStr = cell.getRichStringCellValue().getString().trim();
		} else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) { // 自定义的日期类型
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				Date date = cell.getDateCellValue();
				cellStr = sdf.format(date).trim();
			} else {
				NumberFormat nf = NumberFormat.getInstance();
				nf.setGroupingUsed(false); // 设置数字格式，不自动用科学计数法
				if (i == 15 || i == 18 || i == 19 || i == 21 || i == 23 || i == 47) {
					Integer value = (int) cell.getNumericCellValue(); // 整数
					cellStr = nf.format(value).trim();
				} else {
					double value = cell.getNumericCellValue(); // 小数
					cellStr = nf.format(value).trim();
				}
			}
		} else if (cell.getCellType() == Cell.CELL_TYPE_FORMULA) { // 公式
			cellStr = cell.getCellFormula().trim();
		} else {
			cellStr = cell.getRichStringCellValue().getString().trim();
		}
		return cellStr;
	}

}