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
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
|
<!DOCTYPE html>
<?php include_once '../include/DBConnect.php'; ?>
<?php
if (isset($_GET['search']['submit'])) {
$sth = $dbh->query("SELECT s.working_mode,la.staff_id s FROM lesson_attendance la,staff s WHERE la.lesson_id in (Select lesson_id from lesson where date >='" . $_GET['search']['startdate_id'] . "' and date <= '" . $_GET['search']['Enddate_id'] . "' and deleted = 0 and actived = 1) and la.actived = 1 and la.deleted = 0 and la.staff_id <> 0 and s.actived = 1 and s.deleted = 0 and la.staff_id = s.staff_id group by 2 order by 2");
while ($ResultSet = $sth->fetch(PDO::FETCH_ASSOC))
$amount_list[$ResultSet['s']] = array("hour" => 0, "amount" => 0, "is_FT" => $ResultSet['working_mode']);
$sth = $dbh->query("SELECT la.staff_id s,CEIL(TIME_TO_SEC(TIMEDIFF(l.end_time,l.start_time))/3600) hour, la.rate,s.salary,s.rate1,s.rate2,s.rate3,s.rate4,s.rate5,s.rate6
FROM lesson_attendance la,lesson l,staff s
WHERE la.lesson_id in
(Select lesson_id from lesson where date >='" . $_GET['search']['startdate_id'] . "' and date <= '" . $_GET['search']['Enddate_id'] . "' and deleted = 0 and actived = 1)
and la.actived = 1 and la.deleted = 0
and la.staff_id <> 0
and l.actived = 1 and l.deleted = 0
and l.lesson_id = la.lesson_id
and s.actived = 1 and s.deleted = 0
and la.staff_id = s.staff_id");
while ($ResultSet = $sth->fetch(PDO::FETCH_ASSOC)) {
switch ((int) $ResultSet['rate']) {
case 0:
$amount = $ResultSet['salary'] * $ResultSet["hour"];
$hour = $ResultSet['hour'];
break;
case 1:
$amount = $ResultSet['rate1'] * $ResultSet["hour"];
$hour = $ResultSet['hour'];
break;
case 2:
$amount = $ResultSet['rate2'] * $ResultSet["hour"];
$hour = $ResultSet['hour'];
break;
case 3:
$amount = $ResultSet['rate3'] * $ResultSet["hour"];
$hour = $ResultSet['hour'];
break;
case 4:
$amount = $ResultSet['rate4'] * $ResultSet["hour"];
$hour = $ResultSet['hour'];
break;
case 5:
$amount = $ResultSet['rate5'] * $ResultSet["hour"];
$hour = $ResultSet['hour'];
break;
case 6:
$amount = $ResultSet['rate6'] * $ResultSet["hour"];
$hour = $ResultSet['hour'];
break;
}
for ($i = 0; $i < sizeof($amount_list); $i++) {
if ($ResultSet['s'] == key($amount_list)) {
$amount_list[$ResultSet['s']]['amount'] += $amount;
$amount_list[$ResultSet['s']]['hour'] += $hour;
break;
}
next($amount_list);
}
reset($amount_list);
}
for ($a = 0; $a < sizeof($amount_list); $a++) {
$sth = $dbh->prepare("INSERT INTO `payroll`(`createby`, `createdate`, `lastupby`, `lastupdate`, `actived`, `deleted`, `staff_id`, `code`, `remarks`)
VALUES (0,now(),0,now(),1,0,?,'PR000" . $a . "',0)");
$sth->bindParam(1, key($amount_list));
$sth->execute();
next($amount_list);
date_default_timezone_set("Asia/Hong_Kong");
$create_datetime = date("Y-m-d H:i:s");
}
reset($amount_list);
$sth = $dbh->query("SELECT payroll_id from payroll WHERE actived=1 and deleted=0 and createdate='$create_datetime' ");
while ($ResultSet = $sth->fetch(PDO::FETCH_ASSOC)) {
$sth1 = $dbh->prepare("INSERT INTO `payroll_line`(`createby`, `createdate`, `lastupby`, `lastupdate`, `actived`, `deleted`, `payroll_id`, `is_FT`, `amount`, `original_fee`, `hour`, `remarks`)
VALUES (0,now(),0,now(),1,0,?,?,?,?,?,1)");
$sth1->bindParam(1, $ResultSet['payroll_id']);
$sth1->bindParam(2, $amount_list[key($amount_list)]["is_FT"]);
$sth1->bindParam(5, $amount_list[key($amount_list)]["hour"]);
$sth1->bindParam(3, $amount_list[key($amount_list)]["amount"]);
$sth1->bindParam(4, $amount_list[key($amount_list)]["amount"]);
$sth1->execute();
next($amount_list);
}
reset($amount_list);
}
?>
<html>
<head>
<?php include_once '../include/head.php'; ?>
<?php include_once '../include/checkuser.php'; ?>
<?php include_once '../include/Nav_bar.php'; ?>
<script>
$(function() {
$('#start_date_id').datetimepicker({ autoclose: true,pickTime: false, weekStart: 1 });
$('#start_date_id').datetimepicker().on('changeDate', function(ev){
date = new Date($('#Start_date').val());
$('#End_date_id').datetimepicker('setStartDate',date);
$('#End_date_id').datetimepicker('setDate',date);
$('#End_date_id').datetimepicker('show');
$('#start_date_id').datetimepicker('hide');
$('#End_date_id').datetimepicker('remove');
});
$('#End_date_id').datetimepicker({ autoclose: true,pickTime: false, weekStart: 1,startDate: new Date() });
$('#End_date_id').datetimepicker().on('changeDate', function(ev){ $('#End_date_id').datetimepicker('hide'); });
$('#staff_name').select2();
});
</script>
</head>
<body>
<div class="text-right">
<ul class="breadcrumb">
<li class="active">Payroll</li>
</ul>
</div>
<div class="container-fluid pathways-container">
<h3>Payroll</h3>
<form action="" method="get">
<div id="2" style="margin: 0;padding: 5px 10px;background-color: #F7F7F7;border: 1px solid #E6E6E6;border-radius: 5px;box-shadow: 0 1px 2px rgba(0,0,0,.05);">
<table>
<tr>
<td style="width: 15px;"></td>
<td><label class="" for="startdate_id">Start Date </label></td>
<td style="width:15px;"></td>
<td>
<div id="start_date_id" class="input-append date-picker" style="margin-top: 10px;">
<input id="Start_date" style="width: 200px" class="dateISO" type="text" maxlength="200" placeholder="Start Date" data-format="yyyy-MM-dd" value="<?= date("Y-m-d", time()) ?>" name="search[startdate_id]">
<span class="add-on">
<i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
</span>
</div>
</td>
<td style="width: 15px;"></td>
<td><label class="" for="startdate_id">End Date </label></td>
<td style="width:15px;"></td>
<td>
<div id="End_date_id" class="input-append date-picker" style="margin-top: 10px;">
<input id="data_input_date" style="width: 200px" class="dateISO" type="text" maxlength="200" placeholder="End Date" data-format="yyyy-MM-dd" value="<?= date("Y-m-d", time()) ?>" name="search[Enddate_id]">
<span class="add-on">
<i class="icon-calendar" data-date-icon="icon-calendar" data-time-icon="icon-time"></i>
</span>
</div>
</td>
<td style="width: 15px"></td>
<td><button name="search[submit]" class="btn" style="margin-left:30px;" type="submit"><i class="icon-plus" ></i> Generate</button></td>
</tr>
</table>
<table>
<tr>
<td style="width: 15px;"></td>
<td>Staff Name</td>
<td style="width: 15px;"></td>
<td><input/></td>
<td style="width: 15px;"></td>
<td><button name="search[search_btn]" class="btn" style="margin-left: 30px;" type="submit"><i class="icon-search"></i> Search</button></td>
</tr>
</table>
</div>
</form>
<div style="height: 15px"></div>
<table class="table table-striped table-bordered table-hover table-condensed">
<thead>
<tr>
<th>Payroll No.</th>
<th>Staff Name</th>
<th>Creation Date</th>
<th>Details</th>
</tr>
</thead>
<tbody>
<?php if (isset($_GET['search']['submit'])) { ?>
<?php $sth = $dbh->query("SELECT payroll_id,code pc,concat(last_name,' ',first_name) name from payroll p,staff s WHERE p.actived=1 and p.deleted=0 and s.actived=1 and s.deleted=0 and s.staff_id = p.staff_id and p.createdate='$create_datetime' ") ?>
<?php while ($ResultSet = $sth->fetch(PDO::FETCH_ASSOC)) { ?>
<tr>
<td><?= $ResultSet['pc'] ?></td>
<td><?= $ResultSet['name'] ?></td>
<td><?= date("Y-m-d", strtotime($create_datetime)) ?></td>
<td style="width: 80px"><a class="btn" href="Payroll_DetailForm.php?cdate=<?= date("Y-m-d", strtotime($create_datetime)) ?>&name=<?= $ResultSet['name'] ?>&code=<?= $ResultSet['pc'] ?>&startdate=<?= $_GET['search']['startdate_id'] ?>&enddate=<?= $_GET['search']['Enddate_id'] ?>&staffid=<?= key($amount_list) ?>"><i class="icon-info-sign"></i> Detail</a></td>
</tr>
<?php next($amount_list); ?>
<?php } ?>
<?php } ?>
<?php if (isset($_GET['search']['search_btn'])) { ?>
<?php // $sth = $dbh->query(); ?>
<?php } ?>
</tbody>
</table>
</div>
</body>
</html>
|