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

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