[SOLVED] Which PHP functions to use in this scenario?

[TomAce]

I have three tables: customers, invoices and payments.

* One customer can have multiple invoices

* One invoice can have multiple payments

 

Here is a graphical representation of my tables:

 

I know what I want in human language, but I'm not sure which PHP functions I could use to accomplish this. Perhaps someone can point me in the right direction?

 

 

1. Select all invoices of a customer.

I'd use this query: SELECT * FROM customers JOIN invoices ON customers.company_id = invoices.company_id WHERE company_id = <some id>

 

2. For each invoice, check if it has been fully paid by comparing if payments.amount_paid is equal to invoices.amount.

Here is the first problem: how do I create a loop that will check each invoice that matches the query from point 1?

 

3. If the invoice hasn't been paid yet, calculate how much is yet to be paid (invoices.amount - payments.amount_paid).

Question 1: since one invoice can have multiple payments, it should add all payments together first. Say I were to store this as $AllPaymentsForThisInvoice so I can use it in the next step. How would I first add up all payments for this invoice and store it as one variable?

Question 2: substract $AllPaymentsForThisInvoice from invoices.amount. The result is how much the customer still needs to pay for this particular invoice.

 

How do I store this result? Because in point 4 I want to..

 

4. Add together all the amounts that still need to be paid (add together all results from point 3).

Say we store this as $TotalAmountThatNeedsToBePaid for clarity reasons... How?

 

 

Once I am here I will compare $TotalAmountThatNeedsToBePaid to the customer's limit and take the appropriate action.

 

 

Thank you in advance.

[cooldude832]

You can use a join or an multi table select, but I would let php do all the math to it (mysql can, but you be pulling too much data)

This is what I'd say the query looks like

<?php
ConnectSQL();
$invoice_id = "15";
define("INVOICE_TABLE", "Invoices");
define("CUSTOMERS_TABLE", "Customers");
define("PAYMENTS_TABLE", "Payments");
$fields = array(
	INVOICE_TABLE.".Invoice_id as Invoice_id",
	INVOICE_TABLE.".amount as Invoice_amount",
	PAYMENT_TABLE.".Payment_id as Payment_id",
	PAYMENT_TABLE.".Amount_paid as Payment_amount",
	CUSTOMERS_TABLE.".Company_id as Company_id",
	CUSTOMERS_TABLE.".Limit as Cust_lim"
	CUSTOMERS_TABLE.".Company_name 	as Company_name";
);
$fields = implode(" , ",$fields);
$q = "Select ".$fields." from `".INVOICE_TABLE."`, `".CUSTOMERS_TABLE."`, `".PAYMENTS_TABLE."`
Where ".INVOICE_TABLE.".Invoice_id = '".$invoice_id."'  and ".CUSTOMERS_TABLE.".Comapny_id=".INVOICE_TABLE.".company_id
and ".PAYMENT_TABLE.".Invoice_id = ".INVOICE_TABLE.".Invoice_id
Group by ".INVOICE_TABLE.".Invoice_id";
$r = mysql_query($q) or die(mysql_error()."<br /><br />".$q);

?>

 

And then work from there, multiple payments might be an issue though

 

and I didn't spell check it

 

[craygo]

OK here is something for starters. I will help with code later if you need it but here goes.

 

1.

$sql = "SELECT SUM(amount_paid) AS paid, * FROM Invoices 
    LEFT JOIN customers ON invoices.company_id = Customers.company_id 
    LEFT JOIN Payments ON invoices.invoice_id = Payments.invoice_id 
    WHERE invoices.company_id = '$id' GROUP BY payments.invoice_id";

 

This will give you all the invoices regardless of weather a payment was made or not. If you use just JOIN only records with matching values in each table will be selected.

 

2.

$paid = $invoice_amount-$invoice_amount_paid;
if($invoice_amount == $invoice_amount_paid){
echo "$paid due";
} else {
echo "$paid past due";
}

 

Well I just read the rest of your post tough for me to just write code for you without testing. (I like to test things) if you want some more help I can be reached on AIM screen name craygo69

 

Ray

 

 

[TomAce]

Hey thank you. I wasn't even expecting full code examples  :o

 

I will get to work and bump this thread when needed.

[craygo]

Here's something quick

<?php
$companyid = '1';
$sql = "SELECT SUM(amount_paid) AS paid, Customers.company_name, Customers.limit, Invoices.invoice_id, Invoices.amount, Payments.amount_paid FROM Invoices
        LEFT JOIN Customers ON Invoices.company_id = Customers.company_id
        LEFT JOIN Payments ON Invoices.invoice_id = Payments.invoice_id
        WHERE invoices.company_id = '$companyid' GROUP BY invoice_id ORDER BY company_name, invoice_id";
$res = mysql_query($sql) or die(mysql_error());

echo "<table width=100% align=left>\n";
$lastcompany = '';
$lastinvoice = '';
$paid_total = 0;
$invoice_total = 0;
while($r = mysql_fetch_assoc($res)){
  if($r['company_name'] != $lastcompany){
  echo "<tr bgcolor=c0c0c0>
    <td colspan=2>".$r['company_name']."</td>
    <td>$".$r['limit']." Limit</td>
  </tr>
  <tr>
    <td>Invoice ID</td>
    <td>Invoice Amount</td>
    <td>Amount paid</td>
  </tr>\n";
  }
echo "<tr>
  <td>".$r['invoice_id']."</td><td>".$r['amount']."</td><td>".$r['paid']."</td>
</tr>\n";


$invoice_total += $r['amount'];
$paid_total += $r['paid'];
$lastcompany = $r['company_name'];
$lastinvoice = $r['invoice_id'];
}
    echo "<tr bgcolor=yellow>
    <td><br /></td>
    <td>".number_format($invoice_total, 2)."</td>
    <td>".number_format($paid_total, 2)."</td>
    </tr>
    </table>\n";
?>

 

Now your invoice total is in the variable $invoice_total and the paid total is in $paid_total.

 

Ray

[TomAce]

Craygo, thank you again. Your last code example has been extremely helpful!