alum_import.php

Go to the documentation of this file.

#!/usr/bin/php -q
<?php
// {{{ Configuration

// Do a dry run only?
$b_dryRun = true;

// The DSN for the import database
$s_dsnImport = 'mysql://username:password@localhost/studentinfo';

// The import table with alumni info?
$s_importTable = 'importstudents';

// The DSN for the FastFrame database
$s_dsnFF = 'mysql://username:password@localhost/fastframe_alumni';

// FastFrame alumni data table 
$s_alumTable = 'alumni_alums';

// FastFrame profile table 
$s_profileTable = 'profile';

// }}}
// {{{ Setup

require_once 'DB.php';
error_reporting(E_ALL);
set_time_limit(0);
PEAR::setErrorHandling(PEAR_ERROR_CALLBACK, 'handle_pear_error');
$o_dataImport =& DB::connect($s_dsnImport);
$o_dataFF =& DB::connect($s_dsnFF);

// }}}
// {{{ handle_pear_error()

function handle_pear_error (&$error_obj) 
{
    die ($error_obj->getMessage()."\n".$error_obj->getDebugInfo());
}

// }}}
// {{{ Loop through imported alums

print  "Importing alums...\n";
// Get all graduates and incompletes that were here > 1 year
$s_query = sprintf('SELECT * FROM %s 
            WHERE status = %s OR 
            (status = %s AND (TO_DAYS(grad_date) - TO_DAYS(start_date)) > 365)', 
            $s_importTable, $o_dataImport->quote('GRAD'), $o_dataImport->quote('INC'));
$o_result = $o_dataImport->query($s_query);
$s_insertCount = 0;
$s_updateCount = 0;
while ($a_row = $o_result->fetchRow(DB_FETCHMODE_ASSOC)) {
    // See if they are already in the FastFrame database in which case we update
    $s_query = sprintf('SELECT %s FROM %s WHERE %s = %s', 
            'id', $s_alumTable, 'student_id', $o_dataFF->quote($a_row['student_id']));
    $a_row['email'] = empty($a_row['email2']) ? $a_row['email'] : $a_row['email2'];
    if (!is_null($s_id = $o_dataFF->getOne($s_query))) {
        $s_updateCount++;
        if ($b_dryRun) {
            echo "Student ID {$a_row['student_id']} would be updated\n";
        }
        else {
            $s_query = sprintf('UPDATE %s SET %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s WHERE %s=%s', 
                    $s_profileTable,
                    'firstname', $o_dataFF->quote($a_row['first_name']),
                    'lastname', $o_dataFF->quote($a_row['last_name']),
                    'email', $o_dataFF->quote($a_row['email']),
                    'phone', $o_dataFF->quote($a_row['phone']),
                    'username', $o_dataFF->quote($a_row['student_id']),
                    'password', $o_dataFF->quote(md5($a_row['student_id'])),
                    'id', $s_id);
            $o_dataFF->query($s_query);

            $s_query = sprintf('UPDATE %s SET %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s WHERE %s=%s', 
                    $s_alumTable,
                    'student_id', $o_dataFF->quote($a_row['student_id']),
                    'address', $o_dataFF->quote($a_row['street']),
                    'city', $o_dataFF->quote($a_row['city']),
                    'state', $o_dataFF->quote($a_row['state']),
                    'major', $o_dataFF->quote($a_row['program']),
                    'country', $o_dataFF->quote($a_row['citizenship']),
                    'postal_code', $o_dataFF->quote($a_row['zip']),
                    // Not syncing grad year because it's incorrect in c2k for <78 alums
                    // 'grad_year', $o_dataFF->quote(substr($a_row['grad_date'], 0, 4)),
                    // NOTE: a bit of a hack, we save status in fax
                    'fax', $o_dataFF->quote($a_row['status']),
                    'id', $s_id);
            $o_dataFF->query($s_query);
        }
    }
    else {
        $s_insertCount++;
        if ($b_dryRun) {
            echo "Student ID {$a_row['student_id']} would be inserted\n";
        }
        else {
            $s_id = $o_dataFF->nextId($s_profileTable);
            $s_query = sprintf('INSERT INTO %s SET %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, receive_emails=1', 
                    $s_profileTable,
                    'id', $s_id,
                    'firstname', $o_dataFF->quote($a_row['first_name']),
                    'lastname', $o_dataFF->quote($a_row['last_name']),
                    'email', $o_dataFF->quote($a_row['email']),
                    'username', $o_dataFF->quote($a_row['student_id']),
                    'password', $o_dataFF->quote(md5($a_row['student_id'])),
                    'phone', $o_dataFF->quote($a_row['phone']),
                    // because authsource is empty they won't be able to log in
                    'authsource', '\'\'',
                    'theme', '\'\'', 
                    'list_mode', '\'\'', 
                    'language', '\'\'', 
                    'initial_page', '\'\'');
            $o_dataFF->query($s_query);

            $s_query = sprintf('INSERT INTO %s SET %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s, %s=%s', 
                    $s_alumTable,
                    'id', $s_id,
                    'student_id', $o_dataFF->quote($a_row['student_id']),
                    'address', $o_dataFF->quote($a_row['street']),
                    'city', $o_dataFF->quote($a_row['city']),
                    'state', $o_dataFF->quote($a_row['state']),
                    'major', $o_dataFF->quote($a_row['program']), 
                    'grad_year', $o_dataFF->quote(substr($a_row['grad_date'], 0, 4)),
                    'title', $o_dataFF->quote($a_row['title']), 
                    'country', $o_dataFF->quote($a_row['citizenship']), 
                    'postal_code', $o_dataFF->quote($a_row['zip']), 
                    // NOTE: a bit of a hack, we save status in fax
                    'fax', $o_dataFF->quote($a_row['status']), 
                    'company', '\'\'',
                    'web_site', '\'\'');
            $o_dataFF->query($s_query);
        }
    }
}

// }}}
$s_total = $s_updateCount + $s_insertCount;
print "Import Done.  Inserted: $s_insertCount.  Updated: $s_updateCount.  Total: $s_total.\n";
?>

---