Dynamic Pricing

[SideonDeaka]

@serveit-ca Creating this issue to assign trello tasks, it can be updated tonight / tomorrow.

[SideonDeaka]

Pricing Development - Database Creation

[SideonDeaka]

@serveit-ca Schema Proposal:

New Table cura_corp_prices id PK int(11) corp_id FK int(11) price_tier FK int(11)

New Table cura_corp_tiers id PK int(11) min_users int (5) max users int (5) price_per_user float (5) is_default int (1)

[serveit-ca]

Scheme is approved.

We will also want to create a function to make sure we check for now overlap in the tiers.

[SideonDeaka]

Functions to be made:

newPricingTier($minUser, $maxUser, $pricePer)

newDefaultPricingTier($minUser, $maxUser, $pricePer)

updatePricingTier($minUser, $maxUser, $pricePer, $tierId)

checkTierUserLimits($tierId) - returns min and max for row.

checkValidTierUpdates($previousMax, $newMin, $newMax, $nextMin, $tierId) - validates that the tiers minimum users are higher than the previous tiers max, and the new max is lower than the next tiers min. Also check for "white space" new min - previous max = 1, next min - new max = 1.

getCurrentPricePerUser($tierId)

getCurrentPricePerUserByCorp($corpId)

getTotalSubscriptionPrice($corpId)

getNumberOfCorpSubAccounts($corpId)

[serveit-ca]

As i see it now those function look good.

[SideonDeaka]

Pricing Development - Function Creation

[SideonDeaka]

CREATE TABLE dev_cura_corp_prices( id int(11) AUTO_INCREMENT, corp_id int(11) NOT NULL, tier_id int(11) NOT NULL, PRIMARY KEY (id));

CREATE TABLE dev_cura_corp_tiers( id int(11) AUTO_INCREMENT, min_users int(5) NOT NULL, max_users int(5) NOT NULL, price_per_user numeric(6,2), is_default int(1), PRIMARY KEY (id));

[SideonDeaka]

Pricing Development - Travis Testing

[SideonDeaka]

Pricing Development - Front End Creation

[serveit-ca]

Ran the following code on production to get the database up to sync.

CREATE TABLE dev_cura_corps( id int(11) AUTO_INCREMENT, mempr_id int(11), name varchar(55) NOT NULL, PRIMARY KEY (id));

CREATE TABLE dev_cura_corp_groups( id int(11) AUTO_INCREMENT, group_id int(11) NOT NULL, corp_id int(11) NOT NULL, PRIMARY KEY(id));

CREATE TABLE dev_cura_groups( id int(11) AUTO_INCREMENT, name varchar(55) NOT NULL, type int(1) NOT NULL, PRIMARY KEY(id));

CREATE TABLE dev_cura_group_users( id int(11) AUTO_INCREMENT, user_id int(11) NOT NULL, group_id int(11) NOT NULL, privilege_level int(1) NOT NULL, PRIMARY KEY(id));

CREATE TABLE dev_cura_group_programs( id int(11) AUTO_INCREMENT, group_id int(11) NOT NULL, program_id int(11) NOT NULL, PRIMARY KEY(id));

CREATE TABLE dev_cura_corp_prices( id int(11) AUTO_INCREMENT, corp_id int(11) NOT NULL, tier_id int(11) NOT NULL, PRIMARY KEY (id));

CREATE TABLE dev_cura_corp_tiers( id int(11) AUTO_INCREMENT, min_users int(5) NOT NULL, max_users int(5) NOT NULL, price_per_user numeric(6,2), is_default int(1), PRIMARY KEY (id));

ALTER TABLE dev_cura_user_programs ADD group_id int(11);