--
-- member-value.sql
--
-- defined by philg@mit.edu on 8/18/98;
-- augmented for release into toolkit on February 12, 1999
--
-- some of the tables here will be prefixed "mv_" ("mv" = "member value")
--
-- if there is a monthly charge, we set different rates for different classes
-- of subscribers, these classes are arbitrary strings, though we suggest
-- 'default' (the standard rate) and 'comp' (complimentary, often for
-- people at other publishing houses)
-- if [ad_parameter "ChargeMonthlyP" "member-value"] is 0
-- then this table need not exist in the database
create table mv_monthly_rates (
subscriber_class varchar(30) not null primary key,
rate number not null,
-- we use three digits because Cybercash does
currency char(3) default 'USD'
);
-- one row per user
create table users_payment (
user_id integer not null unique references users,
-- this might be NULL for systems in which monthly billing
-- is disabled, or maybe not (perhaps we'll use this to give
-- people across-the-board discounts)
subscriber_class varchar(30) references mv_monthly_rates,
name_on_card varchar(100),
-- only really useful for US cardholders, but maybe useful in
-- long run too
card_zip varchar(20),
card_number varchar(20),
-- store this in CyberCash format, e.g., "08/99"
card_exp varchar(10)
);
-- we use this to collect up user charges
create sequence users_order_id_sequence;
-- you can't type this table into SQL*Plus 8.0.5 interactively; you
-- have to make sure that you're loading this file or this table
-- via sqlplus / < member-value.sql
create table users_orders (
-- Goes into table at confirmation time:
order_id integer primary key,
user_id integer not null references users,
confirmed_date date,
order_state varchar(50) not null,
price_charged number,
currency char(3) default 'USD',
-- Goes into table at authorization time (columns named
-- cc_* refer to output from CyberCash):
authorized_date date,
cc_auth_status varchar(100),
cc_auth_txn_id varchar(100),
cc_auth_errloc varchar(100),
cc_auth_errmsg varchar(200),
cc_auth_aux_msg varchar(200),
cc_auth_auth_code varchar(100),
cc_auth_action_code char(3),
cc_auth_avs_code varchar(3),
-- processor-specific and not obviously useful for cybercash
-- perhaps useful when looking at statements from
-- merchant's bank
cc_auth_ref_code varchar(100),
-- Goes into table after querying for "settled" transaction type:
cc_sett_date date,
cc_sett_status varchar(100),
cc_sett_txn_id varchar(100),
cc_sett_auth_code varchar(100),
cc_sett_batch_id varchar(100),
cc_sett_action_code char(3),
cc_sett_avs_code varchar(3),
cc_sett_ref_code varchar(100),
-- Goes into table at return time (i.e. when we use
-- the API message "return" to mark the orders for return).
-- tried_to_return_date exists in case CyberCash doesn't
-- respond to our return attempt (in which case we can
-- retry later).
-- Important note: "return" has no implicit connection with
-- the product being received back (that would recorded in
-- the received_back_date column).
tried_to_return_date date,
return_date date,
refunded_amount number,
cc_retn_status varchar(100),
cc_retn_txn_id varchar(100),
cc_retn_errloc varchar(100),
cc_retn_errmsg varchar(200),
cc_retn_aux_msg varchar(200),
cc_retn_auth_code varchar(100),
cc_retn_action_code char(3),
cc_retn_avs_code varchar(3),
cc_retn_ref_code varchar(100),
-- Goes into table after querying for "setlret" transaction type
-- (for returns that have been settled):
-- NOTE: I'm assuming that CyberCash is automatically settling
-- orders of type "markret" as it is orders of type "marked", since
-- we are in auto settle mode. We will find out shortly.
cc_sret_date date,
cc_sret_status varchar(100),
cc_sret_txn_id varchar(100),
cc_sret_auth_code varchar(100),
cc_sret_batch_id varchar(100),
cc_sret_action_code char(3),
cc_sret_avs_code varchar(3),
cc_sret_ref_code varchar(100),
-- Goes into table when voiding a "marked" transaction
-- The CyberCash manual states that all of the standard
-- output fields are returned, although I've only witnessed
-- aux-msg, Mstatus, MErrMsg, and MErrLoc
tried_to_void_marked_date date,
void_marked_date date,
cc_vdmk_status varchar(100),
cc_vdmk_txn_id varchar(100),
cc_vdmk_errloc varchar(100),
cc_vdmk_errmsg varchar(200),
cc_vdmk_aux_msg varchar(200),
cc_vdmk_auth_code varchar(100),
cc_vdmk_action_code char(3),
cc_vdmk_avs_code varchar(3),
cc_vdmk_ref_code varchar(100),
-- Goes into table when voiding a "markret" transaction
tried_to_void_markret_date date,
void_markret_date date,
cc_vdrn_status varchar(100),
cc_vdrn_txn_id varchar(100),
cc_vdrn_errloc varchar(100),
cc_vdrn_errmsg varchar(200),
cc_vdrn_aux_msg varchar(200),
cc_vdrn_auth_code varchar(100),
cc_vdrn_action_code char(3),
cc_vdrn_avs_code varchar(3),
cc_vdrn_ref_code varchar(100),
-- did the consumer initiate a dispute from his end?
disputed_p char(1) check (disputed_p in ('t','f')),
-- date on which we discovered the dispute
dispute_discovery_date date,
-- if the consumer's bank got his money back from us forcibly
charged_back_p char(1) check (charged_back_p in ('t','f')),
comments varchar(4000)
);
-- transaction charges
-- charge_type will generally be one of the column names from
-- member-values parameters with the "Rate" chopped off, e.g., "ClassifiedAd"
-- for posting an ad, and then we change the style to our
-- standard DB key (lowercase words, underscores), e.g., "classified_ad"
-- for the standard monthly subscription charge (if any), the
-- charge_type will be "monthly"
-- charge_key will vary depending on charge_type; for something
-- like a classified ad, it would be the classified_ad_id (an integer)
-- for a bboard posting it would be the msg_id (char(6)).
-- the amount is theoretically derivable from the charge_type but we
-- keep it here because (1) rates might change over time, (2) the admins
-- might decide to charge someone a non-standard rate
create table users_charges (
user_id integer not null references users,
-- if a human being decided to charge this person
admin_id integer references users,
charge_type varchar(30) not null,
charge_key varchar(100),
amount number not null,
currency char(3) default 'USD',
entry_date date not null,
charge_comment varchar(4000),
-- if we're trying to bill this out, order_id will be non-null
order_id integer references users_orders
);
create index users_charges_by_user on users_charges(user_id);
-- billing them all out every month (or whatever)
-- we write a row in this table whenever the billing proc was
-- completely successful (i.e., billed everyone who needed to be
-- billed). That way we know we don't have to sweep for
-- users who need to be billed
-- we generate sweep_id with select max+1 rather than an Oracle
-- sequence because we want them guaranteed sequential
create table mv_billing_sweeps (
sweep_id integer primary key,
start_time date,
success_time date,
n_orders integer
);
-- to bill correctly, we often need to query for charges that
-- accumulated until the end of the last month, so we use < 1st of
-- this month at midnight
create or replace function mv_first_of_month
return date
as
current_year varchar(30);
current_month varchar(30);
begin
current_year := to_char(sysdate,'YYYY');
current_month := to_char(sysdate,'MM');
return to_date(current_year || '-' || current_month || '-01 00:00:00','YYYY-MM-DD HH24:MI:SS');
end mv_first_of_month;
/
show errors