scorm_migrate_db.php 45 KB

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