Index: openacs-4/packages/telecom-number/sql/postgresql/telecom-number-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/telecom-number/sql/postgresql/telecom-number-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/telecom-number/sql/postgresql/telecom-number-create.sql 14 Mar 2003 18:18:31 -0000 1.1 @@ -0,0 +1,66 @@ +-- @cvs-id: $Id: telecom-number-create.sql,v 1.1 2003/03/14 18:18:31 jong Exp $ + +create table phone_types ( + phone_type_id integer + constraint phone_types_id_pk + primary key, + description varchar (40) + constraint phone_types_desc_nn + not null +); + +-- insert some data + +-- telecom_numbers +-- This table is used to store telephone numbers +-- I have deviated from HR-XML slightly to make the +-- table map more directly to dml + +create table telecom_numbers +( + number_id integer + constraint telecom_number_id_pk + primary key + constraint telecom_number_id_fk + references acs_objects (object_id), + party_id integer + constraint telecom_party_id_fk + references parties (party_id), + itu_id integer + constraint telecom_number_itu_code_fk + references itu_codes(itu_id), + -- trunk number + national_number varchar(20), + -- area code npa + area_city_code varchar(30), + -- local number nxx + subscriber_number varchar(100) + constraint telecom_number_sub_num_nn + not null, + extension varchar(100), + sms_enabled_p boolean, + -- 24x7,weekdays + best_contact_time varchar (200), + -- home, office, etc + location varchar (200), + -- mobile, pager, fax + phone_type_id integer + constraint telecom_numbers_phontyp_id_fk + references phone_types(phone_type_id) +); + +create index telecom_numbers_itu_ix on telecom_numbers (itu_id); +create index telecom_numbers_party_ix on telecom_numbers (party_id); + +-- view +create view telecom_number_vw as + select + n.*, + area_city_code || ' ' || subscriber_number as pretty_number, + description as phone_type + from phone_types t,telecom_numbers n + where t.phone_type_id = n.phone_type_id; + + +-- plsql +\i telecom-number-plsql-create.sql Index: openacs-4/packages/telecom-number/sql/postgresql/telecom-number-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/telecom-number/sql/postgresql/telecom-number-drop.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/telecom-number/sql/postgresql/telecom-number-drop.sql 14 Mar 2003 18:18:31 -0000 1.1 @@ -0,0 +1,48 @@ +--drop functions +drop function +drop function + + +--drop permissions +delete from acs_permissions where object_id in (select _id from telecom_number); + +--drop objects +create function inline_0 () +returns integer as ' +declare + object_rec record; +begin + for object_rec in select object_id from acs_objects where object_type=''telecom_number'' + loop + perform acs_object__delete( object_rec.object_id ); + end loop; + + return 0; +end;' language 'plpgsql'; + +select inline_0(); +drop function inline_0(); + +--drop table +drop table telecom_numbers; +drop table phone_types; + +--drop attributes +select acs_attribute__drop_attribute ( + 'note', + 'TITLE' + ); + +select acs_attribute__drop_attribute ( + 'note', + 'BODY' + ); + + +--drop type +select acs_object_type__drop_type( + 'telecom_number', + 't' + ); + + Index: openacs-4/packages/telecom-number/sql/postgresql/telecom-number-plsql-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/telecom-number/sql/postgresql/telecom-number-plsql-create.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/telecom-number/sql/postgresql/telecom-number-plsql-create.sql 14 Mar 2003 18:18:31 -0000 1.1 @@ -0,0 +1,125 @@ +-- packages/telecom_number/sql/postgresql/telecom_number-plsql.sql +-- +-- @author Jon Griffin +-- @creation-date 27 February 2003 +-- @cvs-id $Id: telecom-number-plsql-create.sql,v 1.1 2003/03/14 18:18:31 jong Exp $ + +/* +create function inline_0 () +returns integer as ' +begin + PERFORM acs_object_type__create_type ( + ''telecom_number'', -- object_type + ''Telecom Number'', -- pretty_name + ''Telecom Number'', -- pretty_plural + ''acs_object'', -- supertype + ''telecom_numbers'', -- table_name + ''number_id'', -- id_column + ''telecom_number'', -- package_name + ''f'', -- abstract_p + null, -- type_extension_table + null -- name_method + ); + + return 0; +end;' language 'plpgsql'; + +select inline_0 (); + +drop function inline_0 (); + +*/ + +------ start of oacs new proc +create or replace function telecom_number__new ( varchar,varchar,varchar,integer,varchar, +varchar,integer,integer,integer,bool,varchar,integer,varchar,integer ) +returns integer as ' +declare + p_area_city_code alias for $1; -- comment + p_best_contact_time alias for $2; -- comment + p_extension alias for $3; -- comment + p_itu_id alias for $4; -- comment + p_location alias for $5; -- comment + p_national_number alias for $6; -- comment + p_number_id alias for $7; -- comment + p_party_id alias for $8; -- comment + p_phone_type_id alias for $9; -- comment + p_sms_enabled_p alias for $10; -- comment + p_subscriber_number alias for $11; -- comment + p_creation_user alias for $12; -- comment + p_creation_ip alias for $13; -- comment + p_context_id alias for $14; -- comment + + -- local vars + v_number_id telecom_numbers.number_id%TYPE; +begin + v_number_id := acs_object__new ( + null, + ''telecom_number'', + now(), + p_creation_user, + p_creation_ip, + p_context_id + ); + + + insert into telecom_numbers ( + area_city_code, + best_contact_time, + extension, + itu_id, + location, + national_number, + number_id, + party_id, + phone_type_id, + sms_enabled_p, + subscriber_number + ) + values ( + p_area_city_code, + p_best_contact_time, + p_extension, + p_itu_id, + p_location, + p_national_number, + v_number_id, + p_party_id, + p_phone_type_id, + p_sms_enabled_p, + p_subscriber_number + ); + + PERFORM acs_permission__grant_permission ( + v_number_id, + p_creation_user, + ''admin'' + ); + + raise NOTICE ''Adding telecom_number - %'',v_number_id; + return v_number_id; + +end;' language 'plpgsql'; + +------ end new proc + +create or replace function telecom_number__del (integer) +returns integer as ' +declare + p_number_id alias for $1; + v_return integer := 0; +begin + + delete from acs_permissions + where object_id = p_number_id; + + delete from telecom_numbers + where number_id = p_number_id; + + raise NOTICE ''Deleting telecom_number - %'',p_number_id; + + PERFORM acs_object__delete(p_number_id); + return v_return; + +end;' language 'plpgsql'; +