getting moodle roles

SELECT u.idnumber, u.firstname, u.lastname, r.id, r.roleid, c.idnumber AS m_name, c.id AS c_id, role.shortname
FROM mdl_user u
LEFT JOIN mdl_role_assignments r ON u.id = r.userid
LEFT JOIN mdl_role role ON r.roleid = role.id
LEFT JOIN mdl_context c1 ON r.contextid = c1.id
LEFT JOIN mdl_course c ON c1.instanceid = c.id
WHERE u.idnumber =  'p.fagan'
AND (
c.idnumber NOT LIKE  'C%'
AND c.idnumber NOT LIKE  '%-E-%'
AND c.idnumber NOT LIKE  '%-S-%'
AND c.idnumber NOT LIKE  '%-A-%'
AND c.idnumber NOT LIKE  '%-P-%'
AND c.idnumber NOT LIKE  '%-AD-%'
)

Moodle - creating courses from en bulk with templates

I've gone for the following solution:
https://github.com/piersharding/moodle-tool_uploadcourse

There are a few quirks to it. I created the following csv:
============
fullname, shortname, category, idnumber, summary, format, numsections, maxbytes, visible,backupfile
------------------
TEST-1,TEST-1,Cross-College/Admin and Development Cross-College,TEST-1,test,topics,8,10485760,FALSE,C:/Downloads/backup-moodle2-course-admin-template-20130605-1642.mbz
============

I then use the CLI to run it (in windows open a cmd using Administrator rights):

C:\xampp\php>php.exe C:/WEB-APPS/moodleLIVE/admin/tool/uploadcourse/cli/uploadcourse.php --action=addupdate --file=C:/temp/fileimportlist1.csv

I really want to get a metacourse enrolment on each of these courses, so looking at the documentation I can't see much to guide me, maybe:

fullname	shortname	category	idnumber	summary	enrolmethod_1	link_1
Parent	Parent		Parent	Parent	meta	873
Students	Students		Students	Students	meta	862
Teachers	Teachers		Teachers	Teachers	meta	128

###code to import

php.exe C:/WEB-APPS/moodleLIVE/admin/tool/uploadcourse/cli/uploadcourse.php --action=addupdate --file=C:/temp/fileimportlist1.csv -s

###code to delete

php.exe C:/WEB-APPS/moodleLIVE/admin/tool/uploadcourse/cli/uploadcourse.php --action=addupdate --mode=delete --file=C:/temp/fileimportlist1.csv

##code to build from a template zipped file (defined in the CSV)

php.exe C:/WEB-APPS/moodleLIVE/admin/tool/uploadcourse/cli/uploadcourse.php --action=addupdate --file=C:/temp/fileimportlist1.csv

you can also run it from the web link: moodleaddress/admin/tool/uploadcourse/index.php.  From here you can add, update and delete


To bulk import courses you can use the following:

https://github.com/piersharding/moodle-tool_uploadcoursecategory

To access it go to:
Settings -> Site administration -> Courses -> Upload course categories


-----------------------------------------

Other research is below:

Code to list all courses in your moodle install along with the categories

 SELECT `mdl_course`.`id`, `mdl_course`.`category`, `mdl_course`.`fullname`, `mdl_course`.`shortname`, `mdl_course`.`idnumber`, `mdl_course`.`numsections`, `mdl_course_categories`.`description`, `mdl_course_categories`.`id`, `mdl_course_categories`.`parent`
 FROM `mdl_course_categories`
INNER JOIN `mdl_course`
ON `mdl_course`.`category` = `mdl_course_categories`.`id`

The topics on the page seem to be related like so:

the table course contains a field called sequence
sequence contains a list of comma separated ids for the table course_section

label table linked to course table through the id

course_modules table has 3 fields:

• module the id from the module table specifying the module type e.g. 5 for forum.  Where are these ids from?  Is there a table that defines them?
• instance the id of the module you are link to, e.g. 7 here for a label would link to the label table id 7
• section the id of the course_section that it is linking to

Steps to implement:

1. create course and get id
2. create course_section and get id
3. add course_section ids to the sequence field on course
4. create instance of the desired module eg. label get the id
5. create course_modules for each module needed linking course, instance (label id), module, section (course_section id)

Appendix:

module table:

1. assignment
2. chat
3. choice
4. data
5. forum
6. glossary
7. hotpot
8. label
9. lesson
10. quiz
11. resource
12. scorm
13. survey
14. wiki
15. workshop
16. questionnaire
17. feedback
18. folder
19. imscp
20. lti
21. page
22. url
23. checklist
24. lightbox

mdl_course_modules: put this data in these fields

• module: 5 . Seems to be a 'type' of the module and #5 it's a module of type FORUM.
• instance: the ID of the forum in mdl_forum (the id of the forum we just created)
• section: it will contain the ID of the section in which we will put this module in mdl_course_sections (see next table explanation). I could get the forum to work without setting this field properly though...

How does label get linked to course section?  does sequence have to contain a unique list?