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

package com.happy.dao.impl;

import com.happy.Model.Booking;
import com.happy.Model.Holiday;
import com.happy.Model.HouseNumber;
import com.happy.Model.HouseNumberStatus;
import com.happy.Until.BeanMapTool;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.HouseNumberDao;
import com.happy.dao.HouseNumberStatusDao;
import com.happy.dto.HouseNumberStatusDto;
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.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

@Repository("HouseNumberStatusDao")
public class HouseNumberStatusImplDao implements HouseNumberStatusDao {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    /**
     * 按年份删除节假日数据
     *
     * @param year 年份
     * @return
     */
    @Override
    public int deleteHolidayByYear(String year) {
        String sql = "delete from holiday where year = :year";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("year", year);
        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(sql, sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    /**
     * 2023-09-20 a-jax 批量新增节假日数据
     *
     * @param list
     * @return
     */
    @Override
    public int saveHolidayBatch(List<Holiday> list) {
        SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(list.toArray());
        String insertSql = "INSERT INTO holiday (id, year, date, is_rest, name)" +
                " VALUES " +
                " (:id,:year,:date,:isRest,:name) ";
        List<MapSqlParameterSource> houseNumberList = new ArrayList<>();
        for (Holiday h1 : list) {
            MapSqlParameterSource mapSqlParameterSource = new MapSqlParameterSource();
            mapSqlParameterSource.addValue("id", h1.getId());
            mapSqlParameterSource.addValue("year", h1.getYear());
            mapSqlParameterSource.addValue("date", h1.getDate());
            mapSqlParameterSource.addValue("isRest", h1.getIsRest());
            mapSqlParameterSource.addValue("name", h1.getName());
            houseNumberList.add(mapSqlParameterSource);
        }
        int[] m = namedParameterJdbcTemplate.batchUpdate(insertSql, houseNumberList.toArray(new SqlParameterSource[0]));
        return m.length;
    }


    /**
     * 根据开始时间和年份查询节假日数据
     */
    public List<Holiday> queryHolidays(String year, String startDate, String endDates) {
        String sql = "select * from holiday where date >=:startDate and date <=:endDate and year =:year order by date";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("startDate", startDate);
        sps.addValue("endDate", endDates);
        sps.addValue("year", year);
        List<Holiday> list = namedParameterJdbcTemplate.query(sql, sps, new BeanPropertyRowMapper<>(Holiday.class));

        if (list != null && list.size() > 0) {
            return list;
        }
        return null;
    }


    @Override
    public int saveBatch(List<HouseNumberStatus> list) {
        SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(list.toArray());
        String insertSql = "INSERT INTO `house_number_status` (`id`, `number_id`, `booking_id`, `close_type`, `remark`, `set_date`, `create_id`, `create_date` , `modify_date` , `status`  )" +
                " VALUES " +
                " (:id,:numberId,:bookingId,:closeType,:remark,:setDate,:createId,:createDate,:modifyDate,:status) ";
        int[] m = namedParameterJdbcTemplate.batchUpdate(insertSql, params);
        return m.length;
    }

    @Override
    public int updateHouseNumberStatus(String bookId) {
        StringBuffer stringBuffer = new StringBuffer("update house_number_status set status = :status, modify_date = :modifyDate");
        MapSqlParameterSource sps = new MapSqlParameterSource();
        stringBuffer.append("  where booking_id=:booking_id  ");
        sps.addValue("status", 0);
        sps.addValue("booking_id", bookId);
        sps.addValue("modifyDate", UUIDUtil.getNewDate());
        int num = 0;
        try {
            num = namedParameterJdbcTemplate.update(stringBuffer.toString(), sps);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public void updateStatus(HouseNumberStatusDto houseNumberStatusDto) {
        StringBuilder sql = new StringBuilder("update house_number_status set status = :status, modify_date = :modifyDate");
        if (StringUtils.isNotBlank(houseNumberStatusDto.getRemark())) {
            sql.append(", remark = :remark");
        }
        sql.append(" where status != 0");
        Map<String, Object> paramMap = buildParamMap(houseNumberStatusDto);
        houseNumberStatusDto.setStatus(null);
        StringBuilder sqlLast = buildSql(sql.toString(), "", houseNumberStatusDto);
        namedParameterJdbcTemplate.update(sqlLast.toString(), paramMap);
    }

    @Override
    public List<HouseNumberStatusDto> queryList(HouseNumberStatusDto houseNumberStatusDto) {
        final String SQL_HEAD = "select id, number_id, booking_id, close_type, remark, date_format(set_date, '%Y-%m-%d') as set_date, create_id, date_format(create_date, '%Y-%m-%d %H:%i:%s') as create_date, date_format(modify_date, '%Y-%m-%d %H:%i:%s') as modify_date, status from house_number_status where 1 = 1";
        final String SQL_TAIL = "order by create_date desc ";
        Map<String, Object> paramMap = buildParamMap(houseNumberStatusDto);
        StringBuilder sql = buildSql(SQL_HEAD, SQL_TAIL, houseNumberStatusDto);
        return namedParameterJdbcTemplate.query(sql.toString(), paramMap, new BeanPropertyRowMapper<>(HouseNumberStatusDto.class));
    }

    @Override
    public List<HouseNumberStatusDto> queryOrderSomeColumnListByIds(List<String> ids) {
        final String sql = "select id as bookingId, order_num, user_name as orderUserName, user_phone as orderUserPhone, pay_account as orderPayAccount, date_format(order_start_time, '%Y-%m-%d %H:%i:%s') as order_start_time, date_format(order_end_time, '%Y-%m-%d %H:%i:%s') as order_end_time from booking where id in (:ids)";
        return namedParameterJdbcTemplate.query(sql, new MapSqlParameterSource() {{
            addValue("ids", ids);
        }}, new BeanPropertyRowMapper<>(HouseNumberStatusDto.class));
    }

    @Override
    public List<HouseNumberStatus> getList(String sqlx) {
        SqlUtil.filterKeyword(sqlx);
        String sql = "select id, number_id, booking_id, close_type, remark, date_format(set_date, '%Y-%m-%d') as set_date, create_id, date_format(create_date, '%Y-%m-%d %H:%i:%s') as create_date, date_format(modify_date, '%Y-%m-%d %H:%i:%s') as modify_date, status from house_number_status where 1 = 1 and status != 0" + sqlx;
        List<HouseNumberStatus> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, new BeanPropertyRowMapper<>(HouseNumberStatus.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) return list;
        return null;
    }

    private StringBuilder buildSql(String sqlHead, String sqlTail, HouseNumberStatusDto houseNumberStatusDto) {
        StringBuilder sql = new StringBuilder(sqlHead);
        if (StringUtils.isNotBlank(houseNumberStatusDto.getNumberId())) {
            sql.append(" and number_id = :numberId");
        }
        if (houseNumberStatusDto.getHouseNumberIds() != null && !houseNumberStatusDto.getHouseNumberIds().isEmpty()) {
            sql.append(" and number_id in (:houseNumberIds)");
        }
        String setDate = houseNumberStatusDto.getSetDate();
        if (StringUtils.isNotBlank(setDate)) {
            String[] setDateArr = setDate.split(",");
            if (setDateArr.length == 1) {
                sql.append(" and date_format(set_date, '%Y-%m-%d') = :setDate");
            } else {
                sql.append(" and (date_format(set_date, '%Y-%m-%d')")
                        .append(String.format(" between :%s and :%s)", HousePriceDaoImpl.KEY_START_SET_DATE, HousePriceDaoImpl.KEY_END_SET_DATE));
            }
        }
        if (houseNumberStatusDto.getStatus() != null) {
            sql.append(" and status = :status");
        }
        if (houseNumberStatusDto.getStatuses() != null && !houseNumberStatusDto.getStatuses().isEmpty()) {
            sql.append(" and status in (:statuses)");
        }
        sql.append(sqlTail);
        return sql;
    }

    private Map<String, Object> buildParamMap(HouseNumberStatusDto houseNumberStatusDto) {
        Map<String, Object> paramMap = BeanMapTool.beanToMap(houseNumberStatusDto);
        String setDate = houseNumberStatusDto.getSetDate();
        if (StringUtils.isNotBlank(setDate) && setDate.split(",").length == 2) {
            String[] setDateArr = setDate.split(",");
            paramMap.put(HousePriceDaoImpl.KEY_START_SET_DATE, setDateArr[0]);
            paramMap.put(HousePriceDaoImpl.KEY_END_SET_DATE, setDateArr[1]);
        }
        return paramMap;
    }
}