<?php
/**********************************************************
  WPMU Cleanup

This script checks the WPMU database for blogs that have
not been updated for X days, then sends a notice to the
blogowner that the blog will be deleted after Y days.

It also automatically deletes blogs that have not been
updated for Y days, then sends an email to the blogowner.

On both occasions, the script will send a report with
details on warned and deleted blogs to the administrator.

This script has *NOT* been programmed as a plugin since
it is intended to be executed by a cronjob. As it is now
it should be run once every 7 days, but you can configure
it to do otherwise.

This script is *NOT* a final version and should be used
with caution and only if you know what you are doing. I
recommend testing it on a testsite rather than a livesite.

Please feel free to comment at p(a)studinski.dk.

  TODO:

- CSV/TXT-file with list of sent notices with timestamps.
  - Either of two functions in this regard:
    - Make sure bloggers only receive one warning.
    - Make the "x days until deletion"-message variable.
- Authentication for users that wishes to run it manually.
- Your suggestions?
**********************************************************/

/* SETTINGS */

$mysql_server				= 'localhost';
$mysql_username				= 'root';
$mysql_password				= '';
$mysql_database				= 'wpmu';

$options['email_from']		= 'admin@domain.com'; // Email-address used for sending notices.
$options['email_subject']	= 'Your Blog';
$options['mail_report']		= 'admin@domain.com'; // Reports will be sent to this address.
$options['timetomail']		= 7; // Number of days blog can be inactive before receiving notice of deletion.
$options['timetodeletion']	= 30; // Number of days blog can be inactive before deletion.
$options['dateformat']		= 'd-m-Y'; // Dateformat used in emails.

$options['noticemessage']	= 'Dear user,' . "\n\n" . 'You have not updated your blog in %timetomail% days. This message is to remind you that your blog will be deleted if it is not updated for %timetodeletion% days.' . "\n\n" . 'If you would like to keep your blog, please login and update it before %date%. You can login at:' . "\n\n" . '%loginurl%' . "\n\n" . 'Best regards,' . "\n\n" . 'Admin at blogs.com.';
$options['deletemessage']	= 'Dear user,' . "\n\n" . 'Since your blog has not been updated for %timetodeletion% days, it has been deleted.' . "\n\n" . 'Your user details have not been deleted, so if you at some time should want a blog again, you are welcome to create one by logging in and create one at:' . "\n\n" . 'http://blogs.com/wp-login.php' . "\n\n" . 'Best regards,' . "\n\n" . 'Admin at blogs.com.';

/********************/
/*** STOP EDITING ***/
/********************/

$options['unix_timetomail']		= $options['timetomail'] * 24 * 60 * 60;
$options['unix_timetodeletion']	= $options['timetodeletion'] * 24 * 60 * 60;
$options['date']				= date($options['dateformat'], (($options['timetodeletion'] - $options['timetomail']) * 24 * 60 * 60) + time());

// Connect to database
$db =	mysql_connect($mysql_server,
					   $mysql_username,
					   $mysql_password);
							
		mysql_select_db($mysql_database, $db);
			  
// Get blogs
$sql = 'SELECT blog_id,last_updated FROM wp_blogs';
$sql = mysql_query($sql);

		while( $row = mysql_fetch_assoc($sql) ) :
		
			$blogs_raw[$row['blog_id']] = strtotime($row['last_updated']);
			
		endwhile;
		
		// Get blogs for deletion notice
		foreach( $blogs_raw as $key => $value ) :

			$time = time() - $value;
		
			if( $time >= $options['unix_timetomail'] ) :
			
				$blogs_mail[] = array('blogid'  => $key,
									  'email'   => '',
									  'siteurl' => '');
			
			endif;
			
		endforeach;
		
				// Get userdata for deletion notice
				for($i = 0; $i < sizeof($blogs_mail); $i++) :

					$str = 'admin_email';				
					$sql = mysql_query("SELECT option_value FROM wp_" . $blogs_mail[$i]['blogid'] . "_options WHERE option_name = '$str'");
					
						while ( $row = mysql_fetch_assoc($sql) ) :
						
							$blogs_mail[$i]['email'] = $row['option_value'];
							
						endwhile;
						
					$str = 'siteurl';
					$sql = mysql_query("SELECT option_value FROM wp_" . $blogs_mail[$i]['blogid'] . "_options WHERE option_name = '$str'");
					
						while ( $row = mysql_fetch_assoc($sql) ) :
						
							$blogs_mail[$i]['siteurl'] = $row['option_value'];
							
						endwhile;
						
				endfor;
				
						$cronmsg = 'Following blogs have been noticed of deletion:' . "\n\n";
						// Create and send warning notice to users
						for($i = 0; $i < sizeof($blogs_mail); $i++) :
						
							$message = str_replace('%timetomail%', $options['timetomail'], $options['noticemessage']);
							$message = str_replace('%timetodeletion%', $options['timetodeletion'], $message);
							$message = str_replace('%date%', $options['date'], $message);
							$message = str_replace('%loginurl%', $blogs_mail[$i]['siteurl'] . 'wp-login.php', $message);
							
							$to      = $blogs_mail[$i]['email'];
							$subject = $options['email_subject'];
							$headers = 'From: ' . $options['email_from'] . "\r\n" .
									   'Reply-To: ' . $options['email_from'] . "\r\n" .
									   'X-Mailer: PHP/' . phpversion();

							mail($to, $subject, $message, $headers);
							
							$cronmsg .= $blogs_mail[$i]['siteurl'] . "\n";
							
						endfor;
						mail($options['mail_report'], 'Report - Sent notices', $cronmsg);
				

		
		// Get blogs for deletion
		foreach( $blogs_raw as $key => $value ) :
		
			$time = time() - $value;
			
			if( $time >= $options['unix_timetodeletion'] ) :
			
				$blogs_delete[] = array('blogid'  => $key,
										'email'   => '',
										'siteurl' => '');
				
			endif;
			
		endforeach;
		
				// Get userdata for deletion notice
				for($i = 0; $i < sizeof($blogs_delete); $i++) :

					$str = 'admin_email';				
					$sql = mysql_query("SELECT option_value FROM wp_" . $blogs_delete[$i]['blogid'] . "_options WHERE option_name = '$str'");
					
						while ( $row = mysql_fetch_assoc($sql) ) :
						
							$blogs_delete[$i]['email'] = $row['option_value'];
							
						endwhile;
						
					$str = 'siteurl';
					$sql = mysql_query("SELECT option_value FROM wp_" . $blogs_mail[$i]['blogid'] . "_options WHERE option_name = '$str'");
					
						while ( $row = mysql_fetch_assoc($sql) ) :
						
							$blogs_delete[$i]['siteurl'] = $row['option_value'];
							
						endwhile;

				endfor;
				
						// Delete tables
						// $cronmsg = 'Following blogs have been deleted:' . "\n\n";
						for($i = 0; $i < sizeof($blogs_delete); $i++) :
						
							$prefix = 'wp_' . $blogs_delete[$i]['blogid'] . '_';
						
							$sql_categories		= 'DROP TABLE IF EXISTS ' . $prefix . 'categories';
							$sql_comments		= 'DROP TABLE IF EXISTS ' . $prefix . 'comments';
							$sql_link2cat		= 'DROP TABLE IF EXISTS ' . $prefix . 'link2cat';
							$sql_linkcategories	= 'DROP TABLE IF EXISTS ' . $prefix . 'linkcategories';
							$sql_links			= 'DROP TABLE IF EXISTS ' . $prefix . 'links';
							$sql_options		= 'DROP TABLE IF EXISTS ' . $prefix . 'options';
							$sql_post2cat		= 'DROP TABLE IF EXISTS ' . $prefix . 'post2cat';
							$sql_postmeta		= 'DROP TABLE IF EXISTS ' . $prefix . 'postmeta';
							$sql_posts			= 'DROP TABLE IF EXISTS ' . $prefix . 'posts';
							$sql_blogid			= $blogs_delete[$i]['blogid'];
														
							mysql_query($sql_categories) or die('Error: ' . mysql_error());
							mysql_query($sql_comments) or die('Error: ' . mysql_error());
							mysql_query($sql_link2cat) or die('Error: ' . mysql_error());
							mysql_query($sql_linkcategories) or die('Error: ' . mysql_error());
							mysql_query($sql_links) or die('Error: ' . mysql_error());
							mysql_query($sql_options) or die('Error: ' . mysql_error());
							mysql_query($sql_post2cat) or die('Error: ' . mysql_error());
							mysql_query($sql_postmeta) or die('Error: ' . mysql_error());
							mysql_query("DELETE FROM wp_blogs WHERE blog_id = '$sql_blogid'") or die('Error: ' . mysql_error());
							
							// Send mail
							$message = str_replace('%timetodeletion%', $options['timetodeletion'], $options['deletemessage']);
							
							$to      = $blogs_delete[$i]['email'];
							$subject = $options['email_subject'];
							$headers = 'From: ' . $options['email_from'] . "\r\n" .
									   'Reply-To: ' . $options['email_from'] . "\r\n" .
									   'X-Mailer: PHP/' . phpversion();

							mail($to, $subject, $message, $headers);
							
							$cronmsg .= $blogs_delete[$i]['siteurl'] . "\n";
							
						endfor;
						mail($options['mail_report'], 'Report - Deleted blogs', $cronmsg);
		
?>