package com.zhcs.dt.controller.information.annualPlan;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import sun.security.krb5.internal.PAData;

import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.servlet.ModelAndView;

import com.zhcs.dt.controller.base.BaseController;
import com.zhcs.dt.entity.Page;
import com.zhcs.dt.service.information.annualPlan.AnnualPlanCompManager;
import com.zhcs.dt.service.information.annualPlan.AnnualPlanManager;
import com.zhcs.dt.service.information.annualPlan.DownloadZip;
import com.zhcs.dt.service.information.annualPlan.MultExcelUtils;
import com.zhcs.dt.service.information.annualPlan.TotalAmountUtil;
import com.zhcs.dt.util.AppUtil;
import com.zhcs.dt.util.Jurisdiction;
import com.zhcs.dt.util.PageData;
import com.zhcs.dt.util.Tools;

/** 
 * 类名称：年度养护计划
 * 创建人：Yuanf
 * 创建时间：2018-11-12
 */
@Controller
@RequestMapping(value="/annualPlanComp")
class AnnualPlanCompController extends BaseController {
	private static final HashMap<String, ArrayList<PageData>> ArrayList = null;
	String menuUrl = "annualPlanComp/list.do"; //菜单地址(权限用)
	@Resource(name="annualPlanCompService")
	private AnnualPlanCompManager annualPlanCompService;
	@Resource(name="annualPlanService")
	private AnnualPlanManager annualPlanService;
	List<String> newTables = new ArrayList<String>();
	
	/**列表
	 * @param page
	 * @throws Exception
	 */
	@RequestMapping(value="/list")
	public ModelAndView list(Page page) throws Exception{
		logBefore(logger, Jurisdiction.getUsername()+"养护计划完成情况");
		//if(!Jurisdiction.buttonJurisdiction(menuUrl, "cha")){return null;} //校验权限(无权查看时页面会有提示,如果不注释掉这句代码就无法进入列表页面,所以根据情况是否加入本句代码)		
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		//pd.put("USERNAME", Jurisdiction.getUsername().toString());
		//通过username获取对应的公司和路段，控股集团管理员只可以看到状态为“待审批”和“已完成”的计划，路公司管理员和路段管理员可以看到本公司所有计划,admin没有权限控制
		PageData userInfo = annualPlanService.getCompanyAndRoad(Jurisdiction.getUsername().toString());	//通过username获取公司和路段
		//通过ROLE_ID获取角色
		String roleName = annualPlanService.getRoleName(userInfo.get("ROLE_ID").toString());
		pd = this.getPageData();
		/*String keywords = pd.getString("keywords");				//关键词检索条件
		if(null != keywords && !"".equals(keywords)){
			pd.put("keywords", keywords.trim());
		}*/
		List<PageData>	varList = new ArrayList<PageData>();
		if(!"控股集团管理员".equalsIgnoreCase(roleName)){
			pd.put("COMPANY", userInfo.get("COMPANY"));
			varList = annualPlanCompService.getPlanCompList(pd);	//列出年度计划列表
			mv.setViewName("information/annualPlan/annualPlanComp/annualPlanComp_list");
			mv.addObject("varList", varList);
		}else{
			List<String> yearList = new ArrayList<String>();
			varList = annualPlanService.getKGCompYearList(pd);	//列出年度完成计划列表
			yearList = getYearList(varList);
			mv.setViewName("information/annualPlan/annualPlanComp/annualPlanComp_kg_list");
			mv.addObject("varList", yearList);
		}		
		mv.addObject("pd", pd);
		mv.addObject("userInfo", userInfo);
		mv.addObject("roleName", roleName);
		mv.addObject("QX",Jurisdiction.getHC());	//按钮权限
		return mv;
	}
	
	private List<String> getYearList(List<PageData> varList){
		List<String> yearList = new ArrayList<String>();
		for(PageData pd : varList){
			if(pd.get("STATUS") != null && pd.get("YEAR") != null){
				if("审批通过".equalsIgnoreCase(pd.get("STATUS").toString()) && !yearList.contains(pd.get("YEAR").toString())){
					yearList.add(pd.get("YEAR").toString());
				}
			}
		}
		return yearList;
	}
	
	/**查询所选汇总表
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/showSumTable")
	public ModelAndView showSumTable(Model model)throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		pd.put("table", "oa_department_"+pd.getString("YEAR").toString());
		mv.setViewName("information/annualPlan/annualSumTableComp/"+pd.get("type"));
		List<PageData> varList = annualPlanCompService.getdata(pd);
		mv.addObject("varList", varList);
		mv.addObject("pd", pd);
		mv.addObject("QX",Jurisdiction.getHC());	//按钮权限
		return mv;
	}
	
	/**查询所选汇总表
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/showDailySumTable")
	public ModelAndView showDailySumTable(Model model)throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		mv.setViewName("information/annualPlan/annualSumTableComp/"+pd.get("type"));
		pd.put("type", "highway_daily_plan_statable");
		//动态获取oa_department
		pd.put("table", "oa_department_"+pd.getString("YEAR").toString());
		List<PageData> varList = annualPlanCompService.getDailyCompData(pd);
		for(PageData tpd : varList){
			List<Object> list = new ArrayList<Object>();
			list.add(tpd.get("CLEAN_COST"));
			list.add(tpd.get("PATROL_COST"));
			list.add(tpd.get("SUBGRADE_COST"));
			list.add(tpd.get("PAVEMENT_COST"));
			list.add(tpd.get("BRIDGE_COST"));
			list.add(tpd.get("CULVERT_COST"));
			list.add(tpd.get("TUNNEL_COST"));
			list.add(tpd.get("GREEN_COST"));
			list.add(tpd.get("SAFE_COST"));
			list.add(tpd.get("LINE_COST"));
			list.add(tpd.get("CAR_COST"));
			list.add(tpd.get("EMERGENCY_COST"));
			BigDecimal totalCost =  new BigDecimal("0");
			totalCost = addNums(list);
			if(totalCost != BigDecimal.ZERO){
				//小数位格式化
				BigDecimal ten = new BigDecimal("10.00");
        		if(totalCost.compareTo(ten)<1){
        			totalCost = totalCost.setScale(1, BigDecimal.ROUND_HALF_UP);
        		}
        		else{
        			totalCost = totalCost.setScale(0, BigDecimal.ROUND_HALF_UP);
        		}	
				tpd.put("TOTAL_COST", totalCost);
			}
		}
		mv.addObject("varList", varList);
		mv.addObject("pd", pd);
		mv.addObject("QX",Jurisdiction.getHC());	//按钮权限
		return mv;
	}
	
	private BigDecimal addNums(List<Object> list){
		BigDecimal result = new BigDecimal("0");
		for(Object obj : list){
			if(obj != null){
				BigDecimal d = new BigDecimal("0");
				d = getBigDecimal(obj);
				result = result.add(d);
			}
		}
		return result;
	}
	
	  /**
     * Object转BigDecimal类型
     *
     * @param value 要转的object类型
     * @return 转成的BigDecimal类型数据
     */
    public static BigDecimal getBigDecimal(Object value) {
        BigDecimal ret = null;
        if (value != null) {
            if (value instanceof BigDecimal) {
                ret = (BigDecimal) value;
            } else if (value instanceof String) {
                ret = new BigDecimal((String) value);
            } else if (value instanceof BigInteger) {
                ret = new BigDecimal((BigInteger) value);
            } else if (value instanceof Number) {
                ret = new BigDecimal(((Number) value).doubleValue());
            } else {
                throw new ClassCastException("Not possible to coerce [" + value + "] from class " + value.getClass() + " into a BigDecimal.");
            }
        }
        return ret;
    }
	
	/**查询所选明细表
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/showDettTable")
	public ModelAndView showDettTable(Model model)throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		pd.put("table", "oa_department_"+pd.get("YEAR").toString());
		mv.setViewName("information/annualPlan/annualSumTableComp/"+pd.get("type"));
		List<PageData> dettList = annualPlanCompService.getDettData(pd);
		
		if(dettList ==null || dettList.equals(" ") || dettList.isEmpty() ) {
			mv.addObject("dettList", "");
		}else if("highway_reserve_fees_sumtable_view".equalsIgnoreCase(pd.get("type").toString())){
			List<PageData> feeList = annualPlanCompService.getTotalFee(pd);
			dettList = getReserveFee(dettList, feeList);			
			mv.addObject("dettList", dettList);
		}else {
			LinkedHashMap<String, LinkedHashMap<String, ArrayList<PageData>>> varMap = new LinkedHashMap<String, LinkedHashMap<String, ArrayList<PageData>>>();
			Double sumPlanCost = (double) 0;
			Double sumActualCost = (double) 0;
			String prevCompanyName = "";
			LinkedHashMap<String, ArrayList<PageData>> xjroadMap = new LinkedHashMap<String, ArrayList<PageData>>();
			for(PageData tpd : dettList){
				String company = tpd.get("COMPANY").toString();
				String road = tpd.get("ROAD").toString();
				if(varMap.containsKey(company)){
					LinkedHashMap<String, ArrayList<PageData>> roadMap = varMap.get(company);
					if(roadMap != null && roadMap.containsKey(road)){
						if(tpd.get("PLAN_COST")!=null){
							double plan = Double.parseDouble(tpd.get("PLAN_COST").toString());
							plan = formatNum(plan);
							sumPlanCost += plan;
						}
						if(tpd.get("ACTUAL_COST")!=null){
							double actual = Double.parseDouble(tpd.get("ACTUAL_COST").toString());
							actual = formatNum(actual);
							sumActualCost += actual;
						}
						ArrayList<PageData> pList = roadMap.get(road);
						pList.add(tpd);
					}else{
						if(tpd.get("PLAN_COST")!=null){
							double planc = Double.parseDouble(tpd.get("PLAN_COST").toString());
							planc = formatNum(planc);
							sumPlanCost += planc;
						}
						if(tpd.get("ACTUAL_COST")!=null){
							double actualc = Double.parseDouble(tpd.get("ACTUAL_COST").toString());
							actualc = formatNum(actualc);
							sumActualCost += actualc;
						}
						ArrayList<PageData> npList = new ArrayList<PageData>();
						npList.add(tpd);
						roadMap.put(road, npList);					
					}
					xjroadMap = roadMap;
					prevCompanyName = company;
				}else{					
					if(varMap.size() > 0){
						ArrayList<PageData> xjList = new ArrayList<PageData>();
						PageData xjPd = new PageData();
						xjPd.put("COMPANY", prevCompanyName);
						xjPd.put("ROAD", "小计");
						xjPd.put("PROJECT_NAME", "");
						xjPd.put("PROJECT_TYPE", "");
						xjPd.put("SCALE", "");
						xjPd.put("PLAN_QUANTITIES", "");
						xjPd.put("PLAN_COST", sumPlanCost);
						xjPd.put("ACTUAL_QUANTITIES", "");
						xjPd.put("REMARKS", "");
						xjPd.put("ACTUAL_COST", sumActualCost);
						xjList.add(xjPd);
						xjroadMap=varMap.get(prevCompanyName);
						xjroadMap.put("小计", xjList);
						varMap.put(prevCompanyName, xjroadMap);
					}	
					ArrayList<PageData> mpList = new ArrayList<PageData>();
					LinkedHashMap<String, ArrayList<PageData>> mroadMap = new LinkedHashMap<String, ArrayList<PageData>>();
					mpList.add(tpd);				
					mroadMap.put(road, mpList);
					varMap.put(company, mroadMap);
					prevCompanyName = company;
					sumPlanCost = (double) 0;
					sumActualCost = (double) 0;
					if(tpd.get("PLAN_COST")!=null){
						double planco = Double.parseDouble(tpd.get("PLAN_COST").toString());
						planco = formatNum(planco);
						sumPlanCost += planco;
					}
					if(tpd.get("ACTUAL_COST")!=null){
						double actualco = Double.parseDouble(tpd.get("ACTUAL_COST").toString());
						actualco = formatNum(actualco);
						sumActualCost += actualco;
					}
				}
			}
			
			PageData lastxjPd = new PageData();
			lastxjPd.put("COMPANY", prevCompanyName);
			lastxjPd.put("ROAD", "小计");
			lastxjPd.put("PROJECT_NAME", "");
			lastxjPd.put("PROJECT_TYPE", "");
			lastxjPd.put("SCALE", "");
			lastxjPd.put("PLAN_QUANTITIES", "");
			lastxjPd.put("PLAN_COST", sumPlanCost);
			lastxjPd.put("ACTUAL_QUANTITIES", "");
			lastxjPd.put("REMARKS", "");
			lastxjPd.put("ACTUAL_COST", sumActualCost);
			
			List<PageData> dettListNew = new ArrayList<PageData>();
			for(Entry<String, LinkedHashMap<String, java.util.ArrayList<PageData>>> centry : varMap.entrySet()){
				HashMap<String, java.util.ArrayList<PageData>> roadMap = centry.getValue();
				for(Entry<String, java.util.ArrayList<PageData>> rentry : roadMap.entrySet()){
					ArrayList<PageData> projectList = rentry.getValue();
					for(PageData ppd : projectList){
						dettListNew.add(ppd);
					}
				}
			}
			dettListNew.add(lastxjPd);			
			mv.addObject("dettList", dettListNew);
		}
		mv.addObject("pd", pd);
		mv.addObject("QX",Jurisdiction.getHC());	//按钮权限
		return mv;
	}
	
	private List<PageData> getReserveFee(List<PageData> dettList, List<PageData> feeList) throws Exception{
		LinkedHashMap<String, LinkedHashMap<String, ArrayList<PageData>>> varMap = new LinkedHashMap<String, LinkedHashMap<String, ArrayList<PageData>>>();
		Double sumActualCost = (double) 0;
		String prevCompanyName = "";
		String year = "";
		LinkedHashMap<String, ArrayList<PageData>> xjroadMap = new LinkedHashMap<String, ArrayList<PageData>>();
		List<PageData> dettListNew = new ArrayList<PageData>();
		for(PageData tpd : dettList){
			String company = tpd.get("COMPANY").toString();
			String road = tpd.get("ROAD").toString();	
			Object roadfee = "";
			if(varMap.containsKey(company)){
				LinkedHashMap<String, ArrayList<PageData>> roadMap = varMap.get(company);
				for(PageData feepd : feeList){
					if(feepd.get("ROAD").equals(tpd.get("ROAD"))){
						if(feepd.get("AUDIT_FEE") != null){
							roadfee = feepd.get("AUDIT_FEE");
						}else{
							roadfee = feepd.get("TOTAL_COST");  //计划金额
						}
					}
				}
				if(roadMap != null && roadMap.containsKey(road)){
					if(tpd.get("ACTUAL_COST")!=null){
						sumActualCost += Double.parseDouble(tpd.get("ACTUAL_COST").toString());
					}
					ArrayList<PageData> pList = roadMap.get(road);
					if(roadfee != ""){
						tpd.put("ROAD", tpd.get("ROAD")+"("+ roadfee + ")");
					}else{
						tpd.put("ROAD", tpd.get("ROAD"));
					}
					pList.add(tpd);
				}else{
					if(tpd.get("ACTUAL_COST")!=null){
						sumActualCost += Double.parseDouble(tpd.get("ACTUAL_COST").toString());
					}
					ArrayList<PageData> npList = new ArrayList<PageData>();
					if(roadfee != ""){
						tpd.put("ROAD", tpd.get("ROAD")+"("+ roadfee + ")");
					}else{
						tpd.put("ROAD", tpd.get("ROAD"));
					}
					npList.add(tpd);
					roadMap.put(road, npList);					
				}
				xjroadMap = roadMap;
				prevCompanyName = company;
			}else{					
				if(varMap.size() > 0){
					ArrayList<PageData> xjList = new ArrayList<PageData>();
					PageData xjPd = new PageData();
					xjPd.put("COMPANY", prevCompanyName);					
					xjPd.put("ROAD", "小计");
					xjPd.put("PROJECT_NAME", "");
					xjPd.put("REMARKS", "");
					xjPd.put("ACTUAL_COST", sumActualCost);
					xjList.add(xjPd);
					xjroadMap = varMap.get(prevCompanyName);
					xjroadMap.put("小计", xjList);
					varMap.put(prevCompanyName, xjroadMap);
				}	
				ArrayList<PageData> mpList = new ArrayList<PageData>();
				LinkedHashMap<String, ArrayList<PageData>> mroadMap = new LinkedHashMap<String, ArrayList<PageData>>();
				for(PageData feepd : feeList){
					if(feepd.get("ROAD").equals(tpd.get("ROAD"))){
						if(feepd.get("AUDIT_FEE") != null){
							roadfee = feepd.get("AUDIT_FEE");
						}else{
							roadfee = feepd.get("TOTAL_COST");  //计划金额
						}
					}
				}
				if(roadfee != ""){
					tpd.put("ROAD", tpd.get("ROAD")+"("+ roadfee + ")");
				}else{
					tpd.put("ROAD", tpd.get("ROAD"));
				}
				mpList.add(tpd);				
				mroadMap.put(road, mpList);
				varMap.put(company, mroadMap);
				prevCompanyName = company;
				if(tpd.get("YEAR")!=null){
					year = tpd.get("YEAR").toString();
				}
				//换公司名，sumActualCost重新计算
				sumActualCost = (double) 0;
				if(tpd.get("ACTUAL_COST")!=null){
					sumActualCost += Double.parseDouble(tpd.get("ACTUAL_COST").toString());
				}
			}
		}
		
		PageData lastxjPd = new PageData();
		lastxjPd.put("COMPANY", prevCompanyName);
		lastxjPd.put("YEAR", year);
		String lastsumfee = annualPlanCompService.getSumTotalFee(lastxjPd);
		if(lastsumfee != null){
			lastxjPd.put("COMPANY", prevCompanyName+"("+lastsumfee+")");	
		}
		lastxjPd.put("ROAD", "小计");
		lastxjPd.put("PROJECT_NAME", "");
		lastxjPd.put("REMARKS", "");
		lastxjPd.put("ACTUAL_COST", sumActualCost);
		
		for(Entry<String, LinkedHashMap<String, java.util.ArrayList<PageData>>> centry : varMap.entrySet()){
			HashMap<String, java.util.ArrayList<PageData>> roadMap = centry.getValue();
			for(Entry<String, java.util.ArrayList<PageData>> rentry : roadMap.entrySet()){
				ArrayList<PageData> projectList = rentry.getValue();
				for(PageData ppd : projectList){
					String sumfee = annualPlanCompService.getSumTotalFee(ppd);
					if(sumfee != null){
						ppd.put("COMPANY", ppd.get("COMPANY")+"("+sumfee+")");
					}else{
						ppd.put("COMPANY", ppd.get("COMPANY"));
					}
					
					dettListNew.add(ppd);
				}
			}
		}
		dettListNew.add(lastxjPd);
		return dettListNew;
	}	
	
	 /**查询显示报告列表和表格列表
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/showReportAndTable")
	public ModelAndView showReportAndTable(Model model)throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		//String planID = pd.getString("ID");
		List<PageData>	varList = annualPlanService.showReportList(pd);	//根据PLANID读取相关report信息
		if(pd.get("ROLENAME").toString().equalsIgnoreCase("路段管理员")){
			List<PageData> reportList = new ArrayList<PageData>();
			String[] roads = pd.get("ROAD").toString().split(",");
			for(int i = 0; i < roads.length; i++){
				for(PageData reportInfo : varList){
					if(reportInfo.get("ROAD_NAME") != null){
						if(reportInfo.get("ROAD_NAME").toString().trim().equalsIgnoreCase(roads[i].trim())){
							reportList.add(reportInfo);
						}
					}
				}
			}
			varList = reportList;
		}else{
			List<PageData>	subReportList = new ArrayList<PageData>();
			for(PageData var : varList){
				if(var.get("ROAD_ID") == null){
					mv.addObject("reportInfo", var);
				}else{
					subReportList.add(var);
				}
			}
			varList = subReportList;
		}
		
		String reportid = null;
		String reportName = null;
		//用于显示界面右边表格信息
		List<PageData>	vartableList = null;
		if(pd.get("REPORTID") != null){
			for(PageData var : varList){
				if(var.get("ID").toString().equalsIgnoreCase(pd.get("REPORTID").toString())){
					reportName = var.get("NAME").toString();
				}
			}
			vartableList = annualPlanService.getTableList(pd);	//根据reportID读取相关table信息	
			mv.addObject("selectReport", reportName);
		}else{
			for(PageData reportpd : varList){
				reportid = reportpd.getString("ID");
				reportName = reportpd.getString("NAME");
				if(reportName.indexOf("养护计划完成情况分报告") > 0){
					pd.put("REPORTID", reportid);
					pd.put("ROAD_ID", reportpd.get("ROAD_ID"));
					pd.put("ROAD", reportpd.get("ROAD_NAME"));
					vartableList = annualPlanService.getTableList(pd);	//根据reportID读取相关table信息	
					mv.addObject("selectReport", reportName);
					break;
				}
			}
		}
		mv.setViewName("information/annualPlan/annualPlanComp/annualPlanComp_report_list");
		mv.addObject("varList", varList);
		mv.addObject("vartableList", vartableList);
		mv.addObject("pd", pd);
		mv.addObject("QX",Jurisdiction.getHC());	//按钮权限
		return mv;
	}
	
	 /**查询显示表格列表
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/getTableListInfo")
	@ResponseBody
	public Object getTableListInfo()throws Exception{
		Map<String,Object> map = new HashMap<String,Object>();
		String errInfo = "success";
		PageData pd = new PageData();
		pd = this.getPageData();
		List<PageData>  vartableList = annualPlanService.getTableList(pd);	//根据reportID读取相关table信息	
//		mv.setViewName("information/annualPlan/annualPlan_report_list");
//		mv.addObject("vartableList", vartableList);
//		mv.addObject("data", vartableList);
//		mv.addObject("pd", pd);
//		mv.addObject("QX",Jurisdiction.getHC());	//按钮权限
//		return mv;
		List<PageData> pdList = new ArrayList<PageData>();
		for(PageData tpd :vartableList){
			PageData pdf = new PageData();
			pdf.put("NAME", tpd.get("NAME"));
			pdf.put("COMPANY_ID", tpd.get("COMPANY_ID"));
			pdf.put("COMPANY_NAME", tpd.get("COMPANY_NAME"));
			pdf.put("YEAR", tpd.get("YEAR"));
			pdf.put("ROAD_ID", tpd.get("ROAD_ID"));
			pdf.put("ROAD_NAME", tpd.get("ROAD_NAME"));
			pdf.put("TYPE", tpd.get("TYPE"));
			pdf.put("CTIME", tpd.get("CTIME"));
			pdf.put("MTIME", tpd.get("MTIME"));
			pdf.put("STATUS", tpd.get("STATUS"));
			pdList.add(pdf);
		}
		map.put("list", pdList);
		map.put("result", errInfo);				//返回结果
		return AppUtil.returnObject(new PageData(), map);
	}
	
	 /**显示、编辑子表格内容
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/showTableRecod")
	public ModelAndView showTableRecod()throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		List<PageData>	varList = annualPlanService.showTableRecod(pd);	//根据ID读取
		mv.setViewName("information/annualPlan/annualPlan_edit");
		mv.addObject("varList", varList);
		mv.addObject("pd", pd);
		mv.addObject("QX",Jurisdiction.getHC());	//按钮权限
		return mv;
	}	
	
	/**去新增页面
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/goAdd")
	public ModelAndView goAdd()throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		//List<PageData>	varList = annualPlanService.getReportType(pd);	//获取报告类型
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        Date date = new Date();
        String currentYear = sdf.format(date);
        List<String> yearItems = new ArrayList<String>();
        for(int i= -4; i < 6; i++){
        	Integer intyear = Integer.parseInt(currentYear);
        	yearItems.add(String.valueOf(intyear + i));
        }
		mv.setViewName("information/annualPlan/annualPlanComp/annualPlanComp_add");
		//mv.addObject("varList", varList);
		mv.addObject("msg", "save");
		mv.addObject("yearList", yearItems);
		mv.addObject("currentYear", currentYear);
		mv.addObject("pd", pd);
		return mv;
	}
	
	/**新增报告和子表格的关系
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/save")
	public ModelAndView save() throws Exception{
		logBefore(logger, Jurisdiction.getUsername()+"新增annualPlanComp");
		//if(!Jurisdiction.buttonJurisdiction(menuUrl, "add")){return null;} //校验权限
		List<PageData> reportList = new ArrayList<PageData>();
		List<PageData> tableList = new ArrayList<PageData>();
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		String planID = this.get32UUID();
		pd.put("ID", planID);//主键
		pd.put("NAME", pd.get("YEAR")+"年"+pd.get("COMPANY")+"年度养护计划完成情况");
		//设置查询oa_department的动态表名
		PageData cpPd = new PageData();
		String oa_department_table = "oa_department_"+pd.get("YEAR");
		cpPd.put("table", oa_department_table);
		cpPd.put("NAME", pd.get("COMPANY").toString());
		PageData companypd = (PageData)annualPlanService.getCompanyID(cpPd);  //通过路公司名获取路公司ID和父节点id
		String companyID = companypd.getString("BIANMA");
		String DEPARTMENT_ID = companypd.getString("DEPARTMENT_ID");		
		pd.put("COMPANY_ID", companyID);
		pd.put("COMPANY_NAME", pd.get("COMPANY"));
		pd.put("DEPARTMENT_ID", DEPARTMENT_ID);
		pd.put("CTIME", Tools.date2Str(new Date()));	//创建时间
		pd.put("MTIME", Tools.date2Str(new Date()));	//修改时间
		pd.put("STATUS", "新建");	//创建时间
		annualPlanService.addAnnualPlan(pd);   //新建年度计划
		pd.put("NAME", pd.get("YEAR")+"年"+pd.get("COMPANY")+"年度养护计划完成情况总报告");
		
		String reportID = this.get32UUID();
		pd.put("ID", reportID);//主键
		pd.put("PLANID", planID);
		reportList.add(pd);
		
		List<PageData> tableTypeList = annualPlanService.getTableType(); //获取表格类型	
		for(PageData tableInfo : tableTypeList){
			if(tableInfo.get("REPORT_ID").toString().equalsIgnoreCase("1")){
				PageData tablepd = new PageData();	
				tablepd.put("REPORTID", reportID);
				if(tableInfo.getString("TABLE_NAME").indexOf("计划完成情况") > 0){
					String tempYear = String.valueOf(Integer.parseInt(pd.get("YEAR").toString()) - 1);
					tablepd.put("YEAR", tempYear);
					tablepd.put("NAME", tempYear+"年"+pd.get("COMPANY")+tableInfo.getString("TABLE_NAME"));
				}else{
					tablepd.put("YEAR", pd.get("YEAR"));
					tablepd.put("NAME", pd.get("YEAR")+"年"+pd.get("COMPANY")+tableInfo.getString("TABLE_NAME"));
				}
				tablepd.put("TYPE", tableInfo.getString("TABLE_TYPE"));
				tablepd.put("COMPANY_ID", companyID);
				tablepd.put("COMPANY_NAME", pd.get("COMPANY"));				
				tablepd.put("CTIME", Tools.date2Str(new Date()));	//创建时间
				tablepd.put("MTIME", Tools.date2Str(new Date()));	//修改时间
				tablepd.put("STATUS", "自动统计");	//创建时间
				tablepd.put("ROAD_NAME", "");
				tablepd.put("ROAD_ID", "");
				tableList.add(tablepd);
				//annualPlanService.addAnnualTable(tablepd);
			}
		}
		cpPd.put("DEPARTMENT_ID", DEPARTMENT_ID);
		List<PageData> roadpdList = annualPlanService.getRoadInfo(cpPd); //通过父节点ID获取路段信息		
		for(PageData reportinfo : roadpdList){
			PageData reportpd = new PageData();
			reportID = this.get32UUID();
			reportpd.put("ID", reportID);//主键
			reportpd.put("PLANID", planID);
			reportpd.put("ROAD_NAME", reportinfo.getString("NAME"));
			reportpd.put("ROAD_ID", reportinfo.getString("BIANMA"));
			reportpd.put("NAME", pd.get("YEAR")+"年"+pd.get("COMPANY")+reportinfo.getString("NAME")+"年度养护计划分报告");
			reportpd.put("COMPANY_ID", companyID);
			reportpd.put("COMPANY_NAME", pd.get("COMPANY"));
			reportpd.put("DEPARTMENT_ID", DEPARTMENT_ID);
			reportpd.put("CTIME", Tools.date2Str(new Date()));	//创建时间
			reportpd.put("MTIME", Tools.date2Str(new Date()));	//修改时间
			reportpd.put("STATUS", "新建");	//创建时间
			reportList.add(reportpd);
			//annualPlanService.addAnnualReport(pd);   //新建年度分报告列表		
			
			for(PageData tableInfo : tableTypeList){
				if(tableInfo.get("REPORT_ID").toString().equalsIgnoreCase("2")){
					PageData tablepdroad = new PageData();	
					tablepdroad.put("REPORTID", reportID);
					if(tableInfo.getString("TABLE_NAME").indexOf("计划完成情况") > 0 || tableInfo.getString("TABLE_NAME").indexOf("预备费使用情况汇总表") > 0){
						String tempYear = String.valueOf(Integer.parseInt(pd.get("YEAR").toString()) - 1);
						tablepdroad.put("YEAR", tempYear);
						tablepdroad.put("NAME", tempYear + "年"+pd.get("COMPANY")+reportinfo.getString("NAME")+tableInfo.getString("TABLE_NAME"));
					}else{
						tablepdroad.put("YEAR", pd.get("YEAR"));
						tablepdroad.put("NAME", pd.get("YEAR")+"年"+pd.get("COMPANY")+reportinfo.getString("NAME")+tableInfo.getString("TABLE_NAME"));
					}
					tablepdroad.put("TYPE", tableInfo.getString("TABLE_TYPE"));
					tablepdroad.put("COMPANY_ID", companyID);
					tablepdroad.put("COMPANY_NAME", pd.get("COMPANY"));
					tablepdroad.put("ROAD_NAME", reportinfo.getString("NAME"));
					tablepdroad.put("ROAD_ID", reportinfo.getString("BIANMA"));					
					tablepdroad.put("CTIME", Tools.date2Str(new Date()));	//创建时间
					tablepdroad.put("MTIME", Tools.date2Str(new Date()));	//修改时间
					newTables.add("高速公路预防养护计划立项详表");
					newTables.add("高速公路修复养护计划立项详表");
					newTables.add("高速公路试验检测项目计划立项详表");
					newTables.add("高速公路科研项目计划立项详表");
					newTables.add("高速公路设备大修项目计划立项详表");
					newTables.add("高速公路专项养护计划立项详表");
					newTables.add("高速公路道路技术状况现状统计表");
					newTables.add("高速公路交通等信息综合统计表");
					newTables.add("高速公路日常养护计划统计表");
					newTables.add("高速公路预备费使用情况汇总表");
					if(newTables.contains(tableInfo.getString("TABLE_NAME").toString())){
						tablepdroad.put("STATUS", "新建");
					}else{
						tablepdroad.put("STATUS", "自动统计");
					}
					tableList.add(tablepdroad);
				}
			}
		}
		annualPlanService.addAnnualReport(reportList);
		annualPlanService.addAnnualTable(tableList);
		mv.addObject("pd", pd);
	    mv.setViewName("save_result");	
		return mv;
	}
	
	/**判断该年养护完成情况是否已存在
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/hasPlan")
	@ResponseBody
	public Object hasPlan()throws Exception{
		Map<String,String> map = new HashMap<String,String>();
		PageData pd = new PageData();
		pd = this.getPageData();
		String msg = "success";
		//查询系统已存在的养护计划
		List<PageData> planPd = annualPlanCompService.getPlanCompByCompanyAndYear(pd);
		if(planPd.size() > 0){
			msg = "已存在相同的养护完成情况，新建失败";
		}
		map.put("result", msg);
		return AppUtil.returnObject(new PageData(), map);
	}
	
	/**删除年度计划和其下面的报告、表格
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/delAnnualPlan")
	@ResponseBody
	public Object delAnnualPlan()throws Exception{
		Map<String,String> map = new HashMap<String,String>();
		PageData pd = new PageData();
		pd = this.getPageData();
		//PageData planPd = annualPlanService.getYearAndCompanyByID(pd);  //通过ANNUALPLAN_ID获取报告的年份和公司
		annualPlanCompService.delAnnualCompPlan(pd);	//删除年度计划
		List<String> reportIDList = annualPlanService.getReportID(pd);  //获取年度计划对应的报告ID
		annualPlanService.delReport(pd); //删除报告
		annualPlanService.delTables(reportIDList); //删除表格列表
		map.put("result", "success");
		return AppUtil.returnObject(new PageData(), map);
	}	
	
	/**新增立项详表新增弹出页面
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/addNewTablePanel")
	public ModelAndView addNewTablePanel()throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		List<PageData> varList = annualPlanService.getDetTableType();	//获取立项详表报告类型
		//通过reportid获取对应的路段信息
		PageData roadInfo = annualPlanService.getRoadInfoByReportID(pd);
		pd.put("ROAD_NAME", roadInfo.get("ROAD"));
		pd.put("ROAD_ID", roadInfo.get("ROAD_ID"));
		mv.setViewName("information/annualPlan/annualPlan_addNewTable");
		mv.addObject("varList", varList);
		mv.addObject("msg", "addNewTable");
		mv.addObject("pd", pd);
		return mv;
	}
	
	/**只有路段分报告可以新增立项详表
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/addNewTable")
	@ResponseBody
	public Object addNewTable()throws Exception{
		Map<String,String> map = new HashMap<String,String>();
		PageData pd = new PageData();
		pd = this.getPageData();
		//List<PageData> varList = annualPlanService.getDetTableType();	//获取立项详表报告类型
		//通过reportid获取对应的路段信息
		try{
			PageData roadInfo = annualPlanService.getRoadInfoByReportID(pd);
			pd.put("ROAD_NAME", roadInfo.get("ROAD"));
			pd.put("ROAD_ID", roadInfo.get("ROAD_ID"));
//			mv.setViewName("information/annualPlan/annualPlan_addNewTable");
//			mv.addObject("varList", varList);
//			mv.addObject("pd", pd);
			map.put("result", "success");			
		}catch(Exception e){
			map.put("result", "只有路段分报告可以增加立项详表");
		}		
		return AppUtil.returnObject(new PageData(), map);
	}
	
	/**显示立项详表新增弹出页面
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/showNewTable")
	public ModelAndView showNewTable()throws Exception{
		ModelAndView mv = this.getModelAndView();
		PageData pd = new PageData();
		pd = this.getPageData();
		List<PageData> varList = annualPlanService.getDetTableType();	//获取立项详表报告类型
		mv.setViewName("information/annualPlan/annualPlan_addNewTable");
		mv.addObject("varList", varList);
		mv.addObject("msg", "save");
		mv.addObject("pd", pd);
		return mv;
	}
	
	@RequestMapping(value="/hasBetTable")
	@ResponseBody
	public Object hasBetTable() throws Exception{
		PageData pd = new PageData();
	    pd = this.getPageData();
	    String msg = "success";
	    String tableName = pd.get("YEAR")+"年"+ pd.get("COMPANY")+pd.get("ROAD") + pd.get("Table_Name");
	    pd.put("NAME", tableName);
	    List<PageData> tableInfo = annualPlanService.getTableInfo(pd);
	    if(tableInfo.size() > 0){
	    	msg = "已存在相同的立项表";
	    }
		Map<String,Object> map = new HashMap<String,Object>();
	    map.put("msg",msg);
        return AppUtil.returnObject(new PageData(), map);
	}
	
	@RequestMapping(value="/changeTableName")
	@ResponseBody
	public Object changeTableName() throws Exception{
		PageData pd = new PageData();
	    pd = this.getPageData();
	    String name = pd.getString("PROJECT_NAME");
	    pd.put("NAME", name);
	    annualPlanService.changeTableName(pd);	//修改立项详表名称
		Map<String,Object> map = new HashMap<String,Object>();
	    map.put("msg","success");
        return AppUtil.returnObject(new PageData(), map);
	}
	
	@RequestMapping(value="/addNewTableInfo")
	@ResponseBody
	public Object addNewTableInfo() throws Exception{
		PageData pd = new PageData();
	    pd = this.getPageData();
	    String name = pd.getString("PROJECT_NAME");
	    pd.put("NAME", name);
	    //获取reportId "2017年高管中心宁连、宁洛及金马高速年度养护计划分报告"
    	String reportName = pd.getString("YEAR")+"年" + pd.getString("COMPANY") + pd.getString("ROAD") + "年度养护计划分报告";
    	String reportId = annualPlanService.getTableReportID(reportName); //get annual_report_list id
    	//在annual_table_list表中新增一条立项详情表记录       
    	pd.put("REPORTID", reportId);
    	pd.put("CTIME", Tools.date2Str(new Date()));	//创建时间
		pd.put("MTIME", Tools.date2Str(new Date()));	//修改时间
	    annualPlanService.addNewTableInfo(pd);	//insert annual_table_list新增立项详表
		Map<String,Object> map = new HashMap<String,Object>();
	    map.put("msg","success");
        return AppUtil.returnObject(new PageData(), map);
	}
	
	/**删除立项表
	 * @param
	 * @throws Exception
	 */
	@RequestMapping(value="/delTable")
	@ResponseBody
	public Object delTable()throws Exception{
		Map<String,String> map = new HashMap<String,String>();
		PageData pd = new PageData();
		pd = this.getPageData();
		String msg = "删除立项表成功";
		try{
			annualPlanService.delRecordFromTable(pd); //删除table_lis中的表数据
			pd.put("TABLE_NAME", pd.get("TYPE"));
			if(pd.get("NAME") != null){
				if(pd.get("NAME").toString().contains("---")){
					pd.put("PROJECT_NAME", pd.get("NAME").toString().split("---")[1]);
				}
			}			
			annualPlanService.delModifyRecord(pd); //删除留痕表中的主表数据
			pd.put("TABLE_NAME", pd.get("TYPE") + "_cost");
			annualPlanService.delModifyRecord(pd); //删除留痕表中的主表关联的cost表数据
		}catch(Exception e){
			msg = "删除立项表失败";
		}
		map.put("result", msg);
		return AppUtil.returnObject(new PageData(), map);
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

    @RequestMapping("/exporthz")
    public String exporthz(HttpServletRequest request,HttpServletResponse response) throws Exception{
        String path = request.getSession().getServletContext().getRealPath("/uploadFiles/file");
        ModelAndView mv = this.getModelAndView();
        PageData pd = new PageData();
        pd = this.getPageData();
        List<String> paths = new ArrayList<String>();
        List<String> result = new ArrayList<String>();
        //excel文档
        List<PageData> exceldata = setMultExcelData(pd);
        MultExcelUtils ex = new MultExcelUtils();
        String excelpath = ex.exportExcel(pd.getString("exportname")+".xlsx",path,response,exceldata);
        result.add(excelpath);
        //zip压缩下载
        DownloadZip.downloadZip(response,request,result,path);
        return null;
    }
    
    public List<PageData> setMultExcelData(PageData param) throws Exception {
        List<PageData> result = new ArrayList<PageData>();
        String[] modelNames = {"养护计划完成情况汇总","日常养护计划完成情况汇总","预防养护计划完成情况汇总",
                "修复养护计划完成情况汇总","专项养护计划完成情况汇总","应急养护计划完成情况汇总",
                "试验检测项目计划完成情况汇总","科研项目计划完成情况汇总"};
        String[] keyStrs = {
                "DESIGN_KM,CARE_KM,DAILY_DESIGN,DAILY_COMPLETE,DAILY_RATIO,DAILY_COMPLETE_KM,"
                        + "MINOR_PREVENTION_DESIGN,MINOR_PREVENTION_COMPLETE,MINOR_PREVENTION_RATIO,MIDDLE_PREVENTION_DESIGN,MIDDLE_PREVENTION_COMPLETE,MIDDLE_PREVENTION_RATIO,"
                        + "OVERHAUL_PREVENTION_DESIGN,OVERHAUL_PREVENTION_COMPLETE,OVERHAUL_PREVENTION_RATIO,PREVENTION_DESIGN_TOTAL,PREVENTION_COMPLETE_TOTAL,PREVENTION_RATIO_TOTAL,"
                        + "MINOR_REPAIR_DESIGN,MINOR_REPAIR_COMPLETE,MINOR_REPAIR_RATIO,MIDDLE_REPAIR_DESIGN,MIDDLE_REPAIR_COMPLETE,MIDDLE_REPAIR_RATIO,OVERHAUL_DESIGN,OVERHAUL_COMPLETE,OVERHAUL_REPAIR_RATIO,"
                        + "REPAIR_DESIGN_TOTAL,REPAIR_COMPLETE_TOTAL,REPAIR_RATIO_TOTAL,"
                        + "SPECIAL_DESIGN,SPECIAL_COMPLETE,SPECIAL_RATIO,"
                        + "EMERGENCY_DESIGN,EMERGENCY_COMPLETE,EMERGENCY_RATIO,"
                        + "TEST_DESIGN,TEST_COMPLETE,TEST_RATIO,"
                        + "SCIENTIFIC_DESIGN,SCIENTIFIC_COMPLETE,SCIENTIFIC_RATIO,"
                        + "DEVICE_DESIGN,DEVICE_COMPLETE,DEVICE_RATIO,"
                        + "RESERVE_DESIGN,RESERVE_COMPLETE,RESERVE_RATIO,"
                        + "DESIGN_TOTAL,COMPLETE_TOTAL,RATIO_TOTAL"
                ,"DESIGN_KM,CARE_KM,PLAN_COST,"
                                + "CLEAN_COST,PATROL_COST,SUBGRADE_COST,PAVEMENT_COST,BRIDGE_COST,CULVERT_COST,TUNNEL_COST,SAFE_COST,LINE_COST,GREEN_COST,CAR_COST,EMERGENCY_COST,TOTAL_COST,COST_KM,COMPLETE_RATIO"
                ,"DESIGN_KM,CARE_KM,PLAN_COST,"
                        + "MIN_SUBGRADE_COST,MIN_PAVEMENT_COST,MIN_BRIDGE_COST,MIN_TUNNEL_COST,MIN_TOTAL_COST,"
                        + "MID_SUBGRADE_COST,MID_PAVEMENT_COST,MID_BRIDGE_COST,MID_TUNNEL_COST,MID_TOTAL_COST,"
                        + "MAX_SUBGRADE_COST,MAX_PAVEMENT_COST,MAX_BRIDGE_COST,MAX_TUNNEL_COST,MAX_TOTAL_COST,"
                        + "SUBGRADE_COST_TOTAL,PAVEMENT_COST_TOTAL,BRIDGE_COST_TOTAL,TUNNEL_COST_TOTAL,COST_TOTAL,COMPLETE_RATIO"
                ,"DESIGN_KM,CARE_KM,PLAN_COST,"
                        + "MIN_SUBGRADE_COST,MIN_PAVEMENT_COST,MIN_BRIDGE_COST,MIN_TUNNEL_COST,MIN_TRAFFIC_SAFE_COST,MIN_ROADSIDE_COST,MIN_GREEN_COST,MIN_OTHER_COST,MIN_TOTAL_COST,"
                        + "MID_SUBGRADE_COST,MID_PAVEMENT_COST,MID_BRIDGE_COST,MID_TUNNEL_COST,MID_TRAFFIC_SAFE_COST,MID_ROADSIDE_COST,MID_GREEN_COST,MID_OTHER_COST,MID_TOTAL_COST,"
                        + "MAX_SUBGRADE_COST,MAX_PAVEMENT_COST,MAX_BRIDGE_COST,MAX_TUNNEL_COST,MAX_TRAFFIC_SAFE_COST,MAX_ROADSIDE_COST,MAX_GREEN_COST,MAX_OTHER_COST,MAX_TOTAL_COST,"
                        + "SUBGRADE_COST_TOTAL,PAVEMENT_COST_TOTAL,BRIDGE_COST_TOTAL,TUNNEL_COST_TOTAL,TRAFFIC_SAFE_COST_TOTAL,ROADSIDE_SAFE_COST_TOTAL,GREEN_COST_TOTAL,OTHER_COST_TOTAL,TOTAL_COST,COMPLETE_RATIO"
                ,"DESIGN_KM,CARE_KM,PLAN_COST,"
                        + "SUBGRADE_COST,PAVEMENT_COST,BRIDGE_COST,TUNNEL_COST,TRAFFIC_SAFE_COST,ROADSIDE_COST,GREEN_COST,OTHER_COST,TOTAL_COST,COMPLETE_RATIO"
                ,"DESIGN_KM,CARE_KM,PLAN_COST,"
                        + "SUBGRADE_COST,PAVEMENT_COST,BRIDGE_COST,TUNNEL_COST,TRAFFIC_SAFE_COST,ROADSIDE_COST,GREEN_COST,OTHER_COST,TOTAL_COST,COMPLETE_RATIO"
                ,"DESIGN_KM,CARE_KM,PLAN_COST,"
                        + "SUBGRADE_COST,PAVEMENT_COST,BRIDGE_COST,TUNNEL_COST,TRAFFIC_SAFE_COST,ROADSIDE_COST,OTHER_COST,TOTAL_COST,COMPLETE_RATIO"
                ,"DESIGN_KM,CARE_KM,PLAN_COST,"
                        + "SUBGRADE_COST,PAVEMENT_COST,BRIDGE_COST,OTHER_COST,TOTAL_COST,COMPLETE_RATIO"
                
        };
        String[] tableNames = {"annual_comp_sumtable_view","annual_daily_comp_sumtable_view","annual_prevention_comp_sumtable_view",
                "annual_repair_comp_sumtable_view","annual_special_comp_sumtable_view","annual_emergency_comp_sumtable_view",
                "annual_test_comp_sumtable_view","annual_scientific_comp_sumtable_view"};
        int[] startRowIndexs = {4,3,4,4,3,3,3,3};
        int[] tabletype = {6,6,6,6,6,6,6,6};
        int[] totalRange = {2,2,2,2,2,2,2,2};
        String[][][] funcArrys = {
        		{
        			{"percent","DAILY_RATIO","IF(ISERROR((G#row#/F#row#)*100),\"\",(G#row#/F#row#)*100)"},
        			{"percent","DAILY_COMPLETE_KM","ROUND(IF(ISERROR((G#row#/E#row#)),\"0.0\",(G#row#/E#row#)),1)"},
        			{"percent","MINOR_PREVENTION_RATIO","IF(ISERROR((K#row#/J#row#)*100),\"\",(K#row#/J#row#)*100)"},
        			{"percent","MIDDLE_PREVENTION_RATIO","IF(ISERROR((N#row#/M#row#)*100),\"\",(N#row#/M#row#)*100)"},
        			{"percent","OVERHAUL_PREVENTION_RATIO","IF(ISERROR((Q#row#/P#row#)*100),\"\",(Q#row#/P#row#)*100)"},
        			{"sum","PREVENTION_DESIGN_TOTAL","IF(SUM(J#row#,M#row#,P#row#)<10,ROUND(SUM(J#row#,M#row#,P#row#),1),ROUND(SUM(J#row#,M#row#,P#row#),0))"},
        			{"sum","PREVENTION_COMPLETE_TOTAL","IF(SUM(K#row#,N#row#,Q#row#)<10,ROUND(SUM(K#row#,N#row#,Q#row#),1),ROUND(SUM(K#row#,N#row#,Q#row#),0))"},
        			{"percent","PREVENTION_RATIO_TOTAL","IF(ISERROR((T#row#/S#row#)*100),\"\",(T#row#/S#row#)*100)"},
        			{"percent","MINOR_REPAIR_RATIO","IF(ISERROR((W#row#/V#row#)*100),\"\",(W#row#/V#row#)*100)"},
        			{"percent","MIDDLE_REPAIR_RATIO","IF(ISERROR((Z#row#/Y#row#)*100),\"\",(Z#row#/Y#row#)*100)"},
        			{"percent","OVERHAUL_REPAIR_RATIO","IF(ISERROR((AC#row#/AB#row#)*100),\"\",(AC#row#/AB#row#)*100)"},
        			{"sum","REPAIR_DESIGN_TOTAL","IF(SUM(V#row#,Y#row#,AB#row#)<10,ROUND(SUM(V#row#,Y#row#,AB#row#),1),ROUND(SUM(V#row#,Y#row#,AB#row#),0))"},
        			{"sum","REPAIR_COMPLETE_TOTAL","IF(SUM(W#row#,Z#row#,AC#row#)<10,ROUND(SUM(W#row#,Z#row#,AC#row#),1),ROUND(SUM(W#row#,Z#row#,AC#row#),0))"},
        			{"percent","REPAIR_RATIO_TOTAL","IF(ISERROR((AF#row#/AE#row#)*100),\"\",(AF#row#/AE#row#)*100)"},
        			{"percent","SPECIAL_RATIO","IF(ISERROR((AI#row#/AH#row#)*100),\"\",(AI#row#/AH#row#)*100)"},
        			{"percent","EMERGENCY_RATIO","IF(ISERROR((AL#row#/AK#row#)*100),\"\",(AL#row#/AK#row#)*100)"},
        			{"percent","TEST_RATIO","IF(ISERROR((AO#row#/AN#row#)*100),\"\",(AO#row#/AN#row#)*100)"},
        			{"percent","SCIENTIFIC_RATIO","IF(ISERROR((AR#row#/AQ#row#)*100),\"\",(AR#row#/AQ#row#)*100)"},
        			{"percent","DEVICE_RATIO","IF(ISERROR((AU#row#/AT#row#)*100),\"\",(AU#row#/AT#row#)*100)"},
        			{"percent","RESERVE_RATIO","IF(ISERROR((AX#row#/AW#row#)*100),\"\",(AX#row#/AW#row#)*100)"},
        			{"sum","DESIGN_TOTAL","IF(SUM(F#row#,S#row#,AE#row#,AH#row#,AK#row#,AN#row#,AQ#row#,AT#row#,AW#row#)<10,ROUND(SUM(F#row#,S#row#,AE#row#,AH#row#,AK#row#,AN#row#,AQ#row#,AT#row#,AW#row#),1),ROUND(SUM(F#row#,S#row#,AE#row#,AH#row#,AK#row#,AN#row#,AQ#row#,AT#row#,AW#row#),0))"},
        			{"sum","COMPLETE_TOTAL","IF(SUM(G#row#,T#row#,AF#row#,AI#row#,AL#row#,AO#row#,AR#row#,AU#row#,AX#row#)<10,ROUND(SUM(G#row#,T#row#,AF#row#,AI#row#,AL#row#,AO#row#,AR#row#,AU#row#,AX#row#),1),ROUND(SUM(G#row#,T#row#,AF#row#,AI#row#,AL#row#,AO#row#,AR#row#,AU#row#,AX#row#),0))"},
        			{"percent","RATIO_TOTAL","IF(ISERROR((BA#row#/AZ#row#)*100),\"\",(BA#row#/AZ#row#)*100)"}
        		},{
        			{"percent","COST_KM","ROUND(IF(ISERROR((S#row#/E#row#)),\"0.0\",(S#row#/E#row#)),1)"},
        			{"percent","COMPLETE_RATIO","IF(ISERROR((S#row#/F#row#)*100),\"\",(S#row#/F#row#)*100)"}
        		},
        		{
        			{"percent","COMPLETE_RATIO","IF(ISERROR((Z#row#/F#row#)*100),\"\",(Z#row#/F#row#)*100)"}
        		},
        		{
        			{"percent","COMPLETE_RATIO","IF(ISERROR((AP#row#/F#row#)*100),\"\",(AP#row#/F#row#)*100)"}
        		},{
        			{"percent","COMPLETE_RATIO","IF(ISERROR((O#row#/F#row#)*100),\"\",(O#row#/F#row#)*100)"}
        		},{
        			{"percent","COMPLETE_RATIO","IF(ISERROR((O#row#/F#row#)*100),\"\",(O#row#/F#row#)*100)"}
        		},{
        			{"percent","COMPLETE_RATIO","IF(ISERROR((N#row#/F#row#)*100),\"\",(N#row#/F#row#)*100)"}
        		},{
        			{"percent","COMPLETE_RATIO","IF(ISERROR((K#row#/F#row#)*100),\"\",(K#row#/F#row#)*100)"}
        		}};
        PageData pd = new PageData();
        for (int i = 0; i < tableNames.length; i++) {
            PageData data = new PageData();
            pd.put("type", tableNames[i]);
            pd.put("YEAR", param.get("YEAR"));
            pd.put("table", "oa_department_"+param.get("YEAR").toString());
            List<PageData> list = annualPlanCompService.getcompletehzdata(pd);
           /* //养护计划完成情况汇总
        	if(i==0){
        		list = TotalAmountUtil.completeYhjhTotalAmount(list);
        		//格式化数据
            	//list = TotalAmountUtil.formatDecimalPlace(list,"1");
        	}*/
        	
            data.put("list", list);
            data.put("modelName", modelNames[i]);
            data.put("keyStr", keyStrs[i]);
            data.put("startRowIndex", startRowIndexs[i]);
            data.put("tabletype", tabletype[i]);
            data.put("funcArry", funcArrys[i]);
            data.put("totalRange", totalRange[i]);
            data.put("pd", pd);
            result.add(data);
        }
        return result;
    }
    
    @RequestMapping("/exportmx")
    public String exportmx(HttpServletRequest request,HttpServletResponse response) throws Exception{
        String path = request.getSession().getServletContext().getRealPath("/uploadFiles/file");
        ModelAndView mv = this.getModelAndView();
        PageData pd = new PageData();
        pd = this.getPageData();
        List<String> paths = new ArrayList<String>();
        List<String> result = new ArrayList<String>();
        //excel文档
        List<PageData> exceldata = setMxExcelData(pd);
        MultExcelUtils ex = new MultExcelUtils();
        String excelpath = ex.exportExcel(pd.getString("exportname")+".xlsx",path,response,exceldata);
        result.add(excelpath);
        //zip压缩下载
        DownloadZip.downloadZip(response,request,result,path);
        return null;
    }
    
    public List<PageData> setMxExcelData(PageData param) throws Exception {
        List<PageData> result = new ArrayList<PageData>();
        String[] modelNames = {"预防养护计划完成情况汇总表","修复养护计划完成情况汇总表","专项养护计划完成情况汇总表","应急养护计划完成情况汇总表",
        		"试验检测项目计划完成情况汇总表","科研项目计划完成情况汇总表","设备大修项目计划完成情况汇总表","预备费使用情况汇总表"};
        String[] keyStrs = {
        	"PROJECT_NAME,PROJECT_TYPE,SCALE,PLAN_QUANTITIES,PLAN_COST,ACTUAL_QUANTITIES,ACTUAL_COST,PERCENT,REMARKS"
        	,"PROJECT_NAME,PROJECT_TYPE,SCALE,PLAN_QUANTITIES,PLAN_COST,ACTUAL_QUANTITIES,ACTUAL_COST,PERCENT,REMARKS"
        	,"SPECIAL_PROJECT_NAME,PLAN_COST,ACTUAL_COST,PERCENT,COMPLETE_DESCRIBE,REMARKS"
            ,"PROJECT_NAME,PROJECT_TYPE,SCALE,PLAN_QUANTITIES,PLAN_COST,ACTUAL_QUANTITIES,ACTUAL_COST,PERCENT,REMARKS"
            ,"PROJECT_NAME,PROJECT_TYPE,PLAN_COST,ACTUAL_COST,PERCENT,COMPLETE_DESCRIBE,REMARKS"
            ,"PROJECT_NAME,PROJECT_TYPE,PLAN_COST,ACTUAL_COST,PERCENT,COMPLETE_DESCRIBE,REMARKS"
            ,"PROJECT_NAME,PROJECT_TYPE,PLAN_COST,ACTUAL_COST,PERCENT,COMPLETE_DESCRIBE,REMARKS"
            ,"PROJECT_NAME,ACTUAL_COST,COMPLETE_DESCRIBE,REMARKS"
            
        };
        String[] tableNames = {
        		"highway_prevention_plan_sumtable_view","highway_repair_comp_sumtable_view",
        		"highway_special_comp_sumtable_view","highway_emergency_comp_sumtable_view","highway_test_comp_sumtable_view",
        		"highway_scientific_comp_sumtable_view","highway_equipment_overhaul_comp_sumtable_view","highway_reserve_fees_sumtable_view"};
        int[] startRowIndexs = {4,4,3,4,3,3,3,4};
        int[] tabletype = {6,6,6,6,6,6,6,7};
        int[] totalRange = {6,6,3,6,4,4,4,4};
        String[][][] funcArrys = {
        		{
                    {"null","ACTUAL_QUANTITIES","\"\""}
                    ,{"percent","PERCENT","IF(ISERROR((J#row#/H#row#)*100),\"\",(J#row#/H#row#)*100)"}
                    ,{"null","REMARKS","\"\""}
                },
        		
        		{
                    {"null","ACTUAL_QUANTITIES","\"\""}
                    ,{"percent","PERCENT","IF(ISERROR((J#row#/H#row#)*100),\"\",(J#row#/H#row#)*100)"}
                    ,{"null","REMARKS","\"\""}
                },
        		{
                    {"percent","PERCENT","IF(ISERROR((F#row#/E#row#)*100),\"\",(F#row#/E#row#)*100)"}
                    ,{"null","COMPLETE_DESCRIBE","\"\""}
                    ,{"null","REMARKS","\"\""}
                },
        		
	            {
	                {"null","ACTUAL_QUANTITIES","\"\""}
	                ,{"percent","PERCENT","IF(ISERROR((J#row#/H#row#)*100),\"\",(J#row#/H#row#)*100)"}
	                ,{"null","REMARKS","\"\""}
	            },
	            {
	                {"percent","PERCENT","IF(ISERROR((G#row#/F#row#)*100),\"\",(G#row#/F#row#)*100)"}
	                ,{"null","COMPLETE_DESCRIBE","\"\""}
	                ,{"null","REMARKS","\"\""}
	            },
	            
	            {
	                {"percent","PERCENT","IF(ISERROR((G#row#/F#row#)*100),\"\",(G#row#/F#row#)*100)"}
	                ,{"null","COMPLETE_DESCRIBE","\"\""}
	                ,{"null","REMARKS","\"\""}
	            },
	            {
	                {"percent","PERCENT","IF(ISERROR((G#row#/F#row#)*100),\"\",(G#row#/F#row#)*100)"}
	                ,{"null","COMPLETE_DESCRIBE","\"\""}
	                ,{"null","REMARKS","\"\""}
	            },{
	                {"null","COMPLETE_DESCRIBE","\"\""}
	                ,{"null","REMARKS","\"\""}
	            }
        };
        PageData pd = new PageData();
        for (int i = 0; i < tableNames.length; i++) {
            PageData data = new PageData();
            pd.put("type", tableNames[i]);
            pd.put("YEAR", param.get("YEAR"));
            pd.put("table", "oa_department_"+param.get("YEAR"));
            List<PageData> list = annualPlanCompService.getProjectData(pd);
            data.put("list", list);
            data.put("modelName", modelNames[i]);
            data.put("keyStr", keyStrs[i]);
            data.put("startRowIndex", startRowIndexs[i]);
            data.put("tabletype", tabletype[i]);
            data.put("funcArry", funcArrys[i]);
            data.put("totalRange", totalRange[i]);
            data.put("totalCost", "");
            if (tabletype[i]==7) {
                String totalCost = annualPlanCompService.getFeesTotalCost(Integer.parseInt(param.getString("YEAR"))-1+"");
                data.put("totalCost", totalCost);
            }
            data.put("pd", pd);
            result.add(data);
        }
        return result;
    }
    
    /**
     * 格式化金额
     * @param cost_num
     * @return
     */
    public double formatNum(double cost_num){
    	String planstr = "";
    	if(cost_num < 10){
    		planstr = String.format("%.1f", cost_num);
    	}
    	else{
    		planstr = String.format("%.0f", cost_num); 
    	}
    	cost_num = Double.parseDouble(planstr);
     return cost_num;
    }
}