Custom Stat support

[Parket]

Yes, I did properly Save/Apply. I've not had any problems with HUD customizations not being saved.
The weird thing is that I just did a simple test with a Test variable with fixed value 1, and a Test statistic that shows that variable. And that one does seem to persist. I'll do some further testing..

[Parket]

No idea what went wrong previously. I'm pretty sure I configured everything well and saved everything. But this time everything seems to work fine. I have 2 custom stats (Cashout % and Game Currency Cashout), 2 columns (val_cashout_base1 and val_cashout_base2) and 1 variable (var_val_cashout_pct) and all of them properly persist now. So that's good...

But... :)
As soon as I add one of the custom stats to a report, the report no longer works. First I get "Query does not have a result set" and when I hit refresh, the message turns into a SQL error. I'll file a ticket for this.

[kraada]

Thanks, PM me the ticket number and I'll look into it personally for you.

[Parket]

I started delving into the database itself via pgAdmin, and wondered whether your following query is even correct.

log( 1 + if[id_tourney_type in (SELECT id_tourney_type from tourney_table_type where tourney_table_type.val_flags SIMILAR TO '%(D|F)%'), 2, cnt_players])

Shouldn't "id_tourney_type" (2x) be "id_table_type" ? The way I understand this query is that the SELECT takes the primary key from all records from table tourney_table_type that have the D or F flag in val_flags, and then checks whether 'this' tourney has a foreign key id_table_type in that result set.
I'm actually surprised that PT4 even takes your query, because table tourney_table_type doesn't even have a column id_tourney_type.

That being said, I tried to start from scratch again to better understand the stats behavior. So I wanted to create a simple custom Stat that gives the 'equivalent' players. This is basically just the regular number of players of a tournament, except that for DON or F50 this value should be 2. I ended up with this stat :

if[id_table_type in (SELECT id_table_type from tourney_table_type where tourney_table_type.val_flags SIMILAR TO '%(D|F)%'), 2, cnt_players]

Now, thing is that if I don't check the Group By checkbox, I again get the SQL Error when I add that stat to one of the default reports (I use the Tournament - Basic - By Tournament one, and added my Equivalent Players stat after the default Players stat).
If I do tick the GROUP BY checkbox, my results are wrong as the column will always give value 2.

[Parket]

Had a look at the logs to see the actual generated SQL and now understand why I get a 2 in every column. The problem is that the whole CASE construct is not limiting to the tournament at hand.

This is the query that gets executed :

SELECT (tourney_summary.tourney_no) as "tourney_no", (tourney_summary.cnt_players) as "cnt_tourney_players_2", ((case when( (tourney_summary.id_table_type) in (SELECT (tourney_summary.id_table_type) from tourney_table_type where tourney_table_type.val_flags SIMILAR TO '%(D|F)%')) then 2 else tourney_summary.cnt_players end)) as "cnt_tourney_equivalent_players", (tourney_summary.id_table_type) as "id_table_type" FROM tourney_table_type, tourney_summary, tourney_hand_player_statistics WHERE (tourney_table_type.id_table_type = tourney_summary.id_table_type) AND (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney) AND (tourney_hand_player_statistics.id_player = (SELECT id_player FROM player WHERE player_name_search='viperpp' AND id_site='100')) GROUP BY (tourney_summary.tourney_no), (tourney_summary.cnt_players), (tourney_summary.id_table_type)

The CASE statement just takes ANY tourney_summary.id_table_type and matches it against the nested SELECT, rather than taking the specific tourney_summary record that matches tourney_no.

I tried several tricks to solve this, but nothing seemed to work...

[kraada]

That CASE construct is not the problem - the test is if tourney_summary.id_table_type is in the subselect - for each row that is the current table type of the tournament in question. Since the entire query is grouped by tourney_no it should work properly.

There is an id_tourney_type and it's in the tourney_summary table - that is probably part of our problem. If we replace id_tourney_type with id_table_type do we actually get the right values then?

[Parket]

I think I found a more fundamental problem.
I tried to generate a very simple report with 2 columns: Tournament # and Tournament Players. Both are default stats. The outcome of the report is surprising with huge numbers in the Players column.

The query that gets executed is :
SELECT (tourney_summary.tourney_no) as "tourney_no", (sum(tourney_summary.cnt_players)) as "cnt_tourney_players" FROM tourney_summary, tourney_hand_player_statistics WHERE (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney) AND (tourney_hand_player_statistics.id_player = (SELECT id_player FROM player WHERE player_name_search='viperpp' AND id_site='100')) GROUP BY (tourney_summary.tourney_no)

The problem is in the join with tourney_hand_player_statistics. I don't know the details, but I suspect that this actually returns many records per tourney_no (probably one per hand). As a result, the SUM will actually count the number of players multiple times, again probably once per hand. I guess this has to be solved by a DISTINCT or by a nested query on tourney_hand_player_statistics that simply lists the tourney_no's without duplicates.
But I guess the developers will better know what to do.

[Parket]

The bad news is that whatever I define in Custom Statistics, disappears as soon as I leave the Statistics GUI. When I re-enter Custom Statistics, I can't find any of my custom stats or columns.

I've found the circumstances under which this happens.
PT4 allows simultaneously editing/creating a Stat, Column and Variable. If you've saved your Stat and Column e.g., but then didn't save the Variable, and then try to exit the Statistics GUI, you get a popup that warns that you have a Modified variable and if you want to save it. If you now answer No, you not only loose that Variable that you didn't save yet, but you also loose the Stat and Column.

[Parket]

The query that gets executed is :
SELECT (tourney_summary.tourney_no) as "tourney_no", (sum(tourney_summary.cnt_players)) as "cnt_tourney_players" FROM tourney_summary, tourney_hand_player_statistics WHERE (tourney_summary.id_tourney = tourney_hand_player_statistics.id_tourney) AND (tourney_hand_player_statistics.id_player = (SELECT id_player FROM player WHERE player_name_search='viperpp' AND id_site='100')) GROUP BY (tourney_summary.tourney_no)

An interesting find. When I select the same 2 columns (and remove all the rest) in the Results - Tournament tab, the player counts were correct. I looked in the log-file and even though only 2 columns were selected, the query actually queries a 3rd one: amt_buyin_ttl_curr_conv which doesn't get displayed. So I added the same column (My Currency Net Cost) to my own custom report, and then also the count was correct. The query looked as follows :

SELECT (tourney_summary.tourney_no) as "tourney_no", (sum(tourney_summary.cnt_players)) as "cnt_tourney_players", (sum((case when(tourney_summary.val_curr_conv != 0) then tourney_summary.val_curr_conv * (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) else (CASE WHEN tourney_summary.currency='EUR' THEN ((tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) * 1.2947) ELSE (CASE WHEN tourney_summary.currency='GBP' THEN ((tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) * 1.5539) ELSE (CASE WHEN tourney_summary.currency='SEK' THEN ((tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) * 0.1475) ELSE (CASE WHEN tourney_summary.currency='TRY' THEN ((tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) * 0.5458) ELSE (CASE WHEN tourney_summary.currency='PLY' THEN ((tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) * 0.0000) ELSE (tourney_summary.amt_buyin + tourney_summary.amt_fee + tourney_summary.amt_rebuy * tourney_results.cnt_rebuy + tourney_summary.amt_addon * tourney_results.cnt_addon + tourney_summary.amt_bounty) END) END) END) END) END) end))) as "amt_buyin_ttl_curr_conv" FROM tourney_results, tourney_summary WHERE (tourney_results.id_tourney = tourney_summary.id_tourney) AND (tourney_results.id_player = (SELECT id_player FROM player WHERE player_name_search='viperpp' AND id_site='100')) GROUP BY (tourney_summary.tourney_no)

The 'interesting' thing is that the query now joins tourney_results and tourney_summary instead of tourney_summary and tourney_hand_player_statistics. This seems to confirm my earlier belief that joining with tourney_hand_player_statistics in the first case doesn't make a lot of sense. I don't think end users should be verifying the generated SQL to understand how their custom reports are behaving.

PS: sorry about the monologue...

[WhiteRider]

The bad news is that whatever I define in Custom Statistics, disappears as soon as I leave the Statistics GUI. When I re-enter Custom Statistics, I can't find any of my custom stats or columns.

I've found the circumstances under which this happens.
PT4 allows simultaneously editing/creating a Stat, Column and Variable. If you've saved your Stat and Column e.g., but then didn't save the Variable, and then try to exit the Statistics GUI, you get a popup that warns that you have a Modified variable and if you want to save it. If you now answer No, you not only loose that Variable that you didn't save yet, but you also loose the Stat and Column.

I can't reproduce this - if I have clicked Save for the column and stat but not the variable then the column and variable are there when I re-open the custom stats window (but the variable isn't). Can you take me through your exact steps?

I will let Kraada follow up the report/query issue.