Reports - marist-sga/documentation GitHub Wiki
Every semester, Student Life will need the club rosters and Priority Point reports. To get this information, you’ll need to fetch the spreadsheets from the server.
$ scp -r [email protected]:/data/sga_webapps/htdocs/clubdash/app/storage/prioritypoints/SEMESTER/* reports/
Of course, you’ll need to replace USERNAME and SEMESTER with your Marist username (k account) and the current semester (e.g., F2017 for the Fall of 2017).
Additionally you could use something like CyberDuck to SFTP onto the server and navigate to that directory to get the reports.
Student Life will also request event evaluation data in a format accessible by Microsoft Excel. You’ll need to log into phpMyAdmin (see its section for log in details). Change to the SQL tab, and run the following query:
SELECT clubs.name AS ClubName, events.eventTitle, events. meetingDate, eventEvaluations. *
FROM eventEvaluations
JOIN events ON eventEvaluations.eventID = events.id
JOIN clubs ON events.clubID = clubs.clubID
WHERE events.academicYear = <YEAR> AND events.academicSemester = <"S"pring or "F"all>
ORDER BY clubs.name;Export this data as CSV (not MS Excel) delimited by a comma (,) and with headers on.
Student Life will also want to know how much an officer logged in and when they logged in. Run this query in phpMyAdmin:
SELECT officers.clubID, clubs.name, 100 * SUM(CASE WHEN officers.lastLogin IS NULL THEN 1
ELSE 0 END ) / COUNT( officers.cwid ) AS pctOfficersNeverLoggedIn ,
SUM(CASE WHEN officers.lastLogin IS NULL THEN 1 ELSE 0
END ) AS numNeverLoggedIn, COUNT( officers.cwid ) AS countOfficers
FROM officers
JOIN clubs ON officers.clubID = clubs.clubID GROUP BY officers.clubID;Export it the same way as the Priority Point and Roster reports (CSV, comma-delimited, with headers).
SELECT * FROM `allocationEvents`
INNER JOIN `allocationLineItems`
ON allocationEvents.id = allocationLineItems.eventID
INNER JOIN `clubs`
ON clubs.clubID = allocationEvents.clubID
WHERE clubs.name = <club name from club table name column>
AND allocationEvents.allocationTerm = <SemesterYear, e.g. F2017 or S2018> SELECT fName, lName, email, cwid, positions.positionName, positions.abbr, clubs.name as "club"
FROM clubs
INNER JOIN officers USING (clubID)
INNER JOIN positions ON (officers.position_id = positions.positionID)
INNER JOIN students USING (cwid)
WHERE clubs.active = 1
ORDER BY name ASCThis is what I (William Kluge) would use to get the attendance reports for Computer Society's priority points. Obviously, change the academic year, semester, and club ID to match whatever you're searching for.
SELECT DISTINCT students.cwid, students.lName, students.fName, COUNT(students.cwid)
FROM `students`
INNER JOIN attendance
ON students.cwid = attendance.cwid
INNER JOIN events
ON attendance.eventID = events.id
AND events.academicYear = 2019
AND events.academicSemester = 'S'
AND clubID = 'CL025'
GROUP BY cwid ORDER BY COUNT(students.cwid) DESC;