pyfpdb/GuiRingPlayerStats.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 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 Filters
  34 import Charset
  35 import GuiPlayerStats
  36
  37 from TreeViewTooltips import TreeViewTooltips
  38
  39
  40 #colalias,colshowsumm,colshowposn,colheading,colxalign,colformat,coltype = 0,1,2,3,4,5,6
  41 #new order in config file:
  42 colalias,colheading,colshowsumm,colshowposn,colformat,coltype,colxalign = 0,1,2,3,4,5,6
  43 ranks = {'x':0, '2':2, '3':3, '4':4, '5':5, '6':6, '7':7, '8':8, '9':9, 'T':10, 'J':11, 'Q':12, 'K':13, 'A':14}
  44 onlinehelp = {'Game':_('Type of Game'),
  45               'Hand':_('Hole cards'),
  46               'Posn':_('Position'),
  47               'Name':_('Player Name'),
  48               'Hds':_('Number of hands seen'),
  49               'Seats':_('Number of Seats'),
  50               'VPIP':_('Voluntarily put in preflop/3rd street %'),
  51               'PFR':_('Preflop/3rd street raise %'),
  52               'PF3':_('% 3 bet preflop/3rd street'),
  53               'PF4':_('% 4 bet preflop/3rd street'),
  54               'PFF3':_('% fold to 3 bet preflop/3rd street'),
  55               'PFF4':_('% fold to 4 bet preflop/3rd street'),
  56               'AggFac':_('Aggression factor')+("\n"),
  57               'AggFreq':_('Post-flop aggression frequency'),
  58               'ContBet':_('% continuation bet'),
  59               'RFI':_('% Raise First In / % Raise when first to bet'),
  60               'Steals':_('% steal attempted'),
  61               'Saw_F':_('Flop/4th street seen %'),
  62               'SawSD':_('Saw Showdown / River'),
  63               'WtSDwsF':_('% went to showdown when seen flop/4th street'),
  64               'W$SD':_('% won some money at showdown'),
  65               'FlAFq':_('Aggression frequency flop/4th street'),
  66               'TuAFq':_('Aggression frequency turn/5th street'),
  67               'RvAFq':_('Aggression frequency river/6th street'),
  68               'PoFAFq':_('Coming Soon\nTotal % agression'),
  69               'Net($)':_('Total Profit'),
  70               'bb/100':_('Big blinds won per 100 hands'),
  71               'Rake($)':_('Amount of rake paid'),
  72               'bbxr/100':_('Big blinds won per 100 hands\nwhen excluding rake'),
  73               'Variance':_('Measure of uncertainty\nThe lower, the more stable the amounts won')
  74               }
  75
  76
  77
  78 class DemoTips(TreeViewTooltips):
  79
  80     def __init__(self, customer_column):
  81         # call base class init
  82         TreeViewTooltips.__init__(self)
  83
  84     def get_tooltip(self, view, column, path):
  85         model = view.get_model()
  86         cards = model[path][0]
  87         title=column.get_title()
  88         if (title == 'Hand' or title == 'Game'): display=''     #no tooltips on headers
  89         else: display='<big>%s for %s</big>\n<i>%s</i>' % (title,cards,onlinehelp[title])
  90         return (display)
  91
  92     def location(self, x, y, w, h):
  93         # this will place the tooltip above and to the right
  94         return x + 30, y - (h + 10)
  95
  96
  97
  98 class GuiRingPlayerStats (GuiPlayerStats.GuiPlayerStats):
  99
 100     def __init__(self, config, querylist, mainwin, debug=True):
 101         self.debug = debug
 102         self.conf = config
 103         self.main_window = mainwin
 104         self.sql = querylist
 105
 106         self.liststore = []   # gtk.ListStore[]         stores the contents of the grids
 107         self.listcols = []    # gtk.TreeViewColumn[][]  stores the columns in the grids
 108
 109         self.MYSQL_INNODB   = 2
 110         self.PGSQL          = 3
 111         self.SQLITE         = 4
 112
 113         # create new db connection to avoid conflicts with other threads
 114         self.db = Database.Database(self.conf, sql=self.sql)
 115         self.cursor = self.db.cursor
 116
 117         settings = {}
 118         settings.update(self.conf.get_db_parameters())
 119         settings.update(self.conf.get_import_parameters())
 120         settings.update(self.conf.get_default_paths())
 121
 122         # text used on screen stored here so that it can be configured
 123         self.filterText = {'handhead':_('Hand Breakdown for all levels listed above')
 124                           }
 125
 126         filters_display = { "Heroes"    : True,
 127                             "Sites"     : True,
 128                             "Games"     : True,
 129                             "Limits"    : True,
 130                             "LimitSep"  : True,
 131                             "LimitType" : True,
 132                             "Type"      : True,
 133                             "Seats"     : True,
 134                             "SeatSep"   : True,
 135                             "Dates"     : True,
 136                             "Groups"    : True,
 137                             "GroupsAll" : True,
 138                             "Button1"   : True,
 139                             "Button2"   : True
 140                           }
 141
 142         self.filters = Filters.Filters(self.db, self.conf, self.sql, display = filters_display)
 143         self.filters.registerButton1Name(_("_Filters"))
 144         self.filters.registerButton1Callback(self.showDetailFilter)
 145         self.filters.registerButton2Name(_("_Refresh Stats"))
 146         self.filters.registerButton2Callback(self.refreshStats)
 147
 148         # ToDo: store in config
 149         # ToDo: create popup to adjust column config
 150         # columns to display, keys match column name returned by sql, values in tuple are:
 151         #     is column displayed(summary then position), column heading, xalignment, formatting, celltype
 152         self.columns = self.conf.get_gui_cash_stat_params()
 153
 154         # Detail filters:  This holds the data used in the popup window, extra values are
 155         # added at the end of these lists during processing
 156         #                  sql test,              screen description,        min, max
 157         self.handtests = [  # already in filter class : ['h.seats', 'Number of Players', 2, 10]
 158                           ['h.maxSeats',          'Size of Table',         2, 10]
 159                          ,['h.playersVpi',        'Players who VPI',       0, 10]
 160                          ,['h.playersAtStreet1',  'Players at Flop',       0, 10]
 161                          ,['h.playersAtStreet2',  'Players at Turn',       0, 10]
 162                          ,['h.playersAtStreet3',  'Players at River',      0, 10]
 163                          ,['h.playersAtStreet4',  'Players at Street7',    0, 10]
 164                          ,['h.playersAtShowdown', 'Players at Showdown',   0, 10]
 165                          ,['h.street0Raises',     'Bets to See Flop',      0,  5]
 166                          ,['h.street1Raises',     'Bets to See Turn',      0,  5]
 167                          ,['h.street2Raises',     'Bets to See River',     0,  5]
 168                          ,['h.street3Raises',     'Bets to See Street7',   0,  5]
 169                          ,['h.street4Raises',     'Bets to See Showdown',  0,  5]
 170                          ]
 171
 172         self.cardstests = [
 173             [Card.DATABASE_FILTERS['pair'], _('Pocket pairs')],
 174             [Card.DATABASE_FILTERS['suited'], _('Suited')],
 175             [Card.DATABASE_FILTERS['suited_connectors'], _('Suited connectors')],
 176             [Card.DATABASE_FILTERS['offsuit'], _('Offsuit')],
 177             [Card.DATABASE_FILTERS['offsuit_connectors'], _('Offsuit connectors')],
 178         ]
 179         self.stats_frame = None
 180         self.stats_vbox = None
 181         self.detailFilters = []   # the data used to enhance the sql select
 182         self.cardsFilters = []
 183
 184         #self.main_hbox = gtk.HBox(False, 0)
 185         #self.main_hbox.show()
 186         self.main_hbox = gtk.HPaned()
 187
 188         self.stats_frame = gtk.Frame()
 189         self.stats_frame.show()
 190
 191         self.stats_vbox = gtk.VPaned()
 192         self.stats_vbox.show()
 193         self.stats_frame.add(self.stats_vbox)
 194         self.top_pane_height = 0
 195         self.height_inc = None
 196         # self.fillStatsFrame(self.stats_vbox)
 197
 198         #self.main_hbox.pack_start(self.filters.get_vbox())
 199         #self.main_hbox.pack_start(self.stats_frame, expand=True, fill=True)
 200         self.main_hbox.pack1(self.filters.get_vbox())
 201         self.main_hbox.pack2(self.stats_frame)
 202         self.main_hbox.show()
 203
 204         # make sure Hand column is not displayed
 205         [x for x in self.columns if x[0] == 'hand'][0][colshowsumm] = False
 206         [x for x in self.columns if x[0] == 'hand'][0][colshowposn] = False
 207         # if rfi and steal both on for summaries, turn rfi off
 208         if ( [x for x in self.columns if x[0] == 'rfi'][0][colshowsumm]
 209             and [x for x in self.columns if x[0] == 'steals'][0][colshowsumm]):
 210             [x for x in self.columns if x[0] == 'rfi'][0][colshowsumm] = False
 211         # if rfi and steal both on for position breakdowns, turn steals off:
 212         if ( [x for x in self.columns if x[0] == 'rfi'][0][colshowposn]
 213             and [x for x in self.columns if x[0] == 'steals'][0][colshowposn]):
 214             [x for x in self.columns if x[0] == 'steals'][0][colshowposn] = False
 215
 216         self.last_pos = -1
 217
 218
 219     def get_vbox(self):
 220         """returns the vbox of this thread"""
 221         return self.main_hbox
 222     #end def get_vbox
 223
 224     def refreshStats(self, widget, data):
 225         #self.last_pos = self.stats_vbox.get_position()
 226         self.height_inc = None
 227         #old_len = 0
 228         #if self.liststore:
 229         #    old_len = len(self.liststore[0])
 230         try: self.stats_vbox.destroy()
 231         except AttributeError: pass
 232         self.liststore = []
 233         self.listcols = []
 234         self.stats_vbox = gtk.VPaned()
 235         self.stats_vbox.show()
 236         self.stats_frame.add(self.stats_vbox)
 237         self.fillStatsFrame(self.stats_vbox)
 238
 239         # set height of top pane
 240         # (tried 2 ways, guesstimate using ratio of old to new number of rows and sum of
 241         #  heights of parts)
 242         new_len = 0
 243         if self.liststore:
 244             #new_len = len(self.liststore[0])
 245             #print "setting to", self.top_pane_height + self.height_inc
 246             self.stats_vbox.set_position(self.top_pane_height + self.height_inc)
 247         #if self.last_pos > 0:
 248         #    if old_len > 0 and new_len > 0 and new_len <= 10:
 249         #        self.stats_vbox.set_position(self.last_pos * (new_len+1.9)/(old_len+1.9))
 250         #    else:
 251         #        self.stats_vbox.set_position(self.last_pos)
 252     #end def refreshStats
 253
 254     def fillStatsFrame(self, vbox):
 255         sites = self.filters.getSites()
 256         heroes = self.filters.getHeroes()
 257         siteids = self.filters.getSiteIds()
 258         limits  = self.filters.getLimits()
 259         type   = self.filters.getType()
 260         seats  = self.filters.getSeats()
 261         groups = self.filters.getGroups()
 262         dates = self.filters.getDates()
 263         games = self.filters.getGames()
 264         sitenos = []
 265         playerids = []
 266
 267         # Which sites are selected?
 268         for site in sites:
 269             if sites[site] == True:
 270                 sitenos.append(siteids[site])
 271                 _hname = Charset.to_utf8(heroes[site])
 272                 result = self.db.get_player_id(self.conf, site, _hname)
 273                 if result is not None:
 274                     playerids.append(int(result))
 275
 276         if not sitenos:
 277             #Should probably pop up here.
 278             print _("No sites selected - defaulting to PokerStars")
 279             sitenos = [2]
 280         if not playerids:
 281             print _("No player ids found")
 282             return
 283         if not limits:
 284             print _("No limits found")
 285             return
 286
 287         self.createStatsTable(vbox, playerids, sitenos, limits, type, seats, groups, dates, games)
 288     #end def fillStatsFrame
 289
 290     def createStatsTable(self, vbox, playerids, sitenos, limits, type, seats, groups, dates, games):
 291         startTime = time()
 292         show_detail = True
 293
 294         # Scrolled window for summary table
 295         swin = gtk.ScrolledWindow(hadjustment=None, vadjustment=None)
 296         swin.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
 297         vbox.pack1(swin) #, resize=True)  don't use resize, self.height_inc relies on initial
 298                          # height of pane being correct for one row
 299
 300         # Display summary table at top of page
 301         # 3rd parameter passes extra flags, currently includes:
 302         #   holecards - whether to display card breakdown (True/False)
 303         #   numhands  - min number hands required when displaying all players
 304         #   gridnum   - index for grid data structures
 305         flags = [False, self.filters.getNumHands(), 0]
 306         self.addGrid(swin, 'playerDetailedStats', flags, playerids
 307                     ,sitenos, limits, type, seats, groups, dates, games)
 308         swin.show()
 309
 310         if 'allplayers' in groups and groups['allplayers']:
 311             # can't currently do this combination so skip detailed table
 312             show_detail = False
 313
 314         if show_detail:
 315             # Separator
 316             vbox2 = gtk.VBox(False, 0)
 317             heading = gtk.Label(self.filterText['handhead'])
 318             heading.show()
 319             vbox2.pack_start(heading, expand=False, padding=3)
 320
 321             # Scrolled window for detailed table (display by hand)
 322             swin2 = gtk.ScrolledWindow(hadjustment=None, vadjustment=None)
 323             swin2.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
 324             swin2.show()
 325             vbox2.pack_start(swin2, expand=True, padding=3)
 326             vbox.pack2(vbox2)
 327             vbox2.show()
 328
 329             # Detailed table
 330             flags[0] = True
 331             flags[2] = 1
 332             self.addGrid(swin2, 'playerDetailedStats', flags, playerids
 333                         ,sitenos, limits, type, seats, groups, dates, games)
 334
 335         if self.height_inc is None:
 336             self.height_inc = 0
 337             # need this to check whether scrollbar is visible:
 338             while gtk.events_pending(): # see http://faq.pygtk.org/index.py?req=index for more hints (3.7)
 339                 gtk.main_iteration(False)
 340             hs = swin.get_hscrollbar()
 341             if hs is not None:
 342                 #print "hs vis", hs.get_property('visible'), hs.get_property('visible').__class__
 343                 if hs.get_property('visible'):
 344                     self.height_inc = hs.size_request()[1] + swin.style_get_property('scrollbar-spacing')
 345             #print "hh set to", self.height_inc
 346             self.stats_vbox.set_position(self.top_pane_height + self.height_inc)
 347
 348         self.db.rollback()
 349         print (_("Stats page displayed in %4.2f seconds") % (time() - startTime))
 350     #end def createStatsTable
 351
 352     def reset_style_render_func(self, treeviewcolumn, cell, model, iter):
 353         cell.set_property('foreground', None)
 354     #end def reset_style_render_func
 355
 356     def ledger_style_render_func(self, tvcol, cell, model, iter):
 357         str = cell.get_property('text')
 358         if '-' in str:
 359             str = str.replace("-", "")
 360             str = "(%s)" %(str)
 361             cell.set_property('text', str)
 362             cell.set_property('foreground', 'red')
 363         else:
 364             cell.set_property('foreground', 'darkgreen')
 365
 366         return
 367
 368     def sortnums(self, model, iter1, iter2, nums):
 369         try:
 370             ret = 0
 371             (n, grid) = nums
 372             a = self.liststore[grid].get_value(iter1, n)
 373             b = self.liststore[grid].get_value(iter2, n)
 374             if 'f' in self.cols_to_show[n][4]:
 375                 try:     a = float(a)
 376                 except:  a = 0.0
 377                 try:     b = float(b)
 378                 except:  b = 0.0
 379             if n == 0 and grid == 1: #make sure it only works on the starting hands
 380                 a1,a2,a3 = ranks[a[0]], ranks[a[1]], (a+'o')[2]
 381                 b1,b2,b3 = ranks[b[0]], ranks[b[1]], (b+'o')[2]
 382                 if a1 > b1 or ( a1 == b1 and (a2 > b2 or (a2 == b2 and a3 > b3) ) ):
 383                     ret = 1
 384                 else:
 385                     ret = -1
 386             else:
 387                 if a < b:
 388                     ret = -1
 389                 elif a == b:
 390                     ret = 0
 391                 else:
 392                     ret = 1
 393             #print "n =", n, "iter1[n] =", self.liststore[grid].get_value(iter1,n), "iter2[n] =", self.liststore[grid].get_value(iter2,n), "ret =", ret
 394         except:
 395             err = traceback.extract_tb(sys.exc_info()[2])
 396             print _("***sortnums error: ") + str(sys.exc_info()[1])
 397             print "\n".join( [e[0]+':'+str(e[1])+" "+e[2] for e in err] )
 398
 399         return(ret)
 400
 401     def sortcols(self, col, nums):
 402         try:
 403             #This doesn't actually work yet - clicking heading in top section sorts bottom section :-(
 404             (n, grid) = nums
 405             if not col.get_sort_indicator() or col.get_sort_order() == gtk.SORT_ASCENDING:
 406                 col.set_sort_order(gtk.SORT_DESCENDING)
 407             else:
 408                 col.set_sort_order(gtk.SORT_ASCENDING)
 409             self.liststore[grid].set_sort_column_id(n, col.get_sort_order())
 410             self.liststore[grid].set_sort_func(n, self.sortnums, (n,grid))
 411             for i in xrange(len(self.listcols[grid])):
 412                 self.listcols[grid][i].set_sort_indicator(False)
 413             self.listcols[grid][n].set_sort_indicator(True)
 414             # use this   listcols[col].set_sort_indicator(True)
 415             # to turn indicator off for other cols
 416         except:
 417             err = traceback.extract_tb(sys.exc_info()[2])
 418             print _("***sortcols error: ") + str(sys.exc_info()[1])
 419             print "\n".join( [e[0]+':'+str(e[1])+" "+e[2] for e in err] )
 420     #end def sortcols
 421
 422
 423     def addGrid(self, vbox, query, flags, playerids, sitenos, limits, type, seats, groups, dates, games):
 424         counter = 0
 425         row = 0
 426         sqlrow = 0
 427         if not flags:  holecards,grid = False,0
 428         else:          holecards,grid = flags[0],flags[2]
 429
 430         tmp = self.sql.query[query]
 431         tmp = self.refineQuery(tmp, flags, playerids, sitenos, limits, type, seats, groups, dates, games)
 432         #print "DEBUG: query: %s" % tmp
 433         self.cursor.execute(tmp)
 434         result = self.cursor.fetchall()
 435         colnames = [desc[0].lower() for desc in self.cursor.description]
 436
 437         # pre-fetch some constant values:
 438         colshow = colshowsumm
 439         if groups['posn']:  colshow = colshowposn
 440         self.cols_to_show = [x for x in self.columns if x[colshow]]
 441         hgametypeid_idx = colnames.index('hgametypeid')
 442
 443         assert len(self.liststore) == grid, "len(self.liststore)="+str(len(self.liststore))+" grid-1="+str(grid)
 444         self.liststore.append( gtk.ListStore(*([str] * len(self.cols_to_show))) )
 445         view = gtk.TreeView(model=self.liststore[grid])
 446         view.set_grid_lines(gtk.TREE_VIEW_GRID_LINES_BOTH)
 447         #vbox.pack_start(view, expand=False, padding=3)
 448         vbox.add(view)
 449         textcell = gtk.CellRendererText()
 450         textcell50 = gtk.CellRendererText()
 451         textcell50.set_property('xalign', 0.5)
 452         numcell = gtk.CellRendererText()
 453         numcell.set_property('xalign', 1.0)
 454         assert len(self.listcols) == grid
 455         self.listcols.append( [] )
 456
 457         # Create header row   eg column: ("game",     True, "Game",     0.0, "%s")
 458         for col, column in enumerate(self.cols_to_show):
 459             if column[colalias] == 'game' and holecards:
 460                 s = [x for x in self.columns if x[colalias] == 'hand'][0][colheading]
 461             else:
 462                 s = column[colheading]
 463             self.listcols[grid].append(gtk.TreeViewColumn(s))
 464             view.append_column(self.listcols[grid][col])
 465             if column[colformat] == '%s':
 466                 if column[colxalign] == 0.0:
 467                     self.listcols[grid][col].pack_start(textcell, expand=True)
 468                     self.listcols[grid][col].add_attribute(textcell, 'text', col)
 469                     cellrend = textcell
 470                 else:
 471                     self.listcols[grid][col].pack_start(textcell50, expand=True)
 472                     self.listcols[grid][col].add_attribute(textcell50, 'text', col)
 473                     cellrend = textcell50
 474                 self.listcols[grid][col].set_expand(True)
 475             else:
 476                 self.listcols[grid][col].pack_start(numcell, expand=True)
 477                 self.listcols[grid][col].add_attribute(numcell, 'text', col)
 478                 self.listcols[grid][col].set_expand(True)
 479                 cellrend = numcell
 480                 #self.listcols[grid][col].set_alignment(column[colxalign]) # no effect?
 481             self.listcols[grid][col].set_clickable(True)
 482             self.listcols[grid][col].connect("clicked", self.sortcols, (col,grid))
 483             if col == 0:
 484                 self.listcols[grid][col].set_sort_order(gtk.SORT_DESCENDING)
 485                 self.listcols[grid][col].set_sort_indicator(True)
 486             if column[coltype] == 'cash':
 487                 self.listcols[grid][col].set_cell_data_func(numcell, self.ledger_style_render_func)
 488             else:
 489                 self.listcols[grid][col].set_cell_data_func(cellrend, self.reset_style_render_func)
 490
 491         rows = len(result) # +1 for title row
 492
 493         while sqlrow < rows:
 494             treerow = []
 495             for col,column in enumerate(self.cols_to_show):
 496                 if column[colalias] in colnames:
 497                     value = result[sqlrow][colnames.index(column[colalias])]
 498                     if column[colalias] == 'plposition':
 499                         if value == 'B':
 500                             value = 'BB'
 501                         elif value == 'S':
 502                             value = 'SB'
 503                         elif value == '0':
 504                             value = 'Btn'
 505                 else:
 506                     if column[colalias] == 'game':
 507                         if holecards:
 508                             value = Card.decodeStartHandValue(result[sqlrow][colnames.index('category')], result[sqlrow][hgametypeid_idx] )
 509                         else:
 510                             minbb = result[sqlrow][colnames.index('minbigblind')]
 511                             maxbb = result[sqlrow][colnames.index('maxbigblind')]
 512                             value = result[sqlrow][colnames.index('limittype')] + ' ' \
 513                                     + result[sqlrow][colnames.index('category')].title() + ' ' \
 514                                     + result[sqlrow][colnames.index('name')] + ' $'
 515                             if 100 * int(minbb/100.0) != minbb:
 516                                 value += '%.2f' % (minbb/100.0)
 517                             else:
 518                                 value += '%.0f' % (minbb/100.0)
 519                             if minbb != maxbb:
 520                                 if 100 * int(maxbb/100.0) != maxbb:
 521                                     value += ' - $' + '%.2f' % (maxbb/100.0)
 522                                 else:
 523                                     value += ' - $' + '%.0f' % (maxbb/100.0)
 524                     else:
 525                         continue
 526                 if value != None and value != -999:
 527                     treerow.append(column[colformat] % value)
 528                 else:
 529                     treerow.append(' ')
 530             iter = self.liststore[grid].append(treerow)
 531             #print treerow
 532             sqlrow += 1
 533             row += 1
 534         tips = DemoTips(column[colformat])
 535         tips.add_view(view)
 536
 537         vbox.show_all()
 538         view.show()
 539         if len(self.liststore) == 1:
 540             #print "view hieght is ", view.get_allocation().height, view.size_request(), view.get_visible_rect().height, view.get_vadjustment().get_value()
 541             self.top_pane_height = view.size_request()[1]
 542             #print "saved ", self.top_pane_height
 543     #end def addGrid
 544
 545     def refineQuery(self, query, flags, playerids, sitenos, limits, type, seats, groups, dates, games):
 546         having = ''
 547         if not flags:
 548             holecards = False
 549             numhands = 0
 550         else:
 551             holecards = flags[0]
 552             numhands = flags[1]
 553         colshow = colshowsumm
 554         if groups['posn']:  colshow = colshowposn
 555
 556         if 'allplayers' in groups and groups['allplayers']:
 557             nametest = "(hp.playerId)"
 558             if holecards or groups['posn']:
 559                 pname = "'all players'"
 560                 # set flag in self.columns to not show player name column
 561                 [x for x in self.columns if x[0] == 'pname'][0][colshow] = False
 562                 # can't do this yet (re-write doing more maths in python instead of sql?)
 563                 if numhands:
 564                     nametest = "(-1)"
 565             else:
 566                 pname = "p.name"
 567                 # set flag in self.columns to show player name column
 568                 [x for x in self.columns if x[0] == 'pname'][0][colshow] = True
 569                 if numhands:
 570                     having = ' and count(1) > %d ' % (numhands,)
 571         else:
 572             if playerids:
 573                 nametest = str(tuple(playerids))
 574                 nametest = nametest.replace("L", "")
 575                 nametest = nametest.replace(",)",")")
 576             else:
 577                 nametest = "1 = 2"
 578             pname = "p.name"
 579             # set flag in self.columns to not show player name column
 580             [x for x in self.columns if x[0] == 'pname'][0][colshow] = False
 581         query = query.replace("<player_test>", nametest)
 582         query = query.replace("<playerName>", pname)
 583         query = query.replace("<havingclause>", having)
 584
 585         gametest = ""
 586         q = []
 587         for m in self.filters.display.items():
 588             if m[0] == 'Games' and m[1]:
 589                 for n in games:
 590                     if games[n]:
 591                         q.append(n)
 592                 if len(q) > 0:
 593                     gametest = str(tuple(q))
 594                     gametest = gametest.replace("L", "")
 595                     gametest = gametest.replace(",)",")")
 596                     gametest = gametest.replace("u'","'")
 597                     gametest = "and gt.category in %s" % gametest
 598                 else:
 599                     gametest = "and gt.category IS NULL"
 600         query = query.replace("<game_test>", gametest)
 601
 602         sitetest = ""
 603         q = []
 604         for m in self.filters.display.items():
 605             if m[0] == 'Sites' and m[1]:
 606                 for n in sitenos:
 607                         q.append(n)
 608                 if len(q) > 0:
 609                     sitetest = str(tuple(q))
 610                     sitetest = sitetest.replace("L", "")
 611                     sitetest = sitetest.replace(",)",")")
 612                     sitetest = sitetest.replace("u'","'")
 613                     sitetest = "and gt.siteId in %s" % sitetest
 614                 else:
 615                     sitetest = "and gt.siteId IS NULL"
 616         query = query.replace("<site_test>", sitetest)
 617
 618         if seats:
 619             query = query.replace('<seats_test>', 'between ' + str(seats['from']) + ' and ' + str(seats['to']))
 620             if 'show' in seats and seats['show']:
 621                 query = query.replace('<groupbyseats>', ',h.seats')
 622                 query = query.replace('<orderbyseats>', ',h.seats')
 623             else:
 624                 query = query.replace('<groupbyseats>', '')
 625                 query = query.replace('<orderbyseats>', '')
 626         else:
 627             query = query.replace('<seats_test>', 'between 0 and 100')
 628             query = query.replace('<groupbyseats>', '')
 629             query = query.replace('<orderbyseats>', '')
 630
 631         lims = [int(x) for x in limits if x.isdigit()]
 632         potlims = [int(x[0:-2]) for x in limits if len(x) > 2 and x[-2:] == 'pl']
 633         nolims = [int(x[0:-2]) for x in limits if len(x) > 2 and x[-2:] == 'nl']
 634         capnolims = [int(x[0:-2]) for x in limits if len(x) > 2 and x[-2:] == 'cn']
 635         bbtest = "and ( (gt.limitType = 'fl' and gt.bigBlind in "
 636                  # and ( (limit and bb in()) or (nolimit and bb in ()) )
 637         if lims:
 638             blindtest = str(tuple(lims))
 639             blindtest = blindtest.replace("L", "")
 640             blindtest = blindtest.replace(",)",")")
 641             bbtest = bbtest + blindtest + ' ) '
 642         else:
 643             bbtest = bbtest + '(-1) ) '
 644         bbtest = bbtest + " or (gt.limitType = 'pl' and gt.bigBlind in "
 645         if potlims:
 646             blindtest = str(tuple(potlims))
 647             blindtest = blindtest.replace("L", "")
 648             blindtest = blindtest.replace(",)",")")
 649             bbtest = bbtest + blindtest + ' ) '
 650         else:
 651             bbtest = bbtest + '(-1) ) '
 652         bbtest = bbtest + " or (gt.limitType = 'nl' and gt.bigBlind in "
 653         if nolims:
 654             blindtest = str(tuple(nolims))
 655             blindtest = blindtest.replace("L", "")
 656             blindtest = blindtest.replace(",)",")")
 657             bbtest = bbtest + blindtest + ' ) '
 658         else:
 659             bbtest = bbtest + '(-1) ) '
 660         bbtest = bbtest + " or (gt.limitType = 'cn' and gt.bigBlind in "
 661         if capnolims:
 662             blindtest = str(tuple(capnolims))
 663             blindtest = blindtest.replace("L", "")
 664             blindtest = blindtest.replace(",)",")")
 665             bbtest = bbtest + blindtest + ' ) )'
 666         else:
 667             bbtest = bbtest + '(-1) ) )'
 668         if type == 'ring':
 669             bbtest = bbtest + " and gt.type = 'ring' "
 670         elif type == 'tour':
 671             bbtest = " and gt.type = 'tour' "
 672         query = query.replace("<gtbigBlind_test>", bbtest)
 673
 674         if holecards:  # re-use level variables for hole card query
 675             query = query.replace("<hgametypeId>", "hp.startcards")
 676             query = query.replace("<orderbyhgametypeId>"
 677                                  , ",case when floor((hp.startcards-1)/13) >= mod((hp.startcards-1),13) then hp.startcards + 0.1 "
 678                                    +    " else 13*mod((hp.startcards-1),13) + floor((hp.startcards-1)/13) + 1 "
 679                                    +    " end desc ")
 680         else:
 681             query = query.replace("<orderbyhgametypeId>", "")
 682             groupLevels = "show" not in str(limits)
 683             if groupLevels:
 684                 query = query.replace("<hgametypeId>", "p.name")  # need to use p.name for sqlite posn stats to work
 685             else:
 686                 query = query.replace("<hgametypeId>", "h.gametypeId")
 687
 688         # process self.detailFilters (a list of tuples)
 689         flagtest = ''
 690         #self.detailFilters = [('h.seats', 5, 6)]   # for debug
 691         if self.detailFilters:
 692             for f in self.detailFilters:
 693                 if len(f) == 3:
 694                     # X between Y and Z
 695                     flagtest += ' and %s between %s and %s ' % (f[0], str(f[1]), str(f[2]))
 696         query = query.replace("<flagtest>", flagtest)
 697         if self.cardsFilters:
 698             cardstests = []
 699
 700             for filter in self.cardsFilters:
 701                 cardstests.append(filter)
 702             cardstests = ''.join(('and (', ' or '.join(cardstests), ')'))
 703         else:
 704             cardstests = ''
 705         query = query.replace("<cardstest>", cardstests)
 706         # allow for differences in sql cast() function:
 707         if self.db.backend == self.MYSQL_INNODB:
 708             query = query.replace("<signed>", 'signed ')
 709         else:
 710             query = query.replace("<signed>", '')
 711
 712         # Filter on dates
 713         query = query.replace("<datestest>", " between '" + dates[0] + "' and '" + dates[1] + "'")
 714
 715         # Group by position?
 716         if groups['posn']:
 717             #query = query.replace("<position>", "case hp.position when '0' then 'Btn' else hp.position end")
 718             query = query.replace("<position>", "hp.position")
 719             # set flag in self.columns to show posn column
 720             [x for x in self.columns if x[0] == 'plposition'][0][colshow] = True
 721         else:
 722             query = query.replace("<position>", "gt.base")
 723             # unset flag in self.columns to hide posn column
 724             [x for x in self.columns if x[0] == 'plposition'][0][colshow] = False
 725
 726         #print "query =\n", query
 727         return(query)
 728     #end def refineQuery
 729
 730     def showDetailFilter(self, widget, data):
 731         detailDialog = gtk.Dialog(title=_("Detailed Filters"), parent=self.main_window
 732                                  ,flags=gtk.DIALOG_MODAL | gtk.DIALOG_DESTROY_WITH_PARENT
 733                                  ,buttons=(gtk.STOCK_CANCEL, gtk.RESPONSE_REJECT,
 734                                            gtk.STOCK_OK, gtk.RESPONSE_ACCEPT))
 735
 736         handbox = gtk.VBox(True, 0)
 737         detailDialog.vbox.pack_start(handbox, False, False, 0)
 738         handbox.show()
 739
 740         label = gtk.Label(_("Hand Filters:"))
 741         handbox.add(label)
 742         label.show()
 743
 744         betweenFilters = []
 745         def add_hbox():
 746             hbox = gtk.HBox(False, 0)
 747             handbox.pack_start(hbox, False, False, 0)
 748             hbox.show()
 749             return hbox
 750
 751         for htest in self.handtests:
 752             hbox = add_hbox()
 753             cb = gtk.CheckButton()
 754             lbl_from = gtk.Label(htest[1])
 755             lbl_from.set_alignment(xalign=0.0, yalign=0.5)
 756             lbl_tween = gtk.Label(_('between'))
 757             lbl_to   = gtk.Label(_('and'))
 758             adj1 = gtk.Adjustment(value=htest[2], lower=0, upper=10, step_incr=1, page_incr=1, page_size=0)
 759             sb1 = gtk.SpinButton(adjustment=adj1, climb_rate=0.0, digits=0)
 760             adj2 = gtk.Adjustment(value=htest[3], lower=2, upper=10, step_incr=1, page_incr=1, page_size=0)
 761             sb2 = gtk.SpinButton(adjustment=adj2, climb_rate=0.0, digits=0)
 762
 763             for df in [x for x in self.detailFilters if x[0] == htest[0]]:
 764                 cb.set_active(True)
 765
 766             hbox.pack_start(cb, expand=False, padding=3)
 767             hbox.pack_start(lbl_from, expand=True, padding=3)
 768             hbox.pack_start(lbl_tween, expand=False, padding=3)
 769             hbox.pack_start(sb1, False, False, 0)
 770             hbox.pack_start(lbl_to, expand=False, padding=3)
 771             hbox.pack_start(sb2, False, False, 0)
 772
 773             cb.show()
 774             lbl_from.show()
 775             lbl_tween.show()
 776             sb1.show()
 777             lbl_to.show()
 778             sb2.show()
 779
 780             htest[4:7] = [cb,sb1,sb2]
 781
 782         label = gtk.Label(_('Restrict to hand types:'))
 783         handbox.add(label)
 784         label.show()
 785         for ctest in self.cardstests:
 786             hbox = add_hbox()
 787             cb = gtk.CheckButton()
 788             if ctest[0] in self.cardsFilters:
 789                 cb.set_active(True)
 790             label = gtk.Label(ctest[1])
 791             hbox.pack_start(cb, expand=False, padding=3)
 792             hbox.pack_start(label, expand=True, padding=3)
 793             cb.show()
 794             label.show()
 795             ctest[2:3] = [cb]
 796         response = detailDialog.run()
 797
 798         if response == gtk.RESPONSE_ACCEPT:
 799             self.detailFilters = []
 800             for ht in self.handtests:
 801                 if ht[4].get_active():
 802                     self.detailFilters.append( (ht[0], ht[5].get_value_as_int(), ht[6].get_value_as_int()) )
 803                 ht[2],ht[3] = ht[5].get_value_as_int(), ht[6].get_value_as_int()
 804             print "detailFilters =", self.detailFilters
 805             self.cardsFilters = []
 806             for ct in self.cardstests:
 807                 if ct[2].get_active():
 808                     self.cardsFilters.append(ct[0])
 809             print "cardsFilters =", self.cardsFilters
 810             self.refreshStats(None, None)
 811
 812         detailDialog.destroy()
 813
 814
 815
 816
 817