F�rum Ajuda na cria��o de Store procedure #407314

02/09/2011

Galera eu estou precisando criar uma procedure para calcular uma pontua��o de produtos vendidos. Ex: Eu tenho uma tabela de produtos onde tenho o valor da pontua��o do produto, e uma tabela de itens vendidos onde tenho o total de produtos vendidos e uma tabela de devolu��es onde eu tenho os produtos devolvidos. A procedure tem que fazer o seguinte: Lista os produtos vendidos e devolvidos e subitrair eles e depois multiplicar o resultado pela pontua��o do produto(Vendas-Devolu��es*pontua��o). Isso de acordo com um determinado periodo e vendedor. Eu iniciei um select que vou colocar abaixo onde ele me lista os produtos vendidos e falta unir com as devolu��es e fazer o calculo.
Tabelas

CREATE TABLE CX_VENDEDOR ( CXVEN_ID DM_PK /* DM_PK = INTEGER NOT NULL PRIMARY KEY */, VEN_ID INTEGER, DT_CAIXA DATE, VEND_ID DM_FK /* DM_FK = INTEGER */, CXTOTAL DM_VALOR /* DM_VALOR = NUMERIC(15,2) */, SITUACAO VARCHAR(15));
CREATE TABLE CX_ITENS ( CXVEN_ID DM_PK /* DM_PK = INTEGER NOT NULL PRIMARY KEY*/, PROD_ID DM_PK /* DM_PK = INTEGER NOT NULL */, PROD_DESCRICAO DM_NOME /* DM_NOME = VARCHAR(50) */, PROD_PRECO DM_VALOR /* DM_VALOR = NUMERIC(15,2) */, PROD_QTDE NUMERIC(5,3), PROD_TOTAL DM_VALOR /* DM_VALOR = NUMERIC(15,2) */, CARG_QTDE DM_QTDE /* DM_QTDE = NUMERIC(5,3) */, RETOR_QTDE DM_QTDE /* DM_QTDE = NUMERIC(5,3) */, VEN_QTDE DM_QTDE /* DM_QTDE = NUMERIC(5,3) */, CARG_QTDE_DS DM_QTDE /* DM_QTDE = NUMERIC(5,3) */, REPOS_QTDE DM_QTDE /* DM_QTDE = NUMERIC(5,3) */, VEND_ID DM_FK /* DM_FK = INTEGER */, DATA DM_DATA /* DM_DATA = DATE */);
CREATE TABLE PRODUTOS ( PROD_ID INTEGER NOT NULL PRIMARY KEY, PROD_DESCRICAO VARCHAR(50) COLLATE PT_BR, PONTUACAO DM_QTDE /* DM_QTDE = NUMERIC(5,3) */);

CREATE TABLE DEVOLUCAO ( DEV_ID INTEGER NOT NULL PRIMARY KEY, SITUACAO_ID INTEGER NOT NULL, DEV_DATA_INC DATE, DEV_DATA_ALT DATE, DEV_DATA_BAIXA DATE, DEV_OBS VARCHAR(60) COLLATE PT_BR, DEV_TOTAL NUMERIC(15,2), USU_ID INTEGER, EMPRESA_ID DM_FK /* DM_FK = INTEGER */, VEND_ID INTEGER, CLI_ID INTEGER, OPERACAO VARCHAR(1), CANCELADO VARCHAR(1));
CREATE TABLE DEVOLUCAO_ITENS ( DEV_ID INTEGER NOT NULL PRIMARY KEY, PROD_ID INTEGER NOT NULL, IDV_QTDE DM_QTDE /* DM_QTDE = NUMERIC(5,3) */, IDV_PRECO NUMERIC(15,2), IDV_TOTAL NUMERIC(15,2), IDV_DESCRICAO VARCHAR(30) COLLATE PT_BR, IDV_ESTOQUE DM_QTDE /* DM_QTDE = NUMERIC(5,3) */);
Select
select cx_itens.prod_id, cx_itens.prod_descricao, sum(cx_itens.ven_qtde) as ven_qtde, sum(cx_itens.ven_qtde) * produtos.pontuacao as pontosfrom vendedor inner join cx_vendedor on (vendedor.vend_id = cx_vendedor.vend_id) inner join cx_itens on (cx_vendedor.cxven_id = cx_itens.cxven_id) inner join produtos on (cx_itens.prod_id = produtos.prod_id)where cx_vendedor.dt_caixa between :dt_ini and :dt_fim and cx_vendedor.vend_id = :vendedor and cx_vendedor.situacao = 'FECHADO'group by cx_itens.prod_id, cx_itens.prod_descricao, produtos.pontuacao

Se algu�m puder ajudar eu agrade�o.Abra�o.

Sidney Abreu

Curtir t�pico

+ 0

Responder

Posts

05/09/2011

Emerson Nascimento

teste o c�digo abaixo. estou sem FB aqui na m�quina. fiz tudo no bloco de notas e � bem poss�vel vc encontrar algum erro de sintaxe, mas creio que deve estar bem pr�ximo do que voc� precisa.

create procedure PontosVend(
    dt_ini date,
    dt_fim date)
returns (
        vend_id integer,
        prod_id integer,
        prod_descricao varchar(50),
        total double,
        pontuacao integer)
as
declare variable pontos double;
declare variable vendido double;
declare variable devolvido double;
begin
for select distinct
          vend.vend_id, p.prod_id, p.prod_descricao, p.pontuacao
      from vendedor vend
         left join cx_vendedor cx on (cx.vend_id = vend.vend_id
             and cx.dt_caixa between :dt_ini and :dt_fim
             and cx.situacao = 'FECHADO')
         left join cx_itens on (cx_itens.cxven_id = cx.cxven_id)
         left join devolucao dv on (dv.vend_id = vend.vend_id
             and dv.dev_data_baixa between :dt_ini and :dt_fim)
         left join devolucao_itens dv_it on (dv_it.dev_id = dv.dev_id)
         inner join produtos p on (p.prod_id = cx_itens.prod_id
             or p.prod_id = dv_it.prod_id)
      into
          :vend_id, :prod_id, :prod_descricao, :pontos
do
begin
    /* obtem a quantidade total VENDIDA por produto do vendedor */
    select
        coalesce(sum(cx_itens.ven_qtde),0)
    from cx_vendedor cx
       inner join cx_itens on (cx_itens.cxven_id = cx.cxven_id
          and cx_itens.prod_id = :prod_id)
    where
        cx.vend_id = :vend_id
        and cx.dt_caixa between :dt_ini and :dt_fim
        and cx.situacao = 'FECHADO'
    into
        :vendido;

    /* obtem a quantidade total DEVOLVIDA por produto do vendedor */
    select
        coalesce(sum(dv_it.idv_qtde),0)
    from devolucao dv
       inner join devolucao_itens dv_it on (dv_it.dev_id = dv.dev_id
          and dv_it.prod_id = :prod_id)
    where
        dv.vend_id = :vend_id
        and dv.dev_data_baixa between :dt_ini and :dt_fim
        and coalesce(dv.cancelado,'N') <> 'S'
    into
        :devolvido;

    /* faz os calculos necessarios */
    total = (vendido - devolvido);
    pontuacao = total * pontos;

    /* 'devolve' o resultado */
    suspend;
end
end

e, para chamar, voc� usa:
select * from PontosVend( '01.01.2011', '31.01.2011' ) -- traz todos os vendedores do periodo
select * from PontosVend( '01.01.2011', '31.01.2011' ) where vend_id = 1 -- traz somente do vendedor que o id = 1
select * from PontosVend( '01.01.2011', '31.01.2011' ) order by vend_id, pontuacao desc -- traz tudo ordenado por vendedor + pontuacao decrescente

ou seja: voc� usa a stored procedure como uma tabela normal do banco de dados, exceto pelo fato de passar os par�metros com a data inicial e final.

Responder

Gostei + 0