1: <?php
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22: 23: 24: 25: 26: 27: 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:
54: $uploadedfile = F_upload_file('userfile', K_PATH_CACHE);
55: if ($uploadedfile !== false) {
56: $qimp = false;
57: switch ($type) {
58: case 1: {
59:
60: require_once('../code/tce_class_import_xml.php');
61: $qimp = new XMLQuestionImporter(K_PATH_CACHE.$uploadedfile);
62: break;
63: }
64: case 2: {
65:
66: $qimp = F_TSVQuestionImporter(K_PATH_CACHE.$uploadedfile);
67: break;
68: }
69: case 3: {
70:
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 ' '.K_NEWLINE;
96: echo '</div>'.K_NEWLINE;
97:
98: echo '<div class="row">'.K_NEWLINE;
99: echo '<span class="label"> </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:
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: 151: 152: 153: 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:
171: while ($qdata=fgetcsv($tsvfp, 0, "\t", '"')) {
172: if ($qdata === null) {
173: continue;
174: }
175:
176: switch ($qdata[0]) {
177: case 'M': {
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:
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:
192: if (!F_isAuthorizedUser(K_TABLE_MODULES, 'module_id', $m['module_id'], 'module_user_id')) {
193:
194: $current_module_id = 0;
195: } else {
196: $current_module_id = $m['module_id'];
197: }
198: } else {
199:
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:
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': {
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:
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:
244: $current_subject_id = $m['subject_id'];
245: } else {
246:
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:
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': {
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:
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:
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:
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:
338: $question_description = '_'.$question_description;
339: $strkeylimit = min($maxkey, ($strkeylimit + 1));
340: $stop--;
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:
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:
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': {
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:
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:
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:
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: }
471: }
472: return true;
473: }
474:
475:
476:
477:
478: