Hey Guys,
i just spent 2 hours yesterday to get my excel timesheets formatted properly, and that was my sign to find something new. I quickly found kimai and was impressed. The frontend is awesome, super usability!
But the euphory shrunk quickly. I needed three critical features:
- Grouping data. For the export, i need the hours to be grouped by tasks
- Own export interfaces. I need the data in an xml format to import into grand total (mac)
- (from the category 'would be awesome to have'): Automatically import redmine projects/issues into kimai as projects/tasks
Since i really like kimai and being a php developer, i tried to implement them for myself. But (please allow these few words of critic) the code is really awful:
- There is no developer documentation. You need to hang on it for yourself.
- The code does NOT document itself. Yes, there are some phpdoc tags, but the variable names are like 3 chars and underscores and stuff, nobody ever will find out what a function or variable is used for without a *huge* amount of time
- The coding style is not the best. No spaces between operators, bad naming style (also in the templates/css/js), curly brackets only when there are really needed, no (or very less) OOP, The database design (and naming! Hey, 3 char table names, are you serious
), ...
When i figured out that a critical function, which was needed by the export module and which i needed to modify, is declared in a core file, i resigned.
But enouth with the critics, the good thing is: i still like kimai
Theres is nothing to compare out there and it has a big potential. If you restyle your code i may contribute some, but at the moment i just needed to find a solution for my problems.
So i thought a bit and chose the following:
The redmine linking doesnt absolutly need to be implemented into kimai. Since i could figure out the database tables (yes, trial and error
) i wrote a little perl script which access the redmine export function and imports the tasks directly into the kimai database. This works really well for me, and im very happy with it now.
The export problem can also be solved via direct database access. When i finish this script, i will publish it here too.
The script:
#! /usr/bin/perl
use warnings;
use strict;
use DBI;
# the url to your redmine page which shows the issues (can be a project page, or the "all issues" page)
my $redmineUrl = "https://your.redmine.url/projects/project_name/";
# the redmine autologin cookie, you need this for script access to redmine
my $autoLogin = "f41e8d8a8c42d17551774579b867d636856aae97";
# your redmine filter id
my $filterID = 13;
# the kimai group id which the task should be assigned to (1 is mostly the admin group)
my $groupID = 1;
# database credentials from kimai
my ($database, $username, $password) = ("database", "username", "password");
qx#rm issues.csv#;
qx#wget -q --no-check-certificate --header="Cookie: autologin=$autoLogin" $redmineUrl/issues.csv?query_id=$filterID -O issues.csv#;
if(! -f 'issues.csv') {
die("Failed to download issues");
}
my %projectHash;
my $dbh = DBI->connect("DBI:mysql:$database", $username, $password)
or die("Cannot establish connection to mysql server: $DBI::errstr\n");
# Get all project from database
my $sth = $dbh->prepare("SELECT `pct_ID`, `pct_name` FROM `pct`");
$sth->execute();
while(my @row = $sth->fetchrow_array()) {
$projectHash{$row[1]} = $row[0];
}
# Create filter if not exists
if(! -f 'filter.txt') {
qx#touch filter.txt#;
}
# Add filters to an array
my @filter;
open FILTER, '<', 'filter.txt';
while(<FILTER>) {
push @filter, $_;
}
close FILTER;
open FILTER, '+>', 'filter.txt';
open FILE, '<', "issues.csv";
while(<FILE>) {
# skip the first line in export file
next if $_ =~ /^#;/;
# the whole filter. Gets the row you want, its *very* likely that you need to modifcy this for your export rows. Just add parantesis around the row - .*? - which you need
# the rows in the exportfile depend on the filter you set up in redmine
# for example, my export rows look like this (german version of redmine but i think you get the point):
# #;Status;Projekt;Tracker;Priorität;Thema;Zugewiesen an;Kategorie;Zielversion;Autor;Beginn;Abgabedatum;% erledigt;Geschätzter Aufwand;Angelegt;Aktualisiert;Angebot in h;Auftrag erhalten;Beschreibung
if(/^(.*?);.*?;(.*?);.*?;(.*?);(.*?);.*?;(.*?);.*?;(.*?);.*?;.*?;.*?;.*?;(.*?)/) {
my ($issue, $project, $priority, $topic, $category, $author, $creationDate) = ($1, $2, $3, $4, $5, $6, $7);
# skip if already imported
print "skipping $issue\n" and next if grep /$issue/, @filter;
# strip formatting quote which are used by redmine and replace them by plaintext quotes
$topic =~ s/(^"|"$)//g;
$topic =~ s/""/"/g;
# only import if the project of the issue exists as a project in kimai (needs to be exact the same spelling!)
my $pctId;
if($pctId = $projectHash{$project} or $pctId = $projectHash{$category}) {
# insert task into the task table
my $query = "INSERT INTO `evt` SET `evt_name` = CONCAT('#', ?, ': ', ?), `evt_comment` = CONCAT('Created By ', ?, ', Priority: ', ?)";
$sth = $dbh->prepare($query);
$sth->execute($issue, $topic, $author, $priority) or die("failed to execute query: $query\n");
# assign tasks to a group
$query = "INSERT INTO `grp_evt` SET `grp_ID` = ?, `evt_ID` = ?";
my $insertId = $dbh->last_insert_id(undef, undef, undef, undef);
$sth = $dbh->prepare($query);
$sth->execute($groupID, $insertId) or die("failed to execute query: $query\n");
# finally assign tasks to a project
$query = "INSERT INTO `pct_evt` SET `evt_ID` = ?, `pct_ID` = ?";
$sth = $dbh->prepare($query);
$sth->execute($insertId, $pctId) or die("failed to execute query: $query\n");
# add the issue to our filter file for not having duplicates
print FILTER "$issue\n";
}
}
}
close FILTER;
close FILE;
You can add in in a cronjob like this:
*/15 * * * * /usr/bin/perl /home/yourname/bin/redmineSync.pl | tee -a /var/log/redminesync.log &> /dev/null
Which will check for new issues every 15 minutes.
A few words you should note:
At first, you need to create the projects (and, if applies, customers) for yourself, this is not covered by this script. The Project names need to be the exact same spelling as in redmine or the script failes to import issues. You can also add redmine categories as projects, since i use redmine this way.
For example you have a redmine Project "Onlineshop" and the categories "Programming", "Accounting", "Support". Then you may add "Onlineshop", "Programming", "Accounting" and "Support" as projects into kimai and assign them to your customer (if applies). Beware the case sensitivity!
The next thing are the filter rows. You really may need to adjust them, because filters can be freely assembled in redmine. Go to your issue page of redmine where the issues are which you want to import. Now click on "CSV" at the bottom of the page and open the file. You open the file and look at the first lines. These are the rows which are needed to adjust for your needs. Go to line 62 in the script and add/remove parantesis for the ".*?" which stands for one row of the file. The needed rows are printed in the next line: "issue, project, priority..." (ok, not all of them are needed, but can be used eg. in the comment field). Adjust the parantesis that it fits the order of the variable names of the next line... $issue is the first (.*?), $project the next one, and so on.
Ah, and of course, the script works as well on windows if you download wget.exe, place it in the script dir and have perl installed.
Sorry for the inconvenience but i quickly hacked that script and added no ui or something. Maybe i'll add it later, if i get response. I dont even know if someone will ever use it but i'll post it anyway
So thanks kimai team and keep up the good work (and please, consider a style rewrite of your code =) )
Best regards,
Thomas
