Magento newsletter mass import script

newsletter-import.php

<?php 
	ini_set('memory_limit', '3048M');
	ini_set('post_max_size', '32M');
	ini_set('max_input_time', 999);
	ini_set('max_execution_time',999);
	ini_set('display_errors',1);
	set_time_limit(0);
	error_reporting(E_ALL);

	// magento specific connection info
	require_once 'app/Mage.php';
	umask(0);
	Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID); 
	$db = Mage::getSingleton('core/resource')->getConnection('core/write');
	
	// fix the missing column names
	$metadata = $db->describeTable("newsletter_subscriber");
	$columnNames = array_keys($metadata);
	if (!in_array("subscriber_source", $columnNames)){
		$sql = "ALTER TABLE newsletter_subscriber ADD COLUMN subscriber_source varchar(20) DEFAULT NULL";
		$result = $db->query($sql);  
	}
	if (!in_array("subscriber_added", $columnNames)){
		$sql = "ALTER TABLE newsletter_subscriber ADD COLUMN subscriber_added date DEFAULT NULL";
		$result = $db->query($sql);		
	}
	
	// set and check that the e-mail file exists
	$emailerrors = "var/import/emailerrors.csv";
	if (!file_exists($emailerrors)) die("The file $emailerrors does not exist");
	
	// set and check that the e-mail file exists
	$emailimport = "var/import/newsletter.csv";
	$skip_csv_import = (file_exists($emailimport))? true : false;	

	// anything that is wrong goes here
	$emailissues = "var/import/output-".date("Ymd-His").".csv";
	
	/**
	 * This is for removing any odd characters
	 * from e-mail addresses.
	 * 
	 * @param string $in
	 */
	function fixtext($in){
		return preg_replace('/[^a-zA-Z0-9@+.]/', '', trim($in));		
	}
		
	/**
	 * This will fix an invalid e-mail address based
	 * on known spelling mistakes supplied in CSV form:
	 * --------------
	 * Wrong,Correct
	 * otmail,hotmail
	 * hotmai,hotmail
	 * --------------
	 * 
	 * @param $inemail string
	 * @param $emailerrors string
	 * @return string
	 */
	function fixemailaddress($inemail,$emailerrors){
				
		// deconstruct the email address
		$emailar = explode('@', strtolower($inemail));
		if (is_array($emailar)){
			$emailparts[] = $emailar[0];
			$emailparts[] = substr($emailar[1], 0, strpos($emailar[1], '.'));
			$emailparts[] = substr($emailar[1], strpos($emailar[1], '.'), strlen($emailar[1]));
			
			if (count($emailparts) == 3){
				// loop through supplied CSV
				if (($handle = fopen($emailerrors, "r")) !== FALSE) {
					while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
						// make sure we have enough supplied data
						if (count($data) == 2){
							$wrong = strtolower($data[0]);
							$right = strtolower($data[1]);
				
							// fix any that are mispelled
							if ($wrong == $emailparts[1]) $emailparts[1] = $right;
							if ($wrong == $emailparts[2]) $emailparts[2] = $right;
						}
					}
					fclose($handle);
				}
				
				// reconstruct the e-mail address
				return $emailparts[0]."@".$emailparts[1].$emailparts[2];
			} else {
				return $inemail;
			}
		} else {
			return $inemail;
		}
	}	
	
	$count = 0;
	$skip_count = 0;
	$invalid_addresses = array();
            
	if( isset($_POST['email']) && !empty($_POST['email'])){
        $emails = explode("\n",$_POST['email']);
        $count = count($emails);

        if ($count > 0){
	        foreach ($emails as $emailarr){
	        	$threeitems = explode(",",$emailarr);
	        	
	        	if (count($threeitems) == 3){
	        		$source = fixtext($threeitems[0]);
	        		$source = ucfirst($source);
	        		
	        		// fix and replace mispelled addresses 
	        		$email =  fixemailaddress(fixtext($threeitems[1]),$emailerrors);
					$is_valid = filter_var($email,FILTER_VALIDATE_EMAIL);
	        		if (!$is_valid){
	        			$invalid_addresses[] = $threeitems;
	        			continue;
	        		}
	        		
	        		// sort the date out
	        		$when = str_replace("/", "-", trim($threeitems[2]));
	        		$dateTime = new DateTime($when);
	        		$when =  date_format( $dateTime, 'Y-m-d H:i:s' );
	        			        		
					$query = "SELECT * FROM newsletter_subscriber WHERE subscriber_email = '$email'";  
					$result = $db->query($query);  
					if($result->fetchAll()) {
				     	// found an e-mail address
				     	$skip_count++;
					} else {
			     	     $db->query("INSERT INTO newsletter_subscriber (`subscriber_id` , `store_id` , `change_status_at` , `customer_id` , `subscriber_email` , `subscriber_status` , `subscriber_confirm_code`, `subscriber_source`, `subscriber_added`) VALUES (NULL,'1',NULL,'0','$email','1',NULL,'$source','$when')");
					}      		
	        	} else {
      				$count--;
	        	}
	        }
	        $success = 'Successfuly subscribed '.($count-$skip_count).' emails and skipped '.$skip_count;
        } else {
        	$success = "No e-mail submitted";
        }
    } elseif (isset($_POST['process']) && $_POST['process'] == 1){
    	
    	if (!file_exists($emailimport)) {
			die("The file $emailimport does not exist");	
		}    	
    	
    	if (($handle = fopen($emailimport, "r")) !== FALSE) {
			while (($threeitems = fgetcsv($handle, 1000, ",")) !== FALSE) {	        	
	        	if (count($threeitems) == 3){
	        		$count++;
	        		
	        		$source = fixtext($threeitems[0]);
	        		$source = ucfirst($source);
	        		
	        		// fix and replace mispelled addresses 
	        		$email =  fixemailaddress(fixtext($threeitems[1]),$emailerrors);
	        		$is_valid = filter_var($email,FILTER_VALIDATE_EMAIL);
	        		if (!$is_valid){
	        			$invalid_addresses[] = $threeitems;
	        			continue;
	        		}
	        		
	        		// sort the date out
	        		$when = str_replace("/", "-", trim($threeitems[2]));
	        		$dateTime = new DateTime($when);
	        		$when =  date_format( $dateTime, 'Y-m-d H:i:s' );
	        			        		
					$query = "SELECT * FROM newsletter_subscriber WHERE subscriber_email = '$email'";  
					$result = $db->query($query);  
					if($result->fetchAll()) {
				     	// found an e-mail address
				     	$skip_count++;
					} else {
			     	     $db->query("INSERT INTO newsletter_subscriber (`subscriber_id` , `store_id` , `change_status_at` , `customer_id` , `subscriber_email` , `subscriber_status` , `subscriber_confirm_code`, `subscriber_source`, `subscriber_added`) VALUES (NULL,'1',NULL,'0','$email','1',NULL,'$source','$when')");
					}      		
	        	} else {
      				$count--;
	        	}
			}
			
			$success = 'Successfuly subscribed '.($count-$skip_count).' emails and skipped '.$skip_count;
		} else {
			$success = "No e-mail submitted";
		}
    }
    
    // store resulting invalid data in a CSV
    if (count($invalid_addresses) > 0){
		$fp = fopen($emailissues, 'w');
		foreach ($invalid_addresses as $fields) {
		    fputcsv($fp, $fields);
		}
		fclose($fp);    	
    }
    
?>
    
<form action="" method="post">
    <p>Paste a new line separated list of source and email addresses to be subscribed to the magento newsletter.</p>
    <pre>
location1,[email protected],19/04/2011
location2,[email protected],31/05/2011 
    </pre>
    <textarea name="email" rows="15" cols="100"></textarea><br /><br />
    <?php if ($success) echo'<h2 style="color:green;">'.$success.'</h2>'; ?>
    <?php if ($skip_csv_import): ?>
    <input type="checkbox" name="process" id="process" value="1" /><label for="process">Process file "<?php echo $emailimport; ?>" instead.</label><br />
    <?php endif; ?>
    <button type="submit" name="submit">Subscribe Users</button>
    <!-- <button type="reset" name="reset">Start Again</button> -->
</form>