pyfpdb/GuiPositionalStats.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 threading
  22 import pygtk
  23 pygtk.require('2.0')
  24 import gtk
  25 import os
  26 from time import time, strftime
  27
  28 import fpdb_import
  29 import Database
  30 import Filters
  31 import Charset
  32
  33 class GuiPositionalStats (threading.Thread):
  34     def __init__(self, config, querylist, debug=True):
  35         self.debug = debug
  36         self.conf = config
  37         self.sql = querylist
  38         self.MYSQL_INNODB   = 2
  39         self.PGSQL          = 3
  40         self.SQLITE         = 4
  41
  42         # create new db connection to avoid conflicts with other threads
  43         self.db = Database.Database(self.conf, sql=self.sql)
  44         self.cursor = self.db.cursor
  45
  46         settings = {}
  47         settings.update(self.conf.get_db_parameters())
  48         settings.update(self.conf.get_import_parameters())
  49         settings.update(self.conf.get_default_paths())
  50
  51         filters_display = { "Heroes"   :  True,
  52                             "Sites"    :  True,
  53                             "Games"    :  False,
  54                             "Limits"   :  True,
  55                             "LimitSep" :  True,
  56                             "Seats"    :  True,
  57                             "SeatSep"  :  True,
  58                             "Dates"    :  True,
  59                             "Button1"  :  True,
  60                             "Button2"  :  False
  61                           }
  62
  63         self.filters = Filters.Filters(self.db, self.conf, self.sql, display = filters_display)
  64         self.filters.registerButton1Name(_("Refresh"))
  65         self.filters.registerButton1Callback(self.refreshStats)
  66
  67         # ToDo: store in config
  68         # ToDo: create popup to adjust column config
  69         # columns to display, keys match column name returned by sql, values in tuple are:
  70         #     is column displayed, column heading, xalignment, formatting
  71         self.columns = [ ["game",       True,  "Game",     0.0, "%s"]
  72                        , ["hand",       False, "Hand",     0.0, "%s"]   # true not allowed for this line
  73                        , ["plposition", False, "Posn",     1.0, "%s"]   # true not allowed for this line (set in code)
  74                        , ["n",          True,  "Hds",      1.0, "%d"]
  75                        , ["avgseats",   True,  "Seats",    1.0, "%3.1f"]
  76                        , ["vpip",       True,  "VPIP",     1.0, "%3.1f"]
  77                        , ["pfr",        True,  "PFR",      1.0, "%3.1f"]
  78                        , ["pf3",        True,  "PF3",      1.0, "%3.1f"]
  79                        , ["steals",     True,  "Steals",   1.0, "%3.1f"]
  80                        , ["saw_f",      True,  "Saw_F",    1.0, "%3.1f"]
  81                        , ["sawsd",      True,  "SawSD",    1.0, "%3.1f"]
  82                        , ["wtsdwsf",    True,  "WtSDwsF",  1.0, "%3.1f"]
  83                        , ["wmsd",       True,  "W$SD",     1.0, "%3.1f"]
  84                        , ["flafq",      True,  "FlAFq",    1.0, "%3.1f"]
  85                        , ["tuafq",      True,  "TuAFq",    1.0, "%3.1f"]
  86                        , ["rvafq",      True,  "RvAFq",    1.0, "%3.1f"]
  87                        , ["pofafq",     False, "PoFAFq",   1.0, "%3.1f"]
  88                        , ["net",        True,  "Net($)",   1.0, "%6.2f"]
  89                        , ["bbper100",   True,  "bb/100",   1.0, "%4.2f"]
  90                        , ["rake",       True,  "Rake($)",  1.0, "%6.2f"]
  91                        , ["bb100xr",    True,  "bbxr/100", 1.0, "%4.2f"]
  92                        , ["variance",   True,  "Variance", 1.0, "%5.2f"]
  93                        ]
  94
  95         self.stat_table = None
  96         self.stats_frame = None
  97         self.stats_vbox = None
  98
  99         self.main_hbox = gtk.HBox(False, 0)
 100         self.main_hbox.show()
 101
 102         self.stats_frame = gtk.Frame()
 103         self.stats_frame.set_label_align(0.0, 0.0)
 104         self.stats_frame.show()
 105         self.stats_vbox = gtk.VBox(False, 0)
 106         self.stats_vbox.show()
 107
 108         # This could be stored in config eventually, or maybe configured in this window somehow.
 109         # Each posncols element is the name of a column returned by the sql
 110         # query (in lower case) and each posnheads element is the text to use as
 111         # the heading in the GUI. Both sequences should be the same length.
 112         # To miss columns out remove them from both tuples (the 1st 2 elements should always be included).
 113         # To change the heading just edit the second list element as required
 114         # If the first list element does not match a query column that pair is ignored
 115         self.posncols =  ( "game", "avgseats", "plposition", "vpip", "pfr", "pf3", "pf4", "pff3", "pff4", "steals"
 116                          , "saw_f", "sawsd", "wtsdwsf", "wmsd", "flafq", "tuafq", "rvafq"
 117                          , "pofafq", "net", "bbper100", "profitperhand", "variance", "n"
 118                          )
 119         self.posnheads = ( "Game", "Seats", "Posn", "VPIP", "PFR", "PF3", "PF4", "PFF3", "PFF4", "Steals"
 120                          , "Saw_F", "SawSD", "WtSDwsF", "W$SD", "FlAFq", "TuAFq", "RvAFq"
 121                          , "PoFAFq", "Net($)", "bb/100", "$/hand", "Variance", "Hds"
 122                          )
 123
 124         self.fillStatsFrame(self.stats_vbox)
 125         self.stats_frame.add(self.stats_vbox)
 126
 127         self.main_hbox.pack_start(self.filters.get_vbox())
 128         self.main_hbox.pack_start(self.stats_frame)
 129
 130
 131     def get_vbox(self):
 132         """returns the vbox of this thread"""
 133         return self.main_hbox
 134
 135     def toggleCallback(self, widget, data=None):
 136 #        print "%s was toggled %s" % (data, ("OFF", "ON")[widget.get_active()])
 137         self.activesite = data
 138         print (_("DEBUG:") + " " + _("activesite set to %s") % (self.activesite))
 139
 140     def refreshStats(self, widget, data):
 141         try: self.stats_vbox.destroy()
 142         except AttributeError: pass
 143         self.stats_vbox = gtk.VBox(False, 0)
 144         self.stats_vbox.show()
 145         self.stats_frame.add(self.stats_vbox)
 146         self.fillStatsFrame(self.stats_vbox)
 147
 148     def fillStatsFrame(self, vbox):
 149         sites = self.filters.getSites()
 150         heroes = self.filters.getHeroes()
 151         siteids = self.filters.getSiteIds()
 152         limits  = self.filters.getLimits()
 153         seats = self.filters.getSeats()
 154         dates = self.filters.getDates()
 155         sitenos = []
 156         playerids = []
 157
 158         # Which sites are selected?
 159         for site in sites:
 160             if sites[site] == True:
 161                 sitenos.append(siteids[site])
 162                 _hname = Charset.to_utf8(heroes[site])
 163                 result = self.db.get_player_id(self.conf, site, _hname)
 164                 if result is not None:
 165                     playerids.append(result)
 166
 167         if not sitenos:
 168             #Should probably pop up here.
 169             print _("No sites selected - defaulting to PokerStars")
 170             sitenos = [2]
 171         if not playerids:
 172             print _("No player ids found")
 173             return
 174         if not limits:
 175             print _("No limits found")
 176             return
 177
 178         self.createStatsTable(vbox, playerids, sitenos, limits, seats, dates)
 179
 180     def createStatsTable(self, vbox, playerids, sitenos, limits, seats, dates):
 181
 182         starttime = time()
 183         colalias,colshow,colheading,colxalign,colformat = 0,1,2,3,4
 184         row = 0
 185         col = 0
 186
 187         tmp = self.sql.query['playerStatsByPosition']
 188         tmp = self.refineQuery(tmp, playerids, sitenos, limits, seats, dates)
 189         self.cursor.execute(tmp)
 190         result = self.cursor.fetchall()
 191         colnames = [desc[0].lower() for desc in self.cursor.description]
 192
 193         liststore = gtk.ListStore(*([str] * len(colnames)))
 194         view = gtk.TreeView(model=liststore)
 195         view.set_grid_lines(gtk.TREE_VIEW_GRID_LINES_BOTH)
 196         vbox.pack_start(view, expand=False, padding=3)
 197         # left-aligned cells:
 198         textcell = gtk.CellRendererText()
 199         # centred cells:
 200         textcell50 = gtk.CellRendererText()
 201         textcell50.set_property('xalign', 0.5)
 202         # right-aligned cells:
 203         numcell = gtk.CellRendererText()
 204         numcell.set_property('xalign', 1.0)
 205         listcols = []
 206
 207         for t in self.posnheads:
 208             listcols.append(gtk.TreeViewColumn(self.posnheads[col]))
 209             view.append_column(listcols[col])
 210             if col == 0:
 211                 listcols[col].pack_start(textcell, expand=True)
 212                 listcols[col].add_attribute(textcell, 'text', col)
 213                 listcols[col].set_expand(True)
 214             elif col in (1, 2):
 215                 listcols[col].pack_start(textcell50, expand=True)
 216                 listcols[col].add_attribute(textcell50, 'text', col)
 217                 listcols[col].set_expand(True)
 218             else:
 219                 listcols[col].pack_start(numcell, expand=True)
 220                 listcols[col].add_attribute(numcell, 'text', col)
 221                 listcols[col].set_expand(True)
 222             col +=1
 223
 224         # Code below to be used when full column data structures implemented like in player stats:
 225
 226         # Create header row   eg column: ("game",     True, "Game",     0.0, "%s")
 227         #for col, column in enumerate(cols_to_show):
 228         #    if column[colalias] == 'game' and holecards:
 229         #        s = [x for x in self.columns if x[colalias] == 'hand'][0][colheading]
 230         #    else:
 231         #        s = column[colheading]
 232         #    listcols.append(gtk.TreeViewColumn(s))
 233         #    view.append_column(listcols[col])
 234         #    if column[colformat] == '%s':
 235         #        if column[colxalign] == 0.0:
 236         #            listcols[col].pack_start(textcell, expand=True)
 237         #            listcols[col].add_attribute(textcell, 'text', col)
 238         #        else:
 239         #            listcols[col].pack_start(textcell50, expand=True)
 240         #            listcols[col].add_attribute(textcell50, 'text', col)
 241         #        listcols[col].set_expand(True)
 242         #    else:
 243         #        listcols[col].pack_start(numcell, expand=True)
 244         #        listcols[col].add_attribute(numcell, 'text', col)
 245         #        listcols[col].set_expand(True)
 246         #        #listcols[col].set_alignment(column[colxalign]) # no effect?
 247
 248         rows = len(result)
 249
 250         last_game,last_seats,sqlrow = "","",0
 251         while sqlrow < rows:
 252             rowprinted=0
 253             treerow = []
 254             avgcol = colnames.index('avgseats')
 255             for col,colname in enumerate(self.posncols):
 256                 if colname in colnames:
 257                     sqlcol = colnames.index(colname)
 258                 else:
 259                     continue
 260                 if result[sqlrow][sqlcol]:
 261                     if sqlrow == 0:
 262                         value = result[sqlrow][sqlcol]
 263                         rowprinted=1
 264                     elif result[sqlrow][0] != last_game:
 265                         value = ' '
 266                     elif 'show' in seats and seats['show'] and result[sqlrow][avgcol] != last_seats:
 267                         value = ' '
 268                     else:
 269                         value = result[sqlrow][sqlcol]
 270                         rowprinted=1
 271                 else:
 272                     l = gtk.Label(' ')
 273                     value = ' '
 274                 if value and value != -999:
 275                     treerow.append(value)
 276                 else:
 277                     treerow.append(' ')
 278             iter = liststore.append(treerow)
 279             last_game = result[sqlrow][0]
 280             last_seats = result[sqlrow][avgcol]
 281             if rowprinted:
 282                 sqlrow = sqlrow+1
 283             row = row + 1
 284
 285         # show totals at bottom
 286         tmp = self.sql.query['playerStats']
 287         tmp = self.refineQuery(tmp, playerids, sitenos, limits, seats, dates)
 288         self.cursor.execute(tmp)
 289         result = self.cursor.fetchall()
 290         rows = len(result)
 291         colnames = [desc[0].lower() for desc in self.cursor.description]
 292
 293         # blank row between main stats and totals:
 294         col = 0
 295         treerow = [' ' for x in self.posncols]
 296         iter = liststore.append(treerow)
 297         row = row + 1
 298
 299         for sqlrow in range(rows):
 300             treerow = []
 301             for col,colname in enumerate(self.posncols):
 302                 if colname in colnames:
 303                     sqlcol = colnames.index(colname)
 304                 elif colname != "plposition":
 305                     continue
 306                 if colname == 'plposition':
 307                     l = gtk.Label('Totals')
 308                     value = 'Totals'
 309                 elif result[sqlrow][sqlcol]:
 310                     l = gtk.Label(result[sqlrow][sqlcol])
 311                     value = result[sqlrow][sqlcol]
 312                 else:
 313                     l = gtk.Label(' ')
 314                     value = ' '
 315                 if value and value != -999:
 316                     treerow.append(value)
 317                 else:
 318                     treerow.append(' ')
 319             iter = liststore.append(treerow)
 320             row = row + 1
 321         vbox.show_all()
 322
 323         self.db.rollback()
 324         print _("Positional Stats page displayed in %4.2f seconds") % (time() - starttime)
 325     #end def fillStatsFrame(self, vbox):
 326
 327     def refineQuery(self, query, playerids, sitenos, limits, seats, dates):
 328         if playerids:
 329             nametest = str(tuple(playerids))
 330             nametest = nametest.replace("L", "")
 331             nametest = nametest.replace(",)",")")
 332             query = query.replace("<player_test>", nametest)
 333         else:
 334             query = query.replace("<player_test>", "1 = 2")
 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>', ',hc.activeSeats')
 340                 query = query.replace('<orderbyseats>', ',stats.AvgSeats')
 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) for x in limits if x.isdigit()]
 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         capnolims = [int(x[0:-2]) for x in limits if len(x) > 2 and x[-2:] == 'cn']
 353         bbtest = "( (gt.limitType = 'fl' and gt.bigBlind in "
 354                  # and ( (limit and bb in()) or (nolimit and bb in ()) )
 355         if lims:
 356             blindtest = str(tuple(lims))
 357             blindtest = blindtest.replace("L", "")
 358             blindtest = blindtest.replace(",)",")")
 359             bbtest = bbtest + blindtest + ' ) '
 360         else:
 361             bbtest = bbtest + '(-1) ) '
 362         bbtest = bbtest + " or (gt.limitType = 'pl' and gt.bigBlind in "
 363         if potlims:
 364             blindtest = str(tuple(potlims))
 365             blindtest = blindtest.replace("L", "")
 366             blindtest = blindtest.replace(",)",")")
 367             bbtest = bbtest + blindtest + ' ) '
 368         else:
 369             bbtest = bbtest + '(-1) ) '
 370         bbtest = bbtest + " or (gt.limitType = 'nl' and gt.bigBlind in "
 371         if nolims:
 372             blindtest = str(tuple(nolims))
 373             blindtest = blindtest.replace("L", "")
 374             blindtest = blindtest.replace(",)",")")
 375             bbtest = bbtest + blindtest + ' ) '
 376         else:
 377             bbtest = bbtest + '(-1) ) '
 378         bbtest = bbtest + " or (gt.limitType = 'cn' and gt.bigBlind in "
 379         if capnolims:
 380             blindtest = str(tuple(capnolims))
 381             blindtest = blindtest.replace("L", "")
 382             blindtest = blindtest.replace(",)",")")
 383             bbtest = bbtest + blindtest + ' ) )'
 384         else:
 385             bbtest = bbtest + '(-1) ) )'
 386         query = query.replace("<gtbigBlind_test>", bbtest)
 387
 388         groupLevels = "show" not in str(limits)
 389         if groupLevels:
 390             if self.db.backend == self.MYSQL_INNODB:
 391                 bigblindselect = """concat('$'
 392                                           ,trim(leading ' ' from
 393                                                 case when min(gt.bigBlind) < 100
 394                                                      then format(min(gt.bigBlind)/100.0, 2)
 395                                                      else format(min(gt.bigBlind)/100.0, 0)
 396                                                 end)
 397                                           ,' - $'
 398                                           ,trim(leading ' ' from
 399                                                 case when max(gt.bigBlind) < 100
 400                                                      then format(max(gt.bigBlind)/100.0, 2)
 401                                                      else format(max(gt.bigBlind)/100.0, 0)
 402                                                 end)
 403                                           ) """
 404             else:
 405                 bigblindselect = """'$' ||
 406                                     trim(leading ' ' from
 407                                          case when min(gt.bigBlind) < 100
 408                                               then to_char(min(gt.bigBlind)/100.0,'90D00')
 409                                               else to_char(min(gt.bigBlind)/100.0,'999990')
 410                                          end)
 411                                     || ' - $' ||
 412                                     trim(leading ' ' from
 413                                          case when max(gt.bigBlind) < 100
 414                                               then to_char(max(gt.bigBlind)/100.0,'90D00')
 415                                               else to_char(max(gt.bigBlind)/100.0,'999990')
 416                                          end) """
 417             bigblindselect = "cast('' as char)" # avoid odd effects when some posns and/or seats
 418                                                 # are missing from some limits (dunno why cast is
 419                                                 # needed but it says "unknown type" otherwise?!
 420             query = query.replace("<selectgt.bigBlind>", bigblindselect)
 421             query = query.replace("<groupbygt.bigBlind>", "")
 422             query = query.replace("<hcgametypeId>", "-1")
 423             query = query.replace("<hgametypeId>", "-1")
 424         else:
 425             if self.db.backend == self.MYSQL_INNODB:
 426                 bigblindselect = """concat('$', trim(leading ' ' from
 427                                                      case when gt.bigBlind < 100
 428                                                           then format(gt.bigBlind/100.0, 2)
 429                                                           else format(gt.bigBlind/100.0, 0)
 430                                                      end
 431                                                     ) )"""
 432             elif self.db.backend == self.SQLITE:
 433                 bigblindselect = """gt.bigBlind || gt.limitType || ' ' || gt.currency"""
 434             else:
 435                 bigblindselect = """'$' || trim(leading ' ' from
 436                                                 case when gt.bigBlind < 100
 437                                                      then to_char(gt.bigBlind/100.0,'90D00')
 438                                                      else to_char(gt.bigBlind/100.0,'999990')
 439                                                 end
 440                                                ) """
 441             query = query.replace("<selectgt.bigBlind>", bigblindselect)
 442             query = query.replace("<groupbygt.bigBlind>", ",gt.bigBlind")
 443             query = query.replace("<hcgametypeId>", "hc.gametypeId")
 444             query = query.replace("<hgametypeId>", "h.gametypeId")
 445
 446         # Filter on dates
 447         query = query.replace("<datestest>", " between '" + dates[0] + "' and '" + dates[1] + "'")
 448
 449         #print "query =\n", query
 450         return(query)
 451     #end def refineQuery(self, query, playerids, sitenos, limits):