Location: PHPKode > projects > Son of Service > sos-0.1.6/admin/import_ncoa.php
<?php

/*
 * Son of Service
 * Copyright (C) 2003-2009 by Andrew Ziem.  All rights reserved. 
 * Licensed under the GNU General Public License.  See COPYING for details.
 *
 * Import USPS National Change of Address (NCOA) data.
 *
 * $Id: import_ncoa.php,v 1.5 2009/02/12 04:11:20 andrewziem Exp $
 *
 */

if (preg_match('/import_ncoa.php/i', $_SERVER['PHP_SELF']))
{
    die('Do not access this page directly.');
}

// todo
// - option to disable notes
// - progress indicator
// - multiple instances
// - more flexible input through mapping

function import_ncoa1()
{
?>

<P class="instructionstext">Use this feature to import results from USPS
National Change of Address (NCOA).  This process will update volunteer
addresses and add notes.</P>

<P class="instructionstext">Specify the filename of a file to upload and
import.  The file must be a comma-delimited file (CSV) and the first row
must contain column heading names. The CSV file must have this layout.</p>

<table>
<tr>
<th>Column name</th>
<th>Description</th>
</tr>
<tr>
<td>volunteer_id</td>
<td>Unique volunteer record number</td>
</tr>
<tr>
<td>new_address</td>
<td>0 for no new address, 1 for new address</td>
</tr>
<tr>
<td>move_date</td>
<td>In the format YYYYMM (e.g. 200501 is January 2005)</td>
</tr>
<tr>
<td>move_type</td>
<td>I for individual, F for family, B for business</td>
</tr>
<tr>
<td>bad_address</td>
<td>0 for no, 1 for maybe (NIXIE), 2 for yes (no forwarding address)</td>
</tr>
<tr>
<td>address_error</td>
<td>A text description (such as "foreign move") used when bad_address is 1 or 2</td>
</tr>
<tr>
<td>street</td>
<td>Street address</td>
</tr>
<tr>
<td>city</td>
<td>City</td>
</tr>
<tr>
<td>state</td>
<td>State</td>
</tr>
<tr>
<td>postal_code</td>
<td>ZIP code</td>
</tr>
</table>

<P class="instructionstext">You may omit street, city, state, and postal_code for records with no new address.</p>

<P class="instructionstext">Note: It is a good idea to backup SOS data before importing NCOA.</P>

<P class="instructionstext">Note: Please wait after beginning this operation.  Some time may pass during which it seems no progress is occuring.</P>

<FORM enctype="multipart/form-data" method="post" action=".">
<INPUT type="hidden" name="import_ncoa" value="2">
<INPUT type="hidden" name="MAX_FILE_SIZE" value="2000000">
File name <INPUT type="file" name="userfile">
<INPUT type="submit" value="Send file">
</FORM>
<?php
} /* import_ncoa1() */




function import_ncoa2()
{
	global $db;


	// todo: there is some code duplicate between here and import_legacy2


	// move file safely
    
	$dname = SOS_PATH . 'data/'. $_FILES['userfile']['name'];
    
	$_SESSION['import']['dname'] = $dname;
    
	if (!@move_uploaded_file($_FILES['userfile']['tmp_name'], $dname))
	{
		process_system_error("Unable to move uploaded file.");
		return;
	}
    
	// open file to be imported
    
	$f = fopen($dname, 'r');
    
	if (!$f)
	{
		process_system_error("Unable to open uploaded file.");
		return;
	}
    
	// CSV?
    
	$line = fgets($f);
    
	if (0 == substr_count($line, ',') or preg_match('/.(xls|sxc|ods)/i', $dname))
	{
		process_user_error(_("The file you uploaded is not a CSV file."));
		return;
	}
    
	// get header
    
	rewind($f);
    
	$header = fgetcsv($f, 1000, ",");
    
	if (!$header)
	{
		process_system_error("Unable to read uploaded file.");
		return;
	}

	// todo: lowercase values in $header?


	// check header

	$valid_column_names = array('volunteer_id', 'new_address', 'move_date', 'move_type', 'bad_address', 
		'address_error', 'street', 'city', 'state', 'postal_code');

	if (count($header) != count($valid_column_names))
	{
		process_user_error("Number of columns in header does not match 10 expected.");
		return;
	}

	if (0 == count(array_diff($header, $valid_column_names)))
	{
		process_user_error("Unexpected column names.");
		return;
	}
	

	// check column count consistency and validate data types

	$lc = 1; // line counter

	while (FALSE != ($row = fgetcsv($f, 1000, ",")))
	{
        	$lc++;

		if (count($row) != count($header))
		{
			process_user_error("Number of columns in line $lc does not match number of columns in header.");
			return;
		}

	 	//todo: validate more	

		if (!is_numeric($row[array_search('volunteer_id', $valid_column_names)]))
		{
			process_user_error("Not a valid volunteer_id on line $lc");
			return;
		}

		$move_date = $row[array_search('move_date', $valid_column_names)];
		
		if (strlen($move_date) > 0 and !preg_match('/\d{6}/',$move_date))
		{
			process_user_error("Not a valid move_date on line $lc");
			return;
		}

		$move_type = intval($row[array_search('move_type', $valid_column_names)]);

		if (strlen($move_type) > 0)
		{
			switch ($move_type)
			{
				case 'I':
				case 'F':
				case 'B':
					break;
				default:
				{
				    process_user_error("Not a valid move_type on line $lc");
				    return;
				}
			}
		}
	}


	// import

	$new_address_counter = 0;
	$bad_address_counter = 0;
	$lc = 1; // line counter

	rewind($f);
	$row = fgetcsv($f, 1000, ","); // skip header
	while (FALSE != ($row = fgetcsv($f, 1000, ",")))
	{
        	$lc++;

		$volunteer_id = intval($row[array_search('volunteer_id', $valid_column_names)]);
		$new_address = intval($row[array_search('new_address', $valid_column_names)]);
		$move_date = $row[array_search('move_date', $valid_column_names)];
		$move_type = $row[array_search('move_type', $valid_column_names)];
		$bad_address = intval($row[array_search('bad_address', $valid_column_names)]);
		$address_error = $row[array_search('address_error', $valid_column_names)];
		$street = $row[array_search('street', $valid_column_names)];
		$city = $row[array_search('city', $valid_column_names)];
		$state = $row[array_search('state', $valid_column_names)];
		$postal_code = $row[array_search('postal_code', $valid_column_names)];

		// prepare some strings

		$volunteer = volunteer_get($volunteer_id, $errstr);
		if (!$volunteer)
		{
			die_message(MSG_SYSTEM_ERROR, "volunteer_get(): $errstr");
		}

		$wholeoldaddress = $volunteer['street'] . ", " . $volunteer['city'] . " " . $volunteer['state'] . " " . $volunteer['postal_code'];

		$wholenewaddress = "$street, $city $state $postal_code";
	
		$message = "";
		if (1 == $new_address)
		{
			$message = "NCOA indicates $move_type move from $wholeoldaddress to $wholenewaddress on date $move_date";
		}
		else if ($bad_address > 0)
		{
			$message = "NCOA indicates $address_error at $wholeoldaddress with move date $move_date";
		}

		// escape strings

		$address_error = $db->qstr($address_error, get_magic_quotes_gpc());
		// note: move_type was validated earlier
		$street = $db->qstr($street, get_magic_quotes_gpc());
		$city = $db->qstr($city, get_magic_quotes_gpc());
		$state = $db->qstr($state, get_magic_quotes_gpc());
		$postal_code = $db->qstr($postal_code, get_magic_quotes_gpc());
		$message = $db->qstr($message, get_magic_quotes_gpc());

		// prepare SQL

		$sql_volunteers = "UPDATE volunteers SET " .
			" street = $street , " .
			" city = $city , " .
			" state = $state , " .
			" postal_code = $postal_code " .
			" WHERE volunteer_id = $volunteer_id ";

		$sql_notes = "INSERT INTO notes " .
			" (dt, volunteer_id, message, quality, uid_added, uid_modified, dt_modified) " .
			" VALUES (now(), $volunteer_id, $message, 0, 0, 0, now()) ";
	
		// send SQL to database

		if (1 == $new_address)
		{
			// update volunteer address
			$result = $db->Execute($sql_volunteers);

			if ($result)
			{
				save_message(MSG_USER_NOTICE, _("Recorded."));
				$new_address_counter++;
			}
			else
			{
				save_message(MSG_SYSTEM_ERROR, _("Error adding data to database."), __FILE__, __LINE__, $sql_volunteers);
			}
		}
		
		if (1 == $new_address or $bad_address > 0)
		{
			// add note
			$result = $db->Execute($sql_notes);

			if ($result)
			{
				if ($bad_address > 0)
				{
					$bad_address_counter++;
				}
			}
			else
			{
				save_message(MSG_SYSTEM_ERROR, _("Error adding data to database."), __FILE__, __LINE__, $sql_notes);
			}

		}

	}

	// print results	

	echo "<table>\n";
	echo "<tr>\n";
	echo "<th>New addresses</th>\n";
	echo "<td>$new_address_counter</td>\n";
	echo "</tr>\n";
    	echo "<tr>\n";
	echo "<th>Bad addresses and potentially bad addresses</th>\n";
	echo "<td>$bad_address_counter</td>\n";
	echo "</tr>\n";
	echo "<tr>\n";
	echo "<th>Total addresses in file</th>\n";
	echo "<td>$lc</td>\n";
	echo "</tr>\n";
	echo "</table>\n";

    
} /* import_ncoa2() */


function import_ncoa()
{
    if (!has_permission(PC_ADMIN, PT_READ, NULL, NULL))
    {
	die_message(MSG_SYSTEM_ERROR, _("Insufficient permissions."), __FILE__, __LINE__);
    }    

    if (!empty($_POST['import_ncoa']) and 2 == $_POST['import_ncoa'])
    {
	import_ncoa2();
    }
    else
    {
	import_ncoa1();
    }
}
Return current item: Son of Service