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

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