Bom vamos começar, o exemplo e uma seqüência de comandos sql com a sintaxe do sql server 2000, mas e perfeitamente adaptável a outros bd's, é complexo na sua formação, mas muito simples de entender, não vou entrar em detalhes quanto aos comandos(select, create table), pela web há uma infinidade de artigos e apostilas gratuitas, mas vou explicar o uso em conjunto deles que torna a solução robusta, e o rápido o retorno de informação.
Como pode ser usado em minha aplicação?
Exemplo: tenho uma janela que ao momento em que é criada, gera os componentes de acesso a dados, e vários datasets pra vária tabelas diferentes, mas todas co-relacionadas em determinado nível. Veja o diagrama:
(tabela pai) (tabela filha 1)
EQUIPE -------- LISTA DE SERVIÇOS
\ (tabela filha 2)
\-------- EMPREGADOS
Registros co-relacionados
EQUIPES ---- CODIGO, DATA INICIO, DATA FIM
LISTA DE SERVIÇOS ---- VALOR FUNÇÃO
A, VALOR FUNÇÃO B, PERCENTUAL DE CONCLUSÃO
EMPREGADOS ---- MATRICULA, SALARIO, ACRESCIMO, DATA INICIO DATA FIM (sendo que a data final pode ser modificada pra menos, empregado faltou, etc.)
Ou seja uma equipe, que tem uma data inicial e final para
terminar um conjunto de serviços, com valores variáveis para serem pagos aos funcionários de acordo com a
função de cada um, e que por sua vez, o pagamento varia de acordo com os dias trabalhados neste serviço.
O
valor resultante pode vir em um campo calculado(Delphi), pois os eventos dos
datasets seguem uma sequencia.
procedure TFrm.qeEQUIPES_PESSOASCalcFields( DataSet: TDataSet);
begin
if
DsFormaEquipes.DataSet.Active then
if
DataSet.Active then
DataSet.FieldByName('calcVL_TOT_RECEBER').AsCurrency
:=
qeSuaQuery.FieldByName('VALOR_TOTAL').AsCurrency;
end;
Vamos a instrução Sql:
USE BANCO_01 -- banco de
dados que será acessado
/*
verifica se a tabela
existe a tabela e no caso de não cria a tabela (poderia ser uma tabela
temporária, mas
neste caso irei usar uma criada por mim e dar um drop nela
no final. Lembre se que e um cadeia de repetição
por isso a verificação se existe ou não.
*/
IF (select COUNT(*) from sysobjects
where xtype= 'U'
and NAME = 'EQP_PES_TOTAL') = 0
BEGIN
CREATE TABLE EQP_PES_TOTAL(
COD_EMPREG VARCHAR(8) NOT NULL, -- referencia empregado
QUANT_X_FUNCAO FLOAT NULL, -- quantidade do serviço multiplicado
pelo valor descrito por função
PERC_SERVICO FLOAT NULL, -- percentual de conclusão do serviço
QUANT_FUNC_FUNC INT NULL, -- quantidade de empregados com a
mesma função
SALARIO_DIV_30 MONEY NULL, -- valor por dia trabalhado
DIAS_TRAB_PACOTE INT NULL,
-- diferença de dias
trabalhado no pacote de serviços
ACRESCIMO MONEY NULL, -- bonificação ou decréscimo de
valor
VALOR_TOTAL MONEY NULL) -- resultado final
END
-- Declaração das variáveis que serão usadas ao longo do processo
DECLARE @MSG_ERRO VARCHAR(100), -- mensagem que poderá ser gerada em determinada situação
@COD_EMPREG VARCHAR(8),
@EQUIPE INT, --
referencia da equipe
@QUANT_EMPREGADOS INT,
@DIAS_TRAB_PACOTE INT,
@SER_REFERENCIA INT, --
referencia do serviço
@VALOR_FUNCAO MONEY,
@VALOR_TOTAL MONEY,
@VALOR_PARCIAL_A MONEY,
@VALOR_PARCIAL_B MONEY,
@QTD_SERVICO FLOAT,
@SOMA_QTD_SERV FLOAT,
@PERC_SERV FLOAT,
@SALARIO_DIV_30 FLOAT,
@ACRESCIMO FLOAT,
@FUNCAO CHAR(1),
@DATA_EQUIPE_INI DATETIME,
@DATA_EQUIPE_FIM DATETIME
SET @COD_EMPREG = '1234'
SET @EQUIPE = 7
SET @SER_REFERENCIA = 7
SET @DATA_EQUIPE_INI = '26/11/2008'
SET @DATA_EQUIPE_FIM = '29/11/2008'
--RECEBE A QUANTIDADE DE DIAS ENTRE AS DATAS
SET @DIAS_TRAB_PACOTE = (
SELECT DATEDIFF(DAY,DATA_INI,
DATA_FIM)
FROM EQUIPES_PESSOAS P
JOIN EQUIPES E ON E.REFERENCIA = P.EQUIPE -- junção
com a tabela pai
AND P.EQUIPE = @EQUIPE
AND P.MATRICULA = @COD_EMPREG)
-- loop e a partir daqui
DECLARE CUR_SERVICO CURSOR LOCAL
FAST_FORWARD FOR -- declaração do cursor que será usado
SELECT SER_REFERENCIA FROM SGPO_EQUIPES_SERVICOS WHERE SER_EQUIPE =
@EQUIPE
OPEN CUR_SERVICO
FETCH NEXT FROM CUR_SERVICO INTO
@SER_REFERENCIA
WHILE @@FETCH_STATUS = 0
BEGIN
-- muito interessante esta
forma de set pois podemos com um único select trazer vários resultados
SET SELECT
@VALOR_FUNCAO =
CASE
WHEN
(FUN_NIVEL = 'A') THEN SER_FUNCAO_A
WHEN
(FUN_NIVEL = 'B') THEN SER_FUNCAO_B
WHEN
(FUN_NIVEL = 'C') THEN SER_FUNCAO_C
WHEN
(FUN_NIVEL IS NULL) THEN SER_FUNCAO_C
END,
@FUNCAO =
CASE
WHEN
(FUN_NIVEL = 'A') THEN 'A'
WHEN
(FUN_NIVEL = 'B') THEN 'B'
WHEN
(FUN_NIVEL = 'C') THEN 'C'
WHEN
(FUN_NIVEL IS NULL) THEN 'C'
END,
-- aqui eu inicio os cálculos de vários campos de acordo com as variantes
@SALARIO_DIV_30 = EMP_SALARIO / 30,
@QTD_SERVICO = SER_QUANTIDADE *
@VALOR_FUNCAO,
@PERC_SERV = SER_PERCENTUAL / 100
FROM EMPREGADOS
INNER JOIN FUNCOES ON FUN_REFERENCIA
= EMP_FUNCAO
INNER JOIN SGPO_EQUIPES_SERVICOS ON
SER_EQUIPE = @EQUIPE AND SER_REFERENCIA =
@SER_REFERENCIA
WHERE EMP_STATUS = '1'
AND EMP_REGISTRO = @COD_EMPREG
-- select para identificar quantos funcionários existem na equipe com a função determinada
SET @QUANT_EMPREGADOS = (
SELECT COUNT(FUN_NIVEL) FROM
EMPREGADOS
JOIN SGPO_EQUIPES_PESSOAS ON
PES_MATRICULA = EMP_REGISTRO AND
PES_EQUIPE = @EQUIPE
JOIN FUNCOES ON FUN_REFERENCIA =
EMP_FUNCAO -- veja que aqui eu vou buscar a
referencia em outra tabela fora do grupo referido do relacionamento citado no
início
AND FUN_NIVEL = @FUNCAO
WHERE EMP_STATUS = '1')
-- aqui eu realizo vários
testes pra não retornar em minha aplicação um exception com valor nulo por
causa da
divisão(nenhum número pode ser
dividido por zero). O que me retorna e a mensagem identificando onde houve a
falha.
IF (@QTD_SERVICO = 0)
SET @MSG_ERRO = 'Erro encontrado, registro ' +
'QTD.SERVIÇO está com valor = 0'
ELSE
BEGIN
IF (@PERC_SERV = 0)
SET @MSG_ERRO = 'Erro
encontrado, registro ' +
'PRC.SERVIÇO está com valor = 0'
ELSE
BEGIN
IF (@QUANT_EMPREGADOS = 0)
SET @MSG_ERRO = 'Erro encontrado, registro ' +
'QTD.EMPREGADOS está com valor =
0'
ELSE
BEGIN
SET @SOMA_QTD_SERV = (
SELECT @QTD_SERVICO * @PERC_SERV / @QUANT_EMPREGADOS )
--verifica a existência de valores na seleção e se sim insere os valores coletados
IF OBJECT_ID('EQP_PES_TOTAL') IS NOT NULL -- outra forma de verificar se
a tabela existe no BD
BEGIN
-- sendo afirmativo e todos os campos onde
haverá divisão diferente de zero, e hora de inserir na
tabela temporária.
INSERT INTO EQP_PES_TOTAL(
COD_EMPREG, QUANT_X_FUNCAO, PERC_SERVICO,
QUANT_FUNC_FUNC, SALARIO_DIV_30,
DIAS_TRAB_PACOTE, ACRESCIMO
)VALUES( @COD_EMPREG, @VALOR_FUNCAO,
@PERC_SERV,
@QUANT_EMPREGADOS,
@SALARIO_DIV_30,@DIAS_TRAB_PACOTE, @ACRESCIMO)
SET SELECT @VALOR_PARCIAL_A =
QUANT_X_FUNCAO * PERC_SERVICO /
QUANT_FUNC_FUNC FROM EQP_PES_TOTAL
-- aqui eu calculo o valor total
SET SELECT @VALOR_PARCIAL_B =
SALARIO_DIV_30 * DIAS_TRAB_PACOTE FROM
EQP_PES_TOTAL
SET SELECT @VALOR_TOTAL =
@VALOR_PARCIAL_A -
@VALOR_PARCIAL_B + ACRESCIMO FROM EQP_PES_TOTAL
END
-- aqui eu atualizo o valor total de acordo com o que foi
gerado no calculo anterior pra evitar que seja
nulo o valor de update
IF @VALOR_TOTAL <> 0.00
UPDATE EQP_PES_TOTAL SET
VALOR_TOTAL = @VALOR_TOTAL
ELSE UPDATE EQP_PES_TOTAL SET VALOR_TOTAL = 0.00
END
END
END
-- fim do loop
FETCH NEXT FROM CUR_SERVICO INTO @SER_REFERENCIA
END
-- fecho o cursor
CLOSE CUR_SERVICO
-- desaloco da memória
DEALLOCATE CUR_SERVICO
-- caso não retorne o valor
desejado pode substituir o select abaixo por este para retornar onde faltou o valor.
--SELECT @MSG_ERRO
SELECT SUM(VALOR_TOTAL) AS
VALOR_TOTAL FROM EQP_PES_TOTAL WHERE
COD_EMPREG = @COD_EMPREG
Bom pessoal, tudo isso para retornar um único valor, esta e uma situação que se repetirá de acordo com quantas equipes tiver, e cada uma dela se repetindo para cada funcionário da equipe, com quantos serviços houver cadastrados durante o período para concluir, em um empreendimento de grande porte como uma obra de construção, que terá centenas de funcionários, se isto for gerado por loop no lado do aplicativo, pode demorar muito tempo, ou pode ater falhar quando o exponencial for alto, mas sendo gerado no lado do servidor de dados, tem um retorno extremamente rápido. espero que tenham gostado.