Document containing important tables for moodle integration with eHRHub learning.

JNVLE - Moodle Integration.md

JN Moodle Integration

Courses

Interaction with the following tables is required to get courses information.

TableName: mdl_course

Table Structure:

• id bigint(10) NOT NULL AUTO_INCREMENT,
• category bigint(10) NOT NULL DEFAULT '0',
• sortorder bigint(10) NOT NULL DEFAULT '0',
• fullname varchar(254) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
• shortname varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
• idnumber varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
• summary longtext COLLATE utf8_unicode_ci,
• summaryformat tinyint(2) NOT NULL DEFAULT '0',
• format varchar(21) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'topics',
• showgrades tinyint(2) NOT NULL DEFAULT '1',
• sectioncache longtext COLLATE utf8_unicode_ci,
• modinfo longtext COLLATE utf8_unicode_ci,
• newsitems mediumint(5) NOT NULL DEFAULT '1',
• startdate bigint(10) NOT NULL DEFAULT '0',
• marker bigint(10) NOT NULL DEFAULT '0',
• maxbytes bigint(10) NOT NULL DEFAULT '0',
• legacyfiles smallint(4) NOT NULL DEFAULT '0',
• showreports smallint(4) NOT NULL DEFAULT '0',
• visible tinyint(1) NOT NULL DEFAULT '1',
• visibleold tinyint(1) NOT NULL DEFAULT '1',
• groupmode smallint(4) NOT NULL DEFAULT '0',
• groupmodeforce smallint(4) NOT NULL DEFAULT '0',
• defaultgroupingid bigint(10) NOT NULL DEFAULT '0',
• lang varchar(30) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
• theme varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
• timecreated bigint(10) NOT NULL DEFAULT '0',
• timemodified bigint(10) NOT NULL DEFAULT '0',
• requested tinyint(1) NOT NULL DEFAULT '0',
• enablecompletion tinyint(1) NOT NULL DEFAULT '0',
• completionnotify tinyint(1) NOT NULL DEFAULT '0',

PRIMARY KEY (id)

---

TableName: mdl_enrol

Table Structure:

• id bigint(10) NOT NULL AUTO_INCREMENT,
• enrol varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
• status bigint(10) NOT NULL DEFAULT '0',
• courseid bigint(10) NOT NULL,
• sortorder bigint(10) NOT NULL DEFAULT '0',
• name varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
• enrolperiod bigint(10) DEFAULT '0',
• enrolstartdate bigint(10) DEFAULT '0',
• enrolenddate bigint(10) DEFAULT '0',
• expirynotify tinyint(1) DEFAULT '0',
• expirythreshold bigint(10) DEFAULT '0',
• notifyall tinyint(1) DEFAULT '0',
• password varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
• cost varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
• currency varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
• roleid bigint(10) DEFAULT '0',
• customint1 bigint(10) DEFAULT NULL,
• customint2 bigint(10) DEFAULT NULL,
• customint3 bigint(10) DEFAULT NULL,
• customint4 bigint(10) DEFAULT NULL,
• customint5 bigint(10) DEFAULT NULL,
• customint6 bigint(10) DEFAULT NULL,
• customint7 bigint(10) DEFAULT NULL,
• customint8 bigint(10) DEFAULT NULL,
• customchar1 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
• customchar2 varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
• customchar3 varchar(1333) COLLATE utf8_unicode_ci DEFAULT NULL,
• customdec1 decimal(12,7) DEFAULT NULL,
• customdec2 decimal(12,7) DEFAULT NULL,
• customtext1 longtext COLLATE utf8_unicode_ci,
• customtext2 longtext COLLATE utf8_unicode_ci,
• customtext3 longtext COLLATE utf8_unicode_ci,
• customtext4 longtext COLLATE utf8_unicode_ci,
• timecreated bigint(10) NOT NULL DEFAULT '0',
• timemodified bigint(10) NOT NULL DEFAULT '0',

PRIMARY KEY (id),

---

TableName: mdl_course_completions

Table Structure:

• id bigint(10) NOT NULL AUTO_INCREMENT,
• userid bigint(10) NOT NULL DEFAULT '0',
• course bigint(10) NOT NULL DEFAULT '0',
• timeenrolled bigint(10) NOT NULL DEFAULT '0',
• timestarted bigint(10) NOT NULL DEFAULT '0',
• timecompleted bigint(10) DEFAULT NULL,
• reaggregate bigint(10) NOT NULL DEFAULT '0',

PRIMARY KEY (id),

UNIQUE KEY mdl_courcomp_usecou_uix (userid,course),