Invoicing - Dates

[nerd99]

Hi,

 

Having some troubles getting my head around this! Any help would be great.

 

What I am aiming to achieve is the following. I have an accounts homepage. When I open this page I want it to calculate which accounts should be in what category. In other words, there are 3 categories...

1) Outstanding, where paid='0'

2) Paid, where paid='1'

3) Overdue, where the current date is greater than the due date.

 

I was thinking that it might be possible to do it this way; upon opening the accounts homepage the code...

*defines $today

*then gets the DATE field where paid=0 from the table (this is entered on date when invoice is created)

*defines $due

*THEN says if $today>$due then set paid='2'

 

On the next page I would simply display the number of rows where paid = 0, 1, or 2.

 

Here is the code I have so far. Is this heading in the right direction or am I barking up the wrong tree? Ideas?

<?php include('includes/admin_session.php'); ?>
<?php

//Define date for calculating overdue accounts

$b = time (); // Get time from server
$timezone = (date_default_timezone_set("Australia/Sydney")); // Make time Sydney time.
$today = date ("Y-m-d,$b"); // Today's date

require_once("includes/connection.php");

        $result = mysqli_query($connection,"SELECT * FROM invoices WHERE paid='0'")
        or die(mysqli_error($connection));

        while($row = mysqli_fetch_array($result)) {

        // Get all rows and assign them to variables

        $id = $row['id'];
        $stamp = $row['stamp'];
    	$due = strtotime('+2 weeks', strtotime($stamp));

if($today>$due) {

$result = mysqli_query($connection,"UPDATE invoices SET paid='2' WHERE id='$id' ") or die(mysqli_error($connection));

header("Location: admincp_accounts2.php");
exit;

}else{

$result = mysqli_query($connection,"UPDATE invoices SET paid='0' WHERE id='$id' ") or die(mysqli_error($connection));

header("Location: admincp_accounts2.php");

}

}
?>

 

 

[ignace]

Before you perform the below query execute:

 

UPDATE invoices SET paid = 2 WHERE date_due < now()

 

SELECT (CASE paid
  WHEN 0 THEN 'Outstanding'
  WHEN 1 THEN 'Paid'
  WHEN 2 THEN 'Overdue' END) AS paid_status
FROM invoices
ORDER BY field(paid_status, 'Outstanding', 'Paid', 'Overdue')

 

Loop over it as:

 

$status = '';
while ($row = mysql_fetch_assoc($result)) {
  if ($status !== $row['paid_status']) {
    echo '<h2>', $row['paid_status'], '</h2>';
    $status = $row['paid_status'];
  }
  //echo whatever you want to show below each category
}

 

Creates something like:

 

<h2>Outstanding</h2>
..
<h2>Paid</h2>
..
<h2>Overdue</h2>
..

[nerd99]

Hi ignace,

UPDATE invoices SET paid = 2 WHERE date_due < now()

 

This did the trick. Thanks for your help