Calculate hours worked [closed]

Question

Calculate hours worked [closed]

Navigation

#1 by (0 votes)
#2 by (0 votes)
#3 by (0 votes)

2

How about, I'm doing a program in c # that shows the hours of the employees of a store in a week, and also calculates the hours worked, the problem or what I could not calculate correctly are the hours worked, since I have two tables in a bd in mysql, the table schedules and the checked table.

The timetable table has the following structure:

TIENDA | EMPLEADO | FECHA | E1 | S1 | E2 | S2 |

Store is the store number to which the employee belongs.

Employee is the key with which an employee is identified

The date is assigned per day and E1 S1 E2 S2 are the hours of entry and exit.

For example, an employee can be assigned to the day schedule on 07/21/2016 where he enters at 10:00 a.m., goes out to eat at 2:00 p.m., returns from lunch at 4:00 p.m. and leaves at 9:00 p.m.

Now in the checked table you have:

TIENDA | FECHA | FHORA | EMPLEADO | TIPO | HUELLA

FHORA is the date together with the exact employee check time.

TIPO indicates if it is entry or exit

Huella if you checked with a fingerprint reader or password.

My problem is that I have to calculate the actual hours worked, I know that I have to relate the date of the assigned schedule with the date of the checked table, what I do not know how to do is save the hours of entry and exit so that I I add them up and in the end show me the actual hours that an employee worked.

(In case an employee has not checked, for example, his meal return time, he is not assigned time)

I hope you have explained me and can help me, thanks:)

mysql c#

asked by Sandra 21.07.2016 в 17:04

source

3 answers

How to CONSUME a REST web service with Spring (Java)? How to Mark or Uncheck CheckBox?

score 0 · Answer 1

Tipo Entrada = date Fhora  
hEntrada1 = Fhora;    
Tipo Salida =  Fhora
hSalida1 = Fhora;

If the Date is the same

You should have 2 inputs and two outputs

hTrabajadaJornada1 = datediff( hSalida - hEntrada);

hTrabajadaJornada2 = datediff(Entrada - Salida);

HorasTRabajadasDiarias =  suma(hTrabajadaJornada 1 + hTrabajada2);

I hope I have explained well.

score 0 · Answer 2

I recommend you do this from the query to the database (explanation below):

(SQL Fiddle: link )

select
    h.tienda,
    h.empleado,
    h.fecha,
    ce1.fhora as hora_entrada_1,
    cs1.fhora as hora_salida_1,
    ce2.fhora as hora_entrada_2,
    cs2.fhora as hora_salida_2,
    DATE_FORMAT(timediff(convert(DATE_FORMAT(cs1.fhora,'%H:%i:%s'), time),
        convert(DATE_FORMAT(ce1.fhora,'%H:%i:%s'), time)),'%H:%i:%s') as diferencia_1,
    DATE_FORMAT(timediff(convert(DATE_FORMAT(cs2.fhora,'%H:%i:%s'), time),
        convert(DATE_FORMAT(ce2.fhora,'%H:%i:%s'), time)),'%H:%i:%s') as diferencia_2

    from horarios h
    left join checada ce1
        on ce1.empleado = h.empleado
    and ce1.tipo = 'ENTRADA'
    and ce1.fhora = (SELECT fhora
                    from checada c
                    where c.empleado = ce1.empleado
                    and c.fecha = ce1.fecha
                    and c.tipo = ce1.tipo
                    ORDER BY fhora asc
                    limit 1)
    left join checada cs1
        on cs1.empleado = h.empleado
        and cs1.tipo = 'SALIDA'
        and cs1.fhora = (SELECT fhora
                        from checada c
                        where c.empleado = cs1.empleado
                        and c.fecha = cs1.fecha
                        and c.tipo = cs1.tipo
                        ORDER BY fhora asc
                        limit 1)

    left join checada ce2
    on ce2.empleado = h.empleado
    and ce2.tipo = 'ENTRADA'
    and ce2.fhora = (SELECT fhora
                    from checada c
                    where c.empleado = ce2.empleado
                    and c.fecha = ce2.fecha
                    and c.tipo = ce2.tipo
                    ORDER BY fhora desc
                    limit 1)
    left join checada cs2
    on cs2.empleado = h.empleado
    and cs2.tipo = 'SALIDA'
    and cs2.fhora = (SELECT fhora
                    from checada c
                    where c.empleado = cs2.empleado
                    and c.fecha = cs2.fecha
                    and c.tipo = cs2.tipo
                    ORDER BY fhora desc
                    limit 1)

order by empleado ASC;

I will separate the entry and exit times of each day using JOIN , assuming that at most there will be two entries and two exits per employee per day. Then I calculated the time differences between the first entry and first exit, and between the second entry and the second exit. Then you just have to add both differences in your application and give you the total time per day for each employee.

As a consideration, if the employee only leaves and enters once, the times of entry and exit will be the same. Within the application you would have to compare them, and if they are the same, just take the first difference and not add the second one.

I apologize if the query is very long / inefficient. I do not have much experience in MySQL, really, nor do I have much time in this minute to analyze it too much.

I hope you have been clear and that it serves you.

score 0 · Answer 3

When a second input and output is not checked, the values in the DB are in a default value that allows to verify it truth ?, it is precise. Or that, or something that allows to know that there was no second period. I do not program in mysql, but since you just want to know how to do it ... There is a type of structure in C # called TimeSpan that can contain periods of time, perfect for what you need.

Then when you do not check the second schedule, the DateTime variables corresponding to the second entry and exit, must be 2 in the same and identical date and time , (pre-established before making the two assignments ), it is important because this way, when subtracting it leaves 0 hours, that is the correct thing in that case). You have to use for this 4 DateTime variables, to which you will assign the values as I have indicated, and that you will go to the method.

Note: DateTime is a special variable, it contains date and time necessarily, when you assign the values to the DateTime, you must assign them a complete date and time.

I suggest this code (C #) (checked) ..

    using System;

    internal decimal horasTrabajadas_HorarioPartido(DateTime check1_entrada, DateTime check1_salida, DateTime check2_entrada, DateTime check2_salida)
    {
    TimeSpan tiempoTrabajado = check1_salida.Subtract(check1_entrada).Duration();

    tiempoTrabajado += check2_salida.Subtract(check2_entrada).Duration();

    // Esta siguiente opcion de comentario, devolvería solo hora completas ...

    // return tiempoTrabajado.TotalHours;

    return decimal.Round(Convert.ToDecimal(tiempoTrabajado.TotalMinutes/60),1);
    }

This method would return the hours worked with a decimal, so as not to eliminate fractions of hours. (8.5 hours = 8 hours 30 minutes). If you want to return whole hours (complete), you can replace the return line with the other line of return that is just above it as a comment and change the type of value returned by the method (which is as decimal), by int.

Seen what you tell me, I suggest you, quen in the code with which you do the query, pass the hours of check to string, and check what value they adopt if they are empty (maybe an empty string "" or to be). Then if, when making a query and checking the check-in hours, some of the first 2 values (first check-in or first check-out) are missing that return a corresponding error in the calculation of hours, indicating that there is a lack of data to be able to perform the calculation of hours worked; And in case one of the two seconds is missing, check in and out, return a corresponding error indicating the fact that the value is missing.

Another way you can see, is the possibility of detailing what data is missing in the record, returning each of the details of the record checks in detail, instead of returning a calculation of hours, apart from indicate that it is not possible to make a reliable calculation of the hours worked because there is a lack of data in the registry, so that everything will be as clear as possible.