1
2 package org.inigma.waragent.crud;
3
4 import java.io.IOException;
5 import java.sql.Connection;
6 import java.sql.ResultSet;
7 import java.sql.SQLException;
8 import java.sql.Timestamp;
9 import java.util.Collection;
10 import java.util.List;
11 import java.util.Properties;
12 import java.util.UUID;
13
14 import org.inigma.iniglet.utils.QueryHandler;
15 import org.inigma.utopia.Account;
16 import org.inigma.utopia.Army;
17 import org.inigma.utopia.Coordinate;
18 import org.inigma.utopia.Kingdom;
19 import org.inigma.utopia.Military;
20 import org.inigma.utopia.Personality;
21 import org.inigma.utopia.Province;
22 import org.inigma.utopia.Race;
23 import org.inigma.utopia.Rank;
24 import org.inigma.utopia.Relation;
25 import org.inigma.utopia.Science;
26 import org.inigma.utopia.Stance;
27 import org.inigma.utopia.Survey;
28
29 public class AccountCrud extends AbstractCrud {
30 protected static final Properties SQL = new Properties();
31 protected static QueryHandler<Account> rshAccount = null;
32 static {
33 try {
34 SQL.load(AbstractCrud.class.getResourceAsStream("/org/inigma/waragent/account-sql.properties"));
35 } catch (IOException e) {
36 throw new RuntimeException("Unable to load sql queries");
37 }
38
39 try {
40 rshAccount = new QueryHandler<Account>("handleAccount", new AccountResultSetHandler());
41 } catch (SQLException e) {
42 throw new RuntimeException("Unable to initiate account handler", e);
43 }
44 }
45
46 public static Collection<Account> getAccountList() throws SQLException {
47 return rshAccount.selectList(connection, SQL.getProperty("select.account.list"));
48 }
49
50 public static Account getActiveAccount() throws SQLException {
51 String id = configuration.getString("active.account");
52 Account account = rshAccount.select(connection, SQL.getProperty("select.account"), id);
53 if (account == null) {
54 id = UUID.randomUUID().toString();
55 String provinceId = UUID.randomUUID().toString();
56 account = new Account(id, provinceId);
57 configuration.put("active.account", id);
58 AccountCrud crud = new AccountCrud();
59 crud.saveAccount();
60 AbstractCrud.commit();
61 }
62 return account;
63 }
64
65 public static void setActiveAccount(Account account) {
66 configuration.put("active.account", account.getId());
67 }
68
69 protected Account account;
70 protected QueryHandler<Kingdom> rshKingdom;
71 protected QueryHandler<Long> rshLong;
72 protected QueryHandler<Military> rshMilitary;
73 protected QueryHandler<Army> rshArmy;
74 protected QueryHandler<Province> rshProvince;
75 protected QueryHandler<Science> rshScience;
76 protected QueryHandler<Survey> rshSurvey;
77
78 public AccountCrud() throws SQLException {
79 this(getActiveAccount());
80 }
81
82 public AccountCrud(Account account) {
83 try {
84 this.rshKingdom = new QueryHandler<Kingdom>("handleKingdom", this);
85 this.rshLong = new QueryHandler<Long>(QueryHandler.HANDLE_LONG);
86 this.rshMilitary = new QueryHandler<Military>("handleMilitary", this);
87 this.rshArmy = new QueryHandler<Army>("handleArmy", this);
88 this.rshProvince = new QueryHandler<Province>("handleProvince", this);
89 this.rshScience = new QueryHandler<Science>("handleScience", this);
90 this.rshSurvey = new QueryHandler<Survey>("handleSurvey", this);
91 } catch (SQLException e) {
92 throw new RuntimeException("Unable to initialize data handlers");
93 }
94 this.account = account;
95 }
96
97 public Account getAccount() {
98 return account;
99 }
100
101 public List<Kingdom> getKingdomSyncList() throws SQLException {
102 return rshKingdom.selectList(connection, SQL.getProperty("sync.select.kingdom.list"), account.getId(),
103 account.getLastSync());
104 }
105
106 public List<Province> getProvinceSyncList() throws SQLException {
107 return rshProvince.selectList(connection, SQL.getProperty("sync.select.province.list"), account.getId(),
108 account.getLastSync(), account.getLastSync(), account.getLastSync(), account.getLastSync());
109 }
110
111 public Kingdom getKingdom(Coordinate cord) throws SQLException {
112 Kingdom kingdom = rshKingdom.select(connection, SQL.getProperty("select.kingdom"), account.getId(),
113 cord.getKingdom(), cord.getIsland());
114 if (kingdom == null) {
115 kingdom = new Kingdom(cord);
116 saveKingdom(kingdom);
117 }
118 return kingdom;
119 }
120
121 public Kingdom getKingdom(String id) throws SQLException {
122 return rshKingdom.select(connection, SQL.getProperty("select.kingdom.by.id"), account.getId(), id);
123 }
124
125 public List<Kingdom> getKingdomList() throws SQLException {
126 return rshKingdom.selectList(connection, SQL.getProperty("select.kingdom.list"), account.getId());
127 }
128
129 public Province getProvince() throws SQLException {
130 return getProvince(account.getProvinceId());
131 }
132
133 public Province getProvince(String provinceId) throws SQLException {
134 return rshProvince.select(connection, SQL.getProperty("select.province.by.id"), provinceId);
135 }
136
137 public Province getProvinceByName(String provinceName) throws SQLException {
138 return rshProvince.select(connection, SQL.getProperty("select.province.by.name"), provinceName, account.getId());
139 }
140
141 public List<Province> getProvinces(String kingdomId) throws SQLException {
142 return rshProvince.selectList(connection, SQL.getProperty("select.provinces.by.kingdom.id"), kingdomId);
143 }
144
145 public boolean saveAccount() throws SQLException {
146 Timestamp lastSync = QueryHandler.toTimestamp(account.getLastSync());
147 int rows = QueryHandler.update(connection, SQL.getProperty("update.account"), account.getProvinceId(),
148 lastSync, account.getSyncUrl(), account.getSyncLogin(), account.getSyncPassword(), account.getId());
149 if (rows == 0) {
150 rows = QueryHandler.insert(connection, SQL.getProperty("insert.account"), account.getId(),
151 account.getProvinceId(), lastSync, account.getSyncUrl(), account.getSyncLogin(),
152 account.getSyncPassword());
153 }
154 return rows > 0;
155 }
156
157 public boolean saveKingdom(Kingdom kingdom) throws SQLException {
158 Timestamp lastUpdate = QueryHandler.toTimestamp(kingdom.getLastUpdate());
159 int rows = QueryHandler.update(connection, SQL.getProperty("update.kingdom"), lastUpdate, kingdom.getName(),
160 kingdom.getRelation().ordinal(), kingdom.getStance().ordinal(), kingdom.getWarCount(),
161 kingdom.getWarWins(), kingdom.getWarNetworthDiff(), account.getId(),
162 kingdom.getLocation().getKingdom(), kingdom.getLocation().getIsland());
163 if (rows == 0) {
164 rows = QueryHandler.insert(connection, SQL.getProperty("insert.kingdom"), kingdom.getId(), account.getId(),
165 kingdom.getLocation().getKingdom(), kingdom.getLocation().getIsland(), lastUpdate,
166 kingdom.getName(), kingdom.getRelation().ordinal(), kingdom.getStance().ordinal(),
167 kingdom.getWarCount(), kingdom.getWarWins(), kingdom.getWarNetworthDiff());
168 }
169 commit();
170 return rows > 0;
171 }
172
173 public boolean saveProvince(Province province) throws SQLException {
174 String gender = "M";
175 if (province.isGender()) {
176 gender = "F";
177 }
178 if (province.getKingdomId() == null) {
179 Kingdom kingdom = getKingdom(province.getCoordinate());
180 province.setKingdomId(kingdom.getId());
181 }
182
183 boolean updateMode = true;
184 QueryHandler<String> qh = new QueryHandler<String>("handleString");
185 String string = qh.select(connection, SQL.getProperty("find.province.by.id"), province.getId());
186 if (string == null) {
187 updateMode = false;
188 }
189
190 if (updateMode) {
191 QueryHandler.update(connection, SQL.getProperty("update.province"), province.getKingdomId(),
192 province.getName(), province.getLastUpdate(), province.getAcres(), gender, province.getLeader(),
193 province.getNetworth(), province.getRace().ordinal(), province.getRank().ordinal(),
194 province.getPersonality().ordinal(), province.getPeasants(), province.getGold(), province.getFood(),
195 province.getRunes(), province.getTradeBalance(), province.getThieves(), province.getWizards(),
196 province.getSoldiers(), province.getOffspecs(), province.getDefspecs(), province.getElites(),
197 province.getHorses(), province.getPrisoners(), province.getOffense(), province.getDefense(),
198 province.getId());
199 } else {
200 QueryHandler.insert(connection, SQL.getProperty("insert.province"), province.getId(),
201 province.getKingdomId(), province.getLastUpdate(), province.getName(), province.getAcres(), gender,
202 province.getLeader(), province.getNetworth(), province.getRace().ordinal(),
203 province.getRank().ordinal(), province.getPersonality().ordinal(), province.getPeasants(),
204 province.getGold(), province.getFood(), province.getRunes(), province.getTradeBalance(),
205 province.getThieves(), province.getWizards(), province.getSoldiers(), province.getOffspecs(),
206 province.getDefspecs(), province.getElites(), province.getHorses(), province.getPrisoners(),
207 province.getOffense(), province.getDefense());
208 }
209
210 saveProvinceDetails(province, updateMode);
211
212 Messaging.fireNotification(province);
213 commit();
214 return true;
215 }
216
217
218 public boolean saveProvince(Province province, boolean minimode) throws SQLException {
219 if (minimode) {
220 return saveProvinceMini(province);
221 }
222 return saveProvince(province);
223 }
224
225 protected Army handleArmy(ResultSet rs, Connection conn) throws SQLException {
226 Army army = new Army();
227 army.setId(rs.getString("id"));
228 army.setGenerals(rs.getInt("generals"));
229 army.setReturnTime(QueryHandler.toCalendar(rs.getTimestamp("eta")));
230 army.setSoldiers(rs.getInt("soldiers"));
231 army.setOffspecs(rs.getInt("offspecs"));
232 army.setDefspecs(rs.getInt("defspecs"));
233 army.setElites(rs.getInt("elites"));
234 army.setHorses(rs.getInt("horses"));
235 army.setSpoils(rs.getInt("spoils"));
236 return army;
237 }
238
239 protected Kingdom handleKingdom(ResultSet rs, Connection conn) throws SQLException {
240 Coordinate coord = new Coordinate(rs.getInt("kingdom"), rs.getInt("island"));
241 Kingdom kingdom = new Kingdom(coord);
242 kingdom.setId(rs.getString("id"));
243 kingdom.setLastUpdate(QueryHandler.toCalendar(rs.getTimestamp("last_update")));
244 kingdom.setName(rs.getString("name"));
245 kingdom.setRelation(Relation.values()[rs.getInt("relation")]);
246 kingdom.setStance(Stance.values()[rs.getInt("stance")]);
247 kingdom.setWarCount(rs.getInt("war_count"));
248 kingdom.setWarWins(rs.getInt("war_win"));
249 kingdom.setWarNetworthDiff(rs.getInt("war_nwdiff"));
250 kingdom.setTotalAcres(rshLong.select(conn, SQL.getProperty("select.kingdom.acres"), kingdom.getId()));
251 kingdom.setTotalNetworth(rshLong.select(conn, SQL.getProperty("select.kingdom.networth"), kingdom.getId()));
252 return kingdom;
253 }
254
255 protected Military handleMilitary(ResultSet rs, Connection conn) throws SQLException {
256 Military military = new Military();
257 military.setId(rs.getString("id"));
258 military.setLastUpdate(QueryHandler.toCalendar(rs.getTimestamp("last_update")));
259 military.setOffense(rs.getInt("offense"));
260 military.setDefense(rs.getInt("defense"));
261 military.setRaw("Y".equals(rs.getString("raw_ind")));
262 return military;
263 }
264
265 protected Province handleProvince(ResultSet rs, Connection conn) throws SQLException {
266 Province province = new Province();
267 province.setId(rs.getString("id"));
268 province.setKingdomId(rs.getString("kingdom_id"));
269 if (province.getKingdomId() == null) {
270 province.setCoordinate(Coordinate.UNKNOWN);
271 } else {
272 province.setCoordinate(getKingdom(province.getKingdomId()).getLocation());
273 }
274 province.setLastUpdate(QueryHandler.toCalendar(rs.getTimestamp("last_update")));
275 province.setAcres(rs.getInt("acres"));
276 province.setGender("F".equals(rs.getString("gender")));
277 province.setName(rs.getString("name"));
278 province.setLeader(rs.getString("leader"));
279 province.setNetworth(rs.getInt("networth"));
280 province.setRace(Race.values()[rs.getInt("race")]);
281 province.setRank(Rank.values()[rs.getInt("rank")]);
282 province.setPersonality(Personality.values()[rs.getInt("personality")]);
283 province.setPeasants(rs.getInt("peasants"));
284 province.setGold(rs.getInt("gold"));
285 province.setFood(rs.getInt("food"));
286 province.setRunes(rs.getInt("runes"));
287 province.setTradeBalance(rs.getInt("trade"));
288 province.setThieves(rs.getInt("thieves"));
289 province.setWizards(rs.getInt("wizards"));
290 province.setSoldiers(rs.getInt("soldiers"));
291 province.setOffspecs(rs.getInt("offspecs"));
292 province.setDefspecs(rs.getInt("defspecs"));
293 province.setElites(rs.getInt("elites"));
294 province.setHorses(rs.getInt("horses"));
295 province.setPrisoners(rs.getInt("prisoners"));
296 province.setOffense(rs.getInt("offense"));
297 province.setDefense(rs.getInt("defense"));
298
299 Science science = rshScience.select(connection, SQL.getProperty("select.science"), province.getId());
300 if (science != null) {
301 science.setProvince(province);
302 province.setScience(science);
303 }
304
305 Survey survey = rshSurvey.select(conn, SQL.getProperty("select.survey"), province.getId());
306 if (survey != null) {
307 survey.setProvince(province);
308 province.setSurvey(survey);
309 }
310
311 Military military = rshMilitary.select(connection, SQL.getProperty("select.military"), province.getId());
312 if (military != null) {
313 military.setProvince(province);
314 province.setMilitary(military);
315
316 List<Army> armyList = rshArmy.selectList(connection, SQL.getProperty("select.army"), military.getId());
317 for (Army army : armyList) {
318 army.setMilitary(military);
319 }
320 military.setArmies(armyList);
321 }
322
323 return province;
324 }
325
326 protected Science handleScience(ResultSet rs, Connection conn) throws SQLException {
327 Science science = new Science();
328 science.setId(rs.getString("id"));
329 science.setLastUpdate(QueryHandler.toCalendar(rs.getTimestamp("last_update")));
330 science.setAlchemy(rs.getInt("alchemy"));
331 science.setTools(rs.getInt("tools"));
332 science.setHousing(rs.getInt("housing"));
333 science.setFood(rs.getInt("food"));
334 science.setMilitary(rs.getInt("military"));
335 science.setCrime(rs.getInt("crime"));
336 science.setChanneling(rs.getInt("channeling"));
337 return science;
338 }
339
340 protected Survey handleSurvey(ResultSet rs, Connection conn) throws SQLException {
341 Survey survey = new Survey();
342 survey.setId(rs.getString("id"));
343 survey.setLastUpdate(QueryHandler.toCalendar(rs.getTimestamp("last_update")));
344 survey.setEfficiency(rs.getFloat("efficiency"));
345 survey.setBarren(rs.getInt("barren"));
346 survey.setHomes(rs.getInt("homes"));
347 survey.setFarms(rs.getInt("farms"));
348 survey.setMills(rs.getInt("mills"));
349 survey.setBanks(rs.getInt("banks"));
350 survey.setTrainingGrounds(rs.getInt("training_grounds"));
351 survey.setBarracks(rs.getInt("barracks"));
352 survey.setArmories(rs.getInt("armories"));
353 survey.setForts(rs.getInt("forts"));
354 survey.setGuardStations(rs.getInt("guard_stations"));
355 survey.setHospitals(rs.getInt("hospitals"));
356 survey.setGuilds(rs.getInt("guilds"));
357 survey.setTowers(rs.getInt("towers"));
358 survey.setThievesDens(rs.getInt("thief_dens"));
359 survey.setWatchtowers(rs.getInt("watchtowers"));
360 survey.setLibraries(rs.getInt("libraries"));
361 survey.setSchools(rs.getInt("schools"));
362 survey.setStables(rs.getInt("stables"));
363 survey.setDungeons(rs.getInt("dungeons"));
364 return survey;
365 }
366
367 private boolean saveProvinceMini(Province province) throws SQLException {
368 String gender = "M";
369 if (province.isGender()) {
370 gender = "F";
371 }
372
373 boolean updateMode = true;
374 int rows = QueryHandler.update(connection, SQL.getProperty("update.province.mini"), province.getAcres(),
375 gender, province.getNetworth(), province.getRace().ordinal(), province.getRank().ordinal(),
376 province.getKingdomId(), province.getName());
377 if (rows == 0) {
378 updateMode = false;
379 rows = QueryHandler.insert(connection, SQL.getProperty("insert.province.mini"), province.getId(),
380 province.getKingdomId(), province.getName(), QueryHandler.toTimestamp(province.getLastUpdate()),
381 province.getAcres(), gender, province.getNetworth(), province.getRace().ordinal(),
382 province.getRank().ordinal(), province.getPersonality().ordinal());
383 }
384
385 saveProvinceDetails(province, updateMode);
386 commit();
387 return rows > 0;
388 }
389
390 private void saveProvinceDetails(Province province, boolean updateMode) throws SQLException {
391 Military military = province.getMilitary();
392 Science science = province.getScience();
393 Survey survey = province.getSurvey();
394 String militaryRaw = "N";
395 if (military.isRaw()) {
396 militaryRaw = "Y";
397 }
398
399 if (updateMode) {
400 QueryHandler.update(connection, SQL.getProperty("update.science"), science.getLastUpdate(),
401 science.getAlchemy(), science.getTools(), science.getHousing(), science.getFood(),
402 science.getMilitary(), science.getCrime(), science.getChanneling(), science.getProvince().getId());
403 QueryHandler.update(connection, SQL.getProperty("update.military"), military.getLastUpdate(),
404 military.getOffense(), military.getDefense(), militaryRaw, military.getProvince().getId());
405 QueryHandler.update(connection, SQL.getProperty("update.survey"), survey.getLastUpdate(), survey.getEfficiency(),
406 survey.getBarren(), survey.getHomes(), survey.getFarms(), survey.getMills(), survey.getBanks(),
407 survey.getTrainingGrounds(), survey.getBarracks(), survey.getArmories(), survey.getForts(),
408 survey.getGuardStations(), survey.getHospitals(), survey.getGuilds(), survey.getTowers(),
409 survey.getThievesDens(), survey.getWatchtowers(), survey.getLibraries(), survey.getSchools(),
410 survey.getStables(), survey.getDungeons(), survey.getProvince().getId());
411 } else {
412 QueryHandler.insert(connection, SQL.getProperty("insert.science"), science.getId(),
413 science.getProvince().getId(), science.getLastUpdate(), science.getAlchemy(), science.getTools(),
414 science.getHousing(), science.getFood(), science.getMilitary(), science.getCrime(),
415 science.getChanneling());
416 QueryHandler.insert(connection, SQL.getProperty("insert.military"), military.getId(),
417 military.getProvince().getId(), military.getLastUpdate(), military.getOffense(),
418 military.getDefense(), militaryRaw);
419 QueryHandler.insert(connection, SQL.getProperty("insert.survey"), survey.getId(),
420 survey.getProvince().getId(), survey.getLastUpdate(), survey.getEfficiency(), survey.getBarren(), survey.getHomes(),
421 survey.getFarms(), survey.getMills(), survey.getBanks(), survey.getTrainingGrounds(),
422 survey.getBarracks(), survey.getArmories(), survey.getForts(), survey.getGuardStations(),
423 survey.getHospitals(), survey.getGuilds(), survey.getTowers(), survey.getThievesDens(),
424 survey.getWatchtowers(), survey.getLibraries(), survey.getSchools(), survey.getStables(),
425 survey.getDungeons());
426 }
427
428 QueryHandler.delete(connection, SQL.getProperty("delete.army.by.militaryId"), military.getId());
429 for (Army army : military.getArmies()) {
430 QueryHandler.insert(connection, SQL.getProperty("insert.army"), army.getId(),
431 army.getMilitary().getId(),
432 army.getGenerals(), army.getSoldiers(), army.getOffspecs(), army.getDefspecs(), army.getElites(),
433 army.getHorses(), army.getSpoils(), army.getReturnTime());
434 }
435 }
436 }