/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/


CREATE GENERATOR GTBWORKFLOW_CAMPO_SQL;

CREATE TABLE TBWORKFLOW_CAMPO_SQL (
    ID                    INTEGER NOT NULL,
    WORKFLOW_ID           INTEGER NOT NULL,
    WORKFLOW_TAREFA_ID    INTEGER NOT NULL,
    WORKFLOW_CAMPO_ID     INTEGER NOT NULL,
    SQL                   BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CAMPOS                BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    TAMANHO               INTEGER DEFAULT 300,
    CAMPOS_TABELA         BLOB SUB_TYPE 1 SEGMENT SIZE 80,
    CAMPO_RETORNO         VARCHAR(500) DEFAULT '',
    CAMPOS_SUBTABELA_INC  BLOB SUB_TYPE 1 SEGMENT SIZE 80
);




/******************************************************************************/
/****                             Primary keys                             ****/
/******************************************************************************/

ALTER TABLE TBWORKFLOW_CAMPO_SQL ADD CONSTRAINT PK_TBWORKFLOW_CAMPO_SQL PRIMARY KEY (ID, WORKFLOW_ID, WORKFLOW_TAREFA_ID, WORKFLOW_CAMPO_ID);


/******************************************************************************/
/****                               Indices                                ****/
/******************************************************************************/

CREATE INDEX ITBWORKFLOW_CAMPO_SQL_01 ON TBWORKFLOW_CAMPO_SQL (ID);
CREATE INDEX ITBWORKFLOW_CAMPO_SQL_02 ON TBWORKFLOW_CAMPO_SQL (WORKFLOW_ID);
CREATE INDEX ITBWORKFLOW_CAMPO_SQL_03 ON TBWORKFLOW_CAMPO_SQL (WORKFLOW_TAREFA_ID);
CREATE INDEX ITBWORKFLOW_CAMPO_SQL_04 ON TBWORKFLOW_CAMPO_SQL (WORKFLOW_CAMPO_ID);


/******************************************************************************/
/****                               Triggers                               ****/
/******************************************************************************/



SET TERM ^ ;



/******************************************************************************/
/****                         Triggers for tables                          ****/
/******************************************************************************/



/* Trigger: TTBWORKFLOW_CAMPO_SQL_B01I */
CREATE OR ALTER TRIGGER TTBWORKFLOW_CAMPO_SQL_B01I FOR TBWORKFLOW_CAMPO_SQL
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
    IF (NEW.ID IS NULL) THEN
        NEW.ID = GEN_ID(GTBWORKFLOW_CAMPO_SQL, 1);
END
^

SET TERM ; ^



/******************************************************************************/
/****                         Fields descriptions                          ****/
/******************************************************************************/

COMMENT ON COLUMN TBWORKFLOW_CAMPO_SQL.SQL IS 
'SQL de consulta (QTD, PAGINA e FILTRO sao valores padroes)';

COMMENT ON COLUMN TBWORKFLOW_CAMPO_SQL.CAMPOS IS 
'Campos de retorno do SQL';

COMMENT ON COLUMN TBWORKFLOW_CAMPO_SQL.TAMANHO IS 
'Tamnho da consulta na tela em pixels (px)';

COMMENT ON COLUMN TBWORKFLOW_CAMPO_SQL.CAMPOS_TABELA IS 
'Campos que serao exibidos na tabela da consulta';

COMMENT ON COLUMN TBWORKFLOW_CAMPO_SQL.CAMPO_RETORNO IS 
'Campo que sera retornado quando um item for selecionado';

COMMENT ON COLUMN TBWORKFLOW_CAMPO_SQL.CAMPOS_SUBTABELA_INC IS 
'Campos da tabela de visualizacao de dados (Em caso de consulta de multiplos itens)';


/******************************************************************************/
/******************************************************************************/
/******************************************************************************/

SET TERM ^ ;

CREATE OR ALTER trigger ttbworkflow_campo_b02d for tbworkflow_campo
active before delete position 1
AS
declare variable WORKFLOW_ITEM_ID   INTEGER;
declare variable WORKFLOW_DESCRICAO VARCHAR(200);
begin
   WORKFLOW_ITEM_ID   = COALESCE((SELECT FIRST 1 H.ID
                                      FROM TBWORKFLOW_ITEM_CAMPO  T,
                                           TBWORKFLOW_ITEM_TAREFA G,
                                           TBWORKFLOW_TAREFA      H
                                       WHERE t.WORKFLOW_TAREFA_CAMPO_ID = OLD.ID
                                         AND G.WORKFLOW_ITEM_ID         = T.WORKFLOW_ITEM_ID
                                         AND H.ID                       = G.WORKFLOW_TAREFA_MODELO_ID
                                    ), 0);

   WORKFLOW_DESCRICAO = COALESCE((SELECT FIRST 1 H.TITULO
                                      FROM TBWORKFLOW_ITEM_CAMPO  T,
                                           TBWORKFLOW_ITEM_TAREFA G,
                                           TBWORKFLOW_TAREFA      H
                                       WHERE t.WORKFLOW_TAREFA_CAMPO_ID = OLD.ID
                                         AND G.WORKFLOW_ITEM_ID         = T.WORKFLOW_ITEM_ID
                                         AND H.ID                       = G.WORKFLOW_TAREFA_MODELO_ID

                                    ),'');

   IF(:WORKFLOW_ITEM_ID > 0)THEN
   BEGIN
       EXCEPTION "..." ' CAMPO: '||COALESCE(OLD.ROTULO,'')||', NA TAREFA: '||:WORKFLOW_DESCRICAO||' ESTA SENDO UTILIZADO EM WORKFLOWS, OPERAÇÃO CANCELADA!';
   END


   /******************************************************************************/

   DELETE FROM TBWORKFLOW_CAMPO_ENCERRAMENTO A WHERE A.WORKFLOW_ID = OLD.WORKFLOW_ID AND A.WORKFLOW_TAREFA_ID = OLD.WORKFLOW_TAREFA_ID AND A.WORKFLOW_CAMPO_ID = OLD.ID;
   DELETE FROM TBWORKFLOW_CAMPO_SQL B WHERE B.WORKFLOW_ID = OLD.WORKFLOW_ID AND B.WORKFLOW_TAREFA_ID = OLD.WORKFLOW_TAREFA_ID AND B.WORKFLOW_CAMPO_ID = OLD.ID;

end
^

SET TERM ; ^

