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

package com.happy.dao.impl;

import com.happy.Model.BookingComment;
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 com.happy.vo.*;
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 BookingCommentImplDao 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,b.order_num,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, DATE_FORMAT(a.create_date, '%Y-%m-%d %H:%i:%s') create_date, DATE_FORMAT(a.modify_date, '%Y-%m-%d %H:%i:%s') modify_date,a.status,DATE_FORMAT(b.check_out_time, '%Y-%m-%d %H:%i:%s') as checkOutTime,c.user_name as createName,b.hotel_manager_id,c.head_photo as headPhoto" +
            " 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", 2);

        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 AND status = 1 GROUP BY comment_parent_id ) 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 AND `status` = 1 GROUP BY comment_parent_id ) 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 AND status = 1 GROUP BY comment_parent_id ) 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 AND `status` = 1 GROUP BY comment_parent_id ) 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 AND status = 1 GROUP BY comment_parent_id ) 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 AND `status` = 1 GROUP BY comment_parent_id ) 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 List<CommentVo> commentVoList(String bookingCommentId) {
        String sql = "SELECT\n" +
                "\tbc.id as id ,\n" +
                "\tbc.comment_id as commentId,\n" +
                "\tbc.comment_name as commentName,\n" +
                "\tbc.create_username as userName,\n" +
                "\tu.head_photo as headPhoto,\n" +
                "\tbc.create_date as dateTime,\n" +
                "\tbc.content as content \n" +
                "FROM\n" +
                "\t`booking_comment` bc\n" +
                "\tLEFT JOIN users u ON u.id = bc.create_id \n" +
                "WHERE\n" +
                "\tbc.comment_parent_id = :bookingCommentId and bc.status=1";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("bookingCommentId", bookingCommentId);
        List<CommentVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(CommentVo.class));
        if (list != null && list.size() > 0) return list;
        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 CommentDetailsVo commentDetails(String bookingCommentId) {
        String sql = "SELECT\n" +
                "\tbc.id AS id,\n" +
                "\tu.user_name AS userName,\n" +
                "\tu.head_photo AS headPhoto,\n" +
                "\tbc.score AS score,\n" +
                "\tb.live_time AS liveTime,\n" +
                "\tbc.create_date AS commentTime,\n" +
                "\tb.house_name AS houseName,\n" +
                "\tbc.content AS content,\n" +
                "\tfi2.url AS url,\n" +
                "\tb.hotel_name AS hotelName,\n" +
                "\t( SELECT round( AVG( score ), 1 ) FROM `booking_comment` WHERE comment_parent_id IS NULL AND `status` = 1 AND hotel_id = bc.hotel_id ) AS hotelScore,\n" +
                "\thd.`name` AS hotelTownship,\n" +
                "\tb.hotel_type AS hotelType,\n" +
                "\tifnull( cc.commentSum, 0 ) AS commentCount ,h.cover_img as coverImg,b.hotel_id as hotelId \n" +
                "FROM\n" +
                "\t`booking_comment` bc\n" +
                "\tLEFT JOIN users u ON u.id = bc.create_id\n" +
                "\tLEFT JOIN booking b ON b.id = bc.booking_id  LEFT JOIN hotel h ON h.id=b.hotel_id  \n" +
                "\tAND b.status_del = 1\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 hotel_dict hd ON hd.id = b.hotel_township \n" +
                "\tAND hd.`status` = 1\n" +
                "\tLEFT JOIN ( SELECT comment_parent_id, count( 1 ) commentSum FROM booking_comment WHERE comment_parent_id IS NOT NULL  AND `status` = 1 GROUP BY comment_parent_id ) cc ON bc.id = cc.comment_parent_id\n" +
                "\tWHERE bc.`status`=1 and bc.id= :bookingCommentId";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("bookingCommentId", bookingCommentId);
        List<CommentDetailsVo> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(CommentDetailsVo.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) return list.get(0);
        return null;
    }

    @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 BookingComment queryById(String id) {
        String sql = selectSql + " and a.id = :id";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("id",id);
        List<BookingComment> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(BookingComment.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) return list.get(0);
        return null;
    }

    @Override
    public int update(BookingComment bookingComment) {
        StringBuffer stringBuffer =  new StringBuffer(" update `booking_comment` set  ");
        MapSqlParameterSource sps = new MapSqlParameterSource();
        // 将要修改的数据填充到查询语句中
        appendValue(bookingComment,stringBuffer,sps);
        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;
    }

    /**
     * ´ýÆÀ¼Û
     *
     * @param usersId
     * @param page
     * @param rows
     * @return
     */
    @Override
        public List<PersonageCommentVo> personageNotCommentPage(String usersId, int page, int rows) {
        int start = (page - 1) * rows;// ÿҳµÄÆðʼÏÂ
        String sql = "SELECT\n" +
                "\tb.id AS id,\n" +
                "\tbc.id as bookingCommentId ,b.hotel_id as hotelId,b.house_id as houseId,\n" +
                "\tb.hotel_name AS hotelName,\n" +
                "\tb.house_order_number AS houseOrderNumber,\n" +
                "\tb.house_name AS houseName ,\n" +
                "\tb.live_time as liveTime,\n" +
                "\tb.check_out_time as checkOutTime,\n" +
                "\tb.pay_account as payAccount\n" +
                "\t,b.create_userid ,fi.url as url\n" +
                "FROM\n" +
                "\t`booking` b\n" +
                "\tLEFT JOIN booking_comment bc on b.id=bc.booking_id AND bc.comment_parent_id IS NULL  AND bc.`status`!=0\n" +
                "\t\n" +
                "\tLEFT JOIN users u on u.id =b.create_userid LEFT JOIN file_info fi on fi.link_id=b.house_id\n" +
                "\t\n" +
                "\tWHERE bc.id IS NULL  AND u.id= :usersId   AND b.order_status=5 AND b.status_del=1 ORDER BY b.order_start_time DESC LIMIT :start,:rows";

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

        return null;

    }

    /**
     * ÒÑÆÀ¼Û
     *
     * @param usersId
     * @param page
     * @param rows
     * @return
     */
    @Override
    public List<PersonageCommentVo> personageCommentPage(String usersId, int page, int rows) {
        int start = (page - 1) * rows;// ÿҳµÄÆðʼÏÂ
        String sql = "SELECT\n" +
                "\tb.id AS id,\n" +
                "\tbc.id AS bookingCommentId,\n" +
                "\tb.hotel_name AS hotelName,\n" +
                "\tb.house_order_number AS houseOrderNumber,\n" +
                "\tb.house_name AS houseName,\n" +
                "\tb.live_time AS liveTime,\n" +
                "\tb.check_out_time AS checkOutTime,\n" +
                "\tb.pay_account AS payAccount ,fi.url as url\n" +
                "FROM\n" +
                "\t`booking` b\n" +
                "\tINNER JOIN booking_comment bc ON b.id = bc.booking_id \n" +
                "\tAND bc.comment_parent_id IS NULL \n" +
                "\tAND bc.`status` = 1\n" +
                "\tLEFT JOIN users u ON u.id = b.create_userid  LEFT JOIN file_info fi on fi.link_id=b.house_id \n" +
                "WHERE\n" +
                "\tu.id = :usersId \n" +
                "\tAND b.order_status = 5 \n" +
                "\tAND b.status_del =1  ORDER BY b.order_start_time DESC LIMIT :start,:rows";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("usersId", usersId);
        List<PersonageCommentVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(PersonageCommentVo.class));
        if (list != null && list.size() > 0) return list;

        return null;
    }
        /**
         * ´ýÆÀÂÛ´ÎÊý
         *
         * @param usersId
         * @return
         */
        @Override
        public int personageNotCommentTotal (String usersId){
            String sql = "SELECT\n" +
                    "\t count(1) \n" +
                    "FROM\n" +
                    "\t`booking` b\n" +
                    "\tLEFT JOIN booking_comment bc on b.id=bc.booking_id AND bc.comment_parent_id IS NULL  AND bc.`status`=1\n" +
                    "\t\n" +
                    "\tLEFT JOIN users u on u.id =b.create_userid  LEFT JOIN file_info fi on fi.link_id=b.house_id \n" +
                    "\t\n" +
                    "\tWHERE bc.id IS NULL  AND u.id= :usersId   AND b.order_status=5 AND b.status_del=1 ";
            MapSqlParameterSource sps = new MapSqlParameterSource();
            sps.addValue("usersId", usersId);
            return namedParameterJdbcTemplate.queryForInt(sql, sps);
        }

        /**
         * ÒÑÆÀ¼Û×ÜÊý
         *
         * @param usersId
         * @return
         */
        @Override
        public int personageCommentTotal (String usersId){
            String sql = "SELECT\n" +
                    "\t count(1) \n" +
                    "FROM\n" +
                    "\t`booking` b\n" +
                    "\tINNER JOIN booking_comment bc ON b.id = bc.booking_id \n" +
                    "\tAND bc.comment_parent_id IS NULL \n" +
                    "\tAND bc.`status` = 1\n" +
                    "\tLEFT JOIN users u ON u.id = b.create_userid LEFT JOIN file_info fi on fi.link_id=b.house_id \n" +
                    "WHERE\n" +
                    "\tu.id = :usersId \n" +
                    "\tAND b.order_status = 5 \n" +
                    "\tAND b.status_del =1 ";
            MapSqlParameterSource sps = new MapSqlParameterSource();
            sps.addValue("usersId", usersId);
            return namedParameterJdbcTemplate.queryForInt(sql, sps);
        }

    @Override
    public PersonageDetailsVo personageDetails (String bookingCommentId){
        String sql = "SELECT\n" +
                "\tbc.id AS id,\n" +
                "\tbc.create_date as commentTime, \n" +
                "\tbc.score AS score,\n" +
                "\tbc.content AS content,\n" +
                "\tfi2.url AS url,\n" +
                "\tb.hotel_name AS hotelName,\n" +
                "\tb.house_order_number AS houseOrderNumber,\n" +
                "\tb.house_name AS houseName ,\n" +
                "\tb.live_time as liveTime,\n" +
                "\tb.check_out_time as checkOutTime,\n" +
                "\tb.pay_account as payAccount,u.user_name as name,u.head_photo as headPhoto , fi4.url as houseUrl ,b.id as bookingId  \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 booking b on b.id=bc.booking_id  LEFT JOIN users u on u.id=bc.create_id LEFT JOIN ( SELECT fi3.link_id, GROUP_CONCAT( fi3.url ) AS url FROM file_info fi3 GROUP BY fi3.link_id ) fi4 ON fi4.link_id = b.house_id \n" +
                "\tWHERE bc.comment_parent_id is NULL AND bc.`status`=1  and bc.id= :bookingCommentId";

        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("bookingCommentId", bookingCommentId);
        List<PersonageDetailsVo> list = null;
        try {
            list = namedParameterJdbcTemplate.query(sql, sps,
                    new BeanPropertyRowMapper<>(PersonageDetailsVo.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (list != null && list.size() > 0) return list.get(0);
        return null;
    }

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

    }

    @Override
    public List<PersonageCommentVo> auditPageComment(String usersId, int page, int rows) {
        int start = (page - 1) * rows;// ÿҳµÄÆðʼÏÂ
        String sql = "SELECT\n" +
                "\tb.id AS id,\n" +
                "\tbc.id AS bookingCommentId,\n" +
                "\tb.hotel_name AS hotelName,\n" +
                "\tb.house_order_number AS houseOrderNumber,\n" +
                "\tb.house_name AS houseName,\n" +
                "\tb.live_time AS liveTime,\n" +
                "\tb.check_out_time AS checkOutTime,\n" +
                "\tb.pay_account AS payAccount ,fi.url as url\n" +
                "FROM\n" +
                "\t`booking` b\n" +
                "\tINNER JOIN booking_comment bc ON b.id = bc.booking_id \n" +
                "\tAND bc.comment_parent_id IS NULL \n" +
                "\tAND bc.`status` = 2\n" +
                "\tLEFT JOIN users u ON u.id = b.create_userid  LEFT JOIN file_info fi on fi.link_id=b.house_id \n" +
                "WHERE\n" +
                "\tu.id = :usersId \n" +
                "\tAND b.order_status = 5 \n" +
                "\tAND b.status_del =1  ORDER BY b.order_start_time DESC LIMIT :start,:rows";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("usersId", usersId);
        List<PersonageCommentVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(PersonageCommentVo.class));
        if (list != null && list.size() > 0) return list;

        return null;
    }

    @Override
    public int auditTotalComment(String usersId) {
        String sql = "SELECT\n" +
                "\t count(1) \n" +
                "FROM\n" +
                "\t`booking` b\n" +
                "\tINNER JOIN booking_comment bc ON b.id = bc.booking_id \n" +
                "\tAND bc.comment_parent_id IS NULL \n" +
                "\tAND bc.`status` = 2\n" +
                "\tLEFT JOIN users u ON u.id = b.create_userid LEFT JOIN file_info fi on fi.link_id=b.house_id \n" +
                "WHERE\n" +
                "\tu.id = :usersId \n" +
                "\tAND b.order_status = 5 \n" +
                "\tAND b.status_del =1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("usersId", usersId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    @Override
    public List<PersonageCommentVo> refuseAuditPageComment(String usersId, int page, int rows) {
        int start = (page - 1) * rows;// ÿҳµÄÆðʼÏÂ
        String sql = "SELECT\n" +
                "\tb.id AS id,\n" +
                "\tbc.id AS bookingCommentId,\n" +
                "\tb.hotel_name AS hotelName,\n" +
                "\tb.house_order_number AS houseOrderNumber,\n" +
                "\tb.house_name AS houseName,\n" +
                "\tb.live_time AS liveTime,\n" +
                "\tb.check_out_time AS checkOutTime,\n" +
                "\tb.pay_account AS payAccount ,fi.url as url,w.workflow_remark as remark\n" +
                "FROM\n" +
                "\t`booking` b\n" +
                "\tINNER JOIN booking_comment bc ON b.id = bc.booking_id \n" +
                "\tAND bc.comment_parent_id IS NULL \n" +
                "\tAND bc.`status` = 3\n" +
                "\tLEFT JOIN users u ON u.id = b.create_userid  LEFT JOIN file_info fi on fi.link_id=b.house_id LEFT JOIN workflow w ON bc.id=w.link_id \n" +
                "WHERE\n" +
                "\tu.id = :usersId \n" +
                "\tAND b.order_status = 5 \n" +
                "\tAND b.status_del =1  ORDER BY b.order_start_time DESC LIMIT :start,:rows";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("start", start);
        sps.addValue("rows", rows);
        sps.addValue("usersId", usersId);
        List<PersonageCommentVo> list = namedParameterJdbcTemplate.query(sql, sps,
                new BeanPropertyRowMapper<>(PersonageCommentVo.class));
        if (list != null && list.size() > 0) return list;

        return null;
    }

    @Override
    public int refuseAuditTotalComment(String usersId) {
        String sql = "SELECT\n" +
                "\t count(1) \n" +
                "FROM\n" +
                "\t`booking` b\n" +
                "\tINNER JOIN booking_comment bc ON b.id = bc.booking_id \n" +
                "\tAND bc.comment_parent_id IS NULL \n" +
                "\tAND bc.`status` = 3\n" +
                "\tLEFT JOIN users u ON u.id = b.create_userid LEFT JOIN file_info fi on fi.link_id=b.house_id LEFT JOIN workflow w ON bc.id=w.link_id \n" +
                "WHERE\n" +
                "\tu.id = :usersId \n" +
                "\tAND b.order_status = 5 \n" +
                "\tAND b.status_del =1 ";
        MapSqlParameterSource sps = new MapSqlParameterSource();
        sps.addValue("usersId", usersId);
        return namedParameterJdbcTemplate.queryForInt(sql, sps);
    }

    public void appendValue(BookingComment bookingComment, StringBuffer stringBuffer, MapSqlParameterSource sps){
        if (!Func.checkNull(bookingComment.getCommentId())){
            stringBuffer.append(" comment_id=:comment_id ,");
            sps.addValue("comment_id",bookingComment.getCommentId());
        }
        if (!Func.checkNull(bookingComment.getCommentParentId())){
            stringBuffer.append(" comment_parent_id=:comment_parent_id ,");
            sps.addValue("comment_parent_id",bookingComment.getCommentParentId());
        }
        if (!Func.checkNull(bookingComment.getCommentName())){
            stringBuffer.append(" comment_name=:comment_name ,");
            sps.addValue("comment_name",bookingComment.getCommentName());
        }
        if (!Func.checkNull(bookingComment.getHotelId())){
            stringBuffer.append(" hotel_id=:hotel_id ,");
            sps.addValue("hotel_id",bookingComment.getHotelId());
        }
        if (!Func.checkNull(bookingComment.getBookingId())){
            stringBuffer.append(" booking_id=:booking_id ,");
            sps.addValue("booking_id",bookingComment.getBookingId());
        }
        if (!Func.checkNull(bookingComment.getHouseId())){
            stringBuffer.append(" house_id=:house_id ,");
            sps.addValue("house_id",bookingComment.getHouseId());
        }
        if (!Func.checkNull(bookingComment.getContent())){
            stringBuffer.append(" content=:content ,");
            sps.addValue("content",bookingComment.getContent());
        }
        if (!Func.checkNull(bookingComment.getCommentStatus())){
            stringBuffer.append(" comment_status=:comment_status ,");
            sps.addValue("comment_status",bookingComment.getCommentStatus());
        }
        if (!Func.checkNull(String.valueOf(bookingComment.getScore()))){
            stringBuffer.append(" score=:score ,");
            sps.addValue("score",bookingComment.getScore());
        }
        if (!Func.checkNull(String.valueOf(bookingComment.getScoreWs()))){
            stringBuffer.append(" score_ws=:score_ws ,");
            sps.addValue("score_ws",bookingComment.getScoreWs());
        }
        if (!Func.checkNull(String.valueOf(bookingComment.getScoreFw()))){
            stringBuffer.append(" score_fw=:score_fw ,");
            sps.addValue("score_fw",bookingComment.getScoreFw());
        }
        if (!Func.checkNull(String.valueOf(bookingComment.getScoreSs()))){
            stringBuffer.append(" score_ss=:score_ss ,");
            sps.addValue("score_ss",bookingComment.getScoreSs());
        }
        if (!Func.checkNull(String.valueOf(bookingComment.getScoreWz()))){
            stringBuffer.append(" score_wz=:score_wz ,");
            sps.addValue("score_wz",bookingComment.getScoreWz());
        }
        if (!Func.checkNull(String.valueOf(bookingComment.getCreateId()))){
            stringBuffer.append(" create_id=:create_id ,");
            sps.addValue("create_id",bookingComment.getCreateId());
        }
        if (!Func.checkNull(bookingComment.getCreateUsername())){
            stringBuffer.append(" create_username=:create_username ,");
            sps.addValue("create_username",bookingComment.getCreateUsername());
        }
        if (!Func.checkNull(bookingComment.getCreateDate())){
            stringBuffer.append(" create_date=:create_date ,");
            sps.addValue("create_date",bookingComment.getCreateDate());
        }
        if (!Func.checkNull(bookingComment.getStatus())){
            stringBuffer.append(" status=:status ,");
            sps.addValue("status",bookingComment.getStatus());
        }
        stringBuffer.append(" modify_date=:modify_date ");
        sps.addValue("modify_date", UUIDUtil.getNewDate());
    }

}