Posted by nikhiljha on May 20, 2010 at 3:00pm
Hi,
I have been implementing website/crm solution for a school. And I am stuck at implementing a attendance system for it. This attendance system will also include attendance of teachers. Right now I am creating a form with the name of students and giving select list options (present,absent,others) in front of them.
but this solution is not very elegant and analyzing it really difficult. Can anyone help me in this area.
Thanks
Nikhil
Comments
A taste of what is possible
I have a student database / CRM system implemented with Drupal which works fairly well. I also use a form with select lists, but a nice bit of jQuery to change the background colour depending on what's selected, and some more to be create a link setting all the students to present for a particular session helps with the usability, as does changing the background colour of the table rows on mouseover.
The whole system is quite a lot of custom code (12,000+ lines) split across a few custom modules. At the moment it's pretty tied to our own needs but one of these days I'd really like to tidy things up, generalise it slightly, split functionality into a few more distinct modules and release. If I were to start from scratch I'd probably make many entities real Drupal nodes, but other than it makes good use of the Drupal API's. It also doesn't do as much as it should when displaying user entered text to protect against xss and that might be nice to fix before releasing but for my purposes I can trust my administrators not to try and hack their way to higher privileges.
Attendance System
Hi,
I like your work on this attendance system.
I also need this kind of attendance sheet system for users of each class with holidays also.
Please share your approach to do this.
Well, most of the relevant
Well, most of the relevant code is posted below (at least the state it was in 18 months ago). At the moment it's one massive custom D6 module and several much smaller supporting modules. The main module is at the moment far too site specific to be of use to anyone else unfortunately. I've been meaning for a long time to divide this into a number of smaller modules and in the process split off some of our local idiosyncrasies so that it's more reusable by others. Maybe things will be quieter over this summer break?
Attendance System
Hi,
Thanks for the reply .
I have all the information(name,standard) with class .
I need an attendance system that will manage attendance with edit features like your first screen shot that listed user with full attendance.
Looking forward to your response
Unfortunately I think you're
Unfortunately I think you're going to have to write a lot of code to do so. A starting point might be what I posted below, but you're going to need to customise it for your environment.
To deal with attendance per teaching session you need to think about a sensible way of entering the timetable data as well.
Can u please share your
Can u please share your database flow diagram .
For Class , User and attendance diary.
I'm not sure how this will help but...
I'm not sure how this will really help, but here's the part of the database that's relevant:
edit: broke g.d.o theme with large image! Click for a larger version.
Thanks for the snap. I need
Thanks for the snap.
I need the code which is used in listing for
http://www.mdryan.net/sis/images/worst-attendance-list.png
Please share your contributed module full listing code and database tables so that I would also try to implement your great module.
Thanks
Really looking awesome. I
Really looking awesome. I really like the way your system is handling attendance. how do you update your database everytime you make change in attendace table. Are you using ajax to enter only recent changes in database?
how is your system doing performance wise?
If you need any helping hand in tiding up your code.. count me in :)
Nikhil
The database update is fairly
The database update is fairly trivial - there's a save buttton at the bottom of the form and the form submit function just updates the attendance table (which is keyed on the student and teachingsession ids).
That attendance form with all the select elements and javascript can be a little slow in IE when a large form is generated, but is fine in Chrome or FF. The slowest page generation time on the site is also actually for that page at up to 2 or 3 seconds at times. Whilst there's some scope to optimise things a little this has been good enough for our needs and it appears that most of that time is spent in the forms api so there'd be a fairly limited payback for my brain wrestling with a more efficient query.
Generally though the system is pretty responsive, dealing with a little under 400 active students. There's a total of 120k records in 28 tables of data, of which the attendance table is about 75k rows. It makes a lot of use of the phpExcel libarary and can export most data to Excel which is a nice feature. I imported some of our legacy data from Excel using the same library.
I'm pretty tied up with other things the next week or two, but after that I'll look into getting this out as a set of modules for people to play with and adapt.
S.O.S
I am trying to configure an attendance system as well. I have read the "Event Management" chapter of Using Drupal. The chapter suggests using the flags module, but only shows how to configure it like an R.S.V.P. system.
I have spent forever on the views screen trying to get flags to work.
I would make my on custom module but i am just barely starting php and sql, so that solution would likely take even longer.
Does anyone know of a way to do this? it seems like a very simple problem compared to the other things there are modules for.
I have already tried gradebook+gradebook api+attendance. There is something wrong with that module.
Or if anyone had any tips for me configuring flags it would be greatly appreciated.
Thanks!
Since you referenced using
Since you referenced using event management, should one assume you want to manage event registration? Have you looked at http://drupal.org/project/cck_signup? It says it creates nodes for the registrants so that would help when creating a view listing of attendees - I suppose.
Hi, I am also interested in
Hi,
I am also interested in having more information on how you managed to create this system. Any hint would be more than appreciated.
Thanks
T
Glad to see the interest
I'm happy to see that there's some interest in this. Looks like I should make some effort to get it in a state that it can be released. It's nearly the end of semester so maybe I'll have time during the forthcoming break. It's not all that Druplish in that most of the data is stored in a separate database and not as Drupal nodes, but does use the Drupal API and a lot of custom module code.
Watch this space...
Matt
Hello mdryan, Thanks for
Hello mdryan,
Thanks for getting back at us. I suppose my most immediate concern is in the actual drupal part of your projeect. Specifically, I want to know how you build the view where you can select attendance in a select list format. And I was also interested in the ways you built the whole view.
Thanks
T
Well, first off I should
Well, first off I should clarify that the only contrib modules I really make much use of on this site are Date API, Date Popup, Nice Menus, Panels and their dependencies. Of these only really Date API does any heavy lifting. I also make a lot of use of phpExcel and tcpdf for import and export of data to and from Excel and to PDF, plus admin_menu, schema, coder, devel etc during development. Had I been starting again now I'd have put a bit more effort into making things custom node types and integrating with tokens, views, etc. I might slowly refactor things in that direction but it would be a lot of work to do so at this stage.
To try to answer your specific question, that first screen shot starts with the filter form which is built with a few helper functions but is basically just a stock FAPI form with a submit function that stuffs the things to filter by in $_SESSION. I use this pattern a lot over the site and it's modelled on what happens in core, e.g. in filtering watchdog messages.
The second form with all the select boxes is built in a couple of stages. I first build a query based on the variables in $_SESSION to work out which teaching sessions match the selected criteria. Next I pull the students who are supposed to attend these teaching sessions or already have attendance data saved. (This gets a little messy because both teaching groups and individual students can be registered to attend each teaching session and teaching group make up may change over the course of a timetabled semester). I work out some simple stats and build a form which is then rendered with a custom theme function and a bit of jquery.
Not sure how useful it will be but here is the code:
<?php/**
* Attendance main page
*
* @return drupal page content
*/
function sis_attendance_page() {
$output = drupal_get_form('sis_attendance_filter_form');
if (!empty($_SESSION['sis_attendance_filter'])) {
$output .= drupal_get_form('sis_attendance_booklet_form');
}
return $output;
}
?>
<?php/**
* Attendance booklet form
*
* @param $form_state
* @see sis_attendance_booklet_form_submit()
*/
function sis_attendance_booklet_form($form_state) {
$teachingsessions = _sis_attendance_booklet_teachingsessions();
if (empty($teachingsessions)) {
return array(array('#value' => t('No teaching sessions found matching these criteria.')));
}
$attendance = _sis_attendance_booklet_attendance_records(array_keys($teachingsessions));
$student_stats = _sis_attendance_booklet_student_stats($attendance);
$form = array('#tree' => TRUE);
foreach (array_keys($student_stats) as $cdutID) {
foreach (array_keys($teachingsessions) as $teachingsessionID) {
if (isset($attendance[$teachingsessionID][$cdutID])) {
$form['attendance'][$cdutID][$teachingsessionID] = array(
'#type' => 'select',
'#options' => sis_get_attendance_options(),
'#default_value' => array($attendance[$teachingsessionID][$cdutID] => $attendance[$teachingsessionID][$cdutID]),
);
}
else {
$form['attendance'][$cdutID][$teachingsessionID] = array(
'#value' => 'NA',
);
}
}
}
$form['student_stats'] = array('#type' => 'value', '#value' => $student_stats);
$form['teachingsessions'] = array('#type' => 'value', '#value' => $teachingsessions);
$form['submit'] = array('#type' => 'submit', '#value' => t('Save'));
return $form;
}
?>
<?php/**
* Get list of teaching sessions matching the current filters
*
* @return array of teachingsessionID, each an associate array of data about teachingsession
*/
// (Most of the rest of the site makes use of a helper function I came up with later for building WHERE clauses based on the session variable,
// but for some reason this particular bit of code was never refactored to use it.)
function _sis_attendance_booklet_teachingsessions() {
$query = "SELECT ts.teachingsessionID, abbreviation AS module, teachingsessiontype, staff, roomID, UNIX_TIMESTAMP(start) AS startunix, length, groupID"
. " FROM {teachingsessions} ts"
. " LEFT JOIN {modules} m ON ts.moduleID = m.moduleID"
. " LEFT JOIN {staff} s ON ts.staffID = s.staffID"
. " LEFT JOIN {teachingsessiontypes} tst ON ts.teachingsessiontypeID = tst.teachingsessiontypeID"
. " LEFT JOIN {groups2teachingsessions} g2ts ON ts.teachingsessionID = g2ts.teachingsessionID";
$where = array();
$args = array();
$session = @$_SESSION['sis_attendance_filter'];
foreach(array('staffID', 'moduleID', 'teachingsessiontypeID') as $filter) {
if (!empty($session[$filter])) {
$or = array();
foreach ($session[$filter] as $value) {
$or[] = "ts.$filter=%d";
$args[] = $value;
}
$where[] = implode(' OR ', $or);
}
}
if (!empty($session['groupID'])) {
$or = array();
foreach ($session['groupID'] as $value) {
$or[] = "groupID='%s'";
$args[] = $value;
}
$where[] = implode(' OR ', $or);
}
if (!empty($session['date_from'])) {
$where[] = "ts.start > '%s'";
$args[] = $session['date_from'];
}
if (!empty($session['date_to'])) {
$where[] = "ts.start < ('%s' + INTERVAL 1 DAY)";
$args[] = $session['date_to'];
}
if (!empty($where)) {
$query .= ' WHERE (' . implode(') AND (', $where) . ')';
}
$query .= ' ORDER BY start';
db_set_active('sis');
$result = db_query($query, $args);
$teachingsessions = array();
while($row = db_fetch_object($result)) {
if(!isset($teachingsessions[$row->teachingsessionID])) {
$teachingsessions[$row->teachingsessionID] = array(
'date' => date('Y-m-d', $row->startunix),
'time' => date('H:i', $row->startunix),
'module' => $row->module,
'teachingsessiontype' => $row->teachingsessiontype,
'staff' => $row->staff,
'roomID' => $row->roomID,
'groups' => array(),
);
}
$teachingsessions[$row->teachingsessionID]['groups'][$row->groupID] = $row->groupID;
}
db_set_active('default');
if (count($teachingsessions)>36) {
drupal_set_message(t('Results limited to a maximum of 36 teaching sessions, please modify your filter to reduce the number of possible results.'));
while (count($teachingsessions)>36) {
array_pop($teachingsessions);
}
}
return $teachingsessions;
}
?>
<?php/**
* Get student attendance for list of teaching sessions
*
* @param array of teachingsessionIDs
* @return array of attendance status by teachingsession and student IDs
*/
function _sis_attendance_booklet_attendance_records($teachingsessionIDs) {
$attendance = array();
db_set_active('sis');
foreach ($teachingsessionIDs as $teachingsessionID) {
$attendance[$teachingsessionID] = array();
$result = db_query("SELECT cdutID"
. " FROM {students2groups} s2g"
. " LEFT JOIN {groups2teachingsessions} g2ts ON s2g.groupID = g2ts.groupID"
. " WHERE teachingsessionID=%d", $teachingsessionID);
while ($student = db_fetch_object($result)) {
$attendance[$teachingsessionID][$student->cdutID] = '-';
}
$result = db_query("SELECT cdutID, status FROM {attendance} WHERE teachingsessionID=%d", $teachingsessionID);
while ($record = db_fetch_object($result)) {
$attendance[$teachingsessionID][$record->cdutID] = $record->status;
}
}
db_set_active('default');
return $attendance;
}
?>
<?phpfunction theme_sis_attendance_booklet_form($form) {
if (!isset($form['teachingsessions'])) {
return drupal_render($form);
}
$tally = array('module' => array(), 'staff' => array(), 'roomID' => array(), 'groups' => array(), 'teachingsessiontype' => array(), 'date' => array());
$common = array();
foreach ($form['teachingsessions']['#value'] as $data) {
foreach (array_keys($tally) as $field) {
if (is_array($data[$field])) {
$bin = implode(', ', $data[$field]);
}
else {
$bin = $data[$field];
}
$tally[$field][$bin]++;
}
}
foreach($tally as $field => $bin) {
if (count($bin) < 2) {
$common[$field] = array_shift(array_keys($bin));
foreach (array_keys($form['teachingsessions']['#value']) as $teachingsessionID) {
unset($form['teachingsessions']['#value'][$teachingsessionID][$field]);
}
}
}
foreach ($form['teachingsessions']['#value'] as $teachingsessionID => $data) {
if (is_array($data['groups'])) {
$groups = array();
foreach ($data['groups'] as $groupID) {
$groups[$groupID] = theme('sis_group', $groupID);
}
$data['groups'] = implode(', ', $groups);
}
if (user_access('edit timetable')) {
$data['edit'] = l('edit', "sis/timetable/card/$teachingsessionID/edit", array('query' => array('destination' => 'sis/attendance')));
}
$data['present'] = '<a class="mark-present" href="#" rel="' . $teachingsessionID . '">' . t('all present') . '</a>';
foreach ($data as $key => $value) {
$headers[$key][$teachingsessionID] = $value;
}
}
$rowspan = count($headers);
$keys = array_keys($headers);
array_unshift($headers[$keys[0]],
array('data' => t('Student'), 'class' => 'name', 'rowspan' => $rowspan),
array('data' => 'P', 'title' => t('Present'), 'class' => 'stats', 'rowspan' => $rowspan),
array('data' => 'L', 'title' => t('Late'), 'class' => 'stats', 'rowspan' => $rowspan),
array('data' => 'A', 'title' => t('Absent'), 'class' => 'stats', 'rowspan' => $rowspan),
array('data' => 'N', 'title' => t('Insufficient Preparation'), 'class' => 'stats', 'rowspan' => $rowspan),
array('data' => 'T', 'title' => t('Total data points'), 'class' => 'stats', 'rowspan' => $rowspan),
array('data' => t('Rate'), 'title' => t('percentage of classes attended'), 'class' => 'stats', 'rowspan' => $rowspan)
);
$rows = array();
foreach (element_children($form['attendance']) as $cdutID) {
$rows[$cdutID] = array();
$rows[$cdutID]['name'] = array(
'data' => theme('sis_student', sis_get_student($cdutID)),
'class' => 'name',
);
$total = 0;
foreach(array(SIS_PRESENT, SIS_LATE, SIS_ABSENT, SIS_NOPREP) as $stat) {
$rows[$cdutID][$stat] = array(
'data' => (int) $form['student_stats']['#value'][$cdutID][$stat],
'class' => 'stats',
);
$total += $rows[$cdutID][$stat]['data'];
}
$rows[$cdutID]['total'] = array('data' => $total, 'class' => 'stats');
$rows[$cdutID]['percent'] = array(
'data' => $total>0?theme('sis_percentage', $rows[$cdutID][SIS_PRESENT]['data']/$total):'-',
'class' => 'stats',
);
foreach (element_children($form['attendance'][$cdutID]) as $teachingsessionID) {
$rows[$cdutID][$teachingsessionID] = array(
'data' => drupal_render($form['attendance'][$cdutID][$teachingsessionID]),
'class' => 'teachingsessionID-'.$teachingsessionID,
);
}
asort($rows);
}
$output = '';
$friendly = array('staff' => t('Tutor'), 'roomID' => t('Room'), 'groups' => t('Teaching groups'), 'module' => t('Module'), 'date' => t('Date'), 'teachingsessiontype' => t('Session type'));
foreach ($common as $field => $value) {
if ($field == 'groups') $value = theme('sis_group', $value);
$output .= theme('sis_display_field', $friendly[$field], $value) . '<br />';
}
$output .= theme('sis_multiheader_table', $headers, $rows, array('class' => 'highlight-rows'));
$output .= drupal_render($form);
return $output;
}
?>
Drupal.behaviors.allPresent = function(context) {
$('#sis-attendance-booklet-form .mark-present').click(function(event) {
var teachingsessionID = $(this).attr('rel');
$('#sis-attendance-booklet-form .teachingsessionID-' + teachingsessionID + ' select').val('1').css('color', 'green');
event.preventDefault();
});
};
Drupal.behaviors.colorcode = function(context) {
$('#sis-attendance-booklet-form [value="0"]').css('color', 'gray');
$('#sis-attendance-booklet-form [value="1"]').css('color', 'green');
$('#sis-attendance-booklet-form [value="2"]').css('color', 'red');
$('#sis-attendance-booklet-form [value="3"]').css('color', 'blue');
$('#sis-attendance-booklet-form [value="4"]').css('color', 'blue');
$('#sis-attendance-booklet-form select').change(function(event) {
$(this).filter('[value="0"]').css('color', 'gray');
$(this).filter('[value="1"]').css('color', 'green');
$(this).filter('[value="2"]').css('color', 'red');
$(this).filter('[value="3"]').css('color', 'blue');
$(this).filter('[value="4"]').css('color', 'blue');
});
};
<?php/**
* Form submission function for sis_attendance_booklet_form
*
* @param $form
* @param $form_state
* @see sis_attendance_booklet_form()
*/
function sis_attendance_booklet_form_submit($form, &$form_state) {
db_set_active('sis');
foreach ($form_state['values']['attendance'] as $cdutID => $record) {
foreach ($record as $teachingsessionID => $status) {
db_query("DELETE FROM {attendance} WHERE cdutID='%s' AND teachingsessionID=%d", $cdutID, $teachingsessionID);
db_query("INSERT INTO {attendance} SET cdutID='%s', teachingsessionID=%d, status=%d", $cdutID, $teachingsessionID, $status);
}
}
db_set_active('default');
drupal_set_message(t('Changes have been saved'));
}
?>
Hello mrdryan, Thank you so
Hello mrdryan,
Thank you so much for your feedback. I went through the files and this is really clever. Obviously, it would of great use to the drupal community to provide a module mirroring the above given its effectiveness and simplicity.
I will try and reproduce the steps you've described (or try to).
Thanks
T
This would be great!
Hi there,
I know it's quite some time ago but i would love to see a ready-to-go module out there with this functionality. Could you please tell me if you'r still working on this project?
Thanks a lot!
Course module
@christof15 there is: check out Course module. It is bundled with Signup integration for enrollment and attendance tracking.
Great system
recently i had to build an attendance for a church and was really wondering how to get views to have checkboxes of absent and present next to each name. This sure seems to point me in the right direction
is there someone be able to
is there someone be able to create this code as a drupal 7 modul?, I will pay for this effort