WhenIsWhere activity graph

[Samtron]

I dont' know, who is responsible for that, but the new activity graph graphics look really nice!! Cool stuff with the new added data in every row I like it!
I'm not the one who should complain, but I miss overall data (overall time, average ratio) at the bottom of the graphics...

For all who don't know what I mean, just take a look here dswp_tools/wheniswhere/month.php?b3_id=10168 or at your stats page.

[natirips]

Oh, and that new graph reminded me that I should go visit an oculist (I can't see those red numbers well, especially zero and eight).

[wurst]

Ah, that was me.
I was just interrupting my work to go with Ana to buy some longer 10mm wall plugs (cause the shorter 8mm failed...), on the way back i deactivated the car...

Ye, i wanna make the colorizing of grapf different, a green/yellow/red for good/average/bad days would be nice.
I wanna make horizontal weekend frames and a vertical "working hours" box, colorize backgrounds and invent the irrevitable unemployment factor.
under the graph, there should be a 365- day ratio graph what shows overall development.

any suggestions?

i think ill continue next weekend, if someone wanna work some on this: gimme a call

[Samtron]

Giving a call!

Before reading your post I had also the idea of green/yellow/red colored days depending on the ratio. I guess it's made with GD or ImageMagick, or a special charting tool? If I can have access to the source files, I can help with that color thing for a start...

[wurst]

GD forever!
Or better: (Citation Blinky) "Malen nach Zahlen"

If u want theres FTP/SFTP and even Samba (using vpn...)

Code: Select all

<?php

//07-2011
//Display GantT Graph for stats....
//############################################################################


$xlr_id = $_GET['b3_id'];
// $xlr_id = $_GET['xlr_id'];



$dbhost='192.168.9.*';
$dbusername='***';
$dbuserpass='***';
$dbname='***';
$dbname2='***';





//SQL
//############################################################################
$db = mysql_connect ($dbhost, $dbusername, $dbuserpass) or die(mysql_error());

mysql_select_db($dbname, $db) or die('Cannot select database');

//Player matched?
$sql_check = "SELECT p.id FROM xlr_playerstats p INNER JOIN phpbb_users pu ON p.id = pu.xlr_id_22222 WHERE p.id = '$xlr_id' LIMIT 0,1";
$query_sql_check = mysql_query($sql_check, $db);
$matched = mysql_num_rows($query_sql_check);

if ($matched == 1) {

$sql22222="SELECT
mk.kills,
mk.deaths,
FORMAT(p.ratio,2) AS avgratio,
FORMAT(mk.kills/mk.deaths,2) as ratio,
FROM_UNIXTIME(came,'%d.%m.%Y') AS ShowDate,
FROM_UNIXTIME(came,'%Y-%m-%d') AS strtodate,
DATEDIFF(NOW(), FROM_UNIXTIME(came, '%Y-%m-%d') ) AS DaysAgo,
(FROM_UNIXTIME(came,'%H')*3600)+(FROM_UNIXTIME(came,'%i')*60)+(FROM_UNIXTIME(came,'%s')) AS IntTimeCame,
(FROM_UNIXTIME(gone,'%H')*3600)+(FROM_UNIXTIME(gone,'%i')*60)+(FROM_UNIXTIME(gone,'%s')) AS IntTimeGone,   
FROM_UNIXTIME(came, '%w') as WeekDay

FROM ctime ct

INNER JOIN clients c ON ct.guid = c.guid
INNER JOIN xlr_playerstats p ON p.client_id = c.id
LEFT OUTER JOIN dswp_matched_tdm_kills mk ON FROM_UNIXTIME(timestamp, '%Y-%m-%d') = FROM_UNIXTIME(came,'%Y-%m-%d') AND mk.xlr_id = p.id

WHERE p.id =  '$xlr_id'
AND DATEDIFF(NOW(), FROM_UNIXTIME(came, '%Y-%m-%d') ) < 30

ORDER BY came DESC";
}

else {
$sql22222="SELECT
p.ratio AS avgratio,
FROM_UNIXTIME(came,'%d.%m.%Y') AS ShowDate,
FROM_UNIXTIME(came,'%Y-%m-%d') AS strtodate,
DATEDIFF(NOW(), FROM_UNIXTIME(came, '%Y-%m-%d') ) AS DaysAgo,
(FROM_UNIXTIME(came,'%H')*3600)+(FROM_UNIXTIME(came,'%i')*60)+(FROM_UNIXTIME(came,'%s')) AS IntTimeCame,
(FROM_UNIXTIME(gone,'%H')*3600)+(FROM_UNIXTIME(gone,'%i')*60)+(FROM_UNIXTIME(gone,'%s')) AS IntTimeGone,   
FROM_UNIXTIME(came, '%w') as WeekDay

FROM ctime ct

INNER JOIN clients c ON ct.guid = c.guid
INNER JOIN xlr_playerstats p ON p.client_id = c.id

WHERE p.id =  '$xlr_id'
AND DATEDIFF(NOW(), FROM_UNIXTIME(came, '%Y-%m-%d') ) < 30

ORDER BY came DESC";

}


$query22222 = mysql_query($sql22222, $db);


// Schriften, Abmessungen
//############################################################################
header ("Content-type: image/png");

$font_file = '../Share-TechMono.ttf';
$font_file2 = '../../dswp_regular.ttf';


$x_offset=100; //X-Offset, der das menü frei hält
$y_offset = 150;

if ($matched == 1) {$x_max=900;} // Maximum width of the graph or horizontal axis
else               {$x_max=800;}

$y_max=800; // Maximum hight of the graph or vertical axis

$im = @imagecreatetruecolor ($x_max, $y_max)
or die ("Cannot Initialize new GD image stream");

//Farben
//############################################################################
$background_color = ImageColorAllocate ($im, 40, 40, 40);
imagefill($im ,0 ,0 , $background_color);
$menubackground_color = ImageColorAllocate ($im, 32, 32, 32);
$text_color = ImageColorAllocate ($im, 0, 102, 153);
$inactive_text_color = ImageColorAllocate ($im, 80, 80, 80);
$day_bg_color = ImageColorAllocateAlpha($im, 70, 70, 70, 64);
$we_bg_color = ImageColorAllocateAlpha($im, 128, 128, 77, 64);



$mumble_color = ImageColorAllocate ($im,10,90,10);
$tdm_color = ImageColorAllocate ($im,70,70,70);
$phpbb_color = ImageColorAllocate ($im,70,90,90);

$graph_color = ImageColorAllocate ($im,80,80,80);
$grid_color = ImageColorAllocate ($im,60,60,60);
$logo_color = ImageColorAllocate ($im,40,50,60);
$now_color = ImageColorAllocate ($im,255,60,60);


//############################################################################
$linespace = 17;
$x1=$x_offset;
$y1=0;
$first_one="yes";
$width_factor = 0.008;

//GRID

imagefilledrectangle($im,0,10,$x_max,$y_offset - 41,$day_bg_color);
imagefilledrectangle($im,0,$y_offset - (2*$linespace) ,$x_max,$y_offset - 15 ,$day_bg_color);

imagefilledrectangle($im,4,15,$x_offset - 10 ,($y_offset + 1000),$day_bg_color);
imagefilledrectangle($im,($x_max - 100),15,($x_max - 72) ,($y_offset + 1000),$day_bg_color);
imagefilledrectangle($im,($x_max - 65),15,($x_max - 38) ,($y_offset + 1000),$day_bg_color);
imagefilledrectangle($im,($x_max - 31),15,($x_max - 4) ,($y_offset + 1000),$day_bg_color);


imagefttext($im, 15, 0, $x_offset , ($y_offset - 51), $inactive_text_color, $font_file,'connection date and time sheet for this user... ');
imagefttext($im, 13, 0, 7 , ($y_offset- $linespace), $text_color, $font_file,'time/date');
imagefttext($im, 13, 90, ($x_max - 82) , ($y_offset- 51), $text_color, $font_file,'ratio');
imagefttext($im, 13, 90, ($x_max - 46) , ($y_offset- 51), $text_color, $font_file,'kills');
imagefttext($im, 13, 90, ($x_max - 13) , ($y_offset- 51), $text_color, $font_file,'detts');

//Vertical
$i=1;
while ($i <= 24) {
$width_bar = 3600 * $width_factor;
if($i &1) {$hour_bg_color = ImageColorAllocateAlpha($im,50,50,50,64); } else {$hour_bg_color = ImageColorAllocateAlpha($im,70,70,70,64);}
imagefilledrectangle($im,$x_offset + ($width_bar * ($i - 1)),$y_offset-(2*$linespace),$x_offset + ($width_bar * $i) ,($y_offset + 1000),$hour_bg_color);
$hour = sprintf("%02d",($i-1));
imagefttext($im, 10, 0, ($x_offset+3) + ($width_bar * ($i - 1)) , ($y_offset - $linespace), $text_color, $font_file,''.$hour.'h');
$i++;
}


//HORIZONTAL
while($nt2=mysql_fetch_array($query22222)){

$line = $nt2[DaysAgo];

if ($first_one=="yes"){
$DaysAgoStart = $nt2[DaysAgo];
$dummylines = $nt2[DaysAgo];
                      }
else {$dummylines = ($line - $last_line - 1);}

//Ersma Inaktive Lücken füllen
while ($dummylines >= 1) {

$date = $nt2[strtodate];
$newdate = strtotime ( "+$dummylines day" , strtotime ( $date ) ) ;
$newdate = date ( 'd.m.Y' , $newdate );

imagefttext($im, 10, 0, ($x_offset-90) , ($y_offset + ($linespace * ($line - $dummylines))), $inactive_text_color, $font_file,$newdate);
$dummylines--;
}
//Ratio
$act_ratio = ($nt2[ratio]);
$avg_ratio = ($nt2[avgratio]);
$rel_ratio = ($act_ratio / $avg_ratio );


//horizontale hintergründe und legende
if ($last_line < $line OR $first_one == "yes" ) {

if ($nt2[WeekDay] == 6 OR $nt2[WeekDay] == 0 ){ //wochenende
imagefilledrectangle($im,0,($y_offset + ($linespace * $line)+2),$x_max,($y_offset + ($linespace * $line)-$linespace +5),$we_bg_color);
}
else { //werktag
imagefilledrectangle($im,0,($y_offset + ($linespace * $line)+2),$x_max,($y_offset + ($linespace * $line)-$linespace +5),$day_bg_color);
}

imagefttext($im, 10, 0, ($x_offset-90) , ($y_offset + ($linespace * $line)), $text_color, $font_file,"$nt2[ShowDate]");
//Color Ratio (Better then normal vs. worse then normal)
$ratio_red = ImageColorAllocate($im, 130, 65, 65);
$ratio_blue = ImageColorAllocate ($im, 0, 102, 153);
$ratio_grey = ImageColorAllocate ($im, 40, 40, 40);

if($rel_ratio == NULL) {$day_ratio_color = $ratio_grey;}
elseif($rel_ratio > 1) {$day_ratio_color = $ratio_red;}
else {$day_ratio_color = $ratio_blue;}

imagefttext($im, 10, 0, ($x_max-100) , ($y_offset + ($linespace * $line)), $day_ratio_color, $font_file,"$nt2[ratio]");
imagefttext($im, 10, 0, ($x_max-65) , ($y_offset + ($linespace * $line)), $ratio_red, $font_file,"$nt2[kills]");
imagefttext($im, 10, 0, ($x_max-30) , ($y_offset + ($linespace * $line)), $ratio_blue, $font_file,"$nt2[deaths]");
}




if ($nt2[IntTimeGone] > $nt2[IntTimeCame]){ // regular, not over midnight
imagefilledrectangle($im,($x_offset + ($nt2[IntTimeCame] * $width_factor)),($y_offset + ($linespace * $line)),($x_offset + ($nt2[IntTimeGone] * $width_factor)),($y_offset + ($linespace * $line)-10),$day_ratio_color);
}
else{ //over midnight, draw a) Came --> 24h b) 00h --> Gone
imagefilledrectangle($im,($x_offset + ($nt2[IntTimeCame] * $width_factor)),($y_offset + ($linespace * $line)),($x_offset + (86400 *$width_factor)),($y_offset + ($linespace * $line)-10),$day_ratio_color);
imagefilledrectangle($im,($x_offset),($y_offset + ($linespace * $line)-( $linespace)),($x_offset + ($nt2[IntTimeGone] *$width_factor)),($y_offset + ($linespace * $line) -10 - ( $linespace)),$day_ratio_color);
    }


// Here starts horizontal month player ratio development :)
$mrd_x_offset = $x_offset;
$mrd_y_offset = 645;
$mrd_y_base = 720;

if ($first_one == "yes"){
imagefilledrectangle($im,0,$mrd_y_offset,$x_max,$y_max + 20,$background_color); //hintergrund unten
imagefilledrectangle($im,4,$mrd_y_offset + 5,$x_max - 5,$y_max - 3,$day_bg_color);
}
/* erinnerung :)
$act_ratio = ($nt2[ratio]);
$avg_ratio = ($nt2[avgratio]);
$rel_ratio = ($act_ratio / $avg_ratio );
*/

imagefilledrectangle($im,$mrd_x_offset + (10 * $line) ,$mrd_y_base,$mrd_x_offset + (10 * $line) + 5,$mrd_y_base + (10 * $act_ratio),$day_ratio_color);


$first_one="no";
$last_line = $line;
}





//datei erzeugen
//ImagePNG ($im,$cache,9,PNG_ALL_FILTERS);

ImagePNG ($im);


//weghauen nich vergessen...
imagedestroy($im);


//datei aus cache ausgeben
//readfile($cache);
//alternativ nochmal machen und ausgeben :)


?>


[Samtron]

Finally I did something:

activity_graph.gif

(69.97 KiB) Downloaded 11952 times

GNUino, I used your data for this image, I hope you don't mind =]

Colors are now computed and interpolated for every day depending on the day's ratio:
average ratio = yellow
best ratio last month = green
worst ratio last month = red

Also new: right aligned values in the table on the right, at the bottom of these columns are the average values, and I added the kills with blue bars on the bottom of the graphic.

Wursti: There is still an error for unmatched players, I'll fix this (division by zero!!!11einself). So please don't make it public right now.

[Samtron]

Okay, I fixed it. You may want to switch the files if you like the small new features.

To get a sneak preview: Go to your stats page and get the image URL and change month.php to month_smt.php:

Code: Select all

http://www.dswp.de/old/dswp_tools/wheniswhere/month.php?b3_id=10168
http://www.dswp.de/old/dswp_tools/wheniswhere/month_smt.php?b3_id=10168

@Wursti: About the matched players - Is it a technical problem, that unmatched players don't have enough data, or is it a "forum"-feature, that only matched players (with forum account) can see more detailed data?

[wurst]

ye, theres no ratio collection for the non-matched.
in fact B3 dont collect daily ratio, thats a little script of blinky.
he goes every night to substract K/D from last days K/D.
thats the reason for the 2 queries (plus the little one that finds wether the player is matched...)

btw. feel free to exchange ur script with the existing one when u think its time.
thx for care...

[SKracht]

hm maybe we can add, on the left side of the stats page, below Team Kills and Team Death, the % of Teamkills/Kills, very easy to implement and maybe it makes ppl work harder not to kill teamates.

the new graphic is wonderfull, i first wondered about the colors but i figured out that it depends on everyones personal ratio, to go red or green. kewl!

[wurst]

or some nice patch medal aka umweltplakette?
like this?

Code: Select all

//Umweltplakette rein :)

$sql_plakette="SELECT
ROUND((p.kills/p.teamkills),2) AS ktkratio
from xlr_playerstats p where p.id = '$xlr_id'
limit 0,1
";

$query_plakette = mysql_query($sql_plakette, $db);
$ktkratio = mysql_fetch_row($query_plakette);

$ktkratio = $ktkratio[0];


if ($ktkratio > 50)      { $plakette = './env4.png'; }
elseif ($ktkratio > 30)  { $plakette = './env3.png'; }
else                      { $plakette = './env2.png'; }

$plakette = imagecreatefrompng($plakette);


imagecopyresampled ( $im , $plakette , ($x_max-110) , 685 , 0 , 0 , 100 , 100 , 1000 , 1000 );
imagefttext($im, 12, 0, ($x_max-85), 753, $black_color, $font_file, $ktkratio );
//imagefttext($im, 13, 90, ($x_max - 140) , 770, $text_color, $font_file,'Kills/');
//imagefttext($im, 13, 90, ($x_max - 120) , 780, $text_color, $font_file,'TeamKills');
imagefttext($im, 7, 0, ($x_max - 70) , 770, $black_color, $font_file,'K/TK');




ah and i added geshi
http://qbnz.com/highlighter/
omg im so busy