CODEWITHSUNDEEP
mysqlsqlauto-incrementsql-max
TheGix
TheGix

Reputation: 110

Numbering invoices yearly and different structures with SQL and PHP

I'm a beginner in SQL and I tried looking for an answer without any luck.

I would like to generate invoice numbers automatically with the following distinctions:

ex: ENG_2022_1, ENG_2022_2 ... ENG_2023_1

ex: FR_2022_1, FR_2022_2, FR_2022_3, ... FR_2023_1

I have thought of setting a field with auto-increment, but that wouldn't work with the structure and year distinctions unless there's a workaround?

The other solution would be to get the year and structure before invoicing, and comparing it with a SQL MAX of the invoice number, before numbering but i'm not sure how good that solution?

Any help would be greatly appreciated

Upvotes: -1

Views: 974

Answers (2)

markalex
markalex

Reputation: 13394

I would suggest you storing sequence, year, and number separately.

For this create three columns:

invoice_number   INTEGER,
invoice_year     INTEGER,
invoice_sequence VARCHAR(4)

And getting final value as

SELECT CONCAT(invoice_sequence, '_', invoice_year, '_', invoice_number) inv_full number
FROM MY_INVOICES

After that create trigger that will automatically generate invoice number on insert:

CREATE TRIGGER GENERATE_INVOICE_NUMBER
BEFORE INSERT
ON MY_INVOICES FOR EACH ROW
BEGIN
    SELECT IFNULL(MAX(invoice_number), 0) + 1
    INTO new.invoice_number
    FROM MY_INVOICES
    WHERE invoice_year = new.invoice_year
    AND invoice_sequence = new.invoice_sequence;
END

Here function MAX is used to get max value of previous number for specified sequence and year, to take into account possibility of invoice deletion and avoid violation of number uniqueness.

Upvotes: 1

KIKO Software
KIKO Software

Reputation: 16751

The use of an auto-increment column seems obvious, however, in your case you want to restart the numbering at the beginning of each year, so something should accommodate that.

I would create, at least, four columns for this in the invoice table:

  • invoice_id: This is the auto-increment column.
  • invoice_date: You can get the year with YEAR(invoice_date) from this date.
  • invoice_sequence: Either FR or ENG.
  • invoice_number: Contains the actual invoice number.

To create the invoice number you would need to know the number of invoices in a sequence for the current year. This could be retrieved in a separate query:

SELECT
  COUNT(*) AS invoice_count
FROM
  invoice_table
WHERE
  invoice_sequence = :sequence AND
  YEAR(invoice_date) = YEAR(:date);

In this query the :sequence and :date are placeholders for binding the values you want to look for.

After that you can insert the actual invoice. Here's a mock-up of such an insert:

INSERT INTO
  invoice_table
  (invoice_date,
   invoice_sequence,
   invoice_number,
   ......)
VALUES
  (:date,
   :sequence, 
   CONCAT(:sequence, '_', YEAR(:date), '_', :invoiceCount),
   ......)

In this query the :date, :sequence and :invoiceCount are placeholders for binding the values we already know. Note that in some cases you cannot repeat the same placeholder name. In that case use something like :name1 and :name2, or just ?.

Note that you could put the query to retrieve the count in the insert query as a sub-query. I didn't do that here for better readability.

Upvotes: 1

Related Questions