--
-- Cronjob in sql
-- @author tom jackson <tom@zmbh.com>
-- @creation-date 22 Sept 2001
-- @cvs-id $Id: cronjob-create.sql,v 1.3 2003/09/30 12:10:06 mohanp Exp $
--

create table cronjobs (
	cronjob_id integer not null
   constraint cj_cronjob_id_fk references acs_objects(object_id)
   constraint cj_cronjob_id_pk primary key,
  user_id integer not null
   constraint cj_user_id_fk references users,
	description varchar2(100) not null,
  approved_p char(1) default 'f' not null
   constraint cj_approved_p_ck check (approved_p in ('f','t')),
  disabled_p char(1) default 'f' not null
   constraint cj_disabled_p_ck check (disabled_p in ('f','t')),
  minute char(2)  default '0' not null,
  hr  char(2)  default '0' not null,
  mon char(2)  default '0' not null,
  day char(2)  default '0' not null,
  dayofweek char(2)  default '0' not null,
  run_sql varchar2(4000),
  run_tcl varchar2(4000),
  email   varchar2(255)
);

begin 
    acs_object_type.create_type ( 
  supertype => 'acs_object', 
  object_type => 'cronjob', 
  pretty_name => 'CronJob', 
  pretty_plural => 'CronJobs', 
  table_name => 'cronjobs',  
  id_column => 'cronjob_id' 
    ); 
end; 
/ 
show errors; 




create or replace package cronjob
as
    function cronjob_p (
      cronjob_id  in cronjobs.cronjob_id%TYPE
    ) return char;

    function new ( 
      cronjob_id  in cronjobs.cronjob_id%TYPE default null,
      user_id  in cronjobs.user_id%TYPE,
      description  in cronjobs.description%TYPE,
      approved_p  in cronjobs.approved_p%TYPE default 'f',
      disabled_p  in cronjobs.disabled_p%TYPE,
      minute  in cronjobs.minute%TYPE default '0',
      hr  in cronjobs.hr%TYPE default '0',
      mon  in cronjobs.mon%TYPE default '0',
      day  in cronjobs.day%TYPE default '0',
      dayofweek  in cronjobs.dayofweek%TYPE default '0',
      run_sql  in cronjobs.run_sql%TYPE default null,
      run_tcl  in cronjobs.run_tcl%TYPE default null,
      email  in cronjobs.email%TYPE default null,
      object_type  in acs_objects.object_type%TYPE default 'cronjob',
      creation_date  in acs_objects.creation_date%TYPE default sysdate,
      creation_user  in acs_objects.creation_user%TYPE default null,
      creation_ip  in acs_objects.creation_ip%TYPE default null,
      context_id  in acs_objects.context_id%TYPE default null
    ) return cronjobs.cronjob_id%TYPE;

    procedure del (
      cronjob_id  in cronjobs.cronjob_id%TYPE
    );
    procedure set_attrs ( 
      cronjob_id in cronjobs.cronjob_id%TYPE,
      user_id in cronjobs.user_id%TYPE default null,
      description in cronjobs.description%TYPE default null,
      approved_p in cronjobs.approved_p%TYPE default null,
      disabled_p in cronjobs.disabled_p%TYPE default null,
      minute in cronjobs.minute%TYPE default null,
      hr in cronjobs.hr%TYPE default null,
      mon in cronjobs.mon%TYPE default null,
      day in cronjobs.day%TYPE default null,
      dayofweek in cronjobs.dayofweek%TYPE default null,
      run_sql in cronjobs.run_sql%TYPE default null,
      run_tcl in cronjobs.run_tcl%TYPE default null,
      email in cronjobs.email%TYPE default null
    );
    procedure reset_attr ( 
      cronjob_id in cronjobs.cronjob_id%TYPE,
      column_name in varchar
    );
end cronjob;
/
show errors;

create or replace package body cronjob
as
    function cronjob_p (
      cronjob_id  in cronjobs.cronjob_id%TYPE
    ) return char
    is
    -- declare vars here
      v_check_cronjob_id integer;
    begin
      select count(cronjob_id) into v_check_cronjob_id
          from cronjobs
          where cronjob_id = cronjob_p.cronjob_id;
      if v_check_cronjob_id = 1 then
          return 't';
      else
          return 'f';
      end if;
    end cronjob_p;

    -- Context ID May need adjustment to reflect security/access model.
    function new ( 
      cronjob_id  in cronjobs.cronjob_id%TYPE default null,
      user_id  in cronjobs.user_id%TYPE,
      description  in cronjobs.description%TYPE,
      approved_p  in cronjobs.approved_p%TYPE default 'f',
      disabled_p  in cronjobs.disabled_p%TYPE,
      minute  in cronjobs.minute%TYPE default '0',
      hr  in cronjobs.hr%TYPE default '0',
      mon  in cronjobs.mon%TYPE default '0',
      day  in cronjobs.day%TYPE default '0',
      dayofweek  in cronjobs.dayofweek%TYPE default '0',
      run_sql  in cronjobs.run_sql%TYPE default null,
      run_tcl  in cronjobs.run_tcl%TYPE default null,
      email  in cronjobs.email%TYPE default null,
      object_type  in acs_objects.object_type%TYPE default 'cronjob',
      creation_date  in acs_objects.creation_date%TYPE default sysdate,
      creation_user  in acs_objects.creation_user%TYPE default null,
      creation_ip  in acs_objects.creation_ip%TYPE default null,
      context_id  in acs_objects.context_id%TYPE default null
    ) return cronjobs.cronjob_id%TYPE
    is
      v_cronjob_id integer;
    begin
      v_cronjob_id := acs_object.new (
      object_id => cronjob_id,
      object_type => object_type,
      creation_date => creation_date,
      creation_user => creation_user,
      creation_ip => creation_ip,
      context_id => context_id
    );

    insert into cronjobs
      ( cronjob_id, user_id, description, approved_p, disabled_p, minute, hr, mon, day, dayofweek, run_sql, run_tcl, email )
    values
      ( v_cronjob_id, user_id, description, approved_p, disabled_p, minute, hr, mon, day, dayofweek, run_sql, run_tcl, email );

    return v_cronjob_id;
  end new;

  procedure del (
    cronjob_id  in cronjobs.cronjob_id%TYPE
  )
  is
  begin
    if cronjob_p(cronjob.del.cronjob_id) = 'f' then
       return;
    end if;

    delete from cronjobs
    where cronjob_id = cronjob.del.cronjob_id;

    acs_object.del(cronjob_id);
  end del;

  procedure set_attrs ( 
     cronjob_id in cronjobs.cronjob_id%TYPE,
     user_id in cronjobs.user_id%TYPE default null,
     description in cronjobs.description%TYPE default null,
     approved_p in cronjobs.approved_p%TYPE default null,
     disabled_p in cronjobs.disabled_p%TYPE default null,
     minute in cronjobs.minute%TYPE default null,
     hr in cronjobs.hr%TYPE default null,
     mon in cronjobs.mon%TYPE default null,
     day in cronjobs.day%TYPE default null,
     dayofweek in cronjobs.dayofweek%TYPE default null,
     run_sql in cronjobs.run_sql%TYPE default null,
     run_tcl in cronjobs.run_tcl%TYPE default null,
     email in cronjobs.email%TYPE default null
  )
  is
    -- declared vars here
  begin
    if cronjob_p(cronjob.set_attrs.cronjob_id) = 'f' then
      return;
    end if;

  -- modify parts that are not null

    if user_id is not null then
        update cronjobs set user_id = set_attrs.user_id
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if description is not null then
        update cronjobs set description = set_attrs.description
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if approved_p is not null then
        update cronjobs set approved_p = set_attrs.approved_p
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if disabled_p is not null then
        update cronjobs set disabled_p = set_attrs.disabled_p
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if minute is not null then
        update cronjobs set minute = set_attrs.minute
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if hr is not null then
        update cronjobs set hr = set_attrs.hr
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if mon is not null then
        update cronjobs set mon = set_attrs.mon
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if day is not null then
        update cronjobs set day = set_attrs.day
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if dayofweek is not null then
        update cronjobs set dayofweek = set_attrs.dayofweek
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if run_sql is not null then
        update cronjobs set run_sql = set_attrs.run_sql
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if run_tcl is not null then
        update cronjobs set run_tcl = set_attrs.run_tcl
          where cronjob_id = set_attrs.cronjob_id;
    end if;
    if email is not null then
        update cronjobs set email = set_attrs.email
          where cronjob_id = set_attrs.cronjob_id;
    end if;

  end set_attrs;
  procedure reset_attr ( 
    cronjob_id in cronjobs.cronjob_id%TYPE,
    column_name in varchar
  )
  is 
    -- declared vars here
  begin
    if cronjob_p(cronjob.reset_attr.cronjob_id) = 'f' then
      return;
    end if;
  
    if column_name = 'approved_p' then
       update cronjobs set approved_p = 'f'
       where cronjob_id = reset_attr.cronjob_id;
    end if;
    if column_name = 'minute' then
       update cronjobs set minute = '0'
       where cronjob_id = reset_attr.cronjob_id;
    end if;
    if column_name = 'hr' then
       update cronjobs set hr = '0'
       where cronjob_id = reset_attr.cronjob_id;
    end if;
    if column_name = 'mon' then
       update cronjobs set mon = '0'
       where cronjob_id = reset_attr.cronjob_id;
    end if;
    if column_name = 'day' then
       update cronjobs set day = '0'
       where cronjob_id = reset_attr.cronjob_id;
    end if;
    if column_name = 'dayofweek' then
       update cronjobs set dayofweek = '0'
       where cronjob_id = reset_attr.cronjob_id;
    end if;
    if column_name = 'run_sql' then
       update cronjobs set run_sql = NULL
       where cronjob_id = reset_attr.cronjob_id;
    end if;
    if column_name = 'run_tcl' then
       update cronjobs set run_tcl = NULL
       where cronjob_id = reset_attr.cronjob_id;
    end if;
    if column_name = 'email' then
       update cronjobs set email = NULL
       where cronjob_id = reset_attr.cronjob_id;
    end if;

  end reset_attr;

end cronjob;
/
show errors;