Calculate total outstanding balance

Report · Jan 27, 2009

I have 3 tables: Jobs, Customer, Balance

The "jobs" table holds all info on the job and also holds "job_cost" which is the price.

I now need to search by customer, his cost in total for every job he has and then update the job once calculated how much he has paid and how much is remaining from the total cost for all jobs.

This is the first time trying to do calculations and I am not sure what to do.

My table and all details are in the code below:

CREATE TABLE IF NOT EXISTS `balance` (
`bal_id` int(6) NOT NULL auto_increment,
`pay_date` varchar(8) default NULL,
`amount` varchar(11) default NULL,
`outstanding` varchar(11) default NULL,
`cust_id` varchar(8) default NULL,
PRIMARY KEY (`bal_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `customer` (
`cust_id` int(8) NOT NULL auto_increment,
`cust_address` text,
`cust_tel` varchar(12) default NULL,
`cust_mobile` varchar(12) default NULL,
`cust_email` varchar(80) default NULL,
`cust_website` varchar(80) default NULL,
`cust_name` varchar(20) default NULL,
`cust_business_name` varchar(30) NOT NULL,
PRIMARY KEY (`cust_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=73 ;

++++++++++++++++
Currently I have
++++++++++++++++
mysql_select_db($database_db, $db);
$query_total_cost = "SELECT t1.*, t2.*, t3.SUM(CAST(job_cost) AS DECIMAL(6,2)) AS total_amount
FROM customer as t1, balance as t2, jobs as t3
WHERE t1.cust_business_name = t3.cust_business_name
GROUP BY t1.cust_id";

But this does not seem to work and outputs:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS DECIMAL(6,2)) AS total_amount FROM customer as t1, balance as t2, job' at line 1

Report · Jan 27, 2009

The_FedEx_Guy wrote:
SELECT t1.*, t2.*, t3.SUM(CAST(job_cost) AS DECIMAL(6,2))
AS total_amount
FROM customer as t1, balance as t2, jobs as t3
WHERE t1.cust_business_name = t3.cust_business_name
GROUP BY t1.cust_id

Thats a bad way to join tables together, especially when you don't
specify which columns you want returned.

You need to read up on INNER JOIN's, cos even if that did work, it would
list all the data, from all your tables, repeated 3 times!

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 27, 2009

Hi Dooza
I havent done this type of Database in almost 5 years since I left Uni.

I have done it this way instead:

mysql_select_db($database_db, $db);
$query_total_cost = "SELECT SUM(job_cost) AS Total_Bal
FROM customer LEFT JOIN jobs ON customer.cust_business_name = jobs.cust_business_name
WHERE jobs.cust_business_name = '$_REQUEST[cust_business_name]'";
$total_cost = mysql_query($query_total_cost, $db) or die(mysql_error());
$row_total_cost = mysql_fetch_assoc($total_cost);
$totalRows_total_cost = mysql_num_rows($total_cost);

It works to find the total. Now I just need to make sure I can update the balances table.

Report · Jan 27, 2009

The_FedEx_Guy wrote:
> Hi Dooza
> I havent done this type of Database in almost 5 years since I left Uni.
>
> I have done it this way instead:
>
> mysql_select_db($database_db, $db);
> $query_total_cost = "SELECT SUM(job_cost) AS Total_Bal
> FROM customer LEFT JOIN jobs ON customer.cust_business_name =
> jobs.cust_business_name
> WHERE jobs.cust_business_name = '$_REQUEST[cust_business_name]'";
> $total_cost = mysql_query($query_total_cost, $db) or die(mysql_error());
> $row_total_cost = mysql_fetch_assoc($total_cost);
> $totalRows_total_cost = mysql_num_rows($total_cost);
>
> It works to find the total. Now I just need to make sure I can update the
> balances table.

Give me an example of the fields you want displayed, and I will see what
I can do.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 28, 2009

Basically mate,
I'm trying to load the total amount that the customer owes from his various jobs and add them to the "outstanding" field of the balance table.

I then have a textbox called "paid" and I want to calculate the new amount the customer has paid and then update the "outstanding" field to hold the new value.

The calculation I have at the moment is in the code below:
$totalled = $row_total_cost['Total_Bal'] - $_GET[paid];

Ideally when I add the job, I want to add the job, I need the cost added to whatever is in outstanding field.

But I have not created anything for that.

Report · Jan 28, 2009

I think you might be going about this the wrong way, I can't really get
my head around it.

I don't think you need a balance table. In your jobs table you have an
numeric field for invoice amount, and a numeric field for total paid.

Then you have SQL to display the SUM of the invoice amounts, and the SUM
of the paid, then on the page itself, do a calculation for what is
outstanding. You could do this in the SQL too.

SELECT SUM(jobs.invoice) AS InvoiceTotal, SUM(jobs.paid) AS PaidTotal,
SUM(jobs.invoice) - SUM(jobs.paid) AS Outstanding
FROM jobs
WHERE jobs.cutomerid = ?

How does that sound?

Dooza

The_FedEx_Guy wrote:
> Basically mate,
> I'm trying to load the total amount that the customer owes from his various
> jobs and add them to the "outstanding" field of the balance table.
>
> I then have a textbox called "paid" and I want to calculate the new amount the
> customer has paid and then update the "outstanding" field to hold the new value.
>
> The calculation I have at the moment is in the code below:
> $totalled = $row_total_cost['Total_Bal'] - $_GET[paid];
>
> Ideally when I add the job, I want to add the job, I need the cost added to
> whatever is in outstanding field.
>
> But I have not created anything for that.
>
>
>
>
> mysql_select_db($database_db, $db);
> $query_total_cost = "SELECT SUM(job_cost) AS Total_Bal
> FROM customer LEFT JOIN jobs ON customer.cust_business_name =
> jobs.cust_business_name
> WHERE jobs.cust_business_name = '$_REQUEST[cust_business_name]' AND paid =
> 'No'";
> $total_cost = mysql_query($query_total_cost, $db) or die(mysql_error());
> $row_total_cost = mysql_fetch_assoc($total_cost);
> $totalRows_total_cost = mysql_num_rows($total_cost);
>
> mysql_select_db($database_db, $db);
> $query_balances = "SELECT * FROM balance
> LEFT JOIN customer ON balance.cust_id = customer.cust_id
> LEFT JOIN jobs ON customer.cust_business_name = jobs.cust_business_name
> WHERE customer.cust_business_name = '$_REQUEST[cust_business_name]'";
> $balances = mysql_query($query_balances, $db) or die(mysql_error());
> $row_balances = mysql_fetch_assoc($balances);
> $totalRows_balances = mysql_num_rows($balances);
>
>
> ?>
>
> <?php
> $currentPage = $_SERVER["PHP_SELF"];
> $totalled = $row_total_cost['Total_Bal'] - $_GET[paid];
>
> ?>
>
> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
> " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
> <html xmlns=" http://www.w3.org/1999/xhtml">
> <head>
> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
> <title>Admin</title>
> <link href="css/bc-stylesheet.css" rel="stylesheet" type="text/css" />
> <style type="text/css">
> 
> </style>
> </head>
>
> <body>
> <table width="100%" border="0" cellspacing="3" cellpadding="3">
> <tr>
> <td></td>
> </tr>
> </table>
> <table width="100%" border="0" cellspacing="2" cellpadding="2">
> <tr>
> <td width="90%" valign="top"><table border="0" align="center"
> cellpadding="0" cellspacing="0">
> <tr>
> <td><div align="center"><h1>Make Payment</h1></div></td>
> </tr>
> </table>
> <form id="form1" name="form1" method="post" action="">
> <form id="form2" name="form2" method="post" action="<?php">
> <table width="500" border="0" align="center" cellpadding="3"
> cellspacing="3">
> <tr>
> <td width="30" valign="top">Paid:</td>
> <td width="152" valign="top"><input type="text" name="paid"
> id="paid" /></td>
> <td width="136" valign="top">New Balance:</td>
> <td width="143" valign="top"><?php echo $totalled;?></td>
> </tr>
> <tr>
> <td valign="top"></td>
> <td valign="top"> </td>
> <td valign="top"><input type="submit" name="update" id="update"
> value="Update" /></td>
> <td valign="top"> </td>
> </tr>
> </table>
> </form>
> <table width="500" border="0" align="center" cellpadding="2"
> cellspacing="2">
> <tr>
> <td valign="top"><span class="style3">Balance Outstanding:
> <strong><?php echo $row_total_cost['Total_Bal']; ?></strong></span></td>
> <td><div align="right"></div></td>
> </tr>
> </table>
> <table width="100" border="0" align="center" cellpadding="2"
> cellspacing="2">
> <tr>
> <td><input type="submit" name="Submit" id="button"
> value="Submit" /></td>
> <td> </td>
> </tr>
> </table>
> </form>
> </td>
> </tr>
> </table>
> <p> </p>
> <p> </p>
> </body>
> </html>
> <?php
> //mysql_free_result($total_cost);
>
> mysql_free_result($balances);
> ?>
>

--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 28, 2009

Hi,
The thing is I need to beable to add an amount to take off the job_cost field.

So if one customer has 2 jobs and both total £250 and he wants to pay only £50

I need to enter £50 into the textbox and then it should update the figures live then insert them to the database

I dont have a job invoice field in my jobs table . Are you making that on the fly?

Report · Jan 28, 2009

The_FedEx_Guy wrote:
> Hi,
> The thing is I need to beable to add an amount to take off the job_cost field.
>
> So if one customer has 2 jobs and both total ?250 and he wants to pay only ?50
>
> I need to enter ?50 into the textbox and then it should update the figures
> live then insert them to the database
>
> I dont have a job invoice table. Are you making that on the fly?

jobs.invoice just referred to the invoice amount field in the jobs table.

How about a payment table? All payments get recorded in there, so you
can keep track of what came in and when. Each payment record is linked
to the customer and the job.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 28, 2009

That makes more sense.
So shall I delete my balance table?

What I was thinking is an invoice table, because I am making the invoices but they are not held anywhere, I need an invoice number to start 001410

Report · Jan 28, 2009

The_FedEx_Guy wrote:
> That makes more sense.
> So shall I delete my balance table?

Yes

> What I was thinking is an invoice table, because I am making the invoices but
> they are not held anywhere, I need an invoice number to start 001410

Thats a sensible idea. Have a int field as your primary key that
auto-increments, you should be able to set the starting number to 1410,
and then use some clever technique maybe to format it with the leading
zeros.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 29, 2009

Hi Dooza,
I'm just working out the DFD for the database for Invoice but I do not know how in PHP to add more than one job_id in the invoice table for the invoice_no of 1410
I think im going to get stuck.

I need a link entity i think

Report · Jan 29, 2009

The_FedEx_Guy wrote:
> Hi Dooza,
> I'm just working out the DFD for the database for Invoice but I do not know
> how in PHP to add more than one job_id in the invoice table for the invoice_no
> of 1410
> I think im going to get stuck.
>
> I need a link entity i think

Why do you need more than one job_id in the invoice table?

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 29, 2009

Because the user making the invoice will select one or more jobs_id's to add to the invoice and then it displays on screen the selected invoices.

I then need to commit them to the database.

Its not how I would have done it.

This project was only meant to keep track of jobs that come in. But the GIT that I work for is too lazy to use Sage Accounts.

Report · Jan 29, 2009

The_FedEx_Guy wrote:
> Because the user making the invoice will select one or more jobs_id's to add to
> the invoice and then it displays on screen the selected invoices.
>
> I then need to commit them to the database.
>
> Its not how I would have done it.
>
> This project was only meant to keep track of jobs that come in. But the GIT
> that I work for is too lazy to use Sage Accounts.

Ahh, I see, this is getting tricky. One way would be to have yet another
table, lets call it invoice_jobs, it would have invoice number and job
number as primary keys, which will prevent them from being duplicated.
Your invoice would have a repeat region to show the jobs associated with
the invoice. You could create a running total on the page which gets
inserted into the invoice table.

I feel for you, it sounds like its getting very awkward.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 29, 2009

I was thinking of having invoice_jobs.

But would I be able to change the invoice number if a mistake was made by the user?

Report · Jan 29, 2009

The_FedEx_Guy wrote:
> I was thinking of having invoice_jobs.
>
> But would I be able to change the invoice number if a mistake was made by the user?

I don't see why not.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 29, 2009

.oO(Dooza)

>The_FedEx_Guy wrote:
>> Because the user making the invoice will select one or more jobs_id's to add to
>> the invoice and then it displays on screen the selected invoices.
>>
>> I then need to commit them to the database.
>>
>> Its not how I would have done it.
>>
>> This project was only meant to keep track of jobs that come in. But the GIT
>> that I work for is too lazy to use Sage Accounts.
>
>Ahh, I see, this is getting tricky. One way would be to have yet another
>table, lets call it invoice_jobs, it would have invoice number and job
>number as primary keys, which will prevent them from being duplicated.

Yep, that's the correct way to build m:n relations. But is this really
m:n here? Can a particular job be listed on multiple invoices? If not,
then it would be just a 1:m relation, where the invoice ID could be
stored directly in the jobs table.

>Your invoice would have a repeat region to show the jobs associated with
>the invoice. You could create a running total on the page which gets
>inserted into the invoice table.
>
>I feel for you, it sounds like its getting very awkward.

It's a pretty simple thing if you're familiar with databases, we just
don't know all the exact details and requirements, so it's a bit
complicated to give real hints or even code examples.

Micha

Report · Jan 29, 2009

.oO(The_FedEx_Guy)

>I was thinking of having invoice_jobs.
>
>But would I be able to change the invoice number if a mistake was made
>by the user?

Sure. The DB could even automatically propagate such a change to all
related tables where the invoice number is used (FOREIGN KEY is the
keyword here).

Micha

Report · Jan 30, 2009

Hi all,
I was looking at my ERD and this is what I have:

Invoice -< invoice_jobs >- jobs

I cant have 1 jobs has many invoice_jobs.
I'm kinda buggered with this.
I wish I was still at university.

Report · Jan 30, 2009

I've made the invoice table:

CREATE TABLE `jobs`.`invoice` (
`invoice_no` INT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`invoice_date` VARCHAR( 8 ) NULL ,
`job_id` VARCHAR( 8 ) NULL ,
`job_quantity` VARCHAR( 10 ) NULL ,
`job_description` VARCHAR( 255 ) NULL ,
`cost` DECIMAL( 6, 2 ) NULL ,
`total` DECIMAL( 6, 2 ) NULL ,
`grand_total` DECIMAL( 6, 2 ) NULL ,
`cust_id` VARCHAR( 8 ) NOT NULL ,
INDEX ( `job_id` , `cust_id` )
) ENGINE = MYISAM

How can I insert more than 1 job_id into the same invoice_no

Report · Jan 30, 2009

The_FedEx_Guy wrote:
> I've made the invoice table:
>
> CREATE TABLE `jobs`.`invoice` (
> `invoice_no` INT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
> `invoice_date` VARCHAR( 8 ) NULL ,
> `job_id` VARCHAR( 8 ) NULL ,
> `job_quantity` VARCHAR( 10 ) NULL ,
> `job_description` VARCHAR( 255 ) NULL ,
> `cost` DECIMAL( 6, 2 ) NULL ,
> `total` DECIMAL( 6, 2 ) NULL ,
> `grand_total` DECIMAL( 6, 2 ) NULL ,
> `cust_id` VARCHAR( 8 ) NOT NULL ,
> INDEX ( `job_id` , `cust_id` )
> ) ENGINE = MYISAM
>
> How can I insert more than 1 job_id into the same invoice_no

I think, to give Michael (the more experienced database guy) more of a
chance, it might be best to detail what your overall objective is, as I
know I am confused, and I can't really be sure what is the best approach.

You are doing well by posting the above, but I think we need to know more.

Dooza
--
Posting Guidelines
http://www.adobe.com/support/forums/guidelines.html
How To Ask Smart Questions
http://www.catb.org/esr/faqs/smart-questions.html

Report · Jan 30, 2009

The objective is to insert the invoice data from on screen to database with record of all jobs listed in the invoice.

Here is invoice.php where the on screen details for the invoice are processed:

Report · Jan 30, 2009

.oO(The_FedEx_Guy)

>I've made the invoice table:
>
> CREATE TABLE `jobs`.`invoice` (
> `invoice_no` INT( 8 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
> `invoice_date` VARCHAR( 8 ) NULL ,
> `job_id` VARCHAR( 8 ) NULL ,
> `job_quantity` VARCHAR( 10 ) NULL ,
> `job_description` VARCHAR( 255 ) NULL ,
> `cost` DECIMAL( 6, 2 ) NULL ,
> `total` DECIMAL( 6, 2 ) NULL ,
> `grand_total` DECIMAL( 6, 2 ) NULL ,
> `cust_id` VARCHAR( 8 ) NOT NULL ,
> INDEX ( `job_id` , `cust_id` )
> ) ENGINE = MYISAM
>
> How can I insert more than 1 job_id into the same invoice_no

Not with the table above. Remove the job_* fields from above, they don't
belong there. These values are already stored in the jobs table. Maybe
you could even remove some of the DECIMAL fields, because some of these
values look as if they could be calculated on-the-fly from the values in
the jobs table ('total' maybe?).

(As a side node: names like job_quantity really sound like you want to
use a numeric type for them, not VARCHAR.)

I think the only price value you have to store in the invoice table is
the one of how much was already paid by the customer. The total jobs
cost and the difference to what was paid can be calculated on-the-fly.
Storing these values in the invoice table would cause unnecessary
redundancy and will be a potential source of problems and data
inconsistencies, because when you add another job to an invoice, you
would have to modify the invoice record as well. This should be avoided.

Now you can add another table as said before to connect invoices and
jobs. This can be a simple two-column table, one column referencing job
IDs in the jobs table, the other referencing invoice IDs in the invoice
table. This will give you a way to easily get all jobs that belong to a
particular invoice. You can then simply list the job details, calculate
the total cost, the outstanding balance or whatever.

As said earlier as well, you might not really need this 'invoiceJobs'
table. If a single job can't appear on two or more invoices (which I
think it can't), then you can directly store the invoice number in the
jobs table when the job is added to an invoice (until then the field
would simply be NULL, meaning that there isn't an invoice yet for that
job). Then if you want to print the invoice or get its details, simply
fetch all jobs from the jobs table with that given invoice number and do
whatever you want with them.

Micha

Report · Feb 02, 2009

Micha,
Thank you for your reply. I will attempt this now and let you know how I get on.

Thanks again 🙂

Report · Feb 02, 2009

I have made the changes
And I have come up with a snag.

I need to generate the invoice number once the invoice has loaded with the jobs.

Can I select the next available number in the database? Or do I have to create a function to keep a record and then increment by 1?