source-function-F_TSVQuestionImporter

  1: <?php
  2: //============================================================+
  3: // File name   : tce_import_questions.php
  4: // Begin       : 2006-03-12
  5: // Last Update : 2013-04-12
  6: //
  7: // Description : Import questions from an XML file.
  8: //
  9: // Author: Nicola Asuni
 10: //
 11: // (c) Copyright:
 12: //               Nicola Asuni
 13: //               Tecnick.com LTD
 14: //               www.tecnick.com
 15: //               info@tecnick.com
 16: //
 17: // License:
 18: //    Copyright (C) 2004-2013 Nicola Asuni - Tecnick.com LTD
 19: //    See LICENSE.TXT file for more information.
 20: //============================================================+
 21: 
 22: /**
 23:  * @file
 24:  * Import questions from an XML file to a selected subject.
 25:  * @package com.tecnick.tcexam.admin
 26:  * @author Nicola Asuni
 27:  * @since 2006-03-12
 28:  */
 29: 
 30: /**
 31:  */
 32: 
 33: require_once('../config/tce_config.php');
 34: 
 35: $pagelevel = K_AUTH_ADMIN_IMPORT;
 36: require_once('../../shared/code/tce_authorization.php');
 37: 
 38: $thispage_title = $l['t_question_importer'];
 39: require_once('../code/tce_page_header.php');
 40: require_once('../../shared/code/tce_functions_form.php');
 41: require_once('../../shared/code/tce_functions_tcecode.php');
 42: require_once('../../shared/code/tce_functions_auth_sql.php');
 43: 
 44: if (!isset($type) or (empty($type))) {
 45:     $type = 1;
 46: } else {
 47:     $type = intval($type);
 48: }
 49: 
 50: if (isset($menu_mode) and ($menu_mode == 'upload')) {
 51:     if ($_FILES['userfile']['name']) {
 52:         require_once('../code/tce_functions_upload.php');
 53:         // upload file
 54:         $uploadedfile = F_upload_file('userfile', K_PATH_CACHE);
 55:         if ($uploadedfile !== false) {
 56:             $qimp = false;
 57:             switch ($type) {
 58:                 case 1: {
 59:                     // standard TCExam XML format
 60:                     require_once('../code/tce_class_import_xml.php');
 61:                     $qimp = new XMLQuestionImporter(K_PATH_CACHE.$uploadedfile);
 62:                     break;
 63:                 }
 64:                 case 2: {
 65:                     // standard TCExam TSV format
 66:                     $qimp = F_TSVQuestionImporter(K_PATH_CACHE.$uploadedfile);
 67:                     break;
 68:                 }
 69:                 case 3: {
 70:                     // Custom TCExam XML format
 71:                     require_once('../code/tce_import_custom.php');
 72:                     $qimp = new CustomQuestionImporter(K_PATH_CACHE.$uploadedfile);
 73:                     break;
 74:                 }
 75:             }
 76:             if ($qimp) {
 77:                 F_print_error('MESSAGE', $l['m_importing_complete']);
 78:             }
 79:         }
 80:     }
 81: }
 82: echo '<div class="container">'.K_NEWLINE;
 83: 
 84: echo '<div class="tceformbox">'.K_NEWLINE;
 85: echo '<form action="'.$_SERVER['SCRIPT_NAME'].'" method="post" enctype="multipart/form-data" id="form_importquestions">'.K_NEWLINE;
 86: 
 87: echo '<div class="row">'.K_NEWLINE;
 88: echo '<span class="label">'.K_NEWLINE;
 89: echo '<label for="userfile">'.$l['w_upload_file'].'</label>'.K_NEWLINE;
 90: echo '</span>'.K_NEWLINE;
 91: echo '<span class="formw">'.K_NEWLINE;
 92: echo '<input type="hidden" name="MAX_FILE_SIZE" value="'.K_MAX_UPLOAD_SIZE.'" />'.K_NEWLINE;
 93: echo '<input type="file" name="userfile" id="userfile" size="20" title="'.$l['h_upload_file'].'" />'.K_NEWLINE;
 94: echo '</span>'.K_NEWLINE;
 95: echo '&nbsp;'.K_NEWLINE;
 96: echo '</div>'.K_NEWLINE;
 97: 
 98: echo '<div class="row">'.K_NEWLINE;
 99: echo '<span class="label">&nbsp;</span>'.K_NEWLINE;
100: echo '<div class="formw">'.K_NEWLINE;
101: echo '<fieldset class="noborder">'.K_NEWLINE;
102: 
103: echo '<legend title="'.$l['w_type'].'">'.$l['w_type'].'</legend>'.K_NEWLINE;
104: echo '<input type="radio" name="type" id="type_xml" value="1" title="TCExam XML Format"';
105: if ($type == 1) {
106:     echo ' checked="checked"';
107: }
108: echo ' />';
109: echo '<label for="type_xml">TCExam XML</label><br />'.K_NEWLINE;
110: 
111: echo '<input type="radio" name="type" id="type_tsv" value="2" title="TCExam TSV Format"'.K_NEWLINE;
112: if ($type == 2) {
113:     echo ' checked="checked"';
114: }
115: echo ' />';
116: echo '<label for="type_tsv">TCExam TSV</label>'.K_NEWLINE;
117: 
118: $custom_import = K_ENABLE_CUSTOM_IMPORT;
119: if (!empty($custom_import)) {
120:     echo '<input type="radio" name="type" id="type_custom" value="3" title="'.$custom_import.'"'.K_NEWLINE;
121:     if ($type == 3) {
122:         echo ' checked="checked"';
123:     }
124:     echo ' />';
125:     echo '<label for="type_custom">'.$custom_import.'</label>'.K_NEWLINE;
126: }
127: 
128: echo '</fieldset>'.K_NEWLINE;
129: echo '</div>'.K_NEWLINE;
130: echo '</div>'.K_NEWLINE;
131: 
132: echo '<div class="row">'.K_NEWLINE;
133: echo '<br />'.K_NEWLINE;
134: 
135: // show upload button
136: F_submit_button('upload', $l['w_upload'], $l['h_submit_file']);
137: 
138: echo '</div>'.K_NEWLINE;
139: echo '</form>'.K_NEWLINE;
140: echo '</div>'.K_NEWLINE;
141: 
142: echo '<div class="pagehelp">'.$l['hp_import_xml_questions'].'</div>'.K_NEWLINE;
143: echo '</div>'.K_NEWLINE;
144: 
145: require_once('../code/tce_page_footer.php');
146: 
147: // ---------------------------------------------------------------------
148: 
149: /**
150:  * Import questions from TSV file (tab delimited text).
151:  * The format of TSV is the same obtained by exporting data from TCExam interface.
152:  * @param $tsvfile (string) TSV (tab delimited text) file name
153:  * @return boolean TRUE in case of success, FALSE otherwise
154:  */
155: function F_TSVQuestionImporter($tsvfile)
156: {
157:     global $l, $db;
158:     require_once('../config/tce_config.php');
159:     require_once('../../shared/code/tce_functions_auth_sql.php');
160:     $qtype = array('S' => 1, 'M' => 2, 'T' => 3, 'O' => 4);
161:     $tsvfp = fopen($tsvfile, 'r');
162:     if ($tsvfp === false) {
163:         return false;
164:     }
165:     $current_module_id = 0;
166:     $current_subject_id = 0;
167:     $current_question_id = 0;
168:     $current_answer_id = 0;
169:     $questionhash = array();
170:     // for each row
171:     while ($qdata=fgetcsv($tsvfp, 0, "\t", '"')) {
172:         if ($qdata === null) {
173:             continue;
174:         }
175:         // get user data into array
176:         switch ($qdata[0]) {
177:             case 'M': { // MODULE
178:                 $current_module_id = 0;
179:                 if (!isset($qdata[2]) or empty($qdata[2])) {
180:                     break;
181:                 }
182:                 $module_enabled = intval($qdata[1]);
183:                 $module_name = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
184:                 // check if this module already exist
185:                 $sql = 'SELECT module_id
186:                     FROM '.K_TABLE_MODULES.'
187:                     WHERE module_name=\''.$module_name.'\'
188:                     LIMIT 1';
189:                 if ($r = F_db_query($sql, $db)) {
190:                     if ($m = F_db_fetch_array($r)) {
191:                         // get existing module ID
192:                         if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['module_id'], 'module_user_id')) {
193:                             // unauthorized user
194:                             $current_module_id = 0;
195:                         } else {
196:                             $current_module_id = $m['module_id'];
197:                         }
198:                     } else {
199:                         // insert new module
200:                         $sql = 'INSERT INTO '.K_TABLE_MODULES.' (
201:                             module_name,
202:                             module_enabled,
203:                             module_user_id
204:                             ) VALUES (
205:                             \''.$module_name.'\',
206:                             \''.$module_enabled.'\',
207:                             \''.$_SESSION['session_user_id'].'\'
208:                             )';
209:                         if (!$r = F_db_query($sql, $db)) {
210:                             F_display_db_error();
211:                         } else {
212:                             // get new module ID
213:                             $current_module_id = F_db_insert_id($db, K_TABLE_MODULES, 'module_id');
214:                         }
215:                     }
216:                 } else {
217:                     F_display_db_error();
218:                 }
219:                 break;
220:             }
221:             case 'S': { // SUBJECT
222:                 $current_subject_id = 0;
223:                 if ($current_module_id == 0) {
224:                     return;
225:                 }
226:                 if (!isset($qdata[2]) or empty($qdata[2])) {
227:                     break;
228:                 }
229:                 $subject_enabled = intval($qdata[1]);
230:                 $subject_name = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
231:                 $subject_description = '';
232:                 if (isset($qdata[3])) {
233:                     $subject_description = F_empty_to_null(F_tsv_to_text($qdata[3]));
234:                 }
235:                 // check if this subject already exist
236:                 $sql = 'SELECT subject_id
237:                     FROM '.K_TABLE_SUBJECTS.'
238:                     WHERE subject_name=\''.$subject_name.'\'
239:                         AND subject_module_id='.$current_module_id.'
240:                     LIMIT 1';
241:                 if ($r = F_db_query($sql, $db)) {
242:                     if ($m = F_db_fetch_array($r)) {
243:                         // get existing subject ID
244:                         $current_subject_id = $m['subject_id'];
245:                     } else {
246:                         // insert new subject
247:                         $sql = 'INSERT INTO '.K_TABLE_SUBJECTS.' (
248:                             subject_name,
249:                             subject_description,
250:                             subject_enabled,
251:                             subject_user_id,
252:                             subject_module_id
253:                             ) VALUES (
254:                             \''.$subject_name.'\',
255:                             '.$subject_description.',
256:                             \''.$subject_enabled.'\',
257:                             \''.$_SESSION['session_user_id'].'\',
258:                             '.$current_module_id.'
259:                             )';
260:                         if (!$r = F_db_query($sql, $db)) {
261:                             F_display_db_error();
262:                         } else {
263:                             // get new subject ID
264:                             $current_subject_id = F_db_insert_id($db, K_TABLE_SUBJECTS, 'subject_id');
265:                         }
266:                     }
267:                 } else {
268:                     F_display_db_error();
269:                 }
270:                 break;
271:             }
272:             case 'Q': { // QUESTION
273:                 $current_question_id = 0;
274:                 if (($current_module_id == 0) or ($current_subject_id == 0)) {
275:                     return;
276:                 }
277:                 if (!isset($qdata[5])) {
278:                     break;
279:                 }
280:                 $question_enabled = intval($qdata[1]);
281:                 $question_description = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
282:                 $question_explanation = F_empty_to_null(F_tsv_to_text($qdata[3]));
283:                 $question_type = $qtype[$qdata[4]];
284:                 $question_difficulty = intval($qdata[5]);
285:                 if (isset($qdata[6])) {
286:                     $question_position = F_zero_to_null($qdata[6]);
287:                 } else {
288:                     $question_position = F_zero_to_null(0);
289:                 }
290:                 if (isset($qdata[7])) {
291:                     $question_timer = intval($qdata[7]);
292:                 } else {
293:                     $question_timer = 0;
294:                 }
295:                 if (isset($qdata[8])) {
296:                     $question_fullscreen = intval($qdata[8]);
297:                 } else {
298:                     $question_fullscreen = 0;
299:                 }
300:                 if (isset($qdata[9])) {
301:                     $question_inline_answers = intval($qdata[9]);
302:                 } else {
303:                     $question_inline_answers = 0;
304:                 }
305:                 if (isset($qdata[10])) {
306:                     $question_auto_next = intval($qdata[10]);
307:                 } else {
308:                     $question_auto_next = 0;
309:                 }
310:                 // check if this question already exist
311:                 $sql = 'SELECT question_id
312:                     FROM '.K_TABLE_QUESTIONS.'
313:                     WHERE ';
314:                 if (K_DATABASE_TYPE == 'ORACLE') {
315:                     $sql .= 'dbms_lob.instr(question_description,\''.$question_description.'\',1,1)>0';
316:                 } elseif ((K_DATABASE_TYPE == 'MYSQL') and K_MYSQL_QA_BIN_UNIQUITY) {
317:                     $sql .= 'question_description=\''.$question_description.'\' COLLATE utf8_bin';
318:                 } else {
319:                     $sql .= 'question_description=\''.$question_description.'\'';
320:                 }
321:                 $sql .= ' AND question_subject_id='.$current_subject_id.' LIMIT 1';
322:                 if ($r = F_db_query($sql, $db)) {
323:                     if ($m = F_db_fetch_array($r)) {
324:                         // get existing question ID
325:                         $current_question_id = $m['question_id'];
326:                         continue;
327:                     }
328:                 } else {
329:                     F_display_db_error();
330:                 }
331:                 if (K_DATABASE_TYPE == 'MYSQL') {
332:                     // this section is to avoid the problems on MySQL string comparison
333:                     $maxkey = 240;
334:                     $strkeylimit = min($maxkey, strlen($question_description));
335:                     $stop = $maxkey / 3;
336:                     while (in_array(md5(strtolower(substr($current_subject_id.$question_description, 0, $strkeylimit))), $questionhash) and ($stop > 0)) {
337:                         // a similar question was already imported, so we change it a little bit to avoid duplicate keys
338:                         $question_description = '_'.$question_description;
339:                         $strkeylimit = min($maxkey, ($strkeylimit + 1));
340:                         $stop--; // variable used to avoid infinite loop
341:                     }
342:                     if ($stop == 0) {
343:                         F_print_error('ERROR', 'Unable to get unique question ID');
344:                         return;
345:                     }
346:                 }
347:                 $sql = 'START TRANSACTION';
348:                 if (!$r = F_db_query($sql, $db)) {
349:                     F_display_db_error();
350:                 }
351:                 // insert question
352:                 $sql = 'INSERT INTO '.K_TABLE_QUESTIONS.' (
353:                     question_subject_id,
354:                     question_description,
355:                     question_explanation,
356:                     question_type,
357:                     question_difficulty,
358:                     question_enabled,
359:                     question_position,
360:                     question_timer,
361:                     question_fullscreen,
362:                     question_inline_answers,
363:                     question_auto_next
364:                     ) VALUES (
365:                     '.$current_subject_id.',
366:                     \''.$question_description.'\',
367:                     '.$question_explanation.',
368:                     \''.$question_type.'\',
369:                     \''.$question_difficulty.'\',
370:                     \''.$question_enabled.'\',
371:                     '.$question_position.',
372:                     \''.$question_timer.'\',
373:                     \''.$question_fullscreen.'\',
374:                     \''.$question_inline_answers.'\',
375:                     \''.$question_auto_next.'\'
376:                     )';
377:                 if (!$r = F_db_query($sql, $db)) {
378:                     F_display_db_error(false);
379:                 } else {
380:                     // get new question ID
381:                     $current_question_id = F_db_insert_id($db, K_TABLE_QUESTIONS, 'question_id');
382:                     if (K_DATABASE_TYPE == 'MYSQL') {
383:                         $questionhash[] = md5(strtolower(substr($current_subject_id.$question_description, 0, $strkeylimit)));
384:                     }
385:                 }
386:                 $sql = 'COMMIT';
387:                 if (!$r = F_db_query($sql, $db)) {
388:                     F_display_db_error();
389:                 }
390: 
391:                 break;
392:             }
393:             case 'A': { // ANSWER
394:                 $current_answer_id = 0;
395:                 if (($current_module_id == 0) or ($current_subject_id == 0) or ($current_question_id == 0)) {
396:                     return;
397:                 }
398:                 if (!isset($qdata[4])) {
399:                     break;
400:                 }
401:                 $answer_enabled = intval($qdata[1]);
402:                 $answer_description = F_escape_sql($db, F_tsv_to_text($qdata[2]), false);
403:                 $answer_explanation = F_empty_to_null(F_tsv_to_text($qdata[3]));
404:                 $answer_isright = intval($qdata[4]);
405:                 if (isset($qdata[5])) {
406:                     $answer_position = F_zero_to_null($qdata[5]);
407:                 } else {
408:                     $answer_position = F_zero_to_null(0);
409:                 }
410:                 if (isset($qdata[6])) {
411:                     $answer_keyboard_key = F_empty_to_null(F_tsv_to_text($qdata[6]));
412:                 } else {
413:                     $answer_keyboard_key = F_empty_to_null('');
414:                 }
415:                 // check if this answer already exist
416:                 $sql = 'SELECT answer_id
417:                     FROM '.K_TABLE_ANSWERS.'
418:                     WHERE ';
419:                 if (K_DATABASE_TYPE == 'ORACLE') {
420:                     $sql .= 'dbms_lob.instr(answer_description, \''.$answer_description.'\',1,1)>0';
421:                 } elseif ((K_DATABASE_TYPE == 'MYSQL') and K_MYSQL_QA_BIN_UNIQUITY) {
422:                     $sql .= 'answer_description=\''.$answer_description.'\' COLLATE utf8_bin';
423:                 } else {
424:                     $sql .= 'answer_description=\''.$answer_description.'\'';
425:                 }
426:                 $sql .= ' AND answer_question_id='.$current_question_id.' LIMIT 1';
427:                 if ($r = F_db_query($sql, $db)) {
428:                     if ($m = F_db_fetch_array($r)) {
429:                         // get existing subject ID
430:                         $current_answer_id = $m['answer_id'];
431:                     } else {
432:                         $sql = 'START TRANSACTION';
433:                         if (!$r = F_db_query($sql, $db)) {
434:                             F_display_db_error();
435:                         }
436:                         $sql = 'INSERT INTO '.K_TABLE_ANSWERS.' (
437:                             answer_question_id,
438:                             answer_description,
439:                             answer_explanation,
440:                             answer_isright,
441:                             answer_enabled,
442:                             answer_position,
443:                             answer_keyboard_key
444:                             ) VALUES (
445:                             '.$current_question_id.',
446:                             \''.$answer_description.'\',
447:                             '.$answer_explanation.',
448:                             \''.$answer_isright.'\',
449:                             \''.$answer_enabled.'\',
450:                             '.$answer_position.',
451:                             '.$answer_keyboard_key.'
452:                             )';
453:                         if (!$r = F_db_query($sql, $db)) {
454:                             F_display_db_error(false);
455:                             F_db_query('ROLLBACK', $db);
456:                         } else {
457:                             // get new answer ID
458:                             $current_answer_id = F_db_insert_id($db, K_TABLE_ANSWERS, 'answer_id');
459:                         }
460:                         $sql = 'COMMIT';
461:                         if (!$r = F_db_query($sql, $db)) {
462:                             F_display_db_error();
463:                         }
464:                     }
465:                 } else {
466:                     F_display_db_error();
467:                 }
468:                 break;
469:             }
470:         } // end of switch
471:     } // end of while
472:     return true;
473: }
474: 
475: //============================================================+
476: // END OF FILE
477: //============================================================+
478: