[Поиск] csstats mysql

/* AMX Mod X script
*	sqlStats
*	by ripcurl@NeverStrike (neverstrike@gmail.com)
*		with a lot of thanx to AMXMODX forums and all scripts i had to look through to get this simple script working :)
*		thanx to:
*			 OLO (csstats),
*			 BAILOPAN,
*			 and everybody I left out.
*
*	This uses csstats to get stats into a (my)sql database.
*	tested only on mysql 4.0.xx
*	stats saved on client disconnect
*	requires csstats, now csx and sql modules enabled!
*
*	add cvar to sql.cfg (it works with defaults if no entries are found):
*		amx_sqlstats_table "somecooltable1"
*		amx_sqlstats_weekly "somecooltable3"
*		amx_sqlstats_monthly "somecooltable4"
*		amx_sqlstats_yearly "somecooltable5"
*		amx_sqlstats_top15_dayly "somecooltable6"
*		amx_sqlstats_top15_weekly "somecooltable7
*		amx_sqlstats_top15_monthly "somecooltable8
*		amx_sqlstats_top15_yearly "somecooltable9
*
*	How can I make my website with this?
*	-> the sql tables:
*	the weekly/montly/yearly tables are current(more or less live) ranks information
*	dayly resets after 1 day, information is copied to weekly
*	weekly resets after 1 week, information is copied to monthly
*	monthly resets after 1 month, information is copied to yearly
*	yearly... stays yearly :)
*	->the top15 tables will contain entries with date to order by, and the score is ordered, so you can query db per 15 players or per date
*
*	known bugs:
*			timestamp may be wrong, on mysql(linux) it works
*			efficiency, accuracy, accuracyHS are all calculated as integers, DB contains doubles(19,2)
*			BUFFER_SIZE(default 500) may be adjusted for heavily used servers (increase if nescesary)
*
*	changelog sqlstats
*	0.1.6:	merged sqlstats_history (not released plugin)
*	0.1.5:	fixed names garbled (type mismatch)
*	0.1.4:	changed variable names as queries were becoming way too long (compiler problem)
*			added a sql query buffer, saves a bit of computing time at the expense of memory
*			fixed?: not working on lan games (now detecting through cvar sv_lan) untested
*			traced some basic problems and bugs
*	0.1.3:	added columns score, accuracy, accuracyHS, efficiency. sort by score to get the ranking
*			updated for amxmodx 0.20RC7 (require_module csx)
*	0.1.2:	fixed not writing to db
*	0.1.1:	bug: db access via cvars fixed
*	0.1.0:	first night of coding :)
*			it actually works, though hardcoded vars
*			bug: db access via cvars, cvars not set when entering plugin_cfg

*	changelog sqlstats_history
*	0.1.2: added support for sql_bannedids.sma (uncomment //#define SQL_BANNEDIDS	1 further down)
*	0.1.1: added timing for shifting dbtables
*	0.1.0: took sqlstats.sma (0.1.5) as codebase
*/

#include <amxmodx>
#include <csstats>
#include <dbi>

#include <amxmisc>

#define DEBUG			1
#define MAX_PLAYERS		32 + 1
#define MAX_NAME_LENGTH 31
#define MAX_TEXT_LENGTH	255
#define BUFFER_SIZE 	500

//the hour when stats will be processed
#define RESET_HOUR	"06"
//comment if sql_bannedids is not present (else sql server will return error, not major :)
#define SQL_BANNEDIDS	1

#define S_KILLS             0 //csstats,csx
#define S_DEATHS            1 //csstats,csx
#define S_HS                2 //csstats,csx
#define S_TK				3 //csstats,csx
#define S_SHOTS             4 //csstats,csx
#define S_HITS              5 //csstats,csx
#define S_TIME				6 //custom
#define S_SCORE				7 //custom
#define S_EFF				8 //custom
#define S_ACC				9 //custom
#define S_ACCHS				10 // custom
#define S_SIZE				11 // array size

//stats vars
new g_iPStats[BUFFER_SIZE][S_SIZE] //BUFFER_SIZE: buffer sql queries, saves query info in memory till end of map
new g_sPStatsIds[BUFFER_SIZE][MAX_NAME_LENGTH+1] // authids
new g_sPStatsNames[BUFFER_SIZE][MAX_NAME_LENGTH+1] // names
new g_iMutationcount //tracking stats mutations

//sql vars
new g_host[MAX_NAME_LENGTH+1], g_user[MAX_NAME_LENGTH+1], g_pass[MAX_NAME_LENGTH+1], g_dbname[MAX_NAME_LENGTH+1], g_error[MAX_NAME_LENGTH+1]
new Sql:g_dbc
new Result:g_result

//sqltable vars
new g_sqlstats_table[MAX_NAME_LENGTH+1], g_iStats[8], g_iBodyHits[8]
new g_sqlstats_weekly[MAX_NAME_LENGTH+1]
new g_sqlstats_monthly[MAX_NAME_LENGTH+1]
new g_sqlstats_yearly[MAX_NAME_LENGTH+1]
new g_sqlbannedIDs[MAX_NAME_LENGTH+1]

//top15sqltable vars
new g_sqlstats_top15_dayly[MAX_NAME_LENGTH+1]
new g_sqlstats_top15_weekly[MAX_NAME_LENGTH+1]
new g_sqlstats_top15_monthly[MAX_NAME_LENGTH+1]
new g_sqlstats_top15_yearly[MAX_NAME_LENGTH+1]

//time booleans
new bool:bYearPassed=false
new bool:bMonthPassed=false
new bool:bWeekPassed=false
new bool:bDayPassed=false
new g_iWeekday

//file vars
new g_date_file[MAX_TEXT_LENGTH+1]

new g_tmpsv_lan = 0 // set to 1 to force logging by IP instead of STEAM_ID


/* PLUGIN INIT CALLS */

// plugin init
public plugin_init()
{
	//log_amx("[sqlStats] plugin_init")

	register_plugin("sqlStats","0.1.6","NeverStrike")
	register_cvar("amx_sqlstats_table", "sqlstatstable",0)
	register_cvar("amx_sqlstats_weekly", "sqlstats_c_weekly", 0)
	register_cvar("amx_sqlstats_monthly", "sqlstats_c_monthly", 0)
	register_cvar("amx_sqlstats_yearly", "sqlstats_c_yearly", 0)

	// top15 for every day/week/month
	register_cvar("amx_sqlstats_top15_dayly", "sqlstats_t15_dayly", 0)
	register_cvar("amx_sqlstats_top15_weekly", "sqlstats_t15_weekly", 0)
	register_cvar("amx_sqlstats_top15_monthly", "sqlstats_t15_monthly", 0)
	register_cvar("amx_sqlstats_top15_yearly", "sqlstats_t15_yearly", 0)

	g_iWeekday = 0
	g_iMutationcount = 0

	//zero initialize values
	for (new i = 0; i < BUFFER_SIZE; i++)
	{
		g_sPStatsIds[i] = ""
		for (new j = 0; j < S_SIZE; j++)
		{
			g_iPStats[i][j] = 0;
		}
	}
	
	new dataDir[64]
	get_datadir(dataDir, 63)
	format (g_date_file,MAX_TEXT_LENGTH, "%s/date.txt", dataDir)
	
	if (! file_exists(g_date_file) )
	{
		write_date_file()

		new sWeekday[2]
		num_to_str(g_iWeekday,sWeekday,2)
		write_file(g_date_file,sWeekday,2)
	}
	
	//reload sql.cfg since some bug won't load the cvars listed above
	new configsDir[64]
	get_configsdir(configsDir, 63)
	server_cmd("exec %s/sql.cfg", configsDir)
	server_exec()
	
	return PLUGIN_CONTINUE
}

// bug in amxmodx prevents this from executing correctly
public plugin_cfg()
{
	// Fetch the database info stated in sql.cfg
	get_cvar_string("amx_sql_host",g_host,MAX_NAME_LENGTH)
	get_cvar_string("amx_sql_user",g_user,MAX_NAME_LENGTH)
	get_cvar_string("amx_sql_pass",g_pass,MAX_NAME_LENGTH)
	get_cvar_string("amx_sql_db",g_dbname,MAX_NAME_LENGTH)
	
	// get sql table vars	
	get_cvar_string("amx_sqlstats_table", g_sqlstats_table, MAX_NAME_LENGTH)
	get_cvar_string("amx_sqlstats_weekly", g_sqlstats_weekly, MAX_NAME_LENGTH)
	get_cvar_string("amx_sqlstats_monthly", g_sqlstats_monthly, MAX_NAME_LENGTH)
	get_cvar_string("amx_sqlstats_yearly", g_sqlstats_yearly, MAX_NAME_LENGTH)
	
	#if defined SQL_BANNEDIDS
	get_cvar_string("amx_sqlbannedids_table", g_sqlbannedIDs, MAX_NAME_LENGTH)
	#endif
	
	// get top15 sql table vars	
	get_cvar_string("amx_sqlstats_top15_dayly", g_sqlstats_top15_dayly, MAX_NAME_LENGTH)
	get_cvar_string("amx_sqlstats_top15_weekly", g_sqlstats_top15_weekly, MAX_NAME_LENGTH)
	get_cvar_string("amx_sqlstats_top15_monthly", g_sqlstats_top15_monthly, MAX_NAME_LENGTH)
	get_cvar_string("amx_sqlstats_top15_yearly", g_sqlstats_top15_yearly, MAX_NAME_LENGTH)

	sql_init_stuff()
	
	return PLUGIN_CONTINUE
}

//required modules
public plugin_modules()
{
	//log_amx("[sqlStats] plugin_modules")
	
	//require_module("csstats") //amxmodx <0.20 RC7
	require_module("csx") // amxmodx >=0.20 RC7
	require_module("sql")
}

public plugin_end()
{
	//log_amx("[sqlStats] plugin_end")
	//log_amx("[sqlStats] plugin_end mutationcount: %i", g_iMutationcount)
	for (new id = 1; id < MAX_PLAYERS; id++)
		gets_user_stats(id)

	save_user_stats()

	#if defined SQL_BANNEDIDS
	remove_bannedids()
	#endif

	check_date()
	
	if (bDayPassed || bWeekPassed || bMonthPassed || bYearPassed)
	{
		g_dbc = dbi_connect(g_host, g_user, g_pass, g_dbname, g_error, MAX_NAME_LENGTH)
		if (g_dbc == SQL_FAILED)
			log_amx("[sqlStats-history] SQL Connection Failed")
		else
		{
			if (bDayPassed)
			{
				//shift dayly stats to weekly stats (1 day has passed)
				shifttable(g_sqlstats_table,g_sqlstats_weekly)
				save_top15(g_sqlstats_table, g_sqlstats_top15_dayly)
				reset_stats() // reset the dayly stats
				reset_table(g_sqlstats_table)
				g_iWeekday+=1
				new sWeekday[2]
				num_to_str(g_iWeekday,sWeekday,2)
				write_file(g_date_file,sWeekday,2)
			}
			if (bWeekPassed)
			{
				//shift dayly stats to monthly stats (1 week has passed)
				shifttable(g_sqlstats_weekly, g_sqlstats_monthly)
				save_top15(g_sqlstats_weekly, g_sqlstats_top15_weekly)
				reset_table(g_sqlstats_weekly)
				g_iWeekday = 0
				write_file(g_date_file,"0",2)
			}
			if (bMonthPassed)
			{
				//shift monthly stats to yearly stats (1 month has passed)
				shifttable(g_sqlstats_monthly,g_sqlstats_yearly)
				save_top15(g_sqlstats_monthly, g_sqlstats_top15_monthly)
				reset_table(g_sqlstats_weekly)
			}
			if (bYearPassed)
			{
				save_top15(g_sqlstats_yearly, g_sqlstats_top15_yearly)
				reset_table(g_sqlstats_yearly)
			}
		}
		dbi_close(g_dbc)
		write_date_file()
	}
	
	return PLUGIN_CONTINUE
}

/* EVENTS */

// when a player disconnects save his stats
public client_disconnect(id)
{
	//log_amx("[sqlStats] client_disconnect")
	
	gets_user_stats(id) //save to buffer
	
	return PLUGIN_CONTINUE
}


/* PLUGIN FUNCTIONS */

// establish a connection with db
// create table if not present
public sql_init_stuff()
{
	//log_amx("[sqlStats] sql_init_stuff")
	
	//debug
	//log_amx("[sqlStats] sql_init_stuff h:%s, u:%s, p:%s, db:%s, t:%s",g_host,g_user,g_pass,g_dbname,g_sqlstats_table)
	
	g_dbc = dbi_connect(g_host,g_user,g_pass,g_dbname,g_error,MAX_NAME_LENGTH)
	if (g_dbc == SQL_FAILED)
		log_amx("[sqlStats] SQL Connection Failed")
	else
	{
		dbi_query(g_dbc, "CREATE TABLE IF NOT EXISTS `%s` (`authid` VARCHAR( 32 ) NOT NULL, `name` VARCHAR( 32 ) NOT NULL , `date` TIMESTAMP( 10 ) NOT NULL , `score` INT NOT NULL ,`kills` INT NOT NULL , `teamkills` INT NOT NULL, `deaths` INT NOT NULL , `hits` INT NOT NULL , `shots` INT NOT NULL , `headshots` INT NOT NULL , `efficiency` DOUBLE (19,2) DEFAULT NULL, `accuracy` double(19,2) default NULL,`accuracyHS` double(19,2) default NULL, PRIMARY KEY(`authid`))", g_sqlstats_table)
		dbi_query(g_dbc, "CREATE TABLE IF NOT EXISTS `%s` (`authid` VARCHAR( 32 ) NOT NULL, `name` VARCHAR( 32 ) NOT NULL , `date` TIMESTAMP( 10 ) NOT NULL , `score` INT NOT NULL ,`kills` INT NOT NULL , `teamkills` INT NOT NULL, `deaths` INT NOT NULL , `hits` INT NOT NULL , `shots` INT NOT NULL , `headshots` INT NOT NULL , `efficiency` DOUBLE (19,2) DEFAULT 0.0, `accuracy` double(19,2) default 0.0,`accuracyHS` double(19,2) default 0.0, PRIMARY KEY(`authid`))", g_sqlstats_weekly)
		dbi_query(g_dbc, "CREATE TABLE IF NOT EXISTS `%s` (`authid` VARCHAR( 32 ) NOT NULL, `name` VARCHAR( 32 ) NOT NULL , `date` TIMESTAMP( 10 ) NOT NULL , `score` INT NOT NULL ,`kills` INT NOT NULL , `teamkills` INT NOT NULL, `deaths` INT NOT NULL , `hits` INT NOT NULL , `shots` INT NOT NULL , `headshots` INT NOT NULL , `efficiency` DOUBLE (19,2) DEFAULT 0.0, `accuracy` double(19,2) default 0.0,`accuracyHS` double(19,2) default 0.0, PRIMARY KEY(`authid`))", g_sqlstats_monthly)
		dbi_query(g_dbc, "CREATE TABLE IF NOT EXISTS `%s` (`authid` VARCHAR( 32 ) NOT NULL, `name` VARCHAR( 32 ) NOT NULL , `date` TIMESTAMP( 10 ) NOT NULL , `score` INT NOT NULL ,`kills` INT NOT NULL , `teamkills` INT NOT NULL, `deaths` INT NOT NULL , `hits` INT NOT NULL , `shots` INT NOT NULL , `headshots` INT NOT NULL , `efficiency` DOUBLE (19,2) DEFAULT 0.0, `accuracy` double(19,2) default 0.0,`accuracyHS` double(19,2) default 0.0, PRIMARY KEY(`authid`))", g_sqlstats_yearly)
		
		dbi_query(g_dbc, "CREATE TABLE IF NOT EXISTS `%s` (`id` INT NOT NULL AUTO_INCREMENT,`authid` VARCHAR( 32 ) NOT NULL,`name` VARCHAR( 32 ) NOT NULL,`date` TIMESTAMP( 6 ) NOT NULL,`score` INT NOT NULL,`kills` INT NOT NULL,`teamkills` INT NOT NULL,`deaths` INT NOT NULL,`hits` INT NOT NULL,`shots` INT NOT NULL,`headshots` INT NOT NULL,`efficiency` DOUBLE (19,2) DEFAULT 0.0,`accuracy` double(19,2) default 0.0,`accuracyHS` double(19,2) default 0.0,PRIMARY KEY(`id`))", g_sqlstats_top15_dayly)
		dbi_query(g_dbc, "CREATE TABLE IF NOT EXISTS `%s` (`id` INT NOT NULL AUTO_INCREMENT,`authid` VARCHAR( 32 ) NOT NULL,`name` VARCHAR( 32 ) NOT NULL,`date` TIMESTAMP( 6 ) NOT NULL,`score` INT NOT NULL,`kills` INT NOT NULL,`teamkills` INT NOT NULL,`deaths` INT NOT NULL,`hits` INT NOT NULL,`shots` INT NOT NULL,`headshots` INT NOT NULL,`efficiency` DOUBLE (19,2) DEFAULT 0.0,`accuracy` double(19,2) default 0.0,`accuracyHS` double(19,2) default 0.0,PRIMARY KEY(`id`))", g_sqlstats_top15_weekly)
		dbi_query(g_dbc, "CREATE TABLE IF NOT EXISTS `%s` (`id` INT NOT NULL AUTO_INCREMENT,`authid` VARCHAR( 32 ) NOT NULL,`name` VARCHAR( 32 ) NOT NULL,`date` TIMESTAMP( 4 ) NOT NULL,`score` INT NOT NULL,`kills` INT NOT NULL,`teamkills` INT NOT NULL,`deaths` INT NOT NULL,`hits` INT NOT NULL,`shots` INT NOT NULL,`headshots` INT NOT NULL,`efficiency` DOUBLE (19,2) DEFAULT 0.0,`accuracy` double(19,2) default 0.0,`accuracyHS` double(19,2) default 0.0,PRIMARY KEY(`id`))", g_sqlstats_top15_monthly)
		dbi_query(g_dbc, "CREATE TABLE IF NOT EXISTS `%s` (`id` INT NOT NULL AUTO_INCREMENT,`authid` VARCHAR( 32 ) NOT NULL,`name` VARCHAR( 32 ) NOT NULL,`date` TIMESTAMP( 2 ) NOT NULL,`score` INT NOT NULL,`kills` INT NOT NULL,`teamkills` INT NOT NULL,`deaths` INT NOT NULL,`hits` INT NOT NULL,`shots` INT NOT NULL,`headshots` INT NOT NULL,`efficiency` DOUBLE (19,2) DEFAULT 0.0,`accuracy` double(19,2) default 0.0,`accuracyHS` double(19,2) default 0.0,PRIMARY KEY(`id`))", g_sqlstats_top15_yearly)
	}

	dbi_close(g_dbc)
	return PLUGIN_CONTINUE
}

// saves user info to sql db:
// steamid, name, kills, deaths, hits, shots, headshots, +playtime
save_user_stats()
{
	//log_amx("[sqlStats] save_user_stats")
	log_amx("[sqlStats] Saving %i Players", g_iMutationcount)
	
	// sql connect
	g_dbc = dbi_connect(g_host,g_user,g_pass,g_dbname,g_error,MAX_NAME_LENGTH)
	
	if (g_dbc == SQL_FAILED)
		log_amx("[sqlStats] SQL Connection Failed")
	else{
		// process all queries
		for (new i = 0; i < g_iMutationcount; i++)
		{
			// for mysql 4.1.0 use INSERT INTO .... ON DUPLICATE KEY UPDATE, for now, this ugly stuff will do
			if ( !dbi_query(g_dbc,"UPDATE `%s` SET name='%s',date=NOW(),score=%i,kills=%i,teamkills=%i,deaths=%i,hits=%i,shots=%i,headshots=%i,efficiency=%i,accuracy=%i, accuracyHS=%i WHERE authid='%s'", g_sqlstats_table, g_sPStatsNames[i], g_iPStats[i][S_SCORE], g_iPStats[i][S_KILLS], g_iPStats[i][S_TK], g_iPStats[i][S_DEATHS], g_iPStats[i][S_HITS], g_iPStats[i][S_SHOTS], g_iPStats[i][S_HS], g_iPStats[i][S_EFF], g_iPStats[i][S_ACC], g_iPStats[i][S_ACCHS], g_sPStatsIds[i]) )
				dbi_query(g_dbc,"INSERT INTO `%s` (authid, name, date,score,kills,teamkills,deaths,hits,shots,headshots,efficiency,accuracy,accur
acyHS) values('%s','%s',NOW(),%i,%i,%i,%i,%i,%i,%i,%i,%i,%i)",g_sqlstats_table,g_sPStatsIds[i],g_sPStatsNames[i],g_iPStats[i][S_SCORE],g
_iPStats[i][S_KILLS],g_iPStats[i][S_TK],g_iPStats[i][S_DEATHS],g_iPStats[i][S_HI
T
S], g_iPStats[i][S_SHOTS], g_iPStats[i][S_HS], g_iPStats[i][S_EFF], g_iPStats[i][S_ACC], g_iPStats[i][S_ACCHS])
			//log_amx("[sqlStats]: saved %s, %s",g_sPStatsNames[i], g_sPStatsIds[i])
		}
	}
	// sql close	
	dbi_close(g_dbc)
	
	//reset mutationcount
	g_iMutationcount = 0
}

// gets the stats for a player
gets_user_stats(iPlayer)
{
	new bad_id[5] = "hltv"
	new bad_id2[18] = "VALVE_ID_LOOPBACK"
	new sAuthid[MAX_NAME_LENGTH+1]

	get_user_authid(iPlayer, sAuthid, MAX_NAME_LENGTH)

 	if ( is_user_connected(iPlayer) && contain( sAuthid, bad_id) == -1 && contain( sAuthid, bad_id2) == -1 )
 	{
		//if we're on LAN save by IP, else save by steam_id
		if ( g_tmpsv_lan == 1 )
			get_user_ip(iPlayer, g_sPStatsIds[g_iMutationcount], MAX_NAME_LENGTH, 1)
		else{
			get_user_authid(iPlayer, g_sPStatsIds[g_iMutationcount], MAX_NAME_LENGTH)
		}
		get_user_name(iPlayer, g_sPStatsNames[g_iMutationcount], MAX_NAME_LENGTH)
		get_user_stats( iPlayer, g_iStats, g_iBodyHits )
		
		log_amx("[sqlStats] gets_user_stats %s, %s",g_sPStatsNames[g_iMutationcount], g_sPStatsIds[g_iMutationcount])
		
		g_iPStats[g_iMutationcount][S_KILLS] = g_iStats[S_KILLS]		// kills
		g_iPStats[g_iMutationcount][S_DEATHS] = g_iStats[S_DEATHS]		// deaths
		g_iPStats[g_iMutationcount][S_HS] = g_iStats[S_HS]				// headshots
		g_iPStats[g_iMutationcount][S_TK] = g_iStats[S_TK]				// teamkills
		g_iPStats[g_iMutationcount][S_SHOTS] = g_iStats[S_SHOTS]		// shots
		g_iPStats[g_iMutationcount][S_HITS] = g_iStats[S_HITS]			// hits
		//g_iPStats[g_iMutationcount][S_TIME] = get_user_time(iPlayer)	// playing time // dropped query lines are getting too big for the compiler
	
		g_iPStats[g_iMutationcount][S_SCORE] = g_iStats[S_KILLS] - g_iStats[S_DEATHS] - g_iStats[S_TK]
		
		//FIXME: maybe convert these into floats? now done in integers, db already works with floats (double(19,2))
		g_iPStats[g_iMutationcount][S_EFF] = g_iStats[S_KILLS]*100 / (g_iStats[S_KILLS]+g_iStats[S_DEATHS])
		g_iPStats[g_iMutationcount][S_ACC] = g_iStats[S_HITS]*100 / g_iStats[S_SHOTS]
		g_iPStats[g_iMutationcount][S_ACCHS] = g_iStats[S_HS]*100 / g_iStats[S_SHOTS]
	
		//one stats mutation added
		if (g_iMutationcount < BUFFER_SIZE )
			g_iMutationcount++
		else
			log_amx("[sqlStats] g_iMutationcount overflow, increase BUFFER_SIZE") // your server can be very busy :)
			
	}
}

//requires connection established
reset_table(table[])
{
	dbi_query(g_dbc, "TRUNCATE TABLE `%s`", table);
}

reset_stats()
{
	server_cmd("amx_cvar csstats_reset 1")
	server_exec()
}

//shifts tables to other tables e.g. dayly scores into weekly scores, or weekly scores into montly scores
// insert if entry does not exist, adding(+) values if entry exists
//requires connection established
shifttable(src[], dst[])
{
	//log_amx("[sqlStats-history] shifttable: %s -> %s", src, dst)

	dbi_query(g_dbc,"UPDATE `%s`,`%s` SET `%s`.kills=`%s`.kills+`%s`.kills,`%s`.score=`%s`.score+`%s`.score,`%s`.teamkills=`%s`.teamkills+`%s`.teamkills,`%s`.deaths=`%s`.deaths+`%s`.deaths,`%s`.hits=`%s`.hits+`%s`.hits,`%s`.shots=`%s`.shots+`%s`.shots,`%s`.headshots=`%s`.headshots+`%s`.headshots where `%s`.authid=`%s`.authid",dst,src,dst,dst,src,dst,dst,src,dst,dst,src,dst,dst,src,dst,dst,src,dst,ds
t,src,dst,dst,src,dst,src)

	new sName[MAX_NAME_LENGTH+1],sAuthid[MAX_NAME_LENGTH+1]
	new iKills,iDeaths,iHS,iTK,iShots,iHits,iScore,iEff,iAcc,iAccHS

	g_result = dbi_query(g_dbc,"SELECT `%s`.* FROM `%s` LEFT JOIN `%s` ON `%s`.authid=`%s`.authid WHERE `%s`.authid IS NULL",src,src,dst,src,dst,dst)
	while (dbi_nextrow(g_result) > 0 )
	{
		sName = ""
		sAuthid = ""
		dbi_result(g_result,"name",sName,31)
		dbi_result(g_result,"authid",sAuthid,31)
		iKills = dbi_result(g_result,"kills")
		iDeaths = dbi_result(g_result,"deaths")
		iHS = dbi_result(g_result,"headshots")
		iTK = dbi_result(g_result,"teamkills")
		iShots = dbi_result(g_result,"shots")
		iHits = dbi_result(g_result,"hits")
		iScore = dbi_result(g_result,"score")
		iEff = dbi_result(g_result,"efficiency")
		iAcc = dbi_result(g_result,"accuracy")
		iAccHS = dbi_result(g_result,"accuracyHS")
		
		dbi_query(g_dbc,"INSERT INTO `%s` (authid,name,date,score,kills,teamkills,deaths,hits,shots,headshots,efficiency,a
ccuracy,accuracyHS) values('%s','%s',NOW(),%i,%i,%i,%i,%i,%i,%i,%i,%i,%i)", dst, sAuthid, sName, iScore, iKills, iTK, iDeaths, iHits, iShots, iHS, iEff, iAcc, iAccHS)
		//server_print("INSERT INTO `%s` (authid,name,date,score,kills,teamkills,deaths,hits,shots,headshots,efficiency,a
ccuracy,accuracyHS) values('%s','%s',NOW(),%i,%i,%i,%i,%i,%i,%i,%i,%i,%i)", dst, sAuthid, sName, iScore, iKills, iTK, iDeaths, iHits, iShots, iHS, iEff, iAcc, iAccHS)
	}
	dbi_free_result(g_result)
}

//saves the top15 from active table to top15 table
//requires connection established
save_top15(src[],dst[])
{
	//log_amx("[sqlStats-history] shifttable: %s -> %s", src, dst)
	
	new sName[MAX_NAME_LENGTH+1],sAuthid[MAX_NAME_LENGTH+1]
	new iKills,iDeaths,iHS,iTK,iShots,iHits,iScore,iEff,iAcc,iAccHS
	
	g_result = dbi_query(g_dbc,"SELECT * FROM `%s` ORDER BY score DESC LIMIT 15",src)
	while (dbi_nextrow(g_result) > 0 )
	{
		dbi_result(g_result,"name",sName,31)
		dbi_result(g_result,"authid",sAuthid,31)
		iKills = dbi_result(g_result,"kills")
		iDeaths = dbi_result(g_result,"deaths")
		iHS = dbi_result(g_result,"headshots")
		iTK = dbi_result(g_result,"teamkills")
		iShots = dbi_result(g_result,"shots")
		iHits = dbi_result(g_result,"hits")
		iScore = dbi_result(g_result,"score")
		iEff = dbi_result(g_result,"efficiency")
		iAcc = dbi_result(g_result,"accuracy")
		iAccHS = dbi_result(g_result,"accuracyHS")

		dbi_query(g_dbc,"INSERT INTO `%s` (authid,name,date,score,kills,teamkills,deaths,hits,shots,headshots,efficiency,a
ccuracy,accuracyHS) values('%s','%s',NOW(),%i,%i,%i,%i,%i,%i,%i,%i,%i,%i)", dst, sAuthid, sName, iScore, iKills, iTK, iDeaths, iHits, iShots, iHS, iEff, iAcc, iAccHS)
	}
}

//write the current-date to file, for future reference
write_date_file()
{
	new CurrentTime[17]
	get_time("%Y %m %d %H:%M",CurrentTime,16)
	write_file (g_date_file,"//generated by sqlstats, do NOT modify",0)
	write_file(g_date_file,CurrentTime,1)
}

//check if last current-date & save-date has mutated day/week/month/year
check_date()
{
	new oldTime[17],oldWeekday[2],len
	new sNewDatefragment[6]
	
	read_file(g_date_file,1,oldTime,16,len)

	get_time("%Y ",sNewDatefragment,5)
	if (contain(oldTime,sNewDatefragment) == -1)
	{
		bYearPassed=true
		//log_amx("[sqlStats-history] YearPassed=true")
	}	

	get_time(" %m ",sNewDatefragment,4)
	if (contain(oldTime,sNewDatefragment) == -1)
	{
		bMonthPassed=true
		//log_amx("[sqlStats-history] MonthPassed=true")
	}	

	read_file(g_date_file,2,oldWeekday,2,len)	
	g_iWeekday = str_to_num(oldWeekday)
	if (g_iWeekday > 6 )
	{
		bWeekPassed=true
		//log_amx("[sqlStats-history] WeekPassed=true")
	}
		
	get_time(" %d ",sNewDatefragment,4)
	if (contain(oldTime,sNewDatefragment) == -1)
	{
		//log_amx("[sqlStats-history] DayPassed=true")
		get_time("%H",sNewDatefragment,4)
		if (contain(sNewDatefragment,RESET_HOUR) != -1)
		{
			bDayPassed=true
			//log_amx("[sqlStats-history] HourPassed=true")
		}
	}
}

//purge bannedids from stats
remove_bannedids()
{
	//log_amx("[sqlstats] remove_bannedids")
	
	new sAuthid[MAX_NAME_LENGTH+1]

	g_dbc = dbi_connect(g_host, g_user, g_pass, g_dbname, g_error, MAX_NAME_LENGTH)
	if (g_dbc == SQL_FAILED)
		log_amx("[sqlStats-history] SQL Connection Failed")
	else
	{
		g_result = dbi_query(g_dbc,"SELECT * FROM `%s`", g_sqlbannedIDs)
		while (dbi_nextrow(g_result) > 0 )
		{
			dbi_result(g_result,"authid",sAuthid,31)
			dbi_query(g_dbc, "DELETE FROM `%s` WHERE authid = '%s'", g_sqlstats_table, sAuthid)
			//server_print("[sqlstats] DELETE FROM `%s` WHERE authid = '%s'", g_sqlstats_table, sAuthid)
		}
	}
	dbi_close(g_dbc)
}