We made a small script (PHP) for getting the time spent per ticket / per Department / per Month as an overview…
maybe someone needs it too or gets 1-2 ideas from it.
We needed to get costs by department of the customer and different rates per worker per ticket (we use our zammad for internal stuff ONLY and need to split the costs).
It’s pretty self explanatory.
columns atm are
TicketID, Ticket Title, Ticket Owner, Department, Time Supporter 1, Costs Supporter 1, Time Supporter 2, Costs Supporter 2 (extend as you like), Costs Total per Ticket.
We also started to sum things up by department (second table) but it’s not finished yet. Anyway, you’ll get the idea.
We will also add csv and pdf generation by cron.
Above there is a date/month dropdown (works in edge atm).
I’ll post newer versions when available.
And of course I would love to see such functionality in the next release (but I think it doesnt make sense for the majority of users to get costs per department per customer)…
Anyway… it took us 2-3 hours and maybe someone can use it too.
<html>
<head>
<style type="text/css">
.bod {font-family:Arial, sans-serif;font-size:14px;}
table {border-style:solid; border:1;border-collapse:collapse;border-spacing:0;}
table td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;}
table th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:1px;overflow:hidden;word-break:normal;border-color:black;font-weight:bold;}
.tg .tg-0lax{text-align:left;vertical-align:top}
</style>
</head>
<body class="bod">
<h1>IT-Abrechnung</h1>
<form action="/it-abrechnung.php" method=post>
Bitte Monat auswählen:<input type=month name="month"><br>
<input type=submit>
</form>
<?php
$reg='/^\d{4}-\d{2}$/';
if(ISSET($_POST['month'])){
$month=$_POST['month'];
if (preg_match($reg,$month)){
echo ("Erstelle Tabelle für $month<br>");
$year=substr($month,0,4);
$month=substr($month,5);
$dbconn = pg_connect("host=localhost dbname=zammad user=XXXXXXXXXXXXX password=XXXXXXXXXXXXX");
echo ("<h2>Summe pro Ticket</h2><br>\n");
createTicketTable($year,$month,$dbconn);
echo ("<br><br><br><h2>Summe pro Abteilung</h2><br>\n");
createAbteilungTable($year,$month,$dbconn);
pg_close($dbconn);
}else{
echo ("Falscher Eingabewert<br>Muss im Format YYYY-MM sein");
}
}
function createAbteilungTable($year,$month,$dbconn){
$result = pg_query("SELECT
u.department as Abteilung,
SUM(CASE WHEN tta.created_by_id = 4 THEN tta.time_unit ELSE 0 END) AS Zeit_MAO,
SUM(CASE WHEN tta.created_by_id = 4 THEN tta.time_unit ELSE 0 END) * 0.55 AS Summe_MAO,
SUM(CASE WHEN tta.created_by_id = 3 THEN tta.time_unit ELSE 0 END) AS Zeit_AN,
SUM(CASE WHEN tta.created_by_id = 3 THEN tta.time_unit ELSE 0 END) * 1.15 AS Summe_AN
FROM ticket_time_accountings tta
LEFT JOIN tickets t ON tta.ticket_id = t.id
LEFT JOIN users u ON u.id = t.customer_id
WHERE EXTRACT(Year FROM tta.created_at) = $year AND EXTRACT(Month FROM tta.created_at) = $month
GROUP BY
u.department
;");
echo ("
<table>
<tr>
<th>Abteilung</th>
<th>Zeit Malte</th>
<th>Summe Malte</th>
<th>Zeit Alex</th>
<th>Summe Alex</th>
<th>Summe Gesamt</th>
</tr>\n");
while ($line = pg_fetch_array($result,null,PGSQL_ASSOC)){
echo "\t<tr>\n";
echo("\t\t<td align=center>".$line['abteilung']."</td>\n");
echo("\t\t<td align=right>".number_format($line['zeit_mao']/60,$decimals=2)." h</td>\n");
echo("\t\t<td align=right>".number_format($line['summe_mao'],$decimals=2)." €</td>\n");
echo("\t\t<td align=right>".number_format($line['zeit_an']/60,$decimals=2)." h</td>\n");
echo("\t\t<td align=right>".number_format($line['summe_an'],$decimals=2)." €</td>\n");
$sum=$line['summe_mao']+$line['summe_an'];
echo("\t\t<td align=right>".number_format($sum,$decimals=2)." €</td>\n");
#echo "\t\t<td>$sum</td>\n";
echo "\t</tr>\n";
}
echo "</table>\n";
pg_free_result($result);
}
function createTicketTable($year,$month,$dbconn){
$result = pg_query("SELECT
t.id,
t.title,
CONCAT(u.firstname, ' ', u.lastname) AS Auftraggeber,
u.department as Abteilung,
SUM(CASE WHEN tta.created_by_id = 4 THEN tta.time_unit ELSE 0 END) AS Zeit_MAO,
SUM(CASE WHEN tta.created_by_id = 4 THEN tta.time_unit ELSE 0 END) * 0.95 AS Summe_MAO,
SUM(CASE WHEN tta.created_by_id = 3 THEN tta.time_unit ELSE 0 END) AS Zeit_AN,
SUM(CASE WHEN tta.created_by_id = 3 THEN tta.time_unit ELSE 0 END) * 1.05 AS Summe_AN
FROM tickets t
LEFT JOIN users u ON u.id = t.customer_id
LEFT JOIN ticket_time_accountings tta ON tta.ticket_id = t.id
WHERE EXTRACT(Year FROM tta.created_at) = $year AND EXTRACT(Month FROM tta.created_at) = $month
GROUP BY
t.id,
t.title,
u.lastname,
u.firstname,
u.department
;");
echo ("
<table>
<tr>
<th>Ticket-ID</th>
<th>Ticket</th>
<th>Auftraggeber</th>
<th>Abteilung</th>
<th>Zeit Malte</th>
<th>Summe Malte</th>
<th>Zeit Alex</th>
<th>Summe Alex</th>
<th>Summe Gesamt</th>
</tr>\n");
while ($line = pg_fetch_array($result,null,PGSQL_ASSOC)){
echo "\t<tr>\n";
echo("\t\t<td><a href=\"http://XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX/#ticket/zoom/".$line['id']."\">".$line['id']."</a></td>\n");
echo("\t\t<td>".$line['title']."</td>\n");
echo("\t\t<td>".$line['auftraggeber']."</td>\n");
echo("\t\t<td>".$line['abteilung']."</td>\n");
echo("\t\t<td>".number_format($line['zeit_mao'])."</td>\n");
echo("\t\t<td>".number_format($line['summe_mao'],$decimals=2)." €</td>\n");
echo("\t\t<td>".number_format($line['zeit_an'])."</td>\n");
echo("\t\t<td>".number_format($line['summe_an'],$decimals=2)." €</td>\n");
$sum=$line['summe_mao']+$line['summe_an'];
echo("\t\t<td>".number_format($sum,$decimals=2)." €</td>\n");
#echo "\t\t<td>$sum</td>\n";
echo "\t</tr>\n";
}
echo "</table>\n";
pg_free_result($result);
#phpinfo();
}
?>
</body>
</html>
