1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
|
<?php
//echo $_GET["customer_id"];
include ('../../inc/configure.php');
$customer_id = (int)$_GET["customer_id"];
/*$sql = "SELECT * FROM sup_contract where status = 1 and customer_id = '$customer_id'";
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");
}
$contract_calls = $sth->fetchAll();
*/
$nowdate = date("Y-m-d");
$num_schedule_call = 0;
$num_services_call = 0;
$num_urgent_call = 0;
$num_remote_call = 0;
$total_job = 0;
//get contract data
$sql3 = "SELECT * FROM sup_contract WHERE ? <= contract_to and customer_id = ? and status = ?";
if (!($sth = $dbh->prepare($sql3))) {
throw new Exception("sql prepare statement failure: $sql3");
}
$sth->setFetchMode(PDO::FETCH_ASSOC);
if (!$sth->execute(array($nowdate,$customer_id, "1"))) {
throw new Exception("sql execute statement failure: $sql3");
}
$contract = $sth->fetch(PDO::FETCH_ASSOC);
if(!empty($contract)){ //search time and contract time overlap
$search_date_range = dateRange( '2000-01-01', $nowdate, '+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'])){
$schedule_call_type = "(3hrs)";
$contract_schedule_call = $contract['schedule_call_3hrs']*$size_of_overlap_month;
}else if(!empty($contract['schedule_call_half_day'])){
$schedule_call_type = "(4hrs)";
$contract_schedule_call = $contract['schedule_call_half_day']*$size_of_overlap_month;
}else if(!empty($contract['schedule_call_full_day'])){
$schedule_call_type = "(9hrs)";
$contract_schedule_call = $contract['schedule_call_full_day']*$size_of_overlap_month;
}else{
$contract_schedule_call = 0;
}
$contract_service_call = $contract['service_call']*$size_of_overlap_month;
$contract_urgent_call = $contract['urgent_call']*$size_of_overlap_month;
$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_service_call = $contract['service_call'];
$contract_urgent_call = $contract['urgent_call'];
$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;
}
$sql2 = "
SELECT job.*,customer.*, job.id as job_id
FROM sup_job job, v_cm_customer_support customer
WHERE job.customer_id = customer.cust_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` <= ?)
ORDER BY customer.company_name ASC,job.id ASC
";
$parameters2 = array(
$customer_id, $customer_id,
$contract["contract_from"], $contract["contract_from"],
$contract["contract_to"], $contract["contract_to"],
);
if (!($sth = $dbh->prepare($sql2))) {
throw new Exception("sql prepare statement failure: $sql2");
}
$sth->setFetchMode(PDO::FETCH_ASSOC);
if (!$sth->execute($parameters2)) {
throw new Exception("sql execute statement failure: $sql2");
}
$this_company = $sth->fetchAll();
foreach($this_company as $company){
$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 {}
}
if($total_call != 0){
?>
<table class="table table-striped" style="width:550px;">
<caption class="text-left"><strong>Up to Today, this <?php if($contract["contract_type"] == 1) echo "Monthly Contract "; else echo "Package ";?>remain calls:</strong></caption>
<thead>
<tr>
<th>Schedule Call<?=$schedule_call_type?></th>
<th>Services Call</th>
<th>Urgent Call</th>
<th>Remote Call</th>
<th>Total Call</th>
</tr>
</thead>
<tr>
<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>
</tr>
</table>
<?php
}else{
echo "No Contract";
}
?>
|