pyfpdb/GuiTourneyPlayerStats.py

   1 #!/usr/bin/env python
   2 # -*- coding: utf-8 -*-
   3
   4 #Copyright 2010-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 threading
  22 import pygtk
  23 pygtk.require('2.0')
  24 import gtk
  25 from time import time, strftime
  26
  27 import Charset
  28 import TourneyFilters
  29 import GuiPlayerStats
  30
  31 colalias,colshow,colheading,colxalign,colformat,coltype = 0,1,2,3,4,5
  32
  33 class GuiTourneyPlayerStats (GuiPlayerStats.GuiPlayerStats):
  34     def __init__(self, config, db, sql, mainwin, debug=True):
  35         self.conf = config
  36         self.db = db
  37         self.cursor = self.db.cursor
  38         self.sql = sql
  39         self.main_window = mainwin
  40         self.debug = debug
  41
  42         self.liststore = []   # gtk.ListStore[]         stores the contents of the grids
  43         self.listcols = []    # gtk.TreeViewColumn[][]  stores the columns in the grids
  44
  45         filters_display = { "Heroes"    : True,
  46                             "Sites"     : True,
  47                             #"Games"     : True,
  48                             #"Limits"    : True,
  49                             #"LimitSep"  : True,
  50                             #"LimitType" : True,
  51                             #"Type"      : True,
  52                             "Seats"     : True,
  53                             #"SeatSep"   : True,
  54                             "Dates"     : True,
  55                             #"Groups"    : True,
  56                             #"GroupsAll" : True,
  57                             #"Button1"   : True,
  58                             "Button2"   : True}
  59
  60         self.stats_frame = None
  61         self.stats_vbox = None
  62         self.detailFilters = []   # the data used to enhance the sql select
  63
  64         self.main_hbox = gtk.HPaned()
  65
  66         self.filters = TourneyFilters.TourneyFilters(self.db, self.conf, self.sql, display = filters_display)
  67         #self.filters.registerButton1Name(_("_Filters"))
  68         #self.filters.registerButton1Callback(self.showDetailFilter)
  69         self.filters.registerButton2Name(_("_Refresh Stats"))
  70         self.filters.registerButton2Callback(self.refreshStats)
  71
  72         # ToDo: store in config
  73         # ToDo: create popup to adjust column config
  74         # columns to display, keys match column name returned by sql, values in tuple are:
  75         #     is column displayed, column heading, xalignment, formatting, celltype
  76         self.columns = [ ["siteName",       True,  _("Site"),    0.0, "%s", "str"]
  77                        #,["tourney",        False, _("Tourney"), 0.0, "%s", "str"]   # true not allowed for this line
  78                        , ["category",       True,  _("Cat."),    0.0, "%s", "str"]
  79                        , ["limitType",      True,  _("Limit"),   0.0, "%s", "str"]
  80                        , ["currency",       True,  _("Curr."),   0.0, "%s", "str"]
  81                        , ["buyIn",          True,  _("BuyIn"),   1.0, "%3.2f", "str"]
  82                        , ["fee",            True,  _("Fee"),     1.0, "%3.2f", "str"]
  83                        , ["playerName",     False, _("Name"),    0.0, "%s", "str"]   # true not allowed for this line (set in code)
  84                        , ["tourneyCount",   True,  _("#"),       1.0, "%1.0f", "str"]
  85                        , ["itm",            True,  _("ITM%"),    1.0, "%3.2f", "str"]
  86                        , ["_1st",           False, _("1st"),     1.0, "%1.0f", "str"]
  87                        , ["_2nd",           True,  _("2nd"),     1.0, "%1.0f", "str"]
  88                        , ["_3rd",           True,  _("3rd"),     1.0, "%1.0f", "str"]
  89                        , ["unknownRank",    True,  _("Rank?"),   1.0, "%1.0f", "str"]
  90                        , ["spent",          True,  _("Spent"),   1.0, "%3.2f", "str"]
  91                        , ["won",            True,  _("Won"),     1.0, "%3.2f", "str"]
  92                        , ["roi",            True,  _("ROI%"),    1.0, "%3.0f", "str"]
  93                        , ["profitPerTourney", True,_("$/Tour"),  1.0, "%3.2f", "str"]]
  94
  95         self.stats_frame = gtk.Frame()
  96         self.stats_frame.show()
  97
  98         self.stats_vbox = gtk.VPaned()
  99         self.stats_vbox.show()
 100         self.stats_frame.add(self.stats_vbox)
 101         # self.fillStatsFrame(self.stats_vbox)
 102
 103         #self.main_hbox.pack_start(self.filters.get_vbox())
 104         #self.main_hbox.pack_start(self.stats_frame, expand=True, fill=True)
 105         self.main_hbox.pack1(self.filters.get_vbox())
 106         self.main_hbox.pack2(self.stats_frame)
 107         self.main_hbox.show()
 108     #end def __init__
 109
 110     def addGrid(self, vbox, query, numTourneys, tourneyTypes, playerids, sitenos, seats):
 111         #print "start of addGrid query", query
 112         #print "start of addGrid. numTourneys:",numTourneys,"tourneyTypes:", tourneyTypes, "playerids:",playerids
 113         counter = 0
 114         row = 0
 115         sqlrow = 0
 116         grid=numTourneys #TODO: should this be numTourneyTypes?
 117
 118         query = self.sql.query[query]
 119         query = self.refineQuery(query, numTourneys, tourneyTypes, playerids, sitenos, seats)
 120         print "DEBUG:\n%s" % query
 121         self.cursor.execute(query)
 122         result = self.cursor.fetchall()
 123         #print "result of the big query in addGrid:",result
 124         colnames = [desc[0] for desc in self.cursor.description]
 125
 126         # pre-fetch some constant values:
 127         #self.cols_to_show = [x for x in self.columns if x[colshow]]
 128         #htourneytypeid_idx = colnames.index('tourneyTypeId')
 129         self.cols_to_show = self.columns #TODO do i need above 2 lines?
 130
 131         assert len(self.liststore) == grid, "len(self.liststore)="+str(len(self.liststore))+" grid-1="+str(grid)
 132         self.liststore.append( gtk.ListStore(*([str] * len(self.cols_to_show))) )
 133         view = gtk.TreeView(model=self.liststore[grid])
 134         view.set_grid_lines(gtk.TREE_VIEW_GRID_LINES_BOTH)
 135         #vbox.pack_start(view, expand=False, padding=3)
 136         vbox.add(view)
 137         textcell = gtk.CellRendererText()
 138         textcell50 = gtk.CellRendererText()
 139         textcell50.set_property('xalign', 0.5)
 140         numcell = gtk.CellRendererText()
 141         numcell.set_property('xalign', 1.0)
 142         assert len(self.listcols) == grid
 143         self.listcols.append( [] )
 144
 145         # Create header row   eg column: ("game",     True, "Game",     0.0, "%s")
 146         for col, column in enumerate(self.cols_to_show):
 147             if column[colalias] == 'game' and holecards:
 148                 s = [x for x in self.columns if x[colalias] == 'hand'][0][colheading]
 149             else:
 150                 s = column[colheading]
 151             self.listcols[grid].append(gtk.TreeViewColumn(s))
 152             view.append_column(self.listcols[grid][col])
 153             if column[colformat] == '%s':
 154                 if column[colxalign] == 0.0:
 155                     self.listcols[grid][col].pack_start(textcell, expand=True)
 156                     self.listcols[grid][col].add_attribute(textcell, 'text', col)
 157                     cellrend = textcell
 158                 else:
 159                     self.listcols[grid][col].pack_start(textcell50, expand=True)
 160                     self.listcols[grid][col].add_attribute(textcell50, 'text', col)
 161                     cellrend = textcell50
 162                 self.listcols[grid][col].set_expand(True)
 163             else:
 164                 self.listcols[grid][col].pack_start(numcell, expand=True)
 165                 self.listcols[grid][col].add_attribute(numcell, 'text', col)
 166                 self.listcols[grid][col].set_expand(True)
 167                 cellrend = numcell
 168                 #self.listcols[grid][col].set_alignment(column[colxalign]) # no effect?
 169             self.listcols[grid][col].set_clickable(True)
 170             self.listcols[grid][col].connect("clicked", self.sortCols, (col,grid))
 171             if col == 0:
 172                 self.listcols[grid][col].set_sort_order(gtk.SORT_DESCENDING)
 173                 self.listcols[grid][col].set_sort_indicator(True)
 174             if column[coltype] == 'cash':
 175                 self.listcols[grid][col].set_cell_data_func(numcell, self.ledger_style_render_func)
 176             else:
 177                 self.listcols[grid][col].set_cell_data_func(cellrend, self.reset_style_render_func)
 178
 179         rows = len(result) # +1 for title row
 180
 181         while sqlrow < rows:
 182             treerow = []
 183             for col,column in enumerate(self.cols_to_show):
 184                 if column[colalias] in colnames:
 185                     value = result[sqlrow][colnames.index(column[colalias])]
 186                 else:
 187                     value = 111
 188                 if value != None and value != -999:
 189                     treerow.append(column[colformat] % value)
 190                 else:
 191                     treerow.append(' ')
 192             #print "addGrid, just before end of big for. grid:",grid,"treerow:",treerow
 193             iter = self.liststore[grid].append(treerow)
 194             sqlrow += 1
 195             row += 1
 196         vbox.show_all()
 197     #end def addGrid
 198
 199     def createStatsTable(self, vbox, tourneyTypes, playerids, sitenos, seats):
 200         startTime = time()
 201         show_detail = True
 202
 203         # Scrolled window for summary table
 204         swin = gtk.ScrolledWindow(hadjustment=None, vadjustment=None)
 205         swin.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
 206         swin.show()
 207         vbox.pack1(swin)
 208
 209         numTourneys = self.filters.getNumTourneys()
 210         self.addGrid(swin, 'tourneyPlayerDetailedStats', numTourneys, tourneyTypes, playerids, sitenos, seats)
 211
 212         print _("Stats page displayed in %4.2f seconds") % (time() - startTime)
 213     #end def createStatsTable
 214
 215     def fillStatsFrame(self, vbox):
 216         tourneyTypes = self.filters.getTourneyTypes()
 217         #tourneys = self.tourneys.getTourneys()
 218         sites = self.filters.getSites()
 219         heroes = self.filters.getHeroes()
 220         siteids = self.filters.getSiteIds()
 221         seats  = self.filters.getSeats()
 222         dates = self.filters.getDates()
 223         sitenos = []
 224         playerids = []
 225
 226         # Which sites are selected?
 227         for site in sites:
 228             if sites[site] == True:
 229                 sitenos.append(siteids[site])
 230                 _hname = Charset.to_utf8(heroes[site])
 231                 result = self.db.get_player_id(self.conf, site, _hname)
 232                 if result is not None:
 233                     playerids.append(int(result))
 234
 235         if not sitenos:
 236             #Should probably pop up here.
 237             print _("No sites selected - defaulting to PokerStars")
 238             sitenos = [2]
 239         if not playerids:
 240             print _("No player ids found")
 241             return
 242
 243         self.createStatsTable(vbox, tourneyTypes, playerids, sitenos, seats)
 244     #end def fillStatsFrame
 245
 246     def get_vbox(self):
 247         """returns the vbox of this thread"""
 248         return self.main_hbox
 249     #end def get_vbox
 250
 251     def refineQuery(self, query, numTourneys, tourneyTypes, playerids, sitenos, seats):
 252         having = ''
 253
 254         #print "start of refinequery, playerids:",playerids
 255         if playerids:
 256             nametest = str(tuple(playerids))
 257             nametest = nametest.replace("L", "")
 258             nametest = nametest.replace(",)",")")
 259         else:
 260             nametest = "1 = 2"
 261         #print "refinequery, nametest after initial creation:",nametest
 262         pname = "p.name"
 263         # set flag in self.columns to not show player name column
 264         #[x for x in self.columns if x[0] == 'pname'][0][1] = False #TODO: fix and reactivate
 265
 266         query = query.replace("<nametest>", nametest)
 267         query = query.replace("<playerName>", pname)
 268         query = query.replace("<havingclause>", having)
 269
 270         sitetest = ""
 271         q = []
 272         for m in self.filters.display.items():
 273             if m[0] == 'Sites' and m[1]:
 274                 for n in sitenos:
 275                         q.append(n)
 276                 if len(q) > 0:
 277                     sitetest = str(tuple(q))
 278                     sitetest = sitetest.replace("L", "")
 279                     sitetest = sitetest.replace(",)",")")
 280                     sitetest = sitetest.replace("u'","'")
 281                     sitetest = "and tt.siteId in %s" % sitetest#[1:-1]
 282                 else:
 283                     sitetest = "and tt.siteId IS NULL"
 284         #print "refinequery, sitetest before its use for replacement:",sitetest
 285         query = query.replace("<sitetest>", sitetest)
 286
 287         if seats:
 288             query = query.replace('<seats_test>', 'between ' + str(seats['from']) + ' and ' + str(seats['to']))
 289             if 'show' in seats and seats['show']:
 290                 query = query.replace('<groupbyseats>', ',h.seats')
 291                 query = query.replace('<orderbyseats>', ',h.seats')
 292             else:
 293                 query = query.replace('<groupbyseats>', '')
 294                 query = query.replace('<orderbyseats>', '')
 295         else:
 296             query = query.replace('<seats_test>', 'between 0 and 100')
 297             query = query.replace('<groupbyseats>', '')
 298             query = query.replace('<orderbyseats>', '')
 299
 300         #bbtest = self.filters.get_limits_where_clause(limits)
 301
 302         #query = query.replace("<gtbigBlind_test>", bbtest)
 303
 304         #query = query.replace("<orderbyhgametypeId>", "")
 305
 306         # process self.detailFilters (a list of tuples)
 307         flagtest = ''
 308         #self.detailFilters = [('h.seats', 5, 6)]   # for debug
 309         if self.detailFilters:
 310             for f in self.detailFilters:
 311                 if len(f) == 3:
 312                     # X between Y and Z
 313                     flagtest += ' and %s between %s and %s ' % (f[0], str(f[1]), str(f[2]))
 314         query = query.replace("<flagtest>", flagtest)
 315
 316         # allow for differences in sql cast() function:
 317         if self.db.backend == self.db.MYSQL_INNODB:
 318             query = query.replace("<signed>", 'signed ')
 319         else:
 320             query = query.replace("<signed>", '')
 321
 322         # Filter on dates
 323         start_date, end_date = self.filters.getDates()
 324         query = query.replace("<startdate_test>", start_date)
 325         query = query.replace("<enddate_test>", end_date)
 326
 327         return(query)
 328     #end def refineQuery
 329
 330     def refreshStats(self, widget, data):
 331         self.last_pos = self.stats_vbox.get_position()
 332         try: self.stats_vbox.destroy()
 333         except AttributeError: pass
 334         self.liststore = []
 335         self.listcols = []
 336         #self.stats_vbox = gtk.VBox(False, 0)
 337         self.stats_vbox = gtk.VPaned()
 338         self.stats_vbox.show()
 339         self.stats_frame.add(self.stats_vbox)
 340         self.fillStatsFrame(self.stats_vbox)
 341         if self.last_pos > 0:
 342             self.stats_vbox.set_position(self.last_pos)
 343     #end def refreshStats
 344
 345     def reset_style_render_func(self, treeviewcolumn, cell, model, iter):
 346         cell.set_property('foreground', None)
 347     #end def reset_style_render_func
 348
 349     def sortCols(self, col, nums):
 350         #This doesn't actually work yet - clicking heading in top section sorts bottom section :-(
 351         (n, grid) = nums
 352         if not col.get_sort_indicator() or col.get_sort_order() == gtk.SORT_ASCENDING:
 353             col.set_sort_order(gtk.SORT_DESCENDING)
 354         else:
 355             col.set_sort_order(gtk.SORT_ASCENDING)
 356         self.liststore[grid].set_sort_column_id(n, col.get_sort_order())
 357         self.liststore[grid].set_sort_func(n, self.sortnums, (n,grid))
 358         for i in xrange(len(self.listcols[grid])):
 359             self.listcols[grid][i].set_sort_indicator(False)
 360         self.listcols[grid][n].set_sort_indicator(True)
 361         # use this   listcols[col].set_sort_indicator(True)
 362         # to turn indicator off for other cols
 363     #end def sortCols
 364 #end class GuiTourneyPlayerStats