public final void createAlliance(final MapleClient c, final String name) {
if (allianceid != 0) {
c.getPlayer().dropMessage(1, "You are already in an Alliance!");
return;
}
if (checkAllianceName(name)) {
try {
if (name.equals("") || id <= 0) {
return;
}
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement(
"INSERT INTO `alliances` (notice, name, guild1, guild2, guild3, guild4, guild5, rank1, rank2, rank3, rank4, rank5) VALUES ('', ?, ?, 0, 0, 0, 0, 'Master', 'Jr. Master', 'Member', 'Member', 'Member')");
ps.setString(1, name);
ps.setInt(2, id);
ps.executeUpdate();
ps.close();
ps = con.prepareStatement("SELECT id FROM alliances WHERE guild1 = ?");
ps.setInt(1, id);
ResultSet rs = ps.executeQuery();
if (rs.next()) {
allianceid = rs.getInt("id");
}
rs.close();
ps.close();
writeToDB(false);
c.getPlayer().dropMessage(1, "Alliance successfully created!");
} catch (SQLException a) {
}
} else {
c.getPlayer().dropMessage(1, "This name already exists.");
}
}
public static void editBBSThread(
MapleClient client, String title, String text, int icon, int localthreadid) {
MapleCharacter c = client.getPlayer();
if (c.getGuildId() <= 0) {
return; // expelled while viewing?
}
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement(
"UPDATE bbs_threads SET `name` = ?, `timestamp` = ?, "
+ "`icon` = ?, "
+ "`startpost` = ? WHERE guildid = ? AND localthreadid = ? AND (postercid = ? OR ?)");
ps.setString(1, title);
ps.setLong(2, System.currentTimeMillis());
ps.setInt(3, icon);
ps.setString(4, text);
ps.setInt(5, c.getGuildId());
ps.setInt(6, localthreadid);
ps.setInt(7, c.getId());
ps.setBoolean(8, c.getGuildRank() <= 2);
ps.execute();
ps.close();
displayThread(client, localthreadid);
} catch (SQLException se) {
log.error("SQLException: " + se.getLocalizedMessage(), se);
}
}
private final void writeToDB(final boolean bDisband) {
try {
Connection con = DatabaseConnection.getConnection();
if (!bDisband) {
StringBuilder buf =
new StringBuilder(
"UPDATE guilds SET GP = ?, logo = ?, logoColor = ?, logoBG = ?, logoBGColor = ?, ");
for (int i = 1; i < 6; i++) {
buf.append("rank" + i + "title = ?, ");
}
buf.append("capacity = ?, " + "notice = ?, alliance = ? WHERE guildid = ?");
PreparedStatement ps = con.prepareStatement(buf.toString());
ps.setInt(1, gp);
ps.setInt(2, logo);
ps.setInt(3, logoColor);
ps.setInt(4, logoBG);
ps.setInt(5, logoBGColor);
ps.setString(6, rankTitles[0]);
ps.setString(7, rankTitles[1]);
ps.setString(8, rankTitles[2]);
ps.setString(9, rankTitles[3]);
ps.setString(10, rankTitles[4]);
ps.setInt(11, capacity);
ps.setString(12, notice);
ps.setInt(13, allianceid);
ps.setInt(14, id);
ps.execute();
ps.close();
} else {
PreparedStatement ps =
con.prepareStatement(
"UPDATE characters SET guildid = 0, guildrank = 5 WHERE guildid = ?");
ps.setInt(1, id);
ps.execute();
ps.close();
// delete the alliance
if (allianceid > 0) {
if (getAlliance(null).getGuilds().get(0).getLeaderId() == getLeaderId()) {
ps = con.prepareStatement("DELETE FROM alliances WHERE id = ?");
ps.setInt(1, allianceid);
ps.execute();
ps.close();
}
}
ps = con.prepareStatement("DELETE FROM guilds WHERE guildid = ?");
ps.setInt(1, id);
ps.execute();
ps.close();
broadcast(GuildPacket.guildDisband(id));
}
} catch (SQLException se) {
System.err.println("Error saving guild to SQL" + se);
}
}
public final void setGuildNotice(final String notice) {
this.notice = notice;
broadcast(GuildPacket.guildNotice(id, notice));
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("UPDATE guilds SET notice = ? WHERE guildid = ?");
ps.setString(1, notice);
ps.setInt(2, id);
ps.execute();
ps.close();
} catch (SQLException e) {
System.err.println("Saving notice ERROR" + e);
}
}
public final void gainGP(final int amount) {
gp += amount;
guildMessage(GuildPacket.updateGP(id, gp));
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("UPDATE guilds SET gp = ? WHERE guildid = ?");
ps.setInt(1, this.gp);
ps.setInt(2, this.id);
ps.execute();
ps.close();
} catch (SQLException e) {
System.err.println("Saving guild point ERROR" + e);
}
}
public List<DropEntry> retrieveDropChances(int monsterid) {
if (drops.containsKey(monsterid)) {
return drops.get(monsterid);
}
List<DropEntry> ret = new LinkedList<DropEntry>();
if (monsterid > 9300183 && monsterid < 9300216) {
for (int i = 2022359; i < 2022367; i++) {
ret.add(new DropEntry(i, 10));
}
drops.put(monsterid, ret);
return ret;
} else if (monsterid > 9300215 && monsterid < 9300269) {
for (int i = 2022430; i < 2022434; i++) {
ret.add(new DropEntry(i, 3));
}
drops.put(monsterid, ret);
return ret;
}
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement(
"SELECT itemid, chance, monsterid, questid FROM monsterdrops WHERE monsterid = ?");
ps.setInt(1, monsterid);
ResultSet rs = ps.executeQuery();
MapleMonster theMonster = null;
while (rs.next()) {
int rowmonsterid = rs.getInt("monsterid");
int chance = rs.getInt("chance");
int questid = rs.getInt("questid");
if (rowmonsterid != monsterid && rowmonsterid != 0) {
if (theMonster == null) {
theMonster = MapleLifeFactory.getMonster(monsterid);
}
chance += theMonster.getLevel() * rowmonsterid;
}
ret.add(new DropEntry(rs.getInt("itemid"), chance, questid));
}
rs.close();
ps.close();
con.close();
} catch (Exception e) {
log.error("Error retrieving drop", e);
}
drops.put(monsterid, ret);
return ret;
}
public static void newBBSThread(
MapleClient client, String title, String text, int icon, boolean bNotice) {
MapleCharacter c = client.getPlayer();
if (c.getGuildId() <= 0) {
return; // expelled while viewing?
}
int nextId = 0;
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps;
if (!bNotice) // notice's local id is always 0, so we don't need to fetch it
{
ps =
con.prepareStatement(
"SELECT MAX(localthreadid) AS lastLocalId FROM bbs_threads WHERE guildid = ?");
ps.setInt(1, c.getGuildId());
ResultSet rs = ps.executeQuery();
rs.next();
nextId = rs.getInt("lastLocalId") + 1;
rs.close();
ps.close();
}
ps =
con.prepareStatement(
"INSERT INTO bbs_threads "
+ "(`postercid`, `name`, `timestamp`, `icon`, `startpost`, "
+ "`guildid`, `localthreadid`) "
+ "VALUES(?, ?, ?, ?, ?, ?, ?)");
ps.setInt(1, c.getId());
ps.setString(2, title);
ps.setLong(3, System.currentTimeMillis());
ps.setInt(4, icon);
ps.setString(5, text);
ps.setInt(6, c.getGuildId());
ps.setInt(7, nextId);
ps.execute();
ps.close();
displayThread(client, nextId);
} catch (SQLException se) {
log.error("SQLException: " + se.getLocalizedMessage(), se);
}
}
public static void newBBSReply(MapleClient client, int localthreadid, String text) {
MapleCharacter mc = client.getPlayer();
if (mc.getGuildId() <= 0) {
return;
}
Connection con = DatabaseConnection.getConnection();
try {
PreparedStatement ps =
con.prepareStatement(
"SELECT threadid FROM bbs_threads WHERE guildid = ? AND localthreadid = ?");
ps.setInt(1, mc.getGuildId());
ps.setInt(2, localthreadid);
ResultSet threadRS = ps.executeQuery();
if (!threadRS.next()) {
threadRS.close();
ps.close();
return; // thread no longer exists, deleted?
}
int threadid = threadRS.getInt("threadid");
threadRS.close();
ps.close();
ps =
con.prepareStatement(
"INSERT INTO bbs_replies "
+ "(`threadid`, `postercid`, `timestamp`, `content`) VALUES "
+ "(?, ?, ?, ?)");
ps.setInt(1, threadid);
ps.setInt(2, mc.getId());
ps.setLong(3, System.currentTimeMillis());
ps.setString(4, text);
ps.execute();
ps.close();
ps =
con.prepareStatement(
"UPDATE bbs_threads SET replycount = replycount + 1 WHERE threadid = ?");
ps.setInt(1, threadid);
ps.execute();
ps.close();
displayThread(client, localthreadid);
} catch (SQLException se) {
log.error("SQLException: " + se.getLocalizedMessage(), se);
}
}
public Properties getGameProperties() throws RemoteException {
Properties ret = new Properties(WorldServer.getInstance().getWorldProp());
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement("SELECT * FROM channelconfig WHERE channelid = ?");
ps.setInt(1, dbId);
ResultSet rs = ps.executeQuery();
while (rs.next()) {
ret.setProperty(rs.getString("name"), rs.getString("value"));
}
rs.close();
ps.close();
} catch (SQLException ex) {
log.error("Could not retrieve channel configuration", ex);
}
return ret;
}
public final boolean checkAllianceName(final String name) {
boolean canCreate = true;
if (name.length() < 4 && name.length() > 13) {
canCreate = false;
}
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT * FROM alliances WHERE name = ?");
ps.setString(1, name);
ResultSet rs = ps.executeQuery();
if (rs.first()) {
canCreate = false;
}
rs.close();
ps.close();
} catch (SQLException e) {
}
return canCreate;
}
public static void deleteBBSThread(MapleClient client, int localthreadid) {
MapleCharacter mc = client.getPlayer();
if (mc.getGuildId() <= 0) {
return;
}
Connection con = DatabaseConnection.getConnection();
try {
PreparedStatement ps =
con.prepareStatement(
"SELECT threadid, postercid FROM bbs_threads WHERE guildid = ? AND localthreadid = ?");
ps.setInt(1, mc.getGuildId());
ps.setInt(2, localthreadid);
ResultSet threadRS = ps.executeQuery();
if (!threadRS.next()) {
threadRS.close();
ps.close();
return; // thread no longer exists, deleted?
}
if (mc.getId() != threadRS.getInt("postercid")
&& mc.getGuildRank() > 2) { // but let all master/jrs delete
threadRS.close();
ps.close();
return; // [hax] deleting a thread that he didn't make
}
int threadid = threadRS.getInt("threadid");
threadRS.close();
ps.close();
ps = con.prepareStatement("DELETE FROM bbs_replies WHERE threadid = ?");
ps.setInt(1, threadid);
ps.execute();
ps.close();
ps = con.prepareStatement("DELETE FROM bbs_threads WHERE threadid = ?");
ps.setInt(1, threadid);
ps.execute();
ps.close();
} catch (SQLException se) {
log.error("SQLException: " + se.getLocalizedMessage(), se);
}
}
public static void deleteBBSReply(MapleClient client, int replyid) {
MapleCharacter mc = client.getPlayer();
if (mc.getGuildId() <= 0) {
return;
}
int threadid;
Connection con = DatabaseConnection.getConnection();
try {
PreparedStatement ps =
con.prepareStatement("SELECT postercid, threadid FROM bbs_replies WHERE replyid = ?");
ps.setInt(1, replyid);
ResultSet rs = ps.executeQuery();
if (!rs.next()) {
rs.close();
ps.close();
return; // reply no longer exists, deleted already?
}
if (mc.getId() != rs.getInt("postercid") && mc.getGuildRank() > 2) {
rs.close();
ps.close();
return; // [hax] deleting a reply that he didn't make
}
threadid = rs.getInt("threadid");
rs.close();
ps.close();
ps = con.prepareStatement("DELETE FROM bbs_replies WHERE replyid = ?");
ps.setInt(1, replyid);
ps.execute();
ps.close();
ps =
con.prepareStatement(
"UPDATE bbs_threads SET replycount = replycount - 1 WHERE threadid = ?");
ps.setInt(1, threadid);
ps.execute();
ps.close();
displayThread(client, threadid, false);
} catch (SQLException se) {
log.error("SQLException: " + se.getLocalizedMessage(), se);
}
}
public static void listBBSThreads(MapleClient c, int start) {
int gid = c.getPlayer().getGuildId();
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement(
"SELECT * FROM bbs_threads WHERE guildid = ? ORDER BY localthreadid DESC");
ps.setInt(1, gid);
ResultSet rs = ps.executeQuery();
c.getSession().write(MaplePacketCreator.BBSThreadList(rs, start));
rs.close();
ps.close();
} catch (SQLException se) {
log.error("SQLException: " + se.getLocalizedMessage(), se);
}
}
public final boolean increaseCapacity() {
if (capacity >= 100 || ((capacity + 5) > 100)) {
return false;
}
capacity += 5;
broadcast(GuildPacket.guildCapacityChange(this.id, this.capacity));
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement("UPDATE guilds SET capacity = ? WHERE guildid = ?");
ps.setInt(1, this.capacity);
ps.setInt(2, this.id);
ps.execute();
ps.close();
} catch (SQLException e) {
System.err.println("Saving guild capacity ERROR" + e);
}
return true;
}
public final void expelMember(
final MapleGuildCharacter initiator, final String name, final int cid) {
final Iterator<MapleGuildCharacter> itr = members.iterator();
while (itr.hasNext()) {
final MapleGuildCharacter mgc = itr.next();
if (mgc.getId() == cid && initiator.getGuildRank() < mgc.getGuildRank()) {
broadcast(GuildPacket.memberLeft(mgc, true));
bDirty = true;
members.remove(mgc);
try {
if (mgc.isOnline()) {
WorldRegistryImpl.getInstance().getChannel(mgc.getChannel()).setGuildAndRank(cid, 0, 5);
} else {
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement(
"INSERT INTO notes (`to`, `from`, `message`, `timestamp`) VALUES (?, ?, ?, ?)");
ps.setString(1, mgc.getName());
ps.setString(2, initiator.getName());
ps.setString(3, "You have been expelled from the guild.");
ps.setLong(4, System.currentTimeMillis());
ps.executeUpdate();
ps.close();
} catch (SQLException e) {
System.err.println("Error sending guild msg 'expelled'." + e);
}
WorldRegistryImpl.getInstance()
.getChannel(1)
.setOfflineGuildStatus((short) 0, (byte) 5, cid);
}
} catch (RemoteException re) {
re.printStackTrace();
return;
}
}
}
}
public final void changeRankTitle(final String[] ranks) {
for (int i = 0; i < 5; i++) {
rankTitles[i] = ranks[i];
}
broadcast(GuildPacket.rankTitleChange(id, ranks));
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement(
"UPDATE guilds SET rank1title = ?, rank2title = ?, rank3title = ?, rank4title = ?, rank5title = ? WHERE guildid = ?");
for (int i = 0; i < 5; i++) {
ps.setString(i + 1, rankTitles[i]);
}
ps.setInt(6, id);
ps.execute();
ps.close();
} catch (SQLException e) {
System.err.println("Saving rankTitle ERROR" + e);
}
}
public MapleGuild(final int guildid) { // retrieves the guild from database, with guildid
members = new CopyOnWriteArrayList<MapleGuildCharacter>();
try { // first read the guild information
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT * FROM guilds WHERE guildid=" + guildid);
ResultSet rs = ps.executeQuery();
if (!rs
.first()) { // no result... most likely to be someone from a disbanded guild that got
// rolled back
rs.close();
ps.close();
id = -1;
return;
}
id = guildid;
name = rs.getString("name");
gp = rs.getInt("GP");
logo = rs.getInt("logo");
logoColor = rs.getInt("logoColor");
logoBG = rs.getInt("logoBG");
logoBGColor = rs.getInt("logoBGColor");
capacity = rs.getInt("capacity");
rankTitles[0] = rs.getString("rank1title");
rankTitles[1] = rs.getString("rank2title");
rankTitles[2] = rs.getString("rank3title");
rankTitles[3] = rs.getString("rank4title");
rankTitles[4] = rs.getString("rank5title");
leader = rs.getInt("leader");
notice = rs.getString("notice");
signature = rs.getInt("signature");
allianceid = rs.getInt("alliance");
rs.close();
ps.close();
} catch (SQLException se) {
System.err.println("unable to read guild information from sql" + se);
return;
}
}
// function to create guild, returns the guild id if successful, 0 if not
public static final int createGuild(final int leaderId, final String name) {
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT guildid FROM guilds WHERE name = ?");
ps.setString(1, name);
ResultSet rs = ps.executeQuery();
if (rs.first()) { // name taken
rs.close();
ps.close();
return 0;
}
ps.close();
rs.close();
ps =
con.prepareStatement(
"INSERT INTO guilds (`leader`, `name`, `signature`, `alliance`) VALUES (?, ?, ?, 0)");
ps.setInt(1, leaderId);
ps.setString(2, name);
ps.setInt(3, (int) System.currentTimeMillis());
ps.execute();
ps.close();
ps = con.prepareStatement("SELECT guildid FROM guilds WHERE leader = ?");
ps.setInt(1, leaderId);
rs = ps.executeQuery();
rs.first();
final int result = rs.getInt("guildid");
rs.close();
ps.close();
return result;
} catch (SQLException se) {
System.err.println("SQL THROW" + se);
return 0;
}
}
public final void setGuildEmblem(
final short bg, final byte bgcolor, final short logo, final byte logocolor) {
this.logoBG = bg;
this.logoBGColor = bgcolor;
this.logo = logo;
this.logoColor = logocolor;
broadcast(null, -1, BCOp.EMBLEMCHANGE);
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps =
con.prepareStatement(
"UPDATE guilds SET logo = ?, logoColor = ?, logoBG = ?, logoBGColor = ? WHERE guildid = ?");
ps.setInt(1, logo);
ps.setInt(2, logoColor);
ps.setInt(3, logoBG);
ps.setInt(4, logoBGColor);
ps.setInt(5, id);
ps.execute();
ps.close();
} catch (SQLException e) {
System.err.println("Saving guild logo / BG colo ERROR" + e);
}
}
public MapleGuild(final MapleGuildCharacter initiator) {
super();
int guildid = initiator.getGuildId();
members = new CopyOnWriteArrayList<MapleGuildCharacter>();
try {
Connection con = DatabaseConnection.getConnection();
PreparedStatement ps = con.prepareStatement("SELECT * FROM guilds WHERE guildid=" + guildid);
ResultSet rs = ps.executeQuery();
if (!rs.first()) {
rs.close();
ps.close();
id = -1;
return;
}
id = guildid;
name = rs.getString("name");
gp = rs.getInt("GP");
logo = rs.getInt("logo");
logoColor = rs.getInt("logoColor");
logoBG = rs.getInt("logoBG");
logoBGColor = rs.getInt("logoBGColor");
capacity = rs.getInt("capacity");
rankTitles[0] = rs.getString("rank1title");
rankTitles[1] = rs.getString("rank2title");
rankTitles[2] = rs.getString("rank3title");
rankTitles[3] = rs.getString("rank4title");
rankTitles[4] = rs.getString("rank5title");
leader = rs.getInt("leader");
notice = rs.getString("notice");
signature = rs.getInt("signature");
allianceid = rs.getInt("alliance");
rs.close();
ps.close();
ps =
con.prepareStatement(
"SELECT id, name, level, job, guildrank FROM characters WHERE guildid = ? ORDER BY guildrank ASC, name ASC");
ps.setInt(1, guildid);
rs = ps.executeQuery();
if (!rs.first()) {
System.err.println("No members in guild. Impossible...");
rs.close();
ps.close();
return;
}
do {
members.add(
new MapleGuildCharacter(
rs.getInt("id"),
rs.getShort("level"),
rs.getString("name"),
(byte) -1,
rs.getInt("job"),
rs.getInt("guildrank"),
guildid,
false));
} while (rs.next());
setOnline(initiator.getId(), true, initiator.getChannel());
rs.close();
ps.close();
} catch (SQLException se) {
System.err.println("unable to read guild information from sql" + se);
return;
}
}
public static void displayThread(MapleClient client, int threadid, boolean bIsThreadIdLocal) {
MapleCharacter mc = client.getPlayer();
if (mc.getGuildId() <= 0) {
return;
}
Connection con = DatabaseConnection.getConnection();
try {
PreparedStatement ps =
con.prepareStatement(
"SELECT * FROM bbs_threads WHERE guildid = ? AND "
+ (bIsThreadIdLocal ? "local" : "")
+ "threadid = ?");
ps.setInt(1, mc.getGuildId());
ps.setInt(2, threadid);
ResultSet threadRS = ps.executeQuery();
if (!threadRS.next()) {
threadRS.close();
ps.close();
return; // thread no longer exists, deleted?
}
ResultSet repliesRS = null;
PreparedStatement ps2 = null;
if (threadRS.getInt("replycount") > 0) {
ps2 = con.prepareStatement("SELECT * FROM bbs_replies WHERE threadid = ?");
ps2.setInt(1, !bIsThreadIdLocal ? threadid : threadRS.getInt("threadid"));
repliesRS = ps2.executeQuery();
// the lack of repliesRS.next() is intentional
}
client
.getSession()
.write(
MaplePacketCreator.showThread(
bIsThreadIdLocal ? threadid : threadRS.getInt("localthreadid"),
threadRS,
repliesRS));
threadRS.close();
ps.close();
if (ps2 != null) {
ps2.close();
repliesRS.close();
}
} catch (SQLException se) {
log.error("SQLException: " + se.getLocalizedMessage(), se);
} catch (RuntimeException re) {
log.error(
"The number of reply rows does not match the replycount in thread. Thread Id = "
+ re.getMessage(),
re);
try {
PreparedStatement ps = con.prepareStatement("DELETE FROM bbs_threads WHERE threadid = ?");
ps.setInt(1, Integer.parseInt(re.getMessage()));
ps.execute();
ps.close();
ps = con.prepareStatement("DELETE FROM bbs_replies WHERE threadid = ?");
ps.setInt(1, Integer.parseInt(re.getMessage()));
ps.execute();
ps.close();
} catch (Exception e) {
}
}
}