CiviContribute Premiums - Schema / Administer Premiums / Display and Select Premiums for Online Contributions

CiviContribute Premiums
======================
This functionality allows organizations to offer a selection of Premiums (incentive products) to their on-line contributors. Premium products can linked to one or more Contribution Pages via an options Premiums section- which may include optional introductory text and which can be enabled or disabled at the contribution page level. This issue covers schema for premiums, administrative functions and premiums in the online contributions process. Assigning premiums for offline contributions, updating premium selection and status, and export are covered in a separate issue.

=======
Schema
=======
civicrm_premiums
(new table - settings for the Premiums features for a given contribution page)
id INT NOT NULL ,
entity_table VARCHAR (64) NOT NULL COMMENT 'Joins these premium settings to another object. Always civicrm_contribution_page for now.',
entity_id INT NOT NULL,
premiums_active tinyint NOT NULL DEFAULT 0 COMMENT 'Is the Premiums feature enabled for this page?',
premiums_intro_title VARCHAR (255) NULL COMMENT 'Title for Premiums section.',
premiums_intro_text text COMMENT 'Displayed in <div> at top of Premiums section of page. Text and html allowed.',
premiums_contact_email VARCHAR (100) COMMENT 'This email address is included in receipts if it is populated and a premium has been selected.' ,
premiums_contact_phone VARCHAR (50) 'This phone number is included in receipts if it is populated and a premium has been selected.',
premiums_display_min_contribution tinyint NOT NULL COMMENT 'Boolean. Should we automatically display minimum contribution amount text after the premium descriptions.'

civicrm_product
(new table - stores "product info" for premiums and can be used for non-incentive products)
id INT NOT NULL ,
name VARCHAR (255) NOT NULL COMMENT ' Required product/premium name',
description TEXT NULL COMMENT 'Optional description of the product/premium.',
sku VARCHAR (50) NULL COMMENT 'Optional product sku or code.',
options TEXT NULL COMMENT 'Store comma-delimited list of color, size, etc. options for the product.'
image VARCHAR (255) COMMENT 'Full or relative URL to uploaded image - fullsize.',
thumbnail VARCHAR (255) COMMENT 'Full or relative URL to image thumbnail.',
price MONEY COMMENT 'Sell price / market value for premiums. For tax-deductible contributions, this will be stored as non_deductible_amount in the contribution record.',
min_contribution MONEY NULL COMMENT 'Minimum contribution required to be eligible to select this premium.',
cost MONEY NULL COMMENT 'Actual cost of this product. Useful to determine net return from sale or using this as an incentive.',
is_active tinyint NOT NULL COMMENT 'Disabling premium removes it from the premiums_premium join table below.'

// these properties are intended to support time-delimited products / premiums (subscriptions, services, memberships)
period_type ENUM; 'rolling', 'fixed' NULL DEFAULT 'rolling',
COMMENT 'Rolling means we set start/end based on current day, fixed means we set start/end for current year or month
(e.g. 1 year + fixed -> we would set start/end for 1/1/06 thru 12/31/06 for any premium chosen in 2006)
fixed_period_start_day INT NULL DEFAULT 0101 COMMENT 'Month and day (MMDD) that fixed period type subscription or membership starts.',
duration_unit ENUM; 'day, week, month, year' NULL DEFAULT 'year'
duration_interval INT NULL COMMENT 'Number of units for total duration of subscription, service, membership (e.g. 12 Months).'
frequency_unit ENUM; 'day, week, month, year' NULL DEFAULT 'month' COMMENT 'Frequency unit and interval allow option to store actual delivery frequency for a subscription or service.',
frequency_interval INT NULL COMMENT 'Number of units for delivery frequency of subscription, service, membership (e.g. every 3 Months).'

civicrm_premiums_product
(new table - joins premiums (settings) to individual product/premium items - determines which products are available for a given contribution page)
id INT NOT NULL,
premiums_id INT NOT NULL COMMENT 'Foreign key to premiums settings record.',
product_id INT NOT NULL COMMENT 'Foreign key to each product object.',
sort_position INT NOT NULL

civicrm_contribution_product (new table - joins premiums selected by contributors to their contribution record)
id INT NOT NULL ,
contribution_id INT NOT NULL ,
product_id INT NOT NULL ,
product_option VARCHAR (255) NULL COMMENT 'Option value selected if applicable - e.g. color, size etc.',
quantity INT NOT NULL DEFAULT 1,
fulfilled_date DATE NULL COMMENT 'Optional. Can be used to record the date this product was fulfilled or shipped.',
start_date DATE NULL COMMENT 'Actual start date for a time-delimited premium (subscription, service or membership)',
end_date DATE NULL COMMENT 'Actual end date for a time-delimited premium (subscription, service or membership)'
comment TEXT NULL

==========================
Administer Premiums
==========================
Because organizations may want to offer premiums through Online Contribution Pages AND other channels (e.g. phone-in contributions...) - AND because Premiums may be used for several different campaigns/online contribution pages...

• Users will 'Manage Premiums' from a top-level admin menu (add/edit premium records).
• Users will select existing premium(s) for inclusion in a given Online Contribution Page.

1. New top-level civicrm/admin menu item = 'Manage Premiums' . This will be the 2nd item in the CiviContribute menu group.
1.1 Manage Premiums page
Selector lists all existing premiums. Columns are:
Name, SKU, Market Value, Min Contribution, Status
Actions are:
Edit, Preview, Disable, Delete

If none, display msg:
"No premium products have been created for your site. You can <a href="$addPremiumURL">add one now</a>.

Below selector, link to >> New Premium

1.2 Disable Premium
This action sets civicrm_product.is_active to false AND delete any related civicrm_premiums_product join table records. Disable onclick warning:
"Are you sure you want to disable this premium. This action will remove the premium from any contribution pages that currently include it. However it will not delete the premium record - so you can enable it and re-include it in your contribution pages at a later time."

1.3 Delete Premium
This action deletes the civicrm_product record and related civicrm_premiums_product join table records. Disable onclick warning:
"Are you sure you want to delete this premium. This action cannot be undone. This will also remove the premium from any contribution pages that currently include it.

1.4 Preview Premium
Previews the premium display as it would be embedded in a contribution page - including image if any, name and description (see below for layout info).

2. Add/Edit Premium Form
2.1 Standard form includes all primary properties from civicrm_premium class/table. Field labels are:
Name, SKU, Description, Image, Market Value, Minimum Contribution Amount, Options, Status
2.1.1 These fields are all required except Description and Options (both are textarea fields : rows=6 cols=50). Description may contain HTML tags, Options must be a comma-separated list of values.
2.1.2 In 'Add' mode, "Image" is a vertically listed radio button group with 3 states. The first option also includes an upload file field:
[ ] Get image from my computer [ ] [Browse]
[ ] Display image and thumbnail from these locations:
[ ] Use default image
[ ] Do not display an image

If "Display image and thumbnail from these locations" is selected - display two fields to enter URL for image and thumbnail.
(We'll require complete URL's - so validate for them. Both are required for this radio option although they can be the same value.)

2.1.3 In 'Edit' mode, add one more option to radio button group:
[ ] Use current image
This option is checked if image column has a value other than 'default_image.gif'

In Edit mode we'll also display the thumbnail for currently selected image (if any) to the left of the Image field.

2.2 Form includes second fieldset for use with time-delimited products. This fieldset is in a hidden div for add - with link to show it
" >> For Subscriptions, Memberships, Services"
This section includes all the time-delimited properties listed in the schema above. They are all optional. However period_type is required
if any of the others are filled in. Also _unit and _interval pairs are required conditionally (if one is set, the other is required).

2.3 Save on this form conditionally validates the uploaded image file (see next) and inserts/updates civicrm_product record. We'll store
complete URLs to image and thumbnail files for both uploaded and 'display from ...location' cases.

2.4 Image handling - For uploaded images...

• ISSUE: As far as I can tell the existing CIVICRM_UPLOADDIR is for temporary uploaded files and is cleared by our directoryCleanup function. If this is so, then we can use that to pre-process uploaded files and then move them to 'permanent' storage. We will then need a new constant in civicrm.settings.php for this location -> CIVICRM_FILEDIR. By default we could make this just drupal/files or perhaps drupal/files/civicrm/file (see if Lobo has a preference here).
• Use PHP's getimagesize() to verify that the file is a valid picture and to check the dimensions. We will accept files with dimensions between 80 x 80 and 500 x 500. Otherwise we return a form validation error.
• For images larger than 100 x 100, we'll want to show a 100 x 100 thumbnail on the main page, linked to a pop-up with the full-size image. The php manual includes scripts for both displaying and saving thumbnails using imagecopyresampled() and imagecopyresized() functions. We create the thumbnail on upload w/ a set naming pattern relative to the original. (e.g. myimage.jpg -> myimage_thumb.jpg) and save that new file along with the original full-size file to our CIVICRM_FILEDIR

3. Configure Premiums for an Online Contribution Page:
3.1 Add link 'Configure Premiums' to the existing Configure Contribution Page (civicrm/admin/contribute?reset=1&action=update&id=1) - in a new table row below 'Custom Page Elements'

3.2 Configure Premiums screen has two sections. A Form for setting/updating premium-related contribution page settings, and a Selector displaying existing premiums linked to this contribution page.
3.2.1 Form section - add/update the following properties. Note that these are stored in a civicrm_premiums record which is inserted (first time) or updated - and is linked to the selected civicrm_contribution_page via it's entity_table/entity_id columns:
Premiums Section Enabled? (premiums_active) - default = 0
Title (premiums_intro_title)
Introduction (premiums_intro_text) - textarea rows=6 cols=50
Contact Email (premiums_contact_email)
Contact Phone (premiums_contact_phone)

3.2.2 Selector section - lists premiums joined to this contribution page via civicrm_premiums_product table.

If no premiums are linked to this contribution page, AND there are one or more active civicrm_product records, display msg:
"There are no premiums linked to this contribution page yet. You can <a href="$addPremiumToContributionPageURL">add one now</a>.
If no premiums are linked to this page, AND there are NO active civicrm_premium records, display msg:
"There are no active premiums for your site. You can <a href="$managePremiumsURL">create and/or enable premiums here</a>.

If there are premiums - show the Selector. Columns are:
Name, SKU, Market Value, Min Contribution, Weight
Actions are:
Edit, Preview, Remove

3.3 Add/Edit Premium in Contribution Page
Simple form allows user to select from active civicrm_product records which are NOT already linked to this page and set Weight in page relative to other premiums.
Include link on this page to ">> Edit this Premium" (links to form 2. above)

3.4 Preview Premium
(same as 1.4 above)

=================================
Premiums and Online Contribution Wizard
=================================
1. Wizard - Step 1 (Main.php). In this step, we show the contributor what premiums are available (they'll select a premium in the Confirm step).
A Premiums display section is inserted in this page if the linked civicrm_premiums.is_active value is TRUE.
This section is displayed in it's own <div id="premiums">.
The section begins with <h2>$civicrm_premiums.title</h2> (if not NULL)
Followed by <div id=premiums-intro>$intro</div> (if not NULL)
Folllowed by a <div> for each civicrm_product record linked to this page. I'll format these one you've got it roughed in - just expose all the properties for now.

2. Step 2 (Confirm.php). In this step we redisplay all premiums where minimum contribution amount is <= the contribution total amount. In this case - the products form a radio button group (one radio button for each product) - and we add a [ ] No thank you radio button at the bottom of the group (so they can unselect). If civicrm_product.options is NOT NULL - convert comma-separated list into a <select> box displayed next to the product name.

3. Step 3 (Thankyou.php). If contribution is successful:
3.1 When inserting the contribution record - if contribution type is_deductible, then write the price (market value) of the selected premium as the contribution.non-deductible_amount

3.2 Insert record in civicrm_contribution_product - including option value is used. For time-delimited products - calculate start and end dates based on the period_type and duration properties.

3.3 Include the following premium and product properties in the email receipt (don't worry about formatting - just add them at the bottom for now):

• product name and option (if applicable)
• price (market value)
• start and end dates (if applicable)
• premiums.contact_email and / or phone (if not null)

------------------
NOTE: There are some complex IRS rules/calculations regarding determination of when the deductible amount of a contribution should be reduced by the market-value of a premium or benefit received. However, best practice for charitable contributions seems to be "The rule here is: Your contribution is deductible to the extent it exceeds the fair market value of the benefits or privileges you receive."
http://www.onphilanthropy.com/bestpract/bp2004-06-11.html

The last IRS reference to the exact rules I found is dated 1990 - so let's we'll include the full market value on the receipt with a disclaimer to "consult with your tax advisor". http://www.unclefed.com/Tax-News/1990/Nr90-20.html