<?php

	include "library.php";

	$cxn = start_code( true );

	$_SESSION['validation_error'] = "false";
	$_SESSION['validation_errors'] = "";

	page_header( "", $cxn, "", true, false, "#D9C4AE" );

	echo "<font color='#AAAAAA'>".date( "d M Y  g:ia" )."</font><br>";
	
		/* created on each day */

	$query = "SELECT date(date_created) as date_created2, count(*) as num FROM profile GROUP BY date(date_created) ORDER BY date(date_created)";

 	$result = run_query( $query, $cxn );

	echo "<table>";

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

		if ((time() - strtotime( $data['date_created2'] )) < 21*24*60*60 )
			echo "<tr><td>".$data['date_created2']."</td><td>".$data['num']."</td></tr>";
	}

	echo "</table>";

	echo "<br><br>";

	$data = get_first_row( "SELECT sum(hits) as num FROM hits WHERE hit_date = '".date( "Y-m-d")."'", $cxn );

	echo "Site visitors today: ".$data['num']."<br><br>";

	echo "<table>";

	$data = get_first_row( "SELECT COUNT(*) as num FROM profile WHERE date(date_created) = '".date( "Y-m-d", strtotime( date( "Y-m-d") ) )."'", $cxn );

	echo "<tr><td>New members today</td><td>".$data['num']."</td></tr><br>";


		/* visitors on the site */

	$data = get_first_row( "SELECT count(*) as num FROM online_status WHERE member_id = -1", $cxn );

	echo "<tr><td><br>Visitors on the site</td><td><br>".$data['num']."</td></tr>";

	$data2 = get_first_row( "SELECT count(*) as num FROM online_status WHERE member_id <> -1", $cxn );

	echo "<tr><td><br>Members logged in</td><td><br>".$data2['num']."</td></tr>";


		/* emails in the last 24 hours */

	$query = "SELECT sent FROM message";
 	$result = run_query( $query, $cxn );

	$num = 0;

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

		if ((time() - strtotime( $data['sent'] )) < 1*24*60*60 )
			$num++;
	}

	echo "<tr><td><br>Messages in the last 24 hours</td><td><br>".$num."</td></tr>";


		/* photos uploaded in the last 24 hours */

	$query = "SELECT uploaded_datetime FROM photos";
 	$result = run_query( $query, $cxn );

	$num = 0;

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

		if ((time() - strtotime( $data['uploaded_datetime'] )) < 1*24*60*60 )
			$num++;
	}

	echo "<tr><td><br>Photos uploaded in the last 24 hours</td><td><br>".$num."</td></tr>";


		/* members with photos */

	$query = "SELECT count(*) as num FROM (SELECT DISTINCT member_id FROM photos) a";
 	$result = run_query( $query, $cxn );
	$data = get_row( $result );

	$query2 = "SELECT count(*) as num FROM profile";
 	$result2 = run_query( $query2, $cxn );
	$data2 = get_row( $result2 );

	$prop_with_photos = number_format( $data['num'] / $data2['num'] * 100, 0 );

//	echo "<br><br>Members with photos: ".$prop_with_photos."%";


		/* number of members from latest 20 joined that have verified email addresses */

	$query = "select count(*) as num from (select email_address_valid from profile order by id desc limit 20) x where x.email_address_valid = 'yes'";
 	$result = run_query( $query, $cxn );
	$data = get_row( $result );

	echo "<tr><td><br>Number from latest 20 with verified email addresses</td><td><br>".$data['num']."</td></tr>";


		/* total members */

	$query = "SELECT count(*) as num FROM profile";
 	$result = run_query( $query, $cxn );
	$data = get_row( $result );

	echo "<tr><td><br>Total members</td><td><br>".$data['num']."</td></tr>";

	echo "</table>";

	echo "<br><br><br>";


		/* last 20 members */

	$query = "SELECT country, charging as chargable FROM profile LEFT JOIN static_data ON profile.country = static_data.value ORDER BY profile.id DESC LIMIT 20";
	$result = run_query( $query, $cxn );

	$chargable = 0;

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

		if ($data['chargable'] == "yes")
			$chargable++;
	}

	echo "Last 20 members (chargeable: ".$chargable.")<br><br><br><table>";

	echo "<tr><td><b>Handle<br><br></b></td><td><b>Claimed<br><br></b></td><td><b>IP country<br><br></b></td></tr>";

	$query = "SELECT profile.*, charging as chargable FROM profile LEFT JOIN static_data ON profile.country = static_data.value ORDER BY profile.id DESC LIMIT 20";
	$result = run_query( $query, $cxn );

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

//		echo "<tr><td>".show_handle( $data['id'], $data['handle'], "main", "" )."</td><td>".$data['country']."</td><td>".$data['ip_country']."</td><td>".$data['source']."</td><td>".$data['membership_type']."</td><td>".$data['source_referral']."</td></tr>";
		echo "<tr><td>".show_handle( $data['id'], $data['handle'], "main", "" )."</td><td>".$data['country']."</td><td>".$data['ip_country']."</td><td>".$data['source']."</td><td>".$data['membership_type']."</td></tr>";
	}

	echo "</table><br><br>";


		/* aggregate hits yesterday and today */

	$query = "SELECT source, sum(hits) as xhits, sum(conversions) as xconversions FROM hits WHERE hit_date >= '".date( "Y-m-d", strtotime( date( "Y-m-d") ) - 1*24*60*60 )."' GROUP BY source ORDER BY source";
 	$result = run_query( $query, $cxn );

	echo "Hits yesterday and today<br><br><table>";

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

		if ($data['xhits'] != 0)
			$conv_rate = $data['xconversions'] / $data['xhits'] * 100;
		else
			$conv_rate = 0;

		echo "<tr><td>".$data['source']."</td><td>".sp(4).$data['xconversions']."</td><td>".sp(4).$data['xhits']."</td><td>".sp(4).round( $conv_rate, 1 )." %</td></tr>";
	}

	echo "</table><br><br>";

	
		/* list of countries */

	$query = "SELECT country, count(*) as num FROM profile WHERE deleted = 'no' GROUP BY country ORDER BY country";

 	$result = run_query( $query, $cxn );

	echo "<table>";

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

		echo "<tr><td>".$data['country']."</td><td>".$data['num']."</td></tr>";
	}

	echo "</table><br><br>";


		/* total countries */

	$query = "SELECT count(*) as num FROM (SELECT DISTINCT country FROM profile WHERE deleted = 'no') x";
	$result = run_query( $query, $cxn );
	$data = get_row( $result );

	echo "<table cellpadding='4'>";

	echo "<tr><td><br>Total countries</td><td><br>".$data['num']."</td></tr></table><br><br>";


		/* age distribution */
/*
	echo "Age distribution<br><br>";

	$query = "SELECT handle, year(date_of_birth) as dob_year, dayofyear(date_of_birth) as dob_dayofyear FROM profile WHERE deleted = 'no'";

 	$result = run_query( $query, $cxn );
		
	for ($i=0; $i < 1000; $i += 5)
		$bucket[$i] = 0;

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

		if ((date("z")+1) > $data['dob_dayofyear'])
			$age = date( "Y" ) - $data['dob_year'];
		else
   			$age = date( "Y" ) - $data['dob_year'] - 1;

		if ($age < 20)
			$bucket[0]++;
		else
			$bucket[ $age - $age % 5 ]++;

		if ($age > 100)
			echo "Member ".$data['handle']." has age ".$age."<br>";
	}

	echo "Under 20: ".$bucket[0]."<br>";

	for ($i=20; $i < 100; $i += 5)
	{
		if ($bucket[$i] != 0)
			echo $i." to ".($i + 5).": ".$bucket[$i]."<br>";
	}
*/


		/* hits yesterday and today */

	$query = "SELECT ip_country, source, sum(conversions) as sconversions, sum(hits) as shits FROM hits WHERE hit_date >= '".date( "Y-m-d", strtotime( date( "Y-m-d") ) - 1*24*60*60 )."' GROUP BY ip_country, source ORDER BY ip_country, source";
 	$result = run_query( $query, $cxn );

	echo "Hits yesterday and today<br><br><table>";

	for ($i=0; $i < mysql_num_rows($result); $i++)
	{
		$data = get_row( $result );

		echo "<tr><td>".$data['ip_country']."</td><td>".$data['source']."</td><td>".sp(2).$data['sconversions']."</td><td>".$data['shits']."</td></tr>";
	}

	echo "</table><br><br>";

	echo "<br><br>Finished";
?>
