0

I have a database in access where the employees register themseves every day of work.

If they don't get to work, theres no registration.

The clock start to register from march 28th 2014 untill now.

An employee could be not hire in the 2014, so he or she has no registered until he or she start to work with us.

For example: A guy start to work in december 7th, but he did not come to work in december 9th.

With Kettle I fill from the regitration into a mysql table he came to work in december 7th, 8th, 10th and 11th.

TABLE 1 (assistance)

id  employee_id in                   lunch                lunch_end            out
1   1234        2015-12-07 08:00:00  2015-12-07 13:00:00  2015-12-07 14:00:00  2015-12-07 18:00:00
2   1234        2015-12-08 08:15:00  2015-12-08 13:00:00  2015-12-08 14:00:00  2015-12-08 18:00:00
3   1234        2015-12-10 07:55:00  2015-12-10 13:00:00  2015-12-10 14:00:00  2015-12-10 17:41:00
4   1234        2015-12-11 08:00:00  2015-12-11 13:00:00  2015-12-11 14:00:00  2015-12-11 18:00:00

With CakePHP3 I show in a view this data. But due he was absent in december 9th, I dont have that registration.

VIEW 1

EMPLOYEE      DAY                  IN       LUNCH    LUNCH END  OUT      STATUS
Harry Potter  December 7th, 2015   8:00 am  1:00 pm  2:00 pm    6:00 pm  OK
Harry Potter  December 8th, 2015   8:15 am  1:00 pm  2:00 pm    6:00 pm  LATE IN
Harry Potter  December 10th, 2015  7:55 am  1:00 pm  2:00 pm    5:41 pm  EARLY OUT
Harry Potter  December 11th, 2015  8:00 am  1:00 pm  2:00 pm    6:00 pm  OK

So, in the view I must show he was absent on december 9th. But I can not do this due the row for december 9th is not in TABLE 1.

I would like to add a row into CakePHP 3 view with the data of TABLE 1 on the fly and the script can't be slow or too resource consuming:

VIEW 2

EMPLOYEE      DAY                  IN       LUNCH    LUNCH END  OUT      STATUS
Harry Potter  December 7th, 2015   8:00 am  1:00 pm  2:00 pm    6:00 pm  OK
Harry Potter  December 8th, 2015   8:15 am  1:00 pm  2:00 pm    6:00 pm  LATE IN
Harry Potter  December 9th, 2015   -        -        -          -        ABSENT
Harry Potter  December 10th, 2015  7:55 am  1:00 pm  2:00 pm    5:41 pm  EARLY OUT
Harry Potter  December 11th, 2015  8:00 am  1:00 pm  2:00 pm    6:00 pm  OK

Because the clock start in march 28th, 2014 I must skip until the first regsitration of this employee (or any other employee). Of cuorse, every employee start to work on diferents dates.

I can't figure how to solve this algorithm. Please, help!!

JuanEs1eban
  • 61
  • 7
  • Further similar Qs http://stackoverflow.com/q/75752 | http://stackoverflow.com/q/1046865 | http://stackoverflow.com/q/29664686 – ndm Dec 16 '15 at 21:42
  • How do you fill the table with Kettle? It should be easy to add the data in the db in your transformation. – bolav Dec 16 '15 at 23:36
  • The data is in an access db in a very raw format. In a field I have the employee_id, then the type of the operation (In, out, lunch, etc) and then the time of the event. In a step, I use denormalize row step to make it like TABLE 1.Due is not pure mysql, I belive the similar or duplicate sugestion are not ver helpfull. – JuanEs1eban Dec 17 '15 at 00:08
  • 1
    The duplicates will help you to solve the problem on the CakePHP/MySQL side. If you want/need to solve this on the kettle side, ie add actual "empty" rows in the database in beforehand, then you should mention that in your question, and try to call the attention of a kettle expert. – ndm Dec 17 '15 at 12:55

0 Answers0