Kimai2: Prevent overlapping times for one user?

[juppin]

Is your feature request related to a problem? Please describe.
I have some users that do time tracking and sometimes they have entered overlapping times...
This is very time consuming for me to ask the workers to change there work times to not overlapp and sometimes they don´t remember there correct times if it was some time ago.

Describe the solution you'd like
Can we prevent the user to enter work times that overlapp with a setting like:

kimai:
    timesheet:
        overlapping:
            allow_for_user: false

Describe alternatives you've considered
Instruct the workers to recheck the entered times twice :-)

[kevinpapst]

Nothing available right now, but sounds like a legit feature request.

[StefanWinterberger]

Would be in fact, a really nice feature. I upvoted.

[kimaiTSG]

Is your feature request related to a problem? Please describe.
I have some users that do time tracking and sometimes they have entered overlapping times...
This is very time consuming for me to ask the workers to change there work times to not overlapp and sometimes they don´t remember there correct times if it was some time ago.

Describe the solution you'd like
Can we prevent the user to enter work times that overlapp with a setting like:

kimai:
    timesheet:
        overlapping:
            allow_for_user: false

Describe alternatives you've considered
Instruct the workers to recheck the entered times twice :-)

This works?,
Where do I apply this in kimai...
Thanks

[kimaiTSG]

Hi, a question, I want create a query to validate that the entered record does not match a created record, could you tell me where I can apply this query...

$qb = $this->getEntityManager()->createQueryBuilder();
$qb->select('t.begin')
->from(Timesheet::class, 't')
->andWhere('t.begin BETWEEN :inicio AND :fin')
->setParameter('inicio', $timesheet->getBegin())
->setParameter('fin', $timesheet->getEnd());

   $resultVal = $qb->getResult();

and generate an alert showing that you cannot register at the moment because it coincides with another registration, for example:

if ($resultVal > 1) {
$context->buildViolation('Tienes un registro asignado en este horario.')
->atPath('begin')
->setTranslationDomain('validators')
->setCode(TimesheetConstraint::ERROR_CONFLICTO)
->addViolation();
}

If you can help me thanks...

[kevinpapst]

The validation logic goes here:
https://github.com/kevinpapst/kimai2/blob/master/src/Validator/Constraints/TimesheetValidator.php

And the query should be added to the repository:
https://github.com/kevinpapst/kimai2/blob/master/src/Repository/TimesheetRepository.php

And all variables and messages should be in English if you sent in a PR for this feature.

[kimaiTSG]

Hello again... Implement the code sections where you mentioned but I have a doubt if something additional is missing in the execution of the code.

in this route:
https://github.com/kevinpapst/kimai2/blob/master/src/Validator/Constraints/TimesheetValidator.php

Implement this:

$begin = $timesheet->getBegin();

    $resultVal = $this->repository->validarHora($begin);   

    if ($resultVal > 0) {

        $context->buildViolation('You have an assigned record in these hours.')
            ->atPath('begin')
            ->setTranslationDomain('validators')
            ->setCode(TimesheetConstraint::ERROR_CONFLICT)
            ->addViolation();
    }

and in this route:
https://github.com/kevinpapst/kimai2/blob/master/src/Repository/TimesheetRepository.php

Implement this...
/**
* @param DateTime $begin
* @return Timesheet[]
*/
public function validarHora(?DateTime $begin)
{
$qb = $this->getEntityManager()->createQueryBuilder();

    $qb->select('t')
        ->from(Timesheet::class, 't')
        ->andWhere(':b BETWEEN :begin AND :end')
        ->setParameter('b', $begin)
        ->setParameter('begin', '2020-04-13 00:00:00')
        ->setParameter('end', '2020-04-13 23:59:59');

    $resultVal = $qb->getQuery()->getResult();

    return $resultVal;
}

the result is this

but at that time there are no records...

my idea as I have already mentioned is not to allow multiple records in the same time range, I would like to know if you could help me with this code if there is something missing to validate the time in the database and if it is registered, show the warning of otherwise save the record.

Thanks.

[kevinpapst]

You compare null / an object with an int:
if ($resultVal > 0) { should be if (null !== $resultVal) {
but I am not completely sure, maybe you always get an empty array, then it would be:
if (!empty($resultVal)) {

And in the select you compare multiple strings: ':b BETWEEN :begin AND :end'.

Try something like ':b BETWEEN t.begin AND t.end'. Even though this is logically still wrong, but let's try to get it running first.

[kimaiTSG]

Hello again, now if it works as it should be, if the person registers in a busy schedule it does not allow him to do it and he must choose another schedule, thanks.

His recommendation was what was missing

[kevinpapst]

I don't really understand what you want to say.
Can you share your code as PR so everyone can use it?

[kimaiTSG]

Okay, more simple, 2 activity records in the same schedule can't, with your suggestion the modification was made, thanks.

How do I identify my PR?

[kevinpapst]

If you don't know how to submit a PR, you can also simply copy & paste your code and I will add it manually.

[kimaiTSG]

https://github.com/kevinpapst/kimai2/blob/master/src/Repository/TimesheetRepository.php
/**
* @param DateTime $begin
* @param User $user
* @return Timesheet[]
*/
public function validateHour(?DateTime $begin, ?User $user)
{
$qb = $this->getEntityManager()->createQueryBuilder();

    $qb->select('t')
        ->from(Timesheet::class, 't')
        ->andWhere(':b BETWEEN t.begin AND t.end')
        ->andWhere('t.user = :u')
        ->setParameter('b', $begin)
        ->setParameter('u', $user);

    $resultVal = $qb->getQuery()->getResult();

    return $resultVal;
}

---

https://github.com/kevinpapst/kimai2/blob/master/src/Validator/Constraints/TimesheetValidator.php

use App\Repository\TimesheetRepository;

add variable

/**
* @var TimesheetRepository
*/
private $repository;

added in the constructor

/**
* @param AuthorizationCheckerInterface $auth
* @param TimesheetConfiguration $configuration
*/
public function __construct(TimesheetRepository $repository, AuthorizationCheckerInterface $auth, TimesheetConfiguration $configuration, TrackingModeService $service)
{
$this->repository = $repository;
$this->auth = $auth;
$this->configuration = $configuration;
$this->trackingModeService = $service;
}

I add the condition in this function

protected function validateBeginAndEnd(TimesheetEntity $timesheet, ExecutionContextInterface $context)
{
$begin = $timesheet->getBegin();
$user = $timesheet->getUser();

    $resultVal = $this->repository->validateHour($begin, $user); 

    if (!empty($resultVal)) {

        $context->buildViolation('You have an assigned record in these hours.')
            ->atPath('begin')
            ->setTranslationDomain('validators')
            ->setCode(TimesheetConstraint::ERROR_CONFLICTO)
            ->addViolation();
    }

}

[kevinpapst]

Thanks for sharing. You know that you currently don't check the user in the query?
You need to add at least a andWhere('t.user = :user') ...

[kimaiTSG]

It is true, I only tried with a user, if I enter with another sample "You have an assigned record in these hours."

[kimaiTSG]

thanks again, I already updated the commit to make it work...

[kevinpapst]

Please have a look at #1720 and if possible: test it and let me now what you found.

Fixing the issue completely would be a massive change (as there are too many ways to create timesheets), so I went with the 80% rule: creating manual overlapping records will now be prevented.

Together with the new visual indicator in the timesheet listing (the red dotted line between records) this should help in fixing most of the accidentally overlapping bookings.