package com.minpay.reportManage.action;

import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import com.min.util.DateUtil;
import com.minpay.common.bean.User;
import com.minpay.common.constant.Constant;
import com.minpay.common.format.IFormatService;
import com.minpay.common.service.IDianwangService;
import com.minpay.common.service.IPropertiesService;
import com.minpay.common.util.CommonUtil;
import com.minpay.common.util.FilesUtil;
import com.minpay.common.util.ListUtil;
import com.minpay.db.table.mapper.DwFileDetail00Mapper;
import com.minpay.db.table.mapper.DwFileDetail01Mapper;
import com.minpay.db.table.mapper.DwFileDetail02Mapper;
import com.minpay.db.table.mapper.DwFileDetail03Mapper;
import com.minpay.db.table.mapper.DwFileInfMapper;
import com.minpay.db.table.mapper.DwReporttypeFiletypeRelMapper;
import com.minpay.db.table.mapper.ImUserMapper;
import com.minpay.db.table.model.DwFileDetail00;
import com.minpay.db.table.model.DwFileDetail00Example;
import com.minpay.db.table.model.DwFileDetail01;
import com.minpay.db.table.model.DwFileDetail01Example;
import com.minpay.db.table.model.DwFileDetail02;
import com.minpay.db.table.model.DwFileDetail03;
import com.minpay.db.table.model.DwFileInf;
import com.minpay.db.table.model.DwReporttypeFiletypeRelExample;
import com.minpay.db.table.model.DwReporttypeFiletypeRelKey;
import com.minpay.db.table.model.ImUser;
import com.minpay.db.table.model.ImUserExample;
import com.minpay.db.table.own.mapper.DataWareManageMapper;
import com.minpay.db.table.own.mapper.SequenceMapper;
import com.startup.minpay.frame.business.IMINAction;
import com.startup.minpay.frame.business.MINHttpServletRequestContext;
import com.startup.minpay.frame.business.res.MINActionResult;
import com.startup.minpay.frame.constant.IMINBusinessConstant;
import com.startup.minpay.frame.constant.IMINTransactionEnum;
import com.startup.minpay.frame.exception.MINBusinessException;
import com.startup.minpay.frame.jdbc.MINRowBounds;
import com.startup.minpay.frame.service.base.IMINDataBaseService;
import com.startup.minpay.frame.service.base.Service;
import com.startup.minpay.frame.session.MINSession;
import com.startup.minpay.frame.target.MINAction;
import com.startup.minpay.frame.target.MINComponent;
import com.startup.minpay.frame.target.MINParam;
import com.sun.tools.example.debug.expr.ParseException;

import net.sf.json.JSONArray;

@MINComponent
public class DataWareManageAction implements IMINAction {

    private IMINDataBaseService db;
    /** 原始数据查询 */
    public final static String FILE_INF_QUERY = "fileInfQuery";
    
    /** 原始文件上传 */
    public final static String FILE_INF_UPLOAD = "fileInfUpload";
    
    /** 原始文件下载 */
    public final static String FILE_INF_DOWNLOAD = "fileInfDownload";
    
    /** 原始文件删除 */
    public final static String FILE_INF_DELETE = "fileInfDelete";
    
    /** 原始文件详情 */
    public final static String FILE_INF_DETAIL = "fileInfDetail";


    /**
     * 原始数据查询 
     * @param page
     * @param limit
     * @param date
     * @param fileName
     * @return
     * @throws MINBusinessException
     * @throws InvocationTargetException 
     * @throws IllegalArgumentException 
     * @throws IllegalAccessException 
     * @throws SecurityException 
     * @throws NoSuchMethodException 
     * @throws InstantiationException 
     */
    @MINAction(value = FILE_INF_QUERY)
    public MINActionResult fileInfQuery(
		@MINParam(key = "page", defaultValue = "1") int page,
        @MINParam(key = "limit", defaultValue = "10") int limit,
        @MINParam(key = "date") String date,
        @MINParam(key = "fileName") String fileName,
        @MINParam(key = "notInData") String notInData,
        @MINParam(key = "reportType") String reportType,
        MINSession session
    ) throws MINBusinessException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InstantiationException {
        MINActionResult res = new MINActionResult();
        db = Service.lookup(IMINDataBaseService.class);
		//分页
		MINRowBounds rows = new MINRowBounds(page, limit);
		rows.setSeparateSql(true);
		Map<String, Object> param = new HashMap<String, Object>();
		
		param.put("date", date);
		param.put("fileName", fileName);
		
		if (!CommonUtil.isEmpty(notInData)) {
			JSONArray notInArray = JSONArray.fromObject(notInData);
			if (notInArray.size() != 0) {
				param.put("notInArray", notInArray);
			}
		}
		// 查询生成报表所需的文件类型
		if (!CommonUtil.isEmpty(reportType)) {
			DwReporttypeFiletypeRelExample reporttypeFiletypeRelExample = new DwReporttypeFiletypeRelExample();
			reporttypeFiletypeRelExample.createCriteria().andReporttypeIdEqualTo(reportType);
			List<DwReporttypeFiletypeRelKey> selectByExample = db.selectByExample(DwReporttypeFiletypeRelMapper.class, reporttypeFiletypeRelExample);
			if (selectByExample.size() > 0) {
				param.put("fileTypeArray", selectByExample);
			}
		}
		
		User user = session.getUser();
		String roleId = user.getRoleId();
		
		// 非系统管理员
		if (!Constant.ROLE_ID.equals(roleId)) {
			param.put("branchId", user.getBranchid());
		}
		
		List<Map<String, String>> list = db.getMybatisMapper(DataWareManageMapper.class)
												.selectFileInf(param, rows);
		
		if (list.size() == 0) {
			return res;
		}
		
		List<String> creatUserIdList = CommonUtil.getIdFromList(list, "creatUser");
		ImUserExample userExam = new ImUserExample();
		userExam.createCriteria().andIdIn(creatUserIdList);
		List<ImUser> userList = db.selectByExample(ImUserMapper.class, userExam);
		
		String[] str1 = {"creatUser"};
		String[] str2 = {"id"};
		String[] str3 = {"name"};
		String[] str4 = {"userName"};
		list = ListUtil.mergeList(list, userList, str1, str2, str3, str4);
		
		list = Service.lookup(IFormatService.class).formatDate(list, "uploadDate");
		// 设置返回值
		res.set(IMINBusinessConstant.F_PAGING_LAY, list);
		res.set(IMINBusinessConstant.F_PAGING_COUNT, rows.getCount());
		return res;
    }
    
    /**
     * 上传原始文件
     * @param request
     * @param fileType
     * @param chongfuFlag	重复上传标志 true:重复数据确认上传
     * @param session
     * @return
     * @throws MINBusinessException
     * @throws InvalidFormatException
     * @throws IOException
     */
    @MINAction(value = FILE_INF_UPLOAD, transaction = IMINTransactionEnum.CMT)
    public MINActionResult fileInfUpload(
    		MINHttpServletRequestContext request,
    		@MINParam(key = "fileType") String fileType,
    		@MINParam(key = "chongfuFlag") boolean chongfuFlag,
    		MINSession session
    		) throws MINBusinessException, InvalidFormatException, IOException {
    	MINActionResult res = new MINActionResult();
    	db = Service.lookup(IMINDataBaseService.class);
		
		// 获取文件
		FileItem fileItem = request.getFile("file");
		String fileName = fileItem.getName();
		if (!fileName.endsWith(".xlsx") && !fileName.endsWith(".xls")) {
			throw new MINBusinessException("文件格式错误!");
		}
		
		String fileId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_INF_NO");
		List<Map<String,String>> list = null;
		// 台区停电
		if (Constant.FILE_DETAIL_TYPE_00.equals(fileType)) {
			// 获取表格信息
			String[] titleKey = new String[]{"number","addressNum","addressName","date", "area","comonpanyName","terminalName","terminalNum","terminalAddress","startTime","endTime"};
			list = FilesUtil.readExecleasy(fileItem, titleKey, 1);
			
			if (list.size() == 0) {
				throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据
			}
			
			// 详情数据存入数据库
			for (Map<String,String> excelMap : list) {
				// 日期
				String date = excelMap.get("date").replaceAll("-", "").replaceAll(":", "").replaceAll(" ", "").substring(0,8);
				String startTime = excelMap.get("startTime").replaceAll("-", "").replaceAll(":", "").replaceAll(" ", "");
				String endTime = excelMap.get("endTime").replaceAll("-", "").replaceAll(":", "").replaceAll(" ", "");
				
				// 页面确认有重复数据,且点击确认上传,不再判断数据库是否有数据
				if (!chongfuFlag) {
					// 验证是否有重复数据
					DwFileDetail00Example fileDetail00Example = new DwFileDetail00Example();
					fileDetail00Example.createCriteria().andAddressNumEqualTo(excelMap.get("addressNum"))
														.andAddressNameEqualTo(excelMap.get("addressName"))
														.andDateEqualTo(date)
														.andAreaEqualTo(excelMap.get("area"))
														.andComonpanyNameEqualTo(excelMap.get("comonpanyName"))
														.andTerminalNameEqualTo(excelMap.get("terminalName"))
														.andTerminalNumEqualTo(excelMap.get("terminalNum"))
														.andTerminalAddressEqualTo(excelMap.get("terminalAddress"))
														.andStartTimeEqualTo(startTime)
														.andEndTimeEqualTo(endTime);
					List<DwFileDetail00> selectByExample = db.selectByExample(DwFileDetail00Mapper.class, fileDetail00Example);
					if (selectByExample.size() != 0) {
						res.set("chongfu", selectByExample);
						return res;
					}
				}
				
				String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_00_NO");
				DwFileDetail00 fileDetail00 = new DwFileDetail00();
				fileDetail00.setId(fileDetailId);
				fileDetail00.setNumber(excelMap.get("number"));
				fileDetail00.setAddressNum(excelMap.get("addressNum"));
				fileDetail00.setAddressName(excelMap.get("addressName"));
				fileDetail00.setDate(date);
				fileDetail00.setArea(excelMap.get("area"));
				fileDetail00.setComonpanyName(excelMap.get("comonpanyName"));
				fileDetail00.setTerminalName(excelMap.get("terminalName"));
				fileDetail00.setTerminalNum(excelMap.get("terminalNum"));
				fileDetail00.setTerminalAddress(excelMap.get("terminalAddress"));
				fileDetail00.setStartTime(startTime);
				fileDetail00.setEndTime(endTime);
				fileDetail00.setFileId(fileId);
				db.insertSelective(DwFileDetail00Mapper.class, fileDetail00);
			}
		// 终端投托运
		} else if (Constant.FILE_DETAIL_TYPE_01.equals(fileType)) {
			String[] titleKey = new String[]{"id","tname","cmode","value", "facname","type","fdid","yxstatus","tytime"};
			list = FilesUtil.readExecleasy(fileItem, titleKey, 1);
			
			if (list.size() == 0) {
				throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据
			}
			
			// 详情数据存入数据库
			for (Map<String,String> excelMap : list) {
				DwFileDetail01Example dwFileDetail01 = new DwFileDetail01Example();
				dwFileDetail01.createCriteria().andExcelIdEqualTo(excelMap.get("id"))
								.andExcelTnameEqualTo(excelMap.get("tname"))
								.andExcelCmodeEqualTo(excelMap.get("cmode"))
								.andExcelValueEqualTo(excelMap.get("value"))
								.andExcelFacnameEqualTo(excelMap.get("facname"))
								.andExcelTypeEqualTo(excelMap.get("type"))
								.andExcelFdidEqualTo(excelMap.get("fdid"))
								.andExcelYxstatusEqualTo(excelMap.get("yxstatus"));
				String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_01_NO");
				DwFileDetail01 fileDetail01 = new DwFileDetail01();
				fileDetail01.setId(fileDetailId);
				fileDetail01.setExcelId(excelMap.get("id"));
				fileDetail01.setExcelTname(excelMap.get("tname"));
				fileDetail01.setExcelCmode(excelMap.get("cmode"));
				fileDetail01.setExcelValue(excelMap.get("value"));
				fileDetail01.setExcelFacname(excelMap.get("facname"));
				fileDetail01.setExcelType(excelMap.get("type"));
				fileDetail01.setExcelFdid(excelMap.get("fdid"));
				fileDetail01.setExcelYxstatus(excelMap.get("yxstatus"));
				String tytime = excelMap.get("tytime");
				tytime = tytime.replaceAll("-", "").replaceAll(" ", "").replaceAll(":", "");
				if (!CommonUtil.isEmpty(tytime) && tytime.length() != 14) {
					throw new MINBusinessException(excelMap.get("tytime") + "--时间格式错误");
				}
				fileDetail01.setExcelTytime(tytime);
				fileDetail01.setFileId(fileId);
				db.insertSelective(DwFileDetail01Mapper.class, fileDetail01);
			}
		// FA动作
		} else if (Constant.FILE_DETAIL_TYPE_02.equals(fileType)) {
			String[] titleKey = new String[]{"no","beginTime","endTime","gdlx", "cxbdz","xlmc","xllx","qdfa","gzqjpd","gzqhdhf","gzgl"};
			list = FilesUtil.readExecleasy(fileItem, titleKey, 1);
			
			if (list.size() == 0) {
				throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据
			}
			
			// 详情数据存入数据库
			for (Map<String,String> excelMap : list) {
				String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_02_NO");
				DwFileDetail02 fileDetail02 = new DwFileDetail02();
				fileDetail02.setId(fileDetailId);
				fileDetail02.setNo(excelMap.get("no"));
				fileDetail02.setBeginTime(excelMap.get("beginTime"));
				fileDetail02.setEndTime(excelMap.get("endTime"));
				fileDetail02.setGdlx(excelMap.get("gdlx"));
				fileDetail02.setCxbdz(excelMap.get("cxbdz"));
				fileDetail02.setXlmc(excelMap.get("xlmc"));
				fileDetail02.setXllx(excelMap.get("xllx"));
				fileDetail02.setQdfa(excelMap.get("qdfa"));
				fileDetail02.setGzqjpd(excelMap.get("gzqjpd"));
				fileDetail02.setGzqhdhf(excelMap.get("gzqhdhf"));
				fileDetail02.setGzgl(excelMap.get("gzgl"));
				fileDetail02.setFileId(fileId);
				db.insertSelective(DwFileDetail02Mapper.class, fileDetail02);
			}
		}else if(Constant.FILE_DETAIL_TYPE_03.equals(fileType)){
			String[] titleKey = new String[]{"no","sgsmc","xgsmc","bdzmc","xlmc","tqid","byqmc","yhgldw1","yhid","yhmc","yhgldw2","dymax","dymin","dyhgl","csxl","cxxl","dysc","gdlx","ljts","sjrq","gddw"};         
			list = FilesUtil.readExecleasy(fileItem, titleKey, 3);
			
			if (list.size() == 0) {
				throw new MINBusinessException("无数据，请添加数据");//无数据，请添加数据
			}
			// 详情数据存入数据库
			for (Map<String,String> excelMap : list) {
				String fileDetailId = db.getMybatisMapper(SequenceMapper.class).getSequence("FILE_DETAIL_03_NO");
				String sjrq = excelMap.get("sjrq");
				try {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
					Date date = sdf.parse(sjrq);
					SimpleDateFormat todf = new SimpleDateFormat("yyyyMMdd");// 设置日期格式
					sjrq = todf.format(date);
				} catch (Exception ex) {
				}
//				DateFormat formater = new SimpleDateFormat("yyyyMMdd");
//				String sjrq = formater.format(excelMap.get("sjrq"));
				DwFileDetail03 fileDetail03 = new DwFileDetail03();
				fileDetail03.setId(fileDetailId);
				fileDetail03.setNo(excelMap.get("no"));
				fileDetail03.setSgsmc(excelMap.get("sgsmc"));
				fileDetail03.setXgsmc(excelMap.get("xgsmc"));
				fileDetail03.setBdzmc(excelMap.get("bdzmc"));
				fileDetail03.setXlmc(excelMap.get("xlmc"));
				fileDetail03.setTqid(excelMap.get("tqid"));
				fileDetail03.setByqmc(excelMap.get("byqmc"));
				fileDetail03.setYhgldw1(excelMap.get("yhgldw1"));
				fileDetail03.setYhid(excelMap.get("yhid"));
				fileDetail03.setYhmc(excelMap.get("yhmc"));
				fileDetail03.setYhgldw2(excelMap.get("yhgldw2"));
				fileDetail03.setDymax(excelMap.get("dymax"));
				fileDetail03.setDymin(excelMap.get("dymin"));
				fileDetail03.setDyhgl(excelMap.get("dyhgl"));
				fileDetail03.setCsxl(excelMap.get("csxl"));
				fileDetail03.setCxxl(excelMap.get("cxxl"));
				fileDetail03.setDysc(excelMap.get("dysc"));
				fileDetail03.setGdlx(excelMap.get("gdlx"));
				fileDetail03.setLjts(excelMap.get("ljts"));
				fileDetail03.setSjrq(sjrq);
				fileDetail03.setGddw(excelMap.get("gddw"));
				fileDetail03.setFileId(fileId);
				db.insertSelective(DwFileDetail03Mapper.class, fileDetail03);
			}
		}
		
		// 文件名
		String filePath = Service.lookup(IPropertiesService.class).getSystemProperties().get("FILE_SERVER_BASE_PATH").getKey();
		String saveName = UUID.randomUUID().toString().replaceAll("-", "");
		String[] fileNameArray = fileName.split("\\.");
		saveName = saveName + "." + fileNameArray[fileNameArray.length - 1];
		
		// 保存文件数据
		String currentDate = DateUtil.getCurrentDateString();
		User user = (User)session.getUser();
		DwFileInf fileInf = new DwFileInf();
		fileInf.setId(fileId);
		fileInf.setUploadDate(currentDate);
		fileInf.setFileName(fileName);
		fileInf.setCreatUser(user.getId());
		fileInf.setBranch(user.getBranchid());
		fileInf.setDataNum(list.size() + "");
		fileInf.setType(fileType);
		fileInf.setSavePath(filePath + saveName);
		db.insertSelective(DwFileInfMapper.class, fileInf);
		
		// 保存文件到本地
		FilesUtil.uploadFile(fileItem, filePath, "", saveName, 0);
		
		return res;
    }

    /**
     * 原始数据文件下载
     * @param fileId
     * @param response
     * @return
     * @throws MINBusinessException
     */
    @MINAction(value = FILE_INF_DOWNLOAD)
    public HttpServletResponse fileInfDownload(
        @MINParam(key = "fileId") String fileId,
        HttpServletResponse response
    ) throws MINBusinessException {
        db = Service.lookup(IMINDataBaseService.class);
        DwFileInf fileInf = db.selectByPrimaryKey(DwFileInfMapper.class, fileId);
        FilesUtil.writeFile2Response(response, fileInf.getSavePath(), fileInf.getFileName());
        return response;
    }
    
    /**
     * 原始文件删除
     * @param fileId
     * @param fileType
     * @return
     * @throws MINBusinessException
     */
    @MINAction(value = FILE_INF_DELETE, transaction = IMINTransactionEnum.CMT)
    public MINActionResult fileInfDelete(
    		@MINParam(key = "fileId") String fileId,
    		@MINParam(key = "fileType") String fileType
    		) throws MINBusinessException {
    	MINActionResult res = new MINActionResult();
    	db = Service.lookup(IMINDataBaseService.class);
    	// 删除原始文件
    	DwFileInf fileInf = new DwFileInf();
    	fileInf.setState(Constant.FILE_INF_STATE_1);
    	fileInf.setId(fileId);
    	db.updateByPrimaryKeySelective(DwFileInfMapper.class, fileInf);
    	
    	/** 删除明细 */
    	// 台区停电
    	if (Constant.FILE_DETAIL_TYPE_00.equals(fileType)) {
    		DwFileDetail00Example fileDetail00Example = new DwFileDetail00Example();
    		fileDetail00Example.createCriteria().andFileIdEqualTo(fileId);
    		db.deleteByExample(DwFileDetail00Mapper.class, fileDetail00Example);
    	// 低电压
    	} else if (Constant.FILE_DETAIL_TYPE_01.equals(fileType)) {
    		
    	}
    	return res;
    }
    
    /**
     * 原始文件明细查询
     * @param fileId
     * @param fileIdListStr
     * @param fileType
     * @return
     * @throws MINBusinessException
     */
    @MINAction(value = FILE_INF_DETAIL)
    public MINActionResult fileInfDetail(
    		@MINParam(key = "fileId") String fileId,
    		@MINParam(key = "fileIdListStr") String fileIdListStr,
    		@MINParam(key = "fileType") String fileType,
    		@MINParam(key = "page", defaultValue = "1") int page,
            @MINParam(key = "limit", defaultValue = "100") int limit
    		) throws MINBusinessException {
    	MINActionResult res = new MINActionResult();
    	db = Service.lookup(IMINDataBaseService.class);
    	MINRowBounds rows = new MINRowBounds(page, limit);
		rows.setSeparateSql(true);
    	
    	Map<String, String> param = new HashMap<String, String>();
    	param.put("fileId", fileId);
    	param.put("fileIdListStr", fileIdListStr);
    	param.put("fileType", fileType);
    	// 查询明细信息
    	List fileInfDetailList = Service.lookup(IDianwangService.class).queryFileInfDetail(param, rows);
    	res.set(IMINBusinessConstant.F_PAGING_LAY, fileInfDetailList);
    	res.set(IMINBusinessConstant.F_PAGING_COUNT, rows.getCount());
    	return res;
    }
}