· 10 years ago · Aug 10, 2016, 08:22 AM
1package ru.zoom;
2
3import java.sql.Connection;
4import java.sql.DriverManager;
5import java.sql.PreparedStatement;
6import java.sql.ResultSet;
7import java.sql.SQLException;
8import java.sql.Statement;
9import java.text.DateFormat;
10import java.util.ArrayList;
11import java.util.Date;
12import java.util.List;
13import java.util.logging.Logger;
14
15import org.bukkit.ChatColor;
16import org.bukkit.command.CommandSender;
17import org.bukkit.entity.Player;
18import org.bukkit.inventory.ItemStack;
19
20import com.wimbli.onlineusers.OnlineUsers;
21
22import net.ess3.api.IEssentials;
23
24public class SMPluginMySQL {
25 private static String name = "SMPlugin";
26 protected static final Logger log = Logger.getLogger("Minecraft");
27 // TODO CreateSMChat
28 private static String sqlMakeSMChat = "CREATE TABLE IF NOT EXISTS `"
29 + SMPlugin.plugin.getConfig().getString("MySQL.table_chat") + "` (`sender` varchar(32) NOT NULL, "
30 + "`for` varchar(32) NOT NULL, " + "`server` varchar(32) NOT NULL, " + "`message` text(100) NOT NULL, "
31 + "`status` tinyint(1) NOT NULL)";
32 // TODO CheckSMChat
33 private static String sqlCheckTableExist = "SHOW TABLES LIKE '"
34 + SMPlugin.plugin.getConfig().getString("MySQL.table_chat") + "'";
35 // TODO Unbanned
36 private static String queryUnbannedList = "SELECT name, time FROM "
37 + SMPlugin.plugin.getConfig().getString("MySQL.table_unbanned");
38 // TODO Banlist
39 private static String queryBanlist = "SELECT name, admin, reason, time, temptime FROM "
40 + SMPlugin.plugin.getConfig().getString("MySQL.table_banlist");
41 // TODO Reason
42 private static String querySetReason = "UPDATE `" + SMPlugin.plugin.getConfig().getString("MySQL.table_banlist")
43 + "` SET `reason`='%reason%' WHERE `name`='%name%' AND `admin`='%admin%'";
44 // TODO PlayTime
45 private static String queryGetPlayTime = "SELECT time_total FROM "
46 + SMPlugin.plugin.getConfig().getString("MySQL.table_onlineusers") + " WHERE server='" + OnlineUsers.server
47 + "' AND name='%name%'";
48 // TODO Shop
49 private static String queryShopList = "SELECT itemid, summa, count FROM "
50 + SMPlugin.plugin.getConfig().getString("MySQL.table_shophistory")
51 + " WHERE shop='%server%' AND uuid='%name%'";
52 // TODO TopTime
53 private static String queryGetTopTime = "SELECT time_total, name, server FROM "
54 + SMPlugin.plugin.getConfig().getString("MySQL.table_onlineusers");
55 // TODO Onlines
56 private static String queryGetOnlines = "SELECT name, server, time FROM "
57 + SMPlugin.plugin.getConfig().getString("MySQL.table_onlineusers") + " WHERE online=1";
58 // TODO Tell
59 private static String querySetTell = "INSERT INTO `" + SMPlugin.plugin.getConfig().getString("MySQL.table_chat")
60 + "` (`sender`, `for`, `server`, `message`, `status`) VALUES ('%name%', '%player%', '%server%', '%message%', 0)";
61 // TODO CheckSMChat
62 private static String queryGetSMChat = "SELECT sender, server, message FROM "
63 + SMPlugin.plugin.getConfig().getString("MySQL.table_chat") + " WHERE `for`='%name%' AND `status`=0";
64 // TODO SetStatus
65 private static String querySetStatus = "UPDATE `" + SMPlugin.plugin.getConfig().getString("MySQL.table_chat")
66 + "` SET `status`='1' WHERE `for`='%name%' AND `status`=0";
67 // TODO CheckPlayer
68 private static String queryCheckPlayer = "SELECT name FROM "
69 + SMPlugin.plugin.getConfig().getString("MySQL.table_users") + " WHERE `name`='%name%'";
70 // TODO TellSender
71 private static String querySetTellSender = "SELECT sender FROM "
72 + SMPlugin.plugin.getConfig().getString("MySQL.table_chat") + " WHERE `status`=1 AND `for`='%name%'";
73 // TODO TrunceSMChat
74 private static String queryTruncateTable = "TRUNCATE `" + SMPlugin.plugin.getConfig().getString("MySQL.table_chat")
75 + "`";
76
77 public static Connection getConnection() throws SQLException {
78 Connection connection = null;
79 try {
80 connection = DriverManager.getConnection(
81 "jdbc:mysql://" + SMPlugin.plugin.getConfig().getString("MySQL.host") + ":"
82 + SMPlugin.plugin.getConfig().getString("MySQL.port") + "/"
83 + SMPlugin.plugin.getConfig().getString("MySQL.database")
84 + "?useUnicode=true&characterEncoding=UTF-8",
85 SMPlugin.plugin.getConfig().getString("MySQL.user"),
86 SMPlugin.plugin.getConfig().getString("MySQL.password"));
87 } catch (Exception e) {
88 log.severe(name + ": " + e.getMessage());
89 }
90 checkConnection(connection);
91 return connection;
92 }
93
94 private static boolean checkConnection(Connection conn) throws SQLException {
95 if (conn == null) {
96 log.severe("Could not connect to the database");
97 throw new SQLException();
98 }
99 if (!conn.isValid(5)) {
100 log.severe("Could not connect to the database.");
101 throw new SQLException();
102 }
103 return true;
104 }
105
106 public static void getList(CommandSender sender, int list) {
107 execute(queryUnbannedList, sender, null, list);
108 }
109
110 public static void getBanlist(CommandSender sender, int list) {
111 execute(queryBanlist, sender, null, list);
112 }
113
114 public static void getShopList(CommandSender sender, String player, int list) {
115 execute(queryShopList, sender, player, list);
116 }
117
118 public static void getPlayTime(CommandSender sender) {
119 execute(queryGetPlayTime, sender, null, null);
120 }
121
122 public static void getTopTime(CommandSender sender) {
123 execute(queryGetTopTime, sender, null, null);
124 }
125
126 public static void getOnlines(CommandSender sender, int list) {
127 execute(queryGetOnlines, sender, null, list);
128 }
129
130 public static void getSMChat(CommandSender sender) {
131 execute(queryGetSMChat, sender, null, null);
132 }
133
134 public static void setReason(CommandSender sender, String player, String reason) {
135 execute(querySetReason, sender, player, reason);
136 }
137
138 public static void setTell(CommandSender sender, String player, String message) {
139 execute(querySetTell, sender, player, message);
140 }
141
142 public static void setTellSender(CommandSender sender, String message) {
143 execute(querySetTellSender, sender, null, message);
144 }
145
146 public static void setStatus(CommandSender sender) {
147 execute(querySetStatus, null, sender.getName(), null);
148 }
149
150 public static void truncateTable() {
151 execute(queryTruncateTable, null, null, null);
152 }
153
154 public static boolean checkPlayer(String player) {
155 return execute(queryCheckPlayer, null, player, null);
156 }
157
158 private static boolean execute(String query, CommandSender sender, String player, int list) {
159 List<String> arraylist = new ArrayList<String>();
160 DateFormat shortTime = DateFormat.getDateTimeInstance();
161 Connection conn = null;
162 Statement stmt = null;
163 ResultSet rs = null;
164 String msg = null, oldquery = null;
165 boolean success = false;
166 int custom = 0;
167 try {
168 conn = getConnection();
169 stmt = conn.createStatement();
170 // TODO Shop
171 if (query == queryShopList) {
172 oldquery = query;
173 query = query.replace("%server%", SMPlugin.plugin.getConfig().getString("server"));
174 query = query.replace("%name%", player);
175 }
176 rs = stmt.executeQuery(query);
177 while (rs.next()) {
178 // TODO Banlist
179 if (query == queryBanlist) {
180 String name = rs.getString(1);
181 String admin = rs.getString(2);
182 String reason = rs.getString(3);
183 long time = rs.getLong(4);
184 long temptime = rs.getLong(5);
185 msg = SMPlugin.messages.getString("Forms.Banlist");
186 msg = msg.replace("%name%", name);
187 msg = msg.replace("%admin%", admin);
188 msg = msg.replace("%reason%", reason);
189 msg = msg.replace("%time%", shortTime.format(new Date(time * 1000L)));
190 msg = msg.replace("%temptime%", shortTime.format(new Date(temptime * 1000L)));
191 msg = msg.replace("01.01.1970 5:00:00", SMPlugin.messages.getString("Others.Permanent"));
192 arraylist.add(ChatColor.translateAlternateColorCodes('&', msg));
193 } else
194 // TODO List
195 if (query == queryUnbannedList) {
196 String name = rs.getString(1);
197 String time = rs.getString(2);
198 msg = SMPlugin.messages.getString("Forms.List");
199 msg = msg.replace("%name%", name);
200 msg = msg.replace("%time%", time);
201 arraylist.add(ChatColor.translateAlternateColorCodes('&', msg));
202 } else
203 // TODO Shop
204 if (oldquery == queryShopList) {
205 String itemid = rs.getString(1);
206 String summa = rs.getString(2);
207 String count = rs.getString(3);
208 msg = SMPlugin.messages.getString("Forms.Shop");
209 msg = msg.replace("%itemid%", itemid);
210 msg = msg.replace("%count%", count);
211 msg = msg.replace("%summa%", summa);
212 msg = msg.replace("%shop%", SMPlugin.plugin.getConfig().getString("server"));
213 arraylist.add(ChatColor.translateAlternateColorCodes('&', msg));
214 } else {
215 // TODO Onlines
216 if (query == queryGetOnlines) {
217 String name = rs.getString(1);
218 String server = rs.getString(2);
219 String time = rs.getString(3);
220 msg = SMPlugin.messages.getString("Forms.Onlines");
221 msg = msg.replace("%name%", name);
222 msg = msg.replace("%server%", server);
223 msg = msg.replace("%time%", time);
224 arraylist.add(ChatColor.translateAlternateColorCodes('&', msg));
225 }
226 }
227 custom++;
228 }
229 if (list > 1)
230 custom = custom - list * 10;
231 int l = custom;
232 while (custom > 0) {
233 // TODO Banlist
234 if (query == queryBanlist)
235 sender.sendMessage(
236 ChatColor.translateAlternateColorCodes('&', SMPlugin.messages.getString("Lists.Ban")));
237 else
238 // TODO List
239 if (query == queryUnbannedList)
240 sender.sendMessage(
241 ChatColor.translateAlternateColorCodes('&', SMPlugin.messages.getString("Lists.Unbanned")));
242 else
243 // TODO Shop
244 if (oldquery == queryShopList)
245 sender.sendMessage(ChatColor.translateAlternateColorCodes('&',
246 SMPlugin.messages.getString("Lists.Shop").replace("%name%", player)));
247 else
248 // TODO Onlines
249 if (query == queryGetOnlines)
250 sender.sendMessage(
251 ChatColor.translateAlternateColorCodes('&', SMPlugin.messages.getString("Lists.Onlines")));
252 for (int i = 1; custom > 0; i++) {
253 if (custom == l - 10)
254 custom = 0;
255 custom--;
256 if (list > 1)
257 sender.sendMessage(arraylist.get(custom).replace("%id%", String.valueOf(list * 10 - 10 + i)));
258 else
259 sender.sendMessage(arraylist.get(custom).replace("%id%", String.valueOf(i)));
260 }
261 }
262 } catch (SQLException ex) {
263 log.severe(name + ": " + ex.getMessage());
264 }
265 return false;
266 }
267
268 private static boolean execute(String query, CommandSender sender, String player, String reason) {
269 Connection conn = null;
270 Statement stmt = null;
271 PreparedStatement ps = null;
272 ResultSet rs = null;
273 String msg;
274 try {
275 conn = getConnection();
276 stmt = conn.createStatement();
277 // TODO PlayTime
278 if (query == queryGetPlayTime) {
279 query = query.replace("%name%", sender.getName());
280 rs = stmt.executeQuery(query);
281 long time = 0;
282 while (rs.next())
283 time = rs.getLong(1);
284 msg = ChatColor.translateAlternateColorCodes('&', SMPlugin.messages.getString("Forms.PlayTime"));
285 if (time > 60) {
286 if (time > 3600) {
287 if (time > 86400) {
288 time = time / 86400;
289 msg = msg.replace("%data%", SMPlugin.messages.getStringList("Data").get(3));
290 } else {
291 time = time / 3600;
292 msg = msg.replace("%data%", SMPlugin.messages.getStringList("Data").get(2));
293 }
294 } else {
295 time = time / 60;
296 msg = msg.replace("%data%", SMPlugin.messages.getStringList("Data").get(1));
297 }
298 } else
299 msg = msg.replace("%data%", SMPlugin.messages.getStringList("Data").get(0));
300 msg = msg.replace("%time%", String.valueOf(time));
301 sender.sendMessage(ChatColor.translateAlternateColorCodes('&', msg));
302 } else
303 // TODO Reason
304 if (query == querySetReason) {
305 query = query.replace("%name%", player);
306 query = query.replace("%reason%", reason);
307 query = query.replace("%admin%", sender.getName());
308 msg = ChatColor.translateAlternateColorCodes('&', SMPlugin.messages.getString("Success.SetReason"));
309 msg = msg.replace("%reason%", reason);
310 sender.sendMessage(ChatColor.translateAlternateColorCodes('&', msg));
311 } else
312 // TODO Tell
313 if (query == querySetTell) {
314 if (checkPlayer(player)) {
315 query = query.replace("%name%", sender.getName());
316 query = query.replace("%player%", name);
317 query = query.replace("%server%", SMPlugin.plugin.getConfig().getString("server"));
318 query = query.replace("%message%", reason);
319 msg = ChatColor.translateAlternateColorCodes('&', SMPlugin.messages.getString("Forms.Tell"));
320 msg = msg.replace("%player%", name);
321 msg = msg.replace("%message%", reason);
322 sender.sendMessage(ChatColor.translateAlternateColorCodes('&', msg));
323 } else {
324 msg = SMPlugin.messages.getString("Errors.NotFoundPlayer");
325 msg = msg.replace("%name%", player);
326 sender.sendMessage(ChatColor.translateAlternateColorCodes('&', msg));
327 return false;
328 }
329 } else
330 // TODO SMChat
331 if (query == queryGetSMChat) {
332 query = query.replace("%name%", sender.getName());
333 rs = stmt.executeQuery(query);
334 String name = null;
335 String server = null;
336 String message = null;
337 while (rs.next()) {
338 name = rs.getString(1);
339 server = rs.getString(2);
340 message = rs.getString(3);
341 }
342 if (name != null || server != null || message != null) {
343 msg = SMPlugin.messages.getString("Forms.TellFor");
344 msg = msg.replace("%player%", name);
345 msg = msg.replace("%server%", server);
346 msg = msg.replace("%message%", message);
347 sender.sendMessage(ChatColor.translateAlternateColorCodes('&', msg));
348 sender.sendMessage(ChatColor.translateAlternateColorCodes('&',
349 SMPlugin.messages.getString("Success.TellSender")));
350 }
351 } else
352 // TODO CheckPlayer
353 if (query == queryCheckPlayer) {
354 query = query.replace("%name%", player);
355 rs = stmt.executeQuery(query);
356 name = null;
357 while (rs.next()) {
358 name = rs.getString(1);
359 }
360 if (name == null) {
361 return false;
362 } else
363 return true;
364
365 } else
366 // TODO ClearSMChat
367 if (query == querySetStatus)
368 query = query.replace("%name%", player);
369 else
370 // TODO SetTellSender
371 if (query == querySetTellSender) {
372 query = query.replace("%name%", sender.getName());
373 rs = stmt.executeQuery(query);
374 name = null;
375 while (rs.next()) {
376 name = rs.getString(1);
377 }
378 if (name != null)
379 setTell(sender, name, reason);
380 }
381 ps = conn.prepareStatement(query);
382 if (ps.execute()) {
383 return true;
384 }
385 } catch (SQLException ex) {
386 log.severe(name + ": " + ex.getMessage());
387 }
388 return false;
389 }
390
391 public static boolean createTable() {
392 Connection conn = null;
393 Statement s = null;
394 ResultSet rs = null;
395 try {
396 conn = getConnection();
397 s = conn.createStatement();
398 s.executeUpdate(sqlMakeSMChat);
399 rs = s.executeQuery(sqlCheckTableExist);
400 if (rs.first()) {
401 rs.close();
402 s.close();
403 conn.close();
404 return true;
405 }
406 } catch (SQLException ex) {
407 log.severe(name + ": " + ex.getMessage());
408 } finally {
409 try {
410 if (rs != null)
411 rs.close();
412 if (s != null)
413 s.close();
414 if (conn != null)
415 conn.close();
416 } catch (SQLException ex) {
417 log.severe(name + ": " + ex.getMessage());
418 }
419 }
420 return false;
421 }
422}