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>> map = new HashMap>>(); List> 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 listS = busMeterReadInfoService.getStartOneMonthMeterInfo(orgNumber, startTime,getAscOneMonth(startTime)); // if (null == listS) { // return; // } // List 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 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>> map = new HashMap>>(); List> 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 list1 = busOrgService.findByHql(hql2, orgNumber,(nextyear+"").trim()); // BusOrg bo = list1.get(0); // Map map = new Hashtable(); // 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 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 bw = busAnnualPlanForWaterService.findByHql(h, bo.getOrgId()); if(null == bw || bw.size() == 0){ bo.setIsPlanUser(StaticValues.NO_PLAN); Updater updater = new Updater(bo); busOrgService.updateByUpdater(updater); } } //客户提出在年初始化的时候不再判断首月跟尾月是否有水，李周芳 2015-03-26 // 先判断是否12个月都有水,只判断首月跟尾月 // List listS = busMeterReadInfoService.getStartOneMonthMeterInfo(orgNumber, startTime,getAscOneMonth(startTime)); // if (null == listS) { // return; // } // List 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 list1 = busOrgService.findByHql(hql2, orgNumber,(nextyear+"").trim()); BusOrg bo = list1.get(0); bo.setIsPlanUser(StaticValues.IS_PLAN); Updater updater = new Updater(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>> map = new HashMap>>(); List> m = UserType.getList(); String year = getCurrentYear(); map.put("list", m); model.addAttribute("year", year); model.addAttribute("map", JsonUtil.getJSONSerializer().deepSerialize(map)); //载入状态颜色 Map>> statusMap = new HashMap>>(); statusMap.put("list", loadStatusSelectWithColor()); String statusList = JsonUtil.getJSONSerializer().deepSerialize(statusMap); model.addAttribute("statusList", statusList); //载入状态名称 Map>> nameMap = new HashMap>>(); 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 list = (List)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 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 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>> map = new HashMap>>(); 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>> map = new HashMap>>(); 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> loadStatusSelectWithColor(){ List> statusList = new ArrayList>(); Map defaultMap = new HashMap(); defaultMap.put("key", "请选择..."); defaultMap.put("value", ""); statusList.add(defaultMap ); List allOrgType = busStatusService.getAll(); for(BaseStatus bs:allOrgType){ Map map = new HashMap(); map.put("key", bs.getStatusColor()); map.put("value", bs.getId().toString()); statusList.add(map); } return statusList; } private List> loadStatusSelectWithName(){ List> statusList = new ArrayList>(); Map defaultMap = new HashMap(); defaultMap.put("key", "请选择..."); defaultMap.put("value", ""); statusList.add(defaultMap ); List allOrgType = busStatusService.getAll(); for(BaseStatus bs:allOrgType){ Map map = new HashMap(); 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 updater1 = new Updater(busOrg); busOrgService.updateByUpdater(updater1); } //修改季度考核表 String hql="select q from BusQuarterlyAssessment q where q.org.orgId=? order by q.assessmentQuarter"; List 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 updater = new Updater(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 updater = new Updater(busAnnualPlanForWater); // busAnnualPlanForWaterService.updateByUpdater(updater); //季度考核表如何处理取消考核,要删除掉 String hql = "from BusQuarterlyAssessment b where b.org.orgId=?"; List 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 list = (List)this.page.getResult(); for(int i=0;i list = new ArrayList(); 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 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 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 list = new ArrayList(); if(ids.length>0){ for(Long orgId:ids){ BusOrg org = busOrgService.get(orgId); String name = org.getOrgName(); Set 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 orgList = new ArrayList(); 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 list = new ArrayList(); 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 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>> map = new HashMap>>(); List> 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 list = (List)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 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 list = busOrgService.getPlanYearCountData(orgNumber, orgName, year, busOrgCustomerNumber, address, userType, orgNature); int row =1; if(list != null && list.size()>0){ for(int i=0;i0){ 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 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 listo = new ArrayList(); List list = busOrgService.listCountWaterData(year); for(int i=0;ib?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 fileMap = multipartRequest.getFileMap(); response.setContentType("text/html;charset=utf-8"); PrintWriter print = response.getWriter(); for (Map.Entry 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 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(""); } } catch (IOException e) { print.write(""); e.printStackTrace(); } } } }