Coverage for GuiPositionalStats.py: 0%

1#!/usr/bin/env python

2# -*- coding: utf-8 -*-

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.

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.

18from __future__ import print_function

22#import L10n

23#_ = L10n.get_translation()

25import os

26from time import time, strftime

28import Database

29import Filters

30import Charset

32class GuiPositionalStats(object):

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

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

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())

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 }

62 self.filters = Filters.Filters(self.db, display = filters_display)

63 self.filters.registerButton1Name(("Refresh"))

64 self.filters.registerButton1Callback(self.refreshStats)

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 , ["stddev", True, "Stddev", 1.0, "%5.2f"]

94 ]

96 self.stat_table = None

97 self.stats_frame = None

98 self.stats_vbox = None

100 self.main_hbox = gtk.HPaned()

101

102 self.stats_frame = gtk.ScrolledWindow(hadjustment=None, vadjustment=None)

103 self.stats_frame.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)

104 self.stats_frame.show()

105

106 self.stats_vbox = gtk.VBox(False, 0)

107 self.stats_vbox.show()

108 self.stats_frame.add_with_viewport(self.stats_vbox)

109

110 # This could be stored in config eventually, or maybe configured in this window somehow.

111 # Each posncols element is the name of a column returned by the sql

112 # query (in lower case) and each posnheads element is the text to use as

113 # the heading in the GUI. Both sequences should be the same length.

114 # To miss columns out remove them from both tuples (the 1st 2 elements should always be included).

115 # To change the heading just edit the second list element as required

116 # If the first list element does not match a query column that pair is ignored

117 self.posncols = ( "game", "avgseats", "plposition", "vpip", "pfr", "pf3", "pf4", "pff3", "pff4", "steals"

118 , "saw_f", "sawsd", "wtsdwsf", "wmsd", "flafq", "tuafq", "rvafq"

119 , "pofafq", "net", "bbper100", "profitperhand", "variance", "stddev", "n"

120 )

121 self.posnheads = ( "Game", "Seats", "Posn", "VPIP", "PFR", "PF3", "PF4", "PFF3", "PFF4", "Steals"

122 , "Saw_F", "SawSD", "WtSDwsF", "W$SD", "FlAFq", "TuAFq", "RvAFq"

123 , "PoFAFq", "Net($)", "bb/100", "$/hand", "Variance", "Stddev", "Hds"

124 )

125

126 #self.fillStatsFrame(self.stats_vbox) #dont autoload, enter filters first (because of the bug that the tree is not scrollable, you cannot reach the refresh button with a lot of data)

127 #self.stats_frame.add(self.stats_vbox)

128

129 self.main_hbox.pack1(self.filters.get_vbox())

130 self.main_hbox.pack2(self.stats_frame)

131 self.main_hbox.show()

132

133

134 def get_vbox(self):

135 """returns the vbox of this thread"""

136 return self.main_hbox

137

138 def toggleCallback(self, widget, data=None):

139# print "%s was toggled %s" % (data, ("OFF", "ON")[widget.get_active()])

140 self.activesite = data

141 print (("DEBUG:") + " " + ("activesite set to %s") % (self.activesite))

142

143 def refreshStats(self, widget, data):

144 try: self.stats_vbox.destroy()

145 except AttributeError: pass

146 self.stats_vbox = gtk.VBox(False, 0)

147 self.stats_vbox.show()

148 self.stats_frame.add_with_viewport(self.stats_vbox)

149 self.fillStatsFrame(self.stats_vbox)

150

151 def fillStatsFrame(self, vbox):

152 sites = self.filters.getSites()

153 heroes = self.filters.getHeroes()

154 siteids = self.filters.getSiteIds()

155 limits = self.filters.getLimits()

156 seats = self.filters.getSeats()

157 dates = self.filters.getDates()

158 sitenos = []

159 playerids = []

160

161 # Which sites are selected?

162 for site in sites:

163 sitenos.append(siteids[site])

164 _hname = Charset.to_utf8(heroes[site])

165 result = self.db.get_player_id(self.conf, site, _hname)

166 if result is not None:

167 playerids.append(result)

168

169 if not sitenos:

170 #Should probably pop up here.

171 print(("No sites selected - defaulting to PokerStars"))

172 sitenos = [2]

173 if not playerids:

174 print(("No player ids found"))

175 return

176 if not limits:

177 print(("No limits found"))

178 return

179

180 self.createStatsTable(vbox, playerids, sitenos, limits, seats, dates)

181

182 def createStatsTable(self, vbox, playerids, sitenos, limits, seats, dates):

183

184 starttime = time()

185 colalias,colshow,colheading,colxalign,colformat = 0,1,2,3,4

186 row = 0

187 col = 0

188

189 tmp = self.sql.query['playerStatsByPosition']

190 tmp = self.refineQuery(tmp, playerids, sitenos, limits, seats, dates)

191 #print "DEBUG:\n%s" % tmp

192 self.cursor.execute(tmp)

193 result = self.cursor.fetchall()

194 colnames = [desc[0].lower() for desc in self.cursor.description]

195

196 liststore = gtk.ListStore(*([str] * len(colnames)))

197 view = gtk.TreeView(model=liststore)

198 view.set_grid_lines(gtk.TREE_VIEW_GRID_LINES_BOTH)

199 vbox.pack_start(view, expand=False, padding=3)

200 # left-aligned cells:

201 textcell = gtk.CellRendererText()

202 # centred cells:

203 textcell50 = gtk.CellRendererText()

204 textcell50.set_property('xalign', 0.5)

205 # right-aligned cells:

206 numcell = gtk.CellRendererText()

207 numcell.set_property('xalign', 1.0)

208 listcols = []

209

210 for t in self.posnheads:

211 listcols.append(gtk.TreeViewColumn(self.posnheads[col]))

212 view.append_column(listcols[col])

213 if col == 0:

214 listcols[col].pack_start(textcell, expand=True)

215 listcols[col].add_attribute(textcell, 'text', col)

216 listcols[col].set_expand(True)

217 elif col in (1, 2):

218 listcols[col].pack_start(textcell50, expand=True)

219 listcols[col].add_attribute(textcell50, 'text', col)

220 listcols[col].set_expand(True)

221 else:

222 listcols[col].pack_start(numcell, expand=True)

223 listcols[col].add_attribute(numcell, 'text', col)

224 listcols[col].set_expand(True)

225 col +=1

226

227 # Code below to be used when full column data structures implemented like in player stats:

228

229 # Create header row eg column: ("game", True, "Game", 0.0, "%s")

230 #for col, column in enumerate(cols_to_show):

231 # if column[colalias] == 'game' and holecards:

232 # s = [x for x in self.columns if x[colalias] == 'hand'][0][colheading]

233 # else:

234 # s = column[colheading]

235 # listcols.append(gtk.TreeViewColumn(s))

236 # view.append_column(listcols[col])

237 # if column[colformat] == '%s':

238 # if column[colxalign] == 0.0:

239 # listcols[col].pack_start(textcell, expand=True)

240 # listcols[col].add_attribute(textcell, 'text', col)

241 # else:

242 # listcols[col].pack_start(textcell50, expand=True)

243 # listcols[col].add_attribute(textcell50, 'text', col)

244 # listcols[col].set_expand(True)

245 # else:

246 # listcols[col].pack_start(numcell, expand=True)

247 # listcols[col].add_attribute(numcell, 'text', col)

248 # listcols[col].set_expand(True)

249 # #listcols[col].set_alignment(column[colxalign]) # no effect?

250

251 rows = len(result)

252

253 last_game,last_seats,sqlrow = "","",0

254 while sqlrow < rows:

255 rowprinted=0

256 treerow = []

257 avgcol = colnames.index('avgseats')

258 for col,colname in enumerate(self.posncols):

259 if colname in colnames:

260 sqlcol = colnames.index(colname)

261 else:

262 continue

263 if result[sqlrow][sqlcol]:

264 if sqlrow == 0:

265 value = result[sqlrow][sqlcol]

266 rowprinted=1

267 elif result[sqlrow][0] != last_game:

268 value = ' '

269# elif 'show' in seats and seats['show'] and result[sqlrow][avgcol] != last_seats: #FIXME 'show' in seats should now be 'show' in groups, but this class doesn't even use the group filters so it can never be set

270# value = ' '

271 else:

272 value = result[sqlrow][sqlcol]

273 rowprinted=1

274 else:

275 l = gtk.Label(' ')

276 value = ' '

277 if value and value != -999:

278 treerow.append(value)

279 else:

280 treerow.append(' ')

281 iter = liststore.append(treerow)

282 last_game = result[sqlrow][0]

283 last_seats = result[sqlrow][avgcol]

284 if rowprinted:

285 sqlrow = sqlrow+1

286 row = row + 1

287

288 # show totals at bottom

289 tmp = self.sql.query['playerStats']

290 tmp = self.refineQuery(tmp, playerids, sitenos, limits, seats, dates)

291 #print "DEBUG:\n%s" % tmp

292 self.cursor.execute(tmp)

293 result = self.cursor.fetchall()

294 rows = len(result)

295 colnames = [desc[0].lower() for desc in self.cursor.description]

296

297 # blank row between main stats and totals:

298 col = 0

299 treerow = [' ' for x in self.posncols]

300 iter = liststore.append(treerow)

301 row = row + 1

302

303 for sqlrow in range(rows):

304 treerow = []

305 for col,colname in enumerate(self.posncols):

306 if colname in colnames:

307 sqlcol = colnames.index(colname)

308 elif colname != "plposition":

309 continue

310 if colname == 'plposition':

311 l = gtk.Label('Totals')

312 value = 'Totals'

313 elif result[sqlrow][sqlcol]:

314 l = gtk.Label(result[sqlrow][sqlcol])

315 value = result[sqlrow][sqlcol]

316 else:

317 l = gtk.Label(' ')

318 value = ' '

319 if value and value != -999:

320 treerow.append(value)

321 else:

322 treerow.append(' ')

323 iter = liststore.append(treerow)

324 row = row + 1

325 vbox.show_all()

326

327 self.db.rollback()

328 print(("Positional Stats page displayed in %4.2f seconds") % (time() - starttime))

329 #end def fillStatsFrame(self, vbox):

330

331 def refineQuery(self, query, playerids, sitenos, limits, seats, dates):

332 if playerids:

333 nametest = str(tuple(playerids))

334 nametest = nametest.replace("L", "")

335 nametest = nametest.replace(",)",")")

336 query = query.replace("<player_test>", nametest)

337 else:

338 query = query.replace("<player_test>", "1 = 2")

339

340 if seats:

341 query = query.replace('<seats_test>', 'between ' + str(seats['from']) + ' and ' + str(seats['to']))

342 if False: #'show' in seats and seats['show']: should be 'show' in groups but we don't even show groups in filters

343 query = query.replace('<groupbyseats>', ',hc.seats')

344 query = query.replace('<orderbyseats>', ',stats.AvgSeats')

345 else:

346 query = query.replace('<groupbyseats>', '')

347 query = query.replace('<orderbyseats>', '')

348 else:

349 query = query.replace('<seats_test>', 'between 0 and 100')

350 query = query.replace('<groupbyseats>', '')

351 query = query.replace('<orderbyseats>', '')

352

353 bbtest = self.filters.get_limits_where_clause(limits)

354

355 query = query.replace("<gtbigBlind_test>", bbtest)

356

357 if self.db.backend == self.MYSQL_INNODB:

358 bigblindselect = """concat('$', trim(leading ' ' from

359 case when gt.bigBlind < 100

360 then format(gt.bigBlind/100.0, 2)

361 else format(gt.bigBlind/100.0, 0)

362 end

363 ) )"""

364 elif self.db.backend == self.SQLITE:

365 bigblindselect = """gt.bigBlind || gt.limitType || ' ' || gt.currency"""

366 else:

367 bigblindselect = """'$' || trim(leading ' ' from

368 case when gt.bigBlind < 100

369 then to_char(gt.bigBlind/100.0,'90D00')

370 else to_char(gt.bigBlind/100.0,'999990')

371 end

372 ) """

373 query = query.replace("<selectgt.bigBlind>", bigblindselect)

374 query = query.replace("<groupbygt.bigBlind>", ",gt.bigBlind")

375 query = query.replace("<hcgametypeId>", "hc.gametypeId")

376 query = query.replace("<hgametypeId>", "h.gametypeId")

377

378 # Filter on dates

379 query = query.replace("<datestest>", " between '" + dates[0] + "' and '" + dates[1] + "'")

380

381 #print "query =\n", query

382 return(query)

383 #end def refineQuery(self, query, playerids, sitenos, limits):