PDA

View Full Version : Summary of Clients Spend


cocoonfx
02-08-2008, 11:08 PM
I am creating a online invoicing system which i want to see all value of the quotations i have grouped per customer and the total invoices paid.

I so far have created the table which stores all the quote information. But i am not sure how to sum up per customer and create a new table i.e

Joe Blogs have 10 quotes of which has 10,000

Joe Blogs have 5 invoices paid at 5000.

There will be one table which holds this information.

Would it look something like this?

<?PHP

$query =sprintf("SELECT * FROM quote ");
$result =mysql_query($query,$dbh);
$rowAccount = mysql_fetch_array($result);

// then do i do a count function.

$total quote = count($rowAccount['totalquote'] where $rowAccount ['customername'];

Would this work?




Would this do what i want?

davidj
02-09-2008, 08:20 AM
I am creating a online invoicing system

welcome to my world

Applications Development is a proper job!

davidj
02-09-2008, 08:31 AM
you need a paid table and a quote table you also need a customer table with a customer ID.

when you create a customer on the system your table will assign this an ID. You take that ID and add an entry in the other two tables (just the ID is needed to open or create an entry in those tables). Then you just perform UPDATES on the quote and invoice table when an action is carried out against the customer ID

i am assuming here that not every quote would result in an invoice so keep them separate and also it makes it easier to produce Management Information

cocoonfx
02-09-2008, 02:32 PM
Hello DJ

I was thinking i would have to do that, i was thinking that i would have a check box on the quote form which converts it into an invoice which then would update the invoice table with the records taking the id from the quote table and all the details to the invoice table.

I then can have a dashboard where i can see both the quote information and whats been invoiced.

But how would i get the total quote value per customer?

davidj
02-09-2008, 04:12 PM
you could add a row for every job in the quote table

its a simple case of adding the row variables together in the loop

davidj
02-09-2008, 04:16 PM
if the info of the quote table is the same as the invoice table you could just use a flag to determine an invoice or a quote (Q and I) where the same table would be fine

saves duplicating info

cocoonfx
02-09-2008, 04:23 PM
Yeah they would be the same. Thanks for the advice