package com.happy.dao.impl;

import com.happy.Model.Data2;
import com.happy.Model.Data3;
import com.happy.dao.IOschoolDao;
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.Service;

import java.util.List;

@Service("InschoolDao")
public class IOschoolDaoImpl implements IOschoolDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    public List<Data2> queryInSchool(){
        String sql="select * from inschool where ts_state='1' ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Data2> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Data2>(Data2.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public List<Data3> queryOutSchool(){
        String sql="select * from outschool where ts_state='1' ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        List<Data3> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Data3>(Data3.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public int updateInState(Integer id){
        String sql = "update inschool set `ts_state`=2 where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public int updateOutState(Integer id){
        String sql = "update outschool set `ts_state`=2 where id=:id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id", id);
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    public List<Data2> combGetInSchoolList(String sqlx, int page, int rows){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql="select * from inschool " + sqlx + " limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Data2> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Data2>(Data2.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public int findCombInschoolTotal(String sqlx) {
        String sql = "select count(*) from inschool " + sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public List<Data3> combGetOutSchoolList(String sqlx, int page, int rows){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql="select * from outschool " + sqlx + " limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Data3> list=namedParameterJdbcTemplate.query(sql, sps,new BeanPropertyRowMapper<Data3>(Data3.class));
        if(list != null && list.size() >0){
            return list;
        }
        return null;
    }

    public int findCombOutschoolTotal(String sqlx) {
        String sql = "select count(*) from outschool " + sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

}