-- FUNCTION: public.fnc_calcula_peso_ferragem_total_pedido(integer, integer)

-- DROP FUNCTION IF EXISTS public.fnc_calcula_peso_ferragem_total_pedido(integer, integer);

CREATE OR REPLACE FUNCTION public.fnc_calcula_peso_ferragem_total_pedido(
	var_filial integer,
	var_orcamento integer)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$

DECLARE

  var_peso numeric;
  
BEGIN
-- Funcao que retorna o valor total do peso do pedido descontando o que já está em producao
    SELECT  
	COALESCE((SUM(valor)),0) AS valor INTO var_peso

	FROM (

	 (  SELECT 

		  vnd_orcamento_item.sequencia,	 
                (vnd_orcamento_item.quantidade -
		  COALESCE((SELECT SUM(prd_ordem_producao.quantidade_pecas)
                                                               FROM prd_ordem_producao_x_pedido
                                                                 INNER JOIN prd_ordem_producao
                                                                    ON prd_ordem_producao.filial = prd_ordem_producao_x_pedido.filial_ordem_producao
                                                                   AND prd_ordem_producao.ordem_producao = prd_ordem_producao_x_pedido.ordem_producao
                                                              WHERE prd_ordem_producao_x_pedido.filial_pedido = vnd_orcamento_item.filial_orc
                                                                AND prd_ordem_producao_x_pedido.pedido = vnd_orcamento_item.orcamento
                                                                AND prd_ordem_producao_x_pedido.pedido_seq_item = vnd_orcamento_item.sequencia),0)) *
		 COALESCE(CASE WHEN vnd_orcamento_item_especificacao.largura = 0
		      THEN (TRUNC((((((vnd_orcamento_item_especificacao.comprimento +
				     vnd_orcamento_item_especificacao.altura) * 2) +
				     vnd_orcamento_item_especificacao.acrescimo) *
			     pro_especificacao_vergalhao.fator_peso) *
				vnd_orcamento_item_especificacao_detalhe.quantidade),6))

		      ELSE (TRUNC((((vnd_orcamento_item_especificacao.comprimento +
			     vnd_orcamento_item_especificacao.acrescimo) *
			     pro_especificacao_vergalhao.fator_peso) * 
				vnd_orcamento_item_especificacao_detalhe.quantidade),6)) 

	      END ,0.000000) AS valor
	     
	     FROM vnd_orcamento_item
	LEFT JOIN vnd_orcamento_item_especificacao
	       ON vnd_orcamento_item_especificacao.filial         = vnd_orcamento_item.filial_orc
	      AND vnd_orcamento_item_especificacao.orcamento      = vnd_orcamento_item.orcamento
	      AND vnd_orcamento_item_especificacao.sequencia_item = vnd_orcamento_item.sequencia
	LEFT JOIN vnd_orcamento_item_especificacao_detalhe
	       ON vnd_orcamento_item_especificacao_detalhe.filial             = vnd_orcamento_item.filial_orc
	      AND vnd_orcamento_item_especificacao_detalhe.orcamento          = vnd_orcamento_item.orcamento
	      AND vnd_orcamento_item_especificacao_detalhe.sequencia_item     = vnd_orcamento_item.sequencia
	      AND vnd_orcamento_item_especificacao_detalhe.sequencia_dimensao = vnd_orcamento_item_especificacao.sequencia_dimensao
	LEFT JOIN pro_especificacao_vergalhao
	       ON pro_especificacao_vergalhao.codigo    = vnd_orcamento_item_especificacao_detalhe.cod_especificacao
	    WHERE vnd_orcamento_item.filial_orc = var_filial
	      AND vnd_orcamento_item.orcamento  = var_orcamento
	      AND pro_especificacao_vergalhao.produto_ref IS NOT NULL
	GROUP BY vnd_orcamento_item.sequencia,
		 vnd_orcamento_item.quantidade,
		 vnd_orcamento_item.filial_orc,
		 vnd_orcamento_item.orcamento,
		 vnd_orcamento_item_especificacao.largura,
		 vnd_orcamento_item_especificacao.comprimento,
		 vnd_orcamento_item_especificacao.altura,
		 vnd_orcamento_item_especificacao.acrescimo,
		 pro_especificacao_vergalhao.fator_peso,
		 vnd_orcamento_item_especificacao_detalhe.quantidade ,
		 vnd_orcamento_item_especificacao.acrescimo_estribo	      

	)

	UNION ALL

	 (  SELECT 
                vnd_orcamento_item.sequencia,	 
                (vnd_orcamento_item.quantidade -
		  COALESCE((SELECT SUM(prd_ordem_producao.quantidade_pecas)
                                                               FROM prd_ordem_producao_x_pedido
                                                                 INNER JOIN prd_ordem_producao
                                                                    ON prd_ordem_producao.filial = prd_ordem_producao_x_pedido.filial_ordem_producao
                                                                   AND prd_ordem_producao.ordem_producao = prd_ordem_producao_x_pedido.ordem_producao
                                                              WHERE prd_ordem_producao_x_pedido.filial_pedido = vnd_orcamento_item.filial_orc
                                                                AND prd_ordem_producao_x_pedido.pedido = vnd_orcamento_item.orcamento
                                                                AND prd_ordem_producao_x_pedido.pedido_seq_item = vnd_orcamento_item.sequencia),0)) *

		 COALESCE(CASE WHEN vnd_orcamento_item_especificacao.largura = 0  
		 
		      THEN TRUNC((((SELECT SUM(det1.quantidade) 
			     FROM vnd_orcamento_item_especificacao_detalhe AS det1
			    WHERE det1.filial             = vnd_orcamento_item.filial_orc
			      AND det1.orcamento          = vnd_orcamento_item.orcamento
			      AND det1.sequencia_item     = vnd_orcamento_item.sequencia
			      AND det1.sequencia_dimensao = 1)
			    *
			  (SELECT SUM(det2.quantidade) 
			     FROM vnd_orcamento_item_especificacao_detalhe AS det2
			    WHERE det2.filial             = vnd_orcamento_item.filial_orc
			      AND det2.orcamento          = vnd_orcamento_item.orcamento
			      AND det2.sequencia_item     = vnd_orcamento_item.sequencia
			      AND det2.sequencia_dimensao = 2)) * pro_especificacao_vergalhao.fator_peso),6)
		      
		      ELSE (TRUNC((((vnd_orcamento_item_especificacao.comprimento / 
				     CASE WHEN vnd_orcamento_item_especificacao.espacamento_estribo = 0 THEN 1 ELSE vnd_orcamento_item_especificacao.espacamento_estribo END) *
				     vnd_orcamento_item_especificacao_detalhe.quantidade) *
				     pro_especificacao_vergalhao.fator_peso),6))

	      END ,0.000000) AS valor

	     
	     FROM vnd_orcamento_item
	LEFT JOIN vnd_orcamento_item_especificacao
	       ON vnd_orcamento_item_especificacao.filial         = vnd_orcamento_item.filial_orc
	      AND vnd_orcamento_item_especificacao.orcamento      = vnd_orcamento_item.orcamento
	      AND vnd_orcamento_item_especificacao.sequencia_item = vnd_orcamento_item.sequencia
	LEFT JOIN vnd_orcamento_item_especificacao_detalhe
	       ON vnd_orcamento_item_especificacao_detalhe.filial             = vnd_orcamento_item.filial_orc
	      AND vnd_orcamento_item_especificacao_detalhe.orcamento          = vnd_orcamento_item.orcamento
	      AND vnd_orcamento_item_especificacao_detalhe.sequencia_item     = vnd_orcamento_item.sequencia
	      AND vnd_orcamento_item_especificacao_detalhe.sequencia_dimensao = vnd_orcamento_item_especificacao.sequencia_dimensao
	LEFT JOIN par_producao
	       ON par_producao.filial = vnd_orcamento_item.filial_orc   
	LEFT JOIN pro_especificacao_vergalhao
	       ON pro_especificacao_vergalhao.produto_ref    = par_producao.produto_associado_producao
	    
	    WHERE vnd_orcamento_item.filial_orc = var_filial
	      AND vnd_orcamento_item.orcamento  = var_orcamento
	      AND par_producao.produto_associado_producao IS NOT NULL
	GROUP BY vnd_orcamento_item.sequencia,
		 vnd_orcamento_item.quantidade,
		 vnd_orcamento_item.filial_orc,
		 vnd_orcamento_item.orcamento,
		 vnd_orcamento_item_especificacao.largura,
		 vnd_orcamento_item_especificacao.comprimento,
		 vnd_orcamento_item_especificacao.altura,
		 vnd_orcamento_item_especificacao.acrescimo,
		 pro_especificacao_vergalhao.fator_peso,
		 vnd_orcamento_item_especificacao_detalhe.quantidade,
		 vnd_orcamento_item_especificacao.espacamento_estribo,
		 vnd_orcamento_item_especificacao.acrescimo_estribo   	      

	)

	UNION ALL

	(   SELECT 

                vnd_orcamento_item.sequencia,	 
                (vnd_orcamento_item.quantidade -
		  COALESCE((SELECT SUM(prd_ordem_producao.quantidade_pecas)
                                                               FROM prd_ordem_producao_x_pedido
                                                                 INNER JOIN prd_ordem_producao
                                                                    ON prd_ordem_producao.filial = prd_ordem_producao_x_pedido.filial_ordem_producao
                                                                   AND prd_ordem_producao.ordem_producao = prd_ordem_producao_x_pedido.ordem_producao
                                                              WHERE prd_ordem_producao_x_pedido.filial_pedido = vnd_orcamento_item.filial_orc
                                                                AND prd_ordem_producao_x_pedido.pedido = vnd_orcamento_item.orcamento
                                                                AND prd_ordem_producao_x_pedido.pedido_seq_item = vnd_orcamento_item.sequencia),0)) *
		  COALESCE(CASE WHEN vnd_orcamento_item_especificacao.diametro_estribo = 0 

	     THEN (TRUNC((((vnd_orcamento_item_especificacao.comprimento / 
			    CASE WHEN vnd_orcamento_item_especificacao.espacamento_estribo = 0 THEN 1 ELSE vnd_orcamento_item_especificacao.espacamento_estribo END) *
			  ((vnd_orcamento_item_especificacao.largura * 2) + 
			   (vnd_orcamento_item_especificacao.altura * 2) + 
			    vnd_orcamento_item_especificacao.acrescimo_estribo)) *
			    pro_especificacao_vergalhao.fator_peso),6)) 

	     ELSE (TRUNC((((vnd_orcamento_item_especificacao.comprimento / 
			    CASE WHEN vnd_orcamento_item_especificacao.espacamento_estribo = 0 THEN 1 ELSE vnd_orcamento_item_especificacao.espacamento_estribo END) *
			  ((vnd_orcamento_item_especificacao.diametro_estribo * 3.14159) + 
			    vnd_orcamento_item_especificacao.acrescimo_estribo)) *
			    pro_especificacao_vergalhao.fator_peso),6)) 

	 END ,0.000000) AS valor                                 

	     

	     FROM vnd_orcamento_item

	LEFT JOIN vnd_orcamento_item_especificacao
	       ON vnd_orcamento_item_especificacao.filial         = vnd_orcamento_item.filial_orc
	      AND vnd_orcamento_item_especificacao.orcamento      = vnd_orcamento_item.orcamento
	      AND vnd_orcamento_item_especificacao.sequencia_item = vnd_orcamento_item.sequencia

	LEFT JOIN pro_especificacao_vergalhao
	       ON pro_especificacao_vergalhao.codigo    = vnd_orcamento_item_especificacao.cod_especificacao_estribo

	    WHERE vnd_orcamento_item.filial_orc = var_filial
	      AND vnd_orcamento_item.orcamento  = var_orcamento
	      AND pro_especificacao_vergalhao.produto_ref IS NOT NULL
	GROUP BY vnd_orcamento_item.sequencia,
		 vnd_orcamento_item.quantidade,
		 vnd_orcamento_item.filial_orc,
		 vnd_orcamento_item.orcamento,
		 vnd_orcamento_item_especificacao.largura,
		 vnd_orcamento_item_especificacao.comprimento,
		 vnd_orcamento_item_especificacao.altura,
		 vnd_orcamento_item_especificacao.acrescimo,
		 pro_especificacao_vergalhao.fator_peso,
		 vnd_orcamento_item_especificacao.diametro_estribo,
		 vnd_orcamento_item_especificacao.espacamento_estribo,
		 vnd_orcamento_item_especificacao.acrescimo_estribo 
	)

	) AS tabaux

	WHERE valor > 0;
  
  RETURN var_peso;

END;

$BODY$;

ALTER FUNCTION public.fnc_calcula_peso_ferragem_total_pedido(integer, integer)
    OWNER TO postgres;