1 | <dtml-comment> |
---|
2 | connection_id : dbcon_interlegis |
---|
3 | arguments : cod_norma="" tip_norma="" num_norma="" ano_norma="" des_assunto="" dat_norma="" dat_norma2="" dat_publicacao="" dat_publicacao2="" rd_ordenacao="" cod_assunto="" em_vigencia="" |
---|
4 | max_rows : 5000 |
---|
5 | max_cache : 100 |
---|
6 | </dtml-comment> |
---|
7 | SELECT |
---|
8 | REPLACE(cod_norma,'L', '') as cod_norma, |
---|
9 | norma_juridica.tip_norma as tip_norma_sel, |
---|
10 | REPLACE(num_norma,'L', '') as num_norma, |
---|
11 | REPLACE(ano_norma,'L', '') as ano_norma, |
---|
12 | norma_juridica.tip_esfera_federacao, |
---|
13 | REPLACE(cod_materia,'L', '') as cod_materia, |
---|
14 | DATE_FORMAT(dat_norma,'%Y/%m/%d') as dat_norma, |
---|
15 | DATE_FORMAT(dat_publicacao,'%Y/%m/%d') as dat_publicacao, |
---|
16 | DATE_FORMAT(dat_vigencia, '%Y/%m/%d') as dat_vigencia, |
---|
17 | norma_juridica.des_veiculo_publicacao, |
---|
18 | REPLACE(num_pag_inicio_publ,'L', '') as num_pag_inicio_publ, |
---|
19 | REPLACE(num_pag_fim_publ,'L', '') as num_pag_fim_publ, |
---|
20 | <dtml-if expr="rd_ordenacao == '2'"> |
---|
21 | match(norma_juridica.txt_ementa,norma_juridica.txt_indexacao) |
---|
22 | against (<dtml-sqlvar des_assunto type="string"> IN BOOLEAN MODE) as relevancia, |
---|
23 | <dtml-else> |
---|
24 | (100000 - num_norma) as num_norma2, |
---|
25 | </dtml-if> |
---|
26 | norma_juridica.txt_ementa, |
---|
27 | norma_juridica.txt_indexacao, |
---|
28 | norma_juridica.txt_observacao, |
---|
29 | norma_juridica.ind_complemento, |
---|
30 | tipo_norma_juridica.sgl_tipo_norma as sgl_tipo_norma, |
---|
31 | tipo_norma_juridica.des_tipo_norma, |
---|
32 | norma_juridica.cod_assunto as cod_assunto_sel |
---|
33 | FROM |
---|
34 | assunto_norma, |
---|
35 | tipo_norma_juridica left join norma_juridica on (tipo_norma_juridica.tip_norma=norma_juridica.tip_norma) |
---|
36 | |
---|
37 | <dtml-sqlgroup where> |
---|
38 | tipo_norma_juridica.ind_excluido = 0 |
---|
39 | and norma_juridica.ind_excluido = 0 |
---|
40 | and norma_juridica.cod_assunto = assunto_norma.cod_assunto |
---|
41 | <dtml-if expr="em_vigencia=='0'"> |
---|
42 | and ((norma_juridica.dat_vigencia <> '' and norma_juridica.dat_vigencia < <dtml-sqlvar "pysc.verifica_vigencia_norma_dh_pysc()" type="string">) |
---|
43 | or (norma_juridica.cod_norma in (select cod_norma_referida from vinculo_norma_juridica v, norma_juridica |
---|
44 | where v.ind_excluido=0 and v.tip_vinculo in ('L','N','R','T') and |
---|
45 | v.cod_norma_referida=norma_juridica.cod_norma))) |
---|
46 | </dtml-if> |
---|
47 | <dtml-if expr="cod_norma!=''"> |
---|
48 | and <dtml-sqltest cod_norma column="norma_juridica.cod_norma" op="=" type="int" optional> |
---|
49 | </dtml-if> |
---|
50 | <dtml-if expr="tip_norma!=''"> |
---|
51 | and <dtml-sqltest tip_norma column="norma_juridica.tip_norma" op="=" type="int" optional> |
---|
52 | </dtml-if> |
---|
53 | <dtml-if expr="num_norma!=''"> |
---|
54 | and <dtml-sqltest num_norma column="norma_juridica.num_norma" op="=" type="int" optional> |
---|
55 | </dtml-if> |
---|
56 | <dtml-if expr="ano_norma!=''"> |
---|
57 | and <dtml-sqltest ano_norma column="norma_juridica.ano_norma" op="=" type="int" optional> |
---|
58 | </dtml-if> |
---|
59 | <dtml-if expr="dat_norma!='' and dat_norma2!=''"> |
---|
60 | and ( norma_juridica.dat_norma between <dtml-sqlvar "pysc.port_to_iso_pysc(dat_norma)" type="string"> |
---|
61 | and <dtml-sqlvar "pysc.port_to_iso_pysc(dat_norma2)" type="string">) |
---|
62 | <dtml-elif expr="dat_norma!=''"> |
---|
63 | and ( norma_juridica.dat_norma >= <dtml-sqlvar "pysc.port_to_iso_pysc(dat_norma)" type="string">) |
---|
64 | <dtml-elif expr="dat_norma2!=''"> |
---|
65 | and (norma_juridicca.dat_norma <= <dtml-sqlvar "pysc.port_to_iso_pysc(dat_norma2)" type="string">) |
---|
66 | </dtml-if> |
---|
67 | <dtml-if expr="dat_publicacao!='' and dat_publicacao2!=''"> |
---|
68 | and ( norma_juridica.dat_publicacao between <dtml-sqlvar "pysc.port_to_iso_pysc(dat_publicacao)" type="string"> |
---|
69 | and <dtml-sqlvar "pysc.port_to_iso_pysc(dat_publicacao2)" type="string">) |
---|
70 | <dtml-elif expr="dat_publicacao!=''"> |
---|
71 | and ( norma_juridica.dat_publicacao >= <dtml-sqlvar "pysc.port_to_iso_pysc(dat_publicacao)" type="string">) |
---|
72 | <dtml-elif expr="dat_publicacao2!=''"> |
---|
73 | and (norma_juridica.dat_publicacao <= <dtml-sqlvar "pysc.port_to_iso_pysc(dat_publicacao2)" type="string">) |
---|
74 | </dtml-if> |
---|
75 | |
---|
76 | <dtml-if expr="des_assunto!=''"> |
---|
77 | and match(norma_juridica.txt_ementa,norma_juridica.txt_indexacao) |
---|
78 | against (<dtml-sqlvar des_assunto type="string"> IN BOOLEAN MODE) |
---|
79 | </dtml-if> |
---|
80 | |
---|
81 | <dtml-if expr="cod_assunto!=''"> |
---|
82 | and (norma_juridica.cod_assunto like <dtml-sqlvar "'%' + cod_assunto + '%'" type="string">) |
---|
83 | </dtml-if> |
---|
84 | |
---|
85 | <dtml-if expr="em_vigencia=='1'"> |
---|
86 | and ( (norma_juridica.dat_vigencia = ' ' or |
---|
87 | norma_juridica.dat_vigencia is null or |
---|
88 | norma_juridica.dat_vigencia >= <dtml-sqlvar "pysc.verifica_vigencia_norma_dh_pysc()" type="string">) and |
---|
89 | (norma_juridica.cod_norma not in (select cod_norma_referida from vinculo_norma_juridica v, norma_juridica |
---|
90 | where v.ind_excluido=0 and v.tip_vinculo in ('L','N','R','T') and |
---|
91 | v.cod_norma_referida=norma_juridica.cod_norma))) |
---|
92 | </dtml-if> |
---|
93 | </dtml-sqlgroup> |
---|
94 | <dtml-if expr="rd_ordenacao == '2'"> |
---|
95 | <dtml-comment> HAVING relevancia > 0.001</dtml-comment> |
---|
96 | ORDER BY relevancia DESC, dat_norma DESC |
---|
97 | <dtml-elif expr="rd_ordenacao == '1'"> |
---|
98 | ORDER BY sgl_tipo_norma, dat_norma DESC, num_norma2 ASC |
---|
99 | <dtml-else> |
---|
100 | ORDER BY dat_norma DESC, sgl_tipo_norma, dat_norma DESC, num_norma2 ASC |
---|
101 | </dtml-if> |
---|