scorm_migrate_db.php 35 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933
  1. <?php //$id: $
  2. /**
  3. * Script handling the migration between an old Dokeos platform (<1.8.0) to
  4. * setup the new database system (4 scorm tables inside the course's database)
  5. * @package dokeos.scorm
  6. * @author Yannick Warnier <ywarnier@beeznest.org>
  7. */
  8. //TODO change the install_db and update_db scripts to use this script
  9. /**
  10. * Include mandatory libraries
  11. */
  12. require_once('back_compat.inc.php');
  13. require_once('learnpath.class.php');
  14. require_once('scorm.class.php');
  15. ini_set('max_execution_time',7200);
  16. function my_get_time($time){
  17. $matches = array();
  18. if(preg_match('/(\d{2}):(\d{2}):(\d{2})(\.\d*)?/',$time,$matches)){
  19. return ($matches[1]*3600)+($matches[2]*60)+($matches[3]);
  20. }
  21. else return 0;
  22. }
  23. echo "<html><body>";
  24. /**
  25. * New tables definition:
  26. */
  27. $new_lp = 'lp';
  28. $new_lp_view = 'lp_view';
  29. $new_lp_item = 'lp_item';
  30. $new_lp_item_view = 'lp_item_view';
  31. $new_lp_type = 'lp_type';
  32. $max_dsp_lp = 0;
  33. $courses_list = array();
  34. $courses_id_list = array();
  35. $courses_dir_list = array();
  36. $sql = "SELECT * FROM ".Database::get_main_table(MAIN_COURSE_TABLE)."";
  37. $res = api_sql_query($sql,__FILE__,__LINE__);
  38. while ($row = Database::fetch_array($res))
  39. {
  40. //TODO change this db name construction to use DB instead of claro_main.conf settings
  41. $course_pref = Database::get_course_table_prefix();
  42. $dbname = $row['db_name'].'.'.$course_pref;
  43. $courses_list[] = $row['db_name'];
  44. $courses_id_list[$row['code']] = $row['db_name'];
  45. $courses_dir_list[$row['code']] = $row['directory'];
  46. }
  47. echo "Tables created/deleted for all courses<br />\n";
  48. /**
  49. * The migration needs to take all data from the original learnpath tables and add them to the new
  50. * lp, lp_view, lp_item and lp_item_view tables
  51. */
  52. //test only one course
  53. //$courses_list = array('fadtest_BLA');
  54. foreach($courses_list as $db)
  55. {
  56. $incoherences = 0;
  57. echo "Now starting migration of learnpath tables from $db database...<br />\n";
  58. $lp_main = Database::get_course_learnpath_main_table($db);
  59. $lp_ids = array();
  60. $lp_user = Database::get_course_learnpath_user_table($db);
  61. $lp_users = array();
  62. $lp_chap = Database::get_course_learnpath_chapter_table($db);
  63. $parent_chaps = array();
  64. $lp_chap_items = array();
  65. $ordered_chaps = array();
  66. $lp_item = Database::get_course_learnpath_item_table($db);
  67. $lp_items = array();
  68. $lp_ordered_items = array();
  69. $parent_lps = array(); //keeps a track of chapter's learnpath ids
  70. $course_pref = Database::get_course_table_prefix();
  71. $db_name = $db.'.'.$course_pref;
  72. $my_new_lp = $db_name.$new_lp;
  73. $my_new_lp_item = $db_name.$new_lp_item;
  74. $my_new_lp_view = $db_name.$new_lp_view;
  75. $my_new_lp_item_view = $db_name.$new_lp_item_view;
  76. //migrate learnpaths
  77. $sql_test = "SELECT * FROM $my_new_lp";
  78. $res_test = mysql_query($sql_test);
  79. $sql_lp = "SELECT * FROM $lp_main";
  80. echo "$sql_lp<br />\n";
  81. $res_lp = mysql_query($sql_lp);//using mysql_query to avoid dying on failure
  82. if(!$res_lp or !$res_test){
  83. echo "+++Problem querying DB $lp_main+++ skipping (".mysql_error().")<br />\n";
  84. if(!$res_test){
  85. echo "This might be due to no existing table in the destination course<br />\n";
  86. }
  87. continue;
  88. }
  89. $dsp_ord = 1;
  90. while($row = Database::fetch_array($res_lp))
  91. {
  92. //echo "Treating lp id : ".$row['learnpath_id']."<br />\n";
  93. $ins_lp_sql = "INSERT INTO $my_new_lp (lp_type,name,description,display_order,content_maker) " .
  94. "VALUES (1," .
  95. "'".mysql_real_escape_string($row['learnpath_name'])."'," .
  96. "'".mysql_real_escape_string($row['learnpath_description'])."',$dsp_ord,'Dokeos')";
  97. $ins_lp_res = api_sql_query($ins_lp_sql);
  98. $in_id = Database::get_last_insert_id();
  99. if(empty($in_id) or $in_id == false) die('Could not insert lp: '.$ins_lp_sql);
  100. $lp_ids[$row['learnpath_id']] = $in_id;
  101. $dsp_ord++;
  102. $max_dsp_lp = $dsp_ord;
  103. }
  104. //echo "<pre>lp_ids:".print_r($lp_ids,true)."</pre>\n";
  105. //MIGRATING LEARNPATH CHAPTERS
  106. $sql_lp_chap = "SELECT * FROM $lp_chap ORDER BY parent_chapter_id, display_order";
  107. //echo "$sql_lp_chap<br />\n";
  108. $res_lp_chap = api_sql_query($sql_lp_chap);
  109. while($row = Database::fetch_array($res_lp_chap))
  110. {
  111. //echo "Treating chapter id : ".$row['id']."<br />\n";
  112. //TODO build path for this chapter (although there is no real path for any chapter)
  113. //TODO find out how to calculate the "next_item_id" with the "ordre" field
  114. $my_lp_item = Database::get_course_table($new_lp_item);
  115. $myname = mysql_real_escape_string($row['chapter_name']);
  116. $mydesc = mysql_real_escape_string($row['chapter_description']);
  117. $ins_lp_sql = "INSERT INTO $my_new_lp_item (" .
  118. "lp_id," .
  119. "item_type," .
  120. "title," .
  121. "description," .
  122. "path, " .
  123. "display_order, " .
  124. "next_item_id) " .
  125. "VALUES (" .
  126. "'".$lp_ids[$row['learnpath_id']]."'," . //insert new learnpath ID
  127. "'dokeos_chapter'," .
  128. "'".$myname."'," .
  129. "'".$mydesc."'," .
  130. "''," .
  131. $row['display_order'].", " .
  132. "123456" .
  133. ")";
  134. //echo $ins_lp_sql."<br/>\n";
  135. $ins_res = api_sql_query($ins_lp_sql,__FILE__,__LINE__);
  136. $in_id = Database::get_last_insert_id();
  137. //echo "&nbsp;&nbsp;Inserted item $in_id<br />\n";
  138. if(empty($in_id) OR $in_id == false) die('Could not insert lp: '.$ins_sql);
  139. $parent_chaps[$row['id']] = $row['parent_chapter_id'];
  140. $lp_chap_items[$row['id']] = $in_id;
  141. $parent_lps[$row['id']] = $row['learnpath_id'];
  142. $ordered_chaps[$row['parent_chapter_id']][$row['display_order']]=$in_id;
  143. $lp_chaps_list[$row['learnpath_id']][]=$in_id;
  144. }
  145. //echo "<pre>parent_lps:".print_r($parent_lps,true)."</pre>\n";
  146. //Now one loop to update the parent_chapter_ids
  147. foreach($parent_chaps as $old_chap => $old_parent_chap){
  148. if($old_parent_chap != 0){
  149. $new_chap = $lp_chap_items[$old_chap];
  150. $new_parent = $lp_chap_items[$old_parent_chap];
  151. $sql_par_chap = "UPDATE $my_new_lp_item " .
  152. "SET parent_item_id = $new_parent " .
  153. "WHERE id = $new_chap";
  154. $res_par_chap = api_sql_query($sql_par_chap,__FILE__,__LINE__);
  155. }
  156. }
  157. unset($parent_chaps);
  158. //Now one loop to set the next_item_id and the previous_item_id
  159. foreach($ordered_chaps as $parent_chap){
  160. $last = 0;
  161. foreach($ordered_chaps[$parent_chap] as $order => $new_id){
  162. $sql_upd_chaps = "UPDATE $my_new_lp_item " .
  163. "SET previous_item_id = $last " .
  164. "WHERE id = $new_id";
  165. $res_upd_chaps = api_sql_query($sql_upd_chaps,__FILE__,__LINE__);
  166. $next = 0;
  167. if(!empty($ordered_chaps[$parent_chap][$order+1])){
  168. $next = $ordered_chaps[$parent_chap][$order+1];
  169. }
  170. $sql_upd_chaps = "UPDATE $my_new_lp_item " .
  171. "SET next_item_id = $next " .
  172. "WHERE id = $new_id";
  173. $res_upd_chaps = api_sql_query($sql_upd_chaps,__FILE__,__LINE__);
  174. $last = $new_id;
  175. }
  176. }
  177. unset($ordered_chaps);
  178. //migrate learnpath_items
  179. //TODO define this array thanks to types defined in the learnpath_building scripts
  180. //TODO set order correctly
  181. $type_trans = array(
  182. 'document' => TOOL_DOCUMENT,
  183. 'exercise' => TOOL_QUIZ,
  184. 'forum' => TOOL_FORUM,
  185. 'Agenda' => TOOL_CALENDAR_EVENT,
  186. 'Ad_Valvas' => TOOL_ANNOUNCEMENT,
  187. 'Link' => TOOL_LINK,
  188. 'Link _blank' => TOOL_LINK,
  189. 'Exercise' => TOOL_QUIZ,
  190. 'HotPotatoes'=> 'HotPotatoes',
  191. 'Forum' => TOOL_FORUM,
  192. 'Thread' => TOOL_THREAD,
  193. 'Topic' => TOOL_THREAD,
  194. 'Post' => TOOL_POST,
  195. 'Document' => TOOL_DOCUMENT,
  196. 'Assignments'=> 'Assignments',
  197. 'Dropbox' => TOOL_DROPBOX,
  198. 'Introduction_text'=> 'Introduction_text',
  199. 'Course_description' => TOOL_COURSE_DESCRIPTION,
  200. 'Groups' => TOOL_GROUP,
  201. 'Users' => TOOL_USER,
  202. //'chapter' => 'dokeos_chapter', Chapters should all be in learnpath_chapter, no matter the nesting level
  203. );
  204. $sql_lp_item = "SELECT * FROM $lp_item ORDER BY chapter_id, display_order";
  205. //echo "$sql_lp_item<br />\n";
  206. $res_lp_item = api_sql_query($sql_lp_item,__FILE__,__LINE__);
  207. while($row = Database::fetch_array($res_lp_item))
  208. {
  209. //echo "Treating chapter ".$row['chapter_id'].", item ".$row['id']."<br />\n";
  210. $type = $type_trans[$row['item_type']];
  211. $ref = $row['item_id'];
  212. //TODO build item path
  213. //TODO calculate "next_item_id" with the "ordre" field
  214. //prepare prereqs
  215. //prerequisites in Dokeos 1.6 is only authorised on previous items, so
  216. //we know that we are gonna talk about an item that has already been passed
  217. //through here - if none found, print message
  218. $prereq_id = '';
  219. if(!empty($row['prereq_id'])){
  220. switch($row['prereq_type']){
  221. case 'c':
  222. //chapter-type prereq
  223. $prereq_id = $lp_chap_items[$row['prereq_id']];
  224. if(empty($prereq_id)){echo "Could not find prereq chapter ".$row['prereq_id']."<br/>\n";}
  225. break;
  226. case 'i':
  227. default:
  228. //item type prereq
  229. $prereq_id = $lp_items[$parent_lps[$row['chapter_id']]][$row['prereq_id']];
  230. if(empty($prereq_id)){echo "Could not find prereq item ".$row['prereq_id']."<br/>\n";}
  231. break;
  232. }
  233. }
  234. $my_lp_item = Database::get_course_table($new_lp_item);
  235. $ins_lp_sql = "INSERT INTO $my_new_lp_item (" .
  236. "lp_id," .
  237. "item_type," .
  238. "ref, " .
  239. "title," .
  240. "description," .
  241. "path, " .
  242. "parent_item_id," .
  243. "prerequisite," .
  244. "display_order" .
  245. ") VALUES (" .
  246. "'".$lp_ids[$parent_lps[$row['chapter_id']]]."'," . //insert new learnpath ID
  247. "'$type'," .
  248. "'$ref', " .
  249. "'".mysql_real_escape_string($row['title'])."'," .
  250. "'".mysql_real_escape_string($row['description'])."'," .
  251. "''," .
  252. "".$lp_chap_items[$row['chapter_id']]."," .
  253. "'$prereq_id'," .
  254. $row['display_order']." " .
  255. ")";
  256. $ins_res = api_sql_query($ins_lp_sql,__FILE__,__LINE__);
  257. $in_id = Database::get_last_insert_id();
  258. //echo "&nbsp;&nbsp;Inserted item $in_id (".$row['title'].")<br />\n";
  259. if(empty($in_id) OR $in_id == false) die('Could not insert lp_item: '.$ins_sql);
  260. $lp_items[$parent_lps[$row['chapter_id']]][$row['id']] = $in_id;
  261. $lp_ordered_items[$parent_lps[$row['chapter_id']]][$row['chapter_id']][] = $in_id;
  262. }
  263. //echo "<pre>lp_items:".print_r($lp_items,true)."</pre>\n";
  264. // complete next_item_id field by going through the new table and looking at parent_id and display_order
  265. $my_lp_item = Database::get_course_table($new_lp_item);
  266. $order_sql = "SELECT * FROM $my_new_lp_item ORDER by lp_id ASC, parent_item_id ASC, display_order ASC";
  267. //echo "$order_sql<br />\n";
  268. $order_res = api_sql_query($order_sql,__FILE__,__LINE__);
  269. $order_item = array(); //this will contain a sequential list of item_id's, thus allowing to give a simple way to get next id...
  270. $lp_id = 0;
  271. //echo "<pre>";
  272. while($row = Database::fetch_array($order_res))
  273. {
  274. //print_r($row);
  275. if($row['lp_id'] != $lp_id)
  276. {
  277. //apply changes to the database and clean tool arrays
  278. $last = 0;
  279. foreach($order_item as $order_id => $item_id){
  280. $next = 0;
  281. if(!empty($order_item[$order_id+1])){
  282. $next = $order_item[$order_id+1];
  283. }
  284. $upd = "UPDATE $my_new_lp_item " .
  285. "SET next_item_id = ".$next."," .
  286. " previous_item_id = ".$last." " .
  287. "WHERE id = ".$item_id;
  288. //echo "$upd<br />\n";
  289. api_sql_query($upd,__FILE__,__LINE__);
  290. $last = $item_id;
  291. }
  292. $order_item = array();
  293. $lp_id = $row['lp_id'];
  294. $order_item[] = $row['id'];
  295. }else{
  296. $order_item[] = $row['id'];
  297. }
  298. }
  299. //process the last LP stack
  300. $last = 0;
  301. foreach($order_item as $order_id => $item_id){
  302. $next = 0;
  303. if(!empty($order_item[$order_id+1])){
  304. $next = $order_item[$order_id+1];
  305. }
  306. $upd = "UPDATE $my_new_lp_item " .
  307. "SET next_item_id = ".$next."," .
  308. " previous_item_id = ".$last." " .
  309. "WHERE id = ".$item_id;
  310. //echo "$upd<br />\n";
  311. api_sql_query($upd,__FILE__,__LINE__);
  312. $last = $item_id;
  313. }
  314. //echo "</pre>\n";
  315. //MIGRATING THE learnpath_user TABLE (results)
  316. $sql_lp_user = "SELECT * FROM $lp_user ORDER BY user_id, learnpath_id, learnpath_item_id";
  317. //echo "$sql_lp_user<br />\n";
  318. $res_lp_user = api_sql_query($sql_lp_user,__FILE__,__LINE__);
  319. $user_id = 0;
  320. $learnpath_id = 0;
  321. $lp_view = 0;
  322. while($row = Database::fetch_array($res_lp_user))
  323. {
  324. if($row['user_id']!=$user_id OR $row['learnpath_id']!=$learnpath_id) //the user has changed or this is the first
  325. {
  326. //insert a new lp_view
  327. $last = 0;
  328. if(!empty($lp_chaps_list[$row['learnpath_id']][0])){
  329. $last = $lp_chaps_list[$row['learnpath_id']][0];
  330. }
  331. if(empty($lp_ids[$row['learnpath_id']])){
  332. //this can be ignored as it means there was an LP before, this user
  333. //used it, but now it's been removed
  334. //echo "Somehow we also miss a lp_ids[".$row['learnpath_id']."] here<br />\n";
  335. $incoherences ++;
  336. }else{
  337. $mylpid = $lp_ids[$row['learnpath_id']];
  338. $sql_ins_view = "INSERT INTO $my_new_lp_view(" .
  339. "lp_id," .
  340. "user_id," .
  341. "view_count," .
  342. "last_item" .
  343. ")VALUES(" .
  344. "".$mylpid."," . //new learnpath id
  345. "".$row['user_id']."," . //user IDs stay the same
  346. "1," .
  347. "".$last."" . //use the first chapter from this learnpath
  348. ")";
  349. //echo $sql_ins_view;
  350. $res_ins_view = api_sql_query($sql_ins_view,__FILE__,__LINE__);
  351. $in_id = Database::get_last_insert_id();
  352. $user_id = $row['user_id'];
  353. $learnpath_id = $row['learnpath_id'];
  354. $lp_view = $in_id;
  355. }
  356. }
  357. //insert the record into lp_item_view
  358. //TODO fix the whole in here (missing one item at least)
  359. $my_new_lp_item_id = $lp_items[$learnpath_id][$row['learnpath_item_id']];
  360. if(empty($my_new_lp_item_id)){
  361. //this can be ignored safely as it just means a user used a learnpath_item
  362. //before it was removed from items - maybe fix that in Dokeos?
  363. //echo "Somehow we miss lp_items[".$learnpath_id."][".$row['learnpath_item_id']."] here...<br/>";
  364. $incoherences ++;
  365. }else{
  366. $start_time = 0;
  367. if(my_get_time($row['time'])>0){
  368. $start_time = time()-my_get_time($row['time']);
  369. }
  370. $sql_ins_iv = "INSERT INTO $my_new_lp_item_view(" .
  371. "lp_item_id," .
  372. "lp_view_id," .
  373. "view_count," .
  374. "start_time," .
  375. "total_time," .
  376. "score," .
  377. "status" .
  378. ")VALUES(" .
  379. "".$lp_items[$learnpath_id][$row['learnpath_item_id']]."," .
  380. "".$lp_view."," .
  381. "1," .
  382. "$start_time," .
  383. "".my_get_time($row['time'])."," .
  384. "".$row['score']."," .
  385. "'".$row['status']."'" .
  386. ")";
  387. //echo $sql_ins_iv;
  388. $res_ins_iv = api_sql_query($sql_ins_iv,__FILE__,__LINE__);
  389. }
  390. }
  391. /**
  392. * Move prerequisites
  393. * TODO integrate prerequisites migration into learnpath_item migration
  394. */
  395. $msg = '';
  396. if($incoherences>0){
  397. $msg = "(found $incoherences incoherences between views and items - ignored)";
  398. }
  399. /**
  400. * Migrate links on the homepage as well now (look into the TOOL_LIST_TABLE table and
  401. * update the links to newscorm/lp_controller.php?action=view&lp_id=x)
  402. * Only normal learnpaths were visible from the homepage so we only need to update here
  403. */
  404. $tbl_tool = Database::get_course_table(TOOL_LIST_TABLE,$db);
  405. $sql_tool = "SELECT * FROM $tbl_tool WHERE image='scormbuilder.gif' AND link LIKE '%learnpath_handler%'";
  406. $res_tool = api_sql_query($sql_tool,__FILE__,__LINE__);
  407. while($row_tool = Database::fetch_array($res_tool)){
  408. $name = $row_tool['name'];
  409. $link = $row_tool['link'];
  410. //get old lp_id from there
  411. $matches = array();
  412. if(preg_match('/learnpath_id=(\d+)$/',$link,$matches)){
  413. $old_lp_id = $matches[1];
  414. $new_lp_id = $lp_ids[$old_lp_id];
  415. $sql_tool_upd = "UPDATE $tbl_tool " .
  416. "SET link='newscorm/lp_controller.php?action=view&lp_id=$new_lp_id' " .
  417. "WHERE id = ".$row_tool['id'];
  418. error_log('New LP - Migration - Updating tool table: '.$sql_tool_upd,0);
  419. $res_tool_upd = api_sql_query($sql_tool_upd,__FILE__,__LINE__);
  420. }
  421. }
  422. echo "Done!".$msg."<br/>\n";
  423. flush();
  424. ob_flush();
  425. }
  426. unset($lp_ids);
  427. unset($lp_users);
  428. unset($parent_chaps);
  429. unset($lp_chap_items);
  430. unset($ordered_chaps);
  431. unset($lp_items);
  432. unset($lp_ordered_items);
  433. unset($parent_lps);
  434. /**
  435. * SCORM
  436. * The migration needs to take all data from the scorm.scorm_main and scorm.scorm_sco_data tables
  437. * and add them to the new lp, lp_view, lp_item and lp_item_view tables.
  438. */
  439. echo "<br/><br/>Now starting migration of scorm tables from global SCORM database<br />\n";
  440. $scorm_main = Database::get_scorm_main_table($db);
  441. $scorm_item = Database::get_scorm_sco_data_table($db);
  442. $lp_main = Database::get_course_learnpath_main_table($db);
  443. $course_pref = Database::get_course_table_prefix();
  444. $lp_ids = array();
  445. $lp_item_ids = array();
  446. $lp_item_refs = array();
  447. $lp_course = array();
  448. $lp_course_code = array();
  449. $scorm_lp_paths = array();
  450. //avoid empty dokeosCourse fields as they potentially break the rest
  451. $course_main = Database::get_main_table(MAIN_COURSE_TABLE);
  452. $sql_crs = "SELECT * FROM $course_main WHERE target_course_code IS NULL";
  453. echo "$sql_crs<br />\n";
  454. $res_crs = api_sql_query($sql_crs,__FILE__,__LINE__);
  455. $num = Database::num_rows($res_crs);
  456. //prepare an array that will contain course codes and for each course code a list of lps [by path prefixed by '/']
  457. $scorms =array();
  458. $course_code_swap = '';
  459. $scormdocuments_lps = array();
  460. while($course_row = Database::fetch_array($res_crs)){
  461. echo "<br/>\nNow dealing with course ".$course_row['code']."... <br/>\n";
  462. //check the validity of this new course
  463. $my_course_code = $course_row['code'];
  464. //reinit the scormdocuments list
  465. //$scormdocuments_lps = array();
  466. $db_name = $courses_id_list[$my_course_code];
  467. $tblscodoc = Database::get_course_table(SCORMDOC_TABLE,$db_name);
  468. $sql_scodoc = "SELECT path FROM $tblscodoc WHERE path IS NOT NULL AND path != ''";
  469. echo "$sql_scodoc<br/>";
  470. $res_scodoc = api_sql_query($sql_scodoc,__FILE__,__LINE__);
  471. while($row_scodoc = Database::fetch_array($res_scodoc)){
  472. //check if there's more than one slash in total
  473. if(strpos($row_scodoc['path'],'/',1)===false){
  474. $tmp_path = $row_scodoc['path'];
  475. echo "++Now opening $tmp_path<br/>";
  476. //add a prefixing slash if there is none
  477. if(substr($tmp_path,0,1)!='/'){
  478. $tmp_path = '/'.$tmp_path;
  479. }
  480. //if the path is just a slash, empty it
  481. if($tmp_path=='/'){
  482. $tmp_path='';
  483. }
  484. //there is only one 'slash' sign at the beginning,
  485. //or none at all, so we assume
  486. //it is a main directory that should be taken as path
  487. $courses_dir = api_get_path(SYS_COURSE_PATH).''.$courses_dir_list[$my_course_code].'/scorm'.$tmp_path;
  488. if(!is_dir($courses_dir)){
  489. //echo "Scormdocument path $my_content_id: $tmp_path doesn't exist in ".api_get_path(SYS_COURSE_PATH).$courses_dir_list[$my_course_code]."/scorm, skipping<br/>\n";
  490. continue;
  491. //avoid if contentTitle is not the name of an existing directory
  492. }elseif(!is_file($courses_dir."/imsmanifest.xml")){
  493. //if the imsmanifest file was not found there
  494. echo "!!imsmanifest.xml not found at scormdocument's $courses_dir/imsmanifest.xml, skipping<br/>\n";
  495. //try subdirectories on one level depth
  496. echo "Trying subdirectories...<br/>";
  497. $dh = opendir($courses_dir);
  498. while($entry = readdir($dh)){
  499. if(substr($entry,0,1)!='.'){
  500. if(is_dir($courses_dir."/".$entry)){
  501. if(is_file($courses_dir."/".$entry."/imsmanifest.xml")){
  502. echo "... and found $courses_dir/$entry/imsmanifest.xml!<br/>";
  503. if(!in_array($tmp_path."/".$entry."/imsmanifest.xml",$scormdocuments_lps)){
  504. echo "Recording.<br/>";
  505. $scormdocuments_lps[] = $tmp_path."/".$entry;
  506. }
  507. }
  508. }
  509. }
  510. }
  511. }else{
  512. echo "Found scormdocument $tmp_path in ".api_get_path(SYS_COURSE_PATH).$courses_dir_list[$my_course_code]."/scorm, treating it.<br/>\n";
  513. $scormdocuments_lps[] = $tmp_path;
  514. }
  515. }
  516. }
  517. //prepare the new course's space in the scorms array
  518. $scorms[$my_course_code] = array();
  519. $sql_paths = "SELECT * FROM $scorm_main WHERE dokeosCourse = '".$my_course_code."'";
  520. echo "$sql_paths<br/>";
  521. $res_paths = api_sql_query($sql_paths,__FILE__,__LINE__);
  522. $num = Database::num_rows($res_paths);
  523. while($scorm_row = Database::fetch_array($res_paths)){
  524. //check if this is a new course
  525. $my_content_id = $scorm_row['contentId'];
  526. $my_path = $scorm_row['contentTitle'];
  527. if(substr($my_path,0,1)!='/'){
  528. $my_path = '/'.$my_path;
  529. }
  530. if($my_path=='/'){
  531. $my_path='';
  532. }
  533. echo "++++Now opening $my_path<br/>";
  534. if(!is_dir($courses_dir = api_get_path(SYS_COURSE_PATH).''.$courses_dir_list[$my_course_code].'/scorm'.$my_path)){
  535. echo "Path $my_content_id: $my_path doesn't exist in ".api_get_path(SYS_COURSE_PATH).$courses_dir_list[$my_course_code]."/scorm, skipping<br/>\n";
  536. continue;
  537. //avoid if contentTitle is not the name of an existing directory
  538. }elseif(!is_file(api_get_path(SYS_COURSE_PATH).$courses_dir_list[$my_course_code].'/scorm'.$my_path."/imsmanifest.xml")){
  539. echo "!!imsmanifest.xml not found at ".api_get_path(SYS_COURSE_PATH).$courses_dir_list[$my_course_code].'/scorm'.$my_path."/imsmanifest.xml, skipping<br/>\n";
  540. continue;
  541. }else{
  542. echo "Found $my_path in ".api_get_path(SYS_COURSE_PATH).$courses_dir_list[$my_course_code]."/scorm".$mypath."/imsmanifest.xml, keeping it.<br/>\n";
  543. $scorms[$my_course_code][$my_path] = $my_content_id;
  544. }
  545. }
  546. //check if all the lps from scormdocuments_lps are already in the course array,
  547. //otherwise add them (and set ID of 0 so no tracking will be available)
  548. foreach($scormdocuments_lps as $path){
  549. if(!in_array($path,array_keys($scorms[$my_course_code]))){
  550. //add it (-1 means no ID)
  551. echo "** Scormdocument path $path wasn't recorded yet. Added.<br/>\n";
  552. $scorms[$my_course_code][$path] = -1;
  553. }
  554. }
  555. $course_code_swap = $my_course_code;
  556. unset($scormdocuments_lps);
  557. }
  558. //echo "<pre>courses_id_list: ".print_r($courses_id_list,true)."</pre>\n";
  559. echo "<br/>\n---- Scorms array now contains ".count($scorms)." paths to migrate. Starting migration...<br />\n";
  560. /**
  561. * Looping through the SCO_MAIN table for SCORM learnpath attached to courses
  562. * Order by course to try and reuse the maximum data
  563. */
  564. $i_count = 0;
  565. foreach($scorms as $my_course_code => $paths_list )
  566. {
  567. $max_dsp_lp = 0;
  568. $course_lp_done = array();
  569. $db_name = $courses_id_list[$my_course_code].'.'.$course_pref;
  570. foreach($paths_list as $my_path => $old_id){
  571. echo "Migrating lp $my_path from course $my_course_code...<br>\n";
  572. $i_count ++;
  573. //error_log('New LP - Migration script - Content '.$i_count.' on '.$num.' (course '.$scorm['dokeosCourse'].')',0);
  574. //check if there is no embedded learnpaths into other learnpaths (one root-level and another embedded)
  575. $embedded = false;
  576. foreach($course_lp_done as $tmp_lp){
  577. if(empty($tmp_lp)){
  578. $tmp_lp = '/'; //allows finding the lp as a subitem, otherwise strstr returns false
  579. }
  580. if(strstr($my_path,$tmp_lp)===false){
  581. //let it be
  582. }else{
  583. //this lp is embedded inside another lp who's imsmanifest exists, so prevent from parsing
  584. echo "LP $my_path is embedded into $tmp_lp, ignoring...<br/>\n";
  585. $embedded = true;
  586. continue;
  587. }
  588. }
  589. if($embedded){
  590. continue;
  591. }
  592. $course_lp_done[] = $my_path;
  593. //echo "<pre>scorm row: ".print_r($scorm,true)."</pre>\n";
  594. $my_content_id = $old_id;
  595. $my_path = $my_path;
  596. $my_name = basename($my_path);
  597. echo "Try importing LP $my_path from imsmanifest first as it is more reliable<br/>\n";
  598. //Setup the ims path (path to the imsmanifest.xml file)
  599. //echo "Looking for course with code ".$lp_course_code[$my_content_id]." (using $my_content_id)<br />\n";
  600. $courses_dir = api_get_path(SYS_COURSE_PATH).''.$courses_dir_list[$my_course_code];
  601. $sco_path_temp = ($my_path=='/')?'':$my_path;
  602. $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
  603. $ims = $courses_dir.'/scorm'.$sco_path_temp.'/imsmanifest.xml';
  604. if(is_file($ims)){
  605. $oScorm = new scorm();
  606. //check if imsmanifest.xml exists at this location. If not, ignore the imsmanifest.
  607. //That should have been done before already, now.
  608. echo "Found imsmanifest ($ims), importing...<br />\n";
  609. if(!empty($sco_middle_path)){$oScorm->subdir = $sco_middle_path;} //this sets the subdir for the scorm package inside the scorm dir
  610. //parse manifest file
  611. $manifest = $oScorm->parse_manifest($ims);
  612. //the title is already escaped in the method
  613. $oScorm->import_manifest($my_course_code);
  614. //TODO add code to update the path in that new lp created, as it probably uses / where
  615. //$sco_path_temp should be used...
  616. }
  617. //if($my_content_id != -1){
  618. else{
  619. echo "This is a normal SCORM path<br/>\n";
  620. $scorm_lp_paths[$my_content_id]['path'] = $my_path;
  621. //$scorm_lp_paths[$my_content_id]['ims'] = '';
  622. $table_name = $db_name.$new_lp;
  623. $sql_ins = "INSERT INTO $table_name (" .
  624. "lp_type," .
  625. "name," .
  626. "description," .
  627. "path," .
  628. "force_commit, " .
  629. "default_encoding," .
  630. "display_order," .
  631. "content_maker," .
  632. "content_local," .
  633. "js_lib" .
  634. ") VALUES (" .
  635. "2," .
  636. "'$my_name'," .
  637. "''," .
  638. "'$my_path'," .
  639. "1," .
  640. "'UTF-8'," .
  641. "".$max_dsp_lp."," .
  642. "'Unknown'," .
  643. "'Unknown'," .
  644. "'scorm_api.php'" .
  645. ")";
  646. echo "$sql_ins<br />\n";
  647. $sql_res = api_sql_query($sql_ins,__FILE__,__LINE__);
  648. $in_id = Database::get_last_insert_id();
  649. if(empty($in_id) or $in_id == false) die('Could not insert scorm lp: '.$sql_ins);
  650. //echo "&nbsp;&nbsp;Inserted item $in_id<br />\n";
  651. $lp_ids[$my_content_id] = $in_id; //contains the old LP ID => the new LP ID
  652. $lp_course[$my_content_id] = $courses_id_list[$my_course_code]; //contains the old learnpath ID => the course DB name
  653. $lp_course_code[$my_content_id] = $my_course_code;
  654. $max_dsp_lp++;
  655. //Setup the ims path (path to the imsmanifest.xml file)
  656. //echo "Looking for course with code ".$lp_course_code[$my_content_id]." (using $my_content_id)<br />\n";
  657. $courses_dir = api_get_path(SYS_COURSE_PATH).''.$courses_dir_list[$lp_course_code[$my_content_id]];
  658. //$scorm_lp_paths[$my_content_id]['path'] = str_replace(' ','\\ ',$scorm_lp_paths[$my_content_id]['path']);
  659. $sco_path_temp = ($scorm_lp_paths[$my_content_id]['path']=='/')?'':$scorm_lp_paths[$my_content_id]['path'];
  660. $scorm_lp_paths[$my_content_id]['ims'] = $courses_dir.'/scorm'.$sco_path_temp.'/imsmanifest.xml';
  661. //generate an imsmanifest object to get more info about the learnpath from the file
  662. $oScorm = new scorm();
  663. //check if imsmanifest.xml exists at this location. If not, ignore the imsmanifest.
  664. //That should have been done before already, now.
  665. if(!is_file($scorm_lp_paths[$my_content_id]['ims'])){
  666. echo "!!! 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]."<br />\n";
  667. $manifest = false;
  668. }else{
  669. //echo "Parsing ".$scorm_lp_paths[$my_content_id]['ims']."<br>\n";
  670. //parse manifest file
  671. $manifest = $oScorm->parse_manifest($scorm_lp_paths[$my_content_id]['ims']);
  672. //the title is already escaped in the method
  673. //$my_lp_title = mb_convert_encoding($oScorm->get_title(),'ISO-8859-1',$oScorm->manifest_encoding);
  674. $my_lp_title = mb_convert_encoding($oScorm->get_title(),'ISO-8859-1','UTF-8');
  675. if(!empty($my_lp_title)){
  676. $my_new_lp = $db_name.$new_lp;
  677. $my_sql = "UPDATE $my_new_lp " .
  678. "SET name = '$my_lp_title', " .
  679. "default_encoding = '".strtoupper($oScorm->manifest_encoding)."' " .
  680. "WHERE id = ".$lp_ids[$my_content_id];
  681. echo "Updating title and encoding: ".$my_sql."<br/>\n";
  682. $my_res = api_sql_query($my_sql,__FILE__,__LINE__);
  683. }
  684. }
  685. /*
  686. * QUERY SCORM ITEMS FROM SCORM_SCO_DATA
  687. * The danger here is that we might have several users for the same data, and so
  688. * we have to avoid entering the same elements twice
  689. */
  690. $sql_items = "SELECT * FROM $scorm_item WHERE contentId = '".$my_content_id."' ORDER BY scoId";
  691. //echo "$sql_items<br />\n";
  692. $res_items = api_sql_query($sql_items,__FILE__,__LINE__);
  693. while($scormItem = Database::fetch_array($res_items))
  694. {
  695. $my_sco_id = $scormItem['scoId']; //the index for display??? (check that)
  696. $my_identifier = $scormItem['scoIdentifier']; //the scorm item path/name
  697. $my_title = mysql_real_escape_string($scormItem['scoTitle']);
  698. $my_status = $scormItem['status'];
  699. $my_student = $scormItem['studentId'];
  700. $my_score = $scormItem['score'];
  701. $my_time = my_get_time($scormItem['time']);
  702. $my_type = 'sco';
  703. //$my_item_path = $scorm_lp_paths[$my_content_id]['path'];
  704. $my_item_path = '';
  705. //echo "&nbsp;&nbsp;FOUND item belonging to old learnpath num $my_content_id so belongs to course ".$lp_course[$my_content_id]."<br />\n";
  706. $my_new_lp_item = $db_name.$new_lp_item;
  707. $my_new_lp_view = $db_name.$new_lp_view;
  708. $my_new_lp_item_view = $db_name.$new_lp_item_view;
  709. /*
  710. * Query items from the new table to check if it doesn't exist already
  711. * Otherwise insert it
  712. */
  713. $sel_sql = "SELECT * FROM $my_new_lp_item " .
  714. "WHERE ref = '$my_identifier' " .
  715. "AND lp_id = ".$lp_ids[$my_content_id]."";
  716. //echo $sel_sql."<br />\n";
  717. $sel_res = api_sql_query($sel_sql,__FILE__,__LINE__);
  718. if(Database::num_rows($sel_res)>0){
  719. //this item already exists, reuse
  720. $row = Database::fetch_array($sel_res);
  721. $item_insert_id = $row['lp_id'];
  722. }else{
  723. $ins_sql = "INSERT INTO $my_new_lp_item (" .
  724. "lp_id," .
  725. "item_type," .
  726. "ref," .
  727. "title," .
  728. "path" .
  729. ") " .
  730. "VALUES (" .
  731. "'".$lp_ids[$my_content_id]."'," . //insert new learnpath ID
  732. "'$my_type'," .
  733. "'".$my_identifier."'," .
  734. "'".$my_title."'," .
  735. "'$my_item_path'" .
  736. ")";
  737. $ins_res = api_sql_query($ins_sql,__FILE__,__LINE__);
  738. $item_insert_id = Database::get_last_insert_id();
  739. $lp_item_ids[$lp_ids[$my_content_id]][$my_sco_id]=$item_insert_id;
  740. $lp_item_refs[$lp_ids[$my_content_id]][$my_identifier]=$item_insert_id;
  741. }
  742. /*
  743. * Check if a view is needed
  744. */
  745. if($my_score != '' and $my_status != 'not attempted'){
  746. //it is worth creating an lp_view and an lp_item_view - otherwise not
  747. $sel_sqlb = "SELECT * FROM $my_new_lp_view " .
  748. "WHERE lp_id = ".$lp_ids[$my_content_id]." AND user_id = $my_student";
  749. $sel_resb = api_sql_query($sel_sqlb,__FILE__,__LINE__);
  750. if(Database::num_rows($sel_resb)>0){
  751. //dont insert
  752. $rowb = Database::fetch_array($sel_resb);
  753. $view_insert_id = $rowb['id'];
  754. }else{
  755. $ins_sql = "INSERT INTO $my_new_lp_view (" .
  756. "lp_id," .
  757. "user_id," .
  758. "view_count" .
  759. ") VALUES (" .
  760. $lp_ids[$my_content_id].", " .
  761. $my_student.", " .
  762. "1" .
  763. ")";
  764. $ins_res = api_sql_query($ins_sql,__FILE__,__LINE__);
  765. $view_insert_id = Database::get_last_insert_id();
  766. }
  767. $ins_sql = "INSERT INTO $my_new_lp_item_view (" .
  768. "lp_item_id, lp_view_id, view_count," .
  769. "start_time, total_time, score," .
  770. "status" .
  771. ") VALUES (" .
  772. "$item_insert_id, $view_insert_id, 1," .
  773. "0, $my_time, $my_score," .
  774. "'$my_status'" .
  775. ")";
  776. $ins_res = api_sql_query($ins_sql,__FILE__,__LINE__);
  777. }
  778. }
  779. /*
  780. * Set all information that might be more correct coming from imsmanifest
  781. */
  782. //$my_new_lp = $db_name.$new_lp;
  783. //$my_new_lp_item = $db_name.$new_lp_item;
  784. //$my_new_lp_view = $db_name.$new_lp_view;
  785. //$my_new_lp_item_view = $db_name.$new_lp_item_view;
  786. //$sel_sql = "SELECT * FROM $my_new_lp WHERE id = $in_id";
  787. //$res = @mysql_query($sel_sql);
  788. //if(!$res){
  789. // echo "Error selecting lp: $sel_sql - ".mysql_error()."<br />\n";
  790. //}
  791. $lp_details = array();
  792. //while($row = Database::fetch_array($res))
  793. //{
  794. $ordered_list = array();
  795. $mylist = array();
  796. foreach($oScorm->organizations as $org){
  797. //There should be only one organization (generally)
  798. //and if there are more, we are not supposed to have been
  799. //able to manage them before the new tool, so ignore
  800. if(count($ordered_list)>0){
  801. break;
  802. }
  803. $ordered_list = $org->get_flat_items_list();
  804. }
  805. $previous = 0;
  806. $stock = array(0);
  807. $level=0;
  808. $parent_id = 0;
  809. foreach($ordered_list as $index => $subarray)
  810. {
  811. //$subarray is an array representing one item and that contains info like
  812. //identifier, level, rel_order, prerequisites, title, masteryscore, etc.
  813. //echo "<pre>Lookin for ".$subarray['identifier']." ".print_r($lp_item_refs,true)."</pre>\n";
  814. if(!empty($lp_item_refs[$in_id][$subarray['identifier']])){
  815. $new_id = $lp_item_refs[$in_id][$subarray['identifier']];
  816. $next = 0;
  817. $dsp = $subarray['rel_order'];
  818. if($subarray['level']>$level){
  819. //getting one level deeper, just consult
  820. $parent_id = $previous;
  821. array_push($stock,$previous);
  822. $level = $subarray['level'];
  823. }elseif($subarray['level']==$level){
  824. //we are on the same level, going to the next id
  825. //array_pop($stock);
  826. //array_push($stock,$new_id);
  827. }else{
  828. //getting back from one level deeper
  829. array_pop($stock);
  830. $parent_id = array_pop($stock);
  831. array_push($stock,$parent_id);
  832. $level = $subarray['level'];
  833. }
  834. if(!empty($ordered_list[$index+1]['identifier']) && !empty($lp_item_refs[$in_id][$ordered_list[$index+1]['identifier']])){
  835. $next = $lp_item_refs[$in_id][$ordered_list[$index+1]['identifier']];
  836. }
  837. $path = $oScorm->get_res_path($subarray['identifierref']);
  838. $update_path = '';
  839. if(!empty($path)){
  840. //if new path is not empty, update
  841. $update_path = "path = '$path', ";
  842. }
  843. $type = $oScorm->get_res_type($subarray['identifierref']);
  844. $update_type = '';
  845. if(!empty($type)){
  846. //if type is defined, update
  847. $update_type = "item_type = '$type', ";
  848. }
  849. if(empty($path)){
  850. //if path is empty, it is a dir anyway
  851. $update_type = "item_type = 'dir', ";
  852. }
  853. $prereq = $subarray['prerequisites'];
  854. $update_prereq = '';
  855. if(!empty($prereq)){
  856. $update_prereq = "prerequisite = '$prereq', ";
  857. }
  858. //we had previous data about this element, update
  859. $sql2 = "UPDATE $my_new_lp_item " .
  860. "SET parent_item_id = $parent_id, " .
  861. "previous_item_id = $previous, " .
  862. "next_item_id = $next, " .
  863. $update_path.
  864. $update_type.
  865. $update_prereq.
  866. "display_order = $dsp " .
  867. "WHERE lp_id = ".$in_id." AND id = ".$new_id;
  868. //echo "$sql2<br>\n";
  869. $res2 = api_sql_query($sql2,__FILE__,__LINE__);
  870. $previous = $new_id;
  871. }
  872. }
  873. /**
  874. * Migrate links on the homepage as well now (look into the TOOL_LIST_TABLE table and
  875. * update the links to newscorm/lp_controller.php?action=view&lp_id=x)
  876. * See scorm_migrate_hometools.php
  877. */
  878. //}
  879. //end of case where $my_content_id != -1
  880. }
  881. flush();
  882. }
  883. }
  884. echo "All done!";
  885. echo "</body></html>";
  886. ?>