estrutura de SQL

Bem pessoal, quem não consegue escrever "sessenta", que faça dois cheques de "trinta"...

Pois bem,

consegui resolver uma parte:

• quando o usuário seleciona um periodo que compreende dois meses, consigo chegar a quantidade de indenizações em cada um deles, no entanto ainda é uma falha, pois:

pois em 29/06 eu solicitei 04 indenizações

e no dia 05/07 eu solicitei 01 indenização

com o que consegui até agora, fica assim:

mes 06 - 04 (29 e 30/06, 01 e 02/07)

mes 07 -01 (05/07)

quando na verdade eu deveria ter:

mes -06 - 02 indenizacoes (29 e 30/06)

mes 07 - 03 indenizações (01, 02 e 05//07)

outra coisa que não consigo entender,

o meu campo na base de dados é TINYINT(1), porque quando pego ele via recordset, tenho que fazer usar o cint(campo)?

estou usando:

If Not rs.Eof Then
'Bloco I
else
' Bloco 2
end if
mas mesmo sendo um recordset vazio, ele está entrando no Bloco I, tem algum motivo especial para que isso ocorra? se é vazio não há dados, aí ele entra no Bloco I e fica me dizendo que o usei incorretamente o cint em um valor NULL...

rs

Algum motivo específico pelo qual um campo "data" está sendo armazenado como "varchar"?

Isto prejudica e muito na hora de fazer as operações com data que você precisa.

esse campo data é o sDias, que na verdade, pode ter uma combinação de datas, quando o usuário, seleciona por exemplo, 3 indenizações, eu guardo a data da solicitação mais dois dias dentro deste campo, para referência

29/06/201330/06/201301/07/2013

por isso é varchar...

Deveria ser uma tabela a principio.

Em tese uma trigger poderia validar a duplicidade da indenização.

Como citado pelo Motta, estes dados deveriam estar em uma tabela correlacionada, para que você pudesse, de forma mais fácil e prática, consultar os dados por periodo, e evitaria assim "recursos técnicos computacionais" (a.k.a - gambiarra).

Mas vamos nos concentrar no problema. Você colocou no tópico:

>
Quando ele escolhe o setor, eu chamo, via ajax um arquivo que me diz:

• se a pessoa já solicitou indenizacao naquela data
• se ela já atingiu o máximo de indenizações possíveis naquele mês
• o total de indenizações

Até agora funcionava perfeitamente, pois a pessoa só podia solicitar uma indenização de cada vez, no entanto, depois de reavaliarem, o usuário vai poder solicitar, por exemplo, 3 indenizações de cada vez.

O que você quis dizer com "3 indenizações de cada vez"?

Pergunto: cada uma das 3 indenizações poderiam ser, individualmente, um registro na tabela de "indenizacoes"? Assim, ao invés de gravar 3 datas diferentes no mesmo campo, você teria uma linha para cada data, assim:

mysql> select * from indenizacoes;
+-----+----------+----------------+-------------+--------+----------+------------+
| dID | dSetorID | dFuncionarioID | dViagemData | dValor | dQtdeIdz | sDias |
+-----+----------+----------------+-------------+--------+----------+------------+
| 1 | 1 | 1 | 2013-06-01 | 60 | 0 | 2013-06-27 |
| 2 | 1 | 1 | 2013-06-01 | 60 | 0 | 2013-06-29 |
| 3 | 1 | 1 | 2013-06-01 | 60 | 0 | 2013-07-01 |
+-----+----------+----------------+-------------+--------+----------+------------+
3 rows in set (0.00 sec)
E depois, trocaria para que ele verificasse as indenizações feitas para aquele funcionário nos últimos 30 dias:

SELECT COUNT(*) as total
 FROM indenizacoes
 WHERE dFuncionarioID = 1
 AND dViagemData BETWEEN CURRENT_DATE - INTERVAL 30 DAY AND CURRENT_DATE;

Fiz a condição de 30 dias baseado no seu exemplo de consulta em outro post, mas como estou supondo que trocaria o formato do campo sDias, seria possível também verificar em cima deste campo:

SELECT COUNT(*) as total
 FROM indenizacoes
 WHERE dFuncionarioID = 1
 AND sDias BETWEEN CURRENT_DATE - INTERVAL 30 DAY AND CURRENT_DATE;

Assim, você tem a quantidade indenizações feitas para aquele funcionário nos últimos 30 dias.

@braços

>

O que você quis dizer com "3 indenizações de cada vez"?

Pergunto: cada uma das 3 indenizações poderiam ser, individualmente, um registro na tabela de "indenizacoes"? Assim, ao invés de gravar 3 datas diferentes no mesmo campo, você teria uma linha para cada data, assim:

mysql> select * from indenizacoes;
+-----+----------+----------------+-------------+--------+----------+------------+
| dID | dSetorID | dFuncionarioID | dViagemData | dValor | dQtdeIdz | sDias |
+-----+----------+----------------+-------------+--------+----------+------------+
| 1 | 1 | 1 | 2013-06-01 | 60 | 0 | 2013-06-27 |
| 2 | 1 | 1 | 2013-06-01 | 60 | 0 | 2013-06-29 |
| 3 | 1 | 1 | 2013-06-01 | 60 | 0 | 2013-07-01 |
+-----+----------+----------------+-------------+--------+----------+------------+
3 rows in set (0.00 sec)

Bem, com base no seu exemplo, o que estou tentando evitar é justamente a repetição de dados, por isso estou com uma tabela auxiliar para guardar as datas e o sDias, fica apenas como uma forma de evitar buscar nessa tabela auxiliar quando eu só precisar exibir os dados, por exemplo, em um relatório.

O que tenho hoje é isso:

mysql> select * from indenizacoes;
+-----+----------+----------------+-------------+--------+----------+------------------------+
| dID | dSetorID | dFuncionarioID | dViagemData | dValor | dQtdeIdz | sDias |
+-----+----------+----------------+-------------+--------+----------+------------------------+
| 1 | 1 | 1 | 2013-06-01 | 60 | 1 | 2013-06-01* |
| 2 | 3 | 19 | 2013-06-14 | 160 | 2 | 2013-06-14*2013-06-15 |
| 3 | 3 | 27 | 2013-06-22 | 245 | 2 | 2013-06-22*2013-06-23 |
+-----+----------+----------------+-------------+--------+----------+------------------------+
3 rows in set (0.00 sec)
Cada Solicitação contém toda a informação necessária, para (no meu modo de pensar) evitar criar mais linhas, apenas para alterar um campo e repetir os demais (não sei se estou certo no pensamento).

No relatório de solicitação, eu apenas pego o campo sDias, uso um split, e digo a qual período corresponde aquela solicitação. A primeira data do sDias é sempre a data da solicitação, pois ele não pode escolher datas diversas...

Então, faço uso da tabela auxiliar para armazenar as datas e relacioná-las com o dID e o Funcionário:

mysql> select * from indenizacaoDatas;
+------+---------+-----------------+--------------+---------+
| ddID | ddIdzID | ddFuncionarioID | ddViagemData | ddAtivo |
+------+---------+-----------------+--------------+---------+
| 1 | 1 | 1 | 2013-06-01 | 1 |
| 2 | 2 | 19 | 2013-06-14 | 1 |
| 3 | 2 | 19 | 2013-06-25 | 1 |
+------+---------+-----------------+--------------+---------+
3 rows in set (0.00 sec)
Assim, quando o usuário vai cadastrar uma nova indenização, eu consigo chegar se na data solicitada, ou, de acordo com a quantidade, se ele já tem indenização para aquele dia. Se tiver eu bloqueio o pedido...

...

Consegui fazer uma POG aqui, que acho que vai dar pra quebrar um galho:

SELECT sum(dQtdeIdz) as di, MONTH(dViagemData) as mes FROM indenizacoes WHERE dFuncionarioID = '19' and dAtivo = '1' and (MONTH(dViagemData) = '" & MONTH(dDataSelecionada)) &"' or MONTH(dViagemData) = '" & MONTH(dDataSelecionada2) &"') group by mes
Estou pegando o sDias e fazendo uma verificação com o FOR...NEXT e vendo se as datas estão todas no mesmo mês, caso afirmativo, busco naquele mes, caso contrário monto a sql acima, adicionando os meses que contemplam a quantidade de indenização solicitada.

tenho um retorno assim:

+----+-----+
| di | mes |
+----+-----+
| 12 | 6 |
+----+-----+
| 4 | 7 |
+----+-----+

Bom assim, vou caminhando, se não conseguir resolver (espero que eles não me digam que a pessoa pode solicitar 1/2 meia indenização também), eu vou bloquear a solicitação de períodos que possam englobar 2 meses, acho que simplificará mais... rs

o que acham?

então você está no caminho certo com a tabela auxiliar. e nem precisaria mais do campo sDias (no meu ponto de vista).

Mas o seu problema é

• saber quantas solicitações ele fez em um período?
• ou saber quantas solicitações ele recebeu em um período?

Por que, pelo que entendi, posso ter feito uma solicitação no final de junho e receber 2 parcelas de 3 no mês de julho.

Coloque um exemplo, de como fica em suas tabelas, quando solicito 3 indenizações no mesmo dia (para o mesmo funcionário).

@braços

Mais uma coisa: este sDias são as datas das parcelas que ele vai receber uma indenização?

Bem, as solicitações funcionam assim:

Eu fiz (ou vou fazer) uma viagem, para participar de um congresso, digamos, em Belo Horizonte, e por lá vou ficar por 3 dias, a partir do dia 29/06.

Então, vou no formulário e peço uma indenização (que tem um valor fixo) para as despesas que terei por lá, com a data do 1o. dia que ficarei fora: 29/06

Sendo assim, eu terei

• em sDIas: 2013-06-292013-06-302013-07-01
• na tabela auxiliar, 3 registros, 1 para cada data

As regras são:

• o usuário não pode solicitar mais que 10 indenizações completas por mês ou 20 parciais (a parcial, é quando a pessoa viaja após o meio-dia)
• o usuário não pode acumular indenizações em um mesmo dia (se ele, nessa solicitação compreende o dia 01/07, ele não pode ter outra no mesmo dia)

Assim, com a POG do post anterior, eu já consegui pegar quantas em cada mês ele tem em cada mês, e me resolveu uma parte, o problema agora, é que no caso da parcial (que a data de saída para a viagem) é superior ao meio dia, assim:

3 dias fora (2013-06-292013-06-302013-07-01), mas, saindo no primeiro dia após 'as 12h, se tornam, na verdade 02 indenizações completas 92013-06-30*2013-07-01) e uma indenização parcial (2013-06-29)...

Qual a melhor forma de controla isso, na tabela auxiliar eu poderia (deveria), talvez colocar um campo, tipo, true/false ou tinyint(1) para marcar quem saiu após o meio-dia?

Não sei se me fiz entender...

sds

- o usuário não pode solicitar mais que 10 indenizações completas por mês ou 20 parciais (a parcial, é quando a pessoa viaja após o meio-dia

Uma trigger de insert verifica se já não existem estas 10 ou 20 (melhor criar um parametro ou tabela, vai que a regra muda) para o usuário é gera um erro se for o caso, acho que o mysql exige outra trigger para o update (mudar a data por exemplo)

- o usuário não pode acumular indenizações em um mesmo dia (se ele, nessa solicitação compreende o dia 01/07, ele não pode ter outra no mesmo dia

Uma constraint de unique key (usuario,data) não permitirá isto.

Check também se a data não tem hora embutida (check constraint)

Qual a melhor forma de controla isso, na tabela auxiliar eu poderia (deveria), talvez colocar um campo, tipo, true/false ou tinyint(1) para marcar quem saiu após o meio-dia?

Talvez um campo Parcial (s/n) com a devida validação

Então eu faria algo mais ou menos assim:

Tabela de indenizações:

+----------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------+------+-----+---------+----------------+
| codindenizacao | int(11) | NO | PRI | NULL | auto_increment |
| codsetor | int(11) | NO | | NULL | |
| codfuncionario | int(11) | NO | | NULL | |
| dtaviagem | date | NO | MUL | NULL | |
| valor_total | float | NO | | NULL | |
+----------------+---------+------+-----+---------+----------------+
Veja que tirei o sDias de lá, porque você não precisa dele.

Mais abaixo, vou mostrar como recuperar esta informação em uma consulta.

Tabela de data, que chamei de indenizacao_parcela:

mysql> describe indenizacao_parcela;
+-----------------------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+------------+------+-----+---------+----------------+
| codindenizacaoparcela | int(11) | NO | PRI | NULL | auto_increment |
| codindenizacao | int(11) | NO | MUL | NULL | |
| dtavcto | date | NO | MUL | NULL | |
| valor | float | NO | | NULL | |
| integral | tinyint(1) | YES | | 1 | |
+-----------------------+------------+------+-----+---------+----------------+
Agora, fica ultra mais simples fazer as consultas que você precisa.

Por exemplo, para consultar o número de indenizações integrais e parciais numa mesma consulta para um funcionário, separando por mês/ano:

 SELECT CONCAT(MONTH(p.dtavcto), '/', YEAR(p.dtavcto)) as mes,
 SUM(IF(p.integral = 1, 1, 0)) as integrais,
 SUM(IF(p.integral != 1, 1, 0)) as parciais
 FROM indenizacao_parcela p
 JOIN indenizacao i ON p.codindenizacao = i.codindenizacao
 WHERE i.codfuncionario = 1
 AND i.codsetor = 1

GROUP BY 1 Para poder filtrar por um período, seria só colocar na clausula WHERE a sua condição, em cima do campo dtavcto (data de vencimento).

Agora, para saber se já tem alguma indenização num dia, também é fácil.

Neste exemplo, estou pesquisando se ele tem uma indenização em periodo:

SELECT COUNT(*)
FROM indenizacao_parcela p, indenizacao i
WHERE i.codindenizacao = p.codindenizacao
AND i.codfuncionario = 1
AND i.codsetor = 1
AND p.dtavcto BETWEEN '$dataInicial' AND '$dataFinal'
E, como havia lhe dito, se eu quiser pegar todas as parcelas dentro de uma indenização, em uma linha só, group_concat resolve ;)

SELECT i.*,
 GROUP_CONCAT(DATE_FORMAT(p.dtavcto,'%d/%m/%Y') ORDER BY p.dtavcto SEPARATOR '*') as "sDias"

FROM indenizacao i JOIN indenizacao_parcela p ON p.codindenizacao = i.codindenizacao WHERE i.codfuncionario = 1 AND i.codsetor = 1 GROUP BY i.codindenizacao @braços e fique com Deus!

Pessoal, vou fazer uns testes aqui e depois retorno o resultado... sinceramente, nem sabia que poderia "fazer um split" já na consulta do MySQL... tenho muito a aprender ainda...

...