Como criar esta Stored Procedure?
19/01/2010
0
Então fiz assim a SP
CREATE OR ALTER PROCEDURE CALCULOORDEMSERV ( inicio time, fim time, codigoordem integer) returns ( servico varchar(30), horas integer, minutos integer, totminutos integer, totalhoras time, totserv numeric(15,4), resulttotserv numeric(15,2), resulttotcondut numeric(15,2), totalkm float) as declare variable hini integer; declare variable hfim integer; declare variable mini integer; declare variable mfim integer; declare variable somatothoras time; declare variable tiposerv varchar(20); declare variable resultvlrserv float; declare variable resultvlrtotal numeric(15,2); declare variable resultvlrcondutor numeric(15,2); declare variable resultpercserv float; declare variable rkms float; begin for select a.servico, a.vrlservico, a.vrltotal, a.vrlcondutor, b.percserv from ordemserv a inner join preserv b on a.servico = b.descserv where (a.servico = b.descserv) and (a.servico = 'HORAS') and (a.idordem = :codigoordem) into :tiposerv, :resultvlrserv, :resultvlrtotal, :resultvlrcondutor, :resultpercserv do begin servico = tiposerv; hini = substring( inicio from 1 for 2 ); mini = substring( inicio from 4 for 2 ); hfim = substring( fim from 1 for 2 ); mfim = substring( fim from 4 for 2 ); if ( hfim < hini ) then hfim = hfim + 24; horas = hfim - hini; if ( mfim < mini ) then begin mfim = mfim + 60; horas = horas - 1; end minutos = mfim - mini; if (totminutos <= 60 ) then totminutos = 60; else totminutos = minutos + horas * 60; --aqui me traz a quantidade de horas em minutos para calcular totserv = resultvlrserv / 60; --aqui estou dividindo por 60 o resultado da tabela de preço para calcular somatothoras = horas||':'||minutos; --aqui estou mostranto o total em horas if (somatothoras < '01:00:00') then --verifica se for menor que uma hora totalhoras = '01:00:00'; --então o total é de uma hora else totalhoras = somatothoras; if (somatothoras < '01:00:00') then resulttotserv = resultvlrserv; --se for inferior a uma hora ai aplica o valor do serviço else resulttotserv = (totserv * totminutos); --se não deve somar resulttotcondut = (resulttotserv * resultpercserv) / 100; --é a soma da comissao condutor suspend; end --segunda condição for select a.servico, a.vrlservico, a.vrltotal, a.vrlcondutor, b.percserv from ordemserv a inner join preserv b on a.servico = b.descserv where (a.servico = b.descserv) and (a.servico = 'CORRIDA') and (a.idordem = :codigoordem) into :tiposerv, :resultvlrserv, :resultvlrtotal, :resultvlrcondutor, :resultpercserv do begin servico = tiposerv; totserv = resultvlrserv; resulttotserv = totserv; --total do valor é o mesmo do total da corrida resulttotcondut = (resulttotserv * resultpercserv) / 100; --é a soma da comissao condutor end suspend; --terceira condição for select a.servico, a.vrlservico, a.vrltotal, a.vrlcondutor, a.kms, b.percserv from ordemserv a inner join preserv b on a.servico = b.descserv where (a.servico = b.descserv) and (a.servico = 'KM RODADO') and (a.idordem = :codigoordem) into :tiposerv, :resultvlrserv, :resultvlrtotal, :resultvlrcondutor, :rkms, :resultpercserv do begin servico = tiposerv; if (rkms > 0) then totalkm = rkms; totserv = resultvlrserv; resulttotserv = ( totserv * totalkm); --total do valor é o calculo do total do serviço X km rodado resulttotcondut = (resulttotserv * resultpercserv) / 100; --é a soma da comissao condutor end suspend; end
O resultado esta vindo cfe eu quero, mais eu acho que esqueci de fechar os END no bloco nos locais certos, para não trazer resultados em todos os 3 selects.
Acho que falta pouco para resolver isso, apenas o posicionamento dos END's no bloco do Strored Procedure eu creio que seja, mais se eu fecho no primeiro FOR SELECT da erro...
Adriano Dolce
Posts
19/01/2010
Thiago Santana
19/01/2010
Adriano Dolce
19/01/2010
Adriano Dolce
CREATE OR ALTER PROCEDURE CALCULOSERVUNION (
inicio time,
fim time,
codigoordem integer)
returns (
servico varchar(30),
horas integer,
minutos integer,
totminutos integer,
totalhoras time,
totserv numeric(15,4),
resulttotserv numeric(15,2),
resulttotcondut numeric(15,2),
totalkm float)
as
declare variable hini integer;
declare variable hfim integer;
declare variable mini integer;
declare variable mfim integer;
declare variable somatothoras time;
declare variable tiposerv varchar(20);
declare variable resultvlrserv float;
declare variable resultvlrtotal numeric(15,2);
declare variable resultvlrcondutor numeric(15,2);
declare variable resultpercserv float;
declare variable rkms float;
begin
for select a.servico, b.vlrserv, a.vrltotal, b.vlrmotoboy, a.kms, b.percserv
from servicos a
inner join preserv b on a.servico = b.descserv
where (a.servico = b.descserv) and (a.idordem = :codigoordem)
into :tiposerv, :resultvlrserv, :resultvlrtotal, :resultvlrcondutor, :rkms, :resultpercserv
do
begin
if (tiposerv = 'HORAS') then
begin
servico = tiposerv;
hini = substring( inicio from 1 for 2 );
mini = substring( inicio from 4 for 2 );
hfim = substring( fim from 1 for 2 );
mfim = substring( fim from 4 for 2 );
if ( hfim < hini ) then
hfim = hfim + 24;
horas = hfim - hini;
if ( mfim < mini ) then
begin
mfim = mfim + 60;
horas = horas - 1;
end
minutos = mfim - mini;
if (totminutos <= 60 ) then
totminutos = 60;
else
totminutos = minutos + horas * 60; --aqui me traz a quantidade de horas em minutos para calcular
totserv = resultvlrserv / 60; --aqui estou dividindo por 60 o resultado da tabela de preço para calcular
somatothoras = horas||':'||minutos; --aqui estou mostranto o total em horas
if (somatothoras < '01:00:00') then --verifica se for menor que uma hora
totalhoras = '01:00:00'; --então o total é de uma hora
else
totalhoras = somatothoras;
if (somatothoras < '01:00:00') then
resulttotserv = resultvlrserv; --se for inferior a uma hora ai aplica o valor do serviço
else
resulttotserv = (totserv * totminutos); --se não deve somar
resulttotcondut = (resulttotserv * resultpercserv) / 100; --é a soma da comissao condutor
end else
if (tiposerv = 'CORRIDA') then
begin
servico = tiposerv;
totserv = resultvlrserv;
resulttotserv = totserv; --total do valor é o mesmo do total da corrida
resulttotcondut = (resulttotserv * resultpercserv) / 100; --é a soma da comissao condutor
end else
if (tiposerv = 'KM RODADO') then
begin
servico = tiposerv;
if (rkms > 0) then
totalkm = rkms;
totserv = resultvlrserv;
resulttotserv = ( totserv * totalkm); --total do valor é o calculo do total do serviço X km rodado
resulttotcondut = (resulttotserv * resultpercserv) / 100; --é a soma da comissao condutor
end
--- Aqui será executada o calculo cfe a condição do IF
if (tiposerv = 'HORAS') then
begin
execute statement 'select from rdb$database' into servico;
end else
if (tiposerv = 'CORRIDA') then
begin
execute statement 'select from rdb$database' into servico;
end else
if (tiposerv = 'KM RODADO') then
begin
execute statement 'select from rdb$database' into servico;
end
suspend;
end
end
Mais não funcionou, a SP chegou a compilar mais na hora de executar da erro de sintaxe.
Creio que seja no EXECUTE STATEMENT o erro
Alguem poderia me ajudar?
20/01/2010
Emerson Nascimento
diga o que você pretende com essa SP. como ela deve funcionar. o que você deverá passar como parâmetro e qual o retorno desejado.
20/01/2010
Adriano Dolce
A tabela ordem de servico é esta aqui
CREATE TABLE ORDEMSERV (
IDORDEM INTEGER,
IDCLIENTES INTEGER,
IDEMPRESAS INTEGER,
IDCONDUTOR INTEGER,
NRORDEMSERV VARCHAR(20),
EMISSAO DATE,
HORAEMISSAO TIME,
DESPACHO DATE,
HORADESPACHO TIME,
SOLICITANTE VARCHAR(35) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
NOMECLIE VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
DTBAIXA DATE,
PENDCONCL VARCHAR(20),
BORDERO VARCHAR(18),
ENDERCLIE VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
TELCLIE VARCHAR(14),
BAIRROCLIE VARCHAR(50),
CIDADECLIE VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
UFCLIE VARCHAR(2),
IDCONDPGTO INTEGER,
CONDPGTO VARCHAR(40),
NOMECONDUTOR VARCHAR(50),
CENTROCUSTO VARCHAR(50),
ENDSAIDA VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
NUMSAIDA VARCHAR(10),
BAIRROSAIDA VARCHAR(50),
COMPLSAIDA VARCHAR(50),
CEPSAIDA VARCHAR(10),
CIDADESAIDA VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
UFSAIDA VARCHAR(2),
TEL1SAIDA VARCHAR(14),
TEL2SAIDA VARCHAR(14),
CELSAIDA VARCHAR(14),
FAXSAIDA VARCHAR(14),
PONTREFSAIDA VARCHAR(35),
CONTATOSAIDA VARCHAR(35),
ENDDESTINO VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
NUMDESTINO VARCHAR(10),
BAIRRODESTINO VARCHAR(50),
COMPLDESTINO VARCHAR(50),
CEPDESTINO VARCHAR(10),
CIDADEDESTINO VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
UFDESTINO VARCHAR(2),
TEL1DESTINO VARCHAR(14),
TEL2DESTINO VARCHAR(14),
CELDESTINO VARCHAR(14),
FAXDESTINO VARCHAR(14),
PONTREFDESTINO VARCHAR(35),
CONTATODESTINO VARCHAR(35),
OBS BLOB SUB_TYPE 1 SEGMENT SIZE 80,
ACESSO CHAR(1),
USUARIO VARCHAR(50),
DATAACESSO TIMESTAMP,
"BLOCK" CHAR(1) default 'B'
);
a tabela servico é esta aqui
CREATE TABLE SERVICOS (
IDSERVICO INTEGER,
IDORDEM INTEGER,
NRORDEMSERV VARCHAR(20),
IDEMPRESAS INTEGER,
TIPO VARCHAR(20),
RETORNO CHAR(1),
SERVICO VARCHAR(50),
HORAINI TIME,
HORAFIN TIME,
HORATOTAL TIME,
KMS NUMERIC(4,2),
DESCRSERV VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
VRLSERVICO NUMERIC(15,2),
VRLTOTAL NUMERIC(15,2),
VRLCONDUTOR NUMERIC(15,2),
ACESSO CHAR(1),
USUARIO VARCHAR(50),
DATAACESSO TIMESTAMP,
"BLOCK" CHAR(1) default 'B'
);
e a tabela de preco é esta aqui
CREATE TABLE PRESERV (
ID INTEGER,
IDEMPRESAS INTEGER,
IDCLIENTES INTEGER,
IDPRECO INTEGER,
ATIVAR CHAR(1),
CODIGOSERV INTEGER,
DESCSERV VARCHAR(50) CHARACTER SET WIN1252 COLLATE WIN_PTBR,
TIPOSERV VARCHAR(10),
PERCSERV NUMERIC(10,2),
VLRSERV NUMERIC(15,2),
VLRMOTOBOY NUMERIC(15,2)
);
Eu quero algo que ai iniciar um novo ordem de serviço e ir colocando o tipo de serviço (HORAS, CORRIDA, KM RODADO) a SP calcule em tempo de projeto e ir mostrando na grade cfe a figura anexa neste post
Exemplo selecionei HORAS então na SP verifique se é HORAS e executa o calculo, o mesmo procedimento com os outros que vou selecionar.
O retorno na SP seria este mesmo que ja esta na SP que eu fiz, ele executa o calculo como preciso, mais não consegui fazer a verificação nos blocos.
Se precisar de mais informações amigo, é só dizer...
Obrigado pela ajuda.
Fiz via aplicação em códigos delphi, mais não queria tratar no evento onexit do dbedit, por isso acho que talvez isso se torna melhor executar numa sp ou trigger...
O código do delphi que fiz é este (e esta funcionando corretamente)
procedure Tfrmcadordem.vlrservicosExit(Sender: TObject);
var
vhoraini : TDateTime;
vhorafin : TDateTime;
vtotalhoras : TDateTime;
resultado : string;
valorServico : real;
begin
vlrservicos.Color := clGradientInactiveCaption;
//calculando o total dos serviços por hora
if dsprecoservlocal.DataSet.FieldByName('descserv').AsString = 'HORAS' then
begin
vhoraini := StrToTime(horaini.Text);
vhorafin := StrToTime(horafin.Text);
//tem que fazer esta verificação entre dias para não ocorrer retrocesso na
//soma das horas
resultado := TimeToStr(StrToTime('23:59:59') - StrToTime(horaini.Text)
+ StrToTime('00:00:01') + StrTotime(horafin.Text));
vtotalhoras := StrToTime(resultado); //jogando o resultado para uma variavel TDateTime
valorServico := dsservicoslocal.dataset.FieldByName('vrlservico').AsCurrency; //pegando o valor do servico
//passar o resultado da hora diretamente no dataset para mostrar em tempo real na grade
if resultado <= '01:00' then
begin
dsservicoslocal.dataset.FieldByName('horatotal').asstring := '01:00:00'; //tothoras.text := '01:00:00'
dsservicoslocal.dataset.FieldByName('vrltotal').AsCurrency := (valorServico );//se for inferior a uma hora conta o total de uma hora
end else
begin
dsservicoslocal.dataset.FieldByName('horatotal').asstring := resultado; //tothoras.text := resultado;
vtotalhoras := HoraParaMin(TimeToStr(vtotalhoras)); //transformando horas em minuto
valorServico := (valorServico / 60); //dividindo por 60 para calcular por hora
//valor total do serviços
dsservicoslocal.dataset.FieldByName('vrltotal').AsCurrency := (vtotalhoras * valorServico );//o dataset recebendo o calculo do total de horas * valorservico
if dsprecoservlocal.dataset.FieldByName('tiposerv').AsString <> 'P' then
begin
Pt_MessageDlg('Atenção! O valor do Condutor deve ser editado.',mtInformation,[mbOK],0);
exit;
end else
begin
//valor do condutor
dsservicoslocal.dataset.FieldByName('vrlcondutor').AsCurrency :=
( (dsservicoslocal.dataset.FieldByName('vrltotal').AsCurrency * dsprecoservlocal.DataSet.fieldbyname('percserv').asfloat) / 100); //calcula pelo percentual da tabela de precos
end;
end;
end else if dsprecoservlocal.DataSet.FieldByName('descserv').AsString = 'CORRIDA' then
begin
//calculando o total do serviço por corrida
dsservicoslocal.dataset.FieldByName('vrltotal').AsCurrency := (dsservicoslocal.dataset.FieldByName('vrlservico').AsCurrency);
if dsprecoservlocal.dataset.FieldByName('tiposerv').AsString <> 'P' then
begin
Pt_MessageDlg('Atenção! O valor do Condutor deve ser editado.',mtInformation,[mbOK],0);
exit;
end else
begin
//valor do condutor
dsservicoslocal.dataset.FieldByName('vrlcondutor').AsCurrency :=
( (dsservicoslocal.dataset.FieldByName('vrlservico').AsCurrency * dsprecoservlocal.DataSet.fieldbyname('percserv').asfloat) / 100); //calcula pelo percentual da tabela de precos
end;
end else if dsprecoservlocal.DataSet.FieldByName('descserv').AsString = 'KM RODADO' then
begin
//calculando o total do serviço pro km rodade
dsservicoslocal.dataset.FieldByName('vrltotal').AsCurrency :=
(dsservicoslocal.dataset.FieldByName('kms').AsFloat * dsservicoslocal.dataset.FieldByName('vrlservico').AsCurrency);
if dsprecoservlocal.dataset.FieldByName('tiposerv').AsString <> 'P' then
begin
Pt_MessageDlg('Atenção! O valor do Condutor deve ser editado.',mtInformation,[mbOK],0);
exit;
end else
begin
//valor do condutor
dsservicoslocal.dataset.FieldByName('vrlcondutor').AsCurrency :=
( (dsservicoslocal.dataset.FieldByName('vrltotal').AsCurrency * dsprecoservlocal.DataSet.fieldbyname('percserv').asfloat) / 100); //calcula pelo percentual da tabela de precos
end;
end;
end;
Se der pra fazer pelo sp ou trigger e atualizar assim que for editando no dbedit agradeço, e se possível der para atualizar se o usuario optar por fazer diretamente na grade
22/01/2010
Emerson Nascimento
- "Eu quero algo que ai iniciar um novo ordem de serviço e ir colocando o tipo de serviço (HORAS, CORRIDA, KM RODADO) a SP calcule em tempo de projeto e ir mostrando na grade cfe a figura anexa neste post"
IMPOSSÍVEL. só será possível fazer isso em tempo de EXECUÇÃO!
- você quer que a stored procedure efetue esses cálculos ANTES de gravar no banco de dados?
IMPOSSÍVEL!!!
22/01/2010
Adriano Dolce
25/01/2010
Emerson Nascimento
Clique aqui para fazer login e interagir na Comunidade :)