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