#!/usr/bin/php
<?php
    $link = mysql_connect('localhost', 'root', 'password');
    $limit = 50;

    if ($link && mysql_select_db('bacula', $link))
    {
        $query = "SELECT VolumeName, PoolId,
                         LastWritten, 
                         VolRetention, 
                         UNIX_TIMESTAMP(NOW()) uts_now,
                         UNIX_TIMESTAMP(LastWritten) + VolRetention uts_ret,
                         FROM_UNIXTIME(UNIX_TIMESTAMP(LastWritten) + VolRetention) ts_ret
                  FROM Media
                  WHERE UNIX_TIMESTAMP(NOW()) > UNIX_TIMESTAMP(LastWritten) + VolRetention
                    AND VolStatus = 'Full'
                    AND Recycle = 1
                  ORDER BY LastWritten
                  LIMIT $limit";

        $result = mysql_query($query);

        while ($row = mysql_fetch_assoc($result))
        {
            $VolumeName  = $row['VolumeName'];
            $PoolId      = $row['PoolId'];
            $LastWritten = $row['LastWritten'];

            $cmd = "echo \"purge jobs volume\n$PoolId\n$VolumeName\n\" | /usr/sbin/bconsole";
            #print "$cmd\n";
            $outp = system("$cmd"); 
        }

        $query = "SELECT VolumeName, PoolId,
                         LastWritten, 
                         LastWritten + VolRetention
                  FROM Media
                  WHERE NOW() > LastWritten + VolRetention
                    AND VolStatus = 'Purged'
                    AND Recycle = 1
                  ORDER BY LastWritten
                  LIMIT $limit";

        $result = mysql_query($query);

        while ($row = mysql_fetch_assoc($result))
        {
            $VolumeName  = $row['VolumeName'];
            $PoolId      = $row['PoolId'];
            $LastWritten = $row['LastWritten'];

            $cmd = "echo \"delete volume\n$PoolId\n$VolumeName\nyes\n\" | /usr/sbin/bconsole";
            #print "$cmd\n";
            $outp = system("$cmd"); 
        }

        $dir = "/backup/jewel/bacula";

        if (is_dir($dir))
        {
            if ($dh = opendir ($dir))
            {
                while (($file = readdir($dh)) !== false)
                {
                    if ($file == "." || $file == "..")
                    {
                        continue;
                    }
                    #print "filename: $file\n";
                    $query = "SELECT COUNT(*) num
                              FROM Media
                              WHERE VolumeName = '$file'";
                    #print "query: $query\n";
                    $result = mysql_query($query);
                    if ($row = mysql_fetch_assoc($result))
                    {
                        $num = $row['num'];
                        if ($num == 0)
                        {
                            $fullfile = "$dir/$file";
                            print "Can delete $fullfile\n";
                            unlink("$fullfile");
                        }
                    }

                }
            }
            closedir($dh);
        }
    }
    else
    {
        print "\n\nUnable to connect\n\n";
    }
?>