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.
Tag Archive for blackbaud
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.
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.