estrutura de SQL

[Ronaldo Faria Storck Eler 2]

Olá pessoal,

 

Estou desenvolvendo uma aplicação de controla a quantidade e os valores possíveis de indenização que uma pessoa pode ter:

 

- o usuário preenche:

* data

* quantidade de indenização

* selecione o setor de trabalho (selectbox)

 

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.

 

A solução que estou trabalhando:

- criei o campo intQtdeIdz para guardar a quantidade

- um campo chamado sDias, para guardar os dias,

 

nesse campo (sDias), se a pessoa solicitou 3 indenizações, eu pego a data da solicitação (que é sempre o dia da primeira indenização), faço um for...next e gero as 3 datas, o dia da solitação e mais duas datas, sempre adicionando um dia, e fica mais ou menos assim (29/06/2013*30/06/2013*01/07/2013)

 

- depois guardo isso em um outra tabela, onde cada data é uma linha, para que, sempre que a pessoa marcar uma data e a quantidade, eu possa chegar se ela já fez um solicitação que compreenda aquela data...

 

o problema:

- há um limite de indenizações por mês

- quando vou fazer um novo cadastro, checo se o usuário já atingiu o máximo no mês requisitado, mas como posso fazer para checar também nas datas correlatas? pois ela nao está na tabela de indenização como um campo data, e sim texto, e na tabela onde guardo individualmente as datas, não tem todos os dados que preciso?

- a minha preocupação é que se a pessoa solicitar 3 indenizações no dia 30/06 (compreendendo também 01/07 e 02/07) se no futuro ele solicitar uma nova no mês 07 em não consiga contar corretamente as indenizações, porque a data 01/07 e 02/07 não foi colocada explicitamente na tabela de indenizações e sim da tabela auxiliar?

 

Tabela de indenização

CREATE TABLE `indenizacoes` (
  `dID` int(11) NOT NULL AUTO_INCREMENT,
  `dSetorID` int(11) NOT NULL,
  `dFuncionarioID` int(11) NOT NULL,
  `dViagemData` date NOT NULL,
  `dValor` double NOT NULL,
  `dQtdeIdz` tinyint(1) DEFAULT '0',
  `sDias` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`dID`)
)

tabela auxiliar onde coloco as datas individualizadas

CREATE TABLE `indenizacaoDatas` (
  `ddID` int(11) NOT NULL AUTO_INCREMENT,
  `ddIdzID` int(11) NOT NULL,
  `ddViagemData` date NOT NULL,
  `ddDataAtivo` tinyint(1) NOT NULL DEFAULT '1',
  `ddFuncionarioID` int(11) NOT NULL,
  PRIMARY KEY (`ddID`)

Verifico a quantidade (via ajax) dessa forma:

SELECT COUNT(did) as totaldiarias, SUM(dQtdeIdz) AS inteiras, dSetorID FROM indenizacao 
WHERE dFuncionarioID = '36' and dAtivo = '1' and MONTH(dViagemData) = '6'

 

como fazer para colocar 3 datas dentro dessa consulta, uma vez que dViagemData está em uma tabela, e é uma data isolada, e todas as datas só estão na tabela auxiliar?

 

Se que um join pode resolver isso, mas innet, left, right join?

 

Se alguém conseguiu entender o problema e puder contribuir... agradeço...

 

sds

 

[Ronaldo Faria Storck Eler 2]

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

[hufersil 145]

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.

[Ronaldo Faria Storck Eler 2]

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/2013*30/06/2013*01/07/2013

 

por isso é varchar...

[Motta 645]

Deveria ser uma tabela a principio.

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

[hufersil 145]

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

[Ronaldo Faria Storck Eler 2]

 

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?

[hufersil 145]

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?

[Ronaldo Faria Storck Eler 2]

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-29*2013-06-30*2013-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-29*2013-06-30*2013-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

[Motta 645]

- 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

[hufersil 145]

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!

[Ronaldo Faria Storck Eler 2]

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...

 

...