<?php

	include "library.php";

	$_SESSION['sitename'] = "Chat City";

	$cxn = start_code( true );

	$cxn_common = db_common_login();


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

    $full_log = val( $_GET, "fulllog" );
    
    $cxn_workflow = db_login( "academi1_mpsworkflow" );

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


	echo "<style> body html { font-size: 10pt; } </style>";
	
	
	echo "<font color='#999999'>".date( "d M Y  g:ia" )."</font><br><br><br>";
	
	$today_date = date_create( date("Y-m-d") );
	$today_date_str = $today_date->format( "Y-m-d" );		
	
	
	$today_minus_21_days = clone $today_date;

	date_sub( $today_minus_21_days, date_interval_create_from_date_string( '21 days' ));
	$today_minus_21_days_str = $today_minus_21_days->format( "Y-m-d" );		

    
    $day_to_process = clone $today_date;

	$today_minus_365_days = clone $today_date;

	date_sub( $today_minus_365_days, date_interval_create_from_date_string( '365 days' ));
	$today_minus_365_days_str = $today_minus_365_days->format( "Y-m-d" );		
	
	
		/* created on each day */

//	echo "See public_html/chatcity/stats_log.csv for a CSV log of the main results.<br><br><br>";
	
	echo "New members<br><br>";

    if ($full_log == "true")
    {    
    	date_sub( $day_to_process, date_interval_create_from_date_string( '365 days' ));
    	$day_to_process_str = $day_to_process->format( "Y-m-d" );		
    }
//    	$query = "SELECT date(date_created) as date_created2, count(*) as num FROM profile WHERE date_created >= '2017-04-01' AND system_user = 'no' and deleted = 'no' GROUP BY date(date_created) ORDER BY date(date_created)";	
    else
    {
    	date_sub( $day_to_process, date_interval_create_from_date_string( '13 days' ));
    	$day_to_process_str = $day_to_process->format( "Y-m-d" );		
    	
//        $query = "SELECT date(date_created) as date_created2, count(*) as num FROM profile WHERE date_created >= '".$today_minus_21_days_str."' AND system_user = 'no' and deleted = 'no' GROUP BY date(date_created) ORDER BY date(date_created)";	
    }

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

	echo "<table>";
	
	echo "<tr><td>Date</td><td align='center'>New</td><td align='center'>Adwords</td><td align='center'>&nbsp;Organic</td><td align='center'>&nbsp;&nbsp;&nbsp;&nbsp;Page</td><td>&nbsp;&nbsp;&nbsp;Conversion</td></tr>";
	echo "<tr><td></td><td align='center'>Members</td><td align='center'>hits</td><td align='center'>hits</td><td align='center'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Views</td><td align='center'>&nbsp</td></tr>";

	$count = 0;
	$total_new_members = 0;
	$total_organic = 0;
	$total_adwords_hits = 0;
	$total_organic_hits = 0;
    $total_logins = 0;
	$total_page_views = 0;
	$total_conversions = 0;
	
	
		// write a log file of the stats
		
	$fp = fopen( "/home/academi1/m-m-ventures.com/chatcity/stats_log.csv", "w" );

	fputs( $fp, "Date,New Members,Organic Members,Adwords Hits,Organic Hits,Members logged in this day,Messages sent this day,Page views,Conversion Rate,,,Adwords Spend,Adsense Income,Defecit,Recovery Rate,Adwords Clicks,Adsense Clicks,Adwords Gross CPC,Adwords Net CPC,Adsense CPC,Conversions,Conversion Rate,Net CP Conversion\n" );

	
//	for ($i=0; $i < query_num_rows($result); $i++)

    for (; $day_to_process_str <= $today_date_str;)
	{
    	$query = "SELECT DISTINCT email FROM profile WHERE date(date_created) = '".$day_to_process_str."' AND system_user = 'no' and deleted = 'no'";		    
	    
     	$result = run_query( $query, $cxn );	    
     	
		$num_created = query_num_rows( $result );

		$query_wrk = "SELECT * FROM website_advertising_accounts WHERE effective_date = '".$day_to_process_str."' AND website_name = 'Chat City' ORDER BY website_name";

		$result_wrk = run_query( $query_wrk, $cxn_workflow );

        if (query_num_rows( $result_wrk ) == 0)
        {
            $workflow_present = false;
        }
        else
        {
            $workflow_present = true;            
            
    		$data_wrk = get_row( $result_wrk );
    
    		$adwords_spend_str = number_format( $data_wrk['adwords_spend'], 2 );
    		$adsense_income_str = number_format( $data_wrk['adsense_income'], 2 );
    		$deficit_str = number_format( ($data_wrk['adwords_spend']-$data_wrk['adsense_income']), 2 );
    		$adwords_clicks_str = $data_wrk['adwords_clicks'];
    		$adsense_clicks_str = $data_wrk['adsense_clicks'];				
    		
			if ($data_wrk['adwords_spend'] == 0)
				$recovery_rate_str = "";
			else
				$recovery_rate_str = number_format( ($data_wrk['adsense_income'] / $data_wrk['adwords_spend'])*100, 1 )."%";


			if ($data_wrk['adwords_clicks'] == 0)
				$gross_cpc_str = "";
			else
				$gross_cpc_str = number_format( ($data_wrk['adwords_spend']/$data_wrk['adwords_clicks'])*100, 2 )." c";

				
			if ($data_wrk['adwords_clicks'] == 0)
				$net_cpc_str = "";
			else
				$net_cpc_str = number_format( (($data_wrk['adwords_spend']-$data_wrk['adsense_income'])/$data_wrk['adwords_clicks'])*100, 2 )." c";

			if ($data_wrk['adsense_clicks'] == 0)
				$adsense_cpc_str = "";
			else
				$adsense_cpc_str = number_format( ($data_wrk['adsense_income']/$data_wrk['adsense_clicks'])*100, 1 )." c";
    		
			if ($data_wrk['adwords_clicks'] == 0)
				$conversion_rate_str = "";
			else
				$conversion_rate_str = number_format( ($data_wrk['conversions'] / $data_wrk['adwords_clicks']) * 100, 1 )."%";
				
			if ($data_wrk['adsense_clicks'] == 0)
			    $adsense_cpc = 0;
			else
			    $adsense_cpc = $data_wrk['adsense_income']/$data_wrk['adsense_clicks'];
				
        }
        			    
	    
	    
//		$query4 = "SELECT count(*) as num FROM profile WHERE date(date_created) = '".$date_created2."' AND (source = 'organic' or source = '.blank')";
		$query4 = "SELECT count(*) as num FROM profile WHERE date(date_created) = '".$day_to_process_str."' AND (source = 'organic')";
		
 		$result4 = run_query( $query4, $cxn );

        $data4 = get_row( $result4 );
        
        $num_organic = $data4['num'];

	    
		$query2 = "SELECT DISTINCT ip_address FROM hits WHERE hit_date = '".$day_to_process_str."' AND source = 'adwords'";

 		$result2 = run_query( $query2, $cxn );

		$num_adwords = query_num_rows( $result2 );


		$query3 = "SELECT DISTINCT ip_address FROM hits WHERE hit_date = '".$day_to_process_str."' AND (source = 'organic' or source = '.blank')";

 		$result3 = run_query( $query3, $cxn );


    	$query7 = "SELECT count(*) as num FROM message WHERE date(sent) = '".$day_to_process_str."'";
    	
     	$result7 = run_query( $query7, $cxn );
    
    	$data7 = get_row( $result7 );

	
		$query5 = "SELECT sum(count) as num FROM page_views WHERE page_view_date = '".$day_to_process_str."'";

 		$result5 = run_query( $query5, $cxn );

        $data5 = get_row( $result5 );
        
        $num_page_views = $data5['num'];


		$query6 = "SELECT DISTINCT member_id FROM logins_log WHERE date(login_datetime) = '".$day_to_process_str."'";

 		$result6 = run_query( $query6, $cxn );

		$num_logins_this_date = query_num_rows( $result6 );


//		$query8 = "SELECT DISTINCT sender_id FROM message WHERE date(sent) = '".$day_to_process_str."'";
//		
//		$result8 = run_query( $query8, $cxn );
//
//		$num_members_sending_messages = query_num_rows( $result8 );


		if ($num_adwords != 0)
			$conversion = number_format( ($num_created / $num_adwords)*100, 1 );
		else
			$conversion = "0";

        if ($workflow_present)		            
        {
    		fputs( $fp, $day_to_process_str.",".$num_created.",".$num_organic.",".$num_adwords.",".query_num_rows( $result3 ).",".$num_logins_this_date.",".$data7['num'].",".$num_page_views.",".$conversion.
		            ",,,".
            
			$data_wrk['adwords_spend'].",".
			$data_wrk['adsense_income'].",".
			($data_wrk['adwords_spend']-$data_wrk['adsense_income']).",".
			$data_wrk['adsense_income'] / $data_wrk['adwords_spend'].",".
			$data_wrk['adwords_clicks'].",".
			$data_wrk['adsense_clicks'].",".					
			($data_wrk['adwords_spend']/$data_wrk['adwords_clicks']).",".					
			(($data_wrk['adwords_spend']-$data_wrk['adsense_income'])/$data_wrk['adwords_clicks']).",".
			$adsense_cpc.",".										
			$data_wrk['conversions'].",".
			($data_wrk['conversions'] / $data_wrk['adwords_clicks']).",".
			(($data_wrk['adwords_spend']-$data_wrk['adsense_income'])/$data_wrk['conversions'])
			
            ."\n" );			
        }
        else
    		fputs( $fp, $day_to_process_str.",".$num_created.",".$num_organic.",".$num_adwords.",".query_num_rows( $result3 ).",".$num_logins_this_date.",".$data7['num'].",".$num_page_views.",".$conversion."\n" );


//		if ((time() - strtotime( $data['date_created2'] )) < 14*24*60*60 )
		{
			echo "<tr><td>".$day_to_process_str."</td><td align='right'>".$num_created."</td>";

//			echo "<td align='right'>".$num_organic."</td>";
			
			echo "<td align='right'>".$num_adwords."</td>";

			echo "<td align='right'>".query_num_rows( $result3 )."</td>";

//			echo "<td align='right'>".$num_logins_this_date."</td>";

			echo "<td align='right'>".number_format( $num_page_views, 0 )."</td>";

			echo "<td align='right'>".$conversion."%</td>";

			if ((time() - strtotime( $day_to_process_str )) < 6*24*60*60 && (time() - strtotime( $day_to_process_str )) > 24*60*60 )
			{
				$count += 1;
				$total_new_members += $num_created;
				$total_organic += $num_organic;
				$total_adwords_hits += $num_adwords;
				$total_organic_hits += query_num_rows( $result3 );
				$total_logins += $num_logins_this_date;
				$total_page_views += $num_page_views;
				$total_conversions += $conversion;
			}
			
			echo"</tr>";
		}
	
		date_add( $day_to_process, date_interval_create_from_date_string( '1 day' ));
    	$day_to_process_str = $day_to_process->format( "Y-m-d" );		
	}

	fclose( $fp );


	echo "<tr><td>&nbsp;</td></tr>";
	
//	echo "<tr><td>5 day average</td><td align='right'>";
//	
//	echo number_format( ($total_new_members / $count), 1 );
//	
//	echo "</td><td align='right'>";		
//
////	echo number_format( ($total_organic / $count), 1 );
//
////	echo "</td><td align='right'>";		
//
//	echo number_format( ($total_adwords_hits / $count), 0 );
//	
//	echo "</td><td align='right'>";		
//
//	echo number_format( ($total_organic_hits / $count), 0 );
//	
//	echo "</td><td align='right'>";		
//
////	echo number_format( ($total_logins / $count), 0 );
//
////	echo "</td><td align='right'>";		
//	
//	echo number_format( ($total_page_views / $count), 0 );
//
//	echo "</td><td align='right'>";		
//
////	echo number_format( ($total_conversions / $count), 1 )."%";
//	
//	echo "</td></tr>";		

	echo "</table>";

	echo "<br>";


		/* site visitors today */

//	$data = get_first_row( "SELECT count(*) as num FROM hits WHERE hit_date = '".date( "Y-m-d")."' GROUP BY hit_date, source, ip_address", $cxn );

	$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 (total): ".query_num_rows( $result )."<br>";



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

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

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



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

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

	echo "<br>Site visitors today (adwords): ".query_num_rows( $result )."<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>Searches: ".$data['num']."<br>";


	$query = "SELECT sum(count) as num FROM page_views WHERE date(page_view_date) = '".date( "Y-m-d" )."'";

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

    $data = get_row( $result );

	echo "<br>Page views: ".number_format( $data['num'], 0 )."<br><br><br>";


		// main stats

	echo "<table>";

	$query = "SELECT DISTINCT email FROM profile WHERE system_user = 'no' and deleted = 'no' and date(date_created) = '".date( "Y-m-d", strtotime( date( "Y-m-d") ) )."'";

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

    $num_created = query_num_rows( $result );
    
	echo "<tr><td>New members today (excluding system users)</td><td align='right'>".$num_created."</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'];
//	    
//	echo "<tr><td><br>Visitors on the site</td><td align='right'><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 align='right'><br>".$num."</td></tr>";


		// Contact Rrequests messages today

	$query = "SELECT count(*) as num FROM message WHERE date(sent) = '".date( "Y-m-d" )."' and type = 'contact_request'";
	
 	$result = run_query( $query, $cxn );

	$data = get_row( $result );

	echo "<tr><td><br>Contact Request messages today</td><td align='right'><br>".number_format( $data['num'], 0 )."</td></tr>";


		// emails today

	$query = "SELECT count(*) as num FROM message WHERE date(sent) = '".date( "Y-m-d" )."' and type = 'email'";
	
 	$result = run_query( $query, $cxn );

	$data = get_row( $result );

	echo "<tr><td><br>Email messages today</td><td align='right'><br>".number_format( $data['num'], 0 )."</td></tr>";


		// system messages today

	$query = "SELECT count(*) as num FROM sys_message WHERE date(sent) = '".date( "Y-m-d" )."'";
	
 	$result = run_query( $query, $cxn );

	$data = get_row( $result );

	echo "<tr><td><br>System messages today</td><td align='right'><br>".number_format( $data['num'], 0 )."</td></tr>";


		// Number of members sending emails today
		
//	$query = "SELECT DISTINCT sender_id FROM message WHERE type = 'email' AND date(sent) = '".date( "Y-m-d" )."'";
//	
//	$result = run_query( $query, $cxn );
//
//	$num = query_num_rows( $result );
//
//	echo "<tr><td><br>Number of members sending emails today</td><td align='right'><br>".number_format( $num, 0 )."</td></tr>";


		// Number of members sending kisses today
		
//	$query = "SELECT DISTINCT sender_id FROM message WHERE type = 'kiss' AND date(sent) = '".date( "Y-m-d" )."'";
//	
// 	$result = run_query( $query, $cxn );
//
//	$num = query_num_rows( $result );
//
//	echo "<tr><td><br>Number of members sending kisses today</td><td align='right'><br>".number_format( $num, 0 )."</td></tr>";



		// Number of members logged in today
		
//	$query = "SELECT id FROM profile WHERE date(last_login) = '".date( "Y-m-d" )."'";
//	
//	$result = run_query( $query, $cxn );
//
//	$num = query_num_rows( $result );
//
//	echo "<tr><td><br>Number of members logged in today</td><td align='right'><br>".number_format( $num, 0 )."</td></tr>";



		// photos uploaded today

//	$query = "SELECT id FROM photos WHERE uploaded_datetime >= '".date( "Y-m-d 00:00:00" )."'";
//	
// 	$result = run_query( $query, $cxn );
//
//	$num = query_num_rows( $result );
//
//	echo "<tr><td><br>Photos uploaded today</td><td align='right'><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."%";


		// Emails in the queues
		

	$query = "SELECT count(*) as num FROM emails_to_send_live";
	
 	$result = run_query( $query, $cxn_common );

	$data = get_row( $result );

	$num = $data['num'];

	echo "<tr><td><br>Number of emails queued to send (live)</td><td align='right'><br>".number_format( $num, 0 )."</td></tr>";


	$query = "SELECT count(*) as num FROM emails_to_send_background";
	
 	$result = run_query( $query, $cxn_common );

	$data = get_row( $result );

	$num = $data['num'];

	echo "<tr><td><br>Number of emails queued to send (background)</td><td align='right'><br>".number_format( $num, 0 )."</td></tr>";


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

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

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



		/* total members */

	$query = "SELECT count(*) as num FROM profile WHERE deleted = 'no' and system_user = 'no' and last_login >= '".$today_minus_365_days_str."'";
 	$result = run_query( $query, $cxn );
	$data = get_row( $result );

	echo "<tr><td><br>Total members</td><td align='right'><br>".number_format( $data['num'], 0 )."</td></tr>";



	echo "</table>";

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


		/* last 30 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 < query_num_rows($result); $i++)
//	{
//		$data = get_row( $result );

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

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

	echo "Last 30 members<br><br><br><table>";

	echo "<tr style='padding-left:15'><td style='padding-left:15'><b>Handle</b></td><td style='padding-left:15'><b>Claimed</b></td><td style='padding-left:15'><b>IP country</b></td><td style='padding-left:15'><b>Source</b></td><td style='padding-left:15' align='right'><b>Created</b></td></tr>";

	echo "<tr><td></td></tr>";
	
	$query = "SELECT * FROM profile WHERE deleted = 'no' and system_user = 'no' ORDER BY id DESC LIMIT 30";
	$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", "", "stats_screen", "true" )."</td><td>".$data['country']."</td><td>".$data['ip_country']."</td><td>".$data['source']."</td><td>".$data['membership_type']."</td></tr>";

		$time_txt = substr( $data['date_created'], 11, 8 );
		
		$time_display = date_format( date_create_from_format( "H:i:s", $time_txt ),  "g:i a" );
		
		$url = url_encode( "display_profile.php", "showid", $data['id'], "returnpoint", "main", "startpos", "0", "stats_screen", "true" );

		$handle = "<a class='glink' href='".$url."'>".$data['handle']."</a>";

		echo "<tr style='padding-left: 15px'><td>".$handle."</td><td style='padding-left:15'>".$data['country']."</td><td style='padding-left:15'>".$data['ip_country']."</td><td style='padding-left:15'>".$data['source']."</td><td style='padding-left:15' align='right'>".$time_display."</td></tr>";
	}

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


		// members logged in

//	echo "<br>Members logged in<br><br><br><table>";
//
//	echo "<tr><td>Handle</td><td>Claimed</td><td>IP country</td></tr>";
//
//	echo "<tr><td></td></tr>";
//		
//	$query = "SELECT member_id, handle, country, ip_country FROM profile INNER JOIN online_status ON profile.id = online_status.member_id WHERE member_id <> -1";
//	
//	$result = run_query( $query, $cxn );
//
//	$num = 0;
//
//	for ($i=0; $i < query_num_rows($result); $i++)
//	{
//		$data = get_row( $result );
//
//		$url = url_encode( "display_profile.php", "showid", $data['member_id'], "returnpoint", "main", "startpos", "0", "stats_screen", "true" );
//
//		$handle = "<a class='glink' href='".$url."'>".$data['handle']."</a>";
//
//		echo "<tr><td>".$handle."</td><td>".$data['country']."</td><td>".$data['ip_country']."</td></tr>";
//	}
//
//	echo "</table>";
//	
	

		// visitors on the site
		// ** duplicated with the list of 20 latest site visitors

//	echo "Visitors on the site<br><br><br><table>";
//
//	echo "<tr><td>IP address</td><td>IP country</td></tr>";
//
//	echo "<tr><td></td></tr>";
//		
//	$query = "SELECT ip_address FROM online_status WHERE member_id = -1";
//	
//	$result = run_query( $query, $cxn );
//
//	$num = 0;
//
//	for ($i=0; $i < query_num_rows($result); $i++)
//	{
//		$data = get_row( $result );
//
//		echo "<tr><td>".$data['ip_address']."</td><td>".get_ip_country( $cxn_common, $data['ip_address'] )."</td></tr>";
//	}
//
//	echo "</table>";
		

		/* aggregate hits today */

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

//	echo "Hits yesterday and 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>".sp(4).$data['xconversions']."</td><td>".sp(4).$data['xhits']."</td><td>".sp(4).round( $conv_rate, 1 )." %</td></tr>";
//	}
//
	echo "</table><br><br>";


		/* latest site visitors */
		
	echo "<br><br><br>Latest site visitors<br><br>";

	echo "<table>";
	
	$query = "SELECT * FROM hits ORDER BY latest_hit DESC LIMIT 20";

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

	for ($i=0; $i < 20; $i++)
	{
		$this_row = get_row( $result );
		
		$data[$i] = $this_row;
	}
	
	for ($i=19; $i >= 0; $i--)
	{
		$time_txt = substr( $data[$i]['latest_hit'], 11, 8 );
		
		$time_display = date_format( date_create_from_format( "H:i:s", $time_txt ),  "g:i a" );
		
		echo "<tr><td>&nbsp;".$data[$i]['ip_address']."&nbsp;</td><td>&nbsp;".$data[$i]['ip_country']."&nbsp;</td><td>&nbsp;".$data[$i]['source']."&nbsp;</td><td>&nbsp;".$time_display."</td></tr>";
	}
	

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


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

	
		/* list of countries */

	$query = "SELECT country, count(*) as num FROM profile WHERE deleted = 'no' and system_user = 'no' and last_login >= '".$today_minus_365_days_str."' 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 align='right'>".number_format( $data['num'], 0 )."</td></tr>";
	}

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


		/* total countries */

//	$query = "SELECT count(*) as num FROM (SELECT DISTINCT country FROM profile WHERE deleted = 'no' and system_user = 'no' and last_login >= '".$today_minus_365_days_str."') 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>";


	echo "Number of active members, split by ip country<br><br>";

	
		/* list of ip countries */

	$query = "SELECT ip_country, count(*) as num FROM profile WHERE deleted = 'no' and system_user = 'no' and last_login >= '".$today_minus_365_days_str."' GROUP BY ip_country ORDER BY ip_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['ip_country']."</td><td align='right'>".number_format( $data['num'], 0 )."</td></tr>";
	}

	echo "</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 < query_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 today */

//	$query = "SELECT ip_country, source, sum(conversions) as sconversions, sum(hits) as s_hits FROM hits WHERE date(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>";
//
//	echo "<tr><td>Country</td><td align='center'>Source</td><td align='center'>Conversions</td><td align='center'>&nbsp;Hits</td></tr>";
//
//	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 align='right'>&nbsp;".$data['sconversions']."</td><td align='right'>".$data['s_hits']."</td></tr>";
//	}
//
//	echo "</table><br><br>";

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