Index: openacs-4/contrib/packages/survey/survey.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/Attic/survey.info,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/survey.info 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,32 @@
+
+
+
+
+ Survey
+ Surveys
+ f
+ f
+
+
+ Dave Bauer
+ Luke Pond
+ Malte Sussdorff
+ New version of survey package for dotLRN/OpenACS 5.0
+ dotLRN
+ New version of survey package for dotLRN/OpenACS 5.0
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/contrib/packages/survey/sql/oracle/survey-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/oracle/Attic/survey-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/oracle/survey-create.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,475 @@
+-- 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 2003/09/19 16:48:03 rmello 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.create_privilege('survey_create_section');
+-- acs_privilege.create_privilege('survey_modify_section');
+-- acs_privilege.create_privilege('survey_delete_section');
+
+-- acs_privilege.add_child('survey_admin_survey','survey_create_section');
+-- acs_privilege.add_child('survey_admin_survey','survey_modify_section');
+-- acs_privilege.add_child('survey_admin_survey','survey_delete_section');
+
+-- acs_privilege.add_child('write','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_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_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 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');
+
+
+create table surveys (
+ survey_id 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 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,
+ public_p char(1) default 't'
+ constraint surveys_public_p_ck
+ check(public_p in ('t','f')),
+ status_bar_color varchar(100) default null
+ constraint surveys_status_bar_colors
+ check(status_bar_color in ('blue','red','green','yellow')),
+ template integer references survey_templates(template_id)
+);
+
+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')),
+ 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')),
+ -- are there any branches dependant from this section?
+ branched_p char(1) default 'f'
+ constraint survey_sections_branched_p_ck
+ check (branched_p in ('t','f')),
+ block_section_p char(1) default 'f'
+ constraint survey_sections_block_p_ck
+ check (block_section_p in ('t','f')),
+ page_break_p char(1) default 't'
+ constraint survey_sections_break_p_ck
+ check (page_break_p in ('t','f'))
+);
+
+create index survey_sections_survey_id_fk on survey_sections(survey_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'))
+);
+
+
+create table survey_questions (
+ question_id constraint survey_q_question_id_fk
+ references acs_objects (object_id)
+ on delete cascade
+ constraint survey_q_question_id_pk
+ primary key,
+ section_id 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 clob
+ constraint survey_q_question_text_nn
+ not null,
+ question_html_p char(1) default 'f'
+ constraint survey_q_question_html_p_ck
+ check(question_html_p in ('t','f')),
+ 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')),
+ summary_type varchar(20)
+ constraint survey_q_summary_type check
+ (summary_type in ('sum','avg','concat')),
+ -- describing the options of radio button set
+ answer_description varchar(100),
+ predefined_question_id constraint survey_q_pred_q_id_fk
+ references survey_predefined_questions
+);
+
+create table survey_block_questions (
+ block_section_id integer,
+ section_id integer
+ constraint survey_b_q_section_id_fk
+ references survey_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;
+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,
+ predef_choice_id constraint survey_qc_predef_choice_id_fk
+ references survey_predef_question_choices,
+ question_id 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 number,
+ -- lower is earlier
+ sort_order integer,
+ presentation_alignment varchar(15) default 'right'
+ constraint survey_qc_pres_align_ck
+ check (presentation_alignment in ('left','right')),
+ more_info_type varchar(30) default ''
+ constraint survey_qc_more_info_ck
+ check (more_info_type in ('','varchar','number'))
+);
+
+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);
+
+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
+);
+
+
+-- 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)
+ on delete cascade
+ constraint survey_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
+ on delete cascade,
+ title varchar(100),
+ notify_on_comment_p char(1)
+ constraint survey_resp_noton_com_p_ck
+ check(notify_on_comment_p in ('t','f')),
+ finished_p char(1) default 'f'
+ constraint survey_resp_finished_p_ck
+ check (finished_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 constraint survey_qr_response_id_nn
+ not null
+ constraint survey_qr_response_id_fk
+ references survey_responses,
+ question_id 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 constraint survey_qr_choice_id_fk
+ references survey_question_choices
+ on delete cascade,
+ 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,
+ -- file name including extension but not path
+ attachment_file_name varchar(500),
+ attachment_file_type varchar(100), -- this is a MIME type (e.g., image/jpeg)
+ attachment_file_extension varchar(50), -- e.g., "jpg"
+ constraint surv_quest_resp_uq unique(response_id,question_id,choice_id)
+);
+
+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);
+
+
+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
+
+@@ survey-package-create.sql
+@@ survey-notifications-init.sql
+@@ survey-portlet-create.sql
Index: openacs-4/contrib/packages/survey/sql/oracle/survey-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/oracle/Attic/survey-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/oracle/survey-drop.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,70 @@
+--
+-- drop SQL for survey package
+--
+-- by nstrug@arsdigita.com on 29th September 2000
+--
+-- $Id: survey-drop.sql,v 1.1 2003/09/19 16:48:03 rmello Exp $
+
+@@ survey-package-drop.sql
+
+drop view survey_ques_responses_latest;
+drop table survey_question_responses cascade constraints;
+drop view survey_responses_latest;
+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_block_questions;
+drop table survey_branches;
+drop sequence survey_branch_id_sequence;
+drop table survey_conditions;
+drop sequence survey_condition_id_sequence;
+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 ('write','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_section');
+-- acs_privilege.remove_child ('survey_admin_survey','survey_modify_section');
+-- acs_privilege.remove_child ('survey_admin_survey','survey_create_section');
+-- 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_section');
+-- acs_privilege.drop_privilege('survey_modify_section');
+-- acs_privilege.drop_privilege('survey_create_section');
+-- 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/contrib/packages/survey/sql/oracle/survey-notifications-init.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/oracle/Attic/survey-notifications-init.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/oracle/survey-notifications-init.sql 19 Sep 2003 16:48:03 -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/contrib/packages/survey/sql/oracle/survey-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/oracle/Attic/survey-package-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/oracle/survey-package-create.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,579 @@
+-- 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,
+ public_p in surveys.public_p%TYPE default 't',
+ 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,
+ sort_key in survey_sections.sort_key%TYPE default 0,
+ 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',
+ 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_predefined_question
+--
+
+create or replace package survey_predefined_question
+as
+ function new (
+ predefined_question_id in survey_predefined_questions.predefined_question_id%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,
+ 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',
+ question_html_p in survey_predefined_questions.question_html_p%TYPE default 'f',
+ summary_type in survey_predefined_questions.summary_type%TYPE,
+ action_type in survey_predefined_questions.action_type%TYPE default 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,
+ 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
+ ) return acs_objects.object_id%TYPE;
+
+end survey_predefined_question;
+/
+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',
+ question_html_p in survey_questions.question_html_p%TYPE default 'f',
+ summary_type in survey_questions.summary_type%TYPE,
+ answer_description in survey_questions.answer_description%TYPE,
+ predefined_question_id in survey_questions.predefined_question_id%TYPE default null,
+ 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,
+ public_p in surveys.public_p%TYPE default 't',
+ 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, 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.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,
+ sort_key in survey_sections.sort_key%TYPE default 0,
+ 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',
+ 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, sort_key, branch_p, branched_p,page_break_p, block_section_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.page_break_p,new.block_section_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_predefined_question
+as
+ function new (
+ predefined_question_id in survey_predefined_questions.predefined_question_id%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,
+ 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',
+ question_html_p in survey_predefined_questions.question_html_p%TYPE default 'f',
+ summary_type in survey_predefined_questions.summary_type%TYPE,
+ action_type in survey_predefined_questions.action_type%TYPE default 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,
+ 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
+ ) 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 => predefined_question_id,
+ object_type => object_type,
+ creation_date => creation_date,
+ creation_user => creation_user,
+ creation_ip => creation_ip
+ );
+ insert into survey_predefined_questions
+ (predefined_question_id,question_text,abstract_data_type,presentation_type,presentation_options,presentation_alignment,question_html_p,summary_type,action_type,tcl,table_name,column_name,key_name)
+ values
+ (v_predefined_question_id,new.question_text,new.abstract_data_type,new.presentation_type,new.presentation_options,new.presentation_alignment,new.question_html_p,new.summary_type,new.action_type,new.tcl,new.table_name,new.column_name,new.key_name);
+ return v_predefined_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',
+ question_html_p in survey_questions.question_html_p%TYPE default 'f',
+ summary_type in survey_questions.summary_type%TYPE,
+ answer_description in survey_questions.answer_description%TYPE,
+ predefined_question_id in survey_questions.predefined_question_id%TYPE default null,
+ 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,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.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.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 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;
+
Index: openacs-4/contrib/packages/survey/sql/oracle/survey-package-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/oracle/Attic/survey-package-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/oracle/survey-package-drop.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,5 @@
+drop package survey_response;
+drop package survey_question;
+drop package survey_section;
+drop package survey_predefined_question;
+drop package survey;
Index: openacs-4/contrib/packages/survey/sql/oracle/survey-portlet-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/oracle/Attic/survey-portlet-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/oracle/survey-portlet-create.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,159 @@
+
+-- This is free software distributed under the terms of the GNU Public
+-- License version 2 or higher. Full text of the license is available
+-- from the GNU Project: http://www.fsf.org/copyleft/gpl.html
+
+
+
+declare
+ ds_id portal_datasources.datasource_id%TYPE;
+begin
+ ds_id := portal_datasource.new(
+ name => 'survey_portlet',
+ description => 'Displays surveys to take'
+ );
+
+ -- 4 defaults procs
+
+ -- shadeable_p
+ portal_datasource.set_def_param (
+ datasource_id => ds_id,
+ config_required_p => 't',
+ configured_p => 't',
+ key => 'shadeable_p',
+ value => 't'
+);
+
+ -- shaded_p
+ portal_datasource.set_def_param (
+ datasource_id => ds_id,
+ config_required_p => 't',
+ configured_p => 't',
+ key => 'shaded_p',
+ value => 'f'
+);
+
+ -- hideable_p
+ portal_datasource.set_def_param (
+ datasource_id => ds_id,
+ config_required_p => 't',
+ configured_p => 't',
+ key => 'hideable_p',
+ value => 't'
+);
+
+ -- user_editable_p
+ portal_datasource.set_def_param (
+ datasource_id => ds_id,
+ config_required_p => 't',
+ configured_p => 't',
+ key => 'user_editable_p',
+ value => 'f'
+);
+
+ -- link_hideable_p
+ portal_datasource.set_def_param (
+ datasource_id => ds_id,
+ config_required_p => 't',
+ configured_p => 't',
+ key => 'link_hideable_p',
+ value => 't'
+);
+
+
+end;
+/
+show errors
+
+
+declare
+ foo integer;
+begin
+ -- create the implementation
+ foo := acs_sc_impl.new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'survey_portlet'
+ );
+
+end;
+/
+show errors
+
+declare
+ foo integer;
+begin
+
+ -- add all the hooks
+ foo := acs_sc_impl.new_alias (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'GetMyName',
+ 'survey_portlet::get_my_name',
+ 'TCL'
+ );
+
+ foo := acs_sc_impl.new_alias (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'GetPrettyName',
+ 'survey_portlet::get_pretty_name',
+ 'TCL'
+ );
+
+ foo := acs_sc_impl.new_alias (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Link',
+ 'survey_portlet::link',
+ 'TCL'
+ );
+
+ foo := acs_sc_impl.new_alias (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'AddSelfToPage',
+ 'survey_portlet::add_self_to_page',
+ 'TCL'
+ );
+
+ foo := acs_sc_impl.new_alias (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Show',
+ 'survey_portlet::show',
+ 'TCL'
+ );
+
+ foo := acs_sc_impl.new_alias (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Edit',
+ 'survey_portlet::edit',
+ 'TCL'
+ );
+
+ foo := acs_sc_impl.new_alias (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'RemoveSelfFromPage',
+ 'survey_portlet::remove_self_from_page',
+ 'TCL'
+ );
+
+end;
+/
+show errors
+
+declare
+ foo integer;
+begin
+
+ -- Add the binding
+ acs_sc_binding.new (
+ contract_name => 'portal_datasource',
+ impl_name => 'survey_portlet'
+ );
+end;
+/
+show errors
Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-create.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,505 @@
+-- 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 2003/09/19 16:48:03 rmello Exp $
+
+-- We don't want these any longer
+-- select acs_privilege__create_privilege('survey_create_survey', null, null);
+-- select acs_privilege__create_privilege('survey_modify_survey', null, null);
+-- select acs_privilege__create_privilege('survey_delete_survey', null, null);
+-- select acs_privilege__create_privilege('survey_create_question', null, null);
+-- select acs_privilege__create_privilege('survey_modify_question', null, null);
+-- select acs_privilege__create_privilege('survey_delete_question', null, null);
+-- select acs_privilege__create_privilege('survey_take_survey', null, null);
+-- select acs_privilege__create_privilege('survey_admin_survey', null, null);
+
+-- select acs_privilege__create_privilege('survey_create_section', null, null);
+-- select acs_privilege__create_privilege('survey_modify_section', null, null);
+-- select acs_privilege__create_privilege('survey_delete_section', null, null);
+
+
+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('write','survey_take_survey');
+-- select acs_privilege__add_child('admin','survey_admin_survey');
+
+-- select acs_privilege__add_child('survey_admin_survey','survey_create_section');
+-- select acs_privilege__add_child('survey_admin_survey','survey_modify_section');
+-- select acs_privilege__add_child('survey_admin_survey','survey_delete_section');
+
+-- -- 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 ();
+
+commit;
+
+
+
+ select acs_object_type__create_type (
+ 'survey',
+ 'Survey',
+ 'Surveys',
+ 'acs_object',
+ 'surveys',
+ 'survey_id',
+ null,
+ 'f',
+ null,
+ 'survey__name'
+ );
+
+ select acs_object_type__create_type (
+ 'survey_section',
+ 'Survey Section',
+ 'Survey Sections',
+ 'acs_object',
+ 'survey_sections',
+ 'section_id',
+ null,
+ 'f',
+ null,
+ null
+ );
+
+ select acs_object_type__create_type (
+ 'survey_predefined_question',
+ 'Predefined Survey Question',
+ 'Predefined Survey Questions',
+ 'acs_object',
+ 'survey_predefined_questions',
+ 'predefined_question_id',
+ null,
+ 'f',
+ null,
+ null
+ );
+
+ select acs_object_type__create_type (
+ 'survey_question',
+ 'Survey Question',
+ 'Survey Questions',
+ 'acs_object',
+ 'survey_questions',
+ 'question_id',
+ null,
+ 'f',
+ null,
+ null
+ );
+
+ select acs_object_type__create_type (
+ 'survey_response',
+ 'Survey Response',
+ 'Survey Responses',
+ 'acs_object',
+ 'survey_responses',
+ 'response_id',
+ null,
+ 'f',
+ null,
+ null
+ );
+
+
+create table survey_templates (
+-- until we got a better solution
+ template_id integer
+ constraint survey_templates_pk
+ primary key,
+ template_file varchar(400)
+ constraint survey_templates_tpl_file_un
+ 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');
+
+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,
+ public_p boolean default TRUE,
+ status_bar_color varchar(100) default null
+ constraint surveys_status_bar_colors
+ check(status_bar_color in ('blue','red','green','yellow')),
+ template integer
+ constraint surveys_template_fk
+ references survey_templates(template_id)
+);
+
+
+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,
+ sort_key integer default 0 not null,
+ branch_p boolean default FALSE,
+ -- are there any branches dependant from this section?
+ branched_p boolean default FALSE,
+ block_section_p boolean default FALSE,
+ page_break_p boolean default TRUE
+);
+
+create index survey_sections_survey_id_fk on survey_sections(survey_id);
+
+create table survey_predefined_questions (
+ predefined_question_id integer
+ constraint survey_pq_pquestion_id_fk
+ references acs_objects (object_id)
+ constraint survey_pq_pquestion_id_pk
+ primary key,
+ question_text text
+ constraint survey_pq_question_text_nn
+ not null,
+ question_html_p boolean default FALSE,
+ 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 text,
+ 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_pk
+ primary key,
+ question_id integer
+ constraint survey_pqc_question_id_nn
+ not null
+ constraint survey_pqc_question_id_fk
+ references survey_predefined_questions(predefined_question_id),
+ -- human readable
+ label varchar(500)
+ constraint survey_pqc_label_nn
+ not null,
+ -- might be useful for averaging or whatever, generally null
+ numeric_value numeric,
+ -- 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'))
+);
+
+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,
+ question_html_p boolean default FALSE,
+ 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 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')),
+ summary_type varchar(20)
+ constraint survey_q_summary_type check
+ (summary_type in ('sum','avg','concat')),
+ -- describing the options of radio button set
+ answer_description varchar(100),
+ predefined_question_id integer
+ constraint survey_q_pred_q_id_fk
+ references survey_predefined_questions
+);
+
+create index survey_q_sort_order on survey_questions(sort_order);
+create index survey_q_active_p on survey_questions(active_p);
+
+create table survey_block_questions (
+ block_section_id integer,
+ section_id integer
+ constraint survey_b_q_section_id_fk
+ references survey_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;
+
+-- for when a question has a fixed set of responses
+
+create sequence survey_choice_id_sequence;
+
+create table survey_question_choices (
+ choice_id integer
+ constraint survey_qc_choice_id_nn
+ not null
+ constraint survey_qc_choice_id_pk
+ primary key,
+ predef_choice_id integer
+ constraint survey_qc_predef_choice_id_fk
+ references survey_predef_question_choices(choice_id),
+ 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,
+ presentation_alignment varchar(15) default 'right'
+ constraint survey_qc_pres_align_ck
+ check (presentation_alignment in ('left','right')),
+ more_info_type varchar(30) default ''
+ constraint survey_qc_more_info_ck
+ check (more_info_type in ('','varchar','number'))
+);
+
+
+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);
+
+create sequence survey_branch_id_sequence start 1;
+create sequence survey_condition_id_sequence start 1;
+
+create table survey_conditions (
+ condition_id integer
+ constraint survey_conditions_pk
+ primary key,
+ question_id integer
+ constraint survey_conditions_question_id_nn
+ not null
+ constraint survey_conditions_question_id_fk
+ references survey_questions,
+ -- like question_responses
+ choice_id integer
+ constraint survey_conditions_choice_id_fk
+ references survey_question_choices,
+ boolean_answer char(1),
+ constraint survey_c_boolean_answer_ck
+ check(boolean_answer in ('t','f'))
+);
+
+create table survey_branches (
+ branch_id integer
+ constraint survey_branches_pk
+ primary key,
+ section_id integer
+ constraint survey_branches_section_id_fk
+ references survey_sections,
+ after integer
+ constraint survey_branches_after_nn
+ not null
+ constraint survey_branches_after_fk
+ references survey_sections,
+ -- null means always
+ condition integer
+ constraint survey_branches_condition_fk
+ references survey_conditions
+);
+
+-- 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,
+ finished_p boolean default FALSE
+);
+
+-- 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 timestamptz,
+ attachment_answer integer
+ constraint survey_q_response_item_id_fk
+ references cr_revisions(revision_id)
+ on delete cascade,
+ -- file name including extension but not path
+ attachment_file_name varchar(500),
+ attachment_file_type varchar(100), -- this is a MIME type (e.g., image/jpeg)
+ attachment_file_extension varchar(50), -- e.g., "jpg"
+ constraint surv_quest_resp_uq unique(response_id,question_id,choice_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);
+create index survey_response_index on survey_question_responses (response_id, question_id);
+
+create or replace function survey_question_all_choices
+(integer)
+returns text as '
+declare
+ v_answers text;
+ first_p integer;
+ choice_cursor RECORD;
+ v_question_id alias for $1;
+begin
+ v_answers := '''';
+ first_p := 0;
+
+ for choice_cursor in
+ SELECT cast (label as text)
+ from survey_question_choices
+ where question_id = v_question_id
+ order by sort_order
+ loop
+ if first_p = 0 then
+ v_answers := choice_cursor.label::text;
+ first_p := 1;
+ else
+ v_answers := v_answers || '','' || choice_cursor.label::text;
+ end if;
+ end loop;
+
+ return v_answers;
+end' language 'plpgsql';
+
+\i survey-package-create.sql
+\i survey-notifications-init.sql
+
Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-drop.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,71 @@
+--
+-- drop SQL for survey package
+--
+-- by nstrug@arsdigita.com on 29th September 2000
+--
+-- $Id: survey-drop.sql,v 1.1 2003/09/19 16:48:03 rmello Exp $
+
+\i survey-package-drop.sql
+\i survey-notifications-drop.sql
+
+drop view survey_ques_responses_latest;
+drop view survey_responses_latest;
+drop table survey_question_responses;
+drop table survey_responses;
+drop table survey_question_choices;
+drop sequence survey_choice_id_sequence;
+drop table survey_questions;
+drop table survey_predef_question_choices;
+drop table survey_predefined_questions;
+drop table survey_block_questions;
+drop sequence survey_block_section_id_seq;
+drop sequence survey_block_choice_id_seq;
+drop table survey_branches;
+drop sequence survey_branch_id_sequence;
+drop table survey_conditions;
+drop sequence survey_condition_id_sequence;
+drop table survey_sections;
+drop table surveys;
+
+drop table survey_templates;
+
+
+-- 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';
+
+ select acs_object_type__drop_type ('survey_response','f');
+ select acs_object_type__drop_type ('survey_question','f');
+ select acs_object_type__drop_type ('survey_predefined_question','f');
+ select acs_object_type__drop_type ('survey_section','f');
+ select acs_object_type__drop_type ('survey','f');
+
+-- select acs_privilege__remove_child ('admin','survey_admin_survey');
+-- select acs_privilege__remove_child ('write','survey_take_survey');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_delete_question');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_modify_question');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_create_question');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_delete_section');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_modify_section');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_create_section');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_delete_survey');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_modify_survey');
+-- select acs_privilege__remove_child ('survey_admin_survey','survey_create_survey');
+
+-- select acs_privilege__drop_privilege('survey_admin_survey');
+-- select acs_privilege__drop_privilege('survey_take_survey');
+-- select acs_privilege__drop_privilege('survey_delete_question');
+-- select acs_privilege__drop_privilege('survey_modify_question');
+-- select acs_privilege__drop_privilege('survey_create_question');
+-- select acs_privilege__drop_privilege('survey_delete_section');
+-- select acs_privilege__drop_privilege('survey_modify_section');
+-- select acs_privilege__drop_privilege('survey_create_section');
+-- select acs_privilege__drop_privilege('survey_delete_survey');
+-- select acs_privilege__drop_privilege('survey_modify_survey');
+-- select acs_privilege__drop_privilege('survey_create_survey');
+
+
+
Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-notifications-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-notifications-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-notifications-drop.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,75 @@
+create function inline_0 ()
+returns integer as '
+declare
+ row record;
+begin
+ for row in select nt.type_id
+ from notification_types nt
+ where nt.short_name in (''survey_response_notif'')
+ loop
+ perform notification_type__delete(row.type_id);
+ end loop;
+
+ return null;
+end;' language 'plpgsql';
+
+select inline_0();
+drop function inline_0 ();
+
+create function inline_0() returns integer as '
+declare
+ impl_id integer;
+ v_foo integer;
+begin
+ -- the notification type impl
+ impl_id := acs_sc_impl__get_id (
+ ''NotificationType'', -- impl_contract_name
+ ''survey_response_notif_type'' -- impl_name
+ );
+
+ PERFORM acs_sc_binding__delete (
+ ''NotificationType'',
+ ''survey_response_notif_type''
+ );
+
+ v_foo := acs_sc_impl_alias__delete (
+ ''NotificationType'', -- impl_contract_name
+ ''survey_response_notif_type'', -- impl_name
+ ''GetURL'' -- impl_operation_name
+ );
+
+ v_foo := acs_sc_impl_alias__delete (
+ ''NotificationType'', -- impl_contract_name
+ ''survey_response_notif_type'', -- impl_name
+ ''ProcessReply'' -- impl_operation_name
+ );
+
+ select into v_foo type_id
+ from notification_types
+ where sc_impl_id = impl_id
+ and short_name = ''survey_response_notif'';
+
+ perform notification_type__delete (v_foo);
+
+ delete from notification_types_intervals
+ where type_id = v_foo
+ and interval_id in (
+ select interval_id
+ from notification_intervals
+ where name in (''instant'',''hourly'',''daily'')
+ );
+
+ delete from notification_types_del_methods
+ where type_id = v_foo
+ and delivery_method_id in (
+ select delivery_method_id
+ from notification_delivery_methods
+ where short_name in (''email'')
+ );
+
+ return (0);
+end;
+' language 'plpgsql';
+
+select inline_0();
+drop function inline_0();
Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-notifications-init.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-notifications-init.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-notifications-init.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,71 @@
+-- Survey
+--
+-- @author dave@thedesignexperience.org, ben@openforce.biz
+-- @creation-date 2002-08-03
+--
+-- integration with Notifications
+create function inline_0 ()
+returns integer as '
+
+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_alias__new (
+ ''NotificationType'',
+ ''survey_response_notif_type'',
+ ''GetURL'',
+ ''survey::notification::get_url'',
+ ''TCL''
+ );
+
+ v_foo := acs_sc_impl_alias__new (
+ ''NotificationType'',
+ ''survey_response_notif_type'',
+ ''ProcessReply'',
+ ''survey::notification::process_reply'',
+ ''TCL''
+ );
+
+ perform acs_sc_binding__new (
+ ''NotificationType'',
+ ''survey_response_notif_type''
+ );
+
+ v_foo:= notification_type__new (
+ NULL,
+ impl_id,
+ ''survey_response_notif'',
+ ''Survey Response Notification'',
+ ''Notifications for Survey'',
+ current_timestamp,
+ NULL,
+ NULL,
+ 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'');
+return 0;
+end;' language 'plpgsql';
+
+select inline_0();
+
+drop function inline_0();
+
+
Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-package-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-package-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-package-create.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,410 @@
+-- API for survey objects
+
+create or replace function survey__new (integer,varchar,text,boolean,boolean,boolean,boolean,boolean,varchar,varchar,integer,boolean,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__public_p alias for $12; -- default t
+ new__creation_user alias for $13; -- default null
+ new__context_id alias for $14; -- 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, 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;' language 'plpgsql';
+
+create or replace function survey__remove (integer)
+returns integer as '
+declare
+ remove__survey_id alias for $1;
+ 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
+ PERFORM 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
+ PERFORM survey_section__remove(v_section_row.section_id);
+ end loop;
+
+ delete from surveys
+ where survey_id = remove__survey_id;
+
+ PERFORM acs_object__delete(remove__survey_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+create or replace function survey__name (integer)
+returns varchar as '
+declare
+ name__survey_id alias for $1;
+ v_name surveys.name%TYPE;
+begin
+ select into v_name name
+ from surveys
+ where survey_id = name__survey_id;
+
+ return v_name;
+end;' language 'plpgsql';
+
+
+-- API for survey_section objects
+
+create or replace function survey_section__new (integer,integer,varchar,text,boolean,integer,boolean,boolean,boolean,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__sort_key alias for $6; -- default 0
+ new__branch_p alias for $7; -- default f
+ new__branched_p alias for $8; -- default f
+ new__block_section_p alias for $9; -- default f
+ new__page_break_p alias for $10; -- default t
+ new__creation_user alias for $11; -- default null
+ new__context_id alias for $12; -- 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, sort_key, branch_p, branched_p, page_break_p, block_section_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__page_break_p, new__block_section_p);
+
+ return v_section_id;
+
+end;' language 'plpgsql';
+
+create or replace function survey_section__remove (integer)
+returns integer as '
+declare
+ remove__section_id alias for $1;
+ 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
+ PERFORM survey_question__remove(v_question_row.question_id);
+ end loop;
+
+ delete from survey_sections
+ where section_id = remove__section_id;
+
+ PERFORM acs_object__delete(remove__section_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+-- API for predefined questions
+
+create or replace function survey_predefined_question__new (integer,text,varchar,varchar,varchar,varchar,boolean,varchar,varchar,text,varchar,varchar,varchar,integer,integer)
+returns integer as '
+declare
+ new__predefined_question_id alias for $1; -- default null
+ new__question_text alias for $2; -- default null
+ new__abstract_data_type alias for $3; -- default null
+ new__presentation_type alias for $4; -- default null
+ new__presentation_options alias for $5; -- default null
+ new__presentation_alignment alias for $6; -- default below
+ new__question_html_p alias for $7; -- default f
+ new__summary_type alias for $8;
+ new__action_type alias for $9; -- default null
+ new__tcl alias for $10; -- default null
+ new__table_name alias for $11; -- default null
+ new__column_name alias for $12; -- default null
+ new__key_name alias for $13; -- default null
+ new__creation_user alias for $14; -- default null
+ new__context_id alias for $15; -- default null
+ v_predefined_question_id survey_predefined_questions.predefined_question_id%TYPE;
+begin
+ v_predefined_question_id := acs_object__new (
+ new__predefined_question_id,
+ ''survey_predefined_question'',
+ now(),
+ new__creation_user,
+ null,
+ new__context_id
+ );
+
+ insert into survey_predefined_questions
+ (predefined_question_id,question_text,abstract_data_type,presentation_type,presentation_options,presentation_alignment,question_html_p,summary_type,action_type,tcl,table_name,column_name,key_name)
+ values
+ (v_predefined_question_id,new__question_text,new__abstract_data_type,new__presentation_type,new__presentation_options,new__presentation_alignment,new__question_html_p,new__summary_type,new__action_type,new__tcl,new__table_name,new__column_name,new__key_name);
+
+ return v_predefined_question_id;
+
+end;' language 'plpgsql';
+
+-- API for Survey questions
+
+create or replace function survey_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,boolean,varchar,varchar,integer,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__question_html_p alias for $11; -- default f
+ new__summary_type alias for $12;
+ new__answer_description alias for $13;
+ new__predefined_question_id alias for $14; -- default null
+ new__creation_user alias for $15; -- default null
+ new__context_id alias for $16; -- 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, 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__predefined_question_id);
+
+ return v_question_id;
+
+end;' language 'plpgsql';
+
+create or replace function survey_question__remove (integer)
+returns integer as '
+declare
+ remove__question_id alias for $1;
+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;
+
+ PERFORM acs_object__delete(remove__question_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+
+
+-- API for survey responses
+
+create or replace 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 or replace 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 or replace 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 or replace function survey_response__remove(integer)
+returns integer as '
+declare
+ remove__response_id alias for $1;
+ 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
+ PERFORM survey_response__del(v_response_row.response_id);
+ end loop;
+
+ PERFORM survey_response__del(remove__response_id);
+
+ return 0;
+
+end;' language 'plpgsql';
+
+create or replace function survey_response__del (integer)
+returns integer as '
+declare
+ del__response_id alias for $1;
+ 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
+ PERFORM content_item__delete(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;
+ PERFORM acs_object__delete(del__response_id);
+ return 0;
+
+end;' language 'plpgsql';
+
+create or replace function survey_response__boolean_answer (varchar,integer)
+returns varchar as '
+declare
+ boolean_answer__answer alias for $1;
+ boolean_answer__question_id alias for $2;
+
+ v_answer varchar(100);
+ v_presentation_options survey_questions.presentation_options%TYPE;
+ v_split_pos integer;
+begin
+ if boolean_answer__answer is NOT NULL then
+ select into v_presentation_options presentation_options
+ from survey_questions where question_id=boolean_answer__question_id;
+
+ v_split_pos:= instr(v_presentation_options, ''/'');
+
+ if boolean_answer__answer = ''t'' then
+ v_answer:=substr(v_presentation_options, 1, v_split_pos -1 );
+ end if;
+ if boolean_answer__answer = ''f'' then
+ v_answer:=substr(v_presentation_options, v_split_pos + 1 );
+ end if;
+
+ else
+ v_answer := '';
+ end if;
+ return v_answer;
+
+end;' language 'plpgsql';
+
+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);
+
+create 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/contrib/packages/survey/sql/postgresql/survey-package-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-package-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-package-drop.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,9 @@
+-- drop SQL for survey package "packages"
+--
+-- PostgreSQL port by jarkko.m.laine@tut.fi on 15th July 2003
+
+select drop_package('survey_response');
+select drop_package('survey_question');
+select drop_package('survey_section');
+select drop_package('survey_predefined_question');
+select drop_package('survey');
Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-portlet-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-portlet-create.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-portlet-create.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,142 @@
+
+-- This is free software distributed under the terms of the GNU Public
+-- License version 2 or higher. Full text of the license is available
+-- from the GNU Project: http://www.fsf.org/copyleft/gpl.html
+
+create function inline_0 ()
+returns integer as '
+
+declare
+ ds_id portal_datasources.datasource_id%TYPE;
+begin
+
+ ds_id := portal_datasource__new(
+ ''survey_portlet'',
+ ''Displays surveys to take''
+ );
+
+ -- 4 defaults procs
+
+ -- shadeable_p
+ perform portal_datasource__set_def_param (
+ ds_id,
+ ''t'',
+ ''t'',
+ ''shadeable_p'',
+ ''t''
+);
+
+ -- shaded_p
+ perform portal_datasource__set_def_param (
+ ds_id,
+ ''t'',
+ ''t'',
+ ''shaded_p'',
+ ''f''
+);
+
+ -- hideable_p
+ perform portal_datasource__set_def_param (
+ ds_id,
+ ''t'',
+ ''t'',
+ ''hideable_p'',
+ ''t''
+);
+
+ -- user_editable_p
+ perform portal_datasource__set_def_param (
+ ds_id,
+ ''t'',
+ ''t'',
+ ''user_editable_p'',
+ ''f''
+);
+
+ -- link_hideable_p
+ perform portal_datasource__set_def_param (
+ ds_id,
+ ''t'',
+ ''t'',
+ ''link_hideable_p'',
+ ''t''
+);
+
+return 0;
+
+end;' language 'plpgsql';
+
+select inline_0();
+drop function inline_0();
+
+
+-- create the implementation
+select acs_sc_impl__new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'survey_portlet'
+);
+
+-- add all the needed hooks for the implementation
+select acs_sc_impl_alias__new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'GetMyName',
+ 'survey_portlet::get_my_name',
+ 'TCL'
+);
+
+select acs_sc_impl_alias__new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'GetPrettyName',
+ 'survey_portlet::get_pretty_name',
+ 'TCL'
+);
+
+select acs_sc_impl_alias__new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Link',
+ 'survey_portlet::link',
+ 'TCL'
+);
+
+select acs_sc_impl_alias__new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'AddSelfToPage',
+ 'survey_portlet::add_self_to_page',
+ 'TCL'
+);
+
+select acs_sc_impl_alias__new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Show',
+ 'survey_portlet::show',
+ 'TCL'
+);
+
+select acs_sc_impl_alias__new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Edit',
+ 'survey_portlet::edit',
+ 'TCL'
+);
+
+select acs_sc_impl_alias__new (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'RemoveSelfFromPage',
+ 'survey_portlet::remove_self_from_page',
+ 'TCL'
+);
+
+-- Add the binding
+select acs_sc_binding__new (
+ 'portal_datasource',
+ 'survey_portlet'
+);
+
Index: openacs-4/contrib/packages/survey/sql/postgresql/survey-portlet-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/sql/postgresql/Attic/survey-portlet-drop.sql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/sql/postgresql/survey-portlet-drop.sql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,54 @@
+-- remove all the hooks for the implementation
+select acs_sc_impl_alias__delete (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'GetMyName'
+);
+
+select acs_sc_impl_alias__delete (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'GetPrettyName'
+);
+
+select acs_sc_impl_alias__delete (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Link'
+);
+
+select acs_sc_impl_alias__delete (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'AddSelfToPage'
+);
+
+select acs_sc_impl_alias__delete (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Show'
+);
+
+select acs_sc_impl_alias__delete (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'Edit'
+);
+
+select acs_sc_impl_alias__delete (
+ 'portal_datasource',
+ 'survey_portlet',
+ 'RemoveSelfFromPage'
+);
+
+-- drop the binding
+select acs_sc_binding__delete (
+ 'portal_datasource',
+ 'survey_portlet'
+);
+
+-- drop the implementation
+select acs_sc_impl__delete (
+ 'portal_datasource',
+ 'survey_portlet'
+);
\ No newline at end of file
Index: openacs-4/contrib/packages/survey/tcl/survey-init.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/tcl/Attic/survey-init.tcl,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/tcl/survey-init.tcl 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,6 @@
+# for csv-export
+#db_foreach surveys "select package_id from apm_packages where package_key='survey'" {
+# ns_log notice "ns_register_proc GET \"[apm_package_url_from_id $package_id]admin/responses-export.csv\" survey_export_csv"
+# ns_register_proc GET "[apm_package_url_from_id $package_id]admin/responses-export.csv" survey_export_csv
+#}
+
Index: openacs-4/contrib/packages/survey/tcl/survey-notification-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/tcl/Attic/survey-notification-procs.tcl,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/tcl/survey-notification-procs.tcl 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,26 @@
+ad_library {
+ Notification procs for Survey
+
+ Currently this is a place holder file. These procs are not currently needeed and have not been implemented.
+
+ At some point the notification procs in survey-procs should be moved into this file.
+
+ @creation-date 2002-10-29
+
+}
+
+namespace eval survey::notification {}
+
+ad_proc -public survey::notification::get_url {
+ object_id
+} {
+ set package_id [db_string get_package_id {}]
+ set package_url [site_node::get_url_from_object_id -object_id $package_id]
+ return "${package_url}admin/one?survey_id=$object_id"
+}
+
+ad_proc -public survey::notification::process_reply {
+ reply_id
+} {
+
+}
\ No newline at end of file
Index: openacs-4/contrib/packages/survey/tcl/survey-notification-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/tcl/Attic/survey-notification-procs.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/tcl/survey-notification-procs.xql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,10 @@
+
+
+
+
+
+ select package_id from surveys
+ where survey_id=:object_id
+
+
+
\ No newline at end of file
Index: openacs-4/contrib/packages/survey/tcl/survey-portal-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/tcl/Attic/survey-portal-procs.tcl,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/tcl/survey-portal-procs.tcl 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,94 @@
+#
+# Copyright (C) 2001, 2002 MIT
+#
+# This file is part of dotLRN.
+#
+# dotLRN is free software; you can redistribute it and/or modify it under the
+# terms of the GNU General Public License as published by the Free Software
+# Foundation; either version 2 of the License, or (at your option) any later
+# version.
+#
+# dotLRN is distributed in the hope that it will be useful, but WITHOUT ANY
+# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
+# FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
+# details.
+#
+
+ad_library {
+
+ Procedures to support the survey portlet
+
+ @creation-date April 2003
+ @author mail@koljalehmann.de
+ @cvs-id $Id: survey-portal-procs.tcl,v 1.1 2003/09/19 16:48:03 rmello Exp $
+
+}
+
+namespace eval survey_portlet {
+
+ ad_proc -private get_my_name {
+ } {
+ return "survey_portlet"
+ }
+
+ ad_proc -private my_package_key {
+ } {
+ return "survey"
+ }
+
+ ad_proc -public get_pretty_name {
+ } {
+ return "Surveys"
+ }
+
+ ad_proc -public link {
+ } {
+ return ""
+ }
+
+ ad_proc -public add_self_to_page {
+ {-portal_id:required}
+ {-package_id:required}
+ } {
+ Adds a news PE to the given portal.
+
+ @param portal_id The page to add self to
+ @param package_id The community with the folder
+
+ @return element_id The new element's id
+ } {
+ return [portal::add_element_parameters \
+ -portal_id $portal_id \
+ -portlet_name [get_my_name] \
+ -value $package_id \
+ -pretty_name [get_pretty_name] \
+ ]
+ }
+
+ ad_proc -public remove_self_from_page {
+ {-portal_id:required}
+ {-package_id:required}
+ } {
+ Removes a news PE from the given page or the package_id of the
+ news package from the portlet if there are others remaining
+
+ @param portal_id The page to remove self from
+ @param package_id
+ } {
+ portal::remove_element_parameters \
+ -portal_id $portal_id \
+ -portlet_name [get_my_name] \
+ -value $package_id
+ }
+
+ ad_proc -public show {
+ cf
+ } {
+ } {
+ portal::show_proc_helper \
+ -package_key [my_package_key] \
+ -config_list $cf \
+ -template_src "survey-portlet"
+ }
+
+}
Index: openacs-4/contrib/packages/survey/tcl/survey-procs-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/tcl/Attic/survey-procs-oracle.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/tcl/survey-procs-oracle.xql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,257 @@
+
+
+
+ oracle8.1.6
+
+
+
+ select count(distinct survey_response.initial_user_id(response_id))
+ from
+ survey_responses
+ where survey_id=:survey_id
+
+
+
+
+
+ select min(question_id) as question_id,
+ dbms_lob.substr(question_text) as question_text from
+ survey_questions where section_id=:section_id group by
+ dbms_lob.substr(question_text) order by min(sort_order)
+
+
+
+
+
+ select question_id, required_p
+ from survey_questions where
+ section_id=:section_id and
+ 0=dbms_lob.compare(question_text,(select question_text from
+ survey_questions where question_id=:q_id))
+
+
+
+
+
+
+
+ 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,
+ question_html_p => :question_html_p,
+ summary_type => :summary_type,
+ answer_description => :answer_description,
+ predefined_question_id => :predefined_question_id
+ );
+ end;
+
+
+
+
+
+
+
+ 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,
+ public_p => :public_p
+ );
+ 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,
+ sort_key=>:sort_key,
+ branch_p=>:branch_p,
+ branched_p=>:branched_p,
+ block_section_p=>:block_section_p,
+ page_break_p=>:page_break_p,
+ context_id =>:new_survey_id
+ );
+ end;
+
+
+
+
+
+ select r.initial_response_id, o.creation_user as responding_user_id, r.response_id,
+ u.first_names || ' ' || u.last_name as user_name,
+ edit_p,
+ o.creation_date as response_date, o.creation_user as responding_user_id
+ from (select nvl(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 o.creation_user = u.user_id(+)
+ and r.response_id = o.object_id
+
+
+
+
+
+ select nvl(max(sort_key),0) from
+ survey_sections s,survey_question_responses qr, survey_questions q where
+ qr.response_id=:response_id and qr.question_id=q.question_id and
+ s.section_id=q.section_id
+
+
+
+
+
+select
+ sq.response_id,
+ sq.question_id,
+ sq.email,
+ sq.first_names,
+ sq.last_name,
+ sq.user_id,
+ sq.creation_date,
+ resp.boolean_answer,
+ resp.number_answer,
+ resp.date_answer,
+ resp.varchar_answer,
+ resp.clob_answer,
+ resp.attachment_answer,
+ resp.label
+ from
+ (select
+ sqr.response_id,
+ sqr.question_id,
+ sqr.boolean_answer,
+ sqr.number_answer,
+ sqr.date_answer,
+ sqr.varchar_answer,
+ sqr.clob_answer,
+ sqr.attachment_answer,
+ sqc.label,
+ sqc.sort_order
+ from
+ survey_responses sr,
+ survey_question_responses sqr,
+ survey_question_choices sqc
+ where
+ sr.survey_id=:survey_id
+ and sr.response_id = sqr.response_id
+ and sqr.question_id = sqc.question_id (+)
+ and sqr.choice_id = sqc.choice_id (+)) resp,
+ (select r.response_id,
+ q.question_id,
+ u.email,
+ u.first_names,
+ u.last_name,
+ r.user_id,
+ r.creation_date,
+ q.abstract_data_type,
+ q.sort_order,
+ ss.sort_key
+ from survey_questions q, (select initial_user_id as user_id, creation_date, response_id from survey_responses_latest rt where survey_id=:survey_id) r, cc_users u, survey_sections ss
+ where ss.survey_id=:survey_id
+ and q.section_id=ss.section_id
+ and r.user_id = u.user_id) sq
+ where sq.response_id = resp.response_id (+)
+ and sq.question_id = resp.question_id (+)
+ order by
+ sq.response_id,
+ sq.sort_key,
+ sq.sort_order,
+ sq.question_id,
+ resp.sort_order
+
+
+
+
+
+select
+ sq.response_id,
+ sq.question_id,
+ sq.email,
+ sq.first_names,
+ sq.last_name,
+ sq.user_id,
+ sq.creation_date,
+ resp.boolean_answer,
+ resp.number_answer,
+ resp.date_answer,
+ resp.varchar_answer,
+ resp.clob_answer,
+ resp.attachment_answer,
+ resp.label
+ from
+ (select
+ sqr.response_id,
+ sqr.question_id,
+ sqr.boolean_answer,
+ sqr.number_answer,
+ sqr.date_answer,
+ sqr.varchar_answer,
+ sqr.clob_answer,
+ sqr.attachment_answer,
+ sqc.label,
+ sqc.sort_order
+ from
+ survey_responses sr,
+ survey_question_responses sqr,
+ survey_question_choices sqc
+ where
+ sr.survey_id=:survey_id
+ and sr.response_id = sqr.response_id
+ and sqr.question_id = sqc.question_id (+)
+ and sqr.choice_id = sqc.choice_id (+)) resp,
+ (select r.response_id,
+ q.question_id,
+ u.email,
+ u.first_names,
+ u.last_name,
+ r.user_id,
+ r.creation_date,
+ q.abstract_data_type,
+ q.sort_order,
+ ss.sort_key
+ from survey_questions q, (select initial_user_id as user_id, creation_date, response_id from survey_responses_latest rt where survey_id=:survey_id) r, cc_users u, survey_sections ss
+ where ss.survey_id=:survey_id
+ and q.section_id=ss.section_id
+ and r.user_id = u.user_id(+)) sq
+ where sq.response_id = resp.response_id (+)
+ and sq.question_id = resp.question_id (+)
+ order by
+ sq.response_id,
+ sq.sort_key,
+ sq.sort_order,
+ sq.question_id,
+ resp.sort_order
+
+
+
+
+
Index: openacs-4/contrib/packages/survey/tcl/survey-procs-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/tcl/Attic/survey-procs-postgresql.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/tcl/survey-procs-postgresql.xql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,267 @@
+
+
+
+ postgresql7.1
+
+
+
+ select count(distinct survey_response__initial_user_id(response_id))
+ from
+ survey_responses
+ where survey_id=:survey_id
+
+
+
+
+
+
+ select min(question_id) as question_id,
+ question_text
+ from survey_questions
+ where section_id=:section_id
+ group by question_text
+ order by min(sort_order)
+
+
+
+
+
+ select question_id, required_p
+ from survey_questions where
+ section_id=:section_id and
+ question_text = (select question_text from
+ survey_questions where question_id=:q_id)
+
+
+
+
+
+
+ SELECT survey_question__new (
+ NULL,
+ :section_id,
+ :new_sort_order,
+ :question_text,
+ :abstract_data_type,
+ :required_p,
+ :active_p,
+ :presentation_type,
+ :presentation_options,
+ :presentation_alignment,
+ :question_html_p,
+ :summary_type,
+ :answer_description,
+ :predefined_question_id,
+ :user_id,
+ :section_id
+ );
+
+
+
+
+
+
+
+ select survey__new (
+ NULL,
+ :name,
+ :description,
+ :description_html_p,
+ :single_response_p,
+ :editable_p,
+ :enabled_p,
+ :single_section_p,
+ :type,
+ :display_type,
+ :package_id,
+ :public_p,
+ :user_id,
+ :package_id
+ );
+
+
+
+
+
+ select survey_section__new (
+ NULL,
+ :new_survey_id,
+ :name,
+ :description,
+ :description_html_p,
+ :sort_key,
+ :branch_p,
+ :branched_p,
+ :block_section_p,
+ :page_break_p,
+ :user_id,
+ :package_id
+ );
+
+
+
+
+
+ select r.initial_response_id, o.creation_user as 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
+ left outer join cc_users u on (o.creation_user = u.user_id)
+ where r.response_id=:response_id
+ and r.response_id = o.object_id
+
+
+
+
+
+ select coalesce(max(sort_key),0) from
+ survey_sections s,survey_question_responses qr, survey_questions q
+ where
+ qr.response_id=:response_id and qr.question_id=q.question_id and s.section_id=q.section_id
+
+
+
+
+
+select
+ sq.response_id,
+ sq.question_id,
+ sq.email,
+ sq.first_names,
+ sq.last_name,
+ sq.user_id,
+ sq.creation_date,
+ resp.boolean_answer,
+ resp.number_answer,
+ resp.date_answer,
+ resp.varchar_answer,
+ resp.clob_answer,
+ resp.attachment_answer,
+ resp.label
+ from
+ (select
+ sqr.response_id,
+ sqr.question_id,
+ sqr.boolean_answer,
+ sqr.number_answer,
+ sqr.date_answer,
+ sqr.varchar_answer,
+ sqr.clob_answer,
+ sqr.attachment_answer,
+ sqc.label,
+ sqc.sort_order
+ from
+ survey_responses sr
+ inner join
+ survey_question_responses sqr
+ on (sr.response_id = sqr.response_id)
+ right outer join
+ survey_question_choices sqc
+ on (sqr.question_id = sqc.question_id
+ and sqr.choice_id = sqc.choice_id)
+ where
+ sr.survey_id=:survey_id) resp
+ right outer join
+ (select r.response_id,
+ q.question_id,
+ u.email,
+ u.first_names,
+ u.last_name,
+ r.user_id,
+ r.creation_date,
+ q.abstract_data_type,
+ q.sort_order,
+ ss.sort_key
+ from survey_questions q, (select initial_user_id as user_id, creation_date, response_id from survey_responses_latest rt where survey_id=:survey_id) r, cc_users u, survey_sections ss
+ where ss.survey_id=:survey_id
+ and q.section_id=ss.section_id
+ and r.user_id = u.user_id) sq
+ on (sq.response_id = resp.response_id
+ and sq.question_id = resp.question_id)
+ order by
+ sq.response_id,
+ sq.sort_key,
+ sq.sort_order,
+ sq.question_id,
+ resp.sort_order
+
+
+
+
+
+select
+ sq.response_id,
+ sq.question_id,
+ sq.email,
+ sq.first_names,
+ sq.last_name,
+ sq.user_id,
+ sq.creation_date,
+ resp.boolean_answer,
+ resp.number_answer,
+ resp.date_answer,
+ resp.varchar_answer,
+ resp.clob_answer,
+ resp.attachment_answer,
+ resp.label
+ from
+
+ (select
+ sqr.response_id,
+ sqr.question_id,
+ sqr.boolean_answer,
+ sqr.number_answer,
+ sqr.date_answer,
+ sqr.varchar_answer,
+ sqr.clob_answer,
+ sqr.attachment_answer,
+ sqc.label,
+ sqc.sort_order
+ from
+ survey_responses sr
+ inner join
+ survey_question_responses sqr
+ on (sr.response_id = sqr.response_id)
+ right outer join survey_question_choices sqc
+ on (sqr.question_id = sqc.question_id
+ and sqr.choice_id = sqc.choice_id)
+ where
+ sr.survey_id=:survey_id) resp left outer join
+
+ (select r.response_id,
+ q.question_id,
+ u.email,
+ u.first_names,
+ u.last_name,
+ r.user_id,
+ r.creation_date,
+ q.abstract_data_type,
+ q.sort_order,
+ ss.sort_key
+ from survey_questions q
+ inner join survey_sections ss on
+ (q.section_id=ss.section_id)
+ inner join
+ (select initial_user_id as user_id, survey_id, creation_date, response_id from survey_responses_latest rt where survey_id=:survey_id) r
+ on (ss.survey_id = r.survey_id)
+ right outer join cc_users u on
+ (r.user_id = u.user_id)
+ where ss.survey_id=:survey_id) sq
+
+ on (sq.response_id = resp.response_id
+ and sq.question_id = resp.question_id)
+ order by
+ sq.response_id,
+ sq.sort_key,
+ sq.sort_order,
+ sq.question_id,
+ resp.sort_order
+
+
+
+
+
Index: openacs-4/contrib/packages/survey/tcl/survey-procs.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/tcl/Attic/survey-procs.tcl,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/tcl/survey-procs.tcl 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,974 @@
+# /tcl/survey-procs.tcl
+
+ad_library {
+
+ Support procs for simple survey module, most important being
+ survey_question_add_to_form instead, which uses the form manager to add a question to a form
+
+ @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 {![info exists survey_info(survey_id)]} {
+ # survey doesn't exist, caller has to handle this in their
+ # own way
+ return
+ }
+ # 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
+ }
+
+ # quote description where needed, name is usually needed quoted
+ set survey_info(name_unquoted) $survey_info(name)
+ set survey_info(name) [ad_quotehtml $survey_info(name)]
+ set survey_info(description_unquoted) $survey_info(description)
+ if {$survey_info(description_html_p)=="f"} {
+ set survey_info(description) [ad_quotehtml $survey_info(description)]
+ }
+
+ # These aren't used anywhere but cause a lot of load to the db, so just kick them out,
+ # I think, they are not so useful - Kolja
+ # 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 get_section_info {
+ {-section_id ""}
+} {
+ creates a tcl array variable named "section_info" in the caller's environment,
+ which contains key/value pairs for all properties of the requested section.
+
+} {
+ upvar section_info section_info
+
+ db_1row get_info_by_section_id "" -column_array section_info
+ if {![info exists section_info(survey_id)]} {
+ # section doesn't exist, caller has to handle this in their
+ # own way
+ return
+ }
+
+ # quote description where needed, name is usually needed quoted
+ set section_info(name_unquoted) $section_info(name)
+ set section_info(name) [ad_quotehtml $section_info(name)]
+ set section_info(description_unquoted) $section_info(description)
+ if {$section_info(description_html_p)=="f"} {
+ set section_info(description) [ad_quotehtml $section_info(description)]
+ }
+}
+
+ad_proc -public survey_section_add_to_form {
+ form
+ section_id
+ {response_id ""}
+} {
+ adds the questions for the specified section to the specified form. the forms must have been created with ad_form or template::form:create. This uses survey_question_add_to_form, unless the section is a block section
+} {
+
+ get_section_info -section_id $section_id
+
+ # create the section
+ template::form::section $form "$section_info(name)"
+
+ template::element create $form section_info$section_id \
+ -widget inform \
+ -label "" \
+ -value $section_info(description)
+
+ # Is there a template for the section?
+ set template_filename "[template::get_resource_path]/forms/survey/sections/${section_id}.adp"
+ if {[file exists $template_filename]} {
+ set section_template [template::util::read_file $template_filename]
+ uplevel [list set section_template($section_info(name)) $section_template]
+ }
+
+ # If this is a block section, generate a block question, else add questions one by one
+ if {$section_info(block_section_p)=="t"} {
+ # display as block
+ # for this, the "options"-parameter has to be built, which is somewhat complicated
+ set explanation ""
+ set options [list]
+ set all_choices [list]
+ set old_block 0
+ db_foreach block_choices "" {
+ # replace {xxx.gif} by image inclusion
+ regsub -all "\{(\[^\{\}\]*)\}" $label {} label
+ if {$old_block!=$block_section_id && [llength $all_choices]>0} {
+ set old_block $block_section_id
+ lappend choice_lists $all_choices
+ set all_choices [list $label]
+ } else {
+ set old_block $block_section_id
+ lappend all_choices $label
+ }
+ }
+ lappend choice_lists $all_choices
+ lappend options [list $explanation $choice_lists]
+
+ set question_list [db_list_of_lists questions ""]
+ foreach question $question_list {
+ set q_id [lindex $question 0]
+ set question_text [lindex $question 1]
+ set details [db_list_of_lists question_details ""]
+ set detail_list [list]
+ foreach detail $details {
+ set question_id [lindex $detail 0]
+ set required_p [lindex $detail 1]
+ set choice_id_list [db_list choice_ids ""]
+ lappend detail_list [list "response_to_question.$question_id" $required_p $choice_id_list]
+ }
+ lappend options [list $question_text $detail_list]
+ }
+
+ if {![empty_string_p $response_id]} {
+ set responses [db_list checked ""]
+ } else {
+ set responses [list]
+ }
+
+ template::element::create $form "section$section_id" \
+ -datatype integer \
+ -widget block \
+ -options $options \
+ -value $responses
+ } else {
+ db_foreach all_questions "" {
+ # add all questions to the form
+ survey_question_add_to_form $form $question_id $response_id
+ }
+ }
+}
+
+ad_proc -public survey_question_add_to_form {
+ form
+ question_id
+ {response_id ""}
+} {Adds the question to the form. the form has to be created by ad_form or with template::form::create. 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 ""
+
+ set user_value ""
+
+ db_1row predefined_question_id ""
+ if {$predefined_question_id!=[db_null]} {
+ # question is a predefined question. If it has already been answered, get the last response value from the db
+ # if it is a predefined question with action_type="db" get the value from the db instead.
+ db_1row predefined_question_data ""
+ if {$action_type == "db"} {
+ # select preselected value from the db
+ set user_id [ad_get_user_id]
+ set user_value [db_string ignore "select $column_name from $table_name where $key_name=:user_id"]
+ set choice_id $user_value
+ set boolean_answer $user_value
+ set clob_answer $user_value
+ set number_answer $user_value
+ set varchar_answer $user_value
+ set date_answer $user_value
+ set attachment_answer $user_value
+ } else {
+ # select preselected value from latest response
+ set user_id [ad_get_user_id]
+ if {[db_0or1row locate_predef_preselect ""]} {
+ set count 0
+ db_foreach prev_response_query_predef {} {
+ incr count
+
+ if {$presentation_type == "checkbox"} {
+ set selected_choices($choice_id) "t"
+ }
+ } if_no_rows {
+ set choice_id 0
+ set boolean_answer ""
+ set clob_answer ""
+ set number_answer ""
+ set varchar_answer ""
+ set date_answer ""
+ set attachment_answer ""
+ }
+ set edit_previous_response_p "t"
+ } else {
+ set choice_id 0
+ set boolean_answer ""
+ set clob_answer ""
+ set number_answer ""
+ set varchar_answer ""
+ set date_answer ""
+ set attachment_answer ""
+ }
+ }
+ } else {
+ if {$edit_previous_response_p == "t"} {
+ set user_id [ad_get_user_id]
+
+ set count 0
+ db_foreach prev_response_query {} {
+ incr count
+
+ if {$presentation_type == "checkbox"} {
+ set selected_choices($choice_id) "t"
+ }
+ } if_no_rows {
+ set choice_id 0
+ set boolean_answer ""
+ set clob_answer ""
+ set number_answer ""
+ set varchar_answer ""
+ set date_answer ""
+ set attachment_answer ""
+ }
+ }
+ }
+
+ if {$edit_previous_response_p == "t"} {
+ switch -- $abstract_data_type {
+ "choice" {
+ set user_value $choice_id
+ }
+ "shorttext" {
+ set user_value $varchar_answer
+ }
+ "boolean" {
+ set user_value $boolean_answer
+ }
+ "integer" -
+ "number" {
+ set user_value $number_answer
+ }
+ "text" {
+ set user_value $clob_answer
+ }
+ "date" {
+ set user_value $date_answer
+ }
+ "blob" {
+ if {![empty_string_p $attachment_answer]} {
+ set package_id [ad_conn package_id]
+ set filename [db_string get_file_name ""]
+ regsub "_$response_id$" $filename "" filename
+ set user_value "Uploaded file: \"$filename\""
+ }
+ }
+ }
+ }
+
+ # add the form elements, depending on the presentation type
+ switch -- $presentation_type {
+ "upload_file" {
+ template::element::create $form $element_name \
+ -widget file \
+ -label "$question_text" \
+ -required_p $required_p \
+ -value $user_value
+ }
+ "textbox" {
+ set html [ad_decode $presentation_options "large" {size 70} "medium" {size 40} {size 10}]
+ template::element::create $form $element_name \
+ -datatype text \
+ -widget text \
+ -label "$question_text" \
+ -value $user_value \
+ -html $html \
+ -required_p $required_p
+ }
+ "textarea" {
+ set html [ad_decode $presentation_options "large" {rows 20 cols 65} "medium" {rows 15 cols 55} {rows 8 cols 35}]
+ template::element::create $form $element_name \
+ -datatype text \
+ -widget textarea \
+ -label "$question_text" \
+ -value $user_value \
+ -html $html \
+ -required_p $required_p
+ }
+ "date" {
+ #set options {format "DD Month YYYY"}
+ template::element::create $form $element_name \
+ -datatype date \
+ -widget date \
+ -label "$question_text" \
+ -value $user_value \
+ -required_p $required_p
+ }
+ "select" {
+ if { $abstract_data_type == "boolean" } {
+ 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 options "{$choice_t t} {$choice_f f}"
+ } else {
+ # at some point, we may want to add a UI option for the admin
+ # to sepcify multiple or not for select
+ set optionlist [list]
+ db_foreach question_choices "" {
+ lappend optionlist [list $label $choice_id]
+ }
+ set options $optionlist
+ }
+ template::element::create $form $element_name \
+ -datatype text \
+ -widget select \
+ -label "$question_text" \
+ -value $user_value \
+ -options $options \
+ -required_p $required_p
+ }
+ "radio" {
+ set widget "text(radio)"
+ if { $abstract_data_type == "boolean" } {
+ 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 options "{$choice_t t} {$choice_f f}"
+ } else {
+ set optionlist [list]
+ db_foreach question_choices_2 "" {
+ # replace {xxx.gif} by image inclusion
+ regsub -all "\{(\[^\{\}\]*)\}" $label {} label
+ lappend optionlist [list $label $choice_id]
+ }
+ set options $optionlist
+ }
+ template::element::create $form $element_name \
+ -datatype text \
+ -widget radio \
+ -label "$question_text" \
+ -value $user_value \
+ -options $options \
+ -required_p $required_p
+ }
+
+ "checkbox" {
+ set choices [list]
+ set optionlist [list]
+ db_foreach question_choices_3 "" {
+ lappend optionlist [list $label $choice_id]
+ }
+ set options $optionlist
+ template::element::create $form $element_name \
+ -datatype text \
+ -widget checkbox \
+ -label "$question_text" \
+ -value $user_value \
+ -options $options \
+ -required_p $required_p
+ }
+ }
+}
+
+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 "
\n" good_text
+ # get rid of stupid ^M's
+ regsub -all "\r" $good_text "" good_text
+ return $good_text
+}
+
+ad_proc -public survey_answer_summary_display {response_id {html_p 1}} "Returns a string with the questions and answers. If html_p =t, the format will be html. Otherwise, it will be text. If a list of category_ids is provided, the questions will be limited to that set of categories." {
+
+ set return_string ""
+ set question_id_previous ""
+
+ set last_section_id ""
+ db_foreach summary "" {
+ if {$section_id != $last_section_id} {
+ append return_string "
[ad_quotehtml $section_name]
"
+ set last_section_id $section_id
+ }
+
+ if {$question_id == $question_id_previous} {
+ continue
+ }
+
+ if $html_p {
+ append return_string "# $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 {}]
+ regsub "_$response_id$" $filename "" 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_nextval survey_choice_id_sequence]
+ 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
+ if package_id is specific it copies the 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 {
+ db_1row get_section_info ""
+ 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"]
+ if {![empty_string_p $community_id]} {
+ set community_name [dotlrn_community::get_community_name $community_id]
+ } else {
+ set community_name "No Community"
+ }
+ set community_url "[ad_parameter -package_id [ad_acs_kernel_id] SystemURL][dotlrn_community::get_community_url $community_id]"
+ }
+ db_1row get_response_info {}
+
+ 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
+}
+
+ad_proc survey_next_sections {
+ {-edit_p "f"}
+ survey_id
+ section_id
+ response_id
+} {
+ returns the following sections in the survey. First get a list of all sections after the one just completed, then see, if their condition (in case of branching) holds. If yes or if there are no conditions, add the section. Repeat this, until a condition cannot be decided or a section has page_break_p="t"
+} {
+ if {$section_id==0} {
+ # no last section given, get the last one answered or the first one if editing
+ if {$edit_p=="t"} {
+ set sort_key 0
+ } else {
+ set sort_key [db_string get_last_answered_section_sort_key ""]
+ }
+ } else {
+ set sort_key [db_string sort_key ""]
+ }
+
+ set result [list]
+ set sections [db_list_of_lists get_following_sections ""]
+ foreach section $sections {
+ set section_id [lindex $section 0]
+ set page_break_p [lindex $section 1]
+ set ok "t"
+ db_foreach get_condition "" {
+ if {![db_0or1row get_response ""]} {
+ set ok "f"
+ }
+ }
+ if {$ok=="t"} {
+ lappend result $section_id
+ }
+ if {$page_break_p=="t" && $result!=[list]} {
+ return $result
+ }
+ }
+ return $result
+}
+
+ad_proc survey_status_bar {
+ section_id
+} {
+ returns a status bar, indicating the progress within a survey
+} {
+ db_1row percentage ""
+ if {[empty_string_p $status_bar_color]} {
+ return ""
+ }
+ set html "
+
+
+
+
+
+
+ $percentage% Complete
+ |
+
+
+
+
+
+
+
+
+
+
+
+ |
+
+
+
+ |
+
+
+
+ |
+
+
+ |
+
+
+"
+ return $html
+}
+
+ad_proc survey_block_section_candidate_p {
+ section_id
+} {
+ return 1 if the section can be made a block section, 0 if not
+ criteria:
+ - at least one question
+ - All questions are multiple choice, radio button
+ - For each question_text there must be the same set(s) of choices
+} {
+ set return_value 1
+ db_foreach get_questions "" {
+ if {$abstract_data_type != "choice" || $presentation_type!= "radio"} {
+ set return_value 0
+ }
+ if {[info exist question($question_text)]} {
+ append question($question_text) ":$all_choices"
+ } else {
+ set question($question_text) $all_choices
+ }
+ } if_no_rows {
+ set return_value 0
+ }
+ set count 0
+ foreach name [array names question] {
+ if {$count==0} {
+ set answers $question($name)
+ } else {
+ if {$question($name)!=$answers} {
+ return 0
+ }
+ }
+ incr count
+ }
+ return $return_value
+}
+
+ad_proc survey_export_csv {
+ conn
+ args
+} {
+ Return csv data of a survey to a user, invoked by ns_register_proc from survey-init.tcl
+} {
+ ad_page_contract {
+
+ CSV export of responses to particular survey.
+
+ @author sebastian@arsdigita.com
+ @date July 2000
+ @cvs-id $Id: survey-procs.tcl,v 1.1 2003/09/19 16:48:03 rmello Exp $
+
+ } {
+
+ survey_id:integer,notnull
+ {anonymous_p:boolean "t"}
+ {unique_users_p f}
+ on_what_id:optional,integer
+ {start:naturalnum 1}
+ {end:naturalnum 10000}
+ }
+ set csv_export ""
+ set package_id [ad_conn package_id]
+ ad_require_permission $package_id admin
+
+ set n_responses [db_string get_n_responses {}]
+
+ if {$n_responses==0} {
+ #get_survey_info -survey_id $survey_id
+ #set context_bar [ad_context_bar [list "one?[export_url_vars survey_id]" $survey_info(name)] "CSV Export"]
+ #_return_template "no-responses"
+ ad_return_complaint 1 "No responses"
+ return
+ }
+
+ set question_id_list [list]
+ set responses_table survey_responses
+
+ set headline "email,first_names,last_name,user_id,submission_date,response_id"
+
+ db_foreach get_question_data_types {} {
+ lappend question_id_list $question_id
+ regsub -all {"} $question_text {""} question_text
+ append headline ",\"$question_text"
+ append headline "\""
+ set question_data_type($question_id) $abstract_data_type
+ switch -- $abstract_data_type {
+ "date" {
+ set question_column($question_id) "date_answer"
+ }
+ "text" {
+ set question_column($question_id) "clob_answer"
+ }
+ "shorttext" {
+ set question_column($question_id) "varchar_answer"
+ }
+ "boolean" {
+ set question_column($question_id) "boolean_answer"
+ }
+ "integer" -
+ "number" {
+ set question_column($question_id) "number_answer"
+ }
+ "choice" {
+ set question_column($question_id) "label"
+ }
+ "blob" {
+ set question_column($question_id) "attachment_answer"
+ }
+ default {
+ set question_column($question_id) "varchar_answer"
+ }
+ }
+
+ }
+
+ # We're looping over all question responses in survey_question_responses
+
+ set current_response_id ""
+ set current_response ""
+ set current_question_id ""
+ set current_question_list [list]
+ set csv_export ""
+ set r 0
+ ReturnHeaders "text/comma-separated-values"
+ ns_write "$headline \r\n"
+
+ if {$anonymous_p=="t"} {
+ set query "get_all_survey_question_responses_with_anonymous"
+ } else {
+ set query "get_all_survey_question_responses"
+ }
+ db_foreach $query "" {
+
+ if { $response_id != $current_response_id } {
+ if { ![empty_string_p $current_question_id] } {
+ append current_response ",\"[join $current_question_list ","]\""
+ }
+
+ if { ![empty_string_p $current_response_id] } {
+ append csv_export "$current_response \r\n"
+ }
+ set current_response_id $response_id
+ set one_response [list $email $first_names $last_name $user_id $creation_date $response_id]
+ regsub -all {"} $one_response {""} one_response
+ set current_response "\"[join $one_response {","}]\""
+
+ set current_question_id ""
+ set current_question_list [list]
+ }
+
+ set response_value [set $question_column($question_id)]
+ # Properly escape double quotes to make Excel & co happy
+ regsub -all {"} $response_value {""} response_value
+
+ # Remove any CR or LF characters that may be present in text fields
+ regsub -all {[\r\n]} $response_value {} response_value
+
+ if { $question_id != $current_question_id } {
+ if { ![empty_string_p $current_question_id] } {
+ append current_response ",\"[join $current_question_list ","]\""
+ }
+ set current_question_id $question_id
+ set current_question_list [list]
+ }
+ # decode boolean answers
+ if {$question_data_type($question_id)=="boolean"} {
+ set response_value [survey_decode_boolean_answer -response $response_value -question_id $question_id]
+ }
+ if {$question_data_type($question_id)=="blob"} {
+ set response_value [db_string get_filename {} -default ""]
+ }
+ lappend current_question_list $response_value
+
+ incr r
+ if {$r>99} {
+ ns_write "${csv_export} "
+ set csv_export ""
+ set rows 0
+ }
+
+ }
+
+ if { ![empty_string_p $current_question_id] } {
+ append current_response ",\"[join $current_question_list ","]\""
+ }
+ if { ![empty_string_p $current_response_id] } {
+ append csv_export "$current_response\r\n"
+ }
+ if {[empty_string_p $csv_export]} {
+ set csv_export "\r\n"
+ }
+ ns_write $csv_export
+
+ ns_conn close
+}
+
+
+
Index: openacs-4/contrib/packages/survey/tcl/survey-procs.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/tcl/Attic/survey-procs.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/tcl/survey-procs.xql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,490 @@
+
+
+
+
+
+ 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 min(section_id) as section_id
+ from survey_sections
+ where survey_id = :survey_id
+
+
+
+
+
+
+ select count(*) from dotlrn_member_rels_full
+ where rel_type='dotlrn_member_rel'
+ and community_id=:community_id
+
+
+
+
+
+
+ select s.*,
+ o.creation_user, o.creation_date
+ from survey_sections s, acs_objects o
+ where o.object_id = :section_id
+ and s.section_id = o.object_id
+
+
+
+
+
+ select label,block_section_id from survey_block_questions where section_id=:section_id order by block_section_id,
+ sort_order
+
+
+
+
+
+ select choice_id from survey_question_choices where
+ question_id=:question_id order by sort_order
+
+
+
+
+
+
+ select name, description, description_html_p, block_section_p from survey_sections where section_id=:section_id
+
+
+
+
+
+
+ select choice_id as selected_choice_id from
+ survey_question_responses qr, survey_questions q where
+ section_id=:section_id and q.question_id=qr.question_id and response_id=:response_id
+
+
+
+
+
+ select question_id, question_text from survey_questions where
+ section_id=:section_id order by sort_order
+
+
+
+
+
+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 predefined_question_id from survey_questions where
+ question_id=:question_id
+
+
+
+
+
+ select action_type,table_name,column_name,key_name from
+ survey_predefined_questions where predefined_question_id=:predefined_question_id
+
+
+
+
+
+
+select question_id as preselect_question_id, object_id as response_id
+from survey_question_responses, acs_objects
+ where question_id in (
+ select question_id from survey_questions where
+ predefined_question_id=:predefined_question_id)
+ and object_type='survey_response'
+ and response_id=object_id
+ and creation_date=(select min(creation_date) from
+ survey_question_responses, acs_objects
+ where question_id in (
+ select question_id from survey_questions where
+ predefined_question_id=:predefined_question_id)
+ and object_type='survey_response'
+ and response_id=object_id)
+
+
+
+
+
+
+
+select
+ choice_id,
+ boolean_answer,
+ clob_answer,
+ number_answer,
+ varchar_answer,
+ to_char(date_answer,'YYYY MM DD HH24 MI SS') as date_answer,
+ attachment_answer
+ from survey_question_responses
+ where question_id = :preselect_question_id
+ and response_id = :response_id
+
+
+
+
+
+
+select
+ choice_id,
+ boolean_answer,
+ clob_answer,
+ number_answer,
+ varchar_answer,
+ to_char(date_answer,'YYYY MM DD HH24 MI SS') as date_answer,
+ attachment_answer
+ from survey_question_responses
+ where question_id = :question_id
+ and response_id = :response_id
+
+
+
+
+
+
+ select name from cr_items i, cr_revisions r where
+ i.item_id=r.item_id and r. revision_id=:attachment_answer
+
+
+
+
+
+
+ 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
+ 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,
+ s.name as section_name
+from
+ survey_questions sq,
+ survey_question_responses sqr,
+ survey_sections s
+where
+ sqr.response_id = :response_id
+ and sq.question_id = sqr.question_id
+ and sq.active_p = 't'
+ and s.section_id=sq.section_id
+order by s.sort_key,sq.sort_order
+
+
+
+
+
+
+
+ select name from cr_items i, cr_revisions r where
+ i.item_id=r.item_id and
+ revision_id=:attachment_answer
+
+
+
+
+
+
+ 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 sort_order = sort_order + 1
+ where section_id = :section_id
+ and sort_order > :after
+
+
+
+
+
+
+ update survey_questions
+ set question_text = :question_text
+ where question_id = :new_question_id
+
+
+
+
+
+ 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, presentation_alignment, more_info_type, predef_choice_id)
+ values
+ (:new_choice_id, :new_question_id, :label,
+ :numeric_value, :sort_order, :presentation_alignment,
+ :more_info_type, :predef_choice_id)
+
+
+
+
+
+
+
+select * from surveys where survey_id=:survey_id
+
+
+
+
+
+ select section_id from survey_sections where survey_id=:survey_id
+
+
+
+
+
+select name, description, description_html_p, sort_key, branch_p,
+branched_p, block_section_p, page_break_p from survey_sections where section_id=:section_id
+
+
+
+
+
+ update survey_sections set description=:description
+ where section_id=:new_section_id
+
+
+
+
+
+select question_id from survey_questions
+ where section_id in (select section_id from survey_sections
+ where survey_id=:survey_id)
+
+
+
+
+
+
+ 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 presentation_options
+ from survey_questions
+ where question_id=:question_id
+
+
+
+
+
+ select sort_key from survey_sections where section_id=:section_id
+
+
+
+
+
+ select section_id,page_break_p from survey_sections where
+ survey_id=:survey_id and sort_key>:sort_key order by sort_key
+
+
+
+
+
+ select question_id,choice_id,boolean_answer
+ from survey_conditions c, survey_branches b
+ where b.section_id=:section_id and b.condition=c.condition_id
+
+
+
+
+
+ select 1
+ from survey_question_responses where response_id=:response_id
+ and question_id=:question_id
+ and (choice_id=:choice_id or boolean_answer=:boolean_answer)
+
+
+
+
+
+
+
+ select to_char((sort_key-1)/(select max(sort_key) from survey_sections ssec
+ where ssec.survey_id=ss.survey_id)*100,'999') as percentage,
+ status_bar_color from survey_sections ss, surveys s where
+ section_id=:section_id and s.survey_id=ss.survey_id
+
+
+
+
+
+
+
+
+ select question_text,abstract_data_type, presentation_type,
+ survey_question_all_choices(question_id) as all_choices
+ from survey_questions where section_id=:section_id
+
+
+
+
+
+
+
+
+ select count(*) from survey_responses_latest
+ where survey_id=:survey_id
+
+
+
+
+
+
+ select question_id, abstract_data_type, q.sort_order,
+ question_text
+ from survey_questions q, survey_sections s
+ where s.survey_id = :survey_id
+ and s.section_id=q.section_id
+ order by s.sort_key,q.sort_order,q.question_id
+
+
+
+
+
+
+ select title from cr_revisions where
+ revision_id=:attachment_answer
+
+
+
+
Index: openacs-4/contrib/packages/survey/www/index-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/www/Attic/index-oracle.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/www/index-oracle.xql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,22 @@
+
+
+oracle8.1.6
+
+
+
+select s.survey_id, s.name, s.editable_p, s.single_response_p,
+sub.response_id, to_char(sub.creation_date,'Month FMDD, YYYY') as creation_date
+from surveys s,
+(select creation_date, survey_id, response_id from survey_responses
+ sr, acs_objects o where o.creation_user=:user_id and
+ o.object_id=sr.response_id
+) sub
+where s.package_id=:package_id
+and s.enabled_p='t'
+and 't' = acs_permission.permission_p(s.survey_id, :user_id, 'write')
+and s.survey_id=sub.survey_id(+)
+order by upper(s.name), creation_date desc
+
+
+
+
Index: openacs-4/contrib/packages/survey/www/index-postgresql.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/www/Attic/index-postgresql.xql,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/www/index-postgresql.xql 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,22 @@
+
+
+
+postgresql7.1
+
+
+
+select s.survey_id, s.name, s.editable_p, s.single_response_p,
+sub.response_id, to_char(sub.creation_date,'Month FMDD, YYYY') as creation_date
+from surveys s left outer join
+(select creation_date, survey_id, response_id from survey_responses
+ sr, acs_objects o where o.creation_user=:user_id and
+ o.object_id=sr.response_id
+) sub on (s.survey_id=sub.survey_id)
+where s.package_id=:package_id
+and s.enabled_p='t'
+and 't' = acs_permission__permission_p(s.survey_id, :user_id, 'write')
+order by upper(s.name), creation_date desc
+
+
+
+
Index: openacs-4/contrib/packages/survey/www/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/www/Attic/index.adp,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/www/index.adp 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,33 @@
+
+Surveys
+@context_bar;noquote@
+
+
+
+
+ - @surveys.name@
+
+
+
+
+
+
+ - Previous response on: @surveys.creation_date@
+
+
+
+
+
+
+
+
+
+
+ - No surveys active
+
+
+
+
Index: openacs-4/contrib/packages/survey/www/index.tcl
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/www/Attic/index.tcl,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/www/index.tcl 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,30 @@
+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 2003/09/19 16:48:03 rmello Exp $
+} {
+
+} -properties {
+ surveys:multirow
+}
+
+set package_id [ad_conn package_id]
+
+set context_bar [ad_context_bar]
+
+# Allow public surveys
+# set user_id [ad_maybe_redirect_for_registration]
+set user_id [ad_conn user_id]
+
+set admin_p [ad_permission_p $package_id admin]
+
+db_multirow surveys survey_select {}
+
+
+ad_return_template
+
Index: openacs-4/contrib/packages/survey/www/one-respondent.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/survey/www/Attic/one-respondent.adp,v
diff -u -N
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/contrib/packages/survey/www/one-respondent.adp 19 Sep 2003 16:48:03 -0000 1.1
@@ -0,0 +1,30 @@
+
+Answers to @survey_name@
+@context_bar;noquote@
+
+ @description@
+
+
+
+ @responses.pretty_submission_date@ |
+
+
+ @responses.pretty_submission_date@
+
+
+
+
+
+
+
+ @responses.answer_summary;noquote@ |
+
+
+
+