As I’ve posted about before, I’m trying to find a way to make Gibbon work well for report cards (and ideally transcripts). Reporting module isn’t flexible enough for what we need.
I’ve figured out that Internal Assessment might be a good way for us to save final grades. It keeps the data, can be accessed by query builder, and includes comments (something we need). It does mean teachers have to manually enter final grades, but that’s true of the Reporting Module as well.
So far I’ve managed to put together a query that contains most of the data I do want, none of the data I do not want, and gives column names that I like. I’ve also managed to write up a column that automatically assigns the letter grade based on the percentage reported from Internal Assessment. Automatically assigning grade points should be easy after that, though I do have to figure out how to handle AP classes–unless there’s already an entry somewhere for that? I can’t remember there being one…
The most critical thing I still want to do is pull in the Internal Assessment Comments for each class. Is this possible? I’m assuming I just need a column name that I do not know to add to my SELECT command.
Since I’m trying to pull more things in, I would also love to pull in teacher surname, though my handful of attempts to figure out how have been unsuccessful. If this isn’t a quick, easy thing to get I think I can live without it.
While I’m at it, is there a list of schema and variables for the Gibbon SQL Database? I thought I saw one previously, but I can’t for the life of me find it now.
As I’m making requests anyway, is there any easy way to have my grade points column also include an average at the end? This part isn’t a huge deal, as we will most likely paste the Gibbon output into an Excel-based report card template, so I can aways have Excel do the final GPA calculation if I need to.
Ooooh, I’m also wondering if it’s possible to download my Gibbon SQL Database and toss it into a local copy of MySQL (or the MS equivalent) to play with there.
Sounds like you’ve made some great progress! To see the whole schema, look for the Help icon in Query builder near the top-right of the text editor (looks like a life buoy).
Yep, you should be able to download your full database. If you have a tool like PHPMyAdmin, do a full export of the database. Otherwise, on the command line you can likely use the mysqldump command, something along the lines of mysqldump -u yourdbusername -p yourdbname > /path/to/export/gibbon.sql` (perhaps google for the specific parameters).
From there, when importing your database into localhost, be sure to check and update the absoluteURL and absolutePath in the gibbonSetting table, as they will likely be different for your local machine.
Awesome. I also remembered that, as I identify various schema from the example mark books, I was able to SELECT * FROM schemaName to see all of the columns. So I’ve made a ton of progress since yesterday, including adding comments, and bringing in Effort score (which we are using as Exam score, and string replace managed to change the name of for me).
The main thing I’m unable to do that I would like is bringing in teacher surnames in a column next to class name. But it’s impossible (at least based on my current SQL knowledge) as the teacher surnames are in the same column in gibbonPerson as student surnames, and I don’t think I can bring it in twice in two different columns and a separate lookup criteria. If I’m missing something obvious I’d love to hear it, though!
I’ve also decided it makes more sense to do the averages in Excel. Gibbon can pour out a long table for me that I can paste into Excel, and then take what we will use into the report card template, and that template can automatically handle the averages.
Great to hear! Yep, to join the same table multiple times you can give it an alias, eg: JOIN gibbonPerson AS teacher`
In this case there could be more than one teacher, so a sub-query join may help: (without knowing your query syntax this is just an example)
`JOIN (
SELECT gibbonCourseClassPerson.gibbonCourseClassID, gibbonPerson.surname, gibbonPerson.preferredName, gibbonPerson.title
FROM gibbonCourseClassPerson
JOIN gibbonPerson ON (gibbonPerson.gibbonPersonID=gibbonCourseClassPerson.gibbonPersonID)
WHERE gibbonCourseClassPerson.role='Teacher'
LIMIT 1
) AS teacher ON (teacher.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)`
```
And then use teacher.suname`, teacher.preferredName`, etc to access the data.
I’m going to play with that later. I’m SO CLOSE to what I want! But I’ve run into a huge snag. Once I run my query, I get a big table, and it copies cleanly into Excel. But it doesn’t bring in the entire comment, just the first part and …
This was heartbreaking, because I thought I was where I needed to be for this grade generation. But without those comments I’m not.
Any ideas? If I can get the Query Builder Excel output working, will that have the complete comment? Is my only option going to be to find a way to run my Query using a different tool other than Query Builder?
Here’s an interesting twist (and an intersection with my Markbook download problem):
Downloading Query Builder results as a CSV file works fine, and includes the full comments. Excel and ODF both fail with the zero kb file.
So basically things are working fine without teacher name. I’ll play with your query to see if I can get that added, but I’m good enough to go right now!
And I am planning to post my Query once I’m done. Just want to get it there first.
Sounds like you’re making progress. Yep, it’s truncating the text to keep the table from visually getting overloaded. The full text will be in the excel/csv output.
A 0kb file s very likely an error in the export script, possibly in the Excel-related classes. Any luck locating your PHP error logs? You could turn on display errors to see if you can view them in the browser (set System Install Type to Development & add this snippet to the bottom of your config.php):
`error_reporting(E_ALL);
ini_set('display_errors', 1);`
```
Just be sure to take it out later if it's a production system.
I tried out the code you added, and poked at it a bit, but it isn’t working. I’m too new at SQL to figure out why (if the JOIN commands weren’t already included in the initial provided Query I started with I don’t think I could have pulled this out without a lot more study, as I really don’t have a good working knowledge or even conceptualization of how they work).
I’m going to include my script here. If what you posted before can be easily adapted I’d love to do so, but if not it’s not the end of the world.
This query provides a table output that shows year in school, student name, class name, overall percentage, letter grade, grade points, and teacher comments.
Letter grade is automatically calculated by the query based on the overall percentage, using what I believe is the US-standard breakdown.
Grade points are calculated based on overall percentage as well, using what I believe to be the US standard system where B-, B, and B+ are all 3.0. A course whose name begins with AP is automatically considered a College-Board-approved AP course, and is given an extra grade point. This determination is made solely by whether the course name begins with the characters “AP”.
The results are ordered by year in school, student last name, student first name, and then class name. The output is (at least for us) ready to be downloaded as a file and then copied and pasted into our Excel-based report card template (which automatically calculates the GPA for the term). When pasting, obvious you should paste “Values Only” for best results.
SELECT gibbonYearGroup.name AS Year, surname AS Last, preferredName AS First, gibbonCourse.name AS Class, gibbonInternalAssessmentEntry.attainmentValue AS Percentage,
CASE
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 93 THEN ‘A’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 93 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >=90 THEN ‘A-’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 90 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 87 THEN ‘B+’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 87 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 83 THEN ‘B’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 83 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 80 THEN ‘B-’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 80 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 77 THEN ‘C+’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 77 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 73 THEN ‘C’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 73 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 70 THEN ‘C-’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 70 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 67 THEN ‘D+’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 67 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 63 THEN ‘D’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 83 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 60 THEN ‘D-’
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 60 THEN ‘F’
ELSE ‘Error’
END AS Grade,
CASE
WHEN gibbonCourse.name LIKE ‘AP%’ THEN CASE
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 90 THEN 5.0
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 90 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >=80 THEN 4.0
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 80 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 70 THEN 3.0
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 70 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 60 THEN 2.0
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 60 THEN 0.0
ELSE ‘Error1’
END
ELSE
CASE
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 90 THEN 4.0
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 90 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >=80 THEN 3.0
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 80 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 70 THEN 2.0
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 70 AND CAST(TRIM(‘%’ FROM attainmentValue) AS INT) >= 60 THEN 1.0
WHEN CAST(TRIM(‘%’ FROM attainmentValue) AS INT) < 60 THEN 0.0
ELSE ‘Error2’
END
END AS ‘Grade Points’,
gibbonInternalAssessmentEntry.comment AS Comment
FROM gibbonPerson
JOIN gibbonStudentEnrolment ON (gibbonStudentEnrolment.gibbonPersonID=gibbonPerson.gibbonPersonID)
JOIN gibbonYearGroup ON (gibbonStudentEnrolment.gibbonYearGroupID=gibbonYearGroup.gibbonYearGroupID)
JOIN gibbonInternalAssessmentEntry ON (gibbonInternalAssessmentEntry.gibbonPersonIDStudent=gibbonPerson.gibbonPersonID)
JOIN gibbonInternalAssessmentColumn ON (gibbonInternalAssessmentEntry.gibbonInternalAssessmentColumnID=gibbonInternalAssessmentColumn.gibbonInternalAssessmentColumnID)
JOIN gibbonCourseClass ON (gibbonInternalAssessmentColumn.gibbonCourseClassID=gibbonCourseClass.gibbonCourseClassID)
JOIN gibbonCourse ON (gibbonCourseClass.gibbonCourseID=gibbonCourse.gibbonCourseID)
JOIN gibbonSchoolYear ON (gibbonStudentEnrolment.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID AND gibbonSchoolYear.status=‘Current’)
WHERE gibbonCourse.gibbonSchoolYearID=gibbonSchoolYear.gibbonSchoolYearID
AND gibbonPerson.status=‘Full’
ORDER BY Year, Last, First, Class
`
