I have a problem creating a system in PHP / MySQL. I have a form that I need to insert:
Usually something happens as in the example: the employee enters on 04/29/2014 and leaves on 03/05/2014. I need 2 days worked for April, and 3 days worked for May. How can I solve this problem? And I do not know how best to create the tables in the database. Could someone give a light?
1) Convert the dates to PHP timestamp. Ex:
$entrada = mktime(0,0,0,4,29,2014); //29/04/2014
$saida = mktime(0,0,0,5,3,2014); // 03/05/2014
2) Assess whether there is a difference in the months. If there is a difference you should do two calculations, one for each month. If there is not you just subtract the dates.
$mesEntrada = date('n', $entrada);
$mesSaida = date('n', $saida );
if ($mesEntrada != $mesSaida)
{
$diasTrabalhadosMesEntrada = (date("t", $entrada)-date("j",$entrada))+1; // Subtrai o total de dias do mes pelo dia de entrada.
$diasTrabalhadosMesSaida = date('j', $saida ); // A quantidade de dias trabalhados é igual ao dia de saída.
// Inserir no BD...
}
else
{
// Apenas 1 mes (mesEntrada igual a mesSaida);
$diasTrabalhados = date("j",$saida)-date("j",$entrada);
// Inserir no BD...
}
With regard to DB, you can create a table that stores the month, year, and amount of days worked (columns, month, year, and working days) in separate columns. I also recommend creating a history table that stores all employee entries and exits (official columns, input and output).
I would suggest looking at link for a better understanding.
I did not test the code, it's just a targeting.
How about this:
Employees table:
Shifts table:
And then you get the days worked by doing saida - entrada . If this information is not to be changed later, you can even create a column for it in the plantões tab.