package com.yc.action.excel;

import java.io.*;
import java.text.DateFormat;
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.UUID;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.yc.sdk.weixincp.util.UploadFiles;
import com.yc.service.BaseService;
import com.yc.utils.ImageUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.JSONException;
import org.json.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.yc.action.BaseAction;
import com.yc.action.grid.GTGrid;
import com.yc.action.grid.Grid;
import com.yc.action.grid.TreeGridDTO;
import com.yc.action.grid.TreeGridIfc;
import com.yc.currentThreadInfo.CurrentLocal;
import com.yc.entity.XlsImportParameterEntity;
import com.yc.entity.attachment.AttachmentEntity;
import com.yc.entity.attachment.AttachmentWhereEntity;
import com.yc.exception.CallBackMessage;
import com.yc.factory.FactoryBean;
import com.yc.multiData.SpObserver;
import com.yc.service.commons.SqlQueryIfc;
import com.yc.service.dao.c_sys_cr_pageDao;
import com.yc.service.excel.ExcelIfc;
import com.yc.service.impl.DBHelper;
import com.yc.service.impl.EnvHelper;
import com.yc.service.upload.AttachmentIfc;
import com.yc.utils.GTJson;
import com.yc.utils.SessionKey;
import com.yc.ycenum.ExlsParam;

@Controller("com.yc.action.excel.Excel")
@RequestMapping("/excel.do")
public class Excel extends BaseAction {
	@Autowired
	ExcelIfc excelIfc;
	@Autowired
	c_sys_cr_pageDao cParent;
	@Autowired
	SqlQueryIfc sqlQueryIfc;
	@Autowired
	TreeGridIfc treeGridIfc;

	public String sqlext = "";

	public String getSqlext() {
		return sqlext;
	}

	public void setSqlext(String sqlext) {
		this.sqlext = sqlext;
	}

	/**
	 * 数据范围检查后按确定
	 */
	private static final String OK = "ok";
	/**
	 * 数据范围检查后按取消
	 */
	private static final String QU_XIAO = "quexiao";

	@RequestMapping(params = "e=down")
	public void excelDown(int formId, HttpServletRequest request, HttpServletResponse response) throws IOException {
		String path = EnvHelper.getPath() + File.separator + "excel" + File.separator + formId + ".xls";
		OutputStream out = response.getOutputStream();
		InputStream inputStream = null;
		BufferedInputStream br = null;
		try {
			cParent.createFolderAndFile(path);
			com.yc.excel.Excel excel_manage = new com.yc.excel.Excel();
			List<Map<String, Object>> list = sqlQueryIfc
					.queryForList("select menuname,formtype from _sysmenu where formid=?", formId);
			StringBuffer nameBuffer = new StringBuffer();
			if (list != null && list.size() > 0) {
				Map<String, Object> gformInfo = list.get(0);
				nameBuffer.append(gformInfo.get("menuname")).append("--").append(formId).append("_")
						.append(gformInfo.get("formtype"));
			} else {
				nameBuffer.append(formId);
			}
			excel_manage.writeXls(excelIfc.getImportInfo(formId), path, null, null);
			inputStream = new FileInputStream(new File(path));
			response.reset();
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename="
					+ new String(nameBuffer.toString().getBytes("gb2312"), "ISO_8859_1") + ".xls");
			response.setContentLength(inputStream.available());
			br = new BufferedInputStream(inputStream);
			byte[] buf = new byte[1024];
			int len = 0;
			while ((len = br.read(buf)) > 0) {
				out.write(buf, 0, len);
			}
		} catch (Exception e) {

		} finally {
			out.flush();
			br.close();
			out.close();
			inputStream.close();
			cParent.delete(path);
		}
	}

	@RequestMapping(params = "m=import")
	public void importFromExcelFile(HttpServletRequest request, HttpServletResponse response) throws IOException {
		Map<String, Object> map = new HashMap<String, Object>();
		// 从页面返回时的参数与map的key是一样的，因为在下面将map转换出去的
		map.put(ExlsParam.BEFORE_NAME.getKey(), request.getParameter(ExlsParam.BEFORE_NAME.getKey()));
		map.put(ExlsParam.END_NAME.getKey(), request.getParameter(ExlsParam.END_NAME.getKey()));
		map.put(ExlsParam.FORM_ID.getKey(), request.getParameter(ExlsParam.FORM_ID.getKey()));
		map.put(ExlsParam.FORM_TYPE.getKey(), request.getParameter(ExlsParam.FORM_TYPE.getKey()));
		// map.put(ExlsParam.EXCEL_TITLE.getKey(),
		// (request.getParameter(ExlsParam.EXCEL_TITLE.getKey()) != null) ?
		// request.getParameter(ExlsParam.EXCEL_TITLE.getKey()) : 0);
		map.put(OK, request.getParameter("ok"));// 已经做了检查
		map.put(QU_XIAO, request.getParameter("quexiao"));// 已经做了检查
		map.put(ExlsParam.PARENT_ID.getKey(), request.getParameter(ExlsParam.PARENT_ID.getKey()));
		map.put(ExlsParam.TITLE_ROWS.getKey(), request.getParameter(ExlsParam.TITLE_ROWS.getKey()));
		map.put(ExlsParam.ROW_NOT_INFO.getKey(), request.getParameter(ExlsParam.ROW_NOT_INFO.getKey()));
		map.put(ExlsParam.COL_NO.getKey(), request.getParameter(ExlsParam.COL_NO.getKey()));
		map.put(ExlsParam.ONLY_INFO.getKey(), request.getParameter(ExlsParam.ONLY_INFO.getKey()));
		map.put(ExlsParam.DETIAL_TABLE.getKey(), request.getParameter(ExlsParam.DETIAL_TABLE.getKey()));
		map.put(ExlsParam.DOCCODE.getKey(), request.getParameter(ExlsParam.DOCCODE.getKey()));
		// importExcel(request, response, map,"","");
	}
	
	/**
	 * 这个方法是xls导入数据的最新方法调用 xin 2021-1-5 10:11:47
	 * @param request
	 * @param response
	 * @throws IOException
	 */
	@RequestMapping(params = "m=xlsdata")
	public void XlsDataImport(XlsImportParameterEntity xlsEntity,HttpServletRequest request, HttpServletResponse response) throws IOException {
		try {
			//大改版 2019-10-30 16:23:21
			if (xlsEntity.getState() == 0) {// 0表示映射Excel表
				//映射方法
				JSONObject json=excelMapping(xlsEntity,request);
				super.printJson(response, json.toString());
				return;
			}
			//上传后的文件名称
			xlsEntity.setEndName(xlsEntity.getFile1().getOriginalFilename());
			excelImport(xlsEntity,request,response);
			return;
		} catch (Exception e) {
			super.printJson(response, new CallBackMessage().sendErrorMessage(e.getMessage()));
		}
	}

	/**
	 * 直接保存
	 * 
	 * @param request
	 * @param response
	 * @param formid
	 * @param type
	 * @param gJsonList
	 * @throws UnsupportedEncodingException
	 * @throws DataAccessException
	 * @throws JSONException
	 */
	@SuppressWarnings("unused")
	public void saveTo(HttpServletRequest request, HttpServletResponse response, int formid, String type,
			List<GTJson> gJsonList) throws Exception {
		TreeGridDTO dto = new TreeGridDTO();
		Grid grid = null;
		try {
			SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
			dto.dbid = (String) request.getSession().getAttribute(SessionKey.DATA_BASE_ID);
			grid = treeGridIfc.getGridInfo(formid, type, true, dto);// 获得Grid
			grid.setImport(true);
			if (request == null) { 
				grid.setCallback(true);
				grid.setTaobaoData(gJsonList);
			} else {
				request.setAttribute("_gt_json", gJsonList);// 赋值
				GTGrid gt = (GTGrid) FactoryBean.getBean("GTGrid");
				gt.saveFinal(grid, request, response);// 保存执行
			}
		} catch (Exception e) {
			throw e;
		} finally {
			SpObserver.setDBtoInstance();
		}
	}

	/**
	 * 
	 * @param formtype
	 *            窗体类型
	 * @return
	 */
	private int getWinType(int formtype) {
		int wintype = formtype;
		switch (formtype) {
		case 5:
			wintype = 9;
			break;
		case 16:
			wintype = 17;
			break;
		case 8:
			wintype = 15;
			break;
		case 498:
			wintype = 499;
			break;
		case 496:
			wintype = 497;
			break;
		default:
			break;
		}
		return wintype;
	}

	/**
	 * 映射导入字段页面
	 * 
	 * @param formId
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception 
	 */
	public JSONObject excelMapping(XlsImportParameterEntity xlsEntity,HttpServletRequest request)
			throws Exception {
		// 获取最后一个.的位置
		String xlsName=xlsEntity.getFile1().getOriginalFilename();
		String houzhui = xlsName.substring(xlsName.lastIndexOf(".")).toLowerCase();
		Workbook workbook = null;// Excel文件
		Sheet sheet = null;// Excel里的sheet
		Row row = null;// 行
		try {
			if (houzhui.equals(".xls")) {
				workbook = new HSSFWorkbook(xlsEntity.getFile1().getInputStream());
			} else if (houzhui.equals(".xlsx")) {
				workbook = new XSSFWorkbook(xlsEntity.getFile1().getInputStream());
			} else {
				throw new Exception("只支持xls或xlsx后缀的文件");
			}
			// 获取系统设置的导入字段信息
			SpObserver.setDBtoInstance("_" + request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
			ExcelIfc Ifc = (ExcelIfc) FactoryBean.getBean("com.yc.service.excel.ExcelImpl");
			List<Map<String, Object>> list = Ifc.getImportInfo(xlsEntity.getFormid());
			Map<Integer, List<Map<String, Object>>> ltMap = getSetupArray(list);
			int excelNum = workbook.getNumberOfSheets();// Excel的sheet数
			Map<String, Object> map = new HashMap<>();
			Map<String, Object> mapInfo = new HashMap<>();
			for (int i = 0; i < excelNum; i++) {// 遍历每一个sheet
				sheet = workbook.getSheetAt(i);// 获取每一个sheet的信息
				if (sheet.getLastRowNum() == -1) {// 是否是空行
					continue;
				}
				row = sheet.getRow(0);// 获取Excel表每一个sheet的第一行标题内容（只取第一行内容）
				if (row.getLastCellNum() == -1) {// 是否是空列
					continue;
				}

				if (i == 0) {// 第一页固定为明细表数据
					if (xlsEntity.getIsDetailed() == 1) {
						map.put("xls1", getExcelJson(row));
					} else {
						switch (xlsEntity.getFormtype()) {
						case 1:
						case 3:
							if (excelNum == 2) {
								i++;
								sheet = workbook.getSheetAt(i);
								row = sheet.getRow(0);
								map.put("xls1", getExcelJson(row));
							}
							break;
						default:
							break;
						}
					}
					mapInfo.put("xls1-g", ltMap.get(0));
				} else if (i == 1) {// 第二页固定为主表数据
					map.put("xls2", getExcelJson(row));
					mapInfo.put("xls2-g", ltMap.get(1));
				} else {

				}
			}
			Map<String, Object> resMap = new HashMap<>();
			resMap.put("state", 1);
			resMap.put("xls", map);
			resMap.put("gform", mapInfo);
			// Map 转成 JSONObject 返回
			return new JSONObject(resMap);
		} catch (Exception e) {
            throw e;//向上抛出错误
		} finally {
			workbook.close();
			SpObserver.setDBtoInstance();
		}
	}
	/**
	 * 组装系统导入里的字段
	 *
	 */
	public Map<String, Object> getExcelJson(Row row) {
		Map<String, Object> excel=new HashMap<>();
		try {		
			//遍历第一行的每一个列数信息
			for (int i = 0; i < row.getLastCellNum(); i++) {
				try {
					//每一个列的内容
					excel.put("-"+i, row.getCell(i));	
				} catch (ArrayIndexOutOfBoundsException e) {
					continue;
				}							
			}
			return excel;
		} catch (Exception e) {
			throw e;
		}		
	}
	
	/**
	 * 组装系统导入设置的字段
	 * 
	 * @param array
	 * @param data
	 * @param empty
	 * @param map
	 */
	public Map<Integer, List<Map<String, Object>>> getSetupArray(List<Map<String, Object>> list) {
		List<Map<String, Object>> l1 = new ArrayList<>();
		List<Map<String, Object>> l2 = new ArrayList<>();
		try {
			Map<String, Object> c=new HashMap<>();
			c.put("id", " ");
			c.put("text", " ");
			l1.add(c);
			l2.add(c);
			for (Map<String, Object> map : list) {				
				Map<String, Object> m=new HashMap<>();
				if (!DBHelper.getValue(map, "FieldID").equals("rowid")) {
					m.put("id", DBHelper.getValue(map, "FieldID"));
					m.put("text", DBHelper.getValue(map, "fieldname"));
					if (DBHelper.getValueInt(map, "HeadFlag") == 1) {// 明细数据
						l1.add(m);
				    }else if (DBHelper.getValueInt(map, "HeadFlag") == 0) {// 主数据
				    	l2.add(m);
					} 
				} 
			}
			Map<Integer, List<Map<String, Object>>> sMap=new HashMap<>();
			sMap.put(0, l1);
			sMap.put(1, l2);
			return sMap;
		} catch (Exception e) {
			throw e;
		}
	}

	/**
	 * 执行导入组装保存
	 * @param request
	 * @param response
	 * @param map
	 * @param path
	 * @return
	 * @throws Exception
	 */
	public void excelImport(XlsImportParameterEntity xlsenEntity,HttpServletRequest request,HttpServletResponse response) throws Exception {
		String information="";//返回信息
		String dbId = (String) request.getSession().getAttribute(SessionKey.DATA_BASE_ID);
		String cltCode = (String) request.getSession().getAttribute((SessionKey.USERCODE));
		try {
			
//			InputStream iStream = new FileInputStream(path); // 加载excel文件
			com.yc.excel.Excel excel = new com.yc.excel.Excel();
//			int formid = Integer.parseInt(map.get(ExlsParam.FORM_ID.getKey()).toString());
//			int type = Integer.parseInt(map.get(ExlsParam.FORM_TYPE.getKey()).toString());
			xlsenEntity.setTypeDuiyin(getWinType(xlsenEntity.getFormtype()));
			// add map 对应参数
//			map.put(ExlsParam.TYPE_DUIYING.getKey(), typeDuiyin);
			List<GTJson> gList = null;

			try {
				SpObserver.setDBtoInstance("_" + dbId);
				// 获得设置信息 根据主从表加序号排序
				List<Map<String, Object>> sort = excelIfc.getInfo(xlsenEntity.getFormid());
				// 配置
				gList = excel.getExcelJson(request, sort, xlsenEntity);
			} catch (Exception e) {
				throw e;
			} finally {
				SpObserver.setDBtoInstance();
			}
			String typeToGrid = xlsenEntity.getTypeDuiyin() + "|" + ((xlsenEntity.getTypeDuiyin() == 499 || xlsenEntity.getTypeDuiyin() == 497) ? "0" : ((xlsenEntity.getFormtype() == xlsenEntity.getTypeDuiyin()) ? "0" : "1"));
//			if (map.get(OK) != null) {// 已经检查 确定操作
//				//cParent.delete(path);
//				saveTo(request, response, formid, typeToGrid, gList);
//				String temp = CurrentLocal.getExeclInfo();
//				throw new Exception(temp);
//			} else {// 做检查处理
//				String hasGrid = "0";
				switch (xlsenEntity.getFormtype()) {
				case 16:
//					hasGrid = "0";
//					type = 17;
					xlsenEntity.setFormtype(17);
					break;
				case 498:
				case 496:
//					type = 17;
//					hasGrid = "many";
					xlsenEntity.setFormtype(17);
					break;
				default:
//					hasGrid = "1";
					break;
				}
				saveTo(request, response, xlsenEntity.getFormid(), typeToGrid, gList);// 保存
				
				String resultAll = CurrentLocal.getExeclInfo();// 在保存时候出现的错误信息
				String ActionStatus="";//导入状态
				if (!resultAll.equals("")) {// 是否有错误信息，有则页面提示错误信息内容，没有说明保存成功。					
					information="【导入失败】:"+resultAll;		
					ActionStatus="Fail";
				}else{
					information="【导入成功】";
					ActionStatus="Success";
				}

				// 导入的数据信息
				// 导入的标识 In
				// 导入数据审计记录
				try {
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 设置显示格式
					String unid = UUID.randomUUID().toString().toUpperCase();// 生成uuid
					AttachmentEntity attachment = new AttachmentEntity();
					attachment.setUnid(unid);
					attachment.setFormId(xlsenEntity.getFormid());
					attachment.setOriginalPicture(xlsenEntity.getFile1().getBytes());//input2byte(new FileInputStream(new File(path)))
					attachment.setOriginalFileName(xlsenEntity.getFile1().getOriginalFilename());//map.get(ExlsParam.BEFORE_NAME.getKey()).toString()
					attachment.setAuthorCode(cltCode);
					attachment.setAuthorName((String) request.getSession().getAttribute((SessionKey.USERNAME)));

					// 保存导入导出的文件信息
					SpObserver.setDBtoInstance("_" + dbId);
					AttachmentIfc attachmentIfc = (AttachmentIfc) FactoryBean.getBean("AttachmentImpl");
					// 保存导入的数据到指定数据库里面并且返回一个对象
					AttachmentWhereEntity whereEntity=attachmentIfc.saveAttachment(attachment, xlsenEntity.getFile1(),"0");
					//附件唯一标示 unid
					if(whereEntity.getUnid()!=null && whereEntity.getSeq()!=null){
						unid=whereEntity.getUnid()+";"+whereEntity.getSeq();
					}					
					// 数据导入导出审计记录
					int cont = excelIfc.excelRecord(xlsenEntity.getFormid(), xlsenEntity.getDoccode(),
							request.getSession().getAttribute(SessionKey.USERCODE).toString(),
							request.getSession().getAttribute(SessionKey.USERNAME).toString(), "In",
							df.format(new Date()), unid, resultAll, String.valueOf(xlsenEntity.getFormtype()), ActionStatus, "");
					if (cont>0) {
						information +="导入日志记录成功！";
					}else{
						information +="导入日志记录失败！";
					}
				} catch (Exception e) {
					information +="（在记录导入日志时出现代码错误："+e+")";
				} finally {					
					SpObserver.setDBtoInstance();
				}
				super.printJson(response, new CallBackMessage().sendSuccessMessage(information));
				return;
//			}
		} catch (Exception e) {
			throw e;
		} finally {
		}
	}

	/**
	 * 处理广陶附件，从本地更新附件
	 * @param request
	 * @param response
	 */
	@RequestMapping(params = "m=image")
	public void ImportAttachment(HttpServletRequest request, HttpServletResponse response){
		try{
			BaseService baseService= (BaseService) FactoryBean.getBean("BaseService");
			AttachmentIfc attachmentIfc= (AttachmentIfc) FactoryBean.getBean("AttachmentImpl");
			//取出数据
			List<Map<String, Object>> list=baseService.getSimpleJdbcTemplate().queryForList("select a.doccode,a.EnterCode,a.EnterName,b.fujian,b.rowid from t62042h a left join t62042d b on a.DocCode=b.doccode where isnull(b.fujian,'')<>'' ");
			SpObserver.setDBtoInstance("_"+request.getSession().getAttribute(SessionKey.DATA_BASE_ID));
			byte[] orgBinaryimages=null;//源文件
			byte[] smallBinaryimages=null;//缩略图
			int num=0;
			for(Map<String, Object> map:list){
				Integer seq=null;
				if((map.get("fujian")+"").indexOf(";")>0) continue;
				String fileName = "e:/"+map.get("fujian");
//				if(true){
//					ImageUtils.scaleByConfig(fileName, fileName, request);
//				}

				//源文件二进制
				InputStream photoStream = new FileInputStream(fileName);
				orgBinaryimages=input2byte(photoStream);

				//处理缩略图
				String smallImagePath = ImageUtils.buildSmallPicReName(fileName,
						180,180);

				//缩略图二进制
				InputStream photoStream1 = new FileInputStream(smallImagePath);
				smallBinaryimages=input2byte(photoStream1);
				String	uuid=UUID.randomUUID().toString().toUpperCase();  //生成uuid
				File file=new File(fileName);
				AttachmentEntity attachmentEntity = new AttachmentEntity() ;
				attachmentEntity.setUnid(uuid);
				attachmentEntity.setSeq(seq);
				attachmentEntity.setFormId(62042);
				attachmentEntity.setDoccode(map.get("doccode")+"");
				attachmentEntity.setFieldId("fujian");
				attachmentEntity.setRowId(null);
				attachmentEntity.setPhysicalFile(null);
				attachmentEntity.setPhysicalPath(null);
				attachmentEntity.setOriginalFileName(file.getName());
				attachmentEntity.setFileSize(file.length()/1024);
				attachmentEntity.setFileType("jpg");
				attachmentEntity.setAuthorCode(map.get("EnterCode")+"");
				attachmentEntity.setAuthorName(map.get("EnterName")+"");
				UploadFiles uploadFiles = new UploadFiles() ;
				uploadFiles.setOrgBinaryimages(orgBinaryimages);
				uploadFiles.setSmallBinaryimages(smallBinaryimages);
				AttachmentWhereEntity attachmentWhereEntity =  attachmentIfc.saveAttachment(uploadFiles, attachmentEntity,"1") ;
				//更新t62042d fujian字段为uuid;seq。
				baseService.getSimpleJdbcTemplate().update(" update a set a.fujian=?  from t62042d a where a.doccode=? and a.rowid=?",uuid+";"+attachmentWhereEntity.getSeq(),map.get("doccode"),map.get("rowid"));
				num++;
				System.out.println(num+">>"+map.get("doccode")+";"+map.get("rowid"));
			}
			this.print(response,"一共处理完成-"+num);
		}catch (Exception e){
			this.print(response,e.getMessage());
		}finally {
			SpObserver.setDBtoInstance();
		}
	}
}