Family Plan Membership

Introduction

This is an advanced tutorial.

A very popular request regarding CBSubs GPL is to have some type of family plan, where the family plan subscriber can provide similar benefits to one or more family members.

A similar concept can also be extended to a company plan that would allow the company plan subscriber to provide once again similar benefits to some company employees.

Such a feature has not been implemented yet in CBSubs GPL, mainly because its requirements are not well defined yet. This is the first part of a tutorial series (second part is here) that will present use case implementations of family plan concept requirements and how these can be implemented using CB Solution add-ons.

This first tutorial will describe a family plan use case and its implementation using CBSubs GPL, CB Conditionals, and CB Auto Actions.

Use Case Description

A family plan is defined as a CBSubs GPL plan (in our example it is set to a 1 year duration for a fixed fee) that provides specific website access privileges to the family plan subscriber. However, the family plan subscriber can also specify one family member (to keep our example simple) that can get similar access privileges as the family plan subscriber.

Let us assume that we actually have the following 3 CBSubs GPL plans:

Single member plan (yearly plan at 100 USD allowed to upgrade to Family plan paying the difference based on residual value)
Family Plan (yearly plan at 150 USD)
Family Member Plan (this is really a hidden plan that is tied to the Family plan with similar benefits as single member plan - can upgrade to family plan or single member plan but no residual value is taken into account)

A Family Plan subscriber can specify (a new or existing) user (but not themselves of course) that is to be given a free Family Member Plan subscription. The free Family Member Plan subscription is set to expire when the Family Plan subscription expires.

Once the Family Member plan is allocated (offered) by the Family Plan subscriber no changes can be made until the family plan expires and is then repurchased (thus causing the entire process to restart).

If the Family plan is renewed before expiration then the Family Member assigned subscriber is also automatically renewed once again expiring when the Family plan is set to expire.

Elements used in use case implementation

The following building block elements are used to implement this use case:

CB Tabs:
- Accept Family Plan tab
  This tab is allocated to the Family Plan subscriber using the CBSubs TabsFields Protection integration plugin and contains CB fields that will conditionally appear when needed to assist the subscriber to assign the Family Member plan.
- Family Member tab
  Once a Family Plan subscriber allocates a Family Member plan to a user this Family Member tab will appear (it is conditioned off the cb_fmemshowtab field that is set to 1 by the Register family member 1 auto action or the Assign family member 1 auto action
- Superadmin tab
  This tab is used to host various CB fields that are used to control the workflow states of the processes.
CB Fields:
- CB Fields on Superadmin tab:
  - cb_registerok1 (Read-only single checkbox field)
    This read-only checkbox field titled 'OK to register user 1' is populated by the Check proposal to register user 1 auto action if all checks have passed against the new user registration proposal (email and username uniqueness)
  - cb_afmstatus1 (Read-only text field)
    This read-only text field titled 'Family plan 1 status' is initially populated by the CBSubs CB Field integration plugin of the Family Plan. It is set to OPEN when subscription is activated and to DEACTIVATED when subscription ends.
  - cb_assignok1 (Read-only single checkbox field)
    This read-only checkbox field titled 'Ok to assign user 1' is populated with 1 (checked) by the Check assigned cb_unamelookup1 proposal auto action if the proposed username for Family Member plan assignment exists.
  - cb_fmemshowtab (Read-only single checkbox field)
    This read-only checkbox field titled 'Show Family member tab' is populated with 1 (checked) by either the Register family member 1 auto action or the Assign family member 1 auto action.
- CB Fields on Family Plan tab
  - cb_fmeminstructions1 (delimiter field containing instructions for family plan subscriber)
    This delimiter field titled ''Family member 1 instructions' contains detailed instructions to guide the family plan subscriber on how to allocate the free Family Member plan subscription.
  - cb_fplanexpdate (read-only date field to contain the expiration date of the family plan subscription)
    This read-only date field titled 'Family plan expiration date' is populated by a CBSubs SQL action triggered on the activation of the Family Plan and contains the expiration date of the family plan subscription. This will be used to populate the cb_afmexpdate field on the Accept Family Plan tab.
  - cb_fmemhistory1 (read-only text area field)
    This read-only text area field titled 'Family plan member 1 history' will contain time stamped history information about the workflow processes.
  - cb_fmemstatus1 (read-only text field)
    This read-only text field titeld 'Family member 1 status' contains the status of the Family Member allocation process (OPEN, ALLOCATED, DEACTIVATED, etc)
  - cb_mode1 (single select drop down field)
    The single select drop down field titled 'Mode to handle family member 1' is used to select the mode for the allocation of the Family Member plan user (CREATE to register new user or ASSIGN to use existing user).
    Depending on the selected mode other fields will conditionally show/hide if they are needed or not for the selected process.
  - cb_fname1 (text field)
    This text field titled 'First name 1' will appear if the cb_mode1 field is set to CREATE and it is used to populate the firstname of the new user to be created in order to get the free Family Member plan subscription.
  - cb_lname1 (text field)
    This text field titled 'Last name 1' is also conditioned on the CREATE mode selection of the cb_mode1 field and is used to collect the lastname of the to be created user.
  - cb_uname1 (text field)
    This text field titled 'User name 1' (also conditioned on CREATE mode) is used to get the suggested username of the to be created user. This value needs to be unique as it will be compared to existing usernames.
  - cb_pw1 (text field)
    This text field titled 'Family member 1 password' (conditioned) is used to get the password of the to be created new user.
  - cb_email1 (email field)
    This email field titled 'Email 1' (conditioned) is used to get the email address of the to be created new user. The email value must be unique.
  - cb_unamelookup1 (text field)
    This text field titled 'Assign to username 1' is conditioned on the ASSIGN value of the cb_mode1 field and is used to get the username of (an existing) user that is to be assigned to the free Family Member plan.
  - cb_statmes1 (text field)
    This read-only text field is used to display various status messages for the user workflow process.
  - cb_create1 (check-box field)
    This check-box field appears when the Family plan subscriber user has selected to create a new user (to get the Family member subscription) and has given correct credentials (email address and username not used). Once checked and saved the use case elements will proceed to create a new user and offer this user the Family member subscription option.
  - cb_assign1 (check-box field)
    This check-box field appears when the Family plan subscriber user has selected to assign the Family member plan to an existing site user (giving the username in cb_unamelookup1).
  - cb_asignmessageuser1 (delimiter field)
    This profile edit showing delimiter field titled 'Assign user 1 message' is conditionally shown when the cb_assign1 field is checked.
  - cb_registermessageuser1 (delimiter field)
    This profile edit showing delimiter field titled 'Register user 1 message' is conditionally shown when the cb_create1 field is checked.
  - cb_assignid1 (text field)
    This read-only text field titled 'Assign id 1' will contain the user id of the user that was offered/given a Family member subscription by this Family Plan subscriber.
- CB Fields on Accept Family Member tab
  - cb_afmexpdate (date field)
    This read-only date field titled 'Expiration date' contains the expiration date of the family plan subscription
  - cb_afmaccept1 (check-box)
    This check-box field titled 'Accept family plan' is used to give the user the option to accept the family member subscription. No matter when the subscription is accepted and starts the end date is forced to the expiration date of the Family plan (cb_afmexpdate).
  - cb_afmactive (deliiter field)
    This delimiter field titled 'Family plan granted' will display information regarding the family plan that was used to grant this Family member subscription.
  - cb_afmparent1 (text field)
    This read-only text field titled 'Family plan 1 owner id' contains the user id of the family plan subscriber responsible for offering this family member subscription.
CBSubs GPL plans:
- Family Plan with following characteristics:
  - One year duration at 150 USD
  - Workflow tab --> Allow Registration: Set to 'Yes'
  - Workflow tab --> Propose spontaneously plan at registration: Set to 'Yes, show automatically plan for normal registrations (recommended)'
  - Integrations tab --> CB Fields:
    - CB field (cb_fmemstatus1) 'Family member 1 status' set to 'OPEN' and Remove value on plan deactivation set to 'No' (this will be handled with SQL action on plan)
    - CB field (cb_mode1) 'Mode to handle family member 1' set to null
    - CB field (cb_create1) 'Create user 1' set to 0
    - CB field (cb_registerok1) 'Ok to register user 1' set to 0
    - CB field (cb_assignok1) 'Ok to assign user 1' set to 0
  - Integrations tab --> SQL Actions:
    - SQL Action 1: Activation:
      (gets expiration date from cbsubs table and saves it in cb_flanexpdate of family plan purchaser)
      (updates cb_fmemhistory1 field with timestamp and OPEN status)
      
      UPDATE #__comprofiler SET cb_fplanexpdate = (SELECT expiry_date FROM #__cbsubs_subscriptions WHERE (status='A' AND user_id = '0' AND plan_id = '1'));
      UPDATE #__comprofiler SET cb_fmemhistory1 = CONCAT(NOW(),' : OPEN') WHERE (user_id = '0');
    - SQL Action 1: Renewal:
      (set expiration date of non expired Family Member plan to be same as new expiration date of Family Plan)
      
      UPDATE #__cbsubs_subscriptions SET expiry_date = '[cb_fplanexpdate]' WHERE (status='A' AND plan_id='2' and user_id = '[cb_assignid1]');
    - SQL Action 1: Deactivation:
      (Set expiration date of Family Member plan to be one day after today)
      (Set cb_fmemstatus1 field to 'DEACTIVATED')
      
      UPDATE #__cbsubs_subscriptions SET expiry_date = date_add(now(), interval 1 day) WHERE (status='A' AND plan_id='2' and user_id = '[cb_assignid1]');
      UPDATE #__comprofiler SET cb_fmemstatus1 = 'DEACTIVATED' WHERE user_id = '0';
- Single User Plan with following characteristics:
  - One year duration at 100 USD
  - Workflow tab --> Allow Registration: Set to 'Yes'
  - Workflow tab --> Propose spontaneously plan at registration: Set to 'Yes, show automatically plan for normal registrations (recommended)'
- Family Member Plan with following characteristics:
  - One year duration at 100 USD (doesn't really matter as this plan will never be proposed directly)
  - Pricing tab --> Prorate remaining value of this plan when upgrading: Set to 'No'
  - Pricing tab --> Renewable in advance: Set to 'Non-renewable'
  - Pricing tab --> Grace period before real expiration: Set to 'none'
  - Workflow tab --> Allow Registration: Set to 'No'
  - Workflow tab --> Allow upgrade to this: Set to 'No'
  - Access tab --> Allow access from site frontend: Set to 'No, the plan is not visible in site, only in admin backend'
  - Integrations tab --> CB Fields:
    - CB field (cb_afmstatus1) 'Family plan 1 status' set to 'Active'
  - Integrations tab --> SQL Actions:
    - SQL Action 1: Activation:
      (set cb_assignid1 field value on the family plan purchaser profile to the user id of the family member user)
      (set the expiration date of the family member plan to be the same as the expiration date of the family plan based on the cb_afmexpdate field)
      (set the cb_fmemstatus1 field to 'ACTIVATED' on the family plan user profile)
      (update the cb_fmemhistory1 field on the family plan user profile)
      
      UPDATE #__comprofiler SET cb_assignid1 = '0' WHERE user_id = '[cb_afmparent1]';
      UPDATE #__cbsubs_subscriptions SET expiry_date = '[cb_afmexpdate]' WHERE (status='A' AND user_id = '0' AND plan_id = '2');
      UPDATE #__comprofiler SET cb_fmemstatus1 = 'ACTIVATED' WHERE user_id = '[cb_afmparent1]';
      UPDATE #__comprofiler SET cb_fmemhistory1 = CONCAT(NOW(),' : ACCEPTED by userid = 0 / username =
      ', cb_fmemhistory1) WHERE user_id = '[cb_afmparent1]';
    - SQL Action 1: Expiration:
      (turn off the cb_afmaccept1 and cb_fmemshowtab fields on the family member profile)
      
      UPDATE #__comprofiler SET cb_afmaccept1 = '0', cb_fmemshowtab = '0' WHERE user_id = '0';
    - SQL Action 1: Deactivation:
      (turn off the cb_afmaccept1 and cb_fmemshowtab fields on the family member profile)
      
      UPDATE #__comprofiler SET cb_afmaccept1 = '0', cb_fmemshowtab = '0' WHERE user_id = '0';
CB Auto Actions:
- Auto Action 1: Check proposal to register user 1 with following characteristics:
  - Type: Query
  - Triggers: onAfterUserUpdate
  - User: Automatic
  - Access: Everybody
  - Conditions:
    - [cb_fmemstatus1] Equal To OPEN
    - [cb_mode1] Equal To CREATE
    - [cb_uname1] Not Empty
    - [cb_email1] Not Empty
  - Query:
    
    UPDATE #__comprofiler SET cb_statmes1 = IF( 0 < ( SELECT COUNT(*) FROM #__users WHERE username = '[cb_uname1]' OR email = '[cb_email1]' ), 'Username [cb_uname1] or email [cb_email1] in use - you must select different ones', 'Username [cb_uname1] - email [cb_email1] combination is OK') WHERE user_id = '0';
    UPDATE #__comprofiler SET cb_registerok1 = IF( 0 < ( SELECT COUNT(*) FROM #__users WHERE username = '[cb_uname1]' OR email = '[cb_email1]' ), '0', '1') WHERE user_id = '0';
- Auto Action 2: Check assigned cb_unamelookup1 proposal with following characteristics:
  - Type: Query
  - Triggers: onAfterUserUpdate
  - User: Automatic
  - Access: Everybody
  - Conditions:
    - [cb_fmemstatus1] Equal To OPEN
    - [cb_mode1] Equal To ASSIGN
    - [cb_unamelookup1] Not Empty
    - [cb_unamelookup1] Not Equal To
  - Query:
    
    UPDATE #__comprofiler SET cb_statmes1 = IF( 0 != ( SELECT COUNT(*) FROM #__users WHERE username = '[cb_unamelookup1]' ), 'Username [cb_unamelookup1] found - you can continue', 'Username [cb_unamelookup1] does not exist - you need to enter a valid username') WHERE user_id = '0';
    UPDATE #__comprofiler SET cb_assignok1 = ( SELECT COUNT(*) FROM #__users WHERE username = '[cb_unamelookup1]' ) WHERE user_id = '0';
    UPDATE #__comprofiler SET cb_assignid1 = (SELECT id FROM #__users WHERE username = '[cb_unamelookup1]');
- Auto Action 3: Register family member 1 with following characteristics:
  - Type: Registration
  - Triggers: onAfterUserUpdate
  - User: Automatic
  - Access: Everybody
  - Conditions:
    - [cb_fmemstatus1] Equal To OPEN
    - [cb_mode1] Equal To CREATE
    - [cb_create1] Equal To 1
    - [var1_cb_create1] Not Equal To[var3_cb_create1]
    - [cb_registerok1] Equal To 1
  - Approval: No (override CB)
  - Confirmation: No (override CB)
  - Usergroup: Default (CMS)
  - Username: [cb_uname1]
  - Password: [cb_pw1]
  - Email Address: [cb_email1]
  - First Name: [cb_fname1]
  - Last Name: [cb_lname1]
  - Fields:
    
    cb_afmstatus1=SUBSCRIBE
    cb_afmparent1=0
    cb_afmexpdate=[cb_fplanexpdate]
    cb_fmemshowtab=1
  - Suppress Emails: Disable
- Auto Action 4: Update family profile cb_fmemstatus1 to ALLOCATED when new family member is created with following characteristics:
  - Type: Query Type
  - Triggers: onAfterUserUpdate
  - User: Automatic
  - Access: Everybody
  - Conditions:
    - [cb_afmstatus1] Equal To SUBSCRIBE
    - [cb_afmparent1] Not Empty
  - Query:
    
    UPDATE #__comprofiler SET cb_fmemstatus1 = 'ALLOCATED' WHERE (user_id = [cb_afmparent1] AND cb_fmemstatus1 = 'OPEN');
    UPDATE #__comprofiler SET cb_fmemhistory1 = CONCAT(NOW(),' : ALLOCATED to userid = 0 / username =
    ', cb_fmemhistory1) WHERE user_id = [cb_afmparent1];
- Auto Action 5: Subscribe family member 1 with following characteristics:
  - Type: CB Paid Subscriptions 3.X
  - Triggers: onAfterUserUpdate
  - User: Automatic
  - Access: Everybody
  - Conditions:
    - [cb_afmstatus1] Equal To SUBSCRIBE
    - [cb_afmaccept1] Equal To 1
  - Plans: FMEM (Alias of Family Member plan)
  - Mode: Subscribe
- Auto Action 6: Assign family member 1 with following characteristics:
  - Type: Query
  - Triggers: onAfterUserUpdate
  - User: Automatic
  - Access: Everybody
  - Conditions:
    - [cb_fmemstatus1] Equal To OPEN
    - [cb_mode1] Equal To ASSIGN
    - [cb_assign1] Equal To 1
    - [cb_assignok1] Equal To 1
    - [cb_assignid1] Not Empty
  - Query:
    
    UPDATE #__comprofiler SET cb_afmstatus1 = 'SUBSCRIBE', cb_afmparent1 = '0', cb_afmexpdate = '[cb_fplanexpdate]', cb_fmemshowtab = '1' WHERE user_id = '[cb_assignid1]';
    UPDATE #__comprofiler SET cb_fmemstatus1 = 'ALLOCATED' WHERE user_id = 0;
    UPDATE #__comprofiler SET cb_fmemhistory1 = CONCAT(NOW(),' : ALLOCATED to userid = 0 / username = [cb_unamelookup1]
    ', cb_fmemhistory1) WHERE user_id = 0;

Screenshots

In addition to the detailed configuration information provided earlier, here are a series of screenshots of the various configuration areas.

CB Fields on Family Plan tab

CB Field Manager view with fields of Family Plan tab.

CB Fields on Accept Family Plan tab

CB Field Manager view with fields of Accept Family Plan tab.

CB Fields on Superadmin tab

CB Field Manager view with fields of Superadmin tab.

CBSubs GPL Plans Overview

CBSubs Plans icon area.

CBSubs Family Plan Fields Integration

CBSubs Family Plan fields integration settings.

CBSubs Family Plan SQL Actions Integration

CBSubs Family Plan SQL Actions Integration.

CBSubs Family Member Plan Fields Integration

CBSubs Family Member Plan Fields Integration Settings.

CBSubs Family Member Plan SQL Actions Integration

CBSubs Family Member Plan SQL Actions Integration Settings.

CB Auto Actions Overview

CB Auto Actions Overview page.

CB Auto Action 1 Basic Settings

CB Auto Action 1 Basic Settings.

CB Auto Action 1 Advanced Settings

CB Auto Action Advanced Settings for Check proposal to register user 1.

CB Auto Action 2 Basic Settings

CB Auto Action 2 Basic Settings.

CB Auto Action 2 Advanced Settings

CB Auto Action 2 Advanced Settings.

CB Auto Action 3 Basic Settings

CB Auto Action 3 Basic Settings.

CB Auto Action 3 Advanced Settings

CB Auto Action 3 Advanced Settings.

CB Auto Action 4 Basic Settings

CB Auto Action 4 Basic Settings.

CB Auto Action 4 Advanced Settings

CB Auto Action 3 Basic Settings.

CB Auto Action 5 Basic Settings

CB Auto Action 5 Basic Settings.

CB Auto Action 5 Advanced Settings

CB Auto Action 5 Advanced Settings.

CB Auto Action 6 Basic Settings

CB Auto Action 6 Basic Settings.

CB Auto Action 6 Advanced Settings

CB Auto Action 6 Advanced Settings.

Workflow Walk-through

A typical walk-through of the use case workflow follows:

User purchases a Family Plan subscription and is automatically given an extra Family Plan tab that allows him/her to either assign an existing user (giving the username) or to create a new user (giving the First Name, Last Name, Email, Password, Username). For clarity lets assume that the user that purchased the Family Plan has a username of family. Notice that the Family Plan tab has the 'Family plan expiration date' set (by XXX), the 'Family member 1 status' set to 'OPEN' (by XXX) and the 'Family plan member 1 history' populated with initial activity (by XXX).
User with username=family edits his/her profile and selects the CREATE option in the 'Mode to handle family member 1' drop-down field and then populates the various fields needed to create a new user.
After user family saves profile, if data provided validates ok (the provided email and username are not used), then an OK to proceed message is shown in the 'Status Message 1' field and the 'Create user 1' check-box field will appear (initially unchecked with value No)
User family can then edit his/her profile and click on the 'Create user 1' check box and then save the profile (clicking on the Update button). This will trigger the execution of CB Auto Action 3 (Register family member 1) and also after this the execution of the CB Auto Action 4 (which listens to the onAfterUserRegistration event).
The family user profile is now updated showing additional information in the 'Family plan member 1 history' field and also showing the 'Family member 1 status' set to ALLOCATED.
The new user with username 'member' has been created and has been configured to show the 'Accept Family Plan' tab which contains the 'Expiration date' field set to the expiration date of the family plan subscription, the 'Accept family plan' check-box field unchecked, the 'Family plan granted' field displaying information about which user offered this family member plan and the 'Family plan ownder id' field containing the userid of the family plan member purchaser. At this moment the member user profile has no CBSubs subscription activated.
User member can edit his/her profile and click on the 'Accept family plan' check-box and save the profile.
User member now has an active 'Family member plan' subscription that is set to expire on the exact same moment that the Family Plan subscription expires.
The family profile has an updated 'Family Plan' tab that has 'Family member 1 status' set to ACTIVATED and also an updated 'Family plan member 1 history' field.
At this point the famiy member plan is set to expire at the same time the Family Plan expires. If in the meantime the Family plan user (family) renews early his/her Family plan subscription, then the expiration date of the already assigned family member plan subscription will also be automatically updated (see SQL renewal action of Family Plan). If the family plan expires and a new family plan subscription is activate the workflow is brought back to the initial status.

Worflow Walk-through screenshots

And here are the corresponding screenshots that follow the previously detailed walk-through:

Workflow walk-through step 1

Workflow walk-through step 1.

Workflow walk-through step 2

Workflow walk-through step 2.

Workflow walk-through step 3

Workflow walk-through step 3.

Workflow walk-through step 4

Workflow walk-through step 4.

Workflow walk-through step 5

Workflow walk-through step 5.

Workflow walk-through step 6

Workflow walk-through step 6.

Workflow walk-through step 7

Workflow walk-through step 7.

Workflow walk-through step 8

Workflow walk-through step 8.

Workflow walk-through step 9

Workflow walk-through step 9.

Conclusion

This advanced tutorial has covered many different aspects of CBSubs and CB Auto Actions. The use case can be extended and modified to satisfy different requirements.

The goal is to reach a set a requirements for the family membership request in order to be able to generate the specifications for this new feature.

This tutorial is based on CB 1.9.1 and CBSubs 3.0.0. Please note that database structures are not part of CB / CBSubs specifications and that subsequent CB / CBSubs versions can have different database schemes. When direct accessing databases, it is recommended to have a staging site where upgrades are tested before being applied to live sites.

Lets discuss this on our forums here.

Latest News