Index: openacs-4/packages/accounts-payroll/accounts-payroll.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-payroll/accounts-payroll.info,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-payroll/accounts-payroll.info 3 Jul 2006 19:22:26 -0000 1.1
@@ -0,0 +1,27 @@
+
+
+
+
+ Payroll
+ Payrolls
+ f
+ f
+
+
+ Torben Brosten
+ OpenACS community
+ Payroll package provides payroll related management and package services.
+ Dekka Corp of Oregon
+ Payroll package provides basic UI for managing payroll, and provides services to other packages, for example production workorders, labor timesheets etc.
+ 0
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/packages/accounts-payroll/sql/postgresql/accounts-payroll-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-payroll/sql/postgresql/accounts-payroll-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-payroll/sql/postgresql/accounts-payroll-create.sql 3 Jul 2006 19:22:26 -0000 1.1
@@ -0,0 +1,12 @@
+-- accounts-payroll-create.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+
+-- need to determine where rates, hours etc. are kept in sql-ledger..
+-- they might be converted from a payroll form into a vendor invoice.
+-- in which case, we may need to create our own payroll tables to track
+-- the details prior to conversion.. and allow for other data entry methods
+
Index: openacs-4/packages/accounts-payroll/www/doc/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-payroll/www/doc/index.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-payroll/www/doc/index.adp 3 Jul 2006 19:22:26 -0000 1.1
@@ -0,0 +1,4 @@
+
+
+porting from sql-ledger HR.pm and hr.pl
+
Index: openacs-4/packages/accounts-receivables/accounts-receivables.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-receivables/accounts-receivables.info,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-receivables/accounts-receivables.info 3 Jul 2006 19:25:04 -0000 1.1
@@ -0,0 +1,32 @@
+
+
+
+
+ Accounts Receivables
+ Accounts Receivables
+ f
+ f
+
+
+ Torben Brosten
+ OpenACS community
+ Accounts Receivables provides basic UI for quotes, orders, invoices and their payments, and tracks, reports related info.
+ Dekka Corp of Oregon
+ Accounts Receivables provides manages and tracks quotes, orders, invoices and their payments, and an API for related services to other packages.
+ 0
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/packages/accounts-receivables/sql/postgresql/accounts-receivables-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-receivables/sql/postgresql/accounts-receivables-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-receivables/sql/postgresql/accounts-receivables-create.sql 3 Jul 2006 19:25:05 -0000 1.1
@@ -0,0 +1,1497 @@
+-- accounts-receivables-create.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+--
+
+CREATE SEQUENCE qar_invoiceid;
+SELECT nextval ('qar_invoiceid');
+
+--
+CREATE SEQUENCE qar_orderitemsid;
+SELECT nextval ('qar_orderitemsid');
+--
+CREATE TABLE qar_invoice (
+ id int DEFAULT nextval ( 'qar_invoiceid' ),
+ trans_id int,
+ parts_id int,
+ description text,
+ qty float4,
+ allocated float4,
+ sellprice float,
+ fxsellprice float,
+ discount float4,
+ assemblyitem bool DEFAULT 'f',
+ unit varchar(5),
+ project_id int,
+ deliverydate date,
+ serialnumber text
+);
+
+CREATE TABLE qar_ar (
+ id int DEFAULT nextval ( 'qal_id' ),
+ invnumber text,
+ transdate date DEFAULT current_date,
+ customer_id int,
+ taxincluded bool,
+ amount float,
+ netamount float,
+ paid float,
+ datepaid date,
+ duedate date,
+ invoice bool DEFAULT 'f',
+ shippingpoint text,
+ terms int2 DEFAULT 0,
+ notes text,
+ curr char(3),
+ ordnumber text,
+ employee_id int,
+ till varchar(20),
+ quonumber text,
+ intnotes text,
+ department_id int default 0,
+ shipvia text,
+ language_code varchar(6),
+ ponumber text
+);
+
+
+--
+CREATE TABLE qar_oe (
+ id int default nextval('qal_id'),
+ ordnumber text,
+ transdate date default current_date,
+ vendor_id int,
+ customer_id int,
+ amount float8,
+ netamount float8,
+ reqdate date,
+ taxincluded bool,
+ shippingpoint text,
+ notes text,
+ curr char(3),
+ employee_id int,
+ closed bool default 'f',
+ quotation bool default 'f',
+ quonumber text,
+ intnotes text,
+ department_id int default 0,
+ shipvia text,
+ language_code varchar(6),
+ ponumber text,
+ terms int2 DEFAULT 0
+);
+
+CREATE TRIGGER qci_check_inventory AFTER UPDATE ON qar_oe FOR EACH ROW EXECUTE PROCEDURE qci_check_inventory();
+
+--
+CREATE TABLE qar_orderitems (
+ trans_id int,
+ parts_id int,
+ description text,
+ qty float4,
+ sellprice float8,
+ discount float4,
+ unit varchar(5),
+ project_id int,
+ reqdate date,
+ ship float4,
+ serialnumber text,
+ id int default nextval('qar_orderitemsid')
+);
+--
+--
+CREATE TABLE qar_recurring (
+ id int,
+ reference text,
+ startdate date,
+ nextdate date,
+ enddate date,
+ repeat int2,
+ unit varchar(6),
+ howmany int,
+ payment bool default 'f'
+);
+--
+CREATE TABLE qar_recurringemail (
+ id int,
+ formname text,
+ format text,
+ message text
+);
+--
+CREATE TABLE qar_recurringprint (
+ id int,
+ formname text,
+ format text,
+ printer text
+);
+
+
+--
+create index qar_ar_id_key on qar_ar (id);
+create index qar_ar_transdate_key on qar_ar (transdate);
+create index qar_ar_invnumber_key on qar_ar (invnumber);
+create index qar_ar_ordnumber_key on qar_ar (ordnumber);
+create index qar_ar_customer_id_key on qar_ar (customer_id);
+create index qar_ar_employee_id_key on qar_ar (employee_id);
+create index qar_ar_quonumber_key on qar_ar (quonumber);
+
+--
+create index qar_invoice_id_key on qar_invoice (id);
+create index qar_invoice_trans_id_key on qar_invoice (trans_id);
+--
+create index qar_oe_id_key on qar_oe (id);
+create index qar_oe_transdate_key on qar_oe (transdate);
+create index qar_oe_ordnumber_key on qar_oe (ordnumber);
+create index qar_oe_employee_id_key on qar_oe (employee_id);
+create index qar_orderitems_trans_id_key on qar_orderitems (trans_id);
+create index qar_orderitems_id_key on qar_orderitems (id);
+--
+CREATE FUNCTION qar_del_recurring() returns opaque as '
+BEGIN
+ DELETE FROM qar_recurring WHERE id = old.id;
+ DELETE FROM qar_recurringemail WHERE id = old.id;
+ DELETE FROM qar_recurringprint WHERE id = old.id;
+ RETURN NULL;
+END;
+' language 'plpgsql';
+--end function
+CREATE TRIGGER qar_del_recurring AFTER DELETE ON qar_ar FOR EACH ROW EXECUTE PROCEDURE qar_del_recurring();
+-- end trigger
+
+CREATE TRIGGER qar_del_recurring AFTER DELETE ON qal_gl FOR EACH ROW EXECUTE PROCEDURE qar_del_recurring();
+-- end trigger
+
+-- accounts-ledger maintenance
+
+CREATE TRIGGER qar_del_department AFTER DELETE ON qar_ar FOR EACH ROW EXECUTE PROCEDURE qal_del_department();
+-- end trigger
+CREATE TRIGGER qar_del_department AFTER DELETE ON qar_oe FOR EACH ROW EXECUTE PROCEDURE qal_del_department();
+-- end trigger
+
+
+CREATE TRIGGER qar_del_exchangerate BEFORE DELETE ON qar_ar FOR EACH ROW EXECUTE PROCEDURE qal_del_exchangerate();
+-- end trigger
+--
+--
+CREATE TRIGGER qar_del_exchangerate BEFORE DELETE ON qar_oe FOR EACH ROW EXECUTE PROCEDURE qal_del_exchangerate();
+-- end trigger
+
+CREATE TRIGGER qar_check_department AFTER INSERT OR UPDATE ON qar_ar FOR EACH ROW EXECUTE PROCEDURE qal_check_department();
+-- end trigger
+CREATE TRIGGER qar_check_department AFTER INSERT OR UPDATE ON qar_oe FOR EACH ROW EXECUTE PROCEDURE qal_check_department();
+-- end trigger
+
+
+-- following from ecommerce package
+
+ create table qar_ec_user_session_offer_codes (
+ user_session_id integer not null references qal_ec_user_sessions,
+ product_id integer not null references qci_ec_products,
+ offer_code varchar(20) not null,
+ primary key (user_session_id, product_id)
+ );
+
+ -- create some indices
+ create index qar_ec_u_s_offer_codes_by_u_s_id on qar_ec_user_session_offer_codes(user_session_id);
+ create index qar_ec_u_s_offer_codes_by_p_id on qar_ec_user_session_offer_codes(product_id);
+
+ create sequence qar_ec_order_id_seq start 3000000;
+ create view qar_ec_order_id_sequence as select nextval('qar_ec_order_id_seq') as nextval;
+
+
+
+ create sequence qar_ec_creditcard_id_seq start 1;
+ create view qar_ec_creditcard_id_sequence as select nextval('qar_ec_creditcard_id_seq') as nextval;
+
+ create table qar_ec_creditcards (
+ creditcard_id integer not null primary key,
+ user_id integer not null references users,
+ -- Some credit card gateways do not ask for this but we'll store it anyway
+ creditcard_type char(1),
+ -- no spaces; always 16 digits (oops; except for AMEX, which is 15)
+ -- depending on admin settings, after we get success from the credit card gateway,
+ -- we may bash this to NULL
+ creditcard_number varchar(16),
+ -- just the last four digits for subsequent UI
+ creditcard_last_four char(4),
+ -- ##/##
+ creditcard_expire char(5),
+
+-- this used to reference ec_addresses, but now needs to reference contacts..
+-- billing_address integer references qal_ec_addresses(address_id),
+ billing_address integer,
+
+ -- if it ever failed (conclusively), set this to 't' so we
+ -- won't give them the option of using it again
+ failed_p boolean default 'f'
+ );
+
+ create index qar_ec_creditcards_by_user_idx on qar_ec_creditcards (user_id);
+
+
+
+
+ -- Gift certificate stuff ----
+ ------------------------------
+
+ create sequence qar_ec_gift_cert_id_seq start 1000000;
+ create view qar_ec_gift_cert_id_sequence as select nextval('qar_ec_gift_cert_id_seq') as nextval;
+
+ create table qar_ec_gift_certificates (
+ gift_certificate_id integer primary key,
+ gift_certificate_state varchar(50) not null,
+ amount numeric not null,
+ -- a trigger will update this to f if the
+ -- entire amount is used up (to speed up
+ -- queries)
+ amount_remaining_p boolean default 't',
+ issue_date timestamptz,
+ authorized_date timestamptz,
+ claimed_date timestamptz,
+ -- customer service rep who issued it
+ issued_by integer references users,
+ -- customer who purchased it
+ purchased_by integer references users,
+ expires timestamptz,
+ user_id integer references users,
+ -- if it's unclaimed, claim_check will be filled in,
+ -- and user_id won't be filled in
+ -- claim check should be unique (one way to do this
+ -- is to always begin it with "$gift_certificate_id-")
+ claim_check varchar(50),
+ certificate_message varchar(200),
+ certificate_to varchar(100),
+ certificate_from varchar(100),
+ recipient_email varchar(100),
+ voided_date timestamptz,
+ voided_by integer references users,
+ reason_for_void varchar(4000),
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null,
+ check (user_id is not null or claim_check is not null)
+ );
+
+ create index qar_ec_gc_by_state on qar_ec_gift_certificates(gift_certificate_state);
+ create index qar_ec_gc_by_amount_remaining on qar_ec_gift_certificates(amount_remaining_p);
+ create index qar_ec_gc_by_user on qar_ec_gift_certificates(user_id);
+ create index qar_ec_gc_by_claim_check on qar_ec_gift_certificates(claim_check);
+
+ -- note: there's a trigger in ecommerce-plsql.sql which updates amount_remaining_p
+ -- when a gift certificate is used
+
+ -- note2: there's a 1-1 correspondence between user-purchased gift certificates
+ -- and financial transactions. qar_ec_financial_transactions stores the corresponding
+ -- gift_certificate_id.
+
+ create view qar_ec_gift_certificates_approved
+ as
+ select *
+ from qar_ec_gift_certificates
+ where gift_certificate_state in ('authorized');
+
+ create view qar_ec_gift_certificates_purchased
+ as
+ select *
+ from qar_ec_gift_certificates
+ where gift_certificate_state in ('authorized');
+
+ create view qar_ec_gift_certificates_issued
+ as
+ select *
+ from qar_ec_gift_certificates
+ where gift_certificate_state in ('authorized')
+ and issued_by is not null;
+
+
+ create table qar_ec_gift_certificates_audit (
+ gift_certificate_id integer,
+ gift_certificate_state varchar(50),
+ amount numeric,
+ issue_date timestamptz,
+ authorized_date timestamptz,
+ issued_by integer,
+ purchased_by integer,
+ expires timestamptz,
+ user_id integer,
+ claim_check varchar(50),
+ certificate_message varchar(200),
+ certificate_to varchar(100),
+ certificate_from varchar(100),
+ recipient_email varchar(100),
+ voided_date timestamptz,
+ voided_by integer,
+ reason_for_void varchar(4000),
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+ );
+
+
+ create function qar_ec_gift_certificates_audit_tr ()
+ returns opaque as '
+ begin
+ insert into qar_ec_gift_certificates_audit (
+ gift_certificate_id, amount,
+ issue_date, authorized_date, issued_by, purchased_by, expires,
+ user_id, claim_check, certificate_message,
+ certificate_to, certificate_from,
+ recipient_email, voided_date, voided_by, reason_for_void,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.gift_certificate_id, old.amount,
+ old.issue_date, old.authorized_date, old.issued_by, old.purchased_by, old.expires,
+ old.user_id, old.claim_check, old.certificate_message,
+ old.certificate_to, old.certificate_from,
+ old.recipient_email, old.voided_date, old.voided_by, old.reason_for_void,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+ end;' language 'plpgsql';
+
+ create trigger qar_ec_gift_certificates_audit_tr
+ after update or delete on qar_ec_gift_certificates
+ for each row execute procedure qar_ec_gift_certificates_audit_tr ();
+
+
+
+
+ create table qar_ec_orders (
+ order_id integer not null primary key,
+ -- can be null, until they've checked out or saved their basket
+ user_id integer references users,
+ user_session_id integer references qal_ec_user_sessions,
+ order_state varchar(50) default 'in_basket' not null,
+ tax_exempt_p boolean default 'f',
+ shipping_method varchar(20), -- express or standard or pickup or 'no shipping'
+
+-- used to reference ec_addresses, now needs to reference contacts
+-- shipping_address integer references qal_ec_addresses(address_id),
+ shipping_address integer,
+ -- store credit card info in a different table
+ creditcard_id integer references qar_ec_creditcards(creditcard_id),
+ -- information recorded upon FSM state changes
+ -- we need this to figure out if order is stale
+ -- and should be offered up for removal
+ in_basket_date timestamptz,
+ confirmed_date timestamptz,
+ authorized_date timestamptz,
+ voided_date timestamptz,
+ expired_date timestamptz,
+ -- base shipping, which is added to the amount charged for each item
+ shipping_charged numeric,
+ shipping_refunded numeric,
+ shipping_tax_charged numeric,
+ shipping_tax_refunded numeric,
+ -- entered by customer service
+ cs_comments varchar(4000),
+ reason_for_void varchar(4000),
+ voided_by integer references users,
+ -- if the user chooses to save their shopping cart
+ saved_p boolean
+ check (user_id is not null or user_session_id is not null)
+ );
+
+ create index qar_ec_orders_by_user_idx on qar_ec_orders (user_id);
+ create index qar_ec_orders_by_user_sess_idx on qar_ec_orders (user_session_id);
+ create index qar_ec_orders_by_credit_idx on qar_ec_orders (creditcard_id);
+ create index qar_ec_orders_by_addr_idx on qar_ec_orders (shipping_address);
+ create index qar_ec_orders_by_conf_idx on qar_ec_orders (confirmed_date);
+ create index qar_ec_orders_by_state_idx on qar_ec_orders (order_state);
+
+ -- note that an order could essentially become uninteresting for financial
+ -- accounting if all the items underneath it are individually voided or returned
+
+ create view qar_ec_orders_reportable
+ as
+ select *
+ from qar_ec_orders
+ where order_state <> 'in_basket'
+ and order_state <> 'void';
+
+ -- orders that have items which still need to be shipped
+ create view qar_ec_orders_shippable
+ as
+ select *
+ from qar_ec_orders
+ where order_state in ('authorized','partially_fulfilled');
+
+ create sequence refund_id_seq;
+ create view refund_id_sequence as select nextval('refund_id_seq') as nextval;
+
+ create table qar_ec_refunds (
+ refund_id integer not null primary key,
+ order_id integer not null references qar_ec_orders,
+ -- not really necessary because it's in qar_ec_financial_transactions
+ refund_amount numeric not null,
+ refund_date timestamptz not null,
+ refunded_by integer not null references users,
+ refund_reasons varchar(4000)
+ );
+
+ create index qar_ec_refunds_by_order_idx on qar_ec_refunds (order_id);
+
+
+
+ create table qar_ec_gift_certificate_usage (
+ gift_certificate_id integer not null references qar_ec_gift_certificates,
+ order_id integer references qar_ec_orders,
+ amount_used numeric,
+ used_date timestamptz,
+ amount_reinstated numeric,
+ reinstated_date timestamp
+ );
+
+ create index qar_ec_gift_cert_by_id on qar_ec_gift_certificate_usage (gift_certificate_id);
+
+
+----------- end gift certificate procedures -----------
+-------------------------------------------------------
+
+-- CREDIT CARD STUFF ------------------------
+---------------------------------------------
+
+create sequence qar_ec_transaction_id_seq start 4000000;
+create view qar_ec_transaction_id_sequence as select nextval('qar_ec_transaction_id_seq') as nextval;
+
+create table qar_ec_financial_transactions (
+ transaction_id varchar(20) not null primary key,
+ -- The charge transaction that a refund transaction refunded.
+ refunded_transaction_id varchar(20) references qar_ec_financial_transactions,
+ -- order_id or gift_certificate_id must be filled in
+ order_id integer references qar_ec_orders,
+ -- The following two rows were added 1999-08-11. They re
+ -- not actually needed by the system right now, but
+ -- they might be useful in the future (I can envision them
+ -- being useful as factory functions are automated).
+ shipment_id integer references ecst_ec_shipments,
+ refund_id integer references qar_ec_refunds,
+ -- this refers to the purchase of a gift certificate, not the use of one
+ gift_certificate_id integer references qar_ec_gift_certificates,
+ -- creditcard_id is in here even though order_id has a creditcard_id associated with
+ -- it in case a different credit card is used for a refund or a partial shipment.
+ -- a trigger fills the creditcard_id in if it s not specified
+ creditcard_id integer not null references qar_ec_creditcards,
+ transaction_amount numeric not null,
+ refunded_amount numeric,
+ -- charge doesn't imply that a charge will actually occur; it s just
+ -- an authorization to charge
+ -- in the case of a refund, theres no such thing as an authorization
+ -- to refund, so the refund really will occur
+ transaction_type varchar(6) not null check (transaction_type in ('charge','refund')),
+ -- it starts out null, becomes t when we want to capture it, or becomes
+ -- f it is known that we don't want to capture the transaction (although
+ -- the f is mainly just for reassurance; we only capture ones with t)
+ -- There's no need to set this for refunds. Refunds are always to be captured.
+ to_be_captured_p boolean,
+ inserted_date timestamptz not null,
+ authorized_date timestamptz,
+ -- set when to_be_captured_p becomes 't'; used in cron jobs
+ to_be_captured_date timestamptz,
+ marked_date timestamptz,
+ refunded_date timestamptz,
+ -- if the transaction failed, this will keep the cron jobs from continuing
+ -- to retry it
+ failed_p boolean default 'f',
+ check (order_id is not null or gift_certificate_id is not null)
+);
+
+create index qar_ec_finan_trans_by_order_idx on qar_ec_financial_transactions (order_id);
+create index qar_ec_finan_trans_by_cc_idx on qar_ec_financial_transactions (creditcard_id);
+create index qar_ec_finan_trans_by_gc_idx on qar_ec_financial_transactions (gift_certificate_id);
+
+-- reportable transactions: those which have not failed which are to
+-- be captured (note: refunds are always to be captured)
+create view qar_ec_fin_transactions_reportable
+as
+select * from qar_ec_financial_transactions
+where (transaction_type='charge' and to_be_captured_p='t' and failed_p='f')
+or (transaction_type='refund' and failed_p='f');
+
+
+-- fills creditcard_id into qar_ec_financial_transactions if it's missing
+-- (using the credit card associated with the order)
+create function fin_trans_ccard_update_tr ()
+returns opaque as '
+declare
+ v_creditcard_id qar_ec_creditcards.creditcard_id%TYPE;
+begin
+ IF new.order_id is not null THEN
+ select into v_creditcard_id creditcard_id
+ from qar_ec_orders where order_id=new.order_id;
+ IF new.creditcard_id is null THEN
+ new.creditcard_id := v_creditcard_id;
+ END IF;
+ END IF;
+ return new;
+end;' language 'plpgsql';
+
+create trigger fin_trans_ccard_update_tr
+before insert on qar_ec_financial_transactions
+for each row execute procedure fin_trans_ccard_update_tr ();
+
+-- END CREDIT CARD STUFF ----------------------------
+-----------------------------------------------------
+
+
+ -- these are the items that make up each order
+ create sequence qar_ec_item_id_seq start 1;
+ create view qar_ec_item_id_sequence as select nextval('qar_ec_item_id_seq') as nextval;
+
+ create table qar_ec_items (
+ item_id integer not null primary key,
+ order_id integer not null references qar_ec_orders,
+ product_id integer not null references qci_ec_products,
+ color_choice varchar(4000),
+ size_choice varchar(4000),
+ style_choice varchar(4000),
+
+-- this should probably be changed at some point to not require the relation
+-- or move the reference to ecst_ec_shipments, to not require shipping-tracking
+ shipment_id integer references ecst_ec_shipments,
+
+ -- this is the date that user put this item into their shopping basket
+ in_cart_date timestamptz,
+ voided_date timestamptz,
+ voided_by integer references users,
+ expired_date timestamptz,
+ item_state varchar(50) default 'in_basket',
+ -- NULL if not received back
+ received_back_date timestamptz,
+ -- columns for reporting (e.g., what was done, what was made)
+ price_charged numeric,
+ price_refunded numeric,
+ shipping_charged numeric,
+ shipping_refunded numeric,
+ price_tax_charged numeric,
+ price_tax_refunded numeric,
+ shipping_tax_charged numeric,
+ shipping_tax_refunded numeric,
+ -- like Our Price or Sale Price or Introductory Price
+ price_name varchar(30),
+ -- did we go through a merchant-initiated refund?
+ refund_id integer references qar_ec_refunds,
+ -- comments entered by customer service (CS)
+ cs_comments varchar(4000)
+ );
+
+ create index qar_ec_items_by_product on qar_ec_items(product_id);
+ create index qar_ec_items_by_order on qar_ec_items(order_id);
+ create index qar_ec_items_by_shipment on qar_ec_items(shipment_id);
+
+ create view qar_ec_items_reportable
+ as
+ select *
+ from qar_ec_items
+ where item_state in ('to_be_shipped', 'shipped', 'arrived');
+
+ create view qar_ec_items_refundable
+ as
+ select *
+ from qar_ec_items
+ where item_state in ('shipped','arrived')
+ and refund_id is null;
+
+ create view qar_ec_items_shippable
+ as
+ select *
+ from qar_ec_items
+ where item_state in ('to_be_shipped');
+
+ -- This view displays:
+ -- order_id
+ -- shipment_date
+ -- bal_price_charged sum(price_charged - price_refunded) for all items in the shipment
+ -- bal_shipping_charged
+ -- bal_tax_charged
+ -- The purpose: payment is recognized when an item ships so this sums the various
+ -- parts of payment (price, shipping, tax) for all the items in each shipment
+
+ -- gilbertw - there is a note in OpenACS 3.2.5 from DRB:
+ -- DRB: this view is never used and blows out Postgres, which thinks
+ -- it's too large even with a block size of (gulp) 16384!
+ -- gilbertw - this view is used now.
+
+-- create view ec_items_money_view
+-- as
+-- select i.shipment_id, i.order_id, s.shipment_date, coalesce(sum(i.price_charged),0) - coalesce(sum(i.price_refunded),0) as bal_price_charged,
+-- coalesce(sum(i.shipping_charged),0) - coalesce(sum(i.shipping_refunded),0) as bal_shipping_charged,
+-- coalesce(sum(i.price_tax_charged),0) - coalesce(sum(i.price_tax_refunded),0) + coalesce(sum(i.shipping_tax_charged),0)
+-- - coalesce(sum(i.shipping_tax_refunded),0) as bal_tax_charged
+-- from ec_items i, ec_shipments s
+-- where i.shipment_id=s.shipment_id
+-- and i.item_state <> 'void'
+-- group by i.order_id, i.shipment_id, s.shipment_date;
+
+ -- a set of triggers to update order_state based on what happens
+ -- to the items in the order
+ -- partially_fulfilled: some but not all non-void items have shipped
+ -- fulfilled: all non-void items have shipped
+ -- returned: all non-void items received_back
+ -- void: all items void
+ -- We're not interested in partial returns.
+
+ -- this is hellish because you can't select a count of the items
+ -- in a given item_state from ec_items when you're updating ec_items,
+ -- so we have to do a horrid "trio" (temporary table, row level trigger,
+ -- system level trigger) as discussed in
+ -- http://photo.net/doc/site-wide-search.html (we use a temporary
+ -- table instead of a package because they're better)
+
+ -- I. temporary table to hold the order_ids that have to have their
+ -- state updated as a result of the item_state changes
+
+ -- gilbertw - this table is not needed in PostgreSQL
+ --create global temporary table ec_state_change_order_ids (
+ -- order_id integer
+ --);
+
+ -- gilbertw - this trigger is not needed
+ -- II. row-level trigger which updates ec_state_change_order_ids
+ -- so we know which rows to update in ec_orders
+ -- create function ec_order_state_before_tr ()
+ -- returns opaque as '
+ -- begin
+ -- insert into ec_state_change_order_ids (order_id) values (new.order_id);
+ -- return new;
+ -- end;' language 'plpgsql';
+
+ -- create trigger ec_order_state_before_tr
+ -- before update on ec_items
+ -- for each row execute procedure ec_order_state_before_tr ();
+
+ -- III. System level trigger to update all the rows that were changed
+ -- in the before trigger.
+
+ -- gilbertw - I took the trigger procedure from OpenACS 3.2.5.
+ create function qar_ec_order_state_after_tr ()
+ returns opaque as '
+ declare
+ -- v_order_id integer;
+ n_items integer;
+ n_shipped_items integer;
+ n_received_back_items integer;
+ n_void_items integer;
+ n_nonvoid_items integer;
+
+ begin
+ select count(*) into n_items from qar_ec_items where order_id=NEW.order_id;
+ select count(*) into n_shipped_items from qar_ec_items
+ where order_id=NEW.order_id
+ and item_state=''shipped'' or item_state=''arrived'';
+ select count(*) into n_received_back_items
+ from qar_ec_items where order_id=NEW.order_id
+ and item_state=''received_back'';
+ select count(*) into n_void_items from qar_ec_items
+ where order_id=NEW.order_id and item_state=''void'';
+
+ IF n_items = n_void_items THEN
+ update qar_ec_orders set order_state=''void'', voided_date=now()
+ where order_id=NEW.order_id;
+ ELSE
+ n_nonvoid_items := n_items - n_void_items;
+ IF n_nonvoid_items = n_received_back_items THEN
+ update qar_ec_orders set order_state=''returned''
+ where order_id=NEW.order_id;
+ ELSE
+ IF n_nonvoid_items = n_received_back_items + n_shipped_items THEN
+ update qar_ec_orders set order_state=''fulfilled''
+ where order_id=NEW.order_id;
+ ELSE
+ IF n_shipped_items >= 1 or n_received_back_items >=1 THEN
+ update qar_ec_orders set order_state=''partially_fulfilled''
+ where order_id=NEW.order_id;
+ END IF;
+ END IF;
+ END IF;
+ END IF;
+ return new;
+ end;' language 'plpgsql';
+
+ create trigger qar_ec_order_state_after_tr
+ after update on qar_ec_items
+ for each row execute procedure qar_ec_order_state_after_tr ();
+
+
+
+ -- If a user comes to product.tcl with an offer_code in the url,
+ -- I'm going to shove it into this table and then check this
+ -- table each time I try to determine the price for the users'
+ -- products. The alternative is to store the offer_codes in a
+ -- cookie and look at that each time I try to determine the price
+ -- for a product. But I think this will be a little faster.
+
+ create sequence qar_ec_offer_seq start 1;
+ create view qar_ec_offer_sequence as select nextval('qar_ec_offer_seq') as nextval;
+
+ create table qar_ec_offers (
+ offer_id integer not null primary key,
+ product_id integer not null references qci_ec_products,
+ retailer_location_id integer not null references qar_ec_retailer_locations,
+ store_sku integer,
+ retailer_premiums varchar(500),
+ price numeric not null,
+ shipping numeric,
+ shipping_unavailable_p boolean,
+ -- o = out of stock, q = ships quickly, m = ships
+ -- moderately quickly, s = ships slowly, i = in stock
+ -- with no message about the speed of the shipment (shipping
+ -- messages are in parameters .ini file)
+ stock_status char(1) check (stock_status in ('o','q','m','s','i')),
+ special_offer_p boolean,
+ special_offer_html varchar(500),
+ offer_begins timestamptz not null,
+ offer_ends timestamptz not null,
+ deleted_p boolean default 'f',
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+ );
+
+ create view qar_ec_offers_current
+ as
+ select * from qar_ec_offers
+ where deleted_p='f'
+ and now() >= offer_begins
+ and now() <= offer_ends;
+
+
+ create table qar_ec_offers_audit (
+ offer_id integer,
+ product_id integer,
+ retailer_location_id integer,
+ store_sku integer,
+ retailer_premiums varchar(500),
+ price numeric,
+ shipping numeric,
+ shipping_unavailable_p boolean,
+ stock_status char(1) check (stock_status in ('o','q','m','s','i')),
+ special_offer_p boolean,
+ special_offer_html varchar(500),
+ offer_begins timestamptz,
+ offer_ends timestamptz,
+ deleted_p boolean default 'f',
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ -- This differs from the deleted_p column!
+ -- deleted_p refers to the user request to stop offering
+ -- delete_p indicates the row has been deleted from the main offers table
+ delete_p boolean default 'f'
+ );
+
+
+ create function qar_ec_offers_audit_tr ()
+ returns opaque as '
+ begin
+ insert into qar_ec_offers_audit (
+ offer_id,
+ product_id, retailer_location_id,
+ store_sku, retailer_premiums,
+ price, shipping,
+ shipping_unavailable_p, stock_status,
+ special_offer_p, special_offer_html,
+ offer_begins, offer_ends,
+ deleted_p,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.offer_id,
+ old.product_id, old.retailer_location_id,
+ old.store_sku, old.retailer_premiums,
+ old.price, old.shipping,
+ old.shipping_unavailable_p, old.stock_status,
+ old.special_offer_p, old.special_offer_html,
+ old.offer_begins, old.offer_ends,
+ old.deleted_p,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+ end;' language 'plpgsql';
+
+ create trigger qar_ec_offers_audit_tr
+ after update or delete on qar_ec_offers
+ for each row execute procedure qar_ec_offers_audit_tr ();
+
+
+
+
+--------------- price calculations -------------------
+-------------------------------------------------------
+
+-- just the price of an order, not shipping, tax, or gift certificates
+-- this is actually price_charged minus price_refunded
+create function qar_ec_total_price (integer)
+returns numeric as '
+DECLARE
+ v_order_id alias for $1;
+ price numeric;
+BEGIN
+ select into price
+ coalesce(sum(price_charged),0) - coalesce(sum(price_refunded),0)
+ FROM qar_ec_items
+ WHERE order_id=v_order_id
+ and item_state <> ''void'';
+
+ return price;
+
+END;' language 'plpgsql';
+
+
+-- just the shipping of an order, not price, tax, or gift certificates
+-- this is actually total shipping minus total shipping refunded
+create function qar_ec_total_shipping (integer)
+returns numeric as '
+DECLARE
+ v_order_id alias for $1;
+ order_shipping numeric;
+ item_shipping numeric;
+BEGIN
+ select into order_shipping
+ coalesce(shipping_charged,0) - coalesce(shipping_refunded,0)
+ FROM qar_ec_orders
+ WHERE order_id=v_order_id;
+
+ select into item_shipping
+ coalesce(sum(shipping_charged),0) - coalesce(sum(shipping_refunded),0)
+ FROM qar_ec_items
+ WHERE order_id=v_order_id
+ and item_state <> ''void'';
+
+ return order_shipping + item_shipping;
+END;' language 'plpgsql';
+
+-- OK
+-- just the tax of an order, not price, shipping, or gift certificates
+-- this is tax minus tax refunded
+create function qar_ec_total_tax (integer)
+returns numeric as '
+DECLARE
+ v_order_id alias for $1;
+ order_tax numeric;
+ item_price_tax numeric;
+ item_shipping_tax numeric;
+BEGIN
+ select into order_tax
+ coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0)
+ FROM qar_ec_orders
+ WHERE order_id=v_order_id;
+
+ select into item_price_tax
+ coalesce(sum(price_tax_charged),0) - coalesce(sum(price_tax_refunded),0)
+ FROM qar_ec_items
+ WHERE order_id=v_order_id
+ and item_state <> ''void'';
+
+ select into item_shipping_tax
+ coalesce(sum(shipping_tax_charged),0) - coalesce(sum(shipping_tax_refunded),0)
+ FROM qar_ec_items
+ WHERE order_id=v_order_id;
+
+ return order_tax + item_price_tax + item_shipping_tax;
+END;' language 'plpgsql';
+
+
+-- OK
+-- just the price of a shipment, not shipping, tax, or gift certificates
+-- this is the price charged minus the price refunded of the shipment
+create function qar_ec_shipment_price (integer)
+returns numeric as '
+DECLARE
+ v_shipment_id alias for $1;
+ shipment_price numeric;
+BEGIN
+ SELECT into shipment_price coalesce(sum(price_charged),0) - coalesce(sum(price_refunded),0)
+ FROM qar_ec_items
+ WHERE shipment_id=v_shipment_id
+ and item_state <> ''void'';
+
+ RETURN shipment_price;
+END;' language 'plpgsql';
+
+-- OK
+-- just the shipping charges of a shipment, not price, tax, or gift certificates
+-- note: the base shipping charge is always applied to the first shipment in an order.
+-- this is the shipping charged minus the shipping refunded
+create function qar_ec_shipment_shipping (integer)
+returns numeric as '
+DECLARE
+ v_shipment_id alias for $1;
+ item_shipping numeric;
+ base_shipping numeric;
+ v_order_id qar_ec_orders.order_id%TYPE;
+ min_shipment_id qar_ec_shipments.shipment_id%TYPE;
+BEGIN
+ SELECT into v_order_id order_id
+ FROM qar_ec_shipments where shipment_id=v_shipment_id;
+ SELECT into min_shipment_id min(s.shipment_id)
+ from qar_ec_shipments s, qar_ec_items i, qci_ec_products p
+ where s.order_id = v_order_id
+ and s.shipment_id = i.shipment_id
+ and i.product_id = p.product_id
+ and p.no_shipping_avail_p = ''f'';
+ IF v_shipment_id=min_shipment_id THEN
+ SELECT into base_shipping
+ coalesce(shipping_charged,0) - coalesce(shipping_refunded,0)
+ FROM qar_ec_orders where order_id=v_order_id;
+ ELSE
+ base_shipping := 0;
+ END IF;
+ SELECT into item_shipping
+ coalesce(sum(shipping_charged),0) - coalesce(sum(shipping_refunded),0)
+ FROM qar_ec_items where shipment_id=v_shipment_id
+ and item_state <> ''void'';
+ RETURN item_shipping + base_shipping;
+END;' language 'plpgsql';
+
+-- OK
+-- just the tax of a shipment, not price, shipping, or gift certificates
+-- note: the base shipping tax charge is always applied to the first shipment in an order.
+-- this is the tax charged minus the tax refunded
+create function qar_ec_shipment_tax (integer)
+returns numeric as '
+DECLARE
+ v_shipment_id alias for $1;
+ item_price_tax numeric;
+ item_shipping_tax numeric;
+ base_shipping_tax numeric;
+ v_order_id qar_ec_orders.order_id%TYPE;
+ min_shipment_id qar_ec_shipments.shipment_id%TYPE;
+BEGIN
+ SELECT into v_order_id order_id
+ FROM qar_ec_shipments where shipment_id=v_shipment_id;
+ SELECT into min_shipment_id min(s.shipment_id)
+ from qar_ec_shipments s, qar_ec_items i, qar_ec_products p
+ where s.order_id = v_order_id
+ and s.shipment_id = i.shipment_id
+ and i.product_id = p.product_id
+ and p.no_shipping_avail_p = ''f'';
+ IF v_shipment_id=min_shipment_id THEN
+ SELECT into base_shipping_tax
+ coalesce(shipping_tax_charged,0) - coalesce(shipping_tax_refunded,0)
+ FROM qar_ec_orders where order_id=v_order_id;
+ ELSE
+ base_shipping_tax := 0;
+ END IF;
+ SELECT into item_price_tax
+ coalesce(sum(price_tax_charged),0) - coalesce(sum(price_tax_refunded),0)
+ FROM qar_ec_items where shipment_id=v_shipment_id and item_state <> ''void'';
+ SELECT into item_shipping_tax
+ coalesce(sum(shipping_tax_charged),0) - coalesce(sum(shipping_tax_refunded),0)
+ FROM qar_ec_items where shipment_id=v_shipment_id and item_state <> ''void'';
+ RETURN item_price_tax + item_shipping_tax + base_shipping_tax;
+END;' language 'plpgsql';
+
+
+-- OK
+-- the gift certificate amount used on one order
+create function qar_ec_order_gift_cert_amount (integer)
+returns numeric as '
+DECLARE
+ v_order_id alias for $1;
+ gift_cert_amount numeric;
+BEGIN
+ select into gift_cert_amount
+ coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0)
+ FROM qar_ec_gift_certificate_usage
+ WHERE order_id=v_order_id;
+
+ return gift_cert_amount;
+END;' language 'plpgsql';
+
+
+-- OK
+-- tells how much of the gift certificate amount used on the order is to be applied
+-- to a shipment (it's applied chronologically)
+create function qar_ec_shipment_gift_certificate (integer)
+returns numeric as '
+DECLARE
+ v_shipment_id alias for $1;
+ v_order_id qar_ec_orders.order_id%TYPE;
+ gift_cert_amount numeric;
+ past_ship_amount numeric;
+BEGIN
+ SELECT into v_order_id order_id
+ FROM qar_ec_shipments WHERE shipment_id=v_shipment_id;
+ gift_cert_amount := qar_ec_order_gift_cert_amount(v_order_id);
+ SELECT into past_ship_amount
+ coalesce(sum(qar_ec_shipment_price(shipment_id)) + sum(qar_ec_shipment_shipping(shipment_id))+sum(qar_ec_shipment_tax(shipment_id)),0)
+ FROM qar_ec_shipments WHERE order_id = v_order_id and shipment_id <> v_shipment_id;
+
+ IF past_ship_amount > gift_cert_amount THEN
+ return 0;
+ ELSE
+ return least(gift_cert_amount - past_ship_amount, coalesce(qar_ec_shipment_price(v_shipment_id) + qar_ec_shipment_shipping(v_shipment_id) + qar_ec_shipment_tax(v_shipment_id),0));
+ END IF;
+END;' language 'plpgsql';
+
+--CHECK OUTER JOIN BELOW
+
+-- OK
+-- this can be used for either an item or order
+-- given price and shipping, computes tax that needs to be charged (or refunded)
+-- order_id is an argument so that we can get the usps_abbrev (and thus the tax rate),
+create function qar_ec_tax (numeric, numeric, integer)
+returns numeric as '
+DECLARE
+ v_price alias for $1;
+ v_shipping alias for $2;
+ v_order_id alias for $3;
+ taxes qar_ec_sales_tax_by_state%ROWTYPE;
+ tax_exempt_p qar_ec_orders.tax_exempt_p%TYPE;
+BEGIN
+ SELECT into tax_exempt_p tax_exempt_p
+ FROM qar_ec_orders
+ WHERE order_id = v_order_id;
+
+ IF tax_exempt_p = ''t'' THEN
+ return 0;
+ END IF;
+
+ --SELECT t.* into taxes
+ --FROM qar_ec_orders o, qar_ec_addresses a, qar_ec_sales_tax_by_state t
+ --WHERE o.shipping_address=a.address_id
+ --AND a.usps_abbrev=t.usps_abbrev(+)
+ --AND o.order_id=v_order_id;
+
+ SELECT into taxes t.*
+ FROM qar_ec_orders o
+ JOIN
+ qar_ec_addresses a on (o.shipping_address=a.address_id)
+ LEFT JOIN
+ qar_ec_sales_tax_by_state t using (usps_abbrev)
+ WHERE o.order_id=v_order_id;
+
+
+ IF coalesce(taxes.shipping_p,''f'') = ''f'' THEN
+ return coalesce(taxes.tax_rate,0) * v_price;
+ ELSE
+ return coalesce(taxes.tax_rate,0) * (v_price + v_shipping);
+ END IF;
+END;' language 'plpgsql';
+
+-- OK
+-- total order cost (price + shipping + tax - gift certificate)
+-- this should be equal to the amount that the order was authorized for
+-- (if no refunds have been made)
+create function qar_ec_order_cost (integer)
+returns numeric as '
+DECLARE
+ v_order_id alias for $1;
+ v_price numeric;
+ v_shipping numeric;
+ v_tax numeric;
+ v_certificate numeric;
+BEGIN
+ v_price := qar_ec_total_price(v_order_id);
+ v_shipping := qar_ec_total_shipping(v_order_id);
+ v_tax := qar_ec_total_tax(v_order_id);
+ v_certificate := qar_ec_order_gift_cert_amount(v_order_id);
+
+ return v_price + v_shipping + v_tax - v_certificate;
+END;' language 'plpgsql';
+
+-- OK
+-- total shipment cost (price + shipping + tax - gift certificate)
+create function qar_ec_shipment_cost (integer)
+returns numeric as '
+DECLARE
+ v_shipment_id alias for $1;
+ v_price numeric;
+ v_shipping numeric;
+ v_certificate numeric;
+ v_tax numeric;
+BEGIN
+ v_price := qar_ec_shipment_price(v_shipment_id);
+ v_shipping := qar_ec_shipment_shipping(v_shipment_id);
+ v_tax := qar_ec_shipment_tax(v_shipment_id);
+ v_certificate := qar_ec_shipment_gift_certificate(v_shipment_id);
+
+ return v_price + v_shipping - v_certificate + v_tax;
+END;' language 'plpgsql';
+
+-- OK
+-- total amount refunded on an order so far
+create function qar_ec_total_refund (integer)
+returns numeric as '
+DECLARE
+ v_order_id alias for $1;
+ v_order_refund numeric;
+ v_items_refund numeric;
+BEGIN
+ select into v_order_refund
+ coalesce(shipping_refunded,0) + coalesce(shipping_tax_refunded,0)
+ from qar_ec_orders where order_id=v_order_id;
+ select into v_items_refund
+ sum(coalesce(price_refunded,0)) + sum(coalesce(shipping_refunded,0)) + sum(coalesce(price_tax_refunded,0)) + sum(coalesce(shipping_tax_refunded,0)) from qar_ec_items where order_id=v_order_id;
+ return v_order_refund + v_items_refund;
+END;' language 'plpgsql';
+
+-------------- end price calculations -----------------
+-------------------------------------------------------
+
+--
+-- BMA (PGsql port)
+-- Postgres is way cooler than Oracle with MVCC, which allows it
+-- to have triggers updating the same table. Thus, we get rid of this
+-- trio crap and we have a simple trigger for everything.
+
+create function trig_qar_ec_cert_amount_remains()
+returns opaque
+as '
+DECLARE
+ bal_amount_used numeric;
+ original_amount numeric;
+BEGIN
+ select amount into original_amount
+ from qar_ec_gift_certificates where gift_certificate_id= NEW.certificate_id for update;
+
+ select coalesce(sum(amount_used), 0) - coalesce(sum(amount_reinstated), 0)
+ into bal_amount_used
+ from qar_ec_gift_certificate_usage
+ where gift_certificate_id= NEW.gift_certificate_id;
+
+ UPDATE qar_ec_gift_certificates
+ SET amount_remaining_p = case when amount > bal_amount_used then ''t'' else ''f'' end
+ WHERE gift_certificate_id = gift_certificate_rec.gift_certificate_id;
+ return new;
+END;
+' language 'plpgsql';
+
+create trigger qar_ec_cert_amount_remains
+after update on qar_ec_gift_certificate_usage
+for each row
+execute procedure trig_qar_ec_cert_amount_remains();
+
+
+-- OK
+-- calculates how much a user has in their gift certificate account
+create function qar_ec_gift_certificate_balance (integer)
+returns numeric as '
+DECLARE
+ v_user_id alias for $1;
+ original_amount numeric;
+ total_amount_used numeric;
+ -- these only look at unexpired gift certificates
+ -- where amount_remaining_p is t,
+ -- hence the word subset in their names
+BEGIN
+ SELECT coalesce(sum(amount),0)
+ into original_amount
+ FROM qar_ec_gift_certificates_approved
+ WHERE user_id=v_user_id
+ AND amount_remaining_p=''t''
+ AND expires > now();
+
+ SELECT coalesce(sum(u.amount_used),0) -
+ coalesce(sum(u.amount_reinstated),0)
+ into total_amount_used
+ FROM qar_ec_gift_certificates_approved c, qar_ec_gift_certificate_usage u
+ WHERE c.gift_certificate_id=u.gift_certificate_id
+ AND c.user_id=v_user_id
+ AND c.amount_remaining_p=''t''
+ AND c.expires > now();
+
+ RETURN original_amount - total_amount_used;
+END;' language 'plpgsql';
+
+-- OK
+-- Returns price + shipping + tax - gift certificate amount applied
+-- for one order.
+-- Requirement: qar_ec_orders.shipping_charged, qar_ec_orders.shipping_tax_charged,
+-- qar_ec_items.price_charged, qar_ec_items.shipping_charged, qar_ec_items.price_tax_chaged,
+-- and qar_ec_items.shipping_tax_charged should already be filled in.
+
+create function qar_ec_order_amount_owed (integer)
+returns numeric as '
+DECLARE
+ v_order_id alias for $1;
+ pre_gc_amount_owed numeric;
+ gc_amount numeric;
+BEGIN
+ pre_gc_amount_owed := qar_ec_total_price(v_order_id) + qar_ec_total_shipping(v_order_id) + qar_ec_total_tax(v_order_id);
+ gc_amount := qar_ec_order_gift_cert_amount(v_order_id);
+
+ RETURN pre_gc_amount_owed - gc_amount;
+END;' language 'plpgsql';
+
+-- OK
+-- the amount remaining in an individual gift certificate
+create function gift_certificate_amount_left (integer)
+returns numeric as '
+DECLARE
+ v_gift_certificate_id alias for $1;
+ original_amount numeric;
+ total_amount_used numeric;
+BEGIN
+ SELECT coalesce(sum(amount_used),0) - coalesce(sum(amount_reinstated),0)
+ into total_amount_used
+ FROM qar_ec_gift_certificate_usage
+ WHERE gift_certificate_id = v_gift_certificate_id;
+
+ SELECT amount
+ into original_amount
+ FROM qar_ec_gift_certificates
+ WHERE gift_certificate_id = v_gift_certificate_id;
+
+ RETURN original_amount - total_amount_used;
+END;' language 'plpgsql';
+
+-- I DON'T USE THIS PROCEDURE ANYMORE BECAUSE THERE'S A MORE
+-- FAULT-TOLERANT TCL VERSION
+-- This applies gift certificate balance to an entire order
+-- by iteratively applying unused/unexpired gift certificates
+-- to the order until the order is completely paid for or
+-- the gift certificates run out.
+-- Requirement: qar_ec_orders.shipping_charged, qar_ec_orders.shipping_tax_charged,
+-- qar_ec_items.price_charged, qar_ec_items.shipping_charged, qar_ec_items.price_tax_charged,
+-- qar_ec_items.shipping_tax_charged should already be filled in.
+-- Call this within a transaction.
+--create or replace procedure qar_ec_apply_gift_cert_balance (v_order_id IN integer, v_user_id IN integer)
+--IS
+-- CURSOR gift_certificate_to_use_cursor IS
+-- SELECT *
+-- FROM qar_ec_gift_certificates_approved
+-- WHERE user_id = v_user_id
+-- AND (expires is null or now() < expires )
+-- AND amount_remaining_p = ''t''
+-- ORDER BY expires;
+-- amount_owed number;
+-- gift_certificate_balance number;
+-- certificate qar_ec_gift_certificates_approved%ROWTYPE;
+--BEGIN
+-- gift_certificate_balance := qar_ec_gift_certificate_balance(v_user_id);
+-- amount_owed := qar_ec_order_amount_owed(v_order_id);
+--
+-- OPEN gift_certificate_to_use_cursor;
+-- WHILE amount_owed > 0 and gift_certificate_balance > 0
+-- LOOP
+-- FETCH gift_certificate_to_use_cursor INTO certificate;
+--
+-- INSERT into qar_ec_gift_certificate_usage
+-- (gift_certificate_id, order_id, amount_used, used_date)
+-- VALUES
+-- (certificate.gift_certificate_id, v_order_id, least(gift_certificate_amount_left(certificate.gift_certificate_id), amount_owed), now());
+--
+-- gift_certificate_balance := qar_ec_gift_certificate_balance(v_user_id);
+-- amount_owed := qar_ec_order_amount_owed(v_order_id);
+-- END LOOP;
+-- CLOSE gift_certificate_to_use_cursor;
+--END qar_ec_apply_gift_cert_balance;
+--/
+--show errors
+
+-- OK
+-- reinstates all gift certificates used on an order (as opposed to
+-- individual items), e.g. if the order was voided or an auth failed
+
+create function qar_ec_reinst_gift_cert_on_order (integer)
+returns integer as '
+DECLARE
+ v_order_id alias for $1;
+BEGIN
+ insert into qar_ec_gift_certificate_usage
+ (gift_certificate_id, order_id, amount_reinstated, reinstated_date)
+ select gift_certificate_id, v_order_id, coalesce(sum(amount_used),0)-coalesce(sum(amount_reinstated),0), now()
+ from qar_ec_gift_certificate_usage
+ where order_id=v_order_id
+ group by gift_certificate_id;
+
+ return 0;
+END;' language 'plpgsql';
+
+-- Given an amount to refund to an order, this tells
+-- you how much of that is to be refunded in cash (as opposed to
+-- reinstated in gift certificates). Then you know you have to
+-- go and reinstate v_amount minus (what this function returns)
+-- in gift certificates.
+-- (when I say cash I'm really talking about credit card
+-- payment -- as opposed to gift certificates)
+
+-- Call this before inserting the amounts that are being refunded
+-- into the database.
+create function qar_ec_cash_amount_to_refund (numeric, integer)
+returns numeric as '
+DECLARE
+ v_amount alias for $1;
+ v_order_id alias for $2;
+ amount_paid numeric;
+ items_amount_paid numeric;
+ order_amount_paid numeric;
+ amount_refunded numeric;
+ curr_gc_amount numeric;
+ max_cash_refundable numeric;
+ cash_to_refund numeric;
+BEGIN
+ -- the maximum amount of cash refundable is equal to
+ -- the amount paid (in cash + certificates) for shipped items only (since
+ -- money is not paid until an item actually ships)
+ -- minus the amount refunded (in cash + certificates) (only occurs for shipped items)
+ -- minus the current gift certificate amount applied to this order
+ -- or 0 if the result is negative
+
+ select sum(coalesce(price_charged,0)) + sum(coalesce(shipping_charged,0)) + sum(coalesce(price_tax_charged,0)) + sum(coalesce(shipping_tax_charged,0)) into items_amount_paid from qar_ec_items where order_id=v_order_id and shipment_id is not null and item_state <> ''void'';
+
+ select coalesce(shipping_charged,0) + coalesce(shipping_tax_charged,0) into order_amount_paid from qar_ec_orders where order_id=v_order_id;
+
+ amount_paid := items_amount_paid + order_amount_paid;
+ amount_refunded := qar_ec_total_refund(v_order_id);
+ curr_gc_amount := qar_ec_order_gift_cert_amount(v_order_id);
+
+ max_cash_refundable := amount_paid - amount_refunded - curr_gc_amount;
+ cash_to_refund := least(max_cash_refundable, v_amount);
+
+ RETURN cash_to_refund;
+END;' language 'plpgsql';
+
+-- The amount of a given gift certificate used on a given order.
+-- This is a helper function for qar_ec_gift_cert_unshipped_amount.
+create function qar_ec_one_gift_cert_on_one_order (integer, integer)
+returns numeric as '
+DECLARE
+ v_gift_certificate_id alias for $1;
+ v_order_id alias for $2;
+ bal_amount_used numeric;
+BEGIN
+ select coalesce(sum(amount_used),0)-coalesce(sum(amount_reinstated),0) into bal_amount_used
+ from qar_ec_gift_certificate_usage
+ where order_id=v_order_id
+ and gift_certificate_id=v_gift_certificate_id;
+
+ RETURN bal_amount_used;
+
+END;' language 'plpgsql';
+
+-- The amount of all gift certificates used on a given order that
+-- expire before* a given gift certificate (*in the event that two
+-- expire at precisely the same time, the one with a higher
+-- gift_certificate_id is defined to expire last).
+-- This is a helper function for qar_ec_gift_cert_unshipped_amount.
+create function qar_ec_earlier_certs_on_one_order (integer, integer)
+returns numeric as '
+DECLARE
+ v_gift_certificate_id alias for $1;
+ v_order_id alias for $2;
+ bal_amount_used numeric;
+BEGIN
+ select coalesce(sum(u.amount_used),0)-coalesce(sum(u.amount_reinstated),0) into bal_amount_used
+ from qar_ec_gift_certificate_usage u, qar_ec_gift_certificates g, qar_ec_gift_certificates g2
+ where u.gift_certificate_id=g.gift_certificate_id
+ and g2.gift_certificate_id=v_gift_certificate_id
+ and u.order_id=v_order_id
+ and (g.expires < g2.expires or (g.expires = g2.expires and g.gift_certificate_id < g2.gift_certificate_id));
+
+ return bal_amount_used;
+END;' language 'plpgsql';
+
+-- The amount of a gift certificate that is applied to the upshipped portion of an order.
+-- This is a helper function for qar_ec_gift_cert_unshipped_amount.
+create function qar_ec_cert_unshipped_one_order (integer, integer)
+returns numeric as '
+DECLARE
+ v_gift_certificate_id alias for $1;
+ v_order_id alias for $2;
+ total_shipment_cost numeric;
+ earlier_certs numeric;
+ total_tied_amount numeric;
+BEGIN
+ select coalesce(sum(coalesce(qar_ec_shipment_price(shipment_id),0) + coalesce(qar_ec_shipment_shipping(shipment_id),0) + coalesce(qar_ec_shipment_tax(shipment_id),0)),0) into total_shipment_cost
+ from qar_ec_shipments
+ where order_id=v_order_id;
+
+ earlier_certs := qar_ec_earlier_certs_on_one_order(v_gift_certificate_id, v_order_id);
+
+ IF total_shipment_cost <= earlier_certs THEN
+ total_tied_amount := qar_ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id);
+ ELSE
+ IF total_shipment_cost > earlier_certs + qar_ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) THEN
+ total_tied_amount := 0;
+ ELSE
+ total_tied_amount := qar_ec_one_gift_cert_on_one_order(v_gift_certificate_id, v_order_id) - (total_shipment_cost - earlier_certs);
+ END IF;
+ END IF;
+
+ RETURN total_tied_amount;
+END;' language 'plpgsql';
+
+--HERE
+
+-- Returns the amount of a gift certificate that is applied to the unshipped portions of orders
+-- (this amount is still considered outstanding since revenue, and thus gift certificate usage,
+-- isnt recognized until the items ship).
+create function qar_ec_gift_cert_unshipped_amount (integer)
+returns numeric as '
+DECLARE
+ v_gift_certificate_id alias for $1;
+ tied_but_unshipped_amount numeric;
+BEGIN
+ select coalesce(sum(qar_ec_cert_unshipped_one_order(v_gift_certificate_id,order_id)),0) into tied_but_unshipped_amount
+ from qar_ec_orders
+ where order_id in (select distinct order_id from qar_ec_gift_certificate_usage where gift_certificate_id=v_gift_certificate_id);
+
+ return tied_but_unshipped_amount;
+END;' language 'plpgsql';
+
+
+
+
+---------- end gift certificate procedures ------------
+-------------------------------------------------------
+
+------------ tax related calculations --------
+-----------------------------------------
+--
+-- -- this is populated by the rules the administrator sets in packages/ecommerce/www/admin]/sales-tax.tcl
+ create table qar_ec_sales_tax_by_state (
+ -- Jerry
+ usps_abbrev char(2) not null primary key references us_states(abbrev),
+ -- this a decimal number equal to the percentage tax divided by 100
+ tax_rate numeric not null,
+ -- charge tax on shipping?
+ shipping_p boolean not null,
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+ );
+
+ create table qar_ec_sales_tax_by_state_audit (
+ usps_abbrev char(2),
+ tax_rate numeric,
+ shipping_p boolean,
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+ );
+
+
+ -- Jerry - I removed usps_abbrev and/or state here
+ create function qar_ec_sales_tax_by_state_audit_tr ()
+ returns opaque as '
+ begin
+ insert into qar_ec_sales_tax_by_state_audit (
+ usps_abbrev, tax_rate,
+ shipping_p,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.usps_abbrev, old.tax_rate,
+ old.shipping_p,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+ end;' language 'plpgsql';
+
+ create trigger qar_ec_sales_tax_by_state_audit_tr
+ after update or delete on qar_ec_sales_tax_by_state
+ for each row execute procedure qar_ec_sales_tax_by_state_audit_tr ();
+
Index: openacs-4/packages/accounts-receivables/www/doc/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/accounts-receivables/www/doc/index.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/accounts-receivables/www/doc/index.adp 3 Jul 2006 19:25:05 -0000 1.1
@@ -0,0 +1,4 @@
+
+
+porting from sql-ledger: OE.pm ar.pl cp.pl ct.pl oe.pl ps.pl
+
Index: openacs-4/packages/fabrik/fabrik.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/fabrik/fabrik.info,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/fabrik/fabrik.info 3 Jul 2006 19:27:18 -0000 1.1
@@ -0,0 +1,27 @@
+
+
+
+
+ Fabrication
+ Fabrications
+ f
+ f
+
+
+ Torben Brosten
+ OpenACS community
+ Fabrication package works with bills of materials etc from design to production specifications.
+ Dekka Corp of Oregon
+ Frabrication package is built around the common bills of materials, yet scopes to time, materials and specifications for handling of parts from design to pre-production. This package considers other practical aspects of manufacturing besides the standard bills of materials scenarios.
+ 0
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/packages/fabrik/sql/postgresql/fabrik-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/fabrik/sql/postgresql/fabrik-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/fabrik/sql/postgresql/fabrik-create.sql 3 Jul 2006 19:27:18 -0000 1.1
@@ -0,0 +1,154 @@
+-- fabrik-create.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+
+CREATE TABLE qfab_parts (
+ id int DEFAULT nextval ( 'id' ),
+ partnumber text,
+ description text,
+ unit varchar(5),
+ listprice float,
+ sellprice float,
+ lastcost float,
+ priceupdate date DEFAULT current_date,
+ weight float4,
+ onhand float4 DEFAULT 0,
+ notes text,
+ makemodel bool DEFAULT 'f',
+ assembly bool DEFAULT 'f',
+ alternate bool DEFAULT 'f',
+ rop float4,
+ inventory_accno_id int,
+ income_accno_id int,
+ expense_accno_id int,
+ bin text,
+ obsolete bool DEFAULT 'f',
+ bom bool DEFAULT 'f',
+ image text,
+ drawing text,
+ microfiche text,
+ partsgroup_id int,
+ project_id int,
+ avgcost float
+);
+
+CREATE TABLE qfab_assembly (
+ id int,
+ parts_id int,
+ qty float,
+ bom bool,
+ adj bool
+);
+
+
+CREATE TABLE qfab_partsvendor (
+ vendor_id int,
+ parts_id int,
+ partnumber text,
+ leadtime int2,
+ lastcost float,
+ curr char(3)
+);
+
+
+CREATE SEQUENCE qfab_jcitemsid;
+
+CREATE TABLE qfab_jcitems (
+ id int default nextval('jcitemsid'),
+ project_id int,
+ parts_id int,
+ description text,
+ qty float4,
+ allocated float4,
+ sellprice float8,
+ fxsellprice float8,
+ serialnumber text,
+ checkedin timestamp with time zone,
+ checkedout timestamp with time zone,
+ employee_id int,
+ notes text
+);
+
+create index qfab_assembly_id_key on qfab_assembly (id);
+
+
+
+
+create index qfab_jcitems_id_key on qfab_jcitems (id);
+
+
+-- following needs to be integrated into above
+
+-- rules
+-- provisions for requests to change bom or order through an approval workflow --before the change is accepted (ECO)
+-- bom must be designed to handle the most demanding functions: planning
+-- superbom includes labor/routing with bom, and requirements (think baking recipe etc)
+-- multiple views of bom
+-- bom references are unique
+-- bom includes quantity (manufacturing might result in creation of more than 1 created per bom, for example 1 pipe cut in half creates 2 pipes...)
+-- have provisions for handling perishible goods
+-- bom refeerences are different than drawing references
+-- bom includes all materials to be scheduled
+-- bom editing UI is designed to minimize depth of dependencies
+-- template boms (used during design process) --optionally use specification parameters (called modularizing) to minimize the number of templates. (a kind of parts number system by specs) --also a revision number
+-- orders copy template to their own spec set. (handy for lot number controls, customization, substitutions etc), can create a serial number for each part.
+-- addition of a table to handle customized fields (in the same manner as ecommerce package does)
+--
+-- When to assign part numbers: every item represented in the system has at least an internal part number. Formal numbers are optional if not specified.
+-- parts_master table (links to data in other tables)
+-- part_number
+-- other_table_name other_table_reference (make these external keys?)
+-- (maybe this is an object folder with the CR)? No (or yes, but scary because) CR uses ad_conn for some code with causes problems with scheduled procs. Scheduled procs will be heavily relied on for forecasting and planning, as well as mainenance. CR is meant for more generalized uses, this is a specific datamodel with specific requirements.
+--
+--
+-- bom table
+-- internal_notes
+-- bom_internal_key
+-- part _number
+-- model_series_number (not unique, for grouping purposes)
+-- level (where 0 is lowest assembly level (ie. indivisible/raw material/aquired parts) value is 1 + max of the components in the bom
+-- bom_sku (upc etc.)
+-- sellable_p
+-- service_item_p
+-- assembly_p
+-- stockable_p
+-- phantom_item_p (exists on paper only)
+-- manufactured_part_p (1 =requires specialty tools/labor)
+-- perishible_item_p (uses quality date)
+-- create_serial_number_p
+-- last_issued_serial_number
+-- shipping_notes (contains flags for hazardous shipping, special instructions)
+-- lots_p (track production run, see lot_number)
+-- lot_number
+-- standards_met_p (track grade, standards compliance for part)
+--
+-- make a separate table for tracking each of the _p attributes.
+--
+--
+-- substitution_map
+-- original_part_number
+-- substitute_part_number
+-- notes
+--
+--
+-- part table
+-- length
+-- width
+-- height
+-- volume
+-- weight
+-- variable-dimension(s)
+-- variable-dimension-units
+-- manufactured_date
+-- quality_date (expires, best used by, date made etc)
+-- min_run varchar(5), /* min amount of parts to run to make a profit */
+-- serial_number
+--
+-- routing table
+-- partnumber
+-- labor-ref
+-- location
+--
Index: openacs-4/packages/fabrik/sql/postgresql/fabrik-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/fabrik/sql/postgresql/fabrik-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/fabrik/sql/postgresql/fabrik-drop.sql 3 Jul 2006 19:27:18 -0000 1.1
@@ -0,0 +1,31 @@
+-- fabrik-drop.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+
+
+drop index qfab_jcitems_id_key on qfab_jcitems ();
+
+drop index qfab_partsgroup_key on qfab_partsgroup ();
+drop index qfab_partsgroup_id_key on qfab_partsgroup ();
+
+
+drop index qfab_makemodel_model_key on qfab_makemodel ();
+drop index qfab_makemodel_make_key on qfab_makemodel ();
+drop index qfab_makemodel_parts_id_key on qfab_makemodel ();
+
+drop index qfab_assembly_id_key on qfab_assembly ();
+
+DROP TABLE qfab_jcitems;
+
+DROP SEQUENCE qfab_jcitemsid;
+
+
+DROP TABLE qfab_partsvendor;
+
+
+DROP TABLE qfab_assembly;
+
+DROP TABLE qfab_parts;
Index: openacs-4/packages/fabrik/www/doc/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/fabrik/www/doc/index.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/fabrik/www/doc/index.adp 3 Jul 2006 19:27:18 -0000 1.1
@@ -0,0 +1,4 @@
+
+
+Porting from sql-ledger: JC.pm jc.pl pe.pl
+
Index: openacs-4/packages/inventory-control/inventory-control.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/inventory-control.info,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/inventory-control/inventory-control.info 3 Jul 2006 19:29:13 -0000 1.1
@@ -0,0 +1,27 @@
+
+
+
+
+ Inventory
+ Inventories
+ f
+ f
+
+
+ Torben Brosten
+ OpenACS community
+ Inventory package tracks inventory, parts locations and costs.
+ Dekka Corp of Oregon
+ Inventory package provides inventory control, includes tracking locations and costs, can report changes to general ledger etc.
+ 0
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/packages/inventory-control/sql/postgresql/catalog-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/sql/postgresql/catalog-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/inventory-control/sql/postgresql/catalog-create.sql 3 Jul 2006 19:29:13 -0000 1.1
@@ -0,0 +1,86 @@
+-- catalog-create.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+--
+--
+
+CREATE TABLE qci_parts (
+ id int DEFAULT nextval ( 'id' ),
+ partnumber text,
+ description text,
+ unit varchar(5),
+ listprice float,
+ sellprice float,
+ lastcost float,
+ priceupdate date DEFAULT current_date,
+ weight float4,
+ onhand float4 DEFAULT 0,
+ notes text,
+ makemodel bool DEFAULT 'f',
+ assembly bool DEFAULT 'f',
+ alternate bool DEFAULT 'f',
+ rop float4,
+ inventory_accno_id int,
+ income_accno_id int,
+ expense_accno_id int,
+ bin text,
+ obsolete bool DEFAULT 'f',
+ bom bool DEFAULT 'f',
+ image text,
+ drawing text,
+ microfiche text,
+ partsgroup_id int,
+ project_id int,
+ avgcost float
+);
+
+--
+CREATE TABLE qci_partsgroup (
+ id int default nextval('id'),
+ partsgroup text
+);
+
+create index qci_partsgroup_id_key on qci_partsgroup (id);
+create unique index qci_partsgroup_key on qci_partsgroup (partsgroup);
+
+
+--
+CREATE TABLE qci_pricegroup (
+ id int default nextval('id'),
+ pricegroup text
+);
+--
+CREATE TABLE qci_partscustomer (
+ parts_id int,
+ customer_id int,
+ pricegroup_id int,
+ pricebreak float4,
+ sellprice float,
+ validfrom date,
+ validto date,
+ curr char(3)
+);
+
+--
+CREATE TABLE qci_partstax (
+ parts_id int,
+ chart_id int
+);
+--
+
+create index qci_parts_id_key on qci_parts (id);
+create index qci_parts_partnumber_key on qci_parts (lower(partnumber));
+create index qci_parts_description_key on qci_parts (lower(description));
+create index qci_partstax_parts_id_key on qci_partstax (parts_id);
+
+--
+create index qci_partsvendor_vendor_id_key on qci_partsvendor (vendor_id);
+create index qci_partsvendor_parts_id_key on qci_partsvendor (parts_id);
+--
+create index qci_pricegroup_pricegroup_key on qci_pricegroup (pricegroup);
+create index qci_pricegroup_id_key on qci_pricegroup (id);
+
+
Index: openacs-4/packages/inventory-control/sql/postgresql/inventory-control-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/sql/postgresql/inventory-control-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/inventory-control/sql/postgresql/inventory-control-create.sql 3 Jul 2006 19:29:14 -0000 1.1
@@ -0,0 +1,673 @@
+-- inventory-control-create.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+--
+
+-- following from SL
+
+CREATE TABLE qci_makemodel (
+ parts_id integer,
+ make text,
+ model text
+);
+
+create index qci_makemodel_parts_id_key on qci_makemodel (parts_id);
+create index qci_makemodel_make_key on qci_makemodel (lower(make));
+create index qci_makemodel_model_key on qci_makemodel (lower(model));
+
+
+CREATE TABLE qci_warehouse (
+ id int default nextval('id'),
+ description text
+);
+
+
+CREATE TABLE qci_inventory (
+ warehouse_id int,
+ parts_id int,
+ trans_id int,
+ orderitems_id int,
+ qty float4,
+ shippingdate date,
+ employee_id int
+);
+
+
+CREATE FUNCTION qci_check_inventory() RETURNS OPAQUE AS '
+
+declare
+ itemid int;
+ row_data inventory%rowtype;
+
+begin
+
+ if not old.quotation then
+ for row_data in select * from qci_inventory where trans_id = old.id loop
+ select into itemid id from orderitems where trans_id = old.id and id = row_data.orderitems_id;
+
+ if itemid is null then
+ delete from qci_inventory where trans_id = old.id and orderitems_id = row_data.orderitems_id;
+ end if;
+ end loop;
+ end if;
+return old;
+end;
+' language 'plpgsql';
+-- end function
+
+
+
+
+
+-- following from ecommerce package
+
+-- Helper stuff (ben@adida.net)
+-- gilbertw - I pulled this from OpenACS 3.2.5
+-- there are a few calls to the Oracle least function
+create function qci_least(numeric,numeric)
+returns numeric
+as '
+DECLARE
+ first alias for $1;
+ second alias for $2;
+BEGIN
+ if first < second
+ then return first;
+ else return second;
+ end if;
+END;
+' language 'plpgsql';
+
+-- gilbertw
+-- timespan_days taken from OpenACS 3.2.5
+-- can't cast numeric to varchar/text so I made the input varchar
+create function qci_timespan_days(float) returns interval as '
+DECLARE
+ n_days alias for $1;
+BEGIN
+ return (n_days::text || '' days'')::interval;
+END;
+' language 'plpgsql';
+
+
+-- this should be replaced by the object_id sequence
+-- grep for it in files...
+-- create sequence ec_product_id_sequence start 1;
+
+-- This table contains the products and also the product series.
+-- A product series has the same fields as a product (it actually
+-- *is* a product, since it's for sale, has its own price, etc.).
+-- The only difference is that it has other products associated
+-- with it (that are part of it). So information about the
+-- whole series is kept in this table and the product_series_map
+-- table below keeps track of which products are inside each
+-- series.
+
+-- wtem@olywa.net, 2001-03-24
+-- begin
+-- acs_object_type__create_type (
+-- supertype => 'acs_object',
+-- object_type => 'ec_product',
+-- pretty_name => 'Product',
+-- pretty_plural => 'Products',
+-- table_name => 'EC_PRODUCTS',
+-- id_column => 'PRODUCT_ID',
+-- package_name => 'ECOMMERCE'
+-- );
+-- end;
+-- /
+-- show errors;
+
+create function inline_0 ()
+returns integer as '
+begin
+
+ PERFORM acs_object_type__create_type (
+ ''ec_product'',
+ ''Product'',
+ ''Products'',
+ ''acs_object'',
+ ''ec_products'',
+ ''product_id'',
+ ''ecommerce'',
+ ''f'',
+ null,
+ null
+ );
+
+ return 0;
+
+end;' language 'plpgsql';
+
+select inline_0 ();
+drop function inline_0 ();
+
+-- wtem@olywa.net, 2001-03-24
+-- we aren't going to bother to define all the attributes of an ec_product type
+-- for now, because we are just using it for site-wide-search anyway
+-- we have a corresponding pl/sql package for the ec_product object_type
+-- it can be found at ecommerce/sql/ec-product-package-create.sql
+-- and is called at the end of this script
+create table qci_ec_products (
+ product_id integer constraint qci_ec_products_product_id_fk
+ references acs_objects(object_id)
+ on delete cascade
+ constraint qci_ec_products_product_id_pk
+ primary key,
+ -- above changed by wtem@olywa.net, 2001-03-24
+ -- integer not null primary key,
+ sku varchar(100),
+ product_name varchar(200),
+ creation_date timestamptz default current_timestamp not null,
+ one_line_description varchar(400),
+ detailed_description varchar(4000),
+ search_keywords varchar(4000),
+ -- this is the regular price for the product. If user
+ -- classes are charged a different price, it should be
+ -- specified in qci_ec_product_user_class_prices
+ price numeric,
+ -- for stuff that can't be shipped like services
+ no_shipping_avail_p boolean default 'f',
+ -- leave this blank if shipping is calculated using
+ -- one of the more complicated methods available
+ shipping numeric,
+ -- fill this in if shipping is calculated by: above price
+ -- for first item (with this product_id), and the below
+ -- price for additional items (with this product_id)
+ shipping_additional numeric,
+ -- fill this in if shipping is calculated using weight
+ -- use whatever units you want (lbs/kg), just be consistent
+ -- and make your shipping algorithm take the units into
+ -- account
+ weight numeric,
+ -- holds pictures, sample chapters, etc.
+ dirname varchar(200),
+ -- whether this item should show up in searches (e.g., if it's
+ -- a volume of a series, you might not want it to)
+ present_p boolean default 't',
+ -- whether the item should show up at all in the user pages
+ active_p boolean default 't',
+ -- the date the product becomes available for sale (it can be listed
+ -- before then, it's just not buyable)
+ available_date timestamptz default current_timestamp not null,
+ announcements varchar(4000),
+ announcements_expire timestamptz,
+ -- if there's a web site with more info about the product
+ url varchar(300),
+ template_id integer references ecca_ec_templates,
+ -- o = out of stock, q = ships quickly, m = ships
+ -- moderately quickly, s = ships slowly, i = in stock
+ -- with no message about the speed of the shipment (shipping
+ -- messages are in parameters .ini file)
+ stock_status char(1) check (stock_status in ('o','q','m','s','i')),
+ -- comma-separated lists of available colors, sizes, and styles for the user
+ -- to choose upon ordering
+ color_list varchar(4000),
+ size_list varchar(4000),
+ style_list varchar(4000),
+ -- email this list on purchase
+ email_on_purchase_list varchar(4000),
+ -- the user ID and IP address of the creator of the product
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+);
+
+create view qci_ec_products_displayable
+as
+select * from qci_ec_products
+where active_p='t';
+
+create view qci_ec_products_searchable
+as
+select * from qci_ec_products
+where active_p='t' and present_p='t';
+
+create table qci_ec_products_audit (
+ product_id integer,
+ product_name varchar(200),
+ creation_date timestamptz,
+ one_line_description varchar(400),
+ detailed_description varchar(4000),
+ search_keywords varchar(4000),
+ price numeric,
+ shipping numeric,
+ shipping_additional numeric,
+ weight numeric,
+ dirname varchar(200),
+ present_p boolean default 't',
+ active_p boolean default 't',
+ available_date timestamptz,
+ announcements varchar(4000),
+ announcements_expire timestamptz,
+ url varchar(300),
+ template_id integer,
+ stock_status char(1) check (stock_status in ('o','q','m','s','i')),
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+);
+
+create function qci_ec_products_audit_tr ()
+returns opaque as '
+begin
+ insert into qci_ec_products_audit (
+ product_id, product_name, creation_date,
+ one_line_description, detailed_description,
+ search_keywords, shipping,
+ shipping_additional, weight,
+ dirname, present_p,
+ active_p, available_date,
+ announcements, announcements_expire,
+ url, template_id,
+ stock_status,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.product_id, old.product_name, old.creation_date,
+ old.one_line_description, old.detailed_description,
+ old.search_keywords, old.shipping,
+ old.shipping_additional, old.weight,
+ old.dirname, old.present_p,
+ old.active_p, old.available_date,
+ old.announcements, old.announcements_expire,
+ old.url, old.template_id,
+ old.stock_status,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+end;' language 'plpgsql';
+
+create trigger qci_ec_products_audit_tr
+after update or delete on qci_ec_products
+for each row execute procedure qci_ec_products_audit_tr ();
+
+
+-- people who bought product_id also bought products 0 through
+-- 4, where product_0 is the most frequently purchased, 1 is next,
+-- etc.
+create table qci_ec_product_purchase_comb (
+ product_id integer not null primary key references qci_ec_products,
+ product_0 integer references qci_ec_products,
+ product_1 integer references qci_ec_products,
+ product_2 integer references qci_ec_products,
+ product_3 integer references qci_ec_products,
+ product_4 integer references qci_ec_products
+);
+
+create index qci_ec_product_purchase_comb_idx0 on qci_ec_product_purchase_comb(product_0);
+create index qci_ec_product_purchase_comb_idx1 on qci_ec_product_purchase_comb(product_1);
+create index qci_ec_product_purchase_comb_idx2 on qci_ec_product_purchase_comb(product_2);
+create index qci_ec_product_purchase_comb_idx3 on qci_ec_product_purchase_comb(product_3);
+create index qci_ec_product_purchase_comb_idx4 on qci_ec_product_purchase_comb(product_4);
+
+create sequence qci_ec_sale_price_id_seq start 1;
+create view qci_ec_sale_price_id_sequence as select nextval('qci_ec_sale_price_id_seq') as nextval;
+
+create table qci_ec_sale_prices (
+ sale_price_id integer not null primary key,
+ product_id integer not null references qci_ec_products,
+ sale_price numeric,
+ sale_begins timestamptz not null,
+ sale_ends timestamptz not null,
+ -- like Introductory Price or Sale Price or Special Offer
+ sale_name varchar(30),
+ -- if non-null, the user has to know this code to get the sale price
+ offer_code varchar(20),
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+);
+
+create index qci_ec_sale_prices_by_product_idx on qci_ec_sale_prices(product_id);
+
+create view qci_ec_sale_prices_current
+as
+select * from qci_ec_sale_prices
+where now() >= sale_begins
+and now() <= sale_ends;
+
+
+create table qci_ec_sale_prices_audit (
+ sale_price_id integer,
+ product_id integer,
+ sale_price numeric,
+ sale_begins timestamptz,
+ sale_ends timestamptz,
+ sale_name varchar(30),
+ offer_code varchar(20),
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+);
+
+
+create function qci_ec_sale_prices_audit_tr ()
+returns opaque as '
+begin
+ insert into qci_ec_sale_prices_audit (
+ sale_price_id, product_id, sale_price,
+ sale_begins, sale_ends, sale_name, offer_code,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.sale_price_id, old.product_id, old.sale_price,
+ old.sale_begins, old.sale_ends, old.sale_name, old.offer_code,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+end;' language 'plpgsql';
+
+create trigger qci_ec_sale_prices_audit_tr
+after update or delete on qci_ec_sale_prices
+for each row execute procedure qci_ec_sale_prices_audit_tr ();
+
+
+create table qci_ec_product_series_map (
+ -- this is the product_id of a product that happens to be
+ -- a series
+ series_id integer not null references qci_ec_products,
+ -- this is the product_id of a product that is one of the
+ -- components of the above series
+ component_id integer not null references qci_ec_products,
+ primary key (series_id, component_id),
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+);
+
+create index qci_ec_product_series_map_idx2 on qci_ec_product_series_map(component_id);
+
+create table qci_ec_product_series_map_audit (
+ series_id integer,
+ component_id integer,
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+);
+
+
+create function qci_ec_product_series_map_audit_tr ()
+returns opaque as '
+begin
+ insert into qci_ec_product_series_map_audit (
+ series_id, component_id,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.series_id, old.component_id,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+end;' language 'plpgsql';
+
+create trigger qci_ec_product_series_map_audit_tr
+after update or delete on qci_ec_product_series_map
+for each row execute procedure qci_ec_product_series_map_audit_tr ();
+
+
+
+
+
+-- this specifies that product_a links to product_b on the display page for product_a
+create table qci_ec_product_links (
+ product_a integer not null references qci_ec_products,
+ product_b integer not null references qci_ec_products,
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null,
+ primary key (product_a, product_b)
+);
+
+create index qci_ec_product_links_idx on qci_ec_product_links (product_b);
+
+create table qci_ec_product_links_audit (
+ product_a integer,
+ product_b integer,
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+);
+
+create function qci_ec_product_links_audit_tr ()
+returns opaque as '
+begin
+ insert into qci_ec_product_links_audit (
+ product_a, product_b,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.product_a, old.product_b,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+end;' language 'plpgsql';
+
+create trigger qci_ec_product_links_audit_tr
+after update or delete on qci_ec_product_links
+for each row execute procedure qci_ec_product_links_audit_tr ();
+
+
+-- comments made by users on the products
+create table qci_ec_product_comments (
+ comment_id integer not null primary key,
+ product_id integer not null references qci_ec_products,
+ user_id integer not null references users,
+ user_comment varchar(4000),
+ one_line_summary varchar(300),
+ rating numeric,
+ -- in some systems, the administrator will have to approve comments first
+ approved_p boolean,
+ comment_date timestamptz,
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+);
+
+create index qci_ec_product_comments_idx on qci_ec_product_comments(product_id);
+create index qci_ec_product_comments_idx2 on qci_ec_product_comments(user_id);
+create index qci_ec_product_comments_idx3 on qci_ec_product_comments(approved_p);
+
+create table qci_ec_product_comments_audit (
+ comment_id integer,
+ product_id integer,
+ user_id integer,
+ user_comment varchar(4000),
+ one_line_summary varchar(300),
+ rating numeric,
+ approved_p boolean,
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+);
+
+create function qci_ec_product_comments_audit_tr ()
+returns opaque as '
+begin
+ insert into qci_ec_product_comments_audit (
+ comment_id, product_id, user_id,
+ user_comment, one_line_summary, rating, approved_p,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.comment_id, old.product_id, old.user_id,
+ old.user_comment, old.one_line_summary, old.rating, old.approved_p,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+end;' language 'plpgsql';
+
+create trigger qci_ec_product_comments_audit_tr
+after update or delete on qci_ec_product_comments
+for each row execute procedure qci_ec_product_comments_audit_tr ();
+
+
+create sequence qci_ec_product_review_id_seq start 1;
+create view qci_ec_product_review_id_sequence as select nextval('qci_ec_product_review_id_seq') as nextval;
+
+-- reviews made by professionals of the products
+create table qci_ec_product_reviews (
+ review_id integer not null primary key,
+ product_id integer not null references qci_ec_products,
+ author_name varchar(100),
+ publication varchar(100),
+ review_date timestamptz,
+ -- in HTML format
+ review text,
+ display_p boolean,
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+);
+
+create index qci_ec_product_reviews_idx on qci_ec_product_reviews (product_id);
+create index qci_ec_product_reviews_idx2 on qci_ec_product_reviews (display_p);
+
+create table qci_ec_product_reviews_audit (
+ review_id integer,
+ product_id integer,
+ author_name varchar(100),
+ publication varchar(100),
+ review_date timestamptz,
+ -- in HTML format
+ review text,
+ display_p boolean,
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+);
+
+create function qci_ec_product_reviews_audit_tr ()
+returns opaque as '
+begin
+ insert into qci_ec_product_reviews_audit (
+ review_id, product_id,
+ author_name, publication, review_date,
+ review,
+ display_p,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.review_id, old.product_id,
+ old.author_name, old.publication, old.review_date,
+ old.review,
+ old.display_p,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+end;' language 'plpgsql';
+
+create trigger qci_ec_product_reviews_audit_tr
+after update or delete on qci_ec_product_reviews
+for each row execute procedure qci_ec_product_reviews_audit_tr ();
+
+-- I could in theory make some hairy system that lets them specify
+-- what kind of form element each field will have, does
+-- error checking, etc., but I don't think it's necessary since it's
+-- just the site administrator using it. So here's a very simple
+-- table to store the custom product fields:
+create table qci_ec_custom_product_fields (
+ field_identifier varchar(100) not null primary key,
+ field_name varchar(100),
+ default_value varchar(100),
+ -- column type for oracle (i.e. text, varchar(50), integer, ...)
+ column_type varchar(100),
+ creation_date timestamptz,
+ active_p boolean default 't',
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+);
+
+create table qci_ec_custom_product_fields_audit (
+ field_identifier varchar(100),
+ field_name varchar(100),
+ default_value varchar(100),
+ column_type varchar(100),
+ creation_date timestamptz,
+ active_p boolean default 't',
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+);
+
+create function qci_ec_custom_prod_fields_audit_tr ()
+returns opaque as '
+begin
+ insert into qci_ec_custom_product_fields_audit (
+ field_identifier, field_name,
+ default_value, column_type,
+ creation_date, active_p,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.field_identifier, old.field_name,
+ old.default_value, old.column_type,
+ old.creation_date, old.active_p,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+end;' language 'plpgsql';
+
+create trigger qci_ec_custom_prod_fields_audit_tr
+after update or delete on qci_ec_custom_product_fields
+for each row execute procedure qci_ec_custom_prod_fields_audit_tr ();
+
+-- more columns are added to this table (by Tcl scripts) when the
+-- administrator adds custom product fields
+-- the columns in this table have the name of the field_identifiers
+-- in qci_ec_custom_product_fields
+-- this table stores the values
+create table qci_ec_custom_product_field_values (
+ product_id integer not null primary key references qci_ec_products,
+ last_modified timestamptz not null,
+ last_modifying_user integer not null references users,
+ modified_ip_address varchar(20) not null
+);
+
+create table qci_ec_custom_p_field_values_audit (
+ product_id integer,
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+);
+
+create function qci_ec_custom_p_f_values_audit_tr ()
+returns opaque as '
+begin
+ insert into qci_ec_custom_p_field_values_audit (
+ product_id,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.product_id,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+end;' language 'plpgsql';
+
+create trigger qci_ec_custom_p_f_values_audit_tr
+after update or delete on qci_ec_custom_product_field_values
+for each row execute procedure qci_ec_custom_p_f_values_audit_tr();
+
+
+
Index: openacs-4/packages/inventory-control/sql/postgresql/inventory-control-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/sql/postgresql/inventory-control-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/inventory-control/sql/postgresql/inventory-control-drop.sql 3 Jul 2006 19:29:14 -0000 1.1
@@ -0,0 +1,148 @@
+-- inventory-control-drop.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+--
+
+drop trigger qci_ec_custom_p_f_values_audit_tr on qci_ec_custom_product_field_values;
+
+drop function qci_ec_custom_p_f_values_audit_tr ();
+
+drop table qci_ec_custom_p_field_values_audit;
+
+drop table qci_ec_custom_product_field_values;
+
+drop trigger qci_ec_custom_prod_fields_audit_tr on qci_ec_custom_product_fields;
+
+drop function qci_ec_custom_prod_fields_audit_tr ();
+
+drop table qci_ec_custom_product_fields_audit;
+
+drop table qci_ec_custom_product_fields;
+
+drop trigger qci_ec_product_reviews_audit_tr on qci_ec_product_reviews;
+
+drop function qci_ec_product_reviews_audit_tr;
+
+drop table qci_ec_product_reviews_audit;
+
+drop index qci_ec_product_reviews_idx2 on qci_ec_product_reviews;
+drop index qci_ec_product_reviews_idx on qci_ec_product_reviews;
+
+drop table qci_ec_product_reviews;
+
+
+drop view qci_ec_product_review_id_sequence;
+drop sequence qci_ec_product_review_id_seq;
+
+
+drop trigger qci_ec_product_comments_audit_tr on qci_ec_product_comments;
+
+drop function qci_ec_product_comments_audit_tr ();
+
+drop table qci_ec_product_comments_audit ();
+
+drop index qci_ec_product_comments_idx3 on qci_ec_product_comments();
+drop index qci_ec_product_comments_idx2 on qci_ec_product_comments();
+drop index qci_ec_product_comments_idx on qci_ec_product_comments();
+
+drop table qci_ec_product_comments ();
+
+
+
+drop trigger qci_ec_product_links_audit_tr on qci_ec_product_links;
+
+drop function qci_ec_product_links_audit_tr ();
+
+drop table qci_ec_product_links_audit ();
+
+drop index qci_ec_product_links_idx on qci_ec_product_links ();
+
+drop table qci_ec_product_links ();
+
+
+
+drop trigger qci_ec_product_u_c_prices_audit_tr on qci_ec_product_user_class_prices;
+
+drop function qci_ec_product_u_c_prices_audit_tr ();
+
+drop table qci_ec_product_u_c_prices_audit ();
+
+drop index qci_ec_product_user_class_idx on qci_ec_product_user_class_prices();
+
+drop table qci_ec_product_user_class_prices ();
+
+
+drop trigger qci_ec_product_series_map_audit_tr on qci_ec_product_series_map;
+
+drop function qci_ec_product_series_map_audit_tr ();
+
+
+drop table qci_ec_product_series_map_audit ();
+
+drop index qci_ec_product_series_map_idx2 on qci_ec_product_series_map();
+
+drop table qci_ec_product_series_map ();
+
+
+drop trigger qci_ec_sale_prices_audit_tr on qci_ec_sale_prices;
+
+drop function qci_ec_sale_prices_audit_tr ();
+
+
+drop table qci_ec_sale_prices_audit;
+
+
+drop view qci_ec_sale_prices_current;
+
+drop index qci_ec_sale_prices_by_product_idx on qci_ec_sale_prices();
+
+drop table qci_ec_sale_prices;
+
+drop view qci_ec_sale_price_id_sequence;
+drop sequence qci_ec_sale_price_id_seq;
+
+drop index qci_ec_product_purchase_comb_idx4 on qci_ec_product_purchase_comb();
+drop index qci_ec_product_purchase_comb_idx3 on qci_ec_product_purchase_comb();
+drop index qci_ec_product_purchase_comb_idx2 on qci_ec_product_purchase_comb();
+drop index qci_ec_product_purchase_comb_idx1 on qci_ec_product_purchase_comb();
+drop index qci_ec_product_purchase_comb_idx0 on qci_ec_product_purchase_comb();
+
+drop table qci_ec_product_purchase_comb ();
+
+drop trigger qci_ec_products_audit_tr on qci_ec_products;
+
+drop function qci_ec_products_audit_tr ();
+
+drop table qci_ec_products_audit ();
+
+drop view qci_ec_products_searchable;
+
+drop view qci_ec_products_displayable;
+
+drop table qci_ec_products();
+
+
+
+drop function qci_timespan_days();
+
+drop function qci_least();
+
+DROP TRIGGER qci_check_inventory ON qar_oe;
+
+
+
+DROP FUNCTION qci_check_inventory();
+
+
+DROP TABLE qci_inventory;
+
+
+DROP TABLE qci_warehouse;
+
+
+DROP TABLE qci_makemodel;
+
+
Index: openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities-drop.sql 3 Jul 2006 19:29:14 -0000 1.1
@@ -0,0 +1,4 @@
+-- defined in the acs-core
+-- drop function logical_negation(boolean);
+drop function one_if_within_n_days (timestamptz, integer);
+drop function pseudo_contains (varchar, varchar);
Index: openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/inventory-control/sql/postgresql/pl-sql-utilities.sql 3 Jul 2006 19:29:14 -0000 1.1
@@ -0,0 +1,59 @@
+--
+-- pl-sql.sql
+--
+-- created by philg on 11/18/98
+--
+-- useful pl/sql utility procedures
+--
+
+-- useful for ecommerce and other situations where you want to
+-- know whether something happened within last N days (assumes query_date
+-- is in the past)
+
+create function one_if_within_n_days (timestamptz, integer)
+returns integer as '
+declare
+ query_date alias for $1;
+ n_days alias for $2;
+begin
+ if current_timestamp - query_date <= timespan_days(n_days) then
+ return 1;
+ else
+ return 0;
+ end if;
+end;' language 'plpgsql';
+
+create function pseudo_contains (varchar, varchar)
+returns integer as '
+declare
+ indexed_stuff alias for $1;
+ space_sep_list_untrimmed alias for $2;
+ space_sep_list text;
+ upper_indexed_stuff text;
+ -- if you call this var start you get hosed royally
+ first_space integer;
+ score integer;
+begin
+ space_sep_list := upper(ltrim(rtrim(space_sep_list_untrimmed)));
+ upper_indexed_stuff := upper(indexed_stuff);
+ score := 0;
+ if space_sep_list is null or indexed_stuff is null then
+ return score;
+ end if;
+ loop
+ first_space := position('' '' in space_sep_list);
+ if first_space = 0 then
+ -- one token or maybe end of list
+ if position(space_sep_list in upper_indexed_stuff) <> 0 then
+ return score+10;
+ end if;
+ return score;
+ else
+ -- first_space <> 0
+ if position(substring(space_sep_list from 1 for first_space-1) in upper_indexed_stuff) <> 0 then
+ score := score + 10;
+ end if;
+ end if;
+ space_sep_list := substring(space_sep_list from first_space+1);
+ end loop;
+end;' language 'plpgsql';
Index: openacs-4/packages/inventory-control/www/doc/index.adp
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/inventory-control/www/doc/index.adp,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/inventory-control/www/doc/index.adp 3 Jul 2006 19:29:14 -0000 1.1
@@ -0,0 +1,4 @@
+
+
+Porting from sql-ledger: IC.pm IR.pm IS.pm ic.pl io.pl ir.pl is.pl
+
Index: openacs-4/packages/shipping-tracking/shipping-tracking.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/shipping-tracking/shipping-tracking.info,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/shipping-tracking/shipping-tracking.info 3 Jul 2006 19:30:26 -0000 1.1
@@ -0,0 +1,32 @@
+
+
+
+
+ Ship-Track
+ Ship-Tracks
+ f
+ f
+
+
+ OpenACS community
+ Ship-Track package provides pricing estimates and tracks shipped items
+ Dekka Corp of Oregon
+ Ship-Track package provides pricing estimates (include or integrate with value-based-shipping and tracks shipped items, includes handling shipping address. (may be similar to a shipping-gateway that handles multiple shipping services (3rd party as well as in-house estimating and delivery techniques)
+ 0
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/packages/shipping-tracking/sql/postgresql/shipping-tracking-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/shipping-tracking/sql/postgresql/shipping-tracking-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/shipping-tracking/sql/postgresql/shipping-tracking-create.sql 3 Jul 2006 19:30:26 -0000 1.1
@@ -0,0 +1,124 @@
+-- shipping-tracking-create.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+--
+
+-- this is from SL, but we're moving the address into contacts package
+
+create table ecst_shipto (
+ trans_id int,
+ shiptoname varchar(64),
+ shiptoaddress1 varchar(32),
+ shiptoaddress2 varchar(32),
+ shiptocity varchar(32),
+ shiptostate varchar(32),
+ shiptozipcode varchar(10),
+ shiptocountry varchar(32),
+ shiptocontact varchar(64),
+ shiptophone varchar(20),
+ shiptofax varchar(20),
+ shiptoemail text
+);
+
+--
+create index ecst_shipto_trans_id_key on ecst_shipto (trans_id);
+
+-- we need to import the ecommerce shipping-tracking model here
+
+-- this is needed because orders might be only partially shipped
+-- create sequence ecst_shipment_id_seq;
+ create view ecst_shipment_id_sequence as select nextval('ecst_shipment_id_seq') as nextval;
+
+ create table ecst_shipments (
+ shipment_id integer not null primary key,
+ order_id integer not null references qar_ec_orders,
+ -- usually, but not necessarily, the same as the shipping_address
+ -- in ecst_orders because a customer may change their address between
+ -- shipments.
+ -- a trigger fills address_id in automatically if it's null
+ address_id integer references qal_ec_addresses,
+ shipment_date timestamptz not null,
+ expected_arrival_date timestamptz,
+ carrier varchar(50), -- e.g., 'fedex'
+ tracking_number varchar(24),
+ -- only if we get confirmation from carrier that the goods
+ -- arrived on a specific date
+ actual_arrival_date timestamptz,
+ -- arbitrary info from carrier, e.g., 'Joe Smith signed for it'
+ actual_arrival_detail varchar(4000),
+ -- for things that aren't really shipped like services
+ shippable_p boolean default 't',
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20)
+ );
+
+ create index ecst_shipments_by_order_id on ecst_shipments(order_id);
+ create index ecst_shipments_by_shipment_date on ecst_shipments(shipment_date);
+
+ -- fills address_id into ecst_shipments if it's missing
+ -- (using the shipping_address associated with the order)
+
+ create function ecst_shipment_address_update_tr ()
+ returns opaque as '
+ declare
+ v_address_id qal_ec_addresses.address_id%TYPE;
+ begin
+ select into v_address_id shipping_address
+ from ecst_orders where order_id=new.order_id;
+ IF new.address_id is null THEN
+ new.address_id := v_address_id;
+ END IF;
+ return new;
+ end;' language 'plpgsql';
+
+ create trigger ecst_shipment_address_update_tr
+ before insert on ecst_shipments
+ for each row execute procedure ecst_shipment_address_update_tr ();
+
+ create table ecst_shipments_audit (
+ shipment_id integer,
+ order_id integer,
+ address_id integer,
+ shipment_date timestamptz,
+ expected_arrival_date timestamptz,
+ carrier varchar(50),
+ tracking_number varchar(24),
+ actual_arrival_date timestamptz,
+ actual_arrival_detail varchar(4000),
+ last_modified timestamptz,
+ last_modifying_user integer,
+ modified_ip_address varchar(20),
+ delete_p boolean default 'f'
+ );
+
+ create function ecst_shipments_audit_tr ()
+ returns opaque as '
+ begin
+ insert into ecst_shipments_audit (
+ shipment_id, order_id, address_id,
+ shipment_date,
+ expected_arrival_date,
+ carrier, tracking_number,
+ actual_arrival_date, actual_arrival_detail,
+ last_modified,
+ last_modifying_user, modified_ip_address
+ ) values (
+ old.shipment_id, old.order_id, old.address_id,
+ old.shipment_date,
+ old.expected_arrival_date,
+ old.carrier, old.tracking_number,
+ old.actual_arrival_date, old.actual_arrival_detail,
+ old.last_modified,
+ old.last_modifying_user, old.modified_ip_address
+ );
+ return new;
+ end;' language 'plpgsql';
+
+ create trigger ecst_shipments_audit_tr
+ after update or delete on ecst_shipments
+ for each row execute procedure ecst_shipments_audit_tr ();
+
Index: openacs-4/packages/shipping-tracking/sql/postgresql/shipping-tracking-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/shipping-tracking/sql/postgresql/shipping-tracking-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/shipping-tracking/sql/postgresql/shipping-tracking-drop.sql 3 Jul 2006 19:30:26 -0000 1.1
@@ -0,0 +1,33 @@
+-- shipping-tracking-drop.sql
+--
+-- @author Dekka Corp.
+-- @ported from sql-ledger and combined with parts from OpenACS ecommerce package
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+--
+
+-- this is from SL, but we're moving the address into contacts package
+
+ drop trigger ecst_shipments_audit_tr on ecst_shipments;
+
+ drop function ecst_shipments_audit_tr ();
+
+ drop table ecst_shipments_audit ();
+
+ drop trigger ecst_shipment_address_update_tr on ecst_shipments;
+
+ drop function ecst_shipment_address_update_tr ();
+
+ drop index ecst_shipments_by_shipment_date on ecst_shipments(shipment_date);
+ drop index ecst_shipments_by_order_id on ecst_shipments(order_id);
+
+ drop table ecst_shipments ();
+
+ drop view ecst_shipment_id_sequence as select nextval('ecst_shipment_id_seq') as nextval;
+
+drop index ecst_shipto_trans_id_key on ecst_shipto (trans_id);
+
+
+drop table ecst_shipto ();
+
+
Index: openacs-4/packages/spreadsheet/spreadsheet.info
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/spreadsheet.info,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/spreadsheet/spreadsheet.info 3 Jul 2006 19:31:28 -0000 1.1
@@ -0,0 +1,27 @@
+
+
+
+
+ Spreadsheet
+ Spreadsheets
+ f
+ f
+
+
+ Torben Brosten
+ OpenACS community
+ Spreadsheet package for collaboratively building and managing spreadsheets.
+ Dekka Corp of Oregon
+ Spreadsheet package provides users with some spreadsheet-like functionality, such as ability to perform basic queries on package tables for generating customized reports. Smallest spreadsheet can be 1 by 1.
+ 0
+
+
+
+
+
+
+
+
+
+
+
Index: openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-create.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-create.sql 3 Jul 2006 19:31:28 -0000 1.1
@@ -0,0 +1,98 @@
+-- spreadsheet-create.sql
+--
+-- @author Dekka Corp.
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+--
+
+ CREATE TABLE qss_sheets (
+ sheet_id integer constraint qss_sheets_sheet_id_fk
+ references acs_objects(object_id)
+ on delete cascade constraint qss_sheets_sheets_id_pk primary key,
+ -- sheet_id constrained to object_id for permissions
+
+ instance_id integer,
+ -- object_id of mounted instance (context_id)
+
+ name_abbrev varchar(40),
+ -- no spaces, single word reference that can be used in urls, filenames etc
+
+ style_ref varchar(300),
+ -- might be an absolute ref to a css page, or extended to other style references
+
+ sheet_title varchar(80),
+ sheet_description text,
+ orientation varchar(2) default 'RC',
+ -- rc = row reference, column reference
+
+ row_count integer,
+ -- use value if not null
+
+ column_count integer,
+ -- use value if not null
+
+ last_calculated timestamptz,
+ -- should be the max(qss_cells.last_calculated) for a sheet_id
+
+ last_modified timestamptz,
+ -- should be the max(qss_cells.last_modified) for a sheet_id
+
+ sheet_status varchar(8)
+ -- value will likely be one of
+ -- ready values have been calculated and no active processes working
+ -- working the spreadsheet is in a recalculating process
+ -- recalc values have expired, spreadsheet needs to be recalculated
+ );
+
+ CREATE TABLE qss_cells (
+ sheet_id varchar(40) not null,
+ -- should be a value from qss_sheets.sheet_id
+
+ cell_value varchar(1025),
+ -- returned by function or user input value
+ -- cell_row = 0 is default value for other cells in same column
+
+ cell_value_sq varchar(80),
+ -- square of cell_value, used frequently in statistics
+ -- values in this column are calculated when
+ -- cell_row = 0 and cell_value is a number
+
+ cell_format varchar(80),
+ -- formatting, css style class
+ -- cell_row = 0 is default value for other cells in same column
+ -- allow some kind of odd/even row formatting change
+ -- maybe two styles separated by comma
+ -- in row 0 represents first,second alternating
+
+ cell_proc varchar(1025),
+ -- usually blank or contains a function
+ -- cell_row = 0 is default proc for other cells in same column
+ -- we are calling this a proc because theoretically
+ -- an admin could define a macro-like proc that returns
+ -- a value after executing some task, for example, retrieving
+ -- a value from a url on the net.
+ -- See ecommerce templating for a similar implementation
+
+ cell_calc_depth integer not null default '0',
+ -- this value is to be automatically generated and show this
+ -- cells order of calculation based on calculation dependencies
+ -- for example, calc_depth = max (calc_depth of all dependent cells) + 1
+
+ cell_name varchar(40),
+ -- usually blank, an alternate reference to RC format
+ -- unique to a sheet
+ -- if cell_row is 0 then this is a column_name
+
+ cell_title varchar(80),
+ -- a label when displaying cell as a single value
+ -- if cell_row is 0 then this is a column_title
+
+ cell_row integer not null,
+ cell_column integer not null,
+ last_calculated timestamptz,
+ -- handy for checking when cell value dependencies have changed
+
+ last_modified timestamptz
+ -- data entry (cell value) last changed
+
+ );
Index: openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-drop.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-drop.sql,v
diff -u
--- /dev/null 1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/spreadsheet/sql/postgresql/spreadsheet-drop.sql 3 Jul 2006 19:31:28 -0000 1.1
@@ -0,0 +1,8 @@
+-- spreadsheet-create.sql
+--
+-- @author Dekka Corp.
+-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991
+-- @cvs-id
+--
+ DROP TABLE qss_cells;
+ DROP TABLE qss_sheets;