function run()
{
$name = CRM_Utils_Request::retrieve('name', 'String', $this, true);
$day = CRM_Utils_Request::retrieve('day', 'String', $this, true);
$sess = CRM_Utils_Request::retrieve('sess', 'String', $this, true);
$sql = "\nSELECT c.display_name, c.id\nFROM civicrm_contact c,\n civicrm_value_extended_care s\nWHERE s.name = %1\nAND s.term = %2\nAND s.day_of_week = %3\nAND s.session = %4\nAND s.has_cancelled = 0\nAND s.entity_id = c.id\n";
require_once 'SFS/Utils/ExtendedCare.php';
$params = array(1 => array($name, 'String'), 2 => array(SFS_Utils_ExtendedCare::getTerm(), 'String'), 3 => array($day, 'String'), 4 => array($sess, 'String'));
$dao = CRM_Core_DAO::executeQuery($sql, $params);
$values = array();
while ($dao->fetch()) {
$values[] = array('contact_id' => $dao->id, 'display_name' => $dao->display_name, 'url' => CRM_Utils_System::url('civicrm/profile/view', "reset=1&gid=4&id={$dao->id}"));
}
$this->assign_by_ref('values', $values);
$classDetails = array('name' => $name, 'time' => SFS_Utils_ExtendedCare::getTime($sess), 'day' => $day);
$this->assign($classDetails);
parent::run();
}
function __construct()
{
require_once 'CRM/Core/OptionGroup.php';
require_once 'SFS/Utils/ExtendedCare.php';
$sql = "SELECT column_name,option_group_id FROM civicrm_custom_field WHERE column_name IN('term', 'day_of_week')";
$dao = CRM_Core_DAO::executeQuery($sql);
$options = array();
while ($dao->fetch()) {
$options[$dao->column_name] = CRM_Core_OptionGroup::valuesByID($dao->option_group_id);
}
$sql = " SELECT DISTINCT( name ) as class\nFROM sfschool_extended_care_source\nWHERE is_active = 1";
$dao = CRM_Core_DAO::executeQuery($sql);
while ($dao->fetch()) {
$options['class'][$dao->class] = $dao->class;
}
$this->_columns = array($this->_extentedCareTable => array('dao' => 'CRM_Contact_DAO_Contact', 'filters' => array('term' => array('title' => ts('Term'), 'operatorType' => CRM_Report_Form::OP_SELECT, 'type' => CRM_Utils_Type::T_STRING, 'options' => $options['term'], 'default' => SFS_Utils_ExtendedCare::getTerm()), 'day_of_week' => array('title' => ts('Day Of Week'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'type' => CRM_Utils_Type::T_STRING, 'options' => $options['day_of_week']), 'name' => array('title' => ts('Class'), 'operatorType' => CRM_Report_Form::OP_MULTISELECT, 'type' => CRM_Utils_Type::T_STRING, 'options' => $options['class']))), 'civicrm_student' => array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => array('display_name' => array('no_display' => true, 'required' => true, 'title' => ts('Student')), 'id' => array('no_display' => true, 'no_repeat' => true, 'required' => true))), 'civicrm_parent' => array('dao' => 'CRM_Contact_DAO_Contact', 'fields' => array('display_name' => array('no_display' => true, 'required' => true, 'title' => ts('Parent')), 'id' => array('no_display' => true, 'required' => true))), 'civicrm_email' => array('dao' => 'CRM_Core_DAO_Email', 'fields' => array('email' => array('title' => ts('Email'), 'no_display' => true, 'required' => true))), 'civicrm_phone' => array('dao' => 'CRM_Core_DAO_Phone', 'fields' => array('phone' => array('title' => ts('Phone'), 'no_display' => true, 'required' => true))));
parent::__construct();
}
function buildQuickForm()
{
CRM_Utils_System::setTitle('Afternoon SignIn - Extended Care');
require_once 'SFS/Utils/ExtendedCare.php';
$term = SFS_Utils_ExtendedCare::getTerm($term);
$sql = "\n( \nSELECT c.id as contact_id, c.display_name as display_name, s.name as course_name, v.grade as grade,\n 0 as sout_id, 0 as signout_time, e.location as course_location\nFROM civicrm_contact c\nINNER JOIN civicrm_value_school_information v ON v.entity_id = c.id\nINNER JOIN civicrm_value_extended_care s ON ( s.entity_id = c.id AND s.has_cancelled = 0 AND s.day_of_week = '{$this->_dayOfWeek}' )\nINNER JOIN sfschool_extended_care_source e ON ( s.session = e.session AND s.name = e.name AND s.term = e.term AND s.day_of_week = e.day_of_week ) \nWHERE v.subtype = 'Student'\nAND v.grade_sis >= 1\nAND e.is_active = 1\nAND s.term = %3\n)\nUNION\n(\nSELECT c.id as contact_id, c.display_name as display_name, sout.class as course_name, v.grade as grade,\n sout.id as sout_id, sout.signout_time as signout_time, e.location as course_location\nFROM civicrm_contact c\nINNER JOIN civicrm_value_school_information v ON v.entity_id = c.id\nINNER JOIN civicrm_value_extended_care_signout sout ON sout.entity_id = c.id\nINNER JOIN sfschool_extended_care_source e ON ( sout.class = e.name )\nWHERE v.subtype = 'Student'\nAND v.grade_sis >= 1\nAND ( sout.is_morning = 0 OR sout.is_morning IS NULL )\nAND DATE( sout.signin_time ) = %1\nAND e.is_active = 1\nAND e.day_of_week = %2\n)\nUNION\n(\nSELECT c.id as contact_id, c.display_name as display_name, sout.class as course_name, v.grade as grade,\n -1 as sout_id, 0 as signout_time, e.location as course_location\nFROM civicrm_contact c\nINNER JOIN civicrm_value_school_information v ON v.entity_id = c.id\nINNER JOIN civicrm_value_extended_care_signout sout ON sout.entity_id = c.id\nINNER JOIN sfschool_extended_care_source e ON ( sout.class = e.name )\nWHERE v.subtype = 'Student'\nAND v.grade_sis >= 1\nAND ( sout.is_morning = 0 OR sout.is_morning IS NULL )\nAND DAYNAME( sout.signin_time ) = %2\nAND DATE_ADD( sout.signin_time, INTERVAL 8 DAY ) > '{$this->_date}'\nAND e.is_active = 1\nAND e.day_of_week = %2\nGROUP BY c.id\nORDER BY id DESC\n)\nORDER BY contact_id, sout_id DESC, course_name, display_name, signout_time\n";
$params = array(1 => array($this->_date, 'String'), 2 => array($this->_dayOfWeek, 'String'), 3 => array($term, 'String'));
$dao = CRM_Core_DAO::executeQuery($sql, $params);
$someSignedIn = false;
$studentDetails = array();
while ($dao->fetch()) {
if (array_key_exists($dao->contact_id, $studentDetails)) {
continue;
}
$courseName = $dao->sout_class ? $dao->sout_class : $dao->course_name;
if (empty($courseName)) {
$courseName = $dao->grade <= 5 ? 'Yard Play' : 'Homework';
}
$studentDetails[$dao->contact_id] = array('display_name' => $dao->display_name, 'course_name' => $courseName, 'course_location' => $dao->course_location, 'grade' => $dao->grade, 'contact_id' => $dao->contact_id, 'is_marked' => $dao->sout_id > 0 ? 1 : 0, 'signout_block' => self::signoutBlock($dao->signout_time));
if ($dao->sout_id > 0) {
$someSignedIn = true;
}
}
$this->assign('studentDetails', $studentDetails);
$this->assign('someSignedIn', $someSignedIn);
require_once 'SFS/Utils/Query.php';
$students = array('' => '- Select Student -') + SFS_Utils_Query::getStudentsByGrade(true, false, true, '');
$this->add('select', "student_id", ts('Student'), $students);
$this->add('select', "student_id_top", ts('Student'), $students);
$classes = array('' => '- Select Class -') + SFS_Utils_Query::getClasses();
$this->add('select', "course_name", ts('Course'), $classes);
$this->add('select', "course_name_top", ts('Course'), $classes);
$timeSlots = array('' => '- Select Time -', 1 => 'Before 3:30 pm', 2 => '3:30 - 4:30 pm', 3 => '4:30 - 5:15 pm', 4 => '5:15 - 6:00 pm', 5 => 'After 6:00 pm');
$this->add('select', "signout_time", ts('Signout Time'), $timeSlots);
$this->add('select', "signout_time_top", ts('Signout Time'), $timeSlots);
}
static function getClasses($name = null)
{
$sql = "\nSELECT DISTINCT( name )\nFROM sfschool_extended_care_source\nWHERE is_active = 1\nAND term = %1\n";
if ($name) {
$name = CRM_Utils_Type::escape($name);
$sql .= " AND name like '{$name}%'";
}
$sql .= " ORDER BY name";
require_once 'SFS/Utils/ExtendedCare.php';
$params = array(1 => array(SFS_Utils_ExtendedCare::getTerm(), 'String'));
$dao = CRM_Core_DAO::executeQuery($sql, $params);
$classes = array();
while ($dao->fetch()) {
$classes[$dao->name] = $dao->name;
}
return $classes;
}
function postProcess()
{
$this->beginPostProcess();
$sessionHeaders = array();
foreach ($this->_sesionOrder as $value => $time) {
$sessionHeaders[$value] = array('title' => $time, 'type' => 'signout');
}
$sql = "\nSELECT distinct value_extended_care_2_civireport.{$this->_colMapper['sessionName']} as session_name, \n value_extended_care_2_civireport.{$this->_colMapper['sessionOrder']} as session_order, \n additional_rows as extra_rows, instructor, location\nFROM sfschool_extended_care_source value_extended_care_2_civireport\nWHERE is_active = 1\nAND term = %1\nAND {$this->_colMapper['dayOfWeek']} = '{$this->_params['weekday_value']}'\nORDER BY session, name, additional_rows\n";
$params = array(1 => array(SFS_Utils_ExtendedCare::getTerm('Fall 2009'), 'String'));
$sname = CRM_Core_DAO::executeQuery($sql, $params);
$rows = array();
while ($sname->fetch()) {
$sql = "\nSELECT contact_civireport.id as contact_civireport_id, \n contact_civireport.display_name as contact_civireport_display_name, '' as SignIn, '' as SignOut, '' as parent_initial\nFROM civicrm_value_extended_care value_extended_care_civireport\nINNER JOIN civicrm_contact as contact_civireport ON value_extended_care_civireport.entity_id = contact_civireport.id\nWHERE value_extended_care_civireport.{$this->_colMapper['sessionName']} = '{$sname->session_name}' AND \n value_extended_care_civireport.{$this->_colMapper['sessionOrder']} = '{$sname->session_order}' AND\n value_extended_care_civireport.{$this->_colMapper['dayOfWeek']} = '{$this->_params['weekday_value']}' AND\n value_extended_care_civireport.{$this->_colMapper['isCancelled']} != 1 AND\n term = %1\nGROUP BY contact_civireport.id;\n";
$this->_columnHeaders = array('contact_civireport_id' => array('no_display' => true), 'contact_civireport_display_name' => array('title' => 'Name'), 'SignIn' => array('title' => 'Sign In', 'type' => 'signin'), 'parent_initial' => array('title' => 'Parent<br/>Initial', 'type' => 'parent'));
$this->_columnHeaders = array_merge($this->_columnHeaders, $sessionHeaders);
$index = $sname->session_order . '_' . $sname->session_name;
$rows[$index] = array();
$dao = CRM_Core_DAO::executeQuery($sql, $params);
while ($dao->fetch()) {
if (property_exists($dao, 'contact_civireport_id')) {
if (!$dao->contact_civireport_id) {
continue;
}
}
$row = array();
foreach ($this->_columnHeaders as $key => $value) {
if (property_exists($dao, $key)) {
$row[$key] = $dao->{$key};
}
}
$rows[$index][] = $row;
}
// add extra rows
if ($sname->extra_rows) {
for ($i = 1; $i <= $sname->extra_rows; $i++) {
$rows[$index][] = array('contact_civireport_display_name' => ' ');
}
}
$classRowCount = count($rows[$index]);
$rows[$classRowCount . '_' . $index] = $rows[$index];
unset($rows[$index]);
$sessionInfo[$classRowCount . '_' . $index] = array();
$sessionInfo[$classRowCount . '_' . $index]['title'] = $sname->session_name;
$sessionInfo[$classRowCount . '_' . $index]['session'] = $sname->session_order;
$sessionInfo[$classRowCount . '_' . $index]['instRoom'] = $sname->instructor && $sname->location ? $sname->instructor . ' - ' . $sname->location : ($sname->instructor ? $sname->instructor : $sname->location);
}
// maximize space utilization
// ksort($rows, SORT_NUMERIC);
$this->formatDisplay($rows);
$this->assign_by_ref('sessionInfo', $sessionInfo);
$this->doTemplateAssignment($rows);
$this->endPostProcess($rows);
}
