Commissions
To write a sales commission system which expands the current minimal commission functionality. The system will allow for multiple breakpoints with varying rates, to allow for multiple commission records by stock category, area, length of time being a customer, and currency. It will automatically create sales commission accruals, provide reports of sales commissions outstanding, and statements for each sales person, finally creating an invoice to the sales person. The system should be easily extendible to allow different methods of commission calculation to be added.
- SalesPeople.php
- CompanyPreferences.php
- Suppliers.php
- ConfirmDispatch_Invoice.php
- Credit_invoice.php
- sql/updates/196.php
- SalesCommissionRates.php
- SalesCommissionReports.php
- SalesCommissionStatements.php
- SalesCommissionTypes.php
- includes/CommissionFunctions.php
Amend the file to create an extra field for a GL code to store the sales
commission accruals in.
Amend the file to add an extra field to hold the sales person code, if and
only if this is an account referring to a sales person.
Populates the salescommissiontypes table which holds the names of all the
commission calculation algorithms that have been set up.
Remove the commissionrate1, commissionrate2, and the breakpoint fields from
the salesman table.
Add in a field to store the general ledger expense code where the commission
for this sales person will be posted.
Add in a field to define the sales period which this person receives
commission on. The choices are Monthly/Quarterly/Annually though adding
other periods would not be hard.
Add in a field for the commission calculation method for this sales person. The
choices are those created in the SalesCommissionTypes.php script.
New script to populate the table storing the commission rates and breakpoints
for each salesperson/currency/stockcategory/area/daysactive combination. The table is created
as:
CREATE TABLE salescommissionrates (
salespersoncode` varchar(4) NOT NULL DEFAULT '',` categoryid char(6) NOT NULL DEFAULT '',
area` char(3) NOT NULL DEFAULT '',` startfrom double NOT NULL DEFAULT '0',
daysactive` int(11) NOT NULL DEFAULT '0',` rate double NOT NULL DEFAULT '0',
``currency char(3) NOT NULL DEFAULT '',
`PRIMARY KEY (`salespersoncode`,`categoryid`,`startfrom`),`
`KEY `salespersoncode` (`salespersoncode`)`
`)`
The CalculateCommission() function will be in the includes/CommissionFunctions.php script so that it is accessible to both the invoicing and the credit note scripts. NB This means that the system would be able to be changed to accrue the commission on order or payment in the future if required.
This function delegates the calculation of the commission to individual functions for each of the calculation methods.
New functions can easily be created if companies have different methods.
An example is the StockCategoryCommission function:
function StockCategoryCommission ($SalesPerson,
$StockID,
$Currency,
$Value,
$Period) {
If there is no commission period for this sales person they do not get
commission so straight away return zero.
`If there is a commission period then return and store it (this may need `
`a new function probably in includes/DateFunctions.php)`
`Check if a record exists for this sales person/currency combination and `
`if there isn't return zero.`
`If there is a record for this person/currency combination then we need to `
`check if there are specific records for this stock category`
`If there is only one record returned for this salesperson/currency/category `
`combination then there is no need to go further as the commission due is `
`just the value of this transaction multiplied by the commission rate so `
`just return that amount.`
`If there is more than one records returned then at least two records exist `
`for this specific stock category, so next we need to ascertain the `
`commission period for this transaction.`
`Use a new function GetCommissionPeriods() to return an array of financial`
`periods for the commission period used for this sales person.`
`Now we get the total value of relevant transactions for this commission `
`period so for each financial period in the list of commission periods we `
`obtain the total value of invoiced/credited sales so far in this `
`salesperson/currency/category group`
`Now we cycle through the commission rates for this person/currency/category `
`adding up the commission as we go`
`First read the rates and quantity breaks into an array. Then we cycle `
`through the Breakpoint/rates array calculating the commission along the way`
`and return the commission to pay for this transaction.`
`If we have got this far tthen there is a record for this person/currency `
`combination but not for this category.`
`So, check if there is an "ALL" categories record`
`If there isn't an "ALL" categories record and we have reached this far, `
`then there cannot be commission to pay so return zero.`
`If there is only one "ALL" categories record returned for this `
`salesperson/currency combination then there is no need to go further as `
`the commission due is just the value of this transaction multiplied by the `
`commission rate so just return that amount.`
`If we reach here then at least two records exist for all stock categories,`
`so next we need to ascertain the commission period for this transaction.`
`Use a new function GetCommissionPeriods() to return an array of financial`
`periods for the commission period used for this sales person.`
`Now we get the total value of relevant transactions for this commission `
`period so for each financial period in the list of commission periods we `
`obtain the total value of invoiced/credited sales so far in this `
`salesperson/currency group`
`Now we cycle through the commission rates for this person/currency`
`adding up the commission as we go`
`First read the rates and quantity breaks into an array. Then we cycle `
`through the Breakpoint/rates array calculating the commission along the way`
`and return the commission to pay for this transaction.`
}
As the system is designed to accrue the commission at the point where the invoice or credit note is raised, then in these two scripts the CalculateCommission() function is called. If there is commission to pay then generate an entry in the new salescommissions table:
CREATE TABLE salescommissions (
type smallint(6) NOT NULL DEFAULT 10,
transno int(11) NOT NULL DEFAULT 0,
salespersoncode varchar(4) NOT NULL DEFAULT '',
paid int(1) NOT NULL DEFAULT 0,
amount double NOT NULL DEFAULT 0.0,
PRIMARY KEY (type, transno),
KEY (salespersoncode),
KEY (paid)
)
Also a journal is done to debit the relevant sales commission expense account and to credit the sales commission accruals account. A new systype record will be needed for "Sales commission accruals". If there is no commission for a transaction then no transactions are written to ensure we don't get masses of zero transactions.
This inquiry/report will show outstanding and/or paid commissions by sales
person, or over all sales people by user defined periods.
This may end up being more than one script.
This will provide a statement of unpaid commission for a given salesperson for a given period. This script will also have the option to create a purchase invoice for the commission (if a supplier account was assigned to this sales person in Suppliers.php - see above) and when the invoice is raised the transaction will be marked as paid in the salescommissions table. If no suppliers account is assigned then the transactions are marked as paid, and when the payment is made to the sales person (either via a payroll journal or a GL payment) the opposite entry must be made to clear the accrual.