package com.happy.dao.impl;

import com.happy.Model.*;
import com.happy.dao.ElecDao;
import org.apache.commons.fileupload.util.LimitedInputStream;
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("ElecDao")
public class ElecDaoImpl implements ElecDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    public List<Build_elec> querySchool(){
        String sql = "select distinct school as school from `build_elec`;";
        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 List<Build_elec> queryBuild(String school){
        String sql = "select distinct build as build from `build_elec` where school=:school;";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("school", school);
        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 List<Build_elec> queryFloors(String school, String build){
        String sql = "select distinct floors as floors from `build_elec` where school=:school and build=:build;";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("school", school);
        sps.addValue("build", build);
        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 List<Build_elec> queryDom(String school, String build, String floors){
        String sql = "select distinct dom as dom from `build_elec` where school=:school and build=:build and floors=:floors;";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("school", school);
        sps.addValue("build", build);
        sps.addValue("floors", floors);
        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 List<Build_elec> queryUser_id(String school, String dom){
        String sql = "select * from `build_elec` where school=:school and dom=:dom;";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("school", school);
        sps.addValue("dom", dom);
        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 List<Build_elec> queryBuildByDom(String dom){
        String sql = "select * from `build_elec` where dom=:dom;";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("dom", dom);
        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 List<Build_elec> queryBuildByPoint(String pointid){
        String sql = "select * from `build_elec` where pointid=:pointid;";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        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 List<Build_elec> queryAll(){
        String sql = "select * from `build_elec` ";
        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 List<Detail_elc> queryDetailAll(){
        String sql = "select * from `detail_elc` ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Detail_elc> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Detail_elc>(Detail_elc.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    // 查询未更新使用电费详情
    public List<Detail_elc> queryUse(){
        String sql = "select * from `detail_elc` where use_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Detail_elc> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Detail_elc>(Detail_elc.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public int insertDetail(Detail_elc detail_elc){
        String sql = "insert into detail_elc(build, dom, pointid, dataTime, bm) values(:build, :dom, :pointid, :dataTime, :bm)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("build", detail_elc.getBuild());
        sps.addValue("dom", detail_elc.getDom());
        sps.addValue("pointid", detail_elc.getPointid());
        sps.addValue("dataTime", detail_elc.getDataTime());
        sps.addValue("bm", detail_elc.getBm());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public List<Detail_elc> queryByTimeAndPoint(String pointid, String dataTime){
        String sql = "select * from `detail_elc` where pointid=:pointid and dataTime=:dataTime ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("dataTime", dataTime);
        List<Detail_elc> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Detail_elc>(Detail_elc.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public int updateUse(String pointid, String dataTime, double use_elc){
        String sql = "update `detail_elc` set use_elc=:use_elc, use_state=2 where pointid=:pointid and dataTime=:dataTime and use_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("dataTime", dataTime);
        sps.addValue("use_elc", use_elc);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateUseState(String pointid, String dataTime){
        String sql = "update `detail_elc` set use_state=2 where pointid=:pointid and dataTime=:dataTime and use_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("dataTime", dataTime);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    // 查询每户每天充值记录
    public List<Recharge_elc> queryByPointAndDay(String pointid, String day_time){
        String sql = "select * from recharge_elc where pointid=:pointid and day_time=:day_time ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("day_time", day_time);
        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 List<Detail_elc> queryRe(){
        String sql = "select * from `detail_elc` where re_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Detail_elc> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Detail_elc>(Detail_elc.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    // 更新电费充值详情
    public int updateRe(String pointid, String dataTime, double recharge_elc){
        String sql = "update `detail_elc` set recharge_elc=:recharge_elc, re_state=2 where pointid=:pointid and dataTime=:dataTime and re_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("dataTime", dataTime);
        sps.addValue("recharge_elc", recharge_elc);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    // 更新电费充值详情
    public int updateReState(String pointid, String dataTime){
        String sql = "update `detail_elc` set re_state=2 where pointid=:pointid and dataTime=:dataTime and re_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("dataTime", dataTime);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    // 查询当日剩余电量详情
    public List<Detail_elc> queryUsable(){
        String sql = "select * from `detail_elc` where usable_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Detail_elc> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Detail_elc>(Detail_elc.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    // 更新电费剩余详情
    public int updateUsable(String pointid, String dataTime, double usable_elc){
        String sql = "update `detail_elc` set usable_elc=:usable_elc, usable_state=2 where pointid=:pointid and dataTime=:dataTime and usable_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("dataTime", dataTime);
        sps.addValue("usable_elc", usable_elc);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateUsableState(String pointid, String dataTime){
        String sql = "update `detail_elc` set usable_state=2 where pointid=:pointid and dataTime=:dataTime and usable_state=1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("pointid", pointid);
        sps.addValue("dataTime", dataTime);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public List<Detail_elc2> queryDayPower(String dom, String sqlx){
        String sql = "select * from `detail_elc` where dom=:dom " + sqlx+" order by `dataTime` asc ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("dom", dom);
        List<Detail_elc2> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Detail_elc2>(Detail_elc2.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

}