waterElectricityService/Water.java

package com.happy.action;

import com.happy.Model.*;
import com.happy.Until.DBtoExcel;
import com.happy.Until.ResponseUtil;
import com.happy.service.WaterService;
import com.opensymphony.xwork2.ActionSupport;
import net.sf.json.JSONObject;
import org.apache.struts2.ServletActionContext;
import org.apache.struts2.interceptor.ServletRequestAware;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;


public class Water extends ActionSupport implements ServletRequestAware {

    private HttpServletRequest request;
    public HttpServletResponse response;

    public int page; // 当前页
    public int rows;// 每页显示的行数rows
    public String build;
    public String user_id;
    public int id;
    public String dom;
    public String username;
    public String stu_number;
    public String time;
    public String begin_time;
    public String end_time;
    public double rate;
    public String number;
    public String name;
    public String password;
    public String day;
    public String month;
    public String year;
    public String user_name;
    public String use_amount;
    public String re_time;


    @Resource
    public WaterService waterService;


    public HttpServletRequest getRequest() {
        return request;
    }

    public void setRequest(HttpServletRequest request) {
        this.request = request;
    }

    public void setServletRequest(HttpServletRequest request) {
        this.request = request;
    }

    public HttpServletResponse getResponse() {
        return response;
    }

    public void setResponse(HttpServletResponse response) {
        this.response = response;
    }

    public int getPage() {
        return page;
    }

    public void setPage(int page) {
        this.page = page;
    }

    public int getRows() {
        return rows;
    }

    public void setRows(int rows) {
        this.rows = rows;
    }

    public String getBuild() {
        return build;
    }

    public void setBuild(String build) {
        this.build = build;
    }

    public String getUser_id() {
        return user_id;
    }

    public void setUser_id(String user_id) {
        this.user_id = user_id;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getDom() {
        return dom;
    }

    public void setDom(String dom) {
        this.dom = dom;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getStu_number() {
        return stu_number;
    }

    public void setStu_number(String stu_number) {
        this.stu_number = stu_number;
    }

    public String getTime() {
        return time;
    }

    public void setTime(String time) {
        this.time = time;
    }

    public String info(){
        return "info";
    }

    public String getBegin_time() {
        return begin_time;
    }

    public void setBegin_time(String begin_time) {
        this.begin_time = begin_time;
    }

    public String getEnd_time() {
        return end_time;
    }

    public void setEnd_time(String end_time) {
        this.end_time = end_time;
    }

    public double getRate() {
        return rate;
    }

    public void setRate(double rate) {
        this.rate = rate;
    }

    public String getNumber() {
        return number;
    }

    public void setNumber(String number) {
        this.number = number;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getDay() {
        return day;
    }

    public void setDay(String day) {
        this.day = day;
    }

    public String getMonth() {
        return month;
    }

    public void setMonth(String month) {
        this.month = month;
    }

    public String getYear() {
        return year;
    }

    public void setYear(String year) {
        this.year = year;
    }

    public String getUser_name() {
        return user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUse_amount() {
        return use_amount;
    }

    public void setUse_amount(String use_amount) {
        this.use_amount = use_amount;
    }

    public String getRe_time() {
        return re_time;
    }

    public void setRe_time(String re_time) {
        this.re_time = re_time;
    }

    // 设备管理
    public String list() {
        JSONObject resultJson = new JSONObject();
        int total = waterService.findPageTotal();// 查询表中的总记录数
        List<Build_water> listPage = waterService.getbuild(page, rows);// 查询分页

        if (listPage == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", listPage);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(),
                resultJson.toString());
        return null;
    }

    public String querylist() {
        JSONObject resultJson = new JSONObject();
        StringBuilder sb = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        if (this.build != null) {
            sb.append(" and build='" + this.build + "'");
            sb2.append(" and build='" + this.build + "'");
        }

        if (this.user_id != null) {
            sb.append(" and user_id like '%" + this.user_id + "%'");
            sb2.append(" and user_id like '%" + this.user_id + "%'");
        }
        int start = (page - 1) * rows;// 每页的起始下标
        sb.append(" order by id asc limit "+start+", "+rows);
        int total = this.waterService.total(sb2.toString());
        List<Build_water> pp = this.waterService.findAllN(sb.toString());
        System.out.println(pp);
        if (pp == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", pp);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    public String updateBuild(){
        JSONObject resultJson = new JSONObject();
        Build_water bw = new Build_water();
        bw.setId(id);
        bw.setBuild(build);
        bw.setDom(dom);
        bw.setUser_id(user_id);
        System.out.println(222);
        int m = waterService.updateBuild(bw);
        System.out.println(m);
        if (m>0){
            resultJson.put("msg", "修改成功");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
            return null;
        }
        resultJson.put("msg", "修改失败");
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    public String delBuild(){
        JSONObject resultJson = new JSONObject();
        int m = waterService.delBuild(id);
        if (m>0){
            resultJson.put("msg", "删除成功");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
            return null;
        }
        resultJson.put("msg", "删除失败");
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    // 增加设备
    public String addBuild(){
        JSONObject resultJson = new JSONObject();
        Build_water bw = new Build_water();
        bw.setSchool("墨轩湖校区");
        bw.setBuild(build);
        bw.setDom(dom);
        bw.setUser_id(user_id);
        String floors = dom.charAt(0)+"层";
        bw.setFloors(floors);
        int m = waterService.addBuild(bw);
        if (m>0){
            resultJson.put("msg", "添加成功");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
            return null;
        }
        resultJson.put("msg", "添加失败");
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    // 导出表格
    public String toExcel(){
        JSONObject resultJson = new JSONObject();
        // 项目路径地址
        String path = request.getSession().getServletContext()
                .getRealPath("/download/");// File.separator
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement("select * from build_water order by id asc");
            rs = ps.executeQuery();
            System.out.println("成功获取数据");
            Vector columnName = new Vector();
            columnName.add("id");
            columnName.add("school");
            columnName.add("build");
            columnName.add("floors");
            columnName.add("dom");
            columnName.add("user_id");
            // 导出文件
            new DBtoExcel().WriteExcel(rs, path + "设备信息" + "Report.xls",
                    "sheet1", columnName);
            ps.close();
            String url = request.getSession().getServletContext().getContextPath();
            System.out.println();
            resultJson.put("downurl", url+"/download/设备信息" + "Report.xls");
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 用户余额列表
    public String Money(){
        JSONObject resultJson = new JSONObject();
        StringBuilder s1 = new StringBuilder();
        StringBuilder s2 = new StringBuilder();
        if (user_name!=null){
            s1.append(" and user_name like '%"+ user_name +"%'");
            s2.append(" and user_name like '%"+ user_name +"%'");
        }
        int total = waterService.findUsersTotal(s2.toString());// 查询表中的总记录数
        List<Users> listPage = waterService.queryMoney(page, rows, s1.toString());// 查询分页

        if (listPage == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", listPage);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(),
                resultJson.toString());
        return null;
    }

    // 总余额
    public String findMoneyTotal(){
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement("select sum(balance) as total from users ");
            rs = ps.executeQuery();
            System.out.println("成功获取数据");
            String money = "";
            while (rs.next()){
                money = rs.getString("total");
            }
            resultJson.put("money", money);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 个人充值明细
    public String findRecharge(){
        JSONObject resultJson = new JSONObject();
        StringBuilder sb = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        if (this.stu_number != null) {
            sb.append(" and stu_number = '" + this.stu_number + "' ");
            sb2.append(" and stu_number = '" + this.stu_number + "' ");
        }
        if (this.time != null) {
            sb.append(" and re_time like '" + this.time + "%' ");
            sb2.append(" and re_time like '" + this.time + "%' ");
        }
        int total = this.waterService.chargeTotal(sb2.toString());
        System.out.println(total);
        int start = (page - 1) * rows;// 每页的起始下标
        sb.append(" order by id asc limit "+start+", "+rows+";");
        List<Recharge> pp = this.waterService.chargeRecord(sb.toString());
        System.out.println(pp);
        if (pp == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", pp);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    // 个人充值总额
    public String rechargeTotal(){
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        StringBuffer sb = new StringBuffer("select sum(account) as total from recharge where pay_state=2 ");
        if (this.stu_number != null) {
            sb.append(" and stu_number = '" + this.stu_number + "' ");
        }
        if (this.time != null) {
            sb.append(" and re_time like '" + this.time + "%' ");
        }
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();
            System.out.println("成功获取数据");
            String money = "";
            while (rs.next()){
                money = rs.getString("total");
            }
            resultJson.put("money", money);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 个人消费明细-商家对账
    public String findConsume(){
        JSONObject resultJson = new JSONObject();
        StringBuilder sb = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        if (this.build != null) {
            sb.append(" and b.build = '" + this.build + "' ");
            sb2.append(" and b.build = '" + this.build + "' ");
        }
        if (this.dom != null) {
            sb.append(" and b.dom = '" + this.dom + "' ");
            sb2.append(" and b.dom = '" + this.dom + "' ");
        }
        if (this.begin_time != null) {
            sb.append(" and a.begin_time > '" + this.begin_time + "' ");
            sb2.append(" and a.begin_time > '" + this.begin_time + "' ");
        }
        if (this.end_time != null ) {
            sb.append(" and a.begin_time < '" + this.end_time + "' ");
            sb2.append(" and a.begin_time < '" + this.end_time + "' ");
        }
        int total = this.waterService.findComTotal(sb2.toString());
        System.out.println(total);
        int start = (page - 1) * rows;// 每页的起始下标
        sb.append(" order by a.id desc limit "+start+", "+rows+";");
        List<Consume> pp = this.waterService.findCom(sb.toString());
        System.out.println(pp);
        if (pp == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", pp);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    // 日月年消费总额
    public String totalFeel(){
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        StringBuffer sb1 = new StringBuffer("select sum(a.use_amount) as total from consume a, build_water b where a.device_code=b.user_id and a.begin_time like '"+year+"%'");
        StringBuffer sb2 = new StringBuffer("select sum(a.use_amount) as total from consume a, build_water b where a.device_code=b.user_id and a.begin_time like '"+month+"%'");
        StringBuffer sb3 = new StringBuffer("select sum(a.use_amount) as total from consume a, build_water b where a.device_code=b.user_id and a.begin_time like '"+day+"%'");
        System.out.println(sb3.toString());
        if (this.build != null) {
            sb1.append(" and b.build = '" + this.build + "' ");
            sb2.append(" and b.build = '" + this.build + "' ");
            sb3.append(" and b.build = '" + this.build + "' ");
        }
        if (this.dom != null) {
            sb1.append(" and b.dom = '" + this.dom + "' ");
            sb2.append(" and b.dom = '" + this.dom + "' ");
            sb3.append(" and b.dom = '" + this.dom + "' ");
        }
        Connection conn = null;
        ResultSet rs1 = null;
        ResultSet rs2 = null;
        ResultSet rs3 = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sb1.toString());
            rs1 = ps.executeQuery();
            PreparedStatement ps2 = conn.prepareStatement(sb2.toString());
            rs2 = ps2.executeQuery();
            PreparedStatement ps3 = conn.prepareStatement(sb3.toString());
            rs3 = ps3.executeQuery();
            System.out.println("成功获取数据");
            String money1 = "";
            String money2 = "";
            String money3 = "";
            while (rs1.next()){
                money1 = rs1.getString("total");
            }
            while (rs2.next()){
                money2 = rs2.getString("total");
            }
            while (rs3.next()){
                money3 = rs3.getString("total");
            }
            System.out.println("money3: "+money3);
            resultJson.put("money_year", money1==""?"0":money1);
            resultJson.put("money_month", money2==""?"0":money2);
            resultJson.put("money_day", money3==""?"0":money3);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 下拉列表-楼栋
    public String getBuilds(){
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        StringBuffer sb = new StringBuffer("select distinct  build as build from build_water ");
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();
            System.out.println("成功获取数据");
            ArrayList al = new ArrayList();
            while (rs.next()){
                al.add(rs.getString("build"));
            }
            resultJson.put("builds", al);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 下拉列表-宿舍
    public String getdoms(){
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        StringBuffer sb = new StringBuffer("select distinct  dom as dom from build_water where 1=1 ");
        if (this.build != null){
            sb.append(" and build ='"+this.build + "'");
        }
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();
            System.out.println("成功获取数据");
            ArrayList al = new ArrayList();
            while (rs.next()){
                al.add(rs.getString("dom"));
            }
            resultJson.put("doms", al);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 导出商家对账表格
    public String toComExcel(){
        JSONObject resultJson = new JSONObject();
        StringBuilder sb = new StringBuilder("select a.stu_number, a.user_name, a.use_amount, b.build, b.dom, a.begin_time from consume a, build_water b where a.device_code=b.user_id ");
        if (this.build != null) {
            sb.append(" and b.build = '" + this.build + "' ");
        }
        if (this.dom != null) {
            sb.append(" and b.dom = '" + this.dom + "' ");
        }
        if (this.begin_time != null) {
            sb.append(" and a.begin_time like '" + this.begin_time + "%' ");
        }
        sb.append(" order by a.id asc  ");
        // 项目路径地址
        String path = request.getSession().getServletContext()
                .getRealPath("/download/");// File.separator
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();
            System.out.println("成功获取数据");
            Vector columnName = new Vector();
            columnName.add("学号");
            columnName.add("姓名");
            columnName.add("消费金额");
            columnName.add("楼栋");
            columnName.add("宿舍");
            columnName.add("消费时间");
            // 导出文件
            new DBtoExcel().WriteExcel(rs, path + "商家对账" + "Report.xls",
                    "sheet1", columnName);
            ps.close();
            String url = request.getSession().getServletContext().getContextPath();
            System.out.println();
            resultJson.put("downurl", url+"/download/商家对账" + "Report.xls");
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;

    }

    // 日用水统计
    public String dayTong(){
        List<String> dateList = new ArrayList<>();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        for (int i=0; i>-15; i--){
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.DATE,i);
            dateList.add(simpleDateFormat.format(calendar.getTime()));
        }
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ArrayList al = new ArrayList();
            for (int i=0; i<dateList.size(); i++) {
                StringBuffer sb = new StringBuffer("select sum(use_size) as use_size from `consume` a, build_water b where a.device_code=b.user_id and a.begin_time like '"+dateList.get(i)+"%' ");
                if (this.build != null) {
                    sb.append(" and b.build = '" + this.build + "' ");
                }
                if (this.dom != null) {
                    sb.append(" and b.dom = '" + this.dom + "' ");
                }
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                rs = ps.executeQuery();
                while (rs.next()){
                    if(rs.getString("use_size")==null || rs.getString("use_size").equals("null")) {
                        al.add("0");
                    }else{
                        al.add(rs.getString("use_size"));
                    }
                }
            }
            resultJson.put("day", dateList);
            resultJson.put("use_size", al);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 月用水统计
    public String monthTong(){
        List<String> dateList = new ArrayList<>();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM");
        for (int i=0; i>-15; i--){
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.MONTH,i);
            dateList.add(simpleDateFormat.format(calendar.getTime()));
        }
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ArrayList al = new ArrayList();
            for (int i=0; i<dateList.size(); i++) {
                StringBuffer sb = new StringBuffer("select sum(use_size) as use_size from `consume` a, build_water b where a.device_code=b.user_id and a.begin_time like '"+dateList.get(i)+"%' ");
                if (this.build != null) {
                    sb.append(" and b.build = '" + this.build + "' ");
                }
                if (this.dom != null) {
                    sb.append(" and b.dom = '" + this.dom + "' ");
                }
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                rs = ps.executeQuery();
                while (rs.next()){
                    if(rs.getString("use_size")==null || rs.getString("use_size").equals("null")) {
                        al.add("0");
                    }else{
                        al.add(rs.getString("use_size"));
                    }
                }
            }
            resultJson.put("month", dateList);
            resultJson.put("use_size", al);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 年用水统计
    public String yearTong(){
        List<String> dateList = new ArrayList<>();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy");
        for (int i=0; i>-15; i--){
            Calendar calendar = Calendar.getInstance();
            calendar.add(Calendar.YEAR, i);
            dateList.add(simpleDateFormat.format(calendar.getTime()));
        }
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            ArrayList al = new ArrayList();
            for (int i=0; i<dateList.size(); i++) {
                StringBuffer sb = new StringBuffer("select sum(use_size) as use_size from `consume` a, build_water b where a.device_code=b.user_id and a.begin_time like '"+dateList.get(i)+"%' ");
                if (this.build != null) {
                    sb.append(" and b.build = '" + this.build + "' ");
                }
                if (this.dom != null) {
                    sb.append(" and b.dom = '" + this.dom + "' ");
                }
                PreparedStatement ps = conn.prepareStatement(sb.toString());
                rs = ps.executeQuery();
                while (rs.next()){
                    if(rs.getString("use_size")==null || rs.getString("use_size").equals("null")) {
                        al.add("0");
                    }else{
                        al.add(rs.getString("use_size"));
                    }
                }
            }
            resultJson.put("year", dateList);
            resultJson.put("use_size", al);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 用水总量
    public String waterTotal(){
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        StringBuffer sb = new StringBuffer("select sum(use_size) as total from consume a, build_water b where a.device_code=b.user_id ");
        if (this.build != null) {
            sb.append(" and b.build = '" + this.build + "' ");
        }
        if (this.dom != null) {
            sb.append(" and b.dom = '" + this.dom + "' ");
        }
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();
            System.out.println("成功获取数据");
            String size = "";
            while (rs.next()){
                if(rs.getString("total")==null || rs.getString("total").equals("null")) {
                    size = "0";
                }else{
                    size = rs.getString("total");
                }

            }
            resultJson.put("size", size);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    // 用水分析-用水详情
    public String detailWater(){
        JSONObject resultJson = new JSONObject();
        StringBuilder sb = new StringBuilder();
        StringBuilder sb2 = new StringBuilder();
        if (this.build != null) {
            sb.append(" and b.build = '" + this.build + "' ");
            sb2.append(" and b.build = '" + this.build + "' ");
        }
        if (this.dom != null) {
            sb.append(" and b.dom = '" + this.dom + "' ");
            sb2.append(" and b.dom = '" + this.dom + "' ");
        }
        if (this.username != null){
            sb.append(" and a.user_name = '" + this.username + "' ");
            sb2.append(" and a.user_name = '" + this.username + "' ");
        }
        int total = this.waterService.findComTotal(sb2.toString());
        System.out.println(total);
        int start = (page - 1) * rows;// 每页的起始下标
        sb.append(" order by a.id asc limit "+start+", "+rows+";");
        List<Consume> pp = this.waterService.findCom(sb.toString());
        System.out.println(pp);
        if (pp == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", pp);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    // 修改费率
    public String updatePrice(){
        JSONObject json = new JSONObject();
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = sf.format(new Date());
        Price price = new Price();
        price.setPrice(rate);
        price.setName("热水");
        price.setState(1);
        price.setTime(time);
        price.setTime2("至今");
        int m = waterService.passPrice(time);
        int n = waterService.insertPrice(price);
        if (m >0 & n>0){
            json.put("msg", "修改成功");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
        }
        return null;
    }

    public List<Price> queryPrice(){
        JSONObject resultJson = new JSONObject();
        List<Price> listPage = waterService.queryPrice(page, rows);// 查询分页
        int total = waterService.findPriceTotal();// 查询表中的总记录数

        if (listPage == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", listPage);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(),
                resultJson.toString());
        return null;
    }

    // 当前费率
    public String currentRate(){
        JSONObject resultJson = new JSONObject();
        ApplicationContext ac = new ClassPathXmlApplicationContext(
                "applicationContext.xml");
        DataSource dataSource = (DataSource) ac.getBean("slaveDataSource");
        StringBuffer sb = new StringBuffer("select price from price where name='热水' and state=1 and time in (select MAX(time) from `price` where name='热水' and state=1) ");
        Connection conn = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            PreparedStatement ps = conn.prepareStatement(sb.toString());
            rs = ps.executeQuery();
            System.out.println("成功获取数据");
            String rate = "";
            while (rs.next()){
                rate = rs.getString("price");
            }
            resultJson.put("rate", rate);
            ResponseUtil.writeJson(ServletActionContext.getResponse(),
                    resultJson.toString());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                // TODO: handle exception
            }
        }
        return null;
    }

    public String queryAdmin(){
        JSONObject resultJson = new JSONObject();
        String s1 = "";
        if (number!=null){
            s1 = " and number='"+this.number+"'";
        }
        List<Admin> listPage = waterService.queryAdmin(page, rows, s1); // 查询分页
        int total = waterService.findAdminTotal(s1); // 查询表中的总记录数

        if (listPage == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", listPage);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(),
                resultJson.toString());
        return null;
    }

    public String addAdmin(){
        JSONObject json = new JSONObject();
        List<Admin> ad = waterService.queryAdminByNum(number);
        if (ad!=null){
            json.put("msg", "用户已存在");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
            return null;
        }
        Admin admin = new Admin();
        admin.setName(name);
        admin.setNumber(number);
        admin.setPassword(password);
        int m = waterService.addAdmin(admin);
        if (m>0){
            json.put("msg", "添加成功");
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
        return null;
    }

    public String delAdmin(){
        JSONObject json = new JSONObject();
        int m = this.waterService.delAdmin(id);
        if (m>0){
            json.put("msg", "删除成功");
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
        return null;
    }

    public String updateAdmin(){
        JSONObject json = new JSONObject();
        Admin admin = new Admin();
        System.out.println(id);
        admin.setId(id);
        admin.setName(name);
        admin.setPassword(password);
        admin.setNumber(number);
        int m = waterService.updateAdmin(admin);
        if (m>0){
            json.put("msg", "更新成功");
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
        return null;
    }

    public String login(){
        JSONObject json = new JSONObject();
        List<Admin> login = waterService.login(number, password);
        if (login==null){
            json.put("state", "0");
            json.put("msg", "用户名或密码不正确");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
            return null;
        }
        json.put("state", "1");
        json.put("msg", "登录成功");
        ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
        return null;
    }

    // 查看水费异常充值记录
    public String findErrorPayByNo(){
        JSONObject resultJson = new JSONObject();
        List<Recharge> lrs = waterService.findErrorPayByNo(page, rows);
        int total = waterService.findErrorPayTotal();
        if (lrs == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", lrs);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(),
                resultJson.toString());
        return null;
    }

    // 处理水费异常充值
    public String handlerPayError(){
        JSONObject json = new JSONObject();
        int m = waterService.updateErrorWaterPay(id);
        if (m>0){
            json.put("code", 1);
            json.put("msg", "处理成功");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
            return null;
        }
        json.put("code", 0);
        json.put("msg", "处理失败");
        ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
        return null;
    }

    // 查看电费异常充值记录
    public String findErrorElcPayByNo(){
        JSONObject resultJson = new JSONObject();
        List<Recharge_elc> lrs = waterService.findErrorElcPayByNo(page, rows);
        int total = waterService.findErrorElecTotal();
        if (lrs == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", lrs);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(),
                resultJson.toString());
        return null;
    }

    // 处理电费异常充值
    public String handlerElecPayError(){
        JSONObject json = new JSONObject();
        int m = waterService.updateErrorElecPay(id);
        if (m>0){
            json.put("code", 1);
            json.put("msg", "处理成功");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
            return null;
        }
        json.put("code", 0);
        json.put("msg", "处理失败");
        ResponseUtil.writeJson(ServletActionContext.getResponse(), json.toString());
        return null;
    }

    // 查看异常水费
    public String queryErrorWater(){
        JSONObject resultJson = new JSONObject();
        StringBuffer s1 = new StringBuffer();
        StringBuffer s2 = new StringBuffer();
        StringBuffer s3 = new StringBuffer();
        if (build!=null){
            s1.append(" and b.build='"+this.build+"' ");
        }
        if (begin_time!=null){
            s1.append(" and a.begin_time>'"+this.begin_time+"' ");
        }
        if (end_time!=null){
            s1.append(" and a.begin_time<'"+this.end_time+"' ");
        }
        if (use_amount!=null){
            s2.append(" and a.use_amount<"+this.use_amount);
        }
        if (use_amount!=null){
            s3.append(" and a.use_amount<"+this.use_amount);
        }
        int total = waterService.queryErrorwaterTotal(s1.toString(), s3.toString());
        int start = (page - 1) * rows;// 每页的起始下标
        s2.append(" limit "+start+", "+rows+";");
        List<ErrorWaterPay> pp = this.waterService.queryErrorPay(s1.toString(), s2.toString());
        System.out.println(pp);
        if (pp == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", pp);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    // 根据水表ID查宿舍
    public String queryDom(){
        JSONObject resultJson = new JSONObject();
        if (user_id==null){
            resultJson.put("msg", "无水表id");
            ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
            return null;
        }
        System.out.println(user_id);
        String sqlx = " and user_id in ( '" + user_id.replaceAll(",", "','") +"')";
        System.out.println(sqlx);
        List<Build_water> bw = waterService.queryByUserId(sqlx);
        resultJson.put("msg", "获取成功");
        resultJson.put("data", bw);
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }

    public String queryRe(){
        JSONObject resultJson = new JSONObject();
        StringBuilder sql = new StringBuilder("select * from `recharge` where pay_state=2 ");
        StringBuilder sql2 = new StringBuilder("select count(*) from `recharge` where pay_state=2 ");
        StringBuilder sql3 = new StringBuilder("select sum(account) from `recharge` where pay_state=2 ");
        StringBuilder sql4 = new StringBuilder("select AVG(account) from `recharge` where pay_state=2 ");
        if (begin_time != null ){
            sql.append(" and re_time > '"+begin_time+"'");
            sql2.append(" and re_time > '"+begin_time+"'");
            sql3.append(" and re_time > '"+begin_time+"'");
            sql4.append(" and re_time > '"+begin_time+"'");
        }
        if (end_time != null ){
            sql.append(" and re_time < '"+end_time+" 24:60:60'");
            sql2.append(" and re_time < '"+end_time+" 24:60:60'");
            sql3.append(" and re_time < '"+end_time+" 24:60:60'");
            sql4.append(" and re_time < '"+end_time+" 24:60:60'");
        }
        System.out.println(sql);
        if (user_name != null ){
            sql.append(" and user_name like '"+user_name+"%'");
            sql2.append(" and user_name like '"+user_name+"%'");
            sql3.append(" and user_name like '"+user_name+"%'");
            sql4.append(" and user_name like '"+user_name+"%'");
        }
        int total = waterService.queryReTotal(sql2.toString());
        long totalAccount = waterService.queryReOther(sql3.toString());
        long avgAccount = waterService.queryReOther(sql4.toString());
        int start = (page - 1) * rows;// 每页的起始下标
        sql.append(" limit "+start+", "+rows+";");
        List<Recharge> pp = this.waterService.queryByTime(sql.toString());
        System.out.println(pp);
        if (pp == null) {
            resultJson.put("rows", "");
            resultJson.put("total", 0);
        } else {
            resultJson.put("rows", pp);
            // int total = listAll.size();
            resultJson.put("total", total);// 总记录数
            int totalPage = total % rows == 0 ? (total / rows)
                    : (total / rows) + 1;// 总页数
            resultJson.put("totalPage", totalPage);
            resultJson.put("totalAccount", totalAccount);
            resultJson.put("avgAccount", avgAccount);
            resultJson.put("currentPage", page);// 当前页
            resultJson.put("numPerPage", rows);// 每页数
            resultJson.put("nextPage", totalPage - page == 0 ? page : page + 1);// 下一页
            resultJson.put("previousPage", page - 0 == 1 ? page : page - 1);// 上一页
            resultJson.put("hasPreviousPage", true);// 有上一页
            resultJson.put("hasNextPage", true);// 有下一页
            resultJson.put("firstPage", true);// 首页
            resultJson.put("lastPage", true);// 尾页
        }
        ResponseUtil.writeJson(ServletActionContext.getResponse(), resultJson.toString());
        return null;
    }
}