package com.kingdee.eas.custom.facade.passports;

import org.apache.log4j.Logger;
import org.apache.poi.sl.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.ui.ModelMap;

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

import java.rmi.RemoteException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import com.grapecity.documents.excel.Workbook;
import com.kingdee.bos.*;
import com.kingdee.eas.mobile.BOSMsgTypeEnum;
import com.kingdee.eas.mobile.MimeTypeEnum;
import com.kingdee.eas.mobile.PriorityEnum;
import com.kingdee.eas.mobile.msg.util.MsgUtil;
import com.kingdee.eas.util.app.DbUtil;
import com.kingdee.jdbc.rowset.IRowSet;
import com.kingdee.shr.base.syssetting.exception.SHRWebException;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.io.FileOutputStream;

import java.io.IOException;

import org.apache.poi.ss.usermodel.*;

import org.apache.poi.xssf.usermodel.XSSFWorkbook;
//import com.kingdee.bos.metadata.entity.EntityViewInfo;

//import com.kingdee.bos.dao.IObjectPK;

public class PassportsFacdeControllerBean extends AbstractPassportsFacdeControllerBean {

	private int totalCount;
	private static Logger logger = Logger
			.getLogger("com.kingdee.eas.custom.facade.passports.PassportsFacdeControllerBean");

	public void allPersonEmail(Context ctx, HttpServletRequest request, HttpServletResponse response, ModelMap modelMap)
			throws SHRWebException, BOSException {
		// 查询所有人的护照和邮箱
		String sql = " select a.FCredentialsTypeNO, c.FName_L1,d.FEmail,d.CFPrivateemail from T_HR_PersonCredentialsType a left join T_HR_CredentialsType b on a.FCredentialsTypeID=b.fid left join T_BD_Person c \r\n"
				+ "  left join T_HR_PersonContactMethod d on d.FPERSONID =c.fid\r\n"
				+ "  on a.FPERSONID =c.fid  where b.FNUMBER ='CN01'";
		try {
			 FileOutputStream outputStream = new FileOutputStream("report.xlsx");
			IRowSet resultSet = DbUtil.executeQuery(ctx, sql);
			// 创建工作簿和工作表
			XSSFWorkbook workbook = new XSSFWorkbook();
			XSSFSheet sheet = workbook.createSheet("Sheel");
			// 写入表头行
			Row headerRow = sheet.createRow(0);
			headerRow.createCell(0).setCellValue("CredentialsTypeNO");
			headerRow.createCell(1).setCellValue("Name");
			headerRow.createCell(2).setCellValue("Email");
			headerRow.createCell(3).setCellValue("PrivateEmail");

			// 遍历查询结果并写入Excel表格
			int rowNum = 1;
			while (resultSet.next()) {
				Row row = sheet.createRow(rowNum++);
				row.createCell(0).setCellValue(resultSet.getString("FCredentialsTypeNO"));
				row.createCell(1).setCellValue(resultSet.getString("FName_L1"));
				row.createCell(2).setCellValue(resultSet.getString("FEmail"));
				row.createCell(3).setCellValue(resultSet.getString("CFPrivateemail"));
			}

			// 将工作簿写入输出流并关闭资源
			workbook.write(outputStream);
			System.out.println("输出成功了.");
			//MsgUtil.msgGroupSend(ctx, title, PriorityEnum.HIGHT_VALUE, false, content, userList, BOSMsgTypeEnum.V_TYPE_EMAIL, null);
			//MsgUtil.msgSend(ctx, title,  PriorityEnum.HIGHT_VALUE, false, content, "1CV1Xix+Qua9UEeo/uZ6TIDvfe0=", BOSMsgTypeEnum.V_TYPE_EMAIL, null, null, MimeTypeEnum.HTML);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
	}


	// int rows = Integer.parseInt(request.getParameter("rows"));
	// int page = Integer.parseInt(request.getParameter("page"));
	// try {
	// List<Map<String, Object>> maplist = new ArrayList<Map<String, Object>>();
	// int num = 0;
	// IRowSet rs = DbUtil.executeQuery(ctx, sql);
	// while(rs.next()) {
	// ++num;
	// Map<String, Object> map = new HashMap<String, Object>();
	// map.put("FName_L1", rs.getString("FName_L1"));
	// map.put("FCredentialsTypeNO", rs.getString("FCredentialsTypeNO"));
	// map.put("FEmail", rs.getString("FEmail"));
	// map.put("CFPrivateemail", rs.getString("CFPrivateemail"));
	// maplist.add(map);
	// }
	// this.totalCount = num;
	// Map<String, Object> gridDataMap = new LinkedHashMap<String, Object>();
	// if (this.totalCount % rows == 0) {
	// gridDataMap.put("total", this.totalCount / rows);
	// } else {
	// gridDataMap.put("total", this.totalCount / rows + 1);
	// }
	// gridDataMap.put("page", page);
	// gridDataMap.put("records", this.totalCount);
	// gridDataMap.put("rows", maplist);
	// JSONUtils.writeJson(response, gridDataMap);
	// } catch (BOSException e) {
	// // TODO 自动生成的 catch 块
	// e.printStackTrace();
	// } catch (SQLException e) {
	// // TODO 自动生成的 catch 块
	// e.printStackTrace();
	// }

//}
//
// public String getGridColModelAction(HttpServletRequest request,
// HttpServletResponse response, ModelMap modelMap)
// throws SHRWebException, BOSException, SQLException {
// List<String> colNames = this.getcolNamesAction();
// List<Map<String, Object>> colModel = this.getcolModelAction();
// LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();
// map.put("colNames", colNames);
// map.put("colModel", colModel);
// JSONUtils.writeJson(response, map);
// return null;
// }
// public List<Map<String, Object>> getcolModelAction() {
// List<Map<String, Object>> modelColList = new ArrayList<Map<String,
// Object>>();
// modelColList.add(this.buildColModelMap("FName_L1", "Display Name", 100));
// modelColList.add(this.buildColModelMap("FCredentialsTypeNO", "Passport
// Number", 100));
// modelColList.add(this.buildColModelMap("FEmail", "Email", 100));
// return modelColList;
//
//
// }
// public ArrayList<String> getcolNamesAction() throws SHRWebException {
// ArrayList<String> list = new ArrayList<String>();
// list.add("Display Name");
// list.add("Passport Number");
// list.add("Email");
// return list;
// }
//
// private Map<String, Object> buildColModelMap(String index, String label, int
// width) {
// return this.buildColModelMap(index, label, width, false, false, false);
// }
//
// private Map<String, Object> buildColModelMap(String index, String label, int
// width, boolean rowspan, boolean isKey,
// boolean isHedden) {
// Map<String, Object> gridIdMap = new LinkedHashMap<String, Object>();
// gridIdMap.put("index", index);
// gridIdMap.put("name", index);
// gridIdMap.put("label", label);
// gridIdMap.put("width", width);
// if (rowspan) {
// gridIdMap.put("rowspan", rowspan);
// }
// if (isKey) {
// gridIdMap.put("key", isKey);
// }
// if (isHedden) {
// gridIdMap.put("hidden", isHedden);
// }
// return gridIdMap;
// }

}