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

package com.happy.dao.impl;

import com.happy.Model.Booking;
import com.happy.Model.HotelCoupon;
import com.happy.Model.HouseNumber;
import com.happy.Until.BeanMapTool;
import com.happy.Until.UUIDUtil;
import com.happy.dao.HouseNumberDao;
import com.happy.dto.HouseNumberDto;
import org.apache.commons.lang.StringUtils;
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.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.awt.print.Book;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

@Repository("HouseNumberDao")
public class HouseNumberImplDao implements HouseNumberDao {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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


    @Override
    public int saveBatch(List<HouseNumber> list) {
        SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(list.toArray());
        String insertSql = "INSERT INTO `house_number` (`id`, `house_id`, `number_name`, `create_id`, `create_date`, `modify_date`, `status` )" +
                " VALUES " +
                " (:id,:house_id,:number_name,:create_id,:create_date,:modify_date,:status) ";
        List<MapSqlParameterSource> houseNumberList = new ArrayList<>();
        for (HouseNumber h1 : list) {
            MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
            mapSqlParameterSource.addValue("id", String.valueOf(UUID.randomUUID()));
            mapSqlParameterSource.addValue("house_id", h1.getHouseId());
            mapSqlParameterSource.addValue("number_name", h1.getNumberName());
            mapSqlParameterSource.addValue("create_id", h1.getCreateId());
            mapSqlParameterSource.addValue("create_date", UUIDUtil.getNewDate());
            mapSqlParameterSource.addValue("modify_date", UUIDUtil.getNewDate());
            mapSqlParameterSource.addValue("status", h1.getStatus());
            houseNumberList.add(mapSqlParameterSource);
        }
        int[] m = namedParameterJdbcTemplate.batchUpdate(insertSql, houseNumberList.toArray(new SqlParameterSource[0]));
        return m.length;
    }

    @Override
    public int updateBatch(List<HouseNumber> list) {
        HouseNumber h1 = list.get(0);
        String delSql = "update `house_number` set status=0 where house_id=:houseId";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("houseId", h1.getHouseId());
        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(delSql, sps);
            this.saveBatch(list);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<HouseNumber> queryHouseStatus(Booking book) {
        String sql = "select `id`,`house_id`,`number_name`,`create_id`,`create_date`,`modify_date`,IF(b.number_id is not null, 3, `status`) as `status` from \n" +
                "(select * from house_number where house_id = :house_id and status != 0) a\n" +
                "inner join (select number_id from house_number_status where DATE_FORMAT(set_date,'%Y-%m-%d') >= :order_start_time and DATE_FORMAT(set_date,'%Y-%m-%d') <= :order_end_time and booking_id != :booking_id and `status` > 1 group by number_id) b on a.id = b.number_id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("house_id", book.getHouseId());
        sps.addValue("booking_id", book.getId());
        sps.addValue("order_start_time", book.getOrderStartTime().substring(0, 10));
        sps.addValue("order_end_time", book.getOrderEndTime().substring(0, 10));
        List<HouseNumber> list = new ArrayList<>();
        try {
            list = namedParameterJdbcTemplate.query(sql, sps, new BeanPropertyRowMapper<>(HouseNumber.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) return list;
        return null;
    }

    /**
     * ??????
     */
    @Override
    public List<HouseNumber> queryCheckRoom(Booking book) {
        String sql = "select `id`,`house_id`,`number_name`,`create_id`,`create_date`,`modify_date`from\n" +
                     "(select * from house_number where house_id = :house_id and status != 0) a\n" +
                     "where not exists\n" +
                     "(select number_id from house_number_status where DATE_FORMAT(set_date,'%Y-%m-%d') >= :order_start_time and DATE_FORMAT(set_date,'%Y-%m-%d') <= :order_end_time and booking_id != :booking_id and `status` >1 and number_id = a.id group by number_id)" +
                     "order by number_name asc";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("house_id", book.getHouseId());
        sps.addValue("booking_id", book.getId());
        sps.addValue("order_start_time", book.getOrderStartTime().substring(0, 10));
        sps.addValue("order_end_time", book.getOrderEndTime().substring(0, 10));
        List<HouseNumber> list = new ArrayList<>();
        try {
            list = namedParameterJdbcTemplate.query(sql, sps, new BeanPropertyRowMapper<>(HouseNumber.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) return list;
        return null;
    }


    @Override
    public List<HouseNumberDto> queryListOne(HouseNumberDto houseNumberDto) {
        final String SQL_HEAD = "select id, house_id, number_name, create_id, create_date, modify_date, status, house_status from house_number where status in (1, 2) and house_id in (:houseIdList)";
        final String SQL_TAIL = " order by number_name";
        StringBuilder sql = new StringBuilder(SQL_HEAD);
        if (houseNumberDto.getHouseStatus() != null) sql.append(" and house_status = :houseStatus");
        sql.append(SQL_TAIL);
        return namedParameterJdbcTemplate.query(sql.toString(), BeanMapTool.beanToMap(houseNumberDto), new BeanPropertyRowMapper<>(HouseNumberDto.class));
    }

    @Override
    public void modifyHouseStatus(HouseNumberDto houseNumberDto) {
        if (StringUtils.isBlank(houseNumberDto.getId()) && (houseNumberDto.getIdList() == null || houseNumberDto.getIdList().isEmpty()))
            return;
        final String SQL_HEAD = "update house_number set house_status = :houseStatus, modify_date = :modifyDate";
        StringBuilder sql = new StringBuilder(SQL_HEAD);
        if (StringUtils.isNotBlank(houseNumberDto.getId())) sql.append(" where id = :id");
        if (houseNumberDto.getIdList() != null && !houseNumberDto.getIdList().isEmpty())
            sql.append(" where id in (:idList)");
        namedParameterJdbcTemplate.update(sql.toString(), BeanMapTool.beanToMap(houseNumberDto));
    }

    @Override
    public List<HouseNumber> queryByHouseId(String houseId) {
        final String sql = "select id, house_id, number_name, create_id, create_date, modify_date, status from house_number where status != 0 and house_id = :houseId order by number_name";
        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource() {{
            addValue("houseId", houseId);
        }}, new BeanPropertyRowMapper<>(HouseNumber.class));
    }

    @Override
    public int updateHouseStatus(String ids) {
        String delSql = "update `house_number` set house_status=2 where id in (:ids)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("ids", ids);
        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(delSql, sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }
}