package.sql

CREATE OR REPLACE PACKAGE "UNICAEN_TBL" AS

  ACTIV_TRIGGERS BOOLEAN DEFAULT TRUE;
  ACTIV_CALCULS  BOOLEAN DEFAULT TRUE;

  TYPE t_params IS RECORD (
    p1 VARCHAR2(30), v1 VARCHAR2(80),
    p2 VARCHAR2(30), v2 VARCHAR2(80),
    p3 VARCHAR2(30), v3 VARCHAR2(80),
    p4 VARCHAR2(30), v4 VARCHAR2(80),
    p5 VARCHAR2(30), v5 VARCHAR2(80)
  );

  CALCUL_PROC_PARAMS t_params;

  FUNCTION QUERY_APPLY_PARAM( sqlQuery VARCHAR2, param VARCHAR2, value VARCHAR2) RETURN CLOB;
  FUNCTION MAKE_WHERE(param VARCHAR2 DEFAULT NULL, value VARCHAR2 DEFAULT NULL, alias VARCHAR2 DEFAULT NULL) RETURN VARCHAR2;

  PROCEDURE CALCULER( TBL_NAME VARCHAR2 );
  PROCEDURE CALCULER( TBL_NAME VARCHAR2, param VARCHAR2, value VARCHAR2 );
  PROCEDURE CALCULER(TBL_NAME VARCHAR2, params t_params);
  PROCEDURE DEMANDE_CALCUL( TBL_NAME VARCHAR2, param VARCHAR2, value VARCHAR2 );

  PROCEDURE ANNULER_DEMANDES;
  PROCEDURE ANNULER_DEMANDES( TBL_NAME VARCHAR2 );
  PROCEDURE CALCULER_DEMANDES;

  -- AUTOMATIC GENERATION --

  -- END OF AUTOMATIC GENERATION --

END UNICAEN_TBL;

/

CREATE OR REPLACE PACKAGE BODY "UNICAEN_TBL" AS
  TYPE t_dems_values IS TABLE OF BOOLEAN INDEX BY VARCHAR2(80);
  TYPE t_dems_params IS TABLE OF t_dems_values INDEX BY VARCHAR2(30);
  TYPE t_dems IS TABLE OF t_dems_params INDEX BY VARCHAR2(30);

  dems t_dems;



  FUNCTION MAKE_WHERE(param VARCHAR2 DEFAULT NULL, value VARCHAR2 DEFAULT NULL,
                      alias VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS
    res VARCHAR2(120) DEFAULT '';
  BEGIN
    IF param IS NULL THEN
      RETURN '1=1';
    END IF;
    IF alias IS NOT NULL THEN
      res := alias || '.';
    END IF;
    IF value IS NULL THEN
      RETURN res || param || ' IS NULL';
    END IF;
   RETURN res || param || ' = q''[' || value || ']''';
  END;



  FUNCTION QUERY_APPLY_PARAM(sqlQuery VARCHAR2, param VARCHAR2, value VARCHAR2) RETURN CLOB IS
    pos       NUMERIC;
    paramLen  NUMERIC;
    paramComm VARCHAR2(200);
    debComm   NUMERIC;
    endComm   NUMERIC;
    debReal   NUMERIC;
    realParam VARCHAR2(80);