Tag Archive for blackbaud

Open Blackbaud group

Come join the Open Blackbaud group! Adam Gerson and I want to get together individuals who are writing SQL queries directly against Blackbaud database tables. We have found a number of people doing this sort of work on their own, so it seems like a good time to share our new knowledge. Already, Adam, Tom Phelan, and I have posted a number of queries that others may find useful.


Import classes from Education Edge into Moodle

Here are notes on my successful import of 200 classes into Moodle. This is part of a project to create a major assignments calendar for our upper school that calculates the number of major assignments by student rather than by grade, providing a more accurate view of potential conflicts before they happen.

First, I wrote a Perl script to export classes from Education Edge into a text file. I used the following SQL query and then formatted the results pretty.

deptentries.description as coursedept,
periodstable.entryid as period,
ea7faculty.userdefinedid as facultyid,
ea7records.passportnumber as facultyusername,
inner join schools on (schools.schoolsid = ea7courses.schoolsid)
inner join ea7classes on (ea7classes.ea7coursesid = ea7courses.ea7coursesid)
inner join ea7sessions on (ea7sessions.ea7sessionsid = ea7classes.ea7sessionsid)
inner join ea7academicyears on (ea7academicyears.ea7academicyearsid = ea7sessions.ea7academicyearsid)
inner join ea7classterms on (ea7classterms.ea7classesid = ea7classes.ea7classesid)
inner join ea7terms on (ea7terms.ea7termsid = ea7classterms.ea7termsid)
inner join tableentries as termstable on (termstable.tableentriesid = ea7terms.termid)
left outer join ea7coursefilters on (ea7coursefilters.parentid = ea7courses.ea7coursesid and ea7coursefilters.filtertype=163)
left outer join filtervalues7 on (filtervalues7.parentid = ea7coursefilters.filtersid and filtervalues7.recordtype = 1098)
left outer join tableentries as deptentries on (deptentries.tableentriesid= filtervalues7.filteridvalue1)
left outer join ea7classtermmeetings on (ea7classtermmeetings.ea7classtermsid = ea7classterms.ea7classtermsid)
left outer join ea7timetableentries on (ea7timetableentries.ea7timetableentriesid = ea7classtermmeetings.ea7timetableentriesid)
left outer join tableentries as periodstable on (periodstable.tableentriesid = ea7timetableentries.period)
left outer join ea7facultyclasstermmeetings on (ea7facultyclasstermmeetings.ea7classtermmeetingsid = ea7classtermmeetings.ea7classtermmeetingsid)
left outer join ea7rooms on (ea7rooms.ea7roomsid = ea7classtermmeetings.ea7roomsid)
left outer join ea7faculty on (ea7faculty.ea7facultyid = ea7facultyclasstermmeetings.ea7facultyid)
left outer join ea7records on (ea7records.ea7recordsid = ea7faculty.ea7recordsid)
schools.schoolid = 'US'
and ea7academicyears.description = '2008-2009'
and ea7courses.ea7coursesid <> 404


Then I installed and ran Moodle's Upload Courses contributed module. What a nice job the author did with this. Once I cleared all of the ambiguous teacher names, the courses imported in a flash.

upload results

I decided to create one Moodle class for each course/teacher combination. In other words, if one teacher teaches one section of ninth grade English, and a second teacher teaches two sections, I would create two Moodle courses. Teachers are pretty autonomous at our school, and this structure parallels what most teachers did with Moodle when I created courses only upon request. In cases where the teachers actually co-teach the course, I will need to remove one course and double up the teachers in the other remaining.

I embedded teacher initials in course IDs to distinguish them and provide a key for subsequent Education Edge searches based on this information, but will have to go back and embed them in the course fullnames as well -- the students can't currently tell the courses apart, because they only see the full names!

Rather than importing enrollments as well, I will allow students to enroll themselves on the first day of classes.

I edited config.php to show a limited set of default blocks for all the new courses. Unfortunately, the major assignments block I am working on did not show up, so I will have to add that subsequently with a script.

I am still working on a major assignments block for our Moodle that borrows concepts and SQL queries from Gary Anderson and pulls course enrollments from Education Edge. More on that later.