update-db-scorm-1.6.x-1.8.0.inc.php 60 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284
  1. <?php
  2. /* For licensing terms, see /license.txt */
  3. /**
  4. * Chamilo LMS
  5. * Script handling the migration between an old Dokeos platform (<1.8.0) to
  6. * setup the new database system (4 scorm tables inside the course's database)
  7. * @package chamilo.scorm
  8. * @author Yannick Warnier <ywarnier@beeznest.org>
  9. */
  10. /**
  11. * Include mandatory libraries
  12. */
  13. Log::notice('Entering file');
  14. require_once api_get_path(LIBRARY_PATH).'document.lib.php';
  15. require_once api_get_path(LIBRARY_PATH).'fileManage.lib.php'; //check_name_exists()
  16. require_once api_get_path(SYS_CODE_PATH).'newscorm/learnpath.class.php';
  17. require_once api_get_path(SYS_CODE_PATH).'newscorm/scorm.class.php';
  18. $loglevel = 0;
  19. // Get table prefix from $prefix variable declared in update-db-....inc.php
  20. $table_prefix = $prefix;
  21. $sys_course_path = $pathForm.'courses/';
  22. $upd_course_path = $proposedUpdatePath.'courses/';
  23. function my_get_time($time) {
  24. $matches = array();
  25. if (preg_match('/(\d{1,4}):(\d{2})(:(\d{2})(\.\d*)?)?/', $time, $matches)) {
  26. if (count($matches) == 3) {
  27. return ($matches[1] * 60) + ($matches[2]);
  28. } else {
  29. return ($matches[1] * 3600) + ($matches[2] * 60) + ($matches[4]);
  30. }
  31. }
  32. else return 0;
  33. }
  34. // Open log file
  35. $fh = fopen(api_get_path(SYS_ARCHIVE_PATH).'newscorm_'.time().'.log', 'w');
  36. $fh_revert = fopen(api_get_path(SYS_ARCHIVE_PATH).'newscorm_'.time().'_revert.log', 'w');
  37. $fh_res = fopen(api_get_path(SYS_ARCHIVE_PATH).'newscorm_'.time().'_res.log', 'w');
  38. fwrite($fh, "-- Recording course homepages links changes to enable reverting\n");
  39. fwrite($fh_revert, "-- Recording reverted course homepages links changes to enable reverting\n");
  40. fwrite($fh_res, "-- Recording resulting course homepages links changes\n");
  41. //echo "<html><body>";
  42. /**
  43. * New tables definition:
  44. */
  45. $new_lp = 'lp';
  46. $new_lp_view = 'lp_view';
  47. $new_lp_item = 'lp_item';
  48. $new_lp_item_view = 'lp_item_view';
  49. $new_lp_type = 'lp_type';
  50. $max_dsp_lp = 0;
  51. $courses_list = array();
  52. $courses_id_list = array();
  53. $courses_id_full_table_prefix_list = array();
  54. $courses_dir_list = array();
  55. Database::select_db($dbNameForm);
  56. $sql = "SELECT * FROM course";
  57. $res = Database::query($sql);
  58. while ($row = Database::fetch_array($res)) {
  59. $course_pref = $table_prefix;
  60. if ($singleDbForm) {
  61. $dbname = $_configuration['main_database'].'.'.$course_pref.$row['db_name'].'_';
  62. } else {
  63. $dbname = $row['db_name'].'.'.$course_pref;
  64. }
  65. $courses_list[] = $row['db_name'];
  66. $courses_id_list[$row['code']] = $row['db_name'];
  67. $courses_id_full_table_prefix_list[$row['code']] = $dbname;
  68. $courses_dir_list[$row['code']] = $row['directory'];
  69. }
  70. if ($loglevel > 0) { Log::notice("Tables created/deleted for all courses"); }
  71. /**
  72. * The migration needs to take all data from the original learnpath tables and add them to the new
  73. * lp, lp_view, lp_item and lp_item_view tables
  74. */
  75. // MIGRATING LEARNPATHS
  76. // Test only one course
  77. foreach ($courses_id_full_table_prefix_list as $course_code => $db) {
  78. if (strlen($courses_id_list[$course_code]) > 40) {
  79. Log::error('Database '.$courses_id_list[$course_code].' is too long, skipping');
  80. continue;
  81. }
  82. $incoherences = 0;
  83. if ($loglevel > 0) { Log::notice("Now starting migration of learnpath tables from $db database..."); }
  84. $lp_doc = $db.TABLE_DOCUMENT;
  85. $lp_main = $db.TABLE_LEARNPATH_MAIN;
  86. $lp_ids = array();
  87. $lp_user = $db.TABLE_LEARNPATH_USER;
  88. $lp_users = array();
  89. $lp_chap = $db.TABLE_LEARNPATH_CHAPTER;
  90. $parent_chaps = array();
  91. $lp_chap_items = array();
  92. $ordered_chaps = array();
  93. $lp_item = $db.TABLE_LEARNPATH_ITEM;
  94. $lp_items = array();
  95. $lp_ordered_items = array();
  96. $parent_lps = array(); //keeps a track of chapter's learnpath ids
  97. $my_new_lp = $db.$new_lp;
  98. $my_new_lp_item = $db.$new_lp_item;
  99. $my_new_lp_view = $db.$new_lp_view;
  100. $my_new_lp_item_view = $db.$new_lp_item_view;
  101. // Migrate learnpaths
  102. $sql_test = "SELECT * FROM $my_new_lp";
  103. $res_test = Database::query($sql_test);
  104. $sql_lp = "SELECT * FROM $lp_main";
  105. if ($loglevel > 1) { Log::notice("$sql_lp"); }
  106. $res_lp = Database::query($sql_lp);
  107. if (!$res_lp or !$res_test) {
  108. if ($loglevel > 1) {
  109. Log::error("+++Problem querying DB $lp_main+++ skipping (".Database::error().")");
  110. if (!$res_test) {
  111. Log::error("This might be due to no existing table in the destination course");
  112. }
  113. }
  114. continue;
  115. }
  116. $dsp_ord = 1;
  117. while ($row = Database::fetch_array($res_lp)) {
  118. //echo "Treating lp id : ".$row['learnpath_id']."<br />\n";
  119. $ins_lp_sql = "INSERT INTO $my_new_lp (lp_type,name,description,display_order,content_maker) " .
  120. "VALUES (1," .
  121. "'".Database::escape_string($row['learnpath_name'])."'," .
  122. "'".Database::escape_string($row['learnpath_description'])."',$dsp_ord,'Dokeos')";
  123. $ins_lp_res = Database::query($ins_lp_sql);
  124. $in_id = Database::insert_id();
  125. if (!$in_id) die('Could not insert lp: '.$ins_lp_sql);
  126. $lp_ids[$row['learnpath_id']] = $in_id;
  127. $dsp_ord++;
  128. $max_dsp_lp = $dsp_ord;
  129. }
  130. //echo "<pre>lp_ids:".print_r($lp_ids,true)."</pre>\n";
  131. // MIGRATING LEARNPATH CHAPTERS
  132. $sql_lp_chap = "ALTER TABLE $lp_chap ADD INDEX ( parent_chapter_id, display_order )";
  133. $res_lp_chap = Database::query($sql_lp_chap);
  134. $sql_lp_chap = "SELECT * FROM $lp_chap ORDER BY parent_chapter_id, display_order";
  135. //echo "$sql_lp_chap<br />\n";
  136. $res_lp_chap = Database::query($sql_lp_chap);
  137. while ($row = Database::fetch_array($res_lp_chap)) {
  138. //echo "Treating chapter id : ".$row['id']."<br />\n";
  139. //TODO: Build path for this chapter (although there is no real path for any chapter)
  140. //TODO: Find out how to calculate the "next_item_id" with the "ordre" field
  141. $my_lp_item = $my_new_lp_item;
  142. $myname = Database::escape_string($row['chapter_name']);
  143. $mydesc = Database::escape_string($row['chapter_description']);
  144. $ins_lp_sql = "INSERT INTO $my_new_lp_item (" .
  145. "lp_id," .
  146. "item_type," .
  147. "title," .
  148. "description," .
  149. "path, " .
  150. "display_order, " .
  151. "next_item_id) " .
  152. "VALUES (" .
  153. "'".$lp_ids[$row['learnpath_id']]."'," . //insert new learnpath ID
  154. "'dokeos_chapter'," .
  155. "'".$myname."'," .
  156. "'".$mydesc."'," .
  157. "''," .
  158. $row['display_order'].", " .
  159. "123456" .
  160. ")";
  161. //echo $ins_lp_sql."<br />\n";
  162. $ins_res = Database::query($ins_lp_sql);
  163. $in_id = Database::insert_id();
  164. //echo "&nbsp;&nbsp;Inserted item $in_id<br />\n";
  165. if (!$in_id) die('Could not insert lp: '.$ins_sql);
  166. $parent_chaps[$row['id']] = $row['parent_chapter_id'];
  167. $lp_chap_items[$row['id']] = $in_id;
  168. $parent_lps[$row['id']] = $row['learnpath_id'];
  169. $ordered_chaps[$row['parent_chapter_id']][$row['display_order']] = $in_id;
  170. $lp_chaps_list[$row['learnpath_id']][] = $in_id;
  171. }
  172. //echo "<pre>parent_lps:".print_r($parent_lps,true)."</pre>\n";
  173. // Now one loop to update the parent_chapter_ids
  174. foreach ($parent_chaps as $old_chap => $old_parent_chap) {
  175. if ($old_parent_chap != 0) {
  176. $new_chap = $lp_chap_items[$old_chap];
  177. $new_parent = $lp_chap_items[$old_parent_chap];
  178. if (isset($new_chap) && $new_chap != '' && isset($new_parent) && $new_parent != '') {
  179. $sql_par_chap = "UPDATE $my_new_lp_item " .
  180. "SET parent_item_id = $new_parent " .
  181. "WHERE id = $new_chap";
  182. $res_par_chap = Database::query($sql_par_chap);
  183. }
  184. }
  185. }
  186. unset($parent_chaps);
  187. // Now one loop to set the next_item_id and the previous_item_id
  188. foreach ($ordered_chaps as $parent_chap) {
  189. $last = 0;
  190. foreach ($ordered_chaps[$parent_chap] as $order => $new_id) {
  191. $sql_upd_chaps = "UPDATE $my_new_lp_item " .
  192. "SET previous_item_id = $last " .
  193. "WHERE id = $new_id";
  194. $res_upd_chaps = Database::query($sql_upd_chaps);
  195. $next = 0;
  196. if (!empty($ordered_chaps[$parent_chap][$order + 1])) {
  197. $next = $ordered_chaps[$parent_chap][$order + 1];
  198. }
  199. $sql_upd_chaps = "UPDATE $my_new_lp_item " .
  200. "SET next_item_id = $next " .
  201. "WHERE id = $new_id";
  202. $res_upd_chaps = Database::query($sql_upd_chaps);
  203. $last = $new_id;
  204. }
  205. }
  206. unset($ordered_chaps);
  207. // Migrate learnpath_items
  208. // TODO: Define this array thanks to types defined in the learnpath_building scripts
  209. // TODO: Set order correctly
  210. $type_trans = array(
  211. 'document' => TOOL_DOCUMENT,
  212. 'exercise' => TOOL_QUIZ,
  213. 'forum' => TOOL_FORUM,
  214. 'Agenda' => TOOL_CALENDAR_EVENT,
  215. 'Ad_Valvas' => TOOL_ANNOUNCEMENT,
  216. 'Link' => TOOL_LINK,
  217. 'Link _blank' => TOOL_LINK,
  218. 'Exercise' => TOOL_QUIZ,
  219. 'HotPotatoes'=> 'HotPotatoes',
  220. 'Forum' => TOOL_FORUM,
  221. 'Thread' => TOOL_THREAD,
  222. 'Topic' => TOOL_THREAD,
  223. 'Post' => TOOL_POST,
  224. 'Document' => TOOL_DOCUMENT,
  225. 'Assignments'=> 'Assignments',
  226. 'Dropbox' => TOOL_DROPBOX,
  227. 'Introduction_text'=> 'Introduction_text',
  228. 'Course_description' => TOOL_COURSE_DESCRIPTION,
  229. 'Groups' => TOOL_GROUP,
  230. 'Users' => TOOL_USER,
  231. //'chapter' => 'dokeos_chapter', Chapters should all be in learnpath_chapter, no matter the nesting level
  232. );
  233. // MIGRATING LEARNPATH ITEMS
  234. $sql_lp_item = "ALTER TABLE $lp_item ADD INDEX ( chapter_id, display_order)";
  235. $res_lp_item = Database::query($sql_lp_item);
  236. $sql_lp_item = "SELECT * FROM $lp_item ORDER BY chapter_id, display_order";
  237. //echo "$sql_lp_item<br />\n";
  238. $res_lp_item = Database::query($sql_lp_item);
  239. while ($row = Database::fetch_array($res_lp_item)) {
  240. //echo "Treating chapter ".$row['chapter_id'].", item ".$row['id']."<br />\n";
  241. $type = $type_trans[$row['item_type']];
  242. $ref = $row['item_id'];
  243. //TODO: Build item path
  244. //TODO: Calculate "next_item_id" with the "ordre" field
  245. // Prepare prereqs
  246. // Prerequisites in Dokeos 1.6 is only authorised on previous items, so
  247. // We know that we are gonna talk about an item that has already been passed
  248. // through here - if none found, print message
  249. $prereq_id = '';
  250. if (!empty($row['prereq_id'])) {
  251. switch ($row['prereq_type']) {
  252. case 'c':
  253. // chapter-type prereq
  254. $prereq_id = $lp_chap_items[$row['prereq_id']];
  255. if (empty($prereq_id) && $loglevel > 1) { Log::error("Could not find prereq chapter ".$row['prereq_id']); }
  256. break;
  257. case 'i':
  258. default:
  259. // item type prereq
  260. $prereq_id = $lp_items[$parent_lps[$row['chapter_id']]][$row['prereq_id']];
  261. if (empty($prereq_id) && $loglevel > 1) { Log::error("Could not find prereq item ".$row['prereq_id']); }
  262. break;
  263. }
  264. }
  265. $my_parent_id = 0;
  266. if (isset($lp_chap_items[$row['chapter_id']])) {
  267. $my_parent_id = $lp_chap_items[$row['chapter_id']];
  268. }
  269. $title = '';
  270. if (empty($row['title']) && $type == 'Document' && !empty($row['item_id'])) {
  271. $my_sql_doctitle = "SELECT title FROM $lp_doc WHERE id = ".$row['item_id'];
  272. $my_res_doctitle = Database::query($my_sql_doctitle);
  273. if ($row_doctitle = Database::fetch_array($my_res_doctitle)) {
  274. $title = $row_doctitle['title'];
  275. } else {
  276. $title = '-';
  277. }
  278. } else {
  279. $title = $row['title'];
  280. }
  281. if (isset($lp_ids[$parent_lps[$row['chapter_id']]])) {
  282. // If there is a parent learnpath
  283. $ins_lp_sql = "INSERT INTO $my_new_lp_item (" .
  284. "lp_id," .
  285. "item_type," .
  286. "ref, " .
  287. "title," .
  288. "description," .
  289. "path, " .
  290. "parent_item_id," .
  291. "prerequisite," .
  292. "display_order" .
  293. ") VALUES (" .
  294. "'".$lp_ids[$parent_lps[$row['chapter_id']]]."'," . // Insert new learnpath ID
  295. "'$type'," .
  296. "'$ref', " .
  297. "'".Database::escape_string($row['title'])."'," .
  298. "'".Database::escape_string($row['description'])."'," .
  299. "'$ref'," .
  300. "".$my_parent_id."," .
  301. "'$prereq_id'," .
  302. $row['display_order']." " .
  303. ")";
  304. $ins_res = Database::query($ins_lp_sql);
  305. $in_id = Database::insert_id();
  306. //echo "&nbsp;&nbsp;Inserted item $in_id (".$row['title'].")<br />\n";
  307. if (!$in_id) die('Could not insert lp_item: '.$ins_sql);
  308. $lp_items[$parent_lps[$row['chapter_id']]][$row['id']] = $in_id;
  309. $lp_ordered_items[$parent_lps[$row['chapter_id']]][$row['chapter_id']][] = $in_id;
  310. }
  311. }
  312. //echo "<pre>lp_items:".print_r($lp_items,true)."</pre>\n";
  313. // Complete next_item_id field by going through the new table and looking at parent_id and display_order
  314. $order_sql = "ALTER TABLE $my_new_lp_item ADD INDEX (lp_id, parent_item_id, display_order)";
  315. $order_res = Database::query($order_sql);
  316. $order_sql = "SELECT * FROM $my_new_lp_item ORDER by lp_id ASC, parent_item_id ASC, display_order ASC";
  317. //echo "$order_sql<br />\n";
  318. $order_res = Database::query($order_sql);
  319. $order_item = array(); //this will contain a sequential list of item_id's, thus allowing to give a simple way to get next id...
  320. $lp_id = 0;
  321. //echo "<pre>";
  322. while ($row = Database::fetch_array($order_res)) {
  323. //print_r($row);
  324. if ($row['lp_id'] != $lp_id) {
  325. // Apply changes to the database and clean tool arrays
  326. $last = 0;
  327. foreach ($order_item as $order_id => $item_id) {
  328. $next = 0;
  329. if (!empty($order_item[$order_id+1])) {
  330. $next = $order_item[$order_id+1];
  331. }
  332. $upd = "UPDATE $my_new_lp_item " .
  333. "SET next_item_id = ".$next."," .
  334. " previous_item_id = ".$last." " .
  335. "WHERE id = ".$item_id;
  336. //echo "$upd<br />\n";
  337. Database::query($upd);
  338. $last = $item_id;
  339. }
  340. $order_item = array();
  341. $lp_id = $row['lp_id'];
  342. $order_item[] = $row['id'];
  343. } else {
  344. $order_item[] = $row['id'];
  345. }
  346. }
  347. // Process the last LP stack
  348. $last = 0;
  349. foreach ($order_item as $order_id => $item_id) {
  350. $next = 0;
  351. if (!empty($order_item[$order_id + 1])) {
  352. $next = $order_item[$order_id + 1];
  353. }
  354. $upd = "UPDATE $my_new_lp_item " .
  355. "SET next_item_id = ".$next."," .
  356. " previous_item_id = ".$last." " .
  357. "WHERE id = ".$item_id;
  358. //echo "$upd<br />\n";
  359. Database::query($upd);
  360. $last = $item_id;
  361. }
  362. //echo "</pre>\n";
  363. // MIGRATING THE learnpath_user TABLE (results)
  364. $mysql = "ALTER TABLE $my_new_lp_item_view ADD INDEX (lp_view_id)";
  365. $myres = Database::query($mysql);
  366. $sql_lp_user = "ALTER TABLE $lp_user ADD INDEX (user_id, learnpath_id, learnpath_item_id)";
  367. $res_lp_user = Database::query($sql_lp_user);
  368. $sql_lp_user = "SELECT * FROM $lp_user ORDER BY user_id, learnpath_id, learnpath_item_id";
  369. //echo "$sql_lp_user<br />\n";
  370. $res_lp_user = Database::query($sql_lp_user);
  371. $user_id = 0;
  372. $learnpath_id = 0;
  373. $lp_view = 0;
  374. while ($row = Database::fetch_array($res_lp_user)) {
  375. if ($row['user_id']!=$user_id OR $row['learnpath_id']!=$learnpath_id) { //the user has changed or this is the first
  376. // Insert a new lp_view
  377. $last = 0;
  378. if (!empty($lp_chaps_list[$row['learnpath_id']][0])) {
  379. $last = $lp_chaps_list[$row['learnpath_id']][0];
  380. }
  381. if (empty($lp_ids[$row['learnpath_id']])) {
  382. // This can be ignored as it means there was an LP before, this user
  383. // used it, but now it's been removed
  384. //echo "Somehow we also miss a lp_ids[".$row['learnpath_id']."] here<br />\n";
  385. $incoherences ++;
  386. } else {
  387. $mylpid = $lp_ids[$row['learnpath_id']];
  388. $sql_ins_view = "INSERT INTO $my_new_lp_view(" .
  389. "lp_id," .
  390. "user_id," .
  391. "view_count," .
  392. "last_item" .
  393. ")VALUES(" .
  394. "".$mylpid."," . // new learnpath id
  395. "".$row['user_id']."," . // user IDs stay the same
  396. "1," .
  397. "".$last."" . // Use the first chapter from this learnpath
  398. ")";
  399. //echo $sql_ins_view;
  400. $res_ins_view = Database::query($sql_ins_view);
  401. $in_id = Database::insert_id();
  402. $user_id = $row['user_id'];
  403. $learnpath_id = $row['learnpath_id'];
  404. $lp_view = $in_id;
  405. }
  406. }
  407. // Insert the record into lp_item_view
  408. // TODO: fix the whole in here (missing one item at least)
  409. $my_new_lp_item_id = $lp_items[$learnpath_id][$row['learnpath_item_id']];
  410. if (empty($my_new_lp_item_id)) {
  411. // This can be ignored safely as it just means a user used a learnpath_item
  412. // before it was removed from items - maybe fix that in Dokeos?
  413. //echo "Somehow we miss lp_items[".$learnpath_id."][".$row['learnpath_item_id']."] here...<br />";
  414. $incoherences ++;
  415. } else {
  416. $start_time = 0;
  417. $my_time = my_get_time($row['time']);
  418. if ($my_time > 0) {
  419. $start_time = time() - $my_time;
  420. }
  421. $sql_ins_iv = "INSERT INTO $my_new_lp_item_view(" .
  422. "lp_item_id," .
  423. "lp_view_id," .
  424. "view_count," .
  425. "start_time," .
  426. "total_time," .
  427. "score," .
  428. "status" .
  429. ")VALUES(" .
  430. "".$lp_items[$learnpath_id][$row['learnpath_item_id']]."," .
  431. "".$lp_view."," .
  432. "1," .
  433. "$start_time," .
  434. "".$my_time."," .
  435. "".$row['score']."," .
  436. "'".$row['status']."'" .
  437. ")";
  438. //echo $sql_ins_iv;
  439. $res_ins_iv = Database::query($sql_ins_iv);
  440. }
  441. // UPDATE THE LP_VIEW progress
  442. $mysql = "SELECT count(distinct(lp_item_id)) FROM $my_new_lp_item_view WHERE lp_view_id = ".$lp_view." AND status IN ('passed','completed','succeeded','browsed','failed')";
  443. $myres = Database::query($mysql);
  444. $myrow = Database::fetch_array($myres);
  445. $completed = $myrow[0];
  446. $mylpid = $lp_ids[$row['learnpath_id']];
  447. $sql = "SELECT count(*) FROM $my_new_lp_item WHERE lp_id = '".$mylpid."'";
  448. $myres = Database::query($sql);
  449. $myrow = Database::fetch_array($myres);
  450. $total = $myrow[0];
  451. $progress = ((float)$completed / (float)$total) * 100;
  452. $progress = number_format($progress, 0);
  453. $sql = "UPDATE $my_new_lp_view SET progress = '$progress' WHERE id = '$lp_view'";
  454. $myres = Database::query($sql);
  455. }
  456. /**
  457. * Move prerequisites
  458. * TODO: Integrate prerequisites migration into learnpath_item migration
  459. */
  460. $msg = '';
  461. if ($incoherences > 0) {
  462. $msg = "(found $incoherences incoherences between views and items - ignored)";
  463. }
  464. /**
  465. * Migrate links on the homepage as well now (look into the TABLE_TOOL_LIST table and
  466. * update the links to newscorm/lp_controller.php?action=view&lp_id=x)
  467. * Only normal learnpaths were visible from the homepage so we only need to update here
  468. */
  469. // MIGRATING LEARNPATH LINKS ON COURSES HOMEPAGES
  470. $tbl_tool = $db.TABLE_TOOL_LIST;
  471. $sql_tool = "SELECT * FROM $tbl_tool WHERE image='scormbuilder.gif' AND link LIKE '%learnpath_handler%'";
  472. $res_tool = Database::query($sql_tool);
  473. while ($row_tool = Database::fetch_array($res_tool)) {
  474. $name = $row_tool['name'];
  475. $link = $row_tool['link'];
  476. // Get old lp_id from there
  477. $matches = array();
  478. if (preg_match('/learnpath_id=(\d+)$/', $link,$matches)) {
  479. $old_lp_id = $matches[1];
  480. $new_lp_id = $lp_ids[$old_lp_id];
  481. $sql_tool_upd = "UPDATE $tbl_tool " .
  482. "SET link='newscorm/lp_controller.php?action=view&lp_id=$new_lp_id' " .
  483. "WHERE id = ".$row_tool['id'];
  484. Log::notice('New LP - Migration - Updating tool table: '.$sql_tool_upd);
  485. // Make sure there is a way of retrieving which links were updated (to revert)
  486. fwrite($fh,$sql_tool_upd." AND link ='$link'");
  487. fwrite($fh_revert, "UPDATE $tbl_tool SET link='$link' WHERE id=".$row_tool['id']." AND link ='newscorm/lp_controller.php?action=view&lp_id=$new_lp_id';\n");
  488. //echo $sql_tool_upd." (and link='$link')<br />\n";
  489. $res_tool_upd = Database::query($sql_tool_upd);
  490. }
  491. }
  492. $tbl_tool = $db.TABLE_TOOL_LIST;
  493. $sql_tool = "SELECT * FROM $tbl_tool";
  494. $res_tool = Database::query($sql_tool);
  495. while ($row_tool = Database::fetch_array($res_tool)) {
  496. $link = $row_tool['link'];
  497. $matches = array();
  498. $pattern = '@scorm/showinframes\.php([^\s"\'&]*)(&|&amp;)file=([^\s"\'&]*)@';
  499. if (preg_match($pattern, $link, $matches)) {
  500. //echo "Found match for scorm link in $tbl_tool: $link<br />";
  501. if (!empty($matches[3]) && (strtolower(substr($matches[3], -15)) == 'imsmanifest.xml') && !is_file(realpath(urldecode($matches[3])))) {
  502. //echo "Removing link $link from tools<br />";
  503. $sql_tool_upd = "DELETE FROM $tbl_tool WHERE id = ".$row_tool['id'];
  504. Log::notice('New LP - Migration - Updating tool table (dead link): '.$sql_tool_upd);
  505. // Make sure there is a way of retrieving which links were updated (to revert)
  506. fwrite($fh, $sql_tool_upd." AND link ='$link'");
  507. fwrite($fh_revert, "INSERT INTO $tbl_tool (link) VALUES ('$link');\n");
  508. //echo $sql_tool_upd." (and link='$link')<br />\n";
  509. $res_tool_upd = Database::query($sql_tool_upd);
  510. }
  511. } else {
  512. //echo "No scorm link found in tool link $link<br />";
  513. }
  514. //flush();
  515. }
  516. /**
  517. * Update course description (intro page) to use new links instead of learnpath/learnpath_handler.php
  518. */
  519. $tbl_intro = $db.TABLE_TOOL_INTRO;
  520. $sql_i = "SELECT * FROM $tbl_intro WHERE id='course_homepage'";
  521. $res_i = Database::query($sql_i);
  522. //$link_to_course1 = 'scorm/scormdocument.php';
  523. while ($row_i = Database::fetch_array($res_i)) {
  524. $intro = $row_i['intro_text'];
  525. $update = 0;
  526. $out = array();
  527. if (preg_match_all('/claroline\/learnpath\/showinframes\.php([^\s"\']*)learnpath_id=(\d*)/', $intro, $out, PREG_SET_ORDER)) {
  528. foreach ($out as $results) {
  529. //echo "---> replace ".'/learnpath\/showinframes\.php([^\s"\']*)learnpath_id='.$results[2].'/ by newscorm/lp_controller.php'.$results[1].'action=view&lp_id='.$lp_ids[$results[2]];
  530. $intro = preg_replace('/claroline\/learnpath\/showinframes\.php([^\s"\']*)learnpath_id='.$results[2].'/','main/newscorm/lp_controller.php'.$results[1].'action=view&lp_id='.$lp_ids[$results[2]], $intro);
  531. }
  532. }
  533. if (preg_match_all('/claroline\/phpbb\/index.php/', $intro, $out, PREG_SET_ORDER)) {
  534. foreach ($out as $results) {
  535. $intro = preg_replace('/claroline\/phpbb\/index\.php([^\s"\']*)/','main/forum/index.php'.$results[1], $intro);
  536. }
  537. }
  538. if ($intrp != $row_i['intro_text']) {
  539. //echo "<pre>Replacing ".$row_i['intro_text']."\n by \n ".$intro."</pre><br />\n";
  540. $sql_upd = "update $tbl_intro set intro_text = '".Database::escape_string($intro)."' WHERE id = 'course_homepage' AND intro_text = '".Database::escape_string($row_i['intro_text'])."'";
  541. //echo $sql_upd."<br />\n";
  542. fwrite($fh,"$sql_upd\n");
  543. fwrite($fh_revert,"UPDATE $tbl_intro set intro_text = '".$row_i['intro_text']."' WHERE id = 'course_homepage' AND intro_text = '$intro';\n");
  544. fwrite($fh_res,"$intro\n");
  545. Database::query($sql_upd);
  546. }
  547. }
  548. if ($loglevel > 0) { Log::notice("Done!".$msg); }
  549. //flush();
  550. //ob_flush();
  551. }
  552. unset($lp_ids);
  553. unset($lp_users);
  554. unset($parent_chaps);
  555. unset($lp_chap_items);
  556. unset($ordered_chaps);
  557. unset($lp_items);
  558. unset($lp_ordered_items);
  559. unset($parent_lps);
  560. fwrite($fh, "-- Recording course homepages links changes for SCORM to enable reverting\n");
  561. fwrite($fh_revert, "-- Recording reverted course homepages links changes for SCORM to enable reverting\n");
  562. fwrite($fh_res, "-- Recording resulting course homepages links changes for SCORM\n");
  563. /**
  564. * SCORM
  565. * The migration needs to take all data from the scorm.scorm_main and scorm.scorm_sco_data tables
  566. * and add them to the new lp, lp_view, lp_item and lp_item_view tables.
  567. */
  568. if ($loglevel > 0) { Log::notice("Now starting migration of scorm tables from global SCORM database"); }
  569. $scorm_main = $dbScormForm.'.'.TABLE_SCORM_MAIN;
  570. $scorm_item = $dbScormForm.'.'.TABLE_SCORM_SCO_DATA;
  571. //$lp_main = Database::get_course_table(TABLE_LEARNPATH_MAIN,$db);
  572. $course_pref = $table_prefix;
  573. $lp_ids = array();
  574. $lp_item_ids = array();
  575. $lp_item_refs = array();
  576. $lp_course = array();
  577. $lp_course_code = array();
  578. $scorm_lp_paths = array();
  579. // Avoid empty dokeosCourse fields as they potentially break the rest
  580. Database::select_db($dbNameForm);
  581. $course_main = TABLE_MAIN_COURSE;
  582. $sql_crs = "SELECT * FROM $course_main WHERE target_course_code IS NULL";
  583. if ($loglevel > 0) { Log::notice("$sql_crs"); }
  584. $res_crs = Database::query($sql_crs);
  585. $num = Database::num_rows($res_crs);
  586. // Prepare an array that will contain course codes and for each course code a list of lps [by path prefixed by '/']
  587. $scorms = array();
  588. $course_code_swap = '';
  589. $scormdocuments_lps = array();
  590. while ($course_row = Database::fetch_array($res_crs)) {
  591. if ($loglevel > 0) { Log::notice("Now dealing with course ".$course_row['code']."..."); }
  592. // Check the validity of this new course
  593. $my_course_code = $course_row['code'];
  594. // Reinit the scormdocuments list
  595. //$scormdocuments_lps = array();
  596. $db_name = $courses_id_full_table_prefix_list[$my_course_code];
  597. if (strlen($courses_id_list[$course_code]) > 40) {
  598. Log::notice('Database '.$courses_id_list[$course_code].' is too long, skipping');
  599. continue;
  600. }
  601. //echo "Now processing database $db_name<br />";
  602. $tblscodoc = $db_name.TABLE_SCORMDOC;
  603. $sql_scodoc = "SELECT path FROM $tblscodoc WHERE path IS NOT NULL AND path != ''";
  604. if ($loglevel > 1) { Log::notice("$sql_scodoc"); }
  605. $res_scodoc = Database::query($sql_scodoc);
  606. while ($row_scodoc = Database::fetch_array($res_scodoc)) {
  607. // Check if there's more than one slash in total
  608. if (strpos($row_scodoc['path'], '/', 1) === false) {
  609. $tmp_path = $row_scodoc['path'];
  610. if ($loglevel > 1) { Log::notice("++Now opening $tmp_path"); }
  611. // Add a prefixing slash if there is none
  612. if (substr($tmp_path, 0, 1) != '/') {
  613. $tmp_path = '/'.$tmp_path;
  614. }
  615. // If the path is just a slash, empty it
  616. if ($tmp_path == '/') {
  617. $tmp_path = '';
  618. }
  619. // There is only one 'slash' sign at the beginning, or none at all, so we assume
  620. // it is a main directory that should be taken as path.
  621. $courses_dir = $sys_course_path.''.$courses_dir_list[$my_course_code].'/scorm'.$tmp_path;
  622. if (!is_dir($courses_dir)) {
  623. //echo "Scormdocument path $my_content_id: $tmp_path doesn't exist in ".$sys_course_path.$courses_dir_list[$my_course_code]."/scorm, skipping<br />\n";
  624. continue;
  625. // Avoid if contentTitle is not the name of an existing directory
  626. } elseif (!is_file($courses_dir."/imsmanifest.xml")) {
  627. // If the imsmanifest file was not found there
  628. if ($loglevel > 2) { Log::error(" !!imsmanifest.xml not found at scormdocument's $courses_dir/imsmanifest.xml, skipping"); }
  629. // Try subdirectories on one level depth
  630. if ($loglevel > 2) { Log::notice(" Trying subdirectories..."); }
  631. $dh = opendir($courses_dir);
  632. while ($entry = readdir($dh)) {
  633. if (substr($entry, 0, 1) != '.') {
  634. if (is_dir($courses_dir."/".$entry)) {
  635. if (is_file($courses_dir."/".$entry."/imsmanifest.xml")) {
  636. if ($loglevel > 2) { Log::notice(". .. and found $courses_dir/$entry/imsmanifest.xml!"); }
  637. if (!in_array($tmp_path."/".$entry."/imsmanifest.xml",$scormdocuments_lps)) {
  638. if ($loglevel > 2){ Log::notice(" Recording.<br />"); }
  639. $scormdocuments_lps[] = $tmp_path."/".$entry;
  640. }
  641. }
  642. }
  643. }
  644. }
  645. } else {
  646. if ($loglevel > 2) { Log::notice(" Found scormdocument $tmp_path in ".$sys_course_path.$courses_dir_list[$my_course_code]."/scorm, treating it."); }
  647. $scormdocuments_lps[] = $tmp_path;
  648. }
  649. }
  650. }
  651. // Because certain people with no admin skills had fun adding direct links to SCORM
  652. // from the courses introductions, we have to check for SCORM packages from there too...
  653. $tbl_intro = $db_name.TABLE_TOOL_INTRO;
  654. $sql_i = "SELECT * FROM $tbl_intro WHERE id='course_homepage'";
  655. //echo $sql_i;
  656. $res_i = Database::query($sql_i);
  657. //$link_to_course1 = 'scorm/scormdocument.php';
  658. while ($row_scodoc = Database::fetch_array($res_i)) {
  659. $intro = $row_scodoc['intro_text'];
  660. //echo $intro."<br />\n";
  661. $matches = array();
  662. $pattern = '@scorm/showinframes\.php([^\s"\']*)file=([^\s"\'&]*)@';
  663. if (preg_match_all($pattern, $intro, $matches, PREG_SET_ORDER)) {
  664. if (count($matches) < 1) {
  665. // Skip
  666. } else {
  667. //echo "Found matches in $tbl_intro<br />";
  668. foreach ($matches as $match) {
  669. //echo "Found match ".print_r($match,true)."<br />";
  670. $mymatch = urldecode($match[2]);
  671. $mymatch = str_replace($sys_course_path, $upd_course_path, $mymatch);
  672. if (!empty($mymatch) && (strtolower(substr($mymatch, -15)) == 'imsmanifest.xml') && is_file(realpath(urldecode($mymatch)))) {
  673. //echo $mymatch." seems ok<br />";
  674. // Found a new scorm course in the old directory
  675. $courses_dir = $upd_course_path.''.$courses_dir_list[$my_course_code].'/scorm';
  676. // Check if the file is in the current course path, otherwise just forget about it
  677. // as it would be too difficult to migrate
  678. //echo "Comparing $mymatch with $courses_dir<br />";
  679. if (strpos($mymatch, $courses_dir) !== false) {
  680. // Remove the course dir up to /scorm from this path
  681. $entry = substr($mymatch, strlen($courses_dir));
  682. // Remove the /imsmanifest.xml from the end of the path
  683. $entry = substr($entry, 0, -16);
  684. // If $entry was /var/www/dokeos/courses/ABC/scorm/tralala/imsmanifest.xml,
  685. // $entry is now /tralala
  686. //echo "Checking if manifest exists in ".$courses_dir.$entry."/imsmanifest.xml<br />";
  687. if (is_file($courses_dir.$entry."/imsmanifest.xml")) {
  688. //echo "found $courses_dir/$entry/imsmanifest.xml!<br />";
  689. if ($loglevel > 2) { Log::notice(". .. and found $courses_dir/$entry/imsmanifest.xml!"); }
  690. if (!in_array($entry."/imsmanifest.xml", $scormdocuments_lps)) {
  691. if ($loglevel > 2) { Log::notice(" Recording.<br />"); }
  692. //echo "Recording $entry<br />";
  693. $scormdocuments_lps[] = $entry;
  694. }
  695. } else {
  696. //echo "Manifest does not exist in ".$courses_dir.$entry."/imsmanifest.xml<br />";
  697. }
  698. }
  699. }
  700. }
  701. }
  702. }
  703. }
  704. // Prepare the new course's space in the scorms array
  705. $scorms[$my_course_code] = array();
  706. $sql_paths = "SELECT * FROM $scorm_main WHERE dokeosCourse = '".$my_course_code."'";
  707. if ($loglevel > 0) { Log::notice("$sql_paths"); }
  708. $res_paths = Database::query($sql_paths);
  709. $num = Database::num_rows($res_paths);
  710. while ($scorm_row = Database::fetch_array($res_paths)) {
  711. // Check if this is a new course
  712. $my_content_id = $scorm_row['contentId'];
  713. $my_path = $scorm_row['contentTitle'];
  714. if (substr($my_path, 0, 1) != '/') {
  715. $my_path = '/'.$my_path;
  716. }
  717. if ($my_path == '/') {
  718. $my_path = '';
  719. }
  720. if ($loglevel > 1) { Log::notice("++++Now opening $my_path"); }
  721. if (!is_dir($courses_dir = $sys_course_path.''.$courses_dir_list[$my_course_code].'/scorm'.$my_path)) {
  722. if ($loglevel > 1) { Log::notice("Path $my_content_id: $my_path doesn't exist in ".$sys_course_path.$courses_dir_list[$my_course_code]."/scorm, skipping"); }
  723. continue;
  724. // Avoid if contentTitle is not the name of an existing directory
  725. } elseif (!is_file($sys_course_path.$courses_dir_list[$my_course_code].'/scorm'.$my_path."/imsmanifest.xml")) {
  726. if ($loglevel > 1) { Log::notice("!!imsmanifest.xml not found at ".$sys_course_path.$courses_dir_list[$my_course_code].'/scorm'.$my_path."/imsmanifest.xml, skipping"); }
  727. continue;
  728. } else {
  729. if ($loglevel > 1) { Log::notice("Found $my_path in ".$sys_course_path.$courses_dir_list[$my_course_code]."/scorm".$mypath."/imsmanifest.xml, keeping it."); }
  730. $scorms[$my_course_code][$my_path] = $my_content_id;
  731. }
  732. }
  733. // Check if all the lps from scormdocuments_lps are already in the course array,
  734. // otherwise add them (and set ID of 0 so no tracking will be available)
  735. foreach ($scormdocuments_lps as $path) {
  736. if (!in_array($path,array_keys($scorms[$my_course_code]))) {
  737. // Add it (-1 means no ID)
  738. if ($loglevel > 1) { Log::notice("** Scormdocument path $path wasn't recorded yet. Added."); }
  739. $scorms[$my_course_code][$path] = -1;
  740. }
  741. }
  742. $course_code_swap = $my_course_code;
  743. unset($scormdocuments_lps);
  744. }
  745. //echo "<pre>courses_id_list: ".print_r($courses_id_list,true)."</pre>\n";
  746. $my_count = 0;
  747. foreach ($scorms as $mycourse => $my_paths) {
  748. $my_count += count($my_paths);
  749. }
  750. if ($loglevel > 0) { Log::notice("---- Scorms array now contains ".$mycount." paths to migrate. Starting migration..."); }
  751. /**
  752. * Looping through the SCO_MAIN table for SCORM learnpath attached to courses
  753. * Order by course to try and reuse the maximum data
  754. */
  755. $i_count = 0;
  756. foreach ($scorms as $my_course_code => $paths_list) {
  757. $max_dsp_lp = 0;
  758. $course_lp_done = array();
  759. $db_name = $courses_id_full_table_prefix_list[$my_course_code];
  760. foreach ($paths_list as $my_path => $old_id) {
  761. if ($loglevel > 1) { Log::notice("Migrating lp $my_path from course $my_course_code..."); }
  762. $i_count ++;
  763. //Log::notice('New LP - Migration script - Content '.$i_count.' on '.$num.' (course '.$scorm['dokeosCourse'].')');
  764. // Check whether there is no embedded learnpaths into other learnpaths (one root-level and another embedded)
  765. $embedded = false;
  766. foreach ($course_lp_done as $tmp_lp) {
  767. if (empty($tmp_lp)) {
  768. $tmp_lp = '/'; // Allows finding the lp as a subitem, otherwise strstr returns false
  769. }
  770. if (strstr($my_path, $tmp_lp) === false) {
  771. // Let it be
  772. } else {
  773. // This lp is embedded inside another lp who's imsmanifest exists, so prevent from parsing
  774. if ($loglevel > 1) { Log::notice("LP $my_path is embedded into $tmp_lp, ignoring..."); }
  775. $embedded = true;
  776. continue;
  777. }
  778. }
  779. if ($embedded) {
  780. continue;
  781. }
  782. $course_lp_done[] = $my_path;
  783. //echo "<pre>scorm row: ".print_r($scorm,true)."</pre>\n";
  784. $my_content_id = $old_id;
  785. $my_path = $my_path;
  786. $my_name = basename($my_path);
  787. if ($loglevel > 1) { Log::notice("Try importing LP $my_path from imsmanifest first as it is more reliable"); }
  788. // Setup the ims path (path to the imsmanifest.xml file)
  789. //echo "Looking for course with code ".$lp_course_code[$my_content_id]." (using $my_content_id)<br />\n";
  790. //$courses_dir = $sys_course_path.$courses_dir_list[$my_course_code];
  791. $courses_dir = $upd_course_path.$courses_dir_list[$my_course_code];
  792. $sco_path_temp = ($my_path == '/') ? '' : $my_path;
  793. $sco_middle_path = (empty($sco_path_temp) ? '' : (substr($sco_path_temp, 0, 1) == '/') ? substr($sco_path_temp, 1).'/' : $sco_path_temp.'/'); //same thing as sco_path_temp but with reversed slashes
  794. $ims = $courses_dir.'/scorm'.$sco_path_temp.'/imsmanifest.xml';
  795. if (is_file($ims)){
  796. //echo "Path $ims exists, importing...(line ".__LINE__.")<br />";
  797. $oScorm = new scorm();
  798. // Check whether imsmanifest.xml exists at this location. If not, ignore the imsmanifest.
  799. // That should have been done before already, now.
  800. if ($loglevel > 1) { Log::notice("Found imsmanifest ($ims), importing..."); }
  801. if (!empty($sco_middle_path)) { $oScorm->subdir = $sco_middle_path; } //this sets the subdir for the scorm package inside the scorm dir
  802. // Parse manifest file
  803. $manifest = $oScorm->parse_manifest($ims);
  804. // The title is already escaped in the method
  805. $oScorm->import_manifest($my_course_code);
  806. //TODO: Add code to update the path in that new lp created, as it probably uses / where
  807. // $sco_path_temp should be used...
  808. $lp_ids[$my_content_id] = $oScorm->lp_id; // Contains the old LP ID => the new LP ID
  809. if ($loglevel > 1) { Log::notice(" @@@ Created scorm lp ".$oScorm->lp_id." from imsmanifest [".$ims."] in course $my_course_code"); }
  810. $lp_course[$my_content_id] = $courses_id_list[$my_course_code]; // Contains the old learnpath ID => the course DB name
  811. $lp_course_code[$my_content_id] = $my_course_code;
  812. $max_dsp_lp++;
  813. /*
  814. * QUERY SCORM ITEMS FROM SCORM_SCO_DATA
  815. * The danger here is that we might have several users for the same data, and so
  816. * we have to avoid entering the same elements twice
  817. */
  818. $sql_items = "SELECT * FROM $scorm_item WHERE contentId = '".$my_content_id."' ORDER BY scoId";
  819. //echo "$sql_items<br />\n";
  820. $res_items = Database::query($sql_items);
  821. while ($scormItem = Database::fetch_array($res_items)) {
  822. $my_sco_id = $scormItem['scoId']; //the index for display??? (check that)
  823. $my_identifier = $scormItem['scoIdentifier']; //the scorm item path/name
  824. $my_title = Database::escape_string($scormItem['scoTitle']);
  825. $my_status = $scormItem['status'];
  826. $my_student = $scormItem['studentId'];
  827. $my_score = $scormItem['score'];
  828. $my_time = my_get_time($scormItem['time']);
  829. $my_type = 'sco';
  830. //$my_item_path = $scorm_lp_paths[$my_content_id]['path'];
  831. $my_item_path = '';
  832. //echo "&nbsp;&nbsp;FOUND item belonging to old learnpath num $my_content_id so belongs to course ".$lp_course[$my_content_id]."<br />\n";
  833. $my_new_lp_item = $db_name.$new_lp_item;
  834. $my_new_lp_view = $db_name.$new_lp_view;
  835. $my_new_lp_item_view = $db_name.$new_lp_item_view;
  836. /*
  837. * Check if a view is needed
  838. */
  839. if ($my_score != '' and $my_status != 'not attempted') {
  840. // It is worth creating an lp_view and an lp_item_view - otherwise not
  841. $sel_sqlb = "SELECT * FROM $my_new_lp_view " .
  842. "WHERE lp_id = ".$lp_ids[$my_content_id]." AND user_id = $my_student";
  843. $sel_resb = Database::query($sel_sqlb);
  844. if (Database::num_rows($sel_resb) > 0) {
  845. // Don't insert
  846. $rowb = Database::fetch_array($sel_resb);
  847. $view_insert_id = $rowb['id'];
  848. } else {
  849. $ins_sql = "INSERT INTO $my_new_lp_view (" .
  850. "lp_id," .
  851. "user_id," .
  852. "view_count" .
  853. ") VALUES (" .
  854. $lp_ids[$my_content_id].", " .
  855. $my_student.", " .
  856. "1" .
  857. ")";
  858. //echo "$ins_sql<br />";
  859. $ins_res = Database::query($ins_sql);
  860. $view_insert_id = Database::insert_id();
  861. }
  862. $sel_sqlc = "SELECT * FROM $my_new_lp_item " .
  863. "WHERE lp_id = ".$lp_ids[$my_content_id]." AND ref = '$my_identifier'";
  864. $sel_resc = Database::query($sel_sqlc);
  865. if (Database::num_rows($sel_resc) > 0) {
  866. $my_item_id_row = Database::fetch_array($sel_resc);
  867. $item_insert_id = $my_item_id_row['id'];
  868. $ins_sql = "INSERT INTO $my_new_lp_item_view (" .
  869. "lp_item_id, lp_view_id, view_count," .
  870. "start_time, total_time, score," .
  871. "status" .
  872. ") VALUES (" .
  873. "$item_insert_id, $view_insert_id, 1," .
  874. "0, $my_time, $my_score," .
  875. "'$my_status'" .
  876. ")";
  877. //echo "$ins_sql<br />";
  878. $ins_res = Database::query($ins_sql);
  879. } else {
  880. //echo " Didn't find corresponding item for $my_identifier in new tables<br />\n";
  881. }
  882. }
  883. }
  884. } else {
  885. //echo "Could not find $ims... Proceeding from database...(line ".__LINE__.")<br />";
  886. if ($loglevel > 1) { Log::notice("This is a normal SCORM path"); }
  887. $scorm_lp_paths[$my_content_id]['path'] = $my_path;
  888. //$scorm_lp_paths[$my_content_id]['ims'] = '';
  889. $table_name = $db_name.$new_lp;
  890. $sql_ins = "INSERT INTO $table_name (" .
  891. "lp_type," .
  892. "name," .
  893. "description," .
  894. "path," .
  895. "force_commit, " .
  896. "default_encoding," .
  897. "display_order," .
  898. "content_maker," .
  899. "content_local," .
  900. "js_lib" .
  901. ") VALUES (" .
  902. "2," .
  903. "'$my_name'," .
  904. "''," .
  905. "'$my_path'," .
  906. "0," .
  907. "'UTF-8'," .
  908. "".$max_dsp_lp."," .
  909. "'Unknown'," .
  910. "'Unknown'," .
  911. "'scorm_api.php'" .
  912. ")";
  913. if ($loglevel > 1) { Log::notice("$sql_ins"); }
  914. $sql_res = Database::query($sql_ins);
  915. $in_id = Database::insert_id();
  916. if (!$in_id) die('Could not insert scorm lp: '.$sql_ins);
  917. //echo "&nbsp;&nbsp;Inserted item $in_id<br />\n";
  918. $lp_ids[$my_content_id] = $in_id; //contains the old LP ID => the new LP ID
  919. $lp_course[$my_content_id] = $courses_id_list[$my_course_code]; // Contains the old learnpath ID => the course DB name
  920. $lp_course_code[$my_content_id] = $my_course_code;
  921. $max_dsp_lp++;
  922. // Setup the ims path (path to the imsmanifest.xml file)
  923. //echo "Looking for course with code ".$lp_course_code[$my_content_id]." (using $my_content_id)<br />\n";
  924. $courses_dir = $sys_course_path.$courses_dir_list[$lp_course_code[$my_content_id]];
  925. //$scorm_lp_paths[$my_content_id]['path'] = str_replace(' ', '\\ ', $scorm_lp_paths[$my_content_id]['path']);
  926. $sco_path_temp = ($scorm_lp_paths[$my_content_id]['path'] == '/') ? '' : $scorm_lp_paths[$my_content_id]['path'];
  927. $scorm_lp_paths[$my_content_id]['ims'] = $courses_dir.'/scorm'.$sco_path_temp.'/imsmanifest.xml';
  928. // Generate an imsmanifest object to get more info about the learnpath from the file
  929. $oScorm = new scorm();
  930. // Check whether imsmanifest.xml exists at this location. If not, ignore the imsmanifest.
  931. // That should have been done before already, now.
  932. if (!is_file($scorm_lp_paths[$my_content_id]['ims'])) {
  933. if ($loglevel > 1) { Log::notice("!!! imsmanifest file not found at ".$scorm_lp_paths[$my_content_id]['ims'].' for old lp '.$my_content_id.' and new '.$lp_ids[$my_content_id]); }
  934. $manifest = false;
  935. } else {
  936. //echo "Parsing ".$scorm_lp_paths[$my_content_id]['ims']."<br />\n";
  937. // Parse manifest file
  938. $manifest = $oScorm->parse_manifest($scorm_lp_paths[$my_content_id]['ims']);
  939. // The title is already escaped in the method
  940. //$my_lp_title = api_convert_encoding($oScorm->get_title(),'ISO-8859-1',$oScorm->manifest_encoding);
  941. $my_lp_title = api_convert_encoding($oScorm->get_title(), 'ISO-8859-1', 'UTF-8'); // TODO: This "magic" conversion to be checked.
  942. if (!empty($my_lp_title)) {
  943. $my_new_lp = $db_name.$new_lp;
  944. $my_sql = "UPDATE $my_new_lp " .
  945. "SET name = '$my_lp_title', " .
  946. "default_encoding = '".strtoupper($oScorm->manifest_encoding)."' " .
  947. "WHERE id = ".$lp_ids[$my_content_id];
  948. if ($loglevel > 1) { Log::notice("Updating title and encoding: ".$my_sql); }
  949. $my_res = Database::query($my_sql);
  950. }
  951. }
  952. /*
  953. * QUERY SCORM ITEMS FROM SCORM_SCO_DATA
  954. * The danger here is that we might have several users for the same data, and so
  955. * we have to avoid entering the same elements twice
  956. */
  957. $sql_items = "SELECT * FROM $scorm_item WHERE contentId = '".$my_content_id."' ORDER BY scoId";
  958. //echo "$sql_items<br />\n";
  959. $res_items = Database::query($sql_items);
  960. while ($scormItem = Database::fetch_array($res_items)) {
  961. $my_sco_id = $scormItem['scoId']; // The index for display??? (check that)
  962. $my_identifier = $scormItem['scoIdentifier']; //the scorm item path/name
  963. $my_title = Database::escape_string($scormItem['scoTitle']);
  964. $my_status = $scormItem['status'];
  965. $my_student = $scormItem['studentId'];
  966. $my_score = $scormItem['score'];
  967. $my_time = my_get_time($scormItem['time']);
  968. $my_type = 'sco';
  969. //$my_item_path = $scorm_lp_paths[$my_content_id]['path'];
  970. $my_item_path = '';
  971. //echo "&nbsp;&nbsp;FOUND item belonging to old learnpath num $my_content_id so belongs to course ".$lp_course[$my_content_id]."<br />\n";
  972. $my_new_lp_item = $db_name.$new_lp_item;
  973. $my_new_lp_view = $db_name.$new_lp_view;
  974. $my_new_lp_item_view = $db_name.$new_lp_item_view;
  975. /*
  976. * Query items from the new table to check if it doesn't exist already
  977. * Otherwise insert it
  978. */
  979. $sel_sql = "SELECT * FROM $my_new_lp_item " .
  980. "WHERE ref = '$my_identifier' " .
  981. "AND lp_id = ".$lp_ids[$my_content_id]."";
  982. //echo $sel_sql."<br />\n";
  983. $sel_res = Database::query($sel_sql);
  984. if (Database::num_rows($sel_res) > 0) {
  985. // This item already exists, reuse
  986. $row = Database::fetch_array($sel_res);
  987. $item_insert_id = $row['lp_id'];
  988. } else {
  989. $ins_sql = "INSERT INTO $my_new_lp_item (" .
  990. "lp_id," .
  991. "item_type," .
  992. "ref," .
  993. "title," .
  994. "path" .
  995. ") " .
  996. "VALUES (" .
  997. "'".$lp_ids[$my_content_id]."'," . //insert new learnpath ID
  998. "'$my_type'," .
  999. "'".$my_identifier."'," .
  1000. "'".$my_title."'," .
  1001. "'$my_item_path'" .
  1002. ")";
  1003. $ins_res = Database::query($ins_sql);
  1004. $item_insert_id = Database::insert_id();
  1005. $lp_item_ids[$lp_ids[$my_content_id]][$my_sco_id] = $item_insert_id;
  1006. $lp_item_refs[$lp_ids[$my_content_id]][$my_identifier] = $item_insert_id;
  1007. }
  1008. /*
  1009. * Check if a view is needed
  1010. */
  1011. if ($my_score != '' and $my_status != 'not attempted') {
  1012. // It is worth creating an lp_view and an lp_item_view - otherwise not
  1013. $sel_sqlb = "SELECT * FROM $my_new_lp_view " .
  1014. "WHERE lp_id = ".$lp_ids[$my_content_id]." AND user_id = $my_student";
  1015. $sel_resb = Database::query($sel_sqlb);
  1016. if (Database::num_rows($sel_resb) > 0) {
  1017. // Don't insert
  1018. $rowb = Database::fetch_array($sel_resb);
  1019. $view_insert_id = $rowb['id'];
  1020. } else {
  1021. $ins_sql = "INSERT INTO $my_new_lp_view (" .
  1022. "lp_id," .
  1023. "user_id," .
  1024. "view_count" .
  1025. ") VALUES (" .
  1026. $lp_ids[$my_content_id].", " .
  1027. $my_student.", " .
  1028. "1" .
  1029. ")";
  1030. $ins_res = Database::query($ins_sql);
  1031. $view_insert_id = Database::insert_id();
  1032. }
  1033. $ins_sql = "INSERT INTO $my_new_lp_item_view (" .
  1034. "lp_item_id, lp_view_id, view_count," .
  1035. "start_time, total_time, score," .
  1036. "status" .
  1037. ") VALUES (" .
  1038. "$item_insert_id, $view_insert_id, 1," .
  1039. "0, $my_time, $my_score," .
  1040. "'$my_status'" .
  1041. ")";
  1042. $ins_res = Database::query($ins_sql);
  1043. }
  1044. }
  1045. // UPDATE THE LP_VIEW progress
  1046. if (!empty($view_insert_id)) {
  1047. $sql = "SELECT count(distinct(lp_item_id)) FROM $my_new_lp_item_view WHERE lp_view_id = ".$view_insert_id." AND status IN ('passed','completed','succeeded','browsed','failed')";
  1048. $myres = Database::query($sql);
  1049. $myrow = Database::fetch_array($myres);
  1050. $completed = $myrow[0];
  1051. $mylpid = $lp_ids[$my_content_id];
  1052. $sql = "SELECT count(*) FROM $my_new_lp_item WHERE lp_id = '".$mylpid."'";
  1053. $myres = Database::query($sql);
  1054. $myrow = Database::fetch_array($myres);
  1055. $total = $myrow[0];
  1056. $progress = ((float)$completed / (float)$total) * 100;
  1057. $progress = number_format($progress, 0);
  1058. $sql = "UPDATE $my_new_lp_view SET progress = '$progress' WHERE id = '$view_insert_id'";
  1059. $myres = Database::query($sql);
  1060. }
  1061. /*
  1062. * Set all information that might be more correct coming from imsmanifest
  1063. */
  1064. //$my_new_lp = $db_name.$new_lp;
  1065. //$my_new_lp_item = $db_name.$new_lp_item;
  1066. //$my_new_lp_view = $db_name.$new_lp_view;
  1067. //$my_new_lp_item_view = $db_name.$new_lp_item_view;
  1068. //$sel_sql = "SELECT * FROM $my_new_lp WHERE id = $in_id";
  1069. //$res = @Database::query($sel_sql);
  1070. //if (!$res) {
  1071. // echo "Error selecting lp: $sel_sql - ".Database::error()."<br />\n";
  1072. //}
  1073. $lp_details = array();
  1074. //while ($row = Database::fetch_array($res)) {
  1075. $ordered_list = array();
  1076. $mylist = array();
  1077. foreach ($oScorm->organizations as $org) {
  1078. // There should be only one organization (generally)
  1079. // and if there are more, we are not supposed to have been
  1080. // able to manage them before the new tool, so ignore
  1081. if (count($ordered_list) > 0) {
  1082. break;
  1083. }
  1084. $ordered_list = $org->get_flat_items_list();
  1085. }
  1086. $previous = 0;
  1087. $stock = array(0);
  1088. $level = 0;
  1089. $parent_id = 0;
  1090. foreach ($ordered_list as $index => $subarray) {
  1091. // $subarray is an array representing one item and that contains info like
  1092. // identifier, level, rel_order, prerequisites, title, masteryscore, etc.
  1093. //echo "<pre>Lookin for ".$subarray['identifier']." ".print_r($lp_item_refs,true)."</pre>\n";
  1094. if (!empty($lp_item_refs[$in_id][$subarray['identifier']])) {
  1095. $new_id = $lp_item_refs[$in_id][$subarray['identifier']];
  1096. $next = 0;
  1097. $dsp = $subarray['rel_order'];
  1098. if ($subarray['level'] > $level) {
  1099. // Getting one level deeper, just consult
  1100. $parent_id = $previous;
  1101. array_push($stock,$previous);
  1102. $level = $subarray['level'];
  1103. } elseif ($subarray['level'] == $level) {
  1104. // We are on the same level, going to the next id
  1105. //array_pop($stock);
  1106. //array_push($stock, $new_id);
  1107. } else {
  1108. // Getting back from one level deeper
  1109. array_pop($stock);
  1110. $parent_id = array_pop($stock);
  1111. array_push($stock, $parent_id);
  1112. $level = $subarray['level'];
  1113. }
  1114. if (!empty($ordered_list[$index + 1]['identifier']) && !empty($lp_item_refs[$in_id][$ordered_list[$index + 1]['identifier']])){
  1115. $next = $lp_item_refs[$in_id][$ordered_list[$index + 1]['identifier']];
  1116. }
  1117. $path = $oScorm->get_res_path($subarray['identifierref']);
  1118. $update_path = '';
  1119. if (!empty($path)) {
  1120. // If new path is not empty, update
  1121. $update_path = "path = '$path', ";
  1122. }
  1123. $type = $oScorm->get_res_type($subarray['identifierref']);
  1124. $update_type = '';
  1125. if (!empty($type)) {
  1126. // If type is defined, update
  1127. $update_type = "item_type = '$type', ";
  1128. }
  1129. if (empty($path)) {
  1130. // If path is empty, it is a dir anyway
  1131. $update_type = "item_type = 'dir', ";
  1132. }
  1133. $prereq = $subarray['prerequisites'];
  1134. $update_prereq = '';
  1135. if (!empty($prereq)) {
  1136. $update_prereq = "prerequisite = '$prereq', ";
  1137. }
  1138. // We had previous data about this element, update
  1139. $sql2 = "UPDATE $my_new_lp_item " .
  1140. "SET parent_item_id = $parent_id, " .
  1141. "previous_item_id = $previous, " .
  1142. "next_item_id = $next, " .
  1143. $update_path.
  1144. $update_type.
  1145. $update_prereq.
  1146. "display_order = $dsp " .
  1147. "WHERE lp_id = ".$in_id." AND id = ".$new_id;
  1148. //echo "$sql2<br />\n";
  1149. $res2 = Database::query($sql2);
  1150. $previous = $new_id;
  1151. }
  1152. }
  1153. /**
  1154. * Migrate links on the homepage as well now (look into the TABLE_TOOL_LIST table and
  1155. * update the links to newscorm/lp_controller.php?action=view&lp_id=x)
  1156. * See scorm_migrate_hometools.php
  1157. */
  1158. //}
  1159. // end of case where $my_content_id != -1
  1160. }
  1161. /**
  1162. * Update course description (intro page) to use new links instead of learnpath/learnpath_handler.php
  1163. */
  1164. $tbl_intro = $db_name.TABLE_TOOL_INTRO;
  1165. $sql_i = "SELECT * FROM $tbl_intro WHERE id='course_homepage'";
  1166. $res_i = Database::query($sql_i);
  1167. //$link_to_course1 = 'scorm/scormdocument.php';
  1168. while ($row_i = Database::fetch_array($res_i)) {
  1169. $intro = $row_i['intro_text'];
  1170. $out = array();
  1171. $enc_path = str_replace('/', '%2F', $my_path);
  1172. $enc_path = str_replace(' ', '\+', $enc_path);
  1173. //echo "Looking for path ".$enc_path."<br />\n";
  1174. $pattern = '@claroline/scorm/scormdocument\.php([^\s"\']*)openDir='.$enc_path.'([\\"\'\s&]*)@';
  1175. if (preg_match_all($pattern, $intro, $out, PREG_SET_ORDER)) {
  1176. foreach ($out as $results) {
  1177. //echo "---> replace ".'/'.$results[0].'/ by newscorm/lp_controller.php'.$results[1].'action=view&lp_id='.$lp_ids[$my_content_id];
  1178. //$intro = preg_replace('/scorm\/scormdocument\.php([^\s"\']*)openDir='.$enc_path.'([\\"\'\s&])/', 'newscorm/lp_controller.php'.$results[1].'action=view&lp_id='.$lp_ids[$my_content_id], $intro);
  1179. $intro = preg_replace('@claroline/scorm/scormdocument\.php([^\s"\']*)openDir='.$enc_path.'([^\s"\']*)@','main/newscorm/lp_controller.php'.$results[1].'action=view&lp_id='.$lp_ids[$my_content_id], $intro);
  1180. }
  1181. } else {
  1182. //echo "No scorm link found in intro text<br />";
  1183. }
  1184. $pattern = '@claroline/scorm/showinframes\.php([^\s"\']*)file=([^\s"\'&]*)'.$enc_path.'@';
  1185. if (preg_match_all($pattern, $intro, $out, PREG_SET_ORDER)) {
  1186. foreach ($out as $results) {
  1187. //echo "---> replace ".'/'.$results[0].'/ by newscorm/lp_controller.php'.$results[1].'action=view&lp_id='.$lp_ids[$my_content_id];
  1188. //$intro = preg_replace('/scorm\/showinframes\.php([^\s"\']*)file=([^\s"\']*)'.$enc_path.'/', 'newscorm/lp_controller.php'.$results[1].'action=view&lp_id='.$lp_ids[$my_content_id], $intro);
  1189. $intro = preg_replace('@claroline/scorm/showinframes\.php([^\s"\']*)file=([^\s"\'&]*)'.$enc_path.'([^\s"\']*)@','main/newscorm/lp_controller.php'.$results[1].'action=view&lp_id='.$lp_ids[$my_content_id], $intro);
  1190. }
  1191. } else {
  1192. //echo "No scorm link found in intro text<br />";
  1193. }
  1194. if ($intro != $row_i['intro_text']) {
  1195. //echo "<pre>Replacing ".$row_i['intro_text']."\n by \n ".$intro."</pre><br />\n";
  1196. $sql_upd = "update $tbl_intro set intro_text = '".Database::escape_string($intro)."' WHERE id = 'course_homepage' AND intro_text = '".Database::escape_string($row_i['intro_text'])."'";
  1197. //echo $sql_upd."<br />\n";
  1198. fwrite($fh, $sql_upd."\n");
  1199. fwrite($fh_revert, "UPDATE $tbl_intro set intro_text = '".$row_i['intro_text']."' WHERE id = 'course_homepage' AND intro_text = '$intro';\n");
  1200. fwrite($fh_res, $intro."\n");
  1201. Database::query($sql_upd);
  1202. }
  1203. }
  1204. flush();
  1205. }
  1206. }
  1207. fclose($fh);
  1208. fclose($fh_revert);
  1209. fclose($fh_res);
  1210. if ($loglevel > 0) { Log::notice("All done!"); }
  1211. //echo "</body></html>";