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,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 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 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 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 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 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 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 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 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 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" + "\tbc.create_username 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 \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 \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 GROUP BY comment_id AND `status` = 1 ) 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 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 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 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 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 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 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 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 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 getByCommentId(String commentId) { String sql = selectSql + " and a.comment_id = :id and status != 0"; MapSqlParameterSource sps = new MapSqlParameterSource(); sps.addValue("id", commentId); List 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 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,\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\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\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 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 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 \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 \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 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\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 \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\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 and b.status_del=1\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 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; } 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()); } }