<?php

	include "library.php";

	$cxn = start_code( true );
	
	$cxn_common = db_common_login();
	

	$_SESSION['sitename'] = "International Businesses For Sale";

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

	page_header( "", $cxn, $cxn_common, "", 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 account GROUP BY date(date_created) ORDER BY date(date_created)";

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

	echo "Accounts<br><br>";
	
	echo "<table>";

	for ($i=0; $i < query_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>";

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

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

	echo "Listings<br><br>";
	
	echo "<table>";

	for ($i=0; $i < query_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>";


	$query = "SELECT DISTINCT ip_address FROM hits WHERE hit_date = '".date( "Y-m-d")."'";

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

	echo "<br><br>Site visitors today: ".query_num_rows( $result )."<br><br>";

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

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

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

	echo "Searches: ".$data['num']."<br><br>";

	echo "<table>";

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

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

	echo "<tr><td>&nbsp;</td></tr>";

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

	echo "<tr><td>New listings 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 );
//
//      $num = $data['num'] - 1
//
//	echo "<tr><td><br>Visitors on the site</td><td><br>".$num."</td></tr>";
//
//	$data2 = get_first_row( "SELECT count(*) as num FROM online_status WHERE member_id <> -1", $cxn );
//
//      $num = $data2['num'];
//
//	echo "<tr><td><br>Members logged in</td><td><br>".$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 < query_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 < query_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 listing_id FROM photos) a";
//	$result = run_query( $query, $cxn );
//	$data = get_row( $result );

//	$query2 = "SELECT count(*) as num FROM account";
//	$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 accounts from latest 20 joined that have verified email addresses */

	$query = "select count(*) as num from (select email_address_verified from account order by id desc limit 20) x where x.email_address_verified = '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 accounts */

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

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


		/* total listings */

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

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

	echo "</table>";

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


	echo "<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 listing.*, account.ip_country FROM listing LEFT JOIN static_data ON listing.country = static_data.value INNER JOIN account ON account.id = listing.account_id ORDER BY listing.id DESC LIMIT 20";
	
	$result = run_query( $query, $cxn );

	for ($i=0; $i < query_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_listing_heading( $data['id'], $data['headline_title'], "main", "" )."</td><td>".$data['country']."</td><td>".$data['ip_country']."</td></tr>";
	}

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


		/* aggregate hits 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";
	$query = "SELECT source, sum(hits) as xhits, sum(conversions) as xconversions FROM hits WHERE hit_date = '".date( "Y-m-d" )."' GROUP BY source ORDER BY source";	
 	$result = run_query( $query, $cxn );

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

	for ($i=0; $i < query_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>&nbsp;&nbsp;&nbsp;&nbsp;".$data['xconversions']."</td><td>&nbsp;&nbsp;&nbsp;&nbsp;".$data['xhits']."</td><td>&nbsp;&nbsp;&nbsp;&nbsp;".round( $conv_rate, 1 )." %</td></tr>";
	}

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


	echo "Number of accounts, split by country<br><br>";

	
		/* list of countries */

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

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

	echo "<table>";

	for ($i=0; $i < query_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 account 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>";



		/* hitstoday */

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

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

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

		echo "<tr><td>".$data['ip_country']."</td><td>".$data['source']."</td><td>&nbsp;&nbsp;".$data['sconversions']."</td><td>".$data['shits']."</td></tr>";
	}

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

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