Mastering phpMyAdmin 3.1 for Effective MySQL Management — Save 50%
Increase your MySQL productivity and control by discovering the real power of phpMyAdmin 3.1
Simple Invoices provides freelancers, non-profits, and small business a user-friendly way to bill customers and track invoices. It's an application for people who don't want the hassle of larger accounting programs, such as GNU Cash, Quicken, or Quickbooks.
This article on Simple Invoices by Michael Badger, author of Scratch 1.4 Beginner's Guide and Zenoss Core Network and System Monitoring, provides an alternative to bulky accounting programs and the haphazard system of managing individual files by providing an intuitive web-based invoicing system. Because it's open source project, you enjoy free access to your software and your invoicing data.
As a freelancer I have one primitive motive. I want to do work and get paid. Getting paid means I need to generate invoices and keep track of them. I've tried to manage my invoices via spreadsheets and documents, but keeping track of my payments in a series of disconnected files is a fragile and inefficient process. Simple Invoices provides a solution to this.
Simple Invoices is a relatively young project, and working with it requires that you're willing to do some manual configurations and tolerate the occasional problem.
To work with and install the application, you need to be familiar with running a web server on OS X, Windows, or Linux. The next section, Web Server Required provides some out of the box server packages that allow you to run a server environment on your personal computer. It's point and click easy and perfect for an individual user.
Not up for running a web server, but still need to find a reliable invoicing application? No problem. Visit www.simpleinvoices.com for a list of hosted solutions.
Let's get started.
Web Server Required
Simple Invoices is a web application that requires Apache, PHP, and MySQL to function. Even if you're not a system administrator, you can still run a web server on your computer, regardless of your operating system. Windows users can get the required software by installing WAMP from www.wampserver.com. OS X users can install MAMP from www.mamp.info. Linux users can install Apache, MySql, and PHP5 using their distribution's software repositories.
The database administrative tool, phpMyAdmin makes managing the MySQL database intuitive. Both the WAMP and MAMP installers contain phpMyAdmin, and we'll use it to setup our databases.
Take a moment to setup your web server before continuing with the Simple Invoices installation.
Install Simple Invoices
Our first step will be to prepare the MySQL database. Open a web browser and navigate to http://localhost/phpmyadmin. Replace localhost with the actual server address. A login screen will display and will prompt you for a user name and password. Enter the the root login information for your MySQL install.
MAMP users might try root for both the user name and password. WAMP users might try root with no password. If you plan on keeping your WAMP or MAMP servers installed, setting new root passwords for your MySQL database is a good idea, even if you do not allow external connections to your server.
After you log in to phpMyAdmin, you will see a list of databases on the left sidebar; the main content window displays a set of tabs, including Databases, SQL, and Status.
Let's create the database.
- Click on the Privileges tab to display a list of all users and associated access permissions.
- Find the Add a New User link and click on it. The Add New User page displays.
- Complete the following fields:
- User Name: enter simpleinvoices
- Host: select Local
- Password: specify a password for the user; then retype it in the field provided
- Database for User: select the Create database with same name and grant all privileges option
- Scroll to the bottom of the page and click the Go button.
This procedure creates the database user and the database at the same time. If you wanted to use a database name that was different than the user name, then could have selected None for the Database for user configuration and added the database manually via the Databases tab in phpMyAdmin.
If you prefer to work with MySQL directly, the SQL code for the steps we just ran is (the *** in the first line is the password):
CREATE USER 'simpleinvoices'@'localhost' IDENTIFIED BY '***';
GRANT USAGE ON *.* TO 'simpleinvoices'@'localhost' IDENTIFIED BY
'***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;CREATE DATABASE
IF NOT EXISTS `simpleinvoices`;
GRANT ALL PRIVILEGES ON `simpleinvoices`.* TO 'simpleinvoices'@'
Now that the database is setup, let's download the stable version of Simple Invoices by visiting www.simpleinvoices.org and following the Download link. The versions are identified by the year and version. At the time of this writing, the stable version is 2010.1.
Unzip the Simple Invoices download file into a subdirectory on your web server. Because I like to install a lot of software, I like to keep the application name in my directory structure, so my example installation installs to a directory named simpleinvoices. That makes my installation available at http://localhost/simpleinvoices. Pick a directory path that makes sense for you.
Not sure where the root of your web server resides on your server? Here are some of the default locations for the various server environments:
- WAMP – C:\wamp\www
- MAMP – /Applications/MAMP/htdocs
- Linux – /var/www
Linux users will need to set the ownership of the tmp directory to the web user and make the tmp directory writable. For an Ubuntu system, the appropriate commands are:
chown -R www-data tmp
chmod -R 775 tmp
The command syntax assumes we're working from the Simple Invoices installation directory on the web server. The web user on Ubuntu and other Debian-based systems is www-data. The -R option in both commands applies the permissions to all sub-directories and files. With the chmod command, you are granting write access to the web user.
If you have problems or feel like being less secure, you can reduce this step down to one command: chmod -R 777 tmp.
We're almost ready to open the Simple Invoices installer, but before we go to the web browser, we need to define the database connections in the config/config.ini file.
At a minimum, we need to specify the database.params.username and database.params.password with the values we used to setup the database. If you skip this step and try to open Simple Invoices in your web browser, you will receive an error message indicating that your config.ini settings are incorrect.
The following screenshot shows the relevant settings in confi.ini.
Now, we're ready to start Simple Invoices and step through the graphical installer.
- Open a web browser and navigate to your installation (for example: http://localhost/simpleinvoices). Step 1: Install Database will display in the browser.
- Review the database connection information and click the Install Database button. Step 2: Import essential data displays.
- Click the Install Essential Data button to advance the installation. Step 3: Import sample data displays.
- We can choose to import sample data or start using the application. The sample data contains a few example billers, customers, and invoices. We're going to set all that up from scratch, so I recommend you click the Start using Simple Invoices button.
At this point the Simple Invoices dashboard displays with a yellow note that instructs us to configure a biller, a customer, and a product before we create our first invoice. See the following screenshot.
You might notice that the default access to Simple Invoices is not protected by a username and password. We can force authentication by adding a user and password via the People > Users screen. Then set the authentication.enabled field in config.ini equal to true.
|Increase your MySQL productivity and control by discovering the real power of phpMyAdmin 3.1|
eBook Price: $20.99
Book Price: $34.99
Create a Biller
Think of a biller as someone who makes the sale, does the work, or gets credit for the invoice. More than one biller is allowed. In my system, I have one biller defined for the work I do as a freelancer and one biller defined for selling honey.
To create a biller, click on the Add New Biller button from the dashboard. You can also click on the People tab and navigate to Billers > Add New Biller. The only required field is the Biller Name. Most of the fields in the Add Biller screen are self explanatory, but if you do have a question, click on the question mark next to the field label to display help text.
The following screenshot shows the Create Biller screen.
The Custom fields enable you to associate up to four additional properties for each biller. For example, if each biller had a website, then you could assign the label "Website" to Custom field 1. Custom fields are defined in Settings > Custom Fields.
Logo file allows you to select a logo for each biller. On the invoice, the default positioning for the logo is in the top-left. To make a logo appear in the drop-down list, copy the image file to the template/invoices/logos directory.
Invoice footer defines custom footer text that can be included on each invoice associated with the biller.
After you enter information about the biller, click the Save button. After saving the biller, a list of billers will display for review or editing. You can also reach the list of billers by navigating to People > Billers.
From the People tab, navigate to Customers > Add New Customer. The only required field on page is the Customer Name. Enter as many details as you need to and click Save to add the customer.
Again, you see a list of custom fields. These custom fields are different than the custom fields available for billers. All told, Simple Invoices lets you define four custom fields for each of the following: billers, customers, products, and invoices.
Before you can create an invoice, Simple Invoices requires that you define a product. The products you define do not need to follow any particular rules; they just need to make sense to you and your business. Example products might include, honey, consulting, or web design.
To create a product, select the Products tab and select Add Product. The Description field is the only required field; however, you can set a per unit price, tax rate, and up to four custom fields.
To view all the products, select the Manage Products button from the Products tab.
Finally, we're ready to create an invoice.
Create an Invoice
Now that we have a biller, a customer, and a product we have everything we need to create an invoice. To create an invoice, navigate to the Money tab and select New Invoice.
The biggest choice to make on this screen is whether or not you use an Itemised Style or Total Style invoice. Itemised Style is the default choice, but you can it by clicking on the Total Style hyperlink at the top of the page.
The style of your invoice depends on your personal preferences and what you are generating an invoice for. An itemised invoice allows you to specify several products or services, an if you specify a price and tax rate for your product, the invoice will automatically calculate the total for each item based on the quantity you specify in the invoice.
See the following screen shot for an example of an itemised invoice.
The Total Style invoice provides a description of the services and allows you to enter the amount owed in the Gross field and select a tax rate.
Taxes keep coming up. We've avoided the tax discussion for as long as possible. Let's take a look at how Simple Invoices can add the appropriate taxes for our individual locales.
Collect Applicable Taxes
The default installation of Simple Invoices includes several taxes and rates, available by navigating to Settings > Tax Rates. It would be a good idea to clean up this list to reflect the tax burden you bear in your part of the world.
To change the description, rate, or status of any rate, click on the Edit button in the Action column. Make your changes and click Save.
Though taxes are often calculate as a percentage of the total invoice, we can use the Tax Rates feature to add a flat rate fee onto an invoice. Some examples where you might charge a flat rate fee include an expedited processing fee, a handling charge, or a penalty.
When you edit a rate, notice that the Rate field has two values: a number and a drop-down list to select either percentage or currency. Select your currency symbol for a flat rate fee.
The following screenshot shows an example of a flat fee tax.
|Proven enterprise development tools and best practices for designing, coding, testing, and deploying PHP applications|
eBook Price: $29.99
Book Price: $49.99
Export Invoice for Customer
Now that we know what types of invoices we can create and we have the correct tax rates defined, let's finish our first invoice and prepare it for delivery. Go back to the Add Invoice screen and complete an invoice by selecting the appropriate style, biller, and customer. Save the invoice when done.
When you save the invoice, a Quick View summary displays. This view is not suitable for sending to your customer, but it gives you some interesting details, including the final cost of the invoice with all taxes calculated. At the bottom of the screen, you see a summary of the total money this customer has paid and owes you. See the following screen shot.
Now, we're ready to export the PDF and see, for the first time, our finished invoice. From the yellow bar that runs across the top of the Quick View screen, you can choose to export the invoice to PDF, DOC, or XLS formats. You can also find the export options in the Actions column in the Invoices list on the Money tab.
If you get a fatal error that says the allowed memory size is exhausted when exporting to PDF, then you need to increase the memory limit for PHP by following these steps:
- Find and edit the php.ini file for your server. The location varies depending on your operating system. On a Linux server, try /etc/php5/apache2.
- Under the Resource Limit section of the php.ini, change memory_limit = 16M to memory_limit = 96M
- Save the change.
- Restart apache and export the PDF again.
This increases the memory limit to 96MB and should eliminate any memory errors exporting the PDF.
Export an invoice now, if you haven't already done so, and let's look at it. The following screenshot shows a sample invoice.
As you review the invoice carefully, you should notice the Details section has default payment information does not make any sense; therefore, we need to change it. And depending on where up live, you might need to change the currency symbol.
Close the generated invoice and go back to Simple Invoices. Our next step is to refine the text of the invoice.
- From the Settings tab, select Invoice Preferences to display a list of preferences.
- Edit the invoice by clicking the Edit button in the Action column.
- Change the values on the preferences as you see fit using the default values as a guide.
- Save your changes and export your invoice again. The invoice automatically updates to reflect your changes.
You can create an infinite number of invoice preferences to accommodate a variety of situations based on products, customer, biller, or payment method. For example, some customers may pay by wire transfer while you expect other customers to pay by cash.
By defining an invoice preference for each scenario, you can select the appropriate preference when creating an invoice. That will create an invoice with the correct details.
Simple Invoices includes a few other default invoice preferences to let you track estimates, quotes, and generate receipts. Though we won't cover them here, they're worth exploring.
Exporting the invoices to popular document formats is not the only option we have for delivering our invoices. We can use Simple Invoices to email the invoice directly to the customer.
To send an invoice via email, you need to define your outgoing email server information in Simple Invoices' config.ini. The following screen shot shows the relevant email configuration fields and my example is configured to send via Gmail.
If you're unsure what values you need to send email, consult your email or web hosting provider. The following table provides an overview of the available settings.
The outgoing mail server address.
Enter “true” if the outgoing mail server requires authentication.
If email.smtp_auth = true, then specify your user name.
The corresponding password for email.username.
The default smtp port is 25. SSL uses 465 and TLS uses 587.
No protocol is entered by default, but ssl and tls are valid values.
Set to true to attach a read receipt to the email.
A complete description of all the config.ini settings can be found at www.simpleinvoices.org/config.
See the following screen shot for an example email settings screen.
After the config.ini is properly configured for your email server, you can send an invoice via email by clicking on the email icon, located in the Action column from the Invoice list. The following screenshot shows the email details.
If you set the email preferences for the biller and the customer, those values are populated in the Email Invoice screen. Modify the text of the email as needed and click the Email button. If the email sent successfully, you will be redirected to a confirmation screen.
This error indicates your mail server settings are not correct: Mailer Error: Language string failed to load: connect_host. Double check your config.ini settings and try again.
Processing payments lets us apply a payment from a customer against an invoice so we can keep a running tally of outstanding balances and we can document the date we received payment. Simple Invoices will show the age of outstanding invoices, which helps you keep track of who owes you money.
Not processing payments will result in all of your invoices showing outstanding balances, which is not helpful for tracking who still owes you money.
To process a payment:
- Select Invoices from the Money tab.
- Each invoice in the list has an icon for Process Payment in the Actions column. Click on it.
- Enter a description of the payment, if appropriate.
- Click the Save button.
The following screen shot shows the Process Payment page.
That completes our quick tour of Simple Invoices core feature – invoices. Don't forget to check out some of the other features, such as reports and expense tracking.