Factuurnummers en mysql
Dus ik wil factuurnummers automatisch laten verhogen maar dat kan niet met een auto-increment omdat ik die al gebruik voor mijn id field. Blijkbaar kan ik geen twee keer een auto-increment doen in dezelfde tabel?
Ik heb dus een tabel facturen met volgende velden:
Code (php)
1
2
3
2
3
ID titel aantal omschrijving e_prijs tot factnr
1 Test1 1 Testfactuur 1 100 100 ?
2 Test2 3 Testfactuur 2 100 200 ?
1 Test1 1 Testfactuur 1 100 100 ?
2 Test2 3 Testfactuur 2 100 200 ?
Hoe vul ik nu het beste de ? in? Met welke query?
Het formaat zou moeten 2015/001/VF zijn. Waarbij 2015 het jaar, 001 (moet telkens verhoogd worden met 1) het factnr zelf en VF zou moeten staan voor voorschotfactuur maar dit kan evengoed TF = tussentijdse factuur, RF = regie factuur, SF = slotfactuur.
Hetzelfde topic is door mij ook gepost op http://www.pfz.nl/forum/topic/11035-factuurnummers-en-mysql/
Gewijzigd op 31/12/2014 10:23:58 door Brecht S
3112201401 Uiteraard zet je op die kolom wel een UNIQUE.
Hm, kan ook met MySQL zie ik. Onze databasegoeroe Ger heeft er een mooie query voor gebrouwen ;-)
Gewijzigd op 31/12/2014 10:21:51 door - Ariën -
Toevoeging op 31/12/2014 10:31:35:
Ik had al zitten denken om zoiets te doen:
Code (php)
1
2
2
$seqnr = 001 + 1;
$factnr = '2015/'.$seqnr.'/VF
$factnr = '2015/'.$seqnr.'/VF
Maar hier krijg ik een probleem. Als de volgende factuur dan achteraan RF krijgt komt het volgnr (001+1) niet meer juist. Dus moet ik iets met een MAX(seqnr) of zo gaan doen? En uiteraard 2 mysql fields aanmaken in dezelfde tabel die het nummer moeten samenstellen?
Toevoeging op 31/12/2014 10:33:18:
START TRANSACTION;
SET @billno = (SELECT COALESCE(MAX(b_seq_no), 0) + 1 FROM billing WHERE b_year = YEAR(CURRENT_DATE) FOR UPDATE);
INSERT INTO billing (b_year, b_seq_no, blabla) VALUES (YEAR(CURRENT_DATE), @billno, 'omschrijving');
COMMIT;
Dit werkt helemaal niet. Of is het niet dat wat je bedoelde. Ik had dit al bekeken. Is dit normale php code?
Brecht S op 31/12/2014 10:23:11:
START TRANSACTION;
SET @billno = (SELECT COALESCE(MAX(b_seq_no), 0) + 1 FROM billing WHERE b_year = YEAR(CURRENT_DATE) FOR UPDATE);
INSERT INTO billing (b_year, b_seq_no, blabla) VALUES (YEAR(CURRENT_DATE), @billno, 'omschrijving');
COMMIT;
Dit werkt helemaal niet. Of is het niet dat wat je bedoelde. Ik had dit al bekeken. Is dit normale php code?
SET @billno = (SELECT COALESCE(MAX(b_seq_no), 0) + 1 FROM billing WHERE b_year = YEAR(CURRENT_DATE) FOR UPDATE);
INSERT INTO billing (b_year, b_seq_no, blabla) VALUES (YEAR(CURRENT_DATE), @billno, 'omschrijving');
COMMIT;
Dit werkt helemaal niet. Of is het niet dat wat je bedoelde. Ik had dit al bekeken. Is dit normale php code?
Dat is een MySQL query met exotische variabelen en functies.
Het prefix 2015 kan ik met YEAR() doen of met date('Y') in een variable
Toevoeging op 31/12/2014 10:46:20:
@Aar: ik krijg foutmeldingen op START TRANSACTION; en COMMIT; en die SET ... dus dit werkt niet. Is toch geen gewone php?
Gewijzigd op 31/12/2014 10:44:45 door Brecht S
- factuurdatum (waarvan het jaar het prefix wordt);
- uniek volgnummer;
- type-aanduiding (het postfix).
Brecht S op 31/12/2014 10:23:11:
Ik had al zitten denken om zoiets te doen:
Maar hier krijg ik een probleem. Als de volgende factuur dan achteraan RF krijgt komt het volgnr (001+1) niet meer juist. Dus moet ik iets met een MAX(seqnr) of zo gaan doen? En uiteraard 2 mysql fields aanmaken in dezelfde tabel die het nummer moeten samenstellen?
Code (php)
1
2
2
$seqnr = 001 + 1;
$factnr = '2015/'.$seqnr.'/VF
$factnr = '2015/'.$seqnr.'/VF
Maar hier krijg ik een probleem. Als de volgende factuur dan achteraan RF krijgt komt het volgnr (001+1) niet meer juist. Dus moet ik iets met een MAX(seqnr) of zo gaan doen? En uiteraard 2 mysql fields aanmaken in dezelfde tabel die het nummer moeten samenstellen?
Wat denk je dat $segnr oplevert? Voorloopnullen kun je niet in een database (als getal) opslaan. Wel als text, maar daar kun je dan weer geen 1 bij optellen.
Bij $factnr heb je het jaar hard gecodeerd. Betekent dus dat je elk jaar moet aanpassen?!
Het jaar kun je afleiden uit de factuurdatum. Maak een kolom met daarin het soort factuur en een kolom met nummer. Vervolgens haal je, voor je een nieuwe factuur toevoegt, het laatste nummer op voor de combinatie jaar / factuursoort en telt daar 1 bij op. Die schrijf je weg in de database.
Ger kan dat, ongetwijfeld, in 1 query.
Code (php)
1
CREATE FUNCTION seq(seq_name char(20)) returns int begin UPDATE seq SET val=last_insert_id(val+1) WHERE name=seq_name; RETURN last_insert_id();
Met bijbehorende tabel:
Code (php)
1
2
3
4
5
6
7
2
3
4
5
6
7
mysql> desc seq
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name | varchar(20) | NO | PRI | NULL | |
| val | int(10) unsigned | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name | varchar(20) | NO | PRI | NULL | |
| val | int(10) unsigned | NO | | NULL | |
+-------+------------------+------+-----+---------+-------+
en met (als voorbeeld):
Code (php)
1
SELECT seq('nextval') as nextval;
vraag je een factuurnummer op waarbij tevens het factuurnummer in het tabelletje automatisch opgehoogd wordt.
Gewijzigd op 31/12/2014 10:54:41 door John D
Om het niet ingewikkelder te maken dan het is: het volgende factuurnummer kun je berekenen met een (sub)query zoals SELECT GREATEST(invoice_number) + 1 AS next_invoice_number.
Code (php)
1
2
3
2
3
$year = date('Y');
$seqnr = ???
$value = 'VF';
$seqnr = ???
$value = 'VF';
Dat steek ik dan in 3 verschillende kolommen in mysql. Maar alleen is me niet duidelijk wat ik dan moet doen met de $seqnr???
Toevoeging op 31/12/2014 11:22:47:
@Ward: kan je eens een voorbeeld geven van een volledig werkende query die ik voor dit kan gebruiken? Ben hier niet meer goed mee.
Is dit dan zoiets als:
Code (php)
1
2
3
4
2
3
4
$value = 'VF';
$year = date('Y');
INSERT INTO facturen (year, seqnr, value) VALUES ('$year', (SELECT GREATEST(seqnr) + 1 AS next_invoice_number), '$value')
$year = date('Y');
INSERT INTO facturen (year, seqnr, value) VALUES ('$year', (SELECT GREATEST(seqnr) + 1 AS next_invoice_number), '$value')
Gewijzigd op 31/12/2014 11:26:18 door Brecht S
Ward van der Put op 31/12/2014 11:13:31:
Mee eens, echter dergelijke SQL vraagt vrijwel altijd een full table scan wat bij duizenden of miljoenen records impact heeft op de performance. Met name daarom heeft bijvoorbeeld Oracle de "Sequence" bedacht die (configureerbaar) gecashed kan worden in memory dat dan weer om hotspot performance issues te voorkomen. UIteraard is mijn MySQL oplossing een simpele en op die manier dan weer een hotspot kandidaat.Nice, maar als je toch een aparte tabel gaat toevoegen, dan kun je ook die ook gebruiken voor de facturen mét AUTO_INCREMENT op de primaire sleutel voor het volgnummer?
Om het niet ingewikkelder te maken dan het is: het volgende factuurnummer kun je berekenen met een (sub)query zoals SELECT GREATEST(invoice_number) + 1 AS next_invoice_number.
Om het niet ingewikkelder te maken dan het is: het volgende factuurnummer kun je berekenen met een (sub)query zoals SELECT GREATEST(invoice_number) + 1 AS next_invoice_number.
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
INSERT INTO invoices
(inv_year, inv_seq, inv_suffix, inv_date, inv_total)
SELECT
YEAR(CURRENT_DATE),
COALESCE((SELECT MAX(inv_seq) FROM invoices WHERE inv_year = YEAR(CURRENT_DATE)), 0) + 1,
'VF',
CURRENT_DATE,
14444.44
FROM DUAL
(inv_year, inv_seq, inv_suffix, inv_date, inv_total)
SELECT
YEAR(CURRENT_DATE),
COALESCE((SELECT MAX(inv_seq) FROM invoices WHERE inv_year = YEAR(CURRENT_DATE)), 0) + 1,
'VF',
CURRENT_DATE,
14444.44
FROM DUAL
inv_year en inv_seq zijn samen de PK.
Omdat de combinatie inv_year en inv_seq per definitie uniek moet zijn, kan je daar een samengestelde primary key van maken. Een aparte primary key met auto_increment heeft hier geen enkele zin.
Ger van Steenderen op 31/12/2014 12:24:14:
DUAL is bedacht door Oracle en is daadwerkelijk een tabel met 1 record en 1 attribuut met inhoud "X". Gebruikt voor pseudocolumn selecties alsFROM DUAL betekent dat je niet uit een tabel selecteert, en kan in MySQL achterwege worden gelaten.
select 1024*8 from dual
select username from dual
select sysdate from dual
John D op 31/12/2014 10:52:41:
Ik ben gewend om in Oracle met sequences te werken maar helaas kent MySQL dat (nog) niet.
Ik heb al een tijdje niet meer met Oracle gewerkt, maar volgens mij kan je daar ook geen op een andere kolom gebaseerde auto nummering gebruiken.
John D op 31/12/2014 11:36:02:
Mee eens, echter dergelijke SQL vraagt vrijwel altijd een full table scan wat bij duizenden of miljoenen records impact heeft op de performance.
Niet als er een index gebruikt kan worden, precies de reden waarom ik een subquery in de SELECT gebruik en geen group by.
Overigens los ik het zelf op via een before insert trigger.
Wat ik wel zie is dat als ik in mysql INT meegeef aan die kolom het wel werkt maar bij VARCHAR niet. Hoe komt dit?
Gewijzigd op 01/01/2015 23:11:03 door Brecht S
Nu zal je jezelf afvragen hoe je dan die voorloop nullen krijgt, in mysql is dat super eenvoudig:
Code (php)
1
2
2
ALTER TABLE invoices
CHANGE COLUMN inv_seq inv_seq SMALLINT(5) ZEROFILL NOT NULL
CHANGE COLUMN inv_seq inv_seq SMALLINT(5) ZEROFILL NOT NULL
Dit zorgt ervoor dat je altijd minimaal 5 digits te zien krijgt, dus 123 wordt 00123.
Thanks.