Skip to main content
T
The_FedEx_Guy
Inspiring
January 27, 2009
Question

Calculate total outstanding balance

  • January 27, 2009
  • 21 replies
  • 2505 views
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
This topic has been closed for replies.

21 replies

T
The_FedEx_GuyAuthor
Inspiring
February 2, 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?

T
The_FedEx_GuyAuthor
Inspiring
February 2, 2009
Micha,
Thank you for your reply. I will attempt this now and let you know how I get on.

Thanks again :)
N
Newsgroup_User
Inspiring
January 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
N
Newsgroup_User
Inspiring
January 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
T
The_FedEx_GuyAuthor
Inspiring
January 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:
T
The_FedEx_GuyAuthor
Inspiring
January 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
T
The_FedEx_GuyAuthor
Inspiring
January 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.
N
Newsgroup_User
Inspiring
January 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
N
Newsgroup_User
Inspiring
January 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
N
Newsgroup_User
Inspiring
January 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
T
The_FedEx_GuyAuthor
Inspiring
January 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?
Show more replies
Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices