webadmin.php

/var/www/onesupportdemo.onesolution.hk/job_bk/monthly_index.php

<?php
require_once(__DIR__ . '/../checkuser.php');
function index() {
    global $dbh, $sqlsrv_dbh;

    $get_keys = array('job_id', 'customer_id', 'start_date', 'end_date', 'staff_id', 'status', 'has_quotation', 'monthly_job');
    $get = array();
    foreach ($get_keys as $get_key) {
        $$get_key = isset($_GET[$get_key]) ? $_GET[$get_key] : null;
        $get[$get_key] = &$$get_key;
    }

    // Default search
    $tmp_now = new DateTime;
    if (!isset($_GET['start_date']) && empty($start_date)) {
        //$start_date = Util::date_to_string($tmp_now);
    }
    if (!isset($_GET['end_date']) && empty($end_date)) {
        //$end_date = Util::date_to_string($tmp_now);
    }
    if (!isset($_GET['staff_id']) && empty($staff_id)) {
        if (!Util::isAdmin()) {
            $staff_id = $_SESSION['webadmin']['id'];
        }
    }
    if (!isset($_GET['status']) && empty($status)) {
        //$status = 1; // Open
    }

    // Show all
    if ($_GET['show_all'] == 1) {
        foreach ($get_keys as $get_key) {
            if (Util::isGuest() && $get_key == 'staff_id') {
                continue;
            }
            $$get_key = null;
        }
    }

    $sql = "
SELECT job.*,
    (
        SELECT COUNT(*)
        FROM sup_job_detail detail
        WHERE detail.job_id = job.id
            AND detail.status = 1
            AND detail.actived = 1
            AND detail.deleted = 0
    ) AS detail_open_count,
    (
        SELECT COUNT(*)
        FROM sup_job_detail detail
        WHERE detail.job_id = job.id
            AND detail.status = 2
            AND detail.actived = 1
            AND detail.deleted = 0
    ) AS detail_completed_count,
    (
        SELECT COUNT(*)
        FROM sup_job_detail detail
        WHERE detail.job_id = job.id
            AND detail.status = 3
            AND detail.actived = 1
            AND detail.deleted = 0
    ) AS detail_follow_up_count,
    (
        SELECT COUNT(*)
        FROM sup_job_detail detail
        WHERE detail.job_id = job.id
            AND detail.status = 4
            AND detail.actived = 1
            AND detail.deleted = 0
    ) AS detail_cancel_count
FROM sup_job job
WHERE (COALESCE(LENGTH(?), 0) = 0 OR id = ?)
    AND (COALESCE(LENGTH(?), 0) = 0 OR customer_id = ?)
    AND (COALESCE(LENGTH(?), 0) = 0 OR staff_id = ?)
    AND (COALESCE(LENGTH(?), 0) = 0 OR status = ?)
    AND (COALESCE(LENGTH(?), 0) = 0 OR date >= ?)
    AND (COALESCE(LENGTH(?), 0) = 0 OR date <= ?)
    AND (COALESCE(LENGTH(?), 0) = 0 OR LENGTH(quotation) > 0)
    AND (COALESCE(LENGTH(?), 0) = 0 OR monthly_job = ?)
ORDER BY id
";
    $parameters = array(
        $job_id, $job_id,
        $customer_id, $customer_id,
        $staff_id, $staff_id,
        $status, $status,
        $start_date, $start_date,
        $end_date, $end_date,
        $has_quotation ? '1' : '',
        $monthly_job, $monthly_job,
    );
    $pagination = new MySqlPagination($sql, $parameters);
    $sql .= $pagination->getSqlLimitAndOffset();
    if (!($sth = $dbh->prepare($sql))) {
        throw new Exception("sql prepare statement failure: $sql");
    }
    $sth->setFetchMode(PDO::FETCH_ASSOC);
    if (!$sth->execute($parameters)) {
        throw new Exception("sql execute statement failure: $sql");
    }
    $jobs = $sth->fetchAll();

    $sql = "SELECT * FROM v_cm_customer_support V_CM_CUSTOMER_SUPPORT ORDER BY company_name";
    if (!($sth = $dbh->prepare($sql))) {
        throw new Exception("sql prepare statement failure: $sql");
    }
    $sth->setFetchMode(PDO::FETCH_ASSOC);
    if (!$sth->execute()) {
        throw new Exception("sql execute statement failure: $sql");
    }
    $customers = $sth->fetchAll();

    $sql = "SELECT * FROM sys_login WHERE deleted = ? ORDER BY username";
    $parameters = array(0);
    if (!($sth = $dbh->prepare($sql))) {
        throw new Exception("sql prepare statement failure: $sql");
    }
    $sth->setFetchMode(PDO::FETCH_ASSOC);
    if (!$sth->execute($parameters)) {
        throw new Exception("sql execute statement failure: $sql");
    }
    $staffs = $sth->fetchAll();

    $tmp_customer_map = array();
    foreach ($customers as &$tmp_customer) {
        $tmp_customer_id = $tmp_customer['cust_id'];
        $tmp_customer_map[$tmp_customer_id] = $tmp_customer;
    }

    $tmp_staff_map = array();
    foreach ($staffs as &$tmp_staff) {
        $tmp_staff_id = $tmp_staff['id'];
        $tmp_staff_map[$tmp_staff_id] = $tmp_staff;
    }

    foreach ($jobs as $tmp_key => &$tmp_job) {
        $tmp_customer_id = $tmp_job['customer_id'];
        $tmp_job['customer'] = $tmp_customer_map[$tmp_customer_id];

        $tmp_staff_id = $tmp_job['staff_id'];
        $tmp_job['staff'] = $tmp_staff_map[$tmp_staff_id];
    }

    return array_merge($get, array(
        'jobs' => $jobs,
        'message' => $_GET['message'],
        'customers' => $customers,
        'staffs' => $staffs,
        'statusOptions' => Job::statusOptions(),
        'typeOptions' => Job::typeOptions(),
        'pagination' => $pagination->toString(),
    ));
}
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'); ?>

    <style type="text/css">
        body {
            padding-top: 60px; /* 60px to make the container go all the way to the bottom of the topbar */
            padding-bottom: 40px;
        }
    </style>

    <?php require(__DIR__ . '/../inc/_head_script.php'); ?>

</head>
<body>

<?php require(__DIR__ . '/../inc/_navbar.php'); ?>

<div class="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; ?>

<?php if (!Util::isGuest()): ?>
    <a href="addform.php?monthly=1" class="btn btn-primary pull-right">New Monthly Job</a>
<?php endif; ?>
<h2>Monthly Jobs</h2>

<?=$pagination?>

<table class="table table-striped">
    <caption class="text-left hide"><h2>Jobs</h2></caption>
    <thead>
    <tr>
        <th>&nbsp;</th>
        <th>Number</th>
        <th>Customer</th>
        <th>Date</th>
        <th>Time</th>
        <th>Staff</th>
        <th>Type</th>
        <th>Status</th>
    </tr>
    </thead>
    <tbody>
    <?php foreach ($jobs as $job):?>
        <tr>
            <td><a href="modifyform.php?id=<?=$job['id']?><?php if($job['monthly_job'] == 1) echo '&monthly=1'; ?>" class="btn"><i class="icon-pencil"></i></a></td>
            <td><?=$job['id']?></td>
            <td><?=h($job['customer']['company_name'])?></td>
            <td><?=$job['date']?></td>
            <td>
                <?php
                    ob_start();
                ?>
                Time: <?=h(Util::value_to_time_string($job['start_time']))?> - <?=h(Util::value_to_time_string($job['end_time']))?>
                <br />
                Actual Time: <?=h(Util::value_to_time_string($job['actual_start_time']))?> - <?=h(Util::value_to_time_string($job['actual_end_time']))?>
                <?php
                    $tmp_title = ob_get_contents();
                    ob_end_clean();
                ?>
                <a href="javascript:void(0)" class="job-time" data-toggle="tooltip" title="<?=  htmlentities($tmp_title)?>">
                    <?=h(Util::value_to_time_string($job['start_time']))?> - <?=h(Util::value_to_time_string($job['end_time']))?>
                </a>
            </td>
            <td><?=h($job['staff']['username'])?></td>
            <td><?=h($typeOptions[$job['type']])?></td>
            <td>
                <?php
                    ob_start();
                ?>
                Open: <?=$job['detail_open_count']?>
                <br />
                Completed: <?=$job['detail_completed_count']?>
                <br />
                Follow up: <?=$job['detail_follow_up_count']?>
                <br />
                Cancel: <?=$job['detail_cancel_count']?>
                <?php
                    $tmp_title = ob_get_contents();
                    ob_end_clean();
                ?>
                <a href="javascript:void(0)" class="job-status" data-toggle="tooltip" title="<?=htmlentities($tmp_title)?>">
                    <i class="<?=in_array($job['status'], array(1, 2)) ? 'icon-ok' : 'icon-remove'?>"></i>
                    <?=h($statusOptions[$job['status']])?>
                </a>
            </td>
        </tr>

    <?php endforeach; ?>
    </tbody>
</table>

<?=$pagination?>

<div class="scroll_to_top">
    <a href="#" class="btn btn-link btn-mini scroll-to-top">Scroll to Top</a>
</div>

<script type="text/javascript">
    $(function() {
        $('.job-status, .job-time').tooltip({ html: true });
        $('.scroll-to-top').click(function(event) {
            event.preventDefault();
            scrollToTop();
        });
    });
</script>

<?php require(__DIR__ . '/../inc/_footer.php'); ?>

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