Index: openacs-4/contrib/packages/mail-links/mail-links.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/mail-links.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/mail-links.info 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + Mail Clickthrough + Mail Clickthrough + f + t + mail-links + + + Timo Hentschel + The Clickthrough package provides a service that allows a site to monitor how its users exit the site, by recording which email-links are followed to external sites. + The Clickthrough package provides a service that allows a site to monitor how its users exit the site, by recording which email-links are followed to external sites. + + + + + + + + + + + + Index: openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-create.sql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,40 @@ +create table mail_links ( + link_id integer constraint mail_links_pk primary key, + url varchar2(4000) constraint mail_links_url_nn not null +); + +create sequence mail_links_seq; + +create table mail_link_mail_map ( + mail_id integer + constraint mail_link_mail_map_m_id_fk + references acs_objects on delete cascade, + link_id integer + constraint mail_link_mail_map_link_id_fk + references mail_links on delete cascade, + constraint mail_link_mail_map_pk + primary key (mail_id, link_id) +); + +create table mail_link_clicks ( + mail_id integer + constraint mail_link_clicks_mail_id_fk + references acs_objects on delete cascade, + link_id integer + constraint mail_link_clicks_link_id_fk + references mail_links on delete cascade, + user_id integer + constraint mail_link_clicks_user_id_fk + references users on delete cascade, + click_time date default sysdate +); + +-- to speed up statistic queries + +create index mail_link_clicks_mail_ix on mail_link_clicks(mail_id); +create index mail_link_clicks_link_ix on mail_link_clicks(link_id); +create index mail_link_clicks_user_ix on mail_link_clicks(user_id); +create index mail_link_clicks_date_ix on mail_link_clicks(click_time); + + +@@mail-links-package.sql Index: openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-drop.sql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,7 @@ +drop package mail_link; + +drop table mail_link_clicks; +drop table mail_link_mail_map; +drop table mail_links; + +drop sequence mail_links_seq; Index: openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-package.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/sql/oracle/mail-links-package.sql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,45 @@ +create or replace package mail_link +as + function new ( + mail_id in mail_link_mail_map.mail_id%TYPE, + url in mail_links.url%TYPE + ) return integer; + + procedure delete ( + link_id in mail_links.link_id%TYPE + ); +end mail_link; +/ +show errors + + +create or replace package body mail_link +as + function new ( + mail_id in mail_link_mail_map.mail_id%TYPE, + url in mail_links.url%TYPE + ) return integer + is + v_link_id mail_links.link_id%TYPE; + begin + select mail_links_seq.nextval into v_link_id + from dual; + + insert into mail_links (link_id, url) + values (v_link_id, new.url); + + insert into mail_link_mail_map (mail_id, link_id) + values (new.mail_id, v_link_id); + + return v_link_id; + end new; + + procedure delete ( + link_id in mail_links.link_id%TYPE + ) is + begin + delete from mail_links where link_id = mail_link.delete.link_id; + end delete; +end mail_link; +/ +show errors Index: openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-create.sql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,40 @@ +create table mail_links ( + link_id integer constraint mail_links_pk primary key, + url text constraint mail_links_url_nn not null +); + +create sequence mail_links_seq; + +create table mail_link_mail_map ( + mail_id integer + constraint mail_link_mail_map_m_id_fk + references acs_objects on delete cascade, + link_id integer + constraint mail_link_mail_map_link_id_fk + references mail_links on delete cascade, + constraint mail_link_mail_map_pk + primary key (mail_id, link_id) +); + +create table mail_link_clicks ( + mail_id integer + constraint mail_link_clicks_mail_id_fk + references acs_objects on delete cascade, + link_id integer + constraint mail_link_clicks_link_id_fk + references mail_links on delete cascade, + user_id integer + constraint mail_link_clicks_user_id_fk + references users on delete cascade, + click_time timestamptz default current_timestamp +); + +-- to speed up statistic queries + +create index mail_link_clicks_mail_ix on mail_link_clicks(mail_id); +create index mail_link_clicks_link_ix on mail_link_clicks(link_id); +create index mail_link_clicks_user_ix on mail_link_clicks(user_id); +create index mail_link_clicks_date_ix on mail_link_clicks(click_time); + + +\i mail-links-package.sql Index: openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-drop.sql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,7 @@ +drop table mail_link_clicks; +drop table mail_link_mail_map; +drop table mail_links; + +drop sequence mail_links_seq; + +\i mail-links-package-drop.sql \ No newline at end of file Index: openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-package-drop.sql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,2 @@ +drop function mail_link__new (integer,text); +drop function mail_link__delete (integer); Index: openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-package.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/sql/postgresql/mail-links-package.sql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,29 @@ +-- API for mail links + +create or replace function mail_link__new (integer,text) +returns integer as ' +declare + new__mail_id alias for $1; + new__url alias for $2; + v_link_id mail_links.link_id%TYPE; +begin + select into v_link_id nextval(''mail_links_seq''); + + insert into mail_links (link_id, url) + values (v_link_id, new__url); + + insert into mail_link_mail_map (mail_id, link_id) + values (new__mail_id, v_link_id); + + return v_link_id; +end;' language 'plpgsql'; + +create or replace function mail_link__delete (integer) +returns integer as ' +declare + delete__link_id alias for $1; +begin + delete from mail_links where link_id = delete__link_id; + + return 0; +end;' language 'plpgsql'; Index: openacs-4/contrib/packages/mail-links/tcl/mail-links-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/tcl/mail-links-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/tcl/mail-links-procs-oracle.xql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,19 @@ + + + + oracle8.1.6 + + + + begin + :1 := mail_link.new ( + mail_id => :mail_id, + url => :url + ); + end; + + + + + + Index: openacs-4/contrib/packages/mail-links/tcl/mail-links-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/tcl/mail-links-procs-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/tcl/mail-links-procs-postgresql.xql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,14 @@ + + + + postgresql7.3 + + + + select mail_link__new(:mail_id, :url) + + + + + + Index: openacs-4/contrib/packages/mail-links/tcl/mail-links-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/tcl/mail-links-procs.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/tcl/mail-links-procs.tcl 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,45 @@ +namespace eval mail_link { + + ad_proc -public add { + -url:required + -mail_id:required + } { + Creates a new mail-link + @option url url of the link + @option mail_id id of the mail that contains the link + @returns link_id of the created mail-link + } { + set link_id [db_exec_plsql insert_mail_link {}] + + return $link_id + } + + ad_proc -public click { + -link_id:required + -mail_id:required + {-user_id ""} + } { + Records a user-click + @option link_id id of the clicked link + @option mail_id id of the mail that contained the link + @option user_id user that clicked the link + } { + if {[empty_string_p $user_id]} { + set user_id [ad_conn user_id] + } + db_dml insert_click {} + } + + ad_proc -public get_link_url { + -link_id:required + } { + Returns the url of the link + @option link_id id of the clicked link + } { + + set url [db_string get_link_url {}] + + return $url + } + +} Index: openacs-4/contrib/packages/mail-links/tcl/mail-links-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/tcl/mail-links-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/tcl/mail-links-procs.xql 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,19 @@ + + + + + + insert into mail_link_clicks (mail_id, link_id, user_id) + values (:mail_id, :link_id, :user_id) + + + + + + select url + from mail_links + where link_id = :link_id + + + + Index: openacs-4/contrib/packages/mail-links/www/index.vuh =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/mail-links/www/index.vuh,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/mail-links/www/index.vuh 1 Oct 2003 04:58:31 -0000 1.1 @@ -0,0 +1,27 @@ +ad_page_contract { + This page will accect a link_id, mail_job_id and user_id + and will record the clickthrough before redirecting to the + designated url. +} { +} + +if ![regexp {^([0-9]+)\.([0-9]+)\.([0-9]+)$} [ad_conn path_info] match link_id mail_id user_id] { + ad_return_warning "Invalid Link" [subst { + We could not find this link. This probably means that + the link you have clicked is broken. +

We are sorry for this inconvenience. + }] + return +} + +set current_user_id [ad_conn user_id] +if {![empty_string_p $current_user_id] && ($current_user_id > 0)} { + # if user is logged in, user that user-id to record the click + set user_id $current_user_id +} + +mail_link::click -link_id $link_id -mail_id $mail_id -user_id $user_id + +set url [mail_link::get_link_url -link_id $link_id] + +ad_returnredirect $url Index: openacs-4/contrib/packages/users-selection/users-selection.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/users-selection.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/users-selection.info 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,47 @@ + + + + + Users Selection + Users Selections + f + f + + + + oracle + postgresql + + Timo Hentschel +

Create and manage selections of users that match certain criteria. + Create and manage selections of users that match certain criteria. + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + Index: openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-create.sql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,46 @@ +create table us_selections ( + selection_id integer + constraint us_selections_pk + primary key + constraint us_selections_id_fk + references acs_objects(object_id), + package_id integer + constraint us_selections_pck_id_fk + references apm_packages(package_id), + title varchar2(255), + description varchar2(4000), + full_sql clob, + bind_vars varchar2(4000) +); + +create table us_bind_vars ( + selection_id integer + constraint us_bind_vars_sel_id_fk + references us_selections on delete cascade, + name varchar2(30), + description varchar2(4000), + default_value varchar2(4000), + constraint us_bind_vars_pk + primary key (selection_id, name) +); + + + +-- Create the us_selection object type + +begin + acs_object_type.create_type ( + supertype => 'acs_object', + object_type => 'us_selection', + pretty_name => 'User Selection', + pretty_plural => 'User Selections', + table_name => 'us_selections', + id_column => 'selection_id', + name_method => 'us_selection.name' + ); +end; +/ +show errors + + +@@users-selection-package.sql Index: openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-drop.sql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,13 @@ +drop package us_selection; + +drop table us_bind_vars; + +drop table us_selections; + +delete from acs_objects where object_type = 'us_selection'; + +begin + acs_object_type.drop_type('us_selection'); +end; +/ +show errors Index: openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-package.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-package.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/sql/oracle/users-selection-package.sql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,124 @@ +create or replace package us_selection +as + function new ( + selection_id in us_selections.selection_id%TYPE default null, + package_id in us_selections.package_id%TYPE default null, + title in us_selections.title%TYPE, + description in us_selections.description%TYPE default null, + full_sql in us_selections.full_sql%TYPE default null, + bind_vars in us_selections.bind_vars%TYPE default null, + context_id in acs_objects.context_id%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null + ) return integer; + + procedure delete ( + selection_id in us_selections.selection_id%TYPE + ); + + function name ( + selection_id in us_selections.selection_id%TYPE + ) return varchar2; + + procedure edit ( + selection_id in us_selections.selection_id%TYPE, + title in us_selections.title%TYPE, + description in us_selections.description%TYPE default null, + full_sql in us_selections.full_sql%TYPE default null, + bind_vars in us_selections.bind_vars%TYPE default null, + modifying_user in acs_objects.modifying_user%TYPE default null, + modifying_ip in acs_objects.modifying_ip%TYPE default null + ); +end us_selection; +/ +show errors + + +create or replace package body us_selection +as + function new ( + selection_id in us_selections.selection_id%TYPE default null, + package_id in us_selections.package_id%TYPE default null, + title in us_selections.title%TYPE, + description in us_selections.description%TYPE default null, + full_sql in us_selections.full_sql%TYPE default null, + bind_vars in us_selections.bind_vars%TYPE default null, + context_id in acs_objects.context_id%TYPE default null, + creation_user in acs_objects.creation_user%TYPE default null, + creation_date in acs_objects.creation_date%TYPE default sysdate, + creation_ip in acs_objects.creation_ip%TYPE default null + ) return integer + is + v_selection_id us_selections.selection_id%TYPE; + begin + v_selection_id := acs_object.new ( + object_id => new.selection_id, + object_type => 'us_selection', + creation_date => new.creation_date, + creation_user => new.creation_user, + creation_ip => new.creation_ip, + context_id => new.context_id + ); + + insert into acs_named_objects (object_id, object_name, package_id) + values (v_selection_id, title, package_id); + + insert into us_selections + (selection_id, package_id, title, description, full_sql, bind_vars) + values + (v_selection_id, package_id, title, description, full_sql, bind_vars); + return v_selection_id; + end new; + + procedure delete ( + selection_id in us_selections.selection_id%TYPE + ) is + begin + delete from acs_objects where context_id = us_selection.delete.selection_id; + delete from us_selections where selection_id = us_selection.delete.selection_id; + acs_object.delete(selection_id); + end delete; + + function name ( + selection_id in us_selections.selection_id%TYPE + ) return varchar2 + is + v_title us_selections.title%TYPE; + begin + select title into v_title + from us_selections + where selection_id = name.selection_id; + + return v_title; + end name; + + procedure edit ( + selection_id in us_selections.selection_id%TYPE, + title in us_selections.title%TYPE, + description in us_selections.description%TYPE default null, + full_sql in us_selections.full_sql%TYPE default null, + bind_vars in us_selections.bind_vars%TYPE default null, + modifying_user in acs_objects.modifying_user%TYPE default null, + modifying_ip in acs_objects.modifying_ip%TYPE default null + ) is + begin + update us_selections + set title = edit.title, + description = edit.description, + full_sql = edit.full_sql, + bind_vars = edit.bind_vars + where selection_id = edit.selection_id; + + update acs_named_objects + set object_name = edit.title + where object_id = edit.selection_id; + + update acs_objects + set modifying_user = edit.modifying_user, + modifying_ip = edit.modifying_ip + where object_id = edit.selection_id; + end edit; +end us_selection; +/ +show errors Index: openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-create.sql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,42 @@ +create table us_selections ( + selection_id integer + constraint us_selections_pk + primary key + constraint us_selections_id_fk + references acs_objects(object_id), + package_id integer + constraint us_selections_pck_id_fk + references apm_packages(package_id), + title varchar(255), + description text, + full_sql text, + bind_vars text +); + +create table us_bind_vars ( + selection_id integer + constraint us_bind_vars_sel_id_fk + references us_selections on delete cascade, + name varchar(30), + description text, + default_value text, + constraint us_bind_vars_pk + primary key (selection_id, name) +); + +-- Create the us_selection object type + +select acs_object_type__create_type ( + 'us_selection', + 'Selection', + 'Selections', + 'acs_object', + 'us_selections', + 'selection_id', + null, + 'f', + null, + 'us_selection__name' +); + +\i users-selection-package-create.sql \ No newline at end of file Index: openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-drop.sql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,9 @@ +drop table us_bind_vars; + +drop table us_selections; + +delete from acs_objects where object_type = 'us_selection'; + +select acs_object_type__drop_type('us_selection','f'); + +\i users-selection-package-drop.sql \ No newline at end of file Index: openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-package-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-package-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-package-create.sql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,92 @@ +create or replace function us_selection__new (integer,integer,varchar,text,text,text,integer,integer,timestamptz,varchar) +returns integer as ' +declare + new__selection_id alias for $1; -- default null + new__package_id alias for $2; -- default null + new__title alias for $3; + new__description alias for $4; -- default null + new__full_sql alias for $5; -- default null + new__bind_vars alias for $6; -- default null + new__context_id alias for $7; -- default null + new__creation_user alias for $8; -- default null + new__creation_date alias for $9; -- default current_timestamp + new__creation_ip alias for $10; -- default null + v_selection_id us_selections.selection_id%TYPE; +begin + + v_selection_id := acs_object__new ( + new__selection_id, + ''us_selection'', + new__creation_date, + new__creation_user, + new__creation_ip, + new__context_id, + ''t'' + ); + + insert into acs_named_objects (object_id, object_name, package_id) + values (v_selection_id, new__title, new__package_id); + + insert into us_selections + (selection_id, package_id, title, description, full_sql, bind_vars) + values + (v_selection_id, new__package_id, new__title, new__description, new__full_sql, new__bind_vars); + return v_selection_id; + +end;' language 'plpgsql'; + +create or replace function us_selection__delete (integer) +returns integer as ' +declare + delete__selection_id alias for $1; +begin + delete from acs_objects where context_id = delete__selection_id; + delete from us_selections where selection_id = delete__selection_id; + acs_object__delete(delete__selection_id); + + return 0; +end;' language 'plpgsql'; + +create or replace function us_selection__name (integer) +returns varchar as ' +declare + name__selection_id alias for $1; + v_title us_selections.title%TYPE; +begin + + select into v_title title + from us_selections + where selection_id = name__selection_id; + + return v_title; +end;' language 'plpgsql'; + +create or replace function us_selection__edit (integer,varchar,text,text,text,integer,varchar) +returns integer as ' +declare + edit__selection_id alias for $1; + edit__title alias for $2; + edit__description alias for $3; + edit__full_sql alias for $4; + edit__bind_vars alias for $5; + edit__modifying_user alias for $6; + edit__modifying_ip alias for $7; +begin + update us_selections + set title = edit__title, + description = edit__description, + full_sql = edit__full_sql, + bind_vars = edit__bind_vars + where selection_id = edit__selection_id; + + update acs_named_objects + set object_name = edit__title + where object_id = edit__selection_id; + + update acs_objects + set modifying_user = edit__modifying_user, + modifying_ip = edit__modifying_ip + where object_id = edit__selection_id; + + return 0; +end;' language 'plpgsql'; Index: openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-package-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-package-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/sql/postgresql/users-selection-package-drop.sql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,4 @@ +drop function us_selection__new (integer,integer,varchar,text,text,text,integer,integer,timestamptz,varchar); +drop function us_selections__delete (integer); +drop function us_selections__name (integer); +drop function us_selections__edit (integer,varchar,text,text,text,integer,varchar); \ No newline at end of file Index: openacs-4/contrib/packages/users-selection/tcl/users-selection-procs-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/tcl/users-selection-procs-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/tcl/users-selection-procs-oracle.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,53 @@ + + + + oracle8.1.6 + + + + + begin + :1 := us_selection.new ( + package_id => :package_id, + title => :title, + description => :description, + bind_vars => :bind_vars, + context_id => :package_id, + creation_user => :user_id, + creation_ip => :creation_ip); + end; + + + + + + + + + begin + us_selection.edit ( + selection_id => :selection_id, + title => :title, + description => :description, + bind_vars => :bind_vars, + modifying_user => :user_id, + modifying_ip => :modifying_ip); + end; + + + + + + + + + begin + us_selection.delete ( + selection_id => :selection_id); + end; + + + + + + Index: openacs-4/contrib/packages/users-selection/tcl/users-selection-procs-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/tcl/users-selection-procs-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/tcl/users-selection-procs-postgresql.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,45 @@ + + + + postgresql7.1 + + + + select us_selection__new ( + null, + :package_id, + :title, + :description, + :full_sql, + :bind_vars, + :package_id, + :user_id, + current_timestamp, + :creation_ip); + + + + + + + + select us_selection__edit ( + :selection_id, + :title, + :description, + :full_sql, + :bind_vars, + :user_id, + :modifying_ip); + + + + + + + select us_selection__delete(:selection_id); + + + + + Index: openacs-4/contrib/packages/users-selection/tcl/users-selection-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/tcl/users-selection-procs.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/tcl/users-selection-procs.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,69 @@ +ad_library { + +} + +namespace eval user_selection { + + ad_proc -public add { + {-title:required} + {-description ""} + {-full_sql:required} + {-bind_vars ""} + } { + Creates a new user-selection + } { + set creation_ip [ad_conn peeraddr] + set user_id [ad_conn user_id] + set package_id [ad_conn package_id] + + set selection_id [db_exec_plsql insert_user_selection {}] + + db_dml set_selection_sql {} + + return $selection_id + } + + ad_proc -public edit { + {-selection_id:required} + {-title:required} + {-description ""} + {-full_sql:required} + {-bind_vars ""} + } { + Edits a user-selection + } { + set modifying_ip [ad_conn peeraddr] + set user_id [ad_conn user_id] + + db_exec_plsql update_user_selection {} + + db_dml update_selection_sql {} + } + + ad_proc -public delete { + {-selection_id:required} + } { + Deletes a user-selection + } { + db_exec_plsql delete_user_selection {} + } + + ad_proc get_field_names { + -query:required + } { + Return a list of field names that a query contains. + If the query throws an error it will be propagated, so make sure + to catch it if it is user input. + } { + set result [list] + db_with_handle db { + set selection [ns_db select $db $query] + for { set i 0 } { $i < [ns_set size $selection] } { incr i } { + lappend result [ns_set key $selection $i] + } + ns_db flush $db + } + return $result + } + +} Index: openacs-4/contrib/packages/users-selection/tcl/users-selection-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/tcl/users-selection-procs.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/tcl/users-selection-procs.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,26 @@ + + + + + + + update us_selections + set full_sql = :full_sql + where selection_id = :selection_id + + + + + + + + + update us_selections + set full_sql = :full_sql + where selection_id = :selection_id + + + + + + Index: openacs-4/contrib/packages/users-selection/www/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/index.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,29 @@ + +Users Selections +@context_bar;noquote@ + + + Administer Users Selections

+ + + + (no selections) + + + + + + + + + + + + + + + + + +
TitleCreated byDate
@selections.title@@selections.owner@@selections.creation_date@[details]
+ Index: openacs-4/contrib/packages/users-selection/www/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/index.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/index.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,26 @@ +# /packages/users-selection/www/index.tcl +ad_page_contract { + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:01:54 2002 +} { +} + +set package_id [ad_conn package_id] +set admin_p [permission::permission_p -object_id $package_id -privilege admin] + +db_multirow -extend {details_url delete_url} selections selections { + select u.selection_id, u.title, u.description, + p.first_names || ' ' || p.last_name as owner, + to_char(o.creation_date, 'YYYY-MM-DD') as creation_date + from us_selections u, acs_objects o, persons p + where u.selection_id = o.object_id + and p.person_id = o.creation_user + order by lower(u.title) +} { + set details_url "selection?[export_url_vars selection_id]" +} + +set context_bar "" + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/master.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/master.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/master.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,5 @@ + +@title;noquote@ +@context_bar;noquote@ + + Index: openacs-4/contrib/packages/users-selection/www/master.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/master.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/master.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,4 @@ +# There seems to be no way to elegantly set default values here +if { ![info exists context_bar] } { + set context_bar "" +} Index: openacs-4/contrib/packages/users-selection/www/selection-csv.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/selection-csv.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/selection-csv.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,38 @@ +# /packages/users-selection/www/selection-csv.tcl +ad_page_contract { + + download a query result as csv file. + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:07:25 2002 +} { + selection_id:integer + bind_var:array,optional +} + +db_1row get_selection {} + +if {[exists_and_not_null bind_vars]} { + set subs_list [list] + foreach var_name [array names bind_var] { + lappend subs_list $var_name $bind_var($var_name) + } + set query_string [db_bind_var_substitution $full_sql $subs_list] +} else { + set query_string $full_sql +} + +# start with the field names +set csv [join [user_selection::get_field_names -query $query_string] ","] +append csv "\n" + +foreach row [db_list_of_lists rows $query_string] { + set row_subs [list] + foreach value $row { + lappend row_subs "\"[util_escape_quotes_for_csv $value]\"" + } + append csv "[join $row_subs ","]\n" +} + + +doc_return 200 text/csv $csv Index: openacs-4/contrib/packages/users-selection/www/selection-csv.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/selection-csv.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/selection-csv.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select title, description, full_sql, bind_vars + from us_selections + where selection_id = :selection_id + + + + + + Index: openacs-4/contrib/packages/users-selection/www/selection-execute.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/selection-execute.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/selection-execute.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,24 @@ + +Execute Users Selection +@context_bar;noquote@ + +

@title@

+ +@description@ + +

+ + + + + + @query_error@ + + + + Count: @count@ +

+ Fields: @fields@ +

+ results as csv file + Index: openacs-4/contrib/packages/users-selection/www/selection-execute.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/selection-execute.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/selection-execute.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,77 @@ +# /packages/users-selection/www/selection-execute.tcl +ad_page_contract { + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:07:25 2002 +} { + selection_id:integer + bind_var:array,optional +} + + +db_1row get_selection {} + +set bind_vars_values [db_list_of_lists get_bind_vars {}] + +set context_bar [list [list "selection?[export_vars selection_id]" "Detail"] "Execute"] + +if {[empty_string_p $bind_vars]} { + set show_form_p 0 +} else { + set show_form_p 1 + + set ad_form_code "-form \{\n" + foreach bind_value $bind_vars_values { + util_unlist $bind_value var_name var_description var_value + append ad_form_code "\{bind_var.$var_name:text \{label \"$var_description\"\} \{value \"$var_value\"\}\}\n" + } + append ad_form_code "\}" + eval ad_form -name bind_vars_form -action selection-execute -export {selection_id} $ad_form_code + if {![info exists bind_var]} { + foreach bind_value $bind_vars_values { + util_unlist $bind_value var_name var_description var_value + set bind_var($var_name) $var_value + } + } +} + +set show_result_p 0 + + + +if {$show_form_p} { + if { [array size bind_var] != [llength $bind_vars_values] } { + ad_return_complaint 1 "The number of submitted bind vars doesn't seem right." + ad_script_abort + } + # try to execute the query with the user submitted values for + # the bind vars + set subs_list [list] + foreach var_name [array names bind_var] { + lappend subs_list $var_name $bind_var($var_name) + } + + set query_string [db_bind_var_substitution $full_sql $subs_list] +} else { + set query_string $full_sql +} + +ns_log notice "!> q: $query_string" + +if { [catch { + # get the count + set count [db_string count "select count(*) from ($query_string) q"] + set fields [join [user_selection::get_field_names -query $query_string] ", "] + set show_result_p 1 + + #set resultlist [db_list_of_lists get_resultlist $query_string] + #if { [llength $resultlist] == 0 } { + # set resultlist "(no rows returned)" + #} +} err] } { + set query_error "The query returned an error: $err" +} + +set csv_url "selection-csv?[export_vars [list selection_id bind_var:array]]" + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/selection-execute.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/selection-execute.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/selection-execute.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,34 @@ + + + + + + + select title, description, full_sql, bind_vars + from us_selections + where selection_id = :selection_id + + + + + + + + + select name, description, default_value + from us_bind_vars + where selection_id = :selection_id + order by name + + + + + + + + select count(*) from ($query_string) q + + + + + Index: openacs-4/contrib/packages/users-selection/www/selection.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/selection.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/selection.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,26 @@ + +Users Selection Detail +@context_bar;noquote@ + +

@title@

+ +@description@ + +

+[execute] +

+Current count of matching users: @count@ +

+ + Bind Variables: +

    + +
  • :@vars.name@ @vars.descr@ (default: "@vars.default_value@")
    • +     +
+ + +
 +@comments;noquote@ +@comment_link;noquote@ +

Index: openacs-4/contrib/packages/users-selection/www/selection.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/selection.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/selection.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,39 @@ +# /packages/users-selection/www/selection.tcl +ad_page_contract { + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:07:25 2002 +} { + selection_id:integer +} + +db_1row get_selection {} + +set subs_list [list] +db_multirow vars get_bind_vars {} { + lappend subs_list $name $default_value +} + +set execute_url "selection-execute?[export_vars selection_id]" +set context_bar [list "Detail"] + +if { ![empty_string_p $bind_vars] } { + set query_string [db_bind_var_substitution $full_sql $subs_list] +} else { + # No bind vars: we can get the current count of matching + # users. Note: might fail when there's already an 'q' alias in + # $full_sql. Solution for this missing. + set query_string $full_sql +} + +if { [catch { + set count [db_string count "select count(*) from ($query_string) q"] +}] } { + set count "(error)" +} + +set return_url [ad_return_url] +set comments [general_comments_get_comments -print_content_p 1 $selection_id $return_url] +set comment_link [general_comments_create_link -object_name "users selection \"$title\"" $selection_id $return_url] + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/selection.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/selection.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/selection.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,34 @@ + + + + + + + select title, description, full_sql, bind_vars + from us_selections + where selection_id = :selection_id + + + + + + + + + select name, description as descr, default_value + from us_bind_vars + where selection_id = :selection_id + order by name + + + + + + + + select count(*) from ($query_string) q + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/index.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,28 @@ + +Users Selections +@context_bar;noquote@ + + + (no selections) + + + + + + + + + + + + + + + + + +
TitleCreated byDate
@selections.title@@selections.owner@@selections.creation_date@[details | [copy | delete]
+ + +

+[add new] Index: openacs-4/contrib/packages/users-selection/www/admin/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/index.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/index.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,27 @@ +# /packages/users-selection/www/admin/index.tcl +ad_page_contract { + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:01:54 2002 +} { +} + +db_multirow -extend {edit_url details_url copy_url delete_url} selections selections { + select u.selection_id, u.title, u.description, + p.first_names || ' ' || p.last_name as owner, + to_char(o.creation_date, 'YYYY-MM-DD') as creation_date + from us_selections u, acs_objects o, persons p + where u.selection_id = o.object_id + and p.person_id = o.creation_user + order by lower(u.title) +} { + set edit_url "selection-ae?[export_url_vars selection_id]" + set details_url "selection?[export_url_vars selection_id]" + set copy_url "selection-ae?copy_id=$selection_id" + set delete_url "selection-delete?[export_url_vars selection_id]" +} + +set new_url "selection-ae" +set context_bar "" + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/admin/master.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/master.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/master.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,5 @@ + +@title;noquote@ +@context_bar;noquote@ + + Index: openacs-4/contrib/packages/users-selection/www/admin/master.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/master.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/master.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,4 @@ +# There seems to be no way to elegantly set default values here +if { ![info exists context_bar] } { + set context_bar "" +} Index: openacs-4/contrib/packages/users-selection/www/admin/selection-ae-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-ae-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-ae-oracle.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,20 @@ + + + + oracle8.1.6 + + + + + insert into us_bind_vars (selection_id, name) + (select :selection_id as selection_id, :bind_var as name + from dual + where not exists (select 1 from us_bind_vars + where selection_id = :selection_id + and name = :bind_var)) + + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-ae-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-ae-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-ae-postgresql.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,20 @@ + + + + postgresql7.1 + + + + + insert into us_bind_vars (selection_id, name) + (select :selection_id as selection_id, :bind_var as name + + where not exists (select 1 from us_bind_vars + where selection_id = :selection_id + and name = :bind_var)) + + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-ae.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-ae.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-ae.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,7 @@ + +Add/Edit Users Selection +@context_bar;noquote@ + + +

+Please note that the sql-query must contain the columns user_id and user_email. Index: openacs-4/contrib/packages/users-selection/www/admin/selection-ae.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-ae.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-ae.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,76 @@ +# /packages/users-selection/www/admin/selection-ae.tcl +ad_page_contract { + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:30:13 2002 +} { + selection_id:integer,optional + copy_id:integer,optional +} + +set package_id [ad_conn package_id] +set user_id [ad_conn user_id] + +set context_bar [list "Add/Edit"] + +# If the user is site wide admin then he may edit the sql +# directly. We must not allow this to anyone else since it enables +# messing with everything in the system. +# permission::require_permission -object_id [acs_magic_object security_context_root] -privilege admin + +ad_form -name selection_form -action selection-ae -form { + {selection_id:key} + {title:text {label "Title"} {html {size 20}}} + {description:text(textarea) {label "Description"} {html {rows 10 cols 40 wrap soft}}} + {full_sql:text(textarea) {label "Full SQL"} {html {rows 10 cols 40 wrap soft}}} +} -new_request { + if {[exists_and_not_null copy_id]} { + db_1row get_copied_selection {} + } else { + set title "" + set description "" + set full_sql "select u.user_id, y.email as user_email, p.first_names as user_first_names, p.last_name as user_last_name +from users u, parties y, persons p +where u.user_id = y.party_id +and u.user_id = p.person_id" + set bind_vars "" + set vars_default "" + } +} -edit_request { + db_1row get_selection {} +} -validate { + {full_sql {[regexp {user_id} $full_sql] && [regexp {user_email} $full_sql] && [regexp {user_first_names} $full_sql] && [regexp {user_last_name} $full_sql]} "The SQL Query must contain the columns user_id, user_email, user_first_names and user_last_name"} +} -on_submit { + # extract bind variables + set bind_vars [list] + set s $full_sql + while { [regexp -indices {:[a-zA-Z_]+} $s pos] } { + lappend bind_vars [string range $s [expr [lindex $pos 0]+1] [lindex $pos 1]] + set s [string range $s [expr [lindex $pos 1] + 1] end] + } + set bind_vars [string tolower $bind_vars] +} -new_data { + set selection_id [user_selection::add -title $title -description $description \ + -full_sql $full_sql -bind_vars $bind_vars] +} -edit_data { + user_selection::edit -selection_id $selection_id -title $title \ + -description $description -full_sql $full_sql -bind_vars $bind_vars +} -after_submit { + if {[empty_string_p $bind_vars]} { + set target_url "selection?[export_vars selection_id]" + } else { + db_transaction { + foreach bind_var $bind_vars { + db_dml insert_new_bind_vars {} + } + + set bind_var_sql_list "'[join $bind_vars "' ,'"]'" + db_dml delete_unused_bind_vars {} + } + set target_url "selection-vars?[export_vars selection_id]" + } + ad_returnredirect $target_url + ad_script_abort +} + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/admin/selection-ae.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-ae.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-ae.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,37 @@ + + + + + + + select title, description, full_sql, bind_vars + from us_selections + where selection_id = :copy_id + + + + + + + + + select title, description, full_sql, bind_vars + from us_selections + where selection_id = :selection_id + + + + + + + + + delete from us_bind_vars + where selection_id = :selection_id + and name not in ($bind_var_sql_list) + + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-csv.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-csv.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-csv.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,38 @@ +# /packages/users-selection/www/admin/selection-csv.tcl +ad_page_contract { + + download a query result as csv file. + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:07:25 2002 +} { + selection_id:integer + bind_var:array,optional +} + +db_1row get_selection {} + +if {[exists_and_not_null bind_vars]} { + set subs_list [list] + foreach var_name [array names bind_var] { + lappend subs_list $var_name $bind_var($var_name) + } + set query_string [db_bind_var_substitution $full_sql $subs_list] +} else { + set query_string $full_sql +} + +# start with the field names +set csv [join [user_selection::get_field_names -query $query_string] ","] +append csv "\n" + +foreach row [db_list_of_lists rows $query_string] { + set row_subs [list] + foreach value $row { + lappend row_subs "\"[util_escape_quotes_for_csv $value]\"" + } + append csv "[join $row_subs ","]\n" +} + + +doc_return 200 text/csv $csv Index: openacs-4/contrib/packages/users-selection/www/admin/selection-csv.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-csv.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-csv.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,15 @@ + + + + + + + select title, description, full_sql, bind_vars + from us_selections + where selection_id = :selection_id + + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2-oracle.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,17 @@ + + + + oracle8.1.6 + + + + + begin + us_selection.delete(:selection_id); + end; + + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2-postgresql.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,13 @@ + + + + postgresql7.1 + + + + select us_selection__delete(:selection_id); + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-delete-2.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,13 @@ +ad_page_contract { +} { + selection_id:integer,notnull +} + +set package_id [ad_conn package_id] +permission::require_permission -object_id $package_id -privilege admin + +db_transaction { + db_exec_plsql delete_users_selection {} +} + +ad_returnredirect "." Index: openacs-4/contrib/packages/users-selection/www/admin/selection-delete.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-delete.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-delete.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,15 @@ + +Confirm deletion of users selection +@context_bar;noquote@ + +

Confirm deletion of the users selection "@title@":

+

+

+
+ @export_vars;noquote@ + +
+
+ +
+
Index: openacs-4/contrib/packages/users-selection/www/admin/selection-delete.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-delete.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-delete.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,14 @@ +ad_page_contract { +} { + selection_id:integer,notnull +} + +set package_id [ad_conn package_id] +permission::require_permission -object_id $package_id -privilege admin + +db_1row get_users_selection_info {} + +set context_bar [list "Delete Users Selection"] +set export_vars [export_form_vars selection_id] + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/admin/selection-delete.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-delete.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-delete.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,16 @@ + + + + + + + select title + from us_selections + where selection_id = :selection_id + and package_id = :package_id + + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-execute.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-execute.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-execute.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,27 @@ + +Execute Users Selection +@context_bar;noquote@ + +

@title@

+ +@description@ + +
+@full_sql@
+
+ + + + + + + @query_error@ + + + + Count: @count@ +

+ Fields: @fields@ +

+ results as csv file + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-execute.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-execute.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-execute.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,77 @@ +# /packages/users-selection/www/admin/selection-execute.tcl +ad_page_contract { + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:07:25 2002 +} { + selection_id:integer + bind_var:array,optional +} + + +db_1row get_selection {} + +set bind_vars_values [db_list_of_lists get_bind_vars {}] + +set context_bar [list [list "selection?[export_vars selection_id]" "Detail"] "Execute"] + +if {[empty_string_p $bind_vars]} { + set show_form_p 0 +} else { + set show_form_p 1 + + set ad_form_code "-form \{\n" + foreach bind_value $bind_vars_values { + util_unlist $bind_value var_name var_description var_value + append ad_form_code "\{bind_var.$var_name:text \{label \"$var_description\"\} \{value \"$var_value\"\}\}\n" + } + append ad_form_code "\}" + eval ad_form -name bind_vars_form -action selection-execute -export {selection_id} $ad_form_code + if {![info exists bind_var]} { + foreach bind_value $bind_vars_values { + util_unlist $bind_value var_name var_description var_value + set bind_var($var_name) $var_value + } + } +} + +set show_result_p 0 + + + +if {$show_form_p} { + if { [array size bind_var] != [llength $bind_vars_values] } { + ad_return_complaint 1 "The number of submitted bind vars doesn't seem right." + ad_script_abort + } + # try to execute the query with the user submitted values for + # the bind vars + set subs_list [list] + foreach var_name [array names bind_var] { + lappend subs_list $var_name $bind_var($var_name) + } + + set query_string [db_bind_var_substitution $full_sql $subs_list] +} else { + set query_string $full_sql +} + +ns_log notice "!> q: $query_string" + +if { [catch { + # get the count + set count [db_string count "select count(*) from ($query_string) q"] + set fields [join [user_selection::get_field_names -query $query_string] ", "] + set show_result_p 1 + + #set resultlist [db_list_of_lists get_resultlist $query_string] + #if { [llength $resultlist] == 0 } { + # set resultlist "(no rows returned)" + #} +} err] } { + set query_error "The query returned an error: $err" +} + +set csv_url "selection-csv?[export_vars [list selection_id bind_var:array]]" + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/admin/selection-execute.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-execute.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-execute.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,34 @@ + + + + + + + select title, description, full_sql, bind_vars + from us_selections + where selection_id = :selection_id + + + + + + + + + select name, description, default_value + from us_bind_vars + where selection_id = :selection_id + order by name + + + + + + + + select count(*) from ($query_string) q + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-vars.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-vars.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-vars.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,5 @@ + +Edit Users Selection Bind Vars +@context_bar;noquote@ + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection-vars.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-vars.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-vars.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,50 @@ +# /packages/users-selection/www/admin/selection-vars.tcl +ad_page_contract { + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:30:13 2002 +} { + selection_id:integer + bvar_desc:array,optional + bvar_value:array,optional +} + +set package_id [ad_conn package_id] +set user_id [ad_conn user_id] + +set context_bar [list [list "selection?[export_url_vars selection_id]" "Detail"] "Edit Bind Vars"] + +# If the user is site wide admin then he may edit the sql +# directly. We must not allow this to anyone else since it enables +# messing with everything in the system. +# permission::require_permission -object_id [acs_magic_object security_context_root] -privilege admin + +db_1row get_selection {} + +set bind_vars_values [db_list_of_lists get_bind_vars {}] + +set ad_form_code "-form {\n" +foreach bind_value $bind_vars_values { + util_unlist $bind_value var_name var_description var_value + append ad_form_code "{bvar_desc.$var_name:text {label \":$var_name Description\"} {html {size 100}} {value \"$var_description\"}} +{bvar_value.$var_name:text {label \":$var_name Default Value\"} {html {size 60}} {value \"$var_value\"}}\n" +} + +append ad_form_code "}" +append ad_form_code { -on_submit { + db_transaction { + foreach var_name $bind_vars { + set description $bvar_desc($var_name) + set default_value $bvar_value($var_name) + + db_dml update_bind_var {} + } + } +} -after_submit { + ad_returnredirect "selection?[export_vars selection_id]" + ad_script_abort +}} + +eval ad_form -name bind_vars_form -action selection-vars -export {selection_id} $ad_form_code + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/admin/selection-vars.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection-vars.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection-vars.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,39 @@ + + + + + + + select title, description, bind_vars + from us_selections + where selection_id = :selection_id + + + + + + + + + select name, description, default_value + from us_bind_vars + where selection_id = :selection_id + + + + + + + + + update us_bind_vars + set description = :description, + default_value = :default_value + where name = :var_name + and selection_id = :selection_id + + + + + + Index: openacs-4/contrib/packages/users-selection/www/admin/selection.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection.adp 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,35 @@ + +Users Selection Detail +@context_bar;noquote@ + +

@title@

+ +@description@ + +
+@full_sql@
+
+ +[edit] + + [edit bind vars] + + +

+[execute] +

+Current count of matching users: @count@ +

+ + Bind Variables: +

    + +
  • :@vars.name@ @vars.descr@ (default: "@vars.default_value@")
    • +     +
+ + +
 +@comments;noquote@ +@comment_link;noquote@ +

Index: openacs-4/contrib/packages/users-selection/www/admin/selection.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection.tcl 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,42 @@ +# /packages/users-selection/www/admin/selection.tcl +ad_page_contract { + + @author Tilmann Singer (tils@tils.net) + @creation-date Fri Dec 20 21:07:25 2002 +} { + selection_id:integer +} + +db_1row get_selection {} + +set subs_list [list] +db_multirow vars get_bind_vars {} { + lappend subs_list $name $default_value +} + +set edit_url "selection-ae?[export_vars selection_id]" +set edit_vars_url "selection-vars?[export_vars selection_id]" + +set execute_url "selection-execute?[export_vars selection_id]" +set context_bar [list "Detail"] + +if { ![empty_string_p $bind_vars] } { + set query_string [db_bind_var_substitution $full_sql $subs_list] +} else { + # No bind vars: we can get the current count of matching + # users. Note: might fail when there's already an 'q' alias in + # $full_sql. Solution for this missing. + set query_string $full_sql +} + +if { [catch { + set count [db_string count "select count(*) from ($query_string) q"] +}] } { + set count "(error)" +} + +set return_url [ad_return_url] +set comments [general_comments_get_comments -print_content_p 1 $selection_id $return_url] +set comment_link [general_comments_create_link -object_name "users selection \"$title\"" $selection_id $return_url] + +ad_return_template Index: openacs-4/contrib/packages/users-selection/www/admin/selection.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/contrib/packages/users-selection/www/admin/selection.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/contrib/packages/users-selection/www/admin/selection.xql 1 Oct 2003 04:48:55 -0000 1.1 @@ -0,0 +1,34 @@ + + + + + + + select title, description, full_sql, bind_vars + from us_selections + where selection_id = :selection_id + + + + + + + + + select name, description as descr, default_value + from us_bind_vars + where selection_id = :selection_id + order by name + + + + + + + + select count(*) from ($query_string) q + + + + +