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