Simple Invoices - Forum

laggat

Hi guys...

i adopted Simple Invoices to my needs. Therefore I had to be able to support different invoice numbering ranges:

Invoice Nr. 1, 2, 3, ...
Quote Nr. 1, 2, 3, ...

Having one incremented number (as it is your default in Simple Invoice) is not allowed here in austria.

I found some solutions (e.g. http://www.simpleinvoices.org/wiki/invoice_numbering) but I didn't like to patch the php files on every update.
I invested a bit more than a hour in your database and found a solution which finds my needs.

How it is done:
The PHP-Code is unchanged, I just added a column to si_preferences and linked it to an aditional table to handle the different numbering ranges.
All logic is done in an BEFORE INSERT trigger, and the generated invoice number is written to si_invoices.custom_field4

It is a quick fix, and I do not change the real invoice number (just a custom field). You should think about numbering just by using auto_increment, which is imho a bad idea. I think in future we should use another column than the id.
The procedure could be changed to represent more complex numbering scenarios, and therefore is very flexible.
I made no changes to the gui, so ranges have to be defined in the database directly.

So, stop talking, here's the code:

-- Support for multiple invoice number ranges in SimpleInvoices -- (c) Florian Lagg, 2009 -- This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License, or (at your option) any later version. -- This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. -- You should have received a copy of the GNU General Public License along with this program; if not, see <http://www.gnu.org/licenses/>. DELIMITER | -- add a column to si_preferences to define which number range to use ALTER TABLE `simpleinvoices`.`si_preferences` ( ADD `invoice_num_range` int(11) NOT NULL, ADD KEY `invoice_num_range` (`invoice_num_range`) ) | -- define number ranges (and current value) CREATE TABLE `simpleinvoices`.`invoice_num_ranges` ( `num_range_id` int(11) NOT NULL auto_increment, `inv_range_name` varchar(50) NOT NULL, `next_inv_pre` varchar(50) NOT NULL, `next_inv_num` int(11) NOT NULL, `next_inv_post` varchar(50) NOT NULL, PRIMARY KEY (`num_range_id`) ) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COMMENT='Defines invoice number ranges for different si_preferences' | -- introduce a function to generate invoice numbers and increment them DROP FUNCTION IF EXISTS simpleinvoices.getInvNum | CREATE FUNCTION simpleinvoices.getInvNum ( var_pref_id INTEGER ) RETURNS VARCHAR(50) SQL SECURITY INVOKER COMMENT 'Lets you use different invoice number ranges as defined in invoice_num_ranges which has a relation to si_preferences' BEGIN DECLARE var_num_range_id, var_incr_inv_num INTEGER; DECLARE var_inv_num VARCHAR(50); -- get current invoice number string, next invoice number to set SELECT (concat(inum.next_inv_pre, inum.next_inv_num, inum.next_inv_post)), inum.num_range_id, (next_inv_num+1) INTO var_inv_num, var_num_range_id, var_incr_inv_num FROM si_preferences p, invoice_num_ranges inum WHERE p.invoice_num_range=inum.num_range_id AND pref_id=var_pref_id LIMIT 1; -- set next invoice number UPDATE invoice_num_ranges SET next_inv_num=var_incr_inv_num WHERE num_range_id=var_num_range_id; RETURN var_inv_num; END | -- trigger to automatically write custom_field4 on insert in si_invoices DROP TRIGGER IF EXISTS simpleinvoices.NewInvoiceTrigger | CREATE TRIGGER simpleinvoices.NewInvoiceTrigger BEFORE INSERT ON si_invoices FOR EACH ROW BEGIN SET NEW.custom_field4=getInvNum(NEW.preference_id); END |

Use it if you want.

Regards,
Florian Lagg ( www.lagg.at

laggat

Ah yes...
I did conclude the GPL statement, so you could be sure you may use this code without my explicit permission (as it is not part of your code, where I would have to do so) ;-)

justin

hey florian

thanks so much for this code !!

i've updated the wiki page http://www.simpleinvoices.org/wiki/invoice_numbering to link to your code

cheers

justin

jmruas

Justin, let me say this feature must be inserted in the next release by default. It's very, very important to avoid legal issues.

Thank you.
By the way, SI is a must...

Jos

jmruas

The code above from laggat (thank you!) is inserted where?

Must we create a file with it (.php?) and run it?

It seems
1. alter a table
2. create a table
3. create a function

Help, please!
Jos

justin

hey guys

news re this:
in Simple Invoices 2009.2 (yes i know 2009.1 is not released yet :) ) multiple numbering ranges are now supports

were not using the very nice sql above - its a new system

basically invoice numbering now depends on a new 'numbering group' in the invoices invoice preference
- so you can have each seperate invoice preference increment in there own range
--ie invoice 1,2,3,4,5 | estimate 1,2,3,4,5
- or you can have a group of invoice preferences increment in the same range
-- ie invoice 1,3,5 | estimate 2,4,6 | quote 1,2,3,4
- or if you set eachinvoice preference to be in the same numbering group - the default action - result will be as current
-- ie invoice 1,3,5 | estimate 2,4,6 | quote 7,8,9,10

not - if you change an estimate to an invoice the ID will change to the next invoice
- ie
( you have Invoice 1,2,3 | Estimate 1,2 )
- you create Estimate 3
- client accepts the Estimate 3
- you edit the Estimate and change it to an invoice
- Estimate 3 now becomes Invoices 4

cheers

justin

jmruas

Good news, indeed!

However, as I can't use SI with the actual system numbering, the solution proposed by laggat will be ok for me in this moment and next months.

But, as newbie, I don't know what to do with the code above from laggat :( and the answer was so good...

justin

hey jose

just open phpmyadmin - go to SQL section - paste in the above code and run it

then change custom field 4 for invoices to 'Invoice ID' and test

cheers

justin

jmruas

Thank you Justin for your help.

The code give lots of errors. I talk to my ISP who ran the code, but it can't solve the problem (or doesn't know or doesn't want - I don't know).

This numbering feature is an imperative legal here where I live. I don't give up so be helped for my friends :)

Jos

elizabeth

Is there a Simple Invoices 2009.2 test mode? I want to help but I'm not a developer but I can test it :)

Apache version 2.2.11 (Unix)
PHP version 5.2.9
MySQL version 5.0.81-community
Architecture i686
Operating system Linux

justin

@elizabeth

if your really game you can try the latest development code, unfortunately youhave to use use subversion (svn) to get it
http://code.google.com/p/simpleinvoices/source/checkout
svn checkout http://simpleinvoices.googlecode.com/svn/trunk/ simpleinvoices

hopefully in a few weeks i release a test version of 2009.2

cheers

justin

laggat

Hi, excuse me guys, I was busy the last weeks and were not here in this forum.

The above script is NOT PHP - it uses SQL Triggers and Procedures.
If you get errors using it be sure you have the permission to create Triggers and Functions in MySql. Also - while Triggers are fairly new to MySql - check if your MySql installation is up-to-date.

For those who doesn't know that:

A trigger is a code inside the MySQL Database which can be executed when (before/after) a line is inserted in the database. From the above trigger "NewInvoiceTrigger" we call a function "getInvNum".

A the function gets a new invoice number and increments the number in a helping table.

All this happens inside the database, without a code change in PHP, just between PHP sends the new bill entry to the database and before returning the ID to simple Invoice (in real a bit more complex, but this should be enough for your understanding).

I left the "si_" prefix because I wanted to seperate my changes from SI-defaults.

Another question: I currently doesn't have enough time to look at the code:
What changed in the SVN-Version? Have you implemented something like this in PHP?

Thanks, I'm glad to help some.

regards,
Florian.

justin

hey florian

in svn trunk we now support multiple invoice ranges using a php solution

invoice range can be set per biller per invoice preferences
- also billers, and preferences can be grouped together so multiple preferences share the same range

i'll post more about this closer to a release

cheers

justin

laggat

hi justin,

thanks a lot. a solution in php would be easier to manage and is easier to implement when we try to change the database to somewhat other than mysql.
I have to upgrade my systems now (mine & those of my customers) - is the trunk usable for production?

Thanks,
Florian

justin

hey florian

trunk is not ready for production usage just yet, you can play with it - but still very much a work-in-progress

cheers

justin

laggat

thanks for your information.
continue the project, it's getting better every time I look at it.

cheers
Flo.

justin

no worries florian

cheers

justin

mkblue

Hi there laggat and justin

I can't get that sql query to run...

Running it as root on xampp...

Is there any problem if I have a custom name for the database?

Thanks

justin

@mkblue

just edit the sections that reference 'simpleinvoices' to whatever your database is called

note: multiple invoice number ranges will be available in the next major release 2010.2 of Simple Invoices
- note: its not using the above method - a new php method to sort it out

cheers

justin

mkblue

I'm unable to run this sql query.. i'm still getting errors. Is there someone that have run the query with success?

Can someone please test it.

Thanks

justin

i havent run this query before - just assumed it worked

what errors are you getting?

cheers

justin

Simple Invoices - Forum

Howdy, Stranger!

Categories