Redmine + Subversion PHP automation script

<?php
	include("Mail.php");

	/*
	 * Author: Fernando Possebon
	 * Date: 01/25/2010
	 * Credits: This script was developed based on script posted by aadreja on his blog
	 * http://aadreja.blogspot.com/2010/08/subversion-redmine-ldap-automatically.html
	 */

	#Setup configuration
	$redmineURL = "";
	$redmineDatabase = "redminedevelopment";
	$redmineDatabaseServer = "localhost";
	$redmineDatabaseUser = "redminedev";
	$redmineDatabasePassword = "dbPassword";
	$svnpath = "/opt/svndevelopment";
	$svnrepotemplate = "/opt/redmine-additionals/svntemplaterepodev.dump";
	$SCMType = "Subversion";
	$svnURLRoot = "http://svndev.com/svn/";
	$SVNReadOnlyUser = "redmine.svn";
	$SVNReadOnlyPassword = "svnPassword";
	$logMessage ="";
	$mailFrom = "redmineadmin@localhost";
	$mailHost = "192.168.1.25";
	$mailPort = "25";
	$mailAuth = false;
	$mailUsername = "smtpusername";
	$mailPassword = "smtppassword";
	$mailSubject = "[Redmine & Subversion] - Results from script";
	$apacheUser = "apache";
	$apacheGroup = "apache";

	date_default_timezone_set('America/Sao_Paulo');

	$authfile = "/etc/svndev-acl-conf"; //path of AuthzSVNAccessFile

	/* Tables and columns used
	 * repositories (id, project_id, url, login, password, root_url, type)
	 * projects (id, name, identifier, status)
	 * users (id, login, mail, status, admin)
	 */

	//Read projects database from Redmine
	//only projects that there is no repository configured on database
	//and for projects active i.e. status=1
	$redmine_link =mysql_connect($redmineDatabaseServer, $redmineDatabaseUser, $redmineDatabasePassword) or die("Couldn't connect to MySQL Server on $myServer");

	if (!mysql_select_db($redmineDatabase, $redmine_link)) {
    	echo 'Could not select database';
    	exit;
	}

	$query = "select id,name,identifier from projects where status=1 and id not in (select distinct project_id from repositories)";
	$result = mysql_query($query,$redmine_link);

	while ($row = mysql_fetch_assoc($result)) {

        $projectid = $row["id"];
		$projectident = $row["identifier"];

        $svnprojectpath = $svnpath . "/" . $projectident;

		//First create the repository
		system("/usr/bin/svnadmin create " . $svnprojectpath,$logMessage);

		//Load the template (directory structures and SVN properties)
		system("/usr/bin/svnadmin load --ignore-uuid " . $svnprojectpath . " < " . $svnrepotemplate,$logMessage);

		// Insert repositories information on Redmine database
		$queryinsert = "insert into repositories (project_id, url, login, password, root_url, type) values (" . $projectid . ",'" . $svnURLRoot . $projectident . "','" . $SVNReadOnlyUser . "','" . $SVNReadOnlyPassword . "','" . $svnURLRoot . $projectident . "','" . $SCMType . "')";
		$resultinsert = mysql_query($queryinsert,$redmine_link);

		$logMessage = $logMessage . "\n" . "Repository for " . $projectident . " configured on Redmine.\n";
	}

	// Change the ownership of Subversion folders to apache user
	system("chown " . $apacheUser . ":" . $apacheGroup . " " . $svnpath . " -R",$logMessage);

	mysql_free_result($result);

	//Set the permissions on ACL file (project members)
	//Every administrator user on Redmine will be granted on subversion
	//as administrator

	$admin_users = NULL;

	$fp = fopen($authfile, "w");
	fwrite($fp, "# Subversion authorization file created by script \n");

	#section [groups]
	fwrite($fp, "[groups]\n");
	$queryadmin = "select login from users where admin=1 and status=1";
	$resultadmin = mysql_query($queryadmin,$redmine_link);

	while ($adminrow = mysql_fetch_assoc($resultadmin)) {

    	$admin_users = $admin_users . ($admin_users != NULL ? ", " : "") . $adminrow["login"];

    }

    mysql_free_result($resultadmin);

    $group_name = "svnadmin";

    fwrite($fp, "$group_name = $admin_users \n");

    #create groups from redmine projects
	$query = "select id,name,identifier from projects";
	$result = mysql_query($query,$redmine_link);

	while ($row = mysql_fetch_assoc($result)) {

        $group_name = $row["identifier"];
        $group_id = $row["id"];
        $group_users = NULL;

        //get users for this group
        $query = "select b.id, b.login from members a, users b where a.user_id=b.id AND a.project_id=$group_id and b.status=1";
        $usvn_users_result = mysql_query($query,$redmine_link);

        while ($userrow = mysql_fetch_assoc($usvn_users_result)) {

        	$group_users = $group_users . ($group_users != NULL ? ", " : "") . $userrow["login"];

        }

        mysql_free_result($usvn_users_result);

        fwrite($fp, "$group_name = $group_users \n");

	}

	mysql_free_result($result);

	#section [project]
	fwrite($fp, "\n\n#Projects from Redmine \n");

	#First give permission to SVNAdmin group
	fwrite($fp, "#Project / \n");
    fwrite($fp, "[:/] \n");
    fwrite($fp, "@svnadmin = rw \n\n");

    # Now add the rest
	$query = "select id,name,identifier from projects";
	$result = mysql_query($query,$redmine_link);

	while ($row = mysql_fetch_assoc($result)) {

    	$project_name = $row["name"];
        $project_id = $row["id"];
        $project_identifier = $row["identifier"];

        fwrite($fp, "#Project $project_name \n");
        fwrite($fp, "[$project_identifier:/] \n");
        fwrite($fp, "@$project_identifier = rw \n\n");
	}

	mysql_free_result($result);

	fclose($fp);

	print("SVN Auth file $authfile generated sucessfully\n");

	$logMessage = $logMessage . "\nSVN Auth file $authfile generated sucessfully\n";

	//Send an email to Redmine administrators with the results

	$queryadmin = "select mail from users where admin=1 and status=1";
	$resultadmin = mysql_query($queryadmin,$redmine_link);

	while ($adminrow = mysql_fetch_assoc($resultadmin)) {

    	$admin_users = $admin_users . ($admin_users != NULL ? ", " : "") . $adminrow["mail"];

    }

    mysql_free_result($resultadmin);

    /* mail setup recipients, subject etc */
	$recipients = $admin_users;

	$from = $mailFrom;
	$to = $recipients;
	$subject = $mailSubject;
	$body = $logMessage;

	$host = $mailHost;
	$username = $mailUsername;
	$password = $mailPassword;

	$headers = array ('From' => $from, 'To' => $to, 'Subject' => $subject); $smtp = Mail::factory('smtp', array ('host' => $host, 'auth' => $mailAuth, 'username' => $username, 'password' => $password));

	$mail = $smtp->send($to, $headers, $body);

	if (PEAR::isError($mail)) {
		echo("" . $mail->getMessage() . "");
	}
	else {
		echo("Message successfully sent!");
	}

	mysql_close($redmine_link);

?>