Homestay_app_houtai/mhotel/src/com/happy/dao/impl/HouseImplDao.java

package com.happy.dao.impl;

import com.happy.Model.Admin;
import com.happy.Model.Hotel;
import com.happy.Model.House;
import com.happy.Model.House;
import com.happy.Until.Func;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.HouseDao;
import com.happy.dto.HouseSumEto;
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("HouseDao")
public class HouseImplDao implements HouseDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    @Override
    public int insertHouse(House house) {
        String sql = "INSERT INTO house (id, manager_id, h_name, h_areas, price, number, h_config, remark, create_id, create_date, status) VALUES (:id, :manager_id, :h_name, :h_areas, :price, :number, :h_config, :remark, :create_id, :create_date, :status)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("manager_id",house.getManagerId());
        sps.addValue("h_name",house.gethName());
        sps.addValue("h_areas",house.gethAreas());
        sps.addValue("price",house.getPrice());
        sps.addValue("number",house.getNumber());
        sps.addValue("h_config",house.gethConfig());
        sps.addValue("remark",house.getRemark());
        sps.addValue("create_id",house.getCreateId());
        sps.addValue("create_date",UUIDUtil.getNewDate());
        sps.addValue("status",1);
        if(house.getId()==null){
            sps.addValue("id", UUIDUtil.generateID());
        }else{
            sps.addValue("id", house.getId());

        }
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int updateHouse(House house) {
        StringBuffer stringBuffer =  new StringBuffer(" update `house` set  ");
        MapSqlParameterSource sps = new MapSqlParameterSource();
        // 将要修改的数据填充到查询语句中
        appendValue(house,stringBuffer,sps);
        stringBuffer.append("  where id=:id  ");
        sps.addValue("id", house.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(stringBuffer.toString(), sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public int delHouse(int id) {
        String sql = "update `house` set status = 0 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;
    }

    @Override
    public House getById(int id) {
        String sql = "SELECT * FROM `house` WHERE id = :id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",id);
        List<House> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(House.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }

    @Override
    public List<House> queryPage(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "SELECT * FROM `house` WHERE status=1 "+sqlx+" ORDER BY id DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<House> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(House.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }

    @Override
    public int queryTotal(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "SELECT count(*) FROM `house` where status=1 "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<House> queryList(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "SELECT * FROM `house` WHERE status=1 "+sqlx;
        List<House> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(House.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public List<HouseSumEto> queryPageHouseSum(String sqlx1, String sqlx2, int page, int rows){
        int start = (page - 1) * rows;// 每页的起始下标
        String sql ="SELECT :order_start_time order_start_time,a.h_name houseName,a.number house_num,a.number-ifnull(b.house_order_number,0) house_residue_num,ifnull(b.house_lock_num,0) house_lock_num,ifnull(b.house_due_num,0) house_due_num,ifnull(b.house_order_number,0) house_order_number " +
                "FROM house a left join ( SELECT hotel_manager_id manager_id, house_id, sum( CASE WHEN order_status IN ( 1, 2 ) THEN house_order_number ELSE 0 END ) house_lock_num, sum( CASE WHEN order_status = 3 THEN house_order_number ELSE 0 END ) house_due_num, sum( house_order_number ) house_order_number FROM booking  WHERE order_status IN ( 1, 2, 3, 4 ) and DATE_FORMAT(order_start_time,'%Y-%m-%d') = :order_start_time GROUP BY hotel_manager_id, house_id ) b ON b.house_id = a.id WHERE a.status != 0 "+sqlx2+" ORDER BY a.h_name limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("order_start_time", sqlx1);
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<HouseSumEto> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(HouseSumEto.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }

    @Override
    public int queryPageHouseSumTotal(String sqlx1, String sqlx2){
        SqlUtil.filterKeyword(sqlx1);
        SqlUtil.filterKeyword(sqlx2);
        String sql ="SELECT count(1) " +
                "FROM house a left join ( SELECT hotel_manager_id manager_id, house_id, sum( CASE WHEN order_status IN ( 1, 2 ) THEN house_order_number ELSE 0 END ) house_lock_num, sum( CASE WHEN order_status = 3 THEN house_order_number ELSE 0 END ) house_due_num, sum( house_order_number ) house_order_number FROM booking  WHERE order_status IN ( 1, 2, 3, 4 ) and DATE_FORMAT(order_start_time,'%Y-%m-%d') = :order_start_time GROUP BY hotel_manager_id, house_id ) b ON b.house_id = a.id WHERE a.status != 0 "+sqlx2;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("order_start_time", sqlx1);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public int getHouseSum(int managerId){
        String sql = "select sum(number) from house where status = 1 and manager_id = :manager_id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("manager_id",managerId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    private void appendValue(House house, StringBuffer stringBuffer, MapSqlParameterSource sps){
        if (!Func.checkNull(String.valueOf(house.getManagerId()))){
            stringBuffer.append(" manager_id=:manager_id ,");
            sps.addValue("manager_id",house.getManagerId());
        }
        if (!Func.checkNull(house.gethName())){
            stringBuffer.append(" h_name=:h_name ,");
            sps.addValue("h_name",house.gethName());
        }
        if (!Func.checkNull(house.gethAreas())){
            stringBuffer.append(" h_areas=:h_areas ,");
            sps.addValue("h_areas",house.gethAreas());
        }
        if (!Func.checkNull(String.valueOf(house.getPrice()))){
            stringBuffer.append(" price=:price ,");
            sps.addValue("price",house.getPrice());
        }
        if (!Func.checkNull(String.valueOf(house.getNumber()))){
            stringBuffer.append(" number=:number ,");
            sps.addValue("number",house.getNumber());
        }
        if (!Func.checkNull(house.gethConfig())){
            stringBuffer.append(" h_config=:h_config ,");
            sps.addValue("h_config",house.gethConfig());
        }
        if (!Func.checkNull(house.getRemark())){
            stringBuffer.append(" remark=:remark ,");
            sps.addValue("remark",house.getRemark());
        }
        if (!Func.checkNull(String.valueOf(house.getStatus()))){
            stringBuffer.append(" status=:status ,");
            sps.addValue("status", house.getStatus());
        }
        stringBuffer.append(" modify_date=:modify_date ");
        sps.addValue("modify_date", UUIDUtil.getNewDate());
    }
}