Invoice Management in a PHP Invoicing System

If you’re running a business in which you’re invoicing clients, you need some way to keep track of which invoices have gone out and which clients have paid. In this second article of a four-part series that covers the creation of a PHP invoicing system, we create the parts that deal with this kind of invoice management.


Downloadable files for this article are available here and here.

Continuing with the PHP Invoicing series, we will now concentrate on how to create, view and print an invoice. So let’s get started!

Create a new PHP document and save it as main.php. Refer to fig2 to see what the main page looks like. There are essentially three pages that deal with invoices:

  • Allinv.php – Lists all invoices in the database.
  • Unpaid.php – Lists all unpaid invoices.
  • NewInvoice.php – Enables you to generate a new invoice.

There are also related pages that handle deleting and updating invoices.

Create a new document and save it as allinvoices.php. The page consists of both PHP and HTML code. Add the following code:

Code4  Allinv.php
<?
include “config.php”;
$query_invoice = “SELECT *,DATE_FORMAT(inv_date,’%D %M %Y’) as
idate,DATE_ADD(inv_date,INTERVAL 30 DAY) as duedate  FROM
invoices ORDER BY invno DESC”;
$result_invoice = mysql_query($query_invoice);
$num_invoice = mysql_num_rows($result_invoice);
?>

This is a standard query that retrieves all the invoices in the database. The only things worth explaining are the DATE_FORMAT() and DATE_ADD() functions. The DATE_FORMAT function does exactly what the name implies: it formats the date that is stored in the way that you want it. The DATE_ADD() function adds a specified number of days (in this case it is 30) to the date column in the database.

I wanted the date formatted as dd mm yyyy which is displayed as, for example, “18th June 2005.” There are a variety of ways in which you can format a date. Check http://www.mysql.com/ for more details.  The 30 days that I’ve specified in the DATE_ADD() function will help us determine whether an invoice is overdue.

The number of rows returned by the query is stored in the “$num_invoices” variable. As you’ll see in a little while, it will be used to determine how many rows a table should have.

{mospagebreak title=Finishing the Invoice Page}

The rest of the page looks like this:

<tr valign=”top” class=”tblheadings”>
          <td width=”9%”><strong>Invoice # </strong></td>
          <td width=”14%”><strong>Client Name </strong></td>
          <td width=”8%”><strong>Status</strong></td>
          <td width=”11%”><strong>Date Issued </strong></td>
          <td width=”11%”><strong>Total(excl VAT)</strong></td>
          <td width=”13%”><strong>Total(incl VAT)</strong></td>
          <td width=”20%”><strong> Action </strong></td>
        </tr>
<?
if($num_invoice > 0){
while($invoice = mysql_fetch_assoc($result_invoice)){
                        ?>
        <tr valign=”top” class=”tblinfo”>
          <td><?=$invoice['invno'];?></td>
          <td><?=$invoice['name'];?></td>
          <td><? if($td > $invoice['duedate']){
                          echo “<font color=”#FF0000″>Overdue</font>”;
                          $email=”yes”; 
                          }else{
                          echo $invoice['status'];}
                          ?></td>
          <td><?=$invoice['idate'];?></td>
                          <? $query_invdetails = “SELECT * FROM clientinv WHERE
finv=’”.$invoice['invno'].”‘”;
if(!$result_invdetails= mysql_query($query_invdetails)){
echo mysql_error();
}else{
$num_invdetails = mysql_num_rows($result_invdetails);
$invdetails=mysql_fetch_assoc($result_invdetails);}
?>
          <td>£<?=$invdetails['totxVAT'];?></td>
          <td>£<?=$invdetails['totwVAT'];?></td>
          <td><a href=”invView.php?iid=<?=$invoice['invno']?>”>View </a> |<a
href=”delinvoice.php?iid=<?=$invoice['invno']?>”>Delete</a> <a href=”edInv.php?
iid=<?=$invoice['invno']?>”>Update</a> <? if($td > $invoice['duedate']){
                          echo “<img src=”images/RedFlag.gif” height = “10″/>”; 
                          }
                          ?>
                          <? if(isset($email)){?>
                          <a href=”emailInv.php?iid=<?=$invoice['invno']?>”>Email
Reminder</a>
                          <? }?>
                           <a href=”pdf.php?cid=<?=$invoice['cid']?>”><img
src=”http://www.devshed.com/wp-content/themes/twentyten/images/pdf.gif” width=”18″ height=”18″ border=”0″/></a></td>
        </tr>
                        <? }
                        }else{ ?>
                        <tr>
                        <td colspan=”7″><p>No invoices available at present.</p></td>
                        </tr>
                        <? }?>
      </table>

So, what is happening here? Basically, we are creating a table that will list all the invoices based on the results from the query. In other words, part of the table will be dynamically generated. The first section of the table shows  the headings of the different columns. The columns that we want listed are:

  • Invoice number.
  • Client name.
  • Status – Shows if a  invoice is paid or unpaid.
  • Date issued.
  • Total (excluding VAT).
  • Total (including VAT) .
  • Action - Actions that can be taken, which include viewing, updating or deleting a invoice.

The headings are created by the following HTML code:

<tr valign=”top” class=”tblheadings”>
          <td width=”9%”><strong>Invoice # </strong></td>
          <td width=”14%”><strong>Client Name </strong></td>
          <td width=”8%”><strong>Status</strong></td>
          <td width=”11%”><strong>Date Issued </strong></td>
          <td width=”11%”><strong>Total(excl VAT)</strong></td>
          <td width=”13%”><strong>Total(incl VAT)</strong></td>
          <td width=”20%”><strong> Action </strong></td>
        </tr>

{mospagebreak title=Generating a Table}

The next part of the table is dynamically generated based on the retrieved data. First, PHP checks to see if the returned results are greater than zero. If they are, it then matches and lists the data from the database to the table headings:

<?
                        if($num_invoice > 0){
                        while($invoice = mysql_fetch_assoc($result_invoice)){
                        ?>
        <tr valign=”top” class=”tblinfo”>
          <td><?=$invoice['invno'];?></td>
          <td><?=$invoice['name'];?></td>
          <td><? if($td > $invoice['duedate']){
                          echo “<font color=”#FF0000″>Overdue</font>”;
                          $email=”yes”; 
                          }else{
                          echo $invoice['status'];}
                          ?></td>
          <td><?=$invoice['idate'];?></td>
                          <? $query_invdetails = “SELECT * FROM clientinv WHERE
finv=’”.$invoice['invno'].”‘”;
if(!$result_invdetails= mysql_query($query_invdetails)){
echo mysql_error();
}else{
$num_invdetails = mysql_num_rows($result_invdetails);
$invdetails=mysql_fetch_assoc($result_invdetails);}
?>
          <td>£<?=$invdetails['totxVAT'];?></td>
          <td>£<?=$invdetails['totwVAT'];?></td>
          <td><a href=”invView.php?iid=<?=$invoice['invno']?>”>View </a> |<a
href=”delinvoice.php?iid=<?=$invoice['invno']?>”>Delete</a> <a href=”edInv.php?
iid=<?=$invoice['invno']?>”>Update</a> <? if($td > $invoice['duedate']){
                          echo “<img src=”images/RedFlag.gif” height = “10″/>”; 
                          }
                          ?>
                          <? if(isset($email)){?>
                          <a href=”emailInv.php?iid=<?=$invoice['invno']?>”>Email
Reminder</a>
                          <? }?>
                           <a href=”pdf.php?cid=<?=$invoice['cid']?>”><img
src=”http://www.devshed.com/wp-content/themes/twentyten/images/pdf.gif” width=”18″ height=”18″ border=”0″/></a></td>
        </tr>
                        <? }

The action column is in itself very dynamic, in that it compares the invoice due date with today’s date and based on the result, shows an email reminder link. An invoice becomes overdue if it is 30 days late, in which case a red flag is displayed alongside the reminder link.

You will also notice another query. This query interrogates the clientinvoice table to get the invoice description and totals. This query is located where it is because it needs the invoice ID of the current invoice in the “while loop.” Finally, if the value in the $num_invoices() is less than one, then no rows have been returned by the query, so an appropriate message is shown:

<? }
                        }else{ ?>
                        <tr>
                        <td colspan=”7″><p>No invoices available at present.</p></td>
                        </tr>
                        <? }?>

Another feature of the “action’”column is that it displays a PDF logo. When you click on it, you automatically create a pdf invoice, that will have the name of the client and current date as its name, which you can then use to email to the client. I’ve not automated the process of emailing the PDF, as I’ve already dealt with emailing clients when an invoice is “unpaid” or overdue. Below is a screenshot of what our invoice is going to look like:

{mospagebreak title=Code for Creating an Invoice}

Let’s take a look at the code behind creating a PDF invoice:

<?php
include “config.php”;
//retrieve the invoice info for the client
$query=”SELECT *,DATE_FORMAT(inv_date,’%D %M %Y’) as idate FROM invoices where invno=’”.$_GET['cid'].”‘”;
$results=mysql_query($query);
if(!$results){
exit(mysql_error());
}else{
$row = mysql_fetch_array($results);
}
$query=”SELECT name,address FROM client where id=’”.$_GET
['cid'].”‘”;
$result1 = mysql_query($query);
            if (!$result1) {
               $error = mysql_error();
}else{
$cname = mysql_fetch_array($result1);
}
$pdf = pdf_new();
//SET PDF INFO
pdf_open_file($pdf, ‘c:\’.$cname['name'].”.$td.’.pdf’);
pdf_set_info($pdf, “Author”,”InvoiceGen”);
pdf_set_info($pdf, “Title”,”Invoice”);
pdf_set_info($pdf, “Creator”, “InvoiceGen”);
pdf_set_info($pdf, “Subject”, “Invoice”);
$x=595;
$y=842;
//start building the page
pdf_begin_page($pdf, $x, $y);
pdf_set_value($pdf, ‘textrendering’, 0); // fill
//set the font
$tahoma = pdf_findfont($pdf, “Tahoma”, “host”, 1);
//write the word INVOICE at the top of the page
pdf_setfont($pdf, $tahoma, 11);
pdf_show_xy($pdf, “RE: Invoice “,250,$y-20);
//Now add the text – your address
pdf_setfont($pdf, $tahoma, 11);
pdf_show_xy($pdf, ‘PDF Invoice Generator plc.’,400,$y-40);
pdf_continue_text($pdf, ’64 Martyn Lane’);
pdf_continue_text($pdf, ‘Totley, TA4 6QQ’);
pdf_continue_text($pdf, ”);
pdf_continue_text($pdf, ‘Invoice Number:’.$row['invno'].”);
pdf_continue_text($pdf, ”.$row['idate'].”);
//Client Address
pdf_setfont($pdf, $tahoma, 11);
pdf_show_xy($pdf, ”.$cname['name'].”,20,$y-100);
pdf_continue_text($pdf, ’12 Second Street’);
pdf_continue_text($pdf, ‘Yoeville, Y19 3AS’);
pdf_setfont($pdf, $tahoma, 11);
pdf_show_xy($pdf, “RE: Invoice “,250,$y-20);
//Draw the lines
$offset = 184; $i=0;
pdf_moveto($pdf, 20,$y-160);
pdf_lineto($pdf, $x-20,$y-160);
pdf_stroke($pdf);
pdf_moveto($pdf, $x-140,$y-160);
pdf_lineto($pdf, $x-140,80);
pdf_stroke($pdf);
//Add product description and cost on top of the line
pdf_setfont($pdf, $tahoma, 11);
pdf_show_xy($pdf, “Product Description”,30,$y-150);
pdf_show_xy($pdf, “Total(incl. VAT)”,$x-100,$y-150);
//write the items for which the invoice was issued
pdf_setfont($pdf, $tahoma, 10);
pdf_show_xy($pdf, “”.$row['descr'].”",20,$y-200);
pdf_show_xy($pdf, “”.$row['totwvat'].” “,$x -100,$y-200);
pdf_setfont($pdf, $tahoma, 8);
pdf_continue_text($pdf, ”);
pdf_continue_text($pdf, ”);
pdf_continue_text($pdf, ‘VAT charged @ 17.5′);
pdf_end_page($pdf);
pdf_close($pdf);
?>

I will not explain everything about creating a PDF document because it is an entire subject in its own right. I’ve put some comments in the code to give you a general idea of what is going on. Also, it might be useful if you can read the excellent article on the Dev Articles site that demonstrates how to create a PDF document with PHP. It is very basic but it will give you a very good start in exploring the various functions available. And believe me, there are a lot of PDF functions. The important ones that you need to know are:

pdf_open_file() – Creates a blank pdf file.

pdf_set_info()   – Assigns properties to a pdf file, such as the name of the author, title and subject of the file.

pdf_begin_page() – This function allows you to set the width and height of the page. Currently there are three defined measures, each defining a particular type of  page:

  • A4 = 595 x 842
  • Letter = 612 x 792
  • Legal = 612 x 1008

pdf_findfont() – Searches for a font type in the location that you specify.

pdf_setfont() – Sets the font type  that you specified above.

pdf_show_xy() – Writes text to a specified location on a page. X defines the horizontal location and Y defines the vertical location.

pdf_continue_text() – Writes the text on the next line; basically inserts a line break.

pdf_end_page() – Shows the end of the page.

pdf_close() – Frees the pdf object.

The above functions are enough to create a simple PDF document.  For more information on how to use PDF functions in PHP, visit http://www.php.net/.

That’s all there is to showing a list of invoices in the database.

{mospagebreak title=Creating Unpaid.php}

This page shows all the unpaid invoices. This is what it looks like:

There is really nothing to it. All that it does is list all the unpaid invoices as well as what action you want to take. We follow the same method to display the needed headers and info. So create a new PHP document and save it as unpaid.php. Then at the very top of the page add the following code:

Code5 Unpaid.php
<?
include “config.php”;
$query_invoice = “SELECT *,DATE_FORMAT(inv_date,’%D %M %Y’) as
idate FROM invoices INNER JOIN client ON cid=id WHERE status =
‘Unpaid’”;
$result_invoice = mysql_query($query_invoice);
$num_invoice = mysql_num_rows($result_invoice);
?>

As before, we run a query to retrieve all the invoices marked as unpaid in the database. We also include an inner join to include matching records from the client table:

$query_invoice = “SELECT *,DATE_FORMAT(inv_date,’%D %M %Y’) as
idate FROM invoices INNER JOIN client ON cid=id WHERE status =
‘Unpaid’”;

We then store the number of rows returned in the $num_invoice variable. Next we create the table with the headers:

<tr valign=”top”>
          <td width=”13%”><strong>Invoice # </strong></td>
          <td width=”24%”><strong>Client Name
</strong></td>         

          <td width=”20%”><strong>Date Issued
</strong></td>         

          <td width=”18%”><strong>Total(incl VAT)</strong></td>
          <td width=”25%”><strong> Action </strong></td>
</tr>

Then we create the dynamic section of the table based on the returned rows:

<?
                        if($num_invoice > 0){
                        while($invoice = mysql_fetch_assoc
($result_invoice)){
                        ?>
        <tr valign=”top”>
          <td><?=$invoice['invno'];?></td>
          <td><?=$invoice['name'];?></td>
          <td><?=$invoice['idate'];?></td>
                          <?  $query_invdetails = “SELECT totwVAT
FROM clientinv WHERE finv=’”.$invoice['invno'].”‘”;
if(!$result_invdetails= mysql_query($query_invdetails)){
echo mysql_error();
}else{
$num_invdetails = mysql_num_rows($result_invdetails);
$invdetails=mysql_fetch_assoc($result_invdetails);}
?>
          <td><?=”£”.$invdetails['totwVAT'];?></td>
          <td><? if(!isset($_GET['act'])){?><a
href=”emailInv.php?iid=<?=$invoice['invno']?>”>Email
Reminder</a><? }else{?>Reminder sent!<? }?> </td>
        </tr>

Finally if there are no results returned in the query, we print a message stating so:

<? }
                        }else{?>
                        <tr>
                        <td colspan=”5″><p>No unpaid
invoices</p></td>
                        </tr>
                        <? }?>

And that’s it for this page. Next we are going to look at how to create a new invoice.

{mospagebreak title=Creating a new invoice}

To create a invoice is relatively simple. All we need to do is find out how much your invoice is, who you are invoicing and what the invoice is for. In terms of coding we will of course need a lot more information, such as who is making the invoice, and we also have to work out the total amount with and without the VAT.

Before we go further, take a look at what the “newinvoice.php” page looks like:

 

So, create a new document and save it as newinvoice.php. As always, go right to the top of the page and add the following code:

Code6 newinvoice.php

<?
ob_start();
include “config.php”;
include “FCKeditor/fckeditor.php”;
A
if(isset($_POST['submit'])){
$tot = $_POST['totxvat'] * 17.5;
$rem = $tot / 100;
$totwvat = $rem +$_POST['totxvat'];
B
$query_ins = “INSERT INTO invoices SET status=’Unpaid’,”;
$query_ins .= “inv_date = ‘”.$td.”‘,VAT = ’17.5′,cid = ‘”.$_POST
['cname'].”‘,uID = ‘”.$_SESSION['u_id'].”‘”;
mysql_query($query_ins);
$newID=mysql_insert_id();
$query_inv=”INSERT INTO clientinv SET descr=’”.trim(addslashes
($_POST['descr'])).”‘,”;
$query_inv .= “totwVAT = ‘”.$totwvat.”‘,totxVAT = ‘”.trim
(addslashes($_POST['totxvat'])).”‘,finv=’”.$newID.”‘”;
if(mysql_query($query_inv)){
header(“location:allinv.php”);
}else{
echo mysql_error();
}
}
?>

If you look at the second line of the code, you should see the following:

include “FCKeditor/fckeditor.php”;

http://images.devshed.com/ds/stories/PHP_Invoice/FCKeditor.zip

The FCKEditor is, as the name implies, a text editor for web applications. It basically provides HTML text formatting and many other features such as smilies, image management, and so on. It is freely available online; just google for it and you should get a lot of links to it.  Now I’ve marked sections of the code with the letters A and B; this is so that you know what section I’m talking about when I explain the code. It goes without saying that you should remove them when testing the code.

Section A deals with calculating the total amount with VAT included. Currently in the UK, the VAT is at 17.5; it may be different where you live. So, to find out what 17.5 of the total is, we multiply the total by 17.5 and then divide the result by 100; that should give us the answer we need. Then we simply add the answer to the total entered in the form:

$tot = $_POST['totxvat'] * 17.5;
$rem = $tot / 100;
$totwvat = $rem +$_POST['totxvat'];

On the form is a dropdown box that contains all the names in the clients table. The dropdown box is dynamically filled with the names, like so:

<select name=”cname” id=”cname”>
       <?
                        $cl_query = “SELECT name,id FROM client
ORDER BY id ASC”;
                        $cl_result = mysql_query($cl_query);
                        while($company_list = mysql_fetch_assoc
($cl_result)) {
                                    echo “<option value=”" .
$company_list['id'] . “”";
                                               echo “>” . stripslashes(htmlspecialchars($company_list
['name'])) . “</option>”;
                        }
                        ?>
            </select>

When the form is submitted, the id of the selected client is sent to the script at the top of the page. So, after calculating the VAT, a query is executed to find the name of the client matching the submitted id, and the name of that client is stored in the “$thename” variable :

$queryname=”SELECT name FROM client WHERE id = ‘”.$_POST
['cname'].”‘”;
$result=mysql_query($queryname);
$row=mysql_fetch_assoc($result);
$thename=$row['name'];

Section B primarily inserts the newly created invoice into the appropriate database tables:

$query_ins = “INSERT INTO invoices SET status=’Unpaid’,”;
$query_ins .= “inv_date = ‘”.$td.”‘,VAT = ’17.5′,cid = ‘”.$_POST
['cname'].”‘,uID = ‘”.$_SESSION['u_id'].”‘”;
mysql_query($query_ins);
$newID=mysql_insert_id();
$query_inv=”INSERT INTO clientinv SET descr=’”.trim(addslashes
($_POST['descr'])).”‘,”;
$query_inv .= “totwVAT = ‘”.$totwvat.”‘,totxVAT = ‘”.trim
(addslashes($_POST['totxvat'])).”‘,finv=’”.$newID.”‘”;
if(mysql_query($query_inv)){
header(“location:allinv.php”);
}else{
echo mysql_error();
}

I think for the most part the fields used here are self explanatory, except maybe for the cid and uID fields. The cid field identifies the client and the uID field identifies the user who issued the invoice. The reason I use numbers instead of the actual names of the client and user is because it is generally faster to work with numbers than text when using databases.

The form that takes input from the user looks like this:

<form action=”NewInvoice.php” method=”post” name=”newinv”>
            <table width=”100%” border=”0″ class=”block”>
    <tr>
    <td width=”122″ align=”center”><span class=”style1″>Select  Client to Invoice
</span></td>
    <td width=”426″ align=”center”> Invoice Amount(excluding VAT) </td>
  </tr>
  <tr>
    <td><select name=”cname” id=”cname”>
       <?
                        $cl_query = “SELECT name,id FROM client ORDER BY id ASC”;
                        $cl_result = mysql_query($cl_query);
                        while($company_list = mysql_fetch_assoc($cl_result)) {
                                    echo “<option value=”" . $company_list['id'] . “”";
                                                                        echo “>” . stripslashes
(htmlspecialchars($company_list['name'])) . “</option>”;
                        }
                        ?>
            </select>
    <span class=”smalltext”><a href=”NewClient.php”>Add New Client
</a></span></td>
    <td align=”center”><input name=”totxvat” type=”text” id=”totxvat” size=”40″ />
     </td>
  </tr>

Here we take the description of the invoice using the FCKeditor:

  <tr>
    <td colspan=”2″>Description:<br />
            <?php
$sBasePath = ‘FCKeditor/’ ;
$oFCKeditor = new FCKeditor(‘descr’) ;
$oFCKeditor->BasePath         = $sBasePath ;
//$oFCKeditor->Value             = ‘This is some <strong>sample text</strong>. You are using <a href=”http://www.fckeditor.net/”>FCKeditor</a>.’ ;
$oFCKeditor->Create() ;
?></td>
            </tr>
  <tr>
    <td colspan=”2″><div align=”center” class=”style1″>
      <input name=”submit” type=”submit” id=”submit” value=”Submit New Invoice”>
    </div></td>
  </tr>
</table>
</form>  

You do not have to use the fckeditor. You can simply use a normal HTML text box, if you like. That’s basically it for creating a new invoice. In the next article, we will be looking at client management.

Google+ Comments

Google+ Comments