calculate my monthly profit

[hogava]

I have about 100 product that my profit is a percentage of each product prices. This percentage is different for each product. Now I want to calculate my monthly profit by using the data of DB. But the ID of products is not mentioned in each invoice tables. (tblinvoices - tblinvoiceitems) 

I have just description in tblinvoiceitems that is not help to do this.

If i know each order or invoice is for which product, i can calculate my profit.

Please guide me regarding this issue.

[brian!]

5 minutes ago, hogava said:

But the ID of products is not mentioned in each invoice tables. (tblinvoices - tblinvoiceitems) 

the information you want is in the tables - just not all in the same table. 😞

9 minutes ago, hogava said:

I have just description in tblinvoiceitems that is not help to do this.

If i know each order or invoice is for which product, i can calculate my profit.

• tblinvoices -> this will tell you whether the invoice has been paid... for your needs, only those that are paid will matter.
• tblinvoiceitems -> using the ID value from tblinvoices, this gives you two fields in tblinvoiceitems, 'type' & 'relid'... if these are products, the 'type' will be "Hosting" and the 'relid' value will be the ID value in tblhosting... note: if they're not products, then the relid will point to ID values in other tables, depending on the value on 'type'.
• tblhosting -> with the hosting ID (relid) you got from tblinvoiceitems, you can then get the packageid value in tblhosting - that will be the ID value of the product in tblproducts.
• tblproducts -> with the product ID, you will be able to get the name of the product and this, I assume, calculate your profit for that product.