# Teamratio calculations
# http://css.setti.info/code/teamratio/
#
# Here are instructions how to set up Teamratio calulation in CS:S with Psychostats2.
# Everything needed is in this file. There are "teamratio.php" and "teamratio-player.php"
# also, but they are not needed for calculations. PHP files are needed to output results
# to web page.
#
# All database actions must be done to the same database which PS2 uses.
#
# Mysql 5 is required (for VIEWs).
#
# It is recommended to install phpMyAdmin to make it easier to run these commands (and see the results)
# http://www.phpmyadmin.net/

############
## Step 1 ##

# Create settings table where it is easy to change "skilled player" skill limit and other requirements
# Default setting for skilled player is 1200. This may change much on different servers.
# Good limit is the value on player on rank 50. or 100.
#
# Default setting for skill_threshold is 1200
# Default setting for playtime_threshold is 43200 seconds = 12 hours
# Default setting for lastseen is 864000 = 10 days
#
# You can set playtime_threshold to 0 to see some results. Later on it is better to 
# increase platime_threshold to exclude newbies
#
# You can change settings by passing following commands to mysql:
#   UPDATE `TR_config` SET `skill_threshold` = '1200'
#   UPDATE `TR_config` SET `playtime_threshold` = '43200'
#   UPDATE `TR_config` SET `lastseen` = '864000'
#
# You need to run following two commands only once.

CREATE TABLE `TR_config` (`skill_threshold` int(11) NOT NULL, `playtime_threshold` int(11) NOT NULL, `lastseen` int(11) NOT NULL)
INSERT INTO TR_config VALUES ( 1200, 43200, 864000 );


############
## Step 2 ##

# Create view, so it is easy to make queries there.
# This basically selects all players who have played in the last 10 days on the server
# and have skill over the "skill_threshold" value set in "toomuchvalues" table.
# This is usable table (or view) for other purposes too.
#
# ALL FOLLOWING LINES ARE ONE COMMAND FOR MYSQL

# PS2

CREATE VIEW `SKILLED_PLAYERS` AS
select sql_no_cache max(`pstats_plrdata`.`oldskill`) AS `maxskill`,
`pstats_plr`.`name` AS `name`,
`pstats_plrdata`.`plrid` AS `plrid`,
`pstats_plr`.`rank` AS `rank`,
(`pstats_c_plrdata_halflife_cstrike`.`ctwon` + `pstats_c_plrdata_halflife_cstrike`.`ctlost`) AS `ctrounds`,
(`pstats_c_plrdata_halflife_cstrike`.`terroristwon` + `pstats_c_plrdata_halflife_cstrike`.`terroristlost`) AS `terroristrounds`,
((`pstats_c_plrdata_halflife_cstrike`.`ctwon` + `pstats_c_plrdata_halflife_cstrike`.`ctlost`) / (((`pstats_c_plrdata_halflife_cstrike`.`ctwon` + `pstats_c_plrdata_halflife_cstrike`.`ctlost`) + `pstats_c_plrdata_halflife_cstrike`.`terroristwon`) + `pstats_c_plrdata_halflife_cstrike`.`terroristlost`)) AS `ctratio`,
((`pstats_c_plrdata_halflife_cstrike`.`terroristwon` + `pstats_c_plrdata_halflife_cstrike`.`terroristlost`) / (((`pstats_c_plrdata_halflife_cstrike`.`ctwon` + `pstats_c_plrdata_halflife_cstrike`.`ctlost`) + `pstats_c_plrdata_halflife_cstrike`.`terroristwon`) + `pstats_c_plrdata_halflife_cstrike`.`terroristlost`)) AS `tratio` 

from ((`pstats_c_plrdata_halflife_cstrike` join `pstats_plr`) join `pstats_plrdata`) 

where ((`pstats_c_plrdata_halflife_cstrike`.`plrid` = `pstats_plr`.`plrid`) and 
(`pstats_plr`.`plrid` = `pstats_plrdata`.`plrid`) and 
(`pstats_c_plrdata_halflife_cstrike`.`onlinetime` > (select sql_no_cache `TR_config`.`playtime_threshold` AS `playtime_threshold` 
from `TR_config` limit 1)) and 
(`pstats_c_plrdata_halflife_cstrike`.`lasttime` > (unix_timestamp() - (select sql_no_cache `TR_config`.`lastseen` AS `lastseen` 
from `TR_config` limit 1))) and 
(unix_timestamp(`pstats_plrdata`.`statdate`) > (unix_timestamp() - (select sql_no_cache `TR_config`.`lastseen` AS `lastseen` 
from `TR_config` limit 1)))) group by `pstats_plr`.`plrid` having (max(`pstats_plrdata`.`oldskill`) > (select sql_no_cache `TR_config`.`skill_threshold` AS `skill_threshold` 
from `TR_config`))

# MYSQL COMMAND ENDS HERE

# PS3

CREATE VIEW `SKILLED_PLAYERS` AS
select sql_no_cache max(`ps_plr_data`.`dayskill`) AS `maxskill`,
`ps_plr`.`uniqueid` AS `name`,
`ps_plr_data`.`plrid` AS `plrid`,
`ps_plr`.`rank` AS `rank`,
(`ps_c_plr_data`.`ctwon` + `ps_c_plr_data`.`ctlost`) AS `ctrounds`,
(`ps_c_plr_data`.`terroristwon` + `ps_c_plr_data`.`terroristlost`) AS `terroristrounds`,
((`ps_c_plr_data`.`ctwon` + `ps_c_plr_data`.`ctlost`) / (((`ps_c_plr_data`.`ctwon` + `ps_c_plr_data`.`ctlost`) + `ps_c_plr_data`.`terroristwon`) + `ps_c_plr_data`.`terroristlost`)) AS `ctratio`,
((`ps_c_plr_data`.`terroristwon` + `ps_c_plr_data`.`terroristlost`) / (((`ps_c_plr_data`.`ctwon` + `ps_c_plr_data`.`ctlost`) + `ps_c_plr_data`.`terroristwon`) + `ps_c_plr_data`.`terroristlost`)) AS `tratio` 

from ((`ps_c_plr_data` join `ps_plr`) join `ps_plr_data`) 

where ((`ps_c_plr_data`.`plrid` = `ps_plr`.`plrid`) and 
(`ps_plr`.`plrid` = `ps_plr_data`.`plrid`) and 
(`ps_c_plr_data`.`onlinetime` > (select sql_no_cache `TR_config`.`playtime_threshold` AS `playtime_threshold` 
from `TR_config` limit 1)) and 
(`ps_c_plr_data`.`lasttime` > (unix_timestamp() - (select sql_no_cache `TR_config`.`lastseen` AS `lastseen` 
from `TR_config` limit 1))) and 
(unix_timestamp(`ps_plr_data`.`statdate`) > (unix_timestamp() - (select sql_no_cache `TR_config`.`lastseen` AS `lastseen` 
from `TR_config` limit 1)))) group by `ps_plr`.`plrid` having (max(`ps_plr_data`.`dayskill`) > (select sql_no_cache `TR_config`.`skill_threshold` AS `skill_threshold` 
from `TR_config`))

# MYSQL COMMAND ENDS HERE

############
## Step 3 ##

# This is the query which outputs all players with their teamratio.
# Teamratio algorithm in short is:
#   SUM (<player's ct/t ratio> * <map's ct/t ratio>)
# So basically it's just "player's ct/t * map's ct/t" summed together.
# If either, player's ct/t ratio or map's ct/t ratio, is close to 1
# (player equally playing ct and t, or map is not biased to either team)
# the teamratio doesn't change much.
# Vice versa, if player is playing much ct on ct-biased map he gets much negative teamratio.

SELECT mapdata.ctwon / (mapdata.ctwon + mapdata.terroristwon) as mapctratio, mapdata.terroristwon / (mapdata.ctwon + mapdata.terroristwon) as maptratio, ROUND(mapdata.rounds / maxrounds, 2) AS prcnt,

 mapdef.name AS mapname, SKILLED.name,
 SUM(stats.ctwon + stats.ctlost) AS ctrounds,
 SUM(stats.terroristwon + stats.terroristlost) AS terroristrounds,
 SUM(stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost) AS sumrounds,
 SUM((stats.ctwon + stats.ctlost)) / SUM(( stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost )) AS plrmap_ctratio,
 SUM((stats.terroristwon + stats.terroristlost)) / SUM(( stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost )) AS plrmap_tratio, SKILLED.ctratio AS plroverall_ctratio,
 SKILLED.tratio AS plroverall_tratio,
 SUM(ABS(mapdata.ctwon / (mapdata.ctwon + mapdata.terroristwon) - (mapdata.terroristwon / (mapdata.ctwon + mapdata.terroristwon))) * IF(mapdata.ctwon / (mapdata.ctwon + mapdata.terroristwon) > mapdata.terroristwon / (mapdata.ctwon + mapdata.terroristwon), (stats.terroristwon + stats.terroristlost) / ( stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost ) - (stats.ctwon + stats.ctlost) / ( stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost ), (stats.ctwon + stats.ctlost) / ( stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost ) - (stats.terroristwon + stats.terroristlost) / ( stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost ))) AS teamratio FROM

`SKILLED_PLAYERS` AS SKILLED,
pstats_c_plrmaps_halflife_cstrike AS stats,
( SELECT SKILLED.plrid, AVG( stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost ) AS avg FROM pstats_c_plrmaps_halflife_cstrike AS stats, SKILLED_PLAYERS AS SKILLED WHERE SKILLED.plrid = stats.plrid GROUP BY SKILLED.plrid ) AS rounds,
pstats_c_gamemaps_halflife_cstrike AS mapdata, pstats_defs_maps AS mapdef, ( SELECT SUM(rounds) as maxrounds FROM pstats_c_gamemaps_halflife_cstrike ) AS maxrounds WHERE

(stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost > rounds.avg - 0.5*rounds.avg OR
 stats.ctwon + stats.ctlost + stats.terroristwon + stats.terroristlost > 250) AND
stats.mapid = mapdef.id AND
mapdata.mapid = mapdef.id AND
stats.plrid = SKILLED.plrid AND
rounds.plrid = SKILLED.plrid

GROUP BY SKILLED.plrid

HAVING
sumrounds > 0

ORDER BY teamratio;


#######################
## Step 4 (optional) ##

Set up teamratio.php and teamratio-player.php. Configure username and password in mysql_connect(...) in the code.
Write next line on ../cstrike/cfg/mani_admin_plugin/webshortcutlist.txt

   "teamratio" http://your.server.com/teamratio.php

Now people can see the statistics in-game by writing "teamratio".