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);
    realValue VARCHAR2(120);
    q         CLOB;
  BEGIN
    q := sqlQuery;
    IF param IS NULL THEN
      RETURN q;
    END IF;

    paramlen := length(param);
    IF value IS NULL THEN
      realValue := ' IS NULL';
    ELSE
      BEGIN
        realValue := TO_NUMBER(value);
      EXCEPTION
        WHEN VALUE_ERROR THEN
          realValue := 'q''[' || value || ']''';
      END;
     realValue := '=' || realValue;
    END IF;

    LOOP
      pos := instr(q, '/*@' || param, 1, 1);
      EXIT WHEN pos = 0;
     debComm := pos - 1;
      endComm := instr(q, '*/', pos, 1);
      paramComm := substr(q, debComm, endComm - debComm);
     debReal := instr(paramComm, '=', 1, 1);
     realParam := trim(substr(paramComm, debReal + 1));
     --realParam := 'AND ' || substr(q,pos + paramLen + 4,endComm-pos - paramLen - 4);
      realParam := 'AND ' || realParam || realValue;
     q := substr(q, 1, debComm) || realParam || substr(q, endComm + 2);
    END LOOP;

    RETURN q;
  END;


  FUNCTION QUERY_APPLY_PARAMS(sqlQuery VARCHAR2, useParams BOOLEAN DEFAULT FALSE) RETURN CLOB IS
    q CLOB;
  BEGIN
    q := sqlQuery;

    IF NOT useParams THEN
      RETURN q;
    END IF;

    IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p1 IS NOT NULL THEN
      q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p1, UNICAEN_TBL.CALCUL_PROC_PARAMS.v1);
    END IF;

    IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p2 IS NOT NULL THEN
      q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p2, UNICAEN_TBL.CALCUL_PROC_PARAMS.v2);
    END IF;

    IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p3 IS NOT NULL THEN
      q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p3, UNICAEN_TBL.CALCUL_PROC_PARAMS.v3);
    END IF;

    IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p4 IS NOT NULL THEN
      q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p4, UNICAEN_TBL.CALCUL_PROC_PARAMS.v4);
    END IF;

    IF UNICAEN_TBL.CALCUL_PROC_PARAMS.p5 IS NOT NULL THEN
      q := QUERY_APPLY_PARAM(q, UNICAEN_TBL.CALCUL_PROC_PARAMS.p5, UNICAEN_TBL.CALCUL_PROC_PARAMS.v5);
    END IF;

    RETURN q;
  END;


  FUNCTION PARAMS_MAKE_FILTER(useParams BOOLEAN DEFAULT FALSE) RETURN VARCHAR2 IS
    filter VARCHAR2(4000) DEFAULT '';
  BEGIN
    IF NOT useParams THEN
      RETURN '1=1';
    END IF;

    IF unicaen_tbl.calcul_proc_params.p1 IS NOT NULL THEN
      IF filter IS NOT NULL THEN
        filter := filter || ' AND ';
      END IF;
      filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p1 || ', t.' || unicaen_tbl.calcul_proc_params.p1 || ') ';
      IF unicaen_tbl.calcul_proc_params.v1 IS NULL THEN
        filter := filter || 'IS NULL';
      ELSE
        filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v1 || ']''';
      END IF;
    END IF;

    IF unicaen_tbl.calcul_proc_params.p2 IS NOT NULL THEN
      IF filter IS NOT NULL THEN
        filter := filter || ' AND ';
      END IF;
      filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p2 || ', t.' || unicaen_tbl.calcul_proc_params.p2 || ') ';
      IF unicaen_tbl.calcul_proc_params.v2 IS NULL THEN
        filter := filter || 'IS NULL';
      ELSE
        filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v2 || ']''';
      END IF;
    END IF;

    IF unicaen_tbl.calcul_proc_params.p3 IS NOT NULL THEN
      IF filter IS NOT NULL THEN
        filter := filter || ' AND ';
      END IF;
      filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p3 || ', t.' || unicaen_tbl.calcul_proc_params.p3 || ') ';
      IF unicaen_tbl.calcul_proc_params.v3 IS NULL THEN
        filter := filter || 'IS NULL';
      ELSE
        filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v3 || ']''';
      END IF;
    END IF;

    IF unicaen_tbl.calcul_proc_params.p4 IS NOT NULL THEN
      IF filter IS NOT NULL THEN
        filter := filter || ' AND ';
      END IF;
      filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p4 || ', t.' || unicaen_tbl.calcul_proc_params.p4 || ') ';
      IF unicaen_tbl.calcul_proc_params.v4 IS NULL THEN
        filter := filter || 'IS NULL';
      ELSE
        filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v4 || ']''';
      END IF;
    END IF;

    IF unicaen_tbl.calcul_proc_params.p5 IS NOT NULL THEN
      IF filter IS NOT NULL THEN
        filter := filter || ' AND ';
      END IF;
      filter := filter || 'COALESCE(v.' || unicaen_tbl.calcul_proc_params.p5 || ', t.' || unicaen_tbl.calcul_proc_params.p5 || ') ';
      IF unicaen_tbl.calcul_proc_params.v5 IS NULL THEN
        filter := filter || 'IS NULL';
      ELSE
        filter := filter || '= q''[' || unicaen_tbl.calcul_proc_params.v5 || ']''';
      END IF;
    END IF;

    IF filter = '' THEN
      RETURN '1=1';
    END IF;

    RETURN filter;
  END;



  PROCEDURE CALCULER(TBL_NAME VARCHAR2) IS
    params t_params;
  BEGIN
    ANNULER_DEMANDES(TBL_NAME);
    CALCULER(TBL_NAME, params);
  END;



  PROCEDURE CALCULER(TBL_NAME VARCHAR2, param VARCHAR2, value VARCHAR2) IS
    calcul_proc varchar2(30);
    params t_params;
  BEGIN
    IF NOT UNICAEN_TBL.ACTIV_CALCULS THEN RETURN; END IF;

    SELECT custom_calcul_proc INTO calcul_proc FROM tbl WHERE tbl_name = CALCULER.TBL_NAME;

    params.p1 := param;
    params.v1 := value;

    unicaen_tbl.calcul_proc_params := params;

    IF calcul_proc IS NOT NULL THEN
      EXECUTE IMMEDIATE
        'BEGIN ' || calcul_proc || '(UNICAEN_TBL.CALCUL_PROC_PARAMS.p1, UNICAEN_TBL.CALCUL_PROC_PARAMS.v1); END;';
    ELSE
      EXECUTE IMMEDIATE
        'BEGIN UNICAEN_TBL.C_' || TBL_NAME || '(TRUE); END;';
    END IF;
  END;



  PROCEDURE CALCULER(TBL_NAME VARCHAR2, params t_params) IS
    calcul_proc varchar2(30);
  BEGIN
    IF NOT UNICAEN_TBL.ACTIV_CALCULS THEN RETURN; END IF;

    SELECT custom_calcul_proc INTO calcul_proc FROM tbl WHERE tbl_name = CALCULER.TBL_NAME;

    unicaen_tbl.calcul_proc_params := params;

    IF calcul_proc IS NOT NULL THEN
      EXECUTE IMMEDIATE
              'BEGIN ' || calcul_proc || '(UNICAEN_TBL.CALCUL_PROC_PARAMS.p1, UNICAEN_TBL.CALCUL_PROC_PARAMS.v1); END;';
    ELSE
      EXECUTE IMMEDIATE
              'BEGIN UNICAEN_TBL.C_' || TBL_NAME || '(TRUE); END;';
    END IF;
  END;



  PROCEDURE DEMANDE_CALCUL(TBL_NAME VARCHAR2, param VARCHAR2, value VARCHAR2) IS
  BEGIN
    dems(TBL_NAME)(param)(value) := TRUE;
  END;



  PROCEDURE ANNULER_DEMANDES IS
  BEGIN
    dems.delete;
  END;



  PROCEDURE ANNULER_DEMANDES(TBL_NAME VARCHAR2) IS
  BEGIN
    IF dems.exists(tbl_name) THEN
      dems(tbl_name).delete;
    END IF;
  END;



  FUNCTION HAS_DEMANDES RETURN BOOLEAN IS
  BEGIN
    RETURN dems.count > 0;
  END;



  PROCEDURE CALCULER_DEMANDES IS
    d t_dems;
    tbl_name VARCHAR2(30);
    param VARCHAR2(30);
    value VARCHAR2(80);
  BEGIN
    d := dems;
    dems.delete;

    tbl_name := d.FIRST;
    LOOP EXIT WHEN tbl_name IS NULL;
      param := d(tbl_name).FIRST;
      LOOP EXIT WHEN param IS NULL;
        value := d(tbl_name)(param).FIRST;
        LOOP EXIT WHEN value IS NULL;
          calculer(tbl_name, param, value);
          value := d(tbl_name)(param).NEXT(value);
        END LOOP;
        param := d(tbl_name).NEXT(param);
      END LOOP;
      tbl_name := d.NEXT(tbl_name);
    END LOOP;

    IF HAS_DEMANDES THEN -- pour les boucles !!
      CALCULER_DEMANDES;
    END IF;
  END;



  -- AUTOMATIC GENERATION --

  -- END OF AUTOMATIC GENERATION --

END UNICAEN_TBL;