scorm_migrate_db.php 45 KB

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