gxwss/src/java/com/bus/web/BusInitController.java

package com.bus.web;

import java.awt.image.BufferedImage;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.io.UnsupportedEncodingException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
//import java.time.Year;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

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

import com.base.service.IBaseOrgTypeService;
import com.bus.model.*;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import org.jbarcode.util.ImageUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import com.StaticValues;
import com.base.model.BaseOrgType;
import com.base.model.BaseSection;
import com.base.model.BaseStatus;
import com.base.model.enumType.UserType;
import com.base.service.IBaseStatusService;
import com.bus.model.temp.BusOrgTemp;
import com.bus.model.temp.BusPlanTempRowMap;
import com.bus.model.temp.OrgTemp;
import com.bus.model.temp.OrgTypeCountTemp;
import com.bus.model.temp.ReplyPrintTemp;
import com.bus.model.temp.unitCountYearRowMap;
import com.bus.service.IBusAnnualPlanForWaterService;
import com.bus.service.IBusCustomerMeterService;
import com.bus.service.IBusMeterReadInfoService;
import com.bus.service.IBusOrgService;
import com.bus.service.IBusQuarterlyAssessmentService;
import com.bus.service.IBusWaterAdjRecService;
import com.core.dao.util.Updater;
import com.core.util.FileUploadUtils;
import com.core.util.JsonUtil;
import com.core.web.BaseController;
import com.core.web.security.ContextUtil;
import com.data.model.ImportDataFile;
import com.data.model.enumType.ImportStatus;
import com.data.model.enumType.ImportType;
import com.data.web.ImportDataFileController;
import com.popedom.model.PopUser;


import java.awt.image.BufferedImage;  
import java.io.FileOutputStream;  
import org.jbarcode.JBarcode;
import org.jbarcode.encode.Code128Encoder;
import org.jbarcode.encode.Code39Encoder;  
import org.jbarcode.encode.EAN13Encoder;
import org.jbarcode.encode.InvalidAtributeException;
import org.jbarcode.paint.BaseLineTextPainter;  
import org.jbarcode.paint.EAN13TextPainter;  
import org.jbarcode.paint.WideRatioCodedPainter;  
import org.jbarcode.paint.WidthCodedPainter;  
import org.jbarcode.util.ImageUtil;  
  

@Controller  
@RequestMapping("/busInit/")
public class BusInitController extends BaseController {
	private static Logger log = LoggerFactory
			.getLogger(BusInitController.class);
	@Autowired
	private IBusOrgService busOrgService;

    @Autowired
    private IBaseOrgTypeService baseOrgTypeService;

	@Autowired
	private IBusMeterReadInfoService busMeterReadInfoService;

	@Autowired
	private IBusAnnualPlanForWaterService busAnnualPlanForWaterService;

	@Autowired
	private IBusQuarterlyAssessmentService busQuarterlyAssessmentService;
	
	@Autowired
	private IBusCustomerMeterService busCustomerMeterService;
	
	@Autowired
	private IBaseStatusService busStatusService;
	
	@Autowired
	private IBusWaterAdjRecService busWaterAdjRecService;
	
	private static String path;	
	static {
	    Properties p = new Properties();
	    try {
	        p.load(ImportDataFileController.class.getClassLoader().getResourceAsStream(
	                "config.properties"));
	        path = p.getProperty("upload_mdb");
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	 
	}
	/**
 * 进入年度初值化界面
 * 
 * @return
 */
@RequestMapping("busInitList.do")
public String busInitList(Model model) {
	String year = busOrgService.getMatYearFromBusOrg();
	Map<String,List<Map<String,String>>> map = new HashMap<String,List<Map<String,String>>>();
	List<Map<String, String>> m = UserType.getList();
	map.put("list", m);
	model.addAttribute("year", year);
	model.addAttribute("isPlanUser", StaticValues.IS_PLAN);
	model.addAttribute("map", JsonUtil.getJSONSerializer().deepSerialize(map));
	return "bus/org-init-list";
}

/**
 * 获取要年度初化的信息列表
 * 
 * @return
 */
@RequestMapping("bugInitInfoList.do")
@ResponseBody
public String bugInitInfoList(int pageNo, int pageSize, String orgNumber,
		String orgName, String year, String busOrgCustomerNumber,
		String address, String busOrgMeterNumber, String isPlanUser,
		String userType, String orgNature, HttpServletRequest request) {

	String hql = "select b from BusOrg b where b.importStatus='normal'";
	if (null != orgNumber && !orgNumber.equals("")) {
		hql += " and b.orgNumber = "+ orgNumber;
	}
	if (null != orgName && !orgName.equals("")) {
		hql += " and b.orgName like '%" + orgName + "%'";
	}
	if (null != year && !year.equals("")) {
		hql += " and b.year like '%" + year + "%'";
	}
	if (null != busOrgCustomerNumber && !busOrgCustomerNumber.equals("")) {
		hql += " and b.orgId in(select bcm.org.orgId from BusCustomerMeter bcm where bcm.customerNumber="+busOrgCustomerNumber+")";
	}
	if (null != address && !address.equals("")) {
		hql += " and b.address like '%" + address + "%'";
	}
	if (null != busOrgMeterNumber && !busOrgMeterNumber.equals("")) {
		hql += " and b.orgId in(select bc.org.orgId from BusCustomerMeter bc where bc.meterNumber="+busOrgMeterNumber+")";
	}
	if (null != userType && !userType.equals("")) {
		hql += " and b.baseUserType = " + userType;
	}
	if (null != isPlanUser && !isPlanUser.equals("")) {
		hql += " and b.isPlanUser ='" + isPlanUser + "'";
	}
	if (null != orgNature && !orgNature.equals("")) {
		hql += " and b.orgNature like '%" + orgNature + "%'";
	}
	this.page = busOrgService.getPageByHql(hql, pageNo, pageSize);
	String result = this.getPageJosnResult();

	log.debug("#########################" + result);
	return result;
}

/**
 * 进入年度初值化时间选择区间界面
 * 
 * @param orgIds
 * @param model
 * @return
 */
@RequestMapping("gotoInitYearTimePage.do")
public String gotoInitYearTimePage(String orgIds,String year, Model model) {
	model.addAttribute("ids", orgIds);
	if(!"".equals(year)&&year!=null){
		model.addAttribute("year", year);
	}
	return "bus/org-init-create";
}

/**
 * 年度计划初值化
 * 
 * @param ids
 * @return
 * @throws ParseException 
 */
@RequestMapping("createYearWaterInfo.do")
@ResponseBody
public Object createYearWaterInfo(String ids, String startTime,
		String endTime,int initValue,String param) throws ParseException {
	startTime += "-01 00:00:00";
	endTime += "-01 00:00:00";
	String year = getCurrentYear();
	SimpleDateFormat f = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    try {
		if (ids.length() <= 0) {// 没有选择则所有生成年度计划初值化
			String[] strs1 = ids.split(",");
			for (String str : strs1) {
				Long orgId1 = Long.parseLong(str);
				BusOrg borg1 = busOrgService.get(orgId1);
				String name = borg1.getOrgNature();
				//取增长比例
				Float GR = baseOrgTypeService.getGrowthRatio(name);
				doInityearDate(f.parse(startTime), f.parse(endTime),year,GR);
			}
		} else {
			String[] strs = ids.split(",");
			for (String str : strs) {
				Long orgId = Long.parseLong(str);
				BusOrg busOrg = busOrgService.get(orgId);
				int orgNumber = busOrg.getOrgNumber();
				String y = busOrg.getYear();
				if(!y.equals(year)){ //不是新年的数据不初值化
					continue;
				}
				//批复过的不再初值化
				BusAnnualPlanForWater water = busAnnualPlanForWaterService.get(orgId);
				if(water != null){
					continue;
				}
				doDataInit(orgNumber,orgId, f.parse(startTime), f.parse(endTime));
			}
		}
		busQuarterlyAssessmentService.updateWaterPrice(param);
		return addJsonSuccessMsg("年度计划初值化成功");
	} catch (Exception e) {
		return addJsonErrorMsg("年度计划初值化失败,原因是:"+e.getMessage());
	}

}

/**
 * 循环实现年度初值化
 * 
 * @param startTime
 * @param endTime
 * @param year
 */
public void doInityearDate(Date startTime, Date endTime,String year,Float GR) {
	 PopUser user = ContextUtil.getCurrentUser();
	 String userName = user.getFullName();
	 Long userId = user.getUserId();
	 Date updateTime = new Date();
	 // 年度计划初值化
	 busOrgService.updateCountYearInitDate(userId,updateTime,userName,year,startTime,endTime,startTime,getAscOneMonth(startTime),getDescOneMonth(endTime),endTime,GR);
	 //季度考核初值化
	 for(int i=1;i<5;i++){
		 busOrgService.updateInitbusQuarterlyAssessment(i, year, startTime, endTime, startTime, getAscOneMonth(startTime), getDescOneMonth(endTime), endTime,GR);
	 }

	 //月考核初值化
	 //LHJ 2020-1-11
	 for(int i=1;i<13;i++){
		 busOrgService.updateInitbusMonthAssessment(i, year, startTime, endTime, startTime, getAscOneMonth(startTime), getDescOneMonth(endTime), endTime,GR);
	 }

	 //半年考核初值化
	 //LHJ 2020-1-11
	 for(int i=1;i<3;i++){
		 busOrgService.updateInitbusHalfYearAssessment(i, year, startTime, endTime, startTime, getAscOneMonth(startTime), getDescOneMonth(endTime), endTime,GR);
	 }

}

/**
 * 初值化过程计算
 * 
 * @param orgId
 * @param startTime
 * @param endTime
 */
private void doDataInit(int orgNumber,Long orgId, Date startTime, Date endTime) {
	
	//客户提出在年初始化的时候不再判断首月跟尾月是否有水，李周芳	2015-03-26
	// 不够12个月不做初值化，只判断首月跟尾月
//	List<BusMeterReadInfo> listS = busMeterReadInfoService.getStartOneMonthMeterInfo(orgNumber, startTime,getAscOneMonth(startTime));
//	if (null == listS) {
//		return;
//	}
//	List<BusMeterReadInfo> listE = busMeterReadInfoService.getStartOneMonthMeterInfo(orgNumber, getDescOneMonth(endTime),endTime);
//	if (null == listE) {
//		return;
//	}

	BusOrg borg1 = busOrgService.get(orgId);
	String name = borg1.getOrgNature();
	//取增长比例
	Float GR = baseOrgTypeService.getGrowthRatio(name);

	// 找到12个月区间总水量
	List<BusMeterReadInfo> listM = busMeterReadInfoService.getStartOneMonthMeterInfo(orgNumber, startTime, endTime);
	Float w = 0f;
	for (int i = 0; i < listM.size(); i++) {
        BusMeterReadInfo info = listM.get(i);
        w += info.getWater();
    }

	// 计划水量加上5%
	int planWater = (int) Math.round((1+(GR)/100) * w);
	int fs =Math.round(Float.valueOf(planWater) / 4);

	//月计划用水指标
	int ms = Math.round(Float.valueOf(planWater) / 12);

	//半年计划用水指标
	int hys = Math.round(Float.valueOf(planWater) / 2);
    
	//初值化年度计划表
	BusAnnualPlanForWater busAnnualPlanForWater = busAnnualPlanForWaterService.get(orgId);
	if (null == busAnnualPlanForWater) {
		BusOrg borg = busOrgService.get(orgId);
		BusAnnualPlanForWater busWater = new BusAnnualPlanForWater();
		busWater.setPriYearUseWater(w);
		busWater.setPlanOfWater((float)planWater);
		busWater.setReplyOfWater((float)planWater);
		busWater.setFirstQuarterPlan((float)fs);
		busWater.setFirstQuarterReply((float)fs);
		busWater.setSecondQuarterPlan((float)fs);
		busWater.setSecondQuarterReply((float)fs);
		busWater.setThirdQuarterPlan((float)fs);
		busWater.setThirdQuarterReply((float)fs);
		busWater.setFourthQuarterPlan((float)fs);
		busWater.setFourthQuarterReply((float)fs);
		busWater.setOperatorId(ContextUtil.getCurrentUserId());
		busWater.setUserName(ContextUtil.getCurrentUser().getFullName());
		busWater.setFillTime(new Date());
		busWater.setIsCancel(StaticValues.IS_PLAN); //默认考核yes
		busWater.setOrg(borg);
		busAnnualPlanForWaterService.save(busWater);
		
		//季度考核初值化
		for(int i=1;i<5;i++){	
			BusQuarterlyAssessment bqa = new BusQuarterlyAssessment();
			bqa.setAssessmentQuarter(i);
			bqa.setWaterPlanningIndex((float)fs);
			bqa.setOrg(borg);
			bqa.setBank(borg.getBank());
			bqa.setBankAccount(borg.getBankAccount());
			busQuarterlyAssessmentService.save(bqa);
		}

		//月考核初值化
		for(int i=1;i<13;i++) {
			BusMonthAssessment bma = new BusMonthAssessment();
			bma.setAssessmentMonth(i);
			bma.setWaterPlanningIndex((float)ms);
			bma.setOrg(borg);
			bma.setBank(borg.getBank());
			bma.setBankAccount(borg.getBankAccount());
		}
		//半年考核初值化
		for(int i=1;i<3;i++) {
			BusYearhfAssessment bya = new BusYearhfAssessment();
			bya.setAssessmentYearhf(i);
			bya.setWaterPlanningIndex((float)hys);
			bya.setOrg(borg);
			bya.setBank(borg.getBank());
			bya.setBankAccount(borg.getBankAccount());
		}
		//年考核初值化

	}
	
}

/**
 * 进入年度用户信息初值化界面
 * 
 * @return
 */
@RequestMapping("userInitInfoList.do")
public String userInitInfoList(Model model) {
	SimpleDateFormat format = new SimpleDateFormat("yyyy");
	Date d = new Date();
	Map<String,List<Map<String,String>>> map = new HashMap<String,List<Map<String,String>>>();
	List<Map<String, String>> m = UserType.getList();
	map.put("list", m);
	model.addAttribute("year", format.format(d));
	model.addAttribute("userType",JsonUtil.getJSONSerializer().deepSerialize(map));
	return "bus/user-init-list";
}

/**
 * 获取用户年度初值化列表
 * 
 * @return
 */
@RequestMapping("busUserInfoList.do")
@ResponseBody
public String busUserInfoList(int pageNo, int pageSize, String orgNumber,
		String orgName, String year, String busOrgCustomerNumber,
		String address, String busOrgMeterNumber, String isPlanUser,
		String userType, String orgNature, HttpServletRequest request) {
			String hql="select b from BusOrg b where b.importStatus='normal'";	
			if(null != busOrgCustomerNumber && !busOrgCustomerNumber.equals("")){
				hql +=" and b.orgId in(select bcm.org.orgId from BusCustomerMeter bcm where bcm.customerNumber='"+busOrgCustomerNumber+"')";
			}
			if(null != busOrgMeterNumber && !busOrgMeterNumber.equals("")){
				hql +=" and b.orgId in(select bc.org.orgId from BusCustomerMeter bc where bc.meterNumber='"+busOrgMeterNumber+"')";
			}
			if (null != orgNumber && !orgNumber.equals("")) {
				hql += " and b.orgNumber = " + orgNumber;
			}
			if (null != orgName && !orgName.equals("")) {
				hql += " and b.orgName like '%" + orgName + "%'";
			}
			if (null != year && !year.equals("")) {
				hql += " and b.year = '" + year + "'";
			}
			if (null != address && !address.equals("")) {
				hql += " and b.address like '%" + address + "%'";
			}
			if (null != userType && !userType.equals("")) {
				hql += " and b.baseUserType =" + userType;
			}
			if (null != isPlanUser && !isPlanUser.equals("")) {
				hql += " and b.isPlanUser ='" + isPlanUser+"'";
			}
			if (null != orgNature && !orgNature.equals("")) {
				hql += " and b.orgNature like '%" + orgNature + "%'";
			}
			this.page = busOrgService.getPageByHql(hql, pageNo, pageSize);
			String result = this.getPageJosnResult();
			log.debug("#########################result" + result);
			return result;

}

/**
 * 进入生成新年度用户基本信息库界面
 * 
 * @return
 */
@RequestMapping("createUserInit.do")
public String createBusUserInitInfo(String ids,Model model,String year) {
    model.addAttribute("ids", ids);
    model.addAttribute("year", year);
	return "bus/user-init-create";
}

/**
 * 初值化用户基本信息操作
 * 
 * @param endTime
 * @param startTime
 * @return
 */
@RequestMapping("editbusUserInitInfo.do")
@ResponseBody
public Object editbusUserInitInfo(String endTime, String startTime,String ids,int initValue,String year) {
	startTime += "-01 00:00:00";
	endTime += "-01 00:00:00";
	int sum=0;
	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	try {
		Date startT = format.parse(startTime);
		Date endT = format.parse(endTime);
		if(ids.length()>0){
			String[] strs = ids.split(",");
			for(String s:strs){
				Long orgId = Long.parseLong(s);
				BusOrg busOrg = busOrgService.get(orgId);

				String name = busOrg.getOrgNature();
				//取增长比例
				Float GR = baseOrgTypeService.getGrowthRatio(name);

				//查询水量历史记录要用到单位编号
				int orgNumber = busOrg.getOrgNumber();
				if(!busOrg.getYear().equals(year)){ //只初值化最新数据，如果是去年的就不初值化了
					continue;
				}
				//批复过的单位不能再初值化
//				BusAnnualPlanForWater water = busAnnualPlanForWaterService.get(orgId);
//				if(water != null ){
//					continue;
//				}
				initStepBusOrg(year,orgNumber,orgId,startT,endT,GR);
			}
		}else{//没有选择默认初值化所有
			String a = format.format(getAscOneMonth(startT));
			String b = format.format(getDescOneMonth(endT));
			log.debug("%%%%%%%%%%%%%%%%%%%%%"+a+"   "+b);
			 busOrgService.updateYearneedInitDate(year, startT, endT, startT, getAscOneMonth(startT), getDescOneMonth(endT), endT);
			 busOrgService.updateIsplanYear(year);
			 busOrgService.updateCosMeter(year);

			String[] strs = ids.split(",");
			for (String str : strs) {
				Long orgId1 = Long.parseLong(str);
				BusOrg borg1 = busOrgService.get(orgId1);
				String name = borg1.getOrgNature();
				//取增长比例
				Float GR = baseOrgTypeService.getGrowthRatio(name);

				busOrgService.updateSetplanYear(year, startT, endT, startT, getAscOneMonth(startT), getDescOneMonth(endT), endT, GR);
			}
		}
		
		return addJsonSuccessMsg("用户信息初值化成功");
	} catch (ParseException e) {
		return addJsonErrorMsg("用户初值化信息失败,原因是：" + e.getMessage());
	}
}

/**
 * 单位中弹出处理合并后用户初始化页面
 * @param ids
 * @param model
 * @param year
 * @return
 */
//@RequestMapping("createBusUserInitForOrg.do")
//public String createBusUserInitForOrg(String ids,Model model,String year) {
//    model.addAttribute("ids", ids);
//    model.addAttribute("year", year);
//	return "bus/user-init-create-for-org";
//}

/**
 * 单位中处理合并后用户的初始化
 * @param endTime
 * @param startTime
 * @param ids
 * @param initValue
 * @param year
 * @return
 */
//@RequestMapping("editBusUserInitInfoForOrg")
//@ResponseBody
//public Object editBusUserInitInfoForOrg(String endTime, String startTime,Long orgId,int initValue,String year) {
//	startTime += "-01 00:00:00";
//	endTime += "-01 00:00:00";
//	SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//	try {
//		Date startT = format.parse(startTime);
//		Date endT = format.parse(endTime);
//		BusOrg busOrg = busOrgService.get(orgId);
//		//查询水量历史记录要用到单位编号
//		int orgNumber = busOrg.getOrgNumber();
//		initStepBusOrg(year,orgNumber,orgId,startT,endT,initValue);	
//		//查找初始化之后的单位是否为计划户
//		int nextyear = Integer.parseInt(year)+1;
//		String hql2 = " from BusOrg b where b.orgNumber=? and b.importStatus='normal' and b.year=?";
//		List<BusOrg> list1 = busOrgService.findByHql(hql2, orgNumber,(nextyear+"").trim());
//		BusOrg bo = list1.get(0);
//		Map<String, Object> map = new Hashtable<String,Object>();
//		map.put("status", "y");//成功标识
//		map.put("info", "用户信息初值化成功");//消息内容
//		if("yes".equals(bo.getIsPlanUser())){
//			map.put("isPlanUser", "y");
//			map.put("orgIds", bo.getOrgId());
//			map.put("year", bo.getYear());
//		}else{
//			map.put("isPlanUser", "n");
//		}
//		return map;
//	} catch (ParseException e) {
//		return addJsonErrorMsg("用户初值化信息失败,原因是：" + e.getMessage());
//	}
//}

/**
 * 用户初值化操作
 * @param orgId
 * @param startTime
 * @param endTime
 */
private void initStepBusOrg(String year,int orgNumber,Long orgId,Date startTime,Date endTime,Float GR){
	int nextyear = Integer.parseInt(year)+1;
	
	//先初值化先	
	String hql = " from BusOrg b where b.orgNumber=? and b.importStatus='normal' and b.year=?";
	List<BusOrg> list = busOrgService.findByHql(hql, orgNumber,(nextyear+"").trim());
	if(null == list || list.size()==0){
		busOrgService.initOneData(orgId, nextyear,StaticValues.NO_PLAN);
	}else{ 		
		BusOrg bo = list.get(0);
		String h = " from BusAnnualPlanForWater w where w.id=?";
		List<BusAnnualPlanForWater> bw = busAnnualPlanForWaterService.findByHql(h, bo.getOrgId());
		if(null == bw || bw.size() == 0){
			bo.setIsPlanUser(StaticValues.NO_PLAN);
			Updater<BusOrg> updater = new Updater<BusOrg>(bo);
			busOrgService.updateByUpdater(updater);
		}
	}
	
	//客户提出在年初始化的时候不再判断首月跟尾月是否有水，李周芳	2015-03-26
	// 先判断是否12个月都有水,只判断首月跟尾月
//	List<BusMeterReadInfo> listS = busMeterReadInfoService.getStartOneMonthMeterInfo(orgNumber, startTime,getAscOneMonth(startTime));
//	if (null == listS) {
//		return;
//	}
//	List<BusMeterReadInfo> listE = busMeterReadInfoService.getStartOneMonthMeterInfo(orgNumber, getDescOneMonth(endTime),endTime);
//	if (null == listE) {
//		return;
//	}
	
	// 计算水量
	Float w = busMeterReadInfoService.getSumWaterByTime(orgNumber, startTime, endTime);

	// 根据水量判断是否计划户,超过12万吨为计划户
	if (w >= GR) {
		String hql2 = " from BusOrg b where b.orgNumber=? and b.importStatus='normal' and b.year=?";
		List<BusOrg> list1 = busOrgService.findByHql(hql2, orgNumber,(nextyear+"").trim());
		BusOrg bo = list1.get(0);
		bo.setIsPlanUser(StaticValues.IS_PLAN);
		Updater<BusOrg> updater = new Updater<BusOrg>(bo);
		busOrgService.updateByUpdater(updater);
	
	}
}
/**
 * 得到年份
 * 
 * @return
 */
private String getCurrentYear() {
	String year = busOrgService.getMatYearFromBusOrg();
	return year;
}

/**
 * 时间减一个月
 * 
 * @param endTime
 * @return
 */
private Date getDescOneMonth(Date endTime) {
	Calendar calendar = Calendar.getInstance();
	calendar.setTime(endTime);
	calendar.add(Calendar.MONTH, -1);
	return calendar.getTime();
}

/**
 * 时间加一个月
 * 
 * @param startTime
 * @return
 */
	private Date getAscOneMonth(Date startTime) {
		Calendar calendar = Calendar.getInstance();
		calendar.setTime(startTime);
		calendar.add(Calendar.MONTH, 1);
		return calendar.getTime();
	}
	
/**
 * 进入单位年度计划供水表列表界面
 * @return
 */
@RequestMapping("unitYearPlanWater.do")
public String unitYearPlanWater(Model model){
	Map<String,List<Map<String,String>>> map = new HashMap<String,List<Map<String,String>>>();
	List<Map<String, String>> m = UserType.getList();
	String year = getCurrentYear();
	map.put("list", m);
	model.addAttribute("year", year);
	model.addAttribute("map", JsonUtil.getJSONSerializer().deepSerialize(map));
	//载入状态颜色
	Map<String, List<Map<String, String>>> statusMap = new HashMap<String, List<Map<String, String>>>();
	statusMap.put("list", loadStatusSelectWithColor());
	String statusList = JsonUtil.getJSONSerializer().deepSerialize(statusMap);
	model.addAttribute("statusList", statusList);
	//载入状态名称
	Map<String, List<Map<String, String>>> nameMap = new HashMap<String, List<Map<String, String>>>();
	nameMap.put("list", loadStatusSelectWithName());
	String nameList = JsonUtil.getJSONSerializer().deepSerialize(nameMap);
	model.addAttribute("nameList", nameList);
	return "bus/org-plan-list";
}

/**
 * 列出单位年度计划供水表信息
 * @return
 * @throws ParseException 
 */
@RequestMapping("getYearPlanData.do")
@ResponseBody
public String getYearPlanData(int pageNo,int pageSize,String orgNumber,
		String orgName, String year, String busOrgCustomerNumber,
		String address, String busOrgMeterNumber, String priYearUseWater,
		String userType, String orgNature,String status,String org_name) throws ParseException{
	    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    //String sql = "select p.status_id,o.org_id,o.org_number,o.org_name,o.user_type,o.year,p.plan_of_water,p.replay_of_water,p.first_quarter_plan,p.second_quarter_plan,p.third_quarter_plan,p.fourth_quarter_plan,p.user_name,p.fill_time,p.remark,p.pri_year_usewater FROM `bus_annual_plan_for_water` as p left join bus_org o on p.org_id=o.org_id where p.is_cancel='yes'";
	    String sql = "select p.status_id,o.org_id,o.org_number,o.org_name,o.user_type,o.year,p.plan_of_water,p.reply_of_water,p.first_quarter_reply,p.second_quarter_reply,p.third_quarter_reply,p.fourth_quarter_reply,p.user_name,p.fill_time,p.remark,p.pri_year_usewater ";
	    sql +=",p.first_quarter_plan,p.second_quarter_plan,p.third_quarter_plan,p.fourth_quarter_plan,p.reply_of_increase FROM `bus_annual_plan_for_water` as p left join bus_org o on p.org_id=o.org_id where p.is_cancel='yes' ";
	    if(null != busOrgCustomerNumber && !busOrgCustomerNumber.equals("")){
			   sql +=" and o.org_id in (select bcm.org_id from bus_customer_meter as bcm where bcm.customer_Number = "+busOrgCustomerNumber+")";
		}
		if(null != busOrgMeterNumber && !busOrgMeterNumber.equals("")){
			   sql +=" and o.org_id in (select bc.org_id from bus_customer_meter as bc where bc.meter_Number = "+busOrgMeterNumber+")";
		}
		if (null != orgNumber && !orgNumber.equals("")) {
			   sql +=" and o.org_number="+orgNumber;
		}
		if (null != org_name && !org_name.equals("")) {
			   sql +=" and LENGTH(o.org_name)<"+Integer.parseInt(org_name)+"";
		}		
		
		
		if (null != orgName && !orgName.equals("")) {
			   sql +=" and o.org_name like '%"+orgName+"%'";
		}
		if (null != orgNature && !orgNature.equals("")) {
			sql += " and b.orgNature like '%" + orgNature + "%'";
		}
		if (null != year && !year.equals("")) {
			   sql +=" and o.year='"+year+"'";
		}
		if (null != address && !address.equals("")) {
			   sql +=" and o.address like '%"+address+"%'";
		}
		if (null != userType && !userType.equals("")) {
			   sql +=" and o.user_type="+userType;
		}
		if (null != status && !status.equals("")) {
			   sql +=" and p.status_id="+status;
		}
		if (null != priYearUseWater && !priYearUseWater.equals("")) {
			 try {
				  Float p = Float.parseFloat(priYearUseWater);
				  sql +=" and p.pri_year_usewater>='"+p+"'";
			} catch (Exception e) {
				  log.debug("上一年用水量转化异常："+e.getMessage());
			}			  			
		}
		sql +=" order by o.year,o.org_number desc";
		this.page = busAnnualPlanForWaterService.getPageByJdbcTemplate(sql, pageNo, pageSize, new BusPlanTempRowMap());
		List<BusOrgTemp> list = (List<BusOrgTemp>)this.page.getResult();
		for(BusOrgTemp temp:list){
	    	int o = temp.getOrgNumber();
	    	int y = Integer.parseInt(temp.getYear());
	    	String s = y+"-01-01 00:00:00";
	    	String e = y+1+"-01-01 00:00:00";
	    	List<BusMeterReadInfo> listS = busMeterReadInfoService.getStartOneMonthMeterInfo(o, format.parse(s),format.parse(e));
	    	Float w=0f;
	    	if(null != listS){
	    		for(BusMeterReadInfo info:listS){
		    		w +=info.getWater();
		    	}
	    	}	    	
	    	temp.setYearUseWater(w);
	    }
		String result = this.getPageJosnResult();
		log.debug("####################################"+result);
		return result;
}

/**
 * 进入修改年度计划用水表界面
 * @param orgId
 * @param model
 * @return
 */
@RequestMapping("createYearUnitNotice.do")
public String createYearUnitNotice(Long orgId,Model model){
	BusOrg busOrg = busOrgService.get(orgId);	
	BusAnnualPlanForWater plan  = busAnnualPlanForWaterService.get(orgId);
	
	//BusWaterAdjRec adjplan1  = busWaterAdjRecService.findUniqueBy("orgId",orgId);
	//List<BusWaterAdjRec> adjplan=busWaterAdjRecService.findBy("orgId",orgId);
	
	String sql = "SELECT IFNULL(SUM(i.`water`),0) FROM `bus_meter_read_info` i WHERE i.`org_id`=?";
	Long useWate = busAnnualPlanForWaterService.getCountBySql(sql, orgId);
	Map<String,Collection<Map<String,String>>> map = new HashMap<String,Collection<Map<String,String>>>();
	map.put("list", loadStatusSelectWithName());
	String status = JsonUtil.getJSONSerializer().deepSerialize(map);
	model.addAttribute("status", status);
	if(plan.getBaseStatus()!=null){
		model.addAttribute("statusId", plan.getBaseStatus().getId());
	}
	model.addAttribute("busOrg", busOrg);
	model.addAttribute("plan", plan);
	model.addAttribute("useWate", useWate);
	//model.addAttribute("adjplan", adjplan);

	
	return "bus/org-plan-edit";
}

@RequestMapping("viewYearUnitNotice.do")
public String viewYearUnitNotice(Long orgId,Model model){
	BusOrg busOrg = busOrgService.get(orgId);	
	BusAnnualPlanForWater plan  = busAnnualPlanForWaterService.get(orgId);
	String sql = "SELECT IFNULL(SUM(i.`water`),0) FROM `bus_meter_read_info` i WHERE i.`org_id`=?";
	Long useWate = busAnnualPlanForWaterService.getCountBySql(sql, orgId);
	Map<String,Collection<Map<String,String>>> map = new HashMap<String,Collection<Map<String,String>>>();
	map.put("list", loadStatusSelectWithName());
	String status = JsonUtil.getJSONSerializer().deepSerialize(map);
	model.addAttribute("status", status);
	if(plan.getBaseStatus()!=null){
		model.addAttribute("statusId", plan.getBaseStatus().getId());
	}
	model.addAttribute("busOrg", busOrg);
	model.addAttribute("plan", plan);
	model.addAttribute("useWate", useWate);
	return "bus/org-plan-view-page";
}

private List<Map<String, String>> loadStatusSelectWithColor(){
	List<Map<String,String>> statusList = new ArrayList<Map<String,String>>();
	Map<String,String> defaultMap = new HashMap<String,String>();
	defaultMap.put("key", "请选择...");
	defaultMap.put("value", "");
	statusList.add(defaultMap );
	List<BaseStatus> allOrgType = busStatusService.getAll();
	for(BaseStatus bs:allOrgType){
		Map<String,String> map = new HashMap<String,String>();
		map.put("key", bs.getStatusColor());
		map.put("value", bs.getId().toString());
		statusList.add(map);
	}
	return statusList;
}

private List<Map<String, String>> loadStatusSelectWithName(){
	List<Map<String,String>> statusList = new ArrayList<Map<String,String>>();
	Map<String,String> defaultMap = new HashMap<String,String>();
	defaultMap.put("key", "请选择...");
	defaultMap.put("value", "");
	statusList.add(defaultMap );
	List<BaseStatus> allOrgType = busStatusService.getAll();
	for(BaseStatus bs:allOrgType){
		Map<String,String> map = new HashMap<String,String>();
		map.put("key", bs.getStatusName());
		map.put("value", bs.getId().toString());
		statusList.add(map);
	}
	return statusList;
}

/**
 * 修改通知
 * @param plan
 * @return
 */
@RequestMapping("editYearUnitNotice.do")
@ResponseBody
public Object editYearUnitNotice(BusAnnualPlanForWater plan,BusOrg busOrg,Long status){
	try {
		//修改年度计划表
		plan.setFillTime(new Date());
		plan.setOperatorId(ContextUtil.getCurrentUserId());
		plan.setUserName(ContextUtil.getCurrentUser().getFullName());
		if(status!=null){
			BaseStatus baseStatus = busStatusService.get(status);
			plan.setBaseStatus(baseStatus);
		}else{
			plan.setBaseStatus(null);
		}
		
		Long orgId = plan.getId();
		//取出原计划的数据，跟提交上来的数据做对比，只要修改过一个季度的 临时用水量，则更新到water_adj_rec表
		BusAnnualPlanForWater planwater  = busAnnualPlanForWaterService.get(orgId);
		if(planwater.getFirstQuarterAdjust()!=plan.getFirstQuarterAdjust()||planwater.getSecondQuarterAdjust()!=plan.getSecondQuarterAdjust()||planwater.getThirdQuarterAdjust()!=plan.getThirdQuarterAdjust()||planwater.getFourthQuarterAdjust()!=plan.getFourthQuarterAdjust())
		{			
			BusWaterAdjRec adjWater = new BusWaterAdjRec();
			adjWater.setOrgId(orgId);
			adjWater.setFirstQuarterAdjust(plan.getFirstQuarterAdjust());
			adjWater.setSecondQuarterAdjust(plan.getSecondQuarterAdjust());
			adjWater.setThirdQuarterAdjust(plan.getThirdQuarterAdjust());
			adjWater.setFourthQuarterAdjust(plan.getFourthQuarterAdjust());
			adjWater.setFillUser(ContextUtil.getCurrentUser().getFullName());
			adjWater.setFillTime(new Date());
			busWaterAdjRecService.save(adjWater);			
		}

			
		//客户提出在录入的时候好核对修改邮寄地址，李周芳	2015-03-26
		if(busOrg.getOrgId()!=null){
			Updater<BusOrg> updater1 = new Updater<BusOrg>(busOrg);
			busOrgService.updateByUpdater(updater1);
		}		
		
		//修改季度考核表
		
		String hql="select q from BusQuarterlyAssessment q where q.org.orgId=? order by q.assessmentQuarter";
		List<BusQuarterlyAssessment> list = busQuarterlyAssessmentService.findByHql(hql, orgId);
		for (int i = 0; i < list.size(); i++) {
			switch (i+1) {
			case 1:
				BusQuarterlyAssessment qa1 = list.get(i);
				if(plan.getFirstQuarterAdjust()!=null && plan.getFirstQuarterAdjust()!=0)
				{
					qa1.setWaterPlanningIndex(plan.getFirstQuarterAdjust());
				}
				else
				{
					qa1.setWaterPlanningIndex(plan.getFirstQuarterReply());
				}
				busQuarterlyAssessmentService.update(qa1);
				break;
			case 2:
			    BusQuarterlyAssessment qa2 = list.get(i);
			    if(plan.getSecondQuarterAdjust()!=null && plan.getSecondQuarterAdjust()!=0)
				{
			    	qa2.setWaterPlanningIndex(plan.getSecondQuarterAdjust());
				}
				else
				{
					qa2.setWaterPlanningIndex(plan.getSecondQuarterReply());
				}
			    busQuarterlyAssessmentService.update(qa2);
				break;
			case 3:
			    BusQuarterlyAssessment qa3 = list.get(i);
			    if(plan.getThirdQuarterAdjust()!=null && plan.getThirdQuarterAdjust()!=0)
				{
			    	qa3.setWaterPlanningIndex(plan.getThirdQuarterAdjust());
				}
				else
				{
					qa3.setWaterPlanningIndex(plan.getThirdQuarterReply());
				}
			    busQuarterlyAssessmentService.update(qa3);
				break;
			case 4:
			    BusQuarterlyAssessment qa4 = list.get(i);
			    if(plan.getFourthQuarterAdjust()!=null && plan.getFourthQuarterAdjust()!=0)
				{
			    	qa4.setWaterPlanningIndex(plan.getFourthQuarterAdjust());
				}
				else
				{
					qa4.setWaterPlanningIndex(plan.getFourthQuarterReply());
				}
			    busQuarterlyAssessmentService.update(qa4);
				break;
			default:
				break;
			}
		}
		if(plan.getFirstQuarterAdjust()!=null && plan.getFirstQuarterAdjust()!=0)
		{
			//qa1.setWaterPlanningIndex(plan.getFirstQuarterAdjust());
			plan.setFirstQuarterReply(plan.getFirstQuarterAdjust());
		}
	    if(plan.getSecondQuarterAdjust()!=null && plan.getSecondQuarterAdjust()!=0)
		{
	    	//qa2.setWaterPlanningIndex(plan.getSecondQuarterAdjust());
	    	plan.setSecondQuarterReply(plan.getSecondQuarterAdjust());
		}
	    if(plan.getThirdQuarterAdjust()!=null && plan.getThirdQuarterAdjust()!=0)
		{
	    	//qa3.setWaterPlanningIndex(plan.getThirdQuarterAdjust());
	    	plan.setThirdQuarterReply(plan.getThirdQuarterAdjust());
		}	
	    if(plan.getFourthQuarterAdjust()!=null && plan.getFourthQuarterAdjust()!=0)
		{
	    	//qa4.setWaterPlanningIndex(plan.getFourthQuarterAdjust());
	    	plan.setFourthQuarterReply(plan.getFourthQuarterAdjust());
		}	  
	    float flt = plan.getFirstQuarterReply()+plan.getSecondQuarterReply()+plan.getThirdQuarterReply()+plan.getFourthQuarterReply();
	    plan.setReplyOfWater(flt);
		Updater<BusAnnualPlanForWater> updater = new Updater<BusAnnualPlanForWater>(plan);
		busAnnualPlanForWaterService.updateByUpdater(updater);
		
		return addJsonSuccessMsg("修改通知成功");
	} catch (Exception e) {
		return addJsonErrorMsg("修改通知失败,原因是:"+e.getMessage());
	}
}

/**
 * 水表信息数据
 * @param request
 * @return
 */
@RequestMapping("AdjWaterList.do")
@ResponseBody
public String orgListPage(int pageNo, int pageSize,Long orgId, HttpServletRequest request) {
	this.page = busWaterAdjRecService.getAdjListPage(pageNo, pageSize,orgId);
	String result = this.getPageJosnResult();
	return result;
}
/**
 * 删除,即使就是取消考核
 * @param ids
 * @return
 */
@RequestMapping("YearUnitNoticeCancel.do")
@ResponseBody
public Object YearUnitNoticeCancel(Long[] ids){
	try {
		for(Long id:ids){
//			BusAnnualPlanForWater busAnnualPlanForWater = busAnnualPlanForWaterService.get(id);           
//			busAnnualPlanForWater.setIsCancel(StaticValues.NO_PLAN);
//			Updater<BusAnnualPlanForWater> updater = new Updater<BusAnnualPlanForWater>(busAnnualPlanForWater);
//			busAnnualPlanForWaterService.updateByUpdater(updater);
			
			//季度考核表如何处理取消考核,要删除掉
			String hql = "from BusQuarterlyAssessment b where b.org.orgId=?";
			List<BusQuarterlyAssessment> list = busQuarterlyAssessmentService.findByHql(hql, id);
			if(null != list && list.size()>0){
				for(BusQuarterlyAssessment b:list){
					busQuarterlyAssessmentService.remove(b);
				}
			}
			busAnnualPlanForWaterService.remove(id);
		}
		return addJsonSuccessMsg("操作成功");
	} catch (Exception e) {
		return addJsonErrorMsg("操作失败，原因是:"+e.getMessage());
	}
}

/**
 * 导出单位年度计划供水表
 * @param orgNumber
 * @param orgName
 * @param year
 * @param busOrgCustomerNumber
 * @param address
 * @param busOrgMeterNumber
 * @param priYearUseWater
 * @param userType
 * @throws UnsupportedEncodingException 
 */
@RequestMapping("unitWaterExportData.do")
public void unitWaterExportData(String orgNumber,String orgName,String orgNature,String year,String busOrgCustomerNumber,String address,
		String busOrgMeterNumber,String priYearUseWater,String userType,String status,HttpServletResponse response,HttpServletRequest request) throws UnsupportedEncodingException{
	   //request.setCharacterEncoding("UTF-8");
	   String hql="select w from BusAnnualPlanForWater w join w.org o where w.isCancel='yes'";
	    if(!orgNumber.equals("")){
	    	 hql +=" and o.orgNumber="+orgNumber;
	    }
	    if(!orgName.equals("")){
	    	
	    	 orgName = new String(orgName.getBytes("iso8859-1"),"UTF-8");
	    	 hql +=" and o.orgName like '%"+orgName+"%'";
	    }
		if(!orgNature.equals("")){

			orgNature = new String(orgNature.getBytes("iso8859-1"),"UTF-8");
			hql +=" and o.orgNature like '%"+orgNature+"%'";
		}
	    if(!year.equals("")){
	    	 hql +=" and o.year='"+year+"'";
	    }else{
	    	 year = getCurrentYear();
	    	 hql +=" and o.year='"+year+"'";
	    }
	    if(!busOrgCustomerNumber.equals("")){
	    	 hql +=" and o.orgId in (select bcm.org.orgId from BusCustomerMeter bcm where bcm.customerNumber="+busOrgCustomerNumber+")";
	    }
	    if(!address.equals("")){
	    	 address = new String(address.getBytes("iso8859-1"),"UTF-8");
	    	 hql +=" and o.address like '%"+address+"%'";
	    }
	    if(!busOrgMeterNumber.equals("")){
	    	 hql +=" and o.orgId in (select bc.org.orgId from BusCustomerMeter bc where bc.meterNumber="+busOrgMeterNumber+")";
	    }
	    if(!userType.equals("")){	    	 
	    	 hql +=" and o.baseUserType='"+userType+"'";
	    }
	    if(!priYearUseWater.equals("")){
	    	 Float p = Float.parseFloat(priYearUseWater);
	    	 hql +=" and w.priYearUseWater>="+p;
	    }
		if (null != status && !status.equals("")) {
			hql +=" and w.baseStatus="+status;
		}

		hql +=" order by o.orgId";
	    writeExcel(response,hql,year);
	  
}

private void writeExcel(HttpServletResponse response,String hql,String year){
	String excelName = year+"单位年度计划供水表.xls";
	response.setCharacterEncoding("UTF-8");
	response.setContentType("application/octet-stream");
	OutputStream os = null;
	WritableWorkbook book= null;
	WritableSheet sheet = null;
	try {
	    os = response.getOutputStream();
		String downName = new String(excelName.getBytes("gb2312"),"ISO8859-1");
		response.setHeader("content-disposition", "attachment;filename="+downName);
		//创建写入工作薄对象
		book = Workbook.createWorkbook(os);
		//创建一个sheet
		sheet = book.createSheet("sheet1", 0);
		//设置sheet每列宽度及第一行
		createJxlSheet(sheet);
		//往sheet里面添加内容
		fillExcel(sheet,hql,1,500,1);	
		book.write();//写入到文件中
	} catch (Exception e) {
		// TODO: handle exception
	}finally{
		try {
			if(null != book){
				book.close();
			}
			if(null != os){
				os.close();
			}
		} catch (Exception e2) {
			
		}
	}	
}

private void createJxlSheet(WritableSheet sheet) {
	try {
		// 设置列宽 arg0:列号,arg2宽
		sheet.setColumnView(0, 32);
		sheet.setColumnView(1, 32);
		sheet.setColumnView(2, 32);
		sheet.setColumnView(3, 12);
		sheet.setColumnView(4, 20);
		sheet.setColumnView(5, 5);
		sheet.setColumnView(6, 22);
		sheet.setColumnView(7, 12);
		sheet.setColumnView(8, 12);
		sheet.setColumnView(9, 12);
		sheet.setColumnView(10, 12);
		sheet.setColumnView(11, 12);
		sheet.setColumnView(12, 12);
		// 设置第一行 Label(列号,行号,内容)
		sheet.addCell(new Label(0, 0, "单位名称"));
		sheet.addCell(new Label(1, 0, "单位地址"));
		sheet.addCell(new Label(2, 0, "邮寄地址"));
		sheet.addCell(new Label(3, 0, "邮编"));
		sheet.addCell(new Label(4, 0, "水表户号"));
		sheet.addCell(new Label(5, 0, "年度"));
		sheet.addCell(new Label(6, 0, "上一年度实际用水量(m³)"));
		sheet.addCell(new Label(7, 0, "一季度计划"));
		sheet.addCell(new Label(8, 0, "二季度计划"));
		sheet.addCell(new Label(9, 0, "三季度计划"));
		sheet.addCell(new Label(10, 0, "四季度计划"));
		sheet.addCell(new Label(11, 0, "全年计划"));
		sheet.addCell(new Label(12, 0, "全年批复"));
		sheet.addCell(new Label(13, 0, "是否居民用水"));		
	} catch (Exception e) {
		// TODO: handle exception
	}

}

private void fillExcel(WritableSheet sheet,String hql,int pageNo,int pageSize,int row){
	try {
		this.page = busAnnualPlanForWaterService.getPageByHql(hql, pageNo, pageSize);
		
		//记录信息
		List<BusAnnualPlanForWater> list = (List<BusAnnualPlanForWater>)this.page.getResult();
		for(int i=0;i<list.size();i++){
			BusAnnualPlanForWater plan = list.get(i);
			sheet.addCell(new Label(0,row,plan.getOrgName()));//单位名称
			sheet.addCell(new Label(1,row,plan.getOrgAddress()));//单位地址
			sheet.addCell(new Label(2,row,plan.getEmailAddress()));//邮寄地址
			sheet.addCell(new Label(3,row,plan.getPostCode()));//邮编
			sheet.addCell(new Label(4,row,plan.getOrgMeterNumber()));//水表户号
			sheet.addCell(new Label(5,row,plan.getYear()));//年度
			sheet.addCell(new Label(6,row,Math.round(plan.getPriYearUseWater())+""));//上一年实际用水量
			sheet.addCell(new Label(7,row,Math.round(plan.getFirstQuarterReply())+""));//一季度
			sheet.addCell(new Label(8,row,Math.round(plan.getSecondQuarterReply())+""));//二季度
			sheet.addCell(new Label(9,row,Math.round(plan.getThirdQuarterReply())+""));//三季度
			sheet.addCell(new Label(10,row,Math.round(plan.getFourthQuarterReply())+""));//四季度
			sheet.addCell(new Label(11,row,Math.round(plan.getPlanOfWater())+""));//全年
			sheet.addCell(new Label(12,row,Math.round(plan.getReplyOfWater())+""));//全年
			sheet.addCell(new Label(13,row,plan.getOrgType()));//用户类型
			sheet.addCell(new Label(14,row,plan.getOrgNature()));//用水性质
			
			row+=1;			
		}
		if(this.page.hasNextPage()){
			fillExcel(sheet,hql,pageNo+1,pageSize,row);
		}
	} catch (Exception e) {
		// TODO: handle exception
	}	
}
/**
 * 打印年度计划通知
 * @param model
 * @param ids
 * @return
 */
@RequestMapping("seasonPlanPrint.do")
public String seasonPlanPrint(String year,Model model,Long[] ids, HttpServletRequest request){
		   if(year.equals("")){
			  year = getCurrentYear();
		    }
		   List<ReplyPrintTemp> list = new ArrayList<ReplyPrintTemp>();	
		   JBarcode localJBarcode = new JBarcode(EAN13Encoder.getInstance(), WidthCodedPainter.getInstance(), EAN13TextPainter.getInstance());  
		   if(ids.length>0){
			  for(Long orgId:ids){
				  BusOrg org = busOrgService.get(orgId);
				  String name = org.getOrgName();
				  BusAnnualPlanForWater water = org.getBusAnnualPlanForWater();
				  Float reply = water.getReplyOfWater();
				  Set<BusCustomerMeter> meters = org.getBusCustomerMeters();
				  String meter="";
				  for(BusCustomerMeter m:meters){
					  int me = m.getMeterNumber();
					  if(meter.indexOf(me)<0){
						  if(meter.length()>0){
							  meter +=","+me;
						  }else{
							  meter += me;
						  }
						  
					  }
				  }
				  Date nowdate = new Date();
				  ReplyPrintTemp temp = new ReplyPrintTemp(name,meter,water.getContacts(), water.getContactNo(),orgId.toString(), ContextUtil.getCurrentUser().getFullName());
				  list.add(temp);
				  

				  BufferedImage localBufferedImage;
				try {
					
				      localJBarcode.setEncoder(Code39Encoder.getInstance());  
				      localJBarcode.setPainter(WideRatioCodedPainter.getInstance());  
				      localJBarcode.setTextPainter(BaseLineTextPainter.getInstance());  
				      localJBarcode.setShowCheckDigit(false);  		
				      localBufferedImage = localJBarcode.createBarcode( year+orgId.toString());
					saveToPNG(localBufferedImage,  orgId+".png",request);  
				} catch (InvalidAtributeException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}  
			     
			  } 
		   }
		   if(list!=null)
			   model.addAttribute("listLength", list.size());
		   
		   SimpleDateFormat myFmt=new SimpleDateFormat("yyyy年MM月dd日");
		   Date now=new Date();
		   model.addAttribute("unit", list);
		   model.addAttribute("myFmt", myFmt.format(now));
		   
	return "bus/season-print";
}

/**
 * 打印年度计划供水表预览
 * @param model
 * @return
 */
@RequestMapping("unitYearPlanPrint.do")
public String unitYearPlanPrint(Model model){
	 String year = getCurrentYear();
	 String hql="select w from BusAnnualPlanForWater w join w.org o where w.isCancel='yes' and o.year=?";
	 List<BusAnnualPlanForWater> list = busAnnualPlanForWaterService.findByHql(hql, year);
	 model.addAttribute("plan", list);
	return "bus/org-plan-view";
}

/**
 * 打印通知预览
 * @return
 * @throws UnsupportedEncodingException 
 */
@RequestMapping("unitYearReplyPrint.do")
public String unitYearReplyPrint(String orgNumber,String orgName,String orgNature,String year,String busOrgCustomerNumber,
		String address,String busOrgMeterNumber,String priYearUseWater,String userType,Model model,Long[] ids){	   
        if(year.equals("")){
    	  year = getCurrentYear();
        }
	   List<ReplyPrintTemp> list = new ArrayList<ReplyPrintTemp>();	
	   if(ids.length>0){
        	for(Long orgId:ids){
        		BusOrg org = busOrgService.get(orgId);
        		String name = org.getOrgName();
        		Set<BusCustomerMeter> meters = org.getBusCustomerMeters();
        		String meter="";
        		for(BusCustomerMeter m:meters){
        			int me = m.getMeterNumber();
        			if(meter.indexOf(me)<0){
        				if(meter.length()>0){
        					meter +=","+me;
        				}else{
        					meter +=me;
        				}
        				
        			}
        		}
        		BusAnnualPlanForWater water= org.getBusAnnualPlanForWater();
        		Float one = water.getFirstQuarterReply();
        		Float two = water.getSecondQuarterReply();
        		Float three = water.getThirdQuarterReply();
        		Float fourth = water.getFourthQuarterReply();
        		Float reply = water.getReplyOfWater();
        		ReplyPrintTemp temp = new ReplyPrintTemp(name,one,two,three,fourth,meter,reply);
        		list.add(temp);      		
        	}
        }else{
            list = busOrgService.unitYearReplyPrin(orgNumber, orgName, year, busOrgCustomerNumber, address, busOrgMeterNumber, priYearUseWater, userType, orgNature);
        }	 
         model.addAttribute("unit", list);
	     model.addAttribute("year", year);
	    if(list!=null)
	    	model.addAttribute("listLength", list.size());
	    //log.debug("#######################################################"+JsonUtil.getJSONSerializer().deepSerialize(list));
        return "bus/org-reply-view";
}

static void saveToPNG(BufferedImage paramBufferedImage, String paramString, HttpServletRequest request)  
{  
  saveToFile(paramBufferedImage, paramString, "png",request);  
}  

static void saveToFile(BufferedImage paramBufferedImage, String paramString1, String paramString2, HttpServletRequest request)  
{  
  try  
  {  
	  String path=request.getSession().getServletContext().getRealPath("/");
    FileOutputStream localFileOutputStream = new FileOutputStream(path +"/pic/"+ paramString1);  
    ImageUtil.encodeAndWrite(paramBufferedImage, paramString2, localFileOutputStream, 96, 96);  
    localFileOutputStream.close();  
  }  
  catch (Exception localException)  
  {  
    localException.printStackTrace();  
  }  
}  

/**
 * 打印封停预览
 * @param orgNumber
 * @param orgName
 * @param year
 * @param busOrgCustomerNumber
 * @param address
 * @param busOrgMeterNumber
 * @param priYearUseWater
 * @param userType
 * @param model
 * @return
 * @throws UnsupportedEncodingException 
 */
@RequestMapping("unitYearEmailPrint.do")
public String unitYearEmailPrint(String orgNumber,String orgName,String orgNature,String year,String busOrgCustomerNumber,
		String address,String busOrgMeterNumber,String priYearUseWater,String userType,Model model,Long[] ids) throws UnsupportedEncodingException{
	    List<OrgTemp>  orgList = new ArrayList<OrgTemp>();
	    if(year.equals("")){
	    	year = getCurrentYear();
	    }
	    if(ids.length>0){
	    	for(Long orgId:ids){
	    		BusOrg org = busOrgService.get(orgId);
	    		String code = org.getCode();
	    		String name = org.getOrgName();
				String nature = org.getOrgNature();
	    		String addr= org.getPostalAddress();
	    		OrgTemp temp = new OrgTemp(code,name,addr);
	    		orgList.add(temp);
	    	}
	    }else{
	    	orgList = busOrgService.emailPrintView(orgNumber, orgName, year, busOrgCustomerNumber, address, busOrgMeterNumber, priYearUseWater, userType, orgNature);
	    }
	    String result = JsonUtil.getJSONSerializer().deepSerialize(orgList);
	    log.debug("#####################################size:"+orgList.size()+"  "+result);
	    model.addAttribute("orgList",result);
	return "bus/org-email-view";
}

/**
 * 打印年度计划通知
 * @param orgNumber
 * @param orgName
 * @param year
 * @param busOrgCustomerNumber
 * @param address
 * @param busOrgMeterNumber
 * @param priYearUseWater
 * @param userType
 * @param model
 * @param ids
 * @return
 */
@RequestMapping("yearPlanPrint.do")
public String yearPlanPrint(String orgNumber,String orgName,String orgNature,String year,String busOrgCustomerNumber,
		String address,String busOrgMeterNumber,String priYearUseWater,String userType,Model model,Long[] ids){
		   if(year.equals("")){
			  year = getCurrentYear();
		    }
		   List<ReplyPrintTemp> list = new ArrayList<ReplyPrintTemp>();	
		   if(ids.length>0){
			  for(Long orgId:ids){
				  BusOrg org = busOrgService.get(orgId);
				  String name = org.getOrgName();
				  BusAnnualPlanForWater water = org.getBusAnnualPlanForWater();
				  Float reply = water.getReplyOfWater();
				  Set<BusCustomerMeter> meters = org.getBusCustomerMeters();
				  String meter="";
				  for(BusCustomerMeter m:meters){
					  int me = m.getMeterNumber();
					  if(meter.indexOf(me)<0){
						  if(meter.length()>0){
							  meter +=","+me;
						  }else{
							  meter += me;
						  }
						  
					  }
				  }
				  ReplyPrintTemp temp = new ReplyPrintTemp(name,reply,meter);
				  list.add(temp);
			  } 
		   }else{
			   list = busOrgService.unitYearReplyPrin(orgNumber, orgName, year, busOrgCustomerNumber, address, busOrgMeterNumber, priYearUseWater, userType, orgNature);
		   }
		   if(list!=null)
			   model.addAttribute("listLength", list.size());
		   model.addAttribute("list", list);
	return "bus/org-year-plan-notice-print";
}


/**
 * 进入年度实际用水汇总页面
 * @param model
 * @return
 */
@RequestMapping("countReplyWaterList.do")
public String countReplyWaterList(Model model){
	 String year = getCurrentYear();
	 Map<String,List<Map<String,String>>> map = new HashMap<String,List<Map<String,String>>>();
	 List<Map<String, String>> m = UserType.getList();
	 map.put("list", m);
	 model.addAttribute("year", year);
	 model.addAttribute("userType",JsonUtil.getJSONSerializer().deepSerialize(map));
	 return "bus/org-year-count";
}

/**
 * 获取年度实际用水汇总信息
 * @param pageNo
 * @param pageSize
 * @param orgNumber
 * @param orgName
 * @param year
 * @param busOrgCustomerNumber
 * @param address
 * @param userType
 * @return
 * @throws ParseException 
 */
@RequestMapping("listCountReplyWater.do")
@ResponseBody
public String listCountReplyWater(int pageNo,int pageSize,String orgNumber,
		String orgName,String orgNature, String year, String busOrgCustomerNumber,
		String address,String userType) throws ParseException{
	    SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	    
	    String sql = " SELECT b.`org_number`,b.`org_name`,b.`address`,b.`year`,w.plan_of_water,w.reply_of_water"
                   + " FROM `bus_org` b join `bus_annual_plan_for_water` w on b.org_id=w.org_id"
                   + " WHERE b.`import_status`='normal' and b.year='"+year+"' and w.is_cancel='yes'";
	    if(null !=orgNumber && !orgNumber.equals("")){
	    	sql +=" and b.org_number="+orgNumber;
	    }
	    if(null !=orgName && !orgName.equals("")){
	    	sql +=" and b.org_name like '%"+orgName+"%'";
	    }
		if(null !=orgNature && !orgNature.equals("")){
			sql +=" and b.org_name like '%"+orgNature+"%'";
		}
	    if(null !=address && !address.equals("")){
	    	sql +=" and b.address like '%"+address+"%'";
	    }
	    if(null !=userType && !userType.equals("")){
	    	sql +=" and b.user_type ="+userType;
	    }
	    if(null !=busOrgCustomerNumber && !busOrgCustomerNumber.equals("")){
	    	sql +=" and b.org_id in (select bcm.org_id from `bus_customer_meter` bcm where bcm.customer_Number="+busOrgCustomerNumber+")";
	    }
	    this.page = busOrgService.getPageByJdbcTemplate(sql, pageNo, pageSize, new unitCountYearRowMap());
	    List<BusOrgTemp> list = (List<BusOrgTemp>)this.page.getResult();
	    for(BusOrgTemp b:list){	   	        
	        int o = b.getOrgNumber();
	        int y = Integer.parseInt(b.getYear());
		    String s = y+"-01-01 00:00:00";
		    String e = y+1+"-01-01 00:00:00";
	    	List<BusMeterReadInfo> listS = busMeterReadInfoService.getStartOneMonthMeterInfo(o, format.parse(s),format.parse(e));
	    	Float w=0f;
	    	if(null != listS){
	    		for(BusMeterReadInfo info:listS){
		    		w +=info.getWater();
		    	}
	    	}	    	
	    	b.setYearUseWater(w);
	    }
	    String result = this.getPageJosnResult();
	    log.debug("##################################:"+result);
	    return result;
}

/**
 * 导出年度实际用水汇总
 * @param orgNumber
 * @param orgName
 * @param year
 * @param busOrgCustomerNumber
 * @param address
 * @param userType
 * @throws UnsupportedEncodingException 
 * @throws ParseException 
 */
@RequestMapping("importYearCountUnitWater.do")
public void importYearCountUnitWater(String orgNumber,String orgName,String orgNature,String year,
		String busOrgCustomerNumber,String address,String userType,HttpServletResponse response) throws UnsupportedEncodingException, ParseException{
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int y =Integer.parseInt(year); 
	    String fileName = y +"年度实际用水汇总.xls";
		response.setCharacterEncoding("UTF-8");
		response.setContentType("application/octet-stream");
		OutputStream os = null;
		WritableWorkbook book= null;
		WritableSheet sheet = null;
	    try {
			os = response.getOutputStream();
			String downName = new String(fileName.getBytes("gb2312"),"ISO8859-1");
			response.setHeader("content-disposition", "attachment;filename="+downName);
			//创建写入工作薄对象
			book = Workbook.createWorkbook(os);
			//创建一个sheet
			sheet = book.createSheet("sheet1", 0);
			//样式
			WritableCellFormat f = new WritableCellFormat();
			f.setAlignment(Alignment.CENTRE);
			//第一行
			addFirstRowExcel(sheet,y,f);			
			
	        //获取要导出的数据
	        List<BusOrgTemp> list = busOrgService.getPlanYearCountData(orgNumber, orgName, year, busOrgCustomerNumber, address, userType, orgNature);
	        int row =1;
	        if(list != null && list.size()>0){
	        	for(int i=0;i<list.size();i++){
	        		BusOrgTemp temp = list.get(i);
	        		int o = temp.getOrgNumber();
	        		Float replyWater = temp.getReplyOfWater();
	        		Long orgType = temp.getOrgTypeId();
	        		String type="";
	        		if(orgType != null && orgType>0){
	        			type +=orgType;
	        		}	        		
	        		String s = y+"-01-01 00:00:00";
	        		String e = y+1+"-01-01 00:00:00";
	        		Float w=0f;
	        		Float over = 0f;
	        		List<BusMeterReadInfo> listS = busMeterReadInfoService.getStartOneMonthMeterInfo(o, format.parse(s),format.parse(e));
	        		if(null != listS){
	    	    		for(BusMeterReadInfo info:listS){
	    		    		w +=info.getWater();
	    		    	}
	    	    	}
	        		//节约水量
	        		Float saveWater = replyWater-w;
	        		//超用水量
	        		if(replyWater<w){
	        			over = w-replyWater;
	        		}
	        		
	        		if(i%23 == 0){
	        			//设置行高
	        			 sheet.setRowView(row, 800);
						//填充标题
						sheet.addCell(new Label(1, row, "序号",f));
						sheet.addCell(new Label(2,row,"单位名称",f));
						sheet.addCell(new Label(3,row,"单位类别",f));
						sheet.addCell(new Label(4,row,"计划批复水量",f));
						sheet.addCell(new Label(5,row,"实际批复水量",f));
						sheet.addCell(new Label(6,row,"实用水量",f));
						sheet.addCell(new Label(7,row,"超用水量",f));
						sheet.addCell(new Label(8,row,"节约水量",f));
						
						row++;
						
						//设置行高
						 sheet.setRowView(row, 800);
						//填充内容	
						sheet.addCell(new Label(1, row,i+1+"",f));
						sheet.addCell(new Label(2,row,temp.getOrgName(),f));
						sheet.addCell(new Label(3,row,type,f));
						sheet.addCell(new Label(4,row,temp.getPlanOfWater().toString(),f));
						sheet.addCell(new Label(5,row,temp.getReplyOfWater().toString(),f));
						sheet.addCell(new Label(6,row,w.toString(),f));
						sheet.addCell(new Label(7,row,over.toString(),f));
						sheet.addCell(new Label(8,row,saveWater.toString(),f));
						
						row++;
						
	        		}else{
	        			//设置行高
	        			 sheet.setRowView(row, 800);
	        			//填充内容	
						sheet.addCell(new Label(1, row,i+1+"",f));
						sheet.addCell(new Label(2,row,temp.getOrgName(),f));
						sheet.addCell(new Label(3,row,type,f));
						sheet.addCell(new Label(4,row,temp.getPlanOfWater().toString(),f));
						sheet.addCell(new Label(5,row,temp.getReplyOfWater().toString(),f));
						sheet.addCell(new Label(6,row,w.toString(),f));
						sheet.addCell(new Label(7,row,over.toString(),f));
						sheet.addCell(new Label(8,row,saveWater.toString(),f));
						
						row++;
	        		}
	        	}
	        }
	        book.write();//写入到文件中
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				if(null != book){
					book.close();
				}
				if(null != os){
					os.close();
				}
			} catch (Exception e2) {
				
			}
		}	
	    
	    

}

private void addFirstRowExcel(WritableSheet sheet,int year,WritableCellFormat f){
	try {
		 
		 sheet.setColumnView(0, 10);
		 sheet.setColumnView(1, 5);
		 sheet.setColumnView(2, 15);
		 sheet.setColumnView(3, 15);
		 sheet.setColumnView(4, 10);
		 sheet.setColumnView(5, 10);
		 sheet.setColumnView(6, 10);
		 sheet.setColumnView(7, 10);
		 sheet.setColumnView(8, 10);		 
		
		 //设置行高
		 sheet.setRowView(0, 800);
		 //合并单元格，列开，行开，列结，行结
		 sheet.mergeCells(1, 0, 8, 0);
		 //添加内容
		 sheet.addCell(new Label(1, 0, year+"年度实际用水汇总", f));
	} catch (Exception e) {
		// TODO: handle exception
	}

}

/**
 * 进入单位类别用水汇总页面
 * @param model
 * @return
 */
@RequestMapping("countWaterData.do")
public String countWaterData(Model model){
	   String year = getCurrentYear();
	   model.addAttribute("year", year);
	return "bus/org-count-list";
}

/**
 * 获取单位类别用水汇总数据
 * @param year
 * @return
 */
@RequestMapping("listCountWaterData.do")
@ResponseBody
public String listCountWaterData(String year){
	List<OrgTypeCountTemp> listo = new ArrayList<OrgTypeCountTemp>();
	List<Object[]> list = busOrgService.listCountWaterData(year);
	for(int i=0;i<list.size();i++){
		Object[] obj = list.get(i);
		OrgTypeCountTemp temp = new OrgTypeCountTemp();
		String a = (String) obj[0];
		Float b = (Float) obj[1];
		Float c = (Float) obj[2];
		Float overWater = c>b?c-b:0;
		temp.setOrgType(a);
		temp.setReply(b);
		temp.setUseWater(c);
		temp.setOverWater(overWater);
		temp.setSaveWater(b-c);
		listo.add(temp);
	}
	String result = JsonUtil.getJSONSerializer().deepSerialize(listo);	
	log.debug("###################"+list.size()+"###################result:"+result);
	return result;
}

/**
 * 导入单位年度信息
 * @param model
 * @return
 */
@RequestMapping("importInfo.do")
public String importOrgInfo(ModelMap model){
	return "bus/import-info";
}

/**
 * 根据excel导入单位年度信息
 * @param request
 * @param model
 * @return
 * @throws IOException 
 */
@RequestMapping("importInfoData.do")
@ResponseBody
public void importOrgInfoData(HttpServletRequest request,HttpServletResponse response,final String year,ModelMap model) throws IOException{
    MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
    Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();

    response.setContentType("text/html;charset=utf-8");
    PrintWriter print = response.getWriter();
    for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {  
        MultipartFile mf = entity.getValue();  
//        ImportDataFile idf = new ImportDataFile();
//		idf.setFileName(mf.getOriginalFilename());
//		idf.setFileSize(mf.getSize()/1024);
//		idf.setUploadTime(new Date());
//		idf.setCreater(ContextUtil.getCurrentUser());
//		idf.setCreateTime(new Date());
//		idf.setImportType(ImportType.org_info.name());
//		idf.setYear(year);
        try {
			final Map<String,String> m = FileUploadUtils.uploadFile(mf,path);
//    		idf.setFilePath(m.get("file"));
//			idf.setDataStatus(ImportStatus.in_import.name());
//			idf.setStatusDescription("系统正在导入数据，请稍候...");
			//final ImportDataFile idfTmp = importDataFileService.save(idf);
	        File uploadFilePath = new File(m.get("path"));
	        //判断文件是否已经上传完毕
	        if (uploadFilePath.exists() == true) {
//	        	log.error("****执行单位基本信息导入,导入年份："+year+",批次id"+idf.getImportId());
				//启动一个线程导入数据
			    Thread t1 = new Thread(new Runnable(){  
			            public void run(){
			            	busAnnualPlanForWaterService.insertInfoData(path+m.get("file"), year);
			            }});  
			        t1.start();

			    print.write("<script type='text/javascript'>alert('上传成功！');top.frmright.resetSearch();top.Dialog.close();</script>");
	        }
		} catch (IOException e) {

			print.write("<script type='text/javascript'>alert('上传失败！');</script>");
			e.printStackTrace();
		}
    }

}
}