MySQL Create Commission Reports per Salesperson

PHP
1

I’m creating commission reports for my program now and I need to create one that shows the Customer Number, the Customer’s Name, the Total Sales per customer and the total commission amount per customer.

I only have one problem. It needs to be done per salesperson.

With the event that some people can leave the company, should I include a second table that lists the different salespeople so that the code knows who is who, or am I overthinking something here? My thought is if I can code a form or a table in PHP that is connected to a second table, it will be easier to create queries for each salesperson rather than hardcoding it through PHP and having to update the code each time someone is terminated or hired.

This query shows how the commission is calculated (this is a demo calculation):

SELECT Salesperson_1 as salesman, 
Customer_Number as customer, 
LEFT(Customer_Name, 28) as name, 
SUM(Sales_Amount) as invoiced_sales, 
SUM(CASE WHEN Sales_Amount >= 1000 THEN Sales_Amount ELSE 0 END) as total_above_1000, 
CASE WHEN Sales_Amount >= 1000 THEN ROUND(Sales_Amount * 0.09, 2) ELSE 0.00 END as comm_above_1000, 
SUM(CASE WHEN Sales_Amount < 1000 THEN Sales_Amount ELSE 0 END) as total_below_1000, 
CASE WHEN Sales_Amount < 1000 THEN ROUND(Sales_Amount * 0.045, 2) ELSE 0.00 END as comm_below_1000 
FROM invoices 
WHERE Balance_Due = 0 
GROUP BY Salesperson_1, Customer_Number

What do you guys suggest?

2

what part of your code here is hardcoded?

3

what’s wrong with this query that you posted?

it’s done per salesperson, as you said you needed

4

My apologies, I wasn’t clear enough.

Well… sort of. I need to get the total amount of sales per customer that each salesperson did. So I need one set of totals for Jane Brown, another set for John Smith, etc.

There is no hardcode yet. I am simply thinking ahead.

Basically, what I’m trying to do is come up with different totals for each salesperson. But rather than list all of the salespeople in one query, this time they need to be in different queries (one for John, one for Jane, etc.)

So what happens if John Smith decides to leave the company? Then I cannot include his totals for commission at the end of the report unless he is deleted somehow, right?

Again, I’m not even sure if my thinking is straight. I may be overthinking this.

5

Why?

Let’s think about this from a real world perspective.

You have a button you can push, that prints out a report.

That report lists all of the salespeople, and their totals.

It tells you:

Salesperson Total
John 3000
Jane 1000
Adam 9000
Company Total 13000
(Adam’s an overachiever. Noone likes Adam.)

The button always gives you this chart whenever you push it. You need the company report for the year, so you push the button.

You now get asked by the CFO what John’s total was for the year.

Why do you need another piece of paper?

6

I’m not looking at sales totals, I’m looking at commissions.

John’s total sales is $3000. I need a more detailed report to provide to John how that was calculated. So I would need another query that grabs all records grouped together, but it only contains ones that have John’s name in it.

Also, where does commission come into play? If commission is a percentage of the sales, where is that shown?

It isn’t specifically for the CFO or the President or HR, it is a smaller condensed report to give to the actual salesperson so that they know how much total sales they did that month and how much commission they earned that month.

7

So in terms of Database design, my philosophy is simple;

Tables represent one of two things: Entities, or Relationships-In-Certain-Cases.

If i’m creating a table for an Entity, it’s because I want to store information about that entity.

For example; if I’m creating a Salesperson table, it’s because I want to store things like… the salesperson’s email. Their address. their… pick statistics about the entity ‘salesperson’.

A sale that a salesperson completes is not information about the Salesperson entity; it’s a Sale - another entity. That entity may tie into the salesperson entity (because a salesperson made the sale), but the information (the amount of the sale, the state of the sale, etc) are properties of the sale, not the salesperson.

That said, when putting the salesperson into the sale table, I want to be able to uniquely identify the salesperson. “John” is likely insufficient information - my company is likely to employ more than one John in the course of its existence, and i need to be able to distinguish which John did the sale.

So, if the question is whether you need to create a separate table for salespeople, your answer is likely in what you want to store.

Now, if you want to talk about how your PHP can use variables to make your queries flexible, that’s an entirely different conversation.

1 Like
8

That’s basically my question.

Say I have this query:

SELECT * FROM invoices WHERE Salesperson = 'JS';

That query selects every record in the table with the initials of John Smith (JS). However, that creates a problem.

Notice that the query is defining the salesperson for me. The way I process this is what happens if John Smith left the company? He obviously won’t make sales for us anymore, so if his name is left in the query, then the code would break or that query would be empty.

What I would like to do is have a list of the names and initials of the salespeople we have. This way, there would be some kind of relationship between the two tables and if certain queries have a certain set of initials then the code would work properly.

Now that I’m thinking this through though, it sounds more like a PHP question rather than a database question.

9

It is. So i’m going to use my mystical powers to change the category of the post. (Hint: You can always do that yourself up at the top of the thread.)

You’re using PDO. Which is good. So let’s talk about Prepared Statements.

This scenario is what Prepared Statements exist for. You know what you want to ask the database for, but you have a question mark - in this case, the name of the sales person.

So, we introduce the idea of a parameter.
SELECT * FROM invoices WHERE Salesperson = 'JS';
“I want all of the invoices for the salesperson JS.”

Let’s generalize the query.
“I want all of the invoices for a given salesperson.”

We don’t need to know ahead of time whiat salesperson we’re talking about; we just want to be able to, for any individual salesperson, pull their invoices.

In Prepared Statement terms, the salesperson string is now a parameter of the query. Named Parameters start with :,followed by the name we’re going to reference it by.

SELECT * FROM invoices WHERE Salesperson = :saleperson

So now, if :salesperson is JS, we get JS’s invoices; if it’s AD, we get AD’s invoices, etc.

PDO queries that are going to use parameters follow the prepare-bind-execute flow of query designation. Let’s take a look at how that might occur with this query.

<?php
$salesperson = "JS";
//I assume the existance of $db, a PDO Database Connection.
$stmt = $db->prepare("SELECT * FROM invoices WHERE Salesperson = :saleperson");
$stmt->bindParam('salesperson',$salesperson);
$stmt->execute();

//$stmt is now holding the result of our query, which can be ->fetch or ->fetchAll'd.

Now, I know what you’re about to say - we still statically defined our variable, $salesperson.

But we can pull that value from any number of places, and where we do so depends on our use case. For example, we might pull a list of names from the database by querying for the list of options (either from an individual table, or by querying the existing one for something like SELECT DISTINCT salesperson FROM invoices; we may then present a list of names in a table, with links to our reporting page passing the salesperson’s name on the url: http://example.com/invoicereport.php?salesperson=JS, in which case $salesperson = $_GET['salesperson'] will make our code work.

At this point though, we’re getting into very specific use cases rather than the general theory; this is an example to help you get your mind wrapped around the concept.

1 Like
10

that list of salespeople is most definitely ~not~ a PHP question

11

Rudy is correct that the specific question of “I want a list of names and initials of the salespeople we have” is not a PHP question (strictly); and in fact, it points to the answer to your original question of whether you need a separate table - You want to store multiple pieces of information about a salesperson (Name and Initials). This indicates you do want a separate table for salespeople; you have multiple pieces of information about the entity you want to store.

12

Thank you for clearing up Prepared Statements! I’ve used them in writing PHP before, so I am familiar with how they work, though I get confused on the difference between fetch(); and fetchAll();

Something else I just thought of, I need grand total commissions as well.

Taking my query in my OP, here is part of what the result comes out to be:

image
image1038×158 15.9 KB

Now, I need to figure out how to total the commission columns together so that the salesperson will know how much total commission they have earned for the month. Can PHP handle this or does this have to run through MySQL?

Which of these is the best option? Also, what would happen if the salesperson’s initials are identical with someone else’s? Would the initials need a unique key or can PHP check for this?

13

Well, you tell me. Let me take your screenshot there.

I have two employees. Brian King and Ben Knightly.

Who sold the product to Promo Logic?

14

Well, obviously, you can’t tell.
So, yes I know the initials need to be unique. However, which is better to process that? I know I can use MySQL and create a unique key, but I wonder if it is better to use PHP to check that instead.

15

It is 100% better for your database to enforce such a criteria,

But consider what the criteria should be, or how to enforce it - You’re not going to be able to tell the company “We’ve already got someone with the initials BK, we can’t hire someone else with those initials.”

Instead, consider… IS there a unique piece of information about an employee? Name? Not really, Ask any number of Paul Johnsons in the world. email? Possible, but only if when Paul Johnson leaves the company, we don’t reuse his email address for the next Paul Johnson. Employee ID Number? Probably is unique and not to be reused.

(A caution here - consider the confidentiality of the information. For a US based company, a social security number is a unique identifier - but you would never, ever, use it in a database that will be accessed from the web!)

16

I see. Well, what about this?

17

“So the salesperson will know how much they have earned for the month”

So, walk me through the scenario a bit more. The salesperson is sitting down at a computer, logging into your website, and will pull up their own record?

(Note: The answer to your question is ‘both’, regardless.)

18

No. This part will be printed or sent to them by email from the company. It’s like a check stub for commission.

19

Okay, so it’s the administrator, sitting down and pulling up a list.

Personally, i’d be inclined to just pull all records at once, and just have the output spaced so that it can be copied into the email.

20

The output I’m looking for will be spaced by pages so it’ll be easier to print out or email that way.

Something like this:

image