package nccloud.web.hrhi.psndoc.vo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Comment;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Drawing;
import org.apache.poi.ss.usermodel.Font;
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.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import nc.hr.utils.HRFileUtils;
import nc.hr.utils.ResHelper;
import nc.itf.uap.IUAPQueryBS;
import nc.jdbc.framework.processor.BeanListProcessor;
import nc.ui.pub.bill.IBillItem;
import nc.vo.bd.defdoc.DefdocVO;
import nc.vo.hi.psndoc.TrainVO;
import nc.vo.pub.BusinessException;
import nc.vo.pub.lang.UFBoolean;
import nccloud.framework.service.ServiceLocator;
import nccloud.web.hrwa.helper.DeleteFileUtil;
import uap.lfw.md.vo.EnumValueVO;

public class TrainPsndocXlsExporter {
	private CellStyle titleCellStyle;
	
	private static final String MAIN_SHEET_NAME = "template";
    private static final String HIDDEN_SHEET1_NAME = "hidden1";
    private static final String DEVICE_NAMES = "devices";
    private static final String DEVICE_TYPE_NAMES = "deviceTypes";
    
    public static String getTimeStamp(){
		SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddhhmmssSSS");
		return sdf.format(new Date());
	}

	/**
	 * 导出文件
	 * 
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public ExportFile exportToFile(TrainVO[] exprotvos, List<Map<String, Object>> headList, UFBoolean allowed,
			String pk_org, String pk_loginUser) throws Exception {

		String filePath = HRFileUtils.getExportPath() + "/hrhifile/psndoc/excel/";
		// 删除过期文件
		DeleteFileUtil.delete(filePath);

		String fileName = filePath + pk_loginUser + "/templet.xls";
		File file = new File(fileName);
		if (!file.exists()) {
			file.mkdirs();
		}
		if (file.exists()) {
			file.delete();
		}
		FileOutputStream fileOut = null;
		try {
			fileOut = new FileOutputStream(file);
			HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
			Sheet sheet = hSSFWorkbook.createSheet();
			// 超过四千条 style会报错
			if(null == titleCellStyle){
				this.titleCellStyle = hSSFWorkbook.createCellStyle();
			}
			// 参照缓存
			DefdocRefDatas defdocRefDatas = new DefdocRefDatas();
			Vector<Vector> datas = null;

			// 输出表头
			Row headrow = sheet.createRow(0);// 表头行，也就是首行
			int nLineNo = 0;
			
			for (int j = 0; j < headList.size(); j++) {
				//设置下拉
				Map<String, Object> headMap = headList.get(j);
				int dataType = (Integer) headMap.get("dataType");
				//自定义档案
				if(dataType == 5) {
					addList(sheet, headMap,j);
				}
				//枚举类
				if(dataType == 6) {
					addEnum(sheet, headMap,j);
				}
			}
			
			
			// 输出序号
			Cell headcell = headrow.createCell((short) nLineNo);// 首个单元格
			headcell.setCellValue(ResHelper.getString("common",
					"UC000-0001821")/* @res "序号" */);
			this.setCellStyle4Head(headcell, titleCellStyle);
			nLineNo++;
			int nLineNo2 = nLineNo - 2;

			for (int j = 0; j < headList.size(); j++) {
//				if ((j == 2 || j == 3) && !allowed.booleanValue()) {
//					continue;
//				}
//				if (j > 3 && !allowed.booleanValue()) {
//					nLineNo = nLineNo2;
//				}
				headcell = headrow.createCell((short) (j + nLineNo));
				Map<String, Object> headMap = headList.get(j);
				headcell.setCellValue(headMap.get("name") + "");
				this.setCellCommentStyle(sheet, headcell, headMap, "2003");
				this.setCellStyle4Head(headcell, titleCellStyle);
			}

			// 输出表体
			int nRowIdx = 1;
			int i = 0;//记录额外创建的sheet数量
			sheet.setDefaultColumnWidth((short) 12);
			sheet.autoSizeColumn((short) 25);
			hSSFWorkbook.write(fileOut);

			InputStream inputStream = new FileInputStream(file);
			ExportFile importFile = new ExportFile();
			importFile.setFileName("templet.xls");
			importFile.setInputStream(inputStream);
			return importFile;
		} finally {
			if (fileOut != null) {
				fileOut.close();
			}
		}
	}

	private void addEnum(Sheet sheet, Map<String, Object> headMap,int j) throws BusinessException {
		List<EnumValueVO> enumList = this.getEnumVO(headMap.get("enum_id").toString());
		if(enumList != null && enumList.size() > 0) {
			List<String> list = new ArrayList();
			for(EnumValueVO vo : enumList) {
				list.add(vo.getName());
			}
			CellRangeAddressList regions = new CellRangeAddressList(1, 1000, j+1, j+1);  
			DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
			DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(list.toArray(new String[0]));
			DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
			if (createValidation instanceof XSSFDataValidation) {
	            createValidation.setSuppressDropDownArrow(true);
	            createValidation.setShowErrorBox(true);
	        } else {
	            createValidation.setSuppressDropDownArrow(false);
	        }
			sheet.addValidationData(createValidation);
		}
	}
	
	private void addList(Sheet sheet, Map<String, Object> headMap,int j) throws BusinessException {
		List<DefdocVO> traTypeList = this.getDefdocVO(headMap.get("pk_refinfo").toString());
		if(traTypeList != null && traTypeList.size() > 0) {
			List<String> list = new ArrayList();
			for(DefdocVO vo : traTypeList) {
				list.add(vo.getName());
			}
			CellRangeAddressList regions = new CellRangeAddressList(1, 1000, j+1, j+1);  
			DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
			DataValidationConstraint createExplicitListConstraint = dataValidationHelper.createExplicitListConstraint(list.toArray(new String[0]));
			DataValidation createValidation = dataValidationHelper.createValidation(createExplicitListConstraint, regions);
			if (createValidation instanceof XSSFDataValidation) {
	            createValidation.setSuppressDropDownArrow(true);
	            createValidation.setShowErrorBox(true);
	        } else {
	            createValidation.setSuppressDropDownArrow(false);
	        }
			sheet.addValidationData(createValidation);
		}
	}

	/**
	 * 设置单元格的式样<BR>
	 * <BR>
	 *
	 * @author xuhw on 2010-4-10
	 * @param cell
	 * @param wb
	 * @return
	 */
	private Cell setCellStyle4Head(Cell cell, CellStyle titleCellStyle) {
		titleCellStyle.setBorderBottom(BorderStyle.THIN);
		titleCellStyle.setBorderLeft(BorderStyle.THIN);
		titleCellStyle.setBorderRight(BorderStyle.THIN);
		titleCellStyle.setBorderTop(BorderStyle.THIN);

		cell.setCellStyle(titleCellStyle);
		return cell;
	}

	/**
	 * 设定批注
	 *
	 * @author xuhw on 2010-4-20
	 * @param cell
	 * @param wb
	 * @return
	 */
	private void setCellCommentStyle(Sheet sheet, Cell cell, Map<String, Object> headMap, String version) {
		Drawing p = sheet.createDrawingPatriarch();
		// 创建绘图对象
		Comment comment = null;
		if (null != version && version.equals("2003")) {
			comment = p.createCellComment(new HSSFClientAnchor(1, 2, 3, 4, (short) 3, 3, (short) 5, 6));
			// 输入批注信息
			comment.setString(new HSSFRichTextString(this.getCommentContent(headMap)));
			// 添加作者,选中B5单元格,看状态栏
			comment.setAuthor("toad");
		} else if (null != version && version.equals("2007")) {
			comment = p.createCellComment(new XSSFClientAnchor(1, 2, 3, 4, (short) 3, 3, (short) 5, 6));
			// 输入批注信息
			comment.setString(new XSSFRichTextString(this.getCommentContent(headMap)));
			// 添加作者,选中B5单元格,看状态栏
			comment.setAuthor("toad");
		}

		// 将批注添加到单元格对象中
		cell.setCellComment(comment);
	}

	/**
	 * 核查单元值是否合法 如果不合法，则将相应的单元cell 变为红色
	 *
	 * @param item
	 * @param aValue
	 * @return
	 */
	private String getCommentContent(Map<String, Object> headMap) {
		// 当心类型不统一
		int datatype = (Integer) headMap.get("dataType");
		// int maxinputlength = item.getLength();
		StringBuffer sbMessage = new StringBuffer();
		switch (datatype) {
		case IBillItem.DECIMAL:// 数值型
			sbMessage.append(ResHelper.getString("60130adjmtc",
					"060130adjmtc0150")/* @res "输入类型：数值类型(31.8)\n" */);
			sbMessage.append(ResHelper.getString("60130adjmtc",
					"060130adjmtc0151")/* @res "输入举例：256.123\n" */);
			// sbMessage.append(ResHelper.getString("60130adjmtc","060130adjmtc0152")/*@res
			// "最大长度："*/ + maxinputlength);
			break;
		case IBillItem.BOOLEAN:
			sbMessage.append(ResHelper.getString("60130adjmtc",
					"060130adjmtc0153")/* @res "输入类型：布尔类型\n" */);
			sbMessage.append(ResHelper.getString("60130adjmtc",
					"060130adjmtc0154")/* @res "输入举例：{N}或者{Y}\n" */);
			// sbMessage.append(ResHelper.getString("60130adjmtc","060130adjmtc0152")/*@res
			// "最大长度："*/ + maxinputlength);
			break;
		case IBillItem.DATE:
			sbMessage.append(ResHelper.getString("60130adjmtc",
					"060130adjmtc0155")/* @res "输入类型：日期类型\n" */);
			sbMessage.append(ResHelper.getString("60130adjmtc",
					"060130adjmtc0156")/* @res "输入举例：2012-12-12\n" */);
			// sbMessage.append(ResHelper.getString("60130adjmtc","060130adjmtc0152")/*@res
			// "最大长度："*/ + maxinputlength);
		}
		return sbMessage.toString();
	}

	/**
	 * 设置单元格的式样<BR>
	 * <BR>
	 *
	 * @author xuhw on 2010-4-10
	 * @param cell
	 * @param wb
	 * @return
	 */
	private Cell setCellStyle4Body(final Cell cell, final CellStyle titleCellStyle,final Font font, int intErrorPos, int curentLineNO, short alignment) {
		titleCellStyle.setBorderBottom(BorderStyle.THIN);
		titleCellStyle.setBorderLeft(BorderStyle.THIN);
		titleCellStyle.setBorderRight(BorderStyle.THIN);
		titleCellStyle.setBorderTop(BorderStyle.THIN);

		titleCellStyle.setAlignment(HorizontalAlignment.forInt(alignment));


		if (curentLineNO == intErrorPos) {
			font.setColor(Font.COLOR_RED);
			titleCellStyle.setFont(font);
		}
		cell.setCellStyle(titleCellStyle);
		return cell;
	}
	
	public static IUAPQueryBS iUapQueryBS;
	
	public static IUAPQueryBS getIUAPQueryBS() {
		if(iUapQueryBS == null) {
			iUapQueryBS = ServiceLocator.find(IUAPQueryBS.class);
		}
		return iUapQueryBS;
	}
	
	public  List<EnumValueVO> getEnumVO(String emunid) throws BusinessException{
		String sql = "select * from md_enumvalue where id = '"+emunid+"' and nvl(dr,0) = 0";
        return (List<EnumValueVO>) getIUAPQueryBS().executeQuery(sql, new BeanListProcessor(EnumValueVO.class));
    }
	
    public  List<DefdocVO> getDefdocVO(String pk_refinfo) throws BusinessException{
		String sql = "select * from bd_defdoc where nvl(dr,0) = 0 and pk_defdoclist = (select para1 from bd_refinfo where pk_refinfo = '"+pk_refinfo+"')";
        return (List<DefdocVO>) getIUAPQueryBS().executeQuery(sql, new BeanListProcessor(DefdocVO.class));
    }
	
	public Map<String,String> getSourceType(){
//		指定参训=0,报名参训=1,其他=2,手工添加=3 source_type
		Map<String,String> result = new HashMap();
		result.put("指定参训", "0");
		result.put("报名参训", "1");
		result.put("其他", "2");
		result.put("手工添加", "3");
		return result;
	}
	
	public Map<String,String> getTaketrmMethod(){
//		全部参训=0,部分参训=1,中途参训=2,其他=3 taketrm_method
		Map<String,String> result = new HashMap();
		result.put("全部参训", "0");
		result.put("部分参训", "1");
		result.put("中途参训", "2");
		result.put("其他", "3");
		return result;

	}
}
