Index: openacs-4/packages/survey/survey.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/survey.info,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/survey.info 15 Sep 2002 23:56:54 -0000 1.1
@@ -0,0 +1,189 @@
+
+
+
+
+ Survey
+ Surveys
+ f
+ f
+
+
+
+ oracle
+ postgresql
+
+ Dave Bauer
+ Luke Pond
+ New version of survey package for dotLRN/OpenACS4.5
+ dotLRN
+ New version of survey package for dotLRN/OpenACS4.5
+
+
+
+
+
+
+
+
Index: openacs-4/packages/survey/sql/oracle/diff
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/oracle/Attic/diff,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/oracle/diff 15 Sep 2002 23:57:20 -0000 1.1
@@ -0,0 +1,30 @@
+Index: survey-create.sql
+===================================================================
+RCS file: /usr/local/cvsroot/ssv2/packages/survey/sql/oracle/survey-create.sql,v
+retrieving revision 1.5
+diff -r1.5 survey-create.sql
+120c120
+< title varchar(4000)
+---
+> name varchar(4000)
+131c131
+< -- each question can be
+---
+> create index survey_sections_survey_id_fk on survey_sections(survey_id);
+140,141c140,141
+< sort_key integer
+< constraint survey_q_sort_key_nn
+---
+> sort_order integer
+> constraint survey_q_sort_order_nn
+167a168,170
+> create index survey_q_sort_order on survey_questions(sort_order);
+> create index survey_q_active_p on survey_questions(active_p);
+>
+189a193,195
+> create index survey_q_c_question_id on survey_question_choices(question_id);
+> create index survey_q_c_sort_order on survey_question_choices(sort_order);
+>
+231a238,239
+> create index survey_q_r_choice_id on survey_question_responses(choice_id);
+> create index survey_q_r_attachment_answer on survey_question_responses(attachment_answer);
Index: openacs-4/packages/survey/sql/oracle/survey-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/oracle/survey-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/oracle/survey-create.sql 15 Sep 2002 23:57:20 -0000 1.1
@@ -0,0 +1,242 @@
+-- based on student work from 6.916 in Fall 1999
+-- which was in turn based on problem set 4
+-- in http://photo.net/teaching/one-term-web.html
+--
+-- by philg@mit.edu and raj@alum.mit.edu on February 9, 2000
+-- converted to ACS 4.0 by nstrug@arsdigita.com on 29th September 2000
+-- modified for dotLRN/OpenACS4.5 and renamed from "simple-survey" to "survey"
+-- by dave@thedesignexperience.org on 13 July 2002
+--
+-- $Id: survey-create.sql,v 1.1 2002/09/15 23:57:20 daveb Exp $
+
+begin
+
+ acs_privilege.create_privilege('survey_create_survey');
+ acs_privilege.create_privilege('survey_modify_survey');
+ acs_privilege.create_privilege('survey_delete_survey');
+ acs_privilege.create_privilege('survey_create_question');
+ acs_privilege.create_privilege('survey_modify_question');
+ acs_privilege.create_privilege('survey_delete_question');
+ acs_privilege.create_privilege('survey_take_survey');
+
+ acs_privilege.create_privilege('survey_admin_survey');
+
+ acs_privilege.add_child('survey_admin_survey','survey_create_survey');
+ acs_privilege.add_child('survey_admin_survey','survey_modify_survey');
+ acs_privilege.add_child('survey_admin_survey','survey_delete_survey');
+ acs_privilege.add_child('survey_admin_survey','survey_create_question');
+ acs_privilege.add_child('survey_admin_survey','survey_modify_question');
+ acs_privilege.add_child('survey_admin_survey','survey_delete_question');
+
+ acs_privilege.add_child('read','survey_take_survey');
+ acs_privilege.add_child('admin','survey_admin_survey');
+
+ acs_object_type.create_type (
+ supertype => 'acs_object',
+ object_type => 'survey',
+ pretty_name => 'Survey',
+ pretty_plural => 'Surveys',
+ table_name => 'SURVEYS',
+ id_column => 'SURVEY_ID',
+ name_method => 'survey.name'
+ );
+ acs_object_type.create_type (
+ supertype => 'acs_object',
+ object_type => 'survey_section',
+ pretty_name => 'Survey_Section',
+ pretty_plural => 'Survey_Sections',
+ table_name => 'SURVEY_SECTIONS',
+ id_column => 'SECTION_ID'
+ );
+
+ acs_object_type.create_type (
+ supertype => 'acs_object',
+ object_type => 'survey_question',
+ pretty_name => 'Survey Question',
+ pretty_plural => 'Survey Questions',
+ table_name => 'SURVEY_QUESTIONS',
+ id_column => 'QUESTION_ID'
+ );
+
+ acs_object_type.create_type (
+ supertype => 'acs_object',
+ object_type => 'survey_response',
+ pretty_name => 'Survey Response',
+ pretty_plural => 'Survey Responses',
+ table_name => 'SURVEY_RESPONSES',
+ id_column => 'RESPONSE_ID'
+ );
+
+
+end;
+/
+show errors
+
+create table surveys (
+ survey_id constraint surveys_survey_id_fk
+ references acs_objects (object_id)
+ constraint surveys_pk
+ primary key,
+ name varchar(4000)
+ constraint surveys_name_nn
+ not null,
+ description varchar(4000)
+ constraint surveys_desc_nn
+ not null,
+ description_html_p char(1)
+ constraint surveys_desc_html_p_ck
+ check(description_html_p in ('t','f')),
+ enabled_p char(1)
+ constraint surveys_enabled_p_ck
+ check(enabled_p in ('t','f')),
+ -- limit to one response per user
+ single_response_p char(1)
+ constraint surveys_single_resp_p_ck
+ check(single_response_p in ('t','f')),
+ editable_p char(1)
+ constraint surveys_single_edit_p_ck
+ check(editable_p in ('t','f')),
+ single_section_p char(1)
+ constraint surveys_single_section_p_ck
+ check(single_section_p in ('t','f')),
+ type varchar(20),
+ display_type varchar(20),
+ package_id integer
+ constraint surveys_package_id_nn not null
+ constraint surveys_package_id_fk references
+ apm_packages (package_id) on delete cascade
+);
+
+create table survey_sections (
+ section_id constraint survey_sections_section_id_fk
+ references acs_objects (object_id)
+ constraint survey_sections_pk
+ primary key,
+ survey_id integer
+ constraint survey_sections_survey_id_nn
+ not null
+ constraint survey_sections_survey_id_fk
+ references surveys,
+ name varchar(4000)
+ constraint survey_sections_name_nn
+ not null,
+ description clob
+ constraint survey_sections_desc_nn
+ not null,
+ description_html_p char(1)
+ constraint survey_sections_desc_html_p_ck
+ check(description_html_p in ('t','f'))
+);
+
+create index survey_sections_survey_id_fk on survey_sections(survey_id);
+
+create table survey_questions (
+ question_id constraint survey_q_question_id_fk
+ references acs_objects (object_id)
+ constraint survey_q_question_id_pk
+ primary key,
+ section_id constraint survey_q_section_id_fk
+ references survey_sections,
+ sort_order integer
+ constraint survey_q_sort_order_nn
+ not null,
+ question_text clob
+ constraint survey_q_question_text_nn
+ not null,
+ abstract_data_type varchar(30)
+ constraint survey_q_abs_data_type_ck
+ check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice', 'date','blob')),
+ required_p char(1)
+ constraint survey_q_required_p_ck
+ check (required_p in ('t','f')),
+ active_p char(1)
+ constraint survey_q_qctive_p_ck
+ check (active_p in ('t','f')),
+ presentation_type varchar(20)
+ constraint survey_q_pres_type_nn
+ not null
+ constraint survey_q_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_q_pres_alignment_ck
+ check(presentation_alignment in ('below','beside'))
+);
+
+create index survey_q_sort_order on survey_questions(sort_order);
+create index survey_q_active_p on survey_questions(active_p);
+
+-- for when a question has a fixed set of responses
+
+create sequence survey_choice_id_sequence start with 1;
+
+create table survey_question_choices (
+ choice_id integer constraint survey_qc_choice_id_nn
+ not null
+ constraint survey_qc_choice_id_pk
+ primary key,
+ question_id constraint survey_qc_question_id_nn
+ not null
+ constraint survey_qc_question_id_fk
+ references survey_questions,
+ -- human readable
+ label varchar(500) constraint survey_qc_label_nn
+ not null,
+ -- might be useful for averaging or whatever, generally null
+ numeric_value number,
+ -- lower is earlier
+ sort_order integer
+);
+
+create index survey_q_c_question_id on survey_question_choices(question_id);
+create index survey_q_c_sort_order on survey_question_choices(sort_order);
+
+-- this records a response by one user to one survey
+-- (could also be a proposal in which case we'll do funny
+-- things like let the user give it a title, send him or her
+-- email if someone comments on it, etc.)
+create table survey_responses (
+ response_id constraint survey_resp_response_id_fk
+ references acs_objects (object_id)
+ constraint srvsimp_resp_response_id_pk
+ primary key,
+ initial_response_id constraint init_resp_id_fk
+ references survey_responses (response_id),
+ survey_id constraint survey_resp_survey_id_fk
+ references surveys,
+ title varchar(100),
+ notify_on_comment_p char(1)
+ constraint survey_resp_noton_com_p_ck
+ check(notify_on_comment_p in ('t','f'))
+);
+
+
+
+-- this table stores the answers to each question for a survey
+-- we want to be able to hold different data types in one long skinny table
+-- but we also may want to do averages, etc., so we can't just use CLOBs
+
+create table survey_question_responses (
+ response_id not null references survey_responses,
+ question_id not null references survey_questions,
+ -- if the user picked a canned response
+ choice_id references survey_question_choices,
+ boolean_answer char(1) check(boolean_answer in ('t','f')),
+ clob_answer clob,
+ number_answer number,
+ varchar_answer varchar(4000),
+ date_answer date,
+ attachment_answer integer
+ constraint survey_q_resp_rev_id_fk
+ references cr_revisions(revision_id)
+ on delete cascade
+);
+
+create index survey_response_index on survey_question_responses (response_id, question_id);
+create index survey_q_r_choice_id on survey_question_responses(choice_id);
+create index survey_q_r_attachment_answer on survey_question_responses(attachment_answer);
+
+@@ survey-package-create.sql
+@@ survey-notifications-init.sql
\ No newline at end of file
Index: openacs-4/packages/survey/sql/oracle/survey-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/oracle/survey-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/oracle/survey-drop.sql 15 Sep 2002 23:57:20 -0000 1.1
@@ -0,0 +1,60 @@
+--
+-- drop SQL for survey package
+--
+-- by nstrug@arsdigita.com on 29th September 2000
+--
+-- $Id: survey-drop.sql,v 1.1 2002/09/15 23:57:20 daveb Exp $
+
+@@ survey-package-drop.sql
+
+
+drop view survey_question_responses_un;
+drop table survey_question_responses cascade constraints;
+drop view survey_responses_unique;
+drop table survey_responses cascade constraints;
+drop table survey_question_choices cascade constraints;
+
+drop sequence survey_choice_id_sequence;
+drop table survey_questions cascade constraints;
+drop table survey_sections cascade constraints;
+drop table surveys cascade constraints;
+
+-- nuke all created objects
+-- need to do this before nuking the types
+delete from acs_objects where object_type = 'survey_response';
+delete from acs_objects where object_type = 'survey_question';
+delete from acs_objects where object_type = 'survey_section';
+delete from acs_objects where object_type = 'survey';
+
+begin
+ acs_rel_type.drop_type('user_blob_response_rel');
+
+ acs_object_type.drop_type ('survey_response');
+ acs_object_type.drop_type ('survey_question');
+ acs_object_type.drop_type ('survey_section');
+ acs_object_type.drop_type ('survey');
+
+ acs_privilege.remove_child ('admin','survey_admin_survey');
+ acs_privilege.remove_child ('read','survey_take_survey');
+ acs_privilege.remove_child ('survey_admin_survey','survey_delete_question');
+ acs_privilege.remove_child ('survey_admin_survey','survey_modify_question');
+ acs_privilege.remove_child ('survey_admin_survey','survey_create_question');
+ acs_privilege.remove_child ('survey_admin_survey','survey_delete_survey');
+ acs_privilege.remove_child ('survey_admin_survey','survey_modify_survey');
+ acs_privilege.remove_child ('survey_admin_survey','survey_create_survey');
+
+ acs_privilege.drop_privilege('survey_admin_survey');
+ acs_privilege.drop_privilege('survey_take_survey');
+ acs_privilege.drop_privilege('survey_delete_question');
+ acs_privilege.drop_privilege('survey_modify_question');
+ acs_privilege.drop_privilege('survey_create_question');
+ acs_privilege.drop_privilege('survey_delete_survey');
+ acs_privilege.drop_privilege('survey_modify_survey');
+ acs_privilege.drop_privilege('survey_create_survey');
+
+
+end;
+/
+show errors
+
+
Index: openacs-4/packages/survey/sql/oracle/survey-notifications-init.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/oracle/survey-notifications-init.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/oracle/survey-notifications-init.sql 15 Sep 2002 23:57:20 -0000 1.1
@@ -0,0 +1,62 @@
+--
+-- Survey
+--
+-- -- @author dave@thedesignexperience.org, ben@openforce.biz
+-- @creation-date 2002-08-03
+--
+-- integration with Notifications
+declare
+ impl_id integer;
+ v_foo integer;
+begin
+ -- the notification type impl
+ impl_id := acs_sc_impl.new (
+ 'NotificationType',
+ 'survey_response_notif_type',
+ 'survey'
+ );
+
+ v_foo := acs_sc_impl.new_alias (
+ 'NotificationType',
+ 'survey_response_notif_type',
+ 'GetURL',
+ 'survey::notification::get_url',
+ 'TCL'
+ );
+
+ v_foo := acs_sc_impl.new_alias (
+ 'NotificationType',
+ 'survey_response_notif_type',
+ 'ProcessReply',
+ 'survey::notification::process_reply',
+ 'TCL'
+ );
+
+ acs_sc_binding.new (
+ contract_name => 'NotificationType',
+ impl_name => 'survey_response_notif_type'
+ );
+
+ v_foo:= notification_type.new (
+ short_name => 'survey_response_notif',
+ sc_impl_id => impl_id,
+ pretty_name => 'Survey Response',
+ description => 'Notifications for Survey',
+ creation_user => NULL,
+ creation_ip => NULL
+ );
+
+ -- enable the various intervals and delivery methods
+ insert into notification_types_intervals
+ (type_id, interval_id)
+ select v_foo, interval_id
+ from notification_intervals where name in ('instant','hourly','daily');
+
+ insert into notification_types_del_methods
+ (type_id, delivery_method_id)
+ select v_foo, delivery_method_id
+ from notification_delivery_methods where short_name in ('email');
+
+end;
+/
+show errors
Index: openacs-4/packages/survey/sql/oracle/survey-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/oracle/survey-package-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/oracle/survey-package-create.sql 15 Sep 2002 23:57:20 -0000 1.1
@@ -0,0 +1,484 @@
+-- start off with package declarations
+
+
+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
+) 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
+ ) return acs_objects.object_id%TYPE;
+
+ procedure remove (
+ section_id in survey_sections.section_id%TYPE
+ );
+end survey_section;
+/
+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
+ ) 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
+ ) 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)
+ 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);
+
+ 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.delete(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
+
+ ) 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)
+ values
+ (v_section_id, new.survey_id, new.name, new.description, new.description_html_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.delete(remove.section_id);
+ end remove;
+end survey_section;
+/
+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
+ ) 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)
+ 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);
+ 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.delete(remove.question_id);
+ end remove;
+end survey_question;
+/
+show errors
+
+
+create or replace package body 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
+ is
+ v_response_id survey_responses.response_id%TYPE;
+ begin
+ v_response_id := acs_object.new (
+ object_id => response_id,
+ object_type => object_type,
+ creation_date => creation_date,
+ creation_user => creation_user,
+ creation_ip => creation_ip,
+ context_id => context_id
+ );
+ insert into survey_responses (response_id, survey_id, title, notify_on_comment_p, initial_response_id)
+ values
+ (v_response_id, new.survey_id, new.title, new.notify_on_comment_p, new.initial_response_id);
+ return v_response_id;
+ end new;
+
+ function initial_response_id (
+ response_id in survey_responses.response_id%TYPE
+ ) return survey_responses.response_id%TYPE
+ is
+ v_initial_response_id survey_responses.response_id%TYPE;
+ begin
+ select initial_response_id into v_initial_response_id
+ from survey_responses where
+ response_id = initial_response_id.response_id;
+ if v_initial_response_id is NULL then
+ v_initial_response_id := initial_response_id.response_id;
+ end if;
+ return v_initial_response_id;
+ end initial_response_id;
+
+ function initial_user_id (
+ response_id in survey_responses.response_id%TYPE
+ ) return acs_objects.creation_user%TYPE
+ is
+ v_user_id acs_objects.creation_user%TYPE;
+ begin
+ select creation_user into v_user_id
+ from acs_objects
+ where object_id = survey_response.initial_response_id(initial_user_id.response_id);
+ return v_user_id;
+ end initial_user_id;
+
+ procedure remove (
+ response_id in survey_responses.response_id%TYPE
+ ) is
+ v_response_row survey_responses%ROWTYPE;
+ begin
+ for v_response_row in (select response_id from survey_responses
+ where initial_response_id=remove.response_id) loop
+ survey_response.del(v_response_row.response_id);
+ end loop;
+
+ survey_response.del(remove.response_id);
+ end remove;
+
+ procedure del (
+ response_id in survey_responses.response_id%TYPE
+ )
+ is
+ v_question_response_row survey_question_responses%ROWTYPE;
+ begin
+ for v_question_response_row in (
+ select item_id
+ from survey_question_responses, cr_revisions
+ where response_id=del.response_id
+ and attachment_answer=revision_id)
+ loop
+ content_item.delete(v_question_response_row.item_id);
+ end loop;
+
+ delete from survey_question_responses
+ where response_id=del.response_id;
+ delete from survey_responses
+ where response_id=del.response_id;
+ acs_object.delete(del.response_id);
+ end del;
+
+ function boolean_answer (
+ answer varchar,
+ question_id survey_questions.question_id%TYPE
+ ) return varchar
+ is
+ v_answer varchar(100);
+ v_presentation_options survey_questions.presentation_options%TYPE;
+ 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,
+ survey_response.initial_user_id(sr.response_id) as initial_user_id
+ from survey_responses sr,
+ acs_objects o,
+ (select max(response_id) as response_id
+ from survey_responses
+ group by survey_response.initial_response_id(response_id)) latest
+ where 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;
Index: openacs-4/packages/survey/sql/oracle/survey-package-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/oracle/survey-package-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/oracle/survey-package-drop.sql 15 Sep 2002 23:57:20 -0000 1.1
@@ -0,0 +1,4 @@
+drop package survey_response;
+drop package survey_question;
+drop package survey_section;
+drop package survey;
Index: openacs-4/packages/survey/sql/postgresql/survey-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/postgresql/survey-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/postgresql/survey-create.sql 15 Sep 2002 23:57:48 -0000 1.1
@@ -0,0 +1,567 @@
+-- ported to OpenACS 4 by Gilbert Wong (gwong@orchardlabs.com) on 2001-05-20
+--
+-- based on student work from 6.916 in Fall 1999
+-- which was in turn based on problem set 4
+-- in http://photo.net/teaching/one-term-web.html
+--
+-- by philg@mit.edu and raj@alum.mit.edu on February 9, 2000
+-- converted to ACS 4.0 by nstrug@arsdigita.com on 29th September 2000
+-- modified for dotLRN/OpenACS4.5 and renamed from "simple-survey" to "survey"
+-- by dave@thedesignexperience.org on 13 July 2002
+--
+-- $Id: survey-create.sql,v 1.1 2002/09/15 23:57:48 daveb Exp $
+
+create function inline_0 ()
+returns integer as '
+begin
+ PERFORM acs_privilege__create_privilege(''survey_create_survey'', null, null);
+ PERFORM acs_privilege__create_privilege(''survey_modify_survey'', null, null);
+ PERFORM acs_privilege__create_privilege(''survey_delete_survey'', null, null);
+ PERFORM acs_privilege__create_privilege(''survey_create_question'', null, null);
+ PERFORM acs_privilege__create_privilege(''survey_modify_question'', null, null);
+ PERFORM acs_privilege__create_privilege(''survey_delete_question'', null, null);
+ PERFORM acs_privilege__create_privilege(''survey_take_survey'', null, null);
+ PERFORM acs_privilege__create_privilege(''survey_admin_survey'', null, null);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+select inline_0 ();
+drop function inline_0 ();
+
+
+begin;
+ -- temporarily drop this trigger to avoid a data-change violation
+ -- on acs_privilege_hierarchy_index while updating the child privileges.
+ drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy;
+
+ select acs_privilege__add_child('survey_admin_survey','survey_create_survey');
+ select acs_privilege__add_child('survey_admin_survey','survey_modify_survey');
+ select acs_privilege__add_child('survey_admin_survey','survey_delete_survey');
+ select acs_privilege__add_child('survey_admin_survey','survey_create_question');
+ select acs_privilege__add_child('survey_admin_survey','survey_modify_question');
+ select acs_privilege__add_child('survey_admin_survey','survey_delete_question');
+
+ select acs_privilege__add_child('read','survey_take_survey');
+
+ -- re-enable the trigger before the last insert to force the
+ -- acs_privilege_hierarchy_index table to be updated.
+
+ create trigger acs_priv_hier_ins_del_tr after insert or delete
+ on acs_privilege_hierarchy for each row
+ execute procedure acs_priv_hier_ins_del_tr ();
+
+ select acs_privilege__add_child('admin','survey_admin_survey');
+
+end;
+
+
+
+create function inline_1 ()
+returns integer as '
+begin
+
+ PERFORM acs_object_type__create_type (
+ ''survey'',
+ ''Survey'',
+ ''Surveys'',
+ ''acs_object'',
+ ''surveys'',
+ ''survey_id'',
+ null,
+ ''f'',
+ null,
+ null
+ );
+
+ PERFORM acs_object_type__create_type (
+ ''survey_section'',
+ ''Survey Section'',
+ ''Survey Sections'',
+ ''acs_object'',
+ ''survey_sections'',
+ ''section_id'',
+ null,
+ ''f'',
+ null,
+ null
+ );
+
+ PERFORM acs_object_type__create_type (
+ ''survey_question'',
+ ''Survey Question'',
+ ''Survey Questions'',
+ ''acs_object'',
+ ''survey_questions'',
+ ''question_id'',
+ null,
+ ''f'',
+ null,
+ null
+ );
+
+ PERFORM acs_object_type__create_type (
+ ''survey_response'',
+ ''Survey Response'',
+ ''Survey Responses'',
+ ''acs_object'',
+ ''survey_responses'',
+ ''response_id'',
+ null,
+ ''f'',
+ null,
+ null
+ );
+
+ return 0;
+
+end;' language 'plpgsql';
+
+select inline_1 ();
+drop function inline_1 ();
+
+create table surveys (
+ survey_id integer constraint surveys_survey_id_fk
+ references acs_objects (object_id)
+ on delete cascade
+ constraint surveys_pk
+ primary key,
+ name varchar(4000)
+ constraint surveys_name_nn
+ not null,
+ description text
+ constraint surveys_desc_nn
+ not null,
+ description_html_p boolean not null,
+ enabled_p boolean not null,
+ -- limit to one response per user
+ single_response_p boolean not null,
+ editable_p boolean not null,
+ single_section_p boolean not null,
+ type varchar(20),
+ display_type varchar(20),
+ package_id integer constraint surveys_package_id_nn
+ not null
+ constraint surveys_package_id_fk
+ references apm_packages (package_id) on delete cascade
+);
+
+
+create table survey_sections (
+ section_id integer constraint survey_sections_section_id_fk
+ references acs_objects (object_id)
+ constraint survey_sections_pk
+ primary key,
+ survey_id integer
+ constraint survey_sections_survey_id_nn
+ not null
+ constraint survey_sections_survey_id_fk
+ references surveys,
+ name varchar(4000)
+ constraint survey_sections_name_nn
+ not null,
+ description text
+ constraint survey_sections_desc_nn
+ not null,
+ description_html_p boolean
+);
+
+create index survey_sections_survey_id_fk on survey_sections(survey_id);
+-- each question can be
+
+create table survey_questions (
+ question_id integer constraint survey_q_question_id_fk
+ references acs_objects (object_id)
+ on delete cascade
+ constraint survey_q_question_id_pk
+ primary key,
+ section_id integer constraint survey_q_section_id_fk
+ references survey_sections
+ on delete cascade,
+ sort_order integer
+ constraint survey_q_sort_order_nn
+ not null,
+ question_text text
+ constraint survey_q_question_text_nn
+ not null,
+ abstract_data_type varchar(30)
+ constraint survey_q_abs_data_type_ck
+ check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice','date')),
+ required_p boolean,
+ active_p boolean,
+ presentation_type varchar(20)
+ constraint survey_q_pres_type_nn
+ not null
+ constraint survey_q_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_q_pres_alignment_ck
+ check(presentation_alignment in ('below','beside'))
+);
+
+
+create index survey_q_sort_order on survey_questions(sort_order);
+create index survey_q_active_p on survey_questions(active_p);
+
+-- for when a question has a fixed set of responses
+
+create sequence survey_choice_id_seq;
+create view survey_choice_id_sequence as select nextval('survey_choice_id_seq') as nextval;
+
+create table survey_question_choices (
+ choice_id integer constraint survey_qc_choice_id_nn
+ not null
+ constraint survey_qc_choice_id_pk
+ primary key,
+ question_id integer constraint survey_qc_question_id_nn
+ not null
+ constraint survey_qc_question_id_fk
+ references survey_questions
+ on delete cascade,
+ -- human readable
+ label varchar(500) constraint survey_qc_label_nn
+ not null,
+ -- might be useful for averaging or whatever, generally null
+ numeric_value numeric,
+ -- lower is earlier
+ sort_order integer
+);
+
+
+create index survey_q_c_question_id on survey_question_choices(question_id);
+create index survey_q_c_sort_order on survey_question_choices(sort_order);
+
+-- this records a response by one user to one survey
+-- (could also be a proposal in which case we'll do funny
+-- things like let the user give it a title, send him or her
+-- email if someone comments on it, etc.)
+create table survey_responses (
+ response_id integer constraint survey_resp_response_id_fk
+ references acs_objects (object_id)
+ on delete cascade
+ constraint survey_resp_response_id_pk
+ primary key,
+ initial_response_id integer constraint survey_resp_initial_fk
+ references survey_responses(response_id),
+ survey_id integer constraint survey_resp_survey_id_fk
+ references surveys
+ on delete cascade,
+ title varchar(100),
+ notify_on_comment_p boolean
+);
+
+
+create index survey_response_index on survey_question_responses (response_id, question_id);
+create index survey_q_r_choice_id on survey_question_responses(choice_id);
+create index survey_q_r_attachment_answer on survey_question_responses(attachment_answer);
+
+
+-- this view contains only the most recently edited version
+-- of each survey response.
+
+create view survey_responses_latest as
+select sr.*, o.creation_date,
+ o.creation_user,
+ survey_response__initial_user_id(sr.response_id) as initial_user_id
+ from survey_responses sr
+ join acs_objects o
+ on (sr.response_id = o.object_id)
+ join (select max(response_id) as response_id
+ from survey_responses
+ group by survey_response__initial_response_id(response_id)) latest
+ on (sr.response_id = latest.response_id);
+
+
+
+-- this table stores the answers to each question for a survey
+-- we want to be able to hold different data types in one long skinny table
+-- but we also may want to do averages, etc., so we can't just use CLOBs
+
+create table survey_question_responses (
+ response_id integer constraint survey_qr_response_id_nn
+ not null
+ constraint survey_qr_response_id_fk
+ references survey_responses
+ on delete cascade,
+ question_id integer constraint survey_qr_question_id_nn
+ not null
+ constraint survey_qr_question_id_fk
+ references survey_questions
+ on delete cascade,
+ -- if the user picked a canned response
+ choice_id integer constraint survey_qr_choice_id_fk
+ references survey_question_choices
+ on delete cascade,
+ boolean_answer boolean,
+ clob_answer text,
+ number_answer numeric,
+ varchar_answer text,
+ date_answer timestamp,
+ attachment_answer integer
+ constraint survey_q_response_item_id_fk
+ references cr_items(item_id)
+ on delete cascade
+);
+
+
+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 index survey_response_index on survey_question_responses (response_id, question_id);
+
+-- We create a view that selects out only the last response from each
+-- user to give us at most 1 response from all users.
+-- create or replace view survey_question_responses_un as
+-- select qr.*
+-- from survey_question_responses qr, survey_responses_unique r
+-- where qr.response_id=r.response_id;
+
+
+-- API for survey objects
+
+create function survey__new (integer,varchar,text,boolean,boolean,boolean,boolean,boolean,varchar,varchar,integer,integer,integer)
+returns integer as '
+declare
+ new__survey_id alias for $1; -- default null
+ new__name alias for $2;
+ new__description alias for $3;
+ new__description_html_p alias for $4; -- default f
+ new__single_response_p alias for $5; -- default f
+ new__editable_p alias for $6; -- default t
+ new__enabled_p alias for $7; -- default f
+ new__single_section_p alias for $8; -- default t
+ new__type alias for $9; -- default general
+ new__display_type alias for $10;
+ new__package_id alias for $11;
+ new__creation_user alias for $12; -- default null
+ new__context_id alias for $13; -- default null
+ v_survey_id integer;
+begin
+ v_survey_id := acs_object__new (
+ new__survey_id,
+ ''survey'',
+ now(),
+ new__creation_user,
+ null,
+ new__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)
+ 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);
+
+ return v_survey_id;
+
+end;' language 'plpgsql';
+
+
+create function survey__delete (integer)
+returns integer as '
+declare
+ delete__survey_id alias for $1;
+begin
+ delete from surveys
+ where survey_id = delete__survey_id;
+
+ PERFORM acs_object__delete(delete__survey_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+-- API for survey_section objects
+
+create function survey_section__new (integer,integer,varchar,text,boolean,integer,integer)
+returns integer as '
+declare
+ new__section_id alias for $1; -- default null
+ new__survey_id alias for $2; -- default null
+ new__name alias for $3; -- default null
+ new__description alias for $4; -- default null
+ new__description_html_p alias for $5; -- default f
+ new__creation_user alias for $6; -- default null
+ new__context_id alias for $7; -- default null
+ v_section_id integer;
+begin
+ v_section_id := acs_object__new (
+ new__section_id,
+ ''survey_section'',
+ now(),
+ new__creation_user,
+ null,
+ new__context_id
+ );
+
+ insert into survey_sections
+ (section_id, survey_id, name, description, description_html_p)
+ values
+ (v_section_id, new__survey_id, new__name, new__description, new__description_html_p);
+
+ return v_section_id;
+
+end;' language 'plpgsql';
+
+
+
+create function survey_section__delete (integer)
+returns integer as '
+declare
+ delete__section_id alias for $1;
+begin
+ delete from survey_sections
+ where section_id = delete__section_id;
+
+ PERFORM acs_object__delete(delete__section_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+
+create function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,integer,integer)
+returns integer as '
+declare
+ new__question_id alias for $1; -- default null
+ new__section_id alias for $2; -- default null
+ new__sort_order alias for $3; -- default null
+ new__question_text alias for $4; -- default null
+ new__abstract_data_type alias for $5; -- default null
+ new__required_p alias for $6; -- default t
+ new__active_p alias for $7; -- default
+ new__presentation_type alias for $8; -- default null
+ new__presentation_options alias for $9; -- default null
+ new__presentation_alignment alias for $10; -- default below
+ new__creation_user alias for $11; -- default null
+ new__context_id alias for $12; -- default null
+ v_question_id integer;
+begin
+ v_question_id := acs_object__new (
+ new__question_id,
+ ''survey_question'',
+ now(),
+ new__creation_user,
+ null,
+ new__context_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)
+ 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);
+
+ return v_question_id;
+
+end;' language 'plpgsql';
+
+-- procedure delete
+create function survey_question__delete (integer)
+returns integer as '
+declare
+ delete__question_id alias for $1;
+begin
+ delete from survey_questions
+ where question_id = delete__question_id;
+
+ PERFORM acs_object__delete(delete__question_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+-- create or replace package body survey_response
+-- procedure new
+create function survey_response__new(integer,integer,varchar,boolean,integer,varchar,integer,integer)
+returns integer as '
+declare
+ new__response_id alias for $1; -- default null
+ new__survey_id alias for $2; -- default null
+ new__title alias for $3; -- default null
+ new__notify_on_comment_p alias for $4; -- default f
+ new__creation_user alias for $5; -- default null
+ new__creation_ip alias for $6; -- default null
+ new__context_id alias for $7; -- default null
+ new__initial_response_id alias for $8; -- default null
+ v_response_id integer;
+begin
+ v_response_id := acs_object__new (
+ new__response_id,
+ ''survey_response'',
+ now(),
+ new__creation_user,
+ new__creation_ip,
+ new__context_id
+ );
+
+ insert into survey_responses
+ (response_id, survey_id, title, notify_on_comment_p, initial_response_id)
+ values
+ (v_response_id, new__survey_id, new__title, new__notify_on_comment_p, new__initial_response_id);
+
+ return v_response_id;
+
+end;' language 'plpgsql';
+
+--function initial_response_id
+create function survey_response__initial_response_id(integer)
+returns integer as '
+declare
+ p_response_id alias for $1;
+ v_initial_response_id integer;
+begin
+ select into v_initial_response_id initial_response_id from
+ survey_responses where response_id = p_response_id;
+ if v_initial_response_id is NULL then
+ v_initial_response_id := p_response_id;
+ end if;
+ return v_initial_response_id;
+end;' language 'plpgsql';
+
+create function survey_response__initial_user_id (integer)
+returns integer as '
+declare
+p_response_id alias for $1;
+v_user_id integer;
+begin
+ select into v_user_id creation_user
+ from acs_objects where
+ object_id = survey_response__initial_response_id(p_response_id);
+return v_user_id;
+end;' language 'plpgsql';
+
+-- procedure delete
+create function survey_response__delete(integer)
+returns integer as '
+declare
+ delete__response_id alias for $1;
+begin
+ delete from survey_responses
+ where response_id = delete__response_id;
+
+ PERFORM acs_object__delete(delete__response_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
Index: openacs-4/packages/survey/sql/postgresql/survey-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/postgresql/survey-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/postgresql/survey-drop.sql 15 Sep 2002 23:57:48 -0000 1.1
@@ -0,0 +1,76 @@
+--
+-- drop SQL for survey package
+--
+-- by nstrug@arsdigita.com on 29th September 2000
+--
+-- $Id: survey-drop.sql,v 1.1 2002/09/15 23:57:48 daveb Exp $
+
+select drop_package('survey_response');
+select drop_package('survey_question');
+select drop_package('survey_section');
+select drop_package('survey');
+
+drop table survey_logic_surveys_map;
+drop view survey_logic_id_sequence;
+drop sequence survey_logic_id_seq;
+drop table survey_logic;
+drop table survey_choice_scores;
+drop table survey_variables_surveys_map;
+drop table survey_variables;
+drop view survey_variable_id_sequence;
+drop sequence survey_variable_id_seq;
+drop view survey_question_responses_un;
+drop table survey_question_responses;
+drop view survey_responses_unique;
+drop table survey_responses;
+drop table survey_question_choices;
+drop view survey_choice_id_sequence;
+drop sequence survey_choice_id_seq;
+drop table survey_questions;
+drop table survey_sections;
+drop table surveys;
+
+-- nuke all created objects
+-- need to do this before nuking the types
+delete from acs_objects where object_type = 'survey_response';
+delete from acs_objects where object_type = 'survey_question';
+delete from acs_objects where object_type = 'survey_section';
+delete from acs_objects where object_type = 'survey';
+
+create function inline_0 ()
+returns integer as '
+begin
+
+ PERFORM acs_object_type__drop_type (''survey_response'',''f'');
+ PERFORM acs_object_type__drop_type (''survey_question'',''f'');
+ PERFORM acs_object_type__drop_type (''survey_section'',''f'');
+ PERFORM acs_object_type__drop_type (''survey'',''f'');
+
+ PERFORM acs_privilege__remove_child (''admin'',''survey_admin_survey'');
+ PERFORM acs_privilege__remove_child (''read'',''survey_take_survey'');
+ PERFORM acs_privilege__remove_child (''survey_admin_survey'',''survey_delete_question'');
+ PERFORM acs_privilege__remove_child (''survey_admin_survey'',''survey_modify_question'');
+ PERFORM acs_privilege__remove_child (''survey_admin_survey'',''survey_create_question'');
+ PERFORM acs_privilege__remove_child (''survey_admin_survey'',''survey_delete_survey'');
+ PERFORM acs_privilege__remove_child (''survey_admin_survey'',''survey_modify_survey'');
+ PERFORM acs_privilege__remove_child (''survey_admin_survey'',''survey_create_survey'');
+
+ PERFORM acs_privilege__drop_privilege(''survey_admin_survey'');
+ PERFORM acs_privilege__drop_privilege(''survey_take_survey'');
+ PERFORM acs_privilege__drop_privilege(''survey_delete_question'');
+ PERFORM acs_privilege__drop_privilege(''survey_modify_question'');
+ PERFORM acs_privilege__drop_privilege(''survey_create_question'');
+ PERFORM acs_privilege__drop_privilege(''survey_delete_survey'');
+ PERFORM acs_privilege__drop_privilege(''survey_modify_survey'');
+ PERFORM acs_privilege__drop_privilege(''survey_create_survey'');
+
+ return 0;
+end;' language 'plpgsql';
+
+select inline_0 ();
+drop function inline_0 ();
+
+-- gilbertw - logical_negation is defined in utilities-create.sql in acs-kernel
+-- drop function logical_negation(boolean);
+
+
Index: openacs-4/packages/survey/sql/postgresql/survey-notifications-init.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/sql/postgresql/survey-notifications-init.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/sql/postgresql/survey-notifications-init.sql 15 Sep 2002 23:57:48 -0000 1.1
@@ -0,0 +1,62 @@
+-- daveb: not tested, this will probably break on postgresql
+-- Survey
+--
+-- -- @author dave@thedesignexperience.org, ben@openforce.biz
+-- @creation-date 2002-08-03
+--
+-- integration with Notifications
+
+declare
+ impl_id integer;
+ v_foo integer;
+begin
+ -- the notification type impl
+ impl_id := acs_sc_impl__new (
+ 'NotificationType',
+ 'survey_response_notif_type',
+ 'survey'
+ );
+
+ v_foo := acs_sc_impl__new_alias (
+ 'NotificationType',
+ 'survey_response_notif_type',
+ 'GetURL',
+ 'survey::notification::get_url',
+ 'TCL'
+ );
+
+ v_foo := acs_sc_impl__new_alias (
+ 'NotificationType',
+ 'survey_response_notif_type',
+ 'ProcessReply',
+ 'survey::notification::process_reply',
+ 'TCL'
+ );
+
+ acs_sc_binding__new (
+ contract_name => 'NotificationType',
+ impl_name => 'survey_response_notif_type'
+ );
+
+ v_foo:= notification_type__new (
+ short_name => 'survey_response_notif',
+ sc_impl_id => impl_id,
+ pretty_name => 'Survey Response Notification',
+ description => 'Notifications for Survey',
+ creation_user => NULL,
+ creation_ip => NULL
+ );
+
+ -- enable the various intervals and delivery methods
+ insert into notification_types_intervals
+ (type_id, interval_id)
+ select v_foo, interval_id
+ from notification_intervals where name in ('instant','hourly','daily');
+
+ insert into notification_types_del_methods
+ (type_id, delivery_method_id)
+ select v_foo, delivery_method_id
+ from notification_delivery_methods where short_name in ('email');
+
+end;
+
Index: openacs-4/packages/survey/tcl/survey-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/tcl/survey-procs-oracle.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/tcl/survey-procs-oracle.xql 15 Sep 2002 23:56:54 -0000 1.1
@@ -0,0 +1,83 @@
+
+
+
+ oracle 8.1.6
+
+
+ begin
+ :1 := survey_question.new (
+ question_id => NULL,
+ section_id => :section_id,
+ sort_order => :new_sort_order,
+ question_text => empty_clob(),
+ abstract_data_type => :abstract_data_type,
+ presentation_type => :presentation_type,
+ presentation_alignment => :presentation_alignment,
+ presentation_options => :presentation_options,
+ active_p => :active_p,
+ required_p => :required_p,
+ context_id => :section_id,
+ creation_user => :user_id
+ );
+ end;
+
+
+
+
+
+
+select survey_choice_id_sequence.nextval as choice_id from dual
+
+
+
+
+
+ select count(distinct survey_response.initial_user_id(response_id))
+ from
+ survey_responses
+ where survey_id=:survey_id
+
+
+
+
+
+
+ begin
+ :1 := survey.new (
+ survey_id => NULL,
+ name => :name,
+ description => :description,
+ description_html_p => :description_html_p,
+ editable_p => :editable_p,
+ single_response_p => :single_response_p,
+ enabled_p => :enabled_p,
+ single_section_p => :single_section_p,
+ type => :type,
+ display_type => :display_type,
+ package_id => :package_id,
+ context_id => :package_id,
+ creation_user => :user_id
+ );
+ end;
+
+
+
+
+
+ begin
+ :1 := survey_section.new (
+ section_id=>NULL,
+ survey_id=>:new_survey_id,
+ name=>:name,
+ description=>empty_clob(),
+ description_html_p=>:description_html_p,
+ context_id =>:new_survey_id
+ );
+ end;
+
+
+
+
+
Index: openacs-4/packages/survey/tcl/survey-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/tcl/survey-procs-postgresql.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/tcl/survey-procs-postgresql.xql 15 Sep 2002 23:56:54 -0000 1.1
@@ -0,0 +1,33 @@
+
+
+
+ postgresql 7.1
+
+ begin
+ PERFORM survey_question__new (
+ NULL,
+ :section_id,
+ :new_sort_order,
+ :question_text,
+ :abstract_data_type,
+ :required_p,
+ :active_p,
+ :presentation_type,
+ :presentation_options,
+ :presentation_alignment,
+ :user_id,
+ :section_id
+ );
+ end;
+
+
+
+
+
+select survey_choice_id_sequence.nextval as choice_id
+
+
+
+
\ No newline at end of file
Index: openacs-4/packages/survey/tcl/survey-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/tcl/survey-procs.tcl,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/tcl/survey-procs.tcl 15 Sep 2002 23:56:54 -0000 1.1
@@ -0,0 +1,519 @@
+# /tcl/survey-procs.tcl
+
+ad_library {
+
+ Support procs for simple survey module, most important being
+ survey_question_display which generates a question widget based
+ on data retrieved from database.
+
+ @author philg@mit.edu on
+ @author teadams@mit.edu
+ @author nstrug@arsdigita.com
+ @date February 9, 2000
+ @cvs-id survey-simple-defs.tcl,v 1.29.2.5 2000/07/19 20:11:24 seb Exp
+
+}
+
+ad_proc -public get_survey_info {
+ {-survey_id ""}
+ {-section_id ""}
+} {
+ creates a tcl array variable named "survey_info" in the caller's environment,
+ which contains key/value pairs for all properties of the requested survey.
+
+ If survey_id is passed in, and it's a single-section survey, the
+ section_id will also be looked up and returned in the survey_info array.
+
+ @author luke@museatech.net
+ @date 2002-07-24
+} {
+ upvar survey_info survey_info
+
+ if {[empty_string_p $survey_id]} {
+ db_1row lookup_survey_id ""
+ }
+
+ db_1row get_info_by_survey_id "" -column_array survey_info
+
+ # If it's a single-section survey, look up the section_id
+ if {[empty_string_p $section_id] && $survey_info(single_section_p) == "t"} {
+ db_1row lookup_single_section_id ""
+ set survey_info(section_id) $section_id
+ }
+
+ # some useful stats about the survey, dotLRN specific for sloanspace
+ if {[apm_package_installed_p dotlrn]} {
+ set community_id [dotlrn_community::get_community_id_from_url]
+ set survey_info(eligible) [db_string n_eligible {}]
+ set survey_info(completed) [db_string n_completed {}]
+ set survey_info(not_completed) [expr {$survey_info(eligible) - $survey_info(completed)}]
+ }
+}
+
+
+ad_proc -public survey_question_display {
+ question_id
+ {response_id ""}
+} {Returns a string of HTML to display for a question, suitable for embedding in a form. The form variable is of the form \"response_to_question.\$question_id} {
+
+ if {![empty_string_p $response_id]} {
+ set edit_previous_response_p "t"
+ } else {
+ set edit_previous_response_p "f"
+ }
+
+ set element_name "response_to_question.$question_id"
+
+ db_1row survey_question_properties ""
+ if {$required_p == "t"} {
+ set html "* "
+ } else {
+ set html " "
+ }
+
+ append html $question_text
+ if { $presentation_alignment == "below" } {
+ append html "
+ Select One
+ $choice_t
+ $choice_f
+
+"
+ } else {
+ if {$edit_previous_response_p == "t"} {
+ set user_value $choice_id
+ }
+
+# at some point, we may want to add a UI option for the admin
+# to sepcify multiple or not for select
+ append html "
+ Select One \n"
+ db_foreach question_choices "" {
+
+ if { $user_value == $choice_id } {
+ append html "$label \n"
+ } else {
+ append html "$label \n"
+ }
+ }
+ append html " "
+ }
+ }
+
+ "radio" {
+ if { $abstract_data_type == "boolean" } {
+ if {$edit_previous_response_p == "t"} {
+ set user_value $boolean_answer
+ }
+ if {![empty_string_p $presentation_options]} {
+ set options_list [split $presentation_options "/"]
+ set choice_t [lindex $options_list 0]
+ set choice_f [lindex $options_list 1]
+ } else {
+ set choice_t "True"
+ set choice_f "False"
+ }
+
+ set choices [list "\n[join $choices " "
+ }
+ }
+
+ "checkbox" {
+ set choices [list]
+ db_foreach question_choices_3 "" {
+ if { [info exists selected_choices($choice_id)] } {
+ lappend choices "\n[join $choices " "
+ }
+ }
+ }
+
+ return $html
+}
+
+ad_proc -public util_show_plain_text { text_to_display } "allows plain text (e.g. text entered through forms) to look good on screen without using tags; preserves newlines, angle brackets, etc." {
+ regsub -all "\\&" $text_to_display "\\&" good_text
+ regsub -all "\>" $good_text "\\>" good_text
+ regsub -all "\<" $good_text "\\<" good_text
+ regsub -all "\n" $good_text "# $sort_order: $question_text
+ "
+ } else {
+ append return_string "# $sort_order: $question_text: "
+ }
+ append return_string [util_show_plain_text "$clob_answer $number_answer $varchar_answer $date_answer"]
+
+ if {![empty_string_p $attachment_answer]} {
+ set package_id [ad_conn package_id]
+ set filename [db_string get_filename {}]
+ append return_string "Uploaded file: \"$filename\" "
+ }
+
+ if {$choice_id != 0 && ![empty_string_p $choice_id] && $question_id != $question_id_previous} {
+ set label_list [db_list survey_label_list ""]
+ append return_string "[join $label_list ", "]"
+ }
+
+ if ![empty_string_p $boolean_answer] {
+ append return_string "[survey_decode_boolean_answer -response $boolean_answer -question_id $question_id]"
+
+ }
+
+ if $html_p {
+ append return_string "
+ "
+ } else {
+ append return_string "\n\n"
+ }
+
+ set question_id_previous $question_id
+ }
+
+ return "$return_string"
+}
+
+
+
+ad_proc -public survey_get_score {section_id user_id} "Returns the score of the user's most recent response to a survey" {
+
+ set response_id [ survey_get_response_id $section_id $user_id ]
+
+ if { $response_id != 0 } {
+ set score [db_string get_score "" -default 0]
+ } else {
+ set score {}
+ }
+
+ return $score
+}
+
+
+ad_proc -public survey_display_types {
+} {
+ return {list table paragraph}
+}
+
+
+ad_proc -public survey_question_copy {
+ {-new_section_id ""}
+ {-question_id:required}
+} { copies a question within the same survey
+} {
+ set user_id [ad_conn user_id]
+ db_1row get_question_details {}
+ if {![empty_string_p $new_section_id]} {
+ set section_id $new_section_id
+ }
+
+ set old_question_id $question_id
+ if {[empty_string_p $new_section_id]} {
+ set after $sort_order
+ set new_sort_order [expr {$after + 1}]
+ db_dml renumber_sort_orders {}
+ } else {
+ set new_sort_order $sort_order
+ }
+
+ set new_question_id [db_exec_plsql create_question {}]
+ db_dml insert_question_text {}
+ db_foreach get_survey_question_choices {} {
+ set new_choice_id [db_string get_choice_id {}]
+ db_dml insert_survey_question_choice {}
+
+ }
+
+ return $new_question_id
+}
+
+ad_proc survey_copy {
+ {-survey_id:required}
+ {-package_id ""}
+ {-new_name ""}
+} {
+ copies a survey, copying all questions, but not responses
+ is package_id is specific it copies they survey to another
+ survey package instance, otherwise it copies the survey to the
+ same package instance
+} {
+
+ if {[empty_string_p $package_id]} {
+ set package_id [ad_conn package_id]
+ }
+
+ db_1row get_survey_info {}
+ if {![empty_string_p $new_name]} {
+ set name $new_name
+ }
+ set user_id [ad_conn user_id]
+ set new_survey_id [db_exec_plsql survey_create {} ]
+ set sections_list [db_list get_sections {}]
+
+
+ foreach section_id $sections_list {
+
+ set new_section_id [db_exec_plsql section_create {}]
+ set new_section_ids($section_id) $new_section_id
+ if {![empty_string_p $description]} {
+ db_dml set_section_description {}
+ }
+ }
+ db_foreach get_questions {} {
+
+ survey_question_copy -new_section_id $new_section_ids($section_id) -question_id $question_id
+ }
+return $new_survey_id
+
+}
+
+ad_proc -public survey_do_notifications {
+ {-response_id ""}
+} { process notifications when someone responds to a survey
+ or edits a response
+} {
+
+ set survey_id [db_string get_survey_id_from_response {}]
+ get_survey_info -survey_id $survey_id
+ set survey_name $survey_info(name)
+ set subject "Response to $survey_name"
+
+ #dotlrn specific info
+ set dotlrn_installed_p [apm_package_installed_p dotlrn]
+ if {$dotlrn_installed_p} {
+ set package_id [ad_conn package_id]
+ set community_id [dotlrn_community::get_community_id]
+ set segment_id [dotlrn_community::get_rel_segment_id -community_id $community_id -rel_type "dotlrn_member_rel"]
+ set community_name [dotlrn_community::get_community_name $community_id]
+ set community_url "[ad_parameter -package_id [ad_acs_kernel_id] SystemURL][dotlrn_community::get_community_url $community_id]"
+ }
+ db_1row get_response_info {
+ select r.initial_response_id, r.responding_user_id, r.response_id,
+ u.first_names || ' ' || u.last_name as user_name,
+ edit_p,
+ o.creation_date as response_date
+ from (select survey_response.initial_user_id(response_id) as responding_user_id,
+ survey_response.initial_response_id(response_id) as initial_response_id,
+ response_id, (case when initial_response_id is NULL then 'f' else 't' end) as edit_p
+ from survey_responses) r, acs_objects o,
+ cc_users u where r.response_id=:response_id
+ and r.responding_user_id = u.user_id
+ and r.response_id = o.object_id
+ }
+
+ set notif_text ""
+ if {$dotlrn_installed_p} {
+ append notif_text "
+Group: $community_name"
+ }
+ append notif_text "
+Survey: $survey_name
+Respondent: $user_name
+
+Here is what $user_name <[acs_community_member_url -user_id $responding_user_id]>
+had to say in response to $survey_name:
+ "
+
+ if {$edit_p} {
+ append notif_text "
+Edited "
+ }
+ append notif_text "Response on $response_date\n"
+
+ append notif_text [survey_answer_summary_display $response_id 0]
+
+# add summary info for sloanspace
+ if {$dotlrn_installed_p} {
+ set n_responses [db_string n_responses {}]
+ if {$n_responses > 0} {
+ append notif_text " -----
+Already Responsed: $n_responses users
+
+View these users. <$community_url/survey/admin/respondents?response_type=responded>
+
+Spam these users. <$community_url/survey/admin/send-mail?survey_id=$survey_id&to=responded>
+
+"
+ }
+ set n_members [db_string n_members {}]
+ set n_awaiting [expr {$n_members - $n_responses}]
+
+ append notif_text "
+Awaiting a response: $n_awaiting users
+
+View these users. <$community_url/survey/admin/respondents?response_type=not_responded>
+
+Spam these users. <$community_url/survey/admin/send-mail?survey_id=$survey_id&to=not_responded>
+
+The whole group: $n_members
+
+View these users. <$community_url/survey/admin/respondents?response_type=all>
+
+Spam these users. <$community_url/survey/admin/send-mail?survey_id=$survey_id&to=all>
+
+Responses:
+"
+
+ db_foreach get_questions {} {
+ append notif_text "$sort_order. $question_text - View responses. <$community_url/survey/view-text-responses?question_id=$question_id>
+ "
+ }
+ }
+ notification::new \
+ -type_id [notification::type::get_type_id \
+ -short_name survey_response_notif] \
+ -object_id $survey_id \
+ -response_id $survey_id \
+ -notif_subject $subject \
+ -notif_text $notif_text
+
+}
+
+
+ad_proc survey_decode_boolean_answer {
+ {-response:required}
+ {-question_id:required}
+} {
+ takes t/f value from a boolean_answer column and
+ decodes it based on the presentation_options of the question
+
+ @author Dave Bauer
+
+ @param -response text value of response to be decoded
+ @param -question_id question_id of question response is from
+} {
+ set presentation_options [db_string get_presentation_options {}]
+ if {[empty_string_p $presentation_options]} {
+ set presentation_options "True/False"
+ }
+
+
+ if {![empty_string_p $response]} {
+ set options_list [split $presentation_options "/"]
+
+ if {$response=="t"} {
+ set response [lindex $options_list 0]
+ } else {
+ set response [lindex $options_list 1]
+ }
+ }
+ return $response
+}
\ No newline at end of file
Index: openacs-4/packages/survey/tcl/survey-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/tcl/survey-procs.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/tcl/survey-procs.xql 15 Sep 2002 23:56:54 -0000 1.1
@@ -0,0 +1,295 @@
+
+
+
+
+
+ select count(*) from dotlrn_member_rels_full
+ where rel_type='dotlrn_member_rel'
+ and community_id=:community_id
+
+
+
+
+
+
+ select min(section_id) as section_id
+ from survey_sections
+ where survey_id = :survey_id
+
+
+
+
+
+ select survey_id
+ from survey_sections
+ where section_id = :section_id
+
+
+
+
+
+ select s.*,
+ o.creation_user, o.creation_date, p.first_names || ' ' || p.last_name as creator_name,
+ (case when enabled_p = 't' then 'Enabled' else 'Disabled' end) as enabled_display,
+ (case when single_response_p = 't' then 'One response' else 'Multiple responses' end) as single_response_display,
+ (case when editable_p = 'f' then 'Non-Editable' else 'Editable' end) as editable_display,
+ (case when single_section_p = 'f' then 'Multiple sections' else 'Single section' end) as single_section_display
+ from surveys s, acs_objects o, persons p
+ where o.object_id = :survey_id
+ and s.survey_id = o.object_id
+ and p.person_id = o.creation_user
+
+
+
+
+
+
+select
+ choice_id,
+ boolean_answer,
+ clob_answer,
+ number_answer,
+ varchar_answer,
+ date_answer,
+ attachment_answer
+ from survey_question_responses
+ where question_id = :question_id
+ and response_id = :response_id
+
+
+
+
+
+
+select
+ choice_id,
+ boolean_answer,
+ clob_answer,
+ number_answer,
+ varchar_answer,
+ date_answer,
+ attachment_answer
+ from survey_question_responses
+ where question_id = :question_id
+ and response_id = :response_id
+
+
+
+
+
+
+
+select
+ section_id,
+ sort_order,
+ question_text,
+ abstract_data_type,
+ required_p,
+ active_p,
+ presentation_type,
+ presentation_options,
+ presentation_alignment,
+ creation_user,
+ creation_date
+from
+ survey_questions, acs_objects
+where
+ object_id = question_id
+ and question_id = :question_id
+
+
+
+
+
+ select choice_id, label
+from survey_question_choices
+where question_id = :question_id
+order by sort_order
+
+
+
+
+
+
+ select choice_id, label
+from survey_question_choices
+where question_id = :question_id
+order by sort_order
+
+
+
+
+
+
+ select * from survey_question_choices
+where question_id = :question_id
+order by sort_order
+
+
+
+
+
+
+
+ select label
+ from survey_question_choices, survey_question_responses
+ where survey_question_responses.question_id = :question_id
+ and survey_question_responses.response_id = :response_id
+ and survey_question_choices.choice_id = survey_question_responses.choice_id
+
+
+
+
+
+
+
+
+select * from survey_questions
+where question_id=:question_id
+
+
+
+
+
+ update survey_questions
+ set question_text = :question_text
+ where question_id = :new_question_id
+
+
+
+
+
+update survey_questions
+ set sort_order = sort_order + 1
+ where section_id = :section_id
+ and sort_order > :after
+
+
+
+
+
+ select * from survey_question_choices
+ where question_id=:old_question_id
+
+
+
+
+
+insert into survey_question_choices
+ (choice_id, question_id, label, numeric_value, sort_order)
+ values
+ (:new_choice_id, :new_question_id, :label,
+ :numeric_value, :sort_order)
+
+
+
+
+
+ select survey_id from survey_responses
+ where response_id=:response_id
+
+
+
+
+
+ select count(*) from survey_responses_latest
+ where survey_id=:survey_id
+
+
+
+
+
+ select count(*) from party_approved_member_map
+ where party_id=:segment_id
+
+
+
+
+
+ select sort_order, question_text, question_id
+ from survey_questions
+ where section_id in
+ (select section_id
+ from survey_sections
+ where survey_id=:survey_id)
+
+
+
+
+
+select * from surveys where survey_id=:survey_id
+
+
+
+
+
+ update survey_sections set description=:description
+ where section_id=:new_section_id
+
+
+
+
+
+ select section_id from survey_sections where survey_id=:survey_id
+
+
+
+
+
+select question_id from survey_questions
+ where section_id in (select section_id from survey_sections
+ where survey_id=:survey_id)
+
+
+
+
+
+
+select
+ sq.question_id,
+ sq.section_id,
+ sq.sort_order,
+ sq.question_text,
+ sq.abstract_data_type,
+ sq.required_p,
+ sq.active_p,
+ sq.presentation_type,
+ sq.presentation_options,
+ sq.presentation_alignment,
+ sqr.response_id,
+ sqr.question_id,
+ sqr.choice_id,
+ sqr.boolean_answer,
+ sqr.clob_answer,
+ sqr.number_answer,
+ sqr.varchar_answer,
+ sqr.date_answer,
+ sqr.attachment_answer
+from
+ survey_questions sq,
+ survey_question_responses sqr
+where
+ sqr.response_id = :response_id
+ and sq.question_id = sqr.question_id
+ and sq.active_p = 't'
+order by sort_order
+
+
+
+
+
+
+ select presentation_options
+ from survey_questions
+ where question_id=:question_id
+
+
+
+
+
+ select title from cr_revisions where
+ revision_id=:attachment_answer
+
+
+
+
Index: openacs-4/packages/survey/www/index-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/www/index-oracle.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/www/index-oracle.xql 16 Sep 2002 00:00:23 -0000 1.1
@@ -0,0 +1,21 @@
+
+
+
+
+
+
+
+select s.survey_id, s.name, s.editable_p, s.single_response_p,
+ sr.response_id, sr.creation_date
+ from surveys s ,(select survey_id, response_id, creation_date
+ from survey_responses_latest
+ where initial_user_id = :user_id) sr
+ where s.package_id=:package_id
+and s.survey_id = sr.survey_id(+)
+and s.enabled_p='t'
+ order by upper(s.name)
+
+
+
+
+
\ No newline at end of file
Index: openacs-4/packages/survey/www/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/www/index-postgresql.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/www/index-postgresql.xql 16 Sep 2002 00:00:23 -0000 1.1
@@ -0,0 +1,22 @@
+
+
+
+
+
+
+
+select s.survey_id, s.name, s.editable_p, s.single_response_p,
+ sr.response_id, sr.creation_date
+ from surveys s left outer join
+ (select survey_id, response_id, creation_date
+ from survey_responses_latest
+ where initial_user_id = :user_id) sr
+ on (s.survey_id = sr.survey_id)
+ where s.package_id=:package_id
+ and s.enabled_p='t'
+ order by upper(s.name)
+
+
+
+
+
\ No newline at end of file
Index: openacs-4/packages/survey/www/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/www/index.adp,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/www/index.adp 16 Sep 2002 00:00:23 -0000 1.1
@@ -0,0 +1,33 @@
+
+Surveys
+@context_bar@
+
+
+
+ @surveys.name@
+
+
+
+
+
+ Previous response on: @surveys.creation_date@
+
+
+
+
+
+
+
+
+
+ No surveys active
+
+
+
+
Index: openacs-4/packages/survey/www/index.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/www/index.tcl,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/www/index.tcl 16 Sep 2002 00:00:23 -0000 1.1
@@ -0,0 +1,28 @@
+ad_page_contract {
+
+ Lists all the enabled surveys
+ a user is eligable to complete.
+
+ @author philg@mit.edu
+ @author nstrug@arsdigita.com
+ @date 28th September 2000
+ @cvs-id $Id: index.tcl,v 1.1 2002/09/16 00:00:23 daveb Exp $
+} {
+
+} -properties {
+ surveys:multirow
+}
+
+set package_id [ad_conn package_id]
+
+set context_bar [ad_context_bar]
+
+set user_id [ad_maybe_redirect_for_registration]
+
+set admin_p [ad_permission_p $package_id admin]
+
+db_multirow surveys survey_select {}
+
+
+ad_return_template
+
Index: openacs-4/packages/survey/www/master.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/www/Attic/master.adp,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/www/master.adp 16 Sep 2002 00:00:23 -0000 1.1
@@ -0,0 +1,8 @@
+
+@title@
+@title@
+
+ @context_bar@
+
+
+
Index: openacs-4/packages/survey/www/one-respondent.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/survey/www/one-respondent.adp,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/survey/www/one-respondent.adp 16 Sep 2002 00:00:23 -0000 1.1
@@ -0,0 +1,30 @@
+
+Answers to @survey_name@
+@context_bar@
+
+ @description@
+
+
+
+ @responses.pretty_submission_date@ |
+
+
+ @responses.pretty_submission_date@
+
+
+
+
+
+
+
+ @responses.answer_summary@
+
+
+
+
+ 