ALTER TABLE CRM_CLIENTES
ADD CRCL_NITCNT T_NIT;
ALTER TABLE CRM_CLIENTES
ADD ZONA_COD VARCHAR(2);
ALTER TABLE CRM_CLIENTES
ADD VEND_COD T_ID;
ALTER TABLE CRM_CLIENTES
ADD COBR_COD T_ID;

DROP TRIGGER CRM_CAMPANA_AI;
SET TERM ^ ;

CREATE OR ALTER TRIGGER CRM_CAMPANA_BI FOR CRM_CAMPANA
ACTIVE BEFORE INSERT POSITION 0
as
begin
if ((NEW.crcm_id = 0) or (NEW.crcm_id IS NULL))  then
    NEW.crcm_id = gen_id(id_campana, 1);
if (NEW.crcm_fecha IS NULL)  then
    NEW.crcm_fecha = cast('now' as DATE);
if (NEW.crcm_usuario IS NULL)  then
    NEW.crcm_usuario = USER;
end
^

SET TERM ; ^

ALTER TABLE CRM_CAMPANA DROP CRCM_NITINI;
ALTER TABLE CRM_CAMPANA DROP CRCM_NITFIN;
ALTER TABLE CRM_CAMPANA DROP CRCM_NOMINI;
ALTER TABLE CRM_CAMPANA DROP CRCM_NOMFIN;
ALTER TABLE CRM_CAMPANA DROP CRCM_VENINI;
ALTER TABLE CRM_CAMPANA DROP CRCM_VENFIN;
ALTER TABLE CRM_CAMPANA DROP CRCM_COBINI;
ALTER TABLE CRM_CAMPANA DROP CRCM_COBFIN;
ALTER TABLE CRM_CAMPANA DROP CRCM_ZONINI;
ALTER TABLE CRM_CAMPANA DROP CRCM_ZONFIN;
ALTER TABLE CRM_CAMPANA DROP CRCM_CLAINI;
ALTER TABLE CRM_CAMPANA DROP CRCM_CLAFIN;
ALTER TABLE CRM_CAMPANA DROP CRCM_AGRINI;
ALTER TABLE CRM_CAMPANA DROP CRCM_AGRFIN;
ALTER TABLE CRM_CAMPANA DROP CRCM_TIPINI;
ALTER TABLE CRM_CAMPANA DROP CRCM_TIPFIN;

ALTER TABLE CRM_CAMPANA
ADD CRCM_ASESOR T_ID;
ALTER TABLE CRM_CAMPANA
ADD CRCM_TIPOEVEN T_ID;
ALTER TABLE CRM_CAMPANA
ADD CRCM_DURACION SMALLINT;
ALTER TABLE CRM_CAMPANA
ADD CRCM_HORAINI1 TIME;
ALTER TABLE CRM_CAMPANA
ADD CRCM_HORAFIN1 TIME;
ALTER TABLE CRM_CAMPANA
ADD CRCM_HORAINI2 TIME;
ALTER TABLE CRM_CAMPANA
ADD CRCM_HORAFIN2 TIME;
ALTER TABLE CRM_CAMPANA
ADD CRCM_DIAS SMALLINT;
ALTER TABLE CRM_CAMPANA
ADD CRCM_ABIERTA T_BOOLEANO;

CREATE TABLE CRM_CAMPANA_MOTIVOS (
    CRCM_ID T_ID NOT NULL,
    CRMO_ITEM T_ID NOT NULL,
    CREM_ID T_ID NOT NULL);
alter table CRM_CAMPANA_MOTIVOS
add constraint PK_CRM_CAMPANA_MOTIVOS
primary key (CRCM_ID,CRMO_ITEM);

CREATE TABLE CRM_CAMPANA_RESPUESTAS (
    CRCM_ID T_ID NOT NULL,
    CRCR_ITEM T_ID NOT NULL,
    CRER_ID T_ID NOT NULL);
alter table CRM_CAMPANA_RESPUESTAS
add constraint PK_CRM_CAMPANA_RESPUESTAS
primary key (CRCM_ID,CRCR_ITEM);

ALTER TABLE CRM_EVENTO_MOTIVO
ADD CREM_GENERAL T_BOOLEANO;
ALTER TABLE CRM_EVENTO_RESPUESTA
ADD CRER_GENERAL T_BOOLEANO;

UPDATE CRM_EVENTO_MOTIVO SET CREM_GENERAL = 'S' WHERE CREM_GENERAL IS NULL;
UPDATE crm_evento_respuesta SET CRER_GENERAL = 'S' WHERE CRER_GENERAL IS NULL;
COMMIT WORK;

SET TERM ^ ;

create procedure RANGO_CLIENTES (
    NITINI VARCHAR(20),
    NITFIN VARCHAR(20),
    NOMINI VARCHAR(60),
    NOMFIN VARCHAR(60),
    VENINI INTEGER,
    VENFIN INTEGER,
    COBINI INTEGER,
    COBFIN INTEGER,
    ZONINI VARCHAR(2),
    ZONFIN VARCHAR(2),
    CIUINI VARCHAR(5),
    CIUFIN VARCHAR(5),
    CLAINI INTEGER,
    CLAFIN INTEGER,
    AGRINI INTEGER,
    AGRFIN INTEGER,
    TIPINI INTEGER,
    TIPFIN INTEGER,
    ULTVNT DATE,
    ULTCON DATE,
    CHECK1 CHAR(1),
    CHECK2 CHAR(1),
    CHECK3 CHAR(1),
    CHECK4 CHAR(1),
    CHECK5 CHAR(1),
    CHECK6 CHAR(1))
returns (
    NIT VARCHAR(20))
as
declare variable OK CHAR(1);
begin
FOR SELECT CRCL_NIT from crm_clientes WHERE CRCL_NIT >= :nitini AND CRCL_NIT <= :nitfin AND
    CRCL_NOM >= :nomini AND CRCL_NOM <= :nomfin AND
    (((:CIUINI = '') AND (:CIUFIN = 'zz')) or ((CIUD_COD >= :CIUINI) AND (CIUD_COD <= :CIUFIN))) AND
    (((:ZONINI = '') AND (:ZONFIN = 'zz')) or ((ZONA_COD >= :ZONINI) AND (ZONA_COD <= :CIUFIN))) AND
    (((:VENINI = '') AND (:VENFIN = '99999')) or ((VEND_COD >= :VENINI) AND (VEND_COD <= :VENFIN))) AND
    (((:COBINI = '') AND (:COBFIN = '99999')) or ((COBR_COD >= :COBINI) AND (COBR_COD <= :COBFIN))) AND
    (((:TIPINI = '') AND (:TIPFIN = '99999')) or ((CRCL_TIPORELA >= :TIPINI) AND (CRCL_TIPORELA <= :TIPFIN))) AND
    (((:CLAINI = '') AND (:CLAFIN = '99999')) or ((CRCL_CLASE >= :CLAINI) AND (CRCL_CLASE <= :CLAFIN))) AND
    (((:AGRINI = '') AND (:AGRFIN = '99999')) or ((CRCL_AGRUPA >= :AGRINI) AND (CRCL_AGRUPA <= :AGRFIN))) AND
    ((:CHECK1 = 'N') or (CRCL_CHECK1 = 'S')) AND ((:CHECK2 = 'N') or (CRCL_CHECK2 = 'S')) AND
    ((:CHECK3 = 'N') or (CRCL_CHECK3 = 'S')) AND ((:CHECK4 = 'N') or (CRCL_CHECK4 = 'S')) AND
    ((:CHECK5 = 'N') or (CRCL_CHECK5 = 'S')) AND ((:CHECK6 = 'N') or (CRCL_CHECK6 = 'S'))
    INTO :NIT
    DO
    begin
    OK = 'S';
    if (ultvnt <> '9999/12/31') then
        BEGIN
        if (EXISTS (SELECT FACT_ID FROM FACTURAS WHERE TERC_NIT = :NIT AND FACT_FECHA >= :ultvnt)) then
            OK = 'N';
        END
    if (ultcon <> '9999/12/31') then
        BEGIN
        if (EXISTS (SELECT CREV_ID FROM crm_eventos WHERE CRCL_NIT = :NIT AND CREV_FECHA >= :ultcon)) then
            OK = 'N';
        END
    if (OK = 'S') then
        suspend;
    end
end
^

SET TERM ; ^

GRANT EXECUTE ON PROCEDURE rango_clientes TO PUBLIC;

ALTER TABLE CRM_EVENTOS
ADD CRCM_ID T_ID;
ALTER TABLE CRM_COMPROMISOS
ADD CRCM_ID T_ID;
ALTER TABLE CRM_EVENTO_RESPUESTA
ADD CRER_ACTIVO T_BOOLEANO;
ALTER TABLE CRM_TIPOEVENTO
ADD CRTE_INTERNO T_BOOLEANO;
ALTER TABLE CRM_EVENTO_RESPUESTA
ADD CREM_ID T_ID NOT NULL ;
alter table CRM_EVENTO_RESPUESTA
add constraint FK_CRM_EVENTO_RESPUESTA_1
foreign key (CREM_ID)
references CRM_EVENTO_MOTIVO(CREM_ID);

