";
//csv file format is: name_last, name_first, dob, passcode
//read header file
$csvheader = fgetcsv($fhandle);
if(!($csvheader))
print "Error: CSV file was blank.
";
$sql_describe_subtable = "describe subject";
$describe_subtable = mysql_query($sql_describe_subtable) or die(mysql_error());
while($row_describe_subtable = mysql_fetch_assoc($describe_subtable)) {
$subTableFields[] = $row_describe_subtable['Field'];
//all fields that are of type blob are assumed to be encrypted
//this is a safe assumption for Ensemble
if(strcmp($row_describe_subtable['Type'],'blob') == 0)
$encryptedFields[] = $row_describe_subtable['Field'];
}
$maxKey = 0;
// loop through each header item and see if it exists as a field in the subject table
// If it exists, record the index of the header. If it doesn't exist in the subject table,
// ignore this header.
foreach($csvheader as $key=>$thisHeader) {
if(in_array($thisHeader,$subTableFields)) {
$headerIdxs[$thisHeader] = $key;
$maxKey = ($key > $maxKey)? $key : $maxKey;
}
}
//see if the required headers are in the included headers
$includedHeaders = array_keys($headerIdxs);
$headerRequirementMet = TRUE;
foreach($requiredHeaders as $thisRequired) {
if (!in_array($thisRequired,$includedHeaders)) {
print "Error: missing " . $thisRequired . " header.
";
$headerRequirementMet = FALSE;
}
}
$lineNo = 0;
//only process if we have found required headers
if($headerRequirementMet) {
$doImport = TRUE;
//if subject_id is not one of the included fields, it will be generated.
//Currently, only increment is supported
if(!in_array('subject_id',$includedHeaders)) {
//make sure that the current subID mechanism is "increment"
//only this method is currently supported, although generalizing to other methods should
//not be difficult. Then find the maximum subject ID and use the next available ID
if(!($SUBJECT_ID_GENERATOR == "increment")) {
print "Error: The current setting for SUBJECT_ID_GENERATOR in variables_config.php is not set to 'increment'
";
print "This is the only subject ID method that is currently supported.
";
$doImport = FALSE;
}
//find the maximum subject ID value
//note that it is costly to loop through all of them
//however, since subject IDs are stored as strings,
//simply taking a max() may not be accurate.
$maxSubID = 0;
$sql_get_sub_ids = "select subject_id from subject where 1";
$get_sub_ids = mysql_query($sql_get_sub_ids) or die(mysql_error());
while($row_sub_id = mysql_fetch_assoc($get_sub_ids)) {
$thisSubID = $row_sub_id['subject_id'];
//check if the subID is numeric, since we may have a table of mixed integer/string subject IDs.
if(is_numeric($thisSubID) && (intval($thisSubID) > intval($maxSubID)))
$maxSubID = $thisSubID;
}
$useSubID = $maxSubID;
//find the next integer for the specified offset and increment
do {
$useSubID = strval(intval($useSubID)+1);
} while( (($useSubID - $use_increment_offset) % $use_increment_increment) !== 0);
}
while((($csvline = fgetcsv($fhandle)) != NULL) && $doImport) {
$lineNo++;
//make sure that all fields are included in this line
//we do this by seeing if the length of the csvline is less than the maximum header key + 1
if(sizeof($csvline) < ($maxKey + 1)) {
print "Error: One or more fields are missing on line " . $lineNo . "
";
$doImport = FALSE;
break;
}
if(!preg_match("/^[0-9]{4}-[0-9]{2}-[0-9]{2}$/",$csvline[$headerIdxs['dob']])) {
print "Error: DOB field on line " .$lineNo . " doesn't appear to be in the format YYYY-MM-DD.
";
$doImport = FALSE;
break;
}
//if passphrase was included, make sure that they are unique.
//the passphrase field is not forced to be unique in the subject table
//however, when assigning passcodes, it is bad form to submit duplicate passcodes.
if(array_key_exists('passphrase',$headerIdxs)) {
$thisPasscode = $csvline[$headerIdxs['passphrase']];
$passCodeList[] = $thisPasscode;
$sql_check_passcode_duplicate = sprintf("select * from subject where aes_decrypt(`passphrase`,'%s') = '%s'",$enc_key,$thisPasscode);
$check_passcode_duplicate = mysql_query($sql_check_passcode_duplicate) or die(mysql_error());
if(mysql_num_rows($check_passcode_duplicate) > 0) {
print "Error: Passcode on line " . $lineNo . " is a duplicate of an existing passcode.
";
$doImport = FALSE;
break;
}
//we have already verified whether or not any passcodes are duplicated in the database
//now make sure that all passcodes in this CSV file are unique (unique passcodes are not enforced by MySQL subject table)
if(sizeof(array_unique($passCodeList)) != sizeof($passCodeList)) {
print "Error: Passcodes in this CSV file are not unique.
";
$doImport = FALSE;
}
} //if(array_key_exists('passphrase',$headerIdxs))
$fieldStrs = array();
foreach($headerIdxs as $thisHeaderName=>$thisHeaderIdx) {
//enclose the value in quotes if necessary
if(!preg_match('/^\'.*\'$/',$csvline[$thisHeaderIdx]))
$thisVal = "'" . $csvline[$thisHeaderIdx] . "'";
else
$thisVal = $csvline[$thisHeaderIdx];
if(in_array($thisHeaderName,$encryptedFields)) {
$fieldStrs[] = sprintf("aes_encrypt(%s,'%s')",$thisVal,$enc_key);
}
else {
$fieldStrs[] = $thisVal;
}
}
if(!in_array('subject_id',$includedHeaders)) {
$fieldStrs[] = $useSubID;
//find the next integer for the specified offset and increment
do {
$useSubID = strval(intval($useSubID)+1);
} while( (($useSubID - $use_increment_offset) % $use_increment_increment) !== 0);
}
if(!in_array('date_entered',$includedHeaders)) {
$fieldStrs[] = "'" . $dateToday . "'";
}
$newRecords[] = "(" . implode(",",$fieldStrs) . ")";
} //while
$insertSuccess = FALSE;
if($doImport) {
$valueString = implode(",",$newRecords);
if(!in_array('subject_id',$includedHeaders)) {
//add subject_id to header list if it wasn't in the import file
$includedHeaders[] = 'subject_id';
}
if(!in_array('date_entered',$includedHeaders)) {
$includedHeaders[] = 'date_entered';
}
$fieldList = implode(",",$includedHeaders);
$sql_insert_records = sprintf("insert into subject (%s) values %s;",$fieldList,$valueString);
$insertSuccess = mysql_query($sql_insert_records) or printf("SQL Error: %s
",mysql_error());
if($insertSuccess) {
print "Import was successful
";
$ignoredHeaders = array_diff($csvheader,$includedHeaders);
if(sizeof($ignoredHeaders) > 0)
printf("Ignored headers: %s",implode(", ",$ignoredHeaders));
}
else
print "Import failed.
";
}
else {
print "No data was imported.
";
}
} //if found headers
}
print "Subject Record Importer
";
print "This utility is used for pre-populating the Ensemble subject table with subject records.
";
print "Select a CSV file with, at minimum, the following columns: name_last, name_first, and dob. ";
print "Additional subject fields may also be submitted (e.g. email, phone1) but are not required. ";
print "You must include a header line that indicates the column order. Any header field that does not exist in the subject table will be ignored, ";
print "along with the corresponding values for that header. ";
print "Dates of birth should be in the format YYYY-MM-DD. Duplicate subject passphrases are also not allowed.
";
print "For example,
name_last, name_first, dob, passphrase
Smith, John, 1990-04-25, 123456789
";
printf("");
printf("Back to Main Menu
");
?>