array('page' => 2, 'image_names' => array()), 'q2' => array('page' => 2, 'image_names' => array('CRCL', 'circle', 'circles')), 'q3' => array('page' => 2, 'image_names' => array('CFS', 'CNF', 'CNFS')), 'q4' => array('page' => 2, 'image_names' => array('PICT', 'PCT')), 'q5A' => array('page' => 3, 'image_names' => array('A')), 'q5B' => array('page' => 3, 'image_names' => array('B')), 'q5C' => array('page' => 3, 'image_names' => array('C')), 'q5D' => array('page' => 3, 'image_names' => array('D')), 'q6' => array('page' => 3, 'image_names' => array()), 'q7' => array('page' => 3, 'image_names' => array('OTHR')), 'q8' => array('page' => 3, 'image_names' => array('BNS', 'BONUS')) ); $affected_records = ''; $skipped_records = ''; $add_all = FALSE; $replace_all = FALSE; echo "Database Server: "; /** * @var string */ $server = trim(fgets(STDIN, 128)); echo "Database Username: "; /** * @var string */ $username = trim(fgets(STDIN, 128), "\r\n"); echo "Database Password: "; /** * @var string */ $password = trim(fgets(STDIN, 128), "\r\n"); echo "Import File: "; /** * @var string */ $filename = trim(fgets(STDIN, 128), "\r\n"); if ($file = file_get_contents(dirname(realpath(__FILE__)) . '/' . $filename)) { if ($db_mysql = mysql_connect($server, $username, $password)) { mysql_query("SET NAMES 'utf8';"); mysql_select_db('items'); $hits = split("\r\n", $file); unset($file); $output = ''; foreach ($hits as $index => $hit) { if ($index == 0) { continue; } $output = $output . $hit; if ($index < count($hits)-1 && substr($hits[$index+1], 0, 5) !== 'http:') { $output = $output . "\r\n"; continue; } $data = split("\t", $output); for ($i = 0; $i <= 3; $i++) { preg_match('/(\w\w\w\d\d\d\d\w\d\d\w\d\w?)\/(\d{6})(\w{1,7})_P?(\d{1,2})/is', $data[$i], $img_info); $student_id = (int) $img_info[2]; $packet_code = substr($img_info[1], 2); $mysql_result = mysql_query("SELECT * FROM packets WHERE packets.deleted = 0 AND packets.semester = '" . substr($packet_code, 0, 1) . "' AND SUBSTRING(packets.packet_year, 3) = " . substr($packet_code, 1, 2) . " AND packets.state = " . substr($packet_code, 3, 2) . " AND UPPER(packets.school) = '" . substr($packet_code, 5, 1) . "' AND packets.grade = " . substr($packet_code, 6, 2) . " AND UPPER(packets.classroom) = '" . substr($packet_code, 8, 1) . "' AND packets.class = " . substr($packet_code, 9, 1) . " AND UPPER(packets.grp) = '" . substr($packet_code, 10, 1) . "';"); if ($mysql_result && mysql_num_rows($mysql_result) > 0) { $mysql_data = mysql_fetch_assoc($mysql_result); /** * @var integer * * Packet ID. This value is used when inserting data into the packet_data table. **/ $packet_id = $mysql_data['id']; } else { echo "\r\n\r\n*** Error Retreiving Packet Data ***"; echo "\r\nIndex $index\r\nImage: " , $data[$i] , "\r\nPacket: $packet_code"; exit(); } if ($student_id > $mysql_data['number']) { echo "\r\n\r\n*** Error Student ID Too Large ***"; echo "\r\nIndex $index\r\nImage: " , $data[$i] , "\r\nPacket: $packet_code"; echo "\r\nPacket ID: $packet_id\r\nItem Number: $item_num"; exit(); } $qnum = NULL; foreach ($questions as $question => $question_meta) { if (in_array($img_info[3], $question_meta['image_names'])) { $qnum = substr($question, 1); break; } } if ($qnum != NULL) { $item_num = ($img_info[4]-$questions['q'.$qnum]['page'])/2; $mysql_result = mysql_query("SELECT * FROM packet_items WHERE packet_ref = $packet_id AND sort = $item_num"); if ($mysql_result && mysql_num_rows($mysql_result) > 0) { $mysql_data = mysql_fetch_assoc($mysql_result); $item_id = $mysql_data['item_ref']; } else { echo "\r\n\r\n*** Error Retriving Packet Item ***"; echo "\r\nIndex $index\r\nImage: " , $data[$i] , "\r\nPacket: $packet_code"; echo "\r\nPacket ID: $packet_id\r\nItem Number: $item_num"; exit(); } } else { echo "\r\n\r\n*** Error Determining Question Number ***"; echo "\r\nIndex $index\r\nImage: " , $data[$i] , "\r\nPacket: $packet_code"; exit(); } $mysql_result = mysql_query("SELECT * FROM packet_data WHERE packet_ref = $packet_id AND student_ref = $student_id AND item_ref = $item_id AND qnum = '$qnum'"); $insert_data = FALSE; if ($mysql_result && mysql_num_rows($mysql_result) > 0) { $mysql_data = mysql_fetch_assoc($mysql_result); if (trim($data[$i+4]) != '') { if (trim($mysql_data['answer_comment']) != '' && !$replace_all) { echo "\r\n\r\n*** Error Student Comments Already Present ***"; echo "\r\nIndex $index\r\nImage: " , $data[$i] , "\r\nPacket: $packet_code"; echo "\r\nPacket ID: $packet_id\r\nStudent ID: $student_id\r\nItem ID: $item_id\r\nQuestion: $qnum"; echo "\r\nAnswer comments are already present, replace (Y|N)? "; $minput = NULL; $minput = trim(fgets(STDIN, 128), "\r\n"); if ($minput == 'Y' || $minput == 'YA') { $answer = $mysql_data['answer']; $answer_comment = $data[$i+4]; $insert_data = TRUE; if ($minput == 'YA') { $replace_all = TRUE; } } } else { $answer = $mysql_data['answer']; $answer_comment = $data[$i+4]; $insert_data = TRUE; } } } else { echo "\r\n\r\n*** Error Retriving Student Response ***"; echo "\r\nIndex $index\r\nImage: " , $data[$i] , "\r\nPacket: $packet_code"; echo "\r\nPacket ID: $packet_id\r\nStudent ID: $student_id\r\nItem ID: $item_id\r\nQuestion: $qnum"; if (trim($data[$i+4]) != '') { if ($qnum == 2 || $add_all) { $insert_data = TRUE; $answer = 'Y'; $answer_comment = $data[$i+4]; $insert_data = TRUE; } else { echo "\r\nAdd a record for this data (Y|N)? "; $minput = NULL; $minput = trim(fgets(STDIN, 128), "\r\n"); if ($minput == 'Y' || $minput == 'YA') { $answer = ''; $answer_comment = $data[$i+4]; $insert_data = TRUE; if ($minput == 'YA') { $add_all = TRUE; } } } } } if ($insert_data) { if (substr($answer_comment, 0, 1) == '"') { $answer_comment = substr($answer_comment, 1, strlen($answer_comment)-2); $answer_comment = str_replace('""', '"', $answer_comment); } if (strpos($answer_comment, '\u') > 0) { $answer_comment = preg_replace('/\\\u(\d\d\d\d)/ise', '"&#" . hexdec(ltrim("$1", "0")) . ";"', $answer_comment); //http://nadeausoftware.com/articles/2007/06/php_tip_how_decode_html_entities_web_page $answer_comment = mb_convert_encoding( $answer_comment, "utf-8", "HTML-ENTITIES" ); } mysql_query( 'REPLACE INTO packet_data (packet_ref, student_ref, item_ref, qnum, answer, answer_comment) VALUES (' . $packet_id . ',' . $student_id . ',' . $item_id . ',\'' . strtoupper($qnum) . '\',' . '\'' . mysql_real_escape_string($answer) . '\',\'' . mysql_real_escape_string($answer_comment) . '\'' . ');' ); $affected_records .= $data[$i] . " $packet_id $student_id $item_id $qnum\r\n"; } else { $skipped_records .= $data[$i] . " $packet_id $student_id $item_id $qnum\r\n"; } } $output = ''; } } } $file = fopen('mturk_import_results.txt', 'w'); fwrite($file, "Updated Records:\r\n" . $affected_records . "\r\n\r\nSkipped Records:\r\n" . $skipped_records); fclose($file); ?>