•  
     

Data e hora no Mysql

Data e hora no mysql

Código: Selecionar todos

<%
set ultimoponto = conn.execute("select registro.dia_semana as dia_semana, registro.feriado as feriado, registro.sigla_registro as sigla, usuario.nome as nome, registro.data as data, registro.horario_chegada as horario_chegada,registro.horario_saida as horario_saida, registro.matricula as matricula from registro as registro inner join usuario as usuario on usuario.matricula=registro.matricula where registro.data between '"&mask_dataa(request.Form("datainicio"))&"' and '"&mask_dataa(request.Form("datafim"))&"' and registro.matricula='"&request.Form("matricula")&"'")
%>
<table style="font-family:Calibri; font-size:13px; width:1000px; margin:auto;" border="1">
  <tr>
    <td>Nome</td>
    <td>Matricula</td>
    <td>Dia Semana</td>
    <td>Feriado</td>
    <td>Entrada</td>
    <td>Saida</td>
    <td>Hora Trabalhada</td>
    <td>Extra</td>
    <td>Atraso</td>
    <td>Observação</td>
    <td>Extra 100%</td>
    <td>Falta</td>
  </tr>
<%
 
while ultimoponto.eof = false
 
DATA1 = Cdate(right(ultimoponto("horario_chegada"),8)) 'DATA ATUAL
DATA2 = Cdate(right(ultimoponto("horario_saida"),8)) 'DATA FINALIZAÇÃO
DIAS = DateDiff("d",DATA1,DATA2)
If DateDiff("h",DATA1,DATA2) <> 0 Then
HORA = DateDiff("h",DATA1,DATA2) - Cint(DIAS) * 24
Else
HORA = 0
End If
If DateDiff("n",DATA1,DATA2) <> 0 Then
MINUTO = DateDiff("n",DATA1,DATA2) - DateDiff("h",DATA1,DATA2) * 60
Else
MINUTO = 0
End If
If DateDiff("s",DATA1,DATA2) <> 0 Then
SEGUNDO = DateDiff("s",DATA1,DATA2) - DateDiff("n",DATA1,DATA2) * 60
Else
SEGUNDO = 0
End If
If SEGUNDO < 0 Then
MINUTO = MINUTO - 1
SEGUNDO = 60 - SEGUNDO + SEGUNDO * 2
End If
If MINUTO < 0 Then
HORA = HORA - 1
MINUTO = 60 - MINUTO + MINUTO * 2
End If
If HORA < 0 Then
DIAS = DIAS - 1
HORA = 24 - HORA + HORA * 2
End If
 
if HORA<9 then
HORA = "0"&HORA
end if
 
if MINUTO<9 then
MINUTO = "0"&MINUTO
end if
 
if SEGUNDO<9 then
SEGUNDO = "0"&SEGUNDO
end if
 
If DIAS = 0 AND HORA > 0 Then
HORAEXTRA = HORA & ":" & MINUTO & ":" & SEGUNDO
ElseIf DIAS = 0 AND HORA = 0 AND SEGUNDO > 1 Then
HORAEXTRA = HORA & ":" & MINUTO & ": " & SEGUNDO
End If
 
JORNADA = CDate("09:00:00")            
TRABALHADA = CDate(HORAEXTRA)
%>
 
  <tr>
    <td><%=ultimoponto("nome")%></td>
    <td><%=ultimoponto("matricula")%></td>
    <td><%=formatdatetime(ultimoponto("data"),1)%></td>
    <td><%=ultimoponto("feriado")%></td>
    <td>
<%
if formatdatetime(ultimoponto("horario_chegada"),3) = "00:01:00" then
response.Write("00:00:00")
else
response.Write(formatdatetime(ultimoponto("horario_chegada"),3))
end if
%>
    </td>
    <td>
<%
if formatdatetime(ultimoponto("horario_saida"),3) = "09:01:00" then
response.Write("00:00:00")
else
response.Write(formatdatetime(ultimoponto("horario_saida"),3))
end if
%>
</td>
    <td><%
if CDate(CDate(DATA2) - CDate(DATA1)) = CDate("09:00:00") then
response.Write("00:00:00")
else
response.Write(CDate(CDate(DATA2) - CDate(DATA1)))
end if
%></td>
    <td>
    <%
IF TRABALHADA = JORNADA THEN
RESPONSE.Write("00:00:00")
ELSEIF TRABALHADA < CDATE(JORNADA) THEN
RESPONSE.Write("")
ELSEIF TRABALHADA > JORNADA THEN
response.Write(CDate(CDate(TRABALHADA) - CDate(JORNADA)))
END IF
%>
    </td>
    <td>
    <%
IF ultimoponto("dia_semana") = "DOM" or ultimoponto("dia_semana") = "SAB" or ultimoponto("feriado") <> "" then
 
response.Write("")
 
else
 
IF TRABALHADA = JORNADA THEN
RESPONSE.Write("")
ELSEIF CDATE(DATA1) = CDATE(DATA2) THEN
RESPONSE.Write("")
ELSEIF TRABALHADA < CDATE(JORNADA) THEN
response.Write("-"&CDate(CDate(JORNADA) - CDate(TRABALHADA)))
END IF
 
end if
%>
    </td>
    <td>
    </td>
    <td>
    <%
IF DATA1 <> cdate("00:01:00") and DATA2 <> cdate("09:01:00") and ultimoponto("dia_semana") = "DOM" then
response.Write(TRABALHADA)
elseIF DATA1 <> cdate("00:01:00") and DATA2 <> cdate("09:01:00") and ultimoponto("dia_semana") = "SAB" then
response.Write(TRABALHADA)
elseIF DATA1 <> cdate("00:01:00") and DATA2 <> cdate("09:01:00") and ultimoponto("feriado") <> "" then
response.Write(TRABALHADA)
end if
%>
    </td>
    <td>
    <%
IF ultimoponto("dia_semana") = "DOM" or ultimoponto("dia_semana") = "SAB" or ultimoponto("feriado") <> "" or DATA1 <> cdate("00:01:00") or DATA2 <> cdate("09:01:00") THEN
RESPONSE.Write("")
elseIF ultimoponto("dia_semana") <> "DOM" or ultimoponto("dia_semana") <> "SAB" or ultimoponto("feriado") <> "" and formatdatetime(ultimoponto("horario_chegada"),3) = cdate("00:01:00") and formatdatetime(ultimoponto("horario_saida"),3) = cdate("09:01:00") THEN
response.Write("faltou")
END IF
%>
    </td>
  </tr>
<%
ultimoponto.movenext
wend
%>
  <tr>
    <td colspan="12" align="center">
      <%
set verificahoraextra = conn.execute("select TIME_TO_SEC(timediff(sec_to_time(sum(TIME_TO_SEC(horario_saida))),sec_to_time(sum(TIME_TO_SEC(horario_chegada))))) as horatrabalhada,sum(TIME_TO_SEC(jornada)) as jornada from registro where data between '"&mask_dataa(request.Form("datainicio"))&"' and '"&mask_dataa(request.Form("datafim"))&"' and registro.matricula='"&request.Form("matricula")&"' and dia_semana not in ('SAB','DOM') and horario_chegada!='00:01:00' and horario_saida!='09:01:00'")
 
 
set horaextra = conn.execute("select timediff(sec_to_time(sum(TIME_TO_SEC(horario_saida))),sec_to_time(sum(TIME_TO_SEC(horario_chegada)))) as horatrabalhada, sec_to_time(sum(TIME_TO_SEC(jornada))) as jornada, timediff(timediff(sec_to_time(sum(TIME_TO_SEC(horario_saida))),sec_to_time(sum(TIME_TO_SEC(horario_chegada)))), sec_to_time(sum(TIME_TO_SEC(jornada)))) as horaextra from registro where data between '"&mask_dataa(request.Form("datainicio"))&"' and '"&mask_dataa(request.Form("datafim"))&"' and registro.matricula='"&request.Form("matricula")&"' and dia_semana not in ('SAB','DOM') and horario_chegada!='00:01:00' and horario_saida!='09:01:00'")
 
set funcionario = conn.execute("select * from usuario where matricula='"&request.Form("matricula")&"'")
 
if Cdbl(verificahoraextra("jornada")) > Cdbl(verificahoraextra("horatrabalhada")) then
response.Write("O funcionario '"&funcionario("nome")&"' teve "&Cdate(right(horaextra("horaextra"),8))&" de Hora Extra")
elseif Cdbl(verificahoraextra("jornada")) < Cdbl(verificahoraextra("horatrabalhada")) then
response.Write("O funcionario '"&funcionario("nome")&"' teve "&Cdate(right(horaextra("horaextra"),8))&" de Atraso")
end if
 
%>
<%
set verificahoracemporcento = conn.execute("select TIME_TO_SEC(timediff(sec_to_time(sum(TIME_TO_SEC(horario_saida))),sec_to_time(sum(TIME_TO_SEC(horario_chegada))))) as horatrabalhada,sum(TIME_TO_SEC(jornada)) as jornada from registro where data between '"&mask_dataa(request.Form("datainicio"))&"' and '"&mask_dataa(request.Form("datafim"))&"' and registro.matricula='"&request.Form("matricula")&"' and dia_semana in ('SAB','DOM') and horario_chegada!='00:01:00' and horario_saida!='09:01:00'")
 
set horacemporcento = conn.execute("select timediff(sec_to_time(sum(TIME_TO_SEC(horario_saida))),sec_to_time(sum(TIME_TO_SEC(horario_chegada)))) as horatrabalhada, sec_to_time(sum(TIME_TO_SEC(jornada))) as jornada, timediff(timediff(sec_to_time(sum(TIME_TO_SEC(horario_saida))),sec_to_time(sum(TIME_TO_SEC(horario_chegada)))), sec_to_time(sum(TIME_TO_SEC(jornada)))) as horaextra from registro where data between '"&mask_dataa(request.Form("datainicio"))&"' and '"&mask_dataa(request.Form("datafim"))&"' and registro.matricula='"&request.Form("matricula")&"' and dia_semana in ('SAB','DOM') and horario_chegada!='00:01:00' and horario_saida!='09:01:00'")
 
 
response.Write(" e "&Cdate(right(horacemporcento("horatrabalhada"),8))&" hora extra 100%")
 
 
%>    </td>
    </tr>
</table>