Index: openacs-4/packages/simple-survey/sql/postgresql/survsimp-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/postgresql/Attic/survsimp-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/sql/postgresql/survsimp-create.sql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,583 @@ +-- 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 +-- +-- $Id: survsimp-create.sql,v 1.1 2001/06/15 01:06:39 gilbertw Exp $ + +-- we expect this to be replaced with a more powerful survey +-- module, to be developed by buddy@ucla.edu, so we prefix +-- all of our Oracle structures with "survsimp" + +-- this is a PL/SQL function that used to be in the standard ACS 3.x core - not in the +-- current ACS 4.0 core however... +create function logical_negation(boolean) +returns boolean as ' +declare + true_or_false alias for $1; +begin + if true_or_false is null then + return null; + else + if true_or_false = ''f'' then + return ''t''; + else + return ''f''; + end if; + end if; +end;' language 'plpgsql'; + +create function inline_0 () +returns integer as ' +begin + PERFORM acs_privilege__create_privilege(''survsimp_create_survey'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_modify_survey'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_delete_survey'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_create_question'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_modify_question'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_delete_question'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_take_survey'', null, null); + PERFORM acs_privilege__create_privilege(''survsimp_admin_survey'', null, null); + + return 0; + +end;' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + + +begin; + -- temporarily drop this trigger to avoid a data-change violation + -- on acs_privilege_hierarchy_index while updating the child privileges. + drop trigger acs_priv_hier_ins_del_tr on acs_privilege_hierarchy; + + select acs_privilege__add_child('survsimp_admin_survey','survsimp_create_survey'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_modify_survey'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_delete_survey'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_create_question'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_modify_question'); + select acs_privilege__add_child('survsimp_admin_survey','survsimp_delete_question'); + + select acs_privilege__add_child('read','survsimp_take_survey'); + + -- re-enable the trigger before the last insert to force the + -- acs_privilege_hierarchy_index table to be updated. + + create trigger acs_priv_hier_ins_del_tr after insert or delete + on acs_privilege_hierarchy for each row + execute procedure acs_priv_hier_ins_del_tr (); + + select acs_privilege__add_child('admin','survsimp_admin_survey'); + +end; + + + +create function inline_1 () +returns integer as ' +begin + + PERFORM acs_object_type__create_type ( + ''survsimp_survey'', + ''Simple Survey'', + ''Simple Surveys'', + ''acs_object'', + ''survsimp_surveys'', + ''survey_id'', + null, + ''f'', + null, + null + ); + + PERFORM acs_object_type__create_type ( + ''survsimp_question'', + ''Simple Survey Question'', + ''Simple Survey Questions'', + ''acs_object'', + ''survsimp_questions'', + ''question_id'', + null, + ''f'', + null, + null + ); + + PERFORM acs_object_type__create_type ( + ''survsimp_response'', + ''Simple Survey Response'', + ''Simple Survey Responses'', + ''acs_object'', + ''survsimp_responses'', + ''response_id'', + null, + ''f'', + null, + null + ); + + PERFORM acs_rel_type__create_type ( + ''user_blob_response_rel'', + ''User Blob Response'', + ''User Blob Responses'', + ''relationship'', + ''survsimp_question_responses'', + ''user_id'', + ''user_blob_response_rel'', + ''user'', + ''user'', + 1, + 1, + ''content_item'', + null, + 0, + 1 + ); + + + return 0; + +end;' language 'plpgsql'; + +select inline_1 (); +drop function inline_1 (); + +create table survsimp_surveys ( + survey_id integer constraint survsimp_surveys_survey_id_fk + references acs_objects (object_id) + on delete cascade + constraint survsimp_surveys_pk + primary key, + name varchar(100) + constraint survsimp_surveys_name_nn + not null, + -- short, non-editable name we can identify this survey by + short_name varchar(20) + constraint survsimp_surveys_short_name_u + unique + constraint survsimp_surveys_short_name_nn + not null, + description text -- was varchar(4000) + constraint survsimp_surveys_desc_nn + not null, + description_html_p boolean, -- was char(1) + --constraint survsimp_surv_desc_html_p_ck + --check(description_html_p in ('t','f')), + enabled_p boolean, -- was char(1) + -- constraint survsimp_surveys_enabled_p_ck + -- check(enabled_p in ('t','f')), + -- limit to one response per user + single_response_p boolean, -- was char(1) + -- constraint survsimp_sur_single_resp_p_ck + -- check(single_response_p in ('t','f')), + single_editable_p boolean, -- was char(1) + -- constraint survsimp_surv_single_edit_p_ck + -- check(single_editable_p in ('t','f')), + type varchar(20) +); + +-- each question can be + +create table survsimp_questions ( + question_id integer constraint survsimp_q_question_id_fk + references acs_objects (object_id) + on delete cascade + constraint survsimp_q_question_id_pk + primary key, + survey_id integer constraint survsimp_q_survey_id_fk + references survsimp_surveys + on delete cascade, + sort_key integer + constraint survsimp_q_sort_key_nn + not null, + question_text text + constraint survsimp_q_question_text_nn + not null, + abstract_data_type varchar(30) + constraint survsimp_q_abs_data_type_ck + check (abstract_data_type in ('text', 'shorttext', 'boolean', 'number', 'integer', 'choice','date')), + required_p boolean, -- was char(1) + -- constraint survsimp_q_required_p_ck + -- check (required_p in ('t','f')), + active_p boolean, -- was char(1) + -- constraint survsimp_q_qctive_p_ck + -- check (active_p in ('t','f')), + presentation_type varchar(20) + constraint survsimp_q_pres_type_nn + not null + constraint survsimp_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 survsimp_q_pres_alignment_ck + check(presentation_alignment in ('below','beside')) +); + + +-- for when a question has a fixed set of responses + +create sequence survsimp_choice_id_seq; +create view survsimp_choice_id_sequence as select nextval('survsimp_choice_id_seq') as nextval; + +create table survsimp_question_choices ( + choice_id integer constraint survsimp_qc_choice_id_nn + not null + constraint survsimp_qc_choice_id_pk + primary key, + question_id integer constraint survsimp_qc_question_id_nn + not null + constraint survsimp_qc_question_id_fk + references survsimp_questions + on delete cascade, + -- human readable + label varchar(500) constraint survsimp_qc_label_nn + not null, + -- might be useful for averaging or whatever, generally null + numeric_value number, + -- lower is earlier + sort_order integer +); + +-- 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 survsimp_responses ( + response_id integer constraint survsimp_resp_response_id_fk + references acs_objects (object_id) + on delete cascade + constraint srvsimp_resp_response_id_pk + primary key, + survey_id integer constraint survsimp_resp_survey_id_fk + references survsimp_surveys + on delete cascade, + title varchar(100), + notify_on_comment_p boolean -- was char(1) + -- constraint survsimp_resp_noton_com_p_ck + -- check(notify_on_comment_p in ('t','f')) +); + + +-- mbryzek: 3/27/2000 +-- Sometimes you release a survey, and then later decide that +-- you only want to include one response per user. The following +-- view includes only the latest response from all users +-- create or replace view survsimp_responses_unique as +-- select r1.* from survsimp_responses r1 +-- where r1.response_id=(select max(r2.response_id) +-- from survsimp_responses r2 +-- where r1.survey_id=r2.survey_id +-- and r1.user_id=r2.user_id); + +create view survsimp_responses_unique as +select r1.* from survsimp_responses r1, acs_objects a1 +where r1.response_id = (select max(r2.response_id) + from survsimp_responses r2, acs_objects a2 + where r1.survey_id = r2.survey_id + and a1.object_id = r1.response_id + and a2.object_id = r2.response_id + and a1.creation_user = a2.creation_user); + +-- 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 survsimp_question_responses ( + response_id integer constraint survsimp_qr_response_id_nn + not null + constraint survsimp_qr_response_id_fk + references survsimp_responses + on delete cascade, + question_id integer constraint survsimp_qr_question_id_nn + not null + constraint survsimp_qr_question_id_fk + references survsimp_questions + on delete cascade, + -- if the user picked a canned response + choice_id integer constraint survsimp_qr_choice_id_fk + references survsimp_question_choices + on delete cascade, + boolean_answer boolean, + -- was char(1) + -- check(boolean_answer in ('t','f')), + clob_answer text, + number_answer numeric, + varchar_answer text, + date_answer timestamp, + -- columns useful for attachments, column names + -- lifted from file-storage.sql and bboard.sql + -- this is where the actual content is stored + -- attachment_answer blob, + item_id integer + constraint survsimp_q_response_item_id_fk + references cr_items(item_id) + on delete cascade, + content_length integer, + -- 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" +); + + +-- We create a view that selects out only the last response from each +-- user to give us at most 1 response from all users. +create view survsimp_question_responses_un as +select qr.* + from survsimp_question_responses qr, survsimp_responses_unique r + where qr.response_id=r.response_id; + + + +-- sequence for variable names +create sequence survsimp_variable_id_seq; +create view survsimp_variable_id_sequence as select nextval('survsimp_variable_id_seq') as nextval; + + +-- variable names for scored surveys +create table survsimp_variables ( + variable_id integer + constraint survsimp_variable_id_pk + primary key, + variable_name varchar(100) + constraint survsimp_variable_name_nn not null +); + +-- map variable names to surveys +create table survsimp_variables_surveys_map ( + variable_id integer + constraint survsimp_vs_map_var_id_nn not null + constraint survsimp_vs_map_var_id_fk + references survsimp_variables(variable_id) + on delete cascade, + survey_id integer + constraint survsimp_vs_map_sur_id_nn not null + constraint survsimp_vs_map_sur_id_fk + references survsimp_surveys(survey_id) + on delete cascade +); + +-- scores for scored responses +create table survsimp_choice_scores ( + choice_id integer + constraint survsimp_choi_sc_ch_id_nn not null + constraint survsimp_choi_sc_ch_id_fk + references survsimp_question_choices(choice_id) + on delete cascade, + variable_id integer + constraint survsimp_choi_sc_var_id_nn not null + constraint survsimp_choi_sc_var_id_fk + references survsimp_variables(variable_id) + on delete cascade, + score integer + constraint survsimp_choi_sc_sc_nn not null +); + +-- logic for scored survey redirection +create table survsimp_logic ( + logic_id integer primary key, + logic text +); + +create sequence survsimp_logic_id_seq; +create view survsimp_logic_id_sequence as select nextval('survsimp_logic_id_seq') as nextval; + + +-- map logic to surveys +create table survsimp_logic_surveys_map ( + logic_id integer + constraint survsimp_l_s_map_logic_id_nn not null + constraint survsimp_l_s_map_logic_id_fk + references survsimp_logic(logic_id) + on delete cascade, + survey_id integer + constraint survsimp_l_s_map_sur_id_nn not null + constraint survsimp_l_s_map_sur_id_fk + references survsimp_surveys(survey_id) + on delete cascade +); + + +create index survsimp_response_index on survsimp_question_responses (response_id, question_id); + +-- We create a view that selects out only the last response from each +-- user to give us at most 1 response from all users. +-- create or replace view survsimp_question_responses_un as +-- select qr.* +-- from survsimp_question_responses qr, survsimp_responses_unique r +-- where qr.response_id=r.response_id; + +-- +-- constructor function for a survsimp_survey +-- + +-- create or replace package body survsimp_survey +-- procedure new +create function survsimp_survey__new (integer,varchar,varchar,text,boolean,boolean,boolean,boolean,varchar,integer,integer) +returns integer as ' +declare + new__survey_id alias for $1; -- default null + new__name alias for $2; + new__short_name alias for $3; + new__description alias for $4; + new__description_html_p alias for $5; -- default f + new__single_response_p alias for $6; -- default f + new__single_editable_p alias for $7; -- default t + new__enabled_p alias for $8; -- default f + new__type alias for $9; -- default general + new__creation_user alias for $10; -- default null + new__context_id alias for $11; -- default null + v_survey_id integer; +begin + v_survey_id := acs_object__new ( + new__survey_id, + ''survsimp_survey'', + now(), + new__creation_user, + null, + new__context_id + ); + + insert into survsimp_surveys + (survey_id, name, short_name, description, + description_html_p, single_response_p, single_editable_p, + enabled_p, type) + values + (v_survey_id, new__name, new__short_name, new__description, + new__description_html_p, new__single_response_p, new__single_editable_p, + new__enabled_p, new__type); + + return v_survey_id; + +end;' language 'plpgsql'; + +-- procedure delete +create function survsimp_survey__delete (integer) +returns integer as ' +declare + delete__survey_id alias for $1; +begin + delete from survsimp_surveys + where survey_id = delete__survey_id; + + PERFORM acs_object__delete(delete__survey_id); + + return 0; + +end;' language 'plpgsql'; + + +-- create or replace package body survsimp_question +-- procedure new +create function survsimp_question__new (integer,integer,integer,text,varchar,boolean,boolean,varchar,varchar,varchar,integer,integer) +returns integer as ' +declare + new__question_id alias for $1; -- default null + new__survey_id alias for $2; -- default null + new__sort_key alias for $3; -- default null + new__question_text alias for $4; -- default null + new__abstract_data_type alias for $5; -- default null + new__required_p alias for $6; -- default t + new__active_p alias for $7; -- default + new__presentation_type alias for $8; -- default null + new__presentation_options alias for $9; -- default null + new__presentation_alignment alias for $10; -- default below + new__creation_user alias for $11; -- default null + new__context_id alias for $12; -- default null + v_question_id integer; +begin + v_question_id := acs_object__new ( + new__question_id, + ''survsimp_question'', + now(), + new__creation_user, + null, + new__context_id + ); + + insert into survsimp_questions + (question_id, survey_id, sort_key, question_text, + abstract_data_type, required_p, active_p, + presentation_type, presentation_options, + presentation_alignment) + values + (v_question_id, new__survey_id, new__sort_key, new__question_text, + new__abstract_data_type, new__required_p, new__active_p, + new__presentation_type, new__presentation_options, + new__presentation_alignment); + + return v_question_id; + +end;' language 'plpgsql'; + +-- procedure delete +create function survsimp_question__delete (integer) +returns integer as ' +declare + delete__question_id alias for $1; +begin + delete from survsimp_questions + where question_id = delete__question_id; + + PERFORM acs_object__delete(delete__question_id); + + return 0; + +end;' language 'plpgsql'; + + +-- create or replace package body survsimp_response +-- procedure new +create function survsimp_response__new(integer,integer,varchar,boolean,integer,varchar,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 + v_response_id integer; +begin + v_response_id := acs_object__new ( + new__response_id, + ''survsimp_response'', + now(), + new__creation_user, + new__creation_ip, + new__context_id + ); + + insert into survsimp_responses + (response_id, survey_id, title, notify_on_comment_p) + values + (v_response_id, new__survey_id, new__title, new__notify_on_comment_p); + + return v_response_id; + +end;' language 'plpgsql'; + + +-- procedure delete +create function survsimp_response__delete(integer) +returns integer as ' +declare + delete__response_id alias for $1; +begin + delete from survsimp_responses + where response_id = delete__response_id; + + PERFORM acs_object__delete(delete__response_id); + + return 0; + +end;' language 'plpgsql'; + Index: openacs-4/packages/simple-survey/sql/postgresql/survsimp-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/sql/postgresql/Attic/survsimp-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/sql/postgresql/survsimp-drop.sql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,72 @@ +-- +-- drop SQL for survsimp package +-- +-- by nstrug@arsdigita.com on 29th September 2000 +-- +-- $Id: survsimp-drop.sql,v 1.1 2001/06/15 01:06:39 gilbertw Exp $ + +select drop_package('survsimp_response'); +select drop_package('survsimp_question'); +select drop_package('survsimp_survey'); + +drop table survsimp_logic_surveys_map; +drop view survsimp_logic_id_sequence; +drop sequence survsimp_logic_id_seq; +drop table survsimp_logic; +drop table survsimp_choice_scores; +drop table survsimp_variables_surveys_map; +drop table survsimp_variables; +drop view survsimp_variable_id_sequence; +drop sequence survsimp_variable_id_seq; +drop view survsimp_question_responses_un; +drop table survsimp_question_responses; +drop view survsimp_responses_unique; +drop table survsimp_responses; +drop table survsimp_question_choices; +drop view survsimp_choice_id_sequence; +drop sequence survsimp_choice_id_seq; +drop table survsimp_questions; +drop table survsimp_surveys; + +-- nuke all created objects +-- need to do this before nuking the types +delete from acs_objects where object_type = 'survsimp_response'; +delete from acs_objects where object_type = 'survsimp_question'; +delete from acs_objects where object_type = 'survsimp_survey'; + +create function inline_0 () +returns integer as ' +begin + PERFORM acs_rel_type__drop_type (''user_blob_response_rel'',''f''); + + PERFORM acs_object_type__drop_type (''survsimp_response'',''f''); + PERFORM acs_object_type__drop_type (''survsimp_question'',''f''); + PERFORM acs_object_type__drop_type (''survsimp_survey'',''f''); + + PERFORM acs_privilege__remove_child (''admin'',''survsimp_admin_survey''); + PERFORM acs_privilege__remove_child (''read'',''survsimp_take_survey''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_delete_question''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_modify_question''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_create_question''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_delete_survey''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_modify_survey''); + PERFORM acs_privilege__remove_child (''survsimp_admin_survey'',''survsimp_create_survey''); + + PERFORM acs_privilege__drop_privilege(''survsimp_admin_survey''); + PERFORM acs_privilege__drop_privilege(''survsimp_take_survey''); + PERFORM acs_privilege__drop_privilege(''survsimp_delete_question''); + PERFORM acs_privilege__drop_privilege(''survsimp_modify_question''); + PERFORM acs_privilege__drop_privilege(''survsimp_create_question''); + PERFORM acs_privilege__drop_privilege(''survsimp_delete_survey''); + PERFORM acs_privilege__drop_privilege(''survsimp_modify_survey''); + PERFORM acs_privilege__drop_privilege(''survsimp_create_survey''); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); +drop function inline_0 (); + +drop function logical_negation(boolean); + + Index: openacs-4/packages/simple-survey/tcl/survsimp-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/tcl/survsimp-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/tcl/survsimp-procs-oracle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + + select to_char(creation_date, 'DD/MM/YYYY') + from acs_objects + where object_id = :response_id + + + + + + Index: openacs-4/packages/simple-survey/tcl/survsimp-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/tcl/survsimp-procs-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/tcl/survsimp-procs-postgresql.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,17 @@ + + + + postgresql7.1 + + + + + select to_char(creation_date, 'DD/MM/YYYY') + from acs_objects + where object_id = :response_id + + + + + + Index: openacs-4/packages/simple-survey/tcl/survsimp-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/tcl/survsimp-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/tcl/survsimp-procs.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,129 @@ + + + + + + +select + survey_id, + sort_key, + question_text, + abstract_data_type, + required_p, + active_p, + presentation_type, + presentation_options, + presentation_alignment, + creation_user, + creation_date +from + survsimp_questions, acs_objects +where + object_id = question_id + and question_id = :question_id + + + + + + + select choice_id, label +from survsimp_question_choices +where question_id = :question_id +order by sort_order + + + + + + + select choice_id, label +from survsimp_question_choices +where question_id = :question_id +order by sort_order + + + + + + + select * from survsimp_question_choices +where question_id = :question_id +order by sort_order + + + + + + + select label + from survsimp_question_choices, survsimp_question_responses + where survsimp_question_responses.question_id = :question_id + and survsimp_question_responses.response_id = :response_id + and survsimp_question_choices.choice_id = survsimp_question_responses.choice_id + + + + + + + + select creation_user + from survsimp_surveys + where survey_id = :survey_id + + + + + + + select survey_id, name, description, u.user_id, first_names || ' ' || last_name as creator_name, creation_date +from survsimp_surveys s, $users_table u +where s.creation_user = u.user_id +and creation_date> :since_when +order by creation_date desc + + + + + + + select survey_id from survsimp_surveys where lower(short_name) = lower(:short_name) + + + + + + + + select response_id + from acs_objects, survsimp_responses + where object_id = response_id + and creation_user = :user_id + and survey_id = :survey_id + and creation_date = (select max(creation_date) + from survsimp_responses, acs_objects + where object_id = response_id + and creation_user = :user_id + and survey_id = :survey_id) + + + + + + + + + select + sum(score) + from survsimp_choice_scores, + survsimp_question_responses, survsimp_variables + where + survsimp_choice_scores.choice_id = survsimp_question_responses.choice_id + and survsimp_choice_scores.variable_id = survsimp_variables.variable_id + and survsimp_question_responses.response_id = :response_id + + + + + Index: openacs-4/packages/simple-survey/www/index-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/index-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/index-oracle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,21 @@ + + + + oracle8.1.6 + + + + + select survey_id, name + from survsimp_surveys, acs_objects + where object_id = survey_id + and context_id = :package_id + and acs_permission.permission_p(object_id, :user_id, 'survsimp_take_survey') = 't' + and enabled_p = 't' + order by upper(name) + + + + + + Index: openacs-4/packages/simple-survey/www/index-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/index-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/index-postgresql.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,21 @@ + + + + postgresql7.1 + + + + + select survey_id, name + from survsimp_surveys, acs_objects + where object_id = survey_id + and context_id = :package_id + and acs_permission__permission_p(object_id, :user_id, 'survsimp_take_survey') = 't' + and enabled_p = 't' + order by upper(name) + + + + + + Index: openacs-4/packages/simple-survey/www/one-respondent-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/one-respondent-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/one-respondent-oracle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + + select response_id, creation_date, to_char(creation_date, 'DD MONTH YYYY') as pretty_submission_date + from survsimp_responses, acs_objects + where survey_id = :survey_id + and response_id = object_id + and creation_user = :user_id + order by creation_date desc + + + + + + Index: openacs-4/packages/simple-survey/www/one-respondent-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/one-respondent-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/one-respondent-postgresql.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,19 @@ + + + + postgresql7.1 + + + + select response_id, creation_date, to_char(creation_date, 'DD MONTH YYYY') as pretty_submission_date + from survsimp_responses, acs_objects + where survey_id = :survey_id + and response_id = object_id + and creation_user = :user_id + order by creation_date desc + + + + + + Index: openacs-4/packages/simple-survey/www/one-respondent.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/one-respondent.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/one-respondent.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + + select 1 from survsimp_surveys where survey_id = :survey_id + + + + + + + + select name, description + from survsimp_surveys + where survey_id = :survey_id + + + + + + Index: openacs-4/packages/simple-survey/www/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/one.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/one.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,47 @@ + + + + + + + select 1 from survsimp_surveys where survey_id = :survey_id + + + + + + + + select name, description, single_response_p, single_editable_p + from survsimp_surveys where survey_id = :survey_id + + + + + + + + select count(response_id) + from survsimp_responses, acs_objects + where response_id = object_id + and creation_user = :user_id + and survey_id = :survey_id + + + + + + + + + select question_id + from survsimp_questions + where survey_id = :survey_id + and active_p = 't' + order by sort_key + + + + + + Index: openacs-4/packages/simple-survey/www/process-response-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/process-response-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/process-response-oracle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,98 @@ + + + + oracle8.1.6 + + + + + begin + :1 := survsimp_response.new ( + response_id => :response_id, + survey_id => :survey_id, + context_id => :survey_id, + creation_user => :user_id + ); + end; + + + + + + + + + insert into survsimp_question_responses + (response_id, question_id, clob_answer) + values + (:response_id, :question_id, empty_clob()) + returning clob_answer into :1 + + + + + + + + + begin + :1 := content_item.new ( + name => :name, + creation_ip => :creation_ip + ); + end; + + + + + + + + + begin + :1 := acs_rel.new ( + rel_type => 'user_blob_response_rel', + object_id_one => :user_id, + object_id_two => :item_id); + end; + + + + + + + + + begin + :1 := content_revision.new ( + title => 'A Blob Response', + item_id => :item_id, + text => 'not_important', + mime_type => :guessed_file_type, + creation_date => sysdate, + creation_user => :user_id, + creation_ip => :creation_ip + ); + + update cr_items + set live_revision = :1 + where item_id = :item_id; + + end; + + + + + + + + + update cr_revisions + set content = empty_blob() + where revision_id = :revision_id + returning content into :1 + + + + + Index: openacs-4/packages/simple-survey/www/process-response-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/process-response-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/process-response-postgresql.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,117 @@ + + + + postgresql7.1 + + + + select survsimp_response__new ( + :response_id, + :survey_id, + null, + 'f', + :user_id, + :creation_ip, + :survey_id + ) + + + + + + + insert into survsimp_question_responses + (response_id, question_id, clob_answer) + values + (:response_id, :question_id, :clob_answer) + + + + + + + + select content_item__new ( + :name, + null, + null, + null, + now(), + :user_id, + null, + :creation_ip, + 'content_item', + 'content_revision', + null, + null, + 'text/plain', + null, + null, + 'file' + ) + + + + + + + + + select acs_rel__new ( + null, + 'user_blob_response_rel', + :user_id, + :item_id, + null, + null, + null + ) + + + + + + + + + declare + v_revision_id integer; + begin + v_revision_id := content_revision__new ( + 'A Blob Response', + null, + now(), + :guessed_file_type, + null, + 'not_important', + :item_id, + null, + now(), + :user_id, + :creation_ip + ); + + update cr_items + set live_revision = v_revision_id + where item_id = :item_id; + + return v_revision_id; + + end; + + + + + + + + + update cr_revisions + set content = '[cr_create_content_file $item_id $revision_id $tmp_filename]' + where revision_id = :revision_id + + + + + + Index: openacs-4/packages/simple-survey/www/process-response.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/process-response.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/process-response.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,139 @@ + + + + + + + select 1 from survsimp_surveys where survey_id = :survey_id + + + + + + + + + select question_id, question_text, abstract_data_type, presentation_type, required_p + from survsimp_questions + where survey_id = :survey_id + and active_p = 't' + order by sort_key + + + + + + + + + select question_id, question_text, abstract_data_type, presentation_type, required_p + from survsimp_questions + where survey_id = :survey_id + and active_p = 't' + order by sort_key + + + + + + + + insert into survsimp_question_responses (response_id, question_id, choice_id) + values (:response_id, :question_id, :response_value) + + + + + + + insert into survsimp_question_responses (response_id, question_id, choice_id) + values (:response_id, :question_id, :response_value) + + + + + + + insert into survsimp_question_responses (response_id, question_id, varchar_answer) + values (:response_id, :question_id, :response_value) + + + + + + + insert into survsimp_question_responses (response_id, question_id, boolean_answer) + values (:response_id, :question_id, :response_value) + + + + + + + insert into survsimp_question_responses (response_id, question_id, number_answer) + values (:response_id, :question_id, :response_value) + + + + + + + insert into survsimp_question_responses (response_id, question_id, date_answer) + values (:response_id, :question_id, :response_value) + + + + + + + select type from survsimp_surveys where survey_id = :survey_id + + + + + + + select name from survsimp_surveys where survey_id = :survey_id + + + + + + + select variable_name, sum(score) as sum_of_scores + from survsimp_choice_scores, survsimp_question_responses, survsimp_variables + where survsimp_choice_scores.choice_id = survsimp_question_responses.choice_id + and survsimp_choice_scores.variable_id = survsimp_variables.variable_id + and survsimp_question_responses.response_id = :response_id + group by variable_name + + + + + + + select logic from survsimp_logic, survsimp_logic_surveys_map + where survsimp_logic.logic_id = survsimp_logic_surveys_map.logic_id + and survey_id = :survey_id + + + + + + + + insert into survsimp_question_responses + (response_id, question_id, item_id, + content_length, + attachment_file_name, attachment_file_type, + attachment_file_extension) + values + (:response_id, :question_id, :item_id, + :content_length, + :response_value, :guessed_file_type, + :file_extension) + + + + + Index: openacs-4/packages/simple-survey/www/view-attachment-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/view-attachment-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/view-attachment-oracle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,16 @@ + + + + oracle8.1.6 + + + + select attachment_answer + from survsimp_question_responses + where response_id = $response_id and question_id = $question_id + + + + + + Index: openacs-4/packages/simple-survey/www/view-attachment-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/view-attachment-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/view-attachment-postgresql.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,17 @@ + + + + postgresql7.1 + + + + FIX ME LOB +select attachment_answer + from survsimp_question_responses + where response_id = $response_id and question_id = $question_id + + + + + + Index: openacs-4/packages/simple-survey/www/view-attachment.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/view-attachment.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/view-attachment.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select attachment_file_type + from survsimp_question_responses + where response_id = :response_id and question_id = :question_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/description-edit-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/description-edit-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/description-edit-2.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + + update survsimp_surveys + set description = :description, + description_html_p = :desc_html + where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/description-edit.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/description-edit.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/description-edit.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select name as survey_name, description, description_html_p as desc_html +from survsimp_surveys +where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/edit-logic-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/edit-logic-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/edit-logic-2-oracle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,15 @@ + + + + oracle8.1.6 + + + +update survsimp_logic +set logic = empty_clob() +where logic_id = :logic_id +returning logic into :1 + + + + Index: openacs-4/packages/simple-survey/www/admin/edit-logic-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/edit-logic-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/edit-logic-2-postgresql.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,14 @@ + + + + postgresql7.1 + + + +update survsimp_logic +set logic = :logic +where logic_id = :logic_id + + + + Index: openacs-4/packages/simple-survey/www/admin/edit-logic.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/edit-logic.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/edit-logic.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + + select type from survsimp_surveys where survey_id = :survey_id + + + + + + + select name from survsimp_surveys where survey_id = :survey_id + + + + + + + select logic, survsimp_logic.logic_id from survsimp_logic, survsimp_logic_surveys_map +where survsimp_logic.logic_id = survsimp_logic_surveys_map.logic_id +and survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/index.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/index.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/index.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select survey_id, name, enabled_p +from survsimp_surveys +order by enabled_p desc, upper(name) + + + + + Index: openacs-4/packages/simple-survey/www/admin/modify-responses-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/modify-responses-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/modify-responses-2.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + update survsimp_question_choices + set label = :trimmed_response + where choice_id = :choice_id + + + + + + + update survsimp_choice_scores + set score = :score + where choice_id = :choice_id + and variable_id = :variable_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/modify-responses.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/modify-responses.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/modify-responses.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,49 @@ + + + + + + select name from survsimp_surveys where survey_id=:survey_id + + + + + + + select question_text +from survsimp_questions +where question_id = :question_id + + + + + + + select variable_name, survsimp_variables.variable_id as variable_id + from survsimp_variables, survsimp_variables_surveys_map + where survsimp_variables.variable_id = survsimp_variables_surveys_map.variable_id + and survey_id = :survey_id + order by variable_name + + + + + + + select choice_id, label from survsimp_question_choices where question_id = :question_id order by choice_id + + + + + + + select score, survsimp_variables.variable_id as variable_id + from survsimp_choice_scores, survsimp_variables + where survsimp_choice_scores.choice_id = :choice_id + and survsimp_choice_scores.variable_id = survsimp_variables.variable_id + order by variable_name + + + + + Index: openacs-4/packages/simple-survey/www/admin/one-respondent.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/one-respondent.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/one-respondent.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,44 @@ + + + + + + select name as survey_name, description, type +from survsimp_surveys +where survey_id = :survey_id + + + + + + + select first_names, last_name from persons where person_id = :user_id + + + + + + + select response_id, creation_date +from survsimp_responses, acs_objects +where response_id = object_id +and creation_user = :user_id +and survey_id = :survey_id +order by creation_date desc + + + + + + + select variable_name, sum(score) as sum_score + from survsimp_choice_scores, survsimp_question_responses, survsimp_variables + where survsimp_choice_scores.choice_id = survsimp_question_responses.choice_id + and survsimp_choice_scores.variable_id = survsimp_variables.variable_id + and survsimp_question_responses.response_id = :response_id + group by variable_name + + + + + Index: openacs-4/packages/simple-survey/www/admin/one.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/one.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/one.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,30 @@ + + + + + +select name as survey_name, +short_name, description as survey_description, +first_names || ' ' || last_name as creator_name, creation_user, +creation_date, (case when enabled_p = 't' then 'Enabled' when enabled_p = 'f' then 'Disabled' end) as survey_status, enabled_p, +(case when single_response_p = 't' then 'One' when single_response_p = 'f' then 'Multiple' end) as survey_response_limit, +(case when single_editable_p = 't' then 'Editable' when single_editable_p = 'f' then 'Non-editable' end) as survey_editable_single, type +from survsimp_surveys, acs_objects, persons +where object_id = survey_id +and person_id = creation_user +and survey_id = :survey_id + + + + + + + select question_id, sort_key, active_p, required_p +from survsimp_questions +where survey_id = :survey_id +order by sort_key + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-active-toggle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/Attic/question-active-toggle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-active-toggle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update survsimp_questions set active_p = logical_negation(active_p) +where survey_id = :survey_id +and question_id = :question_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-add-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-add-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-add-2.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,23 @@ + + + + + + select name, description, type + from survsimp_surveys + where survey_id = :survey_id + + + + + + + select count(variable_name) as n_variables + from survsimp_variables, survsimp_variables_surveys_map + where survsimp_variables.variable_id = survsimp_variables_surveys_map.variable_id + and survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-add-3-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-add-3-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-add-3-oracle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,36 @@ + + + + oracle8.1.6 + + + + + begin + :1 := survsimp_question.new ( + question_id => :question_id, + survey_id => :survey_id, + sort_key => :sort_key, + question_text => empty_clob(), + abstract_data_type => :abstract_data_type, + presentation_type => :presentation_type, + presentation_alignment => :presentation_alignment, + active_p => :active_p, + required_p => :required_p, + context_id => :survey_id, + creation_user => :user_id + ); + end; + + + + + + + + select survsimp_choice_id_sequence.nextval as choice_id from dual + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-add-3-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-add-3-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-add-3-postgresql.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,35 @@ + + + + postgresql7.1 + + + + + select survsimp_question__new ( + :question_id, + :survey_id, + :sort_key, + :question_text, + :abstract_data_type, + :required_p, + :active_p, + :presentation_type, + :presentation_options, + :presentation_alignment, + :user_id, + :survey_id + ) + + + + + + + + select survsimp_choice_id_sequence.nextval as choice_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-add-3.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-add-3.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-add-3.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,64 @@ + + + + + + update survsimp_questions + set sort_key = sort_key + 1 + where survey_id = :survey_id + and sort_key > :after + + + + + + + + update survsimp_questions + set question_text = :question_text + where question_id = :question_id + + + + + + + + insert into survsimp_question_choices + (choice_id, question_id, label, sort_order) + values + (:choice_id, :question_id, :trimmed_response, :count) + + + + + + + insert into survsimp_choice_scores + (choice_id, variable_id, score) + values + (:choice_id, :variable_id, :score) + + + + + + + update survsimp_questions + set sort_key = sort_key + 1 + where survey_id = :survey_id + and sort_key > :after + + + + + + + + select case when count(*) = 0 then 0 else 1 end from survsimp_questions where question_id = :question_id + + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-add.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + select name, description, type +from survsimp_surveys +where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-delete-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-delete-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-delete-2.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,43 @@ + + + + + + select survey_id from survsimp_questions where question_id = :question_id + + + + + + + delete from survsimp_question_responses where question_id = :question_id + + + + + + + + delete from survsimp_choice_scores + where choice_id in (select choice_id from survsimp_question_choices + where question_id = :question_id) + + + + + + + + delete from survsimp_question_choices where question_id = :question_id + + + + + + + delete from survsimp_questions where question_id = :question_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-delete-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-delete-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-delete-oracle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + + begin + survsimp_question.delete (:question_id); + end; + + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-delete-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-delete-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-delete-postgresql.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,15 @@ + + + + postgresql7.1 + + + + + select survsimp_question__delete (:question_id); + + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-delete.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,39 @@ + + + + + + select survey_id from survsimp_questions where question_id = :question_id + + + + + + + select count(*) +from survsimp_question_responses +where question_id = :question_id + + + + + + + + delete from survsimp_choice_scores + where choice_id in (select choice_id from survsimp_question_choices + where question_id = :question_id) + + + + + + + + delete from survsimp_question_choices where + question_id = :question_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-modify-text-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-modify-text-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-modify-text-2.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,11 @@ + + + + + + update survsimp_questions set question_text=:question_text where question_id=:question_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-modify-text.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-modify-text.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-modify-text.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,20 @@ + + + + + + select name from survsimp_surveys where survey_id=:survey_id + + + + + + + select question_text +from survsimp_questions +where question_id = :question_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-required-toggle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/Attic/question-required-toggle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-required-toggle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update survsimp_questions set required_p = logical_negation(required_p) +where survey_id = :survey_id +and question_id = :question_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/question-swap.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/question-swap.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/question-swap.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,14 @@ + + + + + +update survsimp_questions +set sort_key = (case when sort_key = :sort_key then :next_sort_key when sort_key = :next_sort_key then :sort_key end) +where survey_id = :survey_id +and sort_key in (:sort_key, :next_sort_key) + + + + + Index: openacs-4/packages/simple-survey/www/admin/respondents.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/respondents.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/respondents.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + + select first_names || ' ' || last_name as name, creation_user as user_id, email +from persons, parties, survsimp_responses, acs_objects +where person_id = creation_user +and person_id = party_id +and object_id = response_id +and survey_id = :survey_id +group by creation_user, email, first_names, last_name +order by last_name + + + + + + + select name as survey_name +from survsimp_surveys +where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/response-drill-down.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/response-drill-down.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/response-drill-down.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,53 @@ + + + + + + +select survey_id, question_text +from survsimp_questions +where question_id = :question_id + + + + + + + +select label as response_text +from survsimp_question_choices +where choice_id = :choice_id + + + + + + + select name from survsimp_surveys where survey_id = :survey_id + + + + + + + +select + first_names || ' ' || last_name as responder_name, + person_id, + creation_date +from + acs_objects, + survsimp_responses sr, + persons u, + survsimp_question_responses qr +where + qr.response_id = sr.response_id + and qr.response_id = object_id + and creation_user = person_id + and qr.question_id = :question_id + and qr.choice_id = :choice_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/response-editable-toggle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/Attic/response-editable-toggle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/response-editable-toggle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,12 @@ + + + + + + update survsimp_surveys set single_editable_p = logical_negation(single_editable_p) +where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/response-limit-toggle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/Attic/response-limit-toggle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/response-limit-toggle.xql 15 Jun 2001 01:06:39 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update survsimp_surveys +set single_response_p = logical_negation(single_response_p) +where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/responses-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/responses-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/responses-oracle.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,27 @@ + + + + oracle8.1.6 + + + + select variable_name, to_char(avg(sum_score), '9999.9') as mean_score, + min(sum_score) as min_score, + max(sum_score) as max_score, + count(sum_score) as count_score, + nvl(to_char(stddev_samp(sum_score), '9999.9'), '0.0') as sd_score + from + (select variable_name, sum(score) as sum_score + from survsimp_choice_scores, survsimp_question_responses, survsimp_variables, + survsimp_responses + where survsimp_choice_scores.choice_id = survsimp_question_responses.choice_id + and survsimp_choice_scores.variable_id = survsimp_variables.variable_id + and survsimp_responses.response_id = survsimp_question_responses.response_id + and survey_id = :local_survey_id + group by survsimp_responses.response_id, variable_name) + group by variable_name + + + + + Index: openacs-4/packages/simple-survey/www/admin/responses-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/responses-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/responses-postgresql.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,27 @@ + + + + postgresql7.1 + + + + select variable_name, to_char(avg(sum_score), '9999.9') as mean_score, + min(sum_score) as min_score, + max(sum_score) as max_score, + count(sum_score) as count_score, + coalesce(to_char(stddev_samp(sum_score), '9999.9'), '0.0') as sd_score + from + (select variable_name, sum(score) as sum_score + from survsimp_choice_scores, survsimp_question_responses, survsimp_variables, + survsimp_responses + where survsimp_choice_scores.choice_id = survsimp_question_responses.choice_id + and survsimp_choice_scores.variable_id = survsimp_variables.variable_id + and survsimp_responses.response_id = survsimp_question_responses.response_id + and survey_id = :local_survey_id + group by survsimp_responses.response_id, variable_name) + group by variable_name + + + + + Index: openacs-4/packages/simple-survey/www/admin/responses.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/responses.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/responses.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,84 @@ + + + + + + select type + from survsimp_surveys + where survey_id = :survey_id + + + + + + + select question_id, question_text, abstract_data_type +from survsimp_questions +where survey_id = :survey_id +order by sort_key + + + + + + +select count(*) as n_responses, (case when boolean_answer = 't' then 'True' when boolean_answer = 'f' then 'False') as boolean_answer +from $question_responses_table +where question_id = :question_id +group by boolean_answer +order by boolean_answer desc + + + + + + + select count(*) as n_responses, number_answer +from $question_responses_table +where question_id = :question_id +group by number_answer +order by number_answer + + + + + + + select avg(number_answer) as mean, stddev(number_answer) as standard_deviation +from $question_responses_table +where question_id = :question_id + + + + + + + select count(*) as n_responses, label, qc.choice_id +from $question_responses_table qr, survsimp_question_choices qc +where qr.choice_id = qc.choice_id + and qr.question_id = :question_id +group by label, sort_order, qc.choice_id +order by sort_order + + + + + + + select type + from survsimp_surveys + where survey_id = :survey_id + + + + + + + select count(*) +from $responses_table +where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/survey-category-add-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/survey-category-add-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/survey-category-add-oracle.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,25 @@ + + + + oracle8.1.6 + + + + select + category_id_sequence.nextval from dual + + + + + + + insert into site_wide_category_map + (map_id, category_id, + on_which_table, on_what_id, mapping_date, one_line_item_desc) + values (site_wide_cat_map_id_seq.nextval, :category_id, 'survsimp_surveys', + :survey_id, sysdate, :one_line_item_desc) + + + + + Index: openacs-4/packages/simple-survey/www/admin/survey-category-add-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/survey-category-add-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/survey-category-add-postgresql.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,25 @@ + + + + postgresql7.1 + + + + select + category_id_sequence.nextval + + + + + + + insert into site_wide_category_map + (map_id, category_id, + on_which_table, on_what_id, mapping_date, one_line_item_desc) + values (site_wide_cat_map_id_seq.nextval, :category_id, 'survsimp_surveys', + :survey_id, current_timestamp, :one_line_item_desc) + + + + + Index: openacs-4/packages/simple-survey/www/admin/survey-category-add.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/survey-category-add.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/survey-category-add.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,21 @@ + + + + + + insert into categories + (category_id, category,category_type) + values (:category_id, :category, 'survsimp') + + + + + + + + select name from survsimp_surveys where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/survey-create-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/survey-create-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/survey-create-2-oracle.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,50 @@ + + + + oracle8.1.6 + + + + + begin + :1 := survsimp_survey.new ( + survey_id => :survey_id, + name => :name, + short_name => :short_name, + description => :description, + description_html_p => :description_html_p, + type => :type, + context_id => :package_id, + creation_user => :user_id + ); + end; + + + + + + + + select survsimp_variable_id_sequence.nextval from dual + + + + + + + select survsimp_logic_id_sequence.nextval from dual + + + + + + insert into survsimp_logic + (logic_id, logic) + values + (:logic_id, empty_clob()) returning logic into :1 + + + + + + Index: openacs-4/packages/simple-survey/www/admin/survey-create-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/survey-create-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/survey-create-2-postgresql.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,50 @@ + + + + postgresql7.1 + + + + + select survsimp_survey__new ( + :survey_id, + :name, + :short_name, + :description, + :description_html_p, + 'f', + 't', + 'f', + :type, + :user_id, + :package_id + ) + + + + + + + + select survsimp_variable_id_sequence.nextval + + + + + + + select survsimp_logic_id_sequence.nextval + + + + + + insert into survsimp_logic + (logic_id, logic) + values + (:logic_id, :logic) + + + + + Index: openacs-4/packages/simple-survey/www/admin/survey-create-2.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/survey-create-2.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/survey-create-2.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,44 @@ + + + + + + +select count(short_name) +from survsimp_surveys +where lower(short_name) = lower(:short_name) + + + + + + + insert into survsimp_variables + (variable_id, variable_name) + values + (:variable_id, :variable_name) + + + + + + + insert into survsimp_variables_surveys_map + (variable_id, survey_id) + values + (:variable_id, :survey_id) + + + + + + + insert into survsimp_logic_surveys_map + (logic_id, survey_id) + values + (:logic_id, :survey_id) + + + + + Index: openacs-4/packages/simple-survey/www/admin/survey-toggle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/survey-toggle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/survey-toggle.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,13 @@ + + + + + + update survsimp_surveys + set enabled_p = :enabled_p + where survey_id = :survey_id + + + + + Index: openacs-4/packages/simple-survey/www/admin/view-text-responses.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/simple-survey/www/admin/view-text-responses.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/simple-survey/www/admin/view-text-responses.xql 15 Jun 2001 01:06:40 -0000 1.1 @@ -0,0 +1,45 @@ + + + + + + + select question_text, survey_id + from survsimp_questions + where question_id = :question_id + + + + + + + select abstract_data_type +from survsimp_questions q +where question_id = :question_id + + + + + + + +select + $column_name as response, + u.user_id, + first_names || ' ' || last_name as respondent_name, + submission_date, + ip_address +from + survsimp_responses r, + survsimp_question_responses qr, + users u +where + qr.response_id = r.response_id + and u.user_id = r.user_id + and qr.question_id = :question_id +order by r.submission_date + + + + +