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

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