pyfpdb/GuiSessionViewer.py

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3
   4 #Copyright 2008-2011 Steffen Schaumburg
   5 #This program is free software: you can redistribute it and/or modify
   6 #it under the terms of the GNU Affero General Public License as published by
   7 #the Free Software Foundation, version 3 of the License.
   8 #
   9 #This program is distributed in the hope that it will be useful,
  10 #but WITHOUT ANY WARRANTY; without even the implied warranty of
  11 #MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  12 #GNU General Public License for more details.
  13 #
  14 #You should have received a copy of the GNU Affero General Public License
  15 #along with this program. If not, see <http://www.gnu.org/licenses/>.
  16 #In the "official" distribution you can find the license in agpl-3.0.txt.
  17
  18 import L10n
  19 _ = L10n.get_translation()
  20
  21 import sys
  22 import threading
  23 import pygtk
  24 pygtk.require('2.0')
  25 import gtk
  26 import os
  27 import traceback
  28 from time import time, strftime, localtime
  29 try:
  30     calluse = not 'matplotlib' in sys.modules
  31     import matplotlib
  32     if calluse:
  33         matplotlib.use('GTKCairo')
  34     from matplotlib.figure import Figure
  35     from matplotlib.backends.backend_gtk import FigureCanvasGTK as FigureCanvas
  36     from matplotlib.backends.backend_gtkagg import NavigationToolbar2GTKAgg as NavigationToolbar
  37     from matplotlib.finance import candlestick
  38
  39     from numpy import diff, nonzero, sum, cumsum, max, min, append
  40
  41 except ImportError, inst:
  42     print _("""Failed to load numpy and/or matplotlib in Session Viewer""")
  43     print "ImportError: %s" % inst.args
  44
  45 import Card
  46 import fpdb_import
  47 import Database
  48 import Filters
  49 import Charset
  50
  51 DEBUG = False
  52
  53 class GuiSessionViewer (threading.Thread):
  54     def __init__(self, config, querylist, mainwin, debug=True):
  55         self.debug = debug
  56         self.conf = config
  57         self.sql = querylist
  58         self.window = mainwin
  59
  60         self.liststore = None
  61
  62         self.MYSQL_INNODB   = 2
  63         self.PGSQL          = 3
  64         self.SQLITE         = 4
  65
  66         self.fig = None
  67         self.canvas = None
  68         self.ax = None
  69         self.graphBox = None
  70
  71         # create new db connection to avoid conflicts with other threads
  72         self.db = Database.Database(self.conf, sql=self.sql)
  73         self.cursor = self.db.cursor
  74
  75         settings = {}
  76         settings.update(self.conf.get_db_parameters())
  77         settings.update(self.conf.get_import_parameters())
  78         settings.update(self.conf.get_default_paths())
  79
  80         # text used on screen stored here so that it can be configured
  81         self.filterText = {'handhead':_('Hand Breakdown for all levels listed above')}
  82
  83         filters_display = { "Heroes"    : True,
  84                             "Sites"     : True,
  85                             "Games"     : True,
  86                             "Currencies": True,
  87                             "Limits"    : True,
  88                             "LimitSep"  : True,
  89                             "LimitType" : True,
  90                             "Type"      : False,
  91                             "Seats"     : True,
  92                             "SeatSep"   : False,
  93                             "Dates"     : True,
  94                             "Groups"    : False,
  95                             "GroupsAll" : False,
  96                             "Button1"   : True,
  97                             "Button2"   : False
  98                           }
  99
 100         self.filters = Filters.Filters(self.db, self.conf, self.sql, display = filters_display)
 101         self.filters.registerButton1Name("_Refresh")
 102         self.filters.registerButton1Callback(self.refreshStats)
 103
 104         # ToDo: store in config
 105         # ToDo: create popup to adjust column config
 106         # columns to display, keys match column name returned by sql, values in tuple are:
 107         #     is column displayed, column heading, xalignment, formatting
 108         self.columns = [ (1.0, "SID"   )
 109                        , (1.0, "Hands" )
 110                        , (0.5, "Start" )
 111                        , (0.5, "End"   )
 112                        , (1.0, "Rate"  )
 113                        , (1.0, "Open"  )
 114                        , (1.0, "Close" )
 115                        , (1.0, "Low"   )
 116                        , (1.0, "High"  )
 117                        , (1.0, "Range" )
 118                        , (1.0, "Profit")
 119                        ]
 120
 121         self.detailFilters = []   # the data used to enhance the sql select
 122
 123         self.main_hbox = gtk.HPaned()
 124
 125         self.stats_frame = gtk.Frame()
 126         self.stats_frame.show()
 127
 128         main_vbox = gtk.VPaned()
 129         main_vbox.show()
 130         self.graphBox = gtk.VBox(False, 0)
 131         self.graphBox.set_size_request(400,400)
 132         self.graphBox.show()
 133         self.stats_vbox = gtk.VBox(False, 0)
 134         self.stats_vbox.show()
 135         self.stats_frame.add(self.stats_vbox)
 136
 137         self.main_hbox.pack1(self.filters.get_vbox())
 138         self.main_hbox.pack2(main_vbox)
 139         main_vbox.pack1(self.graphBox)
 140         main_vbox.pack2(self.stats_frame)
 141         self.main_hbox.show()
 142
 143         # make sure Hand column is not displayed
 144         #[x for x in self.columns if x[0] == 'hand'][0][1] = False
 145         # if DEBUG == False:
 146         #     warning_string = _("Session Viewer is proof of concept code only, and contains many bugs.\n")
 147         #     warning_string += _("Feel free to use the viewer, but there is no guarantee that the data is accurate.\n")
 148         #     warning_string += _("If you are interested in developing the code further please contact us via the usual channels.\n")
 149         #     warning_string += _("Thank you")
 150         #     self.warning_box(warning_string)
 151
 152     def warning_box(self, str, diatitle=_("FPDB WARNING")):
 153         diaWarning = gtk.Dialog(title=diatitle, parent=self.window, flags=gtk.DIALOG_DESTROY_WITH_PARENT, buttons=(gtk.STOCK_OK,gtk.RESPONSE_OK))
 154
 155         label = gtk.Label(str)
 156         diaWarning.vbox.add(label)
 157         label.show()
 158
 159         response = diaWarning.run()
 160         diaWarning.destroy()
 161         return response
 162
 163     def get_vbox(self):
 164         """returns the vbox of this thread"""
 165         return self.main_hbox
 166
 167
 168
 169     def refreshStats(self, widget, data):
 170         try: self.stats_vbox.destroy()
 171         except AttributeError: pass
 172         self.stats_vbox = gtk.VBox(False, 0)
 173         self.stats_vbox.show()
 174         self.stats_frame.add(self.stats_vbox)
 175         self.fillStatsFrame(self.stats_vbox)
 176
 177     def fillStatsFrame(self, vbox):
 178         sites = self.filters.getSites()
 179         heroes = self.filters.getHeroes()
 180         siteids = self.filters.getSiteIds()
 181         games  = self.filters.getGames()
 182         currencies = self.filters.getCurrencies()
 183         limits  = self.filters.getLimits()
 184         seats  = self.filters.getSeats()
 185         sitenos = []
 186         playerids = []
 187
 188         for i in ('show', 'none'):
 189             if i in limits:
 190                 limits.remove(i)
 191
 192         # Which sites are selected?
 193         for site in sites:
 194             if sites[site] == True:
 195                 sitenos.append(siteids[site])
 196                 _hname = Charset.to_utf8(heroes[site])
 197                 result = self.db.get_player_id(self.conf, site, _hname)
 198                 if result is not None:
 199                     playerids.append(result)
 200
 201         if not sitenos:
 202             #Should probably pop up here.
 203             print _("No sites selected - defaulting to PokerStars")
 204             sitenos = [2]
 205         if not games:
 206             print _("No games found")
 207             return
 208         if not currencies:
 209             print _("No currencies found")
 210             return
 211         if not playerids:
 212             print _("No player ids found")
 213             return
 214         if not limits:
 215             print _("No limits found")
 216             return
 217
 218         self.createStatsPane(vbox, playerids, sitenos, games, currencies, limits, seats)
 219
 220     def createStatsPane(self, vbox, playerids, sitenos, games, currencies, limits, seats):
 221         starttime = time()
 222
 223         (results, quotes) = self.generateDatasets(playerids, sitenos, games, currencies, limits, seats)
 224
 225         if DEBUG:
 226             for x in quotes:
 227                 print "start %s\tend %s  \thigh %s\tlow %s" % (x[1], x[2], x[3], x[4])
 228
 229         self.generateGraph(quotes)
 230
 231         heading = gtk.Label(self.filterText['handhead'])
 232         heading.show()
 233         vbox.pack_start(heading, expand=False, padding=3)
 234
 235         # Scrolled window for detailed table (display by hand)
 236         swin = gtk.ScrolledWindow(hadjustment=None, vadjustment=None)
 237         swin.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
 238         swin.show()
 239         vbox.pack_start(swin, expand=True, padding=3)
 240
 241         vbox1 = gtk.VBox(False, 0)
 242         vbox1.show()
 243         swin.add_with_viewport(vbox1)
 244
 245         self.addTable(vbox1, results)
 246
 247         self.db.rollback()
 248         print _("Stats page displayed in %4.2f seconds") % (time() - starttime)
 249     #end def fillStatsFrame(self, vbox):
 250
 251     def generateDatasets(self, playerids, sitenos, games, currencies, limits, seats):
 252         if (DEBUG): print "DEBUG: Starting generateDatasets"
 253         THRESHOLD = 1800     # Min # of secs between consecutive hands before being considered a new session
 254         PADDING   = 5        # Additional time in minutes to add to a session, session startup, shutdown etc
 255
 256         # Get a list of timestamps and profits
 257
 258         q = self.sql.query['sessionStats']
 259         start_date, end_date = self.filters.getDates()
 260         q = q.replace("<datestest>", " BETWEEN '" + start_date + "' AND '" + end_date + "'")
 261
 262         l = []
 263         for m in self.filters.display.items():
 264             if m[0] == 'Games' and m[1]:
 265                 for n in games:
 266                     if games[n]:
 267                         l.append(n)
 268                 if len(l) > 0:
 269                     gametest = str(tuple(l))
 270                     gametest = gametest.replace("L", "")
 271                     gametest = gametest.replace(",)",")")
 272                     gametest = gametest.replace("u'","'")
 273                     gametest = "AND gt.category in %s" % gametest
 274                 else:
 275                     gametest = "AND gt.category IS NULL"
 276         q = q.replace("<game_test>", gametest)
 277
 278         l = []
 279         for n in currencies:
 280             if currencies[n]:
 281                 l.append(n)
 282         currencytest = str(tuple(l))
 283         currencytest = currencytest.replace(",)",")")
 284         currencytest = currencytest.replace("u'","'")
 285         currencytest = "AND gt.currency in %s" % currencytest
 286         q = q.replace("<currency_test>", currencytest)
 287
 288         lims = [int(x[0:-2]) for x in limits if len(x) > 2 and x[-2:] == 'fl']
 289         potlims = [int(x[0:-2]) for x in limits if len(x) > 2 and x[-2:] == 'pl']
 290         nolims = [int(x[0:-2]) for x in limits if len(x) > 2 and x[-2:] == 'nl']
 291         capnolims = [int(x[0:-2]) for x in limits if len(x) > 2 and x[-2:] == 'cn']
 292         limittest = "AND ( (gt.limitType = 'fl' AND gt.bigBlind in "
 293                  # and ( (limit and bb in()) or (nolimit and bb in ()) )
 294         if lims:
 295             blindtest = str(tuple(lims))
 296             blindtest = blindtest.replace("L", "")
 297             blindtest = blindtest.replace(",)",")")
 298             limittest = limittest + blindtest + ' ) '
 299         else:
 300             limittest = limittest + '(-1) ) '
 301         limittest = limittest + " OR (gt.limitType = 'pl' AND gt.bigBlind in "
 302         if potlims:
 303             blindtest = str(tuple(potlims))
 304             blindtest = blindtest.replace("L", "")
 305             blindtest = blindtest.replace(",)",")")
 306             limittest = limittest + blindtest + ' ) '
 307         else:
 308             limittest = limittest + '(-1) ) '
 309         limittest = limittest + " OR (gt.limitType = 'nl' AND gt.bigBlind in "
 310         if nolims:
 311             blindtest = str(tuple(nolims))
 312             blindtest = blindtest.replace("L", "")
 313             blindtest = blindtest.replace(",)",")")
 314             limittest = limittest + blindtest + ' ) '
 315         else:
 316             limittest = limittest + '(-1) ) '
 317         limittest = limittest + " OR (gt.limitType = 'cn' AND gt.bigBlind in "
 318         if capnolims:
 319             blindtest = str(tuple(capnolims))
 320             blindtest = blindtest.replace("L", "")
 321             blindtest = blindtest.replace(",)",")")
 322             limittest = limittest + blindtest + ' ) )'
 323         else:
 324             limittest = limittest + '(-1) ) )'
 325         q = q.replace("<limit_test>", limittest)
 326
 327         if seats:
 328             q = q.replace('<seats_test>',
 329                           'AND h.seats BETWEEN ' + str(seats['from']) +
 330                           ' AND ' + str(seats['to']))
 331         else:
 332             q = q.replace('<seats_test>', 'AND h.seats BETWEEN 0 AND 100')
 333
 334         nametest = str(tuple(playerids))
 335         nametest = nametest.replace("L", "")
 336         nametest = nametest.replace(",)",")")
 337         q = q.replace("<player_test>", nametest)
 338         q = q.replace("<ampersand_s>", "%s")
 339
 340         if DEBUG:
 341             hands = [
 342                 ( u'10000',  10), ( u'10000',  20), ( u'10000',  30),
 343                 ( u'20000', -10), ( u'20000', -20), ( u'20000', -30),
 344                 ( u'30000',  40),
 345                 ( u'40000',   0),
 346                 ( u'50000', -40),
 347                 ( u'60000',  10), ( u'60000',  30), ( u'60000', -20),
 348                 ( u'70000', -20), ( u'70000',  10), ( u'70000',  30),
 349                 ( u'80000', -10), ( u'80000', -30), ( u'80000',  20),
 350                 ( u'90000',  20), ( u'90000', -10), ( u'90000', -30),
 351                 (u'100000',  30), (u'100000', -50), (u'100000',  30),
 352                 (u'110000', -20), (u'110000',  50), (u'110000', -20),
 353                 (u'120000', -30), (u'120000',  50), (u'120000', -30),
 354                 (u'130000',  20), (u'130000', -50), (u'130000',  20),
 355                 (u'140000',  40), (u'140000', -40),
 356                 (u'150000', -40), (u'150000',  40),
 357                 (u'160000', -40), (u'160000',  80), (u'160000', -40),
 358                 ]
 359         else:
 360             self.db.cursor.execute(q)
 361             hands = self.db.cursor.fetchall()
 362
 363         #fixme - nasty hack to ensure that the hands.insert() works
 364         # for mysql data.  mysql returns tuples which can't be inserted
 365         # into so convert explicity to list.
 366         hands = list(hands)
 367
 368         if (not hands):
 369             return ([], [])
 370
 371         hands.insert(0, (hands[0][0], 0))
 372
 373         # Take that list and create an array of the time between hands
 374         times = map(lambda x:long(x[0]), hands)
 375         profits = map(lambda x:float(x[1]), hands)
 376         #print "DEBUG: times   : %s" % times
 377         #print "DEBUG: profits: %s" % profits
 378         #print "DEBUG: len(times) %s" %(len(times))
 379         diffs = diff(times)                      # This array is the difference in starttime between consecutive hands
 380         diffs2 = append(diffs,THRESHOLD + 1)     # Append an additional session to the end of the diffs, so the next line
 381                                                  # includes an index into the last 'session'
 382         index = nonzero(diffs2 > THRESHOLD)      # This array represents the indexes into 'times' for start/end times of sessions
 383                                                  # times[index[0][0]] is the end of the first session,
 384         #print "DEBUG: len(index[0]) %s" %(len(index[0]))
 385         if len(index[0]) > 0:
 386             #print "DEBUG: index[0][0] %s" %(index[0][0])
 387             #print "DEBUG: index %s" %(index)
 388             pass
 389         else:
 390             index = [[0]]
 391             #print "DEBUG: index %s" %(index)
 392             #print "DEBUG: index[0][0] %s" %(index[0][0])
 393             pass
 394
 395         first_idx = 1
 396         quotes = []
 397         results = []
 398         cum_sum = cumsum(profits) / 100
 399         sid = 1
 400
 401         total_hands = 0
 402         total_time = 0
 403         global_open = None
 404         global_lwm = None
 405         global_hwm = None
 406
 407         # Take all results and format them into a list for feeding into gui model.
 408         #print "DEBUG: range(len(index[0]): %s" % range(len(index[0]))
 409         for i in range(len(index[0])):
 410             last_idx = index[0][i]
 411             hds = last_idx - first_idx + 1                                           # Number of hands in session
 412             if hds > 0:
 413                 stime = strftime("%d/%m/%Y %H:%M", localtime(times[first_idx]))      # Formatted start time
 414                 etime = strftime("%d/%m/%Y %H:%M", localtime(times[last_idx]))       # Formatted end time
 415                 minutesplayed = (times[last_idx] - times[first_idx])/60
 416                 minutesplayed = minutesplayed + PADDING
 417                 if minutesplayed == 0:
 418                     minutesplayed = 1
 419                 hph = hds*60/minutesplayed # Hands per hour
 420                 end_idx = last_idx+1
 421                 won = sum(profits[first_idx:end_idx])/100.0
 422                 #print "DEBUG: profits[%s:%s]: %s" % (first_idx, end_idx, profits[first_idx:end_idx])
 423                 hwm = max(cum_sum[first_idx-1:end_idx]) # include the opening balance,
 424                 lwm = min(cum_sum[first_idx-1:end_idx]) # before we win/lose first hand
 425                 open = (sum(profits[:first_idx]))/100
 426                 close = (sum(profits[:end_idx]))/100
 427                 #print "DEBUG: range: (%s, %s) - (min, max): (%s, %s) - (open,close): (%s, %s)" %(first_idx, end_idx, lwm, hwm, open, close)
 428
 429                 total_hands = total_hands + hds
 430                 total_time = total_time + minutesplayed
 431                 if (global_lwm == None or global_lwm > lwm):
 432                     global_lwm = lwm
 433                 if (global_hwm == None or global_hwm < hwm):
 434                     global_hwm = hwm
 435                 if (global_open == None):
 436                     global_open = open
 437                     global_stime = stime
 438
 439                 results.append([sid, hds, stime, etime, hph,
 440                                 "%.2f" % open,
 441                                 "%.2f" % close,
 442                                 "%.2f" % lwm,
 443                                 "%.2f" % hwm,
 444                                 "%.2f" % (hwm - lwm),
 445                                 "%.2f" % won])
 446                 quotes.append((sid, open, close, hwm, lwm))
 447                 #print "DEBUG: Hands in session %4s: %4s  Start: %s End: %s HPH: %s Profit: %s" %(sid, hds, stime, etime, hph, won)
 448                 first_idx = end_idx
 449                 sid = sid+1
 450             else:
 451                 print "hds <= 0"
 452         global_close = close
 453         global_etime = etime
 454         results.append([''] * 11)
 455         results.append([_("all"), total_hands, global_stime, global_etime,
 456                         total_hands * 60 / total_time,
 457                         "%.2f" % global_open,
 458                         "%.2f" % global_close,
 459                         "%.2f" % global_lwm,
 460                         "%.2f" % global_hwm,
 461                         "%.2f" % (global_hwm - global_lwm),
 462                         "%.2f" % (global_close - global_open)])
 463
 464         return (results, quotes)
 465
 466     def clearGraphData(self):
 467
 468         try:
 469             try:
 470                 if self.canvas:
 471                     self.graphBox.remove(self.canvas)
 472             except:
 473                 pass
 474
 475             if self.fig is not None:
 476                 self.fig.clear()
 477             self.fig = Figure(figsize=(5,4), dpi=100)
 478             if self.canvas is not None:
 479                 self.canvas.destroy()
 480
 481             self.canvas = FigureCanvas(self.fig)  # a gtk.DrawingArea
 482         except:
 483             err = traceback.extract_tb(sys.exc_info()[2])[-1]
 484             print _("Error:")+" "+err[2]+"("+str(err[1])+"): "+str(sys.exc_info()[1])
 485             raise
 486
 487
 488     def generateGraph(self, quotes):
 489         self.clearGraphData()
 490
 491         #print "DEBUG:"
 492         #print "\tquotes = %s" % quotes
 493
 494         #for i in range(len(highs)):
 495         #    print "DEBUG: (%s, %s, %s, %s)" %(lows[i], opens[i], closes[i], highs[i])
 496         #    print "DEBUG: diffs h/l: %s o/c: %s" %(lows[i] - highs[i], opens[i] - closes[i])
 497
 498         self.ax = self.fig.add_subplot(111)
 499
 500         self.ax.set_title(_("Session candlestick graph"))
 501
 502         #Set axis labels and grid overlay properites
 503         self.ax.set_xlabel(_("Sessions"), fontsize = 12)
 504         self.ax.set_ylabel("$", fontsize = 12)
 505         self.ax.grid(color='g', linestyle=':', linewidth=0.2)
 506
 507         candlestick(self.ax, quotes, width=0.50, colordown='r', colorup='g', alpha=1.00)
 508         self.graphBox.add(self.canvas)
 509         self.canvas.show()
 510         self.canvas.draw()
 511
 512     def addTable(self, vbox, results):
 513         row = 0
 514         sqlrow = 0
 515         colxalign,colheading = range(2)
 516
 517         self.liststore = gtk.ListStore(*([str] * len(self.columns)))
 518         for row in results:
 519             iter = self.liststore.append(row)
 520
 521         view = gtk.TreeView(model=self.liststore)
 522         view.set_grid_lines(gtk.TREE_VIEW_GRID_LINES_BOTH)
 523         vbox.add(view)
 524         cell05 = gtk.CellRendererText()
 525         cell05.set_property('xalign', 0.5)
 526         cell10 = gtk.CellRendererText()
 527         cell10.set_property('xalign', 1.0)
 528         listcols = []
 529
 530         # Create header row   eg column: ("game",     True, "Game",     0.0, "%s")
 531         for col, column in enumerate(self.columns):
 532             treeviewcolumn = gtk.TreeViewColumn(column[colheading])
 533             listcols.append(treeviewcolumn)
 534             treeviewcolumn.set_alignment(column[colxalign])
 535             view.append_column(listcols[col])
 536             if (column[colxalign] == 0.5):
 537                 cell = cell05
 538             else:
 539                 cell = cell10
 540             listcols[col].pack_start(cell, expand=True)
 541             listcols[col].add_attribute(cell, 'text', col)
 542             listcols[col].set_expand(True)
 543
 544         vbox.show_all()
 545
 546 def main(argv=None):
 547     config = Configuration.Config()
 548     i = GuiBulkImport(settings, config)
 549
 550 if __name__ == '__main__':
 551     sys.exit(main())
 552