--
-- packages/form-to-mail/sql/postgresql/form-to-mail-functions-create.sql
--
-- @author steve@redmonk.net
-- @creation-date 2003-06-19
-- @cvs-id $Id
--
-- 
/* Since each record is also an Object, we make a creation function that will create an object and then use the object id to create a record in our table.  The function also takes several input variables.
        Form Name is required
        Form Pretty Name is required
        Title is required
        Body is required
        Required User Subject is optional and defaults to 
        Creation date is optional and defaults to now
        Creation user, required, is the user id owns the object
        Context id, required, is the id of the package instance.  This allows 
          segregation of records by package, required to make the package 
          "package-aware." 

    define_function_args prepares the function to be used by a tcl wrapper function. */ 

select define_function_args('formtomail__new','form_id,package_id,form_name,form_pretty_name,subject_prefix,subject_default,requires_user_subject,requires_user_comments,recipient,sender,confirm_msg,creation_date,creation_user,context_id'); 

create or replace function formtomail__new(
    integer,        -- form_id
    varchar,        -- form_name
    varchar,        -- form_pretty_name
    varchar,        -- subject_prefix
    varchar,        -- subject_default
    integer,        -- requires_user_subject
    integer,        -- requires_user_comments
    varchar,        -- recipient
    varchar,        -- sender
    varchar,
    timestamptz,    -- creation_date
    integer,        -- creation_user
    integer         -- context_id
) returns integer as '
declare
    p_form_id                                     alias for $1;
    p_form_name                                   alias for $2;
    p_form_pretty_name                            alias for $3;
    p_subject_prefix                              alias for $4;
    p_subject_default                             alias for $5;
    p_requires_user_subject                       alias for $6;
    p_requires_user_comments                      alias for $7;
    p_recipient                                   alias for $8;
    p_sender                                      alias for $9;
    p_confirm_msg                                 alias for $10;
    p_creation_date                               alias for $11; 
    p_creation_user                               alias for $12;
    p_context_id                                  alias for $13;
    v_formtomail_id                               int;
begin
    v_formtomail_id := acs_object__new (
            p_form_id,
            ''formtomail'',
            p_creation_date,
            p_creation_user,
            NULL,
            p_context_id
    );
    insert into formtomail ( 
        form_id, 
        package_id,
        form_name, 
        form_pretty_name, 
        subject_prefix, 
        subject_default, 
        requires_user_subject, 
        requires_user_comments, 
        recipient, 
        sender,
        confirm_msg
    ) values (
        v_formtomail_id,
        p_context_id,
        p_form_name, 
        p_form_pretty_name,
        p_subject_prefix, 
        p_subject_default, 
        p_requires_user_subject, 
        p_requires_user_comments, 
        p_recipient, 
        p_sender,
        p_confirm_msg
    );
    PERFORM acs_permission__grant_permission
    (
      v_formtomail_id,
      p_creation_user,
      ''admin''
    );
    return v_formtomail_id;
end;' language 'plpgsql'; 

/* The __delete function deletes a record and all related overhead. */ 

select define_function_args('formtomail___delete','form_id');
create or replace function formtomail__delete (integer)
returns integer as '
declare
  p_formtomail_id                               alias for $1;
begin
    delete from acs_permissions
                   where object_id = p_formtomail_id;
        delete from formtomail
                   where form_id = p_formtomail_id;
        raise NOTICE ''Deleting formtomail...'';
        PERFORM acs_object__delete(p_formtomail_id);
        return 0;
end;' language 'plpgsql'; 

/* When we created the acs object type above, we specified a
'name_method'.  This is the name of a function that will return the
name of the object.  This is a convention ensuring that all objects
can be identified.  Now we have to build that function.  In this case,
we'll return a field called title as the name. */ 

select define_function_args('formtomail___name','form_id');

create or replace function formtomail__name (integer)
returns varchar as '
declare
    p_formtomail_id      alias for $1;
    v_formtomail_name    formtomail.form_name%TYPE;
begin
        select form_name into v_formtomail_name
                from formtomail
                where form_id = p_formtomail_id;
    return v_formtomail_name;
end;
' language 'plpgsql';