source: publico/ILSAPL/branches/2.5/skins/sk_sapl/zsql/norma_juridica_obter_zsql.zsql @ 8332

<dtml-comment>
connection_id : dbcon_interlegis
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=""
max_rows : 5000
max_cache : 100
</dtml-comment>
  SELECT 
       REPLACE(cod_norma,'L', '') as cod_norma,
       norma_juridica.tip_norma as tip_norma_sel,
       REPLACE(num_norma,'L', '') as num_norma,
       REPLACE(ano_norma,'L', '') as ano_norma,
       norma_juridica.tip_esfera_federacao,
       REPLACE(cod_materia,'L', '') as cod_materia,
       DATE_FORMAT(dat_norma,'%Y/%m/%d') as dat_norma,
       DATE_FORMAT(dat_publicacao,'%Y/%m/%d') as dat_publicacao,
       DATE_FORMAT(dat_vigencia, '%Y/%m/%d')  as dat_vigencia,
       norma_juridica.des_veiculo_publicacao,
       REPLACE(num_pag_inicio_publ,'L', '') as num_pag_inicio_publ,
       REPLACE(num_pag_fim_publ,'L', '') as num_pag_fim_publ,
    <dtml-if expr="rd_ordenacao == '2'">
       match(norma_juridica.txt_ementa,norma_juridica.txt_indexacao)
       against (<dtml-sqlvar des_assunto type="string"> IN BOOLEAN MODE) as relevancia,
    <dtml-else>
       (100000 - num_norma) as num_norma2,
    </dtml-if>
       norma_juridica.txt_ementa,
       norma_juridica.txt_indexacao,
       norma_juridica.txt_observacao,
       norma_juridica.ind_complemento,
       tipo_norma_juridica.sgl_tipo_norma as sgl_tipo_norma,
       tipo_norma_juridica.des_tipo_norma,
       norma_juridica.cod_assunto as cod_assunto_sel
  FROM 
      assunto_norma,
      tipo_norma_juridica left join norma_juridica on (tipo_norma_juridica.tip_norma=norma_juridica.tip_norma)

  <dtml-sqlgroup where>      
          tipo_norma_juridica.ind_excluido = 0 
           and norma_juridica.ind_excluido = 0
           and norma_juridica.cod_assunto  = assunto_norma.cod_assunto
         <dtml-if expr="em_vigencia=='0'">
           and ((norma_juridica.dat_vigencia <> '' and norma_juridica.dat_vigencia < <dtml-sqlvar "pysc.verifica_vigencia_norma_dh_pysc()" type="string">)
                 or (norma_juridica.cod_norma in (select cod_norma_referida from vinculo_norma_juridica v, norma_juridica 
                                                   where v.ind_excluido=0 and v.tip_vinculo in ('L','N','R','T') and 
                                                         v.cod_norma_referida=norma_juridica.cod_norma)))
         </dtml-if>
         <dtml-if expr="cod_norma!=''">
            and <dtml-sqltest cod_norma column="norma_juridica.cod_norma" op="=" type="int" optional>
         </dtml-if>
         <dtml-if expr="tip_norma!=''">
            and <dtml-sqltest tip_norma column="norma_juridica.tip_norma" op="=" type="int" optional>
         </dtml-if>
         <dtml-if expr="num_norma!=''">
            and <dtml-sqltest num_norma column="norma_juridica.num_norma" op="=" type="int" optional>
         </dtml-if>
         <dtml-if expr="ano_norma!=''">
            and <dtml-sqltest ano_norma column="norma_juridica.ano_norma" op="=" type="int" optional>
         </dtml-if>
         <dtml-if expr="dat_norma!='' and dat_norma2!=''">
              and ( norma_juridica.dat_norma between <dtml-sqlvar "pysc.port_to_iso_pysc(dat_norma)" type="string"> 
                                                 and <dtml-sqlvar "pysc.port_to_iso_pysc(dat_norma2)" type="string">)
         <dtml-elif expr="dat_norma!=''">
              and ( norma_juridica.dat_norma >= <dtml-sqlvar "pysc.port_to_iso_pysc(dat_norma)" type="string">)
         <dtml-elif expr="dat_norma2!=''">
              and (norma_juridicca.dat_norma <= <dtml-sqlvar "pysc.port_to_iso_pysc(dat_norma2)" type="string">)
         </dtml-if>
        <dtml-if expr="dat_publicacao!='' and dat_publicacao2!=''">
              and ( norma_juridica.dat_publicacao between <dtml-sqlvar "pysc.port_to_iso_pysc(dat_publicacao)" type="string"> 
                                                      and <dtml-sqlvar "pysc.port_to_iso_pysc(dat_publicacao2)" type="string">) 
        <dtml-elif expr="dat_publicacao!=''">
              and ( norma_juridica.dat_publicacao >= <dtml-sqlvar "pysc.port_to_iso_pysc(dat_publicacao)" type="string">)
        <dtml-elif expr="dat_publicacao2!=''">
              and (norma_juridica.dat_publicacao <= <dtml-sqlvar "pysc.port_to_iso_pysc(dat_publicacao2)" type="string">)
        </dtml-if>

        <dtml-if expr="des_assunto!=''">
           and match(norma_juridica.txt_ementa,norma_juridica.txt_indexacao)
               against (<dtml-sqlvar des_assunto type="string"> IN BOOLEAN MODE)
        </dtml-if>

       <dtml-if expr="cod_assunto!=''">
           and (norma_juridica.cod_assunto like <dtml-sqlvar "'%' + cod_assunto + '%'" type="string">)
       </dtml-if>
  
       <dtml-if expr="em_vigencia=='1'">
           and (  (norma_juridica.dat_vigencia = ' '    or 
                   norma_juridica.dat_vigencia is null  or  
                   norma_juridica.dat_vigencia >= <dtml-sqlvar "pysc.verifica_vigencia_norma_dh_pysc()" type="string">) and
                  (norma_juridica.cod_norma not in (select cod_norma_referida from vinculo_norma_juridica v, norma_juridica
                                                     where v.ind_excluido=0 and v.tip_vinculo in ('L','N','R','T') and
                                                           v.cod_norma_referida=norma_juridica.cod_norma)))
      </dtml-if>
  </dtml-sqlgroup>
  <dtml-if expr="rd_ordenacao == '2'">
   <dtml-comment> HAVING relevancia > 0.001</dtml-comment>
    ORDER BY relevancia DESC, dat_norma DESC
  <dtml-elif expr="rd_ordenacao == '1'">
    ORDER BY sgl_tipo_norma, dat_norma DESC, num_norma2 ASC
  <dtml-else>
    ORDER BY dat_norma DESC, sgl_tipo_norma, dat_norma DESC, num_norma2 ASC
  </dtml-if>