TransfersDAO xref

View Javadoc

1   // %3298555606:hoplugins.transfers.dao%
2   package hoplugins.transfers.dao;
3   
4   import hoplugins.Commons;
5   
6   import hoplugins.commons.utils.DateUtil;
7   import hoplugins.commons.utils.HTCalendar;
8   import hoplugins.commons.utils.HTCalendarFactory;
9   
10  import hoplugins.transfers.utils.PlayerRetriever;
11  import hoplugins.transfers.vo.PlayerTransfer;
12  
13  import plugins.ISpieler;
14  
15  import java.sql.ResultSet;
16  import java.sql.SQLException;
17  import java.sql.Timestamp;
18  
19  import java.util.Calendar;
20  import java.util.HashMap;
21  import java.util.Iterator;
22  import java.util.List;
23  import java.util.Map;
24  import java.util.Vector;
25  
26  
27  /***
28   * DAO to store and retrieve transfers in the HO database.
29   *
30   * @author <a href=mailto:nethyperon@users.sourceforge.net>Boy van der Werf</a>
31   */
32  public final class TransfersDAO {
33      //~ Static fields/initializers -----------------------------------------------------------------
34  
35      /*** Name of the table in the HO database */
36      private static final String TABLE_NAME = "transfers_transfers"; //$NON-NLS-1$
37  
38      static {
39          checkTable();
40      }
41  
42      //~ Constructors -------------------------------------------------------------------------------
43  
44      /***
45       * Private default constuctor to prevent class instantiation.
46       */
47      private TransfersDAO() {
48      }
49  
50      //~ Methods ------------------------------------------------------------------------------------
51  
52      /***
53       * Gets a list of transfers.
54       *
55       * @param playerid Player id for selecting transfers.
56       * @param allTransfers If <code>false</code> this method will only return transfers for your
57       *        own team, otherwise it will return all transfers for the player.
58       *
59       * @return List of transfers.
60       */
61      public static List getTransfers(int playerid, boolean allTransfers) {
62          final StringBuffer sqlStmt = new StringBuffer("SELECT * FROM " + TABLE_NAME); //$NON-NLS-1$
63          sqlStmt.append(" WHERE playerid = " + playerid); //$NON-NLS-1$
64  
65          if (!allTransfers) {
66              final int teamid = Commons.getModel().getBasics().getTeamId();
67              sqlStmt.append(" AND (buyerid = " + teamid); //$NON-NLS-1$
68              sqlStmt.append(" OR sellerid = " + teamid + ")"); //$NON-NLS-1$ //$NON-NLS-2$
69          }
70  
71          sqlStmt.append(" ORDER BY date DESC"); //$NON-NLS-1$
72  
73          return loadTransfers(sqlStmt.toString());
74      }
75  
76      /***
77       * Gets a list of transfers for your own team.
78       *
79       * @param season Season number for selecting transfers.
80       * @param bought <code>true</code> to include BUY transfers.
81       * @param sold <code>true</code> to include SELL transfers.
82       *
83       * @return List of transfers.
84       */
85      public static List getTransfers(int season, boolean bought, boolean sold) {
86          final int teamid = Commons.getModel().getBasics().getTeamId();
87          return getTransfers(teamid, season, bought, sold);
88      }
89  
90      /***
91       * Gets a list of transfers.
92       *
93       * @param teamid Team id to select transfers for.
94       * @param season Season number for selecting transfers.
95       * @param bought <code>true</code> to include BUY transfers.
96       * @param sold <code>true</code> to include SELL transfers.
97       *
98       * @return List of transfers.
99       */
100     public static List getTransfers(int teamid, int season, boolean bought, boolean sold) {
101         final StringBuffer sqlStmt = new StringBuffer("SELECT * FROM " + TABLE_NAME); //$NON-NLS-1$
102         sqlStmt.append(" WHERE 1=1"); //$NON-NLS-1$
103 
104         if (season != 0) {
105             sqlStmt.append(" AND season = " + season); //$NON-NLS-1$
106         }
107 
108         if (bought || sold) {
109             sqlStmt.append(" AND ("); //$NON-NLS-1$
110 
111             if (bought) {
112                 sqlStmt.append(" buyerid = " + teamid); //$NON-NLS-1$
113             }
114 
115             if (bought && sold) {
116                 sqlStmt.append(" OR"); //$NON-NLS-1$
117             }
118 
119             if (sold) {
120                 sqlStmt.append(" sellerid = " + teamid); //$NON-NLS-1$
121             }
122 
123             sqlStmt.append(")"); //$NON-NLS-1$
124         }
125 
126         sqlStmt.append(" ORDER BY date DESC"); //$NON-NLS-1$
127 
128         return loadTransfers(sqlStmt.toString());
129     }
130 
131     /***
132      * Reload transfer data for a team from the HT xml.
133      *
134      * @param teamid Team id to reload data for
135      *
136      * @throws Exception If an error occurs.
137      */
138     public static void reloadTeamTransfers(int teamid) throws Exception {
139         Commons.getModel().getAdapter().executeUpdate("DELETE FROM " + TABLE_NAME
140                                                       + " WHERE buyerid = " + teamid
141                                                       + " OR sellerid = " + teamid);
142         updateTeamTransfers(teamid);
143     }
144 
145     /***
146      * Update transfer data for a player.
147      *
148      * @param playerId Player
149      */
150     public static void updatePlayerTransfers(int playerId) {
151         try {
152             final List transfers = XMLParser.getAllPlayerTransfers(playerId);
153 
154             if (transfers.size() > 0) {
155                 for (Iterator iter = transfers.iterator(); iter.hasNext();) {
156                     final PlayerTransfer transfer = (PlayerTransfer) iter.next();
157                     addTransfer(transfer);
158                 }
159             } else {
160                 // Fired player, update team related transfers and remove the rest of the player's history
161                 final int teamid = Commons.getModel().getBasics().getTeamId();
162 
163                 final StringBuffer sqlStmt = new StringBuffer("UPDATE " + TABLE_NAME); //$NON-NLS-1$
164                 sqlStmt.append(" SET"); //$NON-NLS-1$
165                 sqlStmt.append(" playerid = 0, playername = ''"); //$NON-NLS-1$
166                 sqlStmt.append(" WHERE playerid = " + playerId); //$NON-NLS-1$
167                 sqlStmt.append(" AND (buyerid = " + teamid + " OR sellerid = " + teamid + ")"); //$NON-NLS-1$
168                 Commons.getModel().getAdapter().executeUpdate(sqlStmt.toString());
169                 Commons.getModel().getAdapter().executeUpdate("DELETE FROM " + TABLE_NAME
170                                                               + " WHERE playerid = " + playerId);
171             }
172         } catch (Exception e) {
173             e.printStackTrace();
174         }
175     }
176 
177     /***
178      * Update transfer data for a team from the HT xml.
179      *
180      * @param teamid Team id to update data for
181      */
182     public static void updateTeamTransfers(int teamid) {
183         try {
184             final List players = new Vector();
185 
186             final Calendar cal = Calendar.getInstance();
187             cal.add(Calendar.DAY_OF_MONTH, 1);
188 
189             final List transfers = XMLParser.getAllTeamTransfers(teamid,
190                                                                  DateUtil.resetDay(cal.getTime()));
191 
192             for (Iterator iter = transfers.iterator(); iter.hasNext();) {
193                 PlayerTransfer transfer = (PlayerTransfer) iter.next();
194                 addTransfer(transfer);
195 
196                 final ISpieler player = PlayerRetriever.getPlayer(transfer.getPlayerId());
197 
198                 if (player != null) {
199                     players.add(player);
200                 }
201             }
202         } catch (Exception e) {
203             e.printStackTrace();
204         }
205     }
206 
207     /***
208      * Adds a tranfer to the HO database
209      *
210      * @param transfer Transfer information
211      *
212      * @return Boolean to indicate if the transfer is sucessfully added.
213      */
214     private static boolean addTransfer(PlayerTransfer transfer) {
215         final StringBuffer sqlStmt = new StringBuffer("INSERT INTO " + TABLE_NAME); //$NON-NLS-1$
216         sqlStmt.append("(transferid, date, week, season, playerid, playername, buyerid, buyername, sellerid, sellername, price, marketvalue, tsi)"); //$NON-NLS-1$
217         sqlStmt.append(" VALUES ("); //$NON-NLS-1$
218         sqlStmt.append(transfer.getTransferID() + ","); //$NON-NLS-1$
219         sqlStmt.append("'" + transfer.getDate().toString() + "',"); //$NON-NLS-1$ //$NON-NLS-2$
220         sqlStmt.append(transfer.getWeek() + ","); //$NON-NLS-1$
221         sqlStmt.append(transfer.getSeason() + ","); //$NON-NLS-1$
222         sqlStmt.append(transfer.getPlayerId() + ","); //$NON-NLS-1$
223         sqlStmt.append("'"
224                        + Commons.getModel().getHelper().encodeString4Database(transfer
225                                                                               .getPlayerName())
226                        + "',"); //$NON-NLS-1$ //$NON-NLS-2$
227         sqlStmt.append(transfer.getBuyerid() + ","); //$NON-NLS-1$
228         sqlStmt.append("'"
229                        + Commons.getModel().getHelper().encodeString4Database(transfer.getBuyerName())
230                        + "',"); //$NON-NLS-1$ //$NON-NLS-2$
231         sqlStmt.append(transfer.getSellerid() + ","); //$NON-NLS-1$
232         sqlStmt.append("'"
233                        + Commons.getModel().getHelper().encodeString4Database(transfer
234                                                                               .getSellerName())
235                        + "',"); //$NON-NLS-1$ //$NON-NLS-2$
236         sqlStmt.append(transfer.getPrice() + ","); //$NON-NLS-1$
237         sqlStmt.append(transfer.getMarketvalue() + ","); //$NON-NLS-1$
238         sqlStmt.append(transfer.getTsi());
239         sqlStmt.append(" )"); //$NON-NLS-1$
240 
241         try {
242             Commons.getModel().getAdapter().executeUpdate(sqlStmt.toString());
243             return true;
244         } catch (Exception inore) {
245             return false;
246         }
247     }
248 
249     /***
250      * Method that check if the table exists, if not creates it and sets the values to default
251      */
252     private static void checkTable() {
253         ResultSet rs = null;
254 
255         rs = Commons.getModel().getAdapter().executeQuery("SELECT * FROM " + TABLE_NAME); //$NON-NLS-1$
256 
257         if (rs == null) {
258             final StringBuffer sqlStmt = new StringBuffer("CREATE TABLE " + TABLE_NAME); //$NON-NLS-1$
259             sqlStmt.append("("); //$NON-NLS-1$
260             sqlStmt.append("transferid INTEGER NOT NULL,"); //$NON-NLS-1$
261             sqlStmt.append("date TIMESTAMP,"); //$NON-NLS-1$
262             sqlStmt.append("week INTEGER,"); //$NON-NLS-1$
263             sqlStmt.append("season INTEGER,"); //$NON-NLS-1$
264             sqlStmt.append("playerid INTEGER NOT NULL,"); //$NON-NLS-1$
265             sqlStmt.append("playername VARCHAR(127),"); //$NON-NLS-1$
266             sqlStmt.append("buyerid INTEGER,"); //$NON-NLS-1$
267             sqlStmt.append("buyername VARCHAR(256),"); //$NON-NLS-1$
268             sqlStmt.append("sellerid INTEGER,"); //$NON-NLS-1$
269             sqlStmt.append("sellername VARCHAR(256),"); //$NON-NLS-1$
270             sqlStmt.append("price INTEGER,"); //$NON-NLS-1$
271             sqlStmt.append("marketvalue INTEGER,"); //$NON-NLS-1$
272             sqlStmt.append("tsi INTEGER,"); //$NON-NLS-1$
273             sqlStmt.append("PRIMARY KEY (transferid)"); //$NON-NLS-1$
274             sqlStmt.append(")"); //$NON-NLS-1$
275 
276             Commons.getModel().getAdapter().executeUpdate(sqlStmt.toString());
277             Commons.getModel().getAdapter().executeUpdate("CREATE INDEX pl_id ON " + TABLE_NAME
278                                                           + " (playerid)"); //$NON-NLS-1$ //$NON-NLS-2$
279             Commons.getModel().getAdapter().executeUpdate("CREATE INDEX buy_id ON " + TABLE_NAME
280                                                           + " (buyerid)"); //$NON-NLS-1$ //$NON-NLS-2$
281             Commons.getModel().getAdapter().executeUpdate("CREATE INDEX sell_id ON " + TABLE_NAME
282                                                           + " (sellerid)"); //$NON-NLS-1$ //$NON-NLS-2$
283 
284             TransferSettingDAO.setCalendarFix();
285         } else {
286             // This part is to solve incorrect calculated values from the 1.01 version of the plugin
287             final boolean calendarfix = TransferSettingDAO.isCalendarFix();
288 
289             if (!calendarfix) {
290                 final Map map = new HashMap();
291 
292                 try {
293                     while (rs.next()) {
294                         map.put(new Integer(rs.getInt("transferid")), rs.getTimestamp("date"));
295                     }
296 
297                     final HTCalendar cal = HTCalendarFactory.createEconomyCalendar(Commons.getModel());
298 
299                     for (Iterator iter = map.keySet().iterator(); iter.hasNext();) {
300                         final Integer transferId = (Integer) iter.next();
301                         final Timestamp date = (Timestamp) map.get(transferId);
302                         cal.setTime(date);
303 
304                         final StringBuffer sqlStmt = new StringBuffer("UPDATE " + TABLE_NAME
305                                                                       + " SET"); //$NON-NLS-1$
306                         sqlStmt.append(" week=" + cal.getHTWeek()); //$NON-NLS-1$
307                         sqlStmt.append(",season=" + cal.getHTSeason()); //$NON-NLS-1$
308                         sqlStmt.append(" WHERE transferid=" + transferId.intValue()); //$NON-NLS-1$
309                         Commons.getModel().getAdapter().executeUpdate(sqlStmt.toString());
310                     }
311 
312                     TransferSettingDAO.setCalendarFix();
313                 } catch (SQLException e) {
314                     e.printStackTrace();
315                 }
316             }
317         }
318     }
319 
320     /***
321      * Loads a list of transfers from the HO database.
322      *
323      * @param sqlStmt SQL statement.
324      *
325      * @return List of transfers
326      */
327     private static List loadTransfers(String sqlStmt) {
328         final double curr_rate = Commons.getModel().getXtraDaten().getCurrencyRate();
329 
330         final List results = new Vector();
331         final ResultSet rs = Commons.getModel().getAdapter().executeQuery(sqlStmt.toString());
332 
333         if (rs == null) {
334             return new Vector();
335         }
336 
337         try {
338             while (rs.next()) {
339                 PlayerTransfer transfer = new PlayerTransfer(rs.getInt("transferid"),
340                                                              rs.getInt("playerid")); //$NON-NLS-1$ //$NON-NLS-2$
341                 transfer.setPlayerName(Commons.getModel().getHelper().decodeStringFromDatabase(rs
342                                                                                                .getString("playername"))); //$NON-NLS-1$
343                 transfer.setDate(rs.getTimestamp("date")); //$NON-NLS-1$
344                 transfer.setWeek(rs.getInt("week")); //$NON-NLS-1$
345                 transfer.setSeason(rs.getInt("season")); //$NON-NLS-1$
346 
347                 transfer.setBuyerid(rs.getInt("buyerid")); //$NON-NLS-1$
348                 transfer.setBuyerName(Commons.getModel().getHelper().decodeStringFromDatabase(rs
349                                                                                               .getString("buyername"))); //$NON-NLS-1$
350                 transfer.setSellerid(rs.getInt("sellerid")); //$NON-NLS-1$
351                 transfer.setSellerName(Commons.getModel().getHelper().decodeStringFromDatabase(rs
352                                                                                                .getString("sellername"))); //$NON-NLS-1$
353 
354                 transfer.setPrice((int) (rs.getInt("price") / curr_rate)); //$NON-NLS-1$
355                 transfer.setMarketvalue((int) (rs.getInt("marketvalue") / curr_rate)); //$NON-NLS-1$
356                 transfer.setTsi(rs.getInt("tsi")); //$NON-NLS-1$
357 
358                 results.add(transfer);
359             }
360         } catch (SQLException e) {
361             e.printStackTrace();
362         }
363 
364         for (Iterator iter = results.iterator(); iter.hasNext();) {
365             PlayerTransfer transfer = (PlayerTransfer) iter.next();
366             final ISpieler spieler = Commons.getModel().getSpielerAtDate(transfer.getPlayerId(),
367                                                                          transfer.getDate());
368 
369             if (spieler != null) {
370                 final HTCalendar transferDate = HTCalendarFactory.createTrainingCalendar(Commons
371                                                                                          .getModel(),
372                                                                                          transfer
373                                                                                          .getDate());
374                 final HTCalendar spielerDate = HTCalendarFactory.createTrainingCalendar(Commons
375                                                                                         .getModel(),
376                                                                                         spieler
377                                                                                         .getHrfDate());
378 
379                 // Not in the same week, possible skillup so skip it
380                 if (((transferDate.getHTSeason() * 16) + transferDate.getHTWeek()) == ((spielerDate
381                                                                                         .getHTSeason() * 16)
382                     + spielerDate.getHTWeek())) {
383                     transfer.setPlayerInfo(spieler);
384                 }
385             }
386         }
387 
388         return results;
389     }
390 }