/* NUMERACION DE CLIENTES CONSECUTIVA */
INSERT INTO CONFIGURACION (CONF_MODULO, CONF_CATEGORIA, CONF_PROPIEDAD, CONF_VALOR, CONF_POSIBLES, CONF_AYUDA) VALUES ('CARTERA', 'CLIENTES', 'ASIGNAR CODIGO DEL CLIENTE AUTOMATICAMENTE COMO CONSECUTIVO', 'NO', 'SI,NO', 'SI:Se asigna un consecutivo de 6 dígitos como codigo del cliente. NO:Se asigna inicialmente el NIT como codigo y se puede modificar manualmente luego');
COMMIT WORK;

CREATE SEQUENCE ID_CLIECOD;
ALTER SEQUENCE ID_CLIECOD RESTART WITH 1;

SET TERM ^ ;

CREATE OR ALTER TRIGGER CLIENTES_BI FOR CLIENTES
ACTIVE BEFORE INSERT POSITION 0
as
declare variable NUMCLI VARCHAR(2);
declare variable CUPO NUMERIC(18,2);
declare variable DIAS INTEGER;
declare variable UNICOB VARCHAR(2);
declare variable CLICONT VARCHAR(2);
declare variable CODCONS VARCHAR(2);
declare variable AGENCIA INTEGER;
begin
if (new.clie_fecha is null) then
    new.clie_fecha = cast('now' as DATE);
if (new.clie_diasbloq is null) then
    new.clie_diasbloq = 1;
if (new.clie_contado is null) then
    begin
    execute procedure lee_configuracion ('CARTERA','CLIENTES','CREAR DE FORMA PREDETERMINADA LOS CLIENTES COMO DE CONTADO') returning_values (:CLICONT);
    if (CLICONT = 'SI') then
        new.clie_contado = 'S';
    else
        new.clie_contado = 'N';
    end
if (new.clie_formap is null) then
    new.clie_formap = 1;

if (new.vend_cod is null) then
    SELECT VEND_COD FROM USUARIO WHERE USER_COD = USER INTO NEW.vend_cod;
if (new.vend_cod is null) then
    SELECT first 1 VEND_COD FROM vendedores v where v.vend_estado = 'A' INTO NEW.vend_cod;

execute procedure lee_configuracion ('CARTERA','COMISIONES','UNIFICAR CODIGOS DE VENDEDORES Y COBRADORES') returning_values (:UNICOB);
if (UNICOB = 'SI') then
    NEW.cobr_cod = NEW.vend_cod;
execute procedure lee_configuracion ('CARTERA','CLIENTES','ASIGNAR CODIGO DEL CLIENTE AUTOMATICAMENTE COMO CONSECUTIVO') returning_values (:CODCONS);
if (CODCONS = 'SI') then
    BEGIN
    NEW.clie_cod = LPAD(CAST(GEN_ID(ID_CLIECOD, 1) AS VARCHAR(20)), 6, '0');
    END
if ((new.clie_rtcree is null) or (NEW.clie_rtcree = 0)) then
    execute procedure lee_configuracion ('CARTERA', 'SALDOS', 'PORCENTAJE DE RETENCION CREE PREDETERMINADO') returning_values (NEW.clie_rtcree);

if ((new.clie_cupo is null) or (NEW.clie_cupo = 0)) then
    begin
    execute procedure lee_configuracion ('CARTERA', 'GENERAL', 'CUPO DE CREDITO PREDETERMINADO AL CREAR CLIENTES') returning_values (:cupo);
    new.clie_cupo = :CUPO;
    end
if ((new.clie_dias is null) or (NEW.clie_dias = 0)) then
    begin
    execute procedure lee_configuracion ('CARTERA', 'GENERAL', 'DIAS DE CREDITO PREDETERMINADO AL CREAR CLIENTES') returning_values (:dias);
    new.clie_dias = :dias;
    end
if ((new.lipr_cod is null) or (NEW.lipr_cod = 0)) then
    begin
    select sucu_id from usuario where user_cod = user into :agencia;
    if ((agencia is null) or (agencia = 0)) then
        select first 1 lipr_cod from lista_precios where lipr_predet = 'S' into new.lipr_cod;
    else
        select first 1 lipr_cod from lista_precios where lipr_predet = 'S' and sucu_id = :agencia into new.lipr_cod;
    end
if (NEW.zona_cod IS NULL) then
    SELECT FIRST 1 ZONA_COD FROM ZONAS Z, USUARIO U WHERE U.sucu_id = Z.sucu_id AND U.user_cod = USER INTO NEW.zona_cod;
if (new.zona_cod is null) then
    SELECT first 1 ZONA_COD FROM zonas z INTO NEW.zona_cod;
execute procedure lee_configuracion ('CARTERA', 'GENERAL', 'NUMERAR CLIENTES DE FORMA CONSECUTIVA AUTOMATICAMENTE') returning_values (:NUMCLI);
if (NUMCLI = 'SI') then
    SELECT MAX(CAST(CLIE_COD AS bigINT))+1 FROM CLIENTES INTO NEW.clie_cod;
end^

SET TERM ; ^

/* solo para numerar los clientes actuales
EXECUTE BLOCK
AS
DECLARE VARIABLE NIT VARCHAR(20);
BEGIN
  FOR SELECT ID_CLIENTE 
      FROM CLIENTES 
      INTO :NIT
  DO
  BEGIN
    vContador = vContador + 1;
    UPDATE CLIENTES 
        SET CLIE_COD = LPAD(CAST(GEN_ID(ID_CLIECOD, 1) AS VARCHAR(20)), 6, '0');
    WHERE TERC_NIT = :NIT;
  END
END
*/