| Server IP : 210.245.233.93 / Your IP : 216.73.216.226 Web Server : Apache/2.2.15 (CentOS) System : Linux webserver2.onesolution.com.hk 2.6.32-754.35.1.el6.x86_64 #1 SMP Sat Nov 7 12:42:14 UTC 2020 x86_64 User : apache ( 48) PHP Version : 5.3.3 Disable Function : exec, shell_exec, system, passthru, popen, proc_open, pcntl_exec MySQL : ON | cURL : ON | WGET : ON | Perl : ON | Python : ON | Sudo : ON | Pkexec : ON Directory : /var/www/onesupportsys.onesolution.hk/report/ |
Upload File : |
<?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'])){
$token = explode("-", $_GET['start_date']);
$_start_date = $token[0]."-".$token[1]."-01";
}else
$_start_date = null;
if(isset($_GET['start_date'])){
$token2 = explode("-", $_GET['end_date']);
$_end_date = $token2[0]."-".$token2[1]."-".date('t', strtotime($_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">×</button>
<h5 class="alert-heading">Note:</h5>
<p><?= $_GET["message"] ?></p>
</div>
<?php endif; ?>
<h2>Monthly Usage Report (Contract)</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, format: "yyyy-MM",
viewMode: "months",
minViewMode: "months" });
$('.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">
<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 Month'; ?>
<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" 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 Month'; ?>
<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" 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='MonthlyUsageReport.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_MonthlyUsageReport.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> </th>
<th>Customer</th>
<th style="width: 75px;">Contract From</th>
<th style="width: 75px;">Contract To</th>
<th>Contract Type</th>
<th>Schedule Call / [Contract]</th>
<th>Services Call / [Contract]</th>
<th>Urgent Call / [Contract]</th>
<th>Remote Call / [Contract]</th>
<th>Total Call / [Contract]</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 = ?";
$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
";
//for counting the project num
$sql4 = "
SELECT job.*, job.id as job_id
FROM sup_job job
AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` >= ?)
AND (COALESCE(LENGTH(?), 0) = 0 OR job.`date` <= ?)
AND job.type IS NULL
AND job_project_id IS NOT NULL
ORDER BY job.id ASC
";
$parameters4 = array(
$start_date,$start_date,
$end_date, $end_date
);
if (!($sth4 = $dbh->prepare($sql4))) {
throw new Exception("sql prepare statement failure: $sql4");
}
//if (!$sth4->execute($parameters4)) {
// throw new Exception("sql execute statement failure: $sql4");
//}
$num_project = $sth4->rowCount();
$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 customer.company_name ASC,job.id ASC
";
//if($contract["contract_type"] == 1){
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"];
}else{
$search_job_start_date = $contract["contract_from"];
$search_job_end_date = $contract["contract_to"];
}
$search_job_start_date = $start_date;
$search_job_end_date = $end_date;
$parameters2 = array(
//$customer_id, $customer_id,
$companies["customer_id"], $companies["customer_id"],
$search_job_start_date,$search_job_start_date,
$search_job_end_date,$search_job_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 {}
}
$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;
}
}
//display count num of each type
//if (empty($_staff_id)) {
?>
<tr>
<td><a href="../contract/modifyform.php?id=<?=$contract['contract_id']?>" class="btn" target="_blank"><i class="icon-pencil"></i></a></td>
<td><?=$company['company_name']?></td>
<td><?=$contract['contract_from']?></td>
<td ><?=$contract['contract_to']?></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><?=h($company['username'])?></td>
<td><?php $job_status = Job::statusOptions(); echo $job_status[$company['status']]; ?></td>
</tr>
<?php //}
}
} ?>
</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>