waterElectricityService/WaterDaoImpl.java

package com.happy.dao.impl;

import com.happy.Model.*;
import com.happy.dao.WaterDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("WaterDao")
public class WaterDaoImpl implements WaterDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

    public void setNamedParameterJdbcTemplate(
            NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
        this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
    }

    /**
     * 根据当前页和每页显示行数实现分页查询订单
     */
    public List<Build_water> getbuild(int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from build_water order by id asc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Build_water> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Build_water>(Build_water.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查询表中的总记录数
    public int findPageTotal() {
        String sql = "select count(*) from  build_water";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    // 有条件的查询
    public List<Build_water> findAll(String sqlx) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Build_water> list = this.namedParameterJdbcTemplate.query(sqlx, sps, new BeanPropertyRowMapper(Build_water.class));
        return list != null && list.size() > 0 ? list : null;
    }

    // 查询总记录数
    public int total(String sqlx) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sqlx, sps);
    }

    public int updateBuild(Build_water build_water) {
        String sql = "update build_water set build=:build, dom=:dom, user_id=:user_id where id=:id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", build_water.getId());
        sps.addValue("build", build_water.getBuild());
        sps.addValue("dom", build_water.getDom());
        sps.addValue("user_id", build_water.getUser_id());
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public int delBuild(int id) {
        String sql = "delete from build_water where id=:id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public int addBuild(Build_water build_water) {
        String sql = "insert into build_water(school, build, floors, dom, user_id) values(:school, :build, :floors, :dom, :user_id) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("school", build_water.getSchool());
        sps.addValue("build", build_water.getBuild());
        sps.addValue("dom", build_water.getDom());
        sps.addValue("floors", build_water.getFloors());
        sps.addValue("user_id", build_water.getUser_id());
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public List<Users> queryMoney(int page, int rows, String s1){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from users where 1=1 "+ s1 +" order by stu_number asc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Users> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Users>(Users.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查询用户表中的总记录数
    public int findUsersTotal(String s1) {
        String sql = "select count(*) from  users where 1=1 "+s1;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public List<Recharge> chargeRecord(String sqlx){
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Recharge> list = this.namedParameterJdbcTemplate.query(sqlx, sps, new BeanPropertyRowMapper(Recharge.class));
        return list != null && list.size() > 0 ? list : null;
    }

    public int chargeTotal(String sqlx) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sqlx, sps);
    }

    // 查询消费总记录数
    public int findComTotal(String sqlx) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sqlx, sps);
    }

    // 消费记录
    public List<Consume> findCom(String sqlx) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Consume> list = this.namedParameterJdbcTemplate.query(sqlx, sps, new BeanPropertyRowMapper(Consume.class));
        return list != null && list.size() > 0 ? list : null;
    }

    public int insertPrice(Price price){
        String sql = "insert into price(name, price, state, time, time2) values (:name, :price, :state, :time, :time2)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("name", price.getName());
        sps.addValue("price", price.getPrice());
        sps.addValue("state", price.getState());
        sps.addValue("time", price.getTime());
        sps.addValue("time2", price.getTime2());
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public int passPrice(String time2){
        String sql = "update price set state=0, time2=:time2 where time in ( select time from(select MAX(time) as time from `price` where name='热水') a )";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("time2", time2);
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public List<Price> queryPrice(int page, int rows){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from price where name='热水' order by id desc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Price> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Price>(Price.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查询表中的总记录数
    public int findPriceTotal() {
        String sql = "select count(*) from  price where name='热水'";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public List<Admin> queryAdmin(int page, int rows, String s1){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select * from admin where 1=1 "+s1+" order by id desc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Admin> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Admin>(Admin.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查询表中的总记录数
    public int findAdminTotal(String s1) {
        String sql = "select count(*) from  admin where 1=1 "+s1;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public List<Admin> queryAdminByNum(String number){
        String sql = "select * from `admin` where `number`=:number order by id desc ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("number", number);
        List<Admin> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Admin>(Admin.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    public int addAdmin(Admin admin){
        String sql = "insert into admin(name, number, password) values (:name, :number, :password)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("name", admin.getName());
        sps.addValue("number", admin.getNumber());
        sps.addValue("password", admin.getPassword());
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public int delAdmin(int id){
        String sql = "delete from admin where id=:id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public int updateAdmin(Admin admin){
        String sql = "update `admin` set `name`=:name, `number`=:number, `password`=:password where `id`=:id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("name", admin.getName());
        sps.addValue("number", admin.getNumber());
        sps.addValue("password", admin.getPassword());
        sps.addValue("id", admin.getId());
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public List<Admin> login(String number, String password){
        String sql = "select * from `admin` where `number`=:number and `password`=:password ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("number", number);
        sps.addValue("password", password);
        List<Admin> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Admin>(Admin.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查看水费异常充值
    public List<Recharge> findErrorPayByNo(int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select id, user_name, stu_number, account, SUBSTR(re_time, 1, 10) as re_time, case handler_state when 0 then '未处理' when 1 then '已处理' end as handler_state from recharge  where pay_state=1 order by id desc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Recharge> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Recharge>(Recharge.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查询水费异常总记录数
    public int findErrorPayTotal() {
        String sql = "select count(*) from recharge where pay_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    // 处理异常水费充值
    public int updateErrorWaterPay(int id){
        String sql = "update recharge set `handler_state`=1 where id=:id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    // 查看电费异常充值
    public List<Recharge_elc> findErrorElcPayByNo(int page, int rows) {
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "select id, user_name, stu_number, room, account, SUBSTR(re_time, 1, 10) as re_time, case handler_state when 0 then '未处理' when 1 then '已处理' end as handler_state from recharge_elc  where state=1 order by id desc limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Recharge_elc> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Recharge_elc>(Recharge_elc.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查询电费异常总记录数
    public int findErrorElecTotal() {
        String sql = "select count(*) from recharge_elc  where state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    // 处理异常电费充值
    public int updateErrorElecPay(int id){
        String sql = "update recharge_elc set `handler_state`=1 where id=:id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    // 查询异常用水数据
    public List<ErrorWaterPay> queryErrorPay(String s1, String s2){
        String sql = "select * from ( select b.`build`, b.`dom`, a.`begin_time`, CONVERT(SUM(use_amount), decimal(10, 2)) as use_amount from `consume` a, `build_water` b where a.`device_code`=b.`user_id` "+s1 + " group by dom order by a.`begin_time` desc ) a where 1=1 "+s2;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<ErrorWaterPay> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<ErrorWaterPay>(ErrorWaterPay.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 查询异常用水总条数
    public int queryErrorwaterTotal(String s1, String s3) {
        String sql = "select count(*) from ( select b.`build`, b.`dom`, a.`begin_time`, SUM(a.`use_amount`) as use_amount from `consume` a, `build_water` b where a.`device_code`=b.`user_id` "+s1 + " group by dom order by a.`begin_time` desc ) a where 1=1 "+s3;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public List<Build_water> queryByUserId(String sqlx){
        String sql = " select * from build_water where 1=1 " + sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Build_water> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Build_water>(Build_water.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    // 充值详情
    public List<Recharge> queryByTime(String sql){
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Recharge> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Recharge>(Recharge.class));
        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }

    public int queryReTotal(String sql) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public long queryReOther(String sql) {
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForLong(sql, sps);
    }
}