webadmin.php

/var/www/(Del)pathways.org.hk/MIS_bk/attendance/index.php

<?php
require_once(__DIR__ . '/../checkuser.php');
function index() {
    global $dbh;
    
    $typeOptions = array(
        1 => 'Staff',
        2 => 'Student',
    );
    
    $message = $_GET['message'] ?: null;
    $search = $_GET['search'] ?: array();
    $sort = $_GET['sort'] ?: array();
    
    $sort_by_columns = array(
        'name' => 'Name',
        'type' => 'Type',
        'in_time' => 'In Time',
        'out_time' => 'Out Time',
        'attendance' => 'Attendance',
    );
    $sort_by_options = array(
        'asc' => 'A - Z',
        'desc' => 'Z - A',
    );
    
    $today = date('Y-m-d');
    if (empty($search['start_date']) && empty($search['end_date'])) {
        $search['start_date'] = $today;
        $search['end_date'] = $today;
    }
    
    if (isPost()) {
        // Save
        Db\Util::transaction($dbh, function() use ($dbh) {
            $columns = Db\Util::columns($dbh, 'mis_attendance');
            
            $save_attendance = function($attendance) use ($dbh, &$columns) {
                // check is exists by lesson date, type, person root id
                switch ($attendance['type']) {
                    // Staff
                    case 1: {
                        $person_field = "staff.root_id";
                        break;
                    }
                    // Student
                    case 2: {
                        $person_field = "student.root_id";
                        break;
                    }
                }
                
                $sql = "
SELECT attendance.*, staff.root_id AS staff_root_id, student.root_id AS student_root_id
FROM mis_attendance attendance
LEFT JOIN mis_staff staff ON staff.id = attendance.staff_id
LEFT JOIN mis_student student ON student.id = attendance.student_id
WHERE attendance.`date` = ? AND $person_field = ?";
                $parameters = array($attendance['lesson_date'], $attendance['person_root_id']);
                $sth = Db\Util::execute($dbh, $sql, $parameters);
                $db_attendance = $sth->fetch(PDO::FETCH_ASSOC);
                
                if ($db_attendance) {
                    // update if exists
                    $db_attendance = array_merge($db_attendance, $attendance);
                    Db\Util::update($dbh, 'mis_attendance', $db_attendance, $columns);
                } else {
                    // create if not exists
                    $attendance = array_merge($attendance, array(
                        'actived' => 1,
                        'deleted' => 0,
                        'date' => $attendance['lesson_date'],
                    ));
                    switch ($attendance['type']) {
                        // Staff
                        case 1: {
                            $sql = "SELECT * FROM mis_staff WHERE root_id = ? AND is_latest = ?";
                            $parameters = array($attendance['person_root_id'], 1);
                            $sth = Db\Util::execute($dbh, $sql, $parameters);
                            $staff = $sth->fetch(PDO::FETCH_ASSOC);
                            $attendance['staff_id'] = $staff['id'];
                            break;
                        }
                        // Student
                        case 2: {
                            $sql = "SELECT * FROM mis_student WHERE root_id = ? AND is_latest = ?";
                            $parameters = array($attendance['person_root_id'], 1);
                            $sth = Db\Util::execute($dbh, $sql, $parameters);
                            $student = $sth->fetch(PDO::FETCH_ASSOC);
                            $attendance['student_id'] = $student['id'];
                            break;
                        }
                    }
                    $attendance['id'] = Db\Util::create($dbh, 'mis_attendance', $attendance, $columns);
                }
                
                switch ($attendance['type']) {
                    case 1: { // Staff
                        $sql = "
UPDATE mis_lesson_staff lesson_staff
INNER JOIN mis_event_lesson lesson ON lesson.id = lesson_staff.lesson_id AND lesson.deleted = 0
INNER JOIN mis_event evt ON evt.id = lesson.event_id AND evt.deleted = 0
INNER JOIN mis_staff staff ON staff.id = lesson_staff.staff_id
SET lesson_staff.attendance = ?, lesson_staff.lastupby = ?, lesson_staff.lastupdate = ?
WHERE lesson.`date` = ? AND staff.root_id = ? AND lesson_staff.deleted = ?";
                        $parameters = array(
                            $attendance['attendance'], $_SESSION['webadmin']['id'], date('Y-m-d H:i:s'),
                            $attendance['lesson_date'], $attendance['person_root_id'], 0,
                        );
                        $sth = Db\Util::execute($dbh, $sql, $parameters);
                        break;
                    }
                    case 2: { // Student
                        $sql = "
UPDATE mis_lesson_student lesson_student
INNER JOIN mis_event_lesson lesson ON lesson.id = lesson_student.lesson_id AND lesson.deleted = 0
INNER JOIN mis_event evt ON evt.id = lesson.event_id AND evt.deleted = 0
INNER JOIN mis_student student ON student.id = lesson_student.student_id
SET lesson_student.attendance = ?, lesson_student.lastupby = ?, lesson_student.lastupdate = ?
WHERE lesson.`date` = ? AND student.root_id = ? AND lesson_student.deleted = ?";
                        $parameters = array(
                            $attendance['attendance'], $_SESSION['webadmin']['id'], date('Y-m-d H:i:s'),
                            $attendance['lesson_date'], $attendance['person_root_id'], 0,
                        );
                        $sth = Db\Util::execute($dbh, $sql, $parameters);
                        break;
                    }
                }
            };
            
            $sql = "SELECT * FROM mis_attendance FOR UPDATE";
            $sth = Db\Util::execute($dbh, $sql);
            foreach ($_POST['attendance'] as $id => $attributes) {
                $save_attendance($attributes);
            }
            foreach ($_POST['new_attendance'] as $id => $attributes) {
                $save_attendance($attributes);
            }
        });
        
        $data = array(
            'message' => 'Saved',
            'search' => $search,
            'sort' => $sort,
        );
        redirectAndExit(basename(__FILE__) . '?' . http_build_query($data));
    }
    
    //TODO: search query
    
    $sql = "
SELECT DISTINCT *
FROM (
    /* Staffs */
    SELECT
        1 AS `type`,
        latest_staff.id AS person_id,
        latest_staff.root_id AS person_root_id,
        latest_staff.last_name,
        latest_staff.first_name,
        staff_position.title AS position_title,
        NULL AS registration_number,
        NULL AS grade_title,
        lesson.`date` AS lesson_date,
        attendance.*
    FROM mis_event evt
    INNER JOIN mis_event_lesson lesson ON lesson.event_id = evt.id AND lesson.deleted = 0
    INNER JOIN mis_lesson_staff lesson_staff ON lesson_staff.lesson_id = lesson.id AND lesson_staff.deleted = 0
    INNER JOIN mis_staff staff ON staff.id = lesson_staff.staff_id
    INNER JOIN mis_staff latest_staff ON latest_staff.root_id = staff.root_id AND latest_staff.is_latest = 1
    LEFT JOIN mis_staff_position staff_position ON staff_position.id = latest_staff.position_id
    LEFT JOIN (
        SELECT attendance.*, staff.root_id AS staff_root_id
        FROM mis_attendance attendance
        INNER JOIN mis_staff staff ON staff.id = attendance.staff_id
    ) attendance ON attendance.staff_root_id = latest_staff.root_id AND attendance.`date` = lesson.`date`
    WHERE evt.deleted = 0
    
    UNION ALL
    
    /* Students */
    SELECT
        2 AS `type`,
        latest_student.id AS person_id,
        latest_student.root_id AS person_root_id,
        latest_student.last_name,
        latest_student.first_name,
        NULL AS position_title,
        latest_student.registration_number,
        student_grade.title AS grade_title,
        lesson.`date` AS lesson_date,
        attendance.*
    FROM mis_event evt
    INNER JOIN mis_event_lesson lesson ON lesson.event_id = evt.id AND lesson.deleted = 0
    INNER JOIN mis_lesson_student lesson_student ON lesson_student.lesson_id = lesson.id AND lesson_student.deleted = 0
    INNER JOIN mis_student student ON student.id = lesson_student.student_id
    INNER JOIN mis_student latest_student ON latest_student.root_id = student.root_id AND latest_student.is_latest = 1
    LEFT JOIN mis_student_grade student_grade ON student_grade.id = latest_student.student_grade_id
    LEFT JOIN (
        SELECT attendance.*, student.root_id AS student_root_id
        FROM mis_attendance attendance
        INNER JOIN mis_student student ON student.id = attendance.student_id
    ) attendance ON attendance.student_root_id = latest_student.root_id AND attendance.`date` = lesson.`date`
    WHERE evt.deleted = 0
) attendance
WHERE CASE WHEN COALESCE(LENGTH(?), 0) = 0 THEN 1 = 1 ELSE lesson_date >= ? END
    AND CASE WHEN COALESCE(LENGTH(?), 0) = 0 THEN 1 = 1 ELSE lesson_date <= ? END
ORDER BY lesson_date";
    $order_by_sql = '';
    foreach ($sort as $index => $info) {
        if (!empty($info['column']) && !empty($info['option']) && in_array($info['column'], array_keys($sort_by_columns))) {
            $order_by_sql .= ', ';
            $direction = strtoupper($info['option']) != 'DESC' ? 'ASC' : 'DESC';
            switch ($info['column']) {
                case 'name': {
                    $order_by_sql .= "CONCAT(last_name, ' ', first_name) $direction";
                    break;
                }
                case 'in_time': {
                    $order_by_sql .= "in_time_hour $direction, in_time_min $direction";
                    break;
                }
                case 'out_time': {
                    $order_by_sql .= "out_time_hour $direction, out_time_min $direction";
                    break;
                }
                default: {
                    $column = $info['column'];
                    $order_by_sql .= "`$column` $direction";
                    break;
                }
            }
        }
    }
    if (empty($order_by_sql)) {
        $order_by_sql = ", `type`, last_name, first_name";
    }
    $sql .= $order_by_sql . ", registration_number, person_root_id";
    $parameters = array(
        $search['start_date'], $search['start_date'],
        $search['end_date'], $search['end_date'],
    );
    $sth = Db\Util::execute($dbh, $sql, $parameters);
    $attendances = $sth->fetchAll();
    $attendance_groups = __::groupBy($attendances, 'lesson_date');
    
    $sql = "SELECT * FROM mis_campus WHERE deleted = ? ORDER BY sort";
    $parameters = array(0);
    $sth = Db\Util::execute($dbh, $sql, $parameters);
    $campuses = $sth->fetchAll();
    
    return array(
        'message' => $message,
        'search' => $search,
        'sort' => $sort,
        'sort_by_columns' => $sort_by_columns,
        'sort_by_options' => $sort_by_options,
        
        'typeOptions' => $typeOptions,
        'attendance_groups' => $attendance_groups,
        'campuses' => $campuses,
    );
}
extract(index());
?><!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    
    <?php require(__DIR__ . '/../inc/_head_meta.php'); ?>
    
    <?php require(__DIR__ . '/../inc/_head_css.php'); ?>
    
    <?php require(__DIR__ . '/../inc/_head_script.php'); ?>
    
</head>
<body>
    
    <?php require( __DIR__ . '/../inc/_navbar.php'); ?>
    
    <div class="text-right">
        <ul class="breadcrumb">
            <li class="active"><a href="#">Attendance</a></li>
        </ul>
    </div>
    
    <div class="container-fluid pathways-container">
        
        <?php if (isset($message) && !empty($message)): ?>
        <div class="alert alert-info">
            <button type="button" class="close" data-dismiss="alert">&times;</button>
            <h5 class="alert-heading">Note:</h5>
            <p><?=$message?></p>
        </div>
        <?php endif; ?>
        
        <h3>Attendance</h3>
        
        <div class="pathways-search">
            <form id="search_form" class="form-inline" action="" method="get">
                <div class="pathways-inline-block">
                    <?php $attribute = 'start_date'; $label = 'Date'; ?>
                    <label class="" for="<?=$attribute?>"><?=$label?></label>
                    <div class="input-append date-picker">
                        <input type="text" id="<?=$attribute?>" name="search[<?=$attribute?>]" class="dateISO input-small" style="width:90px" value="<?=h(Util::value_to_date_string($search[$attribute]))?>" data-format="yyyy-MM-dd" placeholder="<?=h($label)?>" maxlength="200" />
                        <span class="add-on"><i data-time-icon="icon-time" data-date-icon="icon-calendar"></i></span>
                    </div>
                    <?php $attribute = 'end_date'; $label = 'To'; ?>
                    <label class="" for="<?=$attribute?>"><?=$label?></label>
                    <div class="input-append date-picker">
                        <input type="text" id="<?=$attribute?>" name="search[<?=$attribute?>]" class="dateISO input-small" style="width:90px" value="<?=h(Util::value_to_date_string($search[$attribute]))?>" data-format="yyyy-MM-dd" placeholder="<?=h($label)?>" maxlength="200" />
                        <span class="add-on"><i data-time-icon="icon-time" data-date-icon="icon-calendar"></i></span>
                    </div>
                </div>
                <!--
                <div class="pathways-inline-block">
                    <?php $attribute = 'campus_id'; $label = 'Campus'; ?>
                    <label class="" for="<?=$attribute?>"><?=$label?></label>
                    <select id="<?=$attribute?>" name="search[<?=$attribute?>]" class="input-medium" placeholder="<?=h($label)?>">
                        <option value="">All</option>
                        <?php foreach ($campuses as $campus): ?>
                        <option value="<?=h($campus['id'])?>"<?=$campus['id'] == $search[$attribute] ? ' selected="selected"' : ''?>><?=h($campus['code'])?></option>
                        <?php endforeach; ?>
                    </select>
                </div>
                
                <div class="pathways-inline-block">
                    <?php $attribute = 'staff'; $label = 'Staff'; ?>
                    <label class="" for="<?=$attribute?>"><?=$label?></label>
                    <input type="text" id="<?=$attribute?>" name="search[<?=$attribute?>]" class="input-medium" value="<?=h($search[$attribute])?>" placeholder="<?=h($label)?>" maxlength="200" />
                </div>
                
                <div class="pathways-inline-block">
                    <?php $attribute = 'student'; $label = 'Student'; ?>
                    <label class="" for="<?=$attribute?>"><?=$label?></label>
                    <input type="text" id="<?=$attribute?>" name="search[<?=$attribute?>]" class="input-medium" value="<?=h($search[$attribute])?>" placeholder="<?=h($label)?>" maxlength="200" />
                </div>
                -->
                <div class="pathways-inline-block">
                    <?php foreach ($sort as $index => $info): ?>
                    <?php if (!empty($info['column']) && !empty($info['option'])): ?>
                    <?php $attribute = 'column'; ?>
                    <input type="hidden" name="sort[<?=h($index)?>][<?=$attribute?>]" value="<?=h($info[$attribute])?>" />
                    <?php $attribute = 'option'; ?>
                    <input type="hidden" name="sort[<?=h($index)?>][<?=$attribute?>]" value="<?=h($info[$attribute])?>" />
                    <?php endif; ?>
                    <?php endforeach; ?>
                    
                    <?php $attribute = 'column'; $label = 'Sort by'; ?>
                    <label class="" for="<?=$attribute?>"><?=$label?></label>
                    <select id="<?=$attribute?>" name="sort[<?=max(array_keys($sort?:array(0)))+1?>][<?=$attribute?>]" class="input-medium" placeholder="<?=h($label)?>">
                        <option value=""></option>
                        <?php foreach ($sort_by_columns as $value => $label): ?>
                        <option value="<?=$value?>"<?=$value == $search[$attribute] ? ' selected="selected"' : ''?>><?=h($label)?></option>
                        <?php endforeach; ?>
                    </select>
                    <?php $attribute = 'option'; $label = 'Option'; ?>
                    <!-- <label class="" for="<?=$attribute?>"><?=$label?></label> -->
                    <select id="<?=$attribute?>" name="sort[<?=max(array_keys($sort?:array(0)))+1?>][<?=$attribute?>]" class="input-mini" placeholder="<?=h($label)?>">
                        <option value=""></option>
                        <?php foreach ($sort_by_options as $value => $label): ?>
                        <option value="<?=$value?>"<?=$value == $search[$attribute] ? ' selected="selected"' : ''?>><?=h($label)?></option>
                        <?php endforeach; ?>
                    </select>
                    <button type="button" id="clear_sorting_btn" class="btn"><i class="icon-remove"></i> Clear sorting</button>
                    <script type="text/javascript">
                        $(function() {
                            $('#clear_sorting_btn').click(function() {
                                var $search_form = $('#search_form');
                                $('input[type="hidden"][name^="sort["]', $search_form).remove();
                                $search_form.submit();
                            });
                        });
                    </script>
                    <button type="submit" class="btn" style="margin-left:30px">GO</button>
                </div>
            </form>
        </div>

        <?php if (empty($attendance_groups)): ?>
        <p>There are no records.</p>
        <?php else: ?>
        <form id="form" action="" method="post">
            <?php
            $index = 0;
            foreach ($attendance_groups as $lesson_date => $attendances):
            ?>
            <h5>Date: <?=$lesson_date?></h5>
            <table class="table table-striped table-bordered table-hover table-condensed">
                <thead>
                    <tr>
                        <th>Name <?=Util::getSortDirectionCaretHtml($sort, 'name')?></th>
                        <th style="min-width:100px; width:100px">Type <?=Util::getSortDirectionCaretHtml($sort, 'type')?></th>
                        <th style="min-width:150px; width:150px">In Time <?=Util::getSortDirectionCaretHtml($sort, 'in_time')?></th>
                        <th style="min-width:150px; width:150px">Out Time <?=Util::getSortDirectionCaretHtml($sort, 'out_time')?></th>
                        <th style="min-width:250px; width:250px">Attendance <?=Util::getSortDirectionCaretHtml($sort, 'attendance')?></th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    foreach ($attendances as $attendance):
                        $is_new_attendance = function(&$attendance) {
                            $tmp = $attendance['id'] ?: 'new';
                            return $tmp == 'new';
                        }
                    ?>
                    <tr>
                        <td>
                            <?=h($attendance['last_name'])?> <?=h($attendance['first_name'])?>
                            <?php if (!empty($attendance['registration_number'])): ?>
                            (<?=h($attendance['registration_number'])?>)
                            <?php endif; ?>
                            <?php if (!empty($attendance['grade_title'])): ?>
                            <?=h($attendance['grade_title'])?>
                            <?php endif; ?>
                            
                            <?php $attribute = 'lesson_date'; ?>
                            <input type="hidden" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" value="<?=h($attendance[$attribute])?>" />
                            <?php $attribute = 'person_root_id'; ?>
                            <input type="hidden" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" value="<?=h($attendance[$attribute])?>" />
                        </td>
                        <td>
                            <?php $attribute = 'type'; ?>
                            <?=h($typeOptions[$attendance[$attribute]])?>
                            <input type="hidden" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" value="<?=h($attendance[$attribute])?>" />
                        </td>
                        <td>
                            <?php $attribute = 'in_time_hour'; $label = 'In Time Hour'; ?>
                            <select id="<?=$attribute?>" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" class="input-mini" placeholder="<?=h($label)?>">
                                <option value=""></option>
                                <?php for ($value = 0; $value < 24; $value++): ?>
                                <option value="<?=h($value)?>"<?=strval($value) === strval($attendance[$attribute]) ? ' selected="selected"' : ''?>><?=h(sprintf('%02d', $value))?></option>
                                <?php endfor; ?>
                            </select>
                            :
                            <?php $attribute = 'in_time_min'; $label = 'In Time Min'; ?>
                            <select id="<?=$attribute?>" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" class="input-mini" placeholder="<?=h($label)?>">
                                <option value=""></option>
                                <?php for ($value = 0; $value < 60; $value++): ?>
                                <option value="<?=h($value)?>"<?=strval($value) === strval($attendance[$attribute]) ? ' selected="selected"' : ''?>><?=h(sprintf('%02d', $value))?></option>
                                <?php endfor; ?>
                            </select>
                        </td>
                        <td>
                            <?php $attribute = 'out_time_hour'; $label = 'Out Time Hour'; ?>
                            <select id="<?=$attribute?>" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" class="input-mini" placeholder="<?=h($label)?>">
                                <option value=""></option>
                                <?php for ($value = 0; $value < 24; $value++): ?>
                                <option value="<?=h($value)?>"<?=strval($value) === strval($attendance[$attribute]) ? ' selected="selected"' : ''?>><?=h(sprintf('%02d', $value))?></option>
                                <?php endfor; ?>
                            </select>
                            :
                            <?php $attribute = 'out_time_min'; $label = 'Out Time Min'; ?>
                            <select id="<?=$attribute?>" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" class="input-mini" placeholder="<?=h($label)?>">
                                <option value=""></option>
                                <?php for ($value = 0; $value < 60; $value++): ?>
                                <option value="<?=h($value)?>"<?=strval($value) === strval($attendance[$attribute]) ? ' selected="selected"' : ''?>><?=h(sprintf('%02d', $value))?></option>
                                <?php endfor; ?>
                            </select>
                        </td>
                        <td>
                            <div class="pathways-inline-block" style="margin-right:0px">
                                <?php $attribute = 'attendance'; $label = 'Present'; ?>
                                <label class="radio inline">
                                    <input type="radio" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" value="1"<?=strval($attendance[$attribute]) === strval(1) || strlen($attendance[$attribute]) == 0 ? ' checked="checked"' : ''?> />
                                    Present
                                </label>
                                <label class="radio inline">
                                    <input type="radio" name="<?=$is_new_attendance($attendance) ? 'new_' : ''?>attendance[<?=$attendance['id'] ?: $index?>][<?=$attribute?>]" value="0"<?=strval($attendance[$attribute]) === strval(0) ? ' checked="checked"' : ''?> />
                                    Absent
                                </label>
                                <?php if ($is_new_attendance($attendance)): ?>
                                <span class="label label-important" style="margin-left:10px">Still not save</span>
                                <?php endif; ?>
                            </div>
                        </td>
                    </tr>
                    <?php
                    $index++;
                    endforeach;
                    ?>
                </tbody>
            </table>
            <?php endforeach; ?>
            
            <button type="submit" class="btn btn-danger">Save</button>
        </form>
        <script type="text/javascript">
            $(function() {
                $('#form').validate();
            });
        </script>
        <?php endif; ?>
        
        <script type="text/javascript">
            $(function() {
                $('.date-picker').datetimepicker({ pickTime: false });
                $('#search_form').validate();
            });
        </script>
    
        <?php require( __DIR__ . '/../inc/_footer.php'); ?>

    </div> <!-- /container -->
</body>
</html>