Untitled

-- Function: public.f_analise_proposta(character varying, character varying, double precision)
 -- DROP FUNCTION public.f_analise_proposta(character varying, character varying, double precision);

CREATE OR REPLACE FUNCTION public.f_analise_proposta(p_propost CHARACTER varying, p_operaca CHARACTER varying DEFAULT 'H'::CHARACTER varying, p_valsuge double precision DEFAULT 0) RETURNS text AS $BODY$
DECLARE

  DECLARE
  V_RESULT text;
  I_EMPRESA   INTEGER;
  S_TIPOVEI   VARCHAR(10);
  S_FORMULA   VARCHAR(4000);
  S_CAMPO     VARCHAR(4000);
  S_FORMRES   VARCHAR(4000);
  S_TABLE     VARCHAR(4000);
  I           INTEGER;
  F_ABRIU     BOOLEAN;
  S_NOMECAM   VARCHAR(400);
  S_VALOR     VARCHAR(4000);
  V_JSON      text;
  CONTADOR    numeric;
  S_BGCOLOR   VARCHAR(50);
  S_FONTCOLOR VARCHAR(50);
  C_TOTALACES FLOAT;
  V_JSONR     text;
  S_COMILUC   VARCHAR(50);
  CUR         RECORD;
  s_sqlform  text;
  ssql  text;
BEGIN

  IF ( P_OPERACA IS NULL ) THEN
    P_OPERACA := 'H';
  END IF;
  IF ( P_VALSUGE IS NULL ) THEN
    P_VALSUGE :=0;
  END IF;
  CONTADOR := 0;
  V_JSON   := '';
  V_JSONR  := '';
  V_RESULT := '<html>';
  IF COALESCE(P_OPERACA, ' ') <> 'R' THEN
    V_RESULT := V_RESULT ||
                '<style>table th {background: #3F51B5;color: white;}' ||
                'table caption {background: #3F51B5;color: white; border: padding: 1px; 1px solid black;}' ||
                'table{border: 0px black;border-collapse: collapse;}table td,th {padding: 4px;border: 0.1px solid black;}' ||
                '</style>';
  END IF;
  V_RESULT := V_RESULT ||
              '<body><table width="100%"><tr><th>Linha</th><th>Identificacao</th><th>Valor</th></tr>';
  if (substr(P_PROPOST, 1, 1) = 'P') then
    select VACM_EMITPRO_1,
           case VVEI_TIPOVEI_1
             when 'N' then
               'Novos'
             when 'U' then
               'Usados'
             else
               'VDI'
               end
        into I_EMPRESA, S_TIPOVEI
    from SGVACM
           left join SGVVEI on VVEI_CODIVEI_1 = VACM_CODIVEI_1
    where VACM_PROPOST_1 = P_PROPOST;
  else
    select VLOC_EMITENT_1,
           case VVEI_TIPOVEI_1
             when 'N' then
               'Novos'
             when 'U' then
               'Usados'
             else
               'VDI'
               end
        into I_EMPRESA, S_TIPOVEI
    from SGVVEI
           left join SGVLOC on VLOC_CODILOC_1 = VVEI_LOCORIG_1
    where VVEI_CODIVEI_1 = P_PROPOST;
  end if;
  FOR CUR in execute 'select *
from SGVRES
 where VRES_EMITENT_1 = ' || cast(I_EMPRESA as varchar) ||
                     ' and VRES_TIPOVEI_1 = ''' || S_TIPOVEI || '''' || ' order by vres_emitent_1, vres_tipovei_1, vres_nrolinh_1'
loop

    I         := 1;
    S_formres := '';
    F_ABRIU   := FALSE;
    s_campo   := '';
    while (I <= LENGTH(CUR.VRES_FORMULA_1)) loop
      IF SUBSTR(CUR.VRES_FORMULA_1, I, 1) = '>' THEN
        S_TABLE := REPLACE(TRIM(REPLACE(LOWER(SUBSTR(S_CAMPO,
                                                     1,
                                                     STRPOS(S_CAMPO, ',') - 1)) ||
                                        '.xfd',
                                        '.arq',
                                        '')),
                           'sgvsim',
                           'sgvacm');

        BEGIN
          SELECT REPLACE(replace(VX103_XFD_FLD_NAME, '-', '_'),
                         'vsim_',
                         'vacm_')
              INTO S_NOMECAM
          from SINVX103
          where VX103_XFD_FILE = lower( s_table )
            and VX103_XFD_FLD_STRING Like
                '%' || TRIM(SUBSTR(s_campo, STRPOS(S_CAMPO, ',') + 1, 5)) || '%';

          EXCEPTION
          WHEN OTHERS THEN
            S_FORMRES := SQLCODE || ' - ' || SUBSTR(SQLERRM, 1, 200);
        END;
        IF (COALESCE(P_VALSUGE, 0) > 0) THEN
          S_NOMECAM := case when UPPER(S_NOMECAM) IN ('VACM_VALSUGE_1', 'VACM_VALNOTA_1') THEN CAST(REPLACE(CAST(P_VALSUGE AS VARCHAR), ',', '.') AS VARCHAR)
                       when UPPER(S_NOMECAM) IN ('vacm_valrevi_1') THEN 'vvei_valrevi_1'
                       when UPPER(S_NOMECAM) IN ('vacm_valrepa_1') THEN 'COALESCE( ( SELECT SUM(vrep_valores_1) FROM sgvrep WHERE vrep_codivei_1 = ''' || P_PROPOST || ''' ), 0 )' else S_NOMECAM end;
        END IF;
        S_formres := s_formres || ' COALESCE( ' || coalesce(S_nomecam, ' ') ||
                     ', 0 )';
        S_CAMPO := '';
        f_abriu := false;
      END IF;
      IF SUBSTR(CUR.VRES_FORMULA_1, I, 1) = '<' THEN
        F_ABRIU   := TRUE;
        S_FORMULA := '';
      END IF;
      IF (NOT F_ABRIU) and
         (SUBSTR(CUR.VRES_FORMULA_1, I, 1) not in ('<', '>')) then
        S_FORmres := S_FORmres || SUBSTR(CUR.VRES_FORMULA_1, I, 1);
      END IF;
      IF (F_ABRIU) and (SUBSTR(CUR.VRES_FORMULA_1, I, 1) not in ('<', '>')) then
        S_campo := s_campo || SUBSTR(CUR.VRES_FORMULA_1, I, 1);
      END IF;
      I := I + 1;
    end loop;
    BEGIN
      IF (S_TIPOVEI = 'VDI') THEN
        s_formres := replace(s_formres, 'vvei_valaqui_1', 'vint_prectot_1');
      end if;
      IF (S_TIPOVEI = 'VDI' or substr(P_PROPOST, 1, 1) <> 'P') THEN
        s_formres := replace(s_formres, 'VVEI_VALBONU_1', 'VINT_VALBON1_1');
        s_formres := replace(s_formres, 'VVEI_VALBON2_1', 'VINT_VALBON2_1');
        s_formres := replace(s_formres, 'VVEI_VALBON3_1', 'VINT_VALBON3_1');
      END IF;
      IF (substr(P_PROPOST, 1, 1) = 'P') then
        ssql := 'select to_char( ' || s_formres || ', ''999G999G990D00'' )'||
                ' AS VALOR from sgvacm ' ||
                ' left join SGVVEI on VVEI_CODIVEI_1 = VACM_CODIVEI_1' ||
                ' left join SGVINT on trim( to_char( VINT_CONTROL_1, ''00000000'' ) ) = VACM_CODIVEI_1' ||
                ' where vacm_propost_1 = ''' || P_PROPOST || '''';
        begin
          execute ssql
          into s_valor;
          EXCEPTION
          WHEN OTHERS THEN
             s_valor := 'erro';
        end;

      else
	raise notice 'Formula 1 % ', s_formres;
        s_formres := replace(s_formres,
                             'VACM_BONUFAB_1',
                             '( VVEI_VALBONU_1 + VVEI_VALBON2_1 + VVEI_VALBON3_1 )');
	raise notice 'Formula 2 % ', s_formres;
        s_formres := replace(s_formres, 'VACM_VALREVI_1', 'VCFG_VALREVI_1');
	raise notice 'Formula 3 % ', s_formres;
	s_formres := replace(s_formres, 'VACM_VALFRET_1', 'VCFG_PLATAFO_1');
	raise notice 'Formula 4 % ', s_formres;
        s_formres := replace(s_formres, 'VACM_PARTIDN_1', 'VCFG_PARTIDN_1');
        raise notice 'Formula 5 % ', s_formres;
        s_formres := replace(s_formres,
                             'VACM_VALCOMI_1',
                             '( ' || REPLACE(cast( P_VALSUGE as varchar(15)), ',', '.') ||
                             ' * CASE VVEI_TIPOVEI_1 WHEN ''N'' then VCFG_PERCOMI_1 else VCFU_PERCOMI_1 END / 100 )');
	raise notice 'Formula 5 % ', s_formres;
        s_formres := replace(s_formres, 'VACM_COMILUC_1', coalesce( S_COMILUC, ' ' ));
	raise notice 'Formula 6 % ', s_formres;
        s_formres := replace(s_formres,
                             'VACM_VALICMS_1',
                             '( ( ' || REPLACE(cast( P_VALSUGE as varchar(15) ), ',', '.') ||
                             ' * 10 / 100 ) * VCFU_ALIQSAI_1 / 100 )');
	raise notice 'Formula 7 % ', s_formres;
        s_formres := replace(s_formres,
                             'VACM_PISCONF_1',
                             '( ( ' || REPLACE( cast( P_VALSUGE as varchar(15) ), ',', '.') ||
                             ' - VVEI_VALAQUI_1 ) * VCFG_PISCONF_1 / 100 )');
	raise notice 'Formula 8 % ', s_formres;
        s_formres := replace(s_formres,
                             'VACM_CUSTADM_1',
                             '( ' || REPLACE(cast(P_VALSUGE as varchar(15)), ',', '.') ||
                             ' * CASE VVEI_TIPOVEI_1 WHEN ''N'' then VCFG_CUSTADM_1 else VCFU_CUSTADM_1 END / 100 )');
	raise notice 'Formula 9 % ', s_formres;
        s_sqlform  := 'select to_char( ' || s_formres ||
                      ', ''999G999G990D00'' )' ||
                      ' AS VALOR from SGVVEI ' ||
                      ' left join SGVLOC on VLOC_CODILOC_1 = VVEI_LOCORIG_1' ||
                      ' left join SGVACM on VACM_CODIVEI_1 = VVEI_CODIVEI_1 and VACM_SITUACA_1 = '' ''' ||
                      ' left join SGVINT on trim( to_char( VINT_CONTROL_1, ''00000000'' ) ) = VACM_CODIVEI_1' ||
                      ' left join SGVCFG on VCFG_EMPRESA_1 = VLOC_EMITENT_1' ||
                      ' where VVEI_CODIVEI_1 = ''' || P_PROPOST || '''';
        execute s_sqlform
        into s_valor;
      end if;
      if (cur.vres_campvin_1 = 'VACM-COMILUC-1') then
        s_comiluc := REPLACE(s_valor, ',', '.');
      end if;
      EXCEPTION
      WHEN OTHERS THEN
	raise notice 'Formula 4 % ', s_formres;
        S_VALOR := 'ERRO' || coalesce( s_sqlform, '' );
    END;

    CASE CUR.VRES_CORLINH_1
      WHEN 'Branco' THEN
      S_BGCOLOR := 'white';
      WHEN 'Azul' THEN
      S_BGCOLOR := '#3598db';
      WHEN 'Verde' THEN
      S_BGCOLOR := 'green';
      WHEN 'Vermelho' THEN
      S_BGCOLOR := 'red';
      WHEN 'Amarelo' THEN
      S_BGCOLOR := 'yellow';
    ELSE
      S_BGCOLOR := '';
    end case;

    CASE CUR.Vres_Corfont_1
      WHEN 'Branco' THEN
      S_FONTCOLOR := 'white';
      WHEN 'Azul' THEN
      S_FONTCOLOR := '#3598db';
      WHEN 'Verde' THEN
      S_FONTCOLOR := 'green';
      WHEN 'Vermelho' THEN
      S_FONTCOLOR := 'red';
      WHEN 'Amarelo' THEN
      S_FONTCOLOR := 'yellow';
      WHEN 'Preto' THEN
      S_FONTCOLOR := 'black';
    ELSE
      S_FONTCOLOR := '';
    end case;

    V_RESULT := V_RESULT || '<tr style="background-color:' || s_bgcolor ||
                ';color:' || s_fontcolor || '"><td>' || cur.vres_nrolinh_1 || '</td><td>' || CUR.VRES_DESCRIC_1 ||
                '</td><td align="right">' || trim(S_valor) || '</td></tr>';

    V_JSONR := V_JSONR || CUR.VRES_DESCRIC_1 || '<br>;' || trim(s_Valor) ||
               '<br>;';

    IF (CUR.VRES_CAMPVIN_1 > ' ') THEN
      IF (CONTADOR > 0) THEN
        V_JSON := V_JSON || ',';
      END IF;
      CONTADOR := CONTADOR + 1;
      V_JSON   := V_JSON || '{"' || replace(CUR.VRES_CAMPVIN_1, '-', '_') ||
                  '":"' || trim(s_valor) || '"}';
    END IF;

  end loop;

  V_RESULT := V_RESULT || '</table><br/>';

  IF COALESCE(P_OPERACA, ' ') <> 'R' THEN

    V_RESULT := V_RESULT ||
                '<table width="100%" ><tr><th>Outras vendas</th></tr><tr><th>Cod</th><th>Descricao</th><th>Valor R$</th><th>Pagamento</th></tr>';
    for cur in (select *
                from SGVOUT o
                       left join SGVTAB05 T05 on T05.VTAB_CODITAB_05 =
                                                 O.VOUT_CODIGOS_1
                Where O.VOUT_PROPOST_1 = P_PROPOST) loop
      V_RESULT := V_RESULT || '<tr><td>' || CUR.VOUT_CODIGOS_1 || '</td>' ||
                  '<td>' || cur.vtab_desctab_05 || '</td>' ||
                  '<td align="right">' ||
                  to_char(cur.vout_valores_1, '999G999G990D00') || '</td>' ||
                  '<td>' || cur.vout_formpag_1 || '</td>' || '</tr>';
    end loop;
    V_RESULT := V_RESULT || '</table><br/>';
    C_TOTALACES := 0;
    V_RESULT    := V_RESULT || '<table width="100%"><tr><th colspam="2">Acessorios</th></tr><tr><tr><th>Codigo</th><th>Descricao</th><th>Valor R$</th></tr>';
    for cur in (select * from SGVACE A left join ESTPRO P on P.TPRO_CODPROD_1 = A.VACE_CODPROD_1 Where A.VACE_PROPOST_1 = P_PROPOST) loop
      V_RESULT    := V_RESULT || '<tr><td>' || CUR.VACE_CODPROD_1 ||
                     '</td>' || '<td>' || CUR.TPRO_DESCPR1_1 || '</td>' ||
                     '<td align="right">' ||
                     to_char(cur.vace_liquido_1, '999G999G990D00') ||
                     '</td>' || '</tr>';
      C_TOTALACES := C_TOTALACES + cur.vace_liquido_1;
    end loop;
    V_RESULT := V_RESULT ||
                '<tr style="background-color:#3F51B5;color:white"><td></td>' ||
                '<td>Total de acessorios</td>' || '<td align="right">' ||
                to_char(C_TOTALACES, '999G999G990D00') || '</td>' ||
                '</tr>';
    V_RESULT := V_RESULT || '</table>';
  END IF;
  V_RESULT := V_RESULT || '</body></html>';

  IF (COALESCE(P_OPERACA, ' ') = 'R') THEN
    return v_jsonr;
  ELSE
    IF (COALESCE(P_OPERACA, ' ') = 'J') THEN
      RETURN '[' || V_JSON || ']';
    ELSE
      RETURN V_RESULT;
    END IF;
  END IF;

  RETURN V_RESULT;
END
$BODY$ LANGUAGE PLPGSQL VOLATILE COST 100;


ALTER FUNCTION public.f_analise_proposta(CHARACTER varying, CHARACTER varying, double precision) OWNER TO postgres;