eb299c9131
Kimai time management system (PHP 5.6 / MySQL) with custom extensions: - Budget tracking (ki_budget) - Change request form (ki_changerequest) - Invoice generation (ki_invoice) - Expense tracking (ki_expenses) - Flexi time (ki_flexitime) - Export, admin panel, timesheets, tasks, summary Database: ~900K time entries, 2,000+ users, 439 projects. Config: includes/autoconf.php — localhost/kimai/kimai
267 lines
9.6 KiB
PHP
267 lines
9.6 KiB
PHP
<?php
|
|
$hostname="localhost";
|
|
$username="kimai";
|
|
$password="kimai";
|
|
$dbname="kimai";
|
|
$conn = mysqli_connect($hostname,$username, $password, $dbname) or die ("<html><script language='JavaScript'>alert('Unable to connect to database! Please try again later.'),history.go(-1)</script></html>");
|
|
|
|
$date_string = '';
|
|
|
|
$fromdate = gmdate("Y-m-d",$in);
|
|
$todate = gmdate("Y-m-d",$out);
|
|
|
|
|
|
$date = $todate;
|
|
$date1 = str_replace('-', '/', $date);
|
|
$todate = date('Y-m-d',strtotime($date1 . "+1 days"));
|
|
|
|
$date = $fromdate;
|
|
$date1 = str_replace('-', '/', $date);
|
|
$fromdate = date('Y-m-d',strtotime($date1 . "+1 days"));
|
|
|
|
|
|
|
|
|
|
$sql = "SELECT (5 * (DATEDIFF('$todate', '$fromdate') DIV 7) + MID('0123455501234445012333450122234501101234000123450',
|
|
7 * WEEKDAY('$fromdate') + WEEKDAY('$todate') + 1, 1)) * 8.5 AS TotalResolutionTimeBusinessDays ";
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
while ($row = mysqli_fetch_array($result)) {
|
|
$corehours = $row['TotalResolutionTimeBusinessDays'];
|
|
|
|
|
|
}
|
|
|
|
?>
|
|
|
|
|
|
<style>
|
|
|
|
table {
|
|
border-collapse: collapse;
|
|
border-spacing: 0;
|
|
margin-bottom:30px;
|
|
}
|
|
th {
|
|
text-align: left;
|
|
font-weight: bold;
|
|
}
|
|
th {
|
|
border-right: 1px solid #999999;
|
|
padding: 5px;
|
|
}
|
|
th:first-child { border-left: 1px solid #999999; }
|
|
td {
|
|
border-right: 1px solid #999999;
|
|
padding: 5px;
|
|
}
|
|
td:first-child { border-left: 1px solid #999999; }
|
|
</style>
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
<legend>Summary Chart</legend>
|
|
|
|
|
|
<table border="1px" style="margin: 10px;" class="highchart" data-graph-container-before="1" data-graph-type="column" >
|
|
<thead>
|
|
<tr>
|
|
<th>Name</th>
|
|
<th>Total Hours</th>
|
|
<th data-graph-type="line">Core Working Hours</th>
|
|
<th>Difference</th>
|
|
</tr>
|
|
</thead>
|
|
<tbody>
|
|
<?php
|
|
$sql = "select u.usr_alias as 'alias',
|
|
round(sum(e.zef_time) / 3600,2) as time_spent,
|
|
((5 * (DATEDIFF('$todate', '$fromdate') DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY('$fromdate') + WEEKDAY('$todate') + 1, 1)) * 8.5) AS needed_time,
|
|
round(((sum(e.zef_time) / 3600) - ((5 * (DATEDIFF('$todate', '$fromdate') DIV 7) + MID('0123455501234445012333450122234501101234000123450', 7 * WEEKDAY('$fromdate') + WEEKDAY('$todate') + 1, 1)) * 8.5)),2) as diff
|
|
from kimai15_zef e
|
|
inner join kimai15_usr u on e.zef_usrID=u.usr_ID
|
|
inner join kimai15_pct p on e.zef_pctID=p.pct_ID
|
|
where
|
|
e.zef_in > UNIX_TIMESTAMP(DATE_FORMAT('$fromdate','%Y-%m-%d')) and
|
|
e.zef_in < UNIX_TIMESTAMP(DATE_FORMAT('$todate','%Y-%m-%d'))
|
|
group by u.usr_name
|
|
";
|
|
|
|
|
|
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
while ($row = mysqli_fetch_array($result)) {
|
|
$name = $row['alias'];
|
|
$time_spent = $row['time_spent'];
|
|
$needed_time = $row['needed_time'];
|
|
$diff = $row['diff'];
|
|
echo "<tr><td>$name</td><td>$time_spent</td><td>$needed_time</td><td>$diff</td><tr>";
|
|
}
|
|
?>
|
|
</tbody>
|
|
</table>
|
|
|
|
<br/>
|
|
<legend>Detailed Summary</legend>
|
|
<table border="1px" style="margin: 10px; width: 99%; font-size: 9px">
|
|
<thead>
|
|
<tr>
|
|
<th>Project Name</th>
|
|
|
|
|
|
|
|
<?php
|
|
$sql = "select u.usr_name as 'name', u.usr_alias as 'alias' from kimai15_zef e
|
|
inner join kimai15_usr u on e.zef_usrID=u.usr_ID
|
|
inner join kimai15_pct p on e.zef_pctID=p.pct_ID
|
|
where
|
|
e.zef_in > UNIX_TIMESTAMP(DATE_FORMAT('$fromdate','%Y-%m-%d')) and
|
|
e.zef_in < UNIX_TIMESTAMP(DATE_FORMAT('$todate','%Y-%m-%d'))
|
|
group by u.usr_name
|
|
";
|
|
|
|
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
while ($row = mysqli_fetch_array($result)) {
|
|
|
|
$name = $row['name'];
|
|
$alias = $row['alias'];
|
|
echo "<th>$alias</th>";
|
|
$names[] = $name;
|
|
}
|
|
?>
|
|
<th>TOTAL</th>
|
|
</tr>
|
|
</thead>
|
|
|
|
<?php
|
|
$sql = "select p.pct_name as project, ROUND(SUM(e.zef_time) / 3600, 1) as project_total from kimai15_zef e
|
|
inner join kimai15_usr u on e.zef_usrID=u.usr_ID
|
|
inner join kimai15_pct p on e.zef_pctID=p.pct_ID
|
|
where
|
|
e.zef_in > UNIX_TIMESTAMP(DATE_FORMAT('$fromdate','%Y-%m-%d')) and
|
|
e.zef_in < UNIX_TIMESTAMP(DATE_FORMAT('$todate','%Y-%m-%d'))
|
|
group by p.pct_name
|
|
";
|
|
|
|
|
|
|
|
$result = mysqli_query($conn, $sql);
|
|
$totalforreport = 0;
|
|
while ($row = mysqli_fetch_array($result)) {
|
|
$projecttotal = $row['project_total'];
|
|
$project = $row['project'];
|
|
echo "<tr><td>$project</td>";
|
|
foreach ($names as $name) {
|
|
$sqla = "select ROUND(SUM(e.zef_time) / 3600, 1) as 'totaltime'
|
|
from kimai15_zef e
|
|
inner join kimai15_usr u on e.zef_usrID=u.usr_ID
|
|
inner join kimai15_pct p on e.zef_pctID=p.pct_ID
|
|
where
|
|
e.zef_in > UNIX_TIMESTAMP(DATE_FORMAT('$fromdate','%Y-%m-%d')) and
|
|
e.zef_in < UNIX_TIMESTAMP(DATE_FORMAT('$todate','%Y-%m-%d'))
|
|
and p.pct_name = '$project'
|
|
and u.usr_name = '$name'
|
|
group by p.pct_name;";
|
|
$result1 = mysqli_query($conn, $sqla);
|
|
|
|
|
|
$total = 0;
|
|
if (mysqli_num_rows($result1) != 0) {
|
|
while ($row = mysqli_fetch_array($result1)) {
|
|
$time = $row['totaltime'];
|
|
echo "<td>$time</td>";
|
|
}
|
|
} else {
|
|
echo "<td></td>";
|
|
}
|
|
}
|
|
|
|
echo "<td>$projecttotal</td>";
|
|
echo "</tr>";
|
|
$totalforreport = $totalforreport + $projecttotal;
|
|
}
|
|
?>
|
|
|
|
<tr>
|
|
<td>TOTAL</td>
|
|
<?php
|
|
foreach ($names as $name) {
|
|
$sql = "select u.usr_alias as name, ROUND(SUM(e.zef_time) / 3600, 1) as 'totaltime'
|
|
from kimai15_zef e
|
|
inner join kimai15_usr u on e.zef_usrID=u.usr_ID
|
|
inner join kimai15_pct p on e.zef_pctID=p.pct_ID
|
|
where
|
|
e.zef_in > UNIX_TIMESTAMP(DATE_FORMAT('$fromdate','%Y-%m-%d')) and
|
|
e.zef_in < UNIX_TIMESTAMP(DATE_FORMAT('$todate','%Y-%m-%d'))
|
|
and u.usr_name = '$name'
|
|
group by u.usr_name;";
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
while ($row = mysqli_fetch_array($result)) {
|
|
$time = $row['totaltime'];
|
|
echo "<td>$time</td>";
|
|
}
|
|
}
|
|
echo "<td>$totalforreport</td>";
|
|
?>
|
|
</tr>
|
|
|
|
|
|
<tr>
|
|
<td>Core Working Hours</td>
|
|
<?php
|
|
$totalcorehours = 0;
|
|
foreach ($names as $name) {
|
|
echo "<td>$corehours</td>";
|
|
$totalcorehours = $totalcorehours + $corehours;
|
|
}
|
|
echo "<td>$totalcorehours</td>";
|
|
?>
|
|
|
|
</tr>
|
|
|
|
<tr>
|
|
<td>Difference</td>
|
|
<?php
|
|
foreach ($names as $name) {
|
|
$sql = "select u.usr_alias as name, ROUND(SUM(e.zef_time) / 3600, 2) as 'totaltime'
|
|
from kimai15_zef e
|
|
inner join kimai15_usr u on e.zef_usrID=u.usr_ID
|
|
inner join kimai15_pct p on e.zef_pctID=p.pct_ID
|
|
where
|
|
e.zef_in > UNIX_TIMESTAMP(DATE_FORMAT('$fromdate','%Y-%m-%d')) and
|
|
e.zef_in < UNIX_TIMESTAMP(DATE_FORMAT('$todate','%Y-%m-%d'))
|
|
and u.usr_name = '$name'
|
|
group by u.usr_name;";
|
|
$result = mysqli_query($conn, $sql);
|
|
|
|
while ($row = mysqli_fetch_array($result)) {
|
|
$time = $row['totaltime'] - $corehours;
|
|
echo "<td>$time</td>";
|
|
}
|
|
}
|
|
$difference = $totalforreport - $totalcorehours;
|
|
echo "<td>$difference</td>";
|
|
?>
|
|
</tr>
|
|
|
|
|
|
|
|
|
|
|
|
</table>
|
|
|
|
|
|
|
|
|
|
|
|
|