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

package com.happy.dao.impl;

import com.happy.Model.Hotel;
import com.happy.Until.Func;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.HotelDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.EmptySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository("HotelDao")
public class HotelImplDao implements HotelDao {

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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


    @Override
    public int insertHotel(Hotel hotel) {
        String sql = "INSERT INTO hotel (id, manager_id,cover_img, hname, hposition, hposition_wens, htype, hstatus, open_time," +
                " fitup_time, live_time, leave_time, room_number, remark, hconfig, is_order, is_checkout, lock_time, is_canorder," +
                " create_id, create_date, status ,cancel_time) VALUES (:id,:manager_id,:cover_img,:hname,:hposition,:hposition_wens,:htype," +
                ":hstatus,:open_time,:fitup_time,:live_time,:leave_time,:room_number,:remark,:hconfig,:is_order,:is_checkout," +
                ":lock_time,:is_canorder, :create_id, :create_date, :status , :cancel_time)";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("manager_id",hotel.getManagerId());
        sps.addValue("cover_img",hotel.getCoverImg());
        sps.addValue("hname",hotel.getHname());
        sps.addValue("hposition",hotel.getHposition());
        sps.addValue("hposition_wens",hotel.getHpositionWens());
        sps.addValue("htype",hotel.getHtype());
        sps.addValue("hstatus",hotel.getHstatus());
        sps.addValue("open_time",hotel.getOpenTime());
        sps.addValue("fitup_time",hotel.getFitupTime());
        sps.addValue("live_time",hotel.getLiveTime());
        sps.addValue("leave_time",hotel.getLeaveTime());
        sps.addValue("room_number",hotel.getRoomNumber());
        sps.addValue("remark",hotel.getRemark());
        sps.addValue("hconfig",hotel.getHconfig());
        sps.addValue("is_order",hotel.getIsOrder());
        sps.addValue("is_checkout",hotel.getIsCheckout());
        sps.addValue("lock_time",hotel.getLockTime());
        sps.addValue("is_canorder",hotel.getIsCheckout());
        sps.addValue("create_id", hotel.getCreateId());
        sps.addValue("create_date",UUIDUtil.getNewDate());
        sps.addValue("status", 1);
        sps.addValue("cancel_time", hotel.getCancelTime());

        if(hotel.getId()==null){
            sps.addValue("id", UUIDUtil.generateID());
        }else{
            sps.addValue("id", hotel.getId());
        }
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(sql, sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

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

    @Override
    public int delHotel(int id) {
        String sql = "update `hotel` 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 Hotel getById(int id) {

        String sql = " SELECT   a.*, c.manager_phone, b.hotel_township_name,c.hotel_name,d.hTypeName,f.room_number_calc,c.link_pro_url  FROM `hotel` a  " +
                "left join (select manager_id,min(price) min_price ,sum(number) AS room_number_calc from house where status=1 group by manager_id) f on a.manager_id = f.manager_id " +
                " LEFT JOIN (SELECT   id, corpn_phone manager_phone, hotel_township,hotel_name,link_pro_url FROM  admin_manager ) c ON a.manager_id = c.id " +
                " LEFT JOIN (  SELECT  id,name as hotel_township_name  FROM  hotel_dict GROUP BY  id) b ON b.id = c.hotel_township   "+
                " LEFT JOIN (  SELECT  id,name as hTypeName  FROM  hotel_dict GROUP BY  id) d ON d.id = a.htype   "+
                " WHERE  a.STATUS = 1  AND a.id =  :id ";
//        String sql = "SELECT * FROM `hotel` WHERE id = :id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",id);
        List<Hotel> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(Hotel.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }

    @Override
    public Hotel getByManagerId(int id) {

        String sql = " SELECT   a.*, c.manager_phone, b.hotel_township_name,c.hotel_name,c.link_pro_url  FROM `hotel` a  " +
                " LEFT JOIN (SELECT   id, corpn_phone manager_phone, hotel_township,hotel_name,link_pro_url FROM  admin_manager ) c ON a.manager_id = c.id " +
                " LEFT JOIN (  SELECT  id,name as hotel_township_name  FROM  hotel_dict GROUP BY  id) b ON b.id = c.hotel_township   "+
                " WHERE  a.STATUS != 0  AND a.manager_id =  :manager_id ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("manager_id",id);
        List<Hotel> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(Hotel.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;
    }

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

//    select a.*,b.min_price from hotel a
//    left join (select manager_id,min(price) min_price from house group by manager_id) b on a.manager_id = b.manager_id  ORDER BY id DESC limit 1,3
    @Override
    public List<Hotel> queryPagePrice(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "SELECT a.*,c.hotel_township,(select Count(*) from booking where hotel_id = a.id and order_status = 5) as booking_num,b.min_price,c.hotel_township,d.hotel_township_name, e.h_type_name,c.hotel_name,c.link_pro_url,(SELECT round( AVG( score ), 1 ) as score FROM `booking_comment` bc where bc.comment_parent_id IS NULL AND bc.hotel_id = a.id AND `status` = 1) as score" +
                " FROM `hotel` a " +
                "left join (select manager_id,min(price) min_price from house where status=1 group by manager_id) b on a.manager_id = b.manager_id " +
                "left join (select id,manager_id,hotel_township,hotel_name,type,status,link_pro_url from admin_manager ) c on a.manager_id = c.id " +
                "LEFT JOIN (  SELECT  id,name as hotel_township_name,status  FROM  hotel_dict GROUP BY  id) d ON d.id = c.hotel_township " +
                "LEFT JOIN (  SELECT  id,name as h_type_name  FROM  hotel_dict GROUP BY  id) e ON e.id = a.htype      " +
                " WHERE hstatus = 1 and a.status=1 and c.status = 1 "+sqlx+" ORDER BY booking_num DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Hotel> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Hotel.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }

    /**
     * 优选 按评分倒序排序
     * @param sqlx
     * @param page
     * @param rows
     * @return
     */
    @Override
    public List<Hotel> queryPreferredPagePrice(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "SELECT a.*,(SELECT round( AVG( score ), 1 ) as score FROM booking_comment WHERE comment_parent_id IS NULL AND hotel_id = a.id AND status = 1) as score_num,b.min_price,c.hotel_township,d.hotel_township_name, e.h_type_name,c.hotel_name,c.link_pro_url,(SELECT round( AVG( score ), 1 ) as score FROM `booking_comment` bc where bc.comment_parent_id IS NULL AND bc.hotel_id = a.id AND `status` = 1) as score" +
                " FROM `hotel` a " +
                "left join (select manager_id,min(price) min_price from house where status=1 group by manager_id) b on a.manager_id = b.manager_id " +
                "left join (select id,manager_id,hotel_township,hotel_name,type,status,link_pro_url from admin_manager ) c on a.manager_id = c.id " +
                "LEFT JOIN (  SELECT  id,name as hotel_township_name,status  FROM  hotel_dict GROUP BY  id) d ON d.id = c.hotel_township " +
                "LEFT JOIN (  SELECT  id,name as h_type_name  FROM  hotel_dict GROUP BY  id) e ON e.id = a.htype      " +
                " WHERE hstatus = 1 and a.status=1 and c.status = 1 "+sqlx+" ORDER BY score_num DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Hotel> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Hotel.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }

    /**
     * 精选 按销售量倒序排序
     * @param sqlx
     * @param page
     * @param rows
     * @return
     */
    @Override
    public List<Hotel> querySelectedPagePrice(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = "SELECT a.*,(select Count(*) from booking where hotel_id = a.id and order_status = 5) as booking_num,b.min_price,c.hotel_township,d.hotel_township_name, e.h_type_name,c.hotel_name,c.link_pro_url,(SELECT round( AVG( score ), 1 ) as score FROM `booking_comment` bc where bc.comment_parent_id IS NULL AND bc.hotel_id = a.id AND `status` = 1) as score" +
                " FROM `hotel` a " +
                "left join (select manager_id,min(price) min_price from house where status=1 group by manager_id) b on a.manager_id = b.manager_id " +
                "left join (select id,manager_id,hotel_township,hotel_name,type,status,link_pro_url from admin_manager ) c on a.manager_id = c.id " +
                "LEFT JOIN (  SELECT  id,name as hotel_township_name,status  FROM  hotel_dict GROUP BY  id) d ON d.id = c.hotel_township " +
                "LEFT JOIN (  SELECT  id,name as h_type_name  FROM  hotel_dict GROUP BY  id) e ON e.id = a.htype      " +
                " WHERE hstatus = 1 and a.status=1 and c.status = 1 and c.type in ('2','3') "+sqlx+" ORDER BY booking_num DESC  ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<Hotel> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(Hotel.class));
        if (list != null && list.size() > 0) return list;
        return null;
    }

    @Override
    public int queryTotalPrice(String sqlx) {
//        String sql = "SELECT count(*) FROM`hotel` a " +
//                "left join (select manager_id,min(price) min_price from house group by manager_id) b on a.manager_id = b.manager_id " +
//                "left join (select manager_id,hotel_township hotel_township from admin_manager group by manager_id) c on a.manager_id = c.manager_id " +
//                "where status=1 "+sqlx;
        SqlUtil.filterKeyword(sqlx);
        String sql = "SELECT count(1) FROM `hotel` a " +
                "left join (select manager_id,min(price) min_price from house where status=1 group by manager_id) b on a.manager_id = b.manager_id " +
                "left join (select id,manager_id,hotel_township,hotel_name,type,status,link_pro_url from admin_manager ) c on a.manager_id = c.id " +
                "LEFT JOIN (  SELECT  id,name as hotel_township_name,status  FROM  hotel_dict GROUP BY  id) d ON d.id = c.hotel_township " +
                "LEFT JOIN (  SELECT  id,name as h_type_name  FROM  hotel_dict GROUP BY  id) e ON e.id = a.htype      " +
                " WHERE hstatus = 1 and a.status=1 and c.status = 1 and c.type in ('2','3') "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }



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

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


    public void appendValue(Hotel hotel, StringBuffer stringBuffer, MapSqlParameterSource sps){
        if (!Func.checkNull(String.valueOf(hotel.getManagerId()))){
            stringBuffer.append(" manager_id=:manager_id ,");
            sps.addValue("manager_id",hotel.getManagerId());
        }
        if (!Func.checkNull(hotel.getHname())){
            stringBuffer.append(" hname=:hname ,");
            sps.addValue("hname",hotel.getHname());
        }
        if (!Func.checkNull(hotel.getCoverImg())){
            stringBuffer.append(" cover_img=:cover_img ,");
            sps.addValue("cover_img",hotel.getCoverImg());
        }
        if (!Func.checkNull(hotel.getHposition())){
            stringBuffer.append(" hposition=:hposition ,");
            sps.addValue("hposition",hotel.getHposition());
        }
        if (!Func.checkNull(hotel.getHpositionWens())){
            stringBuffer.append(" hposition_wens=:hposition_wens ,");
            sps.addValue("hposition_wens",hotel.getHpositionWens());
        }
        if (!Func.checkNull(hotel.getHtype())){
            stringBuffer.append(" htype=:htype ,");
            sps.addValue("htype",hotel.getHtype());
        }
        if (!Func.checkNull(String.valueOf(hotel.getHstatus()))){
            stringBuffer.append(" hstatus=:hstatus ,");
            sps.addValue("hstatus",hotel.getHstatus());
        }
        if (!Func.checkNull(hotel.getOpenTime())){
            stringBuffer.append(" open_time=:open_time ,");
            sps.addValue("open_time",hotel.getOpenTime());
        }
        if (!Func.checkNull(hotel.getFitupTime())){
            stringBuffer.append(" fitup_time=:fitup_time ,");
            sps.addValue("fitup_time",hotel.getFitupTime());
        }
        if (!Func.checkNull(hotel.getLiveTime())){
            stringBuffer.append(" live_time=:live_time ,");
            sps.addValue("live_time",hotel.getLiveTime());
        }
        if (!Func.checkNull(hotel.getLeaveTime())){
            stringBuffer.append(" leave_time=:leave_time ,");
            sps.addValue("leave_time",hotel.getLeaveTime());
        }
        if (!Func.checkNull(String.valueOf(hotel.getRoomNumber()))){
            stringBuffer.append(" room_number=:room_number ,");
            sps.addValue("room_number",hotel.getRoomNumber());
        }
        if (!Func.checkNull(hotel.getRemark())){
            stringBuffer.append(" remark=:remark ,");
            sps.addValue("remark",hotel.getRemark());
        }
        if (!Func.checkNull(hotel.getHconfig())){
            stringBuffer.append(" hconfig=:hconfig ,");
            sps.addValue("hconfig",hotel.getHconfig());
        }
        if (!Func.checkNull(String.valueOf(hotel.getIsOrder()))){
            stringBuffer.append(" is_order=:is_order ,");
            sps.addValue("is_order",hotel.getIsOrder());
        }
        if (!Func.checkNull(String.valueOf(hotel.getIsCheckout()))){
            stringBuffer.append(" is_checkout=:is_checkout ,");
            sps.addValue("is_checkout",hotel.getIsCheckout());
        }
        if (!Func.checkNull(hotel.getLockTime())){
            stringBuffer.append(" lock_time=:lock_time ,");
            sps.addValue("lock_time",hotel.getLockTime());
        }
        if (!Func.checkNull(String.valueOf(hotel.getIsCanorder()))){
            stringBuffer.append(" is_canorder=:is_canorder ,");
            sps.addValue("is_canorder",hotel.getIsCanorder());
        }
        if (!Func.checkNull(String.valueOf(hotel.getStatus()))){
            stringBuffer.append(" status=:status ,");
            sps.addValue("status", hotel.getStatus());
        }
        if (!Func.checkNull(String.valueOf(hotel.getCancelTime()))){
            stringBuffer.append(" cancel_time=:cancel_time ,");
            sps.addValue("cancel_time", hotel.getCancelTime());
        }
        stringBuffer.append(" modify_date=:modify_date ");
        sps.addValue("modify_date", UUIDUtil.getNewDate());
    }

}