MySQL Create Commission Reports per Salesperson

PHP
21

Right - but you want all of the data.

So you would pull all of the data, and then output based upon that data.

At this point, if it’s me and my database, your query is something to the tune of: (Spitball)

SELECT salesperson.name,
       sales.custno,
       customer.name,
       SUM(sales.price) AS totalsale,
       SUM(sales.commission) AS totalcomm
FROM   sales 
INNER  JOIN customer ON sales.custno = customer.custno
INNER  JOIN salesperson ON sales.salesperson = salesperson.id
WHERE  sales.date BETWEEN DATE_ADD(NOW(),INTERVAL -DAY(NOW())+1 DAY) AND LAST_DAY(DATE_ADD(NOW(),INTERVAL -DAY(NOW())+1 DAY))
GROUP  BY sales.salesperson,sales.custno
ORDER  BY salesperson.name,sales.custno

and then use PHP to walk through the results, looking for when my Salesperson’s name changes, and create a new table structure for the new salesperson.

22

That query does sound ideal, but there is no need to look for a sales date. The program I’m creating requires another spreadsheet to be uploaded in order to work. The spreadsheet already has the current month sales jobs in it.

What is the best way to add the two commission columns together?

23

Hi @jmyrtle,

First step, show us an sql dump of your existing DB Schema. If the DB is not properly designed (Normalized), then that needs to be fixed FIRST. The DB is the foundation of all the code you will ever write. If the DB is wrong, so will be all your code.

Then… you are ready to follow @m_hutley’s advice.

As far as whether someone is still with the company or not it is as simple as a flag in the DB that determines active/inactive or however you want to call it. A higher level design would store the hire/discharge dates and maybe even a discharge reason flag (Quit, Fired, Resigned, Died, etc… ). Just depends on your needs and how you might want to scale in the future.

24

Here is the current database schema and structure. There is only one table for now, but I will likely add more later if need be:

-- phpMyAdmin SQL Dump
-- version 5.0.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 24, 2020 at 09:44 PM
-- Server version: 10.4.11-MariaDB
-- PHP Version: 7.4.3

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `sales`
--

-- --------------------------------------------------------

--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `Invoice_Number` varchar(10) DEFAULT NULL,
  `Customer_Number` int(11) DEFAULT NULL,
  `Customer_Name` varchar(47) DEFAULT NULL,
  `Invoice_Date` date DEFAULT NULL,
  `Sales_Order_Number` varchar(10) DEFAULT NULL,
  `Sales_Amount` decimal(7,2) DEFAULT NULL,
  `Progress_Billing` int(11) DEFAULT NULL,
  `Extra_Charge_Amount` decimal(6,2) DEFAULT NULL,
  `Tax_Amount` decimal(6,2) DEFAULT NULL,
  `Invoice_Amount` decimal(8,2) DEFAULT NULL,
  `Writeoff_Amount` decimal(6,2) DEFAULT NULL,
  `Paid_Amount` decimal(7,2) DEFAULT NULL,
  `Balance_Due` decimal(7,2) DEFAULT NULL,
  `Use_Tax` decimal(6,2) DEFAULT NULL,
  `Tax_Stat` varchar(2) DEFAULT NULL,
  `Salesperson_1` varchar(5) DEFAULT NULL,
  `Comm_Rate_1` int(11) DEFAULT NULL,
  `Salesperson_2` varchar(2) DEFAULT NULL,
  `Comm_Rate_2` int(11) DEFAULT NULL,
  `Salesperson_3` varchar(2) DEFAULT NULL,
  `Comm_Rate_3` int(11) DEFAULT NULL,
  `Deposit_Applied` decimal(7,2) DEFAULT NULL,
  `Sales_Tax_Codes` varchar(17) DEFAULT NULL,
  `Sales_Tax_Amounts` varchar(28) DEFAULT NULL,
  `Customer_Address1` varchar(38) DEFAULT NULL,
  `Customer_Address2` varchar(26) DEFAULT NULL,
  `Customer_City` varchar(17) DEFAULT NULL,
  `Customer_State` varchar(2) DEFAULT NULL,
  `Customer_Zip` varchar(10) DEFAULT NULL,
  `Customer_Country` varchar(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
25

Thanks.
So first thing you need to do is learn about “Database Normalization”, then apply what you learned and post the new SQL.

When you get to fixing the db I would recommend you use all lowercase column names.

It would be helpful for you to provide us a high level overview of this project.

26

You remember the talk we had about entities back in post #7?

benanamen is pointing out that from that schema, i see at least 3 entities in your data. 3 entities and a join table, at that.

1 Like
27

Couldn’t you just make views instead of tables?

28

With your current table, that would just be a hack. An RDMS is not a spreadsheet. Do it right and normalize your DB.

29

Which form should the database be in or does it matter? I’ll try my best to attempt it.

Also (and this is just a general note), from everything I’m seeing and researching, database normalization handles duplicate data or multiple pieces of data in a single column.

I’m not entirely sure why normalization is necessary if all I’m doing is changing the data. The objective of importing the spreadsheet beforehand is to prevent the data from being changed at all and using queries to get what you need.

I feel like I’m over complicating this since I don’t understand what’s going on. I know what data I need, I just cant figure out the logical process to get it.

30

it’s not you who is overcomplicating it

developers are like starving carnivores, and you hung red meat out for them

your data comes from an excel workflow process, right?

all the advice you’re getting about designing database tables, in my opinion, overlooks this

31

Correct. All of the data comes from a CSV file that already has all of the data in it.

Interesting analogy here…

32

I did ask the OP for an overview of what he has going on. If OP is using a DB for anything there is no need for an “excel workflow process” except for analytics which comes after (from) the DB. So far OP has only mentioned reports. No reason the CSV can’t be imported into the DB in a normalized fashion. A single table is not scalable but maybe OP will never need anything other than these reports in which case, the suggestion using views would be viable or just a regular o’l query.

33

I am trying to prevent a lot of manual data manipulation. If data has to be altered, then why create the program with the database? It would be pointless.

That’s exactly what I’m trying to get. I need various reports for this program and I’m trying to figure out logically how to get these queries so that the report would show the correct result.

34

Where is this csv source data coming from? A third party or are you creating it? If you create it, where is the data coming from and how does it end up as a csv?

Nobody said the data has to be altered and there is no reason for there to be any manual data manipulation.

This is why I asked for a high level overview so we can correctly guide you.

35

The original data is from a third party source that the company uses for daily business.

The data would be altered slightly for commission before upload. The column names are different, so they would have to be changed to match the format of the program I’m making. Also, there is no id column, so that has to be made.

You mentioned that it would be better to use column names in all lowercase. I can do that, but in order for the import to work, the column names would have to change because the third party that creates the original spreadsheet does not do this.

What I’m trying to avoid is changing the original spreadsheet too much. If that happens, then there’s no point in doing this. I don’t mind changing it slightly to make my app work better, but I don’t want it to change too much.

This program is going to serve two purposes: calculating commission and sales tax. I’m not worried about sales tax at the moment since that is a complicated subject, so I’m mainly focusing on commission right now.

According to the client, each salesperson is given a percentage of commission based on the sales amount of the job they do. If that amount is over $1000, they receive 9%. If it’s under $1000, they receive 4.5%. I have already calculated the total sales amounts per customer and the commission amount per customer both of which are based on jobs that have gone over or under $1000 as you can see in this screenshot.

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

image
image974×161 20.4 KB

Now what I need to do is combine the two commission amount columns together for each customer so the salesperson can see how much total sales and commission they made for that customer, then I have to have a grand total of sales and a grand total of commission. This is so the salesperson can see how much they have sold for that month and how much total commission they’ve made for that month.

Hopefully, that gives you a better look at what I’m trying to do.

36

or

37

Can you provide a further explanation on the queries you provided? They don’t exactly follow what I’m trying to achieve.

38

@sibertius, your queries make no sense in light of the details the OP has provided.

@jmyrtle, Thanks for the detailed explanation.

I get what you are wanting to do.

Is your client the one getting the CSV from a third party or is your client the third party YOU are getting the CSV from. What I am trying to get at is what is the true source of the data and how is it stored. If the data is already in a database, that would be the best place to generate reports from.

I do get that the csv may be the only source available, as I get third party sales reports the same way.

Could you please provide an original csv with sample data (you said you “slightly alter it”) as well as an altered version and a description of what you altered.

Since this is for “daily business” it is even more important to understand all the aspects involved here to come up with the best solution.

39

The client is the third party that obtains the CSV report from the third-party. The third-party is their business software the use that generates the reports. However, they are not generated in a database. They are being provided in CSV format.

Here is a snippet of the original unaltered data:

image
image1377×453 23.8 KB

and here is the same table with the altered data:

image
image1389×463 23.3 KB

(I had to use Notepad++ to show the data since Excel would take multiple shots to show and it would likely be too small to see).

Really, the only thing that the company has to change are the column headers (including adding the id column), calculating proper writeoff amounts and adding cash sales (since for some reason the third party report does not provide this…)

However, that’s a lot to change considering all of the columns in this particular report. No, not all of them are being used for commission (even Comm. Rate isn’t being used), but some of it that isn’t used for commission will be used in sales tax.

Note that the reports only have to be done once a month. So, even though the company uses this software every day to conduct business, the reports are only run at the end of each month.

40

I need the actual csv. I cant import a picture of data.
What is the “business software” they are using?