webadmin.php

/var/www/onesupportsys.onesolution.hk/report/ClientServiceReport.php

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

    // Check permission
    if (!Util::isAdmin()) {
        redirectAndExit(Util::link(__DIR__ . '/../main.php'));
    }

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

    $token = explode("-", $start_date);
    $start_date = $token[0]."-".$token[1]."-01";
    $token2 = explode("-", $end_date);
    $end_date  = $token2[0]."-".$token2[1]."-".date('t', strtotime($end_date));

    $sql = "
SELECT job.*,customer.*,staff.*, job.id as job_id
FROM sup_job job, v_cm_customer_support customer, sys_login staff
WHERE job.customer_id = customer.cust_id AND job.staff_id = staff.id
    AND (COALESCE(LENGTH(?), 0) = 0 OR job.customer_id = ?)
    AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
    AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)

GROUP BY job.customer_id
ORDER BY customer.company_name ASC,job.id ASC
";
    $parameters = array(
        $customer_id, $customer_id,
        $start_date, $start_date,
        $end_date, $end_date,

    );


    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");
    }

    $num_companies = $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();

    return array(
        'staffs' => $staffs,
        'customers' => $customers,
        'num_companies' => $num_companies,
    );

}

extract(index());

if(isset($_GET['start_date'])){
    $_start_date = $_GET['start_date'];
}else
    $_start_date = null;

if(isset($_GET['end_date'])){
    $_end_date = $_GET['end_date'];
}else
    $_end_date = null;



$_customer_id = $_GET['customer_id'] ?: null;

?><!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'); ?>

    <?php if($_GET["search"] == 1){ ?>
        <script>
            $(function(){
                $("#output_excel").submit();
            });
        </script>
    <?php } ?>

</head>
<body>

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

<div class="container">

<?php if (isset($_GET["message"]) && !empty($_GET["message"])): ?>
    <div class="alert alert-info">
        <button type="button" class="close" data-dismiss="alert">&times;</button>
        <h5 class="alert-heading">Note:</h5>

        <p><?= $_GET["message"] ?></p>
    </div>
<?php endif; ?>

<h2>Client Service Report</h2>

<div class="container-fluid">
    <div class="row-fluid">
        <div class="span12">
            <div class="hero-unit" style="padding:10px">
                <a href="#" id="search_btn" class="btn btn-link"><h4><i class="icon-list-alt"></i> Search Form</h4></a>
                <script type="text/javascript">
                    $(function() {
                        $('#search_btn').click(function(event) {
                            event.preventDefault();
                            $('#search_form').toggle();
                        });
                        $('.date-picker').datetimepicker({ pickTime: false });
                        $('.time-picker').datetimepicker({ pickDate: false, pickSeconds: false });
                        $('.select2').select2();
                        $('#search_form').validate();
                    });
                </script>
                <form id="search_form" class="form-horizontal" method="get">
                    <input type="hidden" name="search" value="1" />
                    <div class="control-group">
                        <?php $attribute = 'customer_id';$_value = '_customer_id'; $label = 'Customer'; ?>
                        <label class="control-label" for="<?=$attribute?>"><?=$label?></label>
                        <div class="controls">
                            <select id="<?=$attribute?>" name="<?=$attribute?>" placeholder="Select a <?=$label?>" class="select2 required">
                                <option value=""></option>
                                <?php foreach ($customers as $customer): ?>
                                    <option value="<?=$customer['cust_id']?>"<?=$customer['cust_id'] == $$_value ? ' selected="selected"' : ''?>><?=$customer['enable'] == 1 ? '' : '[Disabled] '?><?=h($customer['company_name'])?></option>
                                <?php endforeach; ?>
                            </select>
                        </div>
                    </div>


                    <div class="control-group">
                        <?php $attribute = 'start_date';$_value = '_start_date'; $label = 'Start Date'; ?>
                        <label class="control-label" for="<?=$attribute?>"><?=$label?></label>
                        <div class="controls">
                            <div class="input-append date-picker">
                                <input type="text" id="<?=$attribute?>" name="<?=$attribute?>" class="required" data-format="yyyy-MM-dd" value="<?=$$_value?>" placeholder="<?=$label?>" />
                                        <span class="add-on">
                                            <i data-time-icon="icon-time" data-date-icon="icon-calendar"></i>
                                        </span>
                            </div>
                        </div>
                    </div>
                    <div class="control-group">
                        <?php $attribute = 'end_date'; $_value = '_end_date';$label = 'End Date'; ?>
                        <label class="control-label" for="<?=$attribute?>"><?=$label?></label>
                        <div class="controls">
                            <div class="input-append date-picker">
                                <input type="text" id="<?=$attribute?>" name="<?=$attribute?>" class="required" data-format="yyyy-MM-dd" value="<?=$$_value?>" placeholder="<?=$label?>" />
                                        <span class="add-on">
                                            <i data-time-icon="icon-time" data-date-icon="icon-calendar"></i>
                                        </span>
                            </div>
                        </div>
                    </div>


                    <div class="control-group">
                        <div class="controls">
                            <button type="submit" class="btn btn-primary"><i class="icon-search icon-white"></i> Search</button>
                            <button type="reset" class="btn" onclick="top.location.href='ClientServiceReport.php'"><i class="icon-refresh icon-black"></i> Reset</button>
                        </div>
                    </div>

                </form>
            </div>
        </div><!--/span-->
    </div><!--/row-->
</div>

<div style="text-align: right">
    <form id="output_excel" action="print_ClientServiceReport.php" method="post">
        <input type="hidden" name="_customer_id" value="<?=$_customer_id?>" />
        <input type="hidden" name="_start_date" value="<?=$_start_date?>" />
        <input type="hidden" name="_end_date" value="<?=$_end_date?>" />
        <input type="hidden" name="project_job" value="<?=$_GET["project_job"]?>" />
        <input type="hidden" name="contract_job" value="<?=$_GET["contract_job"]?>" />

        <?php if($_GET["search"] == 1){ ?>
            <button type="submit" class="btn btn-primary">Print</button>
        <?php } ?>
    </form>
</div>


<?php /*if($_GET["search"] == 1){ ?>

    <table class="table table-striped">
    <caption class="text-left"><h2>Search Result</h2></caption>
    <thead>
    <tr>

        <th>&nbsp;</th>
        <th>Customer</th>
        <th style="width: 75px;">Job Date</th>
        <th>Start Time</th>
        <th>End Time</th>
        <th>Actual Start Time</th>
        <th>Actual End Time</th>
        <th>Schedule Call</th>
        <th>Services Call</th>
        <th>Urgent Call</th>
        <th>Remote Call</th>
        <th>Staff</th>
        <th>Status</th>
    </tr>
    </thead>
    <tbody>
    <?php


        foreach ($num_companies as $companies){

            //get contract data

            $sql3 = "SELECT * FROM sup_contract WHERE contract_from <= ? and ? <= contract_to and customer_id = ? limit 1";

            $parameters3 = array($_end_date, $_start_date, $companies["customer_id"]);


            if (!($sth = $dbh->prepare($sql3))) {
                throw new Exception("sql prepare statement failure: $sql3");
            }
            $sth->setFetchMode(PDO::FETCH_ASSOC);
            if (!$sth->execute($parameters3)) {
                throw new Exception("sql execute statement failure: $sql3");
            }
            //$contract = $sth->fetch(PDO::FETCH_ASSOC);

            while($contract = $sth->fetch(PDO::FETCH_ASSOC)){

                $num_schedule_call = 0;
                $num_services_call = 0;
                $num_urgent_call = 0;
                $num_remote_call = 0;
                $total_job = 0;

                if(!empty($contract)){  //search time and contract time overlap

                    /*$sql2 = "
        SELECT job.*,customer.*,staff.*, job.id as job_id
        FROM sup_job job, v_cm_customer_support customer, sys_login staff
        WHERE job.customer_id = customer.cust_id AND job.staff_id = staff.id
            AND (COALESCE(LENGTH(?), 0) = 0 OR job.customer_id = ?)
            AND (COALESCE(LENGTH(?), 0) = 0 OR job.staff_id = ?)
            AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
            AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)
            AND job.type IS NOT NULL".$include_contract_job.$include_project_job."
        ORDER BY customer.company_name ASC,job.id ASC
        ";

                    $sql2 = "
    SELECT job.*,customer.*,staff.*, job.id as job_id
    FROM sup_job job, v_cm_customer_support customer, sys_login staff
    WHERE job.customer_id = customer.cust_id AND job.staff_id = staff.id
        AND (COALESCE(LENGTH(?), 0) = 0 OR job.customer_id = ?)
        AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
        AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)
        AND job.type IS NOT NULL
    ORDER BY job.date ASC
    ";



                    /*if($_start_date < $contract["contract_from"])
                        $search_job_start_date = $contract["contract_from"];
                    else
                        $search_job_start_date = $_start_date;

                    if($_end_date < $contract["contract_to"])
                        $search_job_end_date = $_end_date;
                    else
                        $search_job_end_date = $contract["contract_to"];


                    $parameters2 = array(
                        //$customer_id, $customer_id,
                        $companies["customer_id"], $companies["customer_id"],
                        $_start_date, $_start_date,
                        $_end_date, $_end_date
                    );


                    if (!($sth2 = $dbh->prepare($sql2))) {
                        throw new Exception("sql prepare statement failure: $sql2");
                    }

                    $sth2->setFetchMode(PDO::FETCH_ASSOC);
                    if (!$sth2->execute($parameters2)) {
                        throw new Exception("sql execute statement failure: $sql2");
                    }

                    $this_company = $sth2->fetchAll();

                    foreach($this_company as $company){

                        $total_flag = 1;
                        $total_job++;
                        if($company["type"] == 1)
                            $num_schedule_call++;
                        else if($company["type"] == 2)
                            $num_services_call++;
                        else if($company["type"] == 3)
                            $num_urgent_call++;
                        else if($company["type"] == 4)
                            $num_remote_call++;
                        else {}

                    ?>
                        <tr>
                            <td><a href="../job/modifyform.php?id=<?=$company['job_id']?>" class="btn"><i class="icon-pencil"></i></a></td>
                            <td><?=$company['company_name']?></td>
                            <td><?=$company['date']?></td>
                            <td ><?=$company['start_time']?></td>
                            <td><?=$company['end_time']?></td>
                            <td <?php if (!empty($company['actual_start_time']) && $company['actual_start_time'] > $company['start_time']) echo "style='background-color: #FA5858;'" ?>><?=$company['actual_start_time']?></td>
                            <td <?php if (!empty($company['actual_end_time']) && $company['actual_end_time'] > $company['end_time']) echo "style='background-color: #FA5858;'" ?>><?=$company['actual_end_time']?></td>
                            <?php
                                if($company['type']==1){
                                    echo "<td><i class='icon-ok'></i></td>";
                                    echo "<td></td>";
                                    echo "<td></td>";
                                    echo "<td></td>";
                                }else if($company['type'] == 2){
                                    echo "<td></td>";
                                    echo "<td><i class='icon-ok'></i></td>";
                                    echo "<td></td>";
                                    echo "<td></td>";
                                }else if($company['type'] == 3){
                                    echo "<td></td>";
                                    echo "<td></td>";
                                    echo "<td><i class='icon-ok'></i></td>";
                                    echo "<td></td>";
                                }else if($company['type'] == 4){
                                    echo "<td></td>";
                                    echo "<td></td>";
                                    echo "<td></td>";
                                    echo "<td><i class='icon-ok'></i></td>";
                                }else{}

                            ?>

                            <td><?=h($company['username'])?></td>
                            <td><?php $job_status = Job::statusOptions(); echo $job_status[$company['status']]; ?></td>

                        </tr>
                    <?php
                    }


                    $search_date_range = dateRange( $_start_date, $_end_date, '+1 month');
                    $contract_date_range = dateRange( $contract["contract_from"], $contract["contract_to"], '+1 month');

                    $overlap_month = array();

                    foreach($search_date_range as $search_date){
                        foreach($contract_date_range as $contract_date){
                            if($search_date == $contract_date){ //overlap month
                                $overlap_month[] = $search_date;
                            }
                        }
                    }

                    if($contract["contract_type"] == 1){    //monthly
                        $size_of_overlap_month = sizeof($overlap_month);

                        if(!empty($contract['schedule_call_3hrs'])){
                            $contract_schedule_call = $contract['schedule_call_3hrs']*$size_of_overlap_month;
                        }else if(!empty($contract['schedule_call_half_day'])){
                            $contract_schedule_call = $contract['schedule_call_half_day']*$size_of_overlap_month;
                        }else if(!empty($contract['schedule_call_full_day'])){
                            $contract_schedule_call = $contract['schedule_call_full_day']*$size_of_overlap_month;
                        }else{
                            $contract_schedule_call = 0;
                        }


                        if($contract['service_call'] == 0)
                            $contract_service_call = 0;
                        else
                            $contract_service_call = $contract['service_call']*$size_of_overlap_month;

                        if($contract['urgent_call'] == 0)
                            $contract_urgent_call = 0;
                        else
                            $contract_urgent_call = $contract['urgent_call']*$size_of_overlap_month;

                        if($contract['remote_service_call'] == 0)
                            $contract_remote_call = 0;
                        else
                            $contract_remote_call = $contract['remote_service_call']*$size_of_overlap_month;


                        $total_call = $contract_schedule_call+$contract_service_call+$contract_urgent_call+$contract_remote_call;
                    }else{  //package
                        if(!empty($contract['schedule_call_3hrs'])){
                            $contract_schedule_call = $contract['schedule_call_3hrs'];
                        }else if(!empty($contract['schedule_call_half_day'])){
                            $contract_schedule_call = $contract['schedule_call_half_day'];
                        }else if(!empty($contract['schedule_call_full_day'])){
                            $contract_schedule_call = $contract['schedule_call_full_day'];
                        }else{
                            $contract_schedule_call = 0;
                        }

                        if($contract['service_call'] == 0)
                            $contract_service_call = 0;
                        else
                            $contract_service_call = $contract['service_call'];

                        if($contract['urgent_call'] == 0)
                            $contract_urgent_call = 0;
                        else
                            $contract_urgent_call = $contract['urgent_call'];

                        if($contract['remote_service_call'] == 0)
                            $contract_remote_call = 0;
                        else
                            $contract_remote_call = $contract['remote_service_call'];


                        $total_call = $contract_schedule_call+$contract_service_call+$contract_urgent_call+$contract_remote_call;
                    }
                }else{
                    $contract_schedule_call = 0;
                    $contract_service_call = 0;
                    $contract_urgent_call = 0;
                    $contract_remote_call = 0;
                    $total_call = 0;
                }


                //display count num of each type

                if($total_job == 0){
                ?>
                    <tr>
                        <td></td>
                        <td colspan="13" style="text-align: center;">No Jobs for the below contract</td>
                    </tr>

                <?php
                }
                    //if (empty($_staff_id)) {
                    ?>
                    <tr>
                        <td colspan="6" style="">
                            <span style="padding-left: 55px;">
                                <a href="../contract/modifyform.php?id=<?=$contract['contract_id']?>" class="btn" target="_blank"><i class="icon-pencil"></i></a>
                            </span>

                            <span style="padding:0 8px 0 8px;vertical-align: top !important;font-weight: bold;">
                                Contract Summary
                            </span>

                            <span style="padding:0 8px 0 8px;vertical-align: top !important;">
                                From: <?=$contract['contract_from']?>
                            </span>

                            <span style="padding:0 8px 0 8px;vertical-align: top !important;">
                                To: <?=$contract['contract_to']?>
                            </span>
                        </td>


                        <td><?php if($contract['contract_type'] == 1) echo "Monthly"; else if($contract['contract_type'] == 2) echo "Package"; else {}?></td>



                        <td <?php if ($num_schedule_call > $contract_schedule_call) echo "style='background-color: #FA5858;'" ?>><?= $num_schedule_call . " / " . $contract_schedule_call ?></td>
                        <td <?php if ($num_services_call > $contract_service_call) echo "style='background-color: #FA5858;'" ?>><?= $num_services_call . " / " . $contract_service_call ?></td>
                        <td <?php if ($num_urgent_call > $contract_urgent_call) echo "style='background-color: #FA5858;'" ?>><?= $num_urgent_call . " / " . $contract_urgent_call ?></td>
                        <td <?php if ($num_remote_call > $contract_remote_call) echo "style='background-color: #FA5858;'" ?>><?= $num_remote_call . " / " . $contract_remote_call ?></td>

                        <td><?= "( ".$total_job."/".$total_call." )" ?></td>

                        <td><?php $job_status = Job::statusOptions(); echo $job_status[$company['status']]; ?></td>

                    </tr>
                    <?php   //}

                //}
            } $total_flag = 0;    }  ?>
    </tbody>
    </table>

<?php
}*/
?>




<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() {
        $('.scroll-to-top').click(function(event) {
            event.preventDefault();
            scrollToTop();
        });
    });
</script>

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

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