CREATE OR REPLACE PACKAGE Claus IS

   FUNCTION UNIR(CLAUSULA1 IN VARCHAR2, PARTICULA IN VARCHAR2, CLAUSULA2 IN VARCHAR2) RETURN VARCHAR2;

   FUNCTION LISTA(X1 IN NUMBER, X2 IN NUMBER DEFAULT NULL, X3 IN NUMBER DEFAULT NULL) RETURN VARCHAR2;
   FUNCTION LISTA(X1 IN VARCHAR2, X2 IN VARCHAR2 DEFAULT NULL, X3 IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;
   FUNCTION LISTA(X1 IN DATE, X2 IN DATE DEFAULT NULL, X3 IN DATE DEFAULT NULL) RETURN VARCHAR2;
   FUNCTION UNIRLISTA(L1 IN VARCHAR2, L2 IN VARCHAR2) RETURN VARCHAR2;

   FUNCTION PATRON(COLUMNA IN VARCHAR2, PATRON IN VARCHAR2) RETURN VARCHAR2;
   FUNCTION CLAUSULA(COLUMNA IN VARCHAR2, INICIO IN NUMBER, FINAL IN NUMBER) RETURN VARCHAR2;
   FUNCTION CLAUSULA(COLUMNA IN VARCHAR2, INICIO IN VARCHAR2, FINAL IN VARCHAR2) RETURN VARCHAR2;
   FUNCTION CLAUSULA(COLUMNA IN VARCHAR2, INICIO IN DATE, FINAL IN DATE) RETURN VARCHAR2;
   FUNCTION CLAUSULATS(COLUMNA IN VARCHAR2, INICIO IN DATE, FINAL IN DATE) RETURN VARCHAR2;

   PROCEDURE VALIDA(COLUMNA IN VARCHAR2, INICIO IN VARCHAR2, FINAL IN VARCHAR2);
   PROCEDURE VALIDA(COLUMNA IN VARCHAR2, INICIO IN NUMBER, FINAL IN NUMBER);
   PROCEDURE VALIDA(COLUMNA IN VARCHAR2, INICIO IN DATE, FINAL IN DATE);

   FUNCTION ULTIMA(TABLAB IN VARCHAR2, UCONSU IN VARCHAR2) RETURN VARCHAR2;

   FUNCTION SUBTITULO(COLUMNA IN VARCHAR2, INICIO IN VARCHAR2, FINAL IN VARCHAR2) RETURN VARCHAR2;

   FUNCTION ATOMO(X VARCHAR2) RETURN VARCHAR2;
   FUNCTION ATOMO(X DATE, FRMT IN VARCHAR2 DEFAULT 'DD/MM/YYYY') RETURN VARCHAR2;


END;
/

CREATE OR REPLACE PACKAGE BODY Claus IS

   FUNCTION ATOMO(X VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      IF X IS NOT NULL THEN
         RETURN ''''||X||'''';
      ELSE
         RETURN NULL;
      END IF;
   END ATOMO;

   FUNCTION ATOMO(X DATE, FRMT IN VARCHAR2 DEFAULT 'DD/MM/YYYY') RETURN VARCHAR2 IS
   BEGIN
      IF X IS NOT NULL THEN
         RETURN 'TO_DATE('''||TO_CHAR(X,FRMT)||''','''||FRMT||''')';
      ELSE
         RETURN NULL;
      END IF;
   END ATOMO;

   FUNCTION UNIR(CLAUSULA1 VARCHAR2, PARTICULA VARCHAR2, CLAUSULA2 VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      IF CLAUSULA1 IS NULL AND CLAUSULA2 IS NULL THEN
         RETURN NULL;
      ELSIF CLAUSULA1 IS NULL OR CLAUSULA2 IS NULL THEN
         RETURN CLAUSULA1||CLAUSULA2;
      ELSE
         RETURN CLAUSULA1||PARTICULA||CLAUSULA2;
      END IF;
   END UNIR;

   FUNCTION PREPLISTA(X1 IN VARCHAR2, X2 IN VARCHAR2 DEFAULT NULL, X3 IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
      VLISTA VARCHAR2(256);
   BEGIN
      IF X1 IS NOT NULL THEN
         VLISTA:=X1;
      END IF;
      IF X2 IS NOT NULL AND X2<>X1 THEN
         VLISTA:=UNIR(VLISTA,',',X2);
      END IF;
      IF X3 IS NOT NULL AND X3 NOT IN (X1, X2) THEN
         VLISTA:=UNIR(VLISTA,',',X3);
      END IF;
      RETURN VLISTA;
   END PREPLISTA;

   FUNCTION LISTA(X1 IN NUMBER, X2 IN NUMBER DEFAULT NULL, X3 IN NUMBER DEFAULT NULL) RETURN VARCHAR2 IS
   BEGIN
      RETURN PREPLISTA(X1, X2, X3);
   END LISTA;

   FUNCTION LISTA(X1 IN VARCHAR2, X2 IN VARCHAR2 DEFAULT NULL, X3 IN VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
   BEGIN
      RETURN PREPLISTA(ATOMO(X1), ATOMO(X2), ATOMO(X3));
   END LISTA;

   FUNCTION LISTA(X1 IN DATE, X2 IN DATE DEFAULT NULL, X3 IN DATE DEFAULT NULL) RETURN VARCHAR2 IS
   BEGIN
      RETURN PREPLISTA(ATOMO(X1), ATOMO(X2), ATOMO(X3));
   END LISTA;

   FUNCTION UNIRLISTA(L1 IN VARCHAR2, L2 IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
   	  RETURN UNIR(L1,',',L2);
   END UNIRLISTA;

   FUNCTION PREPARAR(COLUMNA VARCHAR2, INICIO VARCHAR2, FINAL VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      IF INICIO IS NULL AND FINAL IS NULL THEN
         RETURN NULL;
      ELSIF INICIO IS NULL THEN
         RETURN COLUMNA||' <= '||FINAL;
      ELSIF FINAL IS NULL THEN
         RETURN COLUMNA||' >= '||INICIO;
      ELSIF INICIO=FINAL THEN
         RETURN COLUMNA||' = '||INICIO;
      ELSE
         RETURN COLUMNA||' BETWEEN '||INICIO||' AND '||FINAL;
      END IF;
   END PREPARAR;

   FUNCTION PATRON(COLUMNA VARCHAR2, PATRON VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      IF PATRON IS NULL THEN
         RETURN NULL;
      ELSIF INSTR(PATRON,'%')>0 OR INSTR(PATRON,'_')>0 THEN
         RETURN COLUMNA||' LIKE '||ATOMO(PATRON);
      ELSIF INSTR(PATRON,',')>0 THEN
         RETURN COLUMNA||' IN ('||PATRON||')';
      ELSIF SUBSTR(PATRON, 1, 1)='''' AND SUBSTR(PATRON, LENGTH(PATRON), 1)='''' THEN
         RETURN COLUMNA||' = '||PATRON;
      ELSE
         RETURN COLUMNA||' = '||ATOMO(PATRON);
      END IF;
   END PATRON;

   FUNCTION CLAUSULA(COLUMNA VARCHAR2, INICIO NUMBER, FINAL NUMBER) RETURN VARCHAR2 IS
   BEGIN
      RETURN PREPARAR(COLUMNA, INICIO, FINAL);
   END CLAUSULA;

   FUNCTION CLAUSULA(COLUMNA VARCHAR2, INICIO VARCHAR2, FINAL VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      RETURN PREPARAR(COLUMNA, ATOMO(INICIO), ATOMO(FINAL));
   END CLAUSULA;

   FUNCTION CLAUSULA(COLUMNA VARCHAR2, INICIO DATE, FINAL DATE) RETURN VARCHAR2 IS
   BEGIN
      RETURN PREPARAR(COLUMNA, ATOMO(INICIO), ATOMO(FINAL));
   END CLAUSULA;

   FUNCTION CLAUSULATS(COLUMNA VARCHAR2, INICIO DATE, FINAL DATE) RETURN VARCHAR2 IS
   BEGIN
      RETURN PREPARAR(COLUMNA, ATOMO(INICIO,'DD/MM/YYYY HH24:MI:SS'), ATOMO(FINAL,'DD/MM/YYYY HH24:MI:SS'));
   END CLAUSULATS;

   PROCEDURE VALIDA(COLUMNA VARCHAR2, INICIO NUMBER, FINAL NUMBER) IS
   BEGIN
      IF INICIO>FINAL THEN
         RAISE_APPLICATION_ERROR(-20006,'AVISO: El inicio debe ser menor que el final ('||COLUMNA||') ');
      END IF;
   END VALIDA;

   PROCEDURE VALIDA(COLUMNA VARCHAR2, INICIO VARCHAR2, FINAL VARCHAR2) IS
   BEGIN
      IF INICIO>FINAL THEN
         RAISE_APPLICATION_ERROR(-20006,'AVISO: El inicio debe ser menor que el final ('||COLUMNA||') ');
      END IF;
   END VALIDA;

   PROCEDURE VALIDA(COLUMNA VARCHAR2, INICIO DATE, FINAL DATE) IS
   BEGIN
      IF INICIO>FINAL THEN
         RAISE_APPLICATION_ERROR(-20006,'AVISO: El inicio debe ser menor que el final ('||COLUMNA||') ');
      END IF;
   END VALIDA;

   FUNCTION ULTIMA(TABLAB IN VARCHAR2, UCONSU IN VARCHAR2) RETURN VARCHAR2 is
      consu   varchar2(1024):=UCONSU;
      inipos  number(4);
   BEGIN
      inipos:=instr(consu, ' FROM '||tablab||' ');
      if inipos=0 then
         return null;
      else
         consu:=substr(consu, inipos+nvl(length(tablab),0)+6);
         inipos:=instr(upper(consu), ' ORDER BY ');
         if inipos != 0 then
            consu:=substr(consu, 1, inipos-1);
         end if;
         return(' AND '||consu);
      end if;
   END ULTIMA;

   FUNCTION SUBTITULO(COLUMNA IN VARCHAR2, INICIO IN VARCHAR2, FINAL IN VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      IF INICIO IS NULL AND FINAL IS NULL THEN
         RETURN NULL;
      ELSIF INICIO IS NULL THEN
         RETURN COLUMNA||': <= '||FINAL;
      ELSIF FINAL IS NULL THEN
         RETURN COLUMNA||': >= '||INICIO;
      ELSIF INICIO=FINAL THEN
         RETURN COLUMNA||': '||INICIO;
      ELSE
         RETURN COLUMNA||': '||INICIO||' AL '||FINAL;
      END IF;
   END SUBTITULO;

END;
/
