CleanUp WPMU

<?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             = 'my_user';
$mysql_password             = 'my_pass';
$mysql_database             = 'my_db';

$options['email_from']      = 'suport@zice.ro'; // Email-address used for sending notices.
$options['email_subject']   = 'Blogul tau pe zice.ro';
$options['mail_report']     = 'suport@zice.ro'; // Reports will be sent to this address.
$options['timetomail']      = 180; // Number of days blog can be inactive before receiving notice of deletion.
$options['timetodeletion']  = 365; // Number of days blog can be inactive before deletion.
$options['dateformat']      = 'd-m-Y'; // Dateformat used in emails.

$options['noticemessage']    = 'Draga utilizator zice.ro,' . "\n\n" . 'Blogul tau nu a fost updatat in %timetomail% zile. Acest mesaj este trimis pentru a iti aduce aminte ca blogul tau va fi sters daca nu este updatat timp de %timetodeletion% zile.' . "\n\n" . 'Daca doresti sa-ti pastrezi blogul te rugam sa te loghezi si sa iti updatezi blogul înainte de %date%. te poti loga aici:' . "\n\n" . '%loginurl%' . "\n\n" . 'Cu stima,' . "\n\n" . 'echipa zice.ro.';
$options['deletemessage']    = 'Draga utilizator zice.ro,' . "\n\n" . 'Pentru ca nu ti-ai updatat blogul timp de %timetodeletion% zile a fost sters automat din reteaua zice.ro.' . "\n\n" . 'Contul tau de utilizator nu a fost sters, deci daca o sa doresti vreodata sa-ti faci alt blog esti binevenit sa te loghezi aici pentru ati crea un nou blog:' . "\n\n" . 'http://zice.ro/wp-login.php' . "\n\n" . 'Cu stima,' . "\n\n" . 'Echipa zice.ro.';

/********************/
/*** 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);

?>