Sales documents numbering

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
wizzard81
Forum Commoner
Posts: 66
Joined: Wed Jan 15, 2003 6:05 am
Location: Belgium
Contact:

Sales documents numbering

Post by wizzard81 »

Hi,

I will start soon with my online invoicing script for my company.

I will store all my sales documents in the table salesdocs. Invoices, Proforma, ...

As i want a correct numbering for invoicing like INV2005-001, PRF2005-001. How i can start to make this possible?

I will work with an autoincr for the id. But i want every year my sales documents starting from 1.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

you could just generate the current year and then append it to the invoice "number" along with the other part of the string (INV etc).

ex:

Code: Select all

$year = date("Y");
// your query stuff to get your autoincremented id here
$id = $row['id'];
$invnum = "INV".$year."-".$id;
echo $invnum;
wizzard81
Forum Commoner
Posts: 66
Joined: Wed Jan 15, 2003 6:05 am
Location: Belgium
Contact:

Post by wizzard81 »

I want to start my invoice number for every new year with 01 and also of the other documents like proforma i want a 01 starting number.

Example a new year is starting. My documents printed must be like this

Invoice nr: INV2005-001
Proforma nr: PRF2005-001
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

well then you wont' want to use the autoincrement number of the mysql table, you'd have to reset it to start over (which I'm not sure can be done anyway).

you should just create a few new fields in the database, one that stores the year, and one with your "id". As soon as the years don't match, you'll just update the year to the new year and update the id to "1". If the year is the same, you'll have to select the "id", increment it by one, then update the table with the new value. That could cause a problem potentially however in that if two users perform a request at the same time, your numbering might get thrown off. The likelyhood of that happening is small, but is a potential you need to be aware of.

Another alternative would be to create a new table on the new year and then you could use the autoincremented id field. If I did that, I'd want to create a "master" table however that housed ALL of my records so that I didn't end up with several different tables.
wizzard81
Forum Commoner
Posts: 66
Joined: Wed Jan 15, 2003 6:05 am
Location: Belgium
Contact:

Post by wizzard81 »

=> Can you maybe explain it a bit more? I'm still newbie with php and don't get it fully.

The problem i have is that i have more types of sales documents then one.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

ok, those sales documents have to be identified somewhere, POST data, mysql data, whatever. so you'll need to just use whatever type of var it is and append it to your invoice "number".

ex:

Code: Select all

<form action=&quote;invoice.php&quote; method=&quote;post&quote;>
<input type=&quote;hidden&quote; name=&quote;documenttype&quote; value=&quote;INV&quote;>
</form>
then on your invoice.php page:

Code: Select all

$type = $_POSTї'documenttype'];
$year = date(&quote;Y&quote;,strtotime($rowї'yearforinvoice']));
$id = $rowї'idfromtablefor2005'];
$invnumber = $type.$year.&quote;-&quote;.$id;
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

I think this would insert the invoice in the format you're looking for... I'm still looking for a single query method though since I'd have though it could be done.

I know of "INSERT... SELECT" queries but they don't appear to allow combination with inserting other values too.

Take as an example this table:

Code: Select all

+-----------+--------------------------+------+-----+---------+----------------+
| Field     | Type                     | Null | Key | Default | Extra          |
+-----------+--------------------------+------+-----+---------+----------------+
| id        | int(5)                   |      | PRI | NULL    | auto_increment |
| year      | int(4)                   |      |     | 0       |                |
| inv_index | int(3) unsigned zerofill |      |     | 000     |                |
| inv_code  | varchar(12)              | YES  |     | NULL    |                |
+-----------+--------------------------+------+-----+---------+----------------+

Code: Select all

$q1 = "SELECT
MAX(`inv_index`) AS last_inv
FROM `invoices`
WHERE
`year`=DATE_FORMAT(NOW(), '%Y')";

$r1 = mysql_query($q1) or die(mysql_error());
$next_inv = mysql_result($r1, 0, 'last_inv')+1;
$q2 = "INSERT INTO `invoices` (
`year`,
`inv_index`,
`inv_code`
) VALUES (
DATE_FORMAT(NOW(), '%Y'),
$next_index,
CONCAT('INV', DATE_FORMAT(NOW(), '%Y'), '-', '$next_index')
)";

//Run the query yadda yadda
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

depending on the number of documents...

Code: Select all

create table documents (
  doc_id bigint not null auto_increment,
  name char(255) not null,
  primary key (doc_id)
);
for this year...

Code: Select all

alter table documents auto_increment=(year(now()) * 1000000)
next year

Code: Select all

alter table documents auto_increment=(year(now()) * 1000000)
...


this way, this year they will start with 2005 000 001 , 2005 000 002 , ...
next year will be 2006 000 001 , ...
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

timvw I think it's time you revealed the location of this PHP/MySQL fountain from which you drink :lol:
User avatar
speedpacket
Forum Newbie
Posts: 4
Joined: Sun Jul 03, 2005 4:18 am

Post by speedpacket »

Just in case you're interested, this is how we fixed it in our invoice class:

Code: Select all

function generateNewObjectID() {
  $rs = $this->db->Execute('SELECT MAX(objID) AS objID FROM ' . $this->objTable);
  if (!$rs) {
    $this->objID = date("Y", time()) . "00001";
  } else {
    if($rs->fields[objID] > date("Y", time()) * 100000) {
      $this->objID = $rs->fields[objID]+1;
    } else {
      $this->objID = date("Y", time()) . "00001";
    }
  }
}
Post Reply