Apoio para montar uma query

[WebCorplfs 0]

Pessoal, bom dia,.. por gentileza alguem pode me dar uma ajuda:

Preciso montar uma consulta que funcione da seguinte forma:

SELECT distinct hora_ponto, data, pis 
FROM conferencia_ponto
where pis = '013025727232' and data = '2017/01/30'

que traz o seguinte resultado:

hora_ponto	data	pis
07:40:00	30/01/2017 00:00:00	013025727232
10:59:00	30/01/2017 00:00:00	013025727232
12:56:00	30/01/2017 00:00:00	013025727232
17:25:00	30/01/2017 00:00:00	013025727232

* hora ponto é um campo que estou importando de um txt de relogio de ponto

E outra  query com a seguinte estrutura e resultado :

SELECT b.horario, a.data, a.pis    
 FROM escala_individual as a, cadastro_horario_itens as b, cadastro_horarios as c
 where a.evento = b.codigo_horario and b.codigo_horario = c.codigo AND a.pis = '013025727232'
 and a.data = '2017/01/31' group by data, horario

horario	data	pis
07:30:00	31/01/2017 00:00:00	013025727232
12:00:00	31/01/2017 00:00:00	013025727232
13:00:00	31/01/2017 00:00:00	013025727232
17:00:00	31/01/2017 00:00:00	013025727232

* horario é o campo que parametrizei com as tabelas cadastro_horarios x cadastro_horario_itens, ou seja, sao horarios de trabalho pre definidos.

nao consegui ate o momento montar uma query que me apresentasse da seguinte forma:

horario	hora_ponto	data	pis
07:30:00	07:40:00	31/01/2017 00:00	13025727232
12:00:00	10:59:00	31/01/2017 00:00	13025727232
13:00:00	12:56:00	31/01/2017 00:00	13025727232
17:00:00	17:25:00	31/01/2017 00:00	13025727232

O problema que estou tendo é que ao trazer os registros com um join e as devidas clausulas where ele repete alguns valores devido a quantidade de registros na tabela cadsatro_horario_itens... nao sei se fui claro...

alguem pode me ajudar?

[WebCorplfs 0]

Em 16/03/2017 at 16:06, WebCorplfs disse:

Pessoal, bom dia,.. por gentileza alguem pode me dar uma ajuda:

Preciso montar uma consulta que funcione da seguinte forma:

SELECT distinct hora_ponto, data, pis 
FROM conferencia_ponto
where pis = '013025727232' and data = '2017/01/30'

que traz o seguinte resultado:

hora_ponto	data	pis
07:40:00	30/01/2017 00:00:00	013025727232
10:59:00	30/01/2017 00:00:00	013025727232
12:56:00	30/01/2017 00:00:00	013025727232
17:25:00	30/01/2017 00:00:00	013025727232

* hora ponto é um campo que estou importando de um txt de relogio de ponto

E outra  query com a seguinte estrutura e resultado :

SELECT b.horario, a.data, a.pis    
 FROM escala_individual as a, cadastro_horario_itens as b, cadastro_horarios as c
 where a.evento = b.codigo_horario and b.codigo_horario = c.codigo AND a.pis = '013025727232'
 and a.data = '2017/01/31' group by data, horario

horario	data	pis
07:30:00	31/01/2017 00:00:00	013025727232
12:00:00	31/01/2017 00:00:00	013025727232
13:00:00	31/01/2017 00:00:00	013025727232
17:00:00	31/01/2017 00:00:00	013025727232

* horario é o campo que parametrizei com as tabelas cadastro_horarios x cadastro_horario_itens, ou seja, sao horarios de trabalho pre definidos.

nao consegui ate o momento montar uma query que me apresentasse da seguinte forma:

horario	hora_ponto	data	pis
07:30:00	07:40:00	31/01/2017 00:00	13025727232
12:00:00	10:59:00	31/01/2017 00:00	13025727232
13:00:00	12:56:00	31/01/2017 00:00	13025727232
17:00:00	17:25:00	31/01/2017 00:00	13025727232

O problema que estou tendo é que ao trazer os registros com um join e as devidas clausulas where ele repete alguns valores devido a quantidade de registros na tabela cadsatro_horario_itens... nao sei se fui claro...

alguem pode me ajudar?

 

[Eziquiel 96]

Amigo, apesar de não ser expert em SQL, gostaria de tentar ajudá-lo. Para isso gostaria que você me passasse a estrutura de todas as tabelas envolvidas nessa consulta. Faça o seguinte: aplique um "SELECT * FROM TABELA" e poste aqui uns 4 resultados da pesquisa de cada tabela, juntamento com os nomes dos campos, como neste exemplo (só que todos os campos).

 

hora_ponto	data	pis
07:40:00	30/01/2017 00:00:00	013025727232
10:59:00	30/01/2017 00:00:00	013025727232
12:56:00	30/01/2017 00:00:00	013025727232
17:25:00	30/01/2017 00:00:00	013025727232

[WebCorplfs 0]

Obrigado pelo apoio, seguem informacoes solicitadas:

 

tabela conferencia_ponto

numcontrole	data	pis	hora_ponto	tipo	hora_escala
515	16/02/2017 00:00:00	013025727232	07:07:00	ENTRADA
516	16/02/2017 00:00:00	013025727232	10:54:00	SAIDA
517	16/02/2017 00:00:00	013025727232	12:29:00	ENTRADA
518	16/02/2017 00:00:00	013025727232	17:28:00	SAIDA

Tabela escala_individual:

 

codigo	grupoempresa	funcionario	evento	data	pis
58	1	PAULO - PAULO ESTEVAN VIEIRA	1	16/02/2017 00:00:00	013025727232

 

Tabela cadastro_horario_itens

codigo	codigo_horario	horario	tipohorario	flgintervalo
29	1	07:30:00	ENTRADA	0
30	1	12:00:00	SAIDA	1
31	1	13:00:00	ENTRADA	0
32	1	17:30:00	SAIDA	0

 

 

Resultado esperado:

Mostrar o horario que consta na conferencia (hora_ponto), hora da escala (horario), data e tipo (ENTRADA OU SAIDA).

 

Muito obrigado

 

 

[Eziquiel 96]

Pela minha análise, dessa maneira não vai funcionar.

É necessário uma tipagem diferente no campo "tipo". Exemplo: 

 

• INICIO TURNO
• SAIDA DESCANSO
• VOLTA DESCANSO
• FIM TURNO

Pode fazer isso?

[WebCorplfs 0]

Olha só, se trata de escala de motoristas de onibus, os horarios sao bem distintos, ou seja, tem dia que inicia o turno em um dia e finaliza no outro, bastante complicado... mas de qualquer forma vou analisar o que posso mudar na etrutura das tabelas e forma de importar os dados do relogio de ponto.

 

Até o momento eu fiz o seguinte, criei uma tela para definir a escala de horarios dos motoristas que sao definidas diariamente, ou seja, naquele dia quais horarios deverá fazer. Ex,: Dia 02/04/2017 vai entrar as 17:00 e sair as 21:00... retorna à meia noite (00:00) e trabalha ate as 04:00 am

 

Essas informacoes estou registrando da seguinte forma:

Criei uma tabela de horarios, esta simplesmente tem um codigo e uma descricao. Ex.: HORARIO 17:00 ÀS 00:00 (Essa é a descricao do horario cadstrado). Em outra tabela chamada horarios_itens eu armazeno os horarios nos campos: horario, tipo:

 

horario 17:00 tipo ENTRADA

horario 21:00 tipo SAIDA

horario 00:00 tipo ENTRADA

horario 04:00 tipo SAIDA

 

Agradeço muito a atenção, estou descrevendo aqui como estruturei esse inicio, a partir destas tabelas eu faço a importacao de um txt que vem do relogio de ponto, estes dados armazeno em uma tabela, a informaçao principal é o horario que marcou o ponto e que preciso cruzar com os horarios cadastrados na escala... cruzando estes dados eu devo informar se houve ou nao atraso, se deixou de marcar o ponto, se tera banco de horas, entre outras saidas que o cliente espera...

 

Quando postei neste forum estava querendo montar um select para ter tudo em uma so tabela, hora que entrou, hora que saiu, hora que retornou, e hora que saiu denovo. Acabei mudando a logica e consegui uma solucao, importo os dados, faço um insert com resultado do select. Em outra query pego os dados da escala e vou comparando um horario com o outro e tratando as informacoes. Esta dando alguns problemas mas estou acertando aqui... 

 

Se tiver alguma dica de como faria esta funcionalidade pode mandar, serei muito grato.

 

forte abraço e obrigado pela atencao.

 

 

 

 

[Eziquiel 96]

Já que é complicado alterar a estrutura, a minha dica é a seguinte:

Faça essa comparação em duas transações. Primeiro você faz um select que traz as informações do relógio de ponto e depois faz outro select que traz informações dos horários pré-definidos na tabela que você criou. Feito isso você faz o tratamento dos dados na sua aplicação, comparando os dois resultados.

 

Assim dá certo, sem precisar alterar nada no seu banco de dados. Entendeu?

[Eziquiel 96]

O problema aqui é trazer estas informações agrupadas num só SELECT, sem ter uma critério de ligação entre elas.

Quando você quer ir na sua tabela e buscar o horário pré-definido pra comparar com o horário registrado no ponto, você precisa de algo que liga estes dois registros. Vou te explicar:

 

No registro de ponto consta que PEDRO deu ENTRADA às 07:05 no dia 15/02/2017. Sabemos que o tipo de registro é ENTRADA.

Aí, quando vamos à tabela de horários da escala de PEDRO no dia 15, encontramos 4 registros. Com qual deles vamos comparar?

A resposta é: com o registro de ENTRADA. Mas aí tem um problema: tem o registro de ENTRADA às 07:00:00 e outro às 12:00:00, também de ENTRADA. Com qual deles a comparação será feita?

 

Essa questão vai gerar conflitos e resultados indevidos. 

 

Se não dá pra alterar o que está feito, creio que sua saída seja esta que mencionei anteriormente.

 

Abraço.

 

 

[Eziquiel 96]

Uma possível solução seria você determinar um limite de tolerância, por exemplo: 30 minutos. E este seria o elo de ligação para que a comparação seja feita.

Então, à partir dos horários da escala você varre tabela do registro de ponto e busca pelo registro onde o horário esteja num range de 30 minutos e 30 minutos depois do horário da escala. Isso pode ser feito. O problema aqui é o seguinte: se o horário do registro estiver fora desse range, ele entenderia que o ponto não foi marcado.

[WebCorplfs 0]

Bacana, realmente acredito que a melhor solucao é fazer dois selects e depois comparar... estou fazendo desta forma...

 

obrigado pelo apoio!

 

Abraço.

[Eziquiel 96]

Beleza então. Abraço.

[WebCorplfs 0]

Criei uma tolerancia mas utilizo para definir se vai gerar hora extra ou nao, da meneira que está estou tratando as quantidades de horas para determinar hora extra, banco de horas etc e na saida estou apresentando as marcacoes, quando nao marca o ponto gera uma inconsistencia para o usuario decidir o que fazer, descontar na folha de pagamento, justificar...etc

 

Obrigado amigo, qualquer coisa abro um novo post neste excelente canal. 

 

[Eziquiel 96]

Caso seja do seu interesse, eu criei algumas tabelas semelhantes estas suas para simular a sua necessidade.

Eu usei aqui o MySQL Workbench e criei esta dump pra você testar. 

Aqui funcionou, pois eu criei 4 tipo diferentes.

 

Depois que criar o banco e as tabelas da dump teste esta query:

SELECT HT.hora, CP.hora as hora_ponto, CP.data, CP.pis
FROM conferencia_ponto as CP
JOIN escala_individual as EI ON CP.pis = EI.pis
JOIN horarios_turno as HT ON HT.evento = CP.evento and HT.turno_id = EI.turno
WHERE CP.pis = 2630;

Alterne entre o pis "2630" e "2612", na consulta.

 

dump: 

Dump relogio ponto.sql

[WebCorplfs 0]

Certo, vou retomar na segunda feira com esse desenvolvimento, vou descansar um pouco, muito obrigado pelo apoio, na sequencia eu posto o resultado blz.

 

Abraço

 

[WebCorplfs 0]

Amigo, resolvi analisar a solucao apresentada e é excelente embora na tabela conferencia_ponto que recebe as informacoes importadas do relogio de ponto nao tenho uma referencia ao evento (ENTRADA e SAIDA), nao estou conseguindo associar um horario registrado no ponto ao evento entende?

 

Ou seja nesse join eu nao tenho CP.evento...

JOIN cadastro_horario_itens as HT ON HT.tipohorario = CP.evento and HT.turno_id = EI.turno

 

Nos posts anteriores ate mencionei esse campo mas nao esta associado corretamente como deveria, primeiro horario como ENTRADA, segundo horario como SAIDA e assim por diante...

 

Amanha vou rodar esse dump com certeza atenderia e minha necessidade mas acho que nao vou conseguir aplicar por esse motivo.

 

Vlw, Abraço

[WebCorplfs 0]

Revivendo este topico...

 

Olha só, ainda estou desenvolvendo esta aplicacao, resolvi o problema acima comparando horario por horario, horario de registro de ponto - horario escalado e obtive o total de minutos para banco de horas ou hora extras. sucesso. Obrigado...

 

Agora meu problema é outro, como saber qtas horas trabalhadas conforme um periodo de horas ex.:

 

horario	tipohorario	ponto	pis	data	banco_in	banco_out
07:30:00	ENTRADA	07:53:00	17038016901	16/03/2017	00:00:00	00:23:00
11:00:00	SAIDA	11:06:00	17038016901	16/03/2017	00:06:00	00:00:00
13:00:00	ENTRADA	12:12:00	17038016901	16/03/2017	00:48:00	00:00:00
17:30:00	SAIDA	17:55:00	17038016901	16/03/2017	00:25:00	00:00:00

 

Quantas horas de trabalho entre as 07:30 e 17:30 considerando as 2:00hs de intervalo. (escalado)

Quantas horas de trabalho que realmente fez entre as 07:53 e 17:55 considenrando o intervalo entre as 11:06 e 12:12 (trabalhou)

Se alguem tiver uma dica enquanto vou quebrando a cabeça aqui...

 

Obrigado

 

 

 

 

[WebCorplfs 0]

Compartilhando informação...

 

Consegui fazer da seguinte forma:

 

Peguei com o PHP o horario da primeira saida subtraindo a primeira entrada, o mesmo no segundo período, tanto na escala como no ponto. depois a seguinte query para trazer o que eu precisava.

 

 

select SUM( TIME_TO_SEC(totalperiodoponto) ) as totalponto
FROM cadastro_horario_itens_func where pis = '017038016901' and data ='2017/03/20'
and ponto <> '00:00:00' and tipohorario = 'SAIDA';

select SUM( TIME_TO_SEC(totalperiodoescala) ) as totalescala 
       FROM cadastro_horario_itens_func where pis = '017038016901' and data ='2017/03/20'
and tipohorario = 'SAIDA';