waterElectricityService/PropelDaoImpl.java

package com.happy.dao.impl;

import com.alibaba.fastjson.JSONObject;
import com.happy.Model.*;
import com.happy.dao.PropelDao;
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.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Repository("PropelDao")
public class PropelDaoImpl implements PropelDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    public List<JSONObject> query(){
        List<JSONObject> lj = new ArrayList<JSONObject>();
        String build = "case when build='1栋' then 'qs01' \n" +
                "when build='2栋' then 'qs02' \n" +
                "when build='3栋' then 'qs03'\n" +
                "when build='4栋' then 'qs04'\n" +
                "when build='5栋' then 'qs05'\n" +
                "when build='6栋' then 'qs06'\n" +
                "when build='7栋' then 'qs07'\n" +
                "when build='8栋' then 'qs08'\n" +
                "when build='9栋' then 'qs09'\n" +
                "when build='10栋' then 'qs10'\n" +
                "when build='11栋' then 'qs11'\n" +
                "when build='12栋' then 'qs12' end as build";
        String sql = "select "+build+", CONVERT(SUM(use_size), decimal(10, 2)) as size from (select a.id, a.`begin_time`, a.`device_code`, a.`use_size`, b.`build` from `consume` a  left join `build_water` b on a.`device_code`=b.`user_id` where b.build is not null ) a group by build";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Propel> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<Propel>(Propel.class));
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = sf.format(new Date());
        if (list != null && list.size() > 0) {
            for (int i=0; i<list.size();i++){
                JSONObject json = new JSONObject();
                json.put("buildCode", list.get(i).getBuild());
                json.put("currentAggr", list.get(i).getSize());
                json.put("energyType", "3");
                json.put("updateTime", time);
                lj.add(json);
            }
            return lj;
        }
        return null;
    }

    public List<Build_elec> queryEt(){
        String sql="select * from build_elec where pointid!='0'";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Build_elec> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Build_elec>(Build_elec.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public int updateUseSize(String pointid, double use_size){
        String sql = "update build_elec set use_size=:use_size where pointid=:pointid";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("use_size", use_size);
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    public List<JSONObject> queryElecT(){
        List<JSONObject> lj = new ArrayList<JSONObject>();
        String build = "case when build='1栋' then 'qs01' \n" +
                "when build='2栋' then 'qs02' \n" +
                "when build='3栋' then 'qs03'\n" +
                "when build='4栋' then 'qs04'\n" +
                "when build='5栋' then 'qs05'\n" +
                "when build='6栋' then 'qs06'\n" +
                "when build='7栋' then 'qs07'\n" +
                "when build='8栋' then 'qs08'\n" +
                "when build='9栋' then 'qs09'\n" +
                "when build='10栋' then 'qs10'\n" +
                "when build='11栋' then 'qs11'\n" +
                "when build='12栋' then 'qs12' end as build";
        String sql = "select "+build+", CONVERT(SUM(use_size), decimal(10, 2)) as use_size from `build_elec` group by build";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<ElecTotal> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<ElecTotal>(ElecTotal.class));
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = sf.format(new Date());
        if (list != null && list.size() > 0) {
            for (int i=0; i<list.size();i++){
                JSONObject json = new JSONObject();
                json.put("buildCode", list.get(i).getBuild());
                json.put("currentAggr", list.get(i).getUse_size());
                json.put("energyType", "1");
                json.put("updateTime", time);
                lj.add(json);
            }
            return lj;
        }
        return null;
    }

    // 查询水表信息
    public List<WaterTj> queryWaterTj(){
        String sql="select * from waterTongji where `state`=1";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<WaterTj> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<WaterTj>(WaterTj.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    // 更新冷水使用
    public int updateWaterUseSize(String pointid, double use_size){
        String sql = "update waterTongji set use_size=:use_size where pointid=:pointid";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("use_size", use_size);
        return namedParameterJdbcTemplate.update(sql, sps);
    }

    // 按楼栋查水
    public List<JSONObject> queryWaterT(){
        List<JSONObject> lj = new ArrayList<JSONObject>();
        String build = "case when build='1栋' then 'qs01' \n" +
                "when build='2栋' then 'qs02' \n" +
                "when build='3栋' then 'qs03'\n" +
                "when build='4栋' then 'qs04'\n" +
                "when build='5栋' then 'qs05'\n" +
                "when build='6栋' then 'qs06'\n" +
                "when build='7栋' then 'qs07'\n" +
                "when build='8栋' then 'qs08'\n" +
                "when build='9栋' then 'qs09'\n" +
                "when build='10栋' then 'qs10'\n" +
                "when build='11栋' then 'qs11'\n" +
                "when build='12栋' then 'qs12' end as build";
        String sql = "select "+build+", CONVERT(SUM(use_size), decimal(10, 2)) as use_size from `waterTongji` group by build";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<ElecTotal> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<ElecTotal>(ElecTotal.class));
        SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = sf.format(new Date());
        if (list != null && list.size() > 0) {
            for (int i=0; i<list.size();i++){
                JSONObject json = new JSONObject();
                json.put("buildCode", list.get(i).getBuild());
                json.put("currentAggr", list.get(i).getUse_size());
                json.put("energyType", "2");
                json.put("updateTime", time);
                lj.add(json);
            }
            return lj;
        }
        return null;
    }

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

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

}