/****** Object:  Stored Procedure dbo.ujo_put_jobrow    Script Date: 12/1/2005 11:49:50 PM ******/


create proc ujo_put_jobrow

@insert        int,
@joid         int,
@job_name    varchar(64),
@job_type    char(1),
@owner        varchar(80),
@permission    varchar(30),
@box_joid    int,
@box_name    varchar(64),
@machine    varchar(80),
@n_retrys    int,
@date_conditions    tinyint,
@days_of_week    varchar(80),
@run_calendar    varchar(30),
@exclude_calendar    varchar(30),
@start_times    varchar(255),
@start_mins    varchar(255),
@run_window    varchar(20),
@command    varchar(255),
@description    varchar(255),
@term_run_time    int,
@box_terminator    tinyint,
@job_terminator    tinyint,
@std_in_file    varchar(255),
@std_out_file    varchar(255),
@std_err_file    varchar(255),
@watch_file    varchar(255),
@watch_file_min_size    int,
@watch_interval    int,
@min_run_alarm    int,
@max_run_alarm    int,
@alarm_if_fail    tinyint,
@chk_files    varchar(255),
@free_procs    int,
@profile    varchar(255),
@heartbeat_interval    int,
@auto_hold    tinyint,
@job_load        int,
@priority    int,
@auto_delete    int,
@numero        int,
@max_exit_success    int,
@box_success    varchar(255),
@box_failure    varchar(255),
@avg_runtime    int,
@command2    varchar(255),
@external_app    varchar(40),
@timezone        varchar(50),
@create_modify_user    varchar(80),
@as_applic     varchar(64),
@as_group    varchar(64),
@send_notification  char(1),
@service_desk int

AS

declare @boxid int

if  @box_name = ''
begin
    set @boxid = NULL
end

else
begin
    select @boxid = joid from ujo_job
    where job_name = @box_name AND job_type = 'b'

    if (@@rowcount = 0 )
    begin
        return -1
    end

end

set @box_joid = @boxid


if @insert = 1
BEGIN

    if ( select TOP 1 1 from ujo_job where job_name=@job_name ) = 1
    BEGIN
        return -2
    END
    INSERT INTO ujo_job(
     joid
    ,job_name
    ,job_type    
    ,owner
    ,permission
    ,box_joid    
    ,machine    
    ,n_retrys    
    ,date_conditions
    ,days_of_week
    ,run_calendar
    ,exclude_calendar
    ,start_times
    ,start_mins
    ,run_window
    ,command    
    ,description    
    ,term_run_time    
    ,box_terminator    
    ,job_terminator    
    ,std_in_file
    ,std_out_file    
    ,std_err_file    
    ,watch_file
    ,watch_file_min_size
    ,watch_interval
    ,min_run_alarm    
    ,max_run_alarm    
    ,alarm_if_fail    
    ,chk_files
    ,free_procs
    ,profile
    ,heartbeat_interval
    ,auto_hold
    ,job_load
    ,priority
    ,auto_delete
    ,numero
    ,max_exit_success
    ,box_success
    ,box_failure
    ,send_notification
    ,service_desk
    )
    VALUES (
     @joid
    ,@job_name    
    ,@job_type    
    ,@owner
    ,@permission
    ,@box_joid    
    ,@machine    
    ,@n_retrys    
    ,@date_conditions
    ,@days_of_week
    ,@run_calendar
    ,@exclude_calendar
    ,@start_times
    ,@start_mins
    ,@run_window
    ,@command        
    ,@description
    ,@term_run_time
    ,@box_terminator
    ,@job_terminator
    ,@std_in_file
    ,@std_out_file    
    ,@std_err_file
    ,@watch_file
    ,@watch_file_min_size
    ,@watch_interval    
    ,@min_run_alarm    
    ,@max_run_alarm    
    ,@alarm_if_fail    
    ,@chk_files
    ,@free_procs
    ,@profile
    ,@heartbeat_interval
    ,@auto_hold
    ,@job_load
    ,@priority
    ,@auto_delete
    ,@numero
    ,@max_exit_success
    ,@box_success
    ,@box_failure
    ,@send_notification
    ,@service_desk
    )
    if @@rowcount = 0
        return -1

    /* Setup the job2 table */

    INSERT INTO ujo_job2 (
    joid,
    command2,
    external_app,
    timezone,
    create_user,
    create_stamp,
    modify_user,
    modify_stamp,
    as_applic,
    as_group    
    )
    VALUES (
    @joid,
    @command2,    
    @external_app,
    @timezone,    
    @create_modify_user,
    getdate(),
    @create_modify_user,
    getdate(),
    @as_applic,
    @as_group
    
    )

    /* stub out some rows so the things work! */
    /* start with status = 8 (INACTIVE)       */

    INSERT INTO ujo_job_status ( joid, status, status_time,
        last_start, last_end, next_start, run_priority, exit_code,
        ntry, run_num, pid, jc_pid )
    VALUES ( @joid, 8, 0,0,0, 0,-1,
                 -656, 0, 0, -1, -1)
END

ELSE
BEGIN
    /* Its an UPDATE  */

    UPDATE ujo_job
    SET

     job_name = @job_name    
    ,job_type = @job_type    
    ,owner = @owner
    ,permission = @permission
    ,box_joid = @box_joid    
    ,machine = @machine    
    ,n_retrys = @n_retrys    
    ,date_conditions = @date_conditions
    ,days_of_week = @days_of_week
    ,run_calendar = @run_calendar
    ,exclude_calendar = @exclude_calendar
    ,start_times = @start_times
    ,start_mins = @start_mins
    ,run_window = @run_window
    ,command = @command        
    ,description = @description
    ,term_run_time = @term_run_time
    ,box_terminator = @box_terminator
    ,job_terminator = @job_terminator
    ,std_in_file = @std_in_file
    ,std_out_file = @std_out_file    
    ,std_err_file = @std_err_file
    ,watch_file = @watch_file
    ,watch_file_min_size = @watch_file_min_size
    ,watch_interval = @watch_interval    
    ,min_run_alarm = @min_run_alarm    
    ,max_run_alarm = @max_run_alarm    
    ,alarm_if_fail = @alarm_if_fail    
    ,chk_files = @chk_files
    ,free_procs = @free_procs
    ,profile = @profile
    ,heartbeat_interval = @heartbeat_interval
    ,auto_hold = @auto_hold
    ,job_load = @job_load
    ,priority = @priority
    ,auto_delete = @auto_delete
    ,numero = @numero
    ,max_exit_success = @max_exit_success
    ,box_success = @box_success
    ,box_failure = @box_failure
    ,send_notification = @send_notification
    ,service_desk = @service_desk

    WHERE    joid = @joid


    if @@rowcount = 0
        return -1

    UPDATE  ujo_job2
    SET
    command2 = @command2,    
    external_app = @external_app,
    timezone = @timezone,    
    modify_user = @create_modify_user,
    modify_stamp = getdate(),
    as_applic = @as_applic,
    as_group = @as_group    

    WHERE   joid = @joid

END

/* See if we gotta set the avg_runtime */

if @avg_runtime > 0
BEGIN
    DELETE ujo_avg_job_runs WHERE joid=@joid
    INSERT ujo_avg_job_runs( joid, avg_runtime, num_runs )
    VALUES ( @joid, @avg_runtime, -1 ) /* -1 sez it wuz manual */
END

return 1
GO
GRANT EXECUTE ON  [dbo].[ujo_put_jobrow] TO [ujoadmin]
GO