Index: openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-create.sql 13 Sep 2002 02:11:10 -0000 1.1 @@ -0,0 +1,47 @@ +create table vbs_service_levels ( + service_level_id number not null primary key, + service_level_code varchar2(20), + service_level_description + varchar2(80) +); + +create sequence vbs_service_level_id_seq increment by 1 start with 1; +create function vbs_sequence return number is +ret number; +begin + select vbs_service_level_id_seq.nextval into ret from dual; + return ret; + end; +/ +show errors; +create view vbs_service_level_id_sequence as select vbs_sequence from dual; + +alter table vbs_service_levels add(constraint vbs_service_levels_uk unique(service_level_code)); + +create table vbs_rates( + shipping_rate_id number not null, + service_level_id number not null, + country_iso char(2), + from_value number not null, + to_value number, + from_zip_code varchar2(10), + to_zip_code varchar2(10), + shipping_rate number, + primary key(shipping_rate_id), + foreign key(service_level_id) references vbs_service_levels(service_level_id), + foreign key(country_iso) references countries(iso)); + +create sequence vbs_shipping_rate_id_seq increment by 1 start with 1; +create function vbs_ship_sequence return number is +ret number; +begin + select vbs_shipping_rate_id_seq.nextval into ret from dual; + return ret; + end; +/ +show errors; +create view vbs_shipping_rate_id_sequence as select vbs_ship_sequence from dual; + +alter table vbs_rates add(constraint vbs_rates_uk unique(service_level_id, country_iso, from_value, to_value, from_zip_code, to_zip_code)); + +@value-based-shipping-sc-create.sql Index: openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-drop.sql 13 Sep 2002 02:11:10 -0000 1.1 @@ -0,0 +1,12 @@ +drop table vbs_rates; +drop sequence vbs_shipping_rate_id_seq; +drop view vbs_shipping_rate_id_sequence; + +drop table vbs_service_levels; +drop sequence vbs_service_level_id_seq; +drop view vbs_service_level_id_sequence; + +drop function vbs_ship_sequence; +drop function vbs_sequence; + +@value-based-shipping-sc-drop.sql Index: openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-sc-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-sc-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-sc-create.sql 13 Sep 2002 02:11:10 -0000 1.1 @@ -0,0 +1,44 @@ +-- This is an implementation of the ShippingGateway service contract + +declare + foo integer; +begin + foo := acs_sc_impl.new( + 'ShippingGateway', -- impl_contract_name + 'value-based-shipping', -- impl_name + 'value-based-shipping' -- impl_owner_name + ); + + foo := acs_sc_impl.new_alias( + 'ShippingGateway', -- impl_contract_name + 'value-based-shipping', -- impl_name + 'RatesAndServicesSelection', -- impl_operation_name + 'value-based-shipping.rates_and_services_selection', + -- impl_alias + 'TCL' -- impl_pl + ); + + foo := acs_sc_impl.new_alias( + 'ShippingGateway', -- impl_contract_name + 'value-based-shipping', -- impl_name + 'ServiceDescription', -- impl_operation_name + 'value-based-shipping.service_description', + -- impl_alias + 'TCL' -- impl_pl + ); +end; +/ +show errors + +declare + foo integer; +begin + + -- Add the binding + acs_sc_binding.new ( + contract_name => 'ShippingGateway', + impl_name => 'value-based-shipping' + ); +end; +/ +show errors Index: openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-sc-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-sc-drop.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/value-based-shipping/sql/oracle/value-based-shipping-sc-drop.sql 13 Sep 2002 02:11:10 -0000 1.1 @@ -0,0 +1,28 @@ +declare + foo integer; +begin + foo := acs_sc_impl.delete_alias( + 'ShippingGateway', + 'value-based-shipping', + 'RatesAndServicesSelection' + ); + + foo := acs_sc_impl.delete_alias( + 'ShippingGateway', + 'value-based-shipping', + 'ServiceDescription' + ); + + acs_sc_binding.delete( + contract_name => 'ShippingGateway', + impl_name => 'value-based-shipping' + ); + + acs_sc_impl.delete( + 'ShippingGateway', + 'value-based-shipping' + ); +end; +/ +show errors + Index: openacs-4/packages/value-based-shipping/www/admin/rate-delete-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/value-based-shipping/www/admin/rate-delete-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/value-based-shipping/www/admin/rate-delete-oracle.xql 13 Sep 2002 02:11:09 -0000 1.1 @@ -0,0 +1,19 @@ +<?xml version="1.0"?> + +<queryset> + <rdbms> + <type>oracle</type> + <version>8.1.6</version> + </rdbms> + + <fullquery name="get_shipping_rate"> + <querytext> + select l.service_level_description, c.default_name as country, round(r.from_value, 2) as from_value, round(r.to_value, 2) as to_value, + r.from_zip_code, r.to_zip_code, round(r.shipping_rate, 2) as shipping_rate + from vbs_rates r, countries c, vbs_service_levels l + where r.country_iso = c.iso(+) and r.shipping_rate_id = :shipping_rate_id + and r.service_level_id = l.service_level_id + </querytext> + </fullquery> + +</queryset> Index: openacs-4/packages/value-based-shipping/www/admin/rate-delete-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/value-based-shipping/www/admin/rate-delete-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/value-based-shipping/www/admin/rate-delete-postgresql.xql 13 Sep 2002 02:11:09 -0000 1.1 @@ -0,0 +1,19 @@ +<?xml version="1.0"?> + +<queryset> + <rdbms> + <type>postgresql</type> + <version>7.1</version> + </rdbms> + + <fullquery name="get_shipping_rate"> + <querytext> + select l.service_level_description, c.default_name as country, round(r.from_value, 2) as from_value, round(r.to_value, 2) as to_value, + r.from_zip_code, r.to_zip_code, round(r.shipping_rate, 2) as shipping_rate + from vbs_rates r left join countries c on (r.country_iso = c.iso), vbs_service_levels l + where r.shipping_rate_id = :shipping_rate_id + and r.service_level_id = l.service_level_id + </querytext> + </fullquery> + +</queryset> Index: openacs-4/packages/value-based-shipping/www/admin/rates-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/value-based-shipping/www/admin/rates-oracle.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/value-based-shipping/www/admin/rates-oracle.xql 13 Sep 2002 02:11:09 -0000 1.1 @@ -0,0 +1,19 @@ +<?xml version="1.0"?> + +<queryset> + <rdbms> + <type>oracle</type> + <version>8.1.6</version> + </rdbms> + + <fullquery name="get_shipping_rates"> + <querytext> + select r.shipping_rate_id, l.service_level_description, c.default_name as country, round(r.from_value, 2) as from_value, round(r.to_value, 2) as to_value, + r.from_zip_code, r.to_zip_code, round(r.shipping_rate, 2) as shipping_rate + from vbs_rates r, countries c, vbs_service_levels l + where r.country_iso = c.iso(+) and r.service_level_id = l.service_level_id + order by l.service_level_description, c.default_name, r.from_value, nvl(r.to_value, 0), nvl(r.from_zip_code, '0') + </querytext> + </fullquery> + +</queryset> Index: openacs-4/packages/value-based-shipping/www/admin/rates-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/value-based-shipping/www/admin/rates-postgresql.xql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/value-based-shipping/www/admin/rates-postgresql.xql 13 Sep 2002 02:11:09 -0000 1.1 @@ -0,0 +1,19 @@ +<?xml version="1.0"?> + +<queryset> + <rdbms> + <type>postgresql</type> + <version>7.1</version> + </rdbms> + + <fullquery name="get_shipping_rates"> + <querytext> + select r.shipping_rate_id, l.service_level_description, c.default_name as country, round(r.from_value, 2) as from_value, round(r.to_value, 2) as to_value, + r.from_zip_code, r.to_zip_code, round(r.shipping_rate, 2) as shipping_rate + from vbs_rates r left join countries c on (r.country_iso = c.iso), vbs_service_levels l + where r.service_level_id = l.service_level_id + order by l.service_level_description, c.default_name, r.from_value, coalesce(r.to_value, 0), coalesce(r.from_zip_code, '0') + </querytext> + </fullquery> + +</queryset>