HI Dan,
1. How do I determine if a user has completed a lesson?
This table holds all the data you need:
mysql> desc lams_learner_progress ;
+-----------------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+------------+------+-----+---------+----------------+
| learner_progress_id | bigint(20) | NO | PRI | NULL | auto_increment |
| user_id | bigint(20) | NO | MUL | NULL | |
| lesson_id | bigint(20) | NO | MUL | NULL | |
| lesson_completed_flag | tinyint(1) | NO | | 0 | |
| waiting_flag | tinyint(4) | NO | | NULL | |
| start_date_time | datetime | NO | | NULL | |
| finish_date_time | datetime | YES | | NULL | |
| current_activity_id | bigint(20) | YES | MUL | NULL | |
| next_activity_id | bigint(20) | YES | MUL | NULL | |
| previous_activity_id | bigint(20) | YES | MUL | NULL | |
| requires_restart_flag | tinyint(1) | NO | | NULL | |
+-----------------------+------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
2. What is the data structure behind the mcq_main.html file that is exported with the user portfolio?
This is where things can get a bit tricky.
A learning design, as you author it in author is independent from the instance that you run on a course.
This means that when you create a lesson for a course, automatically the learning design information gets copied (so the runtime version doesn't modify the original authored design) and a row in the lams_lesson table is created.
The lams_lesson table has this datamodel:
mysql> desc lams_lesson;
+----------------------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------------------+--------------+------+-----+---------+----------------+
| lesson_id | bigint(20) | NO | PRI | NULL | auto_increment |
| learning_design_id | bigint(20) | NO | MUL | NULL | |
| user_id | bigint(20) | NO | MUL | NULL | |
| name | varchar(255) | NO | | NULL | |
| description | text | YES | | NULL | |
| create_date_time | datetime | NO | | NULL | |
| organisation_id | bigint(20) | YES | MUL | NULL | |
| class_grouping_id | bigint(20) | YES | MUL | NULL | |
| lesson_state_id | int(3) | NO | MUL | NULL | |
| start_date_time | datetime | YES | | NULL | |
| scheduled_number_days_to_lesson_finish | int(3) | YES | | NULL | |
| schedule_start_date_time | datetime | YES | | NULL | |
| end_date_time | datetime | YES | | NULL | |
| schedule_end_date_time | datetime | YES | | NULL | |
| previous_state_id | int(3) | YES | | NULL | |
| learner_exportport_avail | tinyint(1) | YES | | 1 | |
| learner_presence_avail | tinyint(1) | YES | | 0 | |
| learner_im_avail | tinyint(1) | YES | | 0 | |
| live_edit_enabled | tinyint(1) | YES | | 0 | |
| enable_lesson_notifications | tinyint(1) | YES | | 0 | |
| locked_for_edit | tinyint(4) | YES | | 0 | |
| marks_released | tinyint(4) | YES | | 0 | |
| version | int(11) | YES | | 1 | |
| release_date | datetime | YES | MUL | NULL | |
+----------------------------------------+--------------+------+-----+---------+----------------+
24 rows in set (0.00 sec)
Then a lesson has activities, just like a learning_design does, but in this case, these activities are 'runtime' instances instead. These runtime instances are "linked" to the tools table by the lams_learning_activity table
mysql> desc lams_learning_activity;
+---------------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------------+--------------+------+-----+---------+----------------+
| activity_id | bigint(20) | NO | PRI | NULL | auto_increment |
| activity_ui_id | int(11) | YES | | NULL | |
| description | text | YES | | NULL | |
| title | varchar(255) | YES | | NULL | |
| help_text | text | YES | | NULL | |
| xcoord | int(11) | YES | | NULL | |
| ycoord | int(11) | YES | | NULL | |
| parent_activity_id | bigint(20) | YES | MUL | NULL | |
| parent_ui_id | int(11) | YES | | NULL | |
| learning_activity_type_id | int(11) | NO | MUL | 0 | |
| grouping_support_type_id | int(3) | NO | MUL | NULL | |
| apply_grouping_flag | tinyint(1) | NO | | NULL | |
| grouping_id | bigint(20) | YES | MUL | NULL | |
| grouping_ui_id | int(11) | YES | | NULL | |
| order_id | int(11) | YES | | NULL | |
| define_later_flag | tinyint(4) | NO | | 0 | |
| learning_design_id | bigint(20) | YES | MUL | NULL | |
| learning_library_id | bigint(20) | YES | MUL | NULL | |
| create_date_time | datetime | NO | | NULL | |
| run_offline_flag | tinyint(1) | NO | | NULL | |
| max_number_of_options | int(5) | YES | | NULL | |
| min_number_of_options | int(5) | YES | | NULL | |
| options_instructions | text | YES | | NULL | |
| tool_id | bigint(20) | YES | MUL | NULL | |
| tool_content_id | bigint(20) | YES | | NULL | |
| activity_category_id | int(3) | NO | MUL | NULL | |
| gate_activity_level_id | int(11) | YES | MUL | NULL | |
| gate_open_flag | tinyint(1) | YES | | NULL | |
| gate_start_time_offset | bigint(38) | YES | | NULL | |
| gate_end_time_offset | bigint(38) | YES | | NULL | |
| gate_start_date_time | datetime | YES | | NULL | |
| gate_end_date_time | datetime | YES | | NULL | |
| library_activity_ui_image | varchar(255) | YES | | NULL | |
| create_grouping_id | bigint(20) | YES | MUL | NULL | |
| create_grouping_ui_id | int(11) | YES | | NULL | |
| library_activity_id | bigint(20) | YES | MUL | NULL | |
| language_file | varchar(255) | YES | | NULL | |
| system_tool_id | bigint(20) | YES | MUL | NULL | |
| read_only | tinyint(4) | YES | | 0 | |
| initialised | tinyint(4) | YES | | 0 | |
| default_activity_id | bigint(20) | YES | | NULL | |
| start_xcoord | int(11) | YES | | NULL | |
| start_ycoord | int(11) | YES | | NULL | |
| end_xcoord | int(11) | YES | | NULL | |
| end_ycoord | int(11) | YES | | NULL | |
| stop_after_activity | tinyint(4) | NO | | 0 | |
| transition_to_id | bigint(20) | YES | MUL | NULL | |
| transition_from_id | bigint(20) | YES | MUL | NULL | |
+---------------------------+--------------+------+-----+---------+----------------+
48 rows in set (0.00 sec)
The learning_design_id and the tool_content_id will give you the MCQ instance activity that runs for this lesson.
Here's the description of the MCQ content table
mysql> desc tl_lamc11_content;;
+----------------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+------------+------+-----+---------+----------------+
| uid | bigint(20) | NO | PRI | NULL | auto_increment |
| content_id | bigint(20) | NO | UNI | NULL | |
| title | text | YES | | NULL | |
| instructions | text | YES | | NULL | |
| creation_date | datetime | YES | | NULL | |
| update_date | datetime | YES | | NULL | |
| reflect | tinyint(1) | NO | | 0 | |
| questions_sequenced | tinyint(1) | NO | | 0 | |
| created_by | bigint(20) | NO | | 0 | |
| run_offline | tinyint(1) | NO | | 0 | |
| define_later | tinyint(1) | NO | | 0 | |
| offline_instructions | text | YES | | NULL | |
| online_instructions | text | YES | | NULL | |
| content_in_use | tinyint(1) | NO | | 0 | |
| retries | tinyint(1) | NO | | 0 | |
| pass_mark | int(11) | YES | | NULL | |
| show_report | tinyint(1) | NO | | 0 | |
| reflectionSubject | text | YES | | NULL | |
| showMarks | tinyint(1) | NO | | 0 | |
| randomize | tinyint(1) | NO | | 0 | |
| displayAnswers | tinyint(1) | NO | | 1 | |
| submission_deadline | datetime | YES | | NULL | |
+----------------------+------------+------+-----+---------+----------------+
22 rows in set (0.00 sec)
Now, that was all the prep to answer your question 
The data that holds the students' result is tl_lamc11_usr_attempt:
mysql> desc tl_lamc11_usr_attempt;
+-------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------------+------+-----+---------+----------------+
| uid | bigint(20) | NO | PRI | NULL | auto_increment |
| que_usr_id | bigint(20) | NO | MUL | NULL | |
| mc_que_content_id | bigint(20) | NO | MUL | NULL | |
| mc_que_option_id | bigint(20) | NO | MUL | NULL | |
| attempt_time | datetime | YES | | NULL | |
| isAttemptCorrect | tinyint(1) | NO | | 0 | |
| mark | varchar(255) | YES | | NULL | |
| passed | tinyint(1) | NO | | 0 | |
| attemptOrder | int(11) | NO | | 1 | |
| finished | tinyint(1) | YES | | 0 | |
+-------------------+--------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)
In there and linking all these tables together, you can get the result for a specific user.
I hope I didn't confuse you more
Thanks,
Ernie
Posted by Ernie Ghiglione
