diff options
Diffstat (limited to 'src/main/java/com/juick/server/PMQueries.java')
| -rw-r--r-- | src/main/java/com/juick/server/PMQueries.java | 145 |
1 files changed, 145 insertions, 0 deletions
diff --git a/src/main/java/com/juick/server/PMQueries.java b/src/main/java/com/juick/server/PMQueries.java new file mode 100644 index 00000000..74d5df3e --- /dev/null +++ b/src/main/java/com/juick/server/PMQueries.java @@ -0,0 +1,145 @@ +/* + * Juick + * Copyright (C) 2008-2011, Ugnich Anton + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <http://www.gnu.org/licenses/>. + */ +package com.juick.server; + +import com.juick.User; +import org.springframework.dao.EmptyResultDataAccessException; +import org.springframework.jdbc.core.JdbcTemplate; + +import java.util.List; + +/** + * + * @author Ugnich Anton + */ +public class PMQueries { + + public static boolean createPM(JdbcTemplate sql, int uid_from, int uid_to, String body) { + boolean ret = sql.update("INSERT INTO pm(user_id,user_id_to,txt) VALUES (?,?,?)", + uid_from, uid_to, body) > 0; + + if (ret) { + sql.update("INSERT INTO pm_streams(user_id,user_id_to,lastmessage,unread) " + + "VALUES (?,?,NOW(),1) " + + "ON DUPLICATE KEY UPDATE lastmessage=NOW(),unread=unread+1", + uid_from, uid_to); + } + return ret; + } + + public static boolean addPMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("INSERT INTO pm_inroster(user_id,jid) VALUES (?,?)", + uid, jid) > 0; + } + + public static boolean removePMinRoster(JdbcTemplate sql, int uid, String jid) { + return sql.update("DELETE FROM pm_inroster WHERE user_id=? AND jid=?", uid, jid) > 0; + } + + public static boolean havePMinRoster(JdbcTemplate sql, int uid, String jid) { + List<Integer> res = sql.queryForList("SELECT 1 FROM pm_inroster " + + "WHERE user_id=? AND jid=?", Integer.class, + uid, jid); + return res.size() > 0; + } + + public static String getLastView(JdbcTemplate sql, int uid_from, int uid_to) { + try { + return sql.queryForObject("SELECT lastview FROM pm_streams WHERE user_id=? AND user_id_to=?", String.class, + uid_from, uid_to); + } catch (EmptyResultDataAccessException e) { + return null; + } + } + + public static List<User> getPMLastConversationsUsers(JdbcTemplate sql, int uid, int cnt) { + List<User> qusers = sql.query("SELECT user_id,unread FROM pm_streams " + + "WHERE user_id_to=? " + + "ORDER BY unread DESC, lastmessage DESC LIMIT " + cnt, (rs, rowNum) -> { + com.juick.User u = new com.juick.User(); + u.setUID(rs.getInt(1)); + u.MessagesCount = rs.getInt(2); + return u; + }, uid); + if (!qusers.isEmpty()) { + UserQueries.fillUsersByID(sql, qusers); + } + + return qusers; + } + + public static boolean haveUserInArray(List<com.juick.User> arr, int uid) { + for (User user : arr) { + if (user.getUID() == uid) { + return true; + } + } + return false; + } + + public static List<com.juick.Message> getPMMessages(JdbcTemplate sql, int uid, int uid_to) { + List<com.juick.Message> msgs = sql.query("SELECT user_id,txt,ts FROM pm " + + "WHERE (user_id=? AND user_id_to=?) " + + "OR (user_id_to=? AND user_id=?) ORDER BY ts DESC LIMIT 20", + (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; + }, uid, uid_to, uid, uid_to); + + sql.update("UPDATE pm_streams SET lastview=NOW(),unread=0 " + + "WHERE user_id_to=? AND user_id=?", uid, uid_to); + + return msgs; + } + + public static List<com.juick.Message> getLastPMInbox(JdbcTemplate sql, int uid) { + return sql.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().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + return msg; + }, uid); + } + + public static List<com.juick.Message> getLastPMSent(JdbcTemplate sql, int uid) { + return sql.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().setUName(rs.getString(2)); + msg.setText(rs.getString(3)); + msg.TimeAgo = rs.getInt(4); + msg.setDate(rs.getTimestamp(5)); + return msg; + }, uid); + } +} |