webadmin.php

/var/www/(Del)pathways.org.hk/MIS_bk/staff_payroll/generate.php

<?php
require_once(__DIR__ . '/../checkuser.php');
function generate() {
    global $dbh;
    
    $message = $_GET['message'] ?: null;
    $search = $_GET['search'] ?: array();
    
    if (isPost()) {
        // generate payroll
        $root_ids = $_POST['staff']['selected'];
        $generate = $_POST['generate'];
        if (!empty($root_ids) && is_array($root_ids) && !empty($generate['start_date']) && !empty($generate['end_date'])) {
            $saved_payroll_count = 0;
            $saved_payroll_code_from = '';
            $saved_payroll_code_to = '';
            Db\Util::transaction($dbh, function() use ($dbh, &$root_ids, &$generate, &$saved_payroll_count, &$saved_payroll_code_from, &$saved_payroll_code_to) {
                $payroll_columns = Db\Util::columns($dbh, 'mis_payroll');
                $payroll_line_columns = Db\Util::columns($dbh, 'mis_payroll_line');
                $lesson_columns = Db\Util::columns($dbh, 'mis_event_lesson');
                $event_columns = Db\Util::columns($dbh, 'mis_event');
                
                $sql = "SELECT * FROM mis_payroll FOR UPDATE";
                $sth = Db\Util::execute($dbh, $sql);
                
                // fetch non generate staff payroll information
                $parameters = array();
                $question_marks = array();
                foreach ($root_ids as $root_id) {
                    $parameters[] = $root_id;
                    $question_marks[] = '?';
                }
                $sql = "SELECT lesson_staff.*";
                foreach ($lesson_columns as $column) {
                    $column_name = $column['column_name'];
                    $sql .= ", lesson.`$column_name` AS lesson_$column_name";
                }
                foreach ($event_columns as $column) {
                    $column_name = $column['column_name'];
                    $sql .= ", evt.`$column_name` AS event_$column_name";
                }
                $sql .= ",
    staff.root_id AS staff_root_id,
    latest_staff.id AS latest_staff_id,
    latest_staff.rate1 AS latest_staff_rate1,
    latest_staff.rate2 AS latest_staff_rate2,
    latest_staff.rate3 AS latest_staff_rate3,
    latest_staff.rate4 AS latest_staff_rate4,
    latest_staff.rate5 AS latest_staff_rate5,
    staff_position.title AS position_title
FROM mis_staff staff
INNER JOIN mis_staff latest_staff ON latest_staff.root_id = staff.root_id AND latest_staff.is_latest = 1
INNER JOIN mis_lesson_staff lesson_staff ON lesson_staff.staff_id = staff.id AND lesson_staff.deleted = 0
INNER JOIN mis_staff_position staff_position ON staff_position.id = lesson_staff.staff_position_id
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.is_waiting_list = 0 AND evt.deleted = 0
LEFT JOIN mis_payroll_line payroll_line ON payroll_line.lesson_staff_id = lesson_staff.id AND payroll_line.deleted = 0
LEFT JOIN mis_payroll payroll ON payroll.id = payroll_line.payroll_id AND payroll.deleted = 0
WHERE staff.root_id IN (".implode(', ', $question_marks).")
    AND 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
    AND (payroll_line.lesson_staff_id IS NULL OR payroll.id IS NULL)
";
                $parameters = array_merge($parameters, array(
                    $generate['start_date'], $generate['start_date'],
                    $generate['end_date'], $generate['end_date'],
                ));
                $sth = Db\Util::execute($dbh, $sql, $parameters);
                $lesson_staffs = $sth->fetchAll();
                
                // calculate payroll fee
                $payroll_lines = array();
                foreach ($lesson_staffs as $lesson_staff) {
                    $payroll_line = array(
                        'actived' => 1,
                        'deleted' => 0,
                        'lesson_staff_id' => $lesson_staff['id'],
                        'event_programme_fee_type' => $lesson_staff['event_programme_fee_type'],
                        
                        'staff_id' => $lesson_staff['latest_staff_id'],
                        'staff_root_id' => $lesson_staff['staff_root_id'],
                    );
                    // hour
                    $start_date_time = new DateTime(sprintf('%s %02d:%02d:00', $lesson_staff['lesson_date'], $lesson_staff['lesson_start_time_hour'], $lesson_staff['lesson_start_time_min']));
                    $end_date_time = new DateTime(sprintf('%s %02d:%02d:00', $lesson_staff['lesson_date'], $lesson_staff['lesson_end_time_hour'], $lesson_staff['lesson_end_time_min']));
                    $interval = $start_date_time->diff($end_date_time);
                    $payroll_line['hour'] = $interval->h + ($interval->i / floatval(60));
                    
                    // calculate by staff position
                    switch ($lesson_staff['position_title']) {
                        case 'Teacher (In House)': {
                            $payroll_line['original_fee'] = $lesson_staff['latest_staff_rate1'];
                            break;
                        }
                        case 'Teacher (Outside School)': {
                            $payroll_line['original_fee'] = $lesson_staff['latest_staff_rate2'];
                            break;
                        }
                        case 'Teaching Assistant (In House)': {
                            $payroll_line['original_fee'] = $lesson_staff['latest_staff_rate3'];
                            break;
                        }
                        case 'Teaching Assistant (Outside School)': {
                            $payroll_line['original_fee'] = $lesson_staff['latest_staff_rate4'];
                            break;
                        }
                        case 'Traveling': {
                            $payroll_line['original_fee'] = $lesson_staff['latest_staff_rate5'];
                            break;
                        }
                    }
                    $payroll_line['amount'] = floatval($payroll_line['original_fee']) * $payroll_line['hour'];
                    
                    $payroll_lines[] = $payroll_line;
                }
                
                // create and save payrolls
                $payroll_line_groups = __::groupBy($payroll_lines, 'staff_root_id');
                foreach ($payroll_line_groups as $staff_root_id => $payroll_lines) {
                    $first_payroll_line = __::first($payroll_lines);
                    $payroll = array(
                        'actived' => 1,
                        'deleted' => 0,
                        'staff_id' => $first_payroll_line['staff_id'],
                    );
                    $payroll['id'] = Db\Util::create($dbh, 'mis_payroll', $payroll, $payroll_columns);
                    $payroll['code'] = sprintf('PR%06d', $payroll['id']);
                    Db\Util::update($dbh, 'mis_payroll', $payroll, $payroll_columns);
                    
                    $saved_payroll_count++;
                    $saved_payroll_code_to = $payroll['code'];
                    if (empty($saved_payroll_code_from)) {
                        $saved_payroll_code_from = $payroll['code'];
                    }
                    
                    // fill payroll id into line and save
                    foreach ($payroll_lines as $payroll_line) {
                        $payroll_line['payroll_id'] = $payroll['id'];
                        Db\Util::create($dbh, 'mis_payroll_line', $payroll_line, $payroll_line_columns);
                    }
                }
            });
            
            
            $data = array();
            switch ($saved_payroll_count) {
                case 0: {
                    $data['message'] = 'No payrolls saved';
                    break;
                }
                case 1: {
                    $data['message'] = sprintf('Saved, payroll no: %s', $saved_payroll_code_to);
                    break;
                }
                default: {
                    $data['message'] = sprintf('Saved %d payrolls, start from %s to %s.', $saved_payroll_count, $saved_payroll_code_from, $saved_payroll_code_to);
                    break;
                }
            }
            redirectAndExit(Util::link(__DIR__ . '/' . basename(__FILE__)) . '?' . http_build_query($data));
        }
    }
    
    $staffs = null;
    if (!empty($search)) {
        $sql = "
SELECT DISTINCT latest_staff.*
FROM mis_staff staff
INNER JOIN mis_staff latest_staff ON latest_staff.root_id = staff.root_id AND latest_staff.is_latest = 1
INNER JOIN mis_lesson_staff lesson_staff ON lesson_staff.staff_id = staff.id AND lesson_staff.deleted = 0
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.is_waiting_list = 0 AND evt.deleted = 0
LEFT JOIN mis_payroll_line payroll_line ON payroll_line.lesson_staff_id = lesson_staff.id AND payroll_line.deleted = 0
LEFT JOIN mis_payroll payroll ON payroll.id = payroll_line.payroll_id AND payroll.deleted = 0
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
    AND (payroll_line.lesson_staff_id IS NULL OR payroll.id IS NULL)
ORDER BY latest_staff.last_name, latest_staff.first_name, latest_staff.root_id
";
        $parameters = array(
            $search['start_date'], $search['start_date'],
            $search['end_date'], $search['end_date'],
        );
        $sth = Db\Util::execute($dbh, $sql, $parameters);
        $staffs = $sth->fetchAll();
    }
    
    return array(
        'staffs' => $staffs,
        'message' => $message,
        
        'search' => $search,
    );
}
extract(generate());
?><!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><a href="#">Master</a> <span class="divider">&gt;</span></li>
            <li><a href="#">Staff</a> <span class="divider">&gt;</span></li>
            <li class="active">Generate Payroll</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>Generate Payroll</h3>
        
        <?php if (empty($search)): ?>
        <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="required 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="required 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">
                    <button type="submit" class="btn" style="margin-left:10px">GO</button>
                </div>
            </form>
        </div>
        <script type="text/javascript">
            $(function() {
                $('.date-picker').datetimepicker({ pickTime: false });
                $('#search_form').validate();
            });
        </script>
        
        <?php else: ?>
        
        <p>From <?=h($search['start_date'])?> to <?=h($search['end_date'])?></p>
        
        <?php if (empty($staffs)): ?>
        <p>There are no records.</p>
        <div>
            <a href="<?=basename(__FILE__)?>" class="btn">Back</a>
        </div>
        <?php else: ?>
        <div class="row-fluid" style="margin-bottom:10px">
            <div class="btn-group">
                <button id="select_all_btn" class="btn btn-small">Select All</button>
            </div>
        </div>
        <script type="text/javascript">
            $(function() {
                $('#select_all_btn').click(function() {
                    var $selectAllCheckbox = $('.select-all-checkbox');
                    $selectAllCheckbox.prop('checked', !$selectAllCheckbox.prop('checked'));
                });
            });
        </script>
        
        <form id="form" action="" method="post">
            <?php $attribute = 'start_date'; ?>
            <input type="hidden" name="generate[<?=$attribute?>]" value="<?=h($search[$attribute])?>" />
            <?php $attribute = 'end_date'; ?>
            <input type="hidden" name="generate[<?=$attribute?>]" value="<?=h($search[$attribute])?>" />
            
            <table class="table table-striped table-bordered table-hover table-condensed">
                <thead>
                    <tr>
                        <th style="min-width:20px; width:20px"></th>
                        <th>Name</th>
                    </tr>
                </thead>
                <tbody>
                    <?php foreach ($staffs as $staff): ?>
                    <tr>
                        <td><input type="checkbox" name="staff[selected][]" class="select-all-checkbox" value="<?=h($staff['root_id'])?>" /></td>
                        <td><?=h($staff['last_name'] . ' ' . $staff['first_name'])?></td>
                    </tr>
                    <?php endforeach; ?>
                </tbody>
            </table>
            
            <button type="submit" class="btn btn-danger">Generate</button>
        </form>
        <script type="text/javascript">
            $(function() {
                $('#form').validate({
                    submitHandler: function(form) {
                        var isValid = true;
                        if ($('input[name="staff[selected][]"]:checked').length == 0) {
                            isValid = false;
                            bootbox.alert('Please select staff first.');
                        }
                        if (isValid) {
                            form.submit();
                        }
                    }
                });
            });
        </script>
        <?php endif; ?>
        
        <?php endif; ?>
    
        <?php require( __DIR__ . '/../inc/_footer.php'); ?>

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