Sales documents numbering
Moderator: General Moderators
Sales documents numbering
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.
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.
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:
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;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.
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.
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:
then on your invoice.php page:
ex:
Code: Select all
<form action="e;invoice.php"e; method="e;post"e;>
<input type="e;hidden"e; name="e;documenttype"e; value="e;INV"e;>
</form>Code: Select all
$type = $_POSTї'documenttype'];
$year = date("e;Y"e;,strtotime($rowї'yearforinvoice']));
$id = $rowї'idfromtablefor2005'];
$invnumber = $type.$year."e;-"e;.$id;- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
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:
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 yaddadepending on the number of documents...
for this year...
next year
...
this way, this year they will start with 2005 000 001 , 2005 000 002 , ...
next year will be 2006 000 001 , ...
Code: Select all
create table documents (
doc_id bigint not null auto_increment,
name char(255) not null,
primary key (doc_id)
);Code: Select all
alter table documents auto_increment=(year(now()) * 1000000)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 , ...
- Chris Corbyn
- Breakbeat Nuttzer
- Posts: 13098
- Joined: Wed Mar 24, 2004 7:57 am
- Location: Melbourne, Australia
- speedpacket
- Forum Newbie
- Posts: 4
- Joined: Sun Jul 03, 2005 4:18 am
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";
}
}
}