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

package com.happy.dao.impl;

import com.happy.Model.BookingComment;
import com.happy.Model.HotelCoupon;
import com.happy.Model.HouseNumber;
import com.happy.Until.Func;
import com.happy.Until.SqlUtil;
import com.happy.Until.UUIDUtil;
import com.happy.dao.BookingCommentDao;
import com.happy.vo.BookingCommentPageVo;
import com.happy.vo.EvaluatePageVo;
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.ArrayList;
import java.util.List;
import java.util.UUID;

@Repository("BookingCommentDao")
public class BookingCommentDaoImpl implements BookingCommentDao {
    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
        return namedParameterJdbcTemplate;
    }

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

    private String selectSql = "select " +
            "a.id,a.comment_id,a.comment_parent_id,a.comment_name,a.booking_id,a.hotel_id,a.house_id,a.content,a.comment_status,a.score,a.score_ws," +
            "a.score_fw,a.score_ss,a.score_wz,a.create_id,a.create_username,a.create_date,a.modify_date,a.status,b.check_out_time,c.user_name as createName"+
            " from booking_comment a" +
            " left join booking b on b.id = a.booking_id"+
            " left join users c on c.id = a.create_id" +
            " where 1=1 ";
    /**
     * ������ۣ�����������
     * @param bookingComment
     * @return
     */
    @Override
    public String insertBookingComment(BookingComment bookingComment) {
        String sql = "insert into `booking_comment`(id,comment_id,comment_parent_id,comment_name,booking_id,hotel_id,house_id,content,comment_status,score, score_ws,score_fw,score_ss,score_wz,create_id,create_username,create_date,modify_date,status) values(:id,:comment_id,:comment_parent_id,:comment_name,:booking_id,:hotel_id,:house_id,:content,:comment_status,:score,:score_ws,:score_fw,:score_ss,:score_wz,:create_id,:create_username,:create_date,:modify_date,:status) ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("comment_id", bookingComment.getCommentId());
        sps.addValue("comment_parent_id", bookingComment.getCommentParentId());
        sps.addValue("comment_name", bookingComment.getCommentName());
        sps.addValue("booking_id",bookingComment.getBookingId());
        sps.addValue("hotel_id", bookingComment.getHotelId());
        sps.addValue("house_id", bookingComment.getHouseId());
        sps.addValue("content", bookingComment.getContent());
//        ���ʱĬ�ϴ��ظ�
        sps.addValue("comment_status",bookingComment.getCommentStatus());
        sps.addValue("score", bookingComment.getScore());
        sps.addValue("score_ws", bookingComment.getScoreWs());
        sps.addValue("score_fw", bookingComment.getScoreFw());
        sps.addValue("score_ss", bookingComment.getScoreSs());
        sps.addValue("score_wz", bookingComment.getScoreWz());
        sps.addValue("create_id", bookingComment.getCreateId());
        sps.addValue("create_username",bookingComment.getCreateUsername() );
        sps.addValue("create_date",bookingComment.getCreateDate());
        sps.addValue("modify_date",bookingComment.getModifyDate());
        sps.addValue("status", 1);

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

    /**
     * ���۷�ҳ����
     * @param hotelId
     * @param page
     * @param rows
     * @return
     */
    @Override
    public List<BookingCommentPageVo>  evaluatePage(String hotelId, int page, int rows) {

        int start = (page - 1) * rows;// ÿҳ����ʼ��

        String sql="SELECT bc.id AS id, bc.score AS score ,u.user_name AS userName,u.head_photo AS headPhoto,b.live_time AS liveTime,bc.create_date AS commentTime,h.h_name AS houseName,bc.content AS content,fi2.url AS url,ifnull( cc.commentSum, 0 ) AS commentCount FROM `booking_comment` bc LEFT JOIN ( SELECT fi.link_id, GROUP_CONCAT( fi.url ) AS url FROM file_info fi GROUP BY fi.link_id ) fi2 ON fi2.link_id = bc.id LEFT JOIN users u ON u.id = bc.create_id LEFT JOIN booking b ON b.id = bc.booking_id AND status_del = 1 LEFT JOIN house h ON h.id = bc.house_id AND h.status = 1 LEFT JOIN ( SELECT comment_parent_id, count( 1 ) commentSum FROM booking_comment WHERE comment_parent_id IS NOT NULL GROUP BY comment_id AND status = 1 ) cc ON bc.id = cc.comment_parent_id WHERE bc.comment_parent_id IS NULL AND bc.status = 1 and bc.hotel_id = :hotelId  ORDER BY bc.create_date DESC LIMIT :start,:rows";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("hotelId",hotelId);
        List<BookingCommentPageVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(BookingCommentPageVo.class));
        if (list != null && list.size() > 0) return list;

        return null;
    }

    /**
     * ȫ�����۵��ܴ���
     * @param hotelId
     * @return
     */
    @Override
    public int evaluateTotal(String hotelId) {
        String sql="SELECT\n" +
                "\tcount(1) \n" +
                "FROM\n" +
                "\t`booking_comment` bc\n" +
                "\tLEFT JOIN ( SELECT fi.link_id, GROUP_CONCAT( fi.url ) AS url FROM file_info fi GROUP BY fi.link_id ) fi2 ON fi2.link_id = bc.id\n" +
                "\tLEFT JOIN users u ON u.id = bc.create_id\n" +
                "\tLEFT JOIN booking b ON b.id = bc.booking_id \n" +
                "\tAND status_del = 1\n" +
                "\tLEFT JOIN house h ON h.id = bc.house_id \n" +
                "\tAND h.`status` = 1\n" +
                "\tLEFT JOIN ( SELECT comment_parent_id, count( 1 ) commentSum FROM booking_comment WHERE comment_parent_id IS NOT NULL GROUP BY comment_id AND `status` = 1 ) cc ON bc.id = cc.comment_parent_id \n" +
                "WHERE\n" +
                "\tbc.comment_parent_id IS NULL \n" +
                "\tAND bc.`status` = 1 \n" +
                "\tAND bc.hotel_id = :hotelId \n" +
                "ORDER BY\n" +
                "\tbc.create_date DESC";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("hotelId",hotelId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    /**
     * ��ͼ�����۷�ҳ
     * @param hotelId
     * @param page
     * @param rows
     * @return
     */
    @Override
    public List<BookingCommentPageVo> evaluatePagepicture(String hotelId, int page, int rows) {
        int start = (page - 1) * rows;// ÿҳ����ʼ��

        String sql="SELECT bc.id AS id, bc.score AS score ,u.user_name AS userName,u.head_photo AS headPhoto,b.live_time AS liveTime,bc.create_date AS commentTime,h.h_name AS houseName,bc.content AS content,fi2.url AS url,ifnull( cc.commentSum, 0 ) AS commentCount FROM `booking_comment` bc INNER JOIN ( SELECT fi.link_id, GROUP_CONCAT( fi.url ) AS url FROM file_info fi GROUP BY fi.link_id ) fi2 ON fi2.link_id = bc.id LEFT JOIN users u ON u.id = bc.create_id LEFT JOIN booking b ON b.id = bc.booking_id AND status_del = 1 LEFT JOIN house h ON h.id = bc.house_id AND h.status = 1 LEFT JOIN ( SELECT comment_parent_id, count( 1 ) commentSum FROM booking_comment WHERE comment_parent_id IS NOT NULL GROUP BY comment_id AND status = 1 ) cc ON bc.id = cc.comment_parent_id WHERE bc.comment_parent_id IS NULL AND bc.status = 1 and bc.hotel_id = :hotelId  ORDER BY bc.create_date DESC LIMIT :start,:rows";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("hotelId",hotelId);
        List<BookingCommentPageVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(BookingCommentPageVo.class));
        if (list != null && list.size() > 0) return list;

        return null;
    }

    @Override
    public int evaluateTotalpicture(String hotelId) {
        String sql="SELECT\n" +
                "\tcount(1) \n" +
                "FROM\n" +
                "\t`booking_comment` bc\n" +
                "\tINNER JOIN ( SELECT fi.link_id, GROUP_CONCAT( fi.url ) AS url FROM file_info fi GROUP BY fi.link_id ) fi2 ON fi2.link_id = bc.id\n" +
                "\tLEFT JOIN users u ON u.id = bc.create_id\n" +
                "\tLEFT JOIN booking b ON b.id = bc.booking_id \n" +
                "\tAND status_del = 1\n" +
                "\tLEFT JOIN house h ON h.id = bc.house_id \n" +
                "\tAND h.`status` = 1\n" +
                "\tLEFT JOIN ( SELECT comment_parent_id, count( 1 ) commentSum FROM booking_comment WHERE comment_parent_id IS NOT NULL GROUP BY comment_id AND `status` = 1 ) cc ON bc.id = cc.comment_parent_id \n" +
                "WHERE\n" +
                "\tbc.comment_parent_id IS NULL \n" +
                "\tAND bc.`status` = 1 \n" +
                "\tAND bc.hotel_id = :hotelId \n" +
                "ORDER BY\n" +
                "\tbc.create_date DESC";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("hotelId",hotelId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    /**
     * �����̼����۵ķ�ҳ
     * @param hotelId
     * @param page
     * @param rows
     * @return
     */
    @Override
    public List<BookingCommentPageVo> evaluatePageComment(String hotelId, int page, int rows) {
        int start = (page - 1) * rows;// ÿҳ����ʼ��
        String sql="SELECT bc.id AS id, bc.score AS score ,u.user_name AS userName,u.head_photo AS headPhoto,b.live_time AS liveTime,bc.create_date AS commentTime,h.h_name AS houseName,bc.content AS content,fi2.url AS url,ifnull( cc.commentSum, 0 ) AS commentCount FROM `booking_comment` bc LEFT JOIN ( SELECT fi.link_id, GROUP_CONCAT( fi.url ) AS url FROM file_info fi GROUP BY fi.link_id ) fi2 ON fi2.link_id = bc.id LEFT JOIN users u ON u.id = bc.create_id LEFT JOIN booking b ON b.id = bc.booking_id AND status_del = 1 LEFT JOIN house h ON h.id = bc.house_id AND h.status = 1 LEFT JOIN ( SELECT comment_parent_id, count( 1 ) commentSum FROM booking_comment WHERE comment_parent_id IS NOT NULL GROUP BY comment_id AND status = 1 ) cc ON bc.id = cc.comment_parent_id WHERE bc.comment_parent_id IS NULL AND bc.status = 1 and bc.hotel_id = :hotelId and bc.comment_status=2 ORDER BY bc.create_date DESC LIMIT :start,:rows";


        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("hotelId",hotelId);
        List<BookingCommentPageVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(BookingCommentPageVo.class));
        if (list != null && list.size() > 0) return list;

        return null;
    }

    /**
     * �����̼����۵��ܴ���
     * @param hotelId
     * @return
     */
    @Override
    public int evaluateTotalComment(String hotelId) {
        String sql="SELECT\n" +
                "\tcount(1) \n" +
                "FROM\n" +
                "\t`booking_comment` bc\n" +
                "\tLEFT JOIN ( SELECT fi.link_id, GROUP_CONCAT( fi.url ) AS url FROM file_info fi GROUP BY fi.link_id ) fi2 ON fi2.link_id = bc.id\n" +
                "\tLEFT JOIN users u ON u.id = bc.create_id\n" +
                "\tLEFT JOIN booking b ON b.id = bc.booking_id \n" +
                "\tAND status_del = 1\n" +
                "\tLEFT JOIN house h ON h.id = bc.house_id \n" +
                "\tAND h.`status` = 1\n" +
                "\tLEFT JOIN ( SELECT comment_parent_id, count( 1 ) commentSum FROM booking_comment WHERE comment_parent_id IS NOT NULL GROUP BY comment_id AND `status` = 1 ) cc ON bc.id = cc.comment_parent_id \n" +
                "WHERE\n" +
                "\tbc.comment_parent_id IS NULL \n" +
                "\tAND bc.`status` = 1 \n" +
                "\tAND bc.hotel_id = :hotelId \n" +
                "\tAND bc.comment_status=2 \n" +
                "ORDER BY\n" +
                "\tbc.create_date DESC";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("hotelId",hotelId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public EvaluatePageVo evaluateScore(String hotelId) {
        String sql="SELECT\n" +
                "\tround( AVG( score ), 1 ) as score ,\n" +
                "\tround( AVG( score_wz ), 1 ) as scoreWz,\n" +
                "\tround( AVG( score_ws ), 1 ) as scoreWs,\n" +
                "\tround( AVG( score_ss ), 1 ) as scoreSs,\n" +
                "\tround( AVG( score_fw ), 1 ) as scoreFw \n" +
                "FROM\n" +
                "\t`booking_comment` \n" +
                "WHERE\n" +
                "\tcomment_parent_id IS NULL \n" +
                "\tAND hotel_id = :hotelId \n" +
                "\tAND `status` = 1";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("hotelId",hotelId);
        List<EvaluatePageVo> list = null;
        try{
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(EvaluatePageVo.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list.get(0);
        return null;

    }

    @Override
    public int insterCommpent(BookingComment bookingComment) {
        String sql = "INSERT INTO booking_comment (id, comment_id, comment_parent_id, comment_name, content,create_date,modify_date, status,comment_status,create_id,create_username) VALUES (:id, :comment_id, :comment_parent_id, :comment_name, :content, :create_date, :modify_date, :status,:comment_status,:create_id,:create_username)";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("comment_id",bookingComment.getCommentId());
        sps.addValue("comment_parent_id",bookingComment.getCommentParentId());
        sps.addValue("comment_name",bookingComment.getCommentName());
        sps.addValue("content",bookingComment.getContent());
        sps.addValue("create_date",UUIDUtil.getNewDate());
        sps.addValue("modify_date",UUIDUtil.getNewDate());
        sps.addValue("status",1);
        sps.addValue("comment_status",1);
        sps.addValue("create_id",bookingComment.getCreateId());
        sps.addValue("create_username",bookingComment.getCreateUsername());
        if(bookingComment.getId()==null){
            sps.addValue("id", String.valueOf(UUID.randomUUID()));
        }else{
            sps.addValue("id", bookingComment.getId());

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

    @Override
    public int updateCommpentStatus(BookingComment bookingComment) {
        StringBuffer stringBuffer =  new StringBuffer(" update `booking_comment` set  ");
        MapSqlParameterSource sps = new MapSqlParameterSource();
        if (!Func.checkNull(String.valueOf(bookingComment.getCommentStatus()))){
            stringBuffer.append(" comment_status=:comment_status,");
            stringBuffer.append(" modify_date=:modify_date");
            sps.addValue("comment_status", bookingComment.getCommentStatus());
            sps.addValue("modify_date",UUIDUtil.getNewDate());
        }
        // 将要修改的数据填充到查询语句中
        stringBuffer.append("  where id=:id  ");
        sps.addValue("id", bookingComment.getId());
        int num = 0;
        try{
            num = namedParameterJdbcTemplate.update(stringBuffer.toString(), sps);
        }
        catch(Exception e){
            e.printStackTrace();
        }
        return num;
    }

    @Override
    public List<BookingComment> queryPage(String sqlx, int page, int rows) {
        SqlUtil.filterKeyword(sqlx);
        int start = (page - 1) * rows;// 每页的起始下标
        String sql = selectSql + sqlx + " and a.comment_id is null " + " ORDER BY create_date DESC limit :start,:rows ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        List<BookingComment> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(BookingComment.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 ("+ selectSql + " and a.comment_id is null " +") a where status != 0 "+sqlx;
        MapSqlParameterSource sps = new MapSqlParameterSource();
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<BookingComment> getByBookId(String bookId) {
        String sql = selectSql + " and a.booking_id = :id and a.comment_id is null and status != 0";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",bookId);
        List<BookingComment> list = new ArrayList<>();
        try{
            list = namedParameterJdbcTemplate.query(sql,sps, new BeanPropertyRowMapper<>(BookingComment.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public List<BookingComment> getByParentId(String parentId) {
        String sql = selectSql + " and a.comment_parent_id = :id and a.comment_id = :id  and status != 0";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",parentId);
        List<BookingComment> list = new ArrayList<>();
        try{
            list = namedParameterJdbcTemplate.query(sql,sps, new BeanPropertyRowMapper<>(BookingComment.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }

    @Override
    public List<BookingComment> getByCommentId(String commentId) {
        String sql = selectSql + " and a.comment_id = :id and status != 0";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",commentId);
        List<BookingComment> list = new ArrayList<>();
        try{
            list = namedParameterJdbcTemplate.query(sql,sps, new BeanPropertyRowMapper<>(BookingComment.class));
        }catch (Exception e){
            e.printStackTrace();
        }
        if(list != null && list.size()>0) return list;
        return null;
    }
}