Index: openacs-4/packages/customer-service/customer-service.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/customer-service/customer-service.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/customer-service/customer-service.info 3 Jul 2006 19:33:32 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + Customer Service + Customer Service + f + f + + + Torben Brosten + OpenACS community + Customer Service package manages customer service interactions and customer service issues. + Dekka Corp of Oregon + Customer Service package manages interactions and issues with customers. This package will superscede the customer service part of ecommerce package. + 0 + + + + + + + + + + + Index: openacs-4/packages/customer-service/sql/postgresql/customer-service-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/customer-service/sql/postgresql/customer-service-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/customer-service/sql/postgresql/customer-service-create.sql 3 Jul 2006 19:33:33 -0000 1.1 @@ -0,0 +1,352 @@ +-- customer-service-create.sql +-- +-- @author Dekka Corp. +-- @ported from OpenACS ecommerce package +-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991 +-- @cvs-id +-- + +--------- customer service -------------------- + +-- create sequence eccs_issue_id_seq; +-- create view eccs_issue_id_sequence as select nextval('eccs_issue_id_seq') as nextval; +-- create sequence eccs_action_id_seq; +-- create view eccs_action_id_sequence as select nextval('eccs_action_id_seq') as nextval; +-- create sequence eccs_interaction_id_seq; +-- create view eccs_interaction_id_sequence as select nextval('eccs_interaction_id_seq') as nextval; +-- create sequence eccs_user_ident_id_seq; +-- create view eccs_user_ident_id_sequence as select nextval('eccs_user_ident_id_seq') as nextval; +-- +-- -- this contains the bits of info a cs rep uses to identify +-- -- a user +-- -- often user_id is not known and the customer service rep +-- -- will have to get other info in order to identify the user +-- create table eccs_user_identification ( +-- user_identification_id integer not null primary key, +-- date_added timestamptz, +-- user_id integer references users, +-- email varchar(100), +-- first_names varchar(100), +-- last_name varchar(100), +-- -- this is varchar(80) in community-core.sql, so I'll be consistent +-- postal_code varchar(80), +-- other_id_info varchar(2000) +-- ); +-- +-- -- should index everything because this all columns may potentially +-- -- be searched through every time a new interaction is recorded +-- create index eccs_user_ident_by_user_id on eccs_user_identification(user_id); +-- create index eccs_user_ident_by_email on eccs_user_identification(email); +-- create index eccs_user_ident_by_first_names on eccs_user_identification(first_names); +-- create index eccs_user_ident_by_last_name on eccs_user_identification(last_name); +-- create index eccs_user_ident_by_postal_code on eccs_user_identification(postal_code); +-- +-- +-- -- puts date_added into eccs_user_identification if it's missing +-- create function eccs_user_identificate_date_tr () +-- returns opaque as ' +-- begin +-- IF new.date_added is null THEN +-- new.date_added := now(); +-- END IF; +-- return new; +-- end;' language 'plpgsql'; +-- +-- create trigger eccs_user_identificate_date_tr +-- after insert on eccs_user_identification +-- for each row execute procedure eccs_user_identificate_date_tr (); +-- +-- +-- create table eccs_customer_serv_interactions ( +-- interaction_id integer not null primary key, +-- customer_service_rep integer references users, +-- user_identification_id integer not null +-- references eccs_user_identification, +-- interaction_date timestamptz, +-- interaction_originator varchar(20) not null, -- e.g. customer, customer-service-rep, automatic +-- interaction_type varchar(30) not null, -- e.g. email, phone_call +-- -- will be filled in if the customer-originated interaction is +-- -- an email +-- interaction_headers varchar(4000) +-- ); +-- +-- create index eccs_csin_by_user_ident_id on eccs_customer_serv_interactions(user_identification_id); +-- +-- -- gilbertw - used the code in OpenACS 3.2.5 as a reference +-- create function eccs_cs_interaction_inserts () +-- returns opaque as ' +-- begin +-- IF new.interaction_date is null THEN +-- new.interaction_date := now(); +-- END IF; +-- return new; +-- end;' language 'plpgsql'; +-- +-- create trigger eccs_cs_interaction_inserts +-- after insert on eccs_customer_serv_interactions +-- for each row execute procedure eccs_cs_interaction_inserts (); +-- +-- create view eccs_customer_service_reps +-- as +-- select * from cc_users +-- where user_id in (select customer_service_rep +-- from eccs_customer_serv_interactions) +-- or user_id in (select issued_by from eccs_gift_certificates_issued); +-- +-- create table eccs_customer_service_issues ( +-- issue_id integer not null primary key, +-- user_identification_id integer not null references eccs_user_identification, +-- -- may be null if this issue isn't associated with an order +-- order_id integer references eccs_orders, +-- -- may be null if this issue isn't associated with a gift certificate +-- gift_certificate_id integer references eccs_gift_certificates, +-- open_date timestamptz not null, +-- close_date timestamptz, +-- -- customer service reps who closed the issue +-- closed_by integer references users, +-- -- we never really delete issues +-- deleted_p boolean default 'f' +-- ); +-- +-- create index eccs_csi_by_user_ident_id on eccs_customer_service_issues(user_identification_id); +-- create index eccs_csi_by_open_date on eccs_customer_service_issues(open_date); +-- +-- -- because an issue can have more than one issue_type +-- create table eccs_cs_issue_type_map ( +-- issue_id integer not null references eccs_customer_service_issues, +-- issue_type varchar(40) not null -- e.g. billing, web site +-- ); +-- +-- create index eccs_csitm_by_issue_id on eccs_cs_issue_type_map(issue_id); +-- create index eccs_csitm_by_issue_type on eccs_cs_issue_type_map(issue_type); +-- +-- -- gilbertw - used code OpenACS 3.2.5 as a reference +-- -- removed INSERTING +-- create function eccs_cs_issue_inserts () +-- returns opaque as ' +-- begin +-- IF new.open_date is null THEN +-- new.open_date := now(); +-- END IF; +-- return new; +-- end;' language 'plpgsql'; +-- +-- create trigger eccs_cs_issue_inserts +-- after insert on eccs_customer_service_issues +-- for each row execute procedure eccs_cs_issue_inserts (); +-- +-- create table eccs_customer_service_actions ( +-- action_id integer not null primary key, +-- issue_id integer not null references eccs_customer_service_issues, +-- interaction_id integer not null references eccs_customer_serv_interactions, +-- action_details varchar(4000), +-- follow_up_required varchar(4000) +-- ); +-- +-- create index eccs_csa_by_issue on eccs_customer_service_actions(issue_id); +-- +-- create table eccs_cs_action_info_used_map ( +-- action_id integer not null references eccs_customer_service_actions, +-- info_used varchar(100) not null +-- ); +-- +-- create index eccs_csaium_by_action_id on eccs_cs_action_info_used_map(action_id); +-- create index eccs_csaium_by_info_used on eccs_cs_action_info_used_map(info_used); +-- +-- -- this table contains picklist choices for the customer service data +-- -- entry people +-- +-- create sequence eccs_picklist_item_id_seq; +-- create view eccs_picklist_item_id_sequence as select nextval('eccs_picklist_item_id_seq') as nextval; +-- +-- create table eccs_picklist_items ( +-- picklist_item_id integer not null primary key, +-- -- pretty, human-readable +-- picklist_item varchar(100), +-- -- which picklist this item is in +-- picklist_name varchar(100), +-- sort_key numeric, +-- last_modified timestamptz not null, +-- last_modifying_user integer not null references users, +-- modified_ip_address varchar(20) not null +-- ); +-- +-- create table eccs_picklist_items_audit ( +-- picklist_item_id integer, +-- picklist_item varchar(100), +-- picklist_name varchar(100), +-- sort_key numeric, +-- last_modified timestamptz, +-- last_modifying_user integer, +-- modified_ip_address varchar(20), +-- delete_p boolean default 'f' +-- ); +-- +-- create function eccs_picklist_items_audit_tr () +-- returns opaque as ' +-- begin +-- insert into eccs_picklist_items_audit ( +-- picklist_item_id, picklist_item, +-- picklist_name, sort_key, +-- last_modified, +-- last_modifying_user, modified_ip_address +-- ) values ( +-- old.picklist_item_id, old.picklist_item, +-- old.picklist_name, old.sort_key, +-- old.last_modified, +-- old.last_modifying_user, old.modified_ip_address +-- ); +-- return new; +-- end;' language 'plpgsql'; +-- +-- create trigger eccs_picklist_items_audit_tr +-- after update or delete on eccs_picklist_items +-- for each row execute procedure eccs_picklist_items_audit_tr (); +-- +-- -- Canned responses for customer support +-- create sequence eccs_canned_response_id_seq; +-- create view eccs_canned_response_id_sequence as select nextval('eccs_canned_response_id_seq') as nextval; +-- +-- create table eccs_canned_responses ( +-- response_id integer not null primary key, +-- one_line varchar(100) not null, +-- response_text varchar(4000) not null +-- ); +-- +-- ----------------------------------------------- +-- +-- -- templates 1-6 are pre-defined (see the insert statements in +-- -- ecommerce-defaults.sql) +-- -- the wording of each can be changed at [eccs_url_concat [eccs_url] /admin]/email-templates/ +-- create sequence eccs_email_template_id_seq start 7; +-- create view eccs_email_template_id_sequence as select nextval('eccs_email_template_id_seq') as nextval; +-- +-- create table eccs_email_templates ( +-- email_template_id integer not null primary key, +-- title varchar(100), +-- subject varchar(200), +-- message varchar(4000), +-- -- this lists the variable names that customer service can +-- -- use in this particular email -- for their info only +-- variables varchar(1000), +-- -- for informational purposes only, when the email is +-- -- sent +-- when_sent varchar(1000), +-- -- for customer service issues, this is a tcl list of all +-- -- the issue_types that should be inserted into +-- -- eccs_cs_issue_type_map for the issue that will be created +-- -- when the message is sent +-- issue_type_list varchar(100), +-- last_modified timestamptz not null, +-- last_modifying_user integer not null references users, +-- modified_ip_address varchar(20) not null +-- ); +-- +-- create table eccs_email_templates_audit ( +-- email_template_id integer, +-- title varchar(100), +-- subject varchar(200), +-- message varchar(4000), +-- variables varchar(1000), +-- when_sent varchar(1000), +-- issue_type_list varchar(100), +-- last_modified timestamptz, +-- last_modifying_user integer, +-- modified_ip_address varchar(20), +-- delete_p boolean default 'f' +-- ); +-- +-- create function eccs_email_templates_audit_tr () +-- returns opaque as ' +-- begin +-- insert into eccs_email_templates_audit ( +-- email_template_id, title, +-- subject, message, +-- variables, when_sent, +-- issue_type_list, +-- last_modified, +-- last_modifying_user, modified_ip_address +-- ) values ( +-- old.email_template_id, old.title, +-- old.subject, old.message, +-- old.variables, old.when_sent, +-- old.issue_type_list, +-- old.last_modified, +-- old.last_modifying_user, old.modified_ip_address +-- ); +-- return new; +-- end;' language 'plpgsql'; +-- +-- create trigger eccs_email_templates_audit_tr +-- after update or delete on eccs_email_templates +-- for each row execute procedure eccs_email_templates_audit_tr (); +-- +-- -- 6 default templates are predefined ecommerce-defaults. +-- -- The templates are +-- -- used in procedures which send out the email, so the template_ids +-- -- shouldn t be changed, although the text can be edited at +-- -- [eccs_url_concat [eccs_url] /admin]/email-templates/ +-- -- +-- -- email_template_id used for +-- -- ----------------- --------- +-- -- 1 new order +-- -- 2 order shipped +-- -- 3 delayed credit denied +-- -- 4 new gift certificate order +-- -- 5 gift certificate recipient +-- -- 6 gift certificate order failure +-- +-- +-- -- users can sign up for mailing lists based on category, subcategory, +-- -- or subsubcategory (the appropriate level of categorization on which +-- -- to base mailing lists depends on how the site administrator has +-- -- set up their system) +-- -- when the user is signed up for a subsubcategory list, the subcategory_id +-- -- and category_id are also filled in (which makes it easier to refer +-- -- to the mailing list later). +-- -- cat stands for categorization +-- +-- -- this table needs to be integrated with contacts and categories package and maybe spam users package +-- +-- create table eccs_cat_mailing_lists ( +-- user_id integer not null references users, +-- category_id integer references eccs_categories, +-- subcategory_id integer references eccs_subcategories, +-- subsubcategory_id integer references eccs_subsubcategories +-- ); +-- +-- create index eccs_cat_mailing_list_idx on eccs_cat_mailing_lists(user_id); +-- create index eccs_cat_mailing_list_idx2 on eccs_cat_mailing_lists(category_id); +-- create index eccs_cat_mailing_list_idx3 on eccs_cat_mailing_lists(subcategory_id); +-- create index eccs_cat_mailing_list_idx4 on eccs_cat_mailing_lists(subsubcategory_id); +-- +-- +-- +-- create sequence eccs_spam_id_seq; +-- create view eccs_spam_id_sequence as select nextval('eccs_spam_id_seq') as nextval; +-- +-- -- this table needs to be integrated with contacts package +-- create table eccs_spam_log ( +-- spam_id integer not null primary key, +-- spam_date timestamptz, +-- spam_text varchar(4000), +-- -- the following are all criteria used in choosing the users to be spammed +-- mailing_list_category_id integer references eccs_categories, +-- mailing_list_subcategory_id integer references eccs_subcategories, +-- mailing_list_subsubcategory_id integer references eccs_subsubcategories, +-- user_class_id integer references eccs_user_classes, +-- product_id integer references eccs_products, +-- last_visit_start_date timestamptz, +-- last_visit_end_date timestamp +-- ); +-- +-- create index eccs_spam_log_by_cat_mail_idx on eccs_spam_log (mailing_list_category_id); +-- create index eccs_spam_log_by_cat_mail_idx2 on eccs_spam_log (mailing_list_subcategory_id); +-- create index eccs_spam_log_by_cat_mail_idx3 on eccs_spam_log (mailing_list_subsubcategory_id); +-- create index eccs_spam_log_by_user_cls_idx on eccs_spam_log (user_class_id); +-- create index eccs_spam_log_by_product_idx on eccs_spam_log (product_id); +-- +-- +-- +-- +-- Index: openacs-4/packages/customer-service/www/doc/index.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/customer-service/www/doc/index.adp,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/customer-service/www/doc/index.adp 3 Jul 2006 19:33:33 -0000 1.1 @@ -0,0 +1,143 @@ + + @title;noquote@ + @signatory;noquote@ + @context_bar;noquote@ + +

Introduction

+ +

Customer Service was originally designed as a submodule of ecommerce to provide call center support. +All interactions logged into same Database table by call center stall who sit at web browsers.

+

Features

+ +

Issues and Interactions

+ +

There are two main concepts underlying the customer service + submodule: customer service interactions and customer service + issues.

+ +

There is a many-to-many relationship between issues and + interactions. During the course of one interaction, a customer + may bring up any number of issues ("My credit card shows a charge + for $7.99, but I thought this ant farm was only supposed to cost + $6.99 AND, while I have you on the phone, I'd like to mention that + delivery took three days instead of the promised two."). + Furthermore, if an issue is not resolved the first time it is + brought up, it might span any number of interactions until it is + finally closed.

+ +

Issues can be categorized either for reporting purposes or so + that different departments of your company can handle the issues. + Open issues are linked to from the front page of the customer + service submodule to attract attention. Whenever email is sent to + the customer (either automatically or by a customer service rep), + an issue is created (or added to, if it is based on a previous + issue). This is so that a complete interaction history containing + all correspondence to and from the customer can be maintained. + All issues created due to automatic emails are closed immediately + so that they don't get in the way of other issues.

+ +

Small note: the intersection between an issue and an interaction + is called an "action" (i.e., the part of a specific interaction + that deals with a specific issue). This rarely comes up.

+ +

Registered Users and Unregistered Users

+ +

As a customer service rep, much of your interaction may be with + people who have used the site but are not registered users (people + don't become registered users unless they log in when they order, + when they submit product reviews, etc.), yet you still want to + capture the details of the interaction with as much identifying + information about them as you can.

+ +

Whenever you record a new interaction, you are asked to enter as + much information as you can gather (or feel comfortable gathering) + about the user. The system then tries to match this person up + with either registered users or unregistered people who have had + interacted previously with customer service. If no match can be + made, a new "user identification record" is created.

+ +

Each time you view a user identification record, the system sees + if it can match that person up with a registered user of the + system (in case they have registered in the meantime).

+ +

Sending Email to Customers

+ +

When you're viewing a customer service issue, you can send the + customer email regarding that issue by clicking "send email" at + the top of the page. The contents of your email will + automatically be added to the customer's interaction history and + will become part of the record for that customer service issue.

+ +

If you find yourself using the same phrases over and over again + when you respond to customers' emails, the Canned + Response System will be useful to you. You can enter your + commonly used phrases once, and then whenever you send email + you'll be able to automatically insert any number of these + phrases.

+ +

If you want to send email to customers in bulk, then use the Spam System. + You can spam users based on what products they've bought, what + products they've even looked at, by when they've last visited, by + how much they've spent at your site, by which mailing lists + they're signed up for. If you're spamming customers that you + like, you can issue them all gift certificates at the same time.

+ +

Your email text is also sent through a spell checker before it is + sent to the customer.

+ +

Picklist Management

+ +

When your customer service data entry people are recording + customer interactions, you want it to take as little effort as + possible. One way to help is to predefine picklists that they can + choose from when entering data. With the Picklist + Management tool, you can specify what goes in what picklist in + what order.

+ +

Reports

+ +

Reports and statistics are generated so that you can tell what + types of issues are occurring most frequently, which customer + service reps are handing the most interactions, what resources the + reps need to use most often, etc. Each report can be filtered and + sorted in a variety of ways to give you a clear picture of what's + happening and what can be done to improve efficiency.

+ Index: openacs-4/packages/customer-service/www/doc/index.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/customer-service/www/doc/index.tcl,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/customer-service/www/doc/index.tcl 3 Jul 2006 19:33:33 -0000 1.1 @@ -0,0 +1,37 @@ +ad_page_contract { + + The customer service module documentation. The customer service + module was part of the ecommerce package, a package to implement + business-to-consumer web services. + @ported to OpenACS 5 by Torben Brosten + @modification-date July 2006 + + @author Bart Teeuwisse (bart.teeuwisse@thecodemill.biz) + @creation-date May 2002 + +} { +} -properties { + title:onevalue + context_bar:onevalue +} + +# Authenticate the user + +set user_id [auth::require_login] + +# Check for read privileges + +set package_id [ad_conn package_id] +set admin_p [ad_permission_p $package_id read] + +set package_name "Customer Service" +set title "Documentation" +set package_url [apm_package_url_from_key "customer-service"] + +# Set the context bar. + +set context_bar [ad_context_bar [list "." "$package_name"] $title] + +# Set signatory for at the bottom of the page + +set signatory "kappa@dekka.com" Index: openacs-4/packages/online-catalog/online-catalog.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/online-catalog/online-catalog.info,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/online-catalog/online-catalog.info 3 Jul 2006 19:32:32 -0000 1.1 @@ -0,0 +1,27 @@ + + + + + Catalog + Catalogs + f + f + + + Torben Brosten + OpenACS community + Catalog provides ways to browse items by categories. + Dekka Corp of Oregon + Catalog package provides the browsing part of a traditional ecommerce service. Emphasis on presentation and search engine optimization. Shopping basket functions are separate. Includes ability to choose product(s) using a specifications configurator. + 0 + + + + + + + + + + + Index: openacs-4/packages/online-catalog/sql/postgresql/online-catalog-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/online-catalog/sql/postgresql/online-catalog-create.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/online-catalog/sql/postgresql/online-catalog-create.sql 3 Jul 2006 19:32:32 -0000 1.1 @@ -0,0 +1,107 @@ +-- online-catalog-create.sql +-- +-- @author Dekka Corp. +-- @license GNU GENERAL PUBLIC LICENSE, Version 2, June 1991 +-- @cvs-id +-- + +-- porting from ecommerce +-- product display templates +-- for more generic use + +-- create sequence ec_template_id_seq start 2; +create sequence ecca_template_id_seq start 2; + +-- create view ec_template_id_sequence as select nextval('ec_template_id_seq') as nextval; +create view ecca_ec_template_id_sequence as select nextval('ecca_ec_template_id_seq') as nextval; + + +-- I should have named this product_templates because now we +-- have other kinds of templates. + +create table ecca_ec_templates ( + template_id integer not null primary key, + template_name varchar(200), + template varchar(4000), + last_modified timestamptz not null, + last_modifying_user integer not null references users, + modified_ip_address varchar(20) not null +); + +create table ecca_ec_templates_audit ( + template_id integer, + template_name varchar(200), + template varchar(4000), + last_modified timestamptz, + last_modifying_user integer, + modified_ip_address varchar(20), + delete_p boolean default 'f' +); + +-- A trigger is used to move information from the main table to +-- the audit table +create function ecca_ec_templates_audit_tr () +returns opaque as ' +begin + insert into ecca_ec_templates_audit ( + template_id, template_name, + template, + last_modified, + last_modifying_user, modified_ip_address + ) values ( + old.template_id, + old.template_name, old.template, + old.last_modified, + old.last_modifying_user, old.modified_ip_address + ); + return new; +end;' language 'plpgsql'; + +create trigger ecca_ec_templates_audit_tr +after update or delete on ecca_ec_templates +for each row execute procedure ecca_ec_templates_audit_tr (); + + + +-- This inserts the default template into the ecca_ec_templates table +insert into ecca_ec_templates ( + template_id, template_name, template, + last_modified, last_modifying_user, modified_ip_address + ) values ( + 1,'Default', + '

<%= $product_name %>

' || '\n' || '\n' + || '' || '\n' + || '' || '\n' + || '' || '\n' + || '' || '\n' + || '' || '\n' + || '
' || '\n' + || ' ' || '\n' + || ' ' || '\n' + || ' ' || '\n' + || ' ' || '\n' + || ' ' || '\n' + || '
<%= [ecca_ec_linked_thumbnail_if_it_exists $dirname] %>' || '\n' + || ' <%= $one_line_description %>' || '\n' + || '
' || '\n' + || ' <%= [qar_ec_price_line $product_id $user_id $offer_code] %>' || '\n' + || '
' || '\n' + || '		' || '\n' || '<%= [qar_ec_add_to_cart_link $product_id] %>' || '\n' || '
' || '\n' || '\n' + || '

' || '\n' + || '<%= $detailed_description %>' || '\n' || '\n' + || ' <%= [qci_ec_product_link_if_exists $product_id] %>' || '\n' + || '
' || '\n' + || '<%= [qar_ec_display_product_purchase_combinations $product_id] %>' || '\n' || '\n' + || '<%= [qci_ec_product_links_if_they_exist $product_id] %>' || '\n' || '\n' + || '<%= [qci_ec_professional_reviews_if_they_exist $product_id] %>' || '\n' || '\n' + || '<%= [qar_ec_customer_comments $product_id $comments_sort_by] %>' || '\n' || '\n' + || '

' || '\n' || '\n' + || '<%= [qar_ec_mailing_list_link_for_a_product $product_id] %>' || '\n' || '\n', + now(), (select grantee_id + from acs_permissions + where object_id = acs__magic_object_id('security_context_root') + and privilege = 'admin' + limit 1), + 'none'); + +