mijia_team / mijia-server · Files

package com.bjivt.showtime.api.dao.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.bjivt.showtime.api.dao.LiveDao;
import com.bjivt.showtime.api.entity.Live;
import com.bjivt.showtime.api.entity.User;
import com.bjivt.showtime.api.utils.Pager;

@Repository
public class LiveDaoImpl extends BaseDaoImpl<Live, Integer> implements LiveDao {

	public Map<String, Object> getLiveRoomInfoById(Integer id,Integer type) {
		String sql = "SELECT tbl_live_list.id,tbl_live_list.`name`, tbl_live_list.user_id as userId,tbl_live_list.chatroom_addr as chatroomAddr, tbl_live_list.record_addr as recordAddr,"
				+ " tbl_live_list.video_addr as videoAddr, tbl_live_list.audience_total_num as audienceTotalNum, tbl_live_list.audience_now_num as audienceNowNum, tbl_live_list.type, "
				+ " tbl_live_list.like_num as likeNum, tbl_live_list.tag, tbl_live_list.location,tbl_user_list.`name` as nick, tbl_user_list.exp, tbl_user_list.signature, "
				+ " tbl_user_list.fans_count as fanscount, tbl_user_list.me_coin_total as mecointotal, tbl_user_list.sex, tbl_user_list.age,tbl_user_list.avatar "
				+ " FROM tbl_live_list "
				+ " LEFT JOIN tbl_user_list ON tbl_live_list.user_id = tbl_user_list.id "
				+ " WHERE tbl_live_list.active = 1 AND tbl_live_list.type = ? AND tbl_live_list.id=?";
		try {
			return this.jdbcTemplate.queryForMap(sql, type, id);
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
	}

	public Live getLiveByUserId(Integer userId, Integer type) {
		String sql = "SELECT tbl_live_list.id,tbl_live_list.`name`, tbl_live_list.user_id as userId,tbl_live_list.chatroom_addr as chatroomAddr, "
				+ " tbl_live_list.video_addr as videoAddr, tbl_live_list.audience_total_num as audienceTotalNum, tbl_live_list.audience_now_num as audienceNowNum, "
				+ " tbl_live_list.like_num as likeNum, tbl_live_list.tag, tbl_live_list.location "
				+ " FROM tbl_live_list "
				+ " WHERE tbl_live_list.type = ? AND tbl_live_list.active = 1 AND tbl_live_list.user_id= ?";
		Live live = null; 
		try {
			live = (Live) this.jdbcTemplate.queryForObject(sql, new Object[]{type,userId}, new BeanPropertyRowMapper(Live.class));
		} catch (Exception e) {
		}
		return live;
	}

	public List<Map<String, Object>> getFollowerLiveList(Integer userId, Pager page) {
		String sql = "SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.record_addr as recordAddr, tbl.chatroom_addr as chatroomAddr, tbl.video_addr as videoAddr, tbl.type, tbl.like_num as likeNum, tbl.tag,"
				+ " tbl.audience_total_num as audienceTotalNum,tbl.audience_now_num as audienceNowNum,tbl.location, tbu.`name` as nick, tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
				+ " FROM tbl_live_list tbl "
				+ " LEFT JOIN  tbl_user_follow_list tf ON tf.follow_user_id = tbl.user_id "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tf.follow_user_id "
				+ " WHERE tf.user_id= ? AND tbl.active = 1 AND tbu.active='Y' ORDER BY tbl.create_time DESC ";
		
		String selectSql=sql+" limit ?,?";
		page.setCount(queryForList(sql, new Object[]{userId}).size());
		return this.jdbcTemplate.queryForList(selectSql, userId,page.getFrom(),page.getSize());
	}

	public List<Map<String, Object>> getHotLiveList(Pager page) {
//		String sql = "SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.chatroom_addr as chatroomAddr, "
//				+ " tbl.video_addr as videoAddr, tbl.like_num as likeNum, tbl.tag, tbl.location, "
//				+ " tbl.audience_total_num as audienceTotalNum,tbl.audience_now_num as audienceNowNum,tbu.`name` as nick, "
//				+ " tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
//				+ " FROM tbl_live_list tbl "
//				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tbl.user_id "
//				+ " WHERE (tbl.type = 1 OR tbl.recommend = 1) AND tbl.active = 1 AND tbu.active='Y' ORDER BY tbl.recommend, tbu.exp, tbl.audience_now_num DESC ";
//		
		String sql = "SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.chatroom_addr as chatroomAddr,"
				+ " (CASE tbl.type WHEN 1 THEN tbl.video_addr ELSE tbl.record_addr END) as videoAddr," 
				+ " tbl.like_num as likeNum, tbl.tag, tbl.location, tbl.type AS videoType,"
				+ " tbl.audience_total_num as audienceTotalNum,tbl.audience_now_num as audienceNowNum,tbu.`name` as nick,"
				+ " tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature"
				+ " FROM"
				+ " tbl_live_list tbl"
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tbl.user_id"
				+ " WHERE (tbl.type = 1 OR tbl.recommend = 1) AND tbl.active = 1 AND tbu.active='Y'"
				+ " ORDER BY tbl.recommend, tbu.exp, tbl.audience_now_num, tbl.type DESC";
		
		String selectSql=sql+" LIMIT ?,?";
		page.setCount(queryForList(sql).size());
		
		return this.jdbcTemplate.queryForList(selectSql, page.getFrom(),page.getSize());
	}
	/**
	 * 获取用户的信息
	 * @see com.bjivt.showtime.api.dao.LiveDao#getUserLiveList(java.lang.Integer, com.bjivt.showtime.api.utils.Pager)
	 */
	public List<Map<String, Object>> getUserLiveList(Integer userId, Pager page) {
		String sql ="SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.record_addr as recordAddr, tbl.chatroom_addr as chatroomAddr, tbl.like_num as likeNum, tbl.tag, tbl.location, "
				+ " tbl.video_addr as videoAddr, tbl.audience_total_num as audienceTotalNum, tbl.type, tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
				+ " FROM tbl_live_list tbl "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tbl.user_id "
				+ " WHERE tbl.user_id = ? AND tbl.active = 1 AND tbu.active='Y' AND tbl.type = 2"
				+ " ORDER BY tbl.create_time DESC";
		String selectSql=sql+" limit ?, ?";
		page.setCount(queryForList(sql, new Object[]{userId}).size());
		
		return this.jdbcTemplate.queryForList(selectSql, userId, page.getFrom(), page.getSize());
	}

	public List<Map<String, Object>> requestFollowerVideoLiveList(
			Integer userId, Pager page) {
		String sql = "SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.chatroom_addr as chatroomAddr, tbl.video_addr as videoAddr, tbl.like_num as likeNum, tbl.tag, tbl.location, "
				+ " tbl.audience_total_num as audienceTotalNum,tbl.audience_now_num as audienceNowNum,tbu.`name` as nick, tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
				+ " FROM tbl_live_list tbl "
				+ " LEFT JOIN  tbl_user_follow_list tf ON tf.follow_user_id = tbl.user_id "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tf.follow_user_id "
				+ " WHERE tf.user_id= ? AND tbl.active = 1 AND tbl.type = 1 AND tbu.active='Y' ORDER BY tbl.create_time DESC ";
		
		String selectSql=sql+" limit ?,?";
		page.setCount(queryForList(sql, new Object[]{userId}).size());
		return this.jdbcTemplate.queryForList(selectSql, userId,page.getFrom(),page.getSize());
	}
	/**
	 * 贡献榜
	 * @see com.bjivt.showtime.api.dao.LiveDao#getUserGiftList(java.lang.Integer, com.bjivt.showtime.api.utils.Pager)
	 */
	public List<Map<String, Object>> getUserGiftList(Integer userId, Pager page) {
		String sql = "select  c.price, c.exp, te.`level`,te.`name` as levelName, c.userId,"
				+ " c.name,c.msisdn,c.avatar,c.email,c.signature,c.livecount, "
				+ " c.chinesecoin,c.mecoinbalance, "
				+ " c.likecount,c.fanscount,c.followercount,c.mecointotal, c.sentgiftcount, "
				+ " c.sentgiftmoneycount, c.sentredenvelopcount, c.sentredenvelopmoneycount, c.sex,c.age, "
				+ " c.hometown, c.profession, c.isstar "
				+ "  from( "
				+ " SELECT SUM(td.count * tg.price) as price, SUM(td.count * tg.exp) as exp,tu.id userId, "
				+ " tu.name,tu.msisdn,tu.avatar,tu.email,tu.signature,tu.live_count as livecount, "
				+ " tu.chinese_coin as chinesecoin,tu.me_coin_balance as mecoinbalance, "
				+ " tu.like_count as likecount,tu.fans_count as fanscount,tu.follower_count as followercount,tu.me_coin_total as mecointotal, tu.sent_gift_count as sentgiftcount, "
				+ " tu.sent_gift_money_count as sentgiftmoneycount, tu.sent_red_envelop_count as sentredenvelopcount, tu.sent_red_envelop_money_count as sentredenvelopmoneycount, tu.sex,tu.age, "
				+ " tu.hometown, tu.profession,tu.exp as userExp, tu.is_star as isstar"
				+ " FROM tbl_user_gift_detail td "
				+ " LEFT JOIN tbl_user_list tu ON tu.id = td.from_user_id "
				+ " LEFT JOIN tbl_gift_list tg ON tg.id = td.gift_id "
				+ " WHERE td.to_user_id = ? GROUP BY td.from_user_id ORDER BY tu.exp desc "
				+ " ) c "
				+ " LEFT JOIN tbl_level te ON (c.userExp>=te.exp_start AND c.userExp<=te.exp_end) order by c.price desc";
		
		String selectSql=sql+" limit ?,?";
		
		page.setCount(queryForList(sql, new Object[]{userId}).size());
		return this.jdbcTemplate.queryForList(selectSql, userId,page.getFrom(),page.getSize());
	}
	/**
	 * 获取该用户给别人所产生的贡献列表数据
	 * 支持度
	 */
	public List<Map<String, Object>> getUserGiftToOtherList(Integer userId, Pager page) {
		String sql = "select  c.price, c.exp,c.`name`, c.avatar, c.userId, c.signature,c.sex, te.`level`,te.`name` as levelName "
				+ "  from( "
				+ " SELECT SUM(td.count * tg.price) as price, SUM(td.count * tg.exp) as exp, tu.`name`,tu.avatar,tu.sex,tu.exp as userExp,td.to_user_id as userId,tu.signature "
				+ " FROM tbl_user_gift_detail td "
				+ " LEFT JOIN tbl_user_list tu ON tu.id = td.to_user_id "
				+ " LEFT JOIN tbl_gift_list tg ON tg.id = td.gift_id "
				+ " WHERE td.from_user_id = ? GROUP BY td.to_user_id ORDER BY tu.exp desc "
				+ " ) c "
				+ " LEFT JOIN tbl_level te ON (c.userExp>=te.exp_start AND c.userExp<=te.exp_end) order by c.price desc";
		
		String selectSql=sql+" limit ?,?";
		
		page.setCount(queryForList(sql, new Object[]{userId}).size());
		return this.jdbcTemplate.queryForList(selectSql, userId,page.getFrom(),page.getSize());
	}

	public List<Map<String, Object>> getLiveGiftList(Integer liveId, Pager page) {
		String sql = "SELECT SUM(td.count * tg.price) as price, SUM(td.count * tg.exp) as exp, tu.`name`,tu.avatar,tu.exp,td.from_user_id as userId,tu.signature "
				+ " FROM tbl_user_gift_detail td "
				+ " LEFT JOIN tbl_user_list tu ON tu.id = td.from_user_id  "
				+ " LEFT JOIN tbl_gift_list tg ON tg.id = td.gift_id"
				+ " LEFT JOIN tbl_level te ON (tu.exp>=te.exp_start AND tu.exp<=te.exp_end) "
				+ " WHERE td.live_id = ? GROUP BY td.from_user_id ORDER BY tu.exp desc ";
		
		String selectSql=sql+" limit ?,?";
		page.setCount(queryForList(sql, new Object[]{liveId}).size());
		return this.jdbcTemplate.queryForList(selectSql, liveId, page.getFrom(),page.getSize());
	}

	public List<Map<String, Object>> requestNewLiveList(Pager page) {
		String sql = "SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.chatroom_addr as chatroomAddr, tbl.video_addr as videoAddr, tbl.like_num as likeNum, tbl.tag, tbl.location, "
				+ " tbl.audience_total_num as audienceTotalNum,tbl.audience_now_num as audienceNowNum,tbu.`name` as nick, tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
				+ " FROM tbl_live_list tbl "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tbl.user_id "
				+ " WHERE tbl.active = 1 AND tbl.type = 1 AND tbu.active='Y'  ORDER BY tbl.create_time DESC ";
		
		String selectSql=sql+" limit ?,?";
		page.setCount(queryForList(sql).size());
		return this.jdbcTemplate.queryForList(selectSql, page.getFrom(),page.getSize());
	}

	public List<Map<String, Object>> getStarLiveList(Pager page) {
		String sql = "SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.chatroom_addr as chatroomAddr, tbl.video_addr as videoAddr, tbl.like_num as likeNum, tbl.tag, tbl.location, "
				+ " tbl.audience_total_num as audienceTotalNum,tbl.audience_now_num as audienceNowNum,tbu.`name` as nick, tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
				+ " FROM tbl_live_list tbl "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tbl.user_id "
				+ " WHERE tbl.active = 1 AND tbl.type = 1 AND tbu.active='Y' AND tbu.is_star=1 ORDER BY tbl.create_time DESC ";
		
		String selectSql=sql+" limit ?,?";
		page.setCount(queryForList(sql).size());
		return this.jdbcTemplate.queryForList(selectSql, page.getFrom(),page.getSize());
	}

	public List<Map<String, Object>> getLiveRoom() {
		String sql = "SELECT tbl_live_list.id,tbl_live_list.`name`, tbl_live_list.user_id as userId,tbl_live_list.chatroom_addr as chatroomAddr, tbl_live_list.heartbeat, "
				+ " tbl_live_list.video_addr as videoAddr, tbl_live_list.audience_total_num as audienceTotalNum, tbl_live_list.audience_now_num as audienceNowNum, "
				+ " tbl_live_list.like_num as likeNum, tbl_live_list.tag, tbl_live_list.location, tbu.signature "
				+ " FROM tbl_live_list "
				+ " WHERE tbl_live_list.type = 1";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(sql);
		return list;
	}

	public List<Map<String, Object>> getHotLiveList() {
		String sql = "SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.chatroom_addr as chatroomAddr, tbl.video_addr as videoAddr, "
				+ " tbl.like_num as likeNum, tbl.tag, tbl.location, tbu.avatar, "
				+ " tbl.audience_total_num as audienceTotalNum,tbl.audience_now_num as audienceNowNum,tbu.`name` as nick, tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
				+ " FROM tbl_live_list tbl "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tbl.user_id "
				+ " WHERE tbl.active = 1 AND tbl.type = 1 AND tbu.active='Y' ORDER BY tbl.recommend, tbu.exp, tbl.audience_now_num DESC ";
		return this.jdbcTemplate.queryForList(sql);
	}

	public List<Map<String, Object>> getRecordListByUserId(Integer userId) {
		String sql = "SELECT tl.id, tl.`name`, tl.`thumbnail`, tl.user_id as userId, tl.record_addr,tl.chatroom_addr as chatroomAddr, tbu.avatar, "
				+ " tl.video_addr as videoAddr, tl.audience_total_num as audienceTotalNum, tl.audience_now_num as audienceNowNum, tl.heartbeat, tl.like_num as likeNum, "
				+ " tl.tag, tl.is_allow_location as isAllowLocation, tl.type, tl.location,tl.create_time as createTime,tl.recommend, tl.active "
				+ " FROM tbl_live_list tl "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tl.user_id "
				+ " WHERE tl.user_id = ? AND tl.type = 2";
		return this.jdbcTemplate.queryForList(sql,userId);
	}

	public List<Map<String, Object>> getLocationLLiveList(Float maxlongitude,Float maxlatitude, Float minlongitude, Float minlatitude, Pager page) {

		String sql = "SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.chatroom_addr as chatroomAddr, tbl.video_addr as videoAddr, tbl.like_num as likeNum, tbl.tag, tbl.location, "
				+ " tbl.audience_total_num as audienceTotalNum,tbl.audience_now_num as audienceNowNum,tbu.`name` as nick, tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
				+ " FROM tbl_live_list tbl "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tbl.user_id "
				+ " WHERE tbl.active = 1 AND tbl.type = 1 AND tbu.active='Y' AND (tbl.longitude > ? AND tbl.latitude > ? AND tbl.longitude < ? AND tbl.latitude < ?) ";
		
		String selectSql=sql+" limit ?,?";
		page.setCount(queryForList(sql,new Object[]{minlongitude, minlatitude, maxlongitude , maxlatitude}).size());
		return this.jdbcTemplate.queryForList(selectSql, minlongitude, minlatitude, maxlongitude , maxlatitude, page.getFrom(),page.getSize());
	
	}

	@Override
	public List<Map<String, Object>> getRecordListByUserId(Integer userId, Pager pager) {
		String sql = "SELECT tl.id, tl.`name`, tl.`thumbnail`, tl.user_id as userId, tl.record_addr,tl.chatroom_addr as chatroomAddr, tbu.avatar, "
				+ " tl.video_addr as videoAddr, tl.audience_total_num as audienceTotalNum, tl.audience_now_num as audienceNowNum, tl.heartbeat, tl.like_num as likeNum, "
				+ " tl.tag, tl.is_allow_location as isAllowLocation, tl.type, tl.location,tl.create_time as createTime,tl.recommend, tl.active "
				+ " FROM tbl_live_list tl "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tl.user_id "
				+ " WHERE tl.user_id = ? AND tl.type = 2";
//		return this.jdbcTemplate.queryForList(sql,userId);
		String selectSql=sql+" limit ?,?";
		pager.setCount(queryForList(sql, new Object[]{userId}).size());
		return this.jdbcTemplate.queryForList(selectSql, userId, pager.getFrom(),pager.getSize());
	}

	@Override
	public List<Map<String, Object>> getLiveUserList(Integer liveId, Pager page) {
		String sql = "select tbu.price, tbu.exp,tbu.`name`, tbu.avatar, tbu.userId, tbu.signature,tbu.sex, te.`level`,te.`name` as levelName"
				+ " from ( "
				+ " select SUM(td.count * tg.price) as price, SUM(td.count * tg.exp) as exp, tbl.`name`,  "
				+ " tbl.avatar,tbl.sex,tbl.exp as userExp,tbl.id as userId,tbl.signature  "
				+ " from "
				+ " (select tu.* from tbl_user_list tu, db_showtime_log.tbl_live_record lr  "
				+ " where lr.visit_user_id = tu.id and lr.visit_user_id>0 and lr.live_id=? group by  tu.id) tbl "
				+ " LEFT JOIN tbl_user_gift_detail td ON tbl.id = td.from_user_id "
				+ " LEFT JOIN tbl_gift_list tg ON tg.id = td.gift_id "
				+ " group by  tbl.id order by price desc) tbu"
				+ " LEFT JOIN tbl_level te ON (tbu.userExp>=te.exp_start AND tbu.userExp<=te.exp_end)"
				+ " order by tbu.price desc";
		String selectSql=sql+" limit ?,?";
		page.setCount(queryForList(sql, new Object[]{liveId}).size());
		return this.jdbcTemplate.queryForList(selectSql, liveId, page.getFrom(),page.getSize());
	}
	
	@Override
	public int getUserLiveCount(int userId, Pager pager) {
		String sql ="SELECT tbl.id, tbl.`name`, tbl.`thumbnail`, tbl.user_id as userId, tbl.record_addr as recordAddr, tbl.chatroom_addr as chatroomAddr, tbl.like_num as likeNum, tbl.tag, tbl.location, "
				+ " tbl.video_addr as videoAddr, tbl.audience_total_num as audienceTotalNum, tbl.type, tbu.avatar, tbu.exp, tbu.me_coin_balance as mecoinbalance, tbu.signature "
				+ " FROM tbl_live_list tbl "
				+ " LEFT JOIN tbl_user_list tbu ON tbu.id = tbl.user_id "
				+ " WHERE tbl.user_id = ? AND tbl.active = 1 AND tbu.active='Y' AND tbl.type = 2"
				+ " ORDER BY tbl.create_time DESC";
		
		
		return queryForList(sql, new Object[]{userId}).size();
	}

	@Override
	public User getUserByLiveId(Integer liveId) {
		String sql = "SELECT user.id,user.name,user.msisdn,user.captcha,user.avatar,user.email,user.signature,user.live_count as livecount, user.chinese_coin as chinesecoin,user.me_coin_balance as mecoinbalance,"
				+ " user.like_count as likecount,user.exp, user.fans_count as fanscount,user.follower_count as followercount,user.me_coin_total as mecointotal, user.sent_gift_count as sentgiftcount, user.active,"
				+ " user.sent_gift_money_count as sentgiftmoneycount, user.sent_red_envelop_count as sentredenvelopcount, user.sent_red_envelop_money_count as sentredenvelopmoneycount, user.sex,user.age, "
				+ " user.hometown, user.profession, user.third_party_account as thirdPartyAccount, user.password,user.create_time, level.name AS levelname, level.level AS level, user.is_star"
				+ " FROM tbl_live_list ll LEFT JOIN tbl_user_list user ON ll.user_id=user.id ";
		sql += " LEFT JOIN tbl_level level ON user.exp >= level.exp_start AND user.exp <= level.exp_end WHERE ll.id =?";
		User user = null; 
		try {
			user = (User) this.jdbcTemplate.queryForObject(sql, new Object[]{liveId}, new BeanPropertyRowMapper(User.class));
		} catch (Exception e) {
		}
		return user;
	}
}