Quote:
Originally Posted by sorrow
The session viewer very new and very broken at the moment. Any opinions on what it looks like at the moment?
I have no advice about its appearance, since it looks pretty good at the moment. But I've noticed that it always seems to miss the most recent session. Earlier sessions seem to be shown correctly.
I've added the SQL necessary to get the session viewer to work with SQLite, as given in the following patch. Unfortunately this must contain a literal "%s" (for some reason %s means "convert to UTC time-stamp"), so I've had to alter the code that substitutes ? for %s.
In the player stats code, the bit about
to_char(h.handStart, 'YYYY-MM-DD') turns out the be unnecessary (
handStart alone is sufficient). It is really necessary in MySQL?
The code to put the player statistics in order is commented out ("<orderbyhgameTypeId>") as I couldn't get it to work. Hence the hole cards are initially shown out of order, but one can still force them to be sorted by clicking on the column heading (though this is obviously not a good solution).
Code:
diff --git a/pyfpdb/SQL.py b/pyfpdb/SQL.py
index 3c25963..1f4fd06 100644
--- a/pyfpdb/SQL.py
+++ b/pyfpdb/SQL.py
@@ -1895,9 +1895,9 @@ class Sql:
self.query['playerDetailedStats'] = """
select <hgameTypeId> AS hgametypeid
,gt.base
- ,gt.category
+ ,gt.category AS category
,upper(gt.limitType) AS limittype
- ,s.name
+ ,s.name AS name
,min(gt.bigBlind) AS minbigblind
,max(gt.bigBlind) AS maxbigblind
/*,<hcgametypeId> AS gtid*/
@@ -1939,7 +1939,7 @@ class Sql:
,100.0*avg((hp.totalProfit+hp.rake)/(gt.bigBlind+0.0)) AS bb100xr
,avg((hp.totalProfit+hp.rake)/100.0) AS profhndxr
,avg(h.seats+0.0) AS avgseats
- ,variance(hp.totalProfit/100.0) AS variance
+ ,0.0 AS variance
from HandsPlayers hp
inner join Hands h on (h.id = hp.handId)
inner join Gametypes gt on (gt.Id = h.gameTypeId)
@@ -1949,7 +1949,7 @@ class Sql:
and h.seats <seats_test>
<flagtest>
<gtbigBlind_test>
- and to_char(h.handStart, 'YYYY-MM-DD') <datestest>
+ and h.handStart <datestest>
group by hgameTypeId
,hp.playerId
,gt.base
@@ -1967,7 +1967,7 @@ class Sql:
when '0' then 'Y'
else 'Z'||<position>
end
- <orderbyhgameTypeId>
+ /*<orderbyhgameTypeId>*/
,upper(gt.limitType) desc
,maxbigblind desc
,s.name
@@ -2501,7 +2501,16 @@ class Sql:
AND h.handStart <datestest>
ORDER by time"""
elif db_server == 'sqlite':
- self.query['sessionStats'] = """ """
+ self.query['sessionStats'] = """
+ SELECT STRFTIME('%s', h.handStart) as time, hp.handId, hp.startCash, hp.winnings, hp.totalProfit
+ FROM HandsPlayers hp
+ INNER JOIN Hands h on (h.id = hp.handId)
+ INNER JOIN Gametypes gt on (gt.Id = h.gameTypeId)
+ INNER JOIN Sites s on (s.Id = gt.siteId)
+ INNER JOIN Players p on (p.Id = hp.playerId)
+ WHERE hp.playerId in <player_test>
+ AND h.handStart <datestest>
+ ORDER by time"""
####################################
# Queries to rebuild/modify hudcache
@@ -3194,7 +3203,8 @@ class Sql:
# If using sqlite, use the ? placeholder instead of %s
if db_server == 'sqlite':
for k,q in self.query.iteritems():
- self.query[k] = re.sub('%s','?',q)
+ if k != 'sessionStats':
+ self.query[k] = re.sub('%s','?',q)
if __name__== "__main__":
# just print the default queries and exit