This kind of thing is no longer needed as PT3 now has a built-in "M" stat and mechanism for building other "live" HUD stats.
--------------------
Ok, so I made some quick SQL functions to get M working. These will also let you convert simple stats to M specific such as having a separate VPIP / PFR / 3Bet / almost anything.
Like the last one of these I did, you need to open up pgAdmin to run a script.
This script will calculate the M value and work around the caching issue.
- Code: Select all
CREATE LANGUAGE plpgsql;
-- Function: tourney_getm(integer, integer, integer)
-- DROP FUNCTION tourney_getm(integer, integer, integer);
CREATE OR REPLACE FUNCTION tourney_getm(ihand integer, iplayer integer, bforce integer)
RETURNS double precision AS
$BODY$
DECLARE
idBlinds integer;
iSB float;
iBB float;
iPlayers integer;
iAnte float;
iChipsStart float;
iChipsChange float;
iMax int;
iReturn double precision;
BEGIN
IF (bForce = 1) THEN
iMax := 0;
ELSE
UPDATE tourney_holdem_cache SET val_m = 0 WHERE id_player = iPlayer;
SELECT max(id_hand) INTO iMax FROM tourney_holdem_hand_player_statistics WHERE id_player = iPlayer;
END IF;
IF (iHand < iMax) THEN
iReturn := 0;
ELSE
SELECT id_blinds INTO idBlinds FROM tourney_holdem_hand_player_statistics WHERE id_hand = iHand AND id_player = iPlayer;
SELECT amt_bb INTO iBB FROM tourney_holdem_blinds WHERE id_blinds = idBlinds;
SELECT amt_sb INTO iSB FROM tourney_holdem_blinds WHERE id_blinds = idBlinds;
SELECT amt_ante INTO iAnte FROM tourney_holdem_hand_player_detail WHERE id_hand = iHand;
SELECT amt_before INTO iChipsStart FROM tourney_holdem_hand_player_detail WHERE id_hand = iHand AND id_player = iPlayer;
SELECT cnt_players INTO iPlayers FROM tourney_holdem_hand_summary WHERE id_hand = iHand;
IF (bForce = 1) THEN
iChipsChange := 0;
ELSE
SELECT amt_won INTO iChipsChange FROM tourney_holdem_hand_player_statistics WHERE id_hand = iHand AND id_player = iPlayer;
END IF;
iReturn := (iChipsStart + iChipsChange) / ((iAnte * iPlayers) + iSB + iBB);
END IF;
RETURN iReturn;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION tourney_getm(integer, integer, integer) OWNER TO postgres;
-- Function: tourney_modifystat(integer, integer, integer, text, text, character)
-- DROP FUNCTION tourney_modifystat(integer, integer, integer, text, text, character);
CREATE OR REPLACE FUNCTION tourney_modifystat(ihand integer, iplayer integer, izone integer, _table text, stat text, test character)
RETURNS integer AS
$BODY$
DECLARE
iM double precision;
sExec text;
sValue character(1);
iVerify integer;
BEGIN
SELECT Tourney_GetM(iHand, iPlayer, 1) INTO iM;
SELECT Tourney_VerifyZone(iM, iZone) INTO iVerify;
IF (iVerify = 1) THEN
EXECUTE 'SELECT ' || stat || ' FROM ' || _table || ' WHERE id_Hand = ' || iHand || ' AND id_Player = ' || iPlayer INTO sValue;
END IF;
IF (sValue = test) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION tourney_modifystat(integer, integer, integer, text, text, character) OWNER TO postgres;
-- Function: tourney_modifystat(integer, integer, integer, text, text, integer)
-- DROP FUNCTION tourney_modifystat(integer, integer, integer, text, text, integer);
CREATE OR REPLACE FUNCTION tourney_modifystat(ihand integer, iplayer integer, izone integer, _table text, stat text, i integer)
RETURNS integer AS
$BODY$
DECLARE
iM double precision;
bReturn boolean;
sExec text;
iVerify integer;
BEGIN
bReturn = FALSE;
SELECT Tourney_GetM(iHand, iPlayer, 1) INTO iM;
SELECT Tourney_VerifyZone(iM, iZone) INTO iVerify;
IF (iVerify = 1) THEN
EXECUTE 'SELECT ' || stat || ' FROM ' || _table || ' WHERE id_Hand = ' || iHand || ' AND id_Player = ' || iPlayer INTO bReturn;
END IF;
IF (bReturn) THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION tourney_modifystat(integer, integer, integer, text, text, integer) OWNER TO postgres;
-- Function: tourney_modifystat(integer, integer, integer, text, text)
-- DROP FUNCTION tourney_modifystat(integer, integer, integer, text, text);
CREATE OR REPLACE FUNCTION tourney_modifystat(ihand integer, iplayer integer, izone integer, _table text, stat text)
RETURNS double precision AS
$BODY$
DECLARE
iM double precision;
iReturn double precision;
sExec text;
iVerify integer;
BEGIN
iReturn := 0;
SELECT Tourney_GetM(iHand, iPlayer, 1) INTO iM;
SELECT Tourney_VerifyZone(iM, iZone) INTO iVerify;
IF (iVerify = 1) THEN
EXECUTE 'SELECT ' || stat || ' FROM ' || _table || ' WHERE id_Hand = ' || iHand || ' AND id_Player = ' || iPlayer INTO iReturn;
END IF;
RETURN iReturn;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION tourney_modifystat(integer, integer, integer, text, text) OWNER TO postgres;
-- Function: tourney_verifyzone(double precision, integer)
-- DROP FUNCTION tourney_verifyzone(double precision, integer);
CREATE OR REPLACE FUNCTION tourney_verifyzone(im double precision, izone integer)
RETURNS integer AS
$BODY$
DECLARE
iReturn integer;
BEGIN
iReturn := 0;
IF (iZone = 0) THEN
IF (iM <= 1) THEN iReturn := 1; END IF;
ELSEIF (iZone = 1) THEN
IF (iM <= 5.5 AND iM >= 1) THEN iReturn := 1; END IF;
ELSEIF (iZone = 2) THEN
IF (iM <= 11 AND iM >= 4.75) THEN iReturn := 1; END IF;
ELSEIF (iZone = 3) THEN
IF (iM <= 21 AND iM >= 9) THEN iReturn := 1; END IF;
ELSEIF (iZone = 4) THEN
IF (iM <= 54 AND iM >= 19) THEN iReturn := 1; END IF;
ELSEIF (iZone = 5) THEN
IF (iM >= 48) THEN iReturn := 1; END IF;
END IF;
RETURN iReturn;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION tourney_verifyzone(double precision, integer) OWNER TO postgres;
The last function included here called Tourney_VerifyZone is the one that designates the ranges of zones you'd like to use. I like to have a bit of overlap in my zones such as red going from 1 to 5.5 and orange going from 4.75 to 11. This is up to you if you can edit the code above.
Ok, how to use it?
Make a Column in the "Configure Stats" called "val_m"
The expression you use is:
- Code: Select all
sum((SELECT Tourney_GetM(tourney_holdem_hand_player_statistics.id_hand, tourney_holdem_hand_player_statistics.id_player, 0)))
Then you can make a Statistic called "M" that has the expression of val_m
Next, if you want to make zone specific stats:
Example is for VPIP in the red zone
Make a Column called cnt_zone_vpip_red, and give it the expression:
- Code: Select all
sum((SELECT Tourney_ModifyStat(tourney_holdem_hand_player_statistics.id_hand, tourney_holdem_hand_player_statistics.id_player, 1, 'tourney_holdem_hand_player_statistics', 'flg_vpip', 1)))
The first two variables passed are the hand and player numbers. Don't change these. Next is the zone number - 1 for red, 2 for orange, 3 for yellow, 4 for green and 5 for blue.
Next is the table name of the stat you want to use. Since VPIP is in tourney_holdem_hand_player_statistics, that's what we use (must be in single quotes).
Then you put in the stat you want to use, here it's 'flg_vpip'.
Last is a 1 which is used if you're getting a flg_ variable. If you want to test against a char (to see if the variable holds an 'R' or 'C'), you can pass that on like this for checking on a flop cbet:
- Code: Select all
sum((SELECT Tourney_ModifyStat(tourney_holdem_hand_player_statistics.id_hand, tourney_holdem_hand_player_statistics.id_player, 2, 'tourney_holdem_hand_player_statistics', 'enum_f_cbet_action', 'R')))
I haven't perfected the complex stats yet (such as how someone reacts to a steal attempt), but I will get that done eventually.
I hope someone finds this useful.
)
