package com.juick.service;
import com.juick.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import javax.inject.Inject;
import java.util.List;
/**
* Created by aalexeev on 11/13/16.
*/
@Repository
public class PMQueriesServiceImpl extends BaseJdbcService implements PMQueriesService {
@Inject
public PMQueriesServiceImpl(JdbcTemplate jdbcTemplate) {
super(jdbcTemplate, null);
}
@Transactional
@Override
public boolean createPM(final int uidFrom, final int uid_to, final String body) {
boolean ret = getJdbcTemplate().update(
"INSERT INTO pm(user_id, user_id_to, txt) VALUES (?, ?, ?)",
uidFrom, uid_to, body) > 0;
if (ret) {
getJdbcTemplate().update(
"INSERT INTO pm_streams(user_id, user_id_to, lastmessage, unread) VALUES (?, ?, NOW(), 1) "
+ "ON DUPLICATE KEY UPDATE lastmessage = NOW(), unread = unread + 1",
uidFrom, uid_to);
}
return ret;
}
@Transactional
@Override
public boolean addPMinRoster(final int uid, final String jid) {
return getJdbcTemplate().update(
"INSERT INTO pm_inroster(user_id, jid) VALUES (?, ?)", uid, jid) > 0;
}
@Transactional
@Override
public boolean removePMinRoster(final int uid, final String jid) {
return getJdbcTemplate().update(
"DELETE FROM pm_inroster WHERE user_id = ? AND jid = ?", uid, jid) > 0;
}
@Transactional
@Override
public boolean havePMinRoster(final int uid, final String jid) {
List<Integer> res = getJdbcTemplate().queryForList(
"SELECT 1 FROM pm_inroster WHERE user_id = ? AND jid = ?",
Integer.class,
uid, jid);
return res.size() > 0;
}
@Transactional(readOnly = true)
@Override
public String getLastView(final int uidFrom, final int uidTo) {
List<String> list = getJdbcTemplate().queryForList(
"SELECT lastview FROM pm_streams WHERE user_id = ? AND user_id_to = ?",
String.class,
uidFrom, uidTo);
return list.isEmpty() ?
null : list.get(0);
}
@Transactional(readOnly = true)
@Override
public List<User> getPMLastConversationsUsers(final int uid, final int cnt) {
return getJdbcTemplate().query(
"SELECT pm_streams.user_id, users.nick, pm_streams.unread FROM pm_streams "
+ "INNER JOIN users ON users.id = pm_streams.user_id "
+ "WHERE pm_streams.user_id_to=? "
+ "ORDER BY pm_streams.unread DESC, pm_streams.lastmessage DESC LIMIT ?",
(rs, rowNum) -> {
com.juick.User u = new com.juick.User();
u.setUid(rs.getInt(1));
u.setName(rs.getString(2));
u.setUnreadCount(rs.getInt(3));
return u;
},
uid, cnt);
}
@Transactional
@Override
public List<com.juick.Message> getPMMessages(final int uid, final int uidTo) {
SqlParameterSource sqlParameterSource = new MapSqlParameterSource()
.addValue("uid", uid)
.addValue("uidTo", uidTo);
List<com.juick.Message> msgs = getNamedParameterJdbcTemplate().query(
"SELECT user_id, txt,ts FROM pm WHERE (user_id = :uid AND user_id_to = :uidTo) "
+ "OR (user_id_to = :uid AND user_id = :uidTo) ORDER BY ts DESC LIMIT 20",
sqlParameterSource,
(rs, rowNum) -> {
com.juick.Message msg = new com.juick.Message();
int uuid = rs.getInt(1);
msg.setUser(new User());
msg.getUser().setUid(uuid);
msg.setText(rs.getString(2));
msg.setDate(rs.getTimestamp(3));
return msg;
});
getNamedParameterJdbcTemplate().update(
"UPDATE pm_streams SET lastview = NOW(), unread = 0 WHERE user_id_to = :uid AND user_id = :uidTo",
sqlParameterSource);
return msgs;
}
@Transactional(readOnly = true)
@Override
public List<com.juick.Message> getLastPMInbox(final int uid) {
return getJdbcTemplate().query(
"SELECT pm.user_id,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW()),pm.ts " +
"FROM pm INNER JOIN users ON pm.user_id=users.id WHERE pm.user_id_to=? ORDER BY pm.ts DESC LIMIT 20",
(rs, num) -> {
com.juick.Message msg = new com.juick.Message();
msg.setUser(new User());
msg.getUser().setUid(rs.getInt(1));
msg.getUser().setName(rs.getString(2));
msg.setText(rs.getString(3));
msg.TimeAgo = rs.getInt(4);
msg.setDate(rs.getTimestamp(5));
return msg;
},
uid);
}
@Transactional(readOnly = true)
@Override
public List<com.juick.Message> getLastPMSent(final int uid) {
return getJdbcTemplate().query(
"SELECT pm.user_id_to,users.nick,pm.txt,TIMESTAMPDIFF(MINUTE,pm.ts,NOW())," +
"pm.ts FROM pm INNER JOIN users ON pm.user_id_to=users.id " +
"WHERE pm.user_id=? ORDER BY pm.ts DESC LIMIT 20",
(rs, num) -> {
com.juick.Message msg = new com.juick.Message();
msg.setUser(new User());
msg.getUser().setUid(rs.getInt(1));
msg.getUser().setName(rs.getString(2));
msg.setText(rs.getString(3));
msg.TimeAgo = rs.getInt(4);
msg.setDate(rs.getTimestamp(5));
return msg;
},
uid);
}
}