-- 
-- 
-- 
-- @author Dave Bauer (dave@thedesignexperience.org)
-- @creation-date 2004-12-18
-- @arch-tag: 6f2a0457-27c9-4895-8612-326fa1295799
-- @cvs-id $Id: upgrade-5.1.4d1-5.1.4d2.sql,v 1.2 2005/01/13 13:54:39 jeffd Exp $
--

begin

	acs_object_type.create_type (
		supertype => 'acs_object',
		object_type => 'survey_predefined_question',
		pretty_name => 'Predefined Survey Question',
		pretty_plural => 'Predefined Survey Questions',
		table_name => 'SURVEY_PREDEFINED_QUESTIONS',
		id_column => 'PREDEFINED_QUESTION_ID'
	);
	acs_object_type.create_type (
		supertype => 'acs_object',
		object_type => 'survey_predefined_question',
		pretty_name => 'Predefined Survey Question',
		pretty_plural => 'Predefined Survey Questions',
		table_name => 'SURVEY_PREDEFINED_QUESTIONS',
		id_column => 'PREDEFINED_QUESTION_ID'
	);

end;
/
show errors

create table survey_templates (
-- until we got a better solution
	template_id	integer primary key,
	template_file  	varchar(400) unique,
	description	varchar(4000)
);

insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'plain','Plain');
insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'standard','Standard Blue');
insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'standard-lars','Silver');
insert into survey_templates(template_id,template_file,description) values (acs_object_id_seq.nextval,'two-column','Two Column');

alter table surveys add (public_p char(1) default 't'
				constraint surveys_public_p_ck
				check(public_p in ('t','f')));

alter table surveys add (status_bar_color varchar(100) default null
				constraint surveys_status_bar_colors
				check(status_bar_color in ('blue','red','green','yellow')));

alter table surveys add (template integer references survey_templates(template_id));

alter table surveys alter description drop not null;
alter table survey_sections alter description drop not null;
alter table survey_sections add (sort_key		integer default 0 not null,
	branch_p		char(1) default 'f'
				constraint survey_sections_branch_p_ck
				check (branch_p in ('t','f')));
alter table survey_sections add	(branched_p		char(1) default 'f'
				constraint survey_sections_branched_p_ck
				check (branched_p in ('t','f')));
alter table survey_sections add	(block_section_p		char(1) default 'f'
                                constraint survey_sections_block_p_ck
                                check (block_section_p in ('t','f')));
alter table survey_sections add	(page_break_p		char(1) default 't'
                                constraint survey_sections_break_p_ck
                                check (page_break_p in ('t','f')));
alter table survey_sections add pretty_id varchar(100);
alter table survey_sections add constraint survey_sections_pretty_id_un unique (pretty_id, section_id);


create table survey_predefined_questions (
	predefined_question_id	constraint survey_pq_pquestion_id_fk
                                references acs_objects (object_id)
                                constraint survey_pq_pquestion_id_pk
                                primary key,
	question_text		clob
				constraint survey_pq_question_text_nn
				not null,
	question_html_p		char(1) default 'f'
                                constraint survey_pq_question_html_p_ck
				check(question_html_p in ('t','f')),
        abstract_data_type      varchar(30)
				constraint survey_pq_abs_data_type_ck
				check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice', 'date','blob')),
	presentation_type	varchar(20)
				constraint survey_pq_pres_type_nn
				not null
				constraint survey_pq_pres_type_ck
				check(presentation_type in ('textbox','textarea','select','radio', 'checkbox', 'date', 'upload_file')),
	-- for text, "small", "medium", "large" sizes
	-- for textarea, "rows=X cols=X"
	presentation_options	varchar(50),
	presentation_alignment	varchar(15)
				constraint survey_pq_pres_alignment_ck
            			check(presentation_alignment in ('below','beside')),
	summary_type		varchar(20)
				constraint survey_pq_summary_type check
				(summary_type in ('sum','avg','concat')),
	action_type		varchar(20)
                                constraint survey_pq_action_type_ck
                                check(action_type in ('tcl','db')),
	tcl			clob,
	table_name		varchar(100),
	column_name		varchar(100),
	key_name		varchar(100)
	);
 

create table survey_predef_question_choices (
	choice_id       integer constraint survey_pqc_choice_id_nn
                        not null
                        constraint survey_pqc_choice_id_pk
                        primary key,
        question_id     constraint survey_pqc_question_id_nn
                        not null
                        constraint survey_pqc_question_id_fk
                        references survey_predefined_questions,
        -- human readable
        label           varchar(500) constraint survey_pqc_label_nn
                        not null,
        -- might be useful for averaging or whatever, generally null
        numeric_value   number,
	-- lower is earlier
        sort_order      integer,
        presentation_alignment  varchar(15) default 'right'
                        constraint survey_pqc_pres_align_ck
                        check (presentation_alignment in ('left','right')),
        more_info_type  varchar(30) default ''
                        constraint survey_pqc_more_info_ck
                        check (more_info_type in ('','varchar','number'))
);

alter table survey_questions add (question_html_p		char(1) default 'f'
                                constraint survey_q_question_html_p_ck
				check(question_html_p in ('t','f')));
alter table survey_questions add (summary_type		varchar(20)
				constraint survey_q_summary_type check
				(summary_type in ('sum','avg','concat')));
alter table survey_questions add (answer_description	varchar(100),
	predefined_question_id	constraint survey_q_pred_q_id_fk
				references survey_predefined_questions);
alter table survey_questions add pretty_id varchar(100);
alter table survey_questions add constraint survey_q_pretty_id_un unique (pretty_id, section_id);


create table survey_block_sections (
	block_section_id        integer primary key,
	section_id              integer
                                constraint survey_b_s_section_id_fk
                                references survey_sections,
	answer_description	varchar(400)
);

create table survey_block_questions (
	block_section_id 	integer
                                constraint survey_block_sec_fk
                                foreign key (block_section_id)
                                references survey_block_sections,
	choice_id 		integer
				constraint survey_b_q_choice_id_nn
				not null,
	label 			varchar(200),
	sort_order 		integer
				constraint survey_b_q_sort_order_nn
				not null
);
create sequence survey_block_section_id_seq;
create sequence survey_block_choice_id_seq;


alter table survey_question_choices add (predef_choice_id constraint survey_qc_predef_choice_id_fk
			references survey_predef_question_choices);
alter table survey_question_choices add (presentation_alignment	varchar(15) default 'right'
			constraint survey_qc_pres_align_ck
			check (presentation_alignment in ('left','right')));
alter table survey_question_choices add (more_info_type	varchar(30) default ''
			constraint survey_qc_more_info_ck
			check (more_info_type in ('','varchar','number')));

create sequence survey_branch_id_sequence start with 1;
create sequence survey_condition_id_sequence start with 1;

create table survey_conditions (
       condition_id             integer 
       				constraint survey_cond_pk
       				primary key,
       question_id              constraint survey_cond_question_id_nn
				not null
				constraint survey_cond_question_id_fk
				references survey_questions,
       -- like question_responses
       choice_id 		constraint survey_cond_choice_id_fk
       				references survey_question_choices,
       boolean_answer           char(1)
                                constraint survey_cond_boolean_answer_ck
				check(boolean_answer in ('t','f'))
);

create table survey_branches (
       branch_id               integer
       			       constraint survey_branches_pk
			       primary key,
       section_id              constraint survey_branches_section_id_fk
			       references survey_sections,
       after                   constraint survey_branches_after_nn
			       not null
			       constraint survey_branches_after_fk
			       references survey_sections,
       -- null means always
       condition               constraint survey_branches_condition_fk
			       references survey_conditions
);

alter table survey_responses add (finished_p		char(1) default 'f'
				constraint survey_resp_finished_p_ck
				check (finished_p in ('t','f')));

alter table survey_question_responses add (attachment_file_name    varchar(500));
alter table survey_question_responses add (attachment_file_type    varchar(100));
alter table survey_question_responses add (attachment_file_extension varchar(50),
	constraint surv_quest_resp_uq unique(response_id,question_id,choice_id));


create or replace package survey
as
    function new (
        survey_id in surveys.survey_id%TYPE default null,
        name in surveys.name%TYPE,
        description in surveys.description%TYPE,
        description_html_p in surveys.description_html_p%TYPE default 'f',
        single_response_p in surveys.single_response_p%TYPE default 'f',
        editable_p in surveys.editable_p%TYPE default 't',
        enabled_p in surveys.enabled_p%TYPE default 'f',
        single_section_p in surveys.single_section_p%TYPE default 't',
        type in surveys.type%TYPE default 'general',
        display_type in surveys.display_type%TYPE default 'list',
        package_id in surveys.package_id%TYPE,
        object_type in acs_objects.object_type%TYPE default 'survey',
        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,
        public_p in surveys.public_p%TYPE default 't'
) return acs_objects.object_id%TYPE;

    procedure remove (
        survey_id in surveys.survey_id%TYPE
    );

    function name (
        survey_id in surveys.survey_id%TYPE
    ) return varchar;

end survey;
/
show errors



-- survey_section

create or replace package survey_section
as
    function new (
	section_id in survey_sections.section_id%TYPE default null,
	survey_id in survey_sections.survey_id%TYPE default null,
	name in survey_sections.name%TYPE default null,
 	description in survey_sections.description%TYPE default null,
	description_html_p in survey_sections.description_html_p%TYPE default null,
        object_type in acs_objects.object_type%TYPE default 'survey_section',
        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,
        sort_key in survey_sections.sort_key%TYPE default 1,
        branch_p in survey_sections.branch_p%TYPE default 'f',
        branched_p in survey_sections.branched_p%TYPE default 'f',
        block_section_p in survey_sections.block_section_p%TYPE default 'f',
        page_break_p in survey_sections.page_break_p%TYPE default 't'
    )  return acs_objects.object_id%TYPE;

    procedure remove (
	section_id in survey_sections.section_id%TYPE
    );
end survey_section;
/
show errors

--
-- constructor for a predefined question
--

create or replace package survey_predefined_question as

    function new (
        question_id in survey_predefined_questions.question_id%TYPE default null,
        section_id in survey_predefined_questions.section_id%TYPE default null,
        sort_order in survey_predefined_questions.sort_order%TYPE default null,
        question_text in survey_predefined_questions.question_text%TYPE default null,
        abstract_data_type in survey_predefined_questions.abstract_data_type%TYPE default null,
        required_p in survey_predefined_questions.required_p%TYPE default 't',
        active_p in survey_predefined_questions.active_p%TYPE default 't',
        presentation_type in survey_predefined_questions.presentation_type%TYPE default null,
        presentation_options in survey_predefined_questions.presentation_options%TYPE default null,
        presentation_alignment in survey_predefined_questions.presentation_alignment%TYPE default 'below',
        object_type in acs_objects.object_type%TYPE default 'survey_predefined_question',
        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,
        question_html_p in survey_predefined_questions.%TYPE,
        action_type in survey_predefined_questions.action_type%TYPE null,
        tcl in survey_predefined_questions.tcl%TYPE default null,
        table_name in survey_predefined_questions.table_name%TYPE default null,
        column_name in survey_predefined_questions.column_name%TYPE default null,
        key_name in survey_predefined_questions.key_name%TYPE default null,
        summary_type in survey_predefined_questions.summary_type%TYPE default null
    ) return acs_objects.object_id%TYPE;

end;
/
show errors

--
-- constructor for a survey_question
--

create or replace package survey_question
as
    function new (
        question_id in survey_questions.question_id%TYPE default null,
        section_id in survey_questions.section_id%TYPE default null,
        sort_order in survey_questions.sort_order%TYPE default null,
        question_text in survey_questions.question_text%TYPE default null,
        abstract_data_type in survey_questions.abstract_data_type%TYPE default null,
        required_p in survey_questions.required_p%TYPE default 't',
        active_p in survey_questions.active_p%TYPE default 't',
        presentation_type in survey_questions.presentation_type%TYPE default null,
        presentation_options in survey_questions.presentation_options%TYPE default null,
        presentation_alignment in survey_questions.presentation_alignment%TYPE default 'below',
        object_type in acs_objects.object_type%TYPE default 'survey_question',
        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,
        question_html_p in survey_questions.question_html_p%TYPE default 'f',
        summary_type in survey_questions.summary_type%TYPE default null,
        predefined_question_id in survey_questions.predefined_question_id%TYPE default null
    ) return acs_objects.object_id%TYPE;

    procedure remove (
        question_id in survey_questions.question_id%TYPE
    );
end survey_question;
/
show errors


--
-- constructor for a survey_response
--
create or replace package survey_response
as
    function new (
        response_id in survey_responses.response_id %TYPE default null,
        survey_id in survey_responses.survey_id%TYPE default null,
        title in survey_responses.title%TYPE default null,
        notify_on_comment_p in survey_responses.notify_on_comment_p%TYPE default 'f',
        object_type in acs_objects.object_type%TYPE default 'survey_response',
        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,
	initial_response_id in survey_responses.initial_response_id%TYPE default null
    ) return acs_objects.object_id%TYPE;

    function initial_response_id (
	response_id in survey_responses.response_id%TYPE
    ) return survey_responses.response_id%TYPE;

    function initial_user_id (
	response_id in survey_responses.response_id%TYPE
    ) return acs_objects.creation_user%TYPE;

    procedure remove (
        response_id in survey_responses.response_id%TYPE
    );

    procedure del (
        response_id in survey_responses.response_id%TYPE
    );

    function boolean_answer (
        answer varchar,
	question_id survey_questions.question_id%TYPE
    ) return varchar;

end survey_response;
/
show errors


-- next we define the package bodies

create or replace package body survey
as
    function new (
        survey_id in surveys.survey_id%TYPE default null,
        name in surveys.name%TYPE,
        description in surveys.description%TYPE,
        description_html_p in surveys.description_html_p%TYPE default 'f',
        single_response_p in surveys.single_response_p%TYPE default 'f',
        editable_p in surveys.editable_p%TYPE default 't',
        enabled_p in surveys.enabled_p%TYPE default 'f',
	single_section_p in surveys.single_section_p%TYPE default 't',
        type in surveys.type%TYPE default 'general',
        display_type in surveys.display_type%TYPE default 'list',
        package_id in surveys.package_id%TYPE,
        object_type in acs_objects.object_type%TYPE default 'survey',
        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,
        public_p in surveys.public_p%TYPE default 't'
    ) return acs_objects.object_id%TYPE
    is
        v_survey_id surveys.survey_id%TYPE;
    begin
        v_survey_id := acs_object.new (
            object_id => survey_id,
            object_type => object_type,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip => creation_ip,
            context_id => context_id
        );
        insert into surveys
            (survey_id, name, description, description_html_p,
            single_response_p, editable_p, enabled_p, single_section_p, type, display_type, package_id,public_p)
            values
            (v_survey_id, new.name, new.description, new.description_html_p,
            new.single_response_p, new.editable_p, new.enabled_p, new.single_section_p, new.type, new.display_type, new.package_id,new.public_p);

        return v_survey_id;
    end new;

    procedure remove (
        survey_id surveys.survey_id%TYPE
    )
    is
	v_response_row survey_responses%ROWTYPE;
	v_section_row survey_sections%ROWTYPE;
    begin

	for v_response_row in (select response_id
		from survey_responses
		where survey_id=remove.survey_id
		and initial_response_id is NULL) loop
		survey_response.remove(v_response_row.response_id);
	end loop;

	for v_section_row in (select section_id
		from survey_sections
		where survey_id=remove.survey_id) loop
	survey_section.remove(v_section_row.section_id);
	end loop;
	
	delete from surveys where survey_id=remove.survey_id;
	acs_object.del(survey_id);
    end remove;

    function name (
        survey_id in surveys.survey_id%TYPE
     ) return varchar
     is
         v_name surveys.name%TYPE;
     begin
        select name
        into v_name
        from surveys
        where survey_id = name.survey_id;

        return v_name;
    end name;
end survey;
/
show errors


create or replace package body survey_section
as
    function new (
	section_id in survey_sections.section_id%TYPE default null,
	survey_id in survey_sections.survey_id%TYPE default null,
	name in survey_sections.name%TYPE default null,
 	description in survey_sections.description%TYPE default null,
	description_html_p in survey_sections.description_html_p%TYPE default null,
        object_type in acs_objects.object_type%TYPE default 'survey_section',
        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,
        sort_key in survey_sections.sort_key%TYPE default 1,
        branch_p in survey_sections.branch_p%TYPE default 'f',
        branched_p in survey_sections.branched_p%TYPE default 'f',
        block_section_p in survey_sections.block_section_p%TYPE default 'f',
        page_break_p in survey_sections.page_break_p%TYPE default 't'
    )  return acs_objects.object_id%TYPE
    is
        v_section_id survey_sections.section_id%TYPE;
    begin
        v_section_id := acs_object.new (
            object_id => section_id,
            object_type => object_type,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip => creation_ip,
            context_id => context_id
        );
        insert into survey_sections
            (section_id, survey_id, name, description, description_html_p,sort_key,branch_p,branched_p,block_section_p,page_break_p)
            values
            (v_section_id, new.survey_id, new.name, new.description, new.description_html_p,new.sort_key,new.branch_p,new.branched_p,new.block_section_p,new.page_break_p);

        return v_section_id;
    end new;

    procedure remove (
	section_id in survey_sections.section_id%TYPE
    ) is
	v_question_row survey_questions%ROWTYPE;
    begin
	for v_question_row in (select question_id
		from survey_questions
		where section_id=remove.section_id) loop
		survey_question.remove(v_question_row.question_id);
	end loop;
	delete from survey_sections where section_id=remove.section_id;
	acs_object.del(remove.section_id);
    end remove;
end survey_section;
/
show errors
create or replace package body survey_predefined_question
as
    function new (
        predefined_question_id in survey_predefined_questions.predefined_question_id%TYPE default null,
        section_id in survey_predefined_questions.section_id%TYPE default null,
        sort_order in survey_predefined_questions.sort_order%TYPE default null,
        question_text in survey_predefined_questions.question_text%TYPE default null,
        abstract_data_type in survey_predefined_questions.abstract_data_type%TYPE default null,
        required_p in survey_predefined_questions.required_p%TYPE default 't',
        active_p in survey_predefined_questions.active_p%TYPE default 't',
        presentation_type in survey_predefined_questions.presentation_type%TYPE default null,
        presentation_options in survey_predefined_questions.presentation_options%TYPE default null,
        presentation_alignment in survey_predefined_questions.presentation_alignment%TYPE default 'below',
        object_type in acs_objects.object_type%TYPE default 'survey_predefined_question',
        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,
        question_html_p in survey_predefined_questions.question_html_p%TYPE default 'f',
        action_type in survey_predefined_questions.action_type%TYPE null,
        tcl in survey_predefined_questions.tcl%TYPE default null,
        table_name in survey_predefined_questions.table_name%TYPE default null,
        column_name in survey_predefined_questions.column_name%TYPE default null,
        key_name in survey_predefined_questions.key_name%TYPE default null,
        summary_type in survey_predefined_questions.summary_type%TYPE default null
    ) return acs_objects.object_id%TYPE
    is
        v_predefined_question_id survey_predefined_questions.predefined_question_id%TYPE;
    begin
        v_predefined_question_id := acs_object.new (
            object_id => question_id,
            object_type => object_type,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip => creation_ip,
            context_id => section_id
        );
        insert into survey_predefined_questions
            (question_id, section_id, sort_order, question_text, abstract_data_type,
            required_p, active_p, presentation_type, presentation_options,
            presentation_alignment,question_html_p,action_type,tcl,table_name,column_name,key_name)
            values
            (v_question_id, new.section_id, new.sort_order, new.question_text, new.abstract_data_type,
            new.required_p, new.active_p, new.presentation_type, new.presentation_options,
            new.presentation_alignment,new.question_html_p,new.action_type,new.tcl,new.table_name,new.column_name,new.key_name);
        return v_question_id;
    end new;
end survey_predefined_question;
/
show errors;

create or replace package body survey_question
as
    function new (
        question_id in survey_questions.question_id%TYPE default null,
        section_id in survey_questions.section_id%TYPE default null,
        sort_order in survey_questions.sort_order%TYPE default null,
        question_text in survey_questions.question_text%TYPE default null,
        abstract_data_type in survey_questions.abstract_data_type%TYPE default null,
        required_p in survey_questions.required_p%TYPE default 't',
        active_p in survey_questions.active_p%TYPE default 't',
        presentation_type in survey_questions.presentation_type%TYPE default null,
        presentation_options in survey_questions.presentation_options%TYPE default null,
        presentation_alignment in survey_questions.presentation_alignment%TYPE default 'below',
        object_type in acs_objects.object_type%TYPE default 'survey_question',
        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,
        question_html_p in survey_questions.question_html_p%TYPE default 'f',
        summary_type in survey_questions.summary_type%TYPE default null,
        predefined_question_id in survey_questions.predefined_question_id%TYPE default null
    ) return acs_objects.object_id%TYPE
    is
        v_question_id survey_questions.question_id%TYPE;
    begin
        v_question_id := acs_object.new (
            object_id => question_id,
            object_type => object_type,
            creation_date => creation_date,
            creation_user => creation_user,
            creation_ip => creation_ip,
            context_id => section_id
        );
        insert into survey_questions
            (question_id, section_id, sort_order, question_text, abstract_data_type,
            required_p, active_p, presentation_type, presentation_options,
            presentation_alignment,question_html_p,summary_type,predefined_question_id)
            values
            (v_question_id, new.section_id, new.sort_order, new.question_text, new.abstract_data_type,
            new.required_p, new.active_p, new.presentation_type, new.presentation_options,
            new.presentation_alignment,new.question_html_p,new.summary_type,new.predefined_question_id);
        return v_question_id;
    end new;

    procedure remove (
        question_id in survey_questions.question_id%TYPE
    )
    is
    begin
	
	delete from survey_question_responses
	where question_id=remove.question_id;
	delete from survey_question_choices
	where question_id=remove.question_id;
	delete from survey_questions
            where question_id = remove.question_id;
        acs_object.del(remove.question_id);
    end remove;
end survey_question;
/
show errors


	v_split_pos integer;
    begin
    
    if answer is NOT NULL then
        select presentation_options into v_presentation_options
	from survey_questions where question_id=boolean_answer.question_id;
	
	v_split_pos:= instr(v_presentation_options, '/');
	
	if answer = 't' then
	    v_answer:=substr(v_presentation_options, 1, v_split_pos -1 );
	end if;   
	if answer = 'f' then
	    v_answer:=substr(v_presentation_options, v_split_pos + 1 );
	end if;
	
    else
        v_answer := '';
    end if;
    return v_answer;
    end boolean_answer;

end survey_response;
/
show errors

-- these views depend on functions in this file -DaveB
-- this view contains only the most recently edited version
-- of each survey response.

create or replace view survey_responses_latest as
select sr.*, o.creation_date, 
       o.creation_user as initial_user_id
  from survey_responses sr,
  acs_objects o,
  (select max(response_id) as response_id
          from survey_responses
         group by nvl(initial_response_id, response_id)) latest
  where nvl(sr.initial_response_id,sr.response_id) = o.object_id
  and sr.response_id= latest.response_id;

create or replace view survey_ques_responses_latest as
select qr.*
  from survey_question_responses qr, survey_responses_latest r
 where qr.response_id=r.response_id;  

create or replace function survey_question_all_choices
(v_question_id in integer) return varchar
is
	v_answers varchar(4000);
	first_p integer;
	cursor choice_cursor is
	select label
	from survey_question_choices
	where question_id = v_question_id
	order by sort_order;
begin
	v_answers := '';
	first_p := 0;
	for choice_rec in choice_cursor loop
	   if first_p = 0 then
	     v_answers := choice_rec.label;
	     first_p := 1;
     	   else
	     v_answers := v_answers || ',' || choice_rec.label;
	   end if;
	end loop;

	return v_answers;
end;
/
show errors
