query help with commission table

[andydeans]

Hi,

I was wondering if someone could help me, i have nearly complete my site and need some help creating the queries to produce my commission and pipeline.

my table structure is:

CREATE TABLE `pipeline_commission` (
  `ProductID` varchar(75) NOT NULL,
  `Pip_CommID` int(11) NOT NULL auto_increment,
  `PipComm_UserID` int(11) default NULL,
  `clients_ClientID` int(11) default NULL,
  `Amount` decimal(14,2) default NULL,
  `LenderProvider` varchar(50) default NULL,
  `DueDate` date default NULL,
  `DatePaid` date default NULL,
  PRIMARY KEY  (`Pip_CommID`),
  KEY `Pipeline_UserID` (`PipComm_UserID`),
  KEY `clients_ClientID` (`clients_ClientID`),
  KEY `ProductID` (`ProductID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=69 ;

I need have did a sum for the amount to show how much money is there (amount)but how do i do the following:

1.If the datepaid column has a value then it is then classed as commission paid, how do i query that?
2.If the datepaid column is empty then it is in the pipeline to be paid, how do i query that?
3.I want to then show the above pipeline/commission for each userid, clientid and provider/lender. how can i query this?

Thanks again for all your guys help.

[tyson.crouch]

1. SELECT * FROM `pipeline_commision' WHERE `DatePaid` IS NOT NULL;
2. SELECT * FROM `pipeline_commision' WHERE `DatePaid` IS NULL;

As for number 3, in your PHP do you want to simply iterate through each of the pipeline/commission for each of those?

[andydeans]

Thanks for your help.

question 3 i would like to show in a table the pipeline/commission by userid, so the first 2 queries by userid so the individual can see their own and the boss can see all.

also be able to view the amount of commission in a table by lender/provider by userid and all again. same for the client, i would probably need to join the clientid to clients table so i could display their name.

So:

Client Name: Joe Bloggs
Commission Amount Paid: 4000
Pipeline Due: 2000

for Lenders:

Lender/Provider Name: Company Name
Amount Paid: 9000
Amount Due: 3000

For users:

Username: SalesMan 1
Amount Paid: 21000
Amount Due: 8000

I am just not sure about how to do the counts/sum etc. Ok with basic queries but not too sure how this would work.

Thanks again for all your help.

[tyson.crouch]

where do you keep track of how much is left to pay?

As i understand it, your `Amount` column is the only place tracks amount paid/due?
You should try using the SUM() function in your SELECT expression to get the total amounts.

Something along these lines...

SELECT `clients_ClientID`,
       `SUM(columnName1)` AS `commission_amount_paid`,
       `SUM(columnName2)` AS `commission_amount_due`
FROM `pipeline_commision`
WHERE clients_ClientID = '$clientID'

If you could post a little more information about how you store the amount which has been paid, and which is left to pay it would help a lot.

Sorry i cant be of much more help without this information.

Regards,
TC

Also, this link may help you to possibly solve it on your own http://www.mysqltutorial.org/mysql-aggregate-functions.aspx

[andydeans]

thanks for your help, the company keep track in this one table. so the amount is the amount they are due to be paid so if the datepaid column is empty then it is to be paid.

if it has a value in datepaid then it has been paid and is now commission paid.

they want to be able to search the table by month to show what is due in a month and what has been paid in a month.

is the table setup wrong?

many thanks

[tyson.crouch]

okay, well that is not very efficient in terms of keeping track of how much has been paid and how much is owed.

What i would recommend doing is the following;
1. Set up a column for amountOwed - which is the total of how much is owed initially
2. Set up a column for amountPaid - which will hold the amount which has been paid. This will change as the client continues to pay.

SO your table will look like this:

CREATE TABLE `pipeline_commission` (
  `ProductID` varchar(75) NOT NULL,
  `Pip_CommID` int(11) NOT NULL auto_increment,
  `PipComm_UserID` int(11) default NULL,
  `clients_ClientID` int(11) default NULL,
  `amount_Owed` decimal(14,2) default NULL,
  `amount_Paid` decimal(14,2) default NULL,
  `LenderProvider` varchar(50) default NULL,
  `DueDate` date default NULL,
  `DatePaid` date default NULL,
  PRIMARY KEY  (`Pip_CommID`),
  KEY `Pipeline_UserID` (`PipComm_UserID`),
  KEY `clients_ClientID` (`clients_ClientID`),
  KEY `ProductID` (`ProductID`)
) ENGINE=InnoDB;

SELECT clients_ClientID, amountPaid, (amount_Owed - amountPaid) AS amount_Remaining
FROM pipeline_commission
WHERE clients_ClientID = 1;

I hope this is what you were looking for. In terms of retrieving vendor and user details, its a slight modification of the query.

Regards,
TC

[andydeans]

Hi,

Having problems with my update page.

Basically what i was trying to do is when they inserted a product be that an insurance or mortgage on the fly it inserted into my commission table.

This worked fine, however when i update the product and have it updating the commission table it creates a new entry rather than updating the existing commision row.

Can you help? same table structure as above.

I dont understand why i can update my products no problem but cannot update the commission table, i think its because i have no proper link between the tables but as the ProductID can be the same i cannot make the unique and i cant ref Pip_CommID to the different products tables PK as they will have the same entries at times.

many thanks

[tesuji]

Hi

Usually I don't meddle in an advanced discussion, however I've got the feeling that this update problem (may be also other) coheres with the overall design. Could you post all tables which are related to pipeline_commission? Have all primary keys and foreign-key constraints been properly set up?

Btw, in "SELECT clients_ClientID, SUM(columnName1) ...." group-by clause is a must.

-- tesu

[tyson.crouch]

like tesuji said, we need to see all related tables ! =D thx tesuji!!

Did you implement the table which i proposed ?? We need to see all of your tables so that we can get a proper understanding of what we're deailing with.

That includes posting your;
- commission table
- product table
- client table

and what ever other tables you have